数据库高级/一篇搞定MySQL优化，索引优化（最详细）

码字不易，转载标明出处。 欢迎纠错交流，Q:1847881689

一、MySQL架构介绍

1. MySQL简介

一个关系型数据库管理系统，由瑞典MySQL AB公司开发，目前属于Oracle公司。
一种关联数据库管理系统，将数据保存在不同的表中，而不是将所有数据放在一个大仓库里，这样可以增加速度并提升灵活性。
开源的，无需支付额外费用。
支持大型的数据库，可以拥有长千万条记录的大型数据库。32位操作系统最大可支持4GB，64位操作系统最大的表文件为8TB。
使用标准的SQL数据语言形式。
可以允许于多个系统上，支持多种语言。例如：C、C++、Python、Java、Perl、PHP、Eiffel、Ruby等。
对PHP有很好的支持。
可定制，采用GPL协议，可以通过修改源码来开发自己的MySQL系统。

2. Linux安装MySQL

略

3. MySQL配置文件

1) 二进制日志 log-bin

主要应用于主从复制，记录数据库增量日志，用于主从复制。

2）错误日志 log-error

默认是关闭的，记录严重的警告和错误信息，每次启动和关闭的详细信息等。

3）查询日志 log

默认是关闭的，记录查询的sql语句，如果开启会降低mysql整体性能，因为记录日志是需要消耗系统资源的。可以通过设置阈值，例如记录所有超过五秒以上的查询sql语句。

4）数据文件

文件	功能
frm文件	存放表结构
myd文件	存放表数据
myi文件	存放表索引

5) 如何配置

操作系统	文件
windows	my.ini
linux	/etc/my.cnf

4.MySQL的逻辑架构介绍

1）总体概览

和其他数据库相比，MySQL有点与众不同，它得架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎得架构上，插件式得存储引擎架构将查询处理和其他得系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。

连接层 ⇒ 服务层 ⇒ 引擎层 ⇒ 存储层

5. MySQL存储引擎

1) 查看命令

mysql>show variables like '%storage_engine%';

2) MyISAM和InnoDB区别

对比项	MyISAM	InnoDB
主外键	不支持	支持
事务	不支持	支持
行表锁	表锁，即使操作一条记录也会锁住整个表，不适合高并发场景	行锁，操作时只锁定某一行，不对其它行有影响，适合高并发场景
缓存	只缓存索引，不缓存数据	不仅缓存索引还缓存真实数据，对内存要求较高，而且内存大小对性能有决定性的影响
表空间	小	大
关注点	性能	事务

二、索引优化分析

1.SQL性能下降的原因

1) 查询语句写的没眼看

连接，子查询，没有用到索引，或没有建立索引。

2) 索引失效

建立了索引，但是没有用到。

单值索引： 只给某一个字段建立索引，应用于频繁进行单条件的查询语句，例如：

select * from user where name = "?";

create index inx_user_name on user(name); //建立单值索引

在建立索引后，索引字段会进行排序，大幅提升查询速度。

复合索引： 给某些字段建立复合索引，应用于频繁进行多条件查询语句，例如：

select * from user where name = "?" and email = "?";

create index idx_user_nameEmail on user(name,email); //建立复合索引

3) 关联查询太多join (设计缺陷或不得已的需求)

join越少越好。

4）服务器调优以及各个参数设置

例如缓冲、线程数等。

2.常见通用的join查询

1) SQL执行顺序

2）七种join图

先上两张测试表

1. 第一种
   

SELECT * FROM emp a INNER JOIN dept b on a.dept_id = b.id

执行结果：

2. 第二种
   

SELECT * from emp a left join dept b on a.dept_id = b.id

执行结果：

3. 第三种
   

SELECT * from emp a RIGHT join dept b on a.dept_id = b.id

执行结果：

4. 第四种
   

SELECT * from emp a left join dept b on a.dept_id = b.id where b.id is null

执行结果：

5. 第五种
   
   sql同第四种，修改left ==> reight

6. 第六种
   

select * from emp a left join dept b on a.dept_id = b.id union select * from emp a right join dept b on a.dept_id = b.id

执行结果：

7. 第七种
   

select * from emp a left join dept b on a.dept_id = b.id where b.id is null union select * from emp a right join dept b on a.dept_id = b.id where a.dept_id is null

执行结果：

3. 索引介绍

1）索引是什么

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。
在数据本身之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构上实现高级查找算法。下图是一种可能的索引方式示例（二叉查找树）：

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

适合建立索引的字段是不常进行删改操作且经常查询的字段。

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

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

2) 索引优势和劣势

优势：

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

劣势：

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

3）索引分类

类别	说明
单值索引	即一个索引只包含单个列，一个表可以有多个单列索引
唯一索引	索引列的值必须唯一，但允许空值
复合索引	即一个索引包含多个列

4）索引基本语法

• 添加索引

