Mysql中sql索引优化和慢sql日志，show profiles分析，mysql锁

基础知识和B+tree原理

sql性能下降的原因

1、sql写的不行

2、索引失效

3、多表关联太多join on

4、服务器调优及各个参数设置（缓冲、线程数等）

注意：innodb中按索引列是否为主键列将索引分为聚集索引（主键索引）和普通索引（辅助索引）。按创建的索引列数目分：包含单值索引，复合索引

单值索引

select * from user where name='';
create index idx_user_name on user(name);

复合索引

select * from user where name='' and email='';
create index idx_user_name on user(name, email);

sql执行加载顺序

我们一般手写sql

SELECT DISTINCT
    <select_list>
FROM
    <left_table> <join_type>
JOIN 
    <right_table> 
ON
    <join_condition>
WHERE
    <where_condition>
GROUP BY
    <group_by_list>
HAVING
    <having_condition>
ORDER BY
    <order_by_condition>
LIMIT
    <limit_number>

机器读取顺序

1 FROM  <left_table>
2 ON <join_condition>
3 <join_type> JOIN <right_table>
4 WHERE <where_condition>
5 GROUP BY <group_by_list>
6 HAVING <having_condition>
7 SELECT
8 DISTINCT <select_list>
9 ORDER BY <order_by_condition>
10 LIMIT <limit_number>

索引是什么

索引（Index）是帮助MySQL高效获取数据的数据结构。索引是数据结构, innodb引擎默认使用的B+Tree。

索引优缺点

优势

数据量大时，建立索引，类似于图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本。

通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。

劣势

实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的（占空间）

虽然索引大大提高了查询速度，由于底层使用的B+Tree,对表进行INSERT、UPDATE和DELETE，保存记录数据，会更新索引，需要花费时间，因此主键应该自增，降低新增索引数据的损耗。

索引只是提高效率的一个因素，如果你的MysQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询。

B+Tree原理解析

Mysql中的B-Tree和B+Tree原理解析

innodb索引B+Tree原理解析

MySQL MyISAM和Innodb索引实现原理分析

哪些情况适合建索引

1、主键自动建立唯一索引

2、频繁作为查询条件的字段应该创建索引

3、查询中与其它表关联的字段，外键关系建立索引

4、频繁更新的字段不适合创建索引，因为每次更新不单单是更新了记录还会更新索引

5、Where条件里用不到的字段不创建索引

6、单键/组合索引的选择问题(在高并发下倾向创建组合索引)

7、查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
查询中统计或者分组字段

哪些情况不适合建索引

1、表记录太少

2、经常增删改的表

数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。

索引优化分析

mysql体系结构

性能分析前提知识

MySQL Query Optimizer

Mysql中有专门负责优化SELECT语句的优化器模块，主要功能:通过计算分析系统中收集到的统计信息，为客户端请求的Query提供他认为最优的执行计划（他认为最优的数据检索方式，但不见得是DBA认为是最优的,这部分最耗费时间）

