失眠网 > MySQL数据库性能优化由浅入深（表设计慢查询 SQL索引优化 Explain分析 Show Pro

MySQL数据库性能优化由浅入深（表设计慢查询 SQL索引优化 Explain分析 Show Pro

时间：2018-09-14 16:40:20

文章目录

0 SQL性能分析1 表的设计合理化1.1 为什么需要范式1.2 三范式原理1.3 什么样的表才满足三范式2 慢查询2.1 慢查询介绍2.2 慢查询步骤3 添加适当索引3.1 索引是什么3.2 索引优劣势3.3 索引分类和建索引命令语句3.4 创建索引情况分析4 Explain分析（重点）4.1 Explain介绍4.2 id（表的读取顺序）4.3 select_type（数据读取操作的操作类型）4.4 table（显示执行的表名）4.5 type（访问类型排列）4.6 possible_keys（哪些索引可以使用）4.7 key（哪些索引被实际使用）4.8 key_len（消耗的字节数）4.9 ref（表之间的引用）4.10 rows（每张表有多少行被优化器查询）4.11 Extra [ˈekstrə]4.12 练习5 索引优化5.1 索引单表优化案例5.2 索引两表优化案例5.3 索引三表优化案例5.4 索引失效案例5.5 索引面试题分析5.6 总结5.7 小表驱动大表5.8 Order by优化5.9 Group by优化6 Show Profile分析（重点）7 MySQL配置优化8 MySQL从入门到精通PPT大全下载

0 SQL性能分析

SQL性能下降原因：

1、查询语句写的烂2、索引失效（数据变更）3、关联查询太多join（设计缺陷或不得已的需求）4、服务器调优及各个参数设置（缓冲、线程数等）

通常SQL调优过程：

观察，至少跑1天，看看生产的慢SQL情况。开启慢查询日志，设置阙值，比如超过5秒钟的就是慢SQL，并将它抓取出来。explain + 慢SQL分析。show profile。运维经理 or DBA，进行SQL数据库服务器的参数调优。

总结：

1、慢查询的开启并捕获2、explain + 慢SQL分析（没索引的先建索引）3、show profile查询SQL在Mysql服务器里面的执行细节和生命周期情况4、SQL数据库服务器的参数调优

数据库的分类

关系型数据库：mysq/oracle/db2/informix/sysbase/sql server非关系型数据库：(特点:面向对象或者集合)NoSql数据库：MongoDB(特点是面向文档)

1 表的设计合理化

1.1 为什么需要范式

一个软件项目基本都会用到数据库，项目开发前期分析客户的业务和数据处理需求，然后设计数据库的E-R模型图，确认需求信息的正确和完整。再就要将E-R图转换为多张表，表设计后，很可能结构不合理，出现数据重复保存，简称数据的冗余，这对数据的增删改查带来很多后患，所以我们需要审核是否合理，就像施工图设计后，还需要其他机构进行审核图纸是否设计合理一样。

如何审核呢？需要一些有关数据库设计的理论指导规则，这些规则业界简称数据库的范式。数据库范式为数据库的设计、开发提供了一个可参考的典范。

1.2 三范式原理

