Mysql面试专题

mysql面试题必知必会

一、常见的数据结构

1. 二叉树
   从左到右依次增大，若自增则二叉树就变成了一条单向的链表;
2. 红黑树
   对二叉树做了平衡，但数据量大的话会导致树的高度太高，导致查询效率低
   
3. B-tree
   叶节点具有相同的深度，叶节点的指针为空，所有索引元素不重复，节点中的数据索引从左到右递增排列，索引元素存放了数据，这样每页存放的索引就会变少，随着数据量的增大，树的高度也会变大可能会大于3层，而B+tree的索引全部存放在非叶子节点，B-tree与B+tree的区别在B+tree中介绍；
   
4. B+Tree
   B+Tree(B-Tree变种)，非叶子节点不存储data，只存储索引(冗余)，可以放更多的索引，叶子节点包含所有索引字段，叶子节点用双向指针连接，提高区间访问的性能；页节点分配的空间大小一般是16k，Show global status like ‘innodb_page_size’;执行该sql可以查看页空间大小，指针是下个叶子节点的地址，一个指针大小约占6b，一个bigint类型的字节占8b，那么一页16kb大约能放16kb*1024/(6+8)=1170个元素，三层大约能放千万级别数据量，根节点可能放在内存里面，其他节点放磁盘中，影响查找速度的指标树的高度，mysql最终选择使用B+tree是因为非叶子节点不存数据，从而每页存放的索引更多，减少了树的高度，加大了查找的速度，而B-Tree叶子节点和非叶子节点都存放数据，导致每页能够存放的索引很少，从而导致树的高度变大，增加了查找的次数，使查询速度变慢，同时B+tree的叶子节点之间存在双向指针，因此mysql最终选择了B+tree来作为索引的数据结构；存储引擎是表级别的。
   
   5.Hash
   对索引的key进行一次hash计算就可以定位出数据存储的位置
   很多时候Hash索引要比B+ 树索引更高效
   仅能满足 “=”，“IN”，不支持范围查询
   存在hash冲突问题
   

二、Myisam存储引擎索引实现

MyISAM索引文件和数据文件是分离的(非聚集)，有三个文件存放表结构、表数据、表索引
tablename.frm 表结构文件
tablename.MYD 表数据文件
tablename.MYI 表索引文件
myisam存储引擎的索引也是B+tree数据结构，但其叶子节点存放的是数据的物理地址值，根据索引
从索引文件中查到数据的物理地址后再去表数据文件中查找该数据返回，该索引也叫非聚簇索引

三、Innodb存储引擎索引实现

表数据文件本身就是按B+Tree组织的一个索引结构文件，聚集索引-叶节点包含了完整的数据记录；
Innodb存储引擎表包含两个文件，
tablename.frm：表结构文件
tablename.ibd：索引和数据文件
为什么建议innodb表必须建主键，且推荐使用整型的自增主键？
如果一张innodb表中存在主键，那么会根据主键去建索引，如果表无主键也无索引，那么mysql会从所有列中选择所有数据都不相同的一列去建索引结构，如果没有选择到，那mysql会自己建一个隐藏列作为主键唯一id（类似rowId），并根据这个隐藏主键来建索引结构即根据隐藏主键的id来组织整张表的数据结构；mysql的资源是非常宝贵的，能自己主动来做的一定要自己来做，建表的时候添加主键，mysql会根据指定的主键来组织整张表的数据，而不会自己去创建一个隐藏列来维护数据，减轻mysql自身的工作。
为什么建议使用整型的自增主键？
如果是整型的只比较一次，比如3>2，而uuid或者string类型，则会逐个比较字符，显然速度很慢。
主键字段大小越小，每个非叶子节点存放的索引就越大，占用的磁盘空间也就相对较小，生产环境的磁盘性能很高，但也很贵，可以节省磁盘空间；如果不是自增，会导致树的分裂以及树的平衡发生，而自增会一直往后插入分节点，而不是分裂和平衡节点，这样自增的主键效率会更高一些。一张innodb表只存在一个聚集索引，而二级索引的叶子节点索引存放的是主键索引的rowId，一张表每增加一个索引就会多一个索引的树状结构。
为什么innodb表非主键索引结构叶子节点存储的是主键值？
为了一致性、为了节省空间

四、索引最左前缀原理

新建一个索引或联合索引，数据会根据索引字段把整张表的数据排好序，会根据联合索引的顺序，从左到右进行比对，将第一个字段根据ASCII码表大小比较，哪个大就放前面，如果相等，再比较第二个字段，哪个大继续放前面，依次循环比较大小，就这样把整张表的数据按顺序排好序，所以查找的时候要根据这个排序的原则进行，不然就会导致索引失效，这也就是所谓的最左前缀原则的由来。

