首页  思维导图  详情

72_MySQL 高级开发

2021-05-11 22:09:58   58  举报





AI智能生成

1111

架构师

项目管理

前端开发

作者其他创作

大纲/内容

MySQL 的架构介绍

MySQL 简介

概述

高级 MySQL

MySQL 内核

SQL 优化工程师

MySQ 服务器的优化

各种参数常量的设置

查询语句优化

软硬件升级

容灾备份

SQL 编程

MySQL 安装

Linux 版本的安装

MySQL 5.7

下载地址

https://dev.mysql.com/downloads/mysql/

下载

检查当前系统是否安装过 MySQL

ps -ef | grep mysql

rpm -qa|grep -i mysql

mysql 5.7 rpm 安装

1.安装common

rpm -ivh mysql-community-common-5.7.29-1.el7.x86_64.rpm

2.安装libs

rpm -ivh mysql-community-libs-5.7.29-1.el7.x86_64.rpm

3.安装client

rpm -ivh mysql-community-client-5.7.29-1.el7.x86_64.rpm

4.安装server

rpm -ivh mysql-community-server-5.7.29-1.el7.x86_64.rpm

安装 MySQL 服务端（注意事项）

查询安装是否成功

ps -ef | grep mysql

查看 MySQL 安装时创建 MySQL 用户和MySQL 组

cat /etc/passwd | grep mysql

cat /etc/group | grep mysql

mysqladmin --version

MySQL 服务的关闭和启动

service mysql start

service mysql stop

MySQL 服务启动后，开始链接

获取密码

cd /var/mysql

grep 'temporary password' /var/log/mysqld.log

MySQL 链接

mysql -u root -p
然后输入密码（ l-uj0avBo,Oi）

修改登录密码

set password for root@localhost = password('S9uK$.6S')

注意密码的复杂度不然会提示

ERROR 1819 (HY000): Your password does not satisfy the current policy requirements

自启动 MySQL 服务

systemctl enable mysqld

systemctl list-unit-files | grep mysql

修改配置文件位置

配置文件地址

/etc/my.cnf

修改字符集和数据存储位置

查看字符集

show variables like '%character%'

show variables like '%char%'

查询结果

修改字符集

mysql 安装位置

ps -ef|grep mysql

MYSQL 配置文件

主要配置文件

二进制日志 log-bin

主从复制

log-bin=mysql-bin

错误日志 log-error

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

log-error=/var/log/mysqld.log

查询日志 log

数据文件

两个系统

windows

目录下可以存储很多库

Linux

查询命令

cd /var/lib/mysql

ls -l | grep ^d

执行结果

默认路径： /var/lib/mysql

frm 文件

存放表结构

myd 文件

存放的表数据

myi 文件

存放的是索引

如何配置

windows

my.ini 文件

Linux

/etc/my.conf 文件

MySQL 逻辑架构介绍

总体架构

总体架构说明

详细介绍

1. 连接层

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

注：分配数据库连接线程池，控制数据库的连接和关闭等资源。

2. 业务逻辑处理层

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

注：提供SQL操作的接口，对SQL脚本按一定规则进行解析，并通过SQL优化器优化执行顺序，对于查询的语句还会进入缓存区，提升系统的性能。

3. 数据存储引擎层

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

注：存储引擎都是可插拔的，每个存储引擎所提供的服务都有所差异，所以我们需要根据具体的业务需要，选择合适的存储引擎，常用的只有两种MyISAM和InnoDB。

4. 数据存储层

主要是将数据存储在运行于裸设备的文件系统上，并完成与存储引擎的交互。

注：将数据存储到磁盘上，并协同存储引擎对数据进行读写操作。

总结

数据库逻辑结构共分为四层，分别是连接层（线程连接池）、业务逻辑处理层（SQL解析读取）、数据存储引擎层（存储引擎）、数据存储层（数据存储）

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

MySQL 存储引擎

查看命令

查询命令 show engines;

执行结果

结果截图

当前默认存储引擎 show variables like '%storage_engines%';

执行结果

结果截图

MyISAM 和 InnoDB

对比表格

阿里巴巴和淘宝的如何使用

索引优化分析

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

查询语句问题

索引失效

单值

复合索引

关联查询太多 join （设计曲线或不得已的需求）

服务器调优各个参数的额设置（缓冲、线程数等）

常见通用的 Join 查询

SQL 执行顺序

SQL 语法格式

语法过程