当客户端向MySQL请求一条Query，命令解析器模块完成请求分类，区别出是SELECT并转发给MySQL Query Optimizer时，MySQL Query Optimizer首先会对整条Query进行优化，处理掉一些常量表达式的预算直接换算成常量值。并对Query中的查询条件进行简化和转换，如去掉一些无用或显而易见的条件、结构调整等。然后分析Query 中的 Hint信息(如果有），看显示Hint信息是否可以完全确定该Query的执行计划。如果没有Hint 或Hint信息还不足以完全确定执行计划，则会读取所涉及对象的统计信息，根据Query进行写相应的计算分析，然后再得出最后的执行计划。

MySQL常见瓶颈

CPU：CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候

IO：磁盘I/O瓶颈发生在装入数据远大于内存容量的时候

服务器硬件的性能瓶颈：top，free，iostat和vmstat来查看系统的性能状态

explain是什么

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。

因此explain主要用来对sql进行性能分析的

explain能做什么

1、查看表的读取顺序（哪个表先被处理）

2、数据读取操作的操作类型（是全表扫描，还是index扫描，还是其它扫描）

3、哪些索引可以使用（possible key）

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

5、表之间的引用（ref）

6、每张表有多少行被优化器查询（row）

7、查看sql执行过程中使用了什么，出现什么问题 （extra）

explain 使用

explain + 查询sql语句

explain结果中的id

id主要表示表的执行顺序

三种情况：

1、id相同，执行顺序由上至下

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

3、id既有相同又有不同，同时存在

id如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id值越大，优先级越高，越先执行，衍生=DERIVED

因此上述表的执行顺序：t3 -> derived(衍生得到的表) -> t2

explain结果中的select_type和table

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

select_type有哪些？

• SIMPLE - 简单的select查询,查询中不包含子查询或者UNION。

• PRIMARY - 查询中若包含任何复杂的子部分，最外层查询则被标记为。

• SUBQUERY - 在SELECT或WHERE列表中包含了子查询。

• DERIUED - 在FROM列表中包含的子查询被标记为DERIVED（衍生）MySQL会递归执行这些子查询，把结果放在临时表里。

• UNION - 若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中外层SELECT将被标记为：DERIVED。

• UNION RESULT - 从UNION表获取结果的SELECT。

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

explain结果中的type

type显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index >ALL

简单点的扫描类型

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，将遍历全表以找到匹配的行。

explain结果中的possible_keys和key

possible_keys

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

key

实际使用的索引。如果为NULL，则没有使用索引

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

explain结果中的key_len

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

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

explain结果中的ref

ref：引用参数类型

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

explain结果中的rows

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

explain结果中的Extra

注意：extra十分重要

extra中可能出现的值：

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

Using index
表示相应的select操作中使用了覆盖索引（Covering Index），避免访问了表的数据行（回表查询）

覆盖索引：我们要获取的列，直接通过索引，可以全部被select筛选出来，无需进行回表查询（多一次B+Tree扫描）。

简单来说：就是查询使用了索引

Mysql中的回表查询

Using where

表明使用了where过滤。

Using join buffer

使用了连接缓存。

impossible where

where子句的值总是false，不能用来获取任何数据。

select tables optimized away

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

distinct

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

explain使用案例

执行流程：

1、先执行t2表 （select name， id，from t2）（对t2进行的是全表扫描）（原因：未用到索引）

2、然后执行t1表（select id，name from t1 where other_column = ’ '）(对t1进行的是全表扫描)（原因：未用到索引）

3、然后执行t3表（select id from t3）（对t3进行index扫描）

4、然后执行derived衍生表 （select d1.name，d2 from d1）（system扫描）

5、最后对derived和t2表 进行合并

索引单表优化

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

需求

现在呢，我们有一个需求，对下面sql进行优化
查询category_id为1且comments 大于1的情况下，views最多的article_id。

对应sql

select id,author_id 
from article 
where  category_id = 1 and comments > 1 
ORDER BY views 
desc LIMIT 1;

没有创建index的情况下，会出现全表扫描，文件内排序filesort

解决

where 后的字段，order by后的字段 创建聚合索引

再次进行分析

发现还是出现了filesort，意味着views索引每用上。

因此，我们需要了解复合索引的原理

联合索引原理

我们来看看复合索引的查找步骤

联合索引具体查找步骤：
当咱们的SQL语言能够应用到索引的时候，好比 select * from T1 where b = 12 and c = 14 and d = 3 ；也就是T1表中a列为4的这条记录。

查找步骤具体以下：

1、存储引擎首先从根节点（通常常驻内存）开始查找，第一个索引的第一个索引列为1,12大于1，第二个索引的第一个索引列为56,12小于56，因而从这俩索引的中间读到下一个节点的磁盘文件地址（此处其实是存在一个指针的，指向的是下一个节点的磁盘位置）。

2、进行一次磁盘IO，将此节点值加载后内存中，而后根据第一步同样进行判断，发现 数据都是匹配的，而后根据指针将此联合索引值所在的叶子节点也从磁盘中加载后内存，此时又发生了一次磁盘IO，最终根据叶子节点中索引值关联的 主键值 。

3、根据主键值 回表 去主键索引树（聚簇索引）中查询具体的行记录。

由上图可知，先匹配12，匹配到后，匹配14 以此类推。这就是复合索引的原理。

从上面来看，从category_id开始进行匹配，到comments > 1 是一个范围值，导致后续索引失效（views失效），因此出现的扫描类型是range

最终解决

1、删除原先index

2、创建新的index（不包含范围列）

3、进行优化分析

可以看到，通过category_id 和 views 创建的复合index，排序的时候使用到了views索引列

索引两表优化

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

优化分析

EXPLAIN SELECT * FROM class 
LEFT JOIN book ON class.card = book.card;

现象

上图显示，对sql的执行，分别对两表进行全表扫描（class 和book）

解决方法

由于left join 要显示左边表的全部，那么避免不了左边表进行全表扫描，可以对右边创建索引，避免右边表的全表扫描

1、为右边表关联字段创建索引

2、explain sql优化分析

上图可知避免了对book表的全表扫描

注意：以此类推多表关联操作也是这个原理

表索引优化总结

1、创建索引应该优先考虑创建复合索引

2、where，group by，order by 后的字段需要创建索引

3、创建复合索引时，应该避免复合索引中出现范围的索引列，因为，这样会导致范围索引列后续索引失效

4、表关联操作时，left join 应该创建右表的关联字段索引。同理right join 一样

索引失效原因和解决方案

索引失效

跳过复合索引中间列，或首列

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'),
(2,2,2,2,'aa','2'),
(2,2,2,2,'bba啊','2'),
(1,1,3,3,'cca','3');

需求

查询category_id为1 而且 comments =1 的情况下，views最多的article_id。

对应sql

select id,author_id 
from article 
where  category_id = 1 and comments = 1
ORDER BY views 
desc LIMIT 1;

创建复合索引

跳过复合索引中间列，或首列，导致索引失效

1、跳过首列，全部失效

mysql> explain select id,author_id from article where 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 |    3 |    33.33 | Using where; Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 sec)

2、跳过首列，全部失效

mysql> explain select id,author_id from article 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 |    3 |   100.00 | Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

3、跳过中间列，views索引失效

