Mysql-索引优化

一、索引基本知识 1、索引的优点

• 很大程度上减少服务器扫描的数据量
• 很大程度上避免服务器排序和临时表
• 将随机IO变成顺序IO

2、索引的用处

• 使用索引列可以快速查找Where条件的行数据

mysql> explain select * from emp where empno = 7469;
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key       | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | emp   | NULL       | ref  | pk_emp_no     | pk_emp_no | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+---------------+-----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

• 假如存在多个索引，mysql优化器会使用最少行的索引。

mysql> explain select * from emp where emp.empno = 1 and job >1;
+----+-------------+-------+------------+------+------------------+-----------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys    | key       | key_len | ref   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+------------------+-----------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | emp   | NULL       | ref  | uk_job,pk_emp_no | pk_emp_no | 4       | const |    1 |   100.00 | Using where |
+----+-------------+-------+------------+------+------------------+-----------+---------+-------+------+----------+-------------+
1 row in set, 2 warnings (0.00 sec)

• 假如存在多个索引，则优化器可以使用索引的任何最左前缀来查找行数据。
• 当表有连接的时候，从其他表检索行数据。
• 索引树是有排序机制的，所有能很快找到min或max值。
• 如果排序或分组时在可以用索引的最左前缀上完成的，则对表进行排序和分组。
• 在某些情况下，可以优化查询以检索值而无需查询数据行。

3、索引的分类

• 主键索引

        顾名思义，表的主键作为索引。唯一且非空。

• 唯一索引

        值唯一，不可重复，当数据表没有创建索引时，mysql会为该表的唯一键自动创建索引。

• 普通索引

        普通的列建的索引。

• 全文索引

        一般是给varchar、char、tex类型创建的索引，一般用的极少。

• 组合索引

        多个常用的字段组合创建索引。

where语句	索引是否发挥作用
where a=4	只使用a列索引
where a=4 and b = 5	只使用a、b列
where a=4 and b=5 and c=6	使用了a、b、c列
where b=3 or c=6	没使用
where a=4 and c=6	只使用a
where a=4 and b>5 and c=6	只使用了a、b列
where a=4 and b like '%5%' and c=6	只使用了a列

4、索引数据结构

• 哈希表

哈希表由数组+链表组成，所有数据进入哈希表，需先进行散列算法，算出对应数组中的下标值，放入数组，如果当前下标数组中存在值，就在当前位置追加到链表中。

       优点：查询速度快

       缺点：需要将数据文件放到内存中，比较吃内存空间。由于他是通过散列算法计算后存储数据，故不支持排序，范围查询，所以实际工作中很少使用。根据Mysql官网描述，使用哈希索引只在Memory存储引擎中。

• B+树

   目前，Mysql最常用的索引数据结构，相对于二叉树、红黑树等机构，它的优势就是树的层级少、IO次数相对最少，存储前已经将数据范围变成多个区间，区间检索也比较快。在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有的叶子节点之间是一种链式环结构。

   B+树示意图：

       下面我们分别对InnoDB、MyISAM引擎分析数据机构。

InnoDB引擎是聚簇索引，它将索引和数据保存在一个文件里面，可以直接根据主键扫描叶子节点中的数据，最大限度提高了IO密集型应用的性能。更新数据成本相对高点，因为它会强制将每个被更新的行移动到新的位置，当主键被更新导致需要移动时，可能面临叶分裂的问题。

MyISAM引擎是非聚簇索引，它的数据文件、索引文件是分开存放的。

 

5、索引匹配方式

• 全值匹配

/*
 全值匹配指的是和所有的索引列进行匹配
*/
mysql> explain select * from staffs where names = 'July' and age = '23' and pos = 'dev';
+----+-------------+--------+------------+------+---------------+---------+---------+-------------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key     | key_len | ref               | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+---------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | staffs | NULL       | ref  | idx_nap       | idx_nap | 140     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+---------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.03 sec)

• 最左前缀匹配

