MySQL高级学习笔记

耶耶耶耶耶 · 2022-6-23 21:04:11

文章目录

第1章 MySQL的架构介绍

1.1 MySQL简介

官网：https://www.mysql.com/
MySQL 是一个关系型数据库管理系统，由瑞典 MySQL AB 公司开发，目前属于 Oracle 公司
MySQL 是一种关联数据库管理系统（RDBMS），将数据保存在不同的表中，而不是将所有数据放在一个大的仓库内，这样就增加了速度并提高了灵活性。
MySQL 特征

MySQL 是开源的，所以你不需要支付额外的费用。
MySQL 支持大型的数据库。可以处理拥有处理上千万条记录的大型数据库。
MySQL 可以允许于多个系统上，并且支持多种语言，这些编程语言包含 C、C++、Java、Perl、PHP 、 Eiffel 、 Ruby 和 Tcl 等。
MySQL对 PHP 有很好的支持， PHP 是暮年最流行的 WEB 开发语言之一。
MySQL 支持大型数据库，支持 5000W 条数据记录的数据仓库， 32 为操作系统最大可支持 4GB ， 64位操作系统最大的表文件为 8TB。
MySQL 是可以支持定制的，采用了 GPL协议，你可以修改源码来开发自己的MySQL 系统。

1.2 在Linux上安装MySQL

MySQL高级的应用都是在Linux系统上进行，是对数据库的优化。
请参考博客：在Linux中安装MySQL
1.3 MySQL配置文件

Windows系统下mysql的配置文件为my.ini文件，Linux系统下mysql的配置文件为/etc/my.cnf文件。
MySQL主要配置文件如下：

二进制日志log-bin：用于主从复制
错误日志log-error：默认是关闭的，记录严重的警告和错误信息，每次启动和关闭的详细信息等
查询日志log：默认关闭，记录查询的sql语句，如果开启会减低mysql的整体性能，因为记录日志也是需要消耗系统资源的
数据文件
- 两系统
  - mysql安装目录下的data目录下可以挑选很多库
  - 默认路径：/var/lib/mysql
- frm文件：存放表结构
- myd文件：存放表数据
- myi文件：存放表索引

1.3 MySQL逻辑架构介绍

总体架构：

总体架构说明：

