尚硅谷MySQL课程笔记

P10-P20索引优化分析

原文： https://blog.youkuaiyun.com/qq_56224861/article/details/115595204

SQL 性能下降原因分析

分析

• 执行时间长
• 等待时间长

原因

• 查询语句不好
• 索引失效（单值，复合）
• 关联查询太多 join
• 服务器调优及各个参数设置（缓冲、线程数等）

SQL 执行顺序

手写

机读

什么是索引？

索引是帮助 MySQL 高效获取数据的数据结构。
也可以理解为是：

“排好序的快速查找数据结构”

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

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。
我们平常的索引，如果没有特别指定，都是指 B 树（多路搜索树，并不一定是二叉的）结构组织的索引。 其中聚集索引，次要索引，复合索引，前缀索引，唯一索引默认都是用 B + 树索引，统称索引。除了 B + 树之外，还有 hash 索引等。

索引的优势

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

索引的劣势

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

索引类型

• 单值索引

一个索引只包含单个列，一个表可以有多个单列索引

• 唯一索引

索引列的指必须唯一，但允许有空值

• 复合索引

一个索引包含多个列

基本语法

创建

CREATE [UNIQUE] INDEX indexName ON tableName（columnName）
ALTER tableName ADD[UNIQUE] INDEX [indexName] on（columnName）

删除

DROP INDEX [indexName] on tableName

查看

SHOW INDEX FROM tableName\G

使用 ALTER 命令

• ALTER TABLE tablename ADD PRIMARY KEY(columnname)

该语句添加了一个主键，意味着索引值必须唯一，且不能为 NULL

• ALTER TABLE tablename ADD UNIQUE index_name（column_list)

创捷索引的值必须是唯一的（NULL 可能会出现多次）

• ALTER TABLE tablename ADD INDEX index_name（column_list)

添加普通索引，索引值可出现多次

• ALTER TABLE tablename ADD FULLTEXT index_name （column_list)

指定索引是 FULLTEXT，用于全文索引

索引结构

• BTree 索引
• Hash 索引
• fulltext 全文索引
• R-Tree 索引

Btree 检索原理

浅蓝色的块称之为磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示）。
P1 表示小于 17 的磁盘块，P2 表示在 17 和 35 之间的磁盘块，P3 表示大于 35 的磁盘块。
真实的数据存在于叶子节点即 3，5，9，10，13，15，28，29，36，68，75，79，90，99.
非叶子节点不存储真实的数据，只存储指引搜索方向的数据项，如 17，35

【查找过程】
如果要查找数据项 29，那么首先会把磁盘块 1 由磁盘加载到内存，此时发生一次 IO，在内存中用二分法查找确定 29 在 17 和 35 之间，锁定磁盘块 1 的 P2 指针，通过磁盘块 1 的 P2 指针的磁盘地址把磁盘块 3 由磁盘加载到内存，发生第二次 IO，29 在 26 和 30 之间，锁定磁盘块 3 的 P2 指针，通过指针加载磁盘块 8 到内存，发生第三次 IO，同时内存中做二分法查找找到 29，总计三次 IO。

哪些情况需要建立索引

1. 主键自动创建唯一索引
2. 频繁作为查询条件的字段应该创建索引
3. 查询中与其他表关联的字段，外键关系建立索引
4. 单键 / 组合索引的选择问题（在高并发下倾向创建组合索引）
5. 查询中排序的字段
6. 查询中统计或分组的字段
7. 频繁更新的字段不适合创建索引
8. Where 条件中用不到的字段不创建索引

哪些情况不需要建立索引

1. 表记录太少
2. 经常增删改的表
3. 某个数据列包含大量重复内容（过滤性不强）

性能分析

MySql Query Optimizer

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

MySQL 常见瓶颈

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

P21-P30 explain 执行计划详解

原文： https://blog.youkuaiyun.com/wuseyukui/article/details/71512793

视频： P30 尚硅谷 explain 之热身 Case

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

explain 执行计划包含的信息

其中最重要的字段为：id、type、key、rows、Extra

explain 有两个变种：

1）explain extended：会在 explain 的基础上额外提供一些查询优化的信息。紧随其后通过 show warnings 命令 可以得到优化后的查询语句，从而看出优化器优化了什么。额外还有 filtered 列，是一个百分比的值，rows * filtered/100 可以估算出将要和 explain 中前一个表进行连接的行数（前一个表指 explain 中的 id 值比当前表 id 值小的表）。

mysql> explain extended select * from film where id = 1;
+----+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | film  | const | PRIMARY       | PRIMARY | 4       | const |    1 |   100.00 | NULL  |
+----+-------------+-------+-------+---------------+---------+---------+-------+------+----------+-------+

mysql> show warnings;
+-------+------+--------------------------------------------------------------------------------+
| Level | Code | Message                                                                        |
+-------+------+--------------------------------------------------------------------------------+
| Note  | 1003 | /* select#1 */ select '1' AS `id`,'film1' AS `name` from `test`.`film` where 1 |
+-------+------+--------------------------------------------------------------------------------+

2）explain partitions：相比 explain 多了个 partitions 字段，如果查询是基于分区表的话，会显示查询将访问的分区。