//添加主键索引，唯一且不能为NULL
alter table table_name add primary key (column_list);
//添加唯一索引，唯一可为NULL
alter table table_name add unique index_name (column_list);
//添加普通索引，索引值可出现多次
alter table table_name add index index_name (column_list);
//添加全文索引
alter table table_name add fulltext index_name (column_list);

• 删除索引

drop index index_name on table_name

• 查看索引

show index from table_name

5) 创建索引条件

1. 主键自动建立唯一索引。
2. 查询中与其他表关联的字段，外键关系应该建立索引。
3. 频繁作为查询条件的字段应该建立索引。
4. 单值/复合索引的选择问题，高并发下倾向于创建复合索引。
5. 查询中排序字段，排序字段若通过索引去访问将大大提高排序速度。
6. 查询中同级或者分组的字段要建立索引。
7. 记录太少不需要建立索引。
8. 频繁更新的字段不适合建立索引。
9. 数据重复且分布平均的表字段不适合建立索引。
10. where条件里用不到的字段不适合创建索引。

4.sql性能分析

1)MySQL常见瓶颈

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

2)Explain关键字

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

• 作用

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

• 怎么玩

explain + sql

执行结果：

表头解释：

1. id

select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序。id相同，执行顺序由上至下；如果是子查询，id序号会递增，id值越大表明优先级越高，越早被执行；若有相同有不同，先执行优先级高的，在按照顺序执行优先级相同的。

2. select_type

3. table

表明当前行是哪张表的查询。

4. type

显示的是访问类型，是较为重要的一个性能直表。

类型	含义
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	无索引情况下的全表扫描

结果值从最好到最坏依次是：

system ⇒ const ⇒ eq_ref ⇒ ref ⇒ range ⇒ index ⇒ ALL

一般来说，得保证查询至少达到range级别，最好能达到ref。

5. possible_keys和key

用于判断是否使用了索引（即索引是否失效）且在多个索引竞争的情况下MySQL到底使用了哪个索引。

字段名	含义
possible_keys	显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。
key	实际被使用的索引，如果为NULL，则表明没有索引被使用。查询中若使用了覆盖索引，则该索引仅出现在key列表中。

6. key_len

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

7. ref

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

8. rows

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

9. Extra

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

信息	解释
Using filesort	【较为严重问题】 说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。即MySQL中无法利用索引完成的排序操作成为“文件排序”。[注] 进行排序的查询语句尽量使用其复合索引的全部字段，否则将出现Using filesort影响性能。
Using temporary	【极其严重问题】 说明使用了临时表保存了中间结果，MySQL在对查询结果排序时使用了临时表。常见于排序order by和分组group by查询语句。
Using index	【效率优秀】 表示响应的select操作中使用了覆盖索引（Covering Index），避免了访问表的数据行，效率极佳。如果同时出现了using where，表明索引被用来执行索引键值查找。【索引覆盖 Covering Index】 是指select的数据列只用从索引中就能取到，不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件，换句话说就是查询列刚巧被所建立的索引完成覆盖。
Using where	使用了where条件语句。
using join buffer	使用了连接缓存。
impossible where	即where条件不可能达到。【例】 select … where name = “1” and name = “2”;
select tables optimized away	【仅了解】在没有group by子句的情况下，基于索引优化操作或者对于MyISAM存储引擎优化count（*）操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成阶段。
distinct	优化distinct操作，在找到第一个匹配的元组后即停止找同样值得动作。

5.索引优化 （重要）

1）索引N表优化案例

① 单表优化案例

CREATE TABLE `article`  (
    `id` int(11) UNSIGNED NOT NULL PRIMARY KEY AUTO_INCREMENT,
	`author_id` int(11) UNSIGNED NOT NULL,
	`category_id` int(11) UNSIGNED NOT NULL,
	`views` int(11) UNSIGNED NOT NULL,
	`comments` int(11) UNSIGNED NOT NULL,
    `title` VARBINARY(255) NOT NULL,
	`content` TEXT NOT NULL );

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

执行sql：

结论：type是ALL即全表无索引扫描（最坏情况），Extra还出现了Using filesort，也是最坏的情况。

尝试优化：

//建立索引
mysql> alter table article add index idx_article_ccv (category_id,comments,views);

尝试执行sql：

结论：此处type已经变为了range即表明查询语句进行了范围性的索引扫描，这优于刚才的全表非索引扫描（ALL）。但Extra依旧存在文件内排序（Using filesort）。

分析：使用范围会导致索引失效。

改进思路：既然comments字段是按照范围搜索，会导致索引失效，那么我们能不能越过comments字段，只建立category_id和views字段的复合索引呢?

索引的建立不是一朝一夕就玩的溜的，需要反复的测试和练习。

再次优化：

//删除之前创建的索引
mysql> drop index idx_article_ccv on article;
//重新创建索引
mysql> alter table article add index idx_article_cv (category_id,views);

执行sql：