1、连接层：提供客户端和连接服务，包含本地Sock通信和大多数基于客户端/服务端工具实现的类似于TCP/IP的通信，主要完成一些类似于连接处理、授权认证、及相关的安全方案，在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程，同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。注：分配数据库连接线程池，控制数据库的连接和关闭等资源。
2、业务逻辑处理层：主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化及部分内置函数的操作。所有跨操作引擎的功能也在这一层实现，如过程，函数等。在该层服务器会解析查询并创建相应的内部解析树，并对其完成相应的优A化如确定查询表的顺序，是否利用索引等，最后生成相应的执行操作。如果是SELECT语句，服务器还会查询内部的缓存，如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能。注：提供SQL操作的接口，对SQL脚本按一定规则进行解析，并通过SQL优化器优化执行顺序，对于查询的语句还会进入缓存区，提升系统的性能。
3、数据存储引擎层：存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引[擎进行通信，不同的存储引|擎具有的功能不同，这样我们可以根据自己的实际需要进行选选取。注：存储引擎都是可插拔的，每个存储引l擎所提供的服务都有所差异，所以我们需要根据具体的业务需要，选择合适的存储引[擎，常用的只有两种MyISAM和lnnoDB。
4、数据存储层：主要是将数据存储在运行于裸设备的文件系统上，并完成与存储引擎的交互。注：将数据存储到磁盘上，并协同存储引擎对数据进行读写操作。
数据库逻辑结构共分为四层，分别是连接层（线程连接池)、业务逻辑处理层(SQL解析读取)、数据存储引擎层(存储擎)、数据存储层〈(数据存储)和其它的数据库相比，MySQL有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用，主要体现在存储引擎的架构上，插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离，这种架构可以根据业务的需求和实际需要选择合适的存储引擎。
1.4 MySQL存储引擎

1.4.1 查看命令

可以通过如下命令查看支持的存储引擎：

show engines; #查看支持的存储引擎

复制代码

通过如下命令查看当前默认的存储引擎：

show variables like '%storage_engines%'; #查看当前默认的存储引擎

复制代码

1.4.2 MyISAM和InnoDB

第2章索引优化分析

2.1 概述

2.1.1 性能下降原因

性能下降SQL慢，执行时间长，等待时间长的原因：

查询语句写的烂
索引失效：单值和复合
关联查询太多join（设计缺陷或不得已的需求）
服务器调优及各个参数设置不合理（缓冲、线程数等）

2.1.2 常用的join查询

2.1.2.1 SQL执行顺序

①手写
通常我们按照需求写的SQL查询语句是这样的：

SELECT DISTINCT查询列表
FROM 左表 INNER|LEFT|RIGHT
JOIN 右表 ON 连接条件
WHERE 分组前的筛选条件
GROUP BY 分组列表
HAVING 分组后的筛选条件
ORDER BY 排序列表
LIMIT 分页参数

复制代码

我们手写的SQL中SELECT在最前面的位置。
②机读
随着 Mysql 版本的更新换代，其优化器也在不断的升级，优化器会分析不同执行顺序产生的性能消耗不同而动态调整执行顺序。下面是经常出现的查询顺序：

FROM 左表 INNER|LEFT|RIGHT
JOIN 右表 ON 连接条件
WHERE 分组前的筛选条件
GROUP BY 分组列表
HAVING 分组后的筛选条件
SELECT DISTINCT查询列表
ORDER BY 排序列表
LIMIT 分页参数

复制代码

而在机读中FROM是最先执行的。
③总结

2.1.2.2 join图

左外连接

A表独有部分+AB两表的公有部分。

-- SQL语句如下：
SELECT 查询列表 FROM A LEFT JOIN B ON A.key=B.key# 其中A.key=B.key指的是连接条件
-- 注：
-- 左外连接中左边的是主表，右边的是从表

复制代码

右外连接

-- SQL语句如下：
SELECT 查询列表 FROM A RIGHT JOIN B ON A.key=B.key# 其中A.key=B.key指的是连接条件
-- 注：
-- 右外连接中右边的是主表，左边的是从表

复制代码

内连接

获取的是两张表的公有部分。

-- SQL语句如下：
SELECT 查询列表 FROM A INNER JOIN B ON A.key=B.key# 其中A.key=B.key指的是连接条件
-- 注：
-- 内连接求的是多张表的交集部分

复制代码

左外连接去除交集

SELECT 查询列表 FROM A LEFT JOIN B ON A.key=B.key WHERE B.key IS NULL;# 将从表B的连接条件作为NULL值判断

复制代码

右外连接去除交集

SELECT 查询列表 FROM A RIGHT JOIN B ON A.key=B.key WHERE A.key IS NULL;# 将从表A的连接条件作为NULL值判断

复制代码

全外连接

注意：MySQL不支持full join。

SELECT 查询列表 FROM A FULL JOIN B ON A.key=B.key;# 全外连接就是求并集
# 可以间接完成效果
SELECT 查询列表 FROM A LEFT JOIN B ON A.key=B.key
UNION
SELECT 查询列表 FROM A RIGHT JOIN B ON A.key=B.key

复制代码

全外连接去除交集

SELECT 查询列表 FROM A FULL JOIN B ON A.key=B.key WHERE A.key IS NULL OR B.key IS NULL;

复制代码

实例如下：

-- 左外连接
select * from beauty left join boys on beauty.boyfriend_id=boys.id;
-- 右外连接
select * from beauty right join boys on beauty.boyfriend_id=boys.id;
-- 内连接
select * from beauty inner join boys on beauty.boyfriend_id=boys.id;
-- 左外连接去除交集
select * from beauty left join boys on beauty.boyfriend_id=boys.id where boys.id IS NULL;
-- 右外连接去除交集
select * from beauty right join boys on beauty.boyfriend_id=boys.id where beauty.boyfriend_id IS NULL;
-- 全外连接(MySQL不能运行)
select * from beauty full join boys on beauty.boyfriend_id=boys.id;
-- 全外连接去除交集(MySQL不能运行)
select * from beauty full join boys on beauty.boyfriend_id=boys.id where boys.id IS NULL OR beauty.boyfriend_id IS NULL;

复制代码

2.2 索引简介

2.2.1 索引是什么

MySQL 官方对索引的定义为：索引（Index）是帮助 MySQL 高效获取数据的数据结构。可以得到索引的本质：索引是数据结构。可以简单理解为排好序的快速查找数据结构。
在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。下图就是一种可能的索引方式示例：

左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址。为了加快 Col2 的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。
我们平常所说的索引，如果没有特别指明，都是指B树(多路搜索树，并不一定是二叉的)结构组织的索引。其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种类型的索引之外，还有哈希索引(hashindex)等。
一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上。
2.2.2 索引的优缺点

优势：

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

劣势：

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

2.2.3 索引的分类

索引可以分为单值索引、唯一索引、主键索引和复合索引。
2.2.3.1 单值索引

即一个索引只包含单个列，一个表可以有多个单列索引。
创建单值索引的语法如下：

-- 创建情况一：在创建表时创建
CREATE TABLE 表名(
字段名字段类型 [约束],
字段名字段类型 [约束],
...
字段名字段类型 [约束],
KEY(字段名)# 这一句就是创建单列索引的语句，直接在KEY()中写字段名即可
);
-- 创建情况二：单独创建单值索引
CREATE INDEX 索引名 ON 表名(字段名);
# 注释：
# 1.索引名通常是idx_表名_字段名这样的格式，比如idx_user_name
# 2.单独创建索引的示例：CREATE INDEX idx_user_name user(nmae);

复制代码

2.2.3.2 唯一索引

即索引列的值必须唯一，但允许有空值。
创建唯一索引的语法如下：

-- 创建情况一：在创建表时创建
CREATE TABLE 表名(
字段名字段类型 [约束],
字段名字段类型 [约束],
...
字段名字段类型 [约束],
UNIQUE(字段名)# 这一句就是创建唯一索引的语句，直接在UNIQUE()中写字段名即可
);
-- 创建情况二：单独创建唯一索引
CREATE UNIQUE INDEX 索引名 ON 表名(字段名);
# 注释：
# 1.索引名通常是idx_表名_字段名这样的格式，比如idx_user_name
# 2.单独创建唯一索引的示例：CREATE UNIQUE INDEX idx_user_id user(id);

复制代码

2.2.3.3 主键索引

设定某字段为主键后，数据库会自动建立索引，innodb存储引擎的主键为聚簇索引。
创建索引的基本语法如下：

-- 创建情况一：在创建表时创建
CREATE TABLE 表名(
字段名字段类型 [约束],
字段名字段类型 [约束],
...
字段名字段类型 [约束],
PRIMARY KEY(字段名)# 这一句就是创建主键索引的语句，直接在PRIMARY KEY()中写字段名即可
);
-- 创建情况二：单独创建主键索引
ALTER TABLE 表名 ADD PRIMARY KEY 表名(字段名);
# 注释：
# 1.创建示例：ALTER TABLE user ADD PRIMARY KEY user(id);
-- 删除主键索引
ALTER TABLE 表名 DROP PRIMARY KEY;
# 注释：
# 1.删除示例：ALTER TABLE user DROP PRIMARY KEY;
# 2.如果要修改主键索引，那么必须先删除掉原索引，再新建索引

复制代码

2.2.3.4 复合索引

即一个索引包含多个列。
创建的基本语法如下：

-- 创建情况一：在创建表时创建
CREATE TABLE 表名(
字段名字段类型 [约束],
字段名字段类型 [约束],
...
字段名字段类型 [约束],
KEY(字段名,字段名,..)# 这一句就是创建复合索引的语句，直接在KEY()中写多个字段名即可
);
-- 创建情况二：单独创建复合索引
CREATE INDEX 索引名 ON 表名(字段名,字段名,...);

复制代码

2.2.4 基本语法

①创建索引

-- 语法
CREATE [UNIQUE] INDEX 索引名 ON 表名(字段名[,字段名,字段名,..]);

复制代码

②删除索引

-- 语法
DROP INDEX 索引名 ON 表名;

复制代码

③查看索引

-- 语法
SHOW INDEX FROM 表名;

复制代码

④修改索引

-- 语法
-- 通过修改语句添加主键索引
ALTER TABLE 表名 ADD PRIMARY KEY(字段名);# 该语句添加一个主键，这意味着索引值必须是唯一的，且不能为 NULL。
-- 通过修改语句添加普通索引
ALTER TABLE 表名 ADD INDEX 索引名(字段名,[字段名,..]);# 添加普通索引
-- 通过修改语句添加全文索引
ALTER TABLE 表名 ADD FULLTEXT 索引名(字段名,[字段名,..]);# 该语句指定了索引为FULLTEXT，用于全文索引

复制代码

2.2.5 索引的创建时机

2.2.5.1 适合创建索引的情况

主键自动建立唯一索引；
频繁作为查询条件的字段应该创建索引
查询中与其它表关联的字段，外键关系建立索引
单键/组合索引的选择问题，组合索引性价比更高
查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
查询中统计或者分组字段
频繁更新的字段不适合创建索引，因为每次更新不单单是更新了记录，还会更新索引，加重了IO负担。
where条件里用不到的字段不创建索引。

2.2.5.2 不适合创建索引的情况

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

2.3 性能分析

2.3.1 MySQL Query Optimizer

1、Mysql中有专门负责优化SELECT语句的优化器模块，主要功能:通过计算分析系统中收集到的统计信息，为客户端请求的Query提供他认为最优的执行计划（他认为最优的数据检索方式，但不见得是DBA认为是最优的，这部分最耗费时间)
2、当客户端向MySQL请求一条Query，命令解析器模块完成请求分类，区别出是SELECT并转发给MySQLQuery Optimizer时，MySQL Query Optimizer首先会对整条Query进行优化，处理掉一些常量表达式的预算，直接换算成常量值。并对Query中的查询条件进行简化和转换，如去掉一些无用或显而易见的条件、结构调整等。然后分析Query中的Hint信息(如果有)，看显示Hint信息是否可以完全确定该Query的执行计划。如果没有Hint或Hint信息还不足以完全确定执行计划，则会读取所涉及对象的统计信息，根据Query进行写相应的计算分析，然后再得出最后的执行计划。
2.3.2 MySQL常见瓶颈

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

