PHP面试宝典之Mysql数据库基础篇

字符类型：

• tinyint(4)：占1个字节，4代表字段值长度，用0填充，搭配zero fill使用
  有符号：取值范围 负128 ～ 正127；
  无符号：取值范围 0 ～ 255；
  默认无符号；

• int(11)：占4个字节，11代表字段值长度，用0填充，搭配zero fill才起作用
  有符号：取值范围 负2开头10位数 ～ 正2开头10位数；
  无符号：取值范围 0 ～ 正4开头10位数，使用unsigned设置；
  默认无符号；

• bigint(20)：占8个字节，20代表字段值长度，用0填充，搭配zero fill才起作用
  有符号：取值范围 负9开头19位数 ～ 正9开头19位数；
  无符号：取值范围 0 ～ 正18 开头20位数，使用unsigned设置；
  默认无符号；
  注1：在32位操作系统中，可能会出现内存溢出问题；
  注2：手机号是字符串，不是数字，所以不建议使用bigint；

• char(n)：定长字符串，英文字符占n个字节，中文utf8格式，占用n*3个字节；n表示字符长度，中英文相同；
  注：字段全部是国内手机号时，建议使用char(11)；

• varchr(n)：可变长字符串，有效字节数65532；1~ 3个字节存储数据大小
  utf8格式：n最大值 = 65532/3；
  gbk格式：n最大值 = 65532/2；
  注：当一个字段用来存储：座机和手机号时，建议使用；

• 其余字符类型：
  datetime：时间格式
  text：文本，最多可存储64 kb
  decimal：金额

mysql函数

max：最大值
min：最小值
count：行数
sum：总和
avg：平均值
ifnull：是否为空
if：控制语句
case：搜索语句

如何设计数据库？

1：三范式：
原子性：列、字段的不可拆分
唯一性：设置主键
冗余性：设置字段与主键的直接关联关系

2：表的拆分：根据业务场景进行逻辑拆分（一对一、一对多、多对多）

3：选择数据库引擎 innodb 或 myisam

4：添加索引（注：单表索引最好不超过5个）

5：单表字段最好不超过20个

6：合适的字段类型（使用小类型替代大类型，比如tinyint 代替int，或int代替bigint）

7：逻辑删除代替物理删除

8：字段添加注释

一张表最多能创建多少个字段？
innodb引擎：一张表最多可创建1000个字段

如果创建一个没有主键的表会怎样？
可以创建成功，但mysql会默认创建一个隐藏的聚簇索引，后续再创建主键时，会更改布局，新建一个聚簇索引

myisam和innodb引擎的区别？

innodb引擎：支持事务、支持外键，是聚簇索引，行级锁；
5.5之后的默认引擎，5.7之后支持全文检索

myisam引擎：不支持事务和外键，是非聚簇索引，表级锁；

MySQL常见索引？

主键索引、普通索引、联合索引、唯一索引（数据的唯一，一张表可以有多个唯一索引）、空间索引

如何创建索引？注意事项？

哪些适合创建索引？
1：字段值有唯一性限制时，必须建成唯一索引

2：where子句中的条件字段，创建索引

3：group by和order by的列，创建索引

4：连表时的关联字段，加索引，且在所有表中，字段名和字段类型保持一致

5：使用类型小的列，创建索引；如：能用int就不用bigint

6：字符串字段，使用前缀索引

7：使用最频繁的列，放在联合索引的左侧

8：多个字段都要创建索引时，联合索引优于单值索引

哪些不适合创建索引？

1：where子句中不使用的字段，不建索引

2：数据量小的表，不建索引（300条以内不用加）

3：大量重复数据的列，不建索引（重复度高于10%，不适合建索引）

4：经常更新的表，少建索引

5：不建议用无序的值作为索引；例：哈希，md5，无序长字符串

6：删除不再使用或使用率少的索引

7：不重复定义索引；例：联合索引中的字段，不需要单独再创建索引