SQL 解析过过程

执行过程

总结

SQL 解析过程

JOIN 图

图解

建表SQL

7种 JOIN

1. left join

2. right join

3. 交集（inner join）

4. 左差集

5. 右差集

6. 全集

7. 差集

索引简介

是什么

MySQL 官方对索引的定义为：索引（Index）是帮助 MySQL 高效获取数据的数据结构。
可以得到索引的本质：索引是数据结构

索引理解：“用于排序和快速查找的数据结构”。

详解（重要）

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）的数据，
这样就可以再这些数据结构上实现高级查找算法。这种数据结构，就是索引。

二叉树索引

左边是数据表，一共有两列7条记录，最左边是数据记录的物理地址。

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

结论

数据本身之外，数据库还维护这一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，
这样就可以在这些数据结构的基础上实现高级查找算法。这种数据结构就是索引。

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

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

优势

1. 类似大学图书馆目录索引，提高数据检索的效率，降低数据的IO成本。

2. 通过索引列对数据库进行排序，降低数据排序的成本，降低了CPU的消耗。

劣势

1. 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的

2. 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。
因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，
都会调整因为更新所带来的键值变化后的索引信息

3. 索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句

MySQL 索引分类

单值索引

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

唯一索引

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

复合索引

即一个索引包含多个列

基本语法

创建

create [unique] index indexName on mytable(columnname(length));

alter mytable add [unique] index [indexName] on (clumnname(length))

删除

drop index [indexname] on mytable;

查看

show index form table_name\G

使用 alter 命令

索引创建的4种方式

1. alter table tb_name add primary key (column_list): 添加一个主键，这意味着索引必须是唯一的且不能为null

2. alter table tb_name add unique index_name(column_list): 这条语句创建索引的值必须是唯一的（除了 NULL 外，
NULL 可能会出现多次）

3. alter table tb_name add index index_name (column_list): 添加普通索引，索引值可出现多次

4. alter table tb_name add fulltext index_name(column_list): 该语句指定了索引为 full text，用于全文索引

建议：一张表索引不要超过5个

MySQL 索引结构

BTree 索引

检索原理

B+树

【初始化介绍】：
一颗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，显然成本非常的高

Hash 索引

full-text 全文索引

R-Tree 索引

哪些情况下需要创建索引？

1. 主键自动建立唯一索引

2. 频繁作为查询条件的字段应该创建索引

3.查询中与其他关联的字段，外键关系建立索引

4.频繁更新的字段不是创建索引

因为每次更新不单单是更新了记录还会更新索引

5.where 条件中用不到的字段不创建索引

6.单键/组合索引的选择， who? （高并发下倾向于创建组合索引）

7. 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度。

8.查询中统计或分组字段

哪些情况下不需要创建索引？

1. 表记录太少

2. 经常增删改的字段

提高了查询速度，同时会降低更新表的速度，如对表进行 insert , update , delete 因为表更新时，
MySQL 不仅仅要保存数据，还要保存索引文件。

3. 数据重复，且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。
如果某个数据列包含多个重复的内容，为它建立索引就没有太大的实际效果。

1. 假如一个表由10万记录，由一个字段A只有 T 和 F 两种值，且每个值的分布概率大约为 50%，
那么对这种表A字段建索引一般不会提高数据库的查询速度。

2. 索引的选择性是指缩影列中不同值的数目与表中记录数相比。如果一个表中有2000条记录，表索引列有1980个不同的值，
那么这个缩影的选择性就是 1980/2000=0.99，一个索引的选择性越接近于1，这个索引的效率就越高

性能分析

MySQL Query Optimizer

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

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

Myql 常见性能瓶颈

CPU：CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据的时候

IO：磁盘 I/O 瓶颈发生在装入数据远大于内存容量的时候

服务器硬件的性能瓶颈：top, free, iostat 和 vmstat 来查看系统的性能状态

Explain

什么是执行计划

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

官网介绍

https://dev.mysql.com/doc/refman/5.7/en/explain-output.html

执行计划能帮我们完成什么事情？

表的读取顺序

数据读取操作的操作类型

哪些索引可以使用

哪些索引被实际使用

表之间的引用

每张表有多少行被优化器查询

怎么使用执行计划？

Explain + SQL 语句

执行计划的包含信息

包含信息

名词解释

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

三种情况

id 相同，执行顺序由上而下

运行结果

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

运行结果

id 相同不同，同时存在

