文章目录

  • 索引失效及规则建议
  • 全值匹配我最爱
  • 成功示例—
  • 失效示例
  • 最佳左前缀法则
  • 全部失效示例
  • 部分失效示例
  • 主键插入顺序
  • 计算、函数、类型转换(自动或手动)导致索引失效
  • 索引生效示例
  • 索引**失效**示例
  • 类型转换导致索引失效
  • 索引生效示例
  • 索引失效示例
  • 范围条件右边的列索引失效
  • 不等于(!= 或者<>)索引失效
  • is null可以使用索引，is not null无法使用索引
  • like以通配符%开头索引失效
  • 索引生效示例
  • 索引失效示例
  • OR 前后存在非索引的列，索引失效
  • 常用优化建议
  • 子查询优化
  • 排序优化
  • 排序优化实战
  • oder by 时不limit，索引失效
  • order by 顺序错误，索引失效
  • order by时规则不一致, 索引失效 （顺序错，不索引；方向反，不索引）
  • 无过滤，不索引
  • filesort算法：双路排序和单路排序
  • GROUP BY优化
  • 优化分页查询
  • 数据准备
  • 建表
  • 设置参数
  • 创建函数
  • 创建存储过程
  • 调用存储过程
  • 删除某表上的索引 创建存储过程


索引失效及规则建议

MySQL中提高性能的一个最有效的方式是对数据表设计合理的索引。索引提供了高效访问数据的方法，并且加快 查询的速度，因此索引对查询的速度有着至关重要的影响。

  • 使用索引可以快速地定位表中的某条记录，从而提高数据库查询的速度，提高数据库的性能。
  • 如果查询时没有使用索引，查询语句就会扫描表中的所有记录。在数据量大的情况下，这样查询的速度会很慢。

大多数情况下都(默认)采用B+树来构建索引。只是空间列类型的索引使用R-树，并且MEMORY表还支持hash 索引。

其实，用不用索引，最终都是优化器说了算。优化器是基于什么的优化器？基于cost开销(CostBaseOptimizer),它不是基于规则(Rule-BasedOptimzer)，也不是基于语义。怎么样开销小就怎么 来。另外，SQL语句是使用索引，跟数据库版本、数据量、数据选择度都有关系。

全值匹配我最爱

常用sql如下：

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 and classId=4;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 and classId=4 AND name = 'abed';

建立索引

CREATE INDEX idx_age ON student(age);
CREATE INDEX idx_age_classid ON student(age, classId);
CREATE INDEX idx_age_classid_name ON student(age,classId,name);

成功示例—

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30;
+----+-------------+---------+------------+------+----------------------------------------------+---------+---------+-------+-------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys                                | key     | key_len | ref   | rows  | filtered | Extra |
+----+-------------+---------+------------+------+----------------------------------------------+---------+---------+-------+-------+----------+-------+
|  1 | SIMPLE      | student | NULL       | ref  | idx_age,idx_age_classid,idx_age_classid_name | idx_age | 5       | const | 10072 |   100.00 | NULL  |
+----+-------------+---------+------------+------+----------------------------------------------+---------+---------+-------+-------+----------+-------+
1 row in set, 2 warnings (0.00 sec)

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 and classId=4;
+----+-------------+---------+------------+------+----------------------------------------------+-----------------+---------+-------------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys                                | key             | key_len | ref         | rows | filtered | Extra |
+----+-------------+---------+------------+------+----------------------------------------------+-----------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | student | NULL       | ref  | idx_age,idx_age_classid,idx_age_classid_name | idx_age_classid | 10      | const,const |    9 |   100.00 | NULL  |
+----+-------------+---------+------------+------+----------------------------------------------+-----------------+---------+-------------+------+----------+-------+
1 row in set, 2 warnings (0.00 sec)

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 and classId=4 AND name = 'abed';
+----+-------------+---------+------------+------+----------------------------------------------+----------------------+---------+-------------------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys                                | key                  | key_len | ref               | rows | filtered | Extra |
+----+-------------+---------+------------+------+----------------------------------------------+----------------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | student | NULL       | ref  | idx_age,idx_age_classid,idx_age_classid_name | idx_age_classid_name | 93      | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+----------------------------------------------+----------------------+---------+-------------------+------+----------+-------+
1 row in set, 2 warnings (0.00 sec)