各字段详解

1. id

select 查询的序列号，包含一组数字，表示查询中执行 select 子句或操作表的顺序
三种情况：
1、id 相同：执行顺序由上至下

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

3、id 相同又不同（两种情况同时存在）：id 如果相同，可以认为是一组，从上往下顺序执行；在所有组中，id 值越大，优先级越高，越先执行

2. select_type

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

1、SIMPLE：简单的 select 查询，查询中不包含子查询或者 union
2、PRIMARY：查询中包含任何复杂的子部分，最外层查询则被标记为 primary
3、SUBQUERY：在 select 或 where 列表中包含了子查询
4、DERIVED：在 from 列表中包含的子查询被标记为 derived（衍生），mysql 或递归执行这些子查询，把结果放在零时表里
5、UNION：若第二个 select 出现在 union 之后，则被标记为 union；若 union 包含在 from 子句的子查询中，外层 select 将被标记为 derived
6、UNION RESULT：从 union 表获取结果的 select

3. type

访问类型，sql 查询优化中一个很重要的指标，结果值从好到坏依次是：

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

一般来说，好的 sql 查询至少达到 range 级别，最好能达到 ref

1、system：表只有一行记录（等于系统表），这是 const 类型的特例，平时不会出现，可以忽略不计

2、const：表示通过索引一次就找到了，const 用于比较 primary key 或者 unique 索引。因为只需匹配一行数据，所有很快。如果将主键置于 where 列表中，mysql 就能将该查询转换为一个 const

3、eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键 或 唯一索引扫描。

注意：ALL 全表扫描的表记录最少的表如 t1 表

4、ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质是也是一种索引访问，它返回所有匹配某个单独值的行，然而他可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体

5、range：只检索给定范围的行，使用一个索引来选择行。key 列显示使用了那个索引。一般就是在 where 语句中出现了 bettween、<、>、in 等的查询。这种索引列上的范围扫描比全索引扫描要好。只需要开始于某个点，结束于另一个点，不用扫描全部索引

6、index：Full Index Scan，index 与 ALL 区别为 index 类型只遍历索引树。这通常为 ALL 块，应为索引文件通常比数据文件小。（Index 与 ALL 虽然都是读全表，但 index 是从索引中读取，而 ALL 是从硬盘读取）

7、ALL：Full Table Scan，遍历全表以找到匹配的行

4. possible_keys

查询涉及到的字段上存在索引，则该索引将被列出，但不一定被查询实际使用

5. key

实际使用的索引，如果为 NULL，则没有使用索引。
查询中如果使用了覆盖索引，则该索引仅出现在 key 列表中

6. key_len

表示索引中使用的字节数，查询中使用的索引的长度（最大可能长度），并非实际使用长度，理论上长度越短越好。key_len 是根据表定义计算而得的，不是通过表内检索出的。

这一列显示了 mysql 在索引里使用的字节数，通过这个值可以算出具体使用了索引中的哪些列。

举例来说，film_actor 的联合索引 idx_film_actor_id 由 film_id 和 actor_id 两个 int 列组成，并且每个 int 是 4 字节。通过结果中的 key_len=4 可推断出查询使用了第一个列：film_id 列来执行索引查找。

mysql> explain select * from film_actor where film_id = 2;
+----+-------------+------------+------+-------------------+-------------------+---------+-------+------+-------------+
| id | select_type | table      | type | possible_keys     | key               | key_len | ref   | rows | Extra       |
+----+-------------+------------+------+-------------------+-------------------+---------+-------+------+-------------+
|  1 | SIMPLE      | film_actor | ref  | idx_film_actor_id | idx_film_actor_id | 4       | const |    1 | Using index |
+----+-------------+------------+------+-------------------+-------------------+---------+-------+------+-------------+

key_len 计算规则如下：

• 字符串
  • char(n)：n 字节长度
  • varchar(n)：2 字节存储字符串长度，如果是 utf-8，则长度 3n + 2
• 数值类型
  • tinyint：1 字节
  • smallint：2 字节
  • int：4 字节
  • bigint：8 字节
• 时间类型
  • date：3 字节
  • timestamp：4 字节
  • datetime：8 字节
• 如果字段允许为 NULL，需要 1 字节记录是否为 NULL

索引最大长度是 768 字节，当字符串过长时，mysql 会做一个类似左前缀索引的处理，将前半部分的字符提取出来做索引。

7. ref

这一列显示了在 key 列记录的索引中，表查找值所用到的列或常量，常见的有：const（常量），func，NULL，字段名（例：film.id）

8. rows

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

9. Extra

不适合在其他字段中显示，但是十分重要的额外信息

1、Using filesort ：
mysql 对数据使用一个外部的索引排序，而不是按照表内的索引进行排序读取。也就是说 mysql 无法利用索引完成的排序操作成为 “文件排序”

由于索引是先按 email 排序、再按 address 排序，所以查询时如果直接按 address 排序，索引就不能满足要求了，mysql 内部必须再实现一次 “文件排序”

2、Using temporary：
使用临时表保存中间结果，也就是说 mysql 在对查询结果排序时使用了临时表，常见于 order by 和 group by

