MySQL索引优化详解

MySQL存储引擎简介

1. 查看命令

   a. 查看所使用的MySQL现在已提供什么存储引擎：

   mysql> show engines;

   

   b. 查看所使用的MySQL当前默认的存储引擎：

   mysql> show variables like '%storage_engine%';

   

2. MyISAM和InnoDB两种存储引擎的区别

   对比项	MyISAM	InnoDB
   主外键	不支持	支持
   事务	不支持	支持
   行表锁	表锁，即使操纵一条记录也会锁住整个表，不适合高并发的操作	行锁，操作时只锁某一行，不对其他行有影响，适合高并发的操作
   缓存	只缓存索引，不缓存真实数据	不仅缓存索引还要缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响
   表空间	小	大
   关注点	性能	事务
   默认安装	Y	Y

   • 事务属性（既然提到，就顺带复习一下）
     事务是由一组SQL语句组成的逻辑处理单元，事务具有以下4个属性，通常简称为事务的ACID属性。

     • 原子性（Atomicity）：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行
     • 一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态，这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性。事务结束时，所有的内部数据结构（如B树索引或双向链表）也都必须是正确的
     • 隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行，这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然
     • 持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持

   • 并发事务处理带来的问题

     • 更新丢失（Lost Update）
       当两个或多个事务选择同一行，然后基于选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新的问题——最后的更新覆盖了由其他事务所做的更新。
       例如，两个程序员修改同一java文件。每个程序员独立地更改其副本，然后保存更改后的副本，这样就覆盖了原始文档。最后保存其更改副本的编辑人员覆盖前一个程序员所做的更改。
       如果在一个程序员完成并提交事务之前，另一个程序员不能访问同一文件，则可避免此问题。
     • 脏读（Dirty Reads）
       一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象地叫做“脏读”。
       一句话：事务A读取到了事务B已修改但尚未提交的数据，还在这个数据基础上做了操作，此时，如果B事务回滚，A读取的数据无效，不符合一致性要求。
     • 不可重复读（Non-Repeatable Reads）
       一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变，或某些记录已经被删除了！这种现象就叫做“不可重复读”。
       一句话：事务A读取到了事务B已经提交的修改数据，不符合隔离性。
     • 幻读（Phantom Reads）
       一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”。
       一句话：事务A读取到了事务B已提交的新增数据，不符合隔离性。
       多说一句：幻读和脏读有点类似：脏读是事务B里面修改了数据，幻读是事务B里面新增了数据。

   • 事务隔离级别
     “脏读”、“不可重复读”和“幻读”，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决。

     读数据一致性及允许的并发副作用	读数据一致性	脏读	不可重复读	幻读
     Read uncommitted	最低级别，只能保证不读取物理上损坏的数据	是	是	是
     Read committed	语句级	否	是	是
     Repeatable read	事务级	否	否	是
     Serializable	最高级别，事务级	否	否	否

   数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使事务在一定程度上，“串行化”进行，这显然与“并发”是矛盾的。同时，不同的应用对读一致性和事务隔离程度的要求也是不同的，比如许多应用对“不可重复读”和“幻读”并不敏感，可能更关心数据并发访问的能力。
   查看MySQL的事务隔离级别：show variables like 'tx_isolation';

性能下降的原因

包括但不限于下列情况：

1. 查询语句写的烂
2. 索引失效
3. 关联查询太多join（设计缺陷或不得已的需求）
4. 服务器调优即各个参数设置（缓冲、线程数等）不合理

索引简介

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。可以得到索引的本质：索引是数据结构。
索引的目的在于提高查询效率，可以类比字典，比如：如果要查“MySQL”这个单词，我们肯定需要定为到m字母，然后从上往下找到y字母，再找到剩下的s、q、l。如果没有索引，那么你可能需要a--z，如果我想找到Java开头的单词呢？或者Oracle开头的单词呢？是不是觉得如果没有索引，这个事情根本无法完成？
你可以简单理解为“排好序的快速查找的数据结构”。

• 详解
  在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。下图就是一种可能的索引方式示例：
  

  为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据的记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。

