- 很大程度上减少服务器扫描的数据量
- 很大程度上避免服务器排序和临时表
- 将随机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)二、索引优化 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)