mysql> explain select id,author_id from article where  category_id = 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 | 4       | const |    2 |   100.00 | Using filesort |
+----+-------------+---------+------------+------+-----------------+-----------------+---------+-------+------+----------+----------------+
1 row in set, 1 warning (0.00 sec)

索引列上做额外操作

mysql>  explain select id,author_id from article where  left(category_id,11) = 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 |    3 |    33.33 | Using where; Using filesort |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
1 row in set, 1 warning (0.00 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 | 8       | NULL |    1 |   100.00 | Using index condition; Using filesort |
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)

示例中：views索引失效

select * 可能导致回表查询

Mysql中的回表查询

mysql> explain select * 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 | 8       | NULL |    1 |   100.00 | Using index condition; Using filesort |
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.00 sec)

like以通配符%开头字符串

like以通配符%开头字符串 会导致索引失效

mysql> create index idx_article_title on article(title);

mysql> explain select * from article where  title like '%a';
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | article | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    6 |    16.67 | Using where |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

注意：通配符在最后面，不会导致索引失效

mysql> explain select * from article where  title like 'a%';
+----+-------------+---------+------------+-------+-------------------+-------------------+---------+------+------+----------+-----------------------+
| id | select_type | table   | partitions | type  | possible_keys     | key               | key_len | ref  | rows | filtered | Extra                 |
+----+-------------+---------+------------+-------+-------------------+-------------------+---------+------+------+----------+-----------------------+
|  1 | SIMPLE      | article | NULL       | range | idx_article_title | idx_article_title | 767     | NULL |    1 |   100.00 | Using index condition |
+----+-------------+---------+------------+-------+-------------------+-------------------+---------+------+------+----------+-----------------------+
1 row in set, 1 warning (0.03 sec)

用关键字OR

mysql> explain select * from article where  category_id = 1 or comments = 2;
+----+-------------+---------+------------+------+-----------------+------+---------+------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys   | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+---------+------------+------+-----------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | article | NULL       | ALL  | idx_article_ccv | NULL | NULL    | NULL |    6 |    58.33 | Using where |
+----+-------------+---------+------------+------+-----------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

索引失效总结

索引失效常见解决方案

索引失效（应该避免）

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

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

3、存储引擎不能使用索引中范围条件右边的列。

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

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

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

7、like以通配符开头（’%abc…’），mysql索引失效会变成全表扫描的操作。

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

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

优化总结口诀

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

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

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

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

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

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

慢查询日志和show profile截取分析

小表驱动大表

通常SQL调优过程：

1、测试运行观察，至少跑1天，看看生产的慢SQL情况。

2、开启慢查询日志，设置阙值，比如超过5秒钟的就是慢SQL，并将它抓取出来。

3、explain + 慢SQL分析。

4、show profile。

5、运维经理 or DBA，进行SQL数据库服务器的参数调优。

优化原则：小表驱动大表，即小的数据集驱动大的数据集。

select * from A where id in (select id from B)
等价于:
for select id from B
for select * from A where A.id = B.id

当B表的数据集小于A表的数据集时，用in优于exists。

select * from A where exists (select 1 from B where B.id = A.id)
等价于：
for select * from A
for select * from B where B.id = A.id

当A表的数据集系小于B表的数据集时，用exists优于in。

exists用法

将exist左边的sql先执行，得到的数据匹配exist中的表，结果为true那么就将满足条件的记录保存下来。

mysql> select * from user where id in (select uid from user_account);
+----+-----------+------+------+----------+
| id | username  | sex  | age  | password |
+----+-----------+------+------+----------+
| 18 | 小a       | 女   |   12 | 111      |
| 20 | 小蓝      | 女   |   12 | 111      |
| 24 | 小黑      | 男   |   12 | 111      |
| 25 | 小拉拉    | 女   |   12 | 111      |
+----+-----------+------+------+----------+

mysql> select * from user where exists (select 1 from user_account where uid = user.id);
+----+-----------+------+------+----------+
| id | username  | sex  | age  | password |
+----+-----------+------+------+----------+
| 18 | 小a       | 女   |   12 | 111      |
| 20 | 小蓝      | 女   |   12 | 111      |
| 24 | 小黑      | 男   |   12 | 111      |
| 25 | 小拉拉    | 女   |   12 | 111      |
+----+-----------+------+------+----------+
4 rows in set (0.00 sec)

上述两个sql执行的结果一样但是效率不一样，in 用于子查询为小表，exists 用于左表为小表，这样效率更优。因为小表数据更少，速度更快，驱动整体速度。当然了，也可以使用index

OrderBy索引优化

ORDER BY子句，尽量使用Index方式排序，避免使用FileSort方式排序。也即 orderBy中的字段应当建立索引，且符合索引顺序，不然会产生filesort文件内排序（通过临时表进行文件内排序），效率低下

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

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

• ORDER BY语句使用索引最左前列。

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

ORDER BY 列 如果不在索引列上，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操作，反而得不偿失。

优化策略

1、增大sort_buffer_size参数的设置

2、增大max_length_for_sort_data参数的设置

为什么设置sort_buffer_size、max_length_for_sort_data参数能优化排序？

因为通过index/全表扫描，进行磁盘IO获取数据，如果需要排序将数据放入sort_buffer,在内存中进行排序，提高Order By的速度。

