一、MySQL的体系结构概览

整个MySQL Server由以下组成

Connection Pool : 连接池组件
Management Services & Utilities : 管理服务和工具组件
SQL Interface : SQL接口组件
Parser : 查询分析器组件
Optimizer : 优化器组件
Caches & Buﬀers : 缓冲池组件
Pluggable Storage Engines : 存储引擎
File System : 文件系统

1.1、连接层

最上层是一些客户端和链接服务，包含本地sock 通信和大多数基于客户端/服务端工具实现的类似于 TCP/IP的通信。
主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

1.2、服务层

第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询（Caches & Buﬀers），SQL的分析和优化（Optimizer），部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程、函数等。在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化如确定表的查询的顺序，是否利用索引等，最后生成相应的执行操作。如果是
select语句，服务器还会查询内部的缓存，如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能。

1.3、引擎层

存储引擎层， 存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能，这样我们可以根据自己的需要，来选取合适的存储引擎。

1.4、存储层

数据存储层，主要是将数据存储在文件系统之上，并完成与存储引擎的交互。

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

二、存储引擎

2.1、存储引擎概述

和大多数的数据库不同, MySQL中有一个存储引擎的概念, 针对不同的存储需求可以选择最优的存储引擎。
存储引擎就是存储数据，建立索引，更新查询数据等等技术的实现方式 。
存储引擎是基于表的，而不是基于库的。所以存储引擎也可被称为表类型。
存储引擎负责数据的存储和提取，它是负责与文件打交道的子系统。

在使用数据库可视化工具创建库时，不会要求你选择存储引擎，而在创建表时，会让你选择存储引擎。

Oracle，SqlServer等数据库只有一种存储引擎。MySQL提供了插件式的存储引擎架构。所以MySQL存在多种存储引擎，可以根据需要使用相应引擎，或者编写存储引擎。
MySQL5.0支持的存储引擎包含： InnoDB（5.5之后，MySQL默认的存储引擎） 、MyISAM（5.5之前，MySQL默认的存储引擎） 、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等，其中InnoDB和BDB提供事务安全表，其他存储引擎是非事务安全表。

1、查看当前数据库支持的存储引擎

使用指令 show engines 来查看

1	show engines;

从上面搜索到的结果可以看到，MySQL默认支持的存储引擎是 InnoDB，在建表时如果不加以指定，那么默认使用 InnoDB 作为存储引擎。
InnoDB 支持事务、行级锁 和外键。
MySQL5.5之前的默认存储引擎是MyISAM，5.5之后就改为了InnoDB。

2、查看默认的存储引擎

使用指令 show variables like '%storage_engine%' 查看

1	show variables like '%storage_engine%';

3、常见存储引擎介绍

下面介绍几种常用的存储引擎，和各引擎间区别，如下表

特点	InnoDB	MyISAM	MEMORY	MERGE	NDB
存储限制	64TB	有	有	没有	有
事务安全	支持
锁机制	行锁(适合高并发)	表锁	表锁	表锁	行锁
B树索引	支持	支持	支持	支持	支持
哈希索引	支持		支持
全文索引	支持(5.6 版本之后)	支持
集群索引	支持
数据索引	支持		支持		支持
索引缓存	支持	支持	支持	支持	支持
数据可压缩		支持
空间使用	高	低	N/A	低	低
内存使用	高	低	中等	低	高
批量插入速度	低	高	高	高	高
支持外键	支持

2.2、InnoDB 存储引擎

1、概述

在 MySQL 5.5 之后， InnoDB 替代了原来的 MyISAM 成为了 MySQL 默认的存储引擎，它具有以下几个特点

支持事务

InnoDB 存储引擎提供了具有提交、回滚、崩溃恢复能力的事务安全

写效率较低，同时占用的空间较大（相比 MyISAM）
支持行级锁
支持外键

2、InnoDB 支持事务

创建一张表用于演示 InnoDB 存储引擎的特性–事务控制。

create table goods_innodb
(
    id   int         NOT NULL AUTO_INCREMENT,
    name varchar(20) NOT NULL,
    primary key (id)
) ENGINE = innodb
  DEFAULT CHARSET = utf8;

手动开启事务，打开两个cmd窗口，链接上数据库

1	mysql -u root -p你的密码

在第一个窗口中输入以下命令

1 2	start transaction; insert into goods_innodb(id,name) values (2,'起飞');

此时使用第二个窗口查看表数据，发现没有 id 为2的数据

在第一个窗口中提交事务，再次查看第二个窗口中数据库表数据

commit;

3、InnoDB 支持外键

创建两张表用于演示 InnoDB 存储引擎的特性–支持外键。

MySQL支持外键的存储引擎只有InnoDB ，在创建外键的时候，要求父表必须有对应的索引， 子表在创建外键的时候，也会自动的创建对应的索引。
下面两张表中， country_innodb是父表， country_id为主键索引，city_innodb表是子表，country_id字段为外键，对应于country_innodb表的主键country_id 。