通俗地理解三个范式，对于数据库设计大有好处。在数据库设计中，为了更好地应用三个范式，就必须通俗地理解三个范式(通俗地理解是够用的理解，并不是最科学最准确的理解

第一范式: 1NF是对属性的原子性约束，要求属性(列)具有原子性，不可再分解; (只要是关系型数据库都满足1NF)

第二范式: 2NF是对记录的惟一性约束，要求记录有惟一标识，即实体的惟一性;

第三范式: 3NF是对字段冗余性的约束，它要求字段没有冗余。没有冗余的数据库设计可以做到。

但是，没有冗余的数据库未必是最好的数据库，有时为了提高运行效率，就必须降低范式标准，适当保留冗余数据。具体做法是:在概念数据模型设计时遵守第三范式，降低范式标准的工作放到物理数据模型设计时考虑。降低范式就是增加字段，允许冗余。

1.3 什么样的表才满足三范式

表的范式，是首先符合1NF, 才能满足2NF,进一步满足3NF

1NF：即表的列的具有原子性，不可再分解，即列的信息，不能分解,只有数据库是关系型数据库

2NF：表中的记录是唯一的，就满足2NF,通常我们设计一个主键来实现

3NF：即表中不要有冗余数据，就是说，表的信息，如果能够被推导出来，就不应该单独的设计一个字段来存放

反3NF：在表1->n的情况下，为了提高效率可能会在1这张表设计字段提速

2 慢查询

2.1 慢查询介绍

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10秒以上的语句。由他来查看哪些SQL超出了我们的最大忍耐时间值，比如一条sql执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒的sql，结合之前explain进行全面分析

2.2 慢查询步骤

问题：如何从一个大项目中，迅速的定位执行速度慢的语句.(定位慢查询)

1）首先我们了解mysql数据库的一些运行状态如何查询(比如想知道当前mysql运行的时间一共执行了多少次selecthupdate/delete,当前连接)

当前时间：show status like 'uptime';共执行多少次select：show stauts like 'com_select';共执行多少次update：show stauts like 'com_update';共执行多少次delete：show stauts like 'com_delete';

show [session/global] status like ...如果你不写 [session/global] 默认是session会话，指取出当前窗口的执行，如果你想看所有(从mysql启动到现在，则应该global)

当前MySQL连接数：show status like 'connections';显示慢查询次数：show status like 'slow_queries';

2）开启慢查询日志

操作说明：

默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。

当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件。

查看是否开启及如何开启：

默认：SHOW VARIABLES LIKE '%slow_query_log%';[ˈveəriəbls]开启：set global slow_query_log=1;，只对当前数据库生效，如果MySQL重启后则会失效

如果要永久生效，就必须修改配置文件f(其它系统变量也是如此)

修改f文件，[mysqld] 下增加或修改参数slow_query_log和slow_query_log_file后，然后重启MySQL服务器。也即将如下两行配置进f文件

slow_query_log =1slow_query_log_file=/var/lib/mysqatguigu-slow.log

关于慢查询的参数slow_query_log_file，它指定慢查询日志文件的存放路径，系统默认会给一个缺省的文件host_name-slow.log（如果没有指定参数slow_query_log_file的话）

3）开启了慢查询日志后，什么样的SQL才会记录到慢查询日志里面呢？

这个是由参数long_query_time控制，默认情况下long_query_time的值为10秒，命令：SHOW VARIABLES LIKE 'long_query_time%';

可以使用命令修改，也可以在f参数里面修改。

假如运行时间正好等于long_query_time的情况，并不会被记录下来。也就是说，在mysql源码里是判断大于long_query_time，而非大于等于。

命名修改慢SQL阈值时间：set global long_query_time=3;[ˈɡləʊbl]

看不到修改情况的话，重开连接，或者换一个语句：show global variables like 'long_query_time';

4）记录慢SQL并后续分析

假设我们成功设置慢SQL阈值时间为3秒（set global long_query_time=3;）。

模拟超时SQL：select sleep(4);

查询当前系统中有多少条慢查询记录：show global status like '%Slow_queries%';[ˈsteɪtəs]

在配置文件中设置慢SQL阈值时间（永久生效）：

#[mysqld]下配置:slow_query_log=1;slow_query_log_file=/var/lib/mysql/atguigu-slow.loglong_query_time=3;log_output=FILE;

3 添加适当索引

3.1 索引是什么

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构（索引的本质是数据结构，排序+查询两种功能）。

索引可以理解为：排好序的快速查找数据结构

下图就是一种可能的索引方式示例：

假如：找4号这本书，扫码得到对应的编号为91，91比34大往右边找，91比89大往右边找，然后找到（比较三次后就可以找到，然后检索出对应的物理地址）

为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录

结论：在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用(指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构就是索引

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。

我们平常所说的索引，如果没有特别指明，都是指B树（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种类型的索引之外，还有哈稀索引(hash index)等

3.2 索引优劣势

优势：

类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本。通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。

劣势：

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

3.3 索引分类和建索引命令语句

主键索引：索引值必须是唯一的，且不能为NULL

第一种：CREATE TABLE table_name(id int PRIMARY KEY auto_increment,name varchar(10));第二种：ALTER TABLE table_name ADD PRIMARY KEY (columnName);

普通索引：索引值可出现多次

第一种：CREATE INDEX index_name on table_name(columnName);第二种：ALTER TABLE table_name ADD INDEX index_name (columnName);

全文索引：主要是针对文本的检索，如：文章，全文索引只针对MyISAM引擎有效，并且只针对英文内容生效

建表时创建

#建表CREATE TABLE articles(id INT UNSIGNED ATUO_INCREMENT NOT NULL PRIMARY KEY,title VARCHAR(200),body TEXT,FULLTEXT(title,body))engine=myisam charset utf8;#指定存储引擎#使用select * from articles where match(title,body) against('英文内容'); #只针对英语内容生效#说明#1、在mysql中fultext索引只针对 myisam 生效#2、mysq1自己提供的flltext只针对英文生效->sphinx (coreseek)技术处理中文工#3、使用方法是match(字段名...) against(‘关键字')#4、全文索引一个叫停止词，因为在一个文本中创建索引是一个无穷大的数，因此对一些常用词和字符就不会创建，这些词称为停止词

ALTER TABLE table_name ADD FULLTEXT index_name (columnName);

唯一索引：索引列的值必须唯一，但允许有空值NULL，并可以有多个。

第一种：CREATE UNIQUE INDEX index_name ON table_name(columnName);第二种：ALTER TABLE table_name ADD UNIQUE INDEX index_name ON (columnName);

单值索引：即一个索引只包含单个列，一个表可以有多个单列索引。

第一种：CREATE INDEX index_name ON table_name(columnName);第二种：ALTER TABLE table_name ADD INDEX index_name ON (columnName);

select * from user where name='';//经常查name字段，为其建索引create index idx_user_name on user(name);

复合索引：即一个索引包含多个列

第一种：CREATE INDEX index_name ON table_name(columnName1，columnName2...);第二种：ALTER TABLE table_name ADD INDEX index_name ON (columnName1，columnName2...);

select * from user where name='' and email='';//经常查name和email字段，为其建索引create index idx_user_name on user(name, email);

查询索引

第一种：SHOW INDEX FROM table_name;第二种：SHOW KEYS FROM table_name;

删除索引

第一种：DROP INDEX index_name ON table_name;第二种：ALTER TABLE table_name DROP INDEX index_name;删除主键索引：ALTER TBALE table_name DROP PRIMARY KEY;

查看索引的使用情况：show status like 'Handler_ read%';

handler_ read_ key:这个值越高越好，越高表示使用索引查询到的次数。handler_ read_ rnd_next:这个值越高，说明查询低效。

修改索引通常是先删除再重新建

3.4 创建索引情况分析

哪些情况适合建索引：

主键自动建立唯一索引频繁作为查询条件（where）的字段应该创建索引查询中与其它表关联的字段，外键关系建立索引单键/组合索引的选择问题，who?(在高并发下倾向创建组合索引)查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度查询中统计或者分组字段

哪些情况不适合建索引：

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

假如一个表有10万行记录，有一个字段A只有T和F两种值，且每个值的分布概率天约为50%，那么对这种表A字段建索引一般不会提高数据库的查询速度。

索引的选择性：是指索引列中不同值的数目与表中记录数的比。

如果一个表中有2000条记录，表索引列有1980个不同的值，那么这个索引的选择性就是1980/2000=0.99。一个索引的选择性越接近于1，这个索引的效率就越高

4 Explain分析（重点）

4.1 Explain介绍

使用EXPLAIN关键字可以模拟优化器执行SQL语句，分析你的查询语句或是结构的性能瓶颈在 select 语句之前增加 explain 关键字，MySQL 会在查询上设置一个标记，执行查询会返回执行计划的信息，而不是执行这条SQL 注意：如果 from 中包含子查询，仍会执行该子查询，将结果放入临时表中

Explain官方文档：官网地址

Explain的作用：

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

使用Explain：

mysql> select * from tbl_emp;+----+------+--------+| id | NAME | deptId |+----+------+--------+| 1 | z3 |1 || 2 | z4 |1 || 3 | z5 |1 || 4 | w5 |2 || 5 | w6 |2 || 6 | s7 |3 || 7 | s8 |4 || 8 | s9 |51 |+----+------+--------+8 rows in set (0.00 sec)mysql> explain select * from tbl_emp;+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+| 1 | SIMPLE| tbl_emp | NULL | ALL | NULL| NULL | NULL | NULL | 8 | 100.00 | NULL |+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+1 row in set, 1 warning (0.00 sec)

4.2 id（表的读取顺序）

select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序

三种情况：

1、id相同，执行顺序由上至下（t1、t3、t2）

2、id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行（t3、t1、t2）

3、id相同不同，同时存在。先走数字大的，数字相同的由上至下（t3、s1、t2）

4.3 select_type（数据读取操作的操作类型）

查询的类型，主要是用于区别普通查询、联合查询、子查询等的复杂查询。

SIMPLE [ˈsɪnpl] ：简单的select查询,查询中不包含子查询或者UNIONPRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记为（最后加载的那个）SUBQUERY [ˈkwɪəri] ：在SELECT或WHERE列表中包含了子查询DERIVED [dɪˈraɪvd]：在FROM列表中包含的子查询被标记为DERIVED（衍生）MySQL会递归执行这些子查询，把结果放在临时表里UNION [ˈjuːniən]：若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中外层SELECT将被标记为：DERIVEDUNION RESULT [rɪˈzʌlt] ：从UNION表获取结果的SELECT（两个select语句用UNION合并）

4.4 table（显示执行的表名）

这一列表示 explain 的一行正在访问哪个表。

当 from 子句中有子查询时，table列是格式，表示当前查询依赖 id=N 的查询，于是先执行 id=N 的查询。当有 union 时，UNION RESULT 的 table 列的值为<union1,2>，1和2表示参与 union 的 select 行id。

4.5 type（访问类型排列）

显示查询使用了何种类型

访问类型排列：system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index >ALL

type常用八种类型：

结果值从最好到最坏依次是（重点）：：system > const > eq_ref > ref > range > 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，将遍历全表以找到匹配的行

工作案例：经理这条SQL我跑了一下Explain分析，在系统上可能会有ALL全表扫描的情况，建议尝试一下优化。我把这条SQL改了改，我优化后是这么写，这个效果已经从ALL变成了…

4.6 possible_keys（哪些索引可以使用）

显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段火若存在索引，则该索引将被列出，但不一定被查询实际使用（系统认为理论上会使用某些索引）

4.7 key（哪些索引被实际使用）

实际使用的索引。如果为NULL，则没有使用索引（要么没建，要么建了失效）

查询中若使用了覆盖索引，则该索引仅出现在key列表中

覆盖索引：建的索引字段和查询的字段一致，如下图

4.8 key_len（消耗的字节数）

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好

key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的

4.9 ref（表之间的引用）

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

4.10 rows（每张表有多少行被优化器查询）

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数（越小越好）

未建索引时：

建索引后：扫描行数减少

4.11 Extra [ˈekstrə]

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

信息种类：Using filesort 、Using temporary 、Using index 、Using where 、Using join buffer 、impossible where 、select tables optimized away 、distinct

Using filesort（需要优化）

说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为"文件排序"

Using temporary（需要优化）

使了用临时表保存中间结果，MysQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by

Using index（good）

表示相应的select操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错！

情况一：

情况二：

覆盖索引 / 索引覆盖（Covering Index）。

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

理解方式二：索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据；当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了（或覆盖了）满足查询结果的数据就叫做覆盖索引。

注意：

如果要使用覆盖索引，一定要注意select列表中只取出需要的列，不可select*因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降

Using where：表明使用了where过滤。

Using join buffer：使用了连接缓存

impossible where：where子句的值总是false，不能用来获取任何元组

select tables optimized away

在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作，或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。

distinct

优化distinct操作，在找到第一匹配的元组后即停止找同样值的动作。

4.12 练习

写出下图的表的执行顺序

第一行（执行顺序4）：id列为1，表示是union里的第一个select，select_type列的primary表示该查询为外层查询，table列被标记为，表示查询结果来自一个衍生表，其中derived3中3代表该查询衍生自第三个select查询，即id为3的select。【select d1.name… 】

第二行（执行顺序2）：id为3，是整个查询中第三个select的一部分。因查询包含在from中，所以为derived。【select id,namefrom t1 where other_column=’’】

第三行（执行顺序3）：select列表中的子查询select_type为subquery，为整个查询中的第二个select。【select id from t3】

第四行（执行顺序1）：select_type为union，说明第四个select是union里的第二个select，最先执行【select name,id from t2】

第五行（执行顺序5）：代表从union的临时表中读取行的阶段，table列的<union1,4>表示用第一个和第四个select的结果进行union操作。【两个结果union操作】

5 索引优化

5.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 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

结论：很显然，type是ALL，即最坏的情况。Extra里还出现了Using filesort，也是最坏的情况。优化是必须的

开始优化

新建索引（给WHERE语句后使用的字段添加索引）

创建方式：

create index idx_article_ccv on article(category_id,comments,views);ALTER TABLE 'article' ADD INDEX idx_article_ccv ( 'category_id , 'comments', 'views' );

索引用处不大，删除：DROP INDEX idx_article_ccv ON article;

结论：

type变成了range，这是可以忍受的。但是extra里使用Using filesort仍是无法接受的。

但是我们已经建立了索引，为啥没用呢？

这是因为按照BTree索引的工作原理，先排序category_id，如果遇到相同的category_id则再排序comments,如果遇到相同的comments 则再排序views。

当comments字段在联合索引里处于中间位置时，因comments > 1条件是一个范围值(所谓range)，MySQL无法利用索引再对后面的views部分进行检索，即range类型查询字段后面的索引无效。

改进

上次创建索引相比，这次不为comments字段创建索引

结论：type变为了ref，ref 中是 const，Extra 中的 Using filesort也消失了，结果非常理想

5.2 索引两表优化案例

建表：

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)));//查询mysql> select * from class;+----+------+| id | card |+----+------+| 1 | 17 || 2 | 2 || 3 | 18 || 4 | 4 || 5 | 4 || 6 | 8 || 7 | 9 || 8 | 1 || 9 | 18 || 10 | 6 || 11 | 15 || 12 | 15 || 13 | 12 || 14 | 15 || 15 | 18 || 16 | 2 || 17 | 18 || 18 | 5 || 19 | 7 || 20 | 1 || 21 | 2 |+----+------+21 rows in set (0.00 sec)mysql> select * from book;+--------+------+| bookid | card |+--------+------+|1 | 8 ||2 | 14 ||3 | 3 ||4 | 16 ||5 | 8 ||6 | 12 ||7 | 17 ||8 | 8 ||9 | 10 ||10 | 3 ||11 | 4 ||12 | 12 ||13 | 9 ||14 | 7 ||15 | 6 ||16 | 8 ||17 | 3 ||18 | 11 ||19 | 5 ||20 | 11 |+--------+------+20 rows in set (0.00 sec)