3、Using index：
表示相应的 select 操作中使用了覆盖索引（Covering Index），避免了访问表的数据行，效率高
如果同时出现 Using where，表明索引被用来执行索引键值的查找（参考上图）
如果没用同时出现 Using where，表明索引用来读取数据而非执行查找动作

覆盖索引（Covering Index）：也叫索引覆盖。就是 select 列表中的字段，只用从索引中就能获取，不必根据索引再次读取数据文件，换句话说查询列要被所建的索引覆盖。
注意：
a、如需使用覆盖索引，select 列表中的字段只取出需要的列，不要使用 select *
b、如果将所有字段都建索引会导致索引文件过大，反而降低 crud 性能

4、Using where ：
使用了非索引列作为 where 过滤条件

5、Using join buffer (Block Nested Loop)：
连接查询中，被驱动表不能利用索引查询，mysql分配内存块(join buffer)来加快查询。

6、Impossible WHERE：
where 子句的值总是 false，不能用来获取任何元祖

7、select tables optimized away：
在没有 group by 子句的情况下，基于索引优化 MIN/MAX 操作或者对于 MyISAM 存储引擎优化 COUNT（*）操作，不必等到执行阶段在进行计算，查询执行计划生成的阶段即可完成优化

8、distinct：
优化 distinct 操作，在找到第一个匹配的元祖后即停止找同样值得动作

综合 Case

执行顺序
1（id = 4）、【select id, name from t2】：select_type 为 union，说明 id=4 的 select 是 union 里面的第二个 select。

2（id = 3）、【select id, name from t1 where address = ‘11’】：因为是在 from 语句中包含的子查询所以被标记为 DERIVED（衍生），where address = ‘11’ 通过复合索引 idx_name_email_address 就能检索到，所以 type 为 index。

3（id = 2）、【select id from t3】：因为是在 select 中包含的子查询所以被标记为 SUBQUERY。

4（id = 1）、【select d1.name, … d2 from … d1】：select_type 为 PRIMARY 表示该查询为最外层查询，table 列被标记为 “derived3” 表示查询结果来自于一个衍生表（id = 3 的 select 结果）。

5（id = NULL）、【 … union … 】：代表从 union 的临时表中读取行的阶段，table 列的 “union 1, 4” 表示用 id=1 和 id=4 的 select 结果进行 union 操作。

P31索引单表优化案例

原文： https://blog.youkuaiyun.com/qq_56224861/article/details/115669404

原文视频： P31 尚硅谷 索引单表优化案例

建表语句

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'),
(1,1,3,3,'3','3');

初始查询

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

mysql> explain select id, author_id from article where category_id=1 and comments>1 order by views desc Limit 16;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+
| 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)

可以看到没有建索引的时候，type 是全表搜索，而且可能用的索引和实际索引都是 null；Extra 还出现了 filesort（文件内排序）

开始优化：

新建索引 idx_article_ccv

create index idx_atricle_CCV on article( 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_atricle_CCV |            1 | category_id | A         |           2 |     NULL | NULL   |      | BTREE      |         |               |
| article |          1 | idx_atricle_CCV |            2 | comments    | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
| article |          1 | idx_atricle_CCV |            3 | views       | A         |           3 |     NULL | NULL   |      | BTREE      |         |               |
+---------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
4 rows in set (0.00 sec)

使用了 idx_article_ccv 索引之后

mysql> explain select id, author_id from article where category_id=1 and comments>1 order by views desc Limit 16;
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+---------------------------------------+
| id | select_type | table   | partitions | type  | possible_keys   | key             | key_len | ref  | rows | filtered | Extra                                 |
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+---------------------------------------+
|  1 | SIMPLE      | article | NULL       | range | idx_atricle_CCV | idx_atricle_CCV | 8       | NULL |    1 |   100.00 | Using index condition; Using filesort |
+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.03 sec)

虽然解决了全表扫描的问题，但是 filesort 还没有解决。

因为按照 BTree 索引的工作原理
先排序 category_id，如果遇到相同的 category_id 则再排序 comments，如果再遇到相同的 comments 则再排序 views。
当 comments 字段在联合索引里处于中间位置时，
因为 comments>1 是一个范围（range）
MySQL 无法利用索引再对后面的 views 部分进行检索，即 range 类型查询字段后面的索引无效

第二次建立索引

尝试跳过 comments，建立 category_id 和 views 字段的索引

create index idx_atricle_CV on article(category_id, views);
drop idx_atricle_CCV on article;

mysql> explain select id, author_id from article where category_id=1 and comments>1 order by views desc Limit 16;
+----+-------------+---------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+
| id | select_type | table   | partitions | type | possible_keys  | key            | key_len | ref   | rows | filtered | Extra       |
+----+-------------+---------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+
|  1 | SIMPLE      | article | NULL       | ref  | idx_atricle_CV | idx_atricle_CV | 4       | const |    2 |    33.33 | Using where |
+----+-------------+---------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

可以发现 filesort 已经消失，说明这个索引可行

Backward index scan（倒叙索引）：是 MySQL8 的新特性，是有好处的。

P32索引两表优化案例

原文： https://blog.youkuaiyun.com/qq_56224861/article/details/115674104

