mysql高水位原因 mysql有高水位吗

文章目录

• 一、MySQL简介

• 1. 概述
• 2. 高级MySQL

• 二、MySQL 安装配置（CentOS 7 yum）
• 三、MySQL的架构介绍

• 1. MySQL的目录结构
• 2. 修改字符集
• 3. 配置文件
• 4. 逻辑架构介绍
• 5. 存储引擎

• 四、索引优化分析

• 1. 为什么优化sql
• 2. 常见通用的Join查询

• sql执行顺序
• Join图

• 3. 索引介绍

• 索引是什么
• 索引优势、劣势
• MySQL索引的分类
• MySQL索引结构
• 创建索引的时机

• 4. 性能分析（重点）

• MySQL Query Optimizer
• MySQL的常见瓶颈
• Explain

• 概述
• 各个字段的解释

• 索引优化

• 索引分析
• 索引失效（应该避免）
• 一般性建议

• 面试题讲解

• 五、查询截取分析

• 1. 查询优化（深入优化）

• 永远小表驱动大表
• order by 关键字优化
• group by 优化

• 2. 慢查询日志

• 概念
• 怎么使用
• 日志分析工具mysqldumpslow

• 3. 批量数据脚本
• 4. Show Profile

• 分析步骤

• 5. 全局查询日志

• 六、MySQL锁机制

• 1. 概述
• 2. 锁的分类
• 3. 三锁

• 表锁（偏读）
• 行锁（偏写）
• 页锁（了解）

• 七、主从复制

• 1. 复制的基本原理
• 2. 复制的基本原则
• 3. 复制的最大问题
• 4. 一主一从常见配置

一、MySQL简介

1. 概述

MySQL是一个关系型数据库管理系统，由瑞典MySQL AB公司开发，目前属于Oracle公司。

MySQL是一种关联数据库管理系统，将数据保存在不同的表中，而不是将所有数据放在一个大仓库内，这样就增加了速度并提高了灵活性。

Mysql是开源的，所以你不需要支付额外的费用。

Mysql是可以定制的，采用了GPL协议，你可以修改源码来开发自己的Mysql系统。

Mysql支持大型的数据库。可以处理拥有上千万条记录的大型数据库。

MySQL使用标准的SQL数据语言形式。Mysql可以允许于多个系统上，并且支持多种语言。这些编程语言包括C、C++、Python、Java、Perl、PHP、Eiffel、Ruby和Tcl等。MySQL支持大型数据库，支持5000万条记录的数据仓库，32位系统表文件最大可支持4GB，64位系统支持最大的表文件为8TB。

2. 高级MySQL

• MySQL内核
• SQL优化工程师
• MySQL服务器的优化
• 各种参数常量的设定
• 查询语句优化
• 主从复制
• 软硬件升级
• 容灾备份
• SQL编程

二、MySQL 安装配置（CentOS 7 yum）

安装过程中可能遇到的问题：

1. 初始化报错，error while loading shared libraries: libaio.so.1
   解决：执行命令下载依赖，yum install libaio-devel.x86_64
2. centos7执行 wget命令: command not found
   解决：yum -y install wget

常用命令
启动服务

systemctl start mysqld.service

关闭服务

systemctl stop mysqld.service

登录客户端

mysql -u root -p

退出客户端

exit

查看MySQL进程

ps -ef|grep mysql

查看Linux系统运行状况，详情可自行查询

top

三、MySQL的架构介绍

1. MySQL的目录结构

默认情况下MySQL的目录主要在四大文件目录中

2. 修改字符集

登录客户端

查看字符集

show variables like '%char%';

如果 server 和 client 字符集是 Latin，会导致显示中文乱码，需要修改配置，修改配置需要重启数据库服务；

修改字符集之后，再创建的数据库才会生效，所以一开始安装MySQL时就要将字符集设置好

这里由于我们在上面安装的过程中，已经修改了配置文件，所以显示为utf8字符集

3. 配置文件

默认条件下，Windows环境配置文件叫my.ini，Linux环境下配置文件叫/etc/my.cnf

配置的内容主要包含：

• 二进制日志login-bin：用于主从复制
• 错误日志log-err：默认是关闭的，用于记录严重的警告和错误信息，每次启动和关闭的详细信息等
• 查询日志log：默认关闭，记录查询的sql语句，如果开启会降低MySQL整体性能，因为记录日志也需要消耗系统资源
• 数据文件：windows 在data目录下，Linux在/var/lib/mysql 目录下，一个目录代表一个库

• frm文件：存放表结构
• myd文件：存放表数据
• myi文件：存放查找数据所需的索引

4. 逻辑架构介绍

概述：

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

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

• Management Serveices & Utilities： 系统管理和控制工具
• SQL Interface: SQL接口 接受用户的SQL命令，并且返回用户需要查询的结果。比如select from就是调用SQL Interface
• Parser: 解析器，SQL命令传递到解析器的时候会被解析器验证和解析。
• Optimizer: 查询优化器，SQL语句在查询之前会使用查询优化器对查询进行优化。
• Cache和Buffer： 查询缓存。如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。
  这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等
  缓存是负责读，缓冲负责写。

3. 引擎层：Pluggable Storage Engines ，可拔插组件式存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。主要介绍MyISAM和InnoDB
4. 存储层：主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。

5. 存储引擎

sql命令查看当前使用的存储引擎，两种命令

show engines;
show variables like '%storage_engine%';

MyISAM和InnoDB对比

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

补充知识：

Percona 为 MySQL 数据库服务器进行了改进，在功能和性能上较 MySQL 有着很显著的提升。该版本提升了在高负载情况下的 InnoDB 的性能、为 DBA 提供一些非常有用的性能诊断工具；另外有更多的参数和命令来控制服务器行为。

