Mysql优化分析-索引如何建立与sql优化

随着我们的时间的推移，相关的数据表会变得越来越大；与此同时的数据库查询也会性能下降；执行时间变长…

可能出现的问题：

• 数据过多
  – 这个我们得将数据进行分库分表
• 关联了太多的表，太多join查询
  – 需要进行SQL优化
• 没有充分利用到索引
  – 索引建立==（1:mysql会自动创建主键索引；2:需要根据实际情况，创建索引）==
• 服务器调优及各个参数设置
  – 调整my.cnf配置文件

身为程序员，我们工作上能够接触的可能也就是我们写的sql语句，对sql语句的优化可以说是相当必要的，优化得当效果是非常客观的，尤其是现在的大数据时代；

利用索引来优化我们的sql语句

索引是什么

• 1：MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。
  可以得到索引的本质：索引是数据结构。
• 1.1: 数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。
• 1.2: 索引的目的在于提高查询效率，可以类比字典， 我们只要在字典的相关拼音就能查询到某个字的具体页数；我们可以简单理解为“排好序的快速查找数据结构”。
• 2:一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上

索引的优势

• 类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本
• 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗

索引的劣势

• 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE；因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息
• 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的

mysql索引结构

• 平衡树B+tree和bBree mysql使用的是B+tree：
  
  可以看到B+tree只有数据和向下的指针
• 而Btree结构
  
  Btree 的结构有数据和向下的指针和指向数据的指针；
• 在同等内存的情况下比较，加载B+tree比加载Btree能多三分之一；多三分之一的话缺页率也会降低；因为内存有限，这些索引的磁盘块不是全部都加载进来的；多三分之一可以减少缺页率也就是减少I/O的次数；
• 简单来说B+tree的优势是：
• 1. B+树的磁盘读写代价更低 ：B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。
• 2. B+树的查询效率更加稳定：
     由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

索引的分类和基本语法：

基本语法

创建

• == CREATE [UNIQUE ] INDEX [indexName] ON table_name(column)) ==

删除

• DROP INDEX [indexName] ON mytable;

查看

• SHOW INDEX FROM table_name\G

使用ALTER命令

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

索引的分类

• 单值索引：即一个索引只包含单个列，一个表可以有多个单列索引
  – CREATE INDEX idx_customer_name ON customer(customer_name);
• 唯一索引：索引列的值必须唯一，但允许有空值
  – CREATE UNIQUEINDEX idx_customer_no ON customer(customer_no);
• 主键索引：设定为主键后数据库会自动建立索引，innodb为聚簇索引
  – ALTER TABLE customer
  add PRIMARY KEY customer(customer_no);
• 复合索引：即一个索引包含多个列
  – CREATE INDEX idx_no_name ON customer(customer_no,customer_name);

哪些情况需要创建索引呢？

• 主键自动建立唯一索引
• 频繁作为查询条件的字段应该创建索引
• 查询中与其它表关联的字段，外键关系建立索引
• 单键/组合索引的选择问题， 组合索引性价比更高
• 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
• 查询中统计或者分组字段（order和groud by ：groud by 比较慢 groud by 包含order by ，先order by）

哪些情况需要创建索引呢？

表记录太少

经常增删改的表或者字段

Where条件里用不到的字段不创建索引

过滤性不好的不适合建索引（比如说性别，建了效果也不好）

Mysql对sql的性能分析工具Explain

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈

能干嘛

• 表的读取顺序（id的顺序）
• 哪些索引可以使用
• 数据读取操作的操作类型
• 哪些索引被实际使用
• 表之间的引用
• 每张表有多少行被物理查询

使用方法：

Explain + SQL语句；

执行后出来的信息比如查看个查询语句sql：

• 可以看到查询出来的有相关字段，首先我们得明白相关的字段：
• id：id是select查询的序列号,包含一组数字，表示查询中执行select子句或操作表的顺序
  – 三种情况：id相同，执行顺序由上至下；id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行；id相同不同，同时存在
  – 注意：id号每个号码，表示一趟独立的查询。一个sql 的查询趟数越少越好。
• select_type：查询的类型，主要是用于区别
  普通查询、联合查询、子查询等的复杂查询