五、Explain字段详解

EXPLAIN关键字可以模拟优化器执行SQL语句，分析查询语句或是结构的性能瓶颈，在 select 语句之前增加 explain 关键字，MySQL 会在查询上设置一个标记，执行查询返回执行计划的信息，而不是执行这条SQL。

执行完上面的sql，会出现下图信息：

1.id列： id列的编号是 select 的序列号，有几个 select 就有几个id，并且id的顺序是按 select 出现的顺序增长的，id列越大执行优先级越高，id相同则从上往下执行，id为NULL最后执行。
2.select_type列：
1）simple：简单查询。查询不包含子查询和union

2）primary：复杂查询中最外层的 select
3）subquery：包含在 select 中的子查询（不在 from 子句中）
4）derived：包含在 from 子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表
下图中的sql执行顺序是id=3，id=2，id=1；

5）union：在 union 中的第二个和随后的 select

3. table列： 这一列表示 explain 的一行正在访问哪个表。当 from 子句中有子查询时，table列是 格式，表示当前查询依赖 id=N 的查询，于是先执行 id=N 的查询。当有 union 时，UNION RESULT 的 table 列的值为<union1,2>，1和2表示参与 union 的 select 行id。
4. type列： 这一列表示关联类型或访问类型，即MySQL决定如何查找表中的行，查找数据行记录的大概范围。依次从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL
一般来说，得保证查询达到range级别，最好达到ref。NULL：mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。例如：在索引列中选取最小值，可以单独查找索引来完成，不需要在执行时访问表。

const, system： mysql能对查询的某部分进行优化并将其转化成一个常量（可以看show warnings 的结果）。用于 primary key 或 unique key 的所有列与常数比较时，所以表最多有一个匹配行，读取1次，速度比较快。system是const的特例，表里只有一条元组匹配时为system；

eq_ref： primary key 或 unique key 索引的所有部分被连接使用 ，最多只会返回一条符合条件的记录。这可能是在 const 之外最好的联接类型了，简单的 select 查询不会出现这种 type。

ref： 相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行。
简单 select 查询，name是普通索引（非唯一索引）

关联表查询，idx_film_actor_id是film_id和actor_id的联合索引，这里使用到了film_actor的左边前缀film_id部分。

range： 范围扫描通常出现在 in(), between ,> ,<, >= 等操作中。使用一个索引来检索给定范围的行。

index： 扫描全索引就能拿到结果，一般是扫描某个二级索引，这种扫描不会从索引树根节点开始快速查找，而是直接对二级索引的叶子节点遍历和扫描，速度还是比较慢的，这种查询一般为使用覆盖索引，二级索引一般比较小，所以这种通常比ALL快一些。

ALL： 即全表扫描，扫描你的聚簇索引的所有叶子节点。通常情况下这需要增加索引来进行优化了。

5. possible_keys列
这一列显示查询可能使用哪些索引来查找。 explain 时可能出现 possible_keys 有列名，而 key 显示 NULL 的情况，这种情况是因为表中数据不多，mysql认为索引对此查询帮助不大，选择了全表查询。 如果该列是NULL，则没有相关的索引。在这种情况下，可以通过检查 where 子句看是否可以创造一个适当的索引来提高查询性能，然后用 explain 查看效果。
6. key列
这一列显示mysql实际采用哪个索引来优化对该表的访问。如果没有使用索引，则该列是 NULL。如果想强制mysql使用或忽视possible_keys列中的索引，在查询中使用 force index、ignore index，但不建议这样做。
7. key_len列
这一列显示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。 举例来说，film_actor的联合索引 idx_film_actor_id 由 film_id 和 actor_id 两个int列组成，并且每个int是4字节。通过结果中的key_len=4可推断出查询使用了第一个列：film_id列来执行索引查找。