原文视频： P32 尚硅谷 索引两表优化案例

建表语句

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

• 查看数据

select * from book;
select * from class;

无索引

• 第一次 explain

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

type 为 all；

左连接时索引建在右表上

• 在右表（book 表）上加上索引

mysql> create index Y on book(card) ;

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

• 在左表（class 表）上加索引

drop index Y on book(card);
create index Y on class(card);

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

可以看到 rows 并没有变化，而且 ref 优于 index

LEFT JOIN 条件用于确定从右表搜索行，左边一定都有，所以右边是我们的关键点，一定要加索引。

右连接时索引建在左表上

• 在右表（book 表）上加上索引

create index Y on book(card);

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

此时索引建在 book 表上

• 在左表（class 表）上加索引

drop index Y on book(card);
create index Y on class(card);

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

结论：

左连接的时候，索引应该建在右表。当右连接的时候，索引应该建在左表。左右连接关联的时候，另一张表才是关键点。

P33索引三表优化案例

原文： https://blog.youkuaiyun.com/qq_56224861/article/details/115677019

原文视频：P33 尚硅谷 索引三表优化案例

建表语句

结合 class 表和 book 表

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> explain select * from class left join book on class.card=book.card left join phone on book.card=phone.card;
+----+-------------+-------+------------+------+---------------+------+---------+-----------------+------+----------+----------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref             | rows | filtered | Extra                                              |
+----+-------------+-------+------------+------+---------------+------+---------+-----------------+------+----------+----------------------------------------------------+
|  1 | SIMPLE      | class | NULL       | ALL  | NULL          | NULL | NULL    | NULL            |   20 |   100.00 | NULL                                               |
|  1 | SIMPLE      | book  | NULL       | ref  | Y             | Y    | 4       | test.class.card |    1 |   100.00 | Using index                                        |
|  1 | SIMPLE      | phone | NULL       | ALL  | NULL          | NULL | NULL    | NULL            |   20 |   100.00 | Using where; Using join buffer (Block Nested Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+-----------------+------+----------+----------------------------------------------------+
3 rows in set, 1 warning (0.00 sec)

现在我们在 phone 表建立一个 Z 索引，在 book 表建一个 Y 索引

create index Z on phone(card);

再执行一次查询

mysql> explain select * from class left join book on class.card=book.card left join phone on book.card=phone.card;
+----+-------------+-------+------------+------+---------------+------+---------+-----------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key  | key_len | ref             | rows | filtered | Extra       |
+----+-------------+-------+------------+------+---------------+------+---------+-----------------+------+----------+-------------+
|  1 | SIMPLE      | class | NULL       | ALL  | NULL          | NULL | NULL    | NULL            |   20 |   100.00 | NULL        |
|  1 | SIMPLE      | book  | NULL       | ref  | Y             | Y    | 4       | test.class.card |    1 |   100.00 | Using index |
|  1 | SIMPLE      | phone | NULL       | ref  | Z             | Z    | 4       | test.book.card  |    1 |   100.00 | Using index |
+----+-------------+-------+------------+------+---------------+------+---------+-----------------+------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)

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

结论

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

P34-P41索引优化和口诀总结

原文： https://blog.youkuaiyun.com/qq_56224861/article/details/115677561

原文视频：索引失效总结

建表语句

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

创建复合索引

create index idx_staffs_nameAgePos on staffs(name, age, pos);

索引失效案例分析

1. 全值匹配我最爱

• explain select _ from staffs where name='july';

mysql> explain select * from staffs where name='july';
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys         | key                   | key_len | ref   | rows | filtered | Extra |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | staffs | NULL       | ref  | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 74      | const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

• explain select * from staffs where name='july' and age=23;

mysql> explain select * from staffs where name='july' and age=23;
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys         | key                   | key_len | ref         | rows | filtered | Extra |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+
|  1 | SIMPLE      | staffs | NULL       | ref  | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 78      | const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

• explain select * from staffs where name='july' and age=23 and pos='dev';

mysql> explain select * from staffs where name='july' and age=23 and pos='dev';
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+
| id | select_type | table  | partitions | type | possible_keys         | key                   | key_len | ref               | rows | filtered | Extra |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+
|  1 | SIMPLE      | staffs | NULL       | ref  | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 140     | const,const,const |    1 |   100.00 | NULL  |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

• explain select * from staffs where age=23 and pos='dev';

mysql> explain select * from staffs where age=23 and pos='dev';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | staffs | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |    33.33 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

• explain select * from staffs where name='july' and pos='dev';

mysql> explain select * from staffs where name='july' and pos='dev';
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table  | partitions | type | possible_keys         | key                   | key_len | ref   | rows | filtered | Extra                 |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+
|  1 | SIMPLE      | staffs | NULL       | ref  | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 74      | const |    1 |    33.33 | Using index condition |
+----+-------------+--------+------------+------+-----------------------+-----------------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)

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

2. 最左前缀法则

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

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

• explain select * from staffs where left(name,4)='July';

mysql> explain select * from staffs where left(name,4)='July';
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table  | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | staffs | NULL       | ALL  | NULL          | NULL | NULL    | NULL |    3 |   100.00 | Using where |
+----+-------------+--------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