该公司新建了一款存储引擎叫xtradb完全可以替代innodb，且在性能和并发上做得更好

阿里巴巴大部分mysql数据库其实使用的percona的原型加以修改。AliSql+AliRedis

四、索引优化分析

1. 为什么优化sql

并不一开始就要优化sql，生产环境下需要复现出现的性能问题，且运行一段时间筛查定位，确认是sql问题，才开始优化

性能下降sql慢，执行时间长，等待时间长

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

2. 常见通用的Join查询

sql执行顺序

手写：

select distinct 字段
from 表
left|right|inner join 表
on 关联条件
where 筛选条件
group by 分组
having 分组后筛选
order by 排序
limit 分页参数

机读：

from 表
on 关联条件
left|right|inner join 表
where 筛选条件
group by 分组
having 分组后筛选
select
distinct 去重
order by 排序
limit 分页参数

Join图

3. 索引介绍

索引是什么

直接回答索引像一本书的目录，通过目录找到内容，这样显得不专业，很low

MySQL官方对索引的定义为：

• 索引（Index）是帮助MySQL高效获取数据的数据结构。

索引的本质：

• 索引是数据结构。

理解：

• 索引的目的在于提高查询效率，可以类比字典，如果要查“mysql”这个单词，我们肯定需要定位到m字母，然后从下往下找到y字母，再找到剩下的sql。如果没有索引，那么你可能需要a----z，如果我想找到Java开头的单词呢？或者Oracle开头的单词呢？是不是觉得如果没有索引，这个事情根本无法完成？

你可以简单理解为“排好序的快速查找数据结构”。

• 索引会影响 where 后面的查找和 order by 后面的排序

详解

• 在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。
• 下图就是一种可能的索引方式示例：左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。

二叉树、B TREE：将查询的数据与节点比较，小的往左边走，大的往右边走，到下一级继续比较查找，直至找到对应的值

二叉树弊端之一：二叉树很可能会发生两边不平衡的情况。B-TREE: (B:balance) 会自动根据两边的情况自动调节，使两端无限趋近于平衡状态。可以使性能最稳定。(myisam使用的方式) B-TREE弊端：(插入/修改操作多时，B-TREE会不断调整平衡，消耗性能)从侧面说明了索引不是越多越好。B+TREE:Innodb 所使用的索引

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

我们平常所说的索引，如果没有特别指明，都是指B树(多路搜索树，并不一定是二叉的，比如三叉…)结构组织的索引。

其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种类型的索引之外，还有哈稀索引(hash index)等。

索引优势、劣势

优势：

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

劣势：

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

MySQL索引的分类

单值索引：

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

唯一索引：

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

复合索引：

• 一个索引包含多个列

基本语法：

创建：

create [unique] index indexName on mytable(columnname(length))
ALTER mytable ADD  [UNIQUE ]  INDEX [indexName] ON (columnname(length))

两种创建方法，unique表示唯一索引，看需求添加；columnname(length)有一个就是单值索引，多个就是符合索引；indexname是索引名字，mytable是表名

删除

DROP INDEX [indexName] ON mytable;

查看

SHOW INDEX FROM table_name\G

使用ALTER有四种方式来添加数据表的索引：

• ALTER TABLE tbl_name ADD PRIMARY KEY (column_list)
  该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL
• ALTER TABLE tbl_name ADD UNIQUE index_name (column_list)
  这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
• ALTER TABLE tbl_name ADD INDEX index_name (column_list)
  添加普通索引，索引值可出现多次。
• ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list)
  该语句指定了索引为 FULLTEXT ，用于全文索引。

MySQL索引结构

BTree索引、Hash索引、full-text全文索引、R-Tree索引；我们重点探讨BTree索引

BTree索引

索引的高度越高，查找的层次越多，IO消耗越多，所以我们应该限制索引的高度，增加索引的广度

【初始化介绍】

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

【查找过程】

如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。

真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

创建索引的时机

哪些情况需要建索引：

• 主键自动建立唯一索引
• 频繁作为查询条件的字段应该创建索引
• 查询中与其他表关联的字段，外键关系建立索引
• 频繁更新的字段不适合创建索引
  因为每次更新不只是更新了记录，还会更新索引
• where 条件里用不到的字段不创建索引
• 单值索引还是复合索引？高并发下更倾向创建复合索引
• 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
• 查询中统计或者分组的字段
  group by是分组，实际上分组前必先排序，所以group by 与索引息息相关

哪些情况不需要创建索引：

• 表记录太少
  官方说MySQL 500-800万数据时性能开始下降，实际经验上300万开始下降，这时候就需要优化
• 经常增删改的表
  提高了查询速度，却降低了更新速度，insert/update/delete，MySQL更新表时，不仅要保存数据，还要保存一下索引文件
• 数据重复且分布平均的表字段；
  因此，应该值为最经常查询和最经常排序的数据列简历索引。如果某个数据列包含许多重复内容，为他创建索引就没多大实际效果

4. 性能分析（重点）

MySQL Query Optimizer

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

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

MySQL的常见瓶颈

CPU：

• CPU在饱和的时候，一般发生在数据装入内存或从磁盘上读取数据的时候。SQL中对大量数据进行比较、关联、排序、分组，
• 最大的压力在于 比较

IO：

• 磁盘IO瓶颈发生在装入数据远大于内存容量的时候
• 实例内存满足不了缓存数据或排序等需要，导致产生大量 物理 IO。
• 查询执行效率低，扫描过多数据行

服务器硬件的性能瓶颈：

• 可以通过top,free,iostat和vmstat来查看系统的性能状态

锁：

• 不适宜的锁的设置，导致线程阻塞，性能下降。
• 死锁，线程之间交叉调用资源，导致死锁，程序卡住。

