Mysql：索引分析及优化

索引的数据结构

为什么InnoDB使用B+树作为索引的数据结构？
查用的数据结构有：Hash表、二叉树、平衡二叉树、B树、B+树等等

• Hash表：以键值对的形式存储数据，在等值查找得时候，时间复杂度是O(1)，效率很高；但是不支持范围查询，范围查的时候还是得全表扫描。所以不适合作为索引的数据结构。
• 二叉树：二叉树在一定程度上确实可以减少查询的效率，但是如果插入的数据是单调递增的话，二叉树就会变成一个单向链表，此时的查询时间复杂度是O(n)即全表扫描。也不适合作为索引的数据结构。
• 平衡二叉树：它解决了二叉树的不平衡问题，在插入或删除数据的时候会通过左旋/右旋保证二叉树的平衡，不会出现左子树高右子树低（或者左子树低右子树高）的情况；查询的性能接近于二分查找法，时间复杂度是O(log2n)。但是它的问题是，树有多高就需要检索多少次，如果表数据量大导致二叉树很高，此时的磁盘IO次数就会很多，查询效率就低。所以也不适合作为索引的数据结构。
• B树：B树可以看作是对平衡二叉树的改造，将二叉树改成多叉树，这样树的高度就降低了，磁盘IO次数就少了。它是将一页数据作为一个树的一个节点，而一页的大小是16K，这样的话一个节点上就能放多个元素，假如一个bigint为8字节，再加上元素有两个指针，每个指针占4字节，这样的话一个元素占16字节，一个节点上就可以放1000个元素，意味着向下分叉的时候下一层可以有1000个子节点，按照这样逻辑，两层的树结构可以放10001000个元素，三层高度的树可以放10001000*1000个元素，这样就大大减少了树的高度，也就减少了磁盘IO次数。它的特点是：
  1）每个节点可以有多个元素，节点有多个分叉
  2）节点包含键和数据，键是排好序的
  3）父节点中的元素不会在子节点中出现
  4）所有的叶子结点在同一层，叶节点之间没有指针链接
  缺点：
  1）无法范围查询
  2）节点存放数据，导致每个节点存放的元素个数变少，相同数据量下树的高度就会变高
• B+树：它对B树做了一些优化，首先是非叶子节点不存储数据，只在叶子结点存储数据；叶子节点之间使用双向指针链接，这样就使得底层的叶子节点形成了一个双向链表，这样就能支持范围查询。
  B+树相对于B树的优点：
  1）支持范围查询
  2）中间节点不存数据，每个数据页存储的元素个数多，在相同数据量的情况下树的高度就矮

所以InnoDB选择了B+树作为索引的数据结构！

索引基本知识

• 什么是索引
  索引是一种数据结构，可以提高查找效率。可以理解为：索引是一个排好序的快速查找数据结构。
  我们平时说的索引，没有特殊说明，都是B+Tree结构。
  索引实际上也是一张表，保存了主键和索引字段并指向实体表的记录。
• 创建索引

  create index [索引名] on [表名] ([列名])
  

• 删除索引

  drop index [索引名] on [表名]
  

• 查看索引

  show index from [表名]
  

• 索引的类型
  单值索引
  唯一索引
  复合索引
• 哪些情况需要建索引
  where条件后面的列
  order by 的列
  group by 的列
  …
• 哪些情况不需要建索引
  表数据少
  增删改多的表
  字段差异不大，重复值多的列（比如性别，国籍等）
  …

InnoDB中的两大索引

聚集索引

1. 聚集索引表记录的排列顺序和索引的排列顺序保持一致，所以查询效率相当快。
2. 缺点就是修改起来比较烦，因为要保持表记录和索引顺序一致，插入时会对数据所一次重新排序。
3. 在InnoDB中，表数据文件本身就是按照B+Tree组织的一个索引结构，叶节点data域保存了完整的数据记录。
4. 如果定义了主键，主键就是聚集索引；如果没有就找第一个非null的唯一列作为聚集索引；如果都没有就创建一个隐藏的rowid作为聚集索引。
5. 聚集索引中叶子节点存储的是行中的数据。
6. InnoDB中必须有且只能有一个聚集索引。

非聚集索引

1. InnoDB中，叶子节点中存储的是主键值。 注意：MyISAM的索引叶子节点存储的是行记录的指针！！！
2. 缺点就是索引层次较多，但是不会造成数据的重排。
3. 非聚集索引又分为唯一索引、普通索引、联合索引。

回表查询

1. 通过聚集索引查找数据只需要查一次索引树即可。
2. 通过非聚集索引查找数据需要先去非聚集索引树上获取主键ID，然后去聚集索引树上根据ID去查找到该行的数据。 需要经过两次索引树的查找，这个就叫回表查询。
3. B+Tree的每个叶子节点都有一个指针指向下一个叶子节点，把所有的叶子节点串在了一起，这就是范围查询使用索引的原理。