• table:显示这一行的数据是关于哪张表的
• partitions:代表分区表中的命中情况，非分区表，该项为null
• type:显示查询使用了何种类型，
  从最好到最差依次是：
  system>const>eq_ref>ref>range>index>ALL

• possible_keys:显示可能应用在这张表中的索引，一个或多个。
  查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用
• key:实际使用的索引。如果为NULL，则没有使用索引,查询中若使用了覆盖索引，则该索引和查询的select字段重叠
• key_len: 表示索引中使用的字节数，可通过该列计算查询中使用(命中)的索引的长度。 key_len字段能够帮你检查是否充分的利用上了索引，越大代表命中越多，查询效率就越高
• ref:显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值
• ==rows:rows列显示MySQL认为它执行查询时必须检查的行数。==越少越好，因为是实际对物理表的查询
• filtered：这个字段表示存储引擎返回的数据在server层过滤后，剩下多少满足查询的记录数量的比例，注意是百分比，不是具体记录数
• Extra（主要是看order by或者groud by，和关联查询有没有用上查询）：包含不适合在其他列中显示但十分重要的额外信息
  
• 红色代表是效率底下，需要被优化；

这些字段是对我们分析性能是十分的重要

接下来的话就是真正的sql优化（使用索引的方式），为了能开到效果，使用mysql的explain工具还是十分必要的；

一：首先要想看到明显的效果肯定是要先建立几张大数据的表出来；我们通过建立存储过程，创建随机函数，然后调用存储过程来模拟大量的数据；

• 当然这里也有一个问题，往一张表里插入100w条数据如何插入快：
• 在索引方面：
• 1:索引的优势是查询快，但是在数据的写操作都会变慢；因为也得对索引进行更新；
• 所以我们本次创建大数据表针对索引把表先把除了除了主键索引外其它的索引都删除
• 事务方面：
• 1:创建一个表有事务，发一个语句就自动commit提交，如果发一百万条就会进行一百万次commit次；所以可以先把自动commit关闭先，然后在手动commit一次；
• 所以本次在建立的存储过程中关闭了自动commit
• 如果是在比如java程序中执行插入：使用多线程进行
• 使用持久层框架上进行优化（通过更改数据库的参数配置文件）

这是我们准备的大数据表：这两张表都是我们比较常见的表

查询一般分两种情况，一是单表查询，而是关联擦讯

现在先将单表查询==（SQL_NO_CACHE 就算sql执行多次也不会缓存，避免缓存所造成的干扰）==：列出来的都是比较常用的sql语句，并对其进行所以优化

一：EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30；

• 首先，通过EXPLAIN工具模拟我们能看到很清楚的查询性能参数；第一次模拟是无索引的情况，可以看到：type：ALL，rows: 498863,filtered: 10.00；一个没有结果的查询，但是查询效率还很低下，type：all 代表全表扫描
• 所以下面面的查询是针对age筛选条件，建立了age索引==（create INDEX idx_age ON emp(age)）==，age索引过滤性还算不错：现在查询后：rows: 46208，filtered: 100.00，type：ref；
• 两个对比：可以发现rows之间的差距直接减少了一位的数量级别，过滤器过滤了对比；查询效率是提升的是算挺不错了，因为age其实过滤性来说不怎么好；但是相比于没有来看，有胜于无；

二： EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=4

• 没建索引时（默认是含有主键索引）：
  

• 可以看到没建索引后，的实际查询时间为0.12

• rows: 498863；filtered: 1.00；type=all；全表查询，效率低下；

• 根据where条件创建复合索引create index idx_age_deptid on emp(age,deptid)：

• 在继续查看查询效：
  

• 可以看到：实际查询好时间为0.00几乎不计，通过模拟EXPLAIN可以看到已经使用上索引，type=ref查询；实际查询表rows为1，过滤了100.00，可以说只真正扫描物理表0次；因为没有扫描其实也是算1次

• 对比之前：rows: 498863；filtered: 1.00；type：all

• 提升巨大

三：EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 and deptid=4 AND emp.name = ‘abcd’

• 没有索引的时候：查询和模拟图：
  

• 可以看到：查询可以看到，但是耗时0.12s；通过模拟可以看到扫描了表 rows: 498863；filtered: 0.10；type=all；全表查询

