Mysql索引底层数据结构与算法、Explain、SQL 优化

为什么建议InnoDB表必须建主键?并且推荐使用整型的自增主键？

如果不由我们人工去创建主键，mysql也会帮我们去建立主键，没有必要把这么简单地工作交给Mysql。
整型比较比UUID比较要快，而且更加节省空间。

设置自增有一个考虑就是在插入元素数据的时候由于需要维护一颗B+树，这颗树根据主键排序的，为了维护树平衡，需要一定的时间开销，设置主键自增之后，直接往后面追加元素即可，减少了时间开销。

为什么非主键索引结构叶子节点存储的是主键值？

为了一致性和节省存储空间。

explain extended 可以多展示哪些字段？

filtered，这个和mysql版本有关，有的mysql版本不加extended关键字，也可以展示这个字段。

这是一个半分比的值， rows * filtered / 100 可以估算出将要和 explain 中前一个表进行连接的行数（前一个表指 expalin 中的id值比当前表id值小的表）。

explain partitions可以多展示哪些字段？

partitions, 如果查询基于分区表的话，会显示查询将访问的分区。

怎么查看mysql帮我们做了哪些优化？

在执行完了explain之后，可以使用sho warnings语句查看mysql帮我们执行哪些优化。

explain select_type列的含义？

select_type 表示对应简单还是复杂查询。

1）simple：简单查询，查询不包含子查询和union

2）primary：复杂查询中最外层的select

3）subquery：包含在select中的子查询（不在from 子句中）

4）derived：包含在from子句中的子查询。MySQL会将结果存放在一个临时表中，也称为派生表（derived的英文含义）

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

explain type列有什么含义？

这一列表示关联类型，即MySQL决定如何查找表中的行，查找数据记录的最大范围。

依次从最优到最差分别为：system > const > eq_ref > ref > range > index > ALL。

一般来说，得保证查询达到range级别，最好达到ref。

NULL: mysql能够在优化阶段分解查询语句，在执行阶段用不着再访问表或索引。例如: 在索引列中选取最小值，可以单独查找索引来完成，不需要在执行时访问表。

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

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

ref:相比 eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，索引要和某个值相比较，可能会找到多个符合条件的行.

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

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

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

index效率会比ALL高一些，因为index只包含部分字段，ALL包含所有字段。

explain key_len列有什么含义？

这一列显示了mysql在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。

key len计算规则如下:
字符串
　　char(n): n字节长度
　　varchar(n): 如果是utf-8，则长度 3n +2 字节，加的2字节用来存储字符串长度

数值类型
　　tinyint: 1字节
　　smallint: 2字节
　　int: 4字节
　　bigint: 8字节
时间类型
　　date: 3字节
　　timestamp:4字节
　　datetime: 8字节
如果字段允许为 NULL，需要1字节记录是否为 NULL

索引最大长度是768字节，当字符串过长时，mysql会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引。

explain ref列有什么含义？

这一列显示了在key列记录的索引中，表查找值所用到的列或常量，常见的有: const (常量)，字段名(例: film.id).

explain Extra列有什么含义？

1) Using index: 使用覆盖索引
覆盖索引定义: mysq执行计划explain结果里的key有使用索，如果selet后面查询的字段都可以从这个索引的树中获取，这种情况一般可以说是到了覆盖索引，extra里一般都有using index;覆盖索引一般针对的是辅助索，整个查询结果只通过辅助索就能拿到结果，不需要通过辅助索引树找到主键，再通过主键去主键索引树里获取其它字段值

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

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

4) using temporary: mysq需要创建一张临时表来处理查询。出现这种情况一般是要进行优化的，首先是想到用索引来优化。

5) Using filesort: 将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘完成排序。这种情况下一般也是要考虑使用索引来优化的

6) Select tables optimized away :使用某些聚合函数 (比如 max、 min) 来访问存在索引的某个字段

explain select min(id) from film;

索引下推

索引下推 - 模糊匹配

为什么范围查找Mysql没有用索引下推优化?
估计应该是Mysql认为范围查找过滤的结果集过大，like KK% 在绝大多数情况来看，过滤后的结果集比较小，所以Mysq选择给 like KK% 用索下推优化。

 

set session optimizer_trace="enabled=on", end_markers_in_json=on; -- 开启trace
select * from information_schema.OPTIMIZER_TRACE;

索引设计原则

1. 代码先行，索引后上；
2. 联合索引尽量覆盖条件；
3. 不要在小基数字段上建立索引；
4. 长字符串我们可以采用前缀索引；
5. where与order by冲突时优先where；
6. 基于慢sql查询做优化；可以开启慢查询日志：
set global slow_query_log=1
并且设置多长时间算是慢查询：
set global long_query_time=具体时间，单位为s；

排序分页的优化

select * from employees order by name limit 90000,5;

select * from employees e inner join (select id from employees order by name limit 90000,5) ed on e.id = ed.id;

join连接优化

1. 嵌套循环连接 Nested-Loop Join(NLJ)算法
2. 基于块的嵌套循环连接 Block Nested-Loop Join(BNL) 算法
join连接查询时，尽量让小表驱动大表，
straight join解释: straight join功能同join类似，但能让左边的表来驱动右边的表，能改表优化器对于联表查询的执行顺序。
比如: select* from t2 straight join t1 on t2.a = t1.a; 代表指定mysql选着 t2 表作为驱动表。
straight join只适用于inner join,并不适用于left join,right join。 (因为left join, right join已经代表指定了表的执行顺序)。
尽可能让优化器去判断，因为大部分情况下mysq优化器是比人要聪明的。使用straight join一定要慎重，因为部分情况下人为指定的执行顺序并不一定会比优化引擎要靠谱。

SQL 规约

表记录超过500w行，或者超过2G，要考虑分库分表。

业务上具有唯一特性的字段，即使是组合字段，也必须建成唯一索引。

超过三个表禁止join。需要join的字段，数据类型保持绝对的一致；多表关联查询时，保证被关联的字段需要有索引。

 在 varchar 字段上建立索引时，必须指定索引长度，没必要对全字段建立索引，根据实际文本区分度决定索引长度。

页面搜索严禁左模糊或者全模糊，如果需要请走搜索引擎来解决。

in操作能避免则避免，若实在避免不了，需要仔细评估in后面的集合元素数量，控制在1000个之内。

End!