id 如果相同，可以认为是一组的，从上往下执行；
在所有组中，id 值越大，优先级越高，越先执行

衍生 = DERIVED

运行结果

derived_merge是MySQL 5.7引入的，其会试图将Derived Table（派生表，from后面的子查询），视图引用，
公用表表达式（Common table expressions）与外层查询进行合并。

MySQL 5.7中不再兼容的实现方式，可以通过调整optimizer_switch来加以规避
set optimizer_switch='derived_merge=off';

select_type

有那些

1. simple

2. primary

3. subquery

4. derived

5. union

6. union result

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

1. simple

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

2. primary

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

3. subquery

在 select 或 where 列表中包含子查询

4. derived

在 from 列表中包含的子查询被标记为 derived (衍生) MySQL
会递归执行这些子查询，把结果放在临时表中。

5. union

若第二个 select 出现 union 之后，则被标记为 union ,
若 union 包含在 from 子句查询中，外层 select 将被标记为 derived

6. union result

从 union 表获取结果的 select

table

这行数据是关于哪张表的

type

类型

all

index

range

ref

eq_ref

const, system

NULL

访问类型排列

type 显示的是访问类型，是较为重要的一个指标，结果值从最好到最坏依次是：

system > const > eq_ref > ref > range > index > ALL

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

显示查询使用了何种类型，
从最好到最差依次是：
system>const>eq_ref>range>index>ALL

sytem

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

const

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

示例

eq_ref

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

示例

ref

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

示例

rang

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

index

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

示例

all

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

示例

表示索引中使用的字节数，可通过该列计算查询中的使用的索引的长度，在不损失精确性的情况下，长度越短越好

备注：一般来说，得以保证查询至少达到 rang 级别，最好能达到 ref。

possible_keys

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

key

实际使用的缩影，如果为NULL，则没有使用索引

查询中若使用了覆盖索引，则该索引仅出现在KEY列表中

示例1

示例2

key_len

key_len 显示的只为索引字段的最大可能长度，并非实际使用长度。即 key_len e是更具表定义计算而得，不是通过表内检索出的。

示例

总结：条件越多，付出的代价越大，key_len 的长度也就越大，建议在一定条件的情况下，key_len 越短，效率越高。

ref

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

示例

由，key_len 可知，t2 表的 idx_col1_col2 被从充分使用了, co1 匹配了 t1 表的 col1, t2 表的 col1 匹配了一个常量即 ‘ac’

查询中与其他表关联的字段，外键关系建立索引。（实际开发中不推荐）

rows

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

示例

filtered

Extra

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

id, select_type, table, type , possible_keys, key, key_len, ref, rows, Extra

1. Using filesort

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

示例

2. Using temporary

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

示例

3. Using index

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

示例

覆盖索引（Covering Index）

覆盖索引（Covering Index）, 一说为索引覆盖

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

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

注意

如果要使用覆盖索引，一定要注意 select 列表汇总只取出需要的列，不可 select *

因为如果将所有字段一起做索引将会导致索引文件过大，查询性能下降。

4. Using where

表明使用了 where 过滤

5. using join buffer

使用了链接缓存

6. impossible where

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

示例

7. select tbale optimized away

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

8. distinct

优化 distinct 操作在找到第一匹配的元祖后立即停止找相同值的动作。

子主题

热身 CASE

案例一

问题

案例分析

第一行（执行顺序4）：id 列为1 ，表示 union 的第一个 select , select_type 的 primary 表表示该查询为外层查询， table
列被标记为 <derived3>, 表示查询结果来自一个衍生表，其中 derived3 中的 3 代表查询衍生自第三个 select 查询，即 id 为 3 的 select [select d1.name ... ]

第二行（执行顺序为2）：id 为 3 ，是整个查询中第三个 select 的一部分，因查询包含在from 中，所以为derived 。【select id, name from where other_column = ''】

第三行（执行顺序为3）：select 列表中的子查询 select_type 为 subquery , 为整个查询中的第二个 select . [select id from t3]

第四行（执行顺序为1）：select_type 为 union ，说明第四个 select 是 unin 里的第二个 select , 最先执行【select name ,id from t2】

第五行（执行顺序为5）：代表 union 的临时表中读取行的阶段， table 列的 <union , 1, 4> 表示用第一个和第四个 select 结果进行union 操作。【两个结果 union 操作】

索引优化

索引分析

一表

建SQL

案例

两表

建表SQL

案例

