MySQL索引学习笔记

最新推荐文章于 2023-03-23 10:54:16 发布

施小赞

最新推荐文章于 2023-03-23 10:54:16 发布

阅读量210

点赞数 1

分类专栏： MySQL 文章标签：索引

本文链接：https://blog.csdn.net/dyangel2013/article/details/87216230

版权

MySQL 专栏收录该内容

11 篇文章 0 订阅

订阅专栏

1、什么是索引

官方定义：索引是帮助MySQL高效获取数据的数据结构。可以得到索引的本质：索引是数据结构
++排好序的快速查找数据结构++
结论：
数据本身之外，数据库还维护者一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引
平常所说的索引，如果没有特别说明，都是指B树（多路搜索树，并一定是二叉的）结构组织的索引。其中聚簇索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种类型的索引之外，还有哈希索引（hash index）等。

优势：
提高数据检索的效率，降低数据库IO成本
降低数据排序的成本，降低了CPU消耗
劣势：
实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以所以列也是要占用空间的
虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息
索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句

mysql索引分类：

1 单值索引即一个索引只包含单个列，一个表可以有多个单列索引
2 唯一索引索引列的值必须唯一，但允许空值
3 复合索引即一个索引包含多个列

基本语法

#创建
CREATE [UNIQUE] INDEX indexName ON mytable(columnname(length));
#如果是CHAR,VARCHAR,length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定length

#更新
ALTER mytable ADD [UNIQUE] INDEX [indexName] ON (columnname(length));

#删除
DROP INDEX[indexName] ON mytable;

#查看
SHOW INDEX FROM table_name\G

更新索引

ALTER TABLE tb_name ADD PRIMARY KEY(column_list);
该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL
ALTER TABLE tb_name ADD UNIQUE index_name(column_list);
这条语句创建索引的值必须是唯一的（除了NULL外，NUll可能会出现多次）
ALTER TABLE tb_name ADD INDEX index_name(column_list);
添加普通索引，索引值可出现多次
ALTER TABLE tb_name ADD FULLTEXT index_name(column_list);
该语句指定了索引为FULLTEXT,用于全文索引

mysql索引结构

BTree索引
Hash索引
full-text全文索引
R-Tree索引

哪些情况需要创建索引

1.主键自动建立唯一索引
2.频繁作为查询条件的字段应该创建索引
3.查询中与其他表关联的字段，外键关系建立索引
4.频繁更新的字段不适合创建索引，因为每次更新不单单是更新了记录还会更新索引，加重了IO负担
5.where条件里用不到的字段不创建索引
6.单键/组合索引的选择问题，who?(在高并发下倾向创建组合索引)
7.查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
8.查询中统计或者分组字段

哪些情况不需要创建索引

1.表记录太少
2.经常增删改的表，虽然索引提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE、DELETE，更新表时，MySQL不仅要保存数据，还要保存一下索引文件
3.数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。注意：如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。

MySQL常见瓶颈

CPU:CPU在饱和的时候一般发生数据装入内存或从磁盘上读取数据时候
IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候
服务器硬件的性能瓶颈：top,free,iostat,vmstat来查看系统的性能状态

2、 Explain

能干吗

表的读取顺序
数据读取操作的操作类型
哪些索引可以使用
哪些索引被实际使用
表之间的引用
每张表有多少行被优化器查询

执行计划包含的信息

mysql> explain select * from tb_emp;
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
|  1 | SIMPLE      | tb_emp | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    8 |   100.00 | NULL  |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set (0.16 sec)

① id
select 查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
三种情况：
id相同，执行顺序由上至下
id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
id相同不同，同时存在：越大的越先被执行，相同的由上至下顺序执行

② select_type
有哪些：

SIMPLE  #简单的select查询，查询中不包含子查询或者UNION
PRIMARY  #查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY
SUBQUERY  #在SELECT或WHERE列表中包含了子查询
DERIVED  #在FROM列表中包含的子查询被标记为DERIVED(衍生)MySQL会递归执行这些子查询，把结果放在临时表里。
UNION  #若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED
UNION RESULT #从UNION表读取结果的SELECT

③ table
显示这一行的数据是关于哪张表的