2.3.3 Explain

2.3.3.1 是什么

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

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

2.3.3.3 怎么玩

-- 语法
EXPLAIN 查询语句;
-- 示例
EXPLAIN SELECT * FROM user;

复制代码

查询出来所包含的信息如下：

示例：

2.3.3.4 字段解释

2.3.3.4.1 id

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

id相同，执行顺序由上至下
id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
id相同不同，同时存在

说了表的读取顺序：先加载括号内的子查询。

2.3.3.4.2 select_type

select_type有如下类型值：

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

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

显示这一行的数据是关于哪张表的。
2.3.3.4.4 type

一般上百万条数据才进行优化。
type是访问类型排列，显示查询使用了何种类型，跟索引优化有很大的关系，需掌握。type的值有如下几种：

system：表只有一行记录（等于系统表)，这是const类型的特列，平时不会出现，这个也可以忽略不计。
const：表示通过索引一次就找到了,const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快如将主键置于where列表中,MySQL就能将该查询转换为一个常量。
eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
ref：非唯一性索引扫描,返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。
range：只检索给定范围的行,使用一个索引来选择行。key 列显示使用了哪个索引一般就是在你的where语句中出现了between、、in等的查询
这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引。
index：全索引扫描，index与ALL的区别为index只遍历索引树而非全表。通常比ALL快，因为索引文件通常比数据文件小。也就是all和index都是读全表，但index从索引中读取，而all从硬盘中读取。
all：全表扫描，将遍历全表找到匹配的行。

从最好到最差依次是：system>const>eq_ref>ref>range>index>all。
注：一般来说，得保证查询至少达到range级别，最好能达到ref。
如果百万级别或千万级别的记录查询出现的type是all，那么就需要考虑优化了。
2.3.3.4.5 possible_keys

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

列出实际上被用到的索引。如果为NULL，则没有使用索引。
通常同possible_keys来说，possible_keys是理论上可能会被用到的索引，而key是实际上用到的索引。例如请客估计应该来10人，这是possible_keys，而当天实际上来了6人，这是key。
若查询中使用了覆盖索引，则该索引和查询的select字段重叠。所谓的覆盖索引就是查询的字段正好是复合索引中的字段列表，那么就直接在索引中查找，而不是从全表中查找，如下图：

2.3.3.4.7 key_len

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。 key_len 字段能够帮你检查是否充分的利用上了索引。ken_len 越长，说明索引使用的越充分。但使用的长度越小越好。

如何计算：

①先看索引上字段的类型+长度比如 int=4 ; varchar(20) =20 ; char(20) =20
②如果是 varchar 或者 char 这种字符串字段，视字符集要乘不同的值，比如 utf-8 要乘 3,GBK 要乘 2，
③varchar 这种动态字符串要加 2 个字节
④允许为空的字段要加 1 个字节

第一组：key_len=age 的字节长度+name 的字节长度=4+1 + (20*3+2)=5+62=67
第二组：key_len=age 的字节长度=4+1=5

2.3.3.4.8 ref

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

如"const"表示一个常量，"mytest.emp.deptno"表示"mytest"数据库的"emp"表的"deptno"列的索引被使用了。
2.3.3.4.9 rows

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。
rows 列显示 MySQL 认为它执行查询时必须检查的行数。越少越好！

2.3.3.4.10 extra

extra中包含不适合在其他列中显示，但又十分重要的额外信息。
它可能的值有如下几种情况（重点关于①、②、③的情况）：
①Using filesort
说明mysql 会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL 中无法利用索引完成的排序操作称为“文件排序”。
注：出现了这个值就应该对查询语句进行优化了。
出现 filesort 的情况：

优化后，不再出现 filesort 的情况：

查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度（即将排序的字段添加到索引）。
②Using temporary
使了用临时表保存中间结果,MySQL 在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。
注：出现了这个值就应该对查询语句进行优化了。
优化前：

将group by、order by后面的字段添加到索引中去，如果已有索引，那么它们后面的字段的顺序应该跟复合索引中的字段顺序一样。
优化后：

③Using index
Using index 代表表示相应的 select 操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错！如果同时出现 using where，表明索引被用来执行索引键值的查找;如果没有同时出现 using where，表明索引只是用来读取数据而非利用索引执行查找。
利用索引进行了排序或分组。
④Using where
表明使用了 where 过滤。
⑤Using join buffer
使用了连接缓存。

⑥impossible where
where 子句的值总是 false，不能用来获取任何元组。比如查找一个人名字既为"张三"又为"李四"，不可能筛选成功。

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

在 Myisam 中：

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

2.4 索引优化

2.4.1 索引分析

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

复制代码

②查询及执行情况分析
查询需求：查询category_id为1并且comments>1的情况下，观看数量最多的文章

explain select id,author_id from article where category_id = 1 and comments > 1 order by views desc limit 1;

复制代码

执行情况：

type：ALL，表示是全表查询需要的记录，要考虑优化。
Extra:Using filesort，表示使用了文件内排序，又要考虑优化。

③如何优化
考虑建立表索引来进行优化。两种方式建立索引都可以，先创建索引试试：

ALTER TABLE article ADD INDEX idx_article_ccv (category_id, comments, views); --第一种方式
CREATE INDEX idx_article_ccv ON article (category_id, comments, views) ; --第二种方式

复制代码

可以使用show index from article;查看article表的索引情况。
再次查看执行计划：使用了索引，type也由all变成了range。

说明：type变成range这是可以的，但在extra中还是有Using filesort，这是无法接受的。我们创建了索引，但是没有被用到，这是因为按照BTree索引的工作原理，先排序category_id字段，如果遇到相同的category_id再排序comments字段，如果再遇到相同的comments字段再排序views字段。当comments字段在复合索引里处于中间位置时，因comments>1条件是一个范围值（所谓的range），MySQL无法利用索引对后面的views部分进行检索，即range类型查询字段后面的索引无效。
那么就需要删除索引，并重建有效的索引：