开始Explain分析：type都是all，需要优化（总有一个表来添加索引驱动）

左连接为左表加索引

删除索引：drop index y on class;

左连接为右表添加索引

删除索引：drop index Y on book;

案例：如果别人建的索引位置不对，只需要自己查询时调整左右表的顺序即可

结论：

第二行的type变为了ref，rows也变少了，优化比较明显。这是由左连接特性决定的。LEFT JOIN条件用于确定如何从右表搜索行，左边一定都有，所以右边是我们的关键点，一定需要在右表建立索引（小表驱动大表）。左连接，右表加索引同理：右连接，左表加索引

5.3 索引三表优化案例

建表：

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)));//查询mysql> select * from phone;+---------+------+| phoneid | card |+---------+------+| 1 | 10 || 2 | 13 || 3 | 17 || 4 | 5 || 5 | 12 || 6 | 7 || 7 | 15 || 8 | 17 || 9 | 17 ||10 | 14 ||11 | 19 ||12 | 13 ||13 | 5 ||14 | 8 ||15 | 2 ||16 | 8 ||17 | 11 ||18 | 14 ||19 | 13 ||20 | 5 |+---------+------+20 rows in set (0.00 sec)

用上一节两个表，删除他们的索引：

三表查询语句应为：SELECT * FROM class LEFT JOIN book ON class.card = book.card LEFT JOIN phone ON book.card = phone.card;