值得注意的是：如果order by 没有正确使用索引列，mysql会进行filesort（创建临时文件，然后对数据进行排序，最后销毁文件，这一步特别耗时间）。

注意：
Order by时select * 是一个大忌只Query需要的字段，这点非常重要。在这里的影响是;

当Query的字段大小总和小于max_length_for_sort_data而且排序字段不是TEXT|BLOB类型时，会用改进后的算法——单路排序，否则用老算法——多路排序。

两种算法的数据都有可能超出sort_buffer的容量，超出之后，会创建tmp文件进行合并排序，导致多次IO，但是用单路排序算法的风险会更大一些，所以要提高sort_buffer__size。
尝试提高sort_buffer_size，不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的。

尝试提高max_length_for_sort_data，提高这个参数，会增加用改进算法的概率。但是如果设的太高，数据总容量超出sort_buffer_size的概率就增大，明显症状是高的磁盘I/O活动和低的处理器使用率。

小结
order by排序列使用索引

MySql两种排序方式∶文件排序 或 扫描有序索引排序
MySql能为 排序 与 查询 使用相同的索引

例如：

创建复合索引 a_b_c (a, b, c)

order by能使用索引最左前缀

• ORDER BY a
• ORDER BY a, b
• ORDER BY a, b, c
• ORDER BY a DESC, b DESC, c DESC

如果WHERE使用素引的最左前缀定义为常量，则order by能使用索引

• WHERE a = const ORDER BY b,c
• WHERE a = const AND b = const ORDER BY c
• WHERE a = const ORDER BY b, c
• WHERE a = const AND b > const ORDER BY b, c

不能使用索引进行排序

• ORDER BY a ASC, b DESC, c DESC //排序不—致
• WHERE g = const ORDER BY b, c //产丢失a索引
• WHERE a = const ORDER BY c //产丢失b索引
• WHERE a = const ORDER BY a, d //d不是素引的一部分
• WHERE a in (…) ORDER BY b, c //对于排序来说,多个相等条件也是范围查询

GroupBy索引优化

group by 其实和order by 类似

GroupBy优化

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

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

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

慢查询日志

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。

慢查询sql具体指运行时间超过long_query_time（阀值）值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10秒以上的语句。

由他来查看哪些SQL超出了我们的最大忍耐时间值，比如一条sql执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒的sql，结合之前explain进行全面分析。

如何操作

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

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

查看是否开启及如何开启

查看是否开启

SHOW VARIABLES LIKE '%slow_query_log%'; 

开启慢查询日志，如果MySQL重启后则会失效。

set global slow_query_log=1;

注意：慢查询日志影响效率，因此不建议一直开启。

永久开启慢查询日志

修改my.cnf文件，[mysqld]下增加修改

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

慢查询日志位置

show variables like '%slow_query_log_file%';

设置慢sql记录的阀值

SHOW VARIABLES LIKE 'long_query_time%';

set global long_query_time=3;

注意：需要重新开一个bash/cmd，不然阀值还是10

案例：
模仿慢sql，让慢查询日志记录

select sleep(5);

show variables like '%slow_query_log_file%';

查看慢查询日志文件

可以看到test数据库中，哪一时间，出现了慢sql

查询当前系统中有多少条慢查询记录

mysql> show global status like 'Slow_queries';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 1     |
+---------------+-------+
1 row in set (0.00 sec)

慢查询日志分析工具mysqldumpslow

在生产环境中，可以使用MySQL提供的日志分析工具mysqldumpslow。

查看mysqldumpslow的帮助信息，mysqldumpslow --help。

s：是表示按照何种方式排序
c：访问次数
l：锁定时间
r：返回记录
t：查询时间
al：平均锁定时间
ar：平均返回记录数
at：平均查询时间
t：即为返回前面多少条的数据
g：后边搭配一个正则匹配模式，大小写不敏感的

常用mysqldumpslow命令

linux中慢查询日志文件 /var/lib/mysql/xiaoxuya-slow.log

• 得到返回记录集最多的10个SQL
  mysqldumpslow -s r -t 10 /var/lib/mysql/xiaoxuya-slow.log

• 得到访问次数最多的10个SQL
  mysqldumpslow -s c -t 10 /var/lib/mysql/xiaoxuya-slow.log

• 得到按照时间排序的前10条里面含有左连接的查询语句
  mysqldumpslow -s t -t 10 -g “left join” /var/lib/mysql/xiaoxuya-slow.log

• 另外建议在使用这些命令时结合│和more 使用，否则有可能出现爆屏情况
  mysqldumpslow -s r-t 10 /ar/lib/mysql/xiaoxuya-slow.log | more

慢查询日志

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。

慢查询sql具体指运行时间超过long_query_time（阀值）值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10秒以上的语句。

由他来查看哪些SQL超出了我们的最大忍耐时间值，比如一条sql执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒的sql，结合之前explain进行全面分析。

如何操作

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

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

查看是否开启及如何开启

查看是否开启

SHOW VARIABLES LIKE '%slow_query_log%'; 

开启慢查询日志，如果MySQL重启后则会失效。

set global slow_query_log=1;

注意：慢查询日志影响效率，因此不建议一直开启。