DROP INDEX idx_article_ccv ON article; -- 删除索引
CREATE INDEX idx_article_ccv ON article (category_id,views); -- 重建索引

复制代码

即既然范围值会使索引失效，那么就不对它建立索引即可。

解决了Using filesort问题，也使用了索引。
总结：对单表中要查询的字段添加索引，对如果条件是一个范围值的话就不添加索引。
2.4.1.2 双表

①创建测试表
测试用的数据如下：

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

复制代码

②查询及执行情况分析
查询需求：关联class表和book表

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

复制代码

发现是全表扫描，即type为ALL，那么需要进行优化。
如何优化
由于是LEFT JOIN，所以左表class表是主表，因此第一次添加索引的尝试添加在主表上。

CREATE INDEX idx_class_card ON class (card); -- 对class表的card字段添加索引
EXPLAIN SELECT * from class LEFT JOIN book ON class.card = book.card; -- 再次查看执行计划

复制代码

发现class表添加索引并且使用索引成功。
但查看rows字段发现还是全表扫描。
那么接下来为右表book添加索引：

DROP INDEX idx_class_card on class; -- 删除class表索引
CREATE INDEX idx_book_card ON book (card); -- 为book表的card字段添加索引
EXPLAIN SELECT * from class LEFT JOIN book ON class.card = book.card; -- 查看执行计划

复制代码

发现type变成了ref，效果比index好，并且rows只扫描了一行。
总结：当是两张表时，如果是LEFT JOIN左连接，由于左表数据全部都有，所以关键在于如何从右表进行搜索，故右表一定要为连接条件的字段添加索引；如果是RIGHT JOIN右连接，由于右表数据全部都有，所以关键在于如何从左表进行搜索，故左表一定要为连接条件的字段添加索引。
2.4.1.3 三表

①创建测试表
在双表的基础上新增一张表：

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

复制代码

使用show index from book;语句发现book表刚才添加的索引没有删除掉，那么删除干净：

show index from book; -- 查看book表的索引情况
drop index idx_book_card on book; -- 删除book表的索引

复制代码

②查询及执行情况分析
查询需求：关联book、class、phone三张表。
查看查询计划情况：

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左连接，那么在右表添加索引即可，但这里使用了三张表，那么需要在book表为card字段添加索引，在phone表为card字段添加索引。

CREATE INDEX idx_phone_card ON phone(card); -- 为phone表的card字段添加索引
CREATE INDEX idx_book_card ON book (card); -- 为book表的card字段添加索引
EXPLAIN SELECT * from class LEFT JOIN book ON class.card = book.card LEFT JOIN phone ON book.card = phone.card; -- 查看执行计划

复制代码

总结：如果是三表及多表查询，按照双表的左外连接和右外连接的情况来分批次处理即可。
2.4.1.4 总结

语句优化应尽可能减少join语句中NestedLoop的循环总次数，即“永远用小结果集驱动大结果集*（即添加索引变为小结果集）*”。
优先优化NestedLoop的内层循环。
尽量保证join语句中被驱动表的条件字段添加了索引（即LEFT JOIN在右表上添加，反之亦然）。
当无法保证被驱动表的条件字段添加索引时，且内存资源充足的前提下，不妨调整join buffer以达到性能优化的目的。

2.4.2 索引失效

对于索引失效应该尽可能地避免。
2.4.2.1 创建测试表

测试表的SQL如下：

CREATE TABLE staffs(
id INT PRIMARY KEY AUTO_INCREMENT,
`name` VARCHAR(24) DEFAULT NULL 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());
INSERT INTO staffs(`name`,`age`,`pos`,`add_time`) VALUES(NULL,23,'test',NOW());
ALTER TABLE staffs ADD INDEX index_staffs_nameAgePos(`name`,`age`,`pos`); # 创建索引

复制代码

2.4.2.2 索引失效的各种情况

（1）全值匹配最好
查询的字段按照顺序在索引中都可以匹配，此时速度最快。
比如上面的测试用例中创建了复合索引(name, age, pos)，那么条件的参数（where name=xxx and age=xxx and pos=xxx）和索引中的字段个数相同并且顺序一致（name对应name，age对应age，pos对应age），那么情况最好，如下：

（2）最佳左前缀法制
如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
过滤条件要使用索引必须按照索引建立的顺序，依次满足，一旦跳过某个字段，索引后面的字段都无法被使用
多列索引是先按照第一列进行排序，然后在第一列排好序的基础上再对第二列排序，如果没有第一列的话，直接访问第二列，那第二列肯定是无序的，直接访问后面的列就用不到索引。
如果是多列复合索引，那么下面这些是有效的：

EXPLAIN SELECT * FROM staffs WHERE NAME = 'July';
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' and age = 15;
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' and age = 15 and pos= 'dev';

复制代码

如果是下面这些不按照顺序的，那么就是无效的：

EXPLAIN SELECT * FROM staffs WHERE age=25 and pos='dev';
EXPLAIN SELECT * FROM staffs WHERE pos='dev';

复制代码

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

EXPLAIN SELECT * FROM staffs WHERE NAME = 'July';
EXPLAIN SELECT * FROM staffs WHERE LEFT(NAME,4) = 'July'; -- 使用函数
EXPLAIN SELECT * FROM staffs WHERE NAME = '2000';
EXPLAIN SELECT * FROM staffs WHERE NAME = 2000; -- 使用类型转换

复制代码

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

使用范围查询（如>、21 and pos='dev'; -- 使用范围查询[/code]

**（5）尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select ***
使用覆盖索引（Using index）会提高检索效率：只访问索引列的查询（索引列和查询列一致，尽量不使用select *）。
即如果复合索引列（下例：name,age,pos）和*的字段个数相同，那么建议使用字段名检索而不是星号（ * ）。
例如：

EXPLAIN SELECT * FROM staffs WHERE name='July' and age=23 and pos='dev';
EXPLAIN SELECT * FROM staffs WHERE name='July' and age>21 and pos='dev'; -- 使用范围查询

复制代码

（6）mysql在使用不等于(!=或者)的时候无法使用索引会导致全表扫描
如：

EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' and age = 23 and pos= 'dev'; -- 应该减少select *
EXPLAIN SELECT name,age,pos FROM staffs WHERE NAME = 'July' and age = 23 and pos= 'dev'; -- 尽量使用覆盖索引

复制代码

（7）is null，is not null也无法使用索引
如果允许字段为空，则

IS NULL 不会导致索引失效
IS NOT NULL 会导致索引失效