结论：我们看到type已变为ref，即非唯一性全表扫描，利用到了索引，并且解决了文件内排序（Using filesort）。

② 双表优化案例

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`(
`book_id` int(10) UNSIGNED not null auto_increment,
`card` int(10) UNSIGNED not null,
PRIMARY KEY (`book_id`)
);
//执行20次插入20条数据
INSERT into class(card) VALUES(floor(1+(rand()*20))); 
//执行20次插入20条数据
INSERT into book(card) VALUES(floor(1+(rand()*20))); 

执行sql：

结论：有ALL，且出现Using join buffer。查看rows字段，得知两个表都进行全表扫描了全部字段（20个）。

优化思路：尝试增加索引，但索引到底是加在哪一张表呢？不妨都试试。

尝试优化（方案一）：

//给class表的card字段增加索引
alter table class add index idx_class_card (card);

执行结果：

可知在class表的card字段增加索引后，虽然其class表使用到了索引，并且也成为了非唯一性的索引扫描。但book表中依旧是非索引性全表扫描，使用到的索引也为NULL。

尝试优化（方案二）：

drop index idx_class_card on class
alter table book add index idx_book_card (card);

执行sql：

运行结果：运行结果我们看到，未增加索引的class表变成了非索引性全表扫描，用到的索引为NULL。然而book表虽然增加了索引，并且也使用到了其索引。但查询类型却是index即索引性全表扫描，这与方案一的ref非唯一性索引扫描相比效率要差得多。

分析：由于【join】语句的特性。拿left join举例，查询的结果一定包含所有的左表字段，所以无论如何左表都要进行全表扫描，增加索引无非只能将全表无索引扫描变为全表索引扫描。但右表并不是全部字段，而是根据其条件匹配，所以增加索引的右表，可以进行非唯一性扫描，效率更优秀。

结论：在使用join语句时，left join优化索引建立在右表，right join优化索引建立在左表。

③ 三表优化案例

//1.建立表三
CREATE TABLE IF NOT EXISTS `phone` 
( `phoneid` INT ( 10 ) UNSIGNED NOT NULL auto_increment, 
  `card` INT ( 10 ) UNSIGNED NOT NULL, 
	PRIMARY KEY ( `phoneid` ) 
) ENGINE = INNODB

//2.执行20次插入20条数据
INSERT into phone(card) VALUES (FLOOR(1+(RAND()*20)));

//3.删除前两表的索引
略

执行sql：

explain select * from class left join book on class.card = book.card left join phone on book.card = phone.card;

执行截图：

分析：三张表都进行了非索引全表扫描，无使用到索引，并且使用了Using join buffer。

改进思路：根据刚才left join增加右表索引的结论，由于这次是三表连接，left join时右表有两张，那么尝试增加book和phone表的索引。

alter table book add index idx_book_card (card);
alter table phone add index idx_phone_card (card);

再次执行sql：

结论：可以看到两张右边的查询类型为ref即唯一性索引扫描，使用到了刚刚创建的索引。

N表优化练习结论：

1. 尽可能减少join语句中的循环总次数，即永远使用小表驱动大表！！！
2. 保证join语句中被驱动表上的join条件字段已被索引！！！
3. 当无法保证被驱动表的join条件字段被索引且内存资源充足的情况下，不要吝啬join buffer的设置。

2）索引的使用法则（极其重要）

//建表
CREATE table staffs(
id int primary key auto_increment,
name varchar(24) not null,
age int not null,
position varchar(20) not null,
add_time TIMESTAMP not null DEFAULT CURRENT_TIMESTAMP
)
//插入数据
INSERT into staffs(name,age,position,add_time) VALUES('z3',22,'manager',NOW());
INSERT into staffs(name,age,position,add_time) VALUES('july',23,'dev',NOW());
INSERT into staffs(name,age,position,add_time) VALUES('2000',23,'dev',NOW());
//建立索引
alter table staffs add index idx_staffs_nameAgePosition(name,age,position);

① 全值匹配我最爱（最佳左前缀法则）

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

结论：索引未失效。

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

结论：索引未失效。

explain select * from staffs where name = 'july' and age = 22 and position = 'dev';

结论：索引未失效。

explain select * from staffs where position = 'dev' and age = 22 and name = 'july';

结论：索引未失效。

explain select * from staffs where age = 22 and position = 'dev';

结论：索引失效。

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

注意：这里虽然使用到了非唯一性索引，但并不是所有字段都使用到了其索引，因为ref的属性只有一个const即只有name字段使用进行了非唯一性索引扫描。(如果两个都用到，会有两个const)

结论：索引失效。

小总结：在使用复合索引时，要么查询语句使用到所有的索引字段（任意使用次序），要么按照顺序得使用部分字段。例如，刚才建立了（name,age,position）三个字段的复合索引，若我们只使用其中一个索引，只能使用name,若使用两个，则必须为name和age字段，否则索引会失效。
可以把他们看作 1 ⇒ 2 ⇒ 3楼的阶梯，你无法跳过一层登上二层，以此类推。

② 不要在索引列上做计算

//left(name,4)等价于name.subString(0,4);
explain select * from staffs where left(name,4) = 'july';

小总结：不要在索引列上做任何操作（计算、函数、or、类型转换），会导致索引失效而转向全表非索引扫描。

③ 范围有可能会使复合索引失效。

explain select * from staffs where name = 'z3' and age > 22 and position = 'manager';

结论：range即索引性范围扫描，第一个查询条件name使用到了其索引，但由于age为范围查询，导致索引失效。相应的虽然后面position字段是单值匹配，其索引也会由于age索引的失效而失效。

小总结：在使用复合索引时，尽量避免使用范围查找，若不得已要使用。要尽量保证其索引字段后再无拥有索引的查询条件，否则在范围条件后的所有索引都将失效。即存储引擎不能使用索引中范围条件右边的列，范围之后索引会失效。

④ 按需所取

//铺张浪费sql
explain select * from staffs where name = 'z3' and age = 22 and position = 'manager';
//勤俭节约sql
explain select name,age,position from staffs where name = 'z3' and age = 22 and position = 'manager';

小总结：索引未失效的情况下，遍历索引后，若查询字段多余索引覆盖字段，则需要进行读库操作获取所有需要字段。若索引刚巧覆盖了想要查询的所有字段，则无需读库操作，直接从索引中返回其值，效率更优。所以在真实的开发中，应当按需所取，避免铺张浪费。

⑤ 避免使用（!=或< >）

explain select * from staffs where name != 'z3';

小总结：索引失效。

⑥ 避免使用（is null 或 is not null）

explain select * from staffs where name is not null;

小总结：索引失效。

⑦ 左通配符可能会使索引失效（重要）

//左右通配符
explain select * from staffs where name like '%july%';
//左通配符
explain select * from staffs where name like '%july';

结论：索引失效。

explain select * from staffs where name like 'july%';

结论：索引未失效。

小总结：模糊(*)查询时使用左通配符开头会导致索引失效。

思考：如果在真实开发中，项目经理要求必须使用 %?% 即左右通配符模糊查询时，应该如何避免索引失效的问题呢？

//新建一个测试表
CREATE table `user`(
`id` int(11) not null auto_increment,
`name` varchar(20) DEFAULT null,
`age` int(11) default null,
`email` varchar(20) default null,
primary KEY(`id`)
)

//插入测试数据
INSERT into user(name,age,email) VALUES ('1aa1',21,'b@163.com');
INSERT into user(name,age,email) VALUES ('2aa2',222,'a@163.com');
INSERT into user(name,age,email) VALUES ('3aa3',265,'c@163.com');
INSERT into user(name,age,email) VALUES ('4aa4',21,'d@163.com');
INSERT into user(name,age,email) VALUES ('aa',121,'e@163.com');

//建立索引
alter table user add index idx_name_age (name,age);

//type = index  索引未失效
explain select name,age from user where name like '%aa%'; 

//type = index  索引未失效   
explain select id from user where name like '%aa%';

//type = index  索引未失效
explain select age from user where name like '%aa%';

//type = index  索引未失效
explain select id,name,age from user where name like  '%aa%';

//type = all    索引失效
explain select id,name,age,email from user where name like '%aa%';

小总结：为了避免使用左右通配符时索引失效，我们可以使用覆盖索引，这样至少可以保证是全表索引性扫描，比ALL的效率要高很多。

⑧ 字符串不加单引号会导致索引失效

//使用单引号
explain select * from staffs where name = '2000';
//不适用单引号
explain select * from staffs where name = 2000;

小总结：性能高低显而易见。

⑨ 少用or

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

⑩ 回顾练习

假设index(a,b,c)

where语句	索引是否被使用
where a = 3	√
where a = 3 and b = 5	√
where a = 3 and b = 5 and c = 4	√
where b = 3 or where b = 3 and c = 4 or where c = 4	×
where a = 3 and c = 5	×（a用到，c没有）
where a = 3 and b > 4 and c = 5	× （使用到a和b，c没有）
where a = 3 and b like ‘kk%’ and c = 4	× （使用到a和b，c没有）

3）索引面试题分析

CREATE TABLE test(
id int PRIMARY KEY not null auto_increment,
c1 char(10),
c2 char(10),
c3 char(10),
c4 char(10),
c5 char(10)
);

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

//建立索引
alter table test add index idx_test_c1_c2_c3_c4 (c1,c2,c3,c4);

问题：我们创建了复合索引 index idx_test_c1_c2_c3_c4 ，根据以下SQL分析索引使用情况。

① explain select * from test where c1 = ‘a1’ and c2 = ‘a2’ and c3 = ‘a3’ and c4 = ‘a4’;

② explain select * from test where c1 = ‘a1’ and c2 = ‘a2’ and c4 = ‘a4’ and c3 = ‘a3’;

③ explain select * from test where c4 = ‘a4’ and c3 = ‘a3’ and c2 = ‘a2’ and c1 = ‘a1’;

①②③：SQL语句执行之前，会经由MySQL优化器重构，所以只要使用到了全部字段，无关顺序，最终都会优化为①去执行。

④ explain select * from test where c1 = ‘a1’ and c2 = ‘a2’ and c3 > ‘a3’ and c4 = ‘a4’;

④：范围之后全失效。

⑤ explain select * from test where c1 = ‘a1’ and c2 = ‘a2’ and c4 > ‘a4’ and c3 = ‘a3’;

⑤：使用到了4个索引，优化器自动排序。

⑥ explain select * from test where c1 = ‘a1’ and c2 = ‘a2’ and c4 = ‘a4’ order by c3; （重要）

⑥ c1，c2，c3使用到了排序，只不过c1和c2索引的作用在于查找，而c3在于排序。

⑦ explain select * from test where c1 = ‘a1’ and c2 = ‘a2’ order by c3;

⑧ explain select * from test where c1 = ‘a1’ and c2 = ‘a2’ order by c4; （重要）

⑧：中间兄弟不能断，c4没有使用到索引，所以只好文件内排序（Using filesort）九死一生。

⑨ explain select * from test where c1 = ‘a1’ and c5 = ‘a5’ order by c2,c3;

⑨：c1索引用于查询，c2和c3索引用于排序，c5未使用到索引。

⑩ explain select * from test where c1 = ‘a1’ and c5 = ‘a5’ order by c3,c2; （重要）

⑩：使用索引排序时，必须按照索引顺序。否则会出现文件内排序。

①① explain select * from test where c1 = ‘a1’ and c2 = ‘a2’ order by c2,c3;

①①：查询时带头大哥没有死，中间兄弟没有断。且排序时，顺序没有乱。所以这是一条完美的sql语句。

①② explain select * from test where c1 = ‘a1’ and c2 = ‘a2’ and c5 = 'a5’order by c3,c2; （极其重要）

①②：注意，排序索引没有按照顺序，与【⑩、必须按照顺序否则文件内排序】结论相违背，这是一种特殊情况。因为c2此时已经由于查询条件固定，变成了一个常量。那么题目中按照c3，c2排序其实是按照c3，‘a2’排序。与【explain select * from test where c1 = ‘a1’ and c2 = ‘a2’ and c5 = ‘a5’ order by c3;】无异。 所以不会造成文件内排序。

6.查询优化

1）永远小表驱动大表

在查询时，有以下两种联表方式（仅模拟）

//第一种  (不可取)
for(int i = 1000;...)
{
    sout("表一表二建立连接");
    for(int i = 5;...){}
    sout("表一表二断开连接");
}

//第二种   (可取)
for(int i = 5;...)
{
    sout("表一表二建立连接");
    for(int i = 1000;...){}
    sout("表一表二断开连接");
}

优化原则：小表驱动大表，即小的数据集驱动大的数据集。
因为建立连接和断开连接过于频繁会降低整体效率。

2）in 和 exists

//假设 A为员工表   B为部门表
//且 部门表 < 员工表
select * from A where department_id in(select id from B);
//上述sql等价于：
for select id from B
for select * from A where A.department_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。

select … from table where exists (subquery);
该语法可理解为：将主查询的数据，放到子查询中做条件验证，根据验证结果（true 或者 false）来决定主查询的数据结果是否得以保留。
exists (subquery) 只返回true或者false，因此子查询中的select *也可以是select 1或select ‘x’,官方说法是实际执行时会忽略select清单，因此没有区别。

3）order by优化

//建表sql
CREATE TABLE person ( 
id INT PRIMARY KEY NOT NULL auto_increment, age INT, birthday TIMESTAMP NOT NULL );
//插入数据
INSERT into person(age,birthday) VALUES (22,NOW());
INSERT into person(age,birthday) VALUES (23,NOW());
INSERT into person(age,birthday) VALUES (24,NOW());
//建立索引
alter table person add index idx_person_age_birthday (age,birthday);

① 避免 Using filesort

分析以下sql是否会出现 Using filesort

• 题一

explain select * from person where age > 20 order by age;

分析：age为复合索引的第一个字段，此sql并没有违背之前所学【order by的字段必须要按照索引创建顺序否则将出现文件内排序】规则。

结论：不会出现 filesort。

执行验证：

• 题二

explain select * from person where age > 20 order by age,birthday;

分析：order by条件为索引顺序。(理由同题目一)

结论：不会出现 filesort。

执行验证：

• 题三

explain select * from person where age > 20 order by birthday;

分析：order by排序条件没有用到age，而是直接使用birthday。没有按照索引的建立顺序，带头大哥（age）死掉了。

结论：出现 filesort。

执行验证：

• 题四

explain select * from person where age > 20 order by birthday,age;

分析：order by排序条件刚好与索引建立顺序相反。在此情况下，只有age的条件是一个常量（age = ？）才不会出现filesort。

结论：会出现 filesort。

执行验证：

• 题五

explain select * from person order by birthday;

分析：并没有使用所建立索引的带头大哥（age）。

结论：会出现 filesort。

执行验证：

• 题六

explain select * from person where birthday > '2020-10-8 00:00:00' order by birthday;

分析：未使用索引带头大哥（age）。

结论：会出现 filesort。

执行验证：

order by满足两情况时，会使用index方式排序：

1. 满足最佳左前缀原则。
2. 使用where子句与oder by子句条件列满足索引最佳左前缀原则。

② 提高order by’的速度

• 不在order by时使用select * ，否则有可能超出缓冲区容量，导致多次IO操作。
• 尝试提高sort_buffer_size
• 尝试提高max_length_for_sort_data

三、查询截取分析

1. 观察，至少跑一天，看看生产的慢SQL情况
2. 开启慢查询日志，设置阈值，抓取慢SQL
3. explain + 慢SQL分析
4. show profile查询SQL在MySQL服务器里面的执行细节和生命周期情况
5. 运维 or DBA 进行SQL数据库服务器的参数调优

1. 慢查询日志

1）是什么

MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阈值的语句，具体指运行时间超过long_query_time值得SQL。
我们可以通过设置long_query_time值来告知MySQL，我们最多能够接受的SQL语句执行时间，若超过此时限，就将其收集起来。

2）默认关闭

默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。当然，如果不是调优需要，一般不建议开启此功能，因为这多少会带来一些性能上的影响。

3）怎么玩

① 查看是否开启

show variables like '%low_query_log%';

② 开启慢查询日志

临时开启，重启服务后失效：

set global slow_query_log = 1;

一劳永逸：修改mysql.cnf

③ 修改默认long_query_time

//查看默认配置阈值
show variables like 'long_query_time%';
//修改默认配置阈值
set global long_query_time = 2;

注意：设置阈值后重新查看阈值配置，发现依旧是10秒。并不是因为重新设置阈值失败，而是需要重新开启一个新的MySQL会话才可以生效，（不是重启MySQL服务）

④ 测试慢查询日志是否生效

//模拟慢sql
select sleep(3);

查看log文件（var/lib/mysql/66226fa80b03-slow.log）：

⑤ 快速查看当前慢sql条数

show global status like '%Slow_queries%';

⑥ 日志分析工具mysqldumpslow

在生产环境中，如果要手动分析日志，查找，分析SQL，显然是一个体力活，MySQL提供了日志分析工具mysqldumpslow。

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

常用查看指令：

//得到返回记录集最多的10个SQL
mysql> mysqldumpslow -s r -t 10 /var/lib/mysql/66226fa80b03-slow.log
//得到访问次数最多的10个SQL
mysql> mysqldumpslow -s c -t 10 /var/lib/mysql/66226fa80b03-slow.log
//得到按照时间顺序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/66226fa80b03-slow.log
//另外建议在使用这些指令的时候结合 | more 使用，否则可能出现爆屏情况
mysql> mysqldumpslow -s r -t 10 /var/lib/mysql/66226fa80b03-slow.log | more

2.批量插入数据脚本（1千万条）

1）建表

//表一
CREATE TABLE department(
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 = gbk

//表二
create table employee(
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 = gbk;

2）定义函数

//定义函数


DELIMITER $$
CREATE FUNCTION rand_string ( n INT ) RETURNS VARCHAR ( 255 ) BEGIN
DECLARE
	char_str VARCHAR ( 100 ) DEFAULT 'abcdefghijklmnopqrstuvwxyzABCDEFJHIGKLMNOPQRSTUVWXYZ';
DECLARE
	return_str VARCHAR ( 255 ) DEFAULT '';
DECLARE
	i INT DEFAULT 0;
WHILE
	i < n DO
	
	SET return_str = CONCAT( return_str, substring( char_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 $$

3）创建存储过程

//过程一

delimiter $$
CREATE PROCEDURE insert_employee ( 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 employee ( empno, ename, job, mgr, hiredate, sal, comm, deptno )
VALUES
	(
		( START + 1 ),
		rand_string ( 6 ),
		'SALESMAN',
		0001,
		CURDATE( ),
		2000,
		400,
		rand_num ( ) 
	);
UNTIL i = max_num 
END REPEAT;
COMMIT;

END $$


//过程二
delimiter $$
CREATE PROCEDURE insert_department ( 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 department ( deptno, dname, loc )
VALUES
	( ( START + i ), rand_string ( 10 ), rand_string ( 8 ) );
UNTIL i = max_num 
END REPEAT;
COMMIT;

END $$

4）调用存储过程插入数据

//向employee表插入id从100001起的10000000条数据
delimiter ;
call insert_employee (100001,10000000);

delimiter ;
call insert_department (100001,10000000);

共耗时：43分钟。(1核2G服务器)

3. 使用Show Profile

1）是什么

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

官网超链接

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

2）怎么玩

① 开启Show Profile功能

//查看是否已开启
show variables like 'profiling';

//开启
set profiling = on;

② 执行sql

//略  
//随便执行几条耗时的sql

③ show profile ！

show profiles;

④ 使用详细 profile 指令

show profile cpu,block io for query Query_ID;

可选参数	作用
all	显示所有的开销信息
block io	显示块IO相关开销
context switches	上下文切换相关开销
cpu	显示CPU相关开销信息
ipc	显示发送和接收相关开销信息
memory	显示内存相关开销信息
page faults	显示页面错误相关开销信息
source	显示和source_function,source_file,source_line相关的开销信息
swaps	显示交换次数相关开销信息

四个重要指标： （惹事的四大因素）

• converting HEAP to MyISAM
  查询结果太大，内存不够用了往磁盘上搬运。
• createing tmp table
  使用到临时表，用完再删除。
• copying to tmp table on disk （极其危险）
  把内存中临时表复制到磁盘。
• locked
  上锁等待。

3）全局查询日志

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

① 直接玩

set global general_log = 1;
set global log_output = 'table'
//此后，你的每一条sql都会记录到mysql库里的general_log表
//使用下面命令查看
select * from mysql.general_log;

四、MySQL锁机制

1. 是什么

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

2. 锁的分类

1）按照数据操作的 类型 来分

种类	解释
读锁（共享锁）	针对同一份数据，多个读操作可以同时进行而不会相互影响。
写锁（排它锁）	当前写操作没有完成前，会阻断其他写锁和读锁。

2）按照数据操作的 粒度 来分

种类	解释
行锁	操作时阻断其他进程或线程对本行的操作。
表锁	操作时阻断其他进程或线程对本表的操作。
页锁	操作时阻断其他进程或线程对本页的操作。

3. 怎么玩

//建表
create table mylock(
id int not null PRIMARY KEY auto_increment,
name varchar(20)
)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');

1） 查看正在使用的锁

show open tables;

其中 in_use 字段代表所的数量。

2）上锁与解锁

//上锁（读/写锁）
lock table table_name read/write;
//解锁（所有表的锁）
unlock tables;

4. 三锁详解（表/行/页锁）

1）表锁

偏向MyISAM：开销小、加锁快、锁定粒度大、发生锁冲突概率高、并发度低。

① 读锁

//加读锁给mylock表
lock table mylock read;
//尝试查询（正常）
select * from mylock;
//尝试更新（报错，不允许更改）
update mylock set name = 'a2' where id = 1;
//查询其他表（报错，无法查询）
select * from book;

重新开启一个会话：

//读取别人锁过的表（成功）
select * from mylock;
//更新别人锁过的表（阻塞）
update mylock set name = 'a2' where id = 1;

结论：1. 对表增加读锁后无法对本表进行更新操作，其他session也将阻塞无法更新此表，只能读。 2. 对表增加读锁后，同一个session无法读取其他未上锁的表。

② 写锁

//增加写锁
lock table mylock write;
//读自己锁过的表（成功）
select * from mylock;
//改自己锁过的表（成功）
update mylock set name = 'a1' where id = 1;
//读其他表（报错，失败）
select * from book;

重新开启一个会话：

//读别人锁过的表失败（阻塞）
select * from mylock;
//改别人锁过的表失败（阻塞）
update mylock set name = 'a1' where id = 1;

2）锁的性能分析

show status like 'table%';

这里有两个状态量记录MySQL内部表级锁定的情况，两个变量说明如下：

variable_name	Value
Table_locks_immediate	产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁增加1
Table_locks_waited	出现表级锁定争用而发生等待的次数，每等待增加1。此值过高说明存在较为严重的表级锁征用情况。

3）行锁

偏向InnoDB：开销大、加锁慢、会出现死锁、锁定粒度最小、发生锁冲突概率最低、并发度最高。

① 支持事务（TRANSACTION）及其ACID属性

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

属性	解释
原子性（Atomicity）	事务是一个原子操作单元，其对数据的修改，要么全部执行成功，要么全部执行失败。
一致性（Consistent）	在事务开始和完成时，数据都必须保持一致状态。这意味着所有的相关数据规则都必须应用于事务的修改，以保持数据的完整性。事务结束时，所有的内部数据结构（例如B树或者双向链表）也都必须是正确的。
隔离性（Isolation）	数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的，反之亦然。
持久性（Durable）	事务完成之后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

② 并发事务可能带来的问题：

• 更新丢失

当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题，即最后的更新覆盖了由其他事务所做的更新操作。
解决方案：如果在一个进程或者线程完成并提交事务之前，另一个进程或线程不能访问同一文件，则可避免此问题。

• 脏读

一个事务正在对一条记录做修改，在这个事务完成并提交前，这条记录的数据就处于不一致状态。此时，另一个事务也来读取同一条记录，如果不加以控制，第二个事务读取了这些 “ 脏 ” 数据，并据此做进一步的处理，就会产生未提交的数据依赖关系。这种现象被成为脏读。
事务A读取到了事务B已修改但尚未提交的数据，还在这个数据基础上做了操作。此时，如果B事务回滚，A读取的数据无效，不符合一致性要求。

• 不可重复读

一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变、或某些记录已经被删除。这种现象就叫做不可重复读。
事务A读取到了事务B已经提交的修改数据，不符合隔离性。

• 幻读

一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为幻读。
事务A读到了事务B提交的新增数据，不符合隔离性。
幻读和脏读有些类似。
脏读是事务B里面修改了数据，
幻读是事务B里面新增了数据。

③ 事务的隔离级别

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

读数据一致性及允许的并发副作用隔离界别	读数据一致性	脏读	不可重复读	幻读
未提交读（Read uncommitted）	级别最低，只能保证不读取物理上损坏的数据	是	是	是
已提交读（Read committed）	语句级	否	是	是
可重复读（Repeatable read）	事务级	否	否	是
可序列化（Serializable）	最高级别，事务级	否	否	否

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

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

④ 行锁演示

//建表
create table row_lock(
a int(11),
b varchar(16)
)ENGINE=INNODB

//插入数据
INSERT into row_lock VALUES (1,'b2');
INSERT into row_lock VALUES (3,'3');
INSERT into row_lock VALUES (4,'4000');
INSERT into row_lock VALUES (5,'5000');
INSERT into row_lock VALUES (6,'6000');
INSERT into row_lock VALUES (7,'7000');
INSERT into row_lock VALUES (8,'8000');
INSERT into row_lock VALUES (9,'9000');
INSERT into row_lock VALUES (1,'b1');

//建立索引
alter table row_lock add index idx_row_lock_a (a);
alter table row_lock add index idx_row_lock_b (b);

因为InnoDB默认的会自动提交sql执行，所以为了方便测试，我们需要临时关闭其自动提交。

//临时关闭autocommit
set autocommit = 0;

Session 1执行操作：

Session 2执行操作：

由上两图可以得出，在Session1执行提交之前，Seesion2获取到的依旧是原来的数据。这是因为InnoDB默认的隔离级别，有效避免了脏读问题。但在Session1执行commit后，Session2依旧无法读到新值。因为在Session2提交之前，InnoDB为了保证数据一致性，触发可重复读，所以Session2也需要提交，才能查到新值。
且当Session1执行commit之前，Session2进行更新表操作时，会被阻塞，直至Session1完成提交。

⑤ 无索引 或 索引失效行锁变表锁（危险）

由索引失效的知识可知，此update语句会索引失效（where条件进行了类型转换）。两条update语句更新的是不同行数据，理应不会有冲突。但由于索引失效，导致行锁升级为表锁，使Session2阻塞。

⑥ 间隙锁的危害

当Session1进行批量更新（即更新条件是一个范围而不是相等时），InnoDB会给符合条件的所有数据索引项加锁，包括那些不存在的数据！而对于那些处在范围之内但并不存在的记录，叫做间隙。
InnoDB也会对这个 间隙 加锁，这种锁机制就是所谓的间隙锁。

危害： 因为Query执行过程中通过范围查找的话，他会锁定整个范围内所有的索引键值，即使这个键值并不存在！
间隙锁有一个致命的弱点，就是当锁定一个范围键值之后，即使那些不存在的键值也会被无辜锁定，而造成在锁定的时候无法插入的窘境。在某些特定的场景下这可能会对性能造成巨大的危害。

⑦ 如何锁定一行

//begin起手
begin;
//显式使用for update锁定该行
//期间如果其他人来更改此行，会阻塞线程。
select * from row_lock where a = 8 for update;
//提交
commit;

4）页锁

用的不多，不想写了。

5）总结（完结撒花）

InnoDB存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗要比表级锁定更高一些，但是在整体性能和并发处理能力上要远优于表级锁定。所以当系统并发量较高的时候，InnoDB的整体性能和MyISAM相比就会有比较明显的优势了。
但是，当行锁使用不当时，也会出现性能骤减的问题。

注意点：

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

五、主从复制（可忽略）

有空再更，有兴趣可以自己了解。
除了MySQL配置主从复制，还推荐Alibaba的Canal获取数据库增量日志，不但可以主从复制，还可以实时捕获增量刷新Redis。
托更原因： Java程序员不配操心DBA的事。

六、常用SQL语句（写给自己玩儿的）

select * from dept a,emp b where a.id = b.id;
select * from dept where id between 30 and 60;
select * from dept where id in (1,2,6);
desc table_name; //查询表结构

七、配套视频资料

尚硅谷【周阳/阳哥】MySQL数据库高级