• 结论
  数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级算法，这种数据结构就是索引。
  一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。
  我们平常所说的索引，如果没有特别指明，都是指B树（多路搜索树，并不一定是二叉树）结构组织的索引。其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+数索引，统称索引。当然，除了B+树这种类型的索引之外，还有哈希索引（hash index）等。

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

• 劣势
  实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。
  虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段。
  索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间建立最优秀的索引，或优化查询。

• MySQL索引分类
  建立复合索引优于建立单值索引。一般而言，一张表最多建5个索引

1. 单值索引：即一个索引只包含单个列，一个表可以有多个单列索引。
2. 唯一索引：索引列的值必须唯一，但允许有空值。
3. 复合索引：即一个索引包含多个列。

• 基本语法

  • 创建
    CREATE [UNIQUE] INDEX indexName ON mytable(columnname(length));
ALTER mytable ADD [UNIQUE] INDEX [indexName] ON (columnname(length));
  • 删除
    DROP INDEX [indexName] ON mytable;
  • 查看
    SHOW INDEX FROM table_name;
  • 使用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，用于全文索引。

MySQL索引结构

1. Btree
   尽量增加检索广度，不增加深度。检索原理：

   初始化介绍：一棵B+树，浅蓝色的块，我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示）。
   如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。
   真实的数据存在于叶子节点，即3、5、9、10、13、15、28、29、36、60、75、79、90、99。
   非叶子节点不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。
   查找过程：如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘3由磁盘加载到内存，发生第二次IO，29在26到30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。
   真实的情况是，3层的B+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

2. Hash

3. full-text

4. R-Tree

哪些情况需要创建索引

1. 主键自动建立唯一索引
2. 频繁作为查询条件的字段应该创建索引
3. 查询中与其他表关联的字段，外键关系建立索引
4. 频繁更新的字段不适合创建索引：因为每次更新不仅仅是更新了记录还会更新索引
5. where条件里用不到的字段不创建索引
6. 单键/组合索引的选择问题，which？（在高并发下倾向创建组合索引）
7. 查询中排序的字段，排序字段若通过索引去访问，将大大提高排序速度
8. 查询中统计或者分组字段

哪些情况不需要创建索引

1. 表记录太少（建议：一百万条以内的数据不建议建立索引，因为足够hold得住）
2. 经常增删改的表
   why：虽然提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
3. 数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。
   注意：如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
   假如一张表有10万行记录，有一个字段A只有T和F两种值，且每个值的分布概率大约为50%，那么对这种表A字段建索引一般不会提高数据库的查询速度。
   索引的选择性是指索引列中不同值的数目与表中记录数的比。如果一张表中有2000条记录，表索引列有1980个不同的值，那么这个索引的选择性就是1980/2000=0.99。一个索引的选择性越接近于1，这个索引的效率就越高。

性能分析

MySQL Query Optimizer

• MySQL中有专门负责优化SELECT语句的优化器模块，主要功能：通过计算分析系统中收集到的统计信息，为客户端请求的Query提供它认为最优的执行计划（它认为最优的数据检索方式，但不见得是DBA认为是最优的，这部分最耗费时间）。
• 当客户端向MySQL请求一条Query，命令解析器模块完成请求分类，区别出SELECT并转发给MySQL Query Optimizer时，MySQL Query Optimizer首先会对整条Query进行优化，处理掉一些常量表达式的预算，直接换算成常量值。并对Query中的查询条件进行简化和转换，如去掉一些无用或显而易见的条件、结构调整等。然后分析Query中的Hint信息（如果有），看现实Hint信息是否可以完全确定该Query的执行计划。如果没有Hint或Hint信息还不足以完全确定执行计划，则会读取所涉及对象的统计信息，根据Query进行写相应的计算分析，然后再得出最后的执行计划。

MySQL常见瓶颈

• CPU：CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据的时候
• IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
• 服务器硬件的性能瓶颈：top, free, iostat和vmstat来查看系统的性能状态。

explain

What's this?

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

What can it do?

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

How to play?

• explain + SQL语句

• 包含的信息
  