EXPLAIN SELECT * FROM staffs WHERE NAME = 'July'; -- 使用=等号
EXPLAIN SELECT * FROM staffs WHERE NAME != 'July'; -- 使用!=不等号
EXPLAIN SELECT * FROM staffs WHERE NAME <> 'July'; -- 使用<>不等号

复制代码

（8）like以通配符开头（‘%abc…’），mysql索引失效，变成全表扫描的操作
如下：

EXPLAIN SELECT * FROM staffs WHERE name IS NULL; -- IS NULL的情况
EXPLAIN SELECT * FROM staffs WHERE name IS NOT NULL; -- IS NOT NULL的情况

复制代码

由上图可知，如果要使用模糊查询，那么尽可能让通配符%加在字符串的右边，这样不会让索引失效。
如果实在必须要用like '%字符串%'并且索引又不失效，那么可以使用覆盖索引（查询的字段尽量和索引字段匹配）。

即SELECT后面的字段是索引列字段。
（9）字符串不加单引号索引失效
如：

EXPLAIN SELECT * FROM staffs WHERE NAME like '%July%'; -- 左右都有通配符
EXPLAIN SELECT * FROM staffs WHERE NAME like '%July'; -- 模糊查询加左边
EXPLAIN SELECT * FROM staffs WHERE NAME like 'July%'; -- 模糊查询加右边

复制代码

（10）少用or，用它来连接时会索引失败
如：

EXPLAIN SELECT * FROM staffs WHERE NAME = '2000';
EXPLAIN SELECT * FROM staffs WHERE NAME = 2000; -- 如果不加单引号，就会发生类型转换，导致索引失效

复制代码

2.4.3 小总结

可以创建一张表进行测试：

EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' OR NAME = 'z3'; -- 使用OR导致索引失效

复制代码

2.4.4 优化口诀

全值匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
Like百分写最右，覆盖索引不写星；
不等空值还有or，索引失效要少用；
VAR引号不可丢，SQL高级也不难！
2.4.5 面试题讲解

SQL题目如下：

CREATE TABLE abcd(
a VARCHAR(10);
b VARCHAR(10);
c VARCHAR(10);
d VARCHAR(10);
);
INSERT INTO abcd(a,b,c,d) VALUES('1','2','3','4');
INSERT INTO abcd(a,b,c,d) VALUES('1','kkkk','4','5');

复制代码

题目练习：