④ type
访问类型排列
最好到最差依次是：system>const>eq_ref>ref>index>ALL
一般来说，得保证查询至少达到range级别，最好能达到ref

system 表只有一行记录(等于系统表)，这是const类型的特例，平时不会出现，这个也可以忽略不计
const  表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量
eq_ref  唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
ref  非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独值的行， 然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体
range  只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引一般就是在你的where语句中出现了between、<、>、in等查询 这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用全表索引
index  FULL Index Scan,index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）
all  FULL Table Scan 将遍历全表以找到匹配的行

⑤ possible_keys
显示可能应用在这张表的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用

⑥ key
实际使用的索引。如果为NULL，则没有使用索引，查询中若使用了覆盖索引，则该索引和查询的select字段重叠

⑦ key_len
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好。key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即 key_len是根据表定义计算而得，不是通过表内检索出的

⑧ ref
显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值

⑨ rows
根据表统计信息及索引选用情况，大致估算出找到所需记录所需读取的行数

⑩ Extra
包含不适合在其他列中显示但十分重要的额外信息

覆盖索引：就是 select 的数据列只用从索引中能够取得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件，换句话说查找列被所建索引覆盖

Using filesort 说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。  
               MySQL中无法利用索引完成的排序操作称为‘文件排序’  
Using temporary  使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序 order by 和 分组查询 group by。  
Using index      表示相应的 select 操作中使用了覆盖索引，避免访问了表的数据行，效率不错。  
                 如果同时出现 Using where，表明索引被用来执行索引键值的查找。
                 如果没有同时出现 Using where ，表明索引用来读取数据而非执行查找动作。
Using where      表明使用了where过滤  
Using join buffer 使用了连接缓存  
impossible where  where子句的值总是false，不能用来获取任何元祖  
select tables optimized away 在没有group by子句的情况下，基于索引优化MIN/MAX操作或者对于MYISAM存储引擎优化COUNT（*）操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即可完成优化  
distinct 优化distinct操作，在找到第一匹配的元祖后即停止找同样值的动作

单表优化
建表

CREATE TABLE `article` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `author_id` bigint(20) DEFAULT NULL COMMENT '作者id',
  `category_id` bigint(20) DEFAULT NULL COMMENT '分类id',
  `views` int(10) DEFAULT NULL COMMENT '被查看次数',
  `comments` int(10) DEFAULT NULL COMMENT '回帖备注',
  `title` varchar(255) DEFAULT NULL COMMENT '标题',
  `content` text COMMENT '正文内容',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

插入数据

INSERT INTO `data_study`.`article`(`id`, `author_id`, `category_id`, `views`, `comments`, `title`, `content`) VALUES (1, 1, 1, 1, 1, '1', '1');
INSERT INTO `data_study`.`article`(`id`, `author_id`, `category_id`, `views`, `comments`, `title`, `content`) VALUES (2, 2, 2, 2, 2, '2', '2');
INSERT INTO `data_study`.`article`(`id`, `author_id`, `category_id`, `views`, `comments`, `title`, `content`) VALUES (3, 1, 1, 3, 3, '3', '3');

查询 category_id 为 1 且 comment是大于1 的情况下，views最多的article_id

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   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
|  1 | SIMPLE      | article | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    2 |    50.00 | Using where; Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
1 row in set (0.10 sec)

结论：很显然，type是ALL，即最坏的情况。Extra里还出现了Using filesort，也是最坏的情况。优化是必须的。
尝试着建category_id comments views的复合索引

mysql> create index idx_article_ccv on article(category_id,comments,views);
Query OK, 0 rows affected (0.20 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         |           2 | NULL     | NULL   |      | BTREE      |         |               |
| article |          1 | idx_article_ccv |            1 | category_id | A         |           2 | NULL     | NULL   | YES  | BTREE      |         |               |
| article |          1 | idx_article_ccv |            2 | comments    | A         |           2 | NULL     | NULL   | YES  | BTREE      |         |               |
| article |          1 | idx_article_ccv |            3 | views       | A         |           2 | NULL     | NULL   | YES  | BTREE      |         |               |
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.09 sec)
 
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   | partitions | type  | possible_keys   | key             | key_len | ref  | rows | filtered | Extra                                 |
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+---------------------------------------+
|  1 | SIMPLE      | article | NULL       | range | idx_article_ccv | idx_article_ccv | 14      | NULL |    1 |   100.00 | Using index condition; Using filesort |
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+---------------------------------------+
1 row in set (0.10 sec)