• 创建这三个复合索引==（create index idx_age_deptid_name on emp(age,deptid,name)）==后：
  

• 分析：Empty set (0.00 sec);rows: 1，其实没查数据也是1行

• type: ref，possible_keys: idx_age_deptid_name，key: idx_age_deptid_name，key_len: 73：可以看到len=73；复合索引中的三个都用上了； 效率提升巨大；

所以单表索引应该如何建立 ？：

• 最好的情况就是 经常写的sql，筛选字段有多少就建多少

在对索引进行一些测试；比如改sql中查询的顺序看是否用的上索引…

1:测试改变顺序—-看是改变顺序否能够继续用上索引

• 已经在表emp建立了idx_age_deptid_name的索引
• EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE deptid=4 and emp.age=30 AND emp.name = ‘abcd’
• 
• 可以看到key_len=73，我们可以知道使用上索引了，
• 主要功劳即使在架构中的sql优化器，在不改变查询结果的情况下，改变你的sql顺序；比如where条件的顺序

2:测试删除某一查询字段，—-看是否能够继续用上索引

• 已经在表emp建立了idx_age_deptid_name的索引
• EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 AND emp.name = ‘abcd’
  
• 可以发现，可以命中索引：不过通过key_len：5可以看到，只命中了一个索引，name索引并没有命中
• 在修改sql语句：留deptid，删除age
• EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE deptid=4 AND emp.name = ‘abcd’ ;
  
• 可以看到，现在是用不上索引；
• 这三种情况对比分析：
• 前提，索引的结构，也是一个数据结构，是一颗颗的平衡树；复合索引的话，在你查找第一课平衡树后复合的节点下都对应一棵包含这个条件的平衡树，以此类推
• 比如分析这三个字段的复合索引：
• 结构图：每个索引字段根据索引值建立平衡树：
  之前分析复合索引是有id的，id不一样，代表层级不一样；简单来所：假如age查到50，然后就得查询deptid这颗树，且带age=50这个条件，索引在age=50这个节点下还有一个age=50的deptid的树，如果查询的条件不只50这个条件，那每个节点下都对应其条件所对应的树；deptid和name的层级也是相同，所以当查询到name是已经自带两个条件，索引最后一层树是最小；
  
• 所以总结：复合索引的命中是按照顺序命中；索引一开始删除的是deptid，所以就只能在age这个索引树上进行查询，deptid的条件没有，索引age和name也就断了，所以这就是为什么复合索引只能查age索引

所以在使用索引的时候：

1:所以命中索引的方法：最佳左前缀法则（如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。），如果中间断开了的话，索引的效率也是不佳的

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

• 比如比较这两种写法：
• 1:EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.name LIKE ‘abc%’ 无name索引时实际查询：0.2s
• 2:EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE LEFT(emp.name,3) = ‘abc’ 无name索引时实际查询：0.2s
• 建立索引name之后：
• sql10.001s==
• Sql2=0.2s
• ==所以：不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描；能不用就尽量不用；

3:存储引擎不能使用索引中范围条件右边的列

• ==如果系统经常出现的sql如下：
  EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.age=30 AND emp.deptId>20 AND emp.name = ‘abc’ ; ==
• 上面的sql依然是全表扫描；0.2s
• 之前建立的索引顺序 是 create index idx_age_deptid_name ON emp(age,deptid,name)
• 通过EXPLAIN模拟：Rows 40000+,key_len=10,所以复合索引中的name并没有使用上，断了；也就是==范围查询右边的字段是没有进行索引查询（右边是代表索引的顺序，也就是索引的id；
  ）
  ==
• 因为之前建立的索引顺序 是 create index idx_age_deptid_name ON emp(age,deptid,name)；改为建立的索引顺序 是 ==create index idx_age_name_deptid ON emp(age,name,deptid)==就可以都使用上索引
• 总结：使用第二次索引后 rows=1，所以在电商中的金额字段经常出现范围查询，时间字段等等；索引在经常要进行范围查询的字段，建立符合索引时尽量放至最后

4:mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描

• EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.name != ‘abc’
  CREATE INDEX idx_name ON emp(NAME)

• 建立索引前后：CREATE INDEX idx_name ON emp(NAME)
  

