MySQL索引优化与查询优化
2022/05/04
MySQL
> 视频教程：[尚硅谷_MySQL](http://www.atguigu.com/download_detail.shtml?v=349) # 索引失效的常见情况 `MySQL` 中提高性能的一个最有效的方式是对数据表设计合理的索引。索引提供了高效访问数据的方法，并且加快查询的速度，因此索引对查询的速度有着至关重要的影响。 - 使用索引可以快速地定位表中的某条记录，从而提高数据库查询的速度，提高数据库的性能。 - 如果查询时没有使用索引，查询语句就会扫描表中的所有记录。在数据量大的情况下，这样查询的速度会很慢。 `SQL` 语句是否使用索引，跟数据库版本、数据量、数据选择度都有关系。 下文中的数据库表结构如下 ``` CREATE TABLE `student` ( `id` int NOT NULL AUTO_INCREMENT, `stuno` int NOT NULL, `name` varchar(20) DEFAULT NULL, `age` int DEFAULT NULL, `classId` int DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; CREATE TABLE `class` ( `id` int NOT NULL AUTO_INCREMENT, `className` varchar(30) DEFAULT NULL, `address` varchar(40) DEFAULT NULL, `monitor` int DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4; ``` ## 低匹配度的索引不会使用到 当 `WHERE` 条件有多个时，创建联合索引的效率更高，且越匹配越好，而单一索引或匹配度低的联合索引则不会使用到 ``` mysql> CREATE INDEX idx_age ON student(age); Query OK, 0 rows affected (0.90 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> EXPLAIN SELECT * FROM student WHERE age=30 AND classId=4 AND `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 | 5 | const | 10135 | 1.00 | Using where | +----+-------------+---------+------------+------+---------------+---------+---------+-------+-------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> CREATE INDEX idx_age_classid ON student(age,classId); Query OK, 0 rows affected (1.97 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> EXPLAIN SELECT * FROM student WHERE age=30 AND classId=4 AND `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 | 10 | const,const | 12 | 10.00 | Using where | +----+-------------+---------+------------+------+-------------------------+-----------------+---------+-------------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> CREATE INDEX idx_age_classid_name ON student(age,classId,`name`); Query OK, 0 rows affected (1.30 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> EXPLAIN SELECT * FROM student WHERE age=30 AND classId=4 AND `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_classid_name | 73 | const,const,const | 1 | 100.00 | NULL | +----+-------------+---------+------------+------+----------------------------------------------+----------------------+---------+-------------------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) ``` ## 最佳左前缀法则 `MySQL` 可以为多个字段创建索引，一个索引可以包括 `16` 个字段。对于多列索引，过滤条件要使用索引必须按照索引建立时的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用。如果查询条件中没有使用这些字段中第 `1` 个字段时，联合索引不会被使用。 ``` mysql> CREATE INDEX idx_age_classid_name ON student(age,classId,`name`); Query OK, 0 rows affected (1.30 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE classid=4 AND 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_classid_name | 73 | const,const,const | 1 | 100.00 | NULL | +----+-------------+---------+------------+------+----------------------------------------------+----------------------+---------+-------------------+------+----------+-------+ 1 row in set, 2 warnings (0.01 sec) mysql> EXPLAIN SELECT SQL_NO_CACHE * FROM student WHERE student.classid=1 AND student.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 | 498914 | 1.00 | Using where | +----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+ 1 row in set, 2 warnings (0.00 sec) ``` ## 主键插入顺序 对于一个使用 `InnoDB` 存储引擎的表来说，在没有显式的创建索引时，表中的数据实际上都是存储在聚簇索引的叶子节点的。而记录又是存储在数据页中的，数据页和记录又是按照记录主键值从小到大的顺序进行排序，所以如果插入的记录的主键值是依次增大的话，那每插满一个数据页就换到下一个数据页继续插，而如果插入的主键值忽大忽小的话，需要把当前页面分裂成两个页面，把本页中的一些记录移动到新创建的这个页中。页面分裂和记录移位意味着性能损耗！所以如果想尽量避免这样无谓的性能损耗，最好让插入的记录的主键值依次递增，这样就不会发生这样的性能损耗了。所以建议：让主键具有 `AUTO_INCREMENT` ，让存储引擎自己为表生成主键，在插入记录时存储引擎会自动填入自增的主键值。这样的主键占用空间小，顺序写入，减少页分裂。 ## 计算、函数、类型转换（自动或手动）导致索引失效 ``` mysql> CREATE INDEX idx_name ON student(`name`); Query OK, 0 rows affected (1.28 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> EXPLAIN SELECT * FROM student WHERE `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 | 63 | NULL | 28 | 100.00 | Using index condition | +----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.01 sec) mysql> EXPLAIN SELECT * FROM student WHERE LEFT(`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 | 498914 | 100.00 | Using where | +----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+ 1 row in set, 1 warning (0.01 sec) mysql> EXPLAIN SELECT * FROM student WHERE SUBSTRING(`name`, 1,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 | 498914 | 100.00 | Using where | +----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> CREATE INDEX idx_sno ON student(stuno); Query OK, 0 rows affected (0.68 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> EXPLAIN SELECT * FROM student WHERE stuno = 900000; +----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+-------+ | 1 | SIMPLE | student | NULL | ref | idx_sno | idx_sno | 4 | const | 1 | 100.00 | NULL | +----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) mysql> EXPLAIN SELECT * FROM student WHERE stuno+1 = 900001; +----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 498914 | 100.00 | Using where | +----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) ``` ## 类型转换导致索引失效 ``` mysql> CREATE INDEX idx_name ON student(`name`); Query OK, 0 rows affected (1.25 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> EXPLAIN SELECT * 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 | 63 | const | 1 | 100.00 | NULL | +----+-------------+---------+------------+------+---------------+----------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) mysql> EXPLAIN SELECT * 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 | 498914 | 10.00 | Using where | +----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+ 1 row in set, 3 warnings (0.00 sec) ``` ## 范围条件右边的列索引失效 使用范围查询时，比如: `<` 、 `<=` 、 `>` 、 `>=` 和 `between` 等，范围右边的列不能使用。 如果这种 `sql` 出现较多，在创建联合索引时，务必把范围涉及到的字段放在最后，例如：金额查询、日期查询往往都是范围查询。 ``` mysql> CREATE INDEX idx_age_classId_name ON student(age,classId,`name`); Query OK, 0 rows affected (1.31 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> EXPLAIN SELECT * 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 | 18332 | 10.00 | Using index condition | +----+-------------+---------+------------+-------+----------------------+----------------------+---------+------+-------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec) mysql> CREATE INDEX idx_age_name_cid ON student(age,`name`,classId); Query OK, 0 rows affected (1.37 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> EXPLAIN SELECT * 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_name_cid | idx_age_name_cid | 73 | NULL | 1 | 100.00 | Using index condition | +----+-------------+---------+------------+-------+---------------------------------------+------------------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec) ``` ## 不等于（ `!=` 或 `<>` ）索引失效 ``` mysql> CREATE INDEX idx_name ON student(`name`); Query OK, 0 rows affected (1.14 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> EXPLAIN SELECT * 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 | idx_name | NULL | NULL | NULL | 498914 | 50.15 | Using where | +----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> EXPLAIN SELECT * 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 | idx_name | NULL | NULL | NULL | 498914 | 50.15 | Using where | +----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) ``` ## `is null` 可以使用索引， `is not null` 无法使用索引 最好在设计数据表的时候就将字段设置为 `NOT NULL` 约束，比如可以将 `INT` 类型的字段，默认值设置为 `0` 。将字符类型的默认值设置为空字符串 `"` 。同理，在查询中使用 `not like` 也无法使用索引，导致全表扫描。 ``` mysql> CREATE INDEX idx_age ON student(age); Query OK, 0 rows affected (0.78 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> EXPLAIN SELECT * 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 | idx_age | 5 | const | 1 | 100.00 | Using index condition | +----+-------------+---------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec) mysql> EXPLAIN SELECT * 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 | NULL | NULL | NULL | 498914 | 50.00 | Using where | +----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) ``` ## `LIKE` 以通配符 `%` 开头索引失效 在使用 `LIKE` 关键字进行查询的查询语句中，如果匹配字符串的第一个字符为 `%` ，索引就不会起作用。只有 `%` 不在第一个位置，索引才会起作用。 ``` mysql> CREATE INDEX idx_name ON student(`name`); Query OK, 0 rows affected (1.07 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> EXPLAIN SELECT * 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 | idx_name | idx_name | 63 | NULL | 724 | 100.00 | Using index condition | +----+-------------+---------+------------+-------+---------------+----------+---------+------+------+----------+-----------------------+ 1 row in set, 1 warning (0.00 sec) mysql> EXPLAIN SELECT * 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 | 498914 | 11.11 | Using where | +----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) ``` ## `OR` 前后存在非索引的列，索引失效 在 `WHERE` 子句中，如果在 `OR` 前的条件列进行了索引，而在 `OR` 后的条件列没有进行索引，那么索引会失效。也就是说， `OR` 前后的两个条件中的列都是索引时，查询中才使用索引。因为 `OR` 的含义就是两个只要满足一个即可，因此只有一个条件列进行了索引是没有意义的，只要有条件列没有进行索引，就会进行全表扫描，因此索引的条件列也会失效。 ``` mysql> CREATE INDEX idx_age ON student(age); Query OK, 0 rows affected (0.89 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> EXPLAIN SELECT * 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 | NULL | NULL | NULL | 498914 | 11.88 | Using where | +----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> CREATE INDEX idx_cid ON student(classid); Query OK, 0 rows affected (0.77 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> EXPLAIN SELECT * 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 | index_merge | idx_age,idx_cid | idx_age,idx_cid | 5,5 | NULL | 10565 | 100.00 | Using union(idx_age,idx_cid); Using where | +----+-------------+---------+------------+-------------+-----------------+-----------------+---------+------+-------+----------+-------------------------------------------+ 1 row in set, 1 warning (0.00 sec) ``` ## 数据库和表的字符集统一使用utf8mb4 统一使用 `utf8mb4` 兼容性更好,统一字符集可以避免由于字符集转换产生的乱码。不同的字符集进行比较前需要进行转换会造成索引失效。 ## 总结 - 对于单列索引，尽量选择针对当前 `query` 过滤性更好的索引 - 在选择组合索引的时候，当前 `query` 中过滤性最好的字段在索引字段顺序中，位置越靠前越好。 - 在选择组合索引的时候，尽量选择能够包含当前 `query` 中的 `where` 子句中更多字段的索引。 - 在选择组合索引的时候，如果某个字段可能出现范围查询时，尽量把这个字段放在索引次序的最后面。 # 索引优化 ## 关联查询优化 下文示例的表结构如下，并使用随机函数各插入20条数据（重复执行20次） ``` CREATE TABLE `type` ( `id` int unsigned NOT NULL AUTO_INCREMENT, `card` int unsigned NOT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB; CREATE TABLE `book` ( `bookid` int unsigned NOT NULL AUTO_INCREMENT, `card` int unsigned NOT NULL, PRIMARY KEY (`bookid`) ) ENGINE=InnoDB; INSERT INTO `type`(card) VALUES(FLOOR(1 + (RAND() * 20))); INSERT INTO `book`(card) VALUES(FLOOR(1 + (RAND() * 20))); ``` ### （左）外连接 默认没有索引时，两个表都使用全表扫描，被驱动表使用 `Using join buffer` 缓存加速查询 ``` mysql> EXPLAIN SELECT * FROM `type` LEFT JOIN book ON type.card = book.card; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+ | 1 | SIMPLE | type | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | NULL | | 1 | SIMPLE | book | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | Using where; Using join buffer (hash join) | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+ 2 rows in set, 1 warning (0.01 sec) ``` 被驱动表加上索引之后，可以使用到索引 ``` mysql> CREATE INDEX idx_card ON book ( card ); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> EXPLAIN SELECT * FROM `type` LEFT JOIN book ON type.card = book.card; +----+-------------+-------+------------+------+---------------+----------+---------+----------------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+----------+---------+----------------------+------+----------+-------------+ | 1 | SIMPLE | type | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | NULL | | 1 | SIMPLE | book | NULL | ref | idx_card | idx_card | 4 | atguigudb2.type.card | 1 | 100.00 | Using index | +----+-------------+-------+------------+------+---------------+----------+---------+----------------------+------+----------+-------------+ 2 rows in set, 1 warning (0.00 sec) ``` 驱动表加上索引之后，也可以使用到索引 ``` mysql> CREATE INDEX idx_card ON `type`(card); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> EXPLAIN SELECT * FROM `type` LEFT JOIN book ON type.card = book.card; +----+-------------+-------+------------+-------+---------------+----------+---------+----------------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+----------+---------+----------------------+------+----------+-------------+ | 1 | SIMPLE | type | NULL | index | NULL | idx_card | 4 | NULL | 20 | 100.00 | Using index | | 1 | SIMPLE | book | NULL | ref | idx_card | idx_card | 4 | atguigudb2.type.card | 1 | 100.00 | Using index | +----+-------------+-------+------------+-------+---------------+----------+---------+----------------------+------+----------+-------------+ 2 rows in set, 1 warning (0.00 sec) ``` 再删除被驱动表的索引，被驱动表又进行全表扫描 ``` mysql> DROP INDEX idx_card ON book; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> EXPLAIN SELECT * FROM `type` LEFT JOIN book ON type.card = book.card; +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+--------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+--------------------------------------------+ | 1 | SIMPLE | type | NULL | index | NULL | idx_card | 4 | NULL | 20 | 100.00 | Using index | | 1 | SIMPLE | book | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | Using where; Using join buffer (hash join) | +----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+--------------------------------------------+ 2 rows in set, 1 warning (0.00 sec) ``` ### 内连接 默认没有索引时，两个表都使用全表扫描，被驱动表使用 `Using join buffer` 缓存加速查询 ``` mysql> EXPLAIN SELECT * FROM `type` INNER JOIN book ON type.card = book.card; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+ | 1 | SIMPLE | type | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | NULL | | 1 | SIMPLE | book | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 10.00 | Using where; Using join buffer (hash join) | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------+ 2 rows in set, 1 warning (0.00 sec) ``` 被驱动表加上索引之后，可以使用到索引 ``` mysql> CREATE INDEX idx_card ON book(card); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> EXPLAIN SELECT * FROM `type` INNER JOIN book ON type.card = book.card; +----+-------------+-------+------------+------+---------------+----------+---------+----------------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+----------+---------+----------------------+------+----------+-------------+ | 1 | SIMPLE | type | NULL | ALL | NULL | NULL | NULL | NULL | 20 | 100.00 | NULL | | 1 | SIMPLE | book | NULL | ref | idx_card | idx_card | 4 | atguigudb2.type.card | 1 | 100.00 | Using index | +----+-------------+-------+------------+------+---------------+----------+---------+----------------------+------+----------+-------------+ 2 rows in set, 1 warning (0.00 sec) ``` 驱动表加上索引之后，也可以使用到索引，之后从被驱动表中删除一些数据，再查看执行计划，发现驱动表和被驱动表进行了对调。 结论：对于内连接来说，查询优化器可以决定谁作为驱动表，谁作为被驱动表出现的，在两个表的连接条件都存在索引的情况下，会选择小表作为驱动表，即：“小表驱动大表” ``` mysql> CREATE INDEX idx_card ON `type`(card); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> EXPLAIN SELECT * FROM `type` INNER JOIN book ON type.card = book.card; +----+-------------+-------+------------+-------+---------------+----------+---------+----------------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+----------+---------+----------------------+------+----------+-------------+ | 1 | SIMPLE | type | NULL | index | idx_card | idx_card | 4 | NULL | 20 | 100.00 | Using index | | 1 | SIMPLE | book | NULL | ref | idx_card | idx_card | 4 | atguigudb2.type.card | 1 | 100.00 | Using index | +----+-------------+-------+------------+-------+---------------+----------+---------+----------------------+------+----------+-------------+ 2 rows in set, 1 warning (0.00 sec) mysql> EXPLAIN SELECT * FROM `type` INNER JOIN book ON type.card = book.card; +----+-------------+-------+------------+-------+---------------+----------+---------+----------------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+----------+---------+----------------------+------+----------+-------------+ | 1 | SIMPLE | book | NULL | index | idx_card | idx_card | 4 | NULL | 8 | 100.00 | Using index | | 1 | SIMPLE | type | NULL | ref | idx_card | idx_card | 4 | atguigudb2.book.card | 1 | 100.00 | Using index | +----+-------------+-------+------------+-------+---------------+----------+---------+----------------------+------+----------+-------------+ 2 rows in set, 1 warning (0.00 sec) ``` 此时再删除先创建的 `book` 表的索引，再次查看执行计划，发现 `type` 变为被驱动表 对于内连接来讲，与外连接不同，如果表的连接条件中只有一个字段有索引，则有索引的字段所在的表会被作为被驱动表出现。 ``` mysql> DROP INDEX idx_card ON book; Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> EXPLAIN SELECT * FROM `type` INNER JOIN book ON type.card = book.card; +----+-------------+-------+------------+------+---------------+----------+---------+----------------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+----------+---------+----------------------+------+----------+-------------+ | 1 | SIMPLE | book | NULL | ALL | NULL | NULL | NULL | NULL | 7 | 100.00 | NULL | | 1 | SIMPLE | type | NULL | ref | idx_card | idx_card | 4 | atguigudb2.book.card | 1 | 100.00 | Using index | +----+-------------+-------+------------+------+---------------+----------+---------+----------------------+------+----------+-------------+ 2 rows in set, 1 warning (0.00 sec) ``` ### 总结 - 保证被驱动表的 `JOIN` 字段已经创建了索引 - 需要 `JOIN` 的字段，数据类型保持一致。 - `LEFT JOIN` 时，选择小表作为驱动表， 大表作为被驱动表 。减少外层循环的次数。 - `INNER JOIN` 时， `MySQL` 会自动将小结果集的表选为驱动表 。选择相信 `MySQL` 优化策略。 - 能够直接多表关联的尽量直接关联，不用子查询（减少查询的趟数）。 - 不建议使用子查询，建议将子查询 `SQL` 拆开结合程序多次查询，或使用 `JOIN` 来代替子查询。 - 衍生表建不了索引 ## 排序优化 ### 简介 > 为什么排序也要加索引 在 `MySQL` 中，支持两种排序方式，分别是 `FileSort` 和 `Index` 排序。 - `Index` 排序中，索引可以保证数据的有序性，不需要再进行排序，效率更高。 - `FileSort` 排序则一般在内存中进行排序，占用 `CPU` 较多。如果待排结果较大，会产生临时文件 `I/O`到磁盘进行排序的情况，效率较低。 > 优化建议： 1. `SQL` 中，可以在 `WHERE` 子句和 `ORDER BY` 子句中使用索引，目的是在 `WHERE` 子句中避免全表扫描，在 `ORDER BY` 子句避免使用 `FileSort` 排序。当然，某些情况下全表扫描，或者 `FileSort` 排序不一定比索引慢。但总的来说，我们还是要避免，以提高查询效率。 2. 尽量使用 `Index` 完成 `ORDER BY` 排序。如果 `WHERE` 和 `ORDER BY` 后面是相同的列就使用单索引列；如果不同就使用联合索引。 3. 无法使用 `Index` 时，需要对 `FileSort` 方式进行调优。 ### 示例 默认无索引情况下，使用 `filesort` 排序 ``` mysql> EXPLAIN SELECT * FROM student ORDER BY age,classid; +----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+ | 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 498914 | 100.00 | Using filesort | +----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+ 1 row in set, 1 warning (0.00 sec) mysql> EXPLAIN SELECT * FROM student ORDER BY age,classid LIMIT 10; +----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+ | 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 498914 | 100.00 | Using filesort | +----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+ 1 row in set, 1 warning (0.01 sec) ``` 创建索引后，不限制数量索引失效；增加 `limit` 过滤条件，使用上索引了。 ``` mysql> CREATE INDEX idx_age_classid_name ON student (age,classid,`name`); Query OK, 0 rows affected (1.78 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> EXPLAIN SELECT * FROM student ORDER BY age,classid; +----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+ | 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 498914 | 100.00 | Using filesort | +----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+ 1 row in set, 1 warning (0.00 sec) mysql> EXPLAIN SELECT * FROM student ORDER BY age,classid LIMIT 10; +----+-------------+---------+------------+-------+---------------+----------------------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+----------------------+---------+------+------+----------+-------+ | 1 | SIMPLE | student | NULL | index | NULL | idx_age_classid_name | 73 | NULL | 10 | 100.00 | NULL | +----+-------------+---------+------------+-------+---------------+----------------------+---------+------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) ``` `ORDER BY` 规则不一致，索引失效 （顺序错，不索引；方向反，不索引） ``` mysql> EXPLAIN SELECT * FROM student ORDER BY age,classid LIMIT 10; +----+-------------+---------+------------+-------+---------------+----------------------+---------+------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+----------------------+---------+------+------+----------+-------+ | 1 | SIMPLE | student | NULL | index | NULL | idx_age_classid_name | 73 | NULL | 10 | 100.00 | NULL | +----+-------------+---------+------------+-------+---------------+----------------------+---------+------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) mysql> EXPLAIN SELECT * FROM student ORDER BY classid,age LIMIT 10; +----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+ | 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 498914 | 100.00 | Using filesort | +----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+ 1 row in set, 1 warning (0.00 sec) mysql> EXPLAIN SELECT * FROM student ORDER BY age DESC, classid ASC LIMIT 10; +----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+ | 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 498914 | 100.00 | Using filesort | +----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+ 1 row in set, 1 warning (0.00 sec) mysql> EXPLAIN SELECT * FROM student ORDER BY age ASC,classid DESC LIMIT 10; +----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+ | 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 498914 | 100.00 | Using filesort | +----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+ 1 row in set, 1 warning (0.00 sec) mysql> EXPLAIN SELECT * FROM student ORDER BY age DESC, classid DESC LIMIT 10; +----+-------------+---------+------------+-------+---------------+----------------------+---------+------+------+----------+---------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+----------------------+---------+------+------+----------+---------------------+ | 1 | SIMPLE | student | NULL | index | NULL | idx_age_classid_name | 73 | NULL | 10 | 100.00 | Backward index scan | +----+-------------+---------+------------+-------+---------------+----------------------+---------+------+------+----------+---------------------+ 1 row in set, 1 warning (0.00 sec) ``` 添加 `WHERE` 条件时，可以使用到索引 ``` mysql> EXPLAIN SELECT * FROM student ORDER BY classid; +----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+ | 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 498914 | 100.00 | Using filesort | +----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+----------------+ 1 row in set, 1 warning (0.00 sec) mysql> EXPLAIN SELECT * FROM student WHERE age=45 ORDER BY classid; +----+-------------+---------+------------+------+----------------------+----------------------+---------+-------+-------+----------+-------+ | 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_classid_name | 5 | const | 18212 | 100.00 | NULL | +----+-------------+---------+------------+------+----------------------+----------------------+---------+-------+-------+----------+-------+ 1 row in set, 1 warning (0.00 sec) ``` ### `filesort` 算法：双路排序和单路排序 - 双路排序（慢） - `MySQL 4.1` 之前是使用双路排序，字面意思就是两次扫描磁盘，最终得到数据，读取行指针和 `order by` 列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出 - 从磁盘取排序字段，在 `buffer` 进行排序，再从磁盘取其他字段。 - 取一批数据，要对磁盘进行两次扫描， `IO` 是很耗时的，所以在 `mysql 4.1` 之后，出现了第二种改进的算法，就是单路排序。 - 单路排序（快） - 从磁盘读取查询需要的所有列，按照 `order by` 列在 `buffer` 对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机 `IO` 变成了顺序 `IO` ，但是它会使用更多的空间，因为它把每一行都保存在内存中了。 - 结论及引申出的问题 - 由于单路是后出的，总体而言好过双路 - 但是用单路有问题 - 优化策略 - 尝试提高 `sort_buffer_size` - 尝试提高 `max_length_for_sort_data` - `Order by` 时 `select *` 是一个大忌。最好只 `Query` 需要的字段。 ## 分组优化 - `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` 。 - 包含了 `order by` 、 `group by` 、 `distinct` 这些查询的语句， `where` 条件过滤出来的结果集请保持在 `1000` 行以内，否则 `SQL` 会很慢。 ## 分页优化 一般分页查询时，通过创建覆盖索引能够比较好地提高性能。一个常见又非常头疼的问题就是 `limit 200000,10` ，此时需要 `MySQL` 排序前 `2000010` 记录，仅仅返回 `2000000 - 2000010` 的记录，其他记录丢弃,查询排序的代价非常大。 > 优化方案一： 在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容。 ``` mysql> EXPLAIN SELECT * FROM student LIMIT 400000, 1; +----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------+ | 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 498914 | 100.00 | NULL | +----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------+ 1 row in set, 1 warning (0.00 sec) mysql> EXPLAIN SELECT * FROM student t,(SELECT id FROM student ORDER BY id LIMIT 400000,1) 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 | 400001 | 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 | 400001 | 100.00 | Using index | +----+-------------+------------+------------+--------+---------------+---------+---------+------+--------+----------+-------------+ 3 rows in set, 1 warning (0.00 sec) mysql> SELECT * FROM student LIMIT 400000, 1; +--------+--------+--------+------+---------+ | id | stuno | name | age | classId | +--------+--------+--------+------+---------+ | 400001 | 500001 | lrdrxn | 47 | 864 | +--------+--------+--------+------+---------+ 1 row in set (0.08 sec) mysql> SELECT * FROM student t,(SELECT id FROM student ORDER BY id LIMIT 400000,1) a WHERE t.id = a.id; +--------+--------+--------+------+---------+--------+ | id | stuno | name | age | classId | id | +--------+--------+--------+------+---------+--------+ | 400001 | 500001 | lrdrxn | 47 | 864 | 400001 | +--------+--------+--------+------+---------+--------+ 1 row in set (0.04 sec) mysql> SELECT * FROM student LIMIT 400000, 1; +--------+--------+--------+------+---------+ | id | stuno | name | age | classId | +--------+--------+--------+------+---------+ | 400001 | 500001 | lrdrxn | 47 | 864 | +--------+--------+--------+------+---------+ 1 row in set (0.08 sec) ``` > 优化方案二： 该方案适用于主键自增的表，可以把 `Limit` 查询转换成某个位置的查询 。 ``` mysql> EXPLAIN SELECT * FROM student WHERE id > 400000 LIMIT 1; +----+-------------+---------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+ | 1 | SIMPLE | student | NULL | range | PRIMARY | PRIMARY | 4 | NULL | 186316 | 100.00 | Using where | +----+-------------+---------+------------+-------+---------------+---------+---------+------+--------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> SELECT * FROM student WHERE id > 400000 LIMIT 1; +--------+--------+--------+------+---------+ | id | stuno | name | age | classId | +--------+--------+--------+------+---------+ | 400001 | 500001 | lrdrxn | 47 | 864 | +--------+--------+--------+------+---------+ 1 row in set (0.00 sec) ``` ## 子查询优化 PS：网上都说连接查询优于子查询，其实是要看数据量的。 `MySQL` 从 `4.1` 版本开始支持子查询，使用子查询可以进行 `SELECT` 语句的嵌套查询，即一个 `SELECT` 查询的结果作为另一个 `SELECT` 语句的条件。子查询可以一次性完成很多逻辑上需要多个步骤才能完成的 `SQL` 操作。 子查询是 `MySQL` 的一项重要的功能，可以帮助我们通过一个 `SQL` 语句实现比较复杂的查询。但是，子查询的执行效率不高。原因： 1. 执行子查询时， `MySQL` 需要为内层查询语句的查询结果建立一个临时表，然后外层查询语句从临时表中查询记录。查询完毕后，再撤销这些临时表。这样会消耗过多的 `CPU` 和 `IO` 资源，产性大量的慢查询。 2. 子查询的结果集存储的临时表，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响。 3. 对于返回结果集比较大的子查询，其对查询性能的影响也就越大。 在 `MySQL` 中，可以使用连接查询来替代子查询。连接查询不需要建立临时表，其速度比子查询要快，如果查询中使用索引的话，性能就会更好。 # 覆盖索引 ## 简介 - 索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据；当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了满足查询结果的数据就叫做覆盖索引。 - 非聚簇复合索引的一种形式，它包括在查询里的 `SELECT` 、 `JOIN` 和 `WHERE` 子句用到的所有列（即建索引的字段正好是覆盖查询条件中所涉及的字段）。 简单说就是，`索引列` + `主键` 包含 `SELECT` 到 `FROM` 之间查询的列。 ## 利弊 - 好处： - 避免 `Innodb` 表进行索引的二次查询（回表） - 可以把随机 `IO` 变成顺序 `IO` 加快查询效率 - 弊端： - 索引字段的维护总是有代价的。因此，在建立冗余索引来支持覆盖索引时就需要权衡考虑了。 ## 示例 > 示例1： ``` mysql> CREATE INDEX idx_age_name ON student (age,`name`); Query OK, 0 rows affected (1.33 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> EXPLAIN SELECT * FROM student WHERE age <> 20; +----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | 1 | SIMPLE | student | NULL | ALL | idx_age_name | NULL | NULL | NULL | 498914 | 100.00 | Using where | +----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> EXPLAIN SELECT age,`name` FROM student WHERE age <> 20; +----+-------------+---------+------------+-------+---------------+--------------+---------+------+--------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+--------------+---------+------+--------+----------+--------------------------+ | 1 | SIMPLE | student | NULL | index | idx_age_name | idx_age_name | 68 | NULL | 498914 | 100.00 | Using where; Using index | +----+-------------+---------+------------+-------+---------------+--------------+---------+------+--------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec) ``` > 示例2： ``` mysql> EXPLAIN SELECT * FROM student WHERE `name` LIKE '%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 | 498914 | 11.11 | Using where | +----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) mysql> EXPLAIN SELECT id,age FROM student WHERE `name` LIKE '%abc'; +----+-------------+---------+------------+-------+---------------+--------------+---------+------+--------+----------+--------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+-------+---------------+--------------+---------+------+--------+----------+--------------------------+ | 1 | SIMPLE | student | NULL | index | NULL | idx_age_name | 68 | NULL | 498914 | 11.11 | Using where; Using index | +----+-------------+---------+------------+-------+---------------+--------------+---------+------+--------+----------+--------------------------+ 1 row in set, 1 warning (0.00 sec) ``` # 前缀索引 业务场景中经常有针对字符串相等或模糊匹配的条件查找，如果没有索引，那么这个语句就只能做全表扫描。 ``` mysql> EXPLAIN SELECT * FROM student WHERE `name` = 'xxx'; +----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+ | 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 498914 | 10.00 | Using where | +----+-------------+---------+------------+------+---------------+------+---------+------+--------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) ``` 默认创建索引时，如果不指定长度，那么索引就会包含整个字符串 ``` CREATE INDEX idx_name1 ON student (`name`); ``` `MySQL` 支持前缀索引。创建索引时可以指定字符串长度 ``` CREATE INDEX idx_name2 ON student (`name`(4)); ``` 使用前缀索引，定义好长度，可以做到既节省空间，又不用额外增加太多的查询成本。但是前缀索引用不上覆盖索引对查询性能的优化。 # 索引条件下推（ `ICP` ） `Index Condition Pushdown(ICP)` 是 `MySQL 5.6` 中新特性,是一种在存储引擎层使用索引过滤数据的优化方式。 - 如果没有 `ICP` ，存储引擎会遍历索引以定位基表中的行，并将它们返回给 `MySQL` 服务器，由 `MySQL` 服务器评估 `WHERE` 后面的条件是否保留行。 - 启用 `ICP` 后，如果部分 `WHERE` 条件可以仅使用索引中的列进行筛选，则 `MySQL` 服务器会把这部分 `WHERE` 条件放到存储引擎筛选。然后，存储引擎通过使用索引条目来筛选数据，并且只有在满足这一条件时才从表中读取行。 - 好处: `ICP` 可以减少存储引擎必须访问基表的次数和 `MySQL` 服务器必须访问存储引擎的次数。 - 但是：`ICP` 的加速效果取决于在存储引擎内通过 `ICP` 筛选掉的数据的比例。 - 默认情况下启用索引条件下推。可以通过设置系统变量 `optimizer_ switch` 控制 `index_condition_pushdown` - 打开索引下推 `SET optimizer_switch = 'index_condition_pushdown=on';` - 关闭索引下推 `SET optimizer_switch = 'index_condition_pushdown=off';` - 当使用索引条件下推时， `EXPLAIN` 语句输出结果中 `Extra` 列内容显示为 `Using index condition` 。 - `ICP` 的使用条件： - 如果表访问的类型为 `range` 、 `ref` 、 `eq_ref` 和 `ref_or_null` 可以使用 `ICP` - `ICP` 可以用于 `InnoDB` 和 `MyISAM` 表，包括分区表 `InnoDB` 和 `MyISAM` 表 - 对于 `InnoDB` 表， `ICP` 仅用于二级索引。 `ICP` 的目标是减少全行读取次数，从而减少 `I/O` 操作。 - 当 `SQL` 使用覆盖索引时，不支持 `ICP` 。因为这种情况下使用 `ICP` 不会减少 `I/O` 。 - 相关子查询的条件不能使用 `ICP` # 其他查询优化 ## `EXISTS` 和 `IN` 的区分 > 问题： 不太理解哪种情况下应该使用 `EXISTS` ，哪种情况应该用 `IN` 。选择的标准是看能否使用表的索引吗？ > 回答： 索引是个前提，选择与否还是要看表的大小。可以将选择的标准理解为小表驱动大表。在这种方式下效率是最高的。 ## `COUNT(*)` 与 `COUNT(id)` 与 `COUNT(1)` > 问： 在 `MySQL` 中统计数据表的行数，可以使用三种方式： `SELECT COUNT(*)` 、 `SELECT COUNT(1)` 和 `SELECT COUNT(id)` ，使用这三者之间的查询效率是怎样的? > 答: - 前提：如果要统计的是某个字段的非空数据行数，则另当别论，毕竟比较执行效率的前提是结果一样才可以。 - 环节1： `COUNT(*)` 和 `COUNT(1)` 都是对所有结果进行 `COUNT` ，本质上并没有区别（二者执行时间可能略有差别，不过还是可以把它俩的执行效率看成是相等的)。如果有 `WHERE` 子句，则是对所有符合筛选条件的数据行进行统计；如果没有 `WHERE` 子句，则是对数据表的数据行数进行统计。 - 环节2： - 如果是 `MyISAM` 存储引擎，统计数据表的行数只需要 `O(1)` 的复杂度，这是因为每张 `MyISAM` 的数据表都有一个 `meta` 信息存储了 `row_count` 值，而一致性则由表级锁来保证。 - 如果是 `InnoDB` 存储引擎，因为 `InnoDB` 支持事务，采用行级锁和 `MVCC` 机制，所以无法像 `MyISAM` 一样，维护一个 `row_count` 变量，因此需要采用扫描全表，是 `O(n)` 的复杂度，进行 `循环 + 计数` 的方式来完成统计。 - 环节3：在 `InnoDB` 引擎中，如果采用 `COUNT(具体字段)` 来统计数据行数，要尽量采用二级索引。因为主键采用的索引是聚簇索引，聚簇索引包含的信息多，明显会大于二级索引（非聚簇索引）。对于 `COUNT(*)` 和 `COUNT(1)` 来说，它们不需要查找具体的行，只是统计行数，系统会自动采用占用空间更小的二级索引来进行统计。如果有多个二级索引，会使用 `key_len` 小的二级索引进行扫描。当没有二级索引的时候，才会采用主键索引来进行统计。 ## 关于 `SELECT(*)` 在表查询中，建议明确字段，不要使用 `*` 作为查询的字段列表，推荐使用 `SELECT <字段列表>` 查询。原因： - MySQL在解析的过程中，会通过查询数据字典将 `*` 按序转换成所有列名，这会耗费资源和时间。 - 无法使用覆盖索引 ## `LIMIT 1` 对优化的影响 针对的是会扫描全表的 `SQL` 语句，如果你可以确定结果集只有一条,那么加上 `LIMIT 1` 的时候，当找到一条结果的时候就不会继续扫描了，这样会加快查询速度。如果数据表已经对字段建立了唯一索引，那么可以通过索引进行查询，不会全表扫描的话，就不需要加上 `LIMIT 1` 。
8