三、索引优化分析（下）
4. 性能分析
4.1 MySQL 常见性能瓶颈
① CPU：CPU 在满负荷运行一般发生在数据装入到内存或从磁盘读取数据时；
② IO：磁盘 IO 瓶颈发生在装入数据远大于内存容量时；
③ 服务器硬件瓶颈：通过 top、free、iostat、vmstat 命令查看系统的性能和状态；
④ 数据库服务器配置问题；
4.2 MySQL 性能分析
4.2.1 EXPLAIN 简介
使用 EXPLAIN 关键字可以模拟 MySQL 优化器执行 SQL 查询语句，从而查看 MySQL 是如何理解你的 SQL 语句，用于分析 SQL 查询语句或者表结构的性能瓶颈。
类似于医院检查的化验报告单。
作用：
① 获取表的读取顺序；
② 获得数据读取操作的操作类型；
③ 查看那些索引可以使用；
④ 查看哪些索引实际被使用；
⑤ 查看表之间的引用关系；
⑥ 查看每张表有多少行被优化器查询；
4.2.2 使用方法：
EXPLAIN + SQL 语句
示例：
mysql> explain select * from tbl_emp where id = 2;+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
| 1 | SIMPLE | tbl_emp | const | PRIMARY | PRIMARY | 4 | const | 1 | |
+----+-------------+---------+-------+---------------+---------+---------+-------+------+-------+
1 row in set (0.00 sec)
EXPLAIN 执行后返回的信息列：
id：查询的序列号，包含一组数字，表示查询中执行SELECT子句或操作表的顺序；select_type：表示查询的类型；table：表示数据所在的表；type：查询的访问类型；possible_keys：显示可能在这张表中的索引，一个或多个；key：实际使用到的索引；key_len：表示索引中使用的字节数，可以通过该列计算查询中使用的索引长度ref：显示索引的那一列被使用；rows：显示 MySQL 认为执行查询时必须检查的行数；Extra：其他的额外重要的信息；
4.2.2 各分析字段解释
使用以下 SQL 语句创建数据表
CREATE TABLE t1(id INT(10) AUTO_INCREMENT,content VARCHAR(100) NULL , PRIMARY KEY (id));CREATE TABLE t2(id INT(10) AUTO_INCREMENT,content VARCHAR(100) NULL , PRIMARY KEY (id));
CREATE TABLE t3(id INT(10) AUTO_INCREMENT,content VARCHAR(100) NULL , PRIMARY KEY (id));
CREATE TABLE t4(id INT(10) AUTO_INCREMENT,content VARCHAR(100) NULL , PRIMARY KEY (id));
INSERT INTO t1(content) VALUES(CONCAT("t1_",FLOOR(1+RAND()*1000)));
INSERT INTO t2(content) VALUES(CONCAT("t2_",FLOOR(1+RAND()*1000)));
INSERT INTO t3(content) VALUES(CONCAT("t3_",FLOOR(1+RAND()*1000)));
INSERT INTO t4(content) VALUES(CONCAT("t4_",FLOOR(1+RAND()*1000)));
4.2.2.1 id
查询的序列号，包含一组数字，表示查询中执行 SELECT 子句或操作表的顺序；
主要分为三种情况：
① id 相同，执行顺序由上至下；
mysql> explain select * from t1,t2,t3 where t1.id = t2.id and t2.id = t3.id;+----+-------------+-------+--------+---------------+---------+---------+-----------------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+--------+---------------+---------+---------+-----------------+------+-------+
| 1 | SIMPLE | t1 | ALL | PRIMARY | NULL | NULL | NULL | 1 | |
| 1 | SIMPLE | t2 | eq_ref | PRIMARY | PRIMARY | 4 | base_crud.t1.id | 1 | |
| 1 | SIMPLE | t3 | eq_ref | PRIMARY | PRIMARY | 4 | base_crud.t1.id | 1 | |
+----+-------------+-------+--------+---------------+---------+---------+-----------------+------+-------+
3 rows in set (0.00 sec)
② id 不同，如果是子查询，id 的序号会递增，id 值越大优先级越高，越先被执行；
mysql> explain select t1.id from t1 where t1.id in (select t2.id from t2 where t2.id in (select t3.id from t3 where t3.content = ""));+----+--------------------+-------+-----------------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+-----------------+---------------+---------+---------+------+------+--------------------------+
| 1 | PRIMARY | t1 | index | NULL | PRIMARY | 4 | NULL | 1 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | t2 | unique_subquery | PRIMARY | PRIMARY | 4 | func | 1 | Using index; Using where |
| 3 | DEPENDENT SUBQUERY | t3 | unique_subquery | PRIMARY | PRIMARY | 4 | func | 1 | Using where |
+----+--------------------+-------+-----------------+---------------+---------+---------+------+------+--------------------------+
3 rows in set (0.00 sec)
③ id 有相同的也有不同的，id 如果相同，可以认为是一组的，从上往下执行；所在组中 id 值越大，优先级越高
mysql> explain select t2.* from t2, (select t3.* from t3) s3 where s3.id = t2.id;+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
| 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | |
| 1 | PRIMARY | t2 | const | PRIMARY | PRIMARY | 4 | const | 1 | |
| 2 | DERIVED | t3 | ALL | NULL | NULL | NULL | NULL | 1 | |
+----+-------------+------------+--------+---------------+---------+---------+-------+------+-------+
3 rows in set (0.00 sec)
总结：每个 id 号码。表示一个独立的查询，SQL 的查询行数越少越好
4.2.2.2 select_type
表示查询的类型，主要用于区分普通查询、联合查询、子查询等复杂查询。
SIMPLE
简单的 SELECT 查询，查询中不包含子查询或者 UNION
PRIMARY
查询中若包含复杂查询的子部分，最外层查询则标记为 PAIMARY
DERIVED
在 FROM 列表中包含的子查询被标记为 DERIVED （衍生）MySQL 会递归执行这些子查询，把结果放到临时表中。
SUBQUERY
在 SELECT 或 WHERE 列表中包含了子查询。
DEPEDENT SUBQUERY
在 SELECT 或 WHERE 列表中包含了子查询，子查询基于外层。
UNCACHEABLE SUBQUERY
无法使用缓存的子查询。
UNION
若第二个 SELECT 出现在 UNION 之后，则被标记为 UNION；若 UNION 包含在 FROM 子句的子查询中，外层 SELECT 将被标记为 DERIVED
UNION RESULT
从 UNION 表获取结果的 SELECT
① SIMPLE：表示单表查询
mysql> explain select * from t1;+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| 1 | SIMPLE | t1 | ALL | NULL | NULL | NULL | NULL | 1 | |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set (0.00 sec)
② PRIMARY：查询中 若包含任何复杂的子部分，最外层查询则会被标记为 PRIMARY
mysql> explain select * from (select * from t2) a;+----+-------------+------------+--------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------+------+---------+------+------+-------+
| 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | |
| 2 | DERIVED | t2 | ALL | NULL | NULL | NULL | NULL | 1 | |
+----+-------------+------------+--------+---------------+------+---------+------+------+-------+
2 rows in set (0.00 sec)
③ DERIVED：在 FROM 列表中包含的子查询被标记为 DERIVED（衍生）MySQL 会递归执行这些子查询，把结果放大临时表中。
mysql> explain select * from (select * from t2) a;
+----+-------------+------------+--------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+--------+---------------+------+---------+------+------+-------+
| 1 | PRIMARY | <derived2> | system | NULL | NULL | NULL | NULL | 1 | |
| 2 | DERIVED | t2 | ALL | NULL | NULL | NULL | NULL | 1 | |
+----+-------------+------------+--------+---------------+------+---------+------+------+-------+
2 rows in set (0.00 sec)
④ SUBQUERY：SELECT 或 WHERE 列表中包含子查询
mysql> EXPLAIN SELECT t2.id FROM t2 WHERE t2.id = (SELECT t3.id FROM t3 WHERE t3.id = 1);+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
| 1 | PRIMARY | t2 | const | PRIMARY | PRIMARY | 4 | const | 1 | Using index |
| 2 | SUBQUERY | t3 | const | PRIMARY | PRIMARY | 4 | | 1 | Using index |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+-------------+
2 rows in set (0.00 sec)
⑤ DEPEDENT SUBQUERY：在 SELECT 或 WHERE 列表中包含子查询，子查询基于外层。
mysql> explain select t2.id from t2 where t2.id in (select t3.id from t3 where t3.content = "t3_993");+----+--------------------+-------+-----------------+---------------+---------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------------+-------+-----------------+---------------+---------+---------+------+------+--------------------------+
| 1 | PRIMARY | t2 | index | NULL | PRIMARY | 4 | NULL | 1 | Using where; Using index |
| 2 | DEPENDENT SUBQUERY | t3 | unique_subquery | PRIMARY | PRIMARY | 4 | func | 1 | Using where |
+----+--------------------+-------+-----------------+---------------+---------+---------+------+------+--------------------------+
2 rows in set (0.00 sec)
都在
WHERE后加入子查询，SUPERQUERY是单个值，DEPENDENT SUBQUERY是一组值。
⑥ UNCACHEABLE SUBQUERY：无法使用缓存的子查询。
mysql> explain select * from t3 where id = (select id from t2 where t2.id = @@sort_buffer_size);+----+----------------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+----------------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
| 1 | PRIMARY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE noticed after reading const tables |
| 2 | UNCACHEABLE SUBQUERY | NULL | NULL | NULL | NULL | NULL | NULL | NULL | no matching row in const table |
+----+----------------------+-------+------+---------------+------+---------+------+------+-----------------------------------------------------+
2 rows in set (0.01 sec)
当使用
@@来引用系统变量时，不会使用缓存。
⑦ UNION：若第二个 SELECT 出现在 UNION 后，则被标记为 UNION；若 UNION 包含在 FROM 子句的子查询中，外层 SELECT 将被标记为 DERIVED
mysql> explain select * from t1 union all select * from t2;+----+--------------+------------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+--------------+------------+------+---------------+------+---------+------+------+-------+
| 1 | PRIMARY | t1 | ALL | NULL | NULL | NULL | NULL | 1 | |
| 2 | UNION | t2 | ALL | NULL | NULL | NULL | NULL | 1 | |
| NULL | UNION RESULT | <union1,2> | ALL | NULL | NULL | NULL | NULL | NULL | |
+----+--------------+------------+------+---------------+------+---------+------+------+-------+
3 rows in set (0.00 sec)
⑧ UNION RESULT：从 UNION 表获取结果的 SELECT
4.2.2.3 table
表示数据所在的表
4.2.2.4 type
查询的访问类型；
system
表只有一行记录（等于系统表）是 const 类型的特例，一般不会出现。
const
表示通过索引一次就找到，const 用于比较 primary key 或者 unique 索引。
eq_ref
唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或者唯一索引扫描。
ref
非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，也可能会找到多个符合条件的行，所以它属于查找和扫描的混合体。
range
只检索指定范围的行，使用一个索引来选择行。key 列显示使用了那个索引一般就在 WHERE 语句中出现了 between、<、>、in 等的查询这种范围扫描比全表扫描要好，因为它只需要开始于索引的某一点，结束于另一点，不需要扫描全部索引。
index
出现 index 是 SQL 语句使用了索引但是没使用索引进行过滤，一般是使用了覆盖索引或者利用索引进行了排序分组。
ALL
全表扫描以找到匹配的行
index_merge
在查询中需要用到多个索引组合使用，通常出现在有 OR 关键字的 SQL 中。
ref_or_null
对某个字段即需要关联条件，也需要 null 值的情况下，查询优化器会选择用 ref_or_null 连接查询。
index_subquery
利用索引关联子查询，不再全表扫描。
unique_subquery
类似于 index_subquery 子查询中的唯一索引。
查询的访问类型，是比较重要的一个指标，结果值从最好好到最坏依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merege > unique_subquery > index_subquery > range > index > ALL一般而言要保证查询至少达到
range级别，最好能达到ref
index和ALL的区别：虽然
index和ALL都是读全表，但是index是从索引中读取的，只遍历索引树，而ALL是从硬盘中读取的，相比较而言索引文件通常比数据文件小，因此index比ALL更快。
4.2.2.5 possible_keys
显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引被列出，但不一定被查询实际使用。
4.2.2.6 key
实际使用到的索引，如果为 NULL 则表示没有使用索引；
查询中若使用了覆盖索引，则该索引仅出现在 key 列表中；
4.2.2.7 key_len
表示索引中使用的字节数，可以通过该列计算查询中使用的索引长度。key_len 字段能帮助检查是否充分利用索引。
key_len越长说明索引使用越充分，即使用的索引数越多，匹配越精确；在不损失精度的情况下，长度越短越好；
key_len 的计算方式：
① 首先查看索引上字段的类型和长度，比如：int = 4; varchar(20) = 20; char(20) = 20；
② varchar、char 这种字符串字段，不同的字符集要乘以不同的值，如：utf8 要乘以 3，GBK 要乘以 2；
③ varchar 这样的动态字符要加 2 个字节；
④ 允许为空的字段要加 1 个字节；
key_lenint
4 + 1
允许 NUll，加 1 个字节
int not null
4
不允许为 NULL
char(30) utf8
30 * 3 + 1
允许为 NULL
varchar(30) not null utf8
30 * 3 + 2
动态列类型，加 2 个字节
varchar(30) utf8
30 * 3 + 2 + 1
动态列类型，加两个字节；允许为 NULL 再加一个字节
text(10) utf8
30 * 3 + 3 + 1
text 截取部分，被视作动态列类型，且允许为 NULL
4.2.2.8 ref
显示索引的那一列被使用，如果可能的话是个 const（常量）。那些列或常数被用于查找索引列上的值。
4.2.2.9 rows
根据表统计信息和索引选用情况，大致估算出找到所需记录所需读取的行数，行数越少越好。
4.2.2.10 Extra
包含不适合在其他列中显示但是十分重要的额外信息。
① Using filesort
MySQL 会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL 无法利用索引完成的排序操作称为**“文件排序”**
查询中排序的字段，排序字段若是通过索引去访问将大大提高排序速度。
② Using temporary
使用了临时表保存中间结果，MySQL 在对查询结果排序时使用临时表。常见于排序 ORDER BY 和分组查询 GROUP BY
排序
ORDER BY和分组查询GROUP BY是拖慢 SQL 执行的元凶，临时表会增加 SQL 负担。
③ Using index
表示相应的 SELECT 操作中使用了覆盖索引（Covering Index）避免访问了表的数据行，效率不错。如果同时出现 Using where，表明索引被用来执行索引键值的查找；如果没有同时出现 Using where，表明索引只是用来读取数据而非利用索引执行查找。利用索引进行了排序或分组。
覆盖索引（Covering Index）：
SELECT的数据列只用从索引中就能获取，不需要读取数据行，MySQL 可以利用索引返回SELECT列表中的字段，而不需要根据索引再次读取数据文件，也就是说查询列要被所建立的索引覆盖。- 索引是高效找到行的一个方法，但是一般数据库也能够使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了他们索引的数据；当能通过索引就可以得到需要的数据，就不需要读取行了。索引包含了（或者覆盖了）满足查询结果的数据就叫覆盖索引；
- 简单的讲覆盖索引就是
SELECT中查询的字段和顺序与建立的复合索引顺序和字段刚好是一样的；注意：
如果要使用覆盖索引，一定要注意
SELECT列表中取出需要的列，不能使用SELECT *，因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降。
Using filesort：采用文件排序，影响 SQL 执行效率；
Using temporary：使用了临时表，严重影响 SQL 执行效率；
Using index：使用了覆盖索引，效率不错；以上三个参数是判断 SQL 执行效率的基本方法。
④ Using where
表明使用了 WHERE 过滤。
⑤ Using join buffer
表明使用了连接缓存。
⑥ impossible where
WHERE 子句的值总是 false，不能用来获取任何元组。
⑦ select tables optimized away
在没有 GROUP BY 子句的情况下，基于索引优化 MIN/MAX 操作或者对于 MyISAM 存储引擎优化 COUNT(*) 操作，不必等到执行阶段再进行计算，查询执行计划生成阶段即完成优化。
⑧ distinct
优化 distinct，再找到第一匹配元组后即停止查找同样值的工作。
5. 索引优化
5.1 索引分析
5.1.1 单表优化分析
① 创建数据表
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` VARBINARY(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"),
(3,3,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 | 3 | 3 | 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 |
+----+-----------+
| 1 | 1 |
+----+-----------+
1 row in set (0.00 sec)
③ 分析优化 SQL
SQL 查询中存在的问题：
- SQL 进行了全表扫描；
- 没有也没有使用索引；
- 使用了文件排序效率低下；
④ 解决方案
创建索引
create index idx_article_ccv on article(category_id, comments, views);alter table `article` add index idx_article_ccv(`category_id`,`comments`,`views`)
查看索引
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)
查看性能
优化后可以看到查询已经使用了索引，但是依旧存在文件排序问题。
建立了索引但是没有完全使用的原因：
根据B-Tree 索引的工作原理
① 先排序
category_id；② 如果遇到相同的
category_id则再排序comments；③ 如果遇到相同的
comments则再排序views；④ 当
comments处于联合索引中间位置时，因comments >= 1条件是范围值（即为range）⑤ MySQL 无法再利用索引后面的
views部分进行检索；⑥ 最终 MySQL 使用的索引类型是
range，后面的索引失效；
优化索引
# 删除不合适的索引mysql> drop index idx_article_ccv on article;
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 创建新的索引
mysql> create index idx_article_cv on article(category_id, views);
Query OK, 0 rows affected (0.02 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_cv | 1 | category_id | A | 3 | NULL | NULL | | BTREE | | |
| article | 1 | idx_article_cv | 2 | views | A | 3 | NULL | NULL | | BTREE | | |
+---------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.00 sec)
查看性能
使用新索引后
type类型变成了ref，使用了索引idx_article_cv且两个索列都被使用，而且不会出现文件排序问题，SQL 性能很好。
5.1.2 两表优化分析
两表关联索引加在哪里？
① 创建数据表
CREATE TABLE IF NOT EXISTS `class`(`id` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
);
CREATE TABLE IF NOT EXISTS `book`(
`bookid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
);
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> select * from book;+--------+------+
| bookid | card |
+--------+------+
| 1 | 1 |
| 2 | 16 |
| 3 | 18 |
| 4 | 2 |
| 5 | 13 |
| 6 | 19 |
| 7 | 18 |
| 8 | 10 |
| 9 | 17 |
| 10 | 12 |
| 11 | 10 |
| 12 | 13 |
| 13 | 15 |
| 14 | 14 |
| 15 | 6 |
| 16 | 6 |
| 17 | 11 |
| 18 | 17 |
| 19 | 10 |
| 20 | 20 |
+--------+------+
20 rows in set (0.00 sec)
mysql> select * from class;
+----+------+
| id | card |
+----+------+
| 1 | 10 |
| 2 | 3 |
| 3 | 4 |
| 4 | 9 |
| 5 | 14 |
| 6 | 4 |
| 7 | 16 |
| 8 | 10 |
| 9 | 19 |
| 10 | 6 |
| 11 | 11 |
| 12 | 17 |
| 13 | 12 |
| 14 | 6 |
| 15 | 16 |
| 16 | 1 |
| 17 | 15 |
| 18 | 10 |
| 19 | 8 |
| 20 | 9 |
+----+------+
20 rows in set (0.00 sec)
② 实际问题
两表关联时索引的建立位置：
- 左连接时在左表还是右表建立索引？
- 右连接时在左表还是右表建立索引？
② 分析优化
左连接时 class 作为左表，book 作为右表
分析可知查询进行全表扫描，且没有使用索引。
③ 解决方案
给 class 表建立索引
# 在class表建立索引mysql> create index idx_class_card on class(card);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 查询索引
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_class_card | 1 | card | A | 20 | NULL | NULL | | BTREE | | |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
给左连接的左表加索引后，
type达到index级别，但是扫描的行数没有减少。
为 book 表建立索引
mysql> drop index idx_class_card on class;
Query OK, 0 rows affected (0.03 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> create index idx_book_card on book(card);
Query OK, 0 rows affected (0.01 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_book_card | 1 | card | A | 20 | NULL | NULL | | BTREE | | |
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
给左连接的右表添加索引，
type达到ref级别，且使用了索引，右表扫描的行数减少。出现以上情况的原因是由左连接的特性导致的：
LEFT JOIN条件用于确定如何让右表的搜索行，左表是一定全都包含的。- 因此右表是我们的搜索关键表，需要建立索引。
同理可推出右连接存在同样的问题，因此得出结论：左连接时在右表建立索引，右连接在左表建立索引，即**“连接索引相反建立”**
5.1.3 三表优化分析
① 创建数据表
CREATE TABLE IF NOT EXISTS `phone`(`phoneid` INT(10) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
`card` INT (10) UNSIGNED NOT NULL
)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)));
mysql> select * from phone;+---------+------+
| phoneid | card |
+---------+------+
| 1 | 18 |
| 2 | 13 |
| 3 | 10 |
| 4 | 12 |
| 5 | 8 |
| 6 | 5 |
| 7 | 2 |
| 8 | 11 |
| 9 | 12 |
| 10 | 4 |
| 11 | 3 |
| 12 | 5 |
| 13 | 13 |
| 14 | 11 |
| 15 | 13 |
| 16 | 15 |
| 17 | 15 |
| 18 | 7 |
| 19 | 10 |
| 20 | 7 |
+---------+------+
20 rows in set (0.00 sec)
② 查询需求
三表关联索引建立的位置？
③ 分析优化
三表关联没有使用索引且进行了全表扫描。
④ 解决方案
通过两表关联可知需要和连接建立相反方向的索引，因此需要在 book 表和 phone 表的 card 字段上建立索引。
# 在 book 表建立索引mysql> create index idx_book_card on book(card);
Query OK, 0 rows affected (0.02 sec)
Records: 0 Duplicates: 0 Warnings: 0
# 在 phone 表建立索引
mysql> alter table phone add index idx_phone_card(card);
Query OK, 0 rows affected (0.02 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_book_card | 1 | card | A | 20 | NULL | NULL | | BTREE | | |
+-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
mysql> show index from pnone;
ERROR 1146 (42S02): Table "base_crud.pnone" doesn"t exist
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_phone_card | 1 | card | A | 20 | NULL | NULL | | BTREE | | |
+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
2 rows in set (0.00 sec)
查询分析
加入索引后可以看到查询使用了索引，且表扫描的行数下降。
5.1.4 关联查询优化总结
① 尽可能减少 Join 语句中的 NestedLoop 的循环次数；“用小结果集驱动大结果集”
② 优先优化 NestedLoop 的内层字段；
③ 当无法保证驱动表的 JOIN 字段被索引且内存充足的条件下，可以适当调整 JoinBuffer 设置；
5.2 索引失效
MySQL 中导致索引失效的情况：
① 在索引上做操作（计算、函数、手动/自动类型转化）会导致索引失效转而进行全表扫描；
② SQL 中使用 (!= 或 <>) 会导致索引失效转而进行全表扫描；
③ SQL 中使用 IS NOT NULL 会导致索引失效转而进行全表扫描；
④ SQL 中 LIKE 以通配符开头 LIKE(&字符串) 会导致索引失效转而进行全表扫描；
⑤ SQL 中字符串不加单引号会导致索引失效转而进行全表扫描；
⑥ SQL 中使用 OR 进行连接会导致索引失效转而进行全表扫描；
5.2.1 尽量使用全值匹配
SQL 语句中 WHERE 查询的字段按照顺序在索引中可以全部匹配。
SQL 中查询字段的顺序，跟使用索引中字段的顺序没有关系。优化器会在不影响 SQL 执行结果的前提下，进行自动优化。
5.2.2 最佳左前缀法则
SQL 中的查询字段和索引建立顺序不同会导致索引无法使用，甚至索引失效。因此 SQL 中进行条件过滤时需要按照建立索引时的顺序，依次匹配跳过某个字段索引后面的字段都无法使用。如果使用复合索引，要遵守最左前缀法则。
最左前缀法则：查询从索引最左列开始并且不跳过索引中的列。
① 查询时完全按照索引建立顺序，可以看到索引被使用且查询均为常量
② 不按照索引顺序进行查询且不完全使用索引列，可以看到索引没有完全使用
③ 不使用索引列首位进行查询，可以看到完全没有使用索引进行了全表扫描
5.2.3 索引列上不做任何操作
计算、函数、（自动/手动）类型转换，会导致索引失效转向全表扫描。
SQL 中 WHERE 查询字符串不加单引号 SQL 会进行字符串转化，导致索引失效。
5.2.4 索引列上不进行范围查询
存储引擎不能使用索引范围条件右边的列，所以将可能做范围查询的字段索引顺序放到最后。
5.2.5 尽量使用覆盖索引
只访问索引的查询（索引列和查询列保持一致）减少 SELECT * 操作
5.2.6 索引列上不做判空操作
MySQL 在使用 (!= 或 <>) 时会导致索引失效，导致进行全表扫描
虽然判空操作会导致索引失效，但是具体情况需要具体分析，SQL 查询不能仅考虑索引是否失效。
5.2.7 索引列上不做非空查询
SQL 中在表字段允许为 NULL 的情况下，WHERE 条件中使用 IS NULL 不会导致索引失效，但是 IS NOT NULL 会导致索引失效。
不要进行字段非空判断，最好给字段设置默认值。
5.2.8 正确使用模糊查询
SQL 中 LIKE 以通配符开头 ("%abc...") MySQL 会导致索引失效，转而进行全表扫描。即使用 LIKE 的进行模糊匹配时，左模糊和全模糊会导致索引失效，右模糊才能使用索引。
面试题目：解决
LIKE "%字符%"索引不被使用的方式？建立覆盖索引解决全模糊导致索引失效问题。如下图在
name、age字段上建立索引，再进行全模糊查询时可以使用到覆盖索引。
5.2.9 注意连接的使用
使用 OR 连接会导致索引失效，尽量使用 UNION 或 UNION ALL 替代。
5.2.10 索引优化总结
全值匹配我最爱，最左原则要遵守。
带头大哥不能死，中间兄弟不能断。
索引列上少计算，范围之后全失效。
LIKE 百分写最右，覆盖索引不写 *。
不等空值还有 OR，索引影响要注意。
VAR 引号不可丢，SQL 优化有诀窍。
5.2.11 索引优化面试题
① 创建数据库
create table test(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 test(c1,c2,c3,c4,c5) values ("a1","a2","a3","a4","a5");
insert into test(c1,c2,c3,c4,c5) values ("b1","b2","b3","b4","b5");
insert into test(c1,c2,c3,c4,c5) values ("c1","c2","c3","c4","c5");
insert into test(c1,c2,c3,c4,c5) values ("d1","d2","d3","d4","d5");
insert into test(c1,c2,c3,c4,c5) values ("e1","e2","e3","e4","e5");
create index idx_test03_c1234 on test03(c1,c2,c3,c4);
mysql> select * from test;+----+------+------+------+------+------+
| 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)
② 创建索引
# 创建索引mysql> create index idx_test_c1234 on test(c1,c2,c3,c4);
Query OK, 0 rows affected (0.03 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 | 5 | 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.00 se
③ 分析以下 SQL 执行情况
1）基本查询
explain select * from test where c1 = "a1";explain select * from test where c1 = "a1" and c2 = "a2";
explain select * from test where c1 = "a1" and c2 = "a2" and c3 = "a3";
explain select * from test where c1 = "a1" and c2 = "a2" and c3 = "a3" and c4 = "a4";
以上 SQL 均可以使用索引，且使用的索引列逐渐增加。
2）基本查询
explain select * from test where c1 = "a1" and c2 = "a2" and c3 = "a3" and c4 = "a4";explain select * from test where c1 = "a1" and c3 = "a3" and c2 = "a2" and c4 = "a4";
explain select * from test where c4 = "a4" and c3 = "a3" and c2 = "a2" and c1 = "a1";
以上的 SQL 查询均使用四个索引列，原因在于 MySQL 内部优化器进行 SQL 优化，但是建议索引顺序和查询顺序保持一致。
3）范围查询
explain select * from test where c1 = "a1" and c2 = "a2" and c3 > "a3" and c4 = "a4";以上的 SQL 只能应用索引列 c1、c2、c3因为 c3 处进行范围查询导致索引失效。
explain select * from test where c1 = "a1" and c2 = "a2" and c4 > "a4" and c3 = "a3";以上的 SQL 应用索引列 c1、c2、c3，c4 因为 c4 处进行范围查询导致索引失效，但是 c4 是最后一个索引列，虽然是范围查找，但是仍使用了全部的索引列。
4）单值排序查询
explain select * from test where c1 = "a1" and c2 = "a2" and c4 = "a4" order by c3;以上 SQL 实际使用了三个索引列 c1、c2、c3，但是仅显示使用了 c1、c2 实际情况是 c1、c2 参与查询操作， a3 参与排序操作。由此引出索引的两大作用，查询和排序。
explain select * from test where c1 = "a1" and c2 = "a2" order by c3;与上面的 SQL 执行情况一致。
explain select * from test where c1 = "a1" and c2 = "a2" order by c4;上面的 SQL 会出现
filesort，原因在于复合索引在 a3 处断裂，导致后续的 a4 只能进行文件排序
5）多值排序查询
explain select * from test where c1 = "a1" and c5 = "a5" order by c2,c3;以上 SQL 仅使用到 c1 索引列，实际上 c2、c3 被用作于排序。
explain select * from test where c1 = "a1" and c5 = "a5" order by c3,c2;以上的 SQL 会出现
filesort，原因在于索引在 c2 处断裂导致索引失效转而进行文件排序。
explain select * from test where c1 = "a1" and c2 = "a2" order by c2,c3;应用两个索引列 c1、c2，同时 c2、c3 参与排序
explain select * from test where c1 = "a1" and c2 = "a2" and c5 = "a5" order by c2,c3;与上一条 SQL 执行情况一致。
explain select * from test where c1 = "a1" and c2 = "a2" and c5 = "a5" order by c3,c2;结合 MySQL 的特性可知，c2 没有参与排序只有 c3 参与了排序，实际使用索引列 c1、c2 进行查找，c3 进行排序索引没有失效。
原因：MySQL 中当排序的值是个常量时，这个常量不会参与排序。
explain select * from test where c1 = "a1" and c5 = "a5" order by c3,c2;以上 SQL 会出现
filesort，仅使用索引列 c1 进行查询，在排序使用 c3 时因为索引在 c2 处断裂，导致索引失效转而进行文件排序。
6）分组查询
explain select * from test where c1 = "a1" and c4 = "a4" group by c2,c3;查询时使用索引列 c1 分组时使用索引列 c2、c3 索引没有失效。
explain select * from test where c1 = "a1" and c4 = "a4" group by c3,c2;以上 SQL 会出现
filesort和temporary，仅使用索引列 c1 进行查询，在分组时使用 c3 时因为索引在 c2 处断裂，导致索引失效转而进行文件排序。同时由于对c3 进行分组操作，MySQL 在分组前会先进行排序导致出现临时表。
④ 面试题总结
- 定值、范围实际上还是排序，通常
order by给定的是个范围； group by一般都需要进行排序，会产生临时表；
5.3 优化建议
① 对于单键索引，尽量选择针对当前 query 过滤性更好的字段；
② 在选择组合索引时，当前 query 中过滤性最好的字段在索引字段顺序中越靠前越好；
③ 在选择组合索引时，尽量选择能包含当前 query 中 WHERE 子句中更多字段的索引；
④ 尽可能通过分析统计信息和调整 query 写法来达到选择合适索引的目的；
以上是 三、索引优化分析（下） 的全部内容， 来源链接： utcz.com/z/535454.html