• 建立索引后分析可以发现，使用不等于(!= 或者<>)的时候；可选的索引时name，但是len是null，row=50w+，索引此时失效

5: is not null 也无法使用索引,但是is null是可以使用索引的

• EXPLAIN SELECT * FROM emp WHERE age IS NULL
• EXPLAIN SELECT * FROM emp WHERE age IS NOT NULL
• 建立age索引后分析：
  
• 可以看到：使用了is not null ：type=all ；全表查询；
• 所以如果我们的sql语句中有is not null 或者is not exist 要转化成is null或者is exist

6:like以通配符开头(‘%abc…’)mysql索引失效会变成全表扫描的操作

• 这个结合结构图的话就很容易理解
• 先上分析
• 没建立索引前： EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.name like ‘abc’;
• 查询时间是：0.2s
• 建完索引后 0.01s
• 没建立索引前：EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE emp.name like ‘%abc’; 实际查询是0.2s
• 建立索引后：通肮是0.2s：索引失效，全表扫描，因为索引结构是平衡树按照首字母进行生成，首字母都统配的话索引也就没意义
• 所以在使用like时 避免开头通配

7:字符串不加单引号索引失效

• 上图，已经建立了所以name
  

• 可以看到 建立name索引后 没加引号，类型不匹配索引失效，mysql能查出来是因为mysql进行了类型转换，索引是内部对name这个索引列进行了类型转换，索引失效；和第三个问题一样；
  结合java中使用需要注意，比如现在我们开发很多都是将参数封装在javabean中，如果类型不一样的话，比如数据库中是varchar，但是在bean中属性是integer，虽然查询没问题，但是已经出现这个类型转换的问题；

总结：

使用索引需要注意最佳左前缀法则，注意复合索引的分层；同时注意范围查询的在索引中的位置尽量在最后

单表查询的建议

• 1:对于单键索引，尽量选择针对当前query过滤性更好的索引；过滤性不好比如性别比较难确定唯一，过滤性好的比如手机号，身份证号 容易确定唯一数据行；
• 2:在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。
• 3:在选择组合索引的时候，尽量选择可以能够包含当前query中的where字句中更多字段的索引
• 4:在选择组合索引的时候，如果某个字段可能出现范围查询时，尽量把这个字段放在索引次序的最后面
• 5:书写sql语句时，尽量避免造成索引失效的情况

单表的东西也差不多讲完了

多表查询优化（SQL）

同样是使用EXPLAIN工具，对常用的sql分析进行优化

• 首先我们先建立两张表
• 
• 并且插入了模拟数据

下面开始explain分析

1:EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;

• 未建立索引之前：
  
• 可以看到type都是ALL 全表查询，查询效率差，查询行数:20*20（笛卡尔积）；
• 优化：添加索引优化
• ALTER TABLE book ADD INDEX Y ( card);
  
• ==可以看到book表已经用上索引了 type=ref；查询行数20*1 ==
• __现在也对class表也建立索引：
  
• 可以看到type是index，同时也是全表查询，避免不了全表扫描 ，
• 为什么class表用不到索引呢：两个表的关联查询是又顺序的，关联查询是有角色关系，比如左查询A LEFT join B
  A：是驱动表，b：被驱动表；
  注意所以我们需要给被驱动表建立相关的索引，给驱动表建立索引优化效果有限，会被覆盖索引，type=INDEx（现index是sql使用了索引但是没用通过索引进行过滤，一般是使用了覆盖索引或者是利用索引进行了排序分组）

上面的例子使用的是left join 左外查询,现在换成换成inner join查询

EXPLAIN SELECT * FROM class inner JOIN book ON class.card = book.card;

• 可以看到id一样的顺序book在上面，所以现在book是驱动表，class是被驱动表了
• inner join 连接查询，mysql会自己选择驱动表和被驱动表；
  刚刚我们将book的索引删除，class的没删；
  索引选择mysql自动优化将book作为驱动表；class作为被驱动表；
  这也是left join和inner join的差别
• 同理一张表大一张表小，我们可以将小表作为驱动表，达标作为被驱动表，这样可以减少笛卡尔积

总结关联查询的优化：