回表查询问题的解决

用索引覆盖！
何为索引覆盖，就是讲被查询的字段，建立到联合索引中去。
例子：

1. select id,name from user where name=‘shenjian’;
   name建立了索引，无需回表查询
2. select id,name,sex from user where name=‘shenjian’;
   要把name、sex建立联合索引，这样就无需回表查询。

哪些场景可以利用索引覆盖来优化SQL？
全表count查询优化
select count(name) from user;
其中name是普通索引。

索引条件下推（ICP）

正常情况下，不满足最左前缀的索引条件是在存储引擎层进行过滤的，非索引条件的比较是在mysql server 层进行的。但是如果开启了索引条件下推，则所有的索引条件的比较都是在储存引擎层进行，这样的话储存引擎层就能过滤掉更多的数据，从而能减少回表查询的次数。
举例：select * from table where a=13 and b>=15 and c=5 and d='java';
表有四个字段（a,b,c,d）其中（a,b,c）是一个联合索引。

• 在没有索引条件下推的情况下：
  首先在储存引擎层通过最左前缀原则筛选出来3条数据（分别是id=1,3,6），然后mysql server层分别去聚集索引中查询三次，最后筛选出来一条数据。

• 在开启索引条件下推的情况下：
  因为c也是在索引条件中，所以也会根据c再次在储存引擎层筛选数据，这样的话就筛选出来两条数据（分别是id=3,6）,然后mysql server曾分别去聚集索引中查询两次，最后筛选出来一条数据。

• 可以看出来，开启了索引条件下推，可以减少回表查询的次数，当我们用explain分析的时候，在Extra这一行上显示如下，有这个“Using index condition"表示使用了索引条件下推。
  

• 开启索引条件下推，这是mysql5.6引入的，mysql5.7默认开启，可以通过如下命令开启或关闭SET optimizer_switch = 'index_condition_pushdown=off';
SET optimizer_switch = 'index_condition_pushdown=on';

SQL性能分析之 explain

sql语句前面加explain可以查看sql语句的执行计划，能准确的定位出sql语句是如何执行的。
explain能查出来的列有 id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra。下面分别来分析这些字段代表什么

• id
  表示表执行的顺序：
  如果id都相同，则执行顺序从上到下；
  如果id都不相同，则id大的先执行，小的后执行；
  如果id有相同的，有不相同的，则不同的之间大的先执行，相同的从上到下。
• select_type
  表示查询的类型
  SIMPLE：简单的select查询，不包含子查询和union
  PRIMARY：有子查询，最外层的查询
  SUBQUERY：子查询，select或where中
  DERIVED：在from中包含的子查询，mysql会递归执行子查询放入临时表
  UNION：第二个select出现在union后，被标记为UNION
  UNION RESUL：union后的结果
• table
  表示这行数据是关于哪张表的
  如果table是 < derived3 >，derived表示衍生表，3表示id
• type
  表示访问的类型
  system：最优的，基本不会出现，可以忽略
  const：primary/uniqu index 中，只匹配了一行数据，mysql将查询转为了常量
  eq_ref：唯一性索引扫描后，每个键索引只有一条数据
  ref：索引扫描，匹配某个单独值的范围，索引=单独值
  range：索引是个范围
  index：全索引扫描
  ALL：全表扫描
  基本上百万级别的数据最好优化到range，如果是ALL，基本上SQL查的很慢，type是explain执行计划中重要的一个指标。
• possible_keys
  表示可能应用到这张表的索引，一个或多个
• key
  表示实际使用到的索引，为NULL则无索引
  注：如果用到了覆盖索引，则该索引只会出现在key中，不会在possible_keys中
  覆盖索引：比如建立了c1,c2,c3的复合索引，select c1,c2,c3。。。则用到了覆盖索引，表示直接从索引中取数据，不必读数据行，查询列要被索引覆盖。
• key_len
  表示索引使用到的字节数，可通过该列计算查询中使用到的索引长度；但是并非索引实际使用长度。
• ref
  表示索引哪一列被实际使用了，如果可能，是一个常数；哪些列或常量被用于查找索引上列的值。
• rows
  表示估算出找到记录索要读取的行数
• Extra
  表示一些其他重要信息
  using filsort：表示文件内排序，说明排序的列未用到索引，需要优化
  using temporary：表示mysql使用了临时文件保存中间结果，常见于group by、order by
  using index：表示相应的select操作中使用了覆盖索引；如果同时又using where 表示索引用来执行索引键值的查找；如果没有，表示索引只用来读数据而非查找数据。
  using where：表示用到了where条件
  还有其他一些不是太重要的，主要是前三个，如果有using filsort、using temporary，在数据量大的情况下，是一定要做索引优化的。