Explain

概述

是什么

查询执行计划

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是

如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈

官网介绍：https://dev.mysql.com/doc/refman/8.0/en/explain-output.html

能干什么

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

怎么用

• Explain + SQL语句
• 执行计划包含的信息

各个字段的解释

id

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

如果id相同，则执行/加载顺序由上至下

三个表都是1，那么执行顺序按照自上而下执行，t1,t3,t2

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

查询顺序为 t3,t1,t2，结合我们的理解，查询是按照从里到外的顺序进行的

id同时有相同和不同的，先执行数值大的，相同的值可以看做一组，组内按照从上到下

执行顺序为 t3,derived2,t2，derived2是id=2的查询衍生出的虚表s1，

select_type

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

SIMPLE：

• 简单的 select 查询，查询中不包含子查询或者UNION

PRIMARY：

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

DERIVED：

• 在FROM列表中包含的子查询被标记为DERIVED(衍生)，MySQL会递归执行这些子查询, 把结果放在临时表里。
  DERIVED 既查询通过子查询查出来的 临时表

SUBQUERY：

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

DEPENDENT SUBQUERY：

• 在SELECT或WHERE列表中包含了子查询，子查询基于外层
• dependent subquery 与 subquery 的区别依赖子查询 ： 子查询结果为 多值子查询：查询结果为 单值

UNCACHEABLE SUBQUREY：

• 无法被缓存的子查询

UNION：

• 若第二个SELECT出现在UNION之后，则被标记为UNION；
  若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED
  UNION RESULT 两个语句执行完后的结果

UNION RESULT：

• 从UNION表获取结果的SELECT

table

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

type

显示查询使用了何种类型的索引，

从最好到最差依次是：system > const > eq_ref > ref > range > index >ALL

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

system：系统

• 表只有一行记录（等于系统表），这是const类型的特列，平时不会出现，这个也可以忽略不计
  实际工作中几乎见不到，只有一行记录。。。

const：常量

• 表示通过索引一次就找到了，const用于比较primary key或者unique索引，也就是主键和唯一约束。因为只匹配一行数据，所以很快

如将主键置于where列表中，MySQL就能将该查询转换为一个常量

eq_ref：唯一索引扫描

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

比如，部门表每个部门都有id，部门里有员工，总裁办部门只有一个CEO，给部门id加索引；
当我们查询总裁办的员工时，只有一个记录CEO。

ref：非唯一索引扫描

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

比如，我们根据研发部id查询查询Java开发工程师，结果会有很多行

range：范围

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

index：全索引扫描

• Full Index Scan，index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。
  （也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）

all：

• Full Table Scan，将遍历全表以找到匹配的行

possible_keys

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

key

• 实际使用的索引。如果为NULL，则没有使用索引
• 查询中若使用了覆盖索引，则该索引只出现在key列表中，也就是possible_keys没有，key有
• 覆盖索引：select查询的字段和创建的符合索引各个字段个数和顺序一一对应，这时候查询字段就和索引刚好匹配，所以会出现理论上为null，实际上为index

索引没有使用到，就是索引失效

比如，可能有两个，实际只有一个

理论上没有用索引，实际上没有用索引

理论上为null，实际上是index

key_len

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

精确性和key_len是相互矛盾的，越精确就越需要更多的索引定位，所以，我们要平衡利弊，尽量提高精确，降低使用的索引长度

可以看得见，同样的查询结果，第一个查询用的条件少，key_len小，第二个条件多，key_len大；但是结果一样，所以第一个查询更优

ref

• 显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值
• 这里的ref是字段/列的ref，不是type中的ref

这句话的含义：

type字段为使用的索引类型，当type字段为const、eq_ref、ref、ref or null、unique_subquery、index_subquery 的其中之一时，ref列展示的就是与索引列做等值匹配的条件，比如常数或某个列

上图，t2表没用索引，忽略掉；shared是库名，不要被误导

t1表的type为ref（非唯一索引扫描），因为where条件中有t1表的col1、col2两个字段，所以 idx_col1_col2 这个索引被使用，

其中，col1字段来源于t2.col1，col2字段来源于常量’ac’，所以ref列中显示的就是 shared.t2.col1和const

总结就是：如果type列显示使用了哪种索引，那么ref列就会显示这个被使用的索引锁匹配了哪个值

rows

• 根据表统计信息及索引选用情况，大致估计算出找到所需的记录所需要的读取的行数
• rows列显示MySQL认为它执行查询时必须检查的行数。
• 也就是每张表有多少行被优化器查询，数值越小越好

第一次查询时，没有创建索引；t2表全表搜索，t1表使用了主键索引，共计检索641行

第二次查询，由于创建了idx_col1_col2复合索引，t2表使用了复合索引，t1表使用了主键索引，共计检索199行

Extra

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

Using filesort、Using temporary、Using index 是重点

Using filesort :

• 说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。
• MySQL中无法利用索引完成的排序操作称为“文件排序”

出现这种情况比较坑爹，有索引没用上，自己另起炉灶重新排序，耗费时间、资源；要尽可能避免这种情况

这里创建了一个复合索引

第一个查询：

使用的索引是 col1_col2_col3，但我们使用的条件和排序字段从左到右是col1 col3，与索引并不完全匹配，导致extra中出现了，using filesort，这样导致索引没有充分利用，使用了文件排序，效率低下

第二个查询：

使用的索引是col1_col2_col3，使用的条件和排序字段从左到右是 col1 col2 col3，与索引匹配，索引完全起作用，extr中提示 using index，查询效率高于第一个

Using temporary

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

这种现象更严重，条件和排序使用了临时表，更加消耗资源，拖慢速度

创建的复合索引为 cole_col2

第一个查询：