索引列上少操作

4. 存储引擎不能使用索引中范围条件右边的列

• explain select * from staffs where name='july' and age>23 and pos='dev';

范围之后全失效

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

• explain select * from staffs where name='july' and age=23 and pos='dev';

• explain select name,age,pos from staffs where name='july' and age=23 and pos='dev';

6. mysql 在使用不等于（！= 或者 <>）的时候无法使用索引会导致范围扫描

• explain select * from staffs where name!='july';

7. is null，is not null 也无法使用索引

• explain select * from staffs where name is not null;

8. like 以通配符（‘%abc…’）mysql 索引失效会变成全表扫描的操作

• explain select * from staffs where name like '%July%';

• explain select * from staffs where name like '%July';

• explain select * from staffs where name like 'July%';

• 如何解决 like '% 字段 %'索引不被使用的方法？

------ 可以使用覆盖索引。like 的列有索引，被索引覆盖。

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

• explain select * from staffs where name='2000';

• explain select * from staffs where name=2000;

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

• explain select * from staffs where name='July' or name='z3';

小总结

优化总结口诀

全值匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
LIKE 百分写最右，覆盖索引不写 *；
不等空值还有 or，索引失效要少用；

P42索引题分析

原文： https://blog.youkuaiyun.com/qq_56224861/article/details/115679947

原文视频：索引面试题分析

索引的两大功能：查找、排序。

建表语句

create table test03(
id int primary key not null auto_increment,
c1 char(10),
c2 char(10),
c3 char(10),
c4 char(10),
c5 char(10)
);

insert into test03(c1,c2,c3,c4,c5) values('a1','a2','a3','a4','a5');
insert into test03(c1,c2,c3,c4,c5) values('b1','b2','b3','b4','b5');
insert into test03(c1,c2,c3,c4,c5) values('c1','c2','c3','c4','c5');
insert into test03(c1,c2,c3,c4,c5) values('d1','d2','d3','d4','d5');
insert into test03(c1,c2,c3,c4,c5) values('e1','e2','e3','e4','e5');

创建索引

create index idx_test03_c1234 on test03(c1,c2,c3,c4);

• 1） explain select * from test03 where c1='a1' and c2='a2' and c3='a3' and c4='a4';

• 2） explain select * from test03 where c4='a4' and c2='a2' and c3='a3' and c1='a1';

• 3） explain select * from test03 where c1='a1' and c2='a2' and c3>'a3' and c4='a4';

范围之后全失效。a3，a4 都失效。

• 4） explain select * from test03 where c1='a1' and c2='a2' and c4>'a4' and c3='a3';

a4 失效。

• 5） explain select * from test03 where c1='a1' and c2='a2' and c4='a4' order by c3;

c3 的作用在于排序而不是查找，也算是用到了。a4 未用到。

• 6） explain select * from test03 where c1='a1' and c2='a2' order by c3;

同上

• 7） explain select * from test03 where c1='a1' and c2='a2' order by c4;

中间兄弟不能断。出现了 filesort

• 8） explain select * from test03 where c1='a1' and c5='a5' order by c2,c3;

只用 c1 一个字段索引，但是 c2, c3 用于排序,无 filesort

• 8.1）explain select * from test03 where c1='a1' and c5='a5' order by c3,c2;

出现了 flesot, 我们建的索引是 1234, 它没有按照顺序来，3,2 颠倒了

• 9） explain select * from test03 where c1='a1' and c2='a2' order by c2,c3;

• 10） explain select * from test03 where c1='a1' and c2='a2' and c5='a5' order by c2,c3;

同上。用 c1，c2 两个字段索引，但是 c2、c3 用于排序，无 filesort

• 10.1） explain select * from test03 where c1='a1' and c2='a2' and c5='a5' order by c3,c2;

查找已经用到了 c2, c2 为常量，排序失效，有 filesort

本例有常量 c2 的情况，和 8.1 对比

• 10.2） explain select * from test03 where c1='a1' and c5='a5' order by c3,c2;

同上，有 filesort

• 11） explain select * from test03 where c1='a1' and c4='a4' order by c2,c3;

用到 c1，无 filesort

• 12） explain select * from test03 where c1='a1' and c4='a4' order by c3,c2;

Using temporary; Using filesort

一般性建议：

索引可用于查找（定值、范围）和排序，一般 order by 是给个范围

groupby 基本上都需要进行排序（同 order by），会有临时表产生

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

答疑思考

原文视频：P43 尚硅谷 索引优化答疑补充和总结口诀

• exp1ain select * from test03 where cl='al' and c2>'a2' and c3= 'a3';

c1,c2 用上了索引，c3 失效

• exp1ain se1ect * from test03 where c1='al' and c2 1ike 'kk%' and c3='a3';

c1,c2,c3 都用上了索引。like 是范围查询

• exp1ain se1ect * from test03 where c1='al' and c2 1ike '%kk' and c3='a3';

只有 c1 用上了索引

• exp1ain se1ect * from test03 where c1='al' and c2 1ike '%kk%' and c3='a3';

只有 c1 用上了索引

• exp1ain se1ect * from test03 where c1='al' and c2 1ike 'k%kk%' and c3='a3';

