每次读取数据，磁盘和磁头都得跑好远一段路。
咋办呢，没办法，
只能让磁盘和磁头一起做机械运动，去给你疯狂跑腿，来回跑腿，去读取下一条数据。
磁盘的简化结构可以看成这样：
可以想象一下，为了执行你这条 sql 语句，磁盘要不停的旋转，磁头要不停的移动，
这些机械运动，都是很费时的。
10,000 RPM（ Per ，即转每分） 的机械硬盘，每秒大概可以执行 167 次磁盘读取，
所以在极端情况下，MySQL 每秒只能给你返回 167 条数据，这还不算上 CPU 排队时间。
对于 ，也是一样的。 是聚簇索引（ index）：
磁盘IOPS的计算规则
主要影响的三个参数，分别是平均寻址时间、盘片旋转速度以及最大传送速度：
第一个寻址时间，
考虑到被读写的数据可能在磁盘的任意一个磁道，既有可能在磁盘的最内圈(寻址时间最短)，也可能在磁盘的最外圈(寻址时间最长)，
所以在计算中我们只考虑平均寻址时间，也就是磁盘参数中标明的那个平均寻址时间，这里就采用当前最多的硬盘的5ms。
寻道时间Tseek是指将读写磁头移动至正确的磁道上所需要的时间。
寻道时间越短，I/O操作越快，目前磁盘的平均寻道时间一般在3-15ms。
第二个旋转延时，
和寻址一样，当磁头定位到磁道之后有可能正好在要读写扇区之上，这时候是不需要额外额延时就可以立刻读写到数据，但是最坏的情况确实要磁盘旋转整整一圈之后磁头才能读取到数据，
所以这里我们也考虑的是平均旋转延时，对于的磁盘就是(60s/10k)*(1/2) = 2ms。
第三个传送时间，
磁盘参数提供我们的最大的传输速度，当然要达到这种速度是很有难度的，
但是这个速度却是磁盘纯读写磁盘的速度，因此只要给定了单次 IO的大小，我们就知道磁盘需要花费多少时间在数据传送上，这个时间就是IO Chunk Size / Max Rate。（数据传输率，单位是Mb/s，兆每秒）。
数据传输时间是指完成传输所请求的数据所需要的时间，它取决于数据传输率，其值等于数据大小除以数据传输率。
目前IDE/ATA能达到133MB/s，SATA II可达到300MB/s的接口数据传输率，数据传输时间通常远小于前两部分时间。
因此，理论上可以计算出磁盘的最大IOPS，即IOPS = 1000 ms/ (Tseek + )，忽略数据传输时间。
假设磁盘平均物理寻道时间为3ms, 磁盘转速为7200,10K,15K rpm，
则磁盘IOPS理论最大值分别为，
IOPS = 1000 / (3 + 60000/7200/2) = 140
IOPS = 1000 / (3 + 60000/10000/2) = 167
IOPS = 1000 / (3 + 60000/15000/2) = 200
到这里你知道了磁盘随机访问是多么奢侈的事了，所以，很明显，要把随机访问转化成顺序访问：
顺序读：一场狂风暴雨般的革命
开启了 MRR很明显，要把随机访问转化成顺序访问。
设置开启MRR, 重新执行 sql 语句，发现 Extra 里多了一个「Using MRR」。
mysql > set optimizer_switch='mrr=on';
Query OK, 0 rows affected (0.06 sec)
mysql > explain select * from stu where age between 10 and 20;
+----+-------------+-------+-------+------+---------+------+------+----------------+
| id | select_type | table | type | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+------+---------+------+------+----------------+
| 1 | SIMPLE | tbl | range | age | 5 | NULL | 960 | ...; Using MRR |
+----+-------------+-------+-------+------+---------+------+------+----------------+
这下 MySQL 的查询过程会变成这样：
对于 ，在去磁盘获取完整数据之前，会先按照 rowid 排好序，再去顺序的读取磁盘。
对于 ，则会按照聚簇索引键值排好序，再顺序的读取聚簇索引。
顺序读带来了几个好处：
1、磁盘和磁头不再需要来回做机械运动；
2、可以充分利用磁盘预读
比如在客户端请求一页的数据时，可以把后面几页的数据也一起返回，放到数据缓冲池中，
这样如果下次刚好需要下一页的数据，就不再需要到磁盘读取。
这样做的理论依据是计算机科学中著名的局部性原理：当一个数据被用到时，其附近的数据也通常会马上被使用。
3、在一次查询中，每一页的数据只会从磁盘读取一次
MySQL 从磁盘读取页的数据后，会把数据放到数据缓冲池，下次如果还用到这个页，就不需要去磁盘读取，直接从内存读。
但是如果不排序，可能你在读取了第 1 页的数据后，会去读取第2、3、4页数据，
接着你又要去读取第 1 页的数据，这时你发现第 1 页的数据，已经从缓存中被剔除了，于是又得再去磁盘读取第 1 页的数据。
而转化为顺序读后，你会连续的使用第 1 页的数据，这时候按照 MySQL 的缓存剔除机制，
这一页的缓存是不会失效的，直到你利用完这一页的数据，由于是顺序读，
在这次查询的余下过程中，你确信不会再用到这一页的数据，可以和这一页数据说告辞了。
顺序读就是通过这三个方面，最大的优化了索引的读取。
别忘了，索引本身就是为了减少磁盘 IO，加快查询，而 MRR，则是把索引减少磁盘 IO 的作用，进一步放大。
拆分查询条件，进行批量查询
此外，MRR还可以将某些范围查询，拆分为键值对，以此来进行批量的数据查询。
这样做的好处是可以在拆分过程中，直接过滤一些不符合查询条件的数据。
SELECT * FROM t WHERE key_part1 >=1000 AND key_part1 < 2000 AND key_part2 = 1000;
表t有(,)的联合索引，因此索引根据,的位置关系进行排序。
若没有MRR，此时查询类型为Range，SQL优化器会先将大于1000且小于2000的数据都取出来，即便不等于1000。
取出后再根据的条件进行过滤。这会导致无用的数据被取出。
如果启用MRR优化器会使性能有巨大的提升，优化器会先将查询条件拆分为(1000,1000),(1001,1000),(1002,1000)....(1999,1000) 最后再根据这些拆分出的条件进行数据的查询。
一些关于这场革命的配置
是否启用MRR优化，可以通过参数中的flag来控制。
1、MRR 的开关：mrr =（on | off）
例如，打开MRR的开关:
mysql > set optimizer_switch='mrr=on';
2、用来告诉优化器，要不要基于使用 MRR 的成本：
= （on | off)
例如，通通使用MRR:
SET GLOBAL optimizer_switch='mrr=on,mrr_cost_based=off';
考虑使用 MRR 是否值得(cost-based )，来决定具体的 sql 语句里要不要使用 MRR。
很明显，对于只返回一行数据的查询，是没有必要 MRR 的，而如果你把 设为 off，那优化器就会通通使用 MRR，
这在有些情况下是很 的，所以建议这个配置还是设为 on，毕竟优化器在绝大多数情况下都是正确的。
3、设置用于给 rowid 排序的内存的大小：，该值默认是256KB
查看配置
show VARIABLES like 'read_rnd_buffer_size';
显然，MRR 在本质上是一种用空间换时间的算法。
MySQL 不可能给你无限的内存来进行排序，如果 满了，就会先把满了的 rowid 排好序去磁盘读取，接着清空，然后再往里面继续放 rowid，直到 又达到 配置的上限，如此循环。
没有MRR的情况下，二级索引里面得到多少行，那么就要去访问多少次主键索引(也不能完全这样说，因为MySQL实现了BNL，是把被驱动表的记录加载到内存的时候，一次性和多条驱动表中的记录做匹配，这样就可以大大减少重复从磁盘上加载被驱动表的代价)，而有了MRR的时候，次数就大约减少为之前次数 t / 。
可以简单理解为：
MRR 把分散的 回表操作， 聚合成了 批量的回表操作， 当然，是借助 空间的局部性原理和磁盘预读取等底层机制完成的。
MRR 使用限制
MRR 适用于range、ref、的查询
聊聊：如何使用关键字？
在日常工作中, 我们会记录一些执行时间比较久的SQL语句, 找出这些SQL语句并不意味着完事了,
我们常常用到这个命令来查看一个这些SQL语句的执行计划, 查看该SQL语句有没有使用上了索引, 有没有做全表扫描, 所以我们需要深入了解MySQL基于开销的优化器.
什么是 关键字
使用关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。
分析你的查询语句或是表结构的性能瓶颈。
通过，我们可以分析出以下结果：
关键字使用方式如下：
+ SQL语句
explain select * from t_member where member_id = 1;
在执行命令之后, 显示的信息一共有12列,
执行计划包含的信息
分别是:
执行计划各字段含义1. id: 查询中执行子句或操作表的顺序
查询的序列号，包含一组数字，表示查询中执行子句或操作表的顺序
id相同时执行顺序从上到下, 在所有组中, id值越大, 优先级越高, 越先执行
id的结果共有3中情况
[总结] 加载表的顺序如上图table列所示：t1 t3 t2
如上图所示，在id为1时，table显示的是 ,这里指的是指向id为2的表，即t3表的衍生表。
2. ：每个子句的类型.
常见和常用的值有如下几种：
分别用来表示查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询。
3. table 表
指的就是当前执行的表
4. type 查询类型
type所显示的是查询使用了哪种类型，type包含的类型包括如下图所示的几种：
从最好到最差依次是：
system > const > eq_ref > ref > range > index > all
一般来说，得保证查询至少达到range级别，最好能达到ref。
5. 和 key
显示可能应用在这张表中的索引，一个或多个。
查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。
key
6. 索引中使用的字节数
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，在不损失精确性的情况下，长度越短越好。显示的值为索引字段的最大可能长度，并非实际使用长度，
即是根据表定义计算而得，不是通过表内检索出的。
7 ref 那一列被使用
显示索引的那一列被使用了，如果可能的话，最好是一个常数。
哪些列或常量被用于查找索引列上的值。
8. rows 所需要读取的行数
根据表统计信息及索引选用情况索引是什么，大致估算出找到所需的记录所需要读取的行数，也就是说，用的越少越好
9. Extra
包含不适合在其他列中显式但十分重要的额外信息
9.1 Using
说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。
MySQL中无法利用索引完成的排序操作, 称为“文件排序”。
9.2 Using
使用了用临时表保存中间结果，MySQL在对查询结果排序时使用临时表。
常见于排序order by和分组查询group by。
9.3 Using index
表示相应的操作中使用了覆盖索引（ Index），避免访问了表的数据行，效率不错。
如果同时出现using where，表明索引被用来执行索引键值的查找；
如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。
如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。
理解方式一:
就是的数据列只用从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回列表中的字段，而不必根据索引再次读取数据文件,换句话说查询列要被所建的索引覆盖。
理解方式二:
索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。
毕竞竟索引叶子节点存储了它们索引的数据:当能通过速取索引就可以得到想要的数据，那就不需要速取行了。
一个索引包含了(或覆盖了)满足查询结果的数据就叫做覆盖索引。
注意:如果要使用覆盖索引，一定要注意列表中只取出需要的列，不可 *,
因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降。
9.4 Using where
表明使用了where过滤
9.5 Using join
表明使用了连接缓存,比如说在查询的时候，多表join的次数非常多，那么将配置文件中的缓冲区的join 调大一些。
9.6 where
where子句的值总是false，不能用来获取任何元组
SELECT * FROM t_user WHERE id = '1' and id = '2'
9.7 away
在没有子句的情况下，基于索引优化MIN/MAX操作或者对于存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
9.8
优化操作，在找到第一匹配的元组后即停止找同样值的动作
实例分析
执行顺序1：
id为4，为UNION，
说明第四个是UNION里的第二个，最先执行【 name,id from t2】
执行顺序2：
id为3，是整个查询中第三个的一部分。
因查询包含在from中，所以为【 id,name from t1 where =’’】
执行顺序3：
id为2，列表中的子查询为,
为整个查询中的第二个【 id from t3】
执行顺序4：
id为1，表示是UNION里的第一个，列的表示该查询为外层查询，table列被标记为,表示查询结果来自一个衍生表，其中中的3代表该查询衍生自第三个查询，即id为3的。【 d1.name …】
执行顺序5：
id为null，代表从UNION的临时表中读取行的阶段索引是什么，table列的表示用第一个和第四个的结果进行UNION操作。【两个结果union操作】
实战：MySQL索引优化深入实战
前言：该篇随笔通过一些案例，对索引相关的面试题进行分析。
0.准备1.创建test表(测试表)。
drop table if exists test;
create table test(
id int primary key auto_increment,
c1 varchar(10),
c2 varchar(10),
c3 varchar(10),
c4 varchar(10),
c5 varchar(10)
) ENGINE=INNODB default CHARSET=utf8;
insert into test(c1,c2,c3,c4,c5) values('a1','a2','a3','a4','a5');
insert into test(c1,c2,c3,c4,c5) values('b1','b2','b3','b4','b5');
insert into test(c1,c2,c3,c4,c5) values('c1','c2','c3','c4','c5');
insert into test(c1,c2,c3,c4,c5) values('d1','d2','d3','d4','d5');
insert into test(c1,c2,c3,c4,c5) values('e1','e2','e3','e4','e5');
2.创建索引。
3.普通查询情况
1.根据以下Case分析索引的使用情况
Case 1：
分析：
①创建复合索引的顺序为c1,c2,c3,c4。
②上述四组执行的结果都一样：type=ref，=132，ref=const,const,const,const。
结论：
在执行常量等值查询时，改变索引列的顺序并不会更改的执行结果，
因为mysql底层优化器会进行优化，但是推荐按照索引顺序列编写sql语句。
Case 2：
分析：
当出现范围的时候，type=range，=99，比不用范围=66增加了，说明使用上了索引，
但对比Case1中执行结果，说明c4上索引失效。
结论：范围右边索引列失效，但是范围当前位置(c3)的索引是有效的，从=99可证明。
Case 2.1：
分析：
与上面执行结果对比，=132说明索引用到了4个，
因为对此sql语句mysql底层优化器会进行优化：
范围右边索引列失效(c4右边已经没有索引列了)，注意索引的顺序(c1,c2,c3,c4)，所以c4右边不会出现失效的索引列，因此4个索引全部用上。
结论：
范围右边索引列失效，是有顺序的：c1,c2,c3,c4，如果c3有范围，则c4失效；如果c4有范围，则没有失效的索引列，从而会使用全部索引。
Case 2.2：
分析：
如果在c1处使用范围，则type=ALL，key=Null，索引失效，全表扫描，
这里违背了最佳左前缀法则，带头大哥已死，因为c1主要用于范围，而不是查询。
解决方式使用覆盖索引。
结论：在最佳左前缀法则中，如果最左前列(带头大哥)的索引失效，则后面的索引都失效。
Case 3：
分析：
利用最佳左前缀法则：
中间兄弟不能断，因此用到了c1和c2索引(查找)，从=66，ref=const,const，c3索引列用在排序过程中。
Case 3.1：
分析：
从的执行结果来看：=66，ref=const,const，从而查找只用到c1和c2索引，c3索引用于排序。
Case 3.2：
分析：
从的执行结果来看：=66，ref=const,const，查询使用了c1和c2索引，由于用了c4进行排序，跳过了c3，出现了Using 。
Case 4：
分析：
查找只用到索引c1，c2和c3用于排序，无Using 。
Case 4.1：
分析：
和Case 4中的执行结果一样，但是出现了Using ，因为索引的创建顺序为c1,c2,c3,c4，但是排序的时候c2和c3颠倒位置了。
Case 4.2：
分析：
在查询时增加了c5，但是的执行结果一样，因为c5并未创建索引。
Case 4.3：
分析：
与Case 4.1对比，在Extra中并未出现Using ，因为c2为常量，在排序中被优化，所以索引未颠倒，不会出现Using 。
Case 5：
分析：
只用到c1上的索引，因为c4中间间断了，根据最佳左前缀法则，所以=33，ref=const，表示只用到一个索引。
Case 5.1：
分析：
对比Case 5，在group by时交换了c2和c3的位置，结果出现Using 和Using ，极度恶劣。原因：c3和c2与索引创建顺序相反。
Case 6
分析：
①在c1,c2,c3,c4上创建了索引，直接在c1上使用范围，导致了索引失效（其实这里MySQL底层也是有优化的，如果where后的字段是索引的第一个字段使用了范围查询，如果这个范围很大，几乎已经是要扫描所有数据了，
MySQL就会用全表扫描，如果这个范围不是很大，那么MySQL底层依旧还会使用索引来进行查询），
全表扫描：type=ALL，ref=Null。因为此时c1主要用于排序，并不是查询。
②使用c1进行排序，但是索引失效，出现了Using 。
③解决方法：使用覆盖索引。
就是将索引字段覆盖掉查询字段，实现索引覆盖，MySQL就不会扫描全表而去使用索引了。
Case 7：
分析：
虽然排序的字段列与索引顺序一样，且order by默认升序，这里c2 desc变成了降序，导致与索引的排序方式不同，
因为索引的所有字段都是按照同一个方向的顺序进行排序的，如果出现了排序方向不同，那么已经排列好的索引自然也就失效了，从而产生Using ，而且type还是index（index是扫描全表索引，所以这一个的是132，说明4个索引字段全部都扫描了，ALL是扫描全表，index比ALL稍微快一点）。
Case 8：
EXPLAIN extended select c1 from test where c1 in ('a1','b1') ORDER BY c2,c3;
分析：
对于排序来说，多个相等条件也是范围查询，所以索引失效，c2,c3都无法使用索引，出现Using 。
并且这里type是index，扫描全表索引。
总结
尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则。
如果order by的条件不在索引列上，就会产生Using 。
group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最佳左前缀法则。注意where高于，能写在where中的限定条件就不要去限定了。
通过以上Case的分析，进行如下总结：
①最佳左前缀法则。
1.在等值查询时，更改索引列顺序，并不会影响的执行结果，因为mysql底层会进行优化。
2.在使用order by时，注意索引顺序、常量，以及可能会导致Using 的情况。
②group by容易产生Using 。
③通俗理解口诀：
全值匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
LIKE百分写最右，覆盖索引不写星；
不等空值还有or，索引失效要少用。
参考文献：
硬核面试题推荐
限时特惠:本站持续每日更新海量各大内部创业课程，一年会员仅需要98元，全站资源免费下载
点击查看详情
站长微信:Jiucxh