依旧存在 Using filesort
comments = 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   | partitions | type | possible_keys   | key             | key_len | ref         | rows | filtered | Extra       |
+----+-------------+---------+------------+------+-----------------+-----------------+---------+-------------+------+----------+-------------+
|  1 | SIMPLE      | article | NULL       | ref  | idx_article_ccv | idx_article_ccv | 14      | const,const |    1 |   100.00 | Using where |
+----+-------------+---------+------------+------+-----------------+-----------------+---------+-------------+------+----------+-------------+
1 row in set (0.10 sec)

尝试着建category_id views的复合索引

mysql> drop index idx_article_ccv on article;
Query OK, 0 rows affected (0.14 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         |           2 | NULL     | NULL   |      | BTREE      |         |               |
+---------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.09 sec)
 
mysql> create index idx_article_cv on article(category_id,views);
Query OK, 0 rows affected (0.15 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
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   | partitions | type | possible_keys  | key            | key_len | ref   | rows | filtered | Extra       |
+----+-------------+---------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | article | NULL       | ref  | idx_article_cv | idx_article_cv | 9       | const |    2 |    50.00 | Using where |
+----+-------------+---------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+
1 row in set (0.10 sec)

两表优化
建表sql

CREATE TABLE `class` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `card` int(10) unsigned NOT NULL COMMENT '分类',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

CREATE TABLE `book` (
  `bookid` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
  `card` int(10) unsigned NOT NULL,
  PRIMARY KEY (`bookid`)
) ENGINE=InnoDB AUTO_INCREMENT=11 DEFAULT CHARSET=utf8;

插入数据

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)));

mysql> explain select * from class left join book on book.card = class.card;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | class | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   20 |   100.00 | NULL                                               |
|  1 | SIMPLE      | book  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   20 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
2 rows in set (0.10 sec)

结论：type有ALL

右表book的card建索引

mysql> create index idx_card on book(card);
Query OK, 0 rows affected (0.22 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 | idx_card |            1 | card        | A         |          11 | NULL     | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.09 sec)
 
mysql> explain select * from class left join book on book.card = class.card;
+----+-------------+-------+------------+------+---------------+----------+---------+-----------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref                   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+----------+---------+-----------------------+------+----------+-------------+
|  1 | SIMPLE      | class | NULL       | ALL  | NULL          | NULL     | NULL    | NULL                  |   20 |   100.00 | NULL        |
|  1 | SIMPLE      | book  | NULL       | ref  | idx_card      | idx_card | 4       | data_study.class.card |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+----------+---------+-----------------------+------+----------+-------------+
2 rows in set (0.10 sec)

左表class的card建索引

mysql> drop index idx_card on book;
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> create index idx_card on class(card);
Query OK, 0 rows affected (0.38 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      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
1 row in set (0.09 sec)
 
mysql> 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         |          20 | NULL     | NULL   |      | BTREE      |         |               |
| class |          1 | idx_card |            1 | card        | A         |          14 | NULL     | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.10 sec)
 
mysql> explain select * from class left join book on book.card = class.card;
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type  | possible_keys | key      | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | class | NULL       | index | NULL          | idx_card | 4       | NULL |   20 |   100.00 | Using index                                        |
|  1 | SIMPLE      | book  | NULL       | ALL   | NULL          | NULL     | NULL    | NULL |   20 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+-------+---------------+----------+---------+------+------+----------+----------------------------------------------------+
2 rows in set (0.09 sec)  

mysql> explain select * from class right join book on book.card = class.card;
+----+-------------+-------+------------+------+---------------+----------+---------+----------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key      | key_len | ref                  | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+----------+---------+----------------------+------+----------+-------------+
|  1 | SIMPLE      | book  | NULL       | ALL  | NULL          | NULL     | NULL    | NULL                 |   20 |   100.00 | NULL        |
|  1 | SIMPLE      | class | NULL       | ref  | idx_card      | idx_card | 4       | data_study.book.card |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+----------+---------+----------------------+------+----------+-------------+
2 rows in set (0.11 sec)