• 各字段解释

  • id
select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序。包含三种情况：

    1. id相同，执行顺序由上至下
       

    2. id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
       

    3. id相同和不同，同时存在
       

    id如果相同，可以认为是一组，从上往下顺序执行，在所有组中，id值越大，优先级越高，越先执行。

  • select_type
    有6种类型：SIMPLE，PRIMARY，SUBQUERY，DERIVED，UNION，UNION RESULT，主要是用于区别普通查询、联合查询、子查询等复杂查询。

    1. SIMPLE：简单的select查询，查询中不包含子查询或者UNION
    2. PRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY
    3. SUBQUERY：在SELECT或者WHERE列表中包含了子查询
    4. DERIVED：在FROM列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，把结果放在临时表里
    5. UNION：若第二个SELECT出现在UNION之后，则被标记为UNION，若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED
    6. UNION RESULT：从UNION表获取结果的SELECT

  • table
    显示这一行的数据是关于哪张表的。

  • type

    1. 主要有如下几种类型：ALL，index，range，ref，eq_ref，const,system，NULL。
    2. 访问类型排列：type显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是：
       system > const > eq_ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
    3. 显示查询了显示查询使用了何种类型，常见的如下，从最好到最差依次是：
       system > const > eq_ref > ref > range > index > ALL

       • system：表只有一行记录（等于系统表），这时const类型的特例，平时不会出现，这个也可以忽略不计

       • const：表示通过索引一次就找到了，const用于比较primary key或者unique索引，因为只匹配一行数据，所以很快，如将主键置于where列表中，MySQL就能将该查询转换为一个常量

       • eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
         

       • ref：非唯一性索引扫描，返回匹配某个单独值的所有行。
         本质上也是上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体
         

       • range：只检索给定范围的行，使用一个索引来选择行。
         key列显示使用了哪个索引，一般就是在你的where语句中出现了between、<、>、in等的查询，这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。如下：
         

       • index：Full Index Scan，index与ALL的区别为index类型只遍历索引树，这通常比ALL块，因为索引文件通常比数据文件小。（也就是说虽然ALL和index都是读全表，但index是从索引中读取的，而ALL是从硬盘中读的）

       • ALL：Full Table Scan，将遍历全表以找到匹配的行

  • possible_keys
    显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。

  • key
    实际使用的索引。如果为NULL，则没有使用索引。查询中若使用了覆盖索引，则该索引仅出现在key列表中。如下：
    先建立索引：
    create index idx_col1_col2 on t2(col1, col2);
    然后使用explain语句分析下列SQL语句:
    

  • key_len
    表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好，key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。

  • ref
    显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。如下：
    
    上图中，由key_len可知t1表的idx_col1_col2被充分使用，col1匹配t2表的col1，col2匹配了一个常量，即‘ac’。

  • rows
    根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。如下，观察建立索引前后rows的变化：
    

  • Extra
    包含不适合在其他列中显示但十分重要的额外信息。id、type、key、rows、Extra是较为重要的。

    1. Using filesort：说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”。
       
       从上面两个例子可以看出，后者效率更高，如果出现Using filesort，该SQL语句会执行得比较慢，需要对其进行优化。

    2. Using temporary：使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。临时表会很损耗性能。常见于排序order by和分组查询group by。
       
       如果出现了Using temporary，那么该SQL语句会更慢，需对其进行优化。

    3. Using index：表示相应的select操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错！如果同时出现Using where，表明索引被用来执行索引键值的查找；如果没有同时出现Using where，表明索引被用来读取数据而非执行查找动作。
       
       如果同时出现Using where，表明索引被用来执行索引键值的查找；
       
       如果没有同时出现Using where，表明索引用来读取数据而非执行查找动作。
       覆盖索引（Covering Index），也称为索引覆盖。
       理解方式一：就是select的数据列只用从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件，换句话说查询列要被所建的索引覆盖。
       理解方式二：索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据；当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了（或覆盖了）满足查询结果的数据就叫做覆盖索引。
       注意：如果要使用覆盖索引，一定要注意select列表中只取出需要的列，不可select *，因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降。

    4. Using where：表明使用了where过滤。

    5. Using join buffer：使用了连接缓存。

    6. impossible where：where子句的值总是false，不能用来获取任何元组。如：
       

    7. select tables optimized away：（出现频率很小）在没有GROUP BY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。

    8. distinct：（出现频率很小）优化distinct操作，在找到第一匹配的元组后即停止找同样的动作。