总结：左链接，索引建立到右表，右链接，索引建立到左表

三表

建表SQL

案例(包含结论)

索引失效（应该避免）

建表SQL

案例（索引失效）

1. 全值匹配

2. 最佳左前缀法则

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

索引失效

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

索引正常

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

explain select * from staffs where name = 'July' and pos = 'dev1';

MySQL 5.6 以上版本中的新特性,是一种在存储引擎层使用索引过滤数据的一种优化方式。ICP开启时的执行计划含有 Using index condition 标示，表示优化器使用了ICP对数据访问进行优化。

ICP（index condition pushdown）是MySQL利用索引（二级索引）元组和筛字段在索引中的WHERE条件从表中提取数据记录的一种优化操作。ICP的思想是：存储引擎在访问索引的时候检查筛选字段在索引中的WHERE条件（pushed index condition，推送的索引条件），如果索引元组中的数据不满足推送的索引条件，那么就过滤掉该条数据记录。ICP（优化器）尽可能的把index condition的处理从Server层下推到Storage Engine层。Storage Engine使用索引过过滤不相关的数据，仅返回符合Index Condition条件的数据给Server层。也是说数据过滤尽可能在Storage Engine层进行，而不是返回所有数据给Server层，然后后再根据WHERE条件进行过滤。

图示1

图示2

官方解释：https://dev.mysql.com/doc/refman/5.6/en/index-condition-pushdown-optimization.html

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

示例

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

示例

在 5.6 +， ICP特征可以使用到 Using index condition

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

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

示例

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

示例

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

问题：解决 like '% 字符串 %' 索引不被使用的方法？？

建表SQL

#like 关键字 '%%'
create table `tb_user` (
`id` int(11) not null auto_increment,
`name` varchar(20) default null,
`age` int(11) default null,
`email` varchar(20) default null,
primary key(id)
) engine = innodb auto_increment=1 default charset = utf8;

select * from `tb_user`;
#drop table `tb_user`;

insert into tb_user(name, age, email) values ('1aa1', 21, 'b@163.com');
insert into tb_user(name, age, email) values ('2aa2', 222, 'a@163.com');
insert into tb_user(name, age, email) values ('3aa3', 256, 'c@163.com');
insert into tb_user(name, age, email) values ('4aa4', 21, 'd@163.com');

完整 SQL（含优化结论）

覆盖索引

示例：

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

示例

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

示例

11. 小总结

总结

总结2

面试题讲解

SQL 题目

解题和分析

定值、范围还是排序，一般order by是给一个范围

group by 基本上都是需要排序的，会有临时表产生

一般性建议

对于单键索引，尽量选择针对当前 query 过滤更好的索引

在选择索引的时候，当前 query 中过滤性最好的字段顺序

在选择组合索引的时候，尽量选择可以能够包含当前query 中的where 子句

尽可能通过分析统计信息和调整 query 的写法来达到选择适合索引的目的

总结

全值匹配我最爱，最左匹配前缀要准守

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

索引列上少计算，范围之后全失效

LIKE 百分写最右，覆盖索引不写星

小总结

分析过程

1. 观察，至少一天，看看生产慢SQL的情况

2. 开启慢查询日志，设置阈值，比如超过5秒钟就是是慢SQL ，并将他们抓取出来。

3. explain + 慢SQL分析

4. show profile

5. 运维经理 + DBA，进行 SQL数据库服务器的参数调优。

总结

1. 慢查询开启并捕获

2. explain + 慢 SQL 分析

3. show profile 查询SQL服务器里面的执行细节和生命周期情况

4. SQL 数据库服务器的参数调优

查询截取分析

查询优化

永远小表驱动大表
类似嵌套循环 Nested Loop

Case

in 和 exstis

explain select * from tb_emp where exists (select 1 from tb_dept where tb_dept.id = tb_emp.dept_id);

explain select * from tb_emp where tb_emp.dept_id in (select id from tb_dept);

order by 关键字优化

order by 子句，尽量使用 index 方式排序，避免使用filesort 方式排序

建表 SQL

CASE

示例

MySQL 支持两种方式的排序， FileSort 和 Index, Index 效率高
指 MySQL 扫描索引本省完成排序， FlleSort 方式效率低

ORDER BY 满足的两种情况，会使用 Index 方式排序

ORDER BY 语句使用索引最左前列

使用 Where 子句与 Order By 子句条件列组合满足索引左前列

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