/*
  最左前缀匹配，只匹配前面的几列，如果
*/
mysql> explain select * from staffs where names = 'July' and age > 25;
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
| id | select_type | table  | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | staffs | NULL       | range | idx_nap       | idx_nap | 78      | NULL |    1 |   100.00 | Using index condition |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)


mysql> explain select * from staffs where names >1 and age =22 and pos = '111';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | staffs | NULL       | ALL  | idx_nap       | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 2 warnings (0.00 sec)

• 列前缀匹配

/*
 列前缀匹配，匹配索引列值的前部分字符
*/
mysql> explain select * from staffs where names like 'J%';
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
| id | select_type | table  | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | staffs | NULL       | range | idx_nap       | idx_nap | 74      | NULL |    1 |   100.00 | Using index condition |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.01 sec)

• 范围值匹配

/*
 范围值匹配，可以匹配索引列某个范围的数据。
*/
mysql> explain select * from staffs where id > 1;
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | staffs | NULL       | range | PRIMARY       | PRIMARY | 4       | NULL |    1 |   100.00 | Using where |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

• 精确匹配

/*
 精确匹配，根据索引列，精确指定一个数值进行筛选。
*/
mysql> explain select * from staffs where names = '1';
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | staffs | NULL       | ref  | idx_nap       | idx_nap | 74      | const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

 二、索引优化 1、覆盖索引

        如果有一个查询包含了所有索引的列，并且条件中也是根据这些索引字段检索，我们称之为覆盖索引。不是所有类型的索引都可以称为覆盖索引，它必须要存储索引列的值，不同的存储实现索引的方式不同，不是所有引擎都支持覆盖索引，Memory引擎不支持。

  当执行计划中的Extra列中出现了Using index属性，表示已经实现了覆盖索引。

mysql> explain select names,age,pos from staffs where names =1 and age =22 and pos = '111';
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table  | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | staffs | NULL       | index | idx_nap       | idx_nap | 140     | NULL |    1 |   100.00 | Using where; Using index |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
1 row in set, 3 warnings (0.00 sec)

 2、常用索引优化小技巧

•   使用索引查询时，避免使用索引列来运算，可以将运算转到业务层。

mysql> explain select * from staffs where id  = 1;
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | staffs | NULL       | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+-------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from staffs where id +1 = 2;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | staffs | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    1 |   100.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

•   尽量使用主键查询，主键查询不会触发回表。
•   前缀索引

       有时候需要索引很长的字符串，这会让索引变的大且慢，通常情况下可以使用某个列开始的部分字符串，这样大大的节约索引空间，从而提高索引效率，但这会降低索引的选择性，索引的选择性是指不重复的索引值和数据表记录总数的比值，范围从1/#T到1之间。索引的选择性越高则查询效率越高，因为选择性更高的索引可以让mysql在查找的时候过滤掉更多的行。

        一般情况下某个列前缀的选择性也是足够高的，足以满足查询的性能，但是对应BLOB，TEXT，VARCHAR。类型的列，必须要使用前缀索引，因为mysql不允许索引这些列的完整长度，使用该方法的诀窍在于要选择足够长的前缀以保证较高的选择性，通过又不能太长。

        模拟一个字符串比较多，存在一定重复性的字段做实例

mysql> select count(*) as cnt,userName from test_user_copy group by userName order by cnt desc limit 10;
+-----+-------------------+
| cnt | userName          |
+-----+-------------------+
| 188 | wangwu334444      |
| 144 | zhengqiang334444  |
| 144 | zhengqiang114444  |
| 144 | zhengqiang1134444 |
| 144 | huangli1134444    |
| 144 | huangli2234444    |
| 144 | huangli1234444    |
| 144 | lisi334444        |
|  44 | lili1111          |
|  44 | lili12222         |
+-----+-------------------+
10 rows in set (0.01 sec)