结论：左连接右表加索引
右连接左表加索引

三表优化

建表sql

CREATE TABLE `phone` (
  `phoneid` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `card` int(10) unsigned NOT NULL,
  PRIMARY KEY (`phoneid`)
) ENGINE=InnoDB AUTO_INCREMENT=21 DEFAULT CHARSET=utf8;

插入数据

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)));

#没有任何索引的情况下 
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 | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | class | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   20 |   100.00 | NULL                                               |
|  1 | SIMPLE      | book  | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   20 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
|  1 | SIMPLE      | phone | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   20 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+
3 rows in set (0.05 sec)
 
mysql> create index idx_card on phone(card);
Query OK, 0 rows affected (0.14 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> create index idx_card on book(card);
Query OK, 0 rows affected (0.10 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 | idx_card |            1 | card        | A         |          10 | NULL     | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.05 sec)
 
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 | idx_card |            1 | card        | A         |          11 | NULL     | NULL   |      | BTREE      |         |               |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.05 sec)
 
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 | partitions | type | possible_keys | key      | key_len | ref                   | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+----------+---------+-----------------------+------+----------+-------------+
|  1 | SIMPLE      | class | NULL       | ALL  | NULL          | NULL     | NULL    | NULL                  |   20 |   100.00 | NULL        |
|  1 | SIMPLE      | book  | NULL       | ref  | idx_card      | idx_card | 4       | data_study.class.card |    1 |   100.00 | Using index |
|  1 | SIMPLE      | phone | NULL       | ref  | idx_card      | idx_card | 4       | data_study.book.card  |    2 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+----------+---------+-----------------------+------+----------+-------------+
3 rows in set (0.05 sec)

尽可能减少Join语句中的NestedLoop的循环总次数；“永远用小结果集驱动大的结果集” ，优先优化NestedLoop的内层循环，保证Join语句中被驱动表上Join条件字段已经被索引
；
当无法保证被驱动表的驱动表的Join条件字段被索引且内存资源充足的前提下，不要太吝啬JoinBuffer的设置；

3、索引失效的避免

建表sql