c1,c2,c3 都用上了索引。like 是范围查询

P44-P50查询截取分析

原文： https://blog.youkuaiyun.com/qq_56224861/article/details/115696448

原文视频：P44尚硅谷小表驱动大表

问题

• 何谓小表驱动大表？in 和 exists 的子查询放大数据集还是小数据集？
• ORDER BY列要注意什么？ORDER BY语句的优化配置有哪些？
• 何时会触发双路排序？双路排序和单路排序有什么区别？
• group by 实质是什么？查询要注意什么？
• 慢查询日志记录什么？如何开启慢查询日志？如何设置阈值？如何查询慢查询语句？
• 如何得到返回记录集最多的10个慢SQL？
• Show Profile如何查看某条SQL的cpu, io使用情况？
• 如何开启全局查询日志，并查看？全局查询日志要注意什么？

总结

1. 观察，至少跑 1 天，看看生产的慢 SQL 情况。
2. 开启慢查询日志。设置阙值，比如超过 5 秒钟的就是慢 SQL，并将它抓取出来。
3. explain+慢 SQL 分析
4. show profile 查询 SQL 在 Mysql 服务器里面的执行细节和生命周期情况
5. SQL 数据库服务器的参数调优。

查询优化

1. 永远小表驱动大表

优化原则：小表驱动大表，即小的数据集驱动大的数据集，小数据集先查询，再过滤大数据集。

-- ------------- 原理(RBO) --------------
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

SELECT … FROM table WHERE EXISTS(subquery)

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

提示

1. EXISTS 只返回 TRUE 或 FALSE，因此子查询中的 SELECT * 也可以是 SELECT 1，实际执行时会忽略 SELECT 清单，因此没有区别
2. EXISTS 子查询的实际执行过程可能进行了优化而不是我们理解的逐条对比，如果担忧效率问题，可进行实际校验以确定是否有效率问题
3. EXISTS 子查询往往也可以用条件表达式、其他子查询或者 JOIN 来替代，何种最优需要具体情况具体分析

结论

• 当小表在里时，使用in
• 当大表在里时，使用exists
• 简记：in小，exists大。

2. ORDER BY 语句优化

• ORDER BY 子句，尽量使用 Index 来排序，避免使用 Filesort 排序
• 尽可能在索引列上完成排序操作，遵守索引的最佳左前缀
• 如果不在索引列上，filesort 有两种算法：双路排序和单路排序

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

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

优化策略

• 增大 sort_buffer_size 参数的设置
• 增大 max_length_for_sort_data 的设置

总结

1. order by 时 select * 是一个大忌只 Query 需要的字段，在这里的影响是：

   • 当 Query 的字段大小总和小于 max_length_for_sort_data 而且排序字段不是 TEXT|BLOB 类型时，会用改进后的算法 ---- 单路排序，否则用老算法—多路排序
   • 两种算法的数据都有可能超出 sort_buffer_size 的容量，超出之后，会创建 tmp 文件进行合并排序，导致多次 I/O，但是用单路排序算法的风险会更大一些

2. 尝试提高 sort_buffer_size

   • 不管用哪种算法，提高这个参数都会提高效率，要根据系统的能力去提高

3. 尝试提高 max_length_for_sort_data

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

3. GROUP BY 关键字优化

• group by 实质是先排序后进行分组，按照索引建的最佳左前缀。
• 当无法使用索引列，增大 max_length_for_sort_data 参数的设置 + 增大 sort_buffer_size 参数的设置。
• where 高于 having，能写在 where 限定的条件就不要去 having 限定。

慢查询日志

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

• 查看是否开启及如何开启（默认关闭）

show VARIABLES like '%slow_query_log%';

• 开启（只对当前数据库生效，重启失效)

set global slow_query_log=1;

• 查看参数 long_query_time（规定超过时间）

show VARIABLES like '%long_query_time%';

• 设置参数 long_query_time（需要重新连接或新开一个会话才能看到修改值)

set global long_query_time=5;

• 查询有几条慢查询语句

show global status like '%Slow_queries';  -- 显示有几条慢查询

可以直接查看慢查询日志文件。

日志分析工具 mysqldumpslow

在生产环境中，如果要手工分析日志，查找、分析 SQL，MySQL 提供了日志分析工具 mysqldumpslow

mysqldumpslow --help

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

常用命令

• 得到返回记录集最多的10个SQL

mysqldumpslow -S r -t 10 /var/lib/mysql/atguigu-slow.log

• 得到访问次数最多的10个SQL

mysqldumpslow -S c -t 10 /var/lib/mysql/atguigu-slow.log

• 得到按照时间排序的前10条里面含有左连接的查询语句

mysqldumpslow -s t -t 10 -g "left join" /var/ib/mysql/atguigu-slow.log

• 另外建议在使用这些命令时结合|和more使用，否则有可能出现爆屏情况

mysqldumpslow -s r -t 10 /var/ib/mysql/atguigu-slow.log | more

批量数据脚本

设置参数 log_bin_trust_function_creators

set global log_bin_trust_function_creators=1；

创建函数随机产生字符串