创建索引：

应该为第一个LFET JOIN 的右表 book 建索引

alter table `book` add index Y(`card`);

应该为第二个LFET JOIN 的右表 phone 建索引

alter table `phone` add index z(`card`);

Explain分析：

后2行的 type 都是ref且总 rows优化很好，效果不错。因此索引最好设置在需要经常查询的字段中

结论：

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

5.4 索引失效案例

建表：

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

1、全值匹配我最爱

2、最佳左前缀法则（重要！）：如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过复合索引中间列。

中间列不能断：

3、不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描。

4、存储引擎不能使用索引中范围条件右边的列（范围之后全失效，范围列并不是做的查询而是排序）。

5、尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select *。

6、mysql在使用不等于（!=或者<>）的时候无法使用索引会导致全表扫描。

7、is null, is not null 也无法使用索引。

8、like以通配符开头（’%abc…’），mysql索引失效会变成全表扫描的操作（%写在最右边索引不会失效，或覆盖索引）。

问题：解决like '%字符串%'时索引不被使用的方法？采用覆盖索引的方法！

建表：

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@');INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('2bb2',23,'b@');INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('3cc3',24,'c@');INSERT INTO tbl_user(`name`,`age`,`email`)VALUES('4dd4',26,'d@');//查询mysql> select * from tbl_user;+----+------+------+-----------+| id | name | age | email|+----+------+------+-----------+| 1 | 1aa1 | 21 | a@ || 2 | 2bb2 | 23 | b@ || 3 | 3cc3 | 24 | c@ || 4 | 4dd4 | 26 | d@ |+----+------+------+-----------+4 rows in set (0.00 sec)

