索引

 

 什么是索引？

      索引是帮助Mysql提高获取数据的数据结构，换一句话讲就是“排好序的快速查找的数据结构”。

一.索引的分类

       MySQL主要的几种索引类型：1.普通索引、2.唯一索引、3.主键索引、4.组合索引、5.全文索引。 

     1.普通索引
         是最基本的索引，它没有任何限制。

     2.唯一索引
        与普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一

     3.主键索引
        是一种特殊的唯一索引，一个表只能有一个主键，不允许有空值。

 主键索引和唯一索引的区别：
        主键必唯一，但是唯一索引不一定是主键；
 一张表上只能有一个主键，但是可以有一个或多个唯一索引。 

    4.组合索引
        一个索引包含多个列，实际开发中推荐使用复合索引。

复合索引主要特点：
       如果我们创建了(name, age,xb)的复合索引，那么其实相当于创建了(name, age,xb)、(name, age)、(name)三个索引，这被称为最佳左前缀
特性。因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边，依次递减。

     MySQL INNodb建立复合索引 a,b,c；那么 查询条件 where a =xxx and c= xxx 能用到索引嘛？
回答：可以。

   注意事项: 
    1、对于复合索引,在查询使用时,最好将条件顺序按找索引的顺序,这样效率最高; 
          select * from table1 where col1=A AND col2=B AND col3=D 
    2、如果使用 where col2=B AND col1=A 或者 where col2=B 将不会使用索引

   5.全文索引
   全文搜索的索引。
    FULLTEXT 用于搜索很长一篇文章的时候，效果最好。用在比较短的文本，如果就一两行字的，普通的 INDEX 也可以。

综合小案例理解：

比如你在为某商场做一个会员卡的系统。
这个系统有一个会员表
有下列字段：

会员编号 INT
会员姓名 VARCHAR(10)
会员身份证号码 VARCHAR(18)
会员电话 VARCHAR(10)
会员住址 VARCHAR(50)
会员备注信息 TEXT

那么这个 会员编号，作为主键，使用 PRIMARY
会员姓名 如果要建索引的话，那么就是普通的 INDEX
会员身份证号码 如果要建索引的话，那么可以选择 UNIQUE （唯一的，不允许重复）
会员备注信息 ， 如果需要建索引的话，可以选择 FULLTEXT，全文搜索。
不过 FULLTEXT 用于搜索很长一篇文章的时候，效果最好。
用在比较短的文本，如果就一两行字的，普通的 INDEX 也可以。

 

二.索引的优点缺点

优点：

（1）提高数据检索的效率，降低数据库IO成本。

（2）通过索引对数据进行排序，降低数据的排序成本，降低CPU的消耗。

缺点：

（1）当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。 
（2）索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大

 

三、是否需要创建索引

1：什么时候需要创建索引

     （1.主键自动创建唯一索引

     （2. 较频繁的作为查询条件的字段。

     （3.查询中排序的字段，查询中统计或者分组的字段。