永久开启慢查询日志

修改my.cnf文件，[mysqld]下增加修改

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

慢查询日志位置

show variables like '%slow_query_log_file%';

设置慢sql记录的阀值

SHOW VARIABLES LIKE 'long_query_time%';

set global long_query_time=3;

注意：需要重新开一个bash/cmd，不然阀值还是10

案例：
模仿慢sql，让慢查询日志记录

select sleep(5);

show variables like '%slow_query_log_file%';

查看慢查询日志文件

可以看到test数据库中，哪一时间，出现了慢sql

查询当前系统中有多少条慢查询记录

mysql> show global status like 'Slow_queries';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| Slow_queries  | 1     |
+---------------+-------+
1 row in set (0.00 sec)

慢查询日志分析工具mysqldumpslow

在生产环境中，可以使用MySQL提供的日志分析工具mysqldumpslow。

查看mysqldumpslow的帮助信息，mysqldumpslow --help。

s：是表示按照何种方式排序
c：访问次数
l：锁定时间
r：返回记录
t：查询时间
al：平均锁定时间
ar：平均返回记录数
at：平均查询时间
t：即为返回前面多少条的数据
g：后边搭配一个正则匹配模式，大小写不敏感的

常用mysqldumpslow命令

linux中慢查询日志文件 /var/lib/mysql/xiaoxuya-slow.log

• 得到返回记录集最多的10个SQL
  mysqldumpslow -s r -t 10 /var/lib/mysql/xiaoxuya-slow.log

• 得到访问次数最多的10个SQL
  mysqldumpslow -s c -t 10 /var/lib/mysql/xiaoxuya-slow.log

• 得到按照时间排序的前10条里面含有左连接的查询语句
  mysqldumpslow -s t -t 10 -g “left join” /var/lib/mysql/xiaoxuya-slow.log

• 另外建议在使用这些命令时结合│和more 使用，否则有可能出现爆屏情况
  mysqldumpslow -s r-t 10 /ar/lib/mysql/xiaoxuya-slow.log | more

注意：提取到慢sql之后，可以使用explain/show profile 对sql进行分析优化

Mysql中索引失效常见原因和解决方案

Mysql中explain优化分析详解

show profile进行sql分析

准备50万数据

1、创建对应库和表

create database bigData;
use bigData;

CREATE TABLE dept(
	id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,
	deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,
	dname VARCHAR(20)NOT NULL DEFAULT "",
	loc VARCHAR(13) NOT NULL DEFAULT ""
)ENGINE=INNODB DEFAULT CHARSET=utf8;

CREATE TABLE emp(
    id int unsigned primary key auto_increment,
    empno mediumint unsigned not null default 0,
    ename varchar(20) not null default "",
    job varchar(9) not null default "",
    mgr mediumint unsigned not null default 0,
    hiredate date not null,
    sal decimal(7,2) not null,
    comm decimal(7,2) not null,
    deptno mediumint unsigned not null default 0
)ENGINE=INNODB DEFAULT CHARSET=utf8;

2、设置参数log_bin_trust_function_creators

show variables like 'log_bin_trust_function_creators';
set global log_bin_trust_function_creators=1;

由于开启过慢查询日志，因为我们开启了bin-log，我们就必须为我们的function指定一个参数。不然会报This function has none of DETERMINISTIC

3、创建函数 和 存储过程
3.1、随机字符串函数

delimiter $$ 
create function rand_string(n int) returns varchar(255)
begin
    declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyz';
    declare return_str varchar(255) default '';
    declare i int default 0;
    while i < n do
        set return_str = concat(return_str,substring(chars_str,floor(1+rand()*52),1));
        set i=i+1;
    end while;
    return return_str;
end $$

delimiter $$ 意为设置界限符，以 $ $结束的意思

3.2 随机产生部门编号函数

delimiter $$
create function rand_num() returns int(5)
begin
    declare i int default 0;
    set i=floor(100+rand()*10);
    return i;
end $$

函数记得要先执行，因为存储过程需要使用

3.3、创建往emp表中插入数据的存储过程

delimiter $$
create procedure insert_emp(in start int(10),in max_num int(10))
begin
    declare i int default 0;
    set autocommit = 0;
    repeat
        set i = i+1;
        insert into emp(empno,ename,job,mgr,hiredate,sal,comm,deptno) values((start+i),rand_string(6),'salesman',0001,curdate(),2000,400,rand_num());
        until i=max_num
        end repeat;
    commit;
end $$

3.4、创建往dept表中插入数据的存储过程

delimiter $$
create procedure insert_dept(in start int(10),in max_num int(10))
begin
    declare i int default 0;
    set autocommit = 0;
    repeat
        set i = i+1;
        insert into dept(deptno,dname,loc) values((start+i),rand_string(10),rand_string(8));
        until i=max_num
        end repeat;
    commit;
end $$

4、插入数据，调用存储过程

往部门表插入10条数据

mysql> CALL insert_dept(100, 10);

往员工表插入50万条数据

mysql> CALL insert_emp(100001, 500000);

注意：不要使用可视化软件插入数据，慢！

6、结果

show profile 分析步骤

1、查看当前mysql版本是否支持profile分析，

