Day162.索引优化分析②[索引优化、索引失效] -MySql高级
发布日期：2021-05-07 01:39:51
浏览次数：16
分类：原创文章
本文共 80355 字，大约阅读时间需要 267 分钟。
第 2 章 索引优化分析
5、索引优化
5.1、单表索引优化
单表索引优化分析
创建表
- 建表 SQL
CREATE TABLE IF NOT EXISTS article( id INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT, author_id INT(10) UNSIGNED NOT NULL, category_id INT(10) UNSIGNED NOT NULL, views INT(10) UNSIGNED NOT NULL, comments INT(10) UNSIGNED NOT NULL, title VARCHAR(255) NOT NULL, content TEXT NOT NULL);INSERT INTO article(author_id,category_id,views,comments,title,content)VALUES(1,1,1,1,'1','1'),(2,2,2,2,'2','2'),(1,1,3,3,'3','3');- 表中的测试数据
mysql> SELECT * FROM article;+----+-----------+-------------+-------+----------+-------+---------+| id | author_id | category_id | views | comments | title | content |+----+-----------+-------------+-------+----------+-------+---------+| 1 | 1 | 1 | 1 | 1 | 1 | 1 || 2 | 2 | 2 | 2 | 2 | 2 | 2 || 3 | 1 | 1 | 3 | 3 | 3 | 3 |+----+-----------+-------------+-------+----------+-------+---------+3 rows in set (0.00 sec)查询案例
- 查询category_id为1且comments 大于1的情况下，views最多的article_id。
mysql> SELECT id, author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;+----+-----------+| id | author_id |+----+-----------+| 3 | 1 |+----+-----------+1 row in set (0.00 sec)- 此时 article 表中只有一个主键索引
mysql> SHOW INDEX FROM article;+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| article | 0 | PRIMARY | 1 | id | A | 3 | NULL | NULL | | BTREE | | |+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+1 row in set (0.00 sec)- 使用 explain 分析 SQL 语句的执行效率：
EXPLAIN SELECT id, author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;
mysql> EXPLAIN SELECT id, author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;+----+-------------+---------+------+---------------+------+---------+------+------+-----------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+---------+------+---------------+------+---------+------+------+-----------------------------+| 1 | SIMPLE | article | ALL | NULL | NULL | NULL | NULL | 3 | Using where; Using filesort |+----+-------------+---------+------+---------------+------+---------+------+------+-----------------------------+1 row in set (0.00 sec)- 结论：
- 很显然，type是
ALL，即最坏的情况。 - Extra 里还出现了
Using filesort，也是最坏的情况。 - 优化是必须的。
- 很显然，type是
开始优化：新建索引
- 创建索引的 SQL 命令
# ALTER TABLE article ADD INDEX idx_article_ccv('category_id', 'comments', 'views'); create index idx_article_ccv on article(category_id, comments, views);- 在 category_id 列、comments 列和 views 列上建立联合索引
mysql> create index idx_article_ccv on article(category_id, comments, views);Query OK, 0 rows affected (0.01 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> SHOW INDEX FROM article;+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| article | 0 | PRIMARY | 1 | id | A | 3 | NULL | NULL | | BTREE | | || article | 1 | idx_article_ccv | 1 | category_id | A | 3 | NULL | NULL | | BTREE | | || article | 1 | idx_article_ccv | 2 | comments | A | 3 | NULL | NULL | | BTREE | | || article | 1 | idx_article_ccv | 3 | views | A | 3 | NULL | NULL | | BTREE | | |+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+4 rows in set (0.00 sec)- 再次执行查询：type变成了
range，这是可以忍受的。但是extra里使用Using filesort仍是无法接受的。
mysql> EXPLAIN SELECT id, author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;+----+-------------+---------+-------+-----------------+-----------------+---------+------+------+---------------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+---------+-------+-----------------+-----------------+---------+------+------+---------------------------------------+| 1 | SIMPLE | article | range | idx_article_ccv | idx_article_ccv | 8 | NULL | 1 | Using index condition; Using filesort |+----+-------------+---------+-------+-----------------+-----------------+---------+------+------+---------------------------------------+1 row in set (0.00 sec)- 分析：
- 但是我们已经建立了索引，为啥没用呢？
- 这是因为按照B+Tree索引的工作原理，先排序 category_id，如果遇到相同的 category_id 则再排序comments，如果遇到相同的 comments 则再排序 views。
- 当comments字段在联合索引里处于中间位置时，因为
comments>1条件是一个范围值（所谓 range），MySQL 无法利用索引再对后面的views部分进行检索，即 range 类型查询字段后面的索引无效。
- 将查询条件中的
comments > 1改为comments = 1，发现 Use filesort 神奇地消失了，从这点可以验证：范围后的索引会导致索引失效
mysql> EXPLAIN SELECT id, author_id FROM article WHERE category_id = 1 AND comments = 1 ORDER BY views DESC LIMIT 1;+----+-------------+---------+------+-----------------+-----------------+---------+-------------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+---------+------+-----------------+-----------------+---------+-------------+------+-------------+| 1 | SIMPLE | article | ref | idx_article_ccv | idx_article_ccv | 8 | const,const | 1 | Using where |+----+-------------+---------+------+-----------------+-----------------+---------+-------------+------+-------------+1 row in set (0.00 sec)删除索引
- 删除索引的 SQL 指令
DROP INDEX idx_article_ccv ON article;- 删除刚才创建的 idx_article_ccv 索引
mysql> DROP INDEX idx_article_ccv ON article;Query OK, 0 rows affected (0.00 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> SHOW INDEX FROM article;+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| article | 0 | PRIMARY | 1 | id | A | 3 | NULL | NULL | | BTREE | | |+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+1 row in set (0.00 sec)再次创建索引
- 创建索引的 SQL 指令
# ALTER TABLE article ADD INDEX idx_article_ccv('category_id', 'views'); create index idx_article_ccv on article(category_id, views);- 由于 range 后（
comments > 1）的索引会失效，这次我们建立索引时，直接抛弃 comments 列，先利用 category_id 和 views 的联合索引查询所需要的数据，再从其中取出comments > 1的数据（我觉着应该是这样的）
mysql> create index idx_article_ccv on article(category_id, views);Query OK, 0 rows affected (0.30 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> SHOW INDEX FROM article;+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| article | 0 | PRIMARY | 1 | id | A | 3 | NULL | NULL | | BTREE | | || article | 1 | idx_article_ccv | 1 | category_id | A | 3 | NULL | NULL | | BTREE | | || article | 1 | idx_article_ccv | 2 | views | A | 3 | NULL | NULL | | BTREE | | |+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+3 rows in set (0.00 sec)- 再次执行查询：可以看到，
type变为了ref，Extra中的Using filesort也消失了，结果非常理想
ysql> EXPLAIN SELECT id, author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;+----+-------------+---------+------+-----------------+-----------------+---------+-------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+---------+------+-----------------+-----------------+---------+-------+------+-------------+| 1 | SIMPLE | article | ref | idx_article_ccv | idx_article_ccv | 4 | const | 2 | Using where |+----+-------------+---------+------+-----------------+-----------------+---------+-------+------+-------------+1 row in set (0.00 sec)- 为了不影响之后的测试，删除该表的 idx_article_ccv 索引
mysql> DROP INDEX idx_article_ccv ON article;Query OK, 0 rows affected (0.05 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> SHOW INDEX FROM article;+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| article | 0 | PRIMARY | 1 | id | A | 3 | NULL | NULL | | BTREE | | |+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+1 row in set (0.01 sec)通过设置索引，来规定查询顺序，comment要求的为>1的范围，所以不将其建立索引，建立category_id, views两字段的索引，就可以使Using filesot消失。
5.2、两表索引优化
两表索引优化分析：主外键：左外连接、右外连接
提前结果：左外连接，索引加右表、右外连接，索引加左表
创建表
- 建表 SQL
CREATE TABLE IF NOT EXISTS class( id INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, card INT(10) UNSIGNED NOT NULL, PRIMARY KEY(id));CREATE TABLE IF NOT EXISTS book( bookid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, card INT(10) UNSIGNED NOT NULL, PRIMARY KEY(bookid));INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));INSERT INTO class(card) VALUES(FLOOR(1+(RAND()*20)));INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));INSERT INTO book(card) VALUES(FLOOR(1+(RAND()*20)));- class 表中的测试数据
mysql> select * from class;+----+------+| id | card |+----+------+| 1 | 12 || 2 | 13 || 3 | 12 || 4 | 17 || 5 | 11 || 6 | 3 || 7 | 1 || 8 | 16 || 9 | 17 || 10 | 16 || 11 | 9 || 12 | 17 || 13 | 18 || 14 | 16 || 15 | 7 || 16 | 8 || 17 | 19 || 18 | 9 || 19 | 6 || 20 | 5 || 21 | 6 |+----+------+21 rows in set (0.00 sec)- book 表中的测试数据
mysql> select * from book;+--------+------+| bookid | card |+--------+------+| 1 | 16 || 2 | 1 || 3 | 17 || 4 | 3 || 5 | 20 || 6 | 12 || 7 | 18 || 8 | 13 || 9 | 13 || 10 | 4 || 11 | 1 || 12 | 13 || 13 | 20 || 14 | 20 || 15 | 1 || 16 | 2 || 17 | 9 || 18 | 16 || 19 | 14 || 20 | 2 |+--------+------+20 rows in set (0.00 sec)查询案例
- 实现两表的连接，连接条件是 class.card = book.card
mysql> SELECT * FROM class LEFT JOIN book ON class.card = book.card;+----+------+--------+------+| id | card | bookid | card |+----+------+--------+------+| 1 | 12 | 6 | 12 || 2 | 13 | 8 | 13 || 2 | 13 | 9 | 13 || 2 | 13 | 12 | 13 || 3 | 12 | 6 | 12 || 4 | 17 | 3 | 17 || 5 | 11 | NULL | NULL || 6 | 3 | 4 | 3 || 7 | 1 | 2 | 1 || 7 | 1 | 11 | 1 || 7 | 1 | 15 | 1 || 8 | 16 | 1 | 16 || 8 | 16 | 18 | 16 || 9 | 17 | 3 | 17 || 10 | 16 | 1 | 16 || 10 | 16 | 18 | 16 || 11 | 9 | 17 | 9 || 12 | 17 | 3 | 17 || 13 | 18 | 7 | 18 || 14 | 16 | 1 | 16 || 14 | 16 | 18 | 16 || 15 | 7 | NULL | NULL || 16 | 8 | NULL | NULL || 17 | 19 | NULL | NULL || 18 | 9 | 17 | 9 || 19 | 6 | NULL | NULL || 20 | 5 | NULL | NULL || 21 | 6 | NULL | NULL |+----+------+--------+------+28 rows in set (0.00 sec)- 使用 explain 分析 SQL 语句的性能，可以看到：驱动表是左表 class 表
mysql> EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+| 1 | SIMPLE | class | ALL | NULL | NULL | NULL | NULL | 21 | NULL || 1 | SIMPLE | book | ALL | NULL | NULL | NULL | NULL | 20 | Using where; Using join buffer (Block Nested Loop) |+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+2 rows in set (0.00 sec)- 结论：
- type 有 All ，rows 为表中数据总行数，说明 class 和 book 进行了全表检索
- 即每次 class 表对 book 表进行左外连接时，都需要在 book 表中进行一次全表检索
添加索引：在右表添加索引
- 添加索引的 SQL 指令
# create index Y on book('card');ALTER TABLE 'book' ADD INDEX Y ('card');- 在 book 的 card 字段上添加索引
mysql> ALTER TABLE book ADD INDEX Y (card);Query OK, 0 rows affected (0.30 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> SHOW INDEX FROM book;+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| book | 0 | PRIMARY | 1 | bookid | A | 20 | NULL | NULL | | BTREE | | || book | 1 | Y | 1 | card | A | 20 | NULL | NULL | | BTREE | | |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+2 rows in set (0.00 sec)- 测试结果：可以看到第二行的type变为了ref，rows也变成了优化比较明显。
mysql> EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;+----+-------------+-------+------+---------------+------+---------+-----------------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+------+---------------+------+---------+-----------------+------+-------------+| 1 | SIMPLE | class | ALL | NULL | NULL | NULL | NULL | 21 | NULL || 1 | SIMPLE | book | ref | Y | Y | 4 | db01.class.card | 1 | Using index |+----+-------------+-------+------+---------------+------+---------+-----------------+------+-------------+2 rows in set (0.00 sec)- 分析：
- 这是由左连接特性决定的。LEFT JOIN条件用于确定如何从右表搜索行，左边一定都有，所以右边是我们的关键点，一定需要建立索引。
- 左表连接右表，则需要拿着左表的数据去右表里面查，索引需要在右表中建立索引
添加索引：在右表添加索引
- 删除之前 book 表中的索引
DROP INDEX Y ON book;- 在 class 表的 card 字段上建立索引
# create index X on class('card');ALTER TABLE class ADD INDEX X(card);- 再次执行左连接，凉凉~~~
mysql> EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card;+----+-------------+-------+-------+---------------+------+---------+------+------+----------------------------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+-------+---------------+------+---------+------+------+----------------------------------------------------+| 1 | SIMPLE | class | index | NULL | X | 4 | NULL | 21 | Using index || 1 | SIMPLE | book | ALL | NULL | NULL | NULL | NULL | 20 | Using where; Using join buffer (Block Nested Loop) |+----+-------------+-------+-------+---------------+------+---------+------+------+----------------------------------------------------+2 rows in set (0.00 sec)- 别怕，我们来执行右连接：可以看到第二行的type变为了ref，rows也变成了优化比较明显。
mysql> EXPLAIN SELECT * FROM class RIGHT JOIN book ON class.card = book.card;+----+-------------+-------+------+---------------+------+---------+----------------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+------+---------------+------+---------+----------------+------+-------------+| 1 | SIMPLE | book | ALL | NULL | NULL | NULL | NULL | 20 | NULL || 1 | SIMPLE | class | ref | X | X | 4 | db01.book.card | 1 | Using index |+----+-------------+-------+------+---------------+------+---------+----------------+------+-------------+2 rows in set (0.00 sec)- 分析：
- 这是因为RIGHT JOIN条件用于确定如何从左表搜索行，右边一定都有，所以左边是我们的关键点，一定需要建立索引。
- class RIGHT JOIN book ：book 里面的数据一定存在于结果集中，我们需要拿着 book 表中的数据，去 class 表中搜索，所以索引需要建立在 class 表中
- 为了不影响之后的测试，删除该表的 idx_article_ccv 索引
mysql> DROP INDEX X ON class;Query OK, 0 rows affected (0.04 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> SHOW INDEX FROM class;+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| class | 0 | PRIMARY | 1 | id | A | 21 | NULL | NULL | | BTREE | | |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+1 row in set (0.00 sec)结论：左外连接，索引加右表、右外连接，索引加左表；相反建
原因：因为无论是左外还是右外
-
如左外，左表必然是已经包括全部了，但LEFT JOIN条件用于确定如何从右表搜索行，所以右边是我们的关键点，一定需要建立索引。
-
如右外，右表必然是已经包括全部了，但RIGHT JOIN条件用于确定如何从左表搜索行，所以←边是我们的关键点，同理一定需要建立索引。
.
5.3、三表索引优化
三表索引优化分析
创建表
- 建表 SQL
CREATE TABLE IF NOT EXISTS phone( phoneid INT(10) UNSIGNED NOT NULL AUTO_INCREMENT, card INT(10) UNSIGNED NOT NULL, PRIMARY KEY(phoneid))ENGINE=INNODB;INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));INSERT INTO phone(card) VALUES(FLOOR(1+(RAND()*20)));- phone 表中的测试数据
mysql> select * from phone;+---------+------+| phoneid | card |+---------+------+| 1 | 7 || 2 | 7 || 3 | 13 || 4 | 6 || 5 | 8 || 6 | 4 || 7 | 16 || 8 | 4 || 9 | 15 || 10 | 1 || 11 | 20 || 12 | 18 || 13 | 9 || 14 | 9 || 15 | 20 || 16 | 11 || 17 | 15 || 18 | 3 || 19 | 8 || 20 | 10 |+---------+------+20 rows in set (0.00 sec)查询案例
- 实现三表的连接查询：
mysql> SELECT * FROM class LEFT JOIN book ON class.card = book.card LEFT JOIN phone ON book.card = phone.card;+----+------+--------+------+---------+------+| id | card | bookid | card | phoneid | card |+----+------+--------+------+---------+------+| 2 | 13 | 8 | 13 | 3 | 13 || 2 | 13 | 9 | 13 | 3 | 13 || 2 | 13 | 12 | 13 | 3 | 13 || 8 | 16 | 1 | 16 | 7 | 16 || 10 | 16 | 1 | 16 | 7 | 16 || 14 | 16 | 1 | 16 | 7 | 16 || 8 | 16 | 18 | 16 | 7 | 16 || 10 | 16 | 18 | 16 | 7 | 16 || 14 | 16 | 18 | 16 | 7 | 16 || 7 | 1 | 2 | 1 | 10 | 1 || 7 | 1 | 11 | 1 | 10 | 1 || 7 | 1 | 15 | 1 | 10 | 1 || 13 | 18 | 7 | 18 | 12 | 18 || 11 | 9 | 17 | 9 | 13 | 9 || 18 | 9 | 17 | 9 | 13 | 9 || 11 | 9 | 17 | 9 | 14 | 9 || 18 | 9 | 17 | 9 | 14 | 9 || 6 | 3 | 4 | 3 | 18 | 3 || 4 | 17 | 3 | 17 | NULL | NULL || 9 | 17 | 3 | 17 | NULL | NULL || 12 | 17 | 3 | 17 | NULL | NULL || 1 | 12 | 6 | 12 | NULL | NULL || 3 | 12 | 6 | 12 | NULL | NULL || 5 | 11 | NULL | NULL | NULL | NULL || 15 | 7 | NULL | NULL | NULL | NULL || 16 | 8 | NULL | NULL | NULL | NULL || 17 | 19 | NULL | NULL | NULL | NULL || 19 | 6 | NULL | NULL | NULL | NULL || 20 | 5 | NULL | NULL | NULL | NULL || 21 | 6 | NULL | NULL | NULL | NULL |+----+------+--------+------+---------+------+30 rows in set (0.00 sec)- 使用 explain 分析 SQL 指令：
mysql> EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card = book.card LEFT JOIN phone ON book.card = phone.card;+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+| 1 | SIMPLE | class | ALL | NULL | NULL | NULL | NULL | 21 | NULL || 1 | SIMPLE | book | ALL | NULL | NULL | NULL | NULL | 20 | Using where; Using join buffer (Block Nested Loop) || 1 | SIMPLE | phone | ALL | NULL | NULL | NULL | NULL | 20 | Using where; Using join buffer (Block Nested Loop) |+----+-------------+-------+------+---------------+------+---------+------+------+----------------------------------------------------+3 rows in set (0.00 sec)- 结论：
- type 有All ，rows 为表数据总行数，说明 class、 book 和 phone 表都进行了全表检索
- Extra 中 Using join buffer ，表明连接过程中使用了 join 缓冲区
创建索引
- 创建索引的 SQL 语句
ALTER TABLE book ADD INDEX Y (card);ALTER TABLE phone ADD INDEX Z (card);- 进行 LEFT JOIN ，永远都在右表的字段上建立索引
mysql> ALTER TABLE book ADD INDEX Y (card);Query OK, 0 rows affected (0.06 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> SHOW INDEX FROM book;+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| book | 0 | PRIMARY | 1 | bookid | A | 20 | NULL | NULL | | BTREE | | || book | 1 | Y | 1 | card | A | 20 | NULL | NULL | | BTREE | | |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+2 rows in set (0.00 sec)mysql> ALTER TABLE phone ADD INDEX Z (card);Query OK, 0 rows affected (0.05 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> SHOW INDEX FROM phone;+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| phone | 0 | PRIMARY | 1 | phoneid | A | 20 | NULL | NULL | | BTREE | | || phone | 1 | Z | 1 | card | A | 20 | NULL | NULL | | BTREE | | |+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+2 rows in set (0.00 sec)- 执行查询：后2行的type都是ref，且总rows优化很好，效果不错。因此索引最好设置在需要经常查询的字段中。
mysql> EXPLAIN SELECT * FROM class LEFT JOIN book ON class.card=book.card LEFT JOIN phone ON book.card = phone.card;+----+-------------+-------+------+---------------+------+---------+-----------------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+------+---------------+------+---------+-----------------+------+-------------+| 1 | SIMPLE | class | ALL | NULL | NULL | NULL | NULL | 21 | NULL || 1 | SIMPLE | book | ref | Y | Y | 4 | db01.class.card | 1 | Using index || 1 | SIMPLE | phone | ref | Z | Z | 4 | db01.book.card | 1 | Using index |+----+-------------+-------+------+---------------+------+---------+-----------------+------+-------------+3 rows in set (0.00 sec)Join 语句优化的结论
小结果为遍历全表，大结果建立索引；
如左外连接，左表为小表，右表为大表
将 left join 看作是两层嵌套 for 循环
- 尽可能减少Join语句中的NestedLoop的循环总次数；
- 永远用小结果集驱动大的结果集（在大结果集中建立索引，在小结果集中遍历全表）；
- 优先优化NestedLoop的内层循环；
- 保证Join语句中被驱动表上Join条件字段已经被索引；
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-dICTykMd-1610526935976)(C:\Users\PePe\AppData\Roaming\Typora\typora-user-images\image-20210112221232308.png)]](https://img-blog.csdnimg.cn/20210113163609785.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjg0NDY5,size_16,color_FFFFFF,t_70)
- 当无法保证被驱动表的Join条件字段被索引且内存资源充足的前提下，不要太吝惜JoinBuffer的设置；
我的理解
- 使用
小表驱动大表，这就相当于外层 for 循环的次数少，内层 for 循环的次数多 - 然后我们在
大表中建立索引，这样内层 for 循环的效率明显提高 - 综上，使用
小表驱动大表，在大表中建立了索引
.
6、索引失效
索引失效（应该避免）
创建表
- 建表 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'员工记录表';INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('z3',22,'manager',NOW());INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('July',23,'dev',NOW());INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES('2000',23,'dev',NOW());ALTER TABLE staffs ADD INDEX index_staffs_nameAgePos(`name`,`age`,`pos`);- staffs 表中的测试数据
mysql> select * from staffs;+----+------+-----+---------+---------------------+| id | name | age | pos | add_time |+----+------+-----+---------+---------------------+| 1 | z3 | 22 | manager | 2020-08-04 14:42:33 || 2 | July | 23 | dev | 2020-08-04 14:42:33 || 3 | 2000 | 23 | dev | 2020-08-04 14:42:33 |+----+------+-----+---------+---------------------+3 rows in set (0.00 sec)- staffs 表中的复合索引：name、age、pos
mysql> SHOW INDEX FROM staffs;+--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| staffs | 0 | PRIMARY | 1 | id | A | 3 | NULL | NULL | | BTREE | | || staffs | 1 | index_staffs_nameAgePos | 1 | name | A | 3 | NULL | NULL | | BTREE | | || staffs | 1 | index_staffs_nameAgePos | 2 | age | A | 3 | NULL | NULL | | BTREE | | || staffs | 1 | index_staffs_nameAgePos | 3 | pos | A | 3 | NULL | NULL | | BTREE | | |+--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+4 rows in set (0.00 sec)6.1、索引失效准则
索引失效判断准则
- 全值匹配我最爱
- 最佳左前缀法则：如果索引了多例，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且
不跳过索引中的列。- 带头大哥不能死，中间兄弟不能断，末尾兄弟随便卖
- 不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描
- 索引列上少计算
- 存储引擎不能使用索引中范围条件右边的列
- 范围之后全失效
- 尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少
select *- **按需写查询列，减少select ***
- mysql在使用不等于（!=或者<>）的时候无法使用索引会导致全表扫描
is null，is not null也无法使用索引（早期版本不能走索引，后续版本应该优化过，可以走索引）- like以通配符开头（’%abc…’）mysql索引失效会变成全表扫描操作
- 百分like加右边 — abc%
- 字符串不加单引号索引失效
- MySql会自动隐式调用类型转换，就出现了上面【3】的情况
- 少用or，用它连接时会索引失效
最佳左匹配法则：带头大哥不能死，中间兄弟不能断
- 只有带头大哥 name 时
- key = index_staffs_nameAgePos 表明索引生效
- ref = const ：这个常量就是查询时的 ‘July’ 字符串常量
mysql> EXPLAIN SELECT * FROM staffs WHERE name = 'July';+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+-----------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+-----------------------+| 1 | SIMPLE | staffs | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 74 | const | 1 | Using index condition |+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+-----------------------+1 row in set (0.00 sec)- 带头大哥 name 带上小弟 age
- key = index_staffs_nameAgePos 表明索引生效
- ref = const,const：两个常量分别为 ‘July’ 和 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 | index_staffs_nameAgePos | index_staffs_nameAgePos | 78 | const,const | 1 | Using index condition |+----+-------------+--------+------+-------------------------+-------------------------+---------+-------------+------+-----------------------+1 row in set (0.00 sec)- 带头大哥 name 带上小弟 age ，小弟 age 带上小小弟 pos
- key = index_staffs_nameAgePos 表明索引生效
- ref = const,const,const ：三个常量分别为 ‘July’、23 和 ‘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 | index_staffs_nameAgePos | index_staffs_nameAgePos | 140 | const,const,const | 1 | Using index condition |+----+-------------+--------+------+-------------------------+-------------------------+---------+-------------------+------+-----------------------+1 row in set (0.00 sec)- 带头大哥 name 挂了
- key = NULL 说明索引失效
- ref = null 表示 ref 也失效
mysql> EXPLAIN SELECT * FROM staffs WHERE age = 23 AND pos = 'dev';+----+-------------+--------+------+---------------+------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+--------+------+---------------+------+---------+------+------+-------------+| 1 | SIMPLE | staffs | ALL | NULL | NULL | NULL | NULL | 3 | Using where |+----+-------------+--------+------+---------------+------+---------+------+------+-------------+1 row in set (0.00 sec)- 带头大哥 name 没挂，小弟 age 跑了
- key = index_staffs_nameAgePos 说明索引没有失效
- ref = const 表明只使用了一个常量，即第二个常量（pos = ‘dev’）没有生效
mysql> EXPLAIN SELECT * FROM staffs WHERE name = 'July'AND pos = 'dev';+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+-----------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+-----------------------+| 1 | SIMPLE | staffs | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 74 | const | 1 | Using index condition |+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+-----------------------+1 row in set (0.00 sec)在索引列上进行计算，会导致索引失效，进而转向全表扫描
- 不对带头大哥 name 进行任何操作：key = index_staffs_nameAgePos 表明索引生效
mysql> EXPLAIN SELECT * FROM staffs WHERE name = 'July';+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+-----------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+-----------------------+| 1 | SIMPLE | staffs | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 74 | const | 1 | Using index condition |+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+-----------------------+1 row in set (0.00 sec)- 对带头大哥 name 进行操作：使用 LEFT 函数截取子串
- key = NULL 表明索引生效
- type = ALL 表明进行了全表扫描
mysql> EXPLAIN SELECT * FROM staffs WHERE LEFT(name,4) = 'July';+----+-------------+--------+------+---------------+------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+--------+------+---------------+------+---------+------+------+-------------+| 1 | SIMPLE | staffs | ALL | NULL | NULL | NULL | NULL | 3 | Using where |+----+-------------+--------+------+---------------+------+---------+------+------+-------------+1 row in set (0.00 sec)范围之后全失效
- 精确匹配
- type = ref 表示非唯一索引扫描，SQL 语句将返回匹配某个单独值的所有行。
- key_len = 140 表明表示索引中使用的字节数
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 | index_staffs_nameAgePos | index_staffs_nameAgePos | 140 | const,const,const | 1 | Using index condition |+----+-------------+--------+------+-------------------------+-------------------------+---------+-------------------+------+-----------------------+1 row in set (0.00 sec)- 将 age 改为范围匹配
- type = range 表示范围扫描
- key = index_staffs_nameAgePos 表示索引并没有失效
- key_len = 78 ，ref = NULL 均表明范围搜索使其后面的索引均失效
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 | range | index_staffs_nameAgePos | index_staffs_nameAgePos | 78 | NULL | 1 | Using index condition |+----+-------------+--------+-------+-------------------------+-------------------------+---------+------+------+-----------------------+1 row in set (0.00 sec)尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少
select \*
SELECT *的写法
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 | range | index_staffs_nameAgePos | index_staffs_nameAgePos | 78 | NULL | 1 | Using index condition |+----+-------------+--------+-------+-------------------------+-------------------------+---------+------+------+-----------------------+1 row in set (0.00 sec)- 覆盖索引的写法：Extra = Using where; Using index ，Using index 表示使用索引列进行查询，将大大提高查询的效率
mysql> EXPLAIN SELECT name, age, pos 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 | index_staffs_nameAgePos | index_staffs_nameAgePos | 140 | const,const,const | 1 | Using where; Using index |+----+-------------+--------+------+-------------------------+-------------------------+---------+-------------------+------+--------------------------+1 row in set (0.00 sec)- 覆盖索引中包含 range 条件：type = ref 并且 Extra = Using where; Using index ，虽然在查询条件中使用了 范围搜索，但是由于我们只需要查找索引列，所以无需进行全表扫描
mysql> EXPLAIN SELECT name, age, pos 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 | index_staffs_nameAgePos | index_staffs_nameAgePos | 74 | const | 1 | Using where; Using index |+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+--------------------------+1 row in set (0.00 sec)mysql在使用不等于（!=或者<>）的时候无法使用索引会导致全表扫描
- 在使用 != 会 <> 时会导致索引失效：
- key = null 表示索引失效
- rows = 3 表示进行了全表扫描
mysql> EXPLAIN SELECT * FROM staffs WHERE name != 'July';+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+| 1 | SIMPLE | staffs | ALL | index_staffs_nameAgePos | NULL | NULL | NULL | 3 | Using where |+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+1 row in set (0.00 sec)mysql> EXPLAIN SELECT * FROM staffs WHERE name <> 'July';+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+| 1 | SIMPLE | staffs | ALL | index_staffs_nameAgePos | NULL | NULL | NULL | 3 | Using where |+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+1 row in set (0.00 sec)is null，is not null 也无法使用索引
- is null，is not null 会导致索引失效：key = null 表示索引失效
ysql> EXPLAIN SELECT * FROM staffs WHERE name is null;+----+-------------+-------+------+---------------+------+---------+------+------+------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+-------+------+---------------+------+---------+------+------+------------------+| 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE |+----+-------------+-------+------+---------------+------+---------+------+------+------------------+1 row in set (0.00 sec)mysql> EXPLAIN SELECT * FROM staffs WHERE name is not null;+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+| 1 | SIMPLE | staffs | ALL | index_staffs_nameAgePos | NULL | NULL | NULL | 3 | Using where |+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+1 row in set (0.00 sec)like % 写最右
- staffs 表的索引关系
mysql> SHOW INDEX from staffs;+--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| staffs | 0 | PRIMARY | 1 | id | A | 3 | NULL | NULL | | BTREE | | || staffs | 1 | index_staffs_nameAgePos | 1 | name | A | 3 | NULL | NULL | | BTREE | | || staffs | 1 | index_staffs_nameAgePos | 2 | age | A | 3 | NULL | NULL | | BTREE | | || staffs | 1 | index_staffs_nameAgePos | 3 | pos | A | 3 | NULL | NULL | | BTREE | | |+--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+4 rows in set (0.00 sec)- like % 写在左边的情况
- type = All ，rows = 3 表示进行了全表扫描
- key = null 表示索引失效
mysql> EXPLAIN SELECT * FROM staffs WHERE name like '%July';+----+-------------+--------+------+---------------+------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+--------+------+---------------+------+---------+------+------+-------------+| 1 | SIMPLE | staffs | ALL | NULL | NULL | NULL | NULL | 3 | Using where |+----+-------------+--------+------+---------------+------+---------+------+------+-------------+1 row in set (0.00 sec)mysql> EXPLAIN SELECT * FROM staffs WHERE name like '%July%';+----+-------------+--------+------+---------------+------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+--------+------+---------------+------+---------+------+------+-------------+| 1 | SIMPLE | staffs | ALL | NULL | NULL | NULL | NULL | 3 | Using where |+----+-------------+--------+------+---------------+------+---------+------+------+-------------+1 row in set (0.00 sec)- like % 写在右边的情况：key = index_staffs_nameAgePos 表示索引未失效
mysql> EXPLAIN SELECT * FROM staffs WHERE name like 'July%';+----+-------------+--------+-------+-------------------------+-------------------------+---------+------+------+-----------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+--------+-------+-------------------------+-------------------------+---------+------+------+-----------------------+| 1 | SIMPLE | staffs | range | index_staffs_nameAgePos | index_staffs_nameAgePos | 74 | NULL | 1 | Using index condition |+----+-------------+--------+-------+-------------------------+-------------------------+---------+------+------+-----------------------+1 row in set (0.00 sec)解决【like ‘%str%’ 】索引失效的问题：覆盖索引
创建表
- 建表 SQL
CREATE TABLE `tbl_user`( `id` INT(11) NOT NULL AUTO_INCREMENT, `name` VARCHAR(20) DEFAULT NULL, `age`INT(11) DEFAULT NULL, `email` VARCHAR(20) DEFAULT NULL, PRIMARY KEY(`id`))ENGINE=INNODB AUTO_INCREMENT=1 DEFAULT CHARSET=utf8;INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('1aa1',21,'a@163.com');INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('2bb2',23,'b@163.com');INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('3cc3',24,'c@163.com');INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('4dd4',26,'d@163.com');- tbl_user 表中的测试数据
mysql> select * from tbl_user;+----+------+------+-----------+| id | name | age | email |+----+------+------+-----------+| 1 | 1aa1 | 21 | a@163.com || 2 | 2bb2 | 23 | b@163.com || 3 | 3cc3 | 24 | c@163.com || 4 | 4dd4 | 26 | d@163.com |+----+------+------+-----------+4 rows in set (0.00 sec)创建索引
- 创建索引的 SQL 指令
CREATE INDEX idx_user_nameAge ON tbl_user(name, age);- 在 tbl_user 表的 name 字段和 age 字段创建联合索引
mysql> CREATE INDEX idx_user_nameAge ON tbl_user(name, age);Query OK, 0 rows affected (0.05 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> SHOW INDEX FROM tbl_user;+----------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+----------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| tbl_user | 0 | PRIMARY | 1 | id | A | 4 | NULL | NULL | | BTREE | | || tbl_user | 1 | idx_user_nameAge | 1 | name | A | 4 | NULL | NULL | YES | BTREE | | || tbl_user | 1 | idx_user_nameAge | 2 | age | A | 4 | NULL | NULL | YES | BTREE | | |+----------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+3 rows in set (0.00 sec)测试覆盖索引
- 如下 SQL 的索引均不会失效：
- 只要查询的字段能和覆盖索引扯得上关系，并且没有多余字段，覆盖索引就不会失效
- 但我就想不通了，id 扯得上啥关系。。。
EXPLAIN SELECT name, age FROM tbl_user WHERE NAME LIKE '%aa%';EXPLAIN SELECT name FROM tbl_user WHERE NAME LIKE '%aa%';EXPLAIN SELECT age FROM tbl_user WHERE NAME LIKE '%aa%';EXPLAIN SELECT id FROM tbl_user WHERE NAME LIKE '%aa%';EXPLAIN SELECT id, name FROM tbl_user WHERE NAME LIKE '%aa%';EXPLAIN SELECT id, age FROM tbl_user WHERE NAME LIKE '%aa%';EXPLAIN SELECT id, name, age FROM tbl_user WHERE NAME LIKE '%aa%';mysql> EXPLAIN SELECT id FROM tbl_user WHERE NAME LIKE '%aa%';+----+-------------+----------+-------+---------------+------------------+---------+------+------+--------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+----------+-------+---------------+------------------+---------+------+------+--------------------------+| 1 | SIMPLE | tbl_user | index | NULL | idx_user_nameAge | 68 | NULL | 4 | Using where; Using index |+----+-------------+----------+-------+---------------+------------------+---------+------+------+--------------------------+1 row in set (0.00 sec)mysql> EXPLAIN SELECT name, age FROM tbl_user WHERE NAME LIKE '%aa%';+----+-------------+----------+-------+---------------+------------------+---------+------+------+--------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+----------+-------+---------------+------------------+---------+------+------+--------------------------+| 1 | SIMPLE | tbl_user | index | NULL | idx_user_nameAge | 68 | NULL | 4 | Using where; Using index |+----+-------------+----------+-------+---------------+------------------+---------+------+------+--------------------------+1 row in set (0.00 sec)- 如下 SQL 的索引均会失效：但凡有多余字段，覆盖索引就会失效
EXPLAIN SELECT * FROM tbl_user WHERE NAME LIKE '%aa%';EXPLAIN SELECT id, name, age, email FROM tbl_user WHERE NAME LIKE '%aa%';12mysql> EXPLAIN SELECT * FROM tbl_user WHERE NAME LIKE '%aa%';+----+-------------+----------+------+---------------+------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+----------+------+---------------+------+---------+------+------+-------------+| 1 | SIMPLE | tbl_user | ALL | NULL | NULL | NULL | NULL | 4 | Using where |+----+-------------+----------+------+---------------+------+---------+------+------+-------------+1 row in set (0.00 sec)mysql> EXPLAIN SELECT id, name, age, email FROM tbl_user WHERE NAME LIKE '%aa%';+----+-------------+----------+------+---------------+------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+----------+------+---------------+------+---------+------+------+-------------+| 1 | SIMPLE | tbl_user | ALL | NULL | NULL | NULL | NULL | 4 | Using where |+----+-------------+----------+------+---------------+------+---------+------+------+-------------+1 row in set (0.00 sec)字符串不加单引号索引失效
- 正常操作，索引没有失效
mysql> SHOW INDEX FROM staffs;+--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| staffs | 0 | PRIMARY | 1 | id | A | 3 | NULL | NULL | | BTREE | | || staffs | 1 | index_staffs_nameAgePos | 1 | name | A | 3 | NULL | NULL | | BTREE | | || staffs | 1 | index_staffs_nameAgePos | 2 | age | A | 3 | NULL | NULL | | BTREE | | || staffs | 1 | index_staffs_nameAgePos | 3 | pos | A | 3 | NULL | NULL | | BTREE | | |+--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+4 rows in set (0.00 sec)mysql> explain select * from staffs where name='2000';+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+-----------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+-----------------------+| 1 | SIMPLE | staffs | ref | index_staffs_nameAgePos | index_staffs_nameAgePos | 74 | const | 1 | Using index condition |+----+-------------+--------+------+-------------------------+-------------------------+---------+-------+------+-----------------------+1 row in set (0.00 sec)- 如果字符串忘记写 ‘’ ，那么 mysql 会为我们进行隐式的类型转换，但凡进行了类型转换，索引都会失效
mysql> explain select * from staffs where name=2000;+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+| 1 | SIMPLE | staffs | ALL | index_staffs_nameAgePos | NULL | NULL | NULL | 3 | Using where |+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+1 row in set (0.00 sec)少用or，用它连接时会索引失效
- 使用 or 连接，会导致索引失效
mysql> SHOW INDEX FROM staffs;+--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| staffs | 0 | PRIMARY | 1 | id | A | 3 | NULL | NULL | | BTREE | | || staffs | 1 | index_staffs_nameAgePos | 1 | name | A | 3 | NULL | NULL | | BTREE | | || staffs | 1 | index_staffs_nameAgePos | 2 | age | A | 3 | NULL | NULL | | BTREE | | || staffs | 1 | index_staffs_nameAgePos | 3 | pos | A | 3 | NULL | NULL | | BTREE | | |+--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+4 rows in set (0.00 sec)mysql> explain select * from staffs where name='z3' or name = 'July';+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+| 1 | SIMPLE | staffs | ALL | index_staffs_nameAgePos | NULL | NULL | NULL | 3 | Using where |+----+-------------+--------+------+-------------------------+------+---------+------+------+-------------+1 row in set (0.00 sec)小练习总结
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-AOYmIkjp-1610526935978)(C:\Users\PePe\AppData\Roaming\Typora\typora-user-images\image-20210113150141190.png)]](https://img-blog.csdnimg.cn/20210113163646992.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3FxXzQzMjg0NDY5,size_16,color_FFFFFF,t_70)
- a是带头大哥，√
- a带头大哥，b中间小弟，√
- a带头大哥，b中间小弟，c索引最后，√
- a带头大哥没了，直接不能引用索引，且使用了or，×
- a带头大哥，b中间小弟没了，c最后小弟，前面a用了索引，c没用，因为中间b断了
- a带头大哥，b为range范围，a能用，b能用，c最后小弟没用，b断了
- a带头大哥，b中间小弟用的like右%，like也相当于range范围，原理跟上面条一样，c最后小弟断了，所以a能用，b能用，c不能用
.
6.2、索引优化面试题
索引优化面试题
创建表
- 建表 SQL
create table test03( id int primary key not null auto_increment, c1 char(10), c2 char(10), c3 char(10), c4 char(10), c5 char(10));insert into test03(c1,c2,c3,c4,c5) values ('a1','a2','a3','a4','a5');insert into test03(c1,c2,c3,c4,c5) values ('b1','b2','b3','b4','b5');insert into test03(c1,c2,c3,c4,c5) values ('c1','c2','c3','c4','c5');insert into test03(c1,c2,c3,c4,c5) values ('d1','d2','d3','d4','d5');insert into test03(c1,c2,c3,c4,c5) values ('e1','e2','e3','e4','e5');create index idx_test03_c1234 on test03(c1,c2,c3,c4);- test03 表中的测试数据
mysql> select * from test03;+----+------+------+------+------+------+| id | c1 | c2 | c3 | c4 | c5 |+----+------+------+------+------+------+| 1 | a1 | a2 | a3 | a4 | a5 || 2 | b1 | b2 | b3 | b4 | b5 || 3 | c1 | c2 | c3 | c4 | c5 || 4 | d1 | d2 | d3 | d4 | d5 || 5 | e1 | e2 | e3 | e4 | e5 |+----+------+------+------+------+------+5 rows in set (0.00 sec)- test03 表中的索引
mysql> SHOW INDEX FROM test03;+--------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+--------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| test03 | 0 | PRIMARY | 1 | id | A | 5 | NULL | NULL | | BTREE | | || test03 | 1 | idx_test03_c1234 | 1 | c1 | A | 5 | NULL | NULL | YES | BTREE | | || test03 | 1 | idx_test03_c1234 | 2 | c2 | A | 5 | NULL | NULL | YES | BTREE | | || test03 | 1 | idx_test03_c1234 | 3 | c3 | A | 5 | NULL | NULL | YES | BTREE | | || test03 | 1 | idx_test03_c1234 | 4 | c4 | A | 5 | NULL | NULL | YES | BTREE | | |+--------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+5 rows in set (0.00 sec)问题：我们创建了复合索引idx_test03_c1234，根据以下SQL分析下索引使用情况？
EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c3='a3' AND c4='a4';- 即全值匹配
mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c3='a3' AND c4='a4';+----+-------------+--------+------+------------------+------------------+---------+-------------------------+------+-----------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+--------+------+------------------+------------------+---------+-------------------------+------+-----------------------+| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 124 | const,const,const,const | 1 | Using index condition |+----+-------------+--------+------+------------------+------------------+---------+-------------------------+------+-----------------------+1 row in set (0.00 sec)EXPLAIN SELECT * FROM test03 WHERE c4='a4' AND c3='a3' AND c2='a2' AND c1='a1';- mysql 优化器进行了优化，所以我们的索引都生效了
mysql> EXPLAIN SELECT * FROM test03 WHERE c4='a4' AND c3='a3' AND c2='a2' AND c1='a1';+----+-------------+--------+------+------------------+------------------+---------+-------------------------+------+-----------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+--------+------+------------------+------------------+---------+-------------------------+------+-----------------------+| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 124 | const,const,const,const | 1 | Using index condition |+----+-------------+--------+------+------------------+------------------+---------+-------------------------+------+-----------------------+1 row in set (0.00 sec)EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c3>'a3' AND c4='a4';- c3 列使用了索引进行排序，并没有进行查找，导致 c4 无法用索引进行查找【
范围之后全失效】
mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c3>'a3' AND c4='a4'; +----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+| 1 | SIMPLE | test03 | range | idx_test03_c1234 | idx_test03_c1234 | 93 | NULL | 1 | Using index condition |+----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+1 row in set (0.00 sec)EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c4>'a4' AND c3='a3';- mysql 优化器进行了优化调整顺序，所以我们的索引都生效了，在 c4 时进行了范围搜索
mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c4>'a4' AND c3='a3'; +----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+| 1 | SIMPLE | test03 | range | idx_test03_c1234 | idx_test03_c1234 | 124 | NULL | 1 | Using index condition |+----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+1 row in set (0.00 sec)EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c4='a4' ORDER BY c3;- c3 列将索引
用于排序，而不是查找，c4 列没有用到索引
mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c4='a4' ORDER BY c3; +----+-------------+--------+------+------------------+------------------+---------+-------------+------+------------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+--------+------+------------------+------------------+---------+-------------+------+------------------------------------+| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 62 | const,const | 1 | Using index condition; Using where |+----+-------------+--------+------+------------------+------------------+---------+-------------+------+------------------------------------+1 row in set (0.00 sec)EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' ORDER BY c3;- 那不就和上面一样的嘛~~~，c4 列都没有用到索引
mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' ORDER BY c3; +----+-------------+--------+------+------------------+------------------+---------+-------------+------+------------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+--------+------+------------------+------------------+---------+-------------+------+------------------------------------+| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 62 | const,const | 1 | Using index condition; Using where |+----+-------------+--------+------+------------------+------------------+---------+-------------+------+------------------------------------+1 row in set (0.00 sec)EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' ORDER BY c4;- 妈耶，因为索引建立的顺序和使用的顺序不一致，导致 mysql 动用了文件排序
- 看到
Using filesort就要知道：此句 SQL必须优化
mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' ORDER BY c4; +----+-------------+--------+------+------------------+------------------+---------+-------------+------+----------------------------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+--------+------+------------------+------------------+---------+-------------+------+----------------------------------------------------+| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 62 | const,const | 1 | Using index condition; Using where; Using filesort |+----+-------------+--------+------+------------------+------------------+---------+-------------+------+----------------------------------------------------+1 row in set (0.00 sec)EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c5='a5' ORDER BY c2, c3;- 只用 c1 一个字段索引，但是c2、c3用于排序，无filesort
- 难道因为排序的时候，c2 紧跟在 c1 之后，
按照索引顺序来，所以就不用 filesort 吗？
mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c5='a5' ORDER BY c2, c3; +----+-------------+--------+------+------------------+------------------+---------+-------+------+------------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+--------+------+------------------+------------------+---------+-------+------+------------------------------------+| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 31 | const | 1 | Using index condition; Using where |+----+-------------+--------+------+------------------+------------------+---------+-------+------+------------------------------------+1 row in set (0.00 sec)EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c5='a5' ORDER BY c3, c2;- 出现了filesort，我们建的索引是1234，它
没有按照索引顺序来，32颠倒了
mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c5='a5' ORDER BY c3, c2; +----+-------------+--------+------+------------------+------------------+---------+-------+------+----------------------------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+--------+------+------------------+------------------+---------+-------+------+----------------------------------------------------+| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 31 | const | 1 | Using index condition; Using where; Using filesort |+----+-------------+--------+------+------------------+------------------+---------+-------+------+----------------------------------------------------+1 row in set (0.00 sec)EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' ORDER BY c2, c3;- 用c1、c2两个字段索引，但是c2、c3用于排序，无filesort
mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' ORDER BY c2, c3; +----+-------------+--------+------+------------------+------------------+---------+-------------+------+------------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+--------+------+------------------+------------------+---------+-------------+------+------------------------------------+| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 62 | const,const | 1 | Using index condition; Using where |+----+-------------+--------+------+------------------+------------------+---------+-------------+------+------------------------------------+1 row in set (0.00 sec)EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c5='a5' ORDER BY c2, c3;- 和 c5 这个坑爹货没啥关系
mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c5='a5' ORDER BY c2, c3; +----+-------------+--------+------+------------------+------------------+---------+-------------+------+------------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+--------+------+------------------+------------------+---------+-------------+------+------------------------------------+| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 62 | const,const | 1 | Using index condition; Using where |+----+-------------+--------+------+------------------+------------------+---------+-------------+------+------------------------------------+1 row in set (0.00 sec)EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c5='a5' ORDER BY c3, c2;- 注意查询条件 c2=‘a2’ ，我都把 c2 查出来了（
c2 为常量），我还给它排序作甚，所以没有产生 filesort
mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2='a2' AND c5='a5' ORDER BY c3, c2; +----+-------------+--------+------+------------------+------------------+---------+-------------+------+------------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+--------+------+------------------+------------------+---------+-------------+------+------------------------------------+| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 62 | const,const | 1 | Using index condition; Using where |+----+-------------+--------+------+------------------+------------------+---------+-------------+------+------------------------------------+1 row in set (0.00 sec)EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c4='a4' GROUP BY c2, c3;- 顺序为 1 2 3 ，没有产生文件排序
mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c4='a4' GROUP BY c2, c3; +----+-------------+--------+------+------------------+------------------+---------+-------+------+------------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+--------+------+------------------+------------------+---------+-------+------+------------------------------------+| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 31 | const | 1 | Using index condition; Using where |+----+-------------+--------+------+------------------+------------------+---------+-------+------+------------------------------------+1 row in set (0.00 sec)EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c4='a4' GROUP BY c3, c2;- group by 表面上叫分组，分组之前必排序，group by 和 order by 在索引上的问题基本是一样的
Using temporary、Using filesort两个都有，我只能说是灭绝师太
mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c4='a4' GROUP BY c3, c2; +----+-------------+--------+------+------------------+------------------+---------+-------+------+---------------------------------------------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+--------+------+------------------+------------------+---------+-------+------+---------------------------------------------------------------------+| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 31 | const | 1 | Using index condition; Using where; Using temporary; Using filesort |+----+-------------+--------+------+------------------+------------------+---------+-------+------+---------------------------------------------------------------------+1 row in set (0.01 sec)- 结论：
group by、order by排序法则几乎一致，他们基本上都需要进行排序，分组之前必排序，但凡使用不当，会有临时表产生- 定值为常量、范围之后失效，最终看排序的顺序
.
6.3、索引失效总结
一般性建议
- 对于单键索引，尽量选择针对当前query过滤性更好的索引
- 在选择组合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠左越好。
- 在选择组合索引的时候，尽量选择可以能包含当前query中的where子句中更多字段的索引
- 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的
![[外链图片转存失败,源站可能有防盗链机制,建议将图片保存下来直接上传(img-HLTXDdzt-1610526935981)(C:\Users\PePe\AppData\Roaming\Typora\typora-user-images\image-20210113162134157.png)]](https://img-blog.csdnimg.cn/2021011316370191.png)
索引优化的总结
- like 后面以常量开头，比如 like ‘kk%’ 和 like ‘k%kk%’ ，可以理解为就是常量
like SQL 实测
- = ‘kk’ ：key_len = 93 ，请记住此参数的值，后面有用
----+-------------+--------+------+------------------+------------------+---------+-------------------+------+-----------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+--------+------+------------------+------------------+---------+-------------------+------+-----------------------+| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 93 | const,const,const | 1 | Using index condition |+----+-------------+--------+------+------------------+------------------+---------+-------------------+------+-----------------------+1 row in set (0.00 sec)- like ‘kk%’：
- key_len = 93 ，和上面一样，说明 c1 c2 c3 都用到了索引
- type = range 表明这是一个范围搜索
mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2 like 'kk%' AND c3='a3';+----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+| 1 | SIMPLE | test03 | range | idx_test03_c1234 | idx_test03_c1234 | 93 | NULL | 1 | Using index condition |+----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+1 row in set (0.00 sec)- like ‘%kk’ 和 like ‘%kk%’ ：key_len = 31 ，表示只有 c1 用到了索引
mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2 like '%kk' AND c3='a3';+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------+| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 31 | const | 1 | Using index condition |+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------+1 row in set (0.00 sec)mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2 like '%kk%' AND c3='a3';+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------+| 1 | SIMPLE | test03 | ref | idx_test03_c1234 | idx_test03_c1234 | 31 | const | 1 | Using index condition |+----+-------------+--------+------+------------------+------------------+---------+-------+------+-----------------------+1 row in set (0.00 sec)- like ‘k%kk%’ ：key_len = 93 ，表示 c1 c2 c3 都用到了索引
mysql> EXPLAIN SELECT * FROM test03 WHERE c1='a1' AND c2 like 'k%kk%' AND c3='a3';+----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+| 1 | SIMPLE | test03 | range | idx_test03_c1234 | idx_test03_c1234 | 93 | NULL | 1 | Using index condition |+----+-------------+--------+-------+------------------+------------------+---------+------+------+-----------------------+1 row in set (0.00 sec)索引优化的总结
总结：
全值匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
Like百分写最右，覆盖索引不写星*；
不等空值还有or，索引失效要少用；
VAR引号不可丢，SQL优化有诀窍！
发表评论
最新留言
哈哈，博客排版真的漂亮呢~
[***.90.31.176]2025年04月14日 06时12分40秒
关于作者
喝酒易醉，品茶养心，人生如梦，品茶悟道，何以解忧？唯有杜康！
-- 愿君每日到此一游！
推荐文章
如何提升员工体验 助力企业业务增长？这个棘手的问题终于被解决了！
2021-05-07
AcWing 828. 模拟栈
2021-05-07
OpenAI Gym简介及初级实例
2021-05-07
Java面向对象
2021-05-07
JAVA带标签的break和continue
2021-05-07
Java获取线程基本信息的方法
2021-05-07
（C++11/14/17学习笔记）：线程启动、结束，创建线程多法、join，detach
2021-05-07
紫书——蛇形填数
2021-05-07
A Guide to Node.js Logging
2021-05-07
webwxbatchgetcontact一个神奇的接口
2021-05-07
【考研英语-基础-简单句】简单句的核心变化_谓语情态
2021-05-07
实现一个简易Vue（三）Compiler
2021-05-07
仿小米商城（上）
2021-05-07
HTML 和 CSS 简单实现注册页面
2021-05-07
（SpringMVC）springMVC.xml 和 web.xml
2021-05-07
jQuery中的动画
2021-05-07
1.2.3 项目、项目集、项目组合以及运营管理之间的关系
2021-05-07
【△重点△】LeetCode - 4. 寻找两个正序数组的中位数——二分查找
2021-05-07
LeetCode - 5. 最长回文子串——字符串、动态规划
2021-05-07
全局锁和表锁 ：给表加个字段怎么有这么多阻碍？
2021-05-07
白红宇的个人博客 - 记录点点滴滴的事 - 您是第 457385556 位访客