MySQL高级
2020-02-07 16:38:35 20 举报AI智能生成
MySQL高级部分，主要包括架构介绍、索引优化、查询截取分析、锁机制、主从复制。
作者其他创作
大纲/内容
架构介绍
MySQL简介
概述
高级
MySQL内核
SQL优化工程师
MySQL服务器的优化
各种参数常量设置
查询语句优化
主从复制
软硬件升级
容灾备份
SQL编程
Linux版安装
主流二进制源码安装
rpm
下载
查看是否安装过，如果安装过则删除
安装MySQL服务端
查看MySQL安装时创建的mysql用户和mysql组
mysql服务启动停止
service mysql start
service mysql stop
启动后连接、设置密码
自启动mysql服务
chkconfig mysql on
chkconfig --list|grep mysql
cat /etc/inittab
ntsysv
配置文件
二进制日志log-bin
错误日志log-error
查询日志log
数据文件
Windows
mysql下data文件夹
Linux
查看全部库mysql下用 ls -lF|grep ^d查
mysql默认路径：/var/lib/mysql
data/mydb1/XXX.frm
存放表结构
data/mysql/XXX.MYD
存放表数据
data/mysql/XXX.MYI
存放表索引
如何配置
Windows：my.ini
Linux：/etc/my.cnf
修改字符集
cp /usr/share/mysql/my-huge.cnf /etc/my.cnf
service mysql stop
service mysql stop
mysql>show databases;
mysql>show variables like "%char%";
vim my.cnf
在修改编码前建的库
mysql的安装位置
yum
总体概览
mysql逻辑架构图
逻辑架构分四层
连接层
完成一些类似连接处理，授权认证及相关的安全方案
服务层
mysql数据库处理底层数据之前的所有工作都是在这一层完成的，包括权限判断，SQL接口，
SQL解析，SQL分析优化，缓存查询的处理以及部分内置函数执行（如日期、时间、数学运算、加密）等等。
各个存储引擎提供的功能都集中在这一层，如存储过程、触发器、视图等。
SQL解析，SQL分析优化，缓存查询的处理以及部分内置函数执行（如日期、时间、数学运算、加密）等等。
各个存储引擎提供的功能都集中在这一层，如存储过程、触发器、视图等。
引擎层
是底层数据存取操作实现部分，由多种存储引擎共同组成。真正负责mysql中数据的存储和提取。服务器通过存储引擎API来与它们交互。存储引擎不能解析SQL，互相之间也不能通信，仅仅是简单的响应服务器的请求。
存储层
将数据存储于裸设备的文件系统之上，完成与存储引擎的交互。
搜索引擎
常用
MyISAM/InnoDB
命令查看
show engines;
show variables like "%storage_engine%";
MyISAM和InnoDB对比
阿里/淘宝用什么
索引优化分析
性能下降SQL慢
查询语句写的烂
索引失效
单值
复合
关联查询太多join（设计缺陷或不得已需求）
子主题
常见通用的join查询
索引简介
是什么
官网：索引是帮助mysql高效获取数据的数据结构
可以简单理解为“排好序的快速查找数据结构”
详解（重要）
结论
一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上
我们平时所说的索引，如果没有特别指明，都是指B树（多路搜索树）结构组织的索引。
其中聚集索引、复合索引、前缀索引、唯一索引默认都是使用B+树索引，统称索引。
其中聚集索引、复合索引、前缀索引、唯一索引默认都是使用B+树索引，统称索引。
优势
提高数据检索效率，减低数据库的IO成本
通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗
劣势
实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占空间；
虽然大大提高查询速度，但是同时会降低表的更新速度，更新表时，
mysql不仅要保存数据，还要更新索引信息。
mysql不仅要保存数据，还要更新索引信息。
索引只是提高效率的一个因素，如果mysql有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询
mysql索引分类
单值索引
即一个索引只包含单个列，一个表可以有多个单值索引
唯一索引
索引列的值必须唯一，但允许有空值
复合索引
即一个索引包括多个列
基本语法
创建
create 【unique】 index indexName on mytable(columnname(length));
alter mytable add 【unique】 index indexName on （columnName（length））
删除
drop index indexName on mytable
查看
show index from tableName\G
使用AITER命令
mysql索引结构
BTree索引
索引原理
Hash索引
full-text全文索引
R-Tree索引
哪些情况需要创建索引
1.主键自动建立唯一索引
2.频繁作为查询条件的字段应该创建索引
3.查询中与其他表关联的字段，外键关系建立索引
4.频繁更新的字段不适合创建索引
5.where中用不到的字段不创建索引
6.单键/组合索引的选择问题，在高并发下倾向创建组合索引
7.查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
8.查询中统计或者分组字段
哪些情况不要创建索引
1.表记录太少
2.经常增删改的表
3.数据重复且平均的表字段，为它建立索引就没有太大效果
性能分析
MySQL Query Optimizer
MySQL 的常见瓶颈
CPU：CPU饱和一般发生在数据装入内存或从磁盘上读取数据的时候
IO：磁盘IO瓶颈发生在装入数据远大于内存容量时
服务器硬件的性能瓶颈：top/free/iostat/vmstat查看系统的性能状态
Explain
是什么（查看执行计划）
可以模拟优化器执行SQL查询语句，从而知道MySQL是
如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈
如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈
官网文档
能干嘛
表的读取顺序
数据读取操作的操作类型
哪些索引可以使用
哪些索引被实际使用
表之间的引用
每张表有多少行被优化器查询
怎么用
Explain + SQL语句
执行计划包含的信息
| id | select_type | table | type | possible_keys | key | key_len |ref | rows |Extra|
各字段解释
id
select 查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
三种情况
id相同，执行顺序table从上到下
id不同，如果是子查询，id顺序递增，id 越大优先级越高，越先执行
id相同、不同都存在
select_type
有哪些（主要6个）
SIMPLE
PRIMARY
SUBQUERY
DERIVED
UNION
UNION RESULT
查询类型介绍
SIMPLE
简单的子查询不包含子查询、union
PRIMARY
查询中若包含任何复杂的子部分，最外层查询被标记为Primary
SUBQUERY
在select 或 where 列表中包含了子查询
DERIVED
在from列表中包含的子查询被标记为DERIVED（衍生）
MySQL会递归执行这些子查询，把结果放在临时表里。
MySQL会递归执行这些子查询，把结果放在临时表里。
UNION
若第二个select出现在union后，则被标记为UNION；
若UNION包含在from子句的子查询中，外层select将被标记为：DERIVED
若UNION包含在from子句的子查询中，外层select将被标记为：DERIVED
UNION RESULT
从UNION表获取结果的select
table
type
| ALL | index | range |ref | eq_ref | const,system | null |
访问类型，从最好到最差：
system>const>eq_ref>ref>range>index>ALL
system>const>eq_ref>ref>range>index>ALL
system
表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，可以忽略不计
const
表示通过索引一次就找到，常用于primary key 或者 unique，很快
如果主键置于where列表中，mysql可以将该查询转换为一个常量
如果主键置于where列表中，mysql可以将该查询转换为一个常量
eq_ref
唯一性索引扫描，对于每个索引建，表中只有一条记录与之匹配，常用于主键或唯一索引扫描
ref
非唯一性索引扫描，返回匹配某个单独值的所有行，
本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，
它可能找到多个符合条件的行，所以他应该属于查找和扫描的混合体
本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，
它可能找到多个符合条件的行，所以他应该属于查找和扫描的混合体
range
只检索给定范围的行，使用一个索引来选择行。key显示使用了哪个索引，
一般就是where语句中出现了between、<、>、in 等的查询，这种范围扫描索引扫描比
全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全表。
一般就是where语句中出现了between、<、>、in 等的查询，这种范围扫描索引扫描比
全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全表。
index
index和all区别为index类型只遍历索引树，这通常比all快，因为索引文件通常比数据文件小，
（两者都是读全表，但index是从索引中读取，而all是从硬盘中读取）
（两者都是读全表，但index是从索引中读取，而all是从硬盘中读取）
all
全表扫描
possible_keys
显示可能应用在这张表中的索引，一个或多个。
查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用
查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用
key
实际使用的索引。如果为null，则没有使用索引
查询中若使用了覆盖索引，则该索引仅出现在key列表中
key_len
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好
key_len 显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的
ref
显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值
rows
根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数
Extra
不适合在其他列显示但是很重要的信息
1.Using filesort
说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。
mysql中无法利用索引完成的排序操作称为“文件排序”
mysql中无法利用索引完成的排序操作称为“文件排序”
2.Using temporary
使用了临时表保存中间结果，mysql在对查询结果排序时使用临时表。
常见于排序order by 和 分组查询group by
常见于排序order by 和 分组查询group by
3.Using index
表示相应的select操作中使用了覆盖索引（Covering Index），避免访问表的数据行，效率不错！
覆盖索引
4.Using where
使用where过滤
5.Using join buffer
使用了连续缓存
6.impossible where
where子句总是false，不能用来获取数据
7.select tables optimized away
没有groupby子句的情况下，基于索引优化（不做展开）
8.distinct
优化distinct操作，在找到第一匹配的元组后即停止找同样值得动作
case
索引优化
索引分析
单表
建表SQL
案例
两表
建表SQL
book
class
案例
三表
建表SQL
案例
结论
索引失效（应该避免）
建表SQL
案例（索引失效）
1.全值匹配我最爱
考虑顺序、个数
2.最佳左前缀法则
如果索引多列，查询从索引的最左前列开始并且不跳过索引中的列。
3.不在索引列上做任何操作（计算，函数（自动或手动）类型转换），会导致索引失效而转向全表扫描
4.存储引擎不能使用索引中范围条件右边的列
5.尽量使用覆盖索引（只访问索引的查询，索引列和查询列一致），减少select *
6.mysql在使用不等于（!= 或者<>）的时候无法使用索引会导致全表扫描
7.is null，is not null 也无法使用索引
8.like以通配符开头（'%abc...'）mysql索引失效会变成全表扫描的操作
%like加右边索引有效
问题：解决like两边% 索引失效的方法？
9.字符串不加单引号索引失效
10.少用or，用它来连接时索引会失效
小总结
group by 基本上都需要进行排序，会有临时表产生
面试题
explain select * from test03 where c1='a1' and c2='a2' and c3='a3' and c4='a4';
explain select * from test03 where c1='a1' and c2='a2' and c4='a4' and c3='a3';
explain select * from test03 where c1='a1' and c2='a2' and c3>'a3' and c4='a4';
explain select * from test03 where c1='a1' and c2='a2' and c4>'a4' and c3='a3';
explain select * from test03 where c1='a1' and c2='a2' andc4='a4' order by c3;
c3作用在排序而不是查找
c3作用在排序而不是查找
explain select * from test03 where c1='a1' and c2='a2' order by c3；
和上面一样
和上面一样
explain select * from test03 where c1='a1' and c2='a2' order by c4;
MySQL内部排序 using filesort
MySQL内部排序 using filesort
8.1 explain select * from test03 where c1='a1' and c5='a5' order by c2, c3;
只用c1一个字段索引，但是c2,c3用于排序无filesort
只用c1一个字段索引，但是c2,c3用于排序无filesort
8.2 explain select * from test03 where c1='a1' and c5='a5' order by c3,c2;
using filesort
using filesort
explain select * from test03 where c1='a1' and c2='a2' order by c2,c3;
explain select * from test03 where c1='a1' and c2='a2' and c5='a5' order by c2,c3;
用c1,c2两个字段索引，但是c2,c3用于排序无filesort
用c1,c2两个字段索引，但是c2,c3用于排序无filesort
explain select * from test03 where c1='a1' and c2='a2' and c5='a5' order by c3,c2;
无filesort，因为有where 后c2='a2'，排序的c2已经是一个常量了
无filesort，因为有where 后c2='a2'，排序的c2已经是一个常量了
explain select * from test03 where c1='a1' and c4='a4' group by c2,c3;
explain select * from test03 where c1='a1' and c4='a4' group by c3,c2;
using temporary，using filesort
using temporary，using filesort
分组前必排序，定值、范围还是排序，order by一般是给个范围
group by 基本上都需要进行排序，会有临时表产生
一般性建议
对于单键索引，尽量选择针对当前query过滤性更好的索引
在选择组合索引的时候，当前query中过滤性最好的字段在索引字段顺序中，位置越靠前越好
在选择组合索引的时候，尽量选择考研能够包含当前query中的where子句中更多字段的索引
尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的
查询截取分析
查询优化
永远小表驱动大表，类似嵌套循环Nested Loop
case
order by 关键字优化
order by 子句，尽量using index 不出现using filesort 方式排序
建表SQL
case
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;（filesort）
explain select * from tblA where age > 20 order by birth,age;（filesort）
explain select * from tblA order by birth;（filesort）
EXPLAIN SELECT * FROM tbla where birth>'2020-01-05 00:00:00' ORDER BY birth;（filesort）
EXPLAIN SELECT * FROM tbla where birth>'2020-01-05 00:00:00' ORDER BY age;（using index）
EXPLAIN SELECT * from tbla ORDER BY age asc ,birth desc;（filesort）
MySQL支持二种方式排序，filesort和index，index效率高，
它指MySQL扫描索引本身完成排序。filesort方式效率低。
它指MySQL扫描索引本身完成排序。filesort方式效率低。
orderby 满足两种情况，会使用index方式排序
order by 语句使用索引最左前列
使用where子句与order by子句条件列组合满足索引最左前列
尽量能在索引列上完成排序操作，遵照索引建的最佳左前缀
如果不在索引列上，filesort有两种算法：
MySQL就要启动单路排序和双路排序
MySQL就要启动单路排序和双路排序
双路排序
mysql4.1之前是使用双路排序，字面意思就是两次扫描磁盘，最终得到数据，
读取行指针和orderby列，对他们进行排序，然后扫描已经排序好的列表，按照列表中事务值重新从列表中读取对应数据
读取行指针和orderby列，对他们进行排序，然后扫描已经排序好的列表，按照列表中事务值重新从列表中读取对应数据
从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段
取一批数据，要对磁盘进行两次扫描，IO耗时严重，改进算法，4.1后采取单路排序
单路排序
从磁盘读取查询需要的所有列，按照order by 列在buffer对它们进行排序，然后扫描排序后的列表进行输出，
它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO，但是它会使用更多的空间，
因为它把每一行都保存在内存中了。
它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO，但是它会使用更多的空间，
因为它把每一行都保存在内存中了。
结论及引申出的问题
总体而言好过双路
但是也有问题
优化策略
增大sort_buffer_size参数设置
增大max_length_for_sort_data参数的设置
为什么
小总结
group by 关键字优化
group by 先排序后进行分组，遵照索引建的最佳左前缀
当无法使用索引列，增大max_length_for_sort_data参数的设置+增大sort_buffer_size参数的设置
where 高于having，能写在where限定的条件就不要having限定了
慢查询日志
是什么
怎么用
说明
查看是否开启及如何开启
默认
SHOW VARIABLES like '%slow_query_log%';
开启
set global slow_query_log=1;
开启后，什么样的SQL会记录到其中
case
查看当前多少秒算满慢
SHOW VARIABLES like '%long_query_time%';
设置慢的阈值时间
set global long_query_time = 3；
为什么设置后看不出变化？
需要重新连接或新开一个会话才能看到修改值。
或者 SHOW global VARIABLES like '%long_query_time%';
记录慢SQL并后续分析
查询当前系统中有多少条慢查询记录
show global status like '%slow_queries%';
配置版
日志分析工具MySQLdumpslow
查看MySQLdumpslow的帮助信息
s:是表示按照何种方式排序
c:访问次数
l：锁定时间
r: 返回记录
t: 查询时间
al：平均锁定时间
ar：平均返回记录数
at：平均查询时间
t: 即为返回前面多少条的数据
g：后边搭配一个正则匹配模式，大小写不敏感的
工作常用参考
批量数据脚本
向表中插入1000W数据
1.建表
2.设置参数log_bin_trust_function_creators
3.创建函数，保证每条数据都不同
随机产生字符串
随机产生部门编号
4.创建存储过程
创建往emp表中插入数据的存储过程
创建往dept表中插入数据的存储过程
5.调用存储过程
Show Profile
是什么
mysql提供可以用来分析当前会话中语句执行的资源消耗情况，可以用于SQL的调优的测量
官网
默认情况下，参数处于关闭状态，并保存最近15次的运行结果
分析步骤
1.是否支持
show VARIABLES like 'profiling';
2.默认关闭，使用前开启
set profiling =on;
3.运行SQL
select * from emp group by id%10 limit 150000;
select * from emp group id%20 order by 5;
4.查看结果
show PROFILES;
5.诊断SQL，show profile cpu,block io for query 上一步前面的问题SQL数字号码；
参数备注
6.日常开发需要注意
converting HEAP to MyISAM查询结果太大，内存都不够用了往磁盘上搬了
creating tmp table创建临时表
拷贝数据到临时表
用完再删除
copying to tmp table on disk，把内存中临时表复制到磁盘，危险！！
locked
全局查询日志
配置启用
编码启用
永远不要再生产环境开启这个功能
锁机制
概述
定义
生活购物
锁的分类
从对数据操作的类型分
读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。
写锁（排它锁）：当前写操作没有完成前，它会阻断其他写锁和读锁。
从对数据操作的粒度分
表锁
行锁
三锁
表锁（偏读）
特点
偏向MyISAM存储引擎，开销小，加锁快；无死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。
案例分析
建表SQL
加读锁
当前session可以读此表，不可以查其他没有加锁的表，不可以插入或修改此表
其他session可以读此表，可以查询或者更新其他表，修改此表发生阻塞
加写锁
当前session对此表的读取、插入、更新都可以
其他session，select发生阻塞
案例结论
简而言之，读锁会阻塞写，但是不会阻塞读；写锁则会把读和写都阻塞。
表锁分析
行锁（偏写）
特点
偏向Innodb引擎，开销大，加锁慢；会出现死锁；锁定粒度小，发生锁冲突的概率最低，并发度也最高
innodb与MyISAM的最大不同有两点：一是支持事务（transaction）；二是采用了行级锁
innodb与MyISAM的最大不同有两点：一是支持事务（transaction）；二是采用了行级锁
复习事务
事务及ACID
并发事务的问题
更新丢失（Lost Update）
脏读（Dirty Reads）
不可重复读（Non-Repeatable Reads）
幻读（Phantom Reads）
事务的隔离级别
read uncommitted
read commited
repeatable read
serializable
案例分析
建表SQL
行锁定基本演示
无索引行锁升级为表锁
间隙锁危害
面试题：常考如何锁定一行
案例结论
行锁分析
优化建议
尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁。
合理设计索引，尽量缩小锁的范围
尽可能较少检索条件，避免间隙锁
尽量控制事务大小，减少锁定资源量和时间长度
尽可能低级别事务隔离
页锁（介于两者之间，会出现死锁，并发度一般，了解即可）
主从复制
复制的基本原理
slave会从master读取binlog来进行数据同步
三步骤+原理图
复制的基本原则
每个slave只有一个master
每个slave只能有一个唯一的服务器ID
每个master可以有多个slave
复制的最大问题
延时
一主一从常见配置
在同一网段，能相互ping通
mysql数据库版本一致且后台以服务运行
主从配置在[mysqld]节点下，都是小写
主机修改my.ini配置文件
1.【必须】主服务器唯一ID
server-id=1
2.【必须】启用二进制日志
log-bin=C:\Program Files\MySQL\MySQL Server 5.5\data\mysqlbin
3.【可选】启用错误日志
log-err=C:\Program Files\MySQL\MySQL Server 5.5\data\mysqlerr
4.【可选】根目录
basedir="C:/Program Files/MySQL/MySQL Server 5.5/"
5.【可选】临时目录
tmpdir="C:/Program Files/MySQL/MySQL Server 5.5/"
6.【可选】数据目录
datadir="C:/ProgramData/MySQL/MySQL Server 5.5/Data/"
7.read-only=0
主机读写都可以
8.【可选】设置不要复制的数据库
binlog-ignore-db=mysql
9.【可选】设置需要复制的数据库
bin-do-db=需要复制的主数据库名字
从机修改my.cnf配置文件
【必须】从服务器唯一ID
注释掉server-id=1，打开server-id=2
【可选】启用二进制日志
默认是启用的
配置修改后主从都要重启后台mysql服务
主机从机都关闭防火墙
Windows手动关闭
Linux：service iptables stop
在Windows主机上建立账户并授权slave
grant replication slave on *.* to 'zhangsan'@'从机器数据库IP' identified by '123456';
flush privileges;
查询master的状态
show master status;
记下File和Position
执行完此步骤后就不要再操作主服务器mysql，防止主服务器状态变化
在Linux从机上配置需要复制的主机
设置和主机同步的文件等
启动从服务复制功能
start slave;
show slave status\G
下面两个参数都是yes,则说明主从配置成功！
Slave_IO_Running:yes
Slave_SQL_Running:yes
主机新建库、新建表、insert记录，从机复制
如何停止从服务复制功能
stop slave;
读写分离与分库分表
自由主题
收藏
职业：暂无
0 条评论
下一页
为你推荐
查看更多
