mysql or 创建索引_Mysql索引优化
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，也是最壞的情況。
優化是必須的。
開始優化：新建索引
創建索引的 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: 0
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 | | |
| 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: 0
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)
再次創建索引
創建索引的 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: 0
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 | | |
| 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也消失了，結果非常理想
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 | 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: 0
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.01 sec)
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 指令
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: 0
mysql> 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 字段上建立索引
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 表中
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: 0
mysql> 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: 0
mysql> 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 循環
1、盡可能減少Join語句中的NestedLoop的循環總次數；
2、永遠用小結果集驅動大的結果集(在大結果集中建立索引，在小結果集中遍歷全表)；
3、優先優化NestedLoop的內層循環；
4、保證Join語句中被驅動表上Join條件字段已經被索引；
5、當無法保證被驅動表的Join條件字段被索引且內存資源充足的前提下，不要太吝惜JoinBuffer的設置；
我的理解
1、使用小表驅動大表，這就相當于外層 for 循環的次數少，內層 for 循環的次數多
2、然后我們在大表中建立了索引，這樣內層 for 循環的效率明顯提高
3、綜上，使用小表驅動大表，在大表中建立了索引
索引失效
索引失效(應該避免)
創建表
建表 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)
1、索引失效準則
1、全值匹配我最愛
2、最佳左前綴法則：如果索引了多例，要遵守最左前綴法則。指的是查詢從索引的最左前列開始并且不跳過索引中的列。
3、不在索引列上做任何操作(計算、函數、(自動or手動)類型轉換)，會導致索引失效而轉向全表掃描
4、存儲引擎不能使用索引中范圍條件右邊的列
5、盡量使用覆蓋索引(只訪問索引的查詢(索引列和查詢列一致))，減少select *
6、mysql在使用不等于(!=或者<>)的時候無法使用索引會導致全表掃描
7、is null，is not null 也無法使用索引(早期版本不能走索引，后續版本應該優化過，可以走索引)
8、like以通配符開頭(’%abc…’)mysql索引失效會變成全表掃描操作
9、字符串不加單引號索引失效
10、少用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 表示索引失效
mysql> 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: 0
mysql> 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%';
mysql> 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)
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 基本上都需要進行排序，但凡使用不當，會有臨時表產生
定值為常量、范圍之后失效，最終看排序的順序
3、索引失效總結
一般性建議
對于單鍵索引，盡量選擇針對當前query過濾性更好的索引
在選擇組合索引的時候，當前query中過濾性最好的字段在索引字段順序中，位置越靠左越好。
在選擇組合索引的時候，盡量選擇可以能包含當前query中的where子句中更多字段的索引
盡可能通過分析統計信息和調整query的寫法來達到選擇合適索引的目的
索引優化的總結
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 優化有訣竅。
分組之前必排序 如果 索引順序不對 會產生 臨時表和文件排序
創作挑戰賽新人創作獎勵來咯，堅持創作打卡瓜分現金大獎總結
以上是生活随笔為你收集整理的mysql or 创建索引_Mysql索引优化的全部內容，希望文章能夠幫你解決所遇到的問題。
- 上一篇： java swing 面试题_下面有关J
- 下一篇： mysql脚本的制作_制作脚本实现mys