delimiter $$
create function ran_string(n int) returns varchar(255)
begin
declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
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 $$
create function rand_num() returns int(5)
begin
declare i int default 0;
set i=floor(100+rand()*10);
return i;
end $$

创建存储过程

//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),ran_string(6),'salesman',0001,curdate(),2000,400,rand_num());
until i=max_num
end repeat;
commit;
end $$

//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),ran_string(10),ran_string(8));
until i=max_num
end repeat;
commit;
end $$

调用存储过程

//dept表
DELIMITER;
CALL insert_dept(100,10);

//emp表
DELIMITER;
CALL insert_emp(100001,500000);

Show Profile

Show Profile 是 mysql 提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于 SQL 的调优的测量，默认情况下，参数处于关闭状态，并保存最近 15 次的运行结果。

分析步骤

1. 当前 mysql 版本是否支持 ------- show VARIABLES like 'profiling';

2. 开启功能 ------- set profiling=on;

3. 运行 SQL

4. 查看结果 ------- show profiles;

5. 诊断 SQL------- show profile cpu,block io for query Query_ID(语句 id);

   

6. 日常开发需要注意的结论

converting HEAP to MyISAM ---- 查询结果太大，内存不够用
Creating tmp table ---- 创建了临时表
Copying to tmp table on disk ---- 把内存中临时表复制到磁盘，危险！！！
locked

全局查询日志

set global general_log=1;
set global log_output='TABLE';
//查询sql
select * from mysql.general_log;

永远不要在生产环境开启这个功能

P51-P60 Mysql 锁机制

原文： https://blog.youkuaiyun.com/qq_56224861/article/details/115701667

问题

• 两种锁的分类方法是什么？
• 当给一个MyISAM表加上读锁，本进程读写会怎样？其他进程读写会怎样？如何实验？
• 当给一个MyISAM表加上写锁，本进程读写会怎样？其他进程读写会怎样？如何实验？
• 如何找出哪些表被锁定？哪个进程锁定？
• ACID英文拼写？具体内容是什么？
• 什么是脏读、不可重复读、幻读？
• 事务隔离级别有哪几种？它们与脏读、不可重复读、幻读的关系？
• 当关闭自动提交，更新InnoDB表的一行数据时，本进程读写会怎样？其他进程读写会怎样？如何实验？
• 什么是间隙锁？它有什么危害？
• 如何锁定一行?
• 如何分析行锁定？

---

概述

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

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

锁的分类

• 按数据操作的类型分 ---- 读锁（共享锁）、写锁（排他锁）
• 按数据操作的粒度分 ---- 表锁、行锁

表锁

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

建表

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

手动增加表锁

lock table 表名 read(write),表名2 read(write),其它；

查看表上加过的锁（0–没上锁，1–上锁）

show open tables;

释放锁：在本进程才有用，只能自己解锁自己。

unlock tables;

加入 read 锁后

lock table mylock read;

两个进程都可以查询，但当前进程不可以修改，其他进程修改会阻塞等待，当前进程不可以访问其他表

加入 write 锁后

当前进程

其他进程

案例结论

MyISAM 在执行查询语句前，会自动给涉及的所有表加读锁，在执行增删改操作前，会自动给涉及的表加写锁。MySQL 的表级锁有两种模式：

• 表共享读锁
• 表独占写锁
  

结论

1. 对 MyISAM 表的读操作（加读锁），不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。本进程也不能进行写操作。只有当读锁释放时，才会执行其他线程的写操作
2. 对 MyISAM 表的写操作（加写锁），会阻塞其他进程对同一表的读和写操作，只有当写锁释放时，才会执行其他线程的读写操作

读锁会阻塞写，但不会阻塞读，写锁会把读和写都阻塞

如何分析表锁定

可以通过检查 table_locks_waited 和 table_locks_immediate 状态变量来分析系统上的表锁定；
show status like 'table%';

table_locks_immediate：表示可以获取锁的查询次数，每获取锁值加 1
table_locks_waited：不能立刻获取锁的次数，每等待一次锁值加 1，值高说明存在严重的表级锁争用情况

-- 查询是否锁表
show OPEN TABLES where In_use > 0;

-- 查看所有进程
show processlist;

-- 查询到相对应的进程===然后 kill id
kill $pid
 
-- 查看服务器状态
show status like '%lock%';
-- 查看超时时间：
show variables like '%timeout%';

基础知识回顾

事务及 ACID

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

• 原子性(Atomicity) : 事务是一个原子操作单元，其对数据的修改，要么全都执行，要么全都不执行。
• **一致性(Consistent) **: 数据库总是从一个一致性的状态转换到另外一个一致性的状态。
• 隔离性(Isolation) : 一个事务所做的修改在最终提交以前，对其他事务是不可见的。
• **持久性(Durable) **: 事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

并发事务处理带来的问题

更新丢失(Lost Update)

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

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

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

脏读(Dirty Reads)

一个事务读取了另一个事务操作但未提交的数据。

比如：A、B 两个事务，都操作同一张表，A 刚刚对数据进行了操作（插入、修改等）但还没有提交，这时 B 读取到了 A 刚刚操作的数据，因为 A 有可能回滚，所以这部分数据有可能只是临时的、无效的，即脏数据。