失效示例

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name='abcd';
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 499086 |    10.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 2 warnings (0.00 sec)
# 失效原因：无name索引

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name='abcd' and classId=4;
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 499086 |     1.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 2 warnings (0.00 sec)
# 失效原因：无name和classId索引
# 删除对应表的所有索引
CALL proc_drop_index("idx","student")

最佳左前缀法则

在MySQL建立联合索引时会遵守最佳左前缀匹配原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。

MySQL可以为多个字段创建索引，一个索引可以包括16个字段。对于多列索引，过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。如果查询条件中没有使用这些字段的第1个字段时，联合索引不会被使用。

全部失效示例

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name='abcd';
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 499086 |    10.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 2 warnings (0.00 sec)
# 失效原因：无单独的name索引，没有匹配上联合索引的age

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name='abcd' and classId=4;
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 499086 |     1.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 2 warnings (0.00 sec)
# 失效原因：无name和classId索引，没有匹配上联合索引的age

部分失效示例

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.age=30 AND student.name = 'abcd';
+----+-------------+---------+------------+------+----------------------------------------------+---------+---------+-------+-------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys                                | key     | key_len | ref   | rows  | filtered | Extra       |
+----+-------------+---------+------------+------+----------------------------------------------+---------+---------+-------+-------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ref  | idx_age,idx_age_classid,idx_age_classid_name | idx_age | 5       | const | 10072 |    10.00 | Using where |
+----+-------------+---------+------------+------+----------------------------------------------+---------+---------+-------+-------+----------+-------------+
1 row in set, 2 warnings (0.00 sec)
# 部分失效原因：匹配上age了，但是随后的classId没有匹配上，所以生效的只有age

主键插入顺序

对于一个 使用InnoDB存储引擎的表来说，在没有显式的创建索引时，表中的数据实际上都是存储在聚簇索引的叶子节点的。而记录又是存储在数据页中的，数据页和记录又是按照记录主键值从小到大的顺序进行排序， 所以如果插入的记录的主键值是依次增大的话，那每插满一个数据页就换到下一个数据页继续插，而如果插入的主键值忽大忽小的话，就比较麻烦了，假设某个数据页存储的记录已经满了，它存储的主键值在1-100 之间：

