mysql索引、慢查询和优化分析

说到数据库就需要谈到索引，一句话，索引是帮助mysql高效获取数据的排好序的数据结构。

计算机存取原理

为什么说索引能帮助我们更高效获取数据，先回顾一下计算机是怎么存取数据的，计算机把数据保存在磁盘上，而为了提高性能，每次又可以把部分数据读入内存来计算，因为我们知道访问磁盘的成本大概是访问内存的十万倍左右

考虑到磁盘IO是非常高昂的操作，计算机操作系统做了一些优化，当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。每一次IO读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关，一般为4k或8k，也就是我们读取一页内的数据时候，实际上才发生了一次IO，所以结合我们的例子以及计算机查询数据的原理，为了提高查询数据的查询速度，需要保证最小的IO次数，这样，只有叶子中才包涵真实数据信息的的B+树结构应该是我们最理想的索引数据结构。

其他索引结构

常见的索引除了innodb中的B+树还有B树和hashmap。

为什么innodb不选择hashmap？
• 使用hash表必须要保证具备好的hash算法,如果算法不合适的话会造成hash冲突,会导致数据散列不均匀,有可能会退化成一个链表
• 使用hasi表的时候不支持范围查询,当需要范围匹配的时候,必须要挨个对比,效率太低
• 需要大量内存

为什么innodb不选择B树？
• B树每个节点即保存数据又保存索引，所以访问磁盘IO的次数多，B+单一节点存储的元素更多，使得查询的IO次数更少，所以也就使得它更适合做为数据库MySQL的底层数据结构了。
• 所有的叶子节点形成了一个有序链表，更加便于查找。这样遍历叶子节点就能获得全部数据，这样就能进行区间访问啦。在数据库中基于范围的查询是非常频繁的，而B树不支持这样的遍历操作。

B+树索引结构

这里就不详细讨论B+树了，提两点在索引中比较关键的信息

1.IO次数取决于b+数的高度h，假设当前数据表的数据为N，每个磁盘块的数据项的数量是m，则有h=㏒(m+1)N，当数据量N一定的情况下，m越大，h越小；而m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为什么每个数据项，即索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。当数据项等于1时将会退化成线性表。

2.当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性。

索引优缺点

优点：
•加速查询，提高检索效率，降低io成本
•通过索引对数据进行排序，降低排序成本
缺点：
•在innodb中，要分别存储索引数据和表数据，索引也要占据空间
•虽然索引可以加快查询的效率，但是索引越多就会导致插入和更新数据的成本变高，因为索引是分开存储的，所有数据的插入和更新操作都要对相关的索引进行修改。所以设计索引时还需要控制索引的数量，不能盲目地增加索引。

索引创建方法和相关概念

索引设置：
1.ALTER TABLE tbl_name ADD PRIMARY KEY (column_list): 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
2.ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
3.ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出现多次。
4.ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list):该语句指定了索引为 FULLTEXT ，用于全文索引。
5.组合索引

聚簇索引和非聚簇索引
数据跟索引放在一起的叫做聚簇索引,数据跟索引没有放在一起的叫做非聚簇索引
因为mysql中可以设置n多个索引，也就是n多个b+树，但关联的数据文件只有一个，所以聚簇索引的树的叶子节点是具体的数据，而非聚簇索引叶子节点是聚簇索引，也就是通过非聚簇索引查询时，先获得聚簇索引，之后再进行第二次查询才能获得具体的数据。

索引下推（index condition pushdown ）简称ICP，在Mysql5.6的版本上推出，用于优化查询。
在不使用ICP的情况下，在使用非主键索引（又叫普通索引或者二级索引）进行查询时，存储引擎通过索引检索到数据，然后返回给MySQL服务器，服务器然后判断数据是否符合条件 。
在使用ICP的情况下，如果存在某些被索引的列的判断条件时，MySQL服务器将这一部分判断条件传递给存储引擎，然后由存储引擎通过判断索引是否符合MySQL服务器传递的条件，只有当索引符合条件时才会将数据检索出来返回给MySQL服务器 。
索引条件下推优化可以减少存储引擎查询基础表的次数，也可以减少MySQL服务器从存储引擎接收数据的次数。

适合创建索引和不适合创建索引的情况

哪些情况要建索引

1、主键自动建主键索引
2、频繁作为查询条件的字段应该创建索引
3、查询中与其他表关联的字段，外键关系建立索引
4、在高并发下倾向建立组合索引
5、查询中的排序字段，排序字段若通过索引去访问将大大提高排序速度
6、查询中统计或者分组的数据

哪些情况不适合建索引

1、频繁更新的字段
2、where条件用不到的字段不创建索引
3、表记录太少
4、经常增删改的表
5、数据重复太多的字段，为它建索引意义不大
（假如一个表有10万，有一个字段只有T和F两种值，每个值的分布概率大约只有50%，那么对这个字段的建索引一般不会提高查询效率，索引的选择性是指索引列的不同值数据与表中索引记录的比，，如果，一个表中有2000条记录，表中索引列的不同值记录有1980个，这个索引的选择性为1980/2000=0.99，如果索引项越接近1，这个索引效率越高）

索引失效

1、违反最左前缀法则
如果索引有多列，要遵守最左前缀法则
即查询从索引的最左前列开始并且不跳过索引中的列