• 1:保证被驱动表的join字段已经被索引；
• 2、left join 时，选择小表作为驱动表，大表作为被驱动表。不过还是得看需求
• 3、inner join 时，mysql会自己帮你把小结果集的表选为驱动表。
• 4、子查询尽量不要放在被驱动表，有可能使用不到索引。子查询后是虚表，不能建立索引；索引浪费了一次优化机会
• 5、能够直接多表关联的尽量直接关联，不用子查询。

子查询优化

• 1:尽量不要使用not in 或者 not exists
  用left outer join on xxx is null 替代

排序分组优化

• 使用的是一开始建立的emp和dept表，含有大数据；

1: 无过滤 不索引

• 以下 是否能使用到索引，能否去掉using filesort

1、explain select SQL_NO_CACHE * from emp order by age,deptid;

• 没建索引：
  Extra: Using filesort; 直接在文件上进行排序，速度是很慢的
• 建立索引：create index idx_age_deptid_name on emp (age,deptid,name)
• 查询
• 可以看到索引建立后 Extra: Using filesort;还是手工从文件中排序；并且没用上索引；
  在看下面的sql，进行过滤后
• 在看下面的sql，进行过滤后
• __2、 explain select SQL_NO_CACHE * from emp order by age,deptid limit 10; __
  
• 可以看到，使用了limit过滤使用上了索引；
• 结论：order by 要用上索引必须要有过滤条件，比如where;如果实在是没有的话加个limit也是可以；
• 所以这个就是无过滤，不索引

2:== 顺序错，必排序==

• order by 如果建索引的顺序出现错误肯定会持续using filesort）
• 接下来看几条sql语句：

1:explain select * from emp where age=45 order by deptid;

• 模拟显示：type: ref
  possible_keys: idx_age_deptid_name
  key: idx_age_deptid_name
  key_len: 5
  ref: const
  rows: 47534
  filtered: 100.00
  Extra: Using index condition
• 可以看到可以命中索引；
  按照顺序，而且使用上了过滤条件;
  key_len的定义是where后面的筛选条件命中索引的长度

2:explain select * from emp where age=45 order by deptid,name;

• possible_keys: idx_age_deptid_name,
• Extra: Using index condition
• 按照顺序，而且使用上了过滤条件；Order by 是否用上索引得看Extra字段,如果不是using filesort 就是用上索引了

3:explain select * from emp where age=45 order by deptid,empno;

• Extra: Using index condition; Using filesort;
• 所以如果要优化这条sql的话得优化索引，在索引后面加入empno

4:explain select * from emp where age=45 order by name,deptid;

• Extra: Using index condition; Using filesort
• 顺序不对，此时只用到了age，在order by 的顺序上 优化器是u会调整，因为order by顺序不一样 查询出来就不一样
• 所以如果要使用orderby，所以得建立相同顺序的索引

5: explain select * from emp where deptid=45 order by age;

• Extra: Using where; Using filesort
• 都用不上索引，顺序不对，orderby用不上索引，前面也用不上索引

总结：使用orderby，如果要优化，要注意建立索引的顺序；

3：方向（排序的正倒序）反 必排序

• 同样接下来看几条sql语句：
• 索引顺序: idx_age_deptid_name

1:explain select * from emp where age=45 order by dept id desc, name desc ;

• Extra: Using where;
• 为什么Using filesort没出现，排序相同可以使用索引，结合索引的数据结构，就是换了一下取索引的方向而已；

2:explain select * from emp where age=45 order by deptid asc, name desc ;

• 出现 using filesort

总结：ORDER BY子句，尽量使用Index方式排序,避免使用FileSort方式排序

还有一个十分重要的点：

索引的选择：

查询 年龄为30岁的，且员工编号小于101000的用户，按用户名称排序

• EXPLAIN SELECT SQL_NO_CACHE * FROM emp WHERE age =30 AND empno < 101000 order by name

• 没索引前 0.2s;

• 分析
  

• 怎么建索引呢， 所以age 肯定是要索引，但是如果用员工编号empno的话后面的name索引就会出现断开失效；触发了之前的索引失效原则，范围存储引擎不能是又范围条件右边的索引列
  如果创建create index idx_age_empno_name ON emp(age,empno,name);

• 在通过explain分析
  

