72_MySQL 高级开发
2021-04-02 16:58:06 121 举报AI智能生成
登录查看完整内容
MySQL
作者其他创作
大纲/内容
MySQL 开发技能(Centos7 + MySQL 5.7)
MySQL 的架构介绍
MySQL 简介
概述
高级 MySQL
MySQL 内核
SQL 优化工程师
MySQ 服务器的优化
各种参数常量的设置
查询语句优化
软硬件升级
容灾备份
SQL 编程
完整的 MySQL 优化需要很深的功底很多公司会有专门的DBA来优化
MySQL 安装
Linux 版本的安装
MySQL 5.7
下载地址
MySQL 下载地址
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
安装参考文件：https://blog.csdn.net/wudinaniya/article/details/81094578
错误解决方案：https://blog.csdn.net/ljh101/article/details/113475547
安装 MySQL 服务端（注意事项）
查询安装是否成功
查看 MySQL 安装时创建 MySQL 用户和MySQL 组
cat /etc/passwd | grep mysql
cat /etc/group | grep mysql
mysqladmin --version
MySQL 服务的关闭和启动
service mysql start
service mysql stop
centos7系统：--- systemctl start mysqld.service 启动mysql systemctl status mysqld.service 查看mysql状态 systemctl stop mysqld.service 关闭mysql
MySQL 服务启动后，开始链接
获取密码
cd /var/mysql
grep 'temporary password' /var/log/mysqld.log
MySQL 链接
修改登录密码
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 文件
存放的是索引
如何配置
my.ini 文件
/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+树
【查找过程】：如果要查找数据项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. 经常增删改的字段
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 瓶颈发生在装入数据远大于内存容量的时候
Explain
什么是执行计划
使用 EXPLAIN 关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的，分析你的查询语句或是表结构的性能瓶颈。
官网介绍
https://dev.mysql.com/doc/refman/5.7/en/explain-output.html
执行计划能帮我们完成什么事情？
表的读取顺序
数据读取操作的操作类型
哪些索引可以使用
哪些索引被实际使用
表之间的引用
每张表有多少行被优化器查询
怎么使用执行计划？
Explain + SQL 语句
执行计划的包含信息
包含信息
名词解释
id
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
查询的类型，主要是用于区别普通查询，联合查询，子查询等复杂的查询
简单的 select 查询，查询中不包含子查询或者union
查询中若包含任何复杂的子部分，最外层查询则被标记为
在 select 或 where 列表中包含子查询
在 from 列表中包含的子查询被标记为 derived (衍生) MySQL 会递归执行这些子查询，把结果放在临时表中。
从 union 表获取结果的 select
table
这行数据是关于哪张表的
type
类型
all
index
range
ref
eq_ref
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 就能将该查询转换为一个常量
示例
唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
非唯一性索引扫描， 返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有匹配某个单独的行，然而，它可能会找到多个符合个条件的行，所以它应该属于查找和扫描的混合体
rang
只检索给定范围内的行，使用一个索引来选择行。key 列显示使用了哪个索引一般就是你在 where 语句中出现了 between、<、>、in 等的查询 这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某个点，而结束于另一个点，不用全表扫描
Full Index Scan ， index 于 ALL的却别 ，index 类型只遍历索引树， 这通常比 ALL 快， 因为索引文件通常比数据文件小。（也就是说虽然 all 和 index 都是读全表，但是index 是从索引中读取的， 而 all 是从硬盘中读取的 ）
Full Table Scan 将遍历全表找到匹配的行
备注：一般来说，得以保证查询至少达到 rang 级别， 最好能达到 ref。
possible_keys
显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用
key
实际使用的索引，如果为NULL，则没有使用索引
查询中若使用了覆盖索引，则该索引仅出现在KEY列表中
示例1
示例2
key_len
表示索引中使用的字节数，可通过该列计算查询中的使用的索引的长度，在不损失精确性的情况下，长度越短越好
key_len 显示的只为索引字段的最大可能长度， 并非实际使用长度。即 key_len e是更具表定义计算而得，不是通过表内检索出的。
总结：条件越多，付出的代价越大，key_len 的长度也就越大，建议在一定条件的情况下，key_len 越短，效率越高。
显示索引的哪一列被使用了，如果可能的话，是一个常数。那些列或常量被用于查找索引列上的值
查询中与其他表关联的字段，外键关系建立索引。（实际开发中不推荐）
rows
根据表统计信息及索引选用情况， 大致估算出找到所需的记录所需读取的行数
filtered
Extra
包含不适合其他列中显示但十分重要的额外信息
1. Using filesort
说明 mysql 会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取mysql 中无法利用索引完成的排序叫做 “文件排序”
2. Using temporary
使用了临时表保存中间结果， MySQL 在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by 。
3. Using index
覆盖索引 （Covering Index）
理解方式一：就是 select 的数据列只用从索引中就能取得，不必读取数据行， MySQL 可以利用你索引返回 select 列表的字段， 而不必根据索引再次读取数据文件，换句话说查询列要被所建的索引覆盖
理解方式二：索引是高效找到的行的一个方法， 但是一般数据库也能使用索引找到一个列的数据， 因此它不必读取整个行，毕竟索引叶子节点存储了他们索引的数据；当能通过读取索引就可以得到想要的数据， 那就不需要读取行了。一个索引包含了（或覆盖了）满足查询结果的数据就叫做覆盖索引。
注意
如果要使用覆盖索引，一定要注意 select 列表汇总只取出需要的列，不可 select *
因为如果将所有字段一起做索引将会导致索引文件过大，查询性能下降。
4. Using where
表明使用了 where 过滤
5. using join buffer
使用了链接缓存
6. impossible where
7. select tbale optimized away
在没有 GROUPBY 子句的情况下，基于索引优化 MIN/MAX 操作或者对于 MyISAM 存储引擎优化 COUT（*） 操作不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
8. distinct
优化 distinct 操作 在找到第一匹配的元祖后立即停止找相同值的动作。
子主题
热身 CASE
案例一
问题
案例分析
第二行（执行顺序为2）：id 为 3 ，是整个查询中第三个 select 的一部分， 因查询包含在from 中， 所以为derived 。 font color=\"#c41230\
索引优化
索引分析
一表
建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';
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 在使用不等于 （!= 或者 <>）的时候无法使用索引会导致全表扫描
8. like 以通配符开头 （'%abc ...'）mysql 索引失效会变成全表扫描的操作
问题：解决 like '% 字符串 %' 索引不被使用的方法 ？？
完整 SQL（含优化结论）
覆盖索引
示例：
9. 字符串不加单引号索引失效
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
1
2
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 类型时，会用改进后的算法--单路排序，否则采用老算法--多路排序
2. 尝试提高 sort_buffer_size不管用那种算法， 提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的
3. 尝试提高 max_length_for_sort_data提高这个参数，会增加改进算法的效率。但是如果设的太高，数据总容量超出 sort_buffer_size的概率将增大，明显现象是高的磁盘 I/O 会懂和低的处理器使用率
为排序使用索引
* MySQL 两种排序方式：文件排序和扫描有序索引排序* MySQL 能为排序和查询使用相同的索引
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%';
开启
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 源码里是font color=\"#c41230\
查看当前多少秒算慢
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 个SQLmysqldumpslow -s r -t 10 /var/bin/mysql/xx-slow.log
得到访问次数最多的 10 个SQLmysqldumpslow -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 ...
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. 调用存储过程
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;
记录日志
参数备注
ALL
显示全部开销信息
BLOCK IO
显示块 IO 相关开销
CONTEXXTSWITCHAES
上下文切换相关开销
CPU
显示 CPU 相关的开销信息
IPC
显示发生和接收相关的信息
MEMORY
显示内存相关的信息
PAGE FAULTS
显示页面错误相关的开销信息
SOURCE
SWAPS
显示交换次数相关的开销的信息。
6. 日常开发需要注意的事项
converting HEAP to MyISAM 查询结果太大，内存都不够用了往磁盘上面搬了
Create tmp table 创建临时表
拷贝数据到临时表
用完再删除
Copying to tmp table on disk 把内存中的临时表复制到磁盘， 危险！！！！
locked
全局查询日志（测试环境使用）
配置启用
在 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 存储引擎，开销小，加锁块；无死锁；锁定粒度大，发生锁冲突的概率高，并发度最低。
加读锁
加读锁示例
加写锁
加写锁示例
案例结论
表锁分析
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;
2. slave 将 master 的 binary log events 拷贝到它的中继日志（relay log）;
3. slave 重做中继日志中的时间，将改变应用到自己的数据库中。MySQL 复制是一步的且串行化的。
每个 slave 只有一个 master
每个 slave 只能有一个唯一的服务器ID
每个 master 可以有多个 slave
复制的最大问题
延时
以主一从常见配置
mysql 版本一致且后台以服务运行
主从都配置在 [mysqld] 节点下，都是小写
主库修改 my.ini 配置文件
1. [必须] 主机服务器唯一 ID
server-id=1
2. [必须] 启用二进制日志
log-bin = 自己的本地的路径/mysqlbin
log-bin=D:/devsoft/mysql5.7/data/mysqlbin
3 [可选] 启用错误日志
log-err = 自己的本地的路径/mysqlerr
log-err=D:/devsoft/mysql5.7/data/mysqlerr
4. [可选] 根目录
basedir = \"自己本地路径\"
basedir = \"D:/devsoft/mysql5.7/\"
5. [可选] 临时目录
tempdir = \"自己本地路径\"
tempdir = \"D:/devsoft/mysql5.7/\"
6. [可选] 数据目录
datadir = \"自己本地路径/data\"
datadir = \"D:/devsoft/mysql5.7/data\"
7. read-only = 0
主机读写都可以
8. [可选] 设置不要复制的数据库
binlog-ignore-db=mysql
9. [可选]设置需要复制的数据库
binlog-do-db=需要复制的主数据库名字
从库修改 my.conf 配置文件
2. [可选] 启用二进制日志
因修改过配置文件，请主机 + 从机都重启后台 mysql 服务
主机从机关闭防火墙
windows 手动关闭
liunx service iptables stop
在 Windows 及其上建立账户并授权 slave
grant replication slave on *.* to 'zhangsan'@'从库IP' identified by '123456';
flush privileges;
查询 master 状态
show master status;
查询
记录 File 和 Postition 值
截图
在 LIunx 上配置需要复制的主机
CHANGE MASTER TO MASTER_HOST='你的 IP'MASTER_USER=‘zhangsan’MASTER_PASSWORD='123456'MASTER_LOG_FILE='mysqlbin.具体数字'， MASTER_LOG_POS=具体值；
截图1
启动从服务其复制功能
start slave
show slave status\\G
Slave_IO_Running: Yes
Slave_IO_Running: NO
如果上面的两个参数都是 : Yes 说明配置成功！！
主机新建库、新建表、insert 记录、从机复制
如何重启从服务器的复制
stop slave
收藏
0 条评论
回复 删除
下一页