key_len计算规则如下：
字符串： char(n)和varchar(n)，5.0.3以后版本中，n均代表字符数，而不是字节数，如果是utf-8，一个数字或字母占1个字节，一个汉字占3个字节
char(n)： 如果存汉字长度就是 3n 字节
varchar(n)： 如果存汉字则长度是 3n + 2 字节，加的2字节用来存储字符串长度，因为varchar是变长字符串
数值类型：
tinyint： 1字节
smallint： 2字节
int： 4字节
bigint： 8字节　　
时间类型　
date： 3字节
timestamp： 4字节
datetime： 8字节
如果字段允许为 NULL，需要1字节记录是否为 NULL，索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引。
8. ref列
这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），字段名（例：film.id）
9. rows列
这一列是mysql估计要读取并检测的行数，注意这个不是结果集里的行数。
10. Extra列
这一列展示的是额外信息。常见的重要值如下：
1）Using index：使用覆盖索引
覆盖索引定义：mysql执行计划explain结果里的key有使用索引，如果select后面查询的字段都可以从这个索引的树中获取，这种情况一般可以说是用到了覆盖索引，extra里一般都有using index；覆盖索引一般针对的是辅助索引，整个查询结果只通过辅助索引就能拿到结果，不需要通过辅助索引树找到主键，再通过主键去主键索引树里获取其它字段值；

2）Using where： 使用 where 语句来处理结果，并且查询的列未被索引覆盖

3）Using index condition： 查询的列不完全被索引覆盖，where条件中是一个前导列的范围；

4）Using temporary： mysql需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化。
actor.name没有索引，此时创建了张临时表来distinct

film.name建立了idx_name索引，此时查询时extra是using index,没有用临时表

5）Using filesort： 将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一
般也是要考虑使用索引来优化的。
actor.name未创建索引，会浏览actor整个表，保存排序关键字name和对应的id，然后排序name并检索行记录

film.name建立了idx_name索引,此时查询时extra是using index

六、索引最佳实战

七、SQL底层执行原理详解

客户端连接mysql数据库，mysql有一张系统用户表，从客户端连接mysql服务端，mysql会去加载users表中的数据校验连接的host，用户名和密码等连接信息，如果校验通过则会开辟一个session空间将user用户表中的用户信息缓存在该session中，执行sql的时候还要使用该user表中的权限校验该sql 的执行权限，msql连接是长连接，一般默认是8小时，当客户端连接上mysql服务端后去修改user表中的信息，那这时已连接的客户端不会感知到，需要等8小时断开连接重连后才会再次从user表中加载校验，才会生效。
Mysql为什么使用长连接，而不是修改完权限后立马加载到session空间生效呢？
如果修改后立即更新session中的user表，更新完所有的连接需要一定的时间，可能会造成短暂的不可用，导致业务交易全部失败。
bin-log归档
删库是不需要跑路的，因为我们的SQL执行时，会将sql语句的执行逻辑记录在我们的bin-log当中，什么是bin-log呢？
binlog是Server层实现的二进制日志,他会记录我们的cud操作。Binlog有以下几个特点：
1、Binlog在MySQL的Server层实现（引擎共用）
2、Binlog为逻辑日志,记录的是一条语句的原始逻辑
3、Binlog不限大小,追加写入,不会覆盖以前的日志
如果，我们误删了数据库,可以使用binlog进行归档!要使用binlog归档，首先我们得记录binlog，因此需要先开启MySQL的binlog功能。

八、Mysql索引优化实战一

索引优化总结：
1、MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。index
效率高，filesort效率低。
2、order by满足两种情况会使用Using index。

1. order by语句使用索引最左前列。
2. 使用where子句与order by子句条件列组合满足索引最左前列。
   3、尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则。
   4、如果order by的条件不在索引列上，就会产生Using filesort。
   5、能用覆盖索引尽量用覆盖索引
   6、group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于group
   by的优化如果不需要排序的可以加上order by null禁止排序。注意，where高于having，能写在where中
   的限定条件就不要去having限定了。
   Using filesort文件排序原理详解
   filesort文件排序方式
   单路排序： 是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序；用trace工具可
   以看到sort_mode信息里显示< sort_key, additional_fields >或者< sort_key, packed_additional_fields >
   双路排序（又叫回表排序模式）： 是首先根据相应的条件取出相应的排序字段和可以直接定位行
   数据的行 ID，然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段；用trace工具
   可以看到sort_mode信息里显示< sort_key, rowid >，MySQL 通过比较系统变量 max_length_for_sort_data(默认1024字节) 的大小和需要查询的字段总大小来判断使用哪种排序模式。
   如果字段的总长度小于max_length_for_sort_data ，那么使用 单路排序模式；
   如果字段的总长度大于max_length_for_sort_data ，那么使用 双路排序模∙式。

单路排序的详细过程：

1. 从索引name找到第一个满足 name = ‘zhuge’ 条件的主键 id
2. 根据主键 id 取出整行，取出所有字段的值，存入 sort_buffer 中
3. 从索引name找到下一个满足 name = ‘zhuge’ 条件的主键 id
4. 重复步骤 2、3 直到不满足 name = ‘zhuge’
5. 对 sort_buffer 中的数据按照字段 position 进行排序
6. 返回结果给客户端

