- 很大程度上减少服务器扫描的数据量
- 很大程度上避免服务器排序和临时表
- 将随机IO变成顺序IO
- 使用索引列可以快速查找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值。
- 如果排序或分组时在可以用索引的最左前缀上完成的，则对表进行排序和分组。
- 在某些情况下，可以优化查询以检索值而无需查询数据行。
- 主键索引
顾名思义，表的主键作为索引。唯一且非空。
- 唯一索引
值唯一，不可重复，当数据表没有创建索引时，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列 |
- 哈希表
哈希表由数组+链表组成，所有数据进入哈希表，需先进行散列算法，算出对应数组中的下标值，放入数组，如果当前下标数组中存在值，就在当前位置追加到链表中。
优点：查询速度快
缺点：需要将数据文件放到内存中，比较吃内存空间。由于他是通过散列算法计算后存储数据，故不支持排序，范围查询，所以实际工作中很少使用。根据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)如果有一个查询包含了所有索引的列，并且条件中也是根据这些索引字段检索，我们称之为覆盖索引。不是所有类型的索引都可以称为覆盖索引，它必须要存储索引列的值，不同的存储实现索引的方式不同，不是所有引擎都支持覆盖索引，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)- 使用索引查询时，避免使用索引列来运算，可以将运算转到业务层。
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 | [email protected] | 1112 |
| 2 | lili1111 | wangwu2 | 13894 | [email protected] | 1113 |
| 1 | lili12222 | wangwu1 | 13892 | [email protected] | 1112 |
| 2 | lili12222 | wangwu2 | 13894 | [email protected] | 1113 |
| 1 | lili2222 | wangwu1 | 13892 | [email protected] | 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 | [email protected] | 1112 |
| 2 | lili1111 | wangwu2 | 13894 | [email protected] | 1113 |
| 1 | lili12222 | wangwu1 | 13892 | [email protected] | 1112 |
| 2 | lili12222 | wangwu2 | 13894 | [email protected] | 1113 |
| 1 | lili2222 | wangwu1 | 13892 | [email protected] | 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 | [email protected] | 1112 |
| 2 | lili1111 | wangwu2 | 13894 | [email protected] | 1113 |
| 1 | lili12222 | wangwu1 | 13892 | [email protected] | 1112 |
| 2 | lili12222 | wangwu2 | 13894 | [email protected] | 1113 |
| 1 | lili2222 | wangwu1 | 13892 | [email protected] | 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)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)欢迎分享，转载请注明来源：内存溢出
微信扫一扫
支付宝扫一扫
评论列表（0条）