• 可以看到出现了Using filesort； key: idx_age_empno_name
  key_len: 9；
  rows ：40

• 现在不用empno创建索引：create index idx_age_name ON emp(age,name);
  

• 可以看到此时：不出现Using filesort；不过通过key_len可以知道只用了age一个索引
  rows: 48102

• 现在在加入empno索引：create index idx_age_empno ON emp(age,empno);
  来看看在这两个索引中mysql选择谁：
  

• ==可以看到两个索引都被选；==且选择了idx_age_empno,且出现了using filesort，
  但是虽然出现了using filesort；但是此时选择的idx_age_empno的rows：40
  是最快的；

总结：

• mysql会选择查询最快的索引，虽然上面那个案例使用idx_age_name并没有出现usingfilesort；但是他的真实查询rows:48102；

排序分组如果不在索引列上，filesort有两种算法：

• mysql就要启动双路排序和单路排序（更快）

双路排序：

• MySQL 4.1之前是使用双路排序,字面意思就是两次扫描磁盘，最终得到数据，
  读取行指针和orderby列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出
  从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段

• 取一批数据，要对磁盘进行了两次扫描，众所周知，I\O是很耗时的，

• 总结：简单来说就是先在磁盘把符合条件的数据都找出，然后在进行排序，然后在然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出；因为全程在硬盘中所以效率也很差

• 所以在mysql4.1之后，出现了第二种改进的算法，就是单路排序。

单路排序

• 从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出，
• 从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出，
• 它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO,但是它会使用更多的空间，
  因为它把每一行都保存在内存中了。
• 总结：简单来所就是在硬盘中读取的每一天都先放进内存，并且同时进行排序，当你第一次全部找完了，那排序也完了，就开始输出要的数据

所以我们使用单路

使用单路的问题：提高Order By的速度

1:Order by时select * 是一个大忌；我们只Query需要的字段， 这点非常重要。在这里的影响是：

• 1.1 当Query的字段大小总和小于max_length_for_sort_data 而且排序字段不是 TEXT|BLOB 类型时，会用改进后的算法——单路排序， 否则用老算法——多路排序。
• 1.2 两种算法的数据都有可能超出sort_buffer的容量，超出之后，会创建tmp文件进行合并排序，导致多次I/O，但是用单路排序算法的风险会更大一些,所以要提高sort_buffer_size。
• 这里都涉及到数据库的调优

2： 尝试提高 sort_buffer_size

• 不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的 1M-8M之间调整

3: 尝试提高 max_length_for_sort_data

• 提高这个参数， 会增加用改进算法的概率。但是如果设的太高，数据总容量超出sort_buffer_size的概率就增大，明显症状是高的磁盘I/O活动和低的处理器使用率. 1024-8192之间调整

上面说的都是order by 现在group by 关键字优化

group by 使用索引的原则几乎跟order by一致 ，唯一==区别是groupby 即使没有过滤条件用到索引，==也可以直接使用索引。

SQL最后的一个优化手段(就是什么索引都用不上了的时候的最后手段)

在筛选条件用不上索引时使用：

• 可以看到：使用<>会导致索引失效，索引是用不上inx_age_deptin_name索引
  把select * 改写成具体字段 后，进行了索引覆盖，相比于上面，比较ROWS，确实是优化了一些效果

所以未来的话尽量不写select *

总结总结：

• 1:尽量选择主键关联，能直接关联最后，尽量不用子查询

• 2:使用group by 在查询的时候只能放groud by 的字段 和函数；

• 3:使用inner join 的话sql优化会自动帮你选择好的驱动表

• 4: <>和！=和is not null 和 is no exist 等等 优化转换，因为会导致索引失效

• 5:根据实际sql建立相关的索引

• 6:在特定的情况中使用straight_join直连，功能和inner join 一样都是取交集，不过可以自己指定驱动表和被驱动表；在分析的时候如果被inner优化成大表是驱动表的情况下，如果你确定小表一定小的情况，不管是在未来数据的增加，你可以通过straight_join直连； a straight_join b;那么前面的a就是驱动表，后年的b就是被驱动表；然后在被驱动表的达标建立索引；
  straight_join：使用前得明确概念，能够做什么
  明确前后俩个表的数量级是不会变；