2、在索引列上做任何操作
如计算、函数、（自动or手动）类型转换等操作，会导致索引失效从而全表扫描

3、索引范围条件右边的列
索引范围条件右边的索引列会失效
例如复合索引 index_abcd,
查询语句where a="" and b="" and c>"" and d="" 只会走abc，c之后的就不会走

4、使用不等于（!=、<>）
mysql在使用不等于（!=、<>）的时候无法使用索引会导致全表扫描（除覆盖索引外）

5、like以通配符开头（’%abc’）
以%开头的like查询语句不会使用索引，而以like结尾的查询语句是可以的

6、字符串不加单引号索引失效
explain select * from user where name = 2000;

7、or连接
Or连接的两个语句无法使用联合索引，但是如果or两边都有索引，还是可以走的。
MySQL5.0后的新技术，索引合并。index merge 技术简单说就是在用OR，AND连接的多个查询条件时，可以分别使用前后查询中的索引，然后将它们各自的结果合并交集或并集。
当然具体是否使用index merge，优化器会自己选择，比如and连接时有联合索引，或干脆全表查询就很快，就没必要使用它了

8、orderby
orderby查询的WHERE子句和ORDER BY子句中查询的字段在同一颗索引树上时会走索引，可以参考下面的描述

9、groupby
group by 使用索引的最重要的前提条件是所有group by列引用同一索引，并且索引按顺序保存其关键字。
group by 是否使用索引访问来代替临时表的使用还取决于在查询中使用了哪部分索引、为该部分指定的条件，以及选择的聚合函数。具体参照下方

orderby 优化

FileSort双路排序和单路排序

这里的文件排序并不是字面那表示的意思，利用了磁盘IO来进行排序，不过是优化器告诉你，进行了一个排序操作，具体排序的地方还是内存，相对应的参数是sort_buffer_size 设定的大小
1)filesort不一定会产生临时表
2)filesort 与临时表数据写入磁盘是没有任何直接联系

文件排序（FileSort）分为两种：
双路排序（又叫回表排序模式）：先根据相应的条件取出相应的排序字段和可以直接定位行 数据的行 ID，然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段；
单路排序：是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序；
举个例子，下面有一段sql：

select * from user where name = "kk" order by age

双路排序过程:
MySQL 4.1 之前使用的双路排序，通过两次扫描磁盘得到数据。读取主键id 和 order by 列并对其进行排序，扫描排序好的列表，按照列表中的值重新从列表中读取对应的数据输出。

从索引 name 找到第一个满足 name = ‘kk’ 的主键id
根据主键 id 取出整行，把排序字段 age 和主键 id 这两个字段放到 sort buffer(排序缓存) 中
从索引 name 取下一个满足 name = ‘kk‘ 记录的主键 id
重复 3、4 直到不满足 name = ‘kk’
对 sort_buffer 中的字段 age 和主键 id 按照字段 age进行排序
遍历排序好的 id 和字段 age ，按照 id 的值回到原表中取出 所有字段的值返回给客户端
单路排序过程：

从索引name找到第一个满足 name = ‘kk’ 条件的主键 id
根据主键 id 取出整行，取出所有字段的值，存入 sort_buffer(排序缓存)中
从索引name找到下一个满足 name = ‘kk’ 条件的主键 id
重复步骤 2、3 直到不满足 name = ‘kk’
对 sort_buffer 中的数据按照字段 age 进行排序
返回结果给客户端
从磁盘中读取查询需要的所有列，按照 order by 列在 sort_buffer(排序缓存) 缓冲区对他们进行排序，然后扫描排序后的列表输出。因为单路排序效率更快，避免了二次读取数据，把随机IO变成了顺序IO，但是会使用更多的空间。

对比：
其实对比两个排序模式，单路排序会把所有需要查询的字段都放到 sort buffer 中，而双路排序只会把主键 和需要排序的字段放到 sort buffer 中进行排序，然后再通过主键回到原表查询需要的字段。

选型：
至于mysql优化器使用双路排序还是单路排序是有自己的算法判断的，如果查询的列字段大于max_length_for_sort_data变量，则会使用双路排序，反之则会使用单路排序，单路排序速度是更快的，不过比较占据内存，如果在内存空间允许的情况下想要使用单路排序的话，可以增加max_length_for_sort_data变量的大小，max_length_for_sort_data变量默认为1024字节。

indexsort 利用有序索引获取有序数据

原理：
我们知道，mysql的基础数据结构是B+树，任何的一个表都是一颗B+树，你在表上建的索引也是一颗B+树，B+树的特别是在叶子节点上是有序，且前一个节点存在指向相邻节点的指针。
那么我在写SQL中的ORDER BY语句时候，若是ORDER BY的条件和返回的数据都在一颗树上，那么就可以利用B+树自身的特点来天然排序了，自然效率会比较高。

使用条件：
1)查询的WHERE子句和ORDER BY子句中查询的字段在同一颗索引树上，
2)ORDER BY 字段的顺序是跟建立索引的顺序是一致的。
3)查询的字段也在同一颗索引树

ORDER BY子句，尽量使用Index方式排序，避免使用FileSort方式排序。
MySQL支持二种方式的排序，FileSort和Index，Index效率较高，FileSort方式效率较低。
尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀
增大sort_buffer_size参数，增大max_length_for_sort_data参数的设置可以在使用filesort的情况下尽量使用单路排序