create table country_innodb
(
    country_id   int          NOT NULL AUTO_INCREMENT,
    country_name varchar(100) NOT NULL,
    primary key (country_id)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

create table city_innodb
(
    city_id    int         NOT NULL AUTO_INCREMENT,
    city_name  varchar(50) NOT NULL,
    country_id int         NOT NULL,
    primary key (city_id),
    key idx_fk_country_id (country_id),
    CONSTRAINT `fk_city_country` FOREIGN KEY (country_id) REFERENCES country_innodb (country_id) ON DELETE RESTRICT ON UPDATE CASCADE
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;
  
insert into country_innodb values(null,'China'),(null,'America'),(null,'Japan');
insert into city_innodb values(null,'Xian',1),(null,'NewYork',2), (null,'BeiJing',1);

在删除主表信息时，如果子表中有和待删除数据关联的数据，那么主表不能删除
试着删除主表中 id 为2的数据

1	delete from country_innodb where country_id = 2;

控制台结果为：

在创建索引时，可以指定在删除、更新父表时，对子表进行的相应操作，包括 RESTRICT、CASCADE、SET NULL和 NO ACTION。

RESTRICT和NO ACTION相同，是指限制在子表有关联记录的情况下，父表不能更新；
CASCADE表示父表在更新或者删除时，更新或者删除子表对应的记录；
SET NULL 则表示父表在更新或者删除的时候，子表的对应字段被SET NULL 。

针对上面创建的两个表，子表的外键指定是 ON DELETE RESTRICT ON UPDATE CASCADE 方式的， 那么在主表删除记录的时候，如果子表有对应记录，则不允许删除，主表在更新记录的时候，如果子表有对应记录，则子表对应更新。

下面演示 UPDATE CASCADE ，将 id 为1的记录的 id 设置为100

1	update country_innodb set country_id = 100 where country_id = 1;

此时再次查看两张表中的数据，发现子表与父表关联的数据也被修改了。

4、InnoDB 的存储方式

InnoDB 存储表和索引有以下两种方式：

使用共享表空间存储，这种方式创建的表的表结构保存在.frm文件中，数据和索引保存innodb_data_home_dir 和 innodb_data_ﬁle_path定义的表空间中，可以是多个文件。
使用多表空间存储，这种方式创建的表的表结构仍然存在 .frm 文件中，但是每个表的数据和索引单独保存在.ibd 中。

5、什么时候使用 InnoDB？

InnoDB 是 MySQL 的默认存储引擎，用于事务处理应用程序，支持外键和行级锁。

如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询意外，还包含很多的更新、删除操作，那么InnoDB 存储引擎是比较合适的选择。

InnoDB 支持事务

InnoDB 存储引擎除了有效的降低由于删除和更新导致的锁定，还可以确保事务的完整提交和回滚，对于类似于计费系统或者财务系统等对数据准确性要求比较高的系统，InnoDB是最合适的选择。

开发中绝大部分使用的都是InnoDB

2.3、MyISAM 存储引擎

1、概述

与 InnoDB 相比，MyISAM 的优势是访问的速度快，一些对事务完整性没有要求或者以 SELECT 、INSERT 为主的应用基本上都可以使用 MyISAM 引擎来创建表。
它有两个比较重要的特点

不支持事务
文件存储方式特殊
不支持行级锁，只支持表锁

2、不支持事务

创建一张表，在建表语句中指定存储引擎为MyISAM

create table goods_myisam
(
    id   int         NOT NULL AUTO_INCREMENT,
    name varchar(20) NOT NULL,
    primary key (id)
) ENGINE = myisam
  DEFAULT CHARSET = utf8;

同上，使用两个cmd窗口，在第一个窗口中使用 start transaction 开启事务，插入一条数据

在第二个窗口查看表数据，发现已经查询到了未提交的数据，故 myisam 不支持事务。

1	select * from `index_study`.goods_myisam;

3、文件存储方式

每个MyISAM在磁盘上存储成3个文件，其文件名都和表名相同，但拓展名分别是：

.frm (存储表定义)；
.MYD(MYData , 存储数据)；
.MYI(MYIndex , 存储索引)；

4、什么时候使用 MyISAM

如果应用以读操作和插入操作为主，且只有少量的更新和删除操作，那么可以考虑使用 MyISAM
如果应用对 事务的完整性、并发性要求不是很高，那么可以考虑 MyISAM

2.4、Memory 存储引擎

Memory 存储引擎将表的数据存放在内存中。每个MEMORY表实际对应一个磁盘文件，格式是.frm ，该文件中只存储表的结构，而其数据文件，都是存储在内存中，这样有利于数据的快速处理，提高整个表的效率。
Memory 类型的表访问非常地快，因为他的数据是存放在内存中的，并且默认使用 HASH 索引 ，但是服务一旦关闭，表中的数据就会丢失。

三、SQL优化（一）

在应用的的开发过程中，由于初期数据量小，开发人员写 SQL 语句时更重视功能上的实现，但是当应用系统正式上线后，随着生产数据量的急剧增长，很多 SQL 语句开始逐渐显露出性能问题，对生产的影响也越来越大，此时这些有问题的 SQL 语句就成为整个系统性能的瓶颈，因此我们必须要对它们进行优化。
当面对一个有 SQL 性能问题的数据库时，我们应该从何处入手来进行系统的分析，使得能够尽快定位问题 SQL 并尽快解决问题。

3.1、查看SQL执行频率

MySQL 客户端连接成功后，通过 show [session|global] status 命令可以提供服务器状态信息。show [session|global] status 可以根据需要加上参数“session”或者“global”来显示 session 级（当前连接）的计结果和

global 级（自数据库上次启动至今）的统计结果。如果不写，默认使用参数是“session”。

1、查询当前会话中所有统计参数值

下面的命令显示了当前 session 中所有统计参数的值：

1	show status like 'Com_______';

2、查询全局统计参数值

上面的命令查询的只是当前链接的统计参数，如果要统计全局参数，我们可以在 status 前添加一个 global 参数

1	show global status like 'Com_______';

3、查询InnoDB存储引擎的操作数量

1	show status like 'Innodb_rows_%';

输出结果如下
InnoDB 插入的行：8
InnoDB 查询的行：20
InnoDB 更新的行：3
InnoDB 删除的行：0

通过上面的几条指令，就可以大致确定当前数据库到底是以读为主还是以写为主。

4、参数说明

参数	含义
Com_select	执行 select 操作的次数，一次查询只累加 1。
Com_insert	执行 INSERT 操作的次数，对于批量插入的 INSERT 操作，只累加一次。
Com_update	执行 UPDATE 操作的次数。
Com_delete	执行 DELETE 操作的次数。
Innodb_rows_read	select 查询返回的行数。
Innodb_rows_inserted	执行 INSERT 操作插入的行数。
Innodb_rows_updated	执行 UPDATE 操作更新的行数。
Innodb_rows_deleted	执行 DELETE 操作删除的行数。
Connections	试图连接 MySQL 服务器的次数。
Uptime	服务器工作时间。
Slow_queries	慢查询的次数。

Com_*** : 这些参数对于所有存储引擎的表操作都会进行累计。
Innodb_*** : 这几个参数只是针对InnoDB 存储引擎的，累加的算法也略有不同。

3.2、定位低效率执行SQL

可以通过以下两种方式定位执行效率较低的 SQL 语句。

慢查询日志 : 通过慢查询日志定位那些执行效率较低的 SQL 语句，用–log-slow-queries[=ﬁle_name]选项启动时，mysqld 写一个包含所有执行时间超过 long_query_time 秒的 SQL 语句的日志文件。
show processlist : 慢查询日志在查询结束以后才纪录，所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题，可以使用show processlist命令查看当前MySQL在进行的线程，包括线程的状态、是否锁表等，可以实时地查看 SQL 的执行情况，同时对一些锁表操作进行优化。

1、使用show processlist 指令连接和执行操作

1	show processlist;

查看执行效果

查看 Info 列信息

1 2	/* ApplicationName=DataGrip 2020.2.2 */ 操作数据库的应用，我这里用的是`DATAGRIP` show processlist;//执行的语句

上述参数说明

id列，用户登录mysql时，系统分配的”connection_id”，可以使用函数connection_id()查看
user列，显示当前用户。如果不是root，这个命令就只显示用户权限范围的sql语句
host列，显示这个语句是从哪个ip的哪个端口上发的，可以用来跟踪出现问题语句的用户
db列，显示这个进程目前连接的是哪个数据库
command列，显示当前连接的执行的命令，一般取值为休眠（sleep），查询（query），连接
（connect）等
time列，显示这个状态持续的时间，单位是秒
state列，显示使用当前连接的sql语句的状态，很重要的列。state描述的是语句执行中的某一个状态。一
个sql语句，以查询为例，可能需要经过copying to tmp table、sorting result、sending data等状态才可以完成
info列，显示这个sql语句，是判断问题语句的一个重要依据

2、使用show processlist实时监控慢查询sql

在一张大数据表中，执行一条慢查询sql，然后快速使用 show processlist 查看，结果如下

3.3、Explain分析执行计划

通过以上步骤查询到效率低的 SQL 语句后，可以通过 EXPLAIN或者 DESC命令获取 MySQL如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。

1、使用 explain 查询SQL语句的执行计划

explain 要查询的SQL语句;
这里以视频给的数据库表为例

1	explain select * from tb_item;

结果

说明

字段	含义
id	select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序。
select_type	表示 SELECT 的类型，常见的取值有 SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个或者后面的查询语句）、SUBQUERY（子查询中的第一个 SELECT）等
table	输出结果集的表
type	表示表的连接类型，性能由好到差的连接类型为( system —> const —–> eq_ref ——> ref——-> ref_or_null—-> index_merge —> index_subquery —–> range —–> index ——> all )
possible_keys	表示查询时，可能使用的索引
key	表示实际使用的索引
key_len	索引字段的长度
rows	扫描行的数量
extra	执行情况的说明和描述

2、环境准备

准备三张表，表与表之间的关系如下

CREATE TABLE `t_role`
(
    `id`          varchar(32) NOT NULL,
    `role_name`   varchar(255) DEFAULT NULL,
    `role_code`   varchar(255) DEFAULT NULL,
    `description` varchar(255) DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `unique_role_name` (`role_name`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

CREATE TABLE `t_user`
(
    `id`       varchar(32) NOT NULL,
    `username` varchar(45) NOT NULL,
    `password` varchar(96) NOT NULL,
    `name`     varchar(45) NOT NULL,

    PRIMARY KEY (`id`),
    UNIQUE KEY `unique_user_username` (`username`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

CREATE TABLE `user_role`
(
    `id`      int(11) NOT NULL auto_increment,
    `user_id` varchar(32) DEFAULT NULL,
    `role_id` varchar(32) DEFAULT NULL,
    PRIMARY KEY (`id`),
    KEY `fk_ur_user_id` (`user_id`),
    KEY `fk_ur_role_id` (`role_id`),
    CONSTRAINT `fk_ur_role_id` FOREIGN KEY (`role_id`) REFERENCES `t_role` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION,
    CONSTRAINT `fk_ur_user_id` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`id`) ON DELETE NO ACTION ON UPDATE NO ACTION
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;



insert into `t_user` (`id`, `username`, `password`, `name`)
values ('1', 'super', '$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe', '超级管理员');
insert into `t_user` (`id`, `username`, `password`, `name`)
values ('2', 'admin', '$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe', '系统管理员');
insert into `t_user` (`id`, `username`, `password`, `name`)
values ('3', 'itcast', '$2a$10$8qmaHgUFUAmPR5pOuWhYWOr291WJYjHelUlYn07k5ELF8ZCrW0Cui', 'test02');
insert into `t_user` (`id`, `username`, `password`, `name`)
values ('4', 'stu1', '$2a$10$pLtt2KDAFpwTWLjNsmTEi.oU1yOZyIn9XkziK/y/spH5rftCpUMZa', '学生1');
insert into `t_user` (`id`, `username`, `password`, `name`)
values ('5', 'stu2', '$2a$10$nxPKkYSez7uz2YQYUnwhR.z57km3yqKn3Hr/p1FR6ZKgc18u.Tvqm', '学生2');
insert into `t_user` (`id`, `username`, `password`, `name`)
values ('6', 't1', '$2a$10$TJ4TmCdK.X4wv/tCqHW14.w70U3CC33CeVncD3SLmyMXMknstqKRe', '老师 1');



INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`)
VALUES ('5', '学生', 'student', '学生');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`)
VALUES ('7', '老师', 'teacher', '老师');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`)
VALUES ('8', '教学管理员', 'teachmanager', '教学管理员');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`)
VALUES ('9', '管理员', 'admin', '管理员');
INSERT INTO `t_role` (`id`, `role_name`, `role_code`, `description`)
VALUES ('10', '超级管理员', 'super', '超级管理员');



INSERT INTO user_role(id, user_id, role_id)
VALUES (NULL, '1', '5'),
       (NULL, '1', '7'),
       (NULL, '2', '8'),
       (NULL, '3', '9'),
       (NULL, '4', '8'),
       (NULL, '5', '10');

3、explain 之 id

id 字段是 select查询的序列号，是一组数字，表示的是查询中执行select子句或者是操作表的顺序。
id 情况有三种：

id 相同表示加载表的顺序是从上到下

这里以一条多表查询语句为例

EXPLAIN SELECT *
FROM t_user u
	JOIN user_role ur ON u.id = ur.user_id
	JOIN t_role tr ON ur.role_id = tr.id;

查看结果，可以看到 ur 表的加载顺序高于 tr 表和 u 表，第一列不是 id 。。。

id 不同id值越大，优先级越高

越先被执行，这里以一条子查询语句为例

EXPLAIN SELECT *
FROM t_role
WHERE id = (
	SELECT role_id
	FROM user_role
	WHERE user_id = (
		SELECT id
		FROM t_user
		WHERE username = 'stu1'
	)
);

执行语句查看结果，最内层的子查询的执行优先级最高。

id 有相同，也有不同，同时存在。

id相同的可以认为是一组，从上往下顺序执行；在所有的组中，id的值越大，优先级越高，越先执行。
这里以一条带子查询和连接查询的sql为例

EXPLAIN SELECT *
FROM t_role r, (
		SELECT *
		FROM user_role ur
		WHERE ur.`user_id` = '2'
	) a
WHERE r.id = a.role_id;

查看结果，发现先执行子查询，后执行连接查询。

4、explain 之 select_type

表示 SELECT 的类型，常见的取值如下表，从上到下效率越来越低。

select_type	含义
SIMPLE	简单的select查询，查询中不包含子查询或者UNION
PRIMARY	查询中若包含任何复杂的子查询，最外层查询标记为该标识
SUBQUERY	在SELECT 或 WHERE 列表中包含了子查询
DERIVED	在FROM 列表中包含的子查询，被标记为 DERIVED（衍生） MYSQL会递归执行这些子查询，把结果放在临时表中
UNION	若第二个SELECT出现在UNION之后，则标记为UNION ；若UNION包含在FROM子句的子查询中，外层SELECT将被标记为： DERIVED
UNION RESULT	从UNION表获取结果的SELECT

5、explain 之 table

展示这一行的数据是关于哪一张表的

6、explain 之 type

type 显示的是访问类型，是较为重要的一个指标，可取值为：

type	含义
NULL	MySQL不访问任何表，索引，直接返回结果
system	表只有一行记录(等于系统表)，这是`const`类型的特例，一般不会出现
`const`	表示通过索引一次就找到了，`const`用于比较primary key 或者 unique 索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL 就能将该查询转换为一个常亮。`const`于将”主键” 或 “唯一” 索引的所有部分与常量值进行比较
`eq_ref`	类似ref，区别在于使用的是唯一索引，使用主键的关联查询，关联查询出的记录只有一条。常见于主键或唯一索引扫描
ref	非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，返回所有匹配某个单独值的所有行（多个）
range	只检索给定返回的行，使用一个索引来选择行。 where 之后出现 between ， < , > , in 等操作。
index	index 与 ALL的区别为 index 类型只是遍历了索引树，通常比ALL 快， ALL 是遍历数据文件。
all	将遍历全表以找到匹配的行

type 为 null 的情况，例如使用 select now()访问当前时间

1	explain select now();

查看结果

type 为 system 的情况，数据源中只有一条数据时出现
type 为 const 的情况，使用 t_user 表中的 主键索引 和 唯一索引 查看

1 2	explain select * from t_user where username = 'super'; explain select * from t_user where id = '1';

type为 eq_ref 的情况，使用主键的关联查询，关联查询出的记录只有一条；
type为 index 的情况，查询整个索引树，效率通常不高
type为 all 的情况，遍历整个数据文件。

一般来说，我们需要保证查询至少达到 range 级别，最好达到 ref。

7、explain 之 key

possible_keys : 显示可能应用在这张表的索引，一个或多个。
key ：实际使用的索引，如果为NULL，则没有使用索引。
key_len : 表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的前提下，长度越短越好。
possible_keys : 显示可能应用在这张表的索引，一个或多个。
key ：实际使用的索引，如果为NULL，则没有使用索引。

8、explain 之 rows

扫描行的数量。

9、explain 之 extra

其他的额外的执行计划信息，在该列展示。
优化前两个，保持第三个。

extra	含义
`using ﬁlesort`	说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取，称为“文件排序”, 效率低。
using temporary	使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于 order by 和group by；效率低
using index	表示相应的select操作使用了覆盖索引，避免访问表的数据行，效率不错。索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据;当能通过读取索引就可以得到想要的数据，那就不需要读取行了。
Using where	表明使用了where过滤
using join buffer	使用了连接缓存：
impossible where	where子句的值总是false，不能用来获取任何元组

10、explain之case

执行顺序：4-3-2-1-NULL

执行顺序4

3.4、show profile分析SQL

Mysql从5.0.37版本开始增加了对 show proﬁles 和 show proﬁle 语句的支持。show proﬁles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。

1、查看当前MySQL是否支持profile

通过 have_proﬁling 参数，能够看到当前MySQL是否支持proﬁle：

1	select @@have_profiling;

默认proﬁling是关闭的，可以通过set语句在Session级别开启proﬁling：

1	set profiling = 1;

此时查看 profiling 的值。

1	select @@profiling;

2、使用 show profiles 指令查看sql语句和耗时

其中 Query 为执行的SQL语句，而 Duration 为该SQL执行耗费的时间。

在上面查询出来的结果之上，可以通过 show profile for query id值 指令来查看某条sql语句的详细信息，例如查看id为5的查询语句的详细信息。

1	show profile for query 5;

发现 Sending data 阶段花费了最多时间；
Sending data 状态表示MySQL线程开始访问数据行并把结果返回给客户端，而不仅仅是返回个客户端。
由于在Sending data状态下，MySQL线程往往需要做大量的磁盘读取操作，所以经常是整个查询中耗时最长的状态。

3.5、trace分析优化器执行计划

MySQL5.6提供了对SQL的跟踪trace, 通过trace文件能够进一步了解为什么优化器选择A计划, 而不是选择B计划。

1、打开trace 并设置格式和内存大小

打开trace ，设置格式为 JSON，并设置trace最大能够使用的内存大小，避免解析过程中因为默认内存过小而不能够完整展示。

1 2	SET optimizer_trace="enabled=on",end_markers_in_json=on; set optimizer_trace_max_mem_size=1000000;

2、执行SQL语句

1	select * from where id < 4;

3、查看Mysql如何执行上述的SQL语句

检查 infomation_schema.optimizer_trace 就可以知道MySQL是如何执行SQL的

1	select * from information_schema.optimizer_trace\G;

四、索引的使用

索引是数据库优化最常用也是最重要的手段之一，通过索引通常可以帮助用户解决大多数的MySQL的性能优化问题。

4.1、验证索引提升查询效率

在我们准备的表结构tb_item 中，一共存储了 300 万记录；

1、根据ID查询

1	select * from tb_item where id = 1999\G;

查询速度很快，接近0s ， 主要的原因是因为id为主键，有索引；

2、根据title进行精确查询

1	select * from tb_item where title = 'iphoneX 移动3G 32G941'\G;

执行sql语句，发现返回时间长达9s

使用 explain 查看SQL语句的执行计划：

3、优化查询，为title添加索引

1
2
3

create index idx_item_title on tb_item(title);

创建索引可能会花费一些时间，这是因为MySQL底层正在为3000000条数据创建索引。

索引创建完成之后，再次进行查询

再次通过explain查看SQL语句的执行计划，发现执行SQL时使用了刚才创建的索引

4.2、索引的使用

1、准备环境

use index_study;
create table `tb_seller`
(
    `sellerid`   varchar(100),
    `name`       varchar(100),
    `nickname`   varchar(50),
    `password`   varchar(60),
    `status`     varchar(1),
    `address`    varchar(100),
    `createtime` datetime,
    primary key (`sellerid`)
) engine = innodb
  default charset = utf8mb4;

insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`)
values ('alibaba', '阿里巴巴', '阿里小 店', 'e10adc3949ba59abbe56e057f20f883e', '1', '北京市', '2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`)
values ('baidu', '百度科技有限公司', '百度小 店', 'e10adc3949ba59abbe56e057f20f883e', '1', '北京市', '2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`)
values ('huawei', '华为科技有限公司', '华为小 店', 'e10adc3949ba59abbe56e057f20f883e', '0', '北京市', '2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`)
values ('itcast', '传智播客教育科技有限公司', '传智播 客', 'e10adc3949ba59abbe56e057f20f883e', '1', '北京市', '2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`)
values ('itheima', '黑马程序员', '黑马程序 员', 'e10adc3949ba59abbe56e057f20f883e', '0', '北京市', '2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`)
values ('luoji', '罗技科技有限公司', '罗技小 店', 'e10adc3949ba59abbe56e057f20f883e', '1', '北京市', '2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`)
values ('oppo', 'OPPO科技有限公司', 'OPPO官方旗舰 店', 'e10adc3949ba59abbe56e057f20f883e', '0', '北京市', '2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`)
values ('ourpalm', '掌趣科技股份有限公司', '掌趣小 店', 'e10adc3949ba59abbe56e057f20f883e', '1', '北京市', '2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`)
values ('qiandu', '千度科技', '千度小 店', 'e10adc3949ba59abbe56e057f20f883e', '2', '北京市', '2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`)
values ('sina', '新浪科技有限公司', '新浪官方旗舰 店', 'e10adc3949ba59abbe56e057f20f883e', '1', '北京市', '2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`)
values ('xiaomi', '小米科技', '小米官方旗舰 店', 'e10adc3949ba59abbe56e057f20f883e', '1', '西安市', '2088-01-01 12:00:00');
insert into `tb_seller` (`sellerid`, `name`, `nickname`, `password`, `status`, `address`, `createtime`)
values ('yijia', '宜家家居', '宜家家居旗舰 店', 'e10adc3949ba59abbe56e057f20f883e', '1', '北京市', '2088-01-01 12:00:00');


create index idx_seller_name_sta_addr on tb_seller (name, status, address);

2、避免索引失效–全值匹配

全值匹配，对索引中的所有列都指定具体值。
该情况下，索引生效，执行效率高。

1	explain select from tb_seller where name='小米科技' and status='1' and address='北京市'\G;

使用 explain 查看上述SQL的执行计划：

3、避免索引失效–最左前缀法则

如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过中间的列，
如果跳过中间列，那么只会使用最左列的索引。
上述的索引加在了(name, status, address)中，那么查询就必须从 name 开始且不能跳过 status 直接跳到address

匹配最左前缀法则，走索引：

1	explain select * from tb_seller where name='小米科技' and status='1';

违背最左前缀法则，索引失效

跳过name直接查询status/address，索引失效

1	explain select * from tb_seller where status = '1';

跳过索引列中间的status直接到address，此时只有最左边的索引列（name）生效

1	explain select * from tb_seller where name = '小米科技' and address = '西安市';

不使用索引的最左列进行查询，索引失效

1	explain select * from tb_seller where status = '1' and address = '西安市';

索引失效与否与 where 子句的组装顺序无关。

3、避免索引失效–范围查询右边的列，不能使用索引

范围查询右边的列将不走索引，例如status使用了范围查询，那么只走 name 和 status 的索引，不走 address 的索引。

1	explain select * from tb_seller where name = '小米科技' and status > '1' and address = '西安市';

4、避免索引失效–不要在索引列上进行运算操作

如果在索引列上进行运算操作，那么索引将失效

1	explain select * from tb_seller where substring(name,3,2) = '科技';

在status列上进行运算

1	explain select * from tb_seller where name = '小米科技' and status + 1 = '2';

5、避免索引失效–字符串必须加单引号

字符串不加单引号，造成索引失效。

1	explain select * from tb_seller where name = '小米科技' and status = '1';

演示不加单引号的情况

1	explain select * from tb_seller where name = '小米科技' and status = 1;

由于，在查询时没有对字符串加单引号，MySQL的查询优化器会自动的进行类型转换，造成索引失效。

6、尽量使用覆盖索引，避免select *

尽量使用覆盖索引（只访问索引的查询（索引列完全包含查询列）），减少select *
如果查询列，超出索引列，也会降低性能。（例如多查询了一个password会导致性能下降）
TIPS:

using index ：使用覆盖索引的时候就会出现
using where：在查找使用索引的情况下，需要回表去查询所需的数据
using index condition：查找使用了索引，但是需要回表查询数据
using index ; using where：查找使用了索引，但是需要的数据都在索引列中能找到，所以不需要回表查询数据

7、or分隔的条件

or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。
示例，name字段是索引列，而createtime不是索引列，中间是or进行连接是不走索引的：

1	explain select from tb_seller where name='黑马程序员' or createtime = '2088-01-01 12:00:00'\G;

8、以%开头的Like模糊查询，索引失效。

如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。

1	explain select * from tb_seller where name like '%黑马';

但右%不会导致索引失效

1	explain select * from tb_seller where name like '黑马%';

解决方法

通过覆盖索引（尽量不要使用select * ，使用索引中的那些列）来解决

1	explain select name,status,address from tb_seller where name like '%黑马%';

如果查询的列中有不含索引的属性，那么索引将失效。

9、如果MySQL评估使用索引比全表更慢，则不使用索引。

为 address 创建一个单列索引

1	create index `idx_address` on tb_seller(address);

执行以下的 sql 语句

1	explain select * from tb_seller where address = '北京市';

将上述的条件修改为 西安市，再次执行

1	explain select * from tb_seller where address = '西安市';

为什么？

因为在tb_seller表中 address 为北京市的数据占比非常大，使用索引不如进行全表扫描。

10、is NULL、is NOT NULL `有时`索引失效。

由于上面我们为 address 添加了单列索引，所以我们以 address 为例。

IS NULL

1	explain select * from tb_seller where address is null;

发现此时走了索引。

IS NOT NULL

1	explain select * from tb_seller where address is not null;

发现此时不走索引。

原因

这是因为在 tb_seller 表中绝大部分数据的 address 属性都不为空，那么当使用 IS NOT NULL 的时候，由于非空的数据太多，还不如直接扫描全表。
而使用 is null 时，由于为空的数据不多，所以使用索引速度快。

11、In、not In `有时` 不使用索引

使用in进行测试

1	explain select * from tb_seller where sellerid in ('oppo','xiaomi','sina');

使用 not in 进行测试，发现使用索引。

1	explain select * from tb_seller where sellerid not in ('oppo','xiaomi','sina');

In、not in 使不使用索引不是一刀切的。

12、单列索引和复合索引

尽量使用复合索引，而少使用单列索引。
创建复合索引：

1	create index idx_name_sta_address on tb_seller(name, status, address);

相当于创建了三个索引：
name
name + status
name + status + address

创建单列索引，在我们使用多个带索引的字段进行搜索时，不会使用全部索引

1
2
3

create index idx_seller_name on tb_seller(name);
create index idx_seller_status on tb_seller(status);
create index idx_seller_address on tb_seller(address);

数据库会选择一个最优索引来使用。

演示单列索引和复合索引的区别

删除上面为 address 创建的单列索引

1	drop index `idx_address` on tb_seller;

此时使用复合索引进行数据查找

1	explain select * from tb_seller where name = '小米科技' and status = '1' and address = '西安市';

删除表中的复合索引

1	drop index idx_seller_name_sta_addr on tb_seller;

创建三个单列索引

1
2
3

create index idx_seller_name on tb_seller(name);
create index idx_seller_status on tb_seller(status);
create index idx_seller_address on tb_seller(address);

再次执行上面的查询语句

1	explain select * from tb_seller where name = '小米科技' and status = '1' and address = '西安市';

由于name列中的数据辨识度最高。

4.3、查看索引使用情况

1、查看当前连接（会话）的索引情况

1	show status like 'Handler_read%';

2、查看全局索引使用情况

1	show global status like 'Handler_read%';

3、属性说明

Handler_read_first：索引中第一条被读的次数。如果较高，表示服务器正执行大量全索引扫描（这个值越低
越好）。
Handler_read_key：如果索引正在工作，这个值代表一个行被索引值读的次数，如果值越低，表示索引得到的
性能改善不高，因为索引不经常使用（这个值越高越好）。
Handler_read_next ：按照键顺序读下一行的请求数。如果你用范围约束或如果执行索引扫描来查询索引列，
该值增加。
Handler_read_prev：按照键顺序读前一行的请求数。该读方法主要用于优化ORDER BY … DESC。
Handler_read_rnd ：根据固定位置读一行的请求数。如果你正执行大量查询并需要对结果进行排序该值较高。你可能使用了大量需要MySQL扫描整个表的查询或你的连接没有正确使用键。这个值较高，意味着运行效率低，应
该建立索引来补救。
Handler_read_rnd_next：在数据文件中读下一行的请求数。如果你正进行大量的表扫描，该值较高。通常说明你的表索引不正确或写入的查询没有利用索引。

4.4、综合案例

1、环境准备

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

2、案例1

分析以下案例的索引使用情况

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

优化器会自动优化顺序

3、案例2

分析以下案例的索引使用情况

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

这里仍然用到了4个索引，因为MySQL优化器会优化顺序，将其变为a1,a2,a3,a4，虽然范围比较后的列不使用索引，但范围索引的列是使用索引的，所以使用了4个索引。
可以看到，此处的key_len与上面一致，都是164，所以使用了4个索引

4、案例3

分析以下案例的索引使用情况

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

这里其实也使用了c3这个索引，只不过c3作用在排序而不是查找，所以没有统计进key中。
没有使用C4索引

5、案例4

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

使用了索引，但出现了 filesort，因为此时没有c3，c4索引无法使用了。

6、案例5

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

这里使用了c1索引，但由于c3，c2顺序没有按建索引时的顺序排序，所以c3，c2所以失效，出现了 filesort

7、案例6

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

这里使用了c1，c2索引进行排序，由于c2已经有值了（已经是一个常量了），所以此时c3依然生效，此时c3索引用于排序

4.5、聚簇索引

这两者都是使用 B+ 树这种索引结构

1、聚簇索引

将数据和索引放在一起，而且是按照一定顺序组织的，找到索引就等于找到了数据。聚簇索引中，数据的物理存放顺序和索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。

2、非聚簇索引

叶子节点不存放数据，而是存放数据地址，这意味着在找到数据索引后还需要根据索引上的地址再去磁盘上取数据，类似书的目录。

3、优势

查询通过聚簇索引可以直接获取数据，因为聚簇索引的数据与索引存放在一起，相比非聚簇索引需要第二次查询（非覆盖索引的情况下）效率更高
聚簇索引对于范围查找的效率很高，因为其数据是按大小排序的
聚簇索引适合用在排序的场合，而非聚簇索引不适合

4、劣势

维护索引很昂贵，尤其是插入新行或者主键被更新导致要分页的时候。建议在大量插入数据后，选择负载降低的时间段通过 OPTIMIZE TABLE 优化表。
如果表使用 UUID（随机ID）作为主键，使数据存储稀疏，这就会出现聚簇索引可能比全表扫描更慢，所以建议使用自增主键或者雪花算法生成的唯一ID作为主键
如果主键比较大的话，那么辅助索引将会变得更大，因为辅助索引的叶子节点存储的是主键值，过长的主键会导致叶子节点占用更多空间。

5、补充

InnoB中一定有主键,主键一定是聚族索引，不手动设置、则会使用 unique素引,没有 unique素引,则会使用数据库内部的一个行的隐藏id来当作主键索引。在聚簇索引之上创建的索引称之为辅助索引,辅助索引访问数据总是需要二次查找,非聚簇索引都是铺助索引,像复合索引、前缀索引、唯一索引,辅助索引叶子节点存储的不再是行的物理位置,而是主键值。
MyISAM使用的是非聚簇索引,没有聚簇索引,非聚簇索引的两棵B+树看上去没什么不同,节点的结构完全一致只是存储的内容不同而已,主键索引B+树的节点存储了主键,辅助键索引B+树存储了辅助键。表数据存储在独立的地方,这两颗B+树的叶子节点都使用一个地址指向真正的表数据,对于表数据来说,这两个键没有任何差别。由于索引树是独立的,通过辅助键检索无需访问主键的索引树。

五、SQL优化（二）

5.1、大批量插入数据

1、环境准备

创建两张表，两张表结构相同，名字不同；
tb_user_1和tb_user_2

CREATE TABLE `tb_user_2`
(
    `id`          int(11)     NOT NULL AUTO_INCREMENT,
    `username`    varchar(45) NOT NULL,
    `password`    varchar(96) NOT NULL,
    `name`        varchar(45) NOT NULL,
    `birthday`    datetime    DEFAULT NULL,
    `sex`         char(1)     DEFAULT NULL,
    `email`       varchar(45) DEFAULT NULL,
    `phone`       varchar(45) DEFAULT NULL,
    `qq`          varchar(32) DEFAULT NULL,
    `status`      varchar(32) NOT NULL COMMENT '用户状态',
    `create_time` datetime    NOT NULL,
    `update_time` datetime    DEFAULT NULL,
    PRIMARY KEY (`id`),
    UNIQUE KEY `unique_user_username` (`username`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8;

对于 InnoDB 类型的表，有以下几种方式可以提高导入的效率：

主键顺序插入
关闭唯一性校验
手动提交事务

2、InnoDB引擎表插入优化 - 主键顺序插入

因为InnoDB类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效的提高导入数据的效率。如果InnoDB表没有主键，那么系统会自动默认创建一个内部列作为主键，所以如果可以给表创建一个主键，将可以利用这点，来提高导入数据的效率。
脚本文件介绍（大小相同） :

sql1.log —-> 主键有序
sql2.log —-> 主键无序

使用 load data local infile 文件名 into 表名 进行数据加载，查看结果

插入ID顺序排列数据：

插入ID无序排列数据：

2、InnoDB引擎表插入优化-关闭唯一性校验

在导入数据前执行 SET UNIQUE_CHECKS=0，关闭唯一性校验，在导入结束后执行SET UNIQUE_CHECKS=1，恢复唯一性校验，可以提高导入的效率。

3、InnoDB引擎表插入优化-手动提交事务

如果应用使用自动提交的方式，建议在导入前执行 SET AUTOCOMMIT=0，关闭自动提交，导入结束后再执行 SET AUTOCOMMIT=1，打开自动提交，也可以提高导入的效率。

5.2、优化insert语句

当进行数据的insert操作的时候，可以考虑采用以下几种优化方案。

1、使用多个值表的insert语句

如果需要同时对一张表插入很多行数据时，应该尽量使用多个值表的insert语句，这种方式将大大的缩减客户端与数据库之间的连接、关闭等消耗。使得效率比分开执行的单个insert语句快。

原始方案为：

1
2
3

insert into tb_test values(1,'Tom');
insert into tb_test values(2,'Cat');
insert into tb_test values(3,'Jerry');

优化后的方案为：

1	insert into tb_test values(1,'Tom'),(2,'Cat')，(3,'Jerry');

2、在执行大量的insert语句前使用事务的手动提交

如果数据量实在太大，建议分段提交。
每一万条数据 insert 一次。

start transaction;
insert into tb_test values(1,'Tom');
insert into tb_test values(2,'Cat');
insert into tb_test values(3,'Jerry');
commit;

3、数据有序插入

按照主键顺序插入

优化前

insert into tb_test values(4,'Tim');
insert into tb_test values(1,'Tom');
insert into tb_test values(3,'Jerry');
insert into tb_test values(5,'Rose');
insert into tb_test values(2,'Cat');

优化后

insert	into	tb_test	values(1,'Tom');
insert	into	tb_test	values(2,'Cat');
insert	into	tb_test	values(3,'Jerry');
insert	into	tb_test	values(4,'Tim');
insert	into	tb_test	values(5,'Rose');

5.3、优化order by语句

1、准备环境

use index_study;
CREATE TABLE `emp`
(
    `id`     int(11)      NOT NULL AUTO_INCREMENT,
    `name`   varchar(100) NOT NULL,
    `age`    int(3)       NOT NULL,
    `salary` int(11) DEFAULT NULL,
    PRIMARY KEY (`id`)
) ENGINE = InnoDB
  DEFAULT CHARSET = utf8mb4;
  
insert into `emp` (`id`, `name`, `age`, `salary`)
values ('1', 'Tom', '25', '2300');
insert into `emp` (`id`, `name`, `age`, `salary`)
values ('2', 'Jerry', '30', '3500');
insert into `emp` (`id`, `name`, `age`, `salary`)
values ('3', 'Luci', '25', '2800');
insert into `emp` (`id`, `name`, `age`, `salary`)
values ('4', 'Jay', '36', '3500');
insert into `emp` (`id`, `name`, `age`, `salary`)
values ('5', 'Tom2', '21', '2200');
insert into `emp` (`id`, `name`, `age`, `salary`)
values ('6', 'Jerry2', '31', '3300');
insert into `emp` (`id`, `name`, `age`, `salary`)
values ('7', 'Luci2', '26', '2700');
insert into `emp` (`id`, `name`, `age`, `salary`)
values ('8', 'Jay2', '33', '3500');
insert into `emp` (`id`, `name`, `age`, `salary`)
values ('9', 'Tom3', '23', '2400');
insert into `emp` (`id`, `name`, `age`, `salary`)
values ('10', 'Jerry3', '32', '3100');
insert into `emp` (`id`, `name`, `age`, `salary`)
values ('11', 'Luci3', '26', '2900');
insert into `emp` (`id`, `name`, `age`, `salary`)
values ('12', 'Jay3', '37', '4500');

create index idx_emp_age_salary on emp (age, salary);

2、MySQL支持的两种排序方式

第一种是通过对返回数据进行排序，也就是通常说的 ﬁlesort 排序，所有不是通过索引直接返回排序结果的排序都叫 FileSort 排序。
第二种通过有序索引顺序扫描直接返回有序数据，这种情况即为 using index，不需要额外排序，操作效率高。

3、查询所有字段进行的排序

查询所有字段，根据 age 进行升序/降序排序。

1 2	explain select * from emp order by age; explain select * from emp order by age desc;

和根据 salary 进行排序一样，这里进行的都是文件排序 filesort

查询所有字段，根据 age 和 salary 进行排序

1 2	explain select * from emp order by age,salary; explain select * from emp order by age,salary desc;

此时仍然为 filesort

4、查询索引字段进行的排序

如果使用 覆盖索引 ，那么就会使用索引排序。

5、查询字段中有非索引字段进行的排序

如果查询的字段中有非索引字段，例如 name ，那么将会使用 filesort

6、优化目标

了解了MySQL的排序方式，优化目标就清晰了：

尽量减少额外的排序，通过索引直接返回有序数据。
where 条件和Order by 使用相同的索引，并且Order By 的顺序和索引顺序相同，并且Order by 的字段都是升序，或者都是降序。否则肯定需要额外的操作，这样就会出现 FileSort。

7、举例

假设现有一复合索引 idx_a_b_c(a,b,c)，则以下情况可以避免filesort

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 and b > const order by c;
不能使用索引进行排序的情况
- order by a asc,b desc,c desc
上述语句排序不一致，必须要么全为desc，要么全为asc
- where g = const order by b,c
上述语句丢失a索引
- where a = const order by c
上述语句丢失b索引
- where a = const order by a,d
上述语句中，d不是索引的一部分

8、`FileSort`的优化

通过创建合适的索引，能够减少 Filesort 的出现，但是在某些情况下，条件限制不能让Filesort消失，那就需要加快 Filesort 的排序操作。对于 Filesort ， MySQL 有两种排序算法：

两次扫描算法：MySQL4.1 之前，使用该方式排序。首先根据条件取出排序字段和行指针信息，然后在排序区 sort buﬀer 中排序，如果sort buﬀer不够，则在临时表 temporary table 中存储排序结果。完成排序之后，再根据行指针回表读取记录，该操作可能会导致大量随机I/O操作。
一次扫描算法：一次性取出满足条件的所有字段，然后在排序区 sort buﬀer 中排序后直接输出结果集。排序时内存开销较大，但是排序效率比两次扫描算法要高。

MySQL 通过比较系统变量 max_length_for_sort_data 的大小和Query语句取出的字段总大小，来判定是否那种排序算法，如果max_length_for_sort_data 更大，那么使用第二种优化之后的算法；否则使用第一种。
可以适当提高 sort_buﬀer_size 和 max_length_for_sort_data 系统变量，来增大排序区的大小，提高排序的效率。

5.4、优化group by 语句

由于GROUP BY 实际上也同样会进行排序操作，而且与ORDER BY 相比，GROUP BY 主要只是多了排序之后的分组操作。当然，如果在分组的时候还使用了其他的一些聚合函数，那么还需要一些聚合函数的计算。
所以，在 GROUP BY 的实现过程中，与 ORDER BY 一样也可以利用到索引。
如果查询包含 group by 但是用户想要避免排序结果的消耗，则可以执行order by null禁止排序。如下：

1、删除复合索引，查看group by是否包含排序

1	explain select age,count(*) from emp group by age;

2、使用order by null优化group by避免排序

1	explain select age,count(*) from emp group by age order by null;

从上面的例子可以看出，第一个SQL语句需要进行”ﬁlesort”，而第二个SQL由于order by null 不需要进行
“ﬁlesort”，而上文提过Filesort往往非常耗费时间。

3、使用索引优化Group by

1 2	create index idx_emp_age_salary on emp(age,salary); explain select age,count(*) from emp group by age order by null;

5.5、优化嵌套查询（子查询）

Mysql4.1版本之后，开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询是可以被更高效的连接（JOIN）替代。
示例，查找有角色的所有的用户信息 :

1、优化前

1	explain select * from t_user where id in (select user_id from user_role );

2、优化后

1	explain select * from t_user u , user_role ur where u.id = ur.user_id;

连接( Join)查询之所以更有效率一些，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作。

5.6、优化OR条件

包含OR的查询子句，如果要利用索引，则OR之间的每个条件列都必须用到索引，而且不能使用到复合索引；如果没有索引，则应该考虑增加索引。

1、获取 emp 表中的所有的索引

1	show index from emp;

2、使用union替换or

1	explain select * from emp where id = '1' or id = '10';

1	explain select * from emp where id = '1' union select * from emp where id = '10';

我们来比较下重要指标，发现主要差别是 type 和 ref 这两项

type 显示的是访问类型，是较为重要的一个指标，结果值从好到坏依次是：
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

UNION 语句的 type 值为 ref，OR 语句的 type 值为 range，可以看到这是一个很明显的差距
UNION 语句的 ref 值为 const，OR 语句的 type 值为 null，const 表示是常量值引用，非常快这两项的差距就说明了 UNION 要优于 OR 。

5.7、优化分页查询

一般分页查询时，通过创建覆盖索引能够比较好地提高性能。一个常见又非常头疼的问题就是 limit 2000000,10 ，此时需要MySQL排序前2000010 记录，仅仅返回2000000 - 2000010 的记录，其他记录丢弃，查询排序的代价非常大。

1、优化思路一

在索引上完成排序分页操作，最后根据主键关联回原表查询其他所需要的列的内容

常规写法

1	select * from tb_item limit 200000,10;

优化后写法

1	select t.* from tb_item as t join (select id from tb_seller order by id limit 200000,10) as a on a.id = t.id;

查看优化后写法的执行计划

2、优化思路二

该方案适用于主键自增的表，可以把Limit 查询转换成某个位置的查询。（自增的主键还不能出现断层。）

优化后写法

1	explain select * from tb_item where id > 200000 limit 10;

由于使用了索引，所以非常快

5.8、使用SQL提示

SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。

1、USE INDEX

在查询语句中表名的后面，添加 use index 来提供希望MySQL去参考的索引列表，就可以让MySQL不再考虑其他可用的索引。

查看表中的索引

假设现在有如下SQL

1	select * from tb_seller where name = '小米科技';

那么可能会使用两个索引，即 idx_seller_name 和 idx_seller_name_sta_adds

查看上述SQL的执行计划，发现其使用了 idx_seller_name索引

1	explain select * from tb_seller where name = '小米科技';

使用 use index 人为干预

1	explain select * from tb_seller use index(idx_seller_name_sta_adds) where name = '小米科技';

2、IGNORE INDEX

如果用户只是单纯的想让MySQL忽略一个或者多个索引，则可以使用 ignore index 作为提示

从上面可知，如果不加以干预，Mysql使用的是 idx_seller_name 索引，我们可以使用 IGNORE INDEX 让MySQL忽略 idx_seller_name 索引

1	explain select * from tb_seller ignore index(idx_seller_name) where name = '小米科技';

3、FORCE INDEX

为强制MySQL使用一个特定的索引，可在查询中使用 force index 作为提示。

为 address 属性添加一个单列索引

1	create index idx_address on tb_seller(address);

在 tb_seller 表中执行以下语句，MySQL不会使用索引

这是因为表中 address 属性为北京市的数据占绝大多数，使用索引不如全表扫描

这里我们可以使用 force index 强制MySQL使用索引

1	explain select * from tb_seller force index(idx_address) where address = '北京市';

4、force 和 use的区别

use index 更多的是建议，而force index是强制使用索引。
如果MySQL觉得你使用 use index 建议的索引效率比全表扫描慢，那么仍然不会走索引。
如果你使用 force index 强制MySQL使用你给的索引，那么即使你给的索引执行效率低，那么MySQL也会使用你给的索引。

5.9、分解关联查询

许多高性能的应用都会对关联查询进行分解。
即可以对每一个表进行一次 单表查询 ，然后将结果在应用程序中进行关联。
例如，下面这个查询中

select
  *
from
  tag as t
  join tag_post as tp on tp.tag_id = t.id
  join post as p on p.id = tp.post_id
where
  t.tag = 'mysql';

可以分解为下列这些查询来代替

1
2
3

select * from tag where tag = 'mysql';
select * from tag_post where tag_id = 1234;
select * from post where post.id in (123,456,678,9097);

这样做地原因？

让缓存的效率更高。

许多应用程序可以方便地缓存单表查询对应的结果对象。
例如，上面查询中的 tag 已经被缓存了，那么应用就可以跳过第一个对象。
再例如，应用中已经缓存了 post.id 为 123、456 的内容，那么第三个查询的 in 就可以少几个 ID.
另外，对于MySQL的查询缓存来说，如果关联的中的每个表发生了变化，那么就无法是哟查询缓存了，而拆分后，如果某个表很少改变，那么基于此表的查询就可以重复利用查询缓存。

将查询分解后，执行单个查询可以减少锁的竞争。
在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可拓展。
查询本身效率可能有所提升

这个例子中，使用 in 代替关联查询，可以让MySQL按照 ID 顺序进行查询，这比随机的关联要更高效。

可以减少冗余记录的查询。

在应用层做关联查询,意味着对于某条记录应用只需要查询一次,而在数据库中做关联查询,则可能需要重复地访问一部分数据。从这点看,这样的重构还可能会减少网络和内存的消耗。

六、补充-尚硅谷索引优化

6.1、索引单表查询优化案例

1、环境搭建

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


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


SELECT * FROM article;

2、案例

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

SELECT id,
		author_id
FROM article
WHERE category_id = 1
		AND comments > 1
ORDER BY  views DESC LIMIT 1;

使用explain 分析以上sql的执行计划，发现type为 ALL，即最坏的情况，Extra中还出现了 Using FileSort 的情况，这个时候必须进行优化。

开始优化

为 category_id、comments和views新建一个复合索引

1	alter table `article` add index idx_cate_comm_views (`category_id`,`comments`,`views`);

再次使用 explain 分析上面的sql，发现使用了索引，但 using filesort 依然存在

由于范围比较后的列索引会失效，所以 sql 的执行计划中出现了文件排序，我们需要删除上面这个不怎么合适的索引，重新再建一个。

删除上面的索引。

1	drop index idx_cate_comm_views on `article`;

绕过 comments 字段，为 category_id 和 views 字段建一个复合索引。

1	alter table `article` add index `idx_article_cate_views`(`category_id`,`views`);

再次运行SQL语句，查看执行计划

1	explain SELECT id,author_id FROM article WHERE category_id = 1 AND comments > 1 ORDER BY views DESC LIMIT 1;

6.2、索引关联查询优化案例

1、环境准备

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

2、两表案例

下面开始explain分析

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

给book表的card字段创建一个索引

1	alter table book add index `idx_book_card` (`card`);

此时再次进行explain分析

左连接在右表中创建索引

删除原有的book表中的索引，为class表的card字段创建一个索引

1 2	drop index `idx_book_card` on book; alter table class add index `idx_book_card` (`card`);

再次进行explain分析

可以看到，此时的type为index，index的效率比ref低。
左连接以左表作为主表，左边数据是一定都有的，左连接从右表中选择数据添加到左表中，右边是我们的关键点，所以必须对右表建索引。
右连接同理需要对左表的字段建索引。

3、三表案例

添加新表，插入数据

CREATE TABLE IF NOT EXISTS `phone`
(
    `phoneid` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
    `card`   INT(10) UNSIGNED NOT NULL,
    PRIMARY KEY (`phoneid`)
);

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

先删去上面为class创建的索引

1	drop index `idx_book_card` on class;

编写三表查询SQL

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

使用explain分析执行计划

给 phone 表和 book 表各自建一个索引

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

再次使用explain分析执行计划

4、结论

永远用小表（结果集）驱动大表（结果集）

先了解在join连接时，哪个表是驱动表，哪个是被驱动表
当使用left join时，左表是驱动表，右表是被驱动表
当使用right join时，右表时驱动表，左表是驱动表
当使用join时，mysql会选择数据量比较小的表作为驱动表，大表作为被驱动表

子查询尽量不要放在被驱动表，有可能使用不到索引。

join查询在有索引条件下
驱动表有索引不会使用到索引
被驱动表建立索引会使用到索引
在以小表驱动大表的情况下，再给大表建立索引会大大提高执行速度

在无法保证被驱动表的Join条件字段被索引且内存资源充足的情况下，不要太吝惜JoinButter 的设置

一、MySQL的体系结构概览

1.1、连接层

1.2、服务层

1.3、引擎层

1.4、存储层

二、存储引擎

2.1、存储引擎概述

1、查看当前数据库支持的存储引擎

2、查看默认的存储引擎

3、常见存储引擎介绍

2.2、InnoDB 存储引擎

1、概述

2、InnoDB 支持事务

3、InnoDB 支持外键

4、InnoDB 的存储方式

5、什么时候使用 InnoDB？

2.3、MyISAM 存储引擎

1、概述

2、不支持事务

3、文件存储方式

4、什么时候使用 MyISAM

2.4、Memory 存储引擎

三、SQL优化（一）

3.1、查看SQL执行频率

1、查询当前会话中所有统计参数值

2、查询全局统计参数值

3、查询InnoDB存储引擎的操作数量

4、参数说明

3.2、定位低效率执行SQL

1、使用show processlist 指令连接和执行操作

2、使用show processlist实时监控慢查询sql

3.3、Explain分析执行计划

1、使用 explain 查询SQL语句的执行计划

2、环境准备

3、explain 之 id

4、explain 之 select_type

5、explain 之 table

6、explain 之 type

7、explain 之 key

8、explain 之 rows

9、explain 之 extra

10、explain之case

3.4、show profile分析SQL

1、查看当前MySQL是否支持profile

2、使用 show profiles 指令查看sql语句和耗时

3.5、trace分析优化器执行计划

1、打开trace 并设置格式和内存大小

2、执行SQL语句

3、查看Mysql如何执行上述的SQL语句

四、索引的使用

4.1、验证索引提升查询效率

1、根据ID查询

2、根据title进行精确查询

3、优化查询，为title添加索引

4.2、索引的使用

1、准备环境

2、避免索引失效–全值匹配

3、避免索引失效–最左前缀法则

3、避免索引失效–范围查询右边的列，不能使用索引

4、避免索引失效–不要在索引列上进行运算操作

5、避免索引失效–字符串必须加单引号

6、尽量使用覆盖索引，避免select *

7、or分隔的条件

8、以%开头的Like模糊查询，索引失效。

9、如果MySQL评估使用索引比全表更慢，则不使用索引。

10、is NULL、is NOT NULL 有时索引失效。

11、In、not In 有时 不使用索引

12、单列索引和复合索引

4.3、查看索引使用情况

1、查看当前连接（会话）的索引情况

2、查看全局索引使用情况

3、属性说明

4.4、综合案例

1、环境准备

2、案例1

3、案例2

4、案例3

5、案例4

6、案例5

7、案例6

10、is NULL、is NOT NULL `有时`索引失效。

11、In、not In `有时` 不使用索引

8、`FileSort`的优化