CASE

第一行（执行顺序4）：id列为1，表示是union里的第一个select，select_type列的primary表示该查询为外层查询，table列被标记为<derived3>，表示查询结果里来自一个衍生表，其中derived3中3代表该查询衍生自第三个select查询，即id为3的select。select d1.name ......
第二行（执行顺序2）：id为3，是整个查询中第三个select的一部分，因查询包含在from中，所以为derived。select id, name from t1 where other_column="
第三行（执行顺序3）：select列表中的子查询select_type为subquery，为整个查询中的第二个select。select id from t3
第四行（执行顺序1）：select_type为union，说明第四个select是union里的第二个select，最先执行。select name, id from t2
第五行（执行顺序5）：代表从union的临时表中读取行的阶段，table列的<union1,4>表示用第一个和第四个select的结果进行。

索引优化

索引分析

1. 单表

   • 建表SQL
     

   

   • 案例
     查询category_id为1且comments大于1的情况下，views最多的article_id。
     explain select id, author_id from article where category_id = 1 and comments > 1 order by views desc limit 1;
     结论：很显然，type是ALL，即最坏的情况，Extra里还出现了Using filesort，也是最坏的情况。必须要进行优化。
     开始优化：

     • 新建索引
       alter table article add index idx_article_ccv(category_id, comments, views);或create index idx_article_ccv on article(category_id, comments, views);
     • 第2次EXPLAIN
       explain select id, author_id from article where category_id = 1 and comments > 1 order by views desc limit 1;
       

     结论：type为range，这是可以忍受的。但是extra里使用Using filesort仍是无法接受的。
     但是我们已经建立了索引，为啥没用呢？
     这是因为按照BTree索引的工作原理，先排序category_id，如果遇到相同的category_id，则再排序comments，如果遇到相同的comments则再排序views。
     当comments字段在联合索引里处于中间位置时，因comments > 1 条件是一个范围值（所谓range），MySQL无法利用索引再对后面的views部分进行检索，即range类型查询字段后面的索引无效。

     • 删除第一次建立的索引
       drop index idx_article_ccv on article;
     • 第2次新建索引
       alter table article add index idx_article_cv(category_id, views);或
       create index idx_article_cv on article(category_id, views);
     • 第3次explain
explain select id, author_id from article where category_id = 1 and comments > 1 order by views desc limit 1;
       

     结论：可以看到type变为了ref，Extra中的Using filesort也消失了，结果非常理想。

2. 两表

   • 建表SQL
     

   insert into class(card) values(floor(1 + (rand() * 20)));
insert into book(card) values(floor(1 + (rand() * 20)));
   添加数据，分别执行20次
   
   

   • 案例
     

   explain select * from class left join book on class.card = book.card;
   结论：type中有ALL。
   添加索引进行优化：alter table book add index y(card); 再次进行explain分析：
   
   可以看到第二行的type变为了ref，rows优化也比较明显。 这是由左连接特性决定的。left join条件用于确定如何从右表搜索行，左边一定都有，所以右边是我们的关键点，一定需要建立索引。
   删除旧索引，然后建立新的索引，再进行第3次explain

3. 三表

   • 建表SQL
     

   insert into phone(card) values(floor(1 + (rand() * 20)));
   添加数据，执行20次

   • 案例
     
     建立索引，进行优化：
     alter table phone add index z(card);
alter table book add index y(card);
     再看效果：
     

   后两行的type都是ref，且总的rows优化很好，效果不错，因此索引最好设置在需要经常查询的字段中。

Join语句的优化

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

索引失效（应当避免）

• 建表SQL
  
  插入数据：
  insert into staffs(name, age, pos, add_time) values('z3', 22, 'manager', now());