创建索引：

CREATE INDEX idx_user_nameAge ON tbl_user(NAME,age);

索引成功使用：

索引失效：

总结：%写在最右边，如果非要写在最左边，就使用覆盖索引

9、字符串不加单引号索引失效。

Explain分析：

10、少用or，用它来连接时会索引失效

5.5 索引面试题分析

建表：

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');//查看表结构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)

建索引：

create index idx_test03_c1234 on test03(c1,c2,c3,c4);//查看索引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 | 2 |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)

1）逐一增加列

2）交换条件顺序不影响索引，但最好按照建索引顺序来写SQL

3) 限定范围

4）order by

5）group by

定值、范围还是排序，一般order by是给个范围

group by基本上都需要进行排序，会有临时表产生

建议：

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

5.6 总结

优化总结口诀

全值匹配我最爱，最左前缀要遵守；

带头大哥不能死，中间兄弟不能断；

索引列上少计算，范围之后全失效；

LIKE 百分写最右，覆盖索引不写 *；

不等空值还有OR，索引影响要注意；

VAR 引号不可丢， SQL 优化有诀窍。

5.7 小表驱动大表

EXISTS [ɪɡˈzɪsts]语法：SELECT ...FROM table WHERE EXISTS (subquery)

该语法可以理解为：将主查询的数据，放到子查询中做条件验证，根据验证结果（TRUE或FALSE）来决定主查询的数据结果是否得以保留