索引的优化

SQL JOIN 中的索引如何建

左连接：左表数据一定都有，left join 条件用于确定如何从右表搜索行，所以右表是关键点，一定要建立索引。
右表建立索引后，explain查看，type为ref，row数量变少；
左表建立索引后，explain查看，type为index，row数量无变化；
所以应该在右表建立索引。
同理：右连接应该在左表建立索引。

三表关联的话，同样的也都是在右表建立索引。
优化策略：

• 尽可能减少join语句中的NestedLoop的循环总次数：永远用小结果集驱动大的结果集；
• 优先优化NestedLoop的内层循环；
• 保证join语句中被驱动表上join条件字段已经被索引；☆☆☆☆☆
• 当无法保证被驱动表的join条件字段被索引且内存资源充足前提下，不要太吝啬JoinBuffer的设置；

索引失效情况分析

• 最佳左前缀法则，如果索引了多列，查询从索引的最左前列开始，并且不跳过索引中的列
• 不在索引列上做任何操作（计算、函数、(自动或手动)类型转换），会导致索引失效而全表扫描
• 存储引擎不能使用索引中范围条件右边的列（范围之后全失效）
• 尽量使用覆盖索引(只访问索引的查询)，减少select *
• mysql在使用不等于（!=或<>）的时候无法使用索引会导致全表扫描
• is null,is not null 也无法使用索引
• like以通配符开头(’%abc…’)mysql索引也会变成全表扫描
  这种情况下，使用覆盖索引可以解决问题。
• 字符串不加单引号索引失效，因为mysql在底层会自动做隐式转换，所以会失效，见第2条
• 少用or，用它来连接时会索引失效

其他补充

SQL优化说明：

• 开启慢日志查询并捕获
• explain+慢SQL分析
• show profile 查询SQL在Mysql服务器里面的执行谢姐和生命周期情况
• SQL服务器的参数调优

慢日志查询

指的是运行时间超过long_query_time值的SQL，默认情况下，Mysql是没有开启慢日志查询，需要手动来设置这个参数，如果不是调优需要的话，一般不建议启动该参数，开启慢日志查询会带来一定的性能影响，会将慢查询日志记录写入文件。

my.cnf配置文件：

[mysqld]
slow_quey_log=1
slow_quey_log_file=/home/mysql/data3086/mysql/slow_query.log
long_query_time=3;
log_output=FILE

查询是否开启慢日志

show variables like '%slow_query_log%';

开启慢日志查询，支持当前mysql库生效，重启失效，永久生效修改my.cnf配置文件（见上）

set global slow_query_log=1; 

查看慢日志查询的相关设置：

show variables like 'long_query_time%';
set global long_query_time=3;

查看慢日志条数：

show global status like '%low_queries%';

mysqldumpslow慢日志分析工具，mysqldumpslow --help 查如何筛选出所需要的慢日志，具体百度…

ORDER BY 和 GROUP BY 的优化

Mysql两种排序方式：using filesort / using index
Mysql能为排序和查询使用相同的索引

KEY a_b_c(a,b,c)

order by 能使用索引最左前缀：

• order by a
• order by a,b
• order by a,b,c
• order by a desc,b desc,c desc

如果WHERE使用索引的最左前缀定义为常量，则order by 能使用索引最左前缀

• where a=constant order by b,c
• where a=constant and b=constant order by c
• where a=constant order by b,c
• where a=constant and b>constant order by b,c

不能使用索引进行排序

• order by a asc,b desc,c desc /* 排序不一致 */
• where g=constant order by b,c /* 丢失a索引 */
• where a=constant order c /* 丢失b索引 */
• where a=constant order by a,d
• where a in(…)order by b,c /* 对于排序来说，多个相等条件也是范围查询 */

group by 实质是先排序后进行分组，遵循索引键的最佳左前缀；
当无法使用索引列，增大 max_length_for_sort_data参数的设置 + 增大sort_buffer_size参数的设置；
where高于having，能写在where限定的条件就不要去用having限定了；
其他的和order by一致。

索引分析三部

1. 首先分析语句,看看是否load了额外的数据,可能是查询了多余的行并且抛弃掉了,可能是加载了许多结果中并不需要的列,对语句进行分析以及重写.
2. 分析语句的执行计划,然后获得其使用索引的情况,之后修改语句或者修改索引,使得语句可以尽可能的命中索引.
3. 如果对语句的优化已经无法进行,可以考虑表中的数据量是否太大,如果是的话可以进行横向或者纵向的分表.