--可以通过如下方式来计算完整列的选择性，可以看到当前缀长度到达6之后，再增加前缀长度，选择性提升的幅度已经很小了，所以我们可以截取该字段的前6位做索引。
mysql> select count(distinct left(userName,3))/count(*) as sel3,
    -> count(distinct left(userName,4))/count(*) as sel4,
    -> count(distinct left(userName,5))/count(*) as sel5,
    -> count(distinct left(userName,6))/count(*) as sel6,
    -> count(distinct left(userName,7))/count(*) as sel7,
    -> count(distinct left(userName,8))/count(*) as sel8
    -> from test_user_copy;
+--------+--------+--------+--------+--------+--------+
| sel3   | sel4   | sel5   | sel6   | sel7   | sel8   |
+--------+--------+--------+--------+--------+--------+
| 0.0036 | 0.0036 | 0.0042 | 0.0048 | 0.0060 | 0.0071 |
+--------+--------+--------+--------+--------+--------+
1 row in set (0.01 sec)

--计算完成之后可以创建前缀索引
alter table test_user_copy add key(userName(6));

--注意：前缀索引是一种能使索引更小更快的有效方法，但是也包含缺点：mysql无法使用前缀索引做order by 和 group by。 

•   索引扫描做排序

        mysql有两种方式可以生成有序的结果：通过排序 *** 作或者按索引顺序扫描，如果explain出来的type列的值为index,则说明mysql使用了索引扫描来做排序。扫描索引本身是很快的，因为只需要从一条索引记录移动到紧接着的下一条记录。但如果索引不能覆盖查询所需的全部列，那么就不得不每扫描一条索引记录就得回表查询一次对应的行，这基本都是随机IO，因此按索引顺序读取数据的速度通常要比顺序地全表扫描慢。

        mysql可以使用同一个索引即满足排序，又用于查找行，如果可能的话，设计索引时应该尽可能地同时满足这两种任务。

        只有当索引的列顺序和order by子句的顺序完全一致，并且所有列的排序方式都一样时，mysql才能够使用索引来对结果进行排序，如果查询需要关联多张表，则只有当order by子句引用的字段全部为第一张表时，才能使用索引做排序。order by子句和查找型查询的限制是一样的，需要满足索引的最左前缀的要求，否则，mysql都需要执行顺序 *** 作，而无法利用索引排序。

mysql> explain select names,age,pos from staffs where names =1 and age =22 and pos = '111' order by names, age, pos ;
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
| id | select_type | table  | partitions | type  | possible_keys | key     | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | staffs | NULL       | index | idx_nap       | idx_nap | 140     | NULL |    1 |   100.00 | Using where; Using index |
+----+-------------+--------+------------+-------+---------------+---------+---------+------+------+----------+--------------------------+
1 row in set, 3 warnings (0.00 sec)


mysql> explain select names,age,pos from staffs where names =1 and age =22 and pos = '111' order by add_time ;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | staffs | NULL       | ALL  | idx_nap       | NULL | NULL    | NULL |    1 |   100.00 | Using where; Using filesort |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
1 row in set, 3 warnings (0.00 sec)

• nuion all/in/or如果可以选择，尽量选择in

mysql> select * from test_user_copy where id = 1 or id = 2;
+------+-------------------+----------+-------+-------------+---------+
| id   | userName          | userCode | phone | mail        | address |
+------+-------------------+----------+-------+-------------+---------+
|    1 | lili1111          | wangwu1  | 13892 | 1024@qq.com | 1112    |
|    2 | lili1111          | wangwu2  | 13894 | 1024@qq.com | 1113    |
|    1 | lili12222         | wangwu1  | 13892 | 1024@qq.com | 1112    |
|    2 | lili12222         | wangwu2  | 13894 | 1024@qq.com | 1113    |
|    1 | lili2222          | wangwu1  | 13892 | 1024@qq.com | 1112    |
+----+-------+-----+-------+------------------------------------------+
5 rows in set (0.01 sec)