提示：

EXSTS(subquey) 只返回TRUE或FALSE，因此子查询中的SELECT * 也可以是 SELECT 1 或select ‘X’，官方说法是实际执行时会忽略SELECT清单，因此没有区别。EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比，如果担忧效率问题，可进行实际检验以确定是否有效率问题。EXISTS子查询往往也可以用条件表达式，其他子查询或者JOIN来替代，何种最优需要具体问题具体分析

in和exists用法：

5.8 Order by优化

1、ORDER BY之后子句，尽量使用Index方式排序，避免使用FileSort方式排序

建表：

create table tblA(#id int primary key not null auto_increment,age int,birth timestamp not null);insert into tblA(age, birth) values(22, now());insert into tblA(age, birth) values(23, now());insert into tblA(age, birth) values(24, now());create index idx_A_ageBirth on tblA(age, birth);//查询mysql> select * from tblA;+------+---------------------+| age | birth|+------+---------------------+| 22 | -04-04 19:31:45 || 23 | -04-04 19:31:45 || 24 | -04-04 19:31:45 |+------+---------------------+3 rows in set (0.00 sec)mysql> show index from tblA;+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+| tbla |1 | idx_A_ageBirth | 1 | age | A | 3 |NULL | NULL | YES | BTREE| ||| tbla |1 | idx_A_ageBirth | 2 | birth | A | 3 |NULL | NULL || BTREE| ||+-------+------------+----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+2 rows in set (0.00 sec)

关注点：是order by之后会不会产生Using filesort

MySQL支持二种方式的排序，FileSort和lIndex，Index效率高，它指MySQL扫描索引本身完成排序。FileSort方式效率较低。

ORDER BY满足两情况，会使用Index方式排序：

ORDER BY语句使用索引最左前列。使用where子句与Order BY子句条件列组合满足索引最左前列。

2、尽可能在索引上完成排序操作，遵照建索引的最佳左前缀

3、如果不在索引列上，mysql的filesort有两种算法（自动启动）

双路排序

MySQL4.1之前是使用双路排序，字面意思就是两次扫描磁盘，最终得到数据，读取行指针和OrderBy列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读对应的数据输出。

从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段。

取一批数据，要对磁盘进行了两次扫描，众所周知，I\O是很耗时的，所以在mysql4.1之后，出现了第二种改进的算法，就是单路排序

单路排序

从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序压的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO,但是它会使用更多的空间，因为它把每一行都保存在内存中了

结论及引申出的问题

由于单路是后出的，总体而言好过双路

但是用单路有问题，在sort_buffer中，方法B比方法A要多占用很多空间，因为方法B是把所有字段都取出,所以有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序（创建tmp文件，多路合并)，排完再取取