不可重复读(Non-Repeatable Reads)

一个事务中的多个相同的查询返回了不同数据，不符合隔离性。

比如：A、B 两个事务，A 中先后有两次查询相同数据的操作，第一次查询完之后，B 对相关数据进行了修改，造成 A 事务第二次查询出的数据与第一次不一致。

幻读(Phantom Reads)

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

比如：A、B 两个事务，事务 A 操作表中符合条件的若干行。事务 B 插入符合 A 操作条件的数据行，然后再提交。后来发现事务 A 并没有如愿对“所有”符合条件的数据行做了修改。

幻读和脏读的区别：

• 脏读是事务 B 里面修改了数据,
• 幻读是事务 B 里面新增了数据。

事务隔离级别

• Read uncommitted (读未提交)：如果设置了该隔离级别，则当前事务可以读取到其他事务已经修改但还没有提交的数据。这种隔离级别是最低的，会导致上面所说的脏读
• Read committed (读已提交)：如果设置了该隔离级别，当前事务只可以读取到其他事务已经提交后的数据，这种隔离级别可以防止脏读，但是会导致不可重复读和幻读。这种隔离级别最效率较高，并且不可重复读和幻读在一般情况下是可以接受的，所以这种隔离级别最为常用。
• Repeatable read (可重复读)：如果设置了该隔离级别，可以保证当前事务中多次读取特定记录的结果相同。可以防止脏读、不可重复读，但是会导致幻读。
• Serializable (串行化)：如果设置了该隔离级别，所有的事务会放在一个队列中执行，当前事务开启后，其他事务将不能执行，即同一个时间点只能有一个事务操作数据库对象。这种隔离级别对于保证数据完整性的能力是最高的，但因为同一时刻只允许一个事务操作数据库，所以大大降低了系统的并发能力。

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

隔离级别	读数据一致性	脏读	不可重复读	幻读
未提交读( Read uncommitted )	最低级别,只能保证不读取物理上损坏的数据	是	是	是
已提交度( Read committed )	语句级	否	是	是
可重复读( Repeatable read )	事务级	否	否	是
可序列化( Serializable )	最高级别,事务级	否	否	否

数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使事务在一定程度上“串行化”进行，这显然与“并发”是矛盾的。同时，不同的应用对读-致性和事务隔离程度的要求也是不同的，比如许多应用对“不可重复读”和“幻读”并不敏感，可能更关心数据并发访问的能力。

常看当前数据库的事务隔离级别: show variables like 'tx_isolation';

行锁

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

InnoDB 于 MyISAM 的最大不同有两点：

1. 支持事务（TRANSACTION)
2. 采用了行级锁

建表

CREATE TABLE test_innodb_lock (
a INT(11),
b VARCHAR(16)
)ENGINE=INNODB;

insert into test_innodb_lock values(1, b2);
insert into test_innodb_lock values(3, 3);
insert into test_innodb_lock values(4, 4000);
insert into test_innodb_lock values(5, 5000);
insert into test_innodb_lock values(6, 6000);
insert into test_innodb_lock values(7, 7000);
insert into test_innodb_lock values(8, 8000);
insert into test_innodb_lock values(9, 9000);
insert into test_innodb_lock values(1, b1000);

create index test_innodb_a_ind on test_innodb_lock(a);
create index test_innodb_b_ind on test_innodb_lock(b);

行锁定基本演示

关闭自动提交

set autocommit=0;

当前进程修改值

两个线程都修改值（其他线程阻塞）

两个线程修改不同值

索引失效行锁变表锁

发生了阻塞，说明类型转换导致了索引失效，行锁变表锁；

间隙锁危害

什么是间隙锁

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

【危害】
因为 Query 执行过程中通过范围查找的话，他会锁定整个范围内所有的索引键值，即使这个键值并不存在。间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定情况下无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害。

如何锁定一行

for update

分析行锁定

通过检查 innodb_row_lock 状态变量来分析系统上的行锁的争夺情况
show status like ‘innodb_row_lock%’；

innodb_row_lock_current_waits：当前正在等待锁定的数量
innodb_row_lock_time：从系统启动到现在锁定总时间长度
innodb_row_lock_time_avg: 每次等待所花平均时间
innodb_row_lock_time_max: 从系统启动到现在等待最长的一次所花时间
innodb_row_lock_waits: 从系统启动后总共等待的次数

-- 查询是否锁表
show OPEN TABLES where In_use > 0;

-- 查看所有进程
show processlist;

-- 查询到相对应的进程===然后 kill id
kill $pid

-- 查看正在锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS; 

-- 查看等待锁的事务
SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCK_WAITS; 

-- 查看innodb引擎的运行时信息
show engine innodb status\G;

-- 查看造成死锁的sql语句，分析索引情况，然后优化sql语句；
 
-- 查看服务器状态
show status like '%lock%';
-- 查看超时时间：
show variables like '%timeout%';

优化建议

• 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
• 合理涉及索引，尽量缩小锁的范围
• 尽可能较少检索条件，避免间隙锁
• 尽量控制事务大小，减少锁定资源量和时间长度
• 尽可能低级别事务隔离