mysql> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | OFF   |
+---------------+-------+
1 row in set, 1 warning (0.00 sec)

2、开启profiling

mysql> set profiling = 1;
Query OK, 0 rows affected, 1 warning (0.00 sec)

mysql> show variables like 'profiling';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| profiling     | ON    |
+---------------+-------+
1 row in set, 1 warning (0.00 sec)

3、执行sql

select count(e.deptno) number , e.deptno from emp e 
left join dept d 
on e.deptno = d.deptno  
group by e.deptno 
order by e.deptno;

结果：

mysql> select count(e.deptno) number , e.deptno from emp e left join dept d on e.deptno = d.deptno  group by e.deptno order by e.deptno;
+--------+--------+
| number | deptno |
+--------+--------+
|  50113 |    100 |
|  50433 |    101 |
|  50018 |    102 |
|  49803 |    103 |
|  49803 |    104 |
|  50106 |    105 |
|  49949 |    106 |
|  49855 |    107 |
|  50005 |    108 |
|  49915 |    109 |
+--------+--------+
10 rows in set (0.47 sec)

explain结果分析

mysql> explain  select count(e.deptno) number , e.deptno from emp e left join dept d on e.deptno = d.deptno  group by e.deptno order by e.deptno;
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
|  1 | SIMPLE      | e     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 498620 |   100.00 | Using temporary; Using filesort                    |
|  1 | SIMPLE      | d     | NULL       | ALL  | NULL          | NULL | NULL    | NULL |     10 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+----------------------------------------------------+
2 rows in set, 1 warning (0.00 sec)

产生了临时表，文件内排序

4、 show profiles获取sql列表

mysql> show profiles;
+----------+------------+-------------------------------------------------------------------------------------------------------------------------------------------+
| Query_ID | Duration   | Query                                                                                                                                     |
+----------+------------+-------------------------------------------------------------------------------------------------------------------------------------------+
|        8 | 0.00013875 | set long_query_time = 2                                                                                                                   |
|        9 | 0.00244625 | show variables like 'long_query_time'                                                                                                     |
|       10 | 0.00184675 | show variables like 'profiling'                                                                                                           |
|       11 | 0.00035400 | explain select * from emp group by id%10 limit 150000                                                                                     |
|       12 | 0.00042575 | explain select id from emp group by id%10 limit 150000                                                                                    |
|       13 | 0.00050400 | select * from emp limit 10                                                                                                                |
|       14 | 0.16561600 | select deptno from emp group by deptno                                                                                                    |
|       15 | 0.00079050 | explain select deptno from emp group by deptno                                                                                            |
|       16 | 0.31239600 | select count(deptno) number  , deptno from emp group by deptno                                                                            |
|       17 | 0.00035800 | explain select count(deptno) number  , deptno from emp group by deptno                                                                    |
|       18 | 0.00059450 | select * from emp limit 10                                                                                                                |
|       19 | 0.00127075 | select * from dept limit 10                                                                                                               |
|       20 | 0.00418550 | select * from emp e left join dept d on e.deptno = d.deptno  limit 20                                                                     |
|       21 | 0.47500125 | select count(e.deptno) number , e.deptno from emp e left join dept d on e.deptno = d.deptno  group by e.deptno order by e.deptno          |
|       22 | 0.00038550 | explain  select count(e.deptno) number , e.deptno from emp e left join dept d on e.deptno = d.deptno  group by e.deptno order by e.deptno |
+----------+------------+-------------------------------------------------------------------------------------------------------------------------------------------+
15 rows in set, 1 warning (0.00 sec)

5、诊断SQL，show profile cpu,block io for query sqlQuery_ID;

我们选取执行时间最长的sql，查看其执行步骤

mysql> show profile cpu,block io for query 21;
+--------------------------------+----------+----------+------------+--------------+---------------+
| Status                         | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+--------------------------------+----------+----------+------------+--------------+---------------+
| starting                       | 0.000078 | 0.000000 |   0.000000 |         NULL |          NULL |
| Executing hook on transaction  | 0.000003 | 0.000000 |   0.000000 |         NULL |          NULL |
| starting                       | 0.000004 | 0.000000 |   0.000000 |         NULL |          NULL |
| checking permissions           | 0.000002 | 0.000000 |   0.000000 |         NULL |          NULL |
| checking permissions           | 0.000003 | 0.000000 |   0.000000 |         NULL |          NULL |
| Opening tables                 | 0.000037 | 0.000000 |   0.000000 |         NULL |          NULL |
| init                           | 0.000004 | 0.000000 |   0.000000 |         NULL |          NULL |
| System lock                    | 0.000007 | 0.000000 |   0.000000 |         NULL |          NULL |
| optimizing                     | 0.000008 | 0.000000 |   0.000000 |         NULL |          NULL |
| statistics                     | 0.000017 | 0.000000 |   0.000000 |         NULL |          NULL |
| preparing                      | 0.000028 | 0.000000 |   0.000000 |         NULL |          NULL |
| Creating tmp table             | 0.000058 | 0.000000 |   0.000000 |         NULL |          NULL |
| Sorting result                 | 0.000012 | 0.000000 |   0.000000 |         NULL |          NULL |
| executing                      | 0.000002 | 0.000000 |   0.000000 |         NULL |          NULL |
| Sending data                   | 0.474489 | 0.484375 |   0.000000 |         NULL |          NULL |
| Creating sort index            | 0.000108 | 0.000000 |   0.000000 |         NULL |          NULL |
| end                            | 0.000004 | 0.000000 |   0.000000 |         NULL |          NULL |
| query end                      | 0.000003 | 0.000000 |   0.000000 |         NULL |          NULL |
| waiting for handler commit     | 0.000013 | 0.000000 |   0.000000 |         NULL |          NULL |
| removing tmp table             | 0.000008 | 0.000000 |   0.000000 |         NULL |          NULL |
| waiting for handler commit     | 0.000003 | 0.000000 |   0.000000 |         NULL |          NULL |
| closing tables                 | 0.000008 | 0.000000 |   0.000000 |         NULL |          NULL |
| freeing items                  | 0.000087 | 0.000000 |   0.000000 |         NULL |          NULL |
| cleaning up                    | 0.000019 | 0.000000 |   0.000000 |         NULL |          NULL |
+--------------------------------+----------+----------+------------+--------------+---------------+
24 rows in set, 1 warning (0.00 sec)