insert into staffs(name, age, pos, add_time) values('july', 23, 'dev', now());
insert into staffs(name, age, pos, add_time) values('tom', 23, 'dev', now());
• 案例（索引失效）
  建立索引：ALTER TABLE staffs ADD INDEX idx_staffs_nameAgePos(name,age,pos);

  1. 全值匹配
     

  2. 最佳左前缀法则
     如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。（口诀：带头大哥不能死，中间兄弟不能断）

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

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

  5. 尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select *

  6. MySQL在使用不等于（!=或者< >）的时候无法使用索引会导致全表扫描

  7. is null，is not null也无法使用索引

  8. like以通配符开头（‘%abc…’）MySQL索引失效会变成全表扫描的操作
     问题：解决like '%字符串%'时索引不被使用的方法？
     
     在建立索引前，执行以下语句：
     explain select id from tbl_user where name like '%aa%;
explain select name from tbl_user where name like '%aa%;
explain select age from tbl_user where name like '%aa%;
explain select id, name from tbl_user where name like '%aa%;
explain select id, name, age from tbl_user where name like '%aa%;
explain select name, age from tbl_user where name like '%aa%;
explain select * from tbl_user where name like '%aa%;
explain select id, name, age, email from tbl_user where name like '%aa%;
     建立索引：create index idx_user_nameAge on tbl_user(name, age);
     在建立索引后，再执行上述explain，除了最后两条select *即查询列多出了索引外的email字段，其余都利用了覆盖索引或直接使用了主键索引，效果都会挺好。

  9. 字符串不加单引号索引失效

  10. 少用or，用它来连接时会索引失效

  11. 小总结
      全值匹配我最爱，最左前缀要遵守；
      带头大哥不能死，中间兄弟不能断；
      索引列上少计算，范围之后全失效；
      Like百分写最右，覆盖索引不写星；
      不等空值还有or，索引失效要少用；
      VAR引号不可丢，SQL高级也不难。

      假设index(a, b, c)：下表中的各种情况，可自行试验。

      |where语句|索引是否被使用|
      | ------ |--------|-------|
      |where a = 3 |Y，使用到a |
      |where a = 3 and b = 5 |Y，使用到a，b |
      |where a = 3 and b = 5 and c = 4 |Y，使用到a，b，c |
      |where b = 3 或者 where b = 3 and c = 4 或者 where c = 4|N |
      |where a = 3 或者 c = 5 |使用到a，但是c不可以，b中间断了 |
      |where a = 3 and b > 4 and c = 5 |使用到a和b，c不能用在范围之后，b断了 |
      |where a = 3 and b like 'kk%' and c = 4 |Y，使用到a，b，c |
      |where a = 3 and b like '%kk' and c = 4 |Y，只用到a |
      |where a = 3 and b like '%kk%' and c = 4 |Y，只用到a |
      |where a = 3 and b like 'k%kk%' and c = 4 |Y，使用到a，b，c |

• 一般性建议
  • 对于单值索引，尽量选择针对当前query过滤性更好的索引
  • 在选择组合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。
  • 在选择组合索引的时候，尽量选择可以能够包含当前query中的where子句中更多字段的索引
  • 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的。

查询优化

小表驱动大表

小表驱动大表，即小的数据集驱动大的数据集。

select * from A where id in (select id from B)
等价于
for select id from B
for select * from A where A.id = B.id

当B表的数据集必须小于A表的数据集时，用in优于exists。

select * from A where exists (select 1 from B where B.id = A.id)
等价于
for select * from A
for select * from B where B.id = A.id

当A表的数据集系小于B表的数据集时，用exists优于in。
注意：A表与B表的id字段应建立索引。

• exists
  select ... from table where exists (subquery);
  该语法可以理解为：将主查询的数据，放到子查询中做条件验证，根据验证结果（true或false）来决定主查询的结果数据是否得意保留。
• 提示
  • exists(subquery)只返回true或false，因此子查询中的select * 也可以是select 1 或其他，官方说法是实际执行时会忽略select清单，因此没有区别
  • exists子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比，如果担忧效率问题，可进行实际检验以确定是否有效率问题
  • exists子查询往往也可以用条件表达式、其他子查询或者join来替代，何种最优需要具体问题具体分析