使用到的索引字段为 col2，与复合索引不匹配，所以出现了 using filesort，且产生了临时表，using temporary

第二个查询：

排序使用的索引为 col1 col2，与复合索引匹配，效率高

USING index

• 表示相应的select操作中使用了覆盖索引(Covering Index)，避免访问了表的数据行，效率不错！
• 如果同时出现using where，表明索引被用来执行索引键值的查找;
• 如果没有同时出现using where，表明索引只是用来读取数据而非利用索引执行查找。

覆盖索引(Covering Index)：

理解方式一：

就是select的数据列只用从索引中就能取得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件，换句话说，查询列要被所建的索引覆盖

理解方式二：

索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据；当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了或覆盖了满足查询结果的数据就叫做覆盖索引

注意：

如果要使用覆盖索引，一定要注意select列表中只取出需要的列，不可用select * 。因为将所有字段一起做索引会导致索引文件过大，查询性能下降。

Using where

表明使用了where过滤

using join buffer

使用了连接缓存

impossible where

where子句的值总是false，不能用来获取任何元素

select tables optimized away

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

distinct

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

案例分析

索引优化

索引分析

单表

建表sql

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;

案例

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

先实现功能，此时并没有创建索引

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也是最坏的情况，必须优化！

先创建个 category_id,comments,views的复合索引，

ALTER TABLE `article` ADD INDEX idx_article_ccv ( `category_id` , `comments`, `views` );
create index idx_article_ccv on article(category_id,comments,views);

再次查询分析

type问题解决了，但是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;

再次建索引，将comments剔除掉

ALTER TABLE `article` ADD INDEX idx_article_cv ( `category_id` , `views` ) ;
create index idx_article_cv on article(category_id,views);

查询分析

结论：可以看到，type 变为了 ref，Extra 中的 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` (
`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)));

可以理解为员工表和部门表

开始explain分析

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

结论：type 有All

添加索引优化，左表还是右表不知道，先建一个看看，

ALTER TABLE `book` ADD INDEX Y ( `card`);

第2次explain

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

可以看到第二行的 type 变为了 ref，rows 也变成了优化比较明显。这是由左连接特性决定的。LEFT JOIN 条件用于确定如何从右表搜索行,左边一定都有，所以右边是我们的关键点，一定需要建立索引。

如果把索引换个位置呢？将索引加载左连接的左表上

删除旧索引 + 新建 + 第3次explain

DROP INDEX Y ON book;
ALTER TABLE class ADD INDEX X (card);

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

虽然也是用索引，但是效率没有上面的好

结论：左连接加右表，右连接加左表

三表

建表，加上之前的两表，一共三表

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;

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

查询分析

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

因为连接较多，使用了 usring join buffer连接缓存；全部都是全表扫描

创建索引

ALTER TABLE `phone` ADD INDEX z(`card`);
ALTER TABLE `book` ADD INDEX Y(`card`);

查询分析

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

结论：join的优化

• 尽可能减少join语句中的嵌套循环的循环总次数，永远用小的结果集驱动大的结果集
• 优先优化嵌套循环的内层循环
• 保证join语句中被驱动表上的join条件字段已经被索引
• 当无法保证被驱动表的join条件字段被索引且内存资源充足的前提下，不要太吝啬joinbuffer的设置，大一些，效果更好

索引失效（应该避免）

建表