mysql> select * from test_user_copy where id = 1  union all select * from test_user_copy where id = 2;
+------+-------------------+----------+-------+-------------+---------+
| id   | userName          | userCode | phone | mail        | address |
+------+-------------------+----------+-------+-------------+---------+
|    1 | lili1111          | wangwu1  | 13892 | 1024@qq.com | 1112    |
|    2 | lili1111          | wangwu2  | 13894 | 1024@qq.com | 1113    |
|    1 | lili12222         | wangwu1  | 13892 | 1024@qq.com | 1112    |
|    2 | lili12222         | wangwu2  | 13894 | 1024@qq.com | 1113    |
|    1 | lili2222          | wangwu1  | 13892 | 1024@qq.com | 1112    |
+----+-------+-----+-------+------------------------------------------+
5 rows in set (0.00 sec)

mysql> select * from test_user_copy where id in( 1, 2);
+------+-------------------+----------+-------+-------------+---------+
| id   | userName          | userCode | phone | mail        | address |
+------+-------------------+----------+-------+-------------+---------+
|    1 | lili1111          | wangwu1  | 13892 | 1024@qq.com | 1112    |
|    2 | lili1111          | wangwu2  | 13894 | 1024@qq.com | 1113    |
|    1 | lili12222         | wangwu1  | 13892 | 1024@qq.com | 1112    |
|    2 | lili12222         | wangwu2  | 13894 | 1024@qq.com | 1113    |
|    1 | lili2222          | wangwu1  | 13892 | 1024@qq.com | 1112    |
+----+-------+-----+-------+------------------------------------------+
5 rows in set (0.00 sec)

-- in的效率稍微要高些，可能数据量越大，这个时间会越明显
mysql> show profiles;
+----------+------------+------------------------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                                          |
+----------+------------+------------------------------------------------------------------------------------------------+
|        1 | 0.00354950 | select * from test_user_copy where id = 1 or id = 2                                            |
|        2 | 0.00466700 | select * from test_user_copy where id = 1  union all select * from test_user_copy where id = 2 |
|        3 | 0.00163800 | select * from test_user_copy where id in( 1, 2)                                                |
+----------+------------+------------------------------------------------------------------------------------------------+
8 rows in set, 1 warning (0.00 sec)

• 保持索引字段原始类型

mysql> explain select * from staffs where phone = '1';
+----+-------------+--------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | staffs | NULL       | ref  | uk_phone      | uk_phone | 768     | const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

-- phone 是varchar类型，强制转出数值类型查询后，索引会失效。
mysql> explain select * from staffs where phone = 1;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | staffs | NULL       | ALL  | uk_phone      | NULL | NULL    | NULL |    2 |    50.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 3 warnings (0.00 sec)

• 在已知查询结果数量的时候，尽量使用limit

        在很多应用场景中我们需要将数据进行分页，一般会使用limit加上偏移量的方法实现，同时加上合适的orderby 的子句，如果这种方式有索引的帮助，效率通常不错，否则的话需要进行大量的文件排序 *** 作，还有一种情况，当偏移量非常大的时候，前面的大部分数据都会被抛弃，这样的代价太高。 要优化这种查询的话，要么是在页面中限制分页的数量，要么优化大偏移量的性能。

       我们常常会误以为mysql会只返回需要的数据，实际上mysql却是先返回全部结果再进行计算，在日常的开发习惯中，经常是先用select语句查询大量的结果，然后获取前面的N行后关闭结果集。

mysql> explain select id, phone from  test_user_copy where phone > 32766 order by phone;
+----+-------------+----------------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+
| id | select_type | table          | partitions | type | possible_keys | key  | key_len | ref  | rows  | filtered | Extra                       |
+----+-------------+----------------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+
|  1 | SIMPLE      | test_user_copy | NULL       | ALL  | uk_phone_id   | NULL | NULL    | NULL | 32868 |    33.33 | Using where; Using filesort |
+----+-------------+----------------+------------+------+---------------+------+---------+------+-------+----------+-----------------------------+
1 row in set, 2 warnings (0.00 sec)

mysql> explain select id, phone from  test_user_copy where phone > 32766 order by phone limit 1;
+----+-------------+----------------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
| id | select_type | table          | partitions | type  | possible_keys | key         | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | test_user_copy | NULL       | index | uk_phone_id   | uk_phone_id | 1023    | NULL |    1 |    33.33 | Using where |
+----+-------------+----------------+------------+-------+---------------+-------------+---------+------+------+----------+-------------+
1 row in set, 3 warnings (0.00 sec)