sort_buffer容量大小，再排……从而多次I/O。

本来想省一次I/O操作，反而导致了大量的I/O操作，反而得不偿失

4、优化策略

增大sort_buffer_size参数的设置增大max_length_for_sort_data参数的设置Why？

5、小总结：

5.9 Group by优化

group by实质是先排序后进行分组，遵照索引建的最佳左前缀。

当无法使用索引列，增大max_length_for_sort_data参数的设置 + 增大sort_buffer_size参数的设置。

where高于having，能写在where限定的条件就不要去having限定了

在group by后面加个order by null 就可以防止排序

有些情况下，可以使用连接来替代子查询。因为使用join, MySQL不需要在内存中创建临时表

#简单处理方式select * fom dept,emp where dept.deptno=emp.deptno;#左连接更优select * from dept left join emnp on dept.deptno=emp.deptno；

6 Show Profile分析（重点）

Show Profile是mysql提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优的测量

官网文档

默认情况下，参数处于关闭状态，并保存最近15次的运行结果

分析步骤：

1、是否支持，看看当前的mysql版本是否支持：show variables like 'profiling';

默认是关闭，使用前需要开启

2、开启功能，默认是关闭，使用前需要开启：set profiling=on;

3、运行SQL（随便运行用来测试）

mysql> select * from emp group by id%10 limit 150000;mysql> select * from emp group by id%20 order by 5;