CREATE TABLE `staffs` (
  `id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键id',
  `name` varchar(24) NOT NULL COMMENT '姓名',
  `age` int(11) NOT NULL COMMENT '年龄',
  `pos` varchar(255) DEFAULT NULL COMMENT '职位',
  `add_time` datetime NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT 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());

新建 idx_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         |           2 | NULL     | NULL   |      | BTREE      |         |                           |
| staffs |          1 | idx_staffs_name_age_pos |            1 | name        | A         |           3 | NULL     | NULL   |      | BTREE      |         | Name age pos 复合索引 |
| staffs |          1 | idx_staffs_name_age_pos |            2 | age         | A         |           3 | NULL     | NULL   |      | BTREE      |         | Name age pos 复合索引 |
| staffs |          1 | idx_staffs_name_age_pos |            3 | pos         | A         |           3 | NULL     | NULL   | YES  | BTREE      |         | Name age pos 复合索引 |
+--------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------------------+
4 rows in set (0.36 sec)

索引失效案例
① 全值匹配我最爱
② 最佳左前缀法则：如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
③ 不在索引列上做任何操作（计算，函数，自动或手动）类型转换），会导致索引失效而转向全表扫描
④ 存储引擎不能使用索引中范围条件右边的列 (>,<,in,between and 后面的索引失效)
⑤ 尽量使用覆盖索引（只访问索引的查询（索引列和查询列一直致）），减少select *
⑥ mysql 在使用不等于（!= 或者 <>）的时候无法使用索引会导致全表扫描 [完全失效] (select * 情况下不会走任何索引，但是如果指定 select a,b 并且 a,b建了idx_table_a_b索引，!=字段后面的字段不会用到索引)
⑦ is null,is not null 也无法使用索引 (is null 肯定不会用到索引[完全失效]，is not null ： select * 不会用到索引，但是 select a,b from table where a is not null 存在索引 idx_table_a_b的情况下却会用到整个索引)
⑧ like 以通配符开头（’%abc…’）MySQL索引失效会变成全表扫描操作[断裂索引，其本身就没用到索引]：
如何解决like ‘%字符窜%’ 时索引不被使用的方法?
覆盖索引 (select a from table where a like ‘%…%’ 会用到 idx_table_a 的索引) 查的字段与建的索引字段一致

⑨ 字符串不加单引号索引失效
⑩ 少用or,用它来连接时会索引失效

假设索引 idx_table_a_b_c

where语句	索引是否被使用
where a=3	用到索引a
where a=3 and b=5	用到索引a b
where a=3 and b=5 and c=4	用到索引a b c
where b=3 或者 where b=3 and c=4 或者 where c=4	没用到索引
where a=3 and c=5	用到索引a
where a=3 and b>4 and c=5	用到索引a b
where a=3 and b like ‘kk%’ and c=4	用到索引a b c
where a=3 and b like ‘%kk’ and c=4	用到索引a
where a=3 and b like ‘%kk%’ and c=4	用到索引a
where a=3 and b like ‘k%kk%’ and c=4	用到索引a b c

习题：
建表sql

CREATE TABLE `test` (
  `id` bigint(20) NOT NULL AUTO_INCREMENT,
  `c1` varchar(255) DEFAULT NULL,
  `c2` varchar(255) DEFAULT NULL,
  `c3` varchar(255) DEFAULT NULL,
  `c4` varchar(255) DEFAULT NULL,
  `c5` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=6 DEFAULT CHARSET=utf8;

INSERT INTO `data_study`.`test`(`id`, `c1`, `c2`, `c3`, `c4`, `c5`) VALUES (1, 'a1', 'a2', 'a3', 'a4', 'a5');
INSERT INTO `data_study`.`test`(`id`, `c1`, `c2`, `c3`, `c4`, `c5`) VALUES (2, 'b1', 'b2', 'b3', 'b4', 'b5');
INSERT INTO `data_study`.`test`(`id`, `c1`, `c2`, `c3`, `c4`, `c5`) VALUES (3, 'c1', 'c2', 'c3', 'c4', 'c5');
INSERT INTO `data_study`.`test`(`id`, `c1`, `c2`, `c3`, `c4`, `c5`) VALUES (4, 'd1', 'd2', 'd3', 'd4', 'd5');
INSERT INTO `data_study`.`test`(`id`, `c1`, `c2`, `c3`, `c4`, `c5`) VALUES (5, 'e1', 'e2', 'e3', 'e4', 'e5');

建立 idx_test_c1234 索引

mysql> create index idx_test_c1234 on test(c1,c2,c3,c4);
Query OK, 0 rows affected (0.19 sec)
Records: 0  Duplicates: 0  Warnings: 0
 
mysql> show index from test;
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table | Non_unique | Key_name       | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| test  |          0 | PRIMARY        |            1 | id          | A         |           4 | NULL     | NULL   |      | BTREE      |         |               |
| test  |          1 | idx_test_c1234 |            1 | c1          | A         |           5 | NULL     | NULL   | YES  | BTREE      |         |               |
| test  |          1 | idx_test_c1234 |            2 | c2          | A         |           5 | NULL     | NULL   | YES  | BTREE      |         |               |
| test  |          1 | idx_test_c1234 |            3 | c3          | A         |           5 | NULL     | NULL   | YES  | BTREE      |         |               |
| test  |          1 | idx_test_c1234 |            4 | c4          | A         |           5 | NULL     | NULL   | YES  | BTREE      |         |               |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
5 rows in set (0.08 sec)

问题：我们创建了复合索引idx_test_c1234 根据以下sql分析索引使用情况？

1️⃣

mysql> explain select * from test where c1='a1';
+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys  | key            | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | test  | NULL       | ref  | idx_test_c1234 | idx_test_c1234 | 768     | const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+-------+
1 row in set (27.12 sec)
 
mysql> explain select * from test where c1='a1' and c2='a2';
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys  | key            | key_len | ref         | rows | filtered | Extra |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | test  | NULL       | ref  | idx_test_c1234 | idx_test_c1234 | 1536    | const,const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------+------+----------+-------+
1 row in set (0.09 sec)
 
mysql> explain select * from test where c1='a1' and c2='a2' and c3='a3';
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys  | key            | key_len | ref               | rows | filtered | Extra |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | test  | NULL       | ref  | idx_test_c1234 | idx_test_c1234 | 2304    | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------------+------+----------+-------+
1 row in set (0.08 sec)
 
mysql> explain select * from test where c1='a1' and c2='a2' and c3='a3' and c4='a4';
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys  | key            | key_len | ref                     | rows | filtered | Extra |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------------------+------+----------+-------+
|  1 | SIMPLE      | test  | NULL       | ref  | idx_test_c1234 | idx_test_c1234 | 3072    | const,const,const,const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------------------+------+----------+-------+
1 row in set (0.08 sec)

2️⃣跟顺序无关

mysql> explain select * from test where c1='a1' and c2='a2' and c4='a4' and c3='a3';
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys  | key            | key_len | ref                     | rows | filtered | Extra |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------------------+------+----------+-------+
|  1 | SIMPLE      | test  | NULL       | ref  | idx_test_c1234 | idx_test_c1234 | 3072    | const,const,const,const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------------------+------+----------+-------+
1 row in set (0.08 sec)  

mysql> explain select * from test where c4='a4' and c3='a3' and c2='a2' and c1='a1';
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys  | key            | key_len | ref                     | rows | filtered | Extra |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------------------+------+----------+-------+
|  1 | SIMPLE      | test  | NULL       | ref  | idx_test_c1234 | idx_test_c1234 | 3072    | const,const,const,const |    1 |   100.00 | NULL  |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------------------+------+----------+-------+
1 row in set (0.08 sec)

3️⃣范围之后索引失效

mysql> explain select * from test where c1='a1' and c2='a2' and c3>'a3' and c4='a4';
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys  | key            | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | test  | NULL       | range | idx_test_c1234 | idx_test_c1234 | 2304    | NULL |    1 |    20.00 | Using index condition |
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+
1 row in set (0.08 sec)

4️⃣索引全部使用到了

mysql> explain select * from test where c1='a1' and c2='a2' and c4>'a4' and c3='a3';
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type  | possible_keys  | key            | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | test  | NULL       | range | idx_test_c1234 | idx_test_c1234 | 3072    | NULL |    1 |   100.00 | Using index condition |
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+-----------------------+
1 row in set (18.81 sec)

5️⃣排序字段后面的字段未用到索引，c3用在了排序上，没有体现在key_len上

mysql> explain select * from test where c1='a1' and c2='a2' and c4='a4' order by c3;
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys  | key            | key_len | ref         | rows | filtered | Extra                 |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------+------+----------+-----------------------+
|  1 | SIMPLE      | test  | NULL       | ref  | idx_test_c1234 | idx_test_c1234 | 1536    | const,const |    1 |    20.00 | Using index condition |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------+------+----------+-----------------------+
1 row in set (0.28 sec)  


mysql> explain select * from test where c1='a1' and c2='a2' order by c3;
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys  | key            | key_len | ref         | rows | filtered | Extra                 |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------+------+----------+-----------------------+
|  1 | SIMPLE      | test  | NULL       | ref  | idx_test_c1234 | idx_test_c1234 | 1536    | const,const |    1 |   100.00 | Using index condition |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------+------+----------+-----------------------+
1 row in set (58.57 sec)

6️⃣出现了Using filesort

mysql> explain select * from test where c1='a1' and c2='a2' order by c4;
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys  | key            | key_len | ref         | rows | filtered | Extra                                 |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------+------+----------+---------------------------------------+
|  1 | SIMPLE      | test  | NULL       | ref  | idx_test_c1234 | idx_test_c1234 | 1536    | const,const |    1 |   100.00 | Using index condition; Using filesort |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------+------+----------+---------------------------------------+
1 row in set (0.09 sec)

7️⃣c1用到了索引，c2 c3用在了排序上，没有 filesort

mysql> explain select * from test where c1='a1' and c5='a5' order by c2,c3;
+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+------------------------------------+
| id | select_type | table | partitions | type | possible_keys  | key            | key_len | ref   | rows | filtered | Extra                              |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+------------------------------------+
|  1 | SIMPLE      | test  | NULL       | ref  | idx_test_c1234 | idx_test_c1234 | 768     | const |    1 |    20.00 | Using index condition; Using where |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+------------------------------------+
1 row in set (19.32 sec)

8️⃣又出现了filesort

mysql> explain select * from test where c1='a1' and c5='a5' order by c3,c2;
+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys  | key            | key_len | ref   | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | test  | NULL       | ref  | idx_test_c1234 | idx_test_c1234 | 768     | const |    1 |    20.00 | Using index condition; Using where; Using filesort |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+----------------------------------------------------+
1 row in set (20.92 sec)

9️⃣ c1 c2索引查找 c2 c3 排序没有filesort

mysql> explain select * from test where c1='a1' and c2='a2' order by c2,c3;
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys  | key            | key_len | ref         | rows | filtered | Extra                 |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------+------+----------+-----------------------+
|  1 | SIMPLE      | test  | NULL       | ref  | idx_test_c1234 | idx_test_c1234 | 1536    | const,const |    1 |   100.00 | Using index condition |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------+------+----------+-----------------------+
1 row in set (22.45 sec)  


mysql> explain select * from test where c1='a1' and c2='a2' and c5='a5' order by c2,c3;
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------+------+----------+------------------------------------+
| id | select_type | table | partitions | type | possible_keys  | key            | key_len | ref         | rows | filtered | Extra                              |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------+------+----------+------------------------------------+
|  1 | SIMPLE      | test  | NULL       | ref  | idx_test_c1234 | idx_test_c1234 | 1536    | const,const |    1 |    20.00 | Using index condition; Using where |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------+------+----------+------------------------------------+
1 row in set (0.09 sec)

c2是常量，已经不需要排序，所以实际索引了c1 c2 ，c3进行排序，没有filesort

mysql> explain select * from test where c1='a1' and c2='a2' and c5='a5' order by c3,c2;
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------+------+----------+------------------------------------+
| id | select_type | table | partitions | type | possible_keys  | key            | key_len | ref         | rows | filtered | Extra                              |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------+------+----------+------------------------------------+
|  1 | SIMPLE      | test  | NULL       | ref  | idx_test_c1234 | idx_test_c1234 | 1536    | const,const |    1 |    20.00 | Using index condition; Using where |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------------+------+----------+------------------------------------+
1 row in set (0.09 sec)

group by

没有出现 filesort

mysql> explain select c2,c3 from test where c1='a1' and c4='a4' group by c2,c3;
+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+--------------------------+
| id | select_type | table | partitions | type | possible_keys  | key            | key_len | ref   | rows | filtered | Extra                    |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+--------------------------+
|  1 | SIMPLE      | test  | NULL       | ref  | idx_test_c1234 | idx_test_c1234 | 768     | const |    1 |    20.00 | Using where; Using index |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+--------------------------+
1 row in set (0.17 sec)

出现temporary， filesort

mysql> explain select c2,c3 from test where c1='a1' and c4='a4' group by c3,c2;
+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+-----------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys  | key            | key_len | ref   | rows | filtered | Extra                                                     |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+-----------------------------------------------------------+
|  1 | SIMPLE      | test  | NULL       | ref  | idx_test_c1234 | idx_test_c1234 | 768     | const |    1 |    20.00 | Using where; Using index; Using temporary; Using filesort |
+----+-------------+-------+------------+------+----------------+----------------+---------+-------+------+----------+-----------------------------------------------------------+
1 row in set (0.16 sec)

一般性建议：
对于单键索引，尽量选择针对当前query过滤性更好的索引
在选择组合索引的时候，当前query中过滤性最好的字段在索引字顺序中，位置越靠前越好
在选择组合索引的时候，尽量选择可以能够包含当前query中的where字段中更多字段的索引
尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的

施小赞

关注

1
点赞
踩
0

收藏

觉得还不错? 一键收藏
0
评论
MySQL索引学习笔记

1、什么是索引官方定义：索引是帮助MySQL高效获取数据的数据结构。可以得到索引的本质：索引是数据结构++排好序的快速查找数据结构++结论：数据本身之外，数据库还维护者一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引平常所说的索引，如果没有特别说明，都是指B树（多路搜索树，并一定是二叉的）结构组织的...
复制链接

扫一扫