#【建表语句】
create table test03 (
id int primary key not null auto_increment,
c1 varchar(10),
c2 varchar(10),
c3 varchar(10),
c4 varchar(10),
c5 varchar(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');
select * from test03;

复制代码

总结：

定值、范围还是排序，一般order by是给一个范围
group by基本上都是需要排序的，会有临时表产生

2.4.6 一般性建议

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

第3章查询截取分析

分析：

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

总结：

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

3.1 查询优化

3.1.1 永远小表驱动大表

#索引建立
create index idx_test03_c1234 on test03(c1, c2, c3, c4);
show index from test03;
#问题：我们创建了复合索引 idx_test03_c1234, 根据一下 SQL 分析下索引使用情况？
explain select * from test03 where c1 = 'a1';
explain select * from test03 where c1 = 'a1' and c2 = 'a2';
explain select * from test03 where c1 = 'a1' and c2 = 'a2' and c3 = 'a3';
explain select * from test03 where c1 = 'a1' and c2 = 'a2' and c3 = 'a3' and c4 = 'a4';
#1) Yes
explain select * from test03 where c1='a1' and c2='a2' and c3='a3' and c4='a4';
#2) Yes
explain select * from test03 where c1='a1' and c2='a2' and c4='a4' and c3='a3';
#3) Yes(5.6+ Using index condition)
explain select * from test03 where c1='a1' and c2='a2' and c3>'a3' and c4='a4';
#4) Yes(5.6+ Using index condition)
explain select * from test03 where c1='a1' and c2='a2' and c4>'a4' and c3='a3';
#5)Yes(5.6+ Using index condition)
explain select * from test03 where c1='a1' and c2='a2' and c4='a4' order by c3;
#c3 的作用在于排序而不是查找
#6）Yes(5.6+ Using index condition)
explain select * from test03 where c1='c1' and c2='c2' order by c3;
#7) Yes(5.6+ Using index condition)
explain select * from test03 where c1='c1' and c2='c2' order by c4;
#8) Yes(5.6+ Using index condition, Using where)
explain select * from test03 where c1='a1' and c5='a5' order by c2, c3;
#9) NO (Using index condition; Using where; Using filesort)
explain select * from test03 where c1='a1' and c5='a5' order by c3, c2;
#10) Yes(5.6+ Using index condition)
explain select * from test03 where c1='a1' and c2='a2' order by c2, c3;
#11) Yes(Using index condition; Using where)
explain select * from test03 where c1='a1' and c2='a2' and c5='a5' order by c2, c3;
#12) Yes(5.6+ Using index condition)
explain select * from test03 where c1='a1' and c2='a2' and c5='a5' order by c3, c2;
#本例有常量c2的情况，和#9对比
#13) No (Using index condition; Using where; Using filesort)
explain select * from test03 where c1='a1' and c5='a5' order by c3, c2;
#14) Yes(Using where; Using index)
explain select c2, c3 from test03 where c1='a1' and c4='a4' group by c2, c3;
#15) NO (Using where; Using index; Using temporary; Using filesort)
explain select c2, c3 from test03 where c1='a1' and c4='a4' group by c3, c2;

复制代码

优化原则：小表驱动大表

3.1.2 order by关键字优化

3.1.2.1 order by优化

首先创建测试表：

# 优化原则：小表驱动大表，即小的数据集合驱动大的数据集合
##################### 原理（RBO）###############
select * from A where id in (select id from B)
#等价于
for select id from B
for select id 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 id from A
for select id from B where B.id = A.id
#当 A 表的数据集系小于表的数据集，用 exists 优于 in
#注意： A与B表的id 字段应该建立索引
## exists
select ... from table where exists (subquery);
# 该语法可以理解为：将主查询的数据，放到子查询中做条件验证，根据验证结果（TRUE 或者 FALSE）来决定主查询数据结果是否得到保留。
## 提示
# 1，EXISTS (subquery) 只返回 True 或 False ，因此查询的 SELET * 也可以是SELET 1 或其他，官方说法是执行时会忽略SELECT 清单，因此没有区别
# 2. EXISTS 子查询的实际执行过程可能经过了优化而不是我们理解的逐条比对，如果担忧效率问题，可以进行实际检验以确定是否有效率问题。
# 3. EXISTS 子查询往往也可以使用条件表达式、其他子查询或者 JOIN 来代替，何种最优化需要具体分析。

复制代码

查看执行计划：案例一

# 创建表
create table tblA(
#id int primary key not null auto_increment,
age int,
birth timestamp not null
);
# 插入测试数据
insert into tblA(age, birth) values(22, now());
insert into tblA(age, birth) values(23, now());
insert into tblA(age, birth) values(24, now());
# 创建索引
create index idx_A_ageBirth on tblA(age, birth);# 创建复合索引age和birth

复制代码

查看执行计划：案例二

EXPLAIN SELECT * FROM tblA WHERE age > 20 ORDER BY age;
EXPLAIN SELECT * FROM tblA WHERE age > 20 ORDER BY age,birth;
EXPLAIN SELECT * FROM tblA WHERE age > 20 ORDER BY birth;
EXPLAIN SELECT * FROM tblA WHERE age > 20 ORDER BY birth,age;
EXPLAIN SELECT * FROM tblA WHERE birth > '2021-02-19 22:45:00' ORDER BY birth;
EXPLAIN SELECT * FROM tblA WHERE birth > '2021-02-19 22:45:00' ORDER BY age;

复制代码

最后得出结论：

MySQL支持两种方式的排序，index和filesort。index效率高，它是指扫描索引本身完成排序，filesort效率低。
ORDER BY子句，尽量使用Index方式排序，避免filesort方式排序。
ORDER BY子句满足两种情况，会使用index排序，一是ORDER BY子句采用遵照最佳左前缀法则，二是where条件字段和ORDER BY子句组合起来，满足最佳左前缀法则排序分组优化

3.1.2.2 MySQL的排序算法

当发生 Using filesort 时，MySQL会根据自己的算法对查询结果进行排序。有两种排序算法：双路排序和单路排序。
3.1.2.2.1 双路排序

MySQL 4.1 之前是使用双路排序,字面意思就是两次扫描磁盘，最终得到数据，读取行指针和 order by 列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出.
从磁盘取排序字段，在 buffer 进行排序，再从磁盘取其他字段。
简单来说，取一批数据，要对磁盘进行了两次扫描，众所周知，I\O 是很耗时的，所以在 mysql4.1 之后，出现了第二种改进的算法，就是单路排序。
3.1.2.2.2 单路排序

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

单路排序存在的问题：在 sort_buffer 中，方法 B（单路排序）比方法 A（双路排序）要多占用很多空间，因为方法 B 是把所有字段都取出, 所以有可能取出的数据的总大小超出了 sort_buffer 的容量，导致每次只能取 sort_buffer 容量大小的数据，进行排序（创建 tmp 文件，多路合并），排完再取取 sort_buffer 容量大小，再排……从而多次 I/O。也就是本来想省一次 I/O 操作，反而导致了大量的 I/O 操作，反而得不偿失。
3.1.2.3 优化策略

增大sort_buffer_size参数的设置
不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的 1M-8M 之间调整。
增大max_length_for_sort_data参数的设置
mysql 使用单路排序的前提是排序的字段大小要小于 max_length_for_sort_data，提高这个参数，会增加使用改进算法的概率。
但是如果设的太高，数据总容量超出 sort_buffer_size 的概率反而会增大，就会出现高频磁盘 I/O 和低的处理器使用率。（1024-8192 之间调整）
*减少 select 后面的查询的字段（少用select ）
查询的字段减少，缓冲就能容纳更多的内容，也就相当于间接增大了sort_buffer_size。
当Query的字段大小总和小于max_length_for_sort_data而且排序字段不是TEXT|IBLOB类型时，会用改进后的算法一—单路排序，否则用老算法——多路排序。
两种算法的数据都有可能超出sort_buffer的容量，超出之后，会创建tmp文件进行合并排序，导致多次O，但是用单路排序算法的风险会更大一些,所以要提高sort_buffer_size。

3.1.2.4 总结

EXPLAIN SELECT * FROM tblA ORDER BY age ASC,birth DESC; #排序不一致，要么都是升序或者都是降序

复制代码

3.1.3 group by关键字优化

group by优化和order by优化大致相似。
group by实质是先排序后进行分组，遵照索引建的最佳左前缀。
当无法使用索引列，增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置。
where高于having，能写在where限定的条件就不要去having限定了。
要想在排序时使用索引，避免 Using filesort，可以采用索引覆盖。
ORDER BY /GROUP BY后面字段的顺序要和复合索引的顺序完全一致。
ORDER BY /GROUP BY后面的索引必须按照顺序出现，排在后面的可以不出现。
要进行升序或者降序时，字段的排序顺序必须一致。不能一部分升序，一部分降序，可以都升序或者都降序。
如果复合索引前面的字段作为常量出现在过滤条件中，排序字段可以为紧跟其后的字段。
3.2 慢查询日志

3.2.1 是什么

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

3.2.2 使用

默认情况下，MySQL 数据库没有开启慢查询日志，需要我们手动来设置这个参数。
如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会将SQL语句写入日志，因此或多或少带来一定的性能影响。
SQL语句说明SHOW VARIABLES LIKE ‘%slow_query_log%’;查询慢查询日志是否开启，默认为OFF表示未开启SET GLOBAL slow_query_log=1;开启慢查询日志SET GLOBAL slow_query_log=0;关闭慢查询日志SHOW VARIABLES LIKE ‘long_query_time%’;查询慢查询设定阈值，默认为10秒SET long_query_time=5;设定慢查询阈值为5秒，可以设置为几秒，单位为秒

注意：

et global slow_query_log=1开启慢查询日志，仅对当前数据库生效，MySQL重启后失效。
如果需要永久生效，则需要修改my.ini配置文件（Windows系统下的文件）或/etc/my.cnf配置文件（Linux系统下的文件）。
下面以Linux系统配置永久慢查询日志为例：

为排序使用索引：
（1）MySQL两种排序方式：文件排序或扫描有序索引排序
（2）MySQL能为排序与查询使用相同的索引。（因为索引有两个作用：排序和查找）
例如：key a_b_c(a,b,c) #为a,b,c三个字段创建复合索引
## 第一种情况：ORDER BY能使用索引最左前缀
- ORDER BY a
- ORDER BY a,b
- ORDER BY a,b,c #相当于ORDER BY a ASC,b ASC,c ASC
- ORDER BY a DESC,b DESC,c DESC
## 第二种情况：如果WHERE使用索引的最左前缀定义为常量（如 WHERE name='张三'），则ORDER BY 能使用索引
- WHERE a=const ORDER BY b,c #也按照了索引顺序：a,b,c
- WHERE a=const AND b=const ORDER BY c #也按照了索引顺序：a,b,c
- WHERE a=const ORDER BY b,c #也按照了索引顺序：a,b,c
- WHERE a=const AND b>const ORDER BY b,c #虽然b>const会导致后面的索引失效，但前面的常量加上ORDER BY后面的字段也符合索引顺序：a,b,c
## 第三种情况：不能使用索引进行排序的情况
- ORDER BY a ASC,b DESC,c DESC #排序不一致，既存在升序，也存在降序
- WHERE g=const ORDER BY b,c #丢失a索引，不能使用索引排序
- WHERE a=const ORDER BY c #丢死b索引，也不能使用索引排序
- WHERE a=const ORDER BY a,d #d不是索引的一部分，也不能使用索引排序
- WHERE a in (...) ORDER BY b,c #对于排序来说，多个相等条件也是范围查询

复制代码

退出保存后执行service mysqld restart命令重启mysql服务，再次进入mysql中查看已经设置成功了

3.2.3 日志分析工具mysqldumpslow

生产环境中手工查找，分析日志，非常的耗费时间，因此MySQL提供了日志分析工具mysqldumpslow。
通过mysqldumpslow --help命令可以查看该工具的帮助：

帮助信息的各参数说明：

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

工作常用参考案例：

# 下面的配置语句是添加在[mysqld]标签下的
[mysqld]
# 配置慢查询日志
# 开启慢查询日志，1表示开启，0表示关闭，默认关闭
slow_query_log=1
# 设置日志路径，日志路径通常由在mysql下执行SHOW VARIABLES LIKE '%slow_query_log%';命令>得到的slow_query_log_file参数的值
slow_query_log_file=/var/lib/mysql/localhost-slow.log
# 设置慢查询阈值为5秒，当有查询SQL执行时间超过5秒就会被记录在慢查询日志中
long_query_time=5
log_output=FILE

复制代码

3.3 批处理数据脚本

批处理数据脚本是为了插入百万、千万条数据的脚本。
第一步：创建测试表：

-- 获取返回集最多的10条SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/localhost-slow.log
-- 获取访问次数最多的10条SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/localhost-slow.log
-- 获取按时间排序的前10条含有LEFT JOIN的SQL语句
mysqldumpslow -s t -t 10 -g "LEFT JOIN" /var/lib/mysql/localhost-slow.log
-- 结合|more使用，否则有可能会爆屏
mysqldumpslow -s r -t 10 /var/lib/mysql/localhost-slow.log |more

复制代码

第二步：创建函数，保证每条数据都不相同，产生随机字符串和随机部门编号：

create database big_data;
use big_data;
# dept
create table dept(
id int primary key auto_increment,
deptno mediumint not null default 0,
dname varchar(20) not null default '',
loc varchar(13) not null default ''
) engine = innodb default charset = utf8;
# emp
create table emp(
id int primary key auto_increment,
empno mediumint not null default 0,
ename varchar(20) not null default '',
job varchar(9) not null default '' comment '工作',
mgr mediumint not null default 0 comment '上级编号',
hirdate date not null comment '入职时间',
sal decimal(18,2) not null comment '薪水',
comm decimal(18,2) not null comment '红利',
deptno mediumint not null default 0 comment '部门编号'
) engine = innodb default charset = utf8;

复制代码

第三步：创建存储过程

#随机字符串函数
delimiter $$
create function rand_str(n int) returns varchar(255)
begin
declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
declare return_str varchar(100) 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 ;
#删除函数
#drop function if exists rand_str;
#使用函数
select rand_str(5);
#用于随机产生多少到多少的编号
DELIMITER $$
CREATE FUNCTION rand_num (from_num INT ,to_num INT) RETURNS INT(11)
BEGIN
DECLARE i INT DEFAULT 0;
SET i = FLOOR(from_num +RAND()*(to_num -from_num+1)) ;
RETURN i;
END$$
DELIMITER ;
#删除函数
#drop function if exists rand_num;
#使用函数
select rand_num(100,100000);

复制代码

第四步：调用存储过程批量插入数据

-- 为dept创建存储过程
delimiter $$
create procedure insert_dept(in start int(10), in max_num int(10))
begin
declare i int default 0;
set autocommit = 0;#设置不让一次插入后就自动提交，批量插入的话如果每次都自动提交那么会连接无数次，耗费性能
repeat
set i = i+1;
insert into dept(deptno, dname, loc) values ((start+i), rand_num() , rand_str(6));
until i = max_num
end repeat;# 结束循环
commit;# 最后批量插入后再插入
end $$
delimiter ;
-- 为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, hirdate, sal, comm, deptno) values ((start+i), rand_str(6), 'SALESMAN', 001, curdate(), 2000, 400, rand_num(100,50000));
until i = max_num
end repeat;
commit;
end $$
delimiter ;

复制代码

3.4 Show Profile

3.4.1 是什么

Show Profiles是MySQL提供，可以分析SQL语句执行的资源消耗情况，可用于SQL调优。
通过配置profiling参数启用SQL剖析，该参数可以在全局和session级别来设置。
全局级别作用于整个MySQL实例，而session级别只影响当前回话。
该参数开启后，后续执行的SQL语句都将记录其资源开销，诸如IO，上下文切换，CPU，Memory等
Show profiles是5.0.37之后添加的，要想使用此功能，要确保MySQL版本 > 5.0.37。
官网：https://dev.mysql.com/doc/refman/8.0/en/show-profile.html
3.4.2 使用

默认情况下，参数处于关闭状态，并保存最近15次的运行结果。
第一步：查看是否支持，即查看当前的mysql版本是否支持。
执行show variables like 'profiling'命令查询是否启用，为OFF表示未启用

第二步：由于默认是关闭的，使用前需要开启。
执行set profiling=on命令开启profile。

第三步：运行SQL

# 添加数据到部门表
#执行存储过程，往 dept 表添加 1 万条数据
CALL insert_dept(0,10000);
# 添加数据到员工表
CALL insert_emp(10,50000);

复制代码

第四步：执行show profiles;

第五步：诊断SQL

# 随便写条查询SQL查看效果
SELECT id%10 from emp GROUP BY id%10 LIMIT 10000;

复制代码

第六步：日常开放需要注意的事项

①converting HEAP to MyISAM：出现了这个表示查询结果太大，内存都不够用了，往磁盘上搬了。
②Create tmp table：出现这个表示创建了临时表。
③Copying to tmp table on disk：把内存中的临时表复制到磁盘，需要优化了。
④locked：表示锁。

出现上面这四种情况，就需要考虑优化该条SQL语句了。
3.5 全局查询日志

注意：永远不要在生产环境中开启此功能。
注意：永远不要在生产环境中开启此功能。
注意：永远不要在生产环境中开启此功能。
只能在测试环境中使用，一旦开启，会记录每条查询SQL。有如下两种启用方式：
3.5.1 配置文件来启用

在mysql的 /etc/my.cnf 配置文件中的 [mysqld] 标签下添加如下内容：

# 语法
show profile [type] for query 上一步前面问题的SQL数字号码;
# type参数说明
# all 表示显示全部开销信息
# block io 表示显示块IO相关开销
# contexxtswitchaes 表示显示上下文切换相关开销
# cpu 表示显示CPU相关的开销信息
# ipc 表示显示发生和接收相关的信息
# memory 表示显示内存相关的信息
# page faults 表示显示页面错误相关的开销信息
# source 表示显示和source_function,source_file,source_line相关的开销信息
# swaps 表示显示交换次数相关的开销的信息
# 示例
show profile cpu,block io for query 35;

复制代码

3.5.2 编码来启用

在mysql环境下执行如下语句来启用全局查询日志：

# 开启，默认是关闭的
general_log=1
# 记录日志文件的路径
general_log_file=/var/lib/mysql/localhost.log
# 输出格式
log_output=FILE

复制代码

第4章 MySQL的锁机制

4.1 概述

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

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

生活中的案例：比如在淘宝上买一件商品，商品库存只有一件，这个时候，如果有其他的卖家，那么如何解决是你买到还是别人买到的问题。

这里肯定要用到事务，我们先从库存中取出物品的数量，然后插入订单。付款后插入付款信息。然后更新商品数量，这个过程使用锁,可以对有限的资源进行保护，解决隔离和并发的矛盾。
从对数据的操作类型分为：读锁（共享锁）和写锁（排他锁）
读锁：针对同一份数据，对该数据的读操作可以同时进行且不受影响。
写锁：写操作未完成前，会阻断其他的读操作和写操作。
从对数据的操作粒度分为：表锁和行锁
4.2 三锁

在数据库中有三种锁：表锁（偏读）、行锁（偏写）和页锁。
4.2.1 表锁（偏读）

4.2.1.1 特点

表锁的特点： MylSAM引擎使用表锁，开销小，加锁快，无死锁，锁定力度大，发生锁冲突的概率最高。并发度最低。不支持事务
4.2.1.2 案例分析

建表SQL

插入测试要用到的表：

set global general_log=1; #开启配置文件
set global log_output='TABLE'; #设置后，所以执行的SQL语句，都会被记录在mysql库的general_log表中，可以用下面的语句查看
select * from mysql.general_log;

复制代码

加读锁示例

需要开启两个会话来测试加读锁后的情况。
加读锁的语法如下：

use big_data;
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');
select * from mylock;

复制代码

①打开两个会话session-1和session-2，然后在session-1会话中为mylock表添加读锁。

②在两个会话中都能读取当前已经添加了读锁的mylock表。

③在session-1会话（已经加了读锁）中不能查询没有加锁的表，而其他会话如session-2能够查询或更新其他表。

④当前session-1（已经加了读锁）会话中插入或更新锁定了的表都会提示错误，其他会话中插入或更新锁定了的话会进入阻塞状态一直等待获得解锁。

⑤当前session-1（已经加了读锁）会话中释放锁，那么其他会话中陷入阻塞状态的操作也会继续完成。

加写锁示例

①为session-1会话中的mylock表添加写锁。

②在session-1会话（已经加了写锁）中查询其他表失败，而其他会话能够查询其他表成功。

③在session-1会话（已经加了写锁）可以更新和插入数据，而其他会话不能插入数据。

④在session-1会话（已经加了写锁）查询mylock表，而其他会话中查询mylock表会陷入阻塞状态。

⑤当前session-1（已经加了写锁）会话中释放锁，那么其他会话中陷入阻塞状态的操作也会继续完成。

4.2.1.3 案例结论

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

表共享读锁(Table Read Lock)
表独占写锁(Table Write Lock)

锁类型可否兼容读锁写锁读锁是是否写锁是否否结合上表，所以对 MylSAM表进行操作，会有一下的情况:

1.对MyISAM表的读操作（加读锁)，不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其他进行的写操作。
2.对MylSAM操作的写锁(加写锁)，会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其他进程的读写操作。