4、查看结果：show profiles;

mysql> show profiles;+----------+------------+-----------------------------------------------+| Query_ID | Duration | Query |+----------+------------+-----------------------------------------------+| 1 | 0.00204000 | show variables like 'profiling'|| 2 | 0.55134250 | select * from emp group by id%10 limit 150000 || 3 | 0.56902000 | select * from emp group by id%20 order by 5 |+----------+------------+-----------------------------------------------+3 rows in set, 1 warning (0.00 sec)

5、诊断SQL，show profile cpu,block io for query ID号;（ID号为第4步Query_ID列中数字）

mysql> show profile cpu,block io for query 3;+----------------------+----------+----------+------------+--------------+---------------+| Status| Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |+----------------------+----------+----------+------------+--------------+---------------+| starting | 0.000049 | 0.000000 | 0.000000 | NULL |NULL || checking permissions | 0.000005 | 0.000000 | 0.000000 | NULL |NULL || Opening tables | 0.000012 | 0.000000 | 0.000000 | NULL |NULL || init | 0.000021 | 0.000000 | 0.000000 | NULL |NULL || System lock| 0.000009 | 0.000000 | 0.000000 | NULL |NULL || optimizing | 0.000003 | 0.000000 | 0.000000 | NULL |NULL || statistics | 0.000017 | 0.000000 | 0.000000 | NULL |NULL || preparing | 0.000008 | 0.000000 | 0.000000 | NULL |NULL || Creating tmp table | 0.000045 | 0.000000 | 0.000000 | NULL |NULL || Sorting result | 0.000004 | 0.000000 | 0.000000 | NULL |NULL || executing | 0.000002 | 0.000000 | 0.000000 | NULL |NULL || Sending data | 0.568704 | 0.546875 | 0.046875 | NULL |NULL || Creating sort index | 0.000048 | 0.000000 | 0.000000 | NULL |NULL || end | 0.000003 | 0.000000 | 0.000000 | NULL |NULL || query end | 0.000005 | 0.000000 | 0.000000 | NULL |NULL || removing tmp table | 0.000006 | 0.000000 | 0.000000 | NULL |NULL || query end | 0.000003 | 0.000000 | 0.000000 | NULL |NULL || closing tables | 0.000004 | 0.000000 | 0.000000 | NULL |NULL || freeing items | 0.000061 | 0.000000 | 0.000000 | NULL |NULL || cleaning up| 0.000015 | 0.000000 | 0.000000 | NULL |NULL |+----------------------+----------+----------+------------+--------------+---------------+20 rows in set, 1 warning (0.00 sec)

参数备注（写在代码中）：show profile cpu,block io for query 3;（如此代码中的cpu,block）

ALL：显示所有的开销信息。BLOCK IO：显示块lO相关开销。CONTEXT SWITCHES ：上下文切换相关开销。CPU：显示CPU相关开销信息。IPC：显示发送和接收相关开销信息。MEMORY：显示内存相关开销信息。PAGE FAULTS：显示页面错误相关开销信息。SOURCE：显示和Source_function，Source_file，Source_line相关的开销信息。SWAPS：显示交换次数相关开销的信息。

6、日常开发需要注意的结论（Status列中的出现此四个问题严重）

converting HEAP to MyISAM：查询结果太大，内存都不够用了往磁盘上搬了。Creating tmp table：创建临时表，拷贝数据到临时表，用完再删除Copying to tmp table on disk：把内存中临时表复制到磁盘，危险!locked：锁了