可以看到，创建临时表（create tmp table ），发送数据（send data），创建排序索引，以及释放空间，最耗时，特别是数据量特别大的情况下

这也是为什么，我们要创建索引，一旦为group by ， order by 对应列 创建索引，可以免去创建临时表，和文件内排序（filesort）

我们在来看一个例子：

mysql> select  e.deptno from emp e group by e.deptno  order by e.deptno ;
+--------+
| deptno |
+--------+
|    100 |
|    101 |
|    102 |
|    103 |
|    104 |
|    105 |
|    106 |
|    107 |
|    108 |
|    109 |
+--------+
10 rows in set (0.16 sec)

mysql> explain  select  e.deptno from emp e group by e.deptno  order by e.deptno ;
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+---------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref  | rows   | filtered | Extra                           |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+---------------------------------+
|  1 | SIMPLE      | e     | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 498620 |   100.00 | Using temporary; Using filesort |
+----+-------------+-------+------------+------+---------------+------+---------+------+--------+----------+---------------------------------+
1 row in set, 1 warning (0.00 sec)

产生了filesort，temporary

sql诊断分析

mysql> show profile cpu , block io for query 41;
+--------------------------------+----------+----------+------------+--------------+---------------+
| Status                         | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out |
+--------------------------------+----------+----------+------------+--------------+---------------+
| starting                       | 0.000055 | 0.000000 |   0.000000 |         NULL |          NULL |
| Executing hook on transaction  | 0.000003 | 0.000000 |   0.000000 |         NULL |          NULL |
| starting                       | 0.000005 | 0.000000 |   0.000000 |         NULL |          NULL |
| checking permissions           | 0.000003 | 0.000000 |   0.000000 |         NULL |          NULL |
| Opening tables                 | 0.000042 | 0.000000 |   0.000000 |         NULL |          NULL |
| init                           | 0.000004 | 0.000000 |   0.000000 |         NULL |          NULL |
| System lock                    | 0.000006 | 0.000000 |   0.000000 |         NULL |          NULL |
| optimizing                     | 0.000038 | 0.000000 |   0.000000 |         NULL |          NULL |
| statistics                     | 0.000013 | 0.000000 |   0.000000 |         NULL |          NULL |
| preparing                      | 0.000008 | 0.000000 |   0.000000 |         NULL |          NULL |
| Creating tmp table             | 0.000056 | 0.000000 |   0.000000 |         NULL |          NULL |
| Sorting result                 | 0.000007 | 0.000000 |   0.000000 |         NULL |          NULL |
| executing                      | 0.000001 | 0.000000 |   0.000000 |         NULL |          NULL |
| Sending data                   | 0.000005 | 0.000000 |   0.000000 |         NULL |          NULL |
| Creating sort index            | 0.158347 | 0.156250 |   0.000000 |         NULL |          NULL |
| end                            | 0.000009 | 0.000000 |   0.000000 |         NULL |          NULL |
| query end                      | 0.000003 | 0.000000 |   0.000000 |         NULL |          NULL |
| waiting for handler commit     | 0.000009 | 0.000000 |   0.000000 |         NULL |          NULL |
| removing tmp table             | 0.000008 | 0.000000 |   0.000000 |         NULL |          NULL |
| waiting for handler commit     | 0.000002 | 0.000000 |   0.000000 |         NULL |          NULL |
| closing tables                 | 0.000007 | 0.000000 |   0.000000 |         NULL |          NULL |
| freeing items                  | 0.000067 | 0.000000 |   0.000000 |         NULL |          NULL |
| cleaning up                    | 0.000037 | 0.000000 |   0.000000 |         NULL |          NULL |
+--------------------------------+----------+----------+------------+--------------+---------------+
23 rows in set, 1 warning (0.00 sec)

sql优化

mysql> create index idx_emp_deptno on emp(deptno);
Query OK, 0 rows affected (3.17 sec)
Records: 0  Duplicates: 0  Warnings: 0

explain分析优化后的sql