3、索引监控

mysql> show status like 'Handler_read%';
+-----------------------+-------+
| Variable_name         | Value |
+-----------------------+-------+
| Handler_read_first    | 2     |
| Handler_read_key      | 3     |
| Handler_read_last     | 0     |
| Handler_read_next     | 0     |
| Handler_read_prev     | 0     |
| Handler_read_rnd      | 0     |
| Handler_read_rnd_next | 64    |
+-----------------------+-------+
7 rows in set (0.00 sec)

Handler_read_first：读取索引第一个条目的次数

Handler_read_key：通过index获取数据的次数

Handler_read_last：读取索引最后一个条目的次数

Handler_read_next：通过索引读取下一条数据的次数

Handler_read_prev：通过索引读取上一条数据的次数

Handler_read_rnd：从固定位置读取数据的次数

Handler_read_rnd_next：从数据节点读取下一条数据的次数

重点查看第二条和最后一条的记录。

4、优化案例

表结构如下：

mysql> SET FOREIGN_KEY_CHECKS=0;
Query OK, 0 rows affected (0.00 sec)

mysql> DROP TABLE IF EXISTS `itdragon_order_list`;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> CREATE TABLE `itdragon_order_list` (
    ->   `id` bigint(11) NOT NULL AUTO_INCREMENT COMMENT '主键id，默认自增长',
    ->   `transaction_id` varchar(150) DEFAULT NULL COMMENT '交易号',
    ->   `gross` double DEFAULT NULL COMMENT '毛收入(RMB)',
    ->   `net` double DEFAULT NULL COMMENT '净收入(RMB)',
    ->   `stock_id` int(11) DEFAULT NULL COMMENT '发货仓库',
    ->   `order_status` int(11) DEFAULT NULL COMMENT '订单状态',
    ->   `descript` varchar(255) DEFAULT NULL COMMENT '客服备注',
    ->   `finance_descript` varchar(255) DEFAULT NULL COMMENT '财务备注',
    ->   `create_type` varchar(100) DEFAULT NULL COMMENT '创建类型',
    ->   `order_level` int(11) DEFAULT NULL COMMENT '订单级别',
    ->   `input_user` varchar(20) DEFAULT NULL COMMENT '录入人',
    ->   `input_date` varchar(20) DEFAULT NULL COMMENT '录入时间',
    ->   PRIMARY KEY (`id`)
    -> ) ENGINE=InnoDB AUTO_INCREMENT=10003 DEFAULT CHARSET=utf8;
Query OK, 0 rows affected (0.03 sec)

mysql>
mysql> INSERT INTO itdragon_order_list VALUES ('10000', '81X97310V32236260E', '6.6', '6.13', '1', '10', 'ok', 'ok', 'auto', '1', 'itdragon', '2017-08-28 17:01:49');
Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO itdragon_order_list VALUES ('10001', '61525478BB371361Q', '18.88', '18.79', '1', '10', 'ok', 'ok', 'auto', '1', 'itdragon', '2017-08-18 17:01:50');
Query OK, 1 row affected (0.00 sec)

mysql> INSERT INTO itdragon_order_list VALUES ('10002', '5RT64180WE555861V', '20.18', '20.17', '1', '10', 'ok', 'ok', 'auto', '1', 'itdragon', '2017-09-08 17:01:49');
Query OK, 1 row affected (0.00 sec)

第一个案例： 

mysql> select * from itdragon_order_list where transaction_id = "81X97310V32236260E";
+-------+--------------------+-------+------+----------+--------------+----------+------------------+-------------+-------------+------------+---------------------+
| id    | transaction_id     | gross | net  | stock_id | order_status | descript | finance_descript | create_type | order_level | input_user | input_date          |
+-------+--------------------+-------+------+----------+--------------+----------+------------------+-------------+-------------+------------+---------------------+
| 10000 | 81X97310V32236260E |   6.6 | 6.13 |        1 |           10 | ok       | ok               | auto        |           1 | itdragon   | 2017-08-28 17:01:49 |
+-------+--------------------+-------+------+----------+--------------+----------+------------------+-------------+-------------+------------+---------------------+
1 row in set (0.00 sec)