如果不再索引列上， filesort 有两种算法：
mysql 就要启动双路排序和单路排序

双路排序

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

从磁盘取排序字段，在 buffer 进行排序，再从磁盘中取其他字段

取一批数据，要对磁盘进行了两次扫描，众所周知， I/O 是很耗费时间的，所以在 mysql 4.1 之后，出现了第二种改进的算法，就是单路排序

单路排序

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

结论引出的问题

由于单路是后出的算法，整体而言是要好过双路

但是单路也是有问题的

在 sort_buffer 中，方法B比方法 A要多占很多空间，因为方法B是把所有的字段都取出，所以有可能取出的数据的总大小超出了
sort_buffer 的容量，导致每次只能取 sort_buffer 容量大小的数据，进行排序（创建 tmp 文件，多路合并，排完后再取）

本想的是节省一次 I/O 操作，反而导致了大量的 I/O 操作，反而得不偿失。

优化策略

增大 sort_buffer_size 参数的设置

增大 max_length_for_sort_data 参数的设置

Why

提到 order by 的速度

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

1.1 当 Query 的字段大小总和小于 max_lenght_for_sort_data 而且排序字段不是 text|blob 类型时，会用改进后的算法--单路排序，否则采用老算法--多路排序

1.2 两种算法的数据都有可能超过 sort_buffer 的容量，超出之后，会创建 tmp 文件进合并排序，导致多次 I/O, 但是用单路排序算法的风险会更大一些，所以需要提高 sort_buffer_size

2. 尝试提高 sort_buffer_size
不管用那种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的

3. 尝试提高 max_length_for_sort_data
提高这个参数，会增加改进算法的效率。但是如果设的太高，数据总容量超出 sort_buffer_size的概率将增大，
明显现象是高的磁盘 I/O 会懂和低的处理器使用率

小总结

为排序使用索引

* MySQL 两种排序方式：文件排序和扫描有序索引排序
* MySQL 能为排序和查询使用相同的索引

Key a_b_c(a, b, c)

order by 能使用索引最左前缀

-order by a
-order by a,b
-order by a,b,c
-order by a desc, b desc, cdesc

如果 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 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 /* 对于排序来说，多个相等条件也是范围查询*/

group by 关键字优化

group by 实质是先排序后分组，准照索引建的最佳左前缀

当无法使用索引列，增大 max_length_for_sort_data 参数的设置 + 增大 sort_buffer_size 参数的设置

where 高于 having ，能写在 where 中的限定就不要写在 having 中

慢日志查询

是什么

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 的值来开启

查询结果

开启

set global slow_query_log = 1;

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

执行后的结果

那么慢查询开启了慢查询日志后，怎么样的 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;

为什么设置后看不出变化

需要重新链接或新开一个会话才能看到修改值。
show variables like '%long_query_time%';

show global variables like '%long_query_time%';

记录慢 SQL 并后续分析

模拟慢 SQL ： select sleep(4);

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

show global status like '%Slow_queries%'

示例

日志分析工具 mysqldumpslow

查看 mysqldumpslow 的帮助信息

查看帮助信息

s: 是表示按照何种方式排序；

c: 访问次数

l: 锁定时间

r: 返回记录

t: 查询时间

al: 平均锁定时间

ar: 平均返回记录数

at: 平均查询时间

t: 即为返回前面多少条的数据

g: 后面搭配一个正则匹配模式，大小写不敏感

工作常用参考

得到返回记录集最多的 10 个SQL
mysqldumpslow -s r -t 10 /var/bin/mysql/xx-slow.log

得到访问次数最多的 10 个SQL
mysqldumpslow -s c -t 10 /var/bin/mysql/xx-slow.log

得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s c -t 10 -g "left join" /var/bin/mysql/xx-slow.log

另外建议在使用这些命令结合｜和 more 使用，否则有可能出现爆屏现象
mysqldumpslow -s r -t 10 /var/bin/mysql/xx-slow.log | more

批量数据脚本

插入 1000w 数据

1. 建表

2. 设置参数 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

这样添加参数后，如果mysql 重启，上述参数又会消失，永久方法：

windows 下 my.ini 加上 log_bin_trust_function_creators = 1

liunx 下 /etc/my.conf 下 my.conf[mysqld] 加上 log_bin_trust_function_creators = 1

3. 创建函数，保证每条数据都不同

随机字符串

随机产生部门编号

4. 创建存储过程

dept

emp