CREATE TABLE staffs (  
id INT PRIMARY KEY AUTO_INCREMENT,  
NAME VARCHAR (24)  NULL DEFAULT '' COMMENT '姓名',  
age INT NOT NULL DEFAULT 0 COMMENT '年龄',  
pos VARCHAR (20) NOT NULL DEFAULT '' COMMENT '职位',  
add_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间') CHARSET utf8 COMMENT '员工记录表' ;  

INSERT INTO staffs(NAME,age,pos,add_time) VALUES('z3',22,'manager',NOW());
INSERT INTO staffs(NAME,age,pos,add_time) VALUES('July',23,'dev',NOW());
INSERT INTO staffs(NAME,age,pos,add_time) VALUES('2000',23,'dev',NOW());
INSERT INTO staffs(NAME,age,pos,add_time) VALUES(null,23,'dev',NOW());

SELECT * FROM staffs; 

ALTER TABLE staffs ADD INDEX idx_staffs_nameAgePos(name, age, pos);

1. 全值匹配我最爱

索引 idx_staffs_nameAgePos 建立索引时 以 name ， age ，pos 的顺序建立的。

全值匹配表示 按顺序匹配的

EXPLAIN SELECT * FROM staffs WHERE NAME = 'July';
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25;
EXPLAIN SELECT * FROM staffs WHERE NAME = 'July' AND age = 25 AND pos = 'dev';

三个查询分析，精度越来越高，

如果我们改变sql语句中的条件的顺序或个数

发现索引失效

2. 最佳左前缀法则

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

比如说，我们创建的索引是 name ， age ，pos 的顺序，那么我们查找的使用的条件、排序也要按照 name ， age ，pos 的顺序

如果查询条件的哪一处位置与索引顺序不一致，之前的索引字段有效，之后的索引字段无效；

这是只有第一个字段的索引有效，后面的字段无效

这也是只有第一个字段索引生效，因为从第二个开始字段开始就断开了，不一致

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

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

第一句正常使用，索引生效

第二句，name 用到，age 用到，但是age > 25 是范围，所以age 之后（右侧）的索引失效

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

按需取字段，尽量和索引重合

虽然两句都是用到了索引，但是下边的多了using index，使用了覆盖索引，效果更好

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

7. is not null 也无法使用索引，但是 is null 是可以使用索引的

8. like以通配符开头(’%abc…’)mysql索引失效会变成全表扫描的操作

三个语句只有 like 后面的%不同，其他相同；左边加%会导致索引失效，加在右边不会

如何解决like ‘%字符串%’ 时的索引失效问题？

select 字段 的时候，直接将查询的字段满足覆盖索引，

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

10. 少用or，用它连接会导致索引失效

口诀：

• 全值匹配我最爱，最左前缀要遵守
• 带头大哥不能死，中间兄弟不能断
• 索引列上少计算，范围之后全失效
• like百分写最右，覆盖索引不写星
• 不等空值还有or，索引失效要少用
• 字符串外有引号，SQL高级也不难

思考：为什么索引要满足最佳左前缀匹配呢？

因为根据因为按照BTree索引工作原理，在进行条件筛选和排序的时候，是按照从左到右的顺序逐级查找的；

也就是，按照条件和排序字段，从左到右，先排序第一个字段，出现相同的字段，再排序第二个字段，如果遇到相同的字段，再排序第三个字段，以此类推；

而索引发挥作用也按照上面从左到右的顺序

所以当我们创建索引也遵循这样的顺序时，查找字段的先后顺序刚好与索引的结构顺序一致，索引可以发挥作用

小练习：

注意：

• like 对应的索引如果有效，且不是%开头，那么后面的索引还会继续有效，因为开头是常量，
• 如果 like 对应%开头，那么会导致后面的索引失效，且like本身对应的字段索引也失效；

一般性建议

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

面试题讲解

分析：

• 我们创建的索引是 1，2，3，4 为什么第二句、第三句也能匹配呢？
• 因为MySQL自带的查询优化器 Optimizer 会将命令自动调整优化导致的结果
• 虽然自带优化，但我们还是要尽可能遵循索引的规则写语句，防止自带优化器不起作用

分析：

• 使用了1，2，3索引，范围之后，4失效

分析：

• 索引1，2，3，4 都生效
• 虽然4在前，但是优化器自动生效，把3排在前面，4是范围，4本身有效，后面失效，但是4后面已经没有了

分析：

• 索引1，2 ，3 有效
• 3用于排序，而不是查找，也生效了，但是没有被统计；4没有生效

分析：

• 索引 1，2，3 有效
• 与上面完全一样，侧面说明上面的 4 没有生效

分析：

• 索引 1，2 有效
• 4没有生效，因为没有3，不连续

分析：

• 索引 1，2，3 有效
• 优化器自动将2，3提前，2，3用于排序，没有被统计

分析：

• 索引 1 有效
• 2，3 顺序不对，没有生效；
• 虽然看起来与上面的差不多，但是结尾出现了 using filesort，性能有很大差别

分析：

• 索引 1，2，3 都有效

分析：

• 索引 1，2，3 有效，与上面的一样
• 和5没啥关系

分析：

• 索引 1，2，3有效；和5没关系，直接忽略
• 虽然order by出现了3，2，但是由于前面1，2的存在，排到order by的时候，此时的2已经相当于常量，所以order by 3,2并没有违反顺序，3正常生效

分析：

• 索引 1，2，3有效；4直接忽略
• 没有索引失效

分析：

• 索引1有效
• 2，3，4都无效，效果很差

小结：

• 区分定值、范围、排序，一般order by 是给定范围
• group by 看起来了是分组，但是分组之前必先排序；会产生临时表
• 因此 order by 和 group by 排序和索引优化的套路的一致的

五、查询截取分析

分析：

• 慢查询的开启并捕获慢SQL
• explain+慢SQL分析
• show profile查询SQL在MySQL服务器里的执行细节和生命周期情况
• SQL数据库服务器的参数调优

1. 查询优化（深入优化）

永远小表驱动大表

类似嵌套循环

in 与 exists 使用效果相似，但是性能有所差异；具体看关键字两侧的大小关系

exists

select ... from table where exists(subquery)

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

提示：

• exists(subquery)只返回true或false，因此子查询中的select *也可以是select 1或其他，官方说法是实际执行时会忽略select清单，因此没有区别
• exists子查询的实际执行过程可能经历了优化，而不是我们理解上的逐条对比，如果担心效率问题，可以实际检验以确定是否有效率问题
• exists子查询往往也可以用条件表达式、其他子查询或join来替代，何种最优需要具体分析

order by 关键字优化

尽量使用Index方式排序，避免使用FileSort方式排序

建表

CREATE TABLE tblA(  
id int primary key not null auto_increment,  
age INT,  
birth TIMESTAMP NOT NULL,  
name varchar(200)); 

INSERT INTO tblA(age,birth,name) VALUES(22,NOW(),'abc');
INSERT INTO tblA(age,birth,name) VALUES(23,NOW(),'bcd');
INSERT INTO tblA(age,birth,name) VALUES(24,NOW(),'def'); 

CREATE INDEX idx_A_ageBirth ON tblA(age,birth,name); 

SELECT * FROM tblA;

我们的优化重点，就是不要出现filesort

分析：

• 正常使用索引，没有产生fielsort

MySQL支持两种方式的排序，filesort和index。index效率高，它指MySQL扫描索引本身完成排序；filesort方式效率低。

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

• order by 语句使用索引最左前列
• 使用 where 子句与 order by 子句条件列组合满足索引最左前列

尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀

如果不在索引列上，filesort有两种算法：

mysql就要启动双路排序和单路排序

双路排序

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

单路排序：

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

结论及引申出的问题：

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

优化策略：

• 增大sort_buffer_size参数的设置；用于单路排序的内存大小
• 增大max_length_for_sort_data参数的设置；单次排序字段大小。(单次排序请求)
• 去掉select 后面不需要的字段；select 后的多了，排序的时候也会带着一起，很占内存，所以去掉没有用的

提高Order By的速度：

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

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

• 尝试提高 sort_buffer_size不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的
• 尝试提高 max_length_for_sort_data提高这个参数， 会增加用改进算法的概率。但是如果设的太高，数据总容量超出sort_buffer_size的概率就增大，明显症状是高的磁盘I/O活动和低的处理器使用率.

Order By优化小结

group by 优化

几乎和order by 一致，但也有区别

优化策略：

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

2. 慢查询日志

概念

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

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

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

怎么使用

说明：

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

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

查看是否开启及如何开启

默认

SHOW VARIABLES LIKE '%slow_query_log%';

默认情况下slow_query_log的值为OFF，表示慢查询日志是禁用的，可以通过设置slow_query_log的值来开启SHOW VARIABLES LIKE ‘%slow_query_log%’;

开启

set global slow_query_log=1;

使用set global slow_query_log=1开启了慢查询日志只对当前数据库生效，如果MySQL重启后则会失效。

全局变量设置，对当前连接不影响，对当前连接立刻生效；

如果要永久生效，就必须修改配置文件my.cnf（其它系统变量也是如此）

修改my.cnf文件，[mysqld]下增加或修改参数slow_query_log 和slow_query_log_file后，然后重启MySQL服务器。也即将如下两行配置进my.cnf文件

slow_query_log =1
slow_query_log_file=/var/lib/mysql/atguigu-slow.log

关于慢查询的参数slow_query_log_file ，它指定慢查询日志文件的存放路径，系统默认会给一个缺省的文件host_name-slow.log（如果没有指定参数slow_query_log_file的话）

那么开启了慢查询日志后，什么样的SQL才会记录到慢查询日志里面呢？

这个是由参数long_query_time控制，默认情况下long_query_time的值为10秒，命令：

SHOW VARIABLES LIKE 'long_query_time%';

可以使用命令修改，也可以在my.cnf参数里面修改。假如运行时间正好等于long_query_time的情况，并不会被记录下来。也就是说，在mysql源码里是判断大于long_query_time，而非大于等于。

case

查看多少秒算慢

SHOW VARIABLES LIKE 'long_query_time%';

设置慢的阈值时间

使用命令 set global long_query_time=1修改为阙值到1秒钟的就是慢sql 修改后发现long_query_time并没有改变。

为什么设置后看不出变化？

需要重新连接或新开一个会话才能看到修改值。

SHOW VARIABLES LIKE 'long_query_time%';

或者通过set session long_query_time=1来改变当前session变量;

记录慢SQL并后续分析； 实验一条慢sql跟踪日志信息

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

show global status like '%Slow_queries%';

配置版

【mysqld】下配置：

slow_query_log=1;
slow_query_log_file=/var/lib/mysql/atguigu-slow.log
long_query_time=3;
log_output=FILE

日志分析工具mysqldumpslow

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

查看mysqldumpslow的帮助信息

mysqldumpslow --help

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

工作常用参考

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

• SQLmysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log

得到访问次数最多的10个

• SQLmysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log

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

• mysqldumpslow -s t -t 10 -g “left join” /var/lib/mysql/atguigu-slow.log

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

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

3. 批量数据脚本

往表里插入1000W数据

渐变

# 新建库
create database bigData;
use bigData;  

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

#2 建表emp
CREATE TABLE emp  (  
id INT UNSIGNED PRIMARY KEY AUTO_INCREMENT,  
empno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0, /*编号*/  
ename VARCHAR(20) NOT NULL DEFAULT "", /*名字*/  
job VARCHAR(9) NOT NULL DEFAULT "",/*工作*/  
mgr MEDIUMINT UNSIGNED NOT NULL DEFAULT 0,/*上级编号*/  
hiredate DATE NOT NULL,/*入职时间*/  
sal DECIMAL(7,2) NOT NULL,/*薪水*/  
comm DECIMAL(7,2) NOT NULL,/*红利*/  
deptno MEDIUMINT UNSIGNED NOT NULL DEFAULT 0 /*部门编号*/  )ENGINE=INNODB DEFAULT CHARSET=UTF8 ;

设置参数log_bin_trust_function_creators

创建函数，假如报错：
This function has none of DETERMINISTIC…#

由于开启过慢查询日志，因为我们开启了 bin-log, 我们就必须为我们的function指定一个参数。

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

这样添加了参数以后，如果mysqld重启，上述参数又会消失，永久方法：
windows下my.ini[mysqld]加上

log_bin_trust_function_creators=1

linux下 /etc/my.cnf下my.cnf[mysqld]加上

log_bin_trust_function_creators=1

4. Show Profile

是什么：

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

官网：

• http://dev.mysql.com/doc/refman/5.5/en/show-profile.html

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

分析步骤

是否支持，看看当前的mysql版本是否支持

Show  variables like 'profiling';

开启功能，默认是关闭，使用前需要开启

show variables like 'profiling'; 
set profiling=1;

运行SQL

select * from emp group by id%10 limit 150000;
select * from emp group by id%20  order by 5

查看结果，

show profiles;

诊断SQL，show profile cpu,block io for query n (n为上一步前面的问题SQL数字号码);

show profile cpu,block io for query n;

最常用的就是CPU和IO信息，此外我们还可以修改参数查看其他信息

# show profile type for query queryID

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

如何从查询的信息分出问题呢？

出现以下信息，说明sql执行存在明显问题：

• converting HEAP to MyISAM：查询结果太大，内存都不够用了往磁盘上搬了。
• Creating tmp table：创建临时表。拷贝数据到临时表，用完再删除。
• Copying to tmp table on disk：把内存中临时表复制到磁盘，危险！！！
• locked

5. 全局查询日志

永远不要在生产环境开启这个功能，测试时使用

配置启用

在mysql的my.cnf中，设置如下：

#开启
general_log=1   
# 记录日志文件的路径
general_log_file=/path/logfile
#输出格式
log_output=FILE

编码启用

set global general_log=1; 
#全局日志可以存放到日志文件中，也可以存放到Mysql系统表中。存放到日志中性能更好一些，存储到表中
set global log_output='TABLE';    
#此后 ，你所编写的sql语句，将会记录到mysql库里的general_log表，可以用下面的命令查看 
select * from mysql.general_log;

相比之下，show profile 更好用一些

六、MySQL锁机制

1. 概述

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

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

打个比方，我们到淘宝上买一件商品，商品只有一件库存，这个时候如果还有另一个人买，那么如何解决是你买到还是另一个人买到的问题？ 这里肯定要用到事务，我们先从库存表中取出物品数量，然后插入订单，付款后插入付款表信息，然后更新商品数量。在这个过程中，使用锁可以对有限的资源进行保护，解决隔离和并发的矛盾。

2. 锁的分类

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

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

从对数据操作的粒度分

• 表锁
• 行锁

为了尽可能提高数据库的并发度，每次锁定的数据范围越小越好，理论上每次只锁定当前操作的数据的方案会得到最大的并发度，但是管理锁是很耗资源的事情（涉及获取，检查，释放锁等动作），因此数据库系统需要在高并发响应和系统性能两方面进行平衡，这样就产生了“锁粒度（Lock granularity）”的概念。一种提高共享资源并发发性的方式是让锁定对象更有选择性。尽量只锁定需要修改的部分数据，而不是所有的资源。更理想的方式是，只对会修改的数据片进行精确的锁定。任何时候，在给定的资源上，锁定的数据量越少，则系统的并发程度越高，只要相互之间不发生冲突即可。

3. 三锁

表锁（偏读）

特点：

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

案例分析

建表

【表级锁分析--建表SQL】 
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'); 

select * from mylock; 
【手动增加表锁】 
lock table 表名字1 read(write)，表名字2 read(write)，其它;
【查看表上加过的锁】  
show open tables; 
【释放表锁】
unlock tables;

加读锁

session1也不能修改mylock表，如果执行写操作，则会报错

session1不能读其他表，是因为锁表是比较重要的事情，MySQL要求你必须把当前锁表的事情解决完，解锁，再去做其他事情；

session2可以读mylock，不能写mylock，如果执行写操作，则会出现阻塞的情况；此时若mylock解锁，session2就解除阻塞，继续执行阻塞前的命令；

加写锁

lock table mylock write;

session1自己可以读自己的写锁表，修改自己的写锁表，但是不能读其他表，因为自己当前的事情还没处理完，解锁之后才可以

session2读mylock也会发生阻塞，直到session1解锁，session2才会执行完之前的语句；写操作session2更没机会了

小结

MyISAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行增删改操作前，会自动给涉及的表加写锁。

MySQL的表级锁有两种模式：

• 表共享读锁（Table Read Lock）
• 表独占写锁（Table Write Lock）

锁类型	可否兼容	读锁	写锁
读锁	是	是	否
写锁	是	否	否

结合上表，所以对MyISAM表进行操作，会有以下情况：

1. 对MyISAM表的读操作（加读锁），不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它进程的写操作。
2. 对MyISAM表的写操作（加写锁），会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操作。 简而言之，就是读锁会阻塞写，但是不会堵塞读。而写锁则会把读和写都堵塞

简而言之：读锁会阻塞写，但是不会阻塞读；写锁会把读和写都阻塞

表锁分析

查看哪些表被加锁了

show open tables;

如何分析表锁定，

可以通过检查 table_locks_waited 和 table_locks_immediate 状态变量来分析系统上的表锁定

show status like 'table%';

table_locks_immediate

• 产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁值加1

table_locks_waited

• 出现表级锁定争用而发生等待的次数（不能立即获取锁的次数，每等待一次锁值加1），此值高则说明存在着较严重的表级锁争用的情况；

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

行锁（偏写）

特点

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

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

• 一是支持事务（TRANSACTION）；
• 二是采用了行级锁

并发事务带来的问题

更新丢失(Lost Update)：

• 当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题－－最后的更新覆盖了由其他事务所做的更新。
• 例如，两个程序员修改同一java文件。每程序员独立地更改其副本，然后保存更改后的副本，这样就覆盖了原始文档。最后保存其更改副本的编辑人员覆盖前一个程序员所做的更改。
• 如果在一个程序员完成并提交事务之前，另一个程序员不能访问同一文件，则可避免此问题。

脏读(Dirty Reads)：

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

不可重复读(Non-Repeatable Reads)：

• 在一个事务内，多次读同一个数据。在这个事务还没有结束时，另一个事务也访问该同一数据。那么，在第一个事务的两次读数据之间。由于第二个事务的修改，那么第一个事务读到的数据可能不一样，这样就发生了在一个事务内两次读到的数据是不一样的，因此称为不可重复读，即原始读取不可重复。
• 一句话：一个事务范围内两个相同的查询却返回了不同数据。

幻读(Phantom Reads)：

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

事务的隔离级别

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

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

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

show variables like 'tx_isolation';

案例分析

建表

create table test_innodb_lock (a int(11),b varchar(16))engine=innodb;

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

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

select * from test_innodb_lock;

行锁基本演示

由于MySQL每一句执行都会自动提交事务，我们为了演示，暂时将1和2的自动提交关闭，改为手动提交事务

set autocommit=0;

session1锁行之后，session2无法操作当前行，会产生阻塞；

当session1提交事务，session2也要提交事务（因为session2设置了手动提交），再查询才能看到当前行的最新数据

无索引导致行锁升级为表锁

现在innodb默认是行锁

如果此时，我们创建索引，字段a 和 字段b，分别为数值和字符

session1和session2各自改不同的行，各自提交，不会出现任何问题

但是，如果session1修改行时，没有给b字段加单引号，虽然可以隐式转换，但是会导致 b 索引失效，引发session1从锁行变成锁表；

session2发生阻塞，无法操作表；直到session1提交事务，session2才会解除阻塞；

因此，varchar类型如果不加单引号，极有可能导致行锁变表锁，发生阻塞；且这种现象很隐蔽，不容易发现；

间隙锁的危害

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

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

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

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

举例：

当前表中，a字段没有2这个值；

如果此时session1执行sql，条件限定为a的范围；那么在session1提交之前，范围内的行都会被锁

如果此时session2提交一条数据，且 a=2，尽管这是一条新数据，不该被session1锁住，但a=2在session1的锁表范围之内，所以session2发生阻塞；直到session1提交事务，session2才正常执行

如何锁定一行

行锁小结

InnoDB存储引擎由于实现了行锁定，虽然在锁定机制的实现上带来性能损耗可能比表锁高一些，但在整体并发处理能力方面远远优于Myisam的表锁定。当系统并发量高的时候，InnoDB的整体性能和Myisam相比就会有比较明显的优势

但是InnoDb的行锁同样也有弱点，如果使用不当，会让InnoDB的整体性能表现不如Myisam，甚至还会更差

如何分析行锁定

通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况

show status like 'innodb_row_lock%';

显示的状态量含义如下：

• Innodb_row_lock_current_waits：当前正在等待锁定的数量
• Innodb_row_lock_time：从系统启动到现在锁定总时间长度
• Innodb_row_lock_time_avg：每次等待花费的平均时间
• Innodb_row_lock_max：从系统启动到现在等待最长的一次花费的时间
• Innodb_row_lock_waits：系统启动后到现在总共等待的次数

其中，平均时长、总次数、总时长 最重要

尤其是等待次数很高，且每次等待时间也比较大时，我们需要分析系统出现了什么问题，开始优化

优化建议

• 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁。
• 尽可能较少检索条件，避免间隙锁
• 尽量控制事务大小，减少锁定资源量和时间长度
• 锁住某行后，尽量不要去调别的行或表，赶紧处理被锁住的行然后释放掉锁。
• 涉及相同表的事务，对于调用表的顺序尽量保持一致。
• 在业务环境允许的情况下,尽可能低级别事务隔离

页锁（了解）

开销和加锁时间界于表锁和行锁之间；

会出现死锁；

锁定粒度界于表锁和行锁之间，并发度一般。

七、主从复制

1. 复制的基本原理

slave会从master读取binlog（二进制日志文件）来进行数据同步

三步骤+原理图

• master将改变记录到二进制日志（binary log）。这些记录过程叫做二进制日志事件，binary log events
• slave将master的binary log events拷贝到它的中继日志（relay log）
• slave重做中继日志中的事件，将改变应用到自己的数据库中。 MySQL复制是异步的且串行化的

2. 复制的基本原则

• 每个slave只有一个master
• 每个slave只能有一个唯一的服务器ID
• 每个master可以有多个salve

3. 复制的最大问题

延时

4. 一主一从常见配置

这里我们以windows作为主机，Linux作为从机

mysql版本一致且后台以服务运行

确保主从服务器在同一网段内，ping测试可以联通，双边都ping一下测试

主从都配置在[mysqld]结点下，都是小写

主机修改my.ini配置文件

• [必须]主服务器唯一ID

• 比如，server-id=1

• [必须]启用二进制日志

• log-bin=自己本地的路径/data/mysqlbin #路径
• log-bin=D:/devSoft/MySQLServer5.5/data/mysqlbin #文件名

• [可选]启用错误日志

• log-err=自己本地的路径/data/mysqlerr #路径
• log-err=D:/devSoft/MySQLServer5.5/data/mysqlerr #文件名

• [可选]根目录，类似于JAVA_HOME

• basedir=“自己本地路径”
• basedir=“D:/devSoft/MySQLServer5.5/”

• [可选]临时目录

• tmpdir=“自己本地路径”
• tmpdir=“D:/devSoft/MySQLServer5.5/”

• [可选]数据目录

• datadir=“自己本地路径/Data/”
• datadir=“D:/devSoft/MySQLServer5.5/Data/”

• read-only=0，主机读写都可以
• [可选]设置不要复制的数据库

• binlog-ignore-db=mysql

• [可选]设置需要复制的数据库

• binlog-do-db=需要复制的主数据库名字

从机修改my.cnf配置文件

• [必须]从服务器唯一ID
• [可选]启用二进制日志
• 因修改过配置文件，请主机+从机都重启后台mysql服务
• 主机从机都关闭防火墙
• 在Windows主机上建立帐户并授权slave，sql语句

• GRANT REPLICATION SLAVE ON . TO ‘zhangsan’@‘从机器数据库IP’ IDENTIFIED BY ‘123456’;#zhangsan是账号123465是密码
• flush privileges;#刷新
• 查询master的状态；

• show master status;
• 记录下File和Position的值，这样从机知道从哪里开始复制

• 执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值变化

• 在Linux从机上配置需要复制的主机

• 执行SQL
  CHANGE MASTER TO MASTER_HOST=‘主机IP’,
  MASTER_USER=‘zhangsan’,
  MASTER_PASSWORD=‘123456’,
  MASTER_LOG_FILE=‘File名字’,
  MASTER_LOG_POS=Position数字;
• 启动从服务器复制功能，start slave;
• show slave status\G

• 下面两个参数都是Yes，则说明主从配置成功！
• Slave_IO_Running: Yes
• Slave_SQL_Running: Yes

• 主机新建库、新建表、insert记录，从机复制
• 如何停止从服务复制功能，stop slave;