MySQL优化：索引

时间：2020-07-09 18:47:25 阅读：112 评论：0 收藏：0 [点我收藏+]

前言

索引是关系型数据库优化时最常见、成本最低的一种优化方式。

在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。

如果一张表是一本书，那么索引就相当于目录。查找指定的内容不需要每一页每一页的查找，只需要先查找目录，再直接翻到指定的位置即可。

前置阅读

由于本篇主要利用explain关键字来分析索引使用情况，所以先了解explain关键字很有必要

MySQL优化：explain、show profile和show processlist

SQL脚本

本篇使用的SQL脚本如下

create table staffs(
    id int primary key auto_increment,
    name varchar(24) not null default ‘‘ comment ‘姓名‘,
    age int not null default 0 comment ‘年龄‘,
    pos varchar(20) not null default ‘‘ comment ‘职位‘,
    add_time timestamp not null default current_timestamp comment ‘入职时间‘
  ) charset utf8 comment ‘员工记录表‘;

-- 创建联合索引
alter table staffs add index idx_nap(name, age, pos);

-- 表结构来自sakila数据库（直接导入sakila数据库即可）
-- 地址：https://downloads.mysql.com/docs/sakila-db.zip
CREATE TABLE `rental` (
  `rental_id` int(11) NOT NULL AUTO_INCREMENT,
  `rental_date` datetime NOT NULL,
  `inventory_id` mediumint(8) unsigned NOT NULL,
  `customer_id` smallint(5) unsigned NOT NULL,
  `return_date` datetime DEFAULT NULL,
  `staff_id` tinyint(3) unsigned NOT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`rental_id`),
  UNIQUE KEY `rental_date` (`rental_date`,`inventory_id`,`customer_id`),
  KEY `idx_fk_inventory_id` (`inventory_id`),
  KEY `idx_fk_customer_id` (`customer_id`),
  KEY `idx_fk_staff_id` (`staff_id`),
  CONSTRAINT `fk_rental_customer` FOREIGN KEY (`customer_id`) REFERENCES `customer` (`customer_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_rental_inventory` FOREIGN KEY (`inventory_id`) REFERENCES `inventory` (`inventory_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_rental_staff` FOREIGN KEY (`staff_id`) REFERENCES `staff` (`staff_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=16050 DEFAULT CHARSET=utf8mb4

由于SQL优化器的行为受到表中数据量的影响比较大，所以当使用explain关键字分析出的结果与理论不符合时，请在表中插入大量数据。
下面利用MySQL的存储过程往表中插入一千万数据，以便测试的时候能更好的看到效果
表结构如下：

-- 表结构
CREATE TABLE `sicimike` (
  `id` INT(11) NOT NULL PRIMARY KEY AUTO_INCREMENT,
  `name` VARCHAR(24) NOT NULL DEFAULT ‘‘,
  `age` INT(11) NOT NULL DEFAULT ‘0‘,
  `add_time` DATETIME NOT NULL DEFAULT now()
) ENGINE=INNODB DEFAULT CHARSET=utf8;

编写存储过程，往sicimike表中插入一千万条数据

-- 存储过程
DELIMITER $$

DROP PROCEDURE IF EXISTS `sicimike_data`$$

CREATE PROCEDURE `sicimike_data`()
  BEGIN
    DECLARE i  INT DEFAULT 0;
    -- 关闭自动提交
    SET SESSION autocommit=0;
     WHILE i  < 10000000 DO
       INSERT INTO `sicimike` ( `name`,  `age`)
       VALUES(SUBSTRING(MD5(RAND()),1,10),  FLOOR(RAND()*100));
       SET  i = i + 1;
     END WHILE;
     -- 提交
     COMMIT;
  END$$

DELIMITER ;

编译之后调用

call sicimike_data()

执行大概花了4分多钟，数据新增成功后，创建索引

alter table sicimike add index idx_na(name, age)

再查看数据量

mysql> select count(*) from sicimike;
+----------+
| count(*) |
+----------+
| 10000000 |
+----------+
1 row in set (3.16 sec)

索引

索引分类

MySQL(5.6)支持的索引可以分为五类：主键索引、唯一索引、普通索引、组合索引和全文索引。其中组合索引就是由多个列联合创建的索引。

大多数MySQL索引（PRIMARY KEY，UNIQUE，INDEX和FULLTEXT）存储在B+树中。例外：空间数据类型的索引使用R树； MEMORY（MySQl存储引擎的一种）表还支持哈希索引。 InnoDB对FULLTEXT索引使用反向列表。

索引作用

MySQL可以利用索引进行以下操作：

快速查找与WHERE子句匹配的行。
从consideration中消除行。如果可以在多个索引之间进行选择，则MySQL通常会使用查找最少行数的索引（最具选择性的索引）。
如果表具有多列索引，那么优化器可以使用索引的任何最左前缀来查找行。例如，如果在（col1，col2，col3）上有一个三列索引，则在（col1），（col1，col2）和（col1，col2，col3）上具有索引搜索功能。
当有表连接的时候，从其他表检索行数据。如果声明相同的类型和大小，MySQL可以更有效地在列上使用索引。
查找特定索引列key_col的MIN()或MAX()值。
如果排序或分组是在可用索引的最左前缀上完成的，则可以利用索引对表进行排序或分组。如果在所有关键部分后面都有DESC，则按相反顺序读取key。
在某些情况下，查询可以优化为检索值而无需查询数据行（为查询提供所有必要结果的索引称为覆盖索引）。如果查询仅从表中使用某些索引中包含的列，则可以从索引树中检索所选值以提高速度

也就是说在优化SQL时，如果想优化以上的问题，才需要使用索引，由此可见，索引虽然强大，但是也有适用范围。

匹配方式

索引的匹配方式是指在什么情况下会使用到索引。索引的匹配方式有六种：全值匹配、最左前缀匹配（组合索引）、匹配列前缀、匹配范围值、精确匹配前面的列并范围匹配另外一列、只访问索引的查询（覆盖索引）

全值匹配：全值匹配指的是和索引中的所有列进行匹配
查询方式如下

explain select * from staffs where name = ‘July‘ and age = ‘23‘ and pos = ‘dev‘;

执行结果

mysql> explain select * from staffs where name = ‘July‘ and age = ‘23‘ and pos = ‘dev‘;
+----+-------------+--------+------+---------------+---------+---------+-------------------+------+-----------------------+
| id | select_type | table  | type | possible_keys | key     | key_len | ref               | rows | Extra                 |
+----+-------------+--------+------+---------------+---------+---------+-------------------+------+-----------------------+
|  1 | SIMPLE      | staffs | ref  | idx_nap       | idx_nap | 140     | const,const,const |    1 | Using index condition |
+----+-------------+--------+------+---------------+---------+---------+-------------------+------+-----------------------+
1 row in set (0.01 sec)

最左前缀匹配

查询方式如下

explain select * from staffs where name = ‘July‘ and age = ‘23‘;

执行结果

mysql> explain select * from staffs where name = ‘July‘ and age = ‘23‘;
+----+-------------+--------+------+---------------+---------+---------+-------------+------+-----------------------+
| id | select_type | table  | type | possible_keys | key     | key_len | ref         | rows | Extra                 |
+----+-------------+--------+------+---------------+---------+---------+-------------+------+-----------------------+
|  1 | SIMPLE      | staffs | ref  | idx_nap       | idx_nap | 78      | const,const |    1 | Using index condition |
+----+-------------+--------+------+---------------+---------+---------+-------------+------+-----------------------+
1 row in set (0.00 sec)

可以看到，只查询name和age依然可以使用idx_nap索引，也验证了前文索引作用里的第三条
匹配列前缀：可以匹配某一列的值的开头部分（注意区分于最左前缀匹配）
查询方式如下
```
explain select * from staffs where name like ‘J%‘;
```

执行结果

mysql> explain select * from staffs where name like ‘J%‘;
+----+-------------+--------+-------+---------------+---------+---------+------+------+-----------------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra                 |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-----------------------+
|  1 | SIMPLE      | staffs | range | idx_nap       | idx_nap | 74      | NULL |    1 | Using index condition |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

只匹配name列的前缀部分，依然可以使用到索引。稍微修改下这条SQL，执行得到结果

mysql> explain select * from staffs where name like ‘%J‘;
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | staffs | ALL  | NULL          | NULL | NULL    | NULL |    1 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

可以看到把%放在字符串的前面，就不能使用索引，这也就是有些文章说的：模糊查询可能会使索引失效。
匹配范围值：可以查找某一个范围的数据
查询方式如下
```
explain select * from staffs where name > ‘Mary‘;
```

执行结果

mysql> explain select * from staffs where name > ‘Mary‘;
+----+-------------+--------+-------+---------------+---------+---------+------+------+-----------------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra                 |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-----------------------+
|  1 | SIMPLE      | staffs | range | idx_nap       | idx_nap | 74      | NULL |    1 | Using index condition |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

可以看到根据name列进行范围查询，也可以使用到索引，但是使用别的列可不行

mysql> explain select * from staffs where age > 20;
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | staffs | ALL  | NULL          | NULL | NULL    | NULL |    1 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

可以看到使用age列进行范围查询就不行，具体原因参照最左前缀匹配

精确匹配前面的列并范围匹配另外一列：也就是把范围匹配放在最后
查询方式如下

explain select * from staffs where name = ‘July‘ and age > 25

执行结果

mysql> explain select * from staffs where name = ‘July‘ and age > 25;
+----+-------------+--------+-------+---------------+---------+---------+------+------+-----------------------+
| id | select_type | table  | type  | possible_keys | key     | key_len | ref  | rows | Extra                 |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-----------------------+
|  1 | SIMPLE      | staffs | range | idx_nap       | idx_nap | 78      | NULL |    1 | Using index condition |
+----+-------------+--------+-------+---------------+---------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

只访问索引的查询：查询的时候只需要访问索引，不需要访问数据行，本质上就是覆盖索引
查询方式如下

explain select name,age,pos from staffs where name = ‘July‘ and age = 25 and pos = ‘dev‘;

执行结果

mysql> explain select name,age,pos from staffs where name = ‘July‘ and age = 25 and pos = ‘dev‘;
+----+-------------+--------+------+---------------+---------+---------+-------------------+------+--------------------------+
| id | select_type | table  | type | possible_keys | key     | key_len | ref               | rows | Extra                    |
+----+-------------+--------+------+---------------+---------+---------+-------------------+------+--------------------------+
|  1 | SIMPLE      | staffs | ref  | idx_nap       | idx_nap | 140     | const,const,const |    1 | Using where; Using index |
+----+-------------+--------+------+---------------+---------+---------+-------------------+------+--------------------------+
1 row in set (0.00 sec)

因为组合索引是根据name、age、pos三列创建的，而该条SQL语句返回的列和过滤条件都没有涉及到别的列，所以会发生所以索引覆盖。有的地方也叫覆盖索引，比较容易会被误解成一种索引类型，但是实际上是一种现象，所以博主习惯叫索引覆盖。

数据结构

MySQL索引使用的数据结构主要有两种：B+树和哈希。
要说B+树，首先得知道B树（B-tree就是B树），B树是一种自平衡的多路查找树，能够保证数据有序。查找、插入、删除数据时间复杂度都是O( $\log$ ⁿ)。概括来说是一个一般化的二叉查找树（binary search tree）。一个节点可以拥有至少两个子节点。如果每个节点最多有m个子节点，称为m阶树。每个节点包含两个部分，关键字和指向子节点的指针。其特点如下：

如果根节点不是叶子节点，那么它至少有两个子节点
所有叶子节点均位于同一层
每个节点最多拥有m个子节点（m >= 2）
除根节点和叶子节点外，每个节点最少拥有ceil(m/2)个子节点
假设每个非终端节点包含n个关键字信息，其中：
1. K_i (i=0,1…n)为关键字，且关键字按照升序排列，K_(i-1) < K_i
2. 关键字的个数n必须满足：ceil(m/2)-1 <= n <= m-1
3. 非叶子节点的指针：P[1]，P[2]，…，P[M]。其中P[1]指向关键字小于K[1]的子树，P[M]指向关键字大于K[n]的子树，其他P[i]指向关键字属于(K[i-1], K[i])的子树

B树的结构图如下

技术分享图片

B+树是B树的变体，其定义基本与B树相同，除了以下几点：

非叶子节点的指针与关键字个数相等
非叶子节点的子树指针P[i]，指向关键字值[K[i], K[i+1])的子树
非叶子节点仅用来索引，数据全部存在叶子节点
所有叶子节点均有一个链指向下一个叶子节点（主要是为了方便直接在叶子节点做范围统计）

B+树的结构图如下技术分享图片

B+树相比于B树更适合做数据库索引，原因有以下几点：

非叶子节点不存储数据，只存储索引，所以每个节点容纳的关键字更多，树的高度更低，检索数据时的磁盘I/O次数更少
非叶子节点不存储数据，每次查询最终都会落到叶子节点，所以查询效率更稳定
对数据库的范围扫描更快，因为只需要遍历叶子节点，且叶子节点可以直接访问到下一个叶子节点。而不需要像B树一样对每一层进行遍历

哈希索引顾名思义是基于哈希表的实现。

Hash，一般翻译做散列、杂凑，或音译为哈希，是把任意长度的输入（又叫做预映射pre-image）通过散列算法变换成固定长度的输出，该输出就是散列值。这种转换是一种压缩映射，也就是，散列值的空间通常远小于输入的空间，不同的输入可能会散列成相同的输出，所以不可能从散列值来确定唯一的输入值。简单的说就是一种将任意长度的消息压缩到某一固定长度的消息摘要的函数。

哈希索引自身只需存储对应的hash值，所以索引的结构十分紧凑，这让哈希索引查找的速度非常快。结构紧凑、查找速度快，甚至可以说是哈希索引的唯一优点了。

哈希索引的缺点或者说局限性非常多：

只能进行精确匹配而不能进行模糊查找或者范围匹配
哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行（不支持索引覆盖）
哈希索引数据并不是按照索引值顺序存储的，所以无法进行排序
哈希索引不支持部分列匹配查找，哈希索引是使用索引列的全部内容来计算哈希值
哈希冲突比较严重的时候，查找效率明显降低，维护的代价也比较高

在MySQL中，只有memory类型的存储引擎显式支持哈希索引。

存储方式

我们常常听见聚簇索引和非聚簇索引这连个词，但是这两个词并不是指索引类型，而是指数据的存储方式。

聚簇索引也叫簇类索引，是一种对磁盘上实际数据重新组织以按指定的一个或多个列的值排序。由于聚簇索引的索引页面指针指向数据页面，所以使用聚簇索引查找数据几乎总是比使用非聚簇索引快。每张表只能建一个聚簇索引，并且建聚簇索引需要至少相当该表120%的附加空间，以存放该表的副本和索引中间页。
非聚簇索引，索引的一种。索引分为聚簇索引和非聚簇索引两种。建立索引的目的是加快对表中记录的查找或排序。索引顺序与数据物理排列顺序无关。

由于聚簇索引代表了数据在表中的物理存储顺序，因此一个表只能包含一个聚簇索引。

聚簇索引文件中每个搜索码值都对应一个索引值，叶子节点保存的不只是键值，还保存了该条记录其他列的信息。也就是根据索引值可以直接找到该条记录的所有字段信息。

非聚簇索引只为索引码的某些值建立索引项，叶子节点只保存键值和该列地址或者键值和主键，找到键值后还需根据地址或者主键找到该条记录，也就是回表操作。但是，也不是所有的非聚簇索引查找都需要回表操作，如果查询的SELECT子句和WHERE子句后面出现的列，均在同一个非聚餐索引中，这时候可以直接从索引中返回数据，而不需要进行回表操作，这种操作就叫索引覆盖。

没有回表操作，查询自然会快很多，所以索引覆盖也是常用的优化手段。

MyISAM中所有索引均为非聚簇索引，InnoDB中有且仅有一个聚簇索引，聚簇索引选取规则如下：

若有一个主键被定义，则该主键就是聚簇索引
如果没有主键，该表第一个非空唯一索引就是聚簇索引
如果不满足以上条件，InnoDB会生成一个隐藏的主键作为聚簇索引

最佳实践

想要优化SQL，首先得知道哪些SQL需要被优化，除了前面一篇：MySQL优化：explain、show profile和show processlist 提到的命令，MySQL还提供了一种记录慢查询日志的方式。

开启慢查询日志

SHOW VARIABLES LIKE ‘%query%‘

执行结果

mysql> SHOW VARIABLES LIKE ‘%query%‘;
+------------------------------+---------------+
| Variable_name                | Value         |
+------------------------------+---------------+
| binlog_rows_query_log_events | OFF           |
| ft_query_expansion_limit     | 20            |
| have_query_cache             | YES           |
| long_query_time              | 10.000000     |
| query_alloc_block_size       | 8192          |
| query_cache_limit            | 1048576       |
| query_cache_min_res_unit     | 4096          |
| query_cache_size             | 0             |
| query_cache_type             | OFF           |
| query_cache_wlock_invalidate | OFF           |
| query_prealloc_size          | 8192          |
| slow_query_log               | ON            |
| slow_query_log_file          | *-slow.log |
+------------------------------+---------------+
13 rows in set (0.01 sec)

slow_query_log设置成on，slow_query_log_file是慢日志存放目录，long_query_time是查询超过该时间就认为是慢查询，单位是秒(s)，根据自己需要调整
SHOW STATUS LIKE ‘Slow_queries‘命令可以查看慢查询日志中记录了几条SQL

explain关键字分析SQL执行计划
```
explain select * from table ...
```
根据explain分析结果进行针对性优化

对于SQL语句的优化，有一些比较通用的规则，现在整理如下：

索引并不是越多越好，一张表中建议不超过5个。因为索引本身也占资源，再者数据的更新需要维护索引
单个组合索引字段数不允许超过5个
索引尽量建在where后面经常使用的字段上，参考索引作用的第一条
索引必须建立在离散程度大的列上，比如身份证号、员工工号等，而不是性别、职位等字段，因为性别、职位等字段重复值太多，筛选度低
禁止在select语句后面使用*，而是写出具体要返回的列

当使用索引列进行查询的时候尽量不要使用表达式
在验证这条规则前，先添加一个索引

-- age 列创建索引
alter table staffs add index idx_age(age)

仔细观察这两条SQL语句的执行计划，结果一目了然

mysql> explain select * from staffs where age = 18;
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------+
| id | select_type | table  | type | possible_keys | key     | key_len | ref   | rows | Extra |
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------+
|  1 | SIMPLE      | staffs | ref  | idx_age       | idx_age | 4       | const |    1 | NULL  |
+----+-------------+--------+------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)

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

尽量使用主键查询，而不是其他索引，因为主键查询不会触发回表查询

强制类型转换可能会全表扫描（包括where条件和连接查询时的连接条件）

mysql> explain select * from staffs where name = ‘1234‘;
+----+-------------+--------+------+---------------+---------+---------+-------+------+-----------------------+
| id | select_type | table  | type | possible_keys | key     | key_len | ref   | rows | Extra                 |
+----+-------------+--------+------+---------------+---------+---------+-------+------+-----------------------+
|  1 | SIMPLE      | staffs | ref  | idx_nap       | idx_nap | 74      | const |    1 | Using index condition |
+----+-------------+--------+------+---------------+---------+---------+-------+------+-----------------------+
1 row in set (0.00 sec)

mysql> explain select * from staffs where name = 1234;
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra       |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
|  1 | SIMPLE      | staffs | ALL  | idx_nap       | NULL | NULL    | NULL |    1 | Using where |
+----+-------------+--------+------+---------------+------+---------+------+------+-------------+
1 row in set (0.00 sec)

利用索引完成排序(order by)操作
创建索引的时候，索引本身就是按照升序排列的，如果排序的规则能利用索引来排序，就不需要用文件来排序(extra不会出现using filesort)。只有当索引的列顺序和order by子句的顺序完全一致，并且所有列的排序方式都一样时，MySQL才能够使用索引来对结果进行排序，如果查询需要关联多张表，则只有当order by子句引用的字段全部为第一张表时，才能使用索引做排序。order by子句和查询子句的限制是一样的，需要满足索引的最左前缀的要求。否则，MySQL都需要执行额外的排序操作，而无法利用索引排序。仔细观察以下几条SQL语句的执行计划就明白了，主要关注type和Extra

mysql> explain select rental_id, staff_id from rental where rental_date = ‘2005-05-25‘ order by inventory_id asc, customer_id asc;
+----+-------------+--------+------+---------------+-------------+---------+-------+------+-------------+
| id | select_type | table  | type | possible_keys | key         | key_len | ref   | rows | Extra       |
+----+-------------+--------+------+---------------+-------------+---------+-------+------+-------------+
|  1 | SIMPLE      | rental | ref  | rental_date   | rental_date | 5       | const |    1 | Using where |
+----+-------------+--------+------+---------------+-------------+---------+-------+------+-------------+
1 row in set (0.00 sec)

mysql> explain select rental_id, staff_id from rental where rental_date = ‘2005-05-25‘ order by inventory_id asc, customer_id desc;
+----+-------------+--------+------+---------------+-------------+---------+-------+------+-----------------------------+
| id | select_type | table  | type | possible_keys | key         | key_len | ref   | rows | Extra                       |
+----+-------------+--------+------+---------------+-------------+---------+-------+------+-----------------------------+
|  1 | SIMPLE      | rental | ref  | rental_date   | rental_date | 5       | const |    1 | Using where; Using filesort |
+----+-------------+--------+------+---------------+-------------+---------+-------+------+-----------------------------+
1 row in set (0.00 sec)

mysql> explain select rental_id, staff_id from rental where rental_date > ‘2005-05-25‘ order by inventory_id asc, customer_id desc;
+----+-------------+--------+------+---------------+------+---------+------+-------+-----------------------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows  | Extra                       |
+----+-------------+--------+------+---------------+------+---------+------+-------+-----------------------------+
|  1 | SIMPLE      | rental | ALL  | rental_date   | NULL | NULL    | NULL | 16008 | Using where; Using filesort |
+----+-------------+--------+------+---------------+------+---------+------+-------+-----------------------------+
1 row in set (0.00 sec)

mysql> explain select rental_id, staff_id from rental order by inventory_id asc, customer_id desc;
+----+-------------+--------+------+---------------+------+---------+------+-------+----------------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows  | Extra          |
+----+-------------+--------+------+---------------+------+---------+------+-------+----------------+
|  1 | SIMPLE      | rental | ALL  | NULL          | NULL | NULL    | NULL | 16008 | Using filesort |
+----+-------------+--------+------+---------------+------+---------+------+-------+----------------+
1 row in set (0.00 sec)

第1条和第2条对比，说明order by的排序方式要和索引保持一致，或者order by后面的两个字段都按照desc排序也是可以的；第1条和第3条对比，说明范围查找会使组合索引中，范围查找条件之后的列索引失效，也就是>筛选符号之后的列不能用到索引；第1条和第4条对比，说明order by子句和where一样也需要满足最左前缀。

表中尽量不要有为null的数据，可以用默认值代替

union all, in, or都能够使用索引，但是推荐使用in，仔细观察以下几条SQL语句的执行计划

mysql> explain select * from sicimike where name = ‘1‘ or name = ‘2‘;
+----+-------------+----------+-------+---------------+--------+---------+------+------+-----------------------+
| id | select_type | table    | type  | possible_keys | key    | key_len | ref  | rows | Extra                 |
+----+-------------+----------+-------+---------------+--------+---------+------+------+-----------------------+
|  1 | SIMPLE      | sicimike | range | idx_na        | idx_na | 74      | NULL |    2 | Using index condition |
+----+-------------+----------+-------+---------------+--------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

mysql> explain select * from sicimike where name in (‘1‘, ‘2‘);
+----+-------------+----------+-------+---------------+--------+---------+------+------+-----------------------+
| id | select_type | table    | type  | possible_keys | key    | key_len | ref  | rows | Extra                 |
+----+-------------+----------+-------+---------------+--------+---------+------+------+-----------------------+
|  1 | SIMPLE      | sicimike | range | idx_na        | idx_na | 74      | NULL |    2 | Using index condition |
+----+-------------+----------+-------+---------------+--------+---------+------+------+-----------------------+
1 row in set (0.00 sec)

mysql> explain select * from sicimike where name = ‘1‘ union all select * from sicimike where name = ‘2‘;
+----+--------------+------------+------+---------------+--------+---------+-------+------+-----------------------+
| id | select_type  | table      | type | possible_keys | key    | key_len | ref   | rows | Extra                 |
+----+--------------+------------+------+---------------+--------+---------+-------+------+-----------------------+
|  1 | PRIMARY      | sicimike   | ref  | idx_na        | idx_na | 74      | const |    1 | Using index condition |
|  2 | UNION        | sicimike   | ref  | idx_na        | idx_na | 74      | const |    1 | Using index condition |
| NULL | UNION RESULT | <union1,2> | ALL  | NULL          | NULL   | NULL    | NULL  | NULL | Using temporary       |
+----+--------------+------------+------+---------------+--------+---------+-------+------+-----------------------+
3 rows in set (0.00 sec)

可以看到union all, in, or都能够使用索引，如果数据量小的话就不一定了。

范围列可以用到索引，但是范围列后面的列无法用到索引，包括：<、<=、>、>=、between
模糊查询不要以通配符开头
能使用union all就不使用union，因为union all不需要执行类似distinct操作
使用group by语句时，尽量先过滤再分组。即把条件写在where子句里而不是having子句

优化limit，该关键字常用于分页查询，如果数据量较大时，分页查询可能会变得很慢

mysql> select * from sicimike order by id limit 8000000, 5;
+---------+------------+-----+---------------------+
| id      | name       | age | add_time            |
+---------+------------+-----+---------------------+
| 8000001 | c6eb3f23ed |  55 | 2020-02-14 13:34:12 |
| 8000002 | c64b714367 |  84 | 2020-02-14 13:34:12 |
| 8000003 | fe038583aa |  10 | 2020-02-14 13:34:12 |
| 8000004 | e2b21c0159 |  40 | 2020-02-14 13:34:12 |
| 8000005 | 7c1e70beb8 |  65 | 2020-02-14 13:34:12 |
+---------+------------+-----+---------------------+
5 rows in set (4.18 sec)

查询第800万条数据开始的连续5条数据，查询花费了很长时间，可以改写成如下SQL语句

mysql> select a.* from sicimike a inner join (select id from sicimike order by id limit 8000000, 5) b on a.id = b.id;
+---------+------------+-----+---------------------+
| id      | name       | age | add_time            |
+---------+------------+-----+---------------------+
| 8000001 | c6eb3f23ed |  55 | 2020-02-14 13:34:12 |
| 8000002 | c64b714367 |  84 | 2020-02-14 13:34:12 |
| 8000003 | fe038583aa |  10 | 2020-02-14 13:34:12 |
| 8000004 | e2b21c0159 |  40 | 2020-02-14 13:34:12 |
| 8000005 | 7c1e70beb8 |  65 | 2020-02-14 13:34:12 |
+---------+------------+-----+---------------------+
5 rows in set (3.15 sec)

可以看到，在不添加任何过滤条件的情况下，耗时相对少一些。在有过滤条件的情况下效果可能会更显著，对比下面这两条SQL就一目了然了

mysql> select a.* from sicimike a inner join (select id from sicimike where name like ‘c6%‘ order by id limit 30000, 5) b on a.id = b.id;
+---------+------------+-----+---------------------+
| id      | name       | age | add_time            |
+---------+------------+-----+---------------------+
| 7466563 | c6db537243 |  59 | 2020-02-14 13:34:01 |
| 7466920 | c62dec7921 |  79 | 2020-02-14 13:34:01 |
| 7467162 | c610b89b31 |  71 | 2020-02-14 13:34:01 |
| 7467590 | c67bbd4bfd |  10 | 2020-02-14 13:34:01 |
| 7467825 | c6db24865b |  51 | 2020-02-14 13:34:01 |
+---------+------------+-----+---------------------+
5 rows in set (0.05 sec)

mysql> select * from sicimike where name like ‘c6%‘ order by id limit 30000, 5;
+---------+------------+-----+---------------------+
| id      | name       | age | add_time            |
+---------+------------+-----+---------------------+
| 7466563 | c6db537243 |  59 | 2020-02-14 13:34:01 |
| 7466920 | c62dec7921 |  79 | 2020-02-14 13:34:01 |
| 7467162 | c610b89b31 |  71 | 2020-02-14 13:34:01 |
| 7467590 | c67bbd4bfd |  10 | 2020-02-14 13:34:01 |
| 7467825 | c6db24865b |  51 | 2020-02-14 13:34:01 |
+---------+------------+-----+---------------------+
5 rows in set (2.26 sec)

为什么改写之后会比直接写limit快。主要是因为直接写limit m, n，访问数据的指针并不会直接找到第m条数据。而是依然从第一条数据开始依次往下找，所以m越大，耗时越久。而改写之后的写法，先利用索引覆盖，找到对应记录的ID，再根据ID来进行关联，所以会比直接写limit快。
既然利用索引覆盖找到了需要数据的id，或许我们可以试试in关键字会不会更快
```
mysql> select a.* from sicimike a where a.id in (select id from sicimike where name like ‘c6%‘ order by id limit 30000, 5);
ERROR 1235 (42000): This version of MySQL doesn‘t yet support ‘LIMIT & IN/ALL/ANY/SOME subquery‘
```
MySQL5.6不支持这种语法。

参考

https://dev.mysql.com/doc/refman/5.6/en/mysql-indexes.html

原文

https://blog.csdn.net/Baisitao_/article/details/104267080

MySQL优化：索引

原文：https://www.cnblogs.com/-mrl/p/13275137.html

踩

(0)

评论一句话评论（0）

分享档案

更多>

2021年09月23日 (328)
2021年09月24日 (313)
2021年09月17日 (191)
2021年09月15日 (369)
2021年09月16日 (411)
2021年09月13日 (439)
2021年09月11日 (398)
2021年09月12日 (393)
2021年09月10日 (160)
2021年09月08日 (222)