5. 调用存储过程

dept

# dept 表中插入数据
call insert_dept(100, 1000000);
call insert_dept(2000000, 1000000);
call insert_dept(3000000, 1000000);
call insert_dept(4000000, 1000000);
call insert_dept(5000000, 1000000);
call insert_dept(6000000, 1000000);
call insert_dept(7000000, 1000000);
call insert_dept(8000000, 1000000);
call insert_dept(9000000, 1000000);

emp

# emp 表中插入数据
call insert_emp(100, 1000000);
call insert_emp(2000000, 1000000);
call insert_emp(3000000, 1000000);
call insert_emp(4000000, 1000000);
call insert_emp(5000000, 1000000);
call insert_emp(6000000, 1000000);
call insert_emp(7000000, 1000000);
call insert_emp(8000000, 1000000);
call insert_emp(9000000, 1000000);
call insert_emp(10000000, 1000000);

Show Profile

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

官网：https://dev.mysql.com/doc/refman/5.7/en/show-profile.html

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

分析步骤

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

show variables like 'profiling'
默认关闭，使用前需要开启

查询结果

或者: show variables like 'profiling%';

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

show variables like 'profiling%';

set profiling=on;

子主题

3. 运行 SQL

select `id` % 10 from `emp` group by `id` % 10 limit 150000;

select id%20 from emp group by id%20 order by id;

4. 查看运行结果：show profiles;

5. 诊断SQL ： show profile cpu, block io for query 上一步前面的问题 SQL 数字号码；

示例 show profile cpu, block io for query 3;

记录日志

参数备注

type

ALL

显示全部开销信息

BLOCK IO

显示块 IO 相关开销

CONTEXXTSWITCHAES

上下文切换相关开销

CPU

显示 CPU 相关的开销信息

IPC

显示发生和接收相关的信息

MEMORY

显示内存相关的信息

PAGE FAULTS

显示页面错误相关的开销信息

SOURCE

显示和 Source_function, Source_file, Source_line 相关的开销信息

SWAPS

显示交换次数相关的开销的信息。

6. 日常开发需要注意的事项

converting HEAP to MyISAM 查询结果太大，内存都不够用了往磁盘上面搬了

Create tmp table 创建临时表

拷贝数据到临时表

用完再删除

示例

Copying to tmp table on disk 把内存中的临时表复制到磁盘，危险！！！！

bocked

全局查询日志（测试环境使用）

配置启用

在 mysql 的 my.cnf 中设置如下：
# 开启
general_log=1
#记录日志文件的路径
general_log_file=/path/logfile
#输出格式
log_output=FILE

编码启用

set global general_log = 1;

set global log_output='TABLE';

此后，你所编写的 SQL 语句，都将会记录到 mysql 库的 general_log 表中，可以用如下命令查看

select * from mysql.general_log;

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

MySQL 锁机制

概述

定义

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

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

生活购物案例

打个比方，我们到淘宝上买个一件商品，商品库存只有一件，这个时候，如果有另外的买家。那么如何解决是你买到还是别人买到的问题。

示例

这里肯定要用到事务，我们先从库存中取出物品的数量，然后插入订单。付款后插入付款信息。然后更新商品数量，这个过程使用锁，可以对有限的资源进行保护，解决隔离和并发的矛盾。

锁的分类

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

读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会相互影响

写锁（排他锁）：当前写操作没有完成之前，它会阻隔其他写锁和读锁

对数据操作的粒度来分

表锁

行锁

三锁

表锁（偏读）

特点

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

案例分析

建表SQL

加读锁

加读锁示例

加写锁

加写锁示例

案例结论

结论

表锁分析

1. 查询被锁定的表

show open tables;

2. 如果分析表锁

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

SQL: show status like 'table%';

示例

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

Table_locks_immediate：产生表级锁定的次数，表示可以理解获取锁的查询次数，每次立即获取锁值增加1；
Table_locks_waited：出阿信啊标记锁定争用而发生的等待的次数（不能理解获取锁的次数，每等待一次锁值加1），
此值则说明存在着比较严重的表级争用的情况。

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

页锁

开销和加锁时间介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般

了解一下即可

主从复制

复制的基本原则

slave 会从 master 读取 binlog 来进行数据同步

三个步骤 + 原理图

原理图

三个步骤

MySQL复制过程分为三步：

1. master 将改变记录到二进制日志（binary log）. 这些记录过程叫做二进制日志时间， binary log events;