-- --通过查看执行计划发现type=all,需要进行全表扫描
mysql> explain select * from itdragon_order_list where transaction_id = "81X97310V32236260E";
+----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table               | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | itdragon_order_list | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

--优化一、为transaction_id创建唯一索引
mysql>  create unique index idx_order_transaID on itdragon_order_list (transaction_id);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

--当创建索引之后，唯一索引对应的type是const，通过索引一次就可以找到结果，普通索引对应的type是ref，表示非唯一性索引赛秒，找到值还要进行扫描，直到将索引文件扫描完为止，显而易见，const的性能要高于ref
mysql> explain select * from itdragon_order_list where transaction_id = "81X97310V32236260E";
+----+-------------+---------------------+------------+-------+--------------------+--------------------+---------+-------+------+----------+-------+
| id | select_type | table               | partitions | type  | possible_keys      | key                | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------------------+------------+-------+--------------------+--------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | itdragon_order_list | NULL       | const | idx_order_transaID | idx_order_transaID | 453     | const |    1 |   100.00 | NULL  |
+----+-------------+---------------------+------------+-------+--------------------+--------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)


--优化二、使用覆盖索引，查询的结果变成 transaction_id,当extra出现using index,表示使用了覆盖索引
mysql> explain select transaction_id from itdragon_order_list where transaction_id = "81X97310V32236260E";
+----+-------------+---------------------+------------+-------+--------------------+--------------------+---------+-------+------+----------+-------------+
| id | select_type | table               | partitions | type  | possible_keys      | key                | key_len | ref   | rows | filtered | Extra       |
+----+-------------+---------------------+------------+-------+--------------------+--------------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | itdragon_order_list | NULL       | const | idx_order_transaID | idx_order_transaID | 453     | const |    1 |   100.00 | Using index |
+----+-------------+---------------------+------------+-------+--------------------+--------------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

第二个案例：

--创建复合索引
mysql> create index idx_order_levelDate on itdragon_order_list (order_level,input_date);
Query OK, 0 rows affected (0.03 sec)
Records: 0  Duplicates: 0  Warnings: 0

--创建索引之后发现跟没有创建索引一样，都是全表扫描，都是文件排序
mysql> explain select * from itdragon_order_list order by order_level,input_date;
+----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+----------------+
| id | select_type | table               | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra          |
+----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+----------------+
|  1 | SIMPLE      | itdragon_order_list | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | Using filesort |
+----+-------------+---------------------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

--可以使用force index强制指定索引
mysql> explain select * from itdragon_order_list force index(idx_order_levelDate) order by order_level,input_date;
+----+-------------+---------------------+------------+-------+---------------+---------------------+---------+------+------+----------+-------+
| id | select_type | table               | partitions | type  | possible_keys | key                 | key_len | ref  | rows | filtered | Extra |
+----+-------------+---------------------+------------+-------+---------------+---------------------+---------+------+------+----------+-------+
|  1 | SIMPLE      | itdragon_order_list | NULL       | index | NULL          | idx_order_levelDate | 68      | NULL |    3 |   100.00 | NULL  |
+----+-------------+---------------------+------------+-------+---------------+---------------------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

--其实给订单排序意义不大，给订单级别添加索引意义也不大，因此可以先确定order_level的值，然后再给input_date排序
mysql> explain select * from itdragon_order_list where order_level=3 order by input_date;
+----+-------------+---------------------+------------+------+---------------------+---------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table               | partitions | type | possible_keys       | key                 | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+---------------------+------------+------+---------------------+---------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | itdragon_order_list | NULL       | ref  | idx_order_levelDate | idx_order_levelDate | 5       | const |    1 |   100.00 | Using index condition |
+----+-------------+---------------------+------------+------+---------------------+---------------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)