mysql> explain  select  e.deptno from emp e group by e.deptno  order by e.deptno ;
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+--------------------------+
| id | select_type | table | partitions | type  | possible_keys  | key            | key_len | ref  | rows | filtered | Extra                    |
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+--------------------------+
|  1 | SIMPLE      | e     | NULL       | range | idx_emp_deptno | idx_emp_deptno | 3       | NULL |   10 |   100.00 | Using index for group-by |
+----+-------------+-------+------------+-------+----------------+----------------+---------+------+------+----------+--------------------------+
1 row in set, 1 warning (0.00 sec)

show profile分析优化后的sql

可以看到产生临时表，和filesort没有了，取而代之的index，执行时间也变快了。

show profile参数备注

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

日常开发需要注意的结论

• converting HEAP to MyISAM 查询结果太大，内存都不够用了往磁盘上搬了。

• Creating tmp table 创建临时表，拷贝数据到临时表，用完再删除

• Copying to tmp table on disk 把内存中临时表复制到磁盘，危险!
  locked

Mysql数据库锁

锁是计算机协调多个进程或线程并发访问某一资源的机制。

在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

锁的分类

从对数据操作的类型（读\写）分

• 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。

• 写锁（排它锁）：当前写操作没有完成前，它会阻断其他写锁和读锁。

从对数据操作的粒度分

• 表锁

• 行锁

表锁（偏读）

特点：偏向MyISAM存储引擎，开销小，加锁快；无死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。

MyISAM引擎表锁

建表SQL

create table mylock (
    id int not null primary key auto_increment,
    name varchar(20) default ''
) engine myisam;

insert into mylock(name) values('a');
insert into mylock(name) values('b');
insert into mylock(name) values('c');
insert into mylock(name) values('d');
insert into mylock(name) values('e');

mylock，加读锁

1、读锁是共享锁，因此多线程共享，其它人也能读取到数据
2、当session1加读锁，进行更改时，修改失败，读锁期间不允许修改
3、当session2进行，修改时，对应线程会一直堵塞。读锁期间不允许修改，知道读锁unlock后，才可以修改

mylock加写锁

1、写锁是互斥锁，多线程间互斥访问
2、session1一旦获取到写锁，未释放前，其它线程连读取数据的机会都没有因此session2会堵塞。相当于java中的Reentrantlock，synchronized

Innodb引擎行锁

偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

InnoDB与MyISAM的最大不同有两点：一是支持事务(TRANSACTION)；二是采用了行级锁。

事务ACID原则

事务是由一组SQL语句组成的逻辑处理单元，事务具有以下4个属性，通常简称为事务的ACID属性：

• 原子性(Atomicity）：事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。

• 一致性（Consistent）：在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改，以保持数据的完整性;事务结束时，所有的内部数据结构〈如B树索引或双向链表）也都必须是正确的。

• 隔离性（lsolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。

• 持久性（Durable）：事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

并发事务处理带来的问题

• 更新丢失(Lost Update)
• 脏读(Dirty Reads)
• 不可重复读(Non-Repeatable Reads)
• 幻读(Phantom Reads)

更新丢失(Lost Update)

当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题――最后的事务的更新覆盖了由其他事务所做的更新。

例如，两个程序员修改同一java文件。每程序员独立地更改其副本，然后保存更改后的副本，这样就覆盖了原始文档。最后保存其更改副本的编辑人员覆盖前一个程序员所做的更改。

如果在一个程序员完成并提交事务之前，另一个程序员不能访问同一文件，则可避免此问题。

脏读(Dirty Reads)

一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被形象地叫做”脏读”。

总之：事务A读取到了事务B已修改但尚未提交的的数据，还在这个数据基础上做了操作。

不可重复读(Non-Repeatable Reads)

一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变、或某些记录已经被删除了!这种现象就叫做“不可重复读”。

总结：事务A读取到了事务B已经提交的修改数据，不符合隔离性。

幻读(Phantom Reads)

一个事务接相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读“。

**一句话:事务A读取到了事务B体提交的新增数据，**不符合隔离性。

事务隔离级别

”脏读”、“不可重复读”和“幻读”，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决。

查看当前数据库的事务隔离级别

mysql> show variables like '%isolation%';
+-----------------------+-----------------+
| Variable_name         | Value           |
+-----------------------+-----------------+
| transaction_isolation | REPEATABLE-READ |
+-----------------------+-----------------+
1 row in set, 1 warning (0.00 sec)

innodb默认事务的隔离为可重复读

行锁示例

索引失效行锁变表锁

间隙锁

什么是间隙锁

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁，对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP）”。

InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。

例如：

数据表

id name
1 a
3 a
4 a
5 a

select id,name where id >= 1 and id <=5; 

如果此时insert 一行记录, 这一线程会被堵塞，因为2对应的记录行被间隙锁，锁住了

insert into (id,name) values (2,'a');

mysql会为1~5行全部加上行锁，虽然第2行没有数据，也会加上间隙锁，在session1事务没有commit之前，一直被锁住，其它session无法操作。

危害

因为Query执行过程中通过过范围查找的话，他会锁定整个范围内所有的索引键值，即使这个键值并不存在。

间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害。

mysql锁定一行

使用 for update