order by关键字优化

• order by子句，尽量使用index方式排序，避免使用filesort方式排序

  1. 建表SQL + 添加数据 + 建立索引
     

  2. case
     

  3. MySQL支持两种方式的排序，filesort和index，index效率高，它指MySQL扫描索引本身完成排序，filesort方式效率较低

  4. order by满足两种情况，会使用index方式排序：

     • order by语句使用索引最左前列
     • 使用where子句与order by子句条件列组合满足索引最左前列
• 尽可能在索引列上完成排序操作，遵照索引键的最佳左前缀
• 如果不在索引列上，filesort有两种算法，MySQL就要启动双路排序和单路排序
  1. 双路排序
     MySQL4.1之前是使用双路排序，字面意思就是两次扫描磁盘，最终得到数据，读取行指针和order by列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出。
     从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段。
     取一批数据，要对磁盘进行了两次扫描，总所周知，I/O是很耗时的，所以在MySQL4.1之后，出现了第二种改进的算法，就是单路排序。
  2. 单路排序
     从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO，但是它会使用更多的空间，因为它把每一行都保存在内存中了。
  3. 结论及引申出的问题
     由于单路是后来出现的，总体而言好过双路。但是用单路有问题：
     在sort_buffer中，方法B比方法A要占用很多空间，因为方法B是把所有字段都取出，所以有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序（创建tmp文件，多路合并），排完再去取sort_buffer容量大小，再排......从而多次I/O。
     本来想省一次I/O操作，反而导致了大量的I/O操作，得不偿失。
• 优化策略
  1. 增大sort_buffer_size参数的的设置
  2. 增大max_length_for_sort_data参数的设置

why?

1. order by的时候，select *是一个大忌，因此只查询需要的字段，这点非常重要，在这里影响的是：
   • 当查询的字段大小总和小于max_length_for_sort_data而且排序字段不是text|blob类型时，会用改进后的算法--单路排序，否则用老算法--多路排序
   • 两种算法的数据都有可能超出sort_buffer的容量，超出之后，会创建tmp文件进行合并排序，导致多次I/O，但是用单路排序算法的风险会更大一些，所以要提高sort_buffer_size
2. 尝试提高sort_buffer_size
   不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的
3. 尝试提高max_length_for_sort_data
   提高这个参数，会增加用改进算法的效率，但是如果设的太高，数据总容量超出sort_buffer_size的概率就增大，明显症状是高的磁盘I/O活动和低的处理器使用率

• 小总结
  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=const order by b,c
where a=const and b=const order by c
where a=const order by b,c
where a=const and b>const order by b,c

不能使用索引进行排序
order by a asc,b desc,c desc #排序不一致，有升序有降序
where g=const order by b,c #丢失a索引，没有带头大哥
where a=const order by c #丢失b索引
where a in(...) order by b,c #对于排序来说，多个相等条件也是范围查询

group by关键字优化

• group by实质是先排序后进行分组，遵照索引建的最佳左前缀
• 当无法使用索引列，增大max_length_for_sort_data参数的设置加增大sort_buffer_size参数的设置
• where高于having，能写在where限定的条件就不要去having限定了

慢查询日志

What's this?

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10秒以上的语句。
用它来查看哪些SQL超出了我们的最大忍耐时间值，比如一条SQL执行超过5秒钟，我们就把它定为慢SQL，希望能收集超过5秒钟的SQL，结合之前的explain进行全面分析。

How to play?

• 说明
  默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。
  当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件。

• 查看是否开启及如何开启

  1. 默认
     show varialbes like '%slow_query_log%';
     通过执行上条语句，可以看到，默认情况下，slow_query_log的值为OFF，表示慢查询日志是禁用的。可以通过设置slow_query_log的值来开启。
     

  2. 开启
     set global slow_query_log=1;
     使用set global slow_query_log=1开启了慢查询日志，只对当前数据库生效，并且如果MySQL重启后则会失效。
     如果要永久生效，就必须修改配置文件my.cnf（其它系统变量也是如此）
     修改my.cnf文件，[mysqld]下增加或修改参数slow_query_log和slow_query_log_file后，然后重启MySQL服务器。也即，将如下两行配置进my.cnf文件
     slow_query_log=1
