一、索引基本知识 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 = 1union 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 = 1union 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 fromtest_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 fromtest_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: 0Duplicates: 0Warnings: 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优化|Mysql-索引优化】第二个案例：

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

推荐阅读

• 

  山蕨菜的苦涩味 蕨菜为什么是苦的
• 

  安卓引导文件是哪个好,安卓刷手机有两种方式:刷卡和线刷
• 

  2021年哈尔滨招生政策 哈尔滨2023升学报名问题汇总
• 

  创业时代2018几月播出
• 

  我母亲血压160,心速过快,大概在110以上,蛋白尿检验一个加号..请问是什么原因,应该如何治疗?
• 

  松下NP洗碗机不排水的解决方案
• 

  遇到松下空调代码H怎么办？3个处理方法帮助你
• 

  养小鬼是什么意思?财经女生宿舍养小鬼
• 

  微信里怎样做电子稿微信里做电子稿的方法
• 

  衰老|厨房中这5类食物是加速身体衰老的帮凶，不想老态龙钟，少吃为妙
• 

  华帝燃气热水器怎么开关
• 

  减压的方法 上班族必知11妙招
• 

  白鹿原最新剧情 白鹿原剧情介绍
• 

  男性肾虚吃什么 冬季食疗除男人困扰
• 

  阴天有必要用防晒品吗？
• 

  吃春笋的禁忌注意事有哪些
• 

  10首最美的七夕诗词 鹊桥仙 七夕
• 

  蜂蜜怎样保存才最好
• 

  牛年祝福领导最实在的话 牛年祝福领导
• 

  电脑桌面整理软件哪个好用,占内存少？

• java|创作365天纪念日
• 生产系统中的机器学习工程|基于 Docker 快速使用远程（云）数据库
• TiDB|构建实时数仓 - 当 TiDB 偶遇 Pravega
• redis|Redis的使用场景和注意事项
• 缓存|Redis的应用场景
• MySQL|【MySQL系列】- LIKE查询 以%开头一定会让索引失效吗
• sql|达梦SQL优化经验
• sql|达梦数据库sql优化
• sql|达梦sql优化实践5