结论：简而言之、就是读锁会阻塞写，但是不会阻塞读，而写锁则会把读和写都阻塞
4.2.1.4 表锁分析

查询被锁定的表的语法：

# 加锁语法
lock table 表名 read|write; # read表示为该表添加读锁；write表示为该表添加写锁
# 释放锁语法
unlock tables;

复制代码

如果要分析表锁，可以通过下面的方法来对表锁进行分析：
通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定。table_locks_waited变量和table_locks_immediate变量可以通过下面的SQL语句来进行查看：

# 语法
show open tables;

复制代码

在查询出来的结果集中需要关注Table_locks_immediate变量和Table_locks_waited变量，它们的说明如下：

Table_locks_immediate:产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁值加1。
Table_locks_waited:出现表级锁定争用而发生等待的次数(不能立即获取锁的次数，每等待一次锁值加1)，此值高则说明存在着较严重的表级锁争用情况。

此外，Myisam的读写锁调度是写优先，这也是myisam不适合做写为主表的引擎。因为写锁后，其他线程不能做任何操作大量的更新会使查询很难得到锁，从而造成永远阻塞。
4.2.2 行锁（偏写）

4.2.2.1 特点

偏向InnoDB存储引擎，开销大，加锁慢;会出现死锁;锁定粒度最小，发生锁冲突的概率最低,并发度也最高。
InnoDB与MyISAM的最大不同有两点：一是支持事务（TRANSACTION)；二是采用了行级锁。
4.2.2.2 行锁支持事务