slow_query_log_file=/usr/local/mysql/data/chengchangfudeMacBook-Pro-slow.log
     关于慢查询的参数slow_query_log_file，它指定慢查询日志文件的存放路径，系统默认会给一个缺省的文件 host_name_slow.log（如果没有指定参数slow_query_log_file的话）。

• 开启了慢查询日志后，什么样的SQL才会记录到慢查询日志里面
  这个是由参数long_query_time控制的，默认情况下long_query_time的值为10秒，命令：show variables like 'long_query_time%';
  
  可以使用命令修改，也可以在my.cnf参数里面修改。
  假如在运行时间正好等于long_query_time的情况，并不会被记录下来。也就是说，在MySQL源码里是判断大于long_query_time，而非大于等于。

• case

  1. 查看当前多少秒算慢日志
     show variables like 'long_query_time%';

  2. 设置慢日志的阈值时间
     set global long_query_time=3;
     修改后，需要重新连接数据库或新开一个会话才能看到修改值。

  3. 记录慢查询并分析
     
     

  4. 查询当前系统中有多少条慢查询记录
     

• 配置版
  [mysqld]下配置：
  

日志分析工具mysqldumpslow

在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具：mysqldumpslow。

• 查看mysqldumpslow的帮助信息
  使用mysqldumpslow --help命令进行查看

  s：是表示按照何种方式排序；
  c：访问次数
  l：锁定时间
  r：返回记录
  t：查询时间
  al：平均锁定时间
  ar：平均返回记录数
  at：平均查询时间
  t：即为返回前面多少条的数据；
  g：后边搭配一个正则匹配模式，大小写不敏感的；

• 工作常用参考

  得到返回记录集最多的10个SQL
  mysqlddumpslow -s r -t 10 chengchangfudeMacBook-Pro-slow.log
  得到访问次数最多的10个SQL
  mysqlddumpslow -s c -t 10 chengchangfudeMacBook-Pro-slow.log
  得到按照时间排序的前10条里面含有左连接的查询语句
  mysqlddumpslow -s t -t 10 -g "left join" chengchangfudeMacBook-Pro-slow.log
  另外建议在使用这些命令时结合|和more使用，否则有可能出现爆屏情况
  mysqlddumpslow -s r -t 10 chengchangfudeMacBook-Pro-slow.log | more

Show Profile

Show Profile是MySQL提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL调优的测量。
官网介绍
默认情况下，参数处于关闭状态，并保存最近15次的运行结果，使用set profiling=on;进行开启

• 运行SQL
  select * from emp group by id%10 limit 150000;
select * from emp group by id%20 order by 5;

• 查看结果
  

• 诊断SQL
  show profile cpu, block io for query 上一步前面的问题SQL数字号码;
  如：show profile cpu, block io for query 3;
  
  上面详细列出了一条查询语句经过的所有的过程。
  参数备注：

  ALL 显示所有的开销信息
  BLOCK IO 显示块IO相关开销
  CONTEXT SWITCHES 上下文切换相关开销
  CPU 显示CPU相关开销信息
  IPC 显示发送和接收相关开销信息
  MEMORY 显示内存相关开销信息
  PAGE FAULTS 显示页面错误相关开销信息
  SOURCE 显示和Source_function，Source_file，Source_line相关的开销信息
  SWAPS 显示交换次数相关开销信息

• 日常开发需要注意的结论
  如果出现下列情况，就糟糕了，就必须得对该条SQL语句进行优化。

  1. converting HEAP to MyISAM：查询结果太大，内存都不够用了往磁盘上搬了
  2. create tmp table：创建临时表，拷贝数据到临时表，用完再删除
  3. copying to tmp table on disk：把内存中临时表复制到磁盘，危险！！！
  4. locked