主键索引和唯一索引的区别？

1：主键一定包含唯一，但唯一不一定是主键

2：主键不能为空，唯一可以为空

3：一个表只能有一个主键，但可以有多个唯一

4：主键可以做为其他表的外键

注：null会破坏索引结构，可能会导致该列不走索引，唯一索引可以有多个null，因为null是未知，多个未知不代表重复，但null值不被记录在索引上；唯一索引上只能有一个空字符串，不能有多个

主键可以被修改吗？

1：主键值不能修改，只能删除后，重新添加

2：主键字段，可以修改（id是主键改为name是主键），但一般不这么做，一般情况会将ID的主键属性删除，然后给name添加主键

索引优化方案？

前缀索引、覆盖索引、主键自增、not null、防止索引失效

前缀索引：在字段中字符串的前几个字符建立索引，如：邮箱字段中@符号之前的字符创建索引；
注：order by 无法使用前缀索引，进行索引覆盖

覆盖索引：select 查询字段，可以通过where子句中的索引列来实现，既查询字段值在索引的叶子节点中存在，不需要回表就可以返回结果

什么是MySQL回表？

以innodb为例，如果不使用主键索引查询，且查询结果列不在where子句中的索引列中，就会先由子句索引中的二级索引，查询索引树，找到对应的主键ID，再通过主键ID的索引树，去查询所在行数据，但myisam不同，它的主键索引也没保存行数据，所以无论怎样都会回表；回表会导致多次io，消耗更多资源

简答：如果innodb，不使用主键做条件查询，并且查询结果包含了除条件外的其他字段，就无法一次性得到结果，就必须先查到主键， 再使用主键去索引树中，找到对应的全部数据

索引失效原因？

1：模糊查询以%开头

2：where子句中，对索引做运算，函数

3：联合索引要正确使用最左匹配原则

4：where子句中，or前是索引，or后不是索引，也会导致索引失效

5：索引列存在null值，也会导致索引失效

6：字符串类型，不加引号不走索引，加引号才走索引，int类型加不加都走索引

索引最左匹配原则？

以a、b、c三个字段，做联合索引为例：它会生成三个索引，分别是：（a）、（a、b）、（a、b、c）
在联合索引查询时，优先走最左侧索引：where子句中必须以a为起点，直到遇到范围查询（>，<）为止，之前的字段（包括范围查询字段）都会走索引，但范围之后的索引失效

原因：范围查询之前的索引是有序的，而范围之后的索引是无序的

MySQL优化方案：

1：尽量全值匹配（=）

2：最左匹配原则

3：不对索引列做任何操作

4：尽量使用覆盖索引

5：不等于、like要慎用

6：字符串类型索引，要加引号

SQL性能优化

1：不能使用select *，失去了覆盖索引的效果

2：小表驱动大表时使用in，不使用exists

3：批量插入代替循环插入

4：用limit限制返回条数

5：分页查询优化，页码增大时，查询效率降低，可以借助上次查询最大id，做条件过滤

6：缩小数据集的条件放前面，能排除大量数据的条件放前面

7：连表查询尽量不超过3张表

i

in和exists的区别？

exists：是先查前表中所有数据，然后过滤后面的子句，是否有包含

in：是先执行子句，再通过结果去前表中查询结果集

in走不走索引？

in通常是走索引的，但当in后面的数据集，在表中超过30%的匹配时，是不走索引，而是全表扫描

between、in、or的效率对比

between最快，in次之，or最慢
between > in > or

全文检索

字段类型：fulltext（全文索引）
设置全文索引：fulltext(字段名) with parser ngram comment ‘index_type=smartcn’

查询方式：match（字段名） against(‘搜索信息’)
smartcn：分词器插件，提高中文查询准确率
boolean：高级搜索【‘+要查询信息 且不包含信息’in boolean mode】
自定义分词器：用特殊符号，将字段按自己需求切割成自定义 分词