[外链

monogo索引更新 索引更新数据时效率低_sql

如果此时再插入一条主键值为 9的记录，那它插入的位置就如下图：

monogo索引更新 索引更新数据时效率低_性能优化_02


可这个数据页已经满了，我们需要把当前 页面分裂 成两个页面，把本页中的一些记录移动到新创建的这个页中。页面分裂和记录移位意味着什么？意味着： 性能损耗 ！所以如果想尽量,避免这样无谓的性能损耗，最好让插入的记录的 主键值依次递增 ，这样就不会发生这样的性能损耗了。 所以我们建议：让主键具有 AUTO_INCREMENT，让存储引擎自己为表生成主键，而不是我们手动插入 ，比如：person_info 表：

CREATE TABLE person_info (
  id INT UNSIGNED NOT NULL AUTO_INCREMENT,
  NAME VARCHAR ( 100 ) NOT NULL,
  birthday DATE NOT NULL,
  phone_number CHAR ( 11 ) NOT NULL,
  country VARCHAR ( 100 ) NOT NULL,
  PRIMARY KEY ( id ),
KEY idx_name_birthday_phone_number ( NAME ( 10 ), birthday, phone_number ) 
);

自定义的主键列 id 拥有AUTO_INCREMENT 属性，在插入记录时存储引擎会自动填入自增的主键值。这样的主键占用空间小，顺序写入，减少页分裂。

计算、函数、类型转换(自动或手动)导致索引失效

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc';

创建索引

CREATE INDEX idx_name ON student(NAME);

索引生效示例

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';
+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| id | select_type | table   | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | student | NULL       | range | idx_name      | idx_name | 83      | NULL |   31 |   100.00 | Using index condition |
+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
1 row in set, 2 warnings (0.00 sec)
mysql> SELECT SQL_NO_CACHE * FROM student WHERE student.name LIKE 'abc%';
+--------+-----------+--------+------+---------+
| id     | studentno | name   | age  | classId |
+--------+-----------+--------+------+---------+
| 215082 |    315082 | aBCijw |   30 |     623 |
| 157164 |    257164 | aBCilE |   13 |     488 |
|  66540 |    166540 | aBCjnO |    5 |     146 |
...
| 329297 |    429297 | AbCLYH |   17 |     686 |
| 126627 |    226627 | AbCLYI |   21 |      35 |
+--------+-----------+--------+------+---------+
31 rows in set, 1 warning (0.00 sec)

type 为 range，表示有使用到索引列，查询时间仅为0.029s

索引失效示例

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc';
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 499086 |   100.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 2 warnings (0.00 sec)
mysql> SELECT SQL_NO_CACHE * FROM student WHERE LEFT(student.name,3) = 'abc';
+--------+-----------+--------+------+---------+
| id     | studentno | name   | age  | classId |
+--------+-----------+--------+------+---------+
| 215082 |    315082 | aBCijw |   30 |     623 |
| 157164 |    257164 | aBCilE |   13 |     488 |
|  66540 |    166540 | aBCjnO |    5 |     146 |
...
| 329297 |    429297 | AbCLYH |   17 |     686 |
| 126627 |    226627 | AbCLYI |   21 |      35 |
+--------+-----------+--------+------+---------+
31 rows in set, 1 warning (0.12 sec)

type为ALL，表示没有使用到索引，查询时间为0.12秒，查询效率较之前低很多

类型转换导致索引失效

EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name=123;
EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name='123';

创建索引

CREATE INDEX idx_name ON student(NAME);

索引生效示例

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name='123';
+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------+
| id | select_type | table   | partitions | type | possible_keys | key      | key_len | ref   | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | student | NULL       | ref  | idx_name      | idx_name | 83      | const |    1 |   100.00 | NULL  |
+----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------+
1 row in set, 2 warnings (0.00 sec)

索引失效示例

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE name=123;
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ALL  | idx_name      | NULL | NULL    | NULL | 499086 |    10.00 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 5 warnings (0.00 sec)

name=123发生类型转换，索引失效。(隐式的类型转换)

范围条件右边的列索引失效

ALTER TABLE student DROP INDEX idx_name;
ALTER TABLE student DROP INDEX idx_age;
ALTER TABLE student DROP INDEX idx_age_classid;
# 现在只剩下idx_age_classid_name索引

mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND classId>20 AND name = 'abc';
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+-------+----------+-----------------------+
| id | select_type | table   | partitions | type  | possible_keys        | key                  | key_len | ref  | rows  | filtered | Extra                 |
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+-------+----------+-----------------------+
|  1 | SIMPLE      | student | NULL       | range | idx_age_classid_name | idx_age_classid_name | 10      | NULL | 18624 |    10.00 | Using index condition |
+----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+-------+----------+-----------------------+
1 row in set, 2 warnings (0.00 sec)

以上sql只使用了联合索引的age、classId，没有使用到name。因为classId>20为范围条件。右侧的name = ‘abc’的索引就失效了

那么索引 idx_age_classid_name 这个索引还能正常使用吗？

  • 不能，范围右边的列不能使用。比如 < <= > >= 和between等。
  • 如果这种sql比较多，应该将索引顺序进行置换
    顺序由age-classid-name换变为age-name-classid
create index idx_age_name_classid on student(age,name,classid);
将范围查询条件放置语句最后：
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age=30 AND name = 'abc' AND classId>20;
+----+-------------+---------+------------+-------+-------------------------------------------+----------------------+---------+------+------+----------+-----------------------+
| id | select_type | table   | partitions | type  | possible_keys                             | key                  | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+---------+------------+-------+-------------------------------------------+----------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | student | NULL       | range | idx_age_classid_name,idx_age_name_classid | idx_age_name_classid | 93      | NULL |    1 |   100.00 | Using index condition |
+----+-------------+---------+------------+-------+-------------------------------------------+----------------------+---------+------+------+----------+-----------------------+
1 row in set, 2 warnings (0.00 sec)

直接交换sql语句的位置是没有用的，需要改变联合索引的位置

不等于(!= 或者<>)索引失效

版本变化
5.7之前版本不等式 <> 或 != 会导致索引失效
8之后的版本索引不会失效

建立索引

create index idx_name on student(name);

以下查询均失效

# 使用<>
mysql> explain select SQL_NO_CACHE *from student where name <> 'abc';
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ALL  | ide_name      | NULL | NULL    | NULL | 499086 |    50.14 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 2 warnings (0.00 sec)

# 使用!=
mysql> explain select SQL_NO_CACHE *from student where name != 'abc';
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ALL  | ide_name      | NULL | NULL    | NULL | 499086 |    50.14 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 2 warnings (0.00 sec)

当sql语句中有!=或者<>会出现索引失效的问题，尝试改写为等于，或采用覆盖索引

is null可以使用索引，is not null无法使用索引

  • IS NULL 可以使用索引
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NULL;
+----+-------------+---------+------------+------+-------------------------------------------+----------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table   | partitions | type | possible_keys                             | key                  | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+---------+------------+------+-------------------------------------------+----------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | student | NULL       | ref  | idx_age_classid_name,idx_age_name_classid | idx_age_classid_name | 5       | const |    1 |   100.00 | Using index condition |
+----+-------------+---------+------------+------+-------------------------------------------+----------------------+---------+-------+------+----------+-----------------------+
1 row in set, 2 warnings (0.00 sec)
  • IS NOT NULL 无法触发索引
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age IS NOT NULL;
+----+-------------+---------+------------+------+-------------------------------------------+------+---------+------+--------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys                             | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------+------------+------+-------------------------------------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ALL  | idx_age_classid_name,idx_age_name_classid | NULL | NULL    | NULL | 499086 |    50.00 | Using where |
+----+-------------+---------+------------+------+-------------------------------------------+------+---------+------+--------+----------+-------------+
1 row in set, 2 warnings (0.00 sec)

like以通配符%开头索引失效

索引生效示例

mysql> explain select SQL_NO_CACHE * FROM student where name like 'ab%';
+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
| id | select_type | table   | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | student | NULL       | range | ide_name      | ide_name | 83      | NULL |  754 |   100.00 | Using index condition |
+----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+
1 row in set, 2 warnings (0.00 sec)

索引失效示例

mysql> explain select SQL_NO_CACHE * FROM student where name like '%ab%';
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 499086 |    11.11 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+
1 row in set, 2 warnings (0.00 sec)

OR 前后存在非索引的列，索引失效

在 where 子句中，如果在 OR 前的条件列进行了索引，而在OR 后的条件列没有进行索引，那么索引失效，也就是，让OR的前后条件都具备索引，如果缺少一个就会出现索引失效

因为 OR 的含义就是两个只要满足一个即可，因此只有一个条件列进行了索引时没有意义的。只要有条件列没有索引，就会进行全表扫描，因此所有的条件列也会失效。

# 未使用到索引
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE age = 10 OR classid = 100;
+----+-------------+---------+------------+------+-------------------------------------------+------+---------+------+--------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys                             | key  | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+---------+------------+------+-------------------------------------------+------+---------+------+--------+----------+-------------+
|  1 | SIMPLE      | student | NULL       | ALL  | idx_age_classid_name,idx_age_name_classid | NULL | NULL    | NULL | 499086 |    11.76 | Using where |
+----+-------------+---------+------------+------+-------------------------------------------+------+---------+------+--------+----------+-------------+
1 row in set, 2 warnings (0.00 sec)

常用优化建议

子查询优化

子查询是 MySQL 的一项重要的功能，可以帮助我们通过一个 SQL 语句实现比较复杂的查询。但是，子查询的执行效率不高，因为

  • 执行子查询时，MySQL需要为内层查询语句的查询结果建立一个临时表 ，然后外层查询语句从临时表中查询记录。查询完毕后，再撤销这些临时表 。这样会消耗过多的CPU和IO资源，产生大量的慢查询。
  • 子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都 不会存在索引 ，所以查询性能会受到一定的影响。
  • 对于返回结果集比较大的子查询，其对查询性能的影响也就越大。

在MySQL中，可以使用连接（JOIN）查询来替代子查询。 连接查询 不需要建立临时表，其 速度比子查询要快 ，如果查询中使用索引的话，性能就会更好。

排序优化

问题：

在 WHERE 条件字段上加索引，但是为什么在 ORDER BY 字段上还要加索引呢？

回答：

在MySQL中，支持两种排序方式，分别是FileSort和Index排序。

  • Index排序中，索引可以保证数据的有序性，不需要再进行排序，效率更高。
  • FileSort排序则一般在内存中进行排序，占用CPU较多。如果待排结果较大，会产生临时文件I/O。到磁盘进 行排序的情况，效率较低。

排序优化实战

删除student表和class表中已创建的索引。

#方式1 ：
DROP INDEX idx_monitor ON class;
DROP INDEX idx_cid ON student;
DROP INDEX idx_age ON student;
DROP INDEX idx_name ON student;
DROP INDEX idx_age_name_classid ON student;

#方式2：
call proc_drop_index('idx','student' );

接下来，准备两条查询语句，目的是去掉using filesort

  • EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age, classid
  • EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age, classid LIMIT 10
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age, classid \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        ...
        Extra: Using filesort
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age, classid LIMIT 10 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 499086
     filtered: 100.00
        Extra: Using filesort

oder by 时不limit，索引失效

# 创建索引  
CREATE INDEX idx_age_classid_name ON student ( age, classid, NAME );

# 无limit,索引失效
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 499086
     filtered: 100.00
        Extra: Using filesort

# 增加limit，索引生效
mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student ORDER BY age,classid LIMIT 10 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_age_classid_name
      key_len: 93
          ref: NULL
         rows: 10
     filtered: 100.00
        Extra: NULL

order by 顺序错误，索引失效

#创建索引age,classid,studentno 
CREATE INDEX idx_age_classid_studentno ON student ( age, classid, studentno );

索引失效语句

# 顺序为age, classid, studentno。无age，索引失效
mysql> EXPLAIN SELECT * FROM student ORDER BY classid LIMIT 10 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 499086
     filtered: 100.00
        Extra: Using filesort
# 顺序为age, classid, studentno。无age，索引失效
mysql> EXPLAIN SELECT * FROM student ORDER BY classid,NAME LIMIT 10 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 499086
     filtered: 100.00
        Extra: Using filesort

索引成功语句

# 顺序为age, classid, studentno。顺序正确
mysql> EXPLAIN  SELECT * FROM student ORDER BY age,classid,studentno LIMIT 10 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_age_classid_studentno
      key_len: 14
          ref: NULL
         rows: 10
     filtered: 100.00
        Extra: NULL
# 顺序为age, classid, studentno。顺序正确  
mysql> EXPLAIN SELECT * FROM student ORDER BY age,classid LIMIT 10 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_age_classid_name
      key_len: 93
          ref: NULL
         rows: 10
     filtered: 100.00
        Extra: NULL
# 顺序为age, classid, studentno。顺序正确
mysql> EXPLAIN SELECT * FROM student ORDER BY age LIMIT 10 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_age_classid_name
      key_len: 93
          ref: NULL
         rows: 10
     filtered: 100.00
        Extra: NULL

order by时规则不一致, 索引失效 （顺序错，不索引；方向反，不索引）

顺序对、age和classid 排序相反，索引失效

mysql> EXPLAIN SELECT * FROM student ORDER BY age DESC, classid ASC LIMIT 10 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 499086
     filtered: 100.00
        Extra: Using filesort

顺序错、classid和NAME排序相同，索引失效

mysql> EXPLAIN SELECT * FROM student ORDER BY classid DESC, NAME DESC LIMIT 10 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 499086
     filtered: 100.00
        Extra: Using filesort

顺序对、age 和classid顺序相反，索引失效

mysql> EXPLAIN SELECT * FROM student ORDER BY age ASC,classid DESC LIMIT 10 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 499086
     filtered: 100.00
        Extra: Using filesort

顺序对、age 和classid顺序相同，索引生效

mysql> EXPLAIN SELECT * FROM student ORDER BY age DESC, classid DESC LIMIT 10 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_age_classid_name
      key_len: 93
          ref: NULL
         rows: 10
     filtered: 100.00
        Extra: Backward index scan

无过滤，不索引

age顺序正确，索引生效

mysql> EXPLAIN SELECT * FROM student WHERE age=45 ORDER BY classid \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
   partitions: NULL
         type: ref
possible_keys: idx_age_classid_name
          key: idx_age_classid_name
      key_len: 5
          ref: const
         rows: 19160
     filtered: 100.00
        Extra: NULL

age顺序正确，索引生效

mysql> EXPLAIN SELECT * FROM student WHERE age=45 ORDER BY classid,NAME \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
   partitions: NULL
         type: ref
possible_keys: idx_age_classid_name
          key: idx_age_classid_name
      key_len: 5
          ref: const
         rows: 19160
     filtered: 100.00
        Extra: NULL

classid顺序错误，且无limit，索引失效

mysql> EXPLAIN SELECT * FROM student WHERE classid=45 ORDER BY age \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 499086
     filtered: 10.00
        Extra: Using where; Using filesort

classid顺序错误，有limit，索引生效（无过滤，不索引）

mysql> EXPLAIN SELECT * FROM student WHERE classid=45 ORDER BY age LIMIT 10 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_age_classid_name
      key_len: 93
          ref: NULL
         rows: 10
     filtered: 10.00
        Extra: Using where

filesort算法：双路排序和单路排序

双路排序 （慢）

  • MySQL 4.1之前是使用双路排序 ，字面意思就是两次扫描磁盘，最终得到数据， 读取行指针和order by列 ，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出
  • 从磁盘取排序字段，在buffer进行排序，再从 磁盘取其他字段 。

取一批数据，要对磁盘进行两次扫描，众所周知，IO是很耗时的，所以在mysql4.1之后，出现了第二种改进的算法，就是单路排序。

单路排序 （快）

从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO，但是它会使用更多的空 间，因为它把每一行都保存在内存中了。

结论及引申出的问题

  • 由于单路是后出的，总体而言好过双路
  • 但是用单路有问题

GROUP BY优化

  • group by 使用索引的原则几乎跟order by一致 ，group by 即使没有过滤条件用到索引，也可以直接使用索引。
  • group by 先排序再分组，遵照索引建的最佳左前缀法则
  • 当无法使用索引列，增大 max_length_for_sort_data 和 sort_buffer_size 参数的设置
  • where效率高于having，能写在where限定的条件就不要写在having中
  • 减少使用order by，和业务沟通能不排序就不排序，或将排序放到程序端去做。Order by、group by、distinct这些语句较为耗费CPU，数据库的CPU资源是极其宝贵的。
  • 包含了order by、group by、distinct这些查询的语句，where条件过滤出来的结果集请保持在1000行以内，否则SQL会很慢。

优化分页查询

一般分页查询时，通过创建覆盖索引能够比较好地提高性能。一个常见又非常头疼的问题就是类似limit 2000000,10这种条件查询排序的代价非常大

mysql> EXPLAIN SELECT * FROM student LIMIT 2000000,10 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 499086
     filtered: 100.00
        Extra: NULL

优化思路一：

在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容

mysql> EXPLAIN SELECT * FROM student t,( SELECT id FROM student ORDER BY id LIMIT 2000000, 10 ) a WHERE t.id = a.id;
+----+-------------+------------+------------+--------+---------------+---------+---------+------+--------+----------+-------------+
| id | select_type | table      | partitions | type   | possible_keys | key     | key_len | ref  | rows   | filtered | Extra       |
+----+-------------+------------+------------+--------+---------------+---------+---------+------+--------+----------+-------------+
|  1 | PRIMARY     | <derived2> | NULL       | ALL    | NULL          | NULL    | NULL    | NULL | 499086 |   100.00 | NULL        |
|  1 | PRIMARY     | t          | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | a.id |      1 |   100.00 | NULL        |
|  2 | DERIVED     | student    | NULL       | index  | NULL          | PRIMARY | 4       | NULL | 499086 |   100.00 | Using index |
+----+-------------+------------+------------+--------+---------------+---------+---------+------+--------+----------+-------------+

优化思路二:

该方案适用于主键自增的表，可以把Limit 查询转换成某个位置的查询 。

mysql> EXPLAIN SELECT * FROM student WHERE id > 2000000 LIMIT 10 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
   partitions: NULL
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: Using where

数据准备

建表

# 班级表
CREATE TABLE `class` (
  `id` INT ( 11 ) NOT NULL AUTO_INCREMENT,
  `className` VARCHAR ( 20 ) DEFAULT NULL,
  `address` VARCHAR ( 30 ) DEFAULT NULL,
  `monitor` INT NULL ,
PRIMARY KEY ( `id` ) 
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8mb4;

# 学生表
CREATE TABLE `student` (
  `id` INT ( 11 ) NOT NULL AUTO_INCREMENT,
  `studentno` INT NOT NULL,
  `name` VARCHAR ( 20 ) DEFAULT NULL,
  `age` INT ( 3 ) DEFAULT NULL,
  `classId` INT ( 11 ) DEFAULT NULL,
PRIMARY KEY ( `id` ) 
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8mb4;

设置参数

命令开启：允许创建函数设置

set global log_bin_trust_function_creators=1;
# 不加global只是当前窗口有效。

创建函数

保证每条数据都不同。

#随机产生字符串
DELIMITER //
CREATE FUNCTION rand_string ( n INT ) RETURNS VARCHAR ( 255 ) BEGIN
  DECLARE
    chars_str VARCHAR ( 100 ) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIJKLMNOPQRSTUVWXYZ';
  DECLARE
    return_str VARCHAR ( 255 ) DEFAULT '';
  DECLARE
    i INT DEFAULT 0;
  WHILE
      i < n DO    
      SET return_str = CONCAT(
        return_str,
      SUBSTRING( chars_str, FLOOR( 1+RAND ()* 52 ), 1 ));  
    SET i = i + 1;
  END WHILE;
  RETURN return_str;
END // 
DELIMITER;

#用于随机产生多少到多少的编号
DELIMITER //
CREATE FUNCTION rand_num ( from_num INT, to_num INT ) RETURNS INT ( 11 ) BEGIN
  DECLARE
    i INT DEFAULT 0;
  
  SET i = FLOOR(
    from_num + RAND()*(
      to_num - from_num + 1 
    ));
  RETURN i;
  
END // 
DELIMITER;

# 假如要删除
# drop function rand_string;
# 假如要删除
# drop function rand_num;

创建存储过程

# 创建存储过程，往student表添加随机数据
DELIMITER //
CREATE PROCEDURE insert_student ( START INT, max_num INT ) BEGIN
  DECLARE
    i INT DEFAULT 0;
  SET autocommit = 0;#设置手动提交事务
  REPEAT#循环
    SET i = i + 1;#赋值
    INSERT INTO student ( studentno, NAME, age, classId )
    VALUES
      ((START + i), rand_string ( 6 ), rand_num ( 1, 50 ), rand_num ( 1, 1000 ));
    UNTIL i = max_num 
  END REPEAT;
  COMMIT;#提交事务
END // 
DELIMITER;

# 创建存储过程，往post表添加随机数据
DELIMITER //
CREATE PROCEDURE `insert_class` ( max_num INT ) BEGIN
  DECLARE
    i INT DEFAULT 0;
  SET autocommit = 0;
  REPEAT    
      SET i = i + 1;
    INSERT INTO class ( classname, address, monitor )
    VALUES
      (rand_string ( 8 ), rand_string ( 10 ), rand_num ( 1, 100000 ));
    UNTIL i = max_num 
  END REPEAT;
  COMMIT;
END // 
DELIMITER;

#假如要删除
#drop PROCEDURE insert_class;

调用存储过程

# 执行存储过程，往class表添加1万条数据 
CALL insert_class(10000);
# 执行存储过程，往student表添加50万条数据 
CALL insert_student(100000,500000);

删除某表上的索引 创建存储过程

DELIMITER //
CREATE PROCEDURE `proc_drop_index` (
  dbname VARCHAR ( 200 ),
  tablename VARCHAR ( 200 )) BEGIN
  DECLARE
    done INT DEFAULT 0;
  DECLARE
    ct INT DEFAULT 0;
  DECLARE
    _index VARCHAR ( 200 ) DEFAULT '';
  DECLARE
    _cur CURSOR FOR SELECT
    index_name 
  FROM
    information_schema.STATISTICS 
  WHERE
    table_schema = dbname 
    AND table_name = tablename 
    AND seq_in_index = 1 
    AND index_name <> 'PRIMARY';# 每个游标必须使用不同的declare continue handler for not found set done=1来控制游标的结束
  DECLARE
    CONTINUE HANDLER FOR NOT FOUND 
    SET done = 2;# 若没有数据返回,程序继续,并将变量done设为2
  OPEN _cur;
  FETCH _cur INTO _index;
  WHILE
      _index <> '' DO  
      SET @str = CONCAT( "drop index ", _index, " on ", tablename );
    PREPARE sql_str 
    FROM
      @str;
    EXECUTE sql_str;
    DEALLOCATE PREPARE sql_str;
    SET _index = '';
    FETCH _cur INTO _index;
  END WHILE;
  CLOSE _cur;
END // 
DELIMITER;

执行存储过程

CALL proc_drop_index("dbname","tablename");