双路排序的详细过程：

1. 从索引 name 找到第一个满足 name = ‘zhuge’ 的主键id
2. 根据主键 id 取出整行，把排序字段 position 和主键 id 这两个字段放到 sort buffer 中
3. 从索引 name 取下一个满足 name = ‘zhuge’ 记录的主键 id
4. 重复 3、4 直到不满足 name = ‘zhuge’
5. 对 sort_buffer 中的字段 position 和主键 id 按照字段 position 进行排序
6. 遍历排序好的 id 和字段 position，按照 id 的值回到原表中取出 所有字段的值返回给客户端

对比两个排序模式，单路排序会把所有需要查询的字段都放到 sort buffer 中，而双路排序只会把主键
和需要排序的字段放到 sort buffer 中进行排序，然后再通过主键回到原表查询需要的字段。
如果 MySQL 排序内存 sort_buffer 配置的比较小并且没有条件继续增加了，可以适当把
max_length_for_sort_data 配置小点，让优化器选择使用双路排序算法，可以在sort_buffer 中一次排序更
多的行，只是需要再根据主键回到原表取数据。
如果 MySQL 排序内存有条件可以配置比较大，可以适当增大 max_length_for_sort_data 的值，让优化器
优先选择全字段排序(单路排序)，把需要的字段放到 sort_buffer 中，这样排序后就会直接从内存里返回查询结果了。
所以，MySQL通过 max_length_for_sort_data 这个参数来控制排序，在不同场景使用不同的排序模式，
从而提升排序效率。
注意，如果全部使用sort_buffer内存排序一般情况下效率会高于磁盘文件排序，但不能因为这个就随便增
大sort_buffer(默认1M)，mysql很多参数设置都是做过优化的，不要轻易调整。

索引设计原则
1、代码先行，索引后上
不知大家一般是怎么给数据表建立索引的，是建完表马上就建立索引吗？
这其实是不对的，一般应该等到主体业务功能开发完毕，把涉及到该表相关sql都要拿出来分析之后再建立
索引。
2、联合索引尽量覆盖条件
比如可以设计一个或者两三个联合索引(尽量少建单值索引)，让每一个联合索引都尽量去包含sql语句里的
where、order by、group by的字段，还要确保这些联合索引的字段顺序尽量满足sql查询的最左前缀原则。
3、不要在小基数字段上建立索引
索引基数是指这个字段在表里总共有多少个不同的值，比如一张表总共100万行记录，其中有个性别字段，
其值不是男就是女，那么该字段的基数就是2。如果对这种小基数字段建立索引的话，还不如全表扫描了，因为你的索引树里就包含男和女两种值，根本没法进行快速的二分查找，那用索引就没有太大的意义了。一般建立索引，尽量使用那些基数比较大的字段，就是值比较多的字段，那么才能发挥出B+树快速二分查找的优势来。
4、长字符串我们可以采用前缀索引
尽量对字段类型较小的列设计索引，比如说什么tinyint之类的，因为字段类型较小的话，占用磁盘空间也会
比较小，此时你在搜索的时候性能也会比较好一点。当然，这个所谓的字段类型小一点的列，也不是绝对的，很多时候你就是要针对varchar(255)这种字段建立索引，哪怕多占用一些磁盘空间也是有必要的。对于这种varchar(255)的大字段可能会比较占用磁盘空间，可以稍微优化下，比如针对这个字段的前20个字符建立索引，就是说，对这个字段里的每个值的前20个字符放在索引树里，类似于 KEY index(name(20),age,position)。此时你在where条件里搜索的时候，如果是根据name字段来搜索，那么此时就会先到索引树里根据name字段的前20个字符去搜索，定位到之后前20个字符的前缀匹配的部分数据之后，再回到聚簇索引提取出来完整的name字段值进行比对。但是假如你要是order by name，那么此时你的name因为在索引树里仅仅包含了前20个字符，所以这个排序是没法用上索引的， group by也是同理。所以这里大家要对前缀索引有一个了解。
5、where与order by冲突时优先where
在where和order by出现索引设计冲突时，到底是针对where去设计索引，还是针对order by设计索引？到底是让where去用上索引，还是让order by用上索引?一般这种时候往往都是让where条件去使用索引来快速筛选出来一部分指定的数据，接着再进行排序。因为大多数情况基于索引进行where筛选往往可以最快速度筛选出你要的少部分数据，然后做排序的成本可能会小很多。
6、基于慢sql查询做优化
可以根据监控后台的一些慢sql，针对这些慢sql查询做特定的索引优化。

九、分页查询优化