所以可以复习下事务的知识：

事务及其ACID特性
并发事务带来的问题
事务隔离级别

4.2.2.3 案例分析

建表SQL

插入测试要用到的表：

show status like 'table%'; #查看表锁信息

复制代码

行锁示例

①开启事务，即关闭自动提交。

②在session-1会话中更新但不提交事务，即没有手写commit;语句执行，其他会话如session-2会陷入阻塞，只能等待。

③在session-1会话中提交事务，而其他会话中堵塞的操作解除，继续执行。

④如果对不同的记录进行修改，那么也就不会堵塞

无索引行锁升级为表锁

索引失效，行锁变表锁（通过varchar类型不加单引号让索引失效）。
当索引失效后，即使多个客户端操作的不是同一条记录，如果未提交，其他客户端也会进入阻塞状态，所以要避免索引失效。
为甚么索引失效行锁会变表锁：InnoDB 行级锁是通过给索引上的索引项加锁来实现的，InnoDB行级锁只有通过索引条件检索数据，才使用行级锁。否则，InnoDB使用表锁，在不通过索引(主键)条件查询的时候，InnoDB是表锁而不是行锁。

间隙锁危害

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

比如一共有1、2、4、5、6、7、8、9共八条记录，表中没有3号记录，但是在使用范围条件（id>=2 and id

		自动登录	找回密码
密码			立即注册

MySQL高级学习笔记

本帖子中包含更多资源

0 个回复

快速回复

楼主热帖

标签云