2：什么时候不需要创建索引

       (1.表记录太少的字段

     （2.经常增删改的字段

     （3.唯一性太差的字段，不适合单独创建所以。即使频繁作为查询条件 比如性别，民族，政治面貌（可能总共就是那么几个或几十个值重复使用的字段）

四、索引的注意事项（优化）

1.尽量少使用模糊查询，如果要使用那么，通配符%可以出现在结尾，不能在开头。
    如：name like ‘张%’ ，索引有效
    而：name like ‘%张’ ,索引无效，全表查询

2：or 会引起全表扫描

3：不要使用NOT、!=、NOT IN、NOT LIKE等

4.尽量少使用select*，而是根据需求来选择需要显示的字段

5.索引不会包含有null值的列
只要列中包含有null值都将不会被包含在索引中，复合索引中只要有一列含有null值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为null。

6.不要在列上进行运算，这将导致索引失效而进行全表扫描

7.使用短索引
对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个char(255)的列，如果在前10个或20个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作.

8、union并不绝对比or的执行效率高

我们前面已经谈到了在where子句中使用or会引起全表扫描，一般的，我所见过的资料都是推荐这里用union来代替or。事实证明，这种说法对于大部分都是适用的。
有一点不适用:如果or两边的查询列是一样的话，那么用union则反倒和用or的执行速度差很多，虽然这里union扫描的是索引，而or扫描的是全表。
1.select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi=''2004-9-16'' or fariqi=''2004-2-5''
用时：6423毫秒。扫描计数 2，逻辑读 14726 次，物理读 1 次，预读 7176 次。

 

select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi=''2004-9-16''
union
select gid,fariqi,neibuyonghu,reader,title from Tgongwen where fariqi=''2004-2-5''
用时：11640毫秒。扫描计数 8，逻辑读 14806 次，物理读 108 次，预读 1144 次。

 

五、索引方式（结构）

     mysql有两种所以方式：Hash和BTree。

Hash索引
所谓Hash索引，当我们要给某张表某列增加索引时，将这张表的这一列进行哈希算法计算，得到哈希值，排序在哈希数组上。所以Hash索引可以一次定位，其效率很高，而Btree索引需要经过多次的磁盘IO。
因为Hash索引比较的是经过Hash计算的值，所以在= in <=>(安全等于的时候）塔的效率是非常，但我们开发一般会选择Btree，Hash会存在如下一些缺点。

（1）Hash索引仅仅能满足"=","IN"和"<=>"查询，不能使用范围查询。
由于 Hash 索引比较的是进行 Hash 运算之后的 Hash值，所以它只能用于等值的过滤，不能用于基于范围的过滤，因为经过相应的 Hash算法处理之后的 Hash 值的大小关系，并不能保证和Hash运算前完全一样。

（2）Hash 索引无法被用来避免数据的排序操作。
由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash值，而且Hash值的大小关系并不一定和 Hash运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算；

（3）Hash索引不能利用部分索引键查询。
对于组合索引，Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值，而不是单独计算 Hash值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash 索引也无法被利用。

（4）Hash索引在任何时候都不能避免表扫描。
前面已经知道，Hash 索引是将索引键通过 Hash 运算之后，将 Hash运算结果的 Hash值和所对应的行指针信息存放于一个 Hash 表中，由于不同索引键存在相同 Hash 值，所以即使取满足某个 Hash 键值的数据的记录条数，也无法从 Hash索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果。

（5）Hash索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。

BTREE
B-Tree 索引是 MySQL 数据库中使用最为频繁的索引类型。简单理解，塔就像一棵树，B-Tree索引需要从根节点到枝节点，就能才能访问到页节点的具体数据。
btree索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点开始进行搜索，根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找,通过比较节点页的值和要查找的值可以找到合适的指针进入下一层子节点，这些指针实际上定义了子节点页中值的上限和下限，最终存储引擎要么是找到对应的值，要么是该记录不存在。

B-tree 索引可以用于使用 =, >, >=, <, <= 或者 BETWEEN 运算符的列比较。如果 LIKE 的参数是一个没有以通配符起始的常量字符串的话也可以使用这种索引。

Explain

什么是explain

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

explain 分析sql语句

     使用explain关键字可以模拟优化器执行sql查询语句，从而得知MySQL 是如何处理sql语句。

+----+-------------+-------+------------+------+---------------+-----+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref  | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-----+---------+------+------+----------+-------+

id

select 查询的序列号，包含一组可以重复的数字，表示查询中执行sql语句的顺序。一般有三种情况：
第一种：id全部相同，sql的执行顺序是由上至下；
第二种：id全部不同，sql的执行顺序是根据id大的优先执行；
第三种：id既存在相同，又存在不同的。先根据id大的优先执行，再根据相同id从上至下的执行。

select_type

         simple：   简单的select 查询，查询中不包含子查询或者union
       primary：   查询中若包含任何复杂的子查询，最外层查询则被标记为primary
    subquery：   在select或where 列表中包含了子查询
       derived：   在from列表中包含的子查询被标记为derived（衍生）MySQL会递归执行这些子查询，把结果放在临时表里。
          union：   若第二个select出现在union之后，则被标记为union，若union包含在from子句的子查询中，外层select将被标记为：derived
 union result：   从union表获取结果的select

type

这是一个非常重要的参数，连接类型，常见的有：all , index , range , ref , eq_ref , const , system , null 八个级别。
性能从最优到最差的排序：system > const > eq_ref > ref > range > index > all
对java程序员来说，若保证查询至少达到range级别或者最好能达到ref则算是一个优秀而又负责的程序员。
        all：  全表扫描。全表扫描无疑是最差，若是百万千万级数据量，全表扫描会非常慢。
   index： 索引全表扫描。全索引文件扫描比all好很多，毕竟从索引树中找数据，比从全表中找数据要快。
  range： 索引范围扫描。只检索给定范围的行，使用索引来匹配行。范围缩小了，当然比全表扫描和全索引文件扫描要快。sql语句中一般会有between，in，>，< 等查询。
       ref： 非唯一性索引扫描，本质上也是一种索引访问，返回所有匹配某个单独值的行。比如查询公司所有属于研发团队的同事，匹配的结果是多个并非唯一值。
 eq_ref： 唯一性索引扫描，对于每个索引键，表中有一条记录与之匹配。比如查询公司的CEO，匹配的结果只可能是一条记录，
   const： 表示通过索引一次就可以找到，const用于比较primary key 或者unique索引。因为只匹配一行数据，所以很快，若将主键至于where列表中，MySQL就能将该查询转换为一个常量。
system：表只有一条记录（等于系统表），这是const类型的特列，平时不会出现，了解即可

possible_keys

显示查询语句可能用到的索引(一个或多个或为null)，不一定被查询实际使用。仅供参考使用。

key

显示查询语句实际使用的索引。若为null，则表示没有使用索引。select_type为index_merge时，这里可能出现两个以上的索引，其他的select_type这里只会出现一个。

key_len

显示索引中使用的字节数，可通过key_len计算查询中使用的索引长度。在不损失精确性的情况下索引长度越短越好。另外，key_len只计算where条件用到的索引长度，而排序和分组就算用到了索引，也不会计算到key_len中。

ref

如果是使用的常数等值查询，这里会显示const，如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段，如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func

rows

这里是执行计划中估算的扫描行数，不是精确值，值越大越不好。

extra

     Using filesort：   说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序” 。出现这个就要立刻优化sql。
Using temporary： 使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序 order by 和 分组查询 group by。 出现这个更要立刻优化sql。
        Using index： 表示相应的select 操作中使用了覆盖索引（Covering index），避免访问了表的数据行，效果不错！如果同时出现Using where，表明索引被用来执行索引键值的查找。如果没有同时出现Using                                   where，表示索引用来读取数据而非执行查找动作。
Using index condition： 在5.6版本后加入的新特性，优化器会在索引存在的情况下，通过符合RANGE范围的条数 和 总数的比例来选择是使用索引还是进行全表遍历。
       Using where： 表明使用了where 过滤
 Using join buffer： 表明使用了连接缓存
impossible where： where 语句的值总是false，不可用，不能用来获取任何元素
                 distinct： 优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作。

filtered

一个百分比的值，和rows 列的值一起使用，可以估计出查询执行计划(QEP)中的前一个表的结果集，从而确定join操作的循环次数。小表驱动大表，减轻连接的次数。

总结：

通过explain之后，我们可以通过一些属性的优化

          id属性（通过id属性我们能够准确知道查询语句的执行属性，同时结合小表驱动大表的原则进行优化。
      type属性（至少优化到range级别），
key_len属性（在不损失精确性的情况下索引长度越短越好）
     rows属性  (看是否减少扫描行数)。
     extra属性（如果出现Using filesort Using temporary必须优化，如果能出现Using index那就完美了）。

 

有关覆盖索引的理解:

  就是select 的数据列只用从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select 列表中的字段，而不必根据索引再次读取数据文件。

什么意思呢？比如有张a表，表里有关id属性，同时id是添加主键索引的。
    select * form a where a='1'
这个时候通过索引很快找到唯一一条数据，但还会通过这个属性去表中找到这行的其它属性，因为你是select*
   但如果你改成select id form a where a='1'
那你通过索引获得这个数据后，不需要在获取这条数据的其它信息，那就不用再去查询该表，而是直接把信息返回出来。

MySQL优化

 

一、单表、双表、三表优化

1、单表

   首先结论就是，range类型查询字段后面的索引全都无效

（1）建表

create table if not exists article(
    id int primary key auto_increment,
    author_id int not null,
    category_id int not null,
    views int not null,
    comments int not null,
    title varchar(255) not null,
    content text not null
  );
 insert into article values(null,1,1,1,1,'1','1');
 insert into article values(null,2,2,2,2,'2','2');
 insert into article values(null,1,1,3,3,'3','3');

（2）未创建索引查询

explain select  id,author_id,views from  article
 where category_id = 1
 and comments > 1
 order by views desc limit 1;
+----+-------------+---------+------+---------------+------+---------+------+------+-----------------------------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows | Extra                       |
+----+-------------+---------+------+---------------+------+---------+------+------+-----------------------------+
|  1 | SIMPLE      | article | ALL  | NULL          | NULL | NULL    | NULL |    3 | Using where; Using filesort |
+----+-------------+---------+------+---------------+------+---------+------+------+-----------------------------+
  -- 总结上面出现的情况:type=all,产生了全表扫描, 并且出现了Using filesort,使用了外部的索引排序,所以优化是必须的

（3）创建category_id,comments,views复合索引

  create index ind_article_ccv on article(category_id,comments,views);
  
  --再次执行如下指令:
  explain select  id,author_id,views from  article
  where category_id = 1
  and comments > 1
 order by views desc limit 1;
+----+-------------+---------+-------+-----------------+-----------------+---------+------+------+---------------------------------------+
| id | select_type | table   | type  | possible_keys   | key             | key_len | ref  | rows | Extra                                 |
+----+-------------+---------+-------+-----------------+-----------------+---------+------+------+---------------------------------------+
|  1 | SIMPLE      | article | range | ind_article_ccv | ind_article_ccv | 8       | NULL |    1 | Using index condition; Using filesort |
+----+-------------+---------+-------+-----------------+-----------------+---------+------+------+---------------------------------------+
--创建索引之后type=range, 但是Using filesort 依然存在.

索引创建了,为什么在排序的时候没有生效?

          这是因为先排序category_id, 如果遇到相同的category_id,则再排序comments, 如果遇到相同的comments则再排序views,

   当comments字段在联合索引处于中间位置时,因为comments>1条件是一个范围值,所以type=range,mysql无法再利用索引对后面的views部分进行检索.

   即range类型查询字段后面的索引全都无效

(4)、创建category_id,views复合索引

   -- 删除上面创建的索引:
   drop index ind_article_ccv on article;
   -- 重新创建索引: 
   create index ind_art_cb on article(category_id,views);
   -- 重新执行如下代码 
   explain select  id,author_id,views from  article
   where category_id = 1
   and comments > 1
   order by views desc limit 1;
+----+-------------+---------+------+---------------+------------+---------+-------+------+-------------+
| id | select_type | table   | type | possible_keys | key        | key_len | ref   | rows | Extra       |
+----+-------------+---------+------+---------------+------------+---------+-------+------+-------------+
|  1 | SIMPLE      | article | ref  | ind_art_cb    | ind_art_cb | 4       | const |    2 | Using where |
+----+-------------+---------+------+---------------+------------+---------+-------+------+-------------+
--完美！

2、两张表

记住一句话：左连接索引建在右表，右连接索引做在左表

select * from class left join book on class.card = book.card;

--这是左连接，那么索引应该建在book表中的card（create index X on book(card);）

3、三张表

select * from class left join book on class.card = book.card left join phone on book.card = phone.card;
 --这里就可以见两个索引，一个在book表，一个在phone表中
 -- 创建索引:
 create index Y on book(card);
 create index Z on phone(card);

 

二、经典案例分析

1、建表，插数据，建索引

--建表
 create table test3(
      id int primary key auto_increment,
        c1 char(10),
        c2 char(10),
        c3 char(10),
        c4 char(10),
        c5 char(10)
  );
--插入数据
 insert into test3 values(null,'a1','a2','a3','a4','a5');
 insert into test3 values(null,'b1','b2','b3','b4','b5');
 insert into test3 values(null,'c1','c2','c3','c4','c5');
 insert into test3 values(null,'d1','d2','d3','d4','d5');
 insert into test3 values(null,'e1','e2','e3','e4','e5');
--查看数据
select * from test3;
-- 创建索引
 create index ind_test3_c1234 on test3(c1,c2,c3,c4);
 show index from test3;

2、分析下面索引情况

 explain select * from test3 where c1='a1';
 explain select * from test3 where c1='a1' and c2='a2';
 explain select * from test3 where c1='a1' and c2='a2' and c3='a3';
 explain select * from test3 where c1='a1' and c2='a2' and c3='a3' and c4='a4';
 explain select * from test3 where c1='a1' and c2='a2' and c3='a3' and c4='a4' and c5='a5';
 --上面五条所以肯定都有用，而且越到下面越精准
 
 -- 请执行如下问题SQL,分析会出现的问题
 --(1)
   explain select * from test3 where c1='a1' and c2='a2' and c4='a4' and c3='a3' ;
 --四个字段索引都有效

 -- (2)
    explain select * from test3 where c4='a1' and c3='a2' and c2='a4' and c1='a3' ;
 --四个字段索引都有效

 -- (3)
   explain select * from test3 where c1='a1' and c2='a2' and c3>'a3' and c4='a4';
 --c1、c2、c3索引有效，

 -- (4)
  explain select * from test3 where c1='a1' and c2='a2' and c4>'a4' and c3='a3';
 --四个字段索引都有效

 -- (5)
   explain select * from test3 where c1='a1' and c2='a2' and c4='a4' order by c3; 
 --c1、c2有效，c3仅对排序有效

 -- (6)
   explain select * from test3 where c1='a1' and c2='a2' order by c3; 
 --c1、c2有效，c3仅对排序有效

 -- (7)
    explain select * from test3 where c1='a1' and c2='a2' order by c4; 
 --c1、c2有效，因为中间缺少c3,所以索引排序无效。会报：Using filesort

 -- (8)
   explain select * from test3 where c1='a1' and c5='a5' order by c2,c3; 
 --c1有效，但是c2,c3用于排序,无filesort

 --(9)
   explain select * from test3 where c1='a1' and c5='a5' order by c3,c2; 
 -- 只用了c1这个字段索引,但是由于c3,c2顺序颠倒了,所以无法使用索引排序,出现filesort
 
 --(10)
   explain select * from test3 where c1='a1' and c2='a2' and c5='a5' order by c3,c2; 
 --因为排序字段c2已经是一个常量了，所以对于后面c2排序其实没有啥意义，所以不会出现filesort
 
 --(11)
   explain select * from test3 where c1='a1' and c4='c4' group by c2,c3;
 --用到了c1字段索引,但用到c2、c3排序
 
 --(12)
   explain select * from test3 where c1='a1' and c4='c4' group by c3,c2;
 -- 索引字段顺序不正确，出现了Using temporary; Using filesort

 

三、in和exists区别

   1、 优化规则: 小表驱动大表,即小的数据集驱动大的数据集

       如果是exists，那么以外层表为驱动表，先被访问，如果是IN，那么先执行子查询。 所以如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用in。 反之如果外层的主查询记录较少，子查询中的表大，又有索引时使用exists。

    另外IN时不对NULL进行处理。

    -- 当A的数据集大于B的数据集时, in 优于 exists
  select * from A where id in (select id from B);
  --等价于:
      for select id from B
          for select * from A where A.id = B.id;
  
  -- 当A的数据集小于B的数据集时, exists优于in
  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

2、not in 和not exists

   如果查询语句使用了not in 那么内外表都进行全表扫描，没有用到索引；而not extsts 的子查询依然能用到表上的索引。所以无论那个表大，用not exists都比not in要快。

 

四、Order by 排序优化

1、单路排序、排序

    order by 子句,尽量使用index方式排序,避免使用Filesort方式排序，尽可能再索引列上完成排序操作,遵照索引建的最佳左前缀， 如果不在索引列上。filesort算法:双路排序,单路排序。

     (1)双路排序: mysql4.1之前使用的双路排序,字面意思就是扫描两次磁盘,从而得到最终的数据,读取行指针和order by 列,对它们进行排序,然后扫描已经排序好的列表,按照列表中的值重新从列表中读取对应的数据输出. 从磁盘取排序字段,在buffer进行排序,再从磁盘取其它字段

     (2)单路排序:从磁盘读取查询需要的所有列,按照order by列在buffer对它们进行排序,然后扫描排序后的列表进行输出,它的效率更快一些,避免了第二次读取数据.并且把随机IO变成了顺序IO,但是它会使用更多的空间,因为它把每一行都保存到内存中

2、结论:

   (1)单路排序,优于双路排序
   (2)在sort_buffer中,单路要比双路占用更多的空间,因为单路是把所有的字段都取出来,有可能导致取出的数据总大小超出了sort_buffer的容量,导致每次只能取sort_buffer容量大小的数据,进行排序(创建tmp,多路合并),排完再取sort_buffer容量大小,再排…从而导致了多次IO. (本来想节省一次IO操作,反而导致了大量的IO操作,得不偿失)

3、优化策略

  (1)增大sort_buffer_size参数设置
  (2)增大max_length_for_sort_data参数设置

4、why

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

5、总结

--mysql有两种排序方式: 文件排序和扫描有序索引排序
  -- mysql能为排序和查询使用相同的索引
  
  index abc(a,b,c)
  order by --能使用最左前缀
  order by a
  order by 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=const order by a,d     /*d不是索引*/
 where a in(…) order by b,c     /*对于排序来说,in 相当于是范围查询*/

 

慢查询

   简介
       开启慢查询日志，可以让MySQL记录下查询超过指定时间的语句，通过定位分析性能的瓶颈，才能更好的优化数据库系统的性能。

一、配置慢查询

1、参数说明

        slow_query_log ： 慢查询开启状态（默认关闭）
 slow_query_log_file ： 慢查询日志存放的位置（这个目录需要MySQL的运行帐号的可写权限， 一般设置为MySQL的数据存放目录）
      long_query_time ： 查询超过多少秒才记录(默认10秒）

2.查看慢查询相关参数

 show variables like 'slow_query%';
+---------------------------+----------------------------------+
| Variable_name             | Value                            |
+---------------------------+----------------------------------+
| slow_query_log            | OFF                              |
| slow_query_log_file       | /mysql/data/localhost-slow.log   |
+---------------------------+----------------------------------+

show variables like 'long_query_time';
+-----------------+-----------+
| Variable_name   | Value     |
+-----------------+-----------+
| long_query_time | 10.000000 |
+-----------------+-----------+

3.配置慢查询 

    它有两种配置方式，一种是全局变量配置，一种是配置文件配置

（1）全局变量配置 

 --将 slow_query_log 全局变量设置为“ON”状态
    set global slow_query_log='ON'; 

 --设置慢查询日志存放的位置
  set global slow_query_log_file='/usr/local/mysql/data/slow.log';

 --查询超过1秒就记录
   set global long_query_time=1;

（2）修改配置文件my.cnf(linux环境下)

    slow_query_log = ON
    slow_query_log_file = /usr/local/mysql/data/slow.log
    long_query_time = 1

总结：
（1）通过全局变量配置好后，需要关闭会话后重新打开查询才有效，通过配置文件需要重启Mysql服务器后才有效
（2） 因为开启慢查询会影响性能，一般建议通过全局变量配置，这样重启服务器又是默认关闭慢查询状态。

 

 二、mysqldumpslow工具

   mysqldumpslow是Mysql自带的一个工具，有了它我们可以不用去上面的log文件去找，如果数据平凡操作，那么去log查下日志也是比较繁琐的一件事。

 1、主要命令

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

2、举例

 --1.得到返回记录集最多的10个SQL
   mysqldumpslow -s -r -t 10 /logs/mysql-slow.log

 --2.得到访问次数最多的10个SQL
   mysqldumpslow -s -c -t 10 /logs/mysql-slow.log

 --3.得到按照时间排序的前10条里面含有做链接的查询语句
   mysqldumpslow -s t -t 10 -g "left join" /logs/mysql-slow.log
 
 --4.另外建议在使用这些命令时结合|和more使用，否则又可能出现爆屏情况
   mysqldumpslow -s r -t 10 /logs/mysql-slow.log | more

 

三、show profile

  Show profiles是5.0.37之后添加的，要想使用此功能，要确保版本在5.0.37之后。

1、开启 show profile

 show variables like "profiling";--默认是关闭的
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | OFF   |
+---------------+-------+

--开启
set profiling=1;

2、运行指定SQL 

我这里运行几条SQL语句，然后运行

show profiles;--会列出所有在这个开启期间执行的SQL，并附上QUERY ID
+----------+------------+----------------------------+
| Query_ID | Duration   | Query                      |
+----------+------------+----------------------------+
|        1 | 0.00168025 | select * from vhr.employee |
|        2 | 0.06573200 | select * from vhr.hr       |
+----------+------------+----------------------------+
--我们可以看到显示最近操作的两条语句，列表大小由profiling_history_size会话变量控制， 默认值为15.最大值为100

3、诊断具体SQL

show profile cpu,block io for queryid  --对应2中的query_id

SHOW PROFILE CPU FOR QUERY 1;--查询query_id为1的具体信息
+----------------------+----------+----------+------------+
| Status               | Duration | CPU_user | CPU_system |
+----------------------+----------+----------+------------+
| starting             | 0.000194 | 0.000000 |   0.000000 |
| checking permissions | 0.000012 | 0.000000 |   0.000000 |
| Opening tables       | 0.000030 | 0.000000 |   0.000000 |
| init                 | 0.000053 | 0.000000 |   0.000000 |
| System lock          | 0.000011 | 0.000000 |   0.000000 |
| optimizing           | 0.000003 | 0.000000 |   0.000000 |
| statistics           | 0.000014 | 0.000000 |   0.000000 |
| preparing            | 0.000010 | 0.000000 |   0.000000 |
| executing            | 0.000001 | 0.000000 |   0.000000 |
| Sending data         | 0.001213 | 0.000000 |   0.000000 |
| end                  | 0.000014 | 0.000000 |   0.000000 |
| query end            | 0.000012 | 0.000000 |   0.000000 |
| closing tables       | 0.000019 | 0.000000 |   0.000000 |
| freeing items        | 0.000070 | 0.000000 |   0.000000 |
| cleaning up          | 0.000025 | 0.000000 |   0.000000 |
+----------------------+----------+----------+------------+

日常开发需要注意的结论：

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

   注：以上四个中若出现一个或多个，表示sql 语句 必须优化。