mysql索引原理及慢查询优化

mysql索引原理

索引目的

索引的目的在于提高查询效率，可以类比字典

索引原理

磁盘IO与预读

磁盘读取数据靠的是机械运动，每次读取数据花费时间可以分为寻道时间，旋转延迟，传输时间三个部分，寻道时间指的是磁臂移动到指定磁道所需要的时间，主流磁盘一般在5ms以下；旋转延迟就是我们经常听说的磁盘转速，比如一个磁盘7200转，表示每分钟能转7200次，也就是说1秒钟能转120次，旋转延迟就是1/120/2=4.17ms；传输时间指的是从磁盘读出或将数据写入磁盘的时间，一般在零点几毫秒

索引的数据结构

b+树：
要查找数据项29，首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短，可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发汗恶搞第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。
b+树性质：
1.IO次数取决于b+树到高度h，假设当前数据表的数据为N，每个磁盘块的数据项的数量是m，则有h=log(m+1)N，当数据量N一定的情况下，m越大，h越小；而m=磁盘块的大小/数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为什么每个数据项，即索引字段要尽量的小。这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。
2.当b+树的数据项是复合的数据结构，比如（name，age，sex）的时候，b+树是按照从左到右的顺序来建立搜索树的，优先比较name来确定下一步的搜索方向，相同时再比较age和sex，即索引的最左匹配特性。

慢查询优化

建索引的几大原则

1.最左前缀匹配原则。mysql会一直向右匹配直到遇到范围查询（>、<、between、like）就停止匹配
2.=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式。
3.尽量选择区分度高的列作为索引，区分度的公式是count（distinct 从来）/count（*），表示字段不重复的比例，比例越大，扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0.
4.索引列不能参与计算，如from_unixtime(create_time)='2014-05-29’就不能使用索引，因为b+树中存的是数据表中的字段值，但进行索引时，需要把所有元素都应用函数才能比较，成本太大。
5.尽量的扩展索引，不要新建索引。

查询优化神器- explain命令

慢查询优化基本步骤

0.先运行看是否真的很慢，注意设置SQL_NO_CACHE
1.where条件单表查，锁定最小返回记录表。把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高
2.explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询）
3.order by limit 形式的sql语句让排序的表优先查
4.了解业务放使用场景
5.加索引时参照建索引的几大原则
6.观察结果，不符合预期继续从0分析。

MYSQL查询过程

mysql客户端/服务端通信协议是半双工的：在任一时刻，要么是服务器向客户端发送数据，要么是客户端向服务器发送数据，这两个动作不能同时发生。一旦一端开始发送消息，另一段要接收完整个消息才能响应它，无法将一个消息切成小块独立发送，也没办法进行流量控制。
整个查询过程：
1.客户端向mysql服务器发送一条查询请求
2.服务器首先检查查询缓存，如果命中缓存，则立刻返回在缓存中的结果。否则进入下一阶段
3.服务器进行SQL解析，预处理，再由优化器生成对应的执行计划
4.MYSQL根据执行计划，调用存储引擎的API来执行查询
5.将结果返回给客户端，同时缓存查询结果。