MySQL(十八)-排序
参考文献
高性能MySQL(第三版)
MySQL实战45讲
排序
MySQL有两种方式可以生成有序的结果:通过排序操作或者按索引顺序扫描;
若EXPLAIN出来的type列的值为"index",则说明MySQL使用了索引扫描来做排序.
扫描索引本身是很快的,因为只需要从一条索引记录移动到紧接着的下一条记录.但如果索引不能覆盖查询所需要的全部列,那就不得不每扫描一条索引记录都回表查询一次对应的行.这基本都是随机I/O,因此按索引顺序读取数据的速度通常要比顺序地全表扫描慢,尤其是在I/O密集型的工作负载时.
只有当索引的列顺序和ORDER BY子句的顺序完全一致,并且所有列的排序方向(倒序或正序)一样时,MySQL才能够使用索引来对接过做排序.
如果查询需要关联多张表,则只有当ORDER BY子句引用的字段全部为第一个表时,才能使用索引做排序.
ORDER BY子句和查找性查询的限制是一样的:需要满足索引的最左前缀的原则,否则MySQL都需要执行排序操作,而无法利用索引排序.
有一种情况下ORDER BY子句可以不满足索引的最左前缀的要求 ...
MySQL(十七)-查询优化
参考文献
高性能MySQL(第三版)
MySQL实战45讲
MySQL查询语句的执行流程
大体来说,MySQL可分为服务层和存储引擎层两部分
Server层包括连接器,查询缓存,分析器,优化器,执行器等,涵盖MySQL的大多数核心服务功能,以及所有的内置函数(如日期,时间,数字和加密函数等)
而在存储引擎层负责的数据存储和提取.其架构模式是插件式的,支持InnoDB,MyISAM,Memory等多个存储引擎.现在最常用的存储引擎是InnoDB,它是从MySQL5.5.5版本看是称为默认存储引擎.
MySQL通信协议
MySQL客户端和服务端之间的通信协议是半双工的,这意味着,在任何一个时刻,要么是由服务器向客户端发送数据,要么是有客户端向服务器发送数据,这两个动作不能同时发生,所以我们无法将一个消息切成小块独立来发送.
这种协议让MySQL通信简单快速,但是也从很多地方限制了MySQL.一个明显的限制是,没办法进行流量控制.一旦一段开始发送消息,另外一端要接收完整个消息才能响应它.
客户端用一个单独的数据包将查询传给服务器.这也是为什么当查询的语句很长的时候 ...
MySQL-慢查询
参考文献
高性能MySQL(第三版)
MySQL实战45讲.
慢查询
慢查询相关参数
slow_query_log:是否启用慢查询日志,默认为0,可设置为0或1,1表示开启.
slow_query_log_file:指定慢查询日志位置及名称,默认值为host_name-slow.log,可指定绝对路径.
long_query_time:慢查询执行时间阈值,超过此时间会记录,默认为10,单位为s.
log_output:慢查询日志输出目标,默认为file,即输出到文件[FILE|TABLE|NONE].
log_timestamps:主要是控制error log、slow log、genera log日志文件中的显示时区,默认使用UTC时区,建议改为SYSTEM系统时区.
log_queries_not_using_indexes:是否记录所有未使用索引的查询语句,默认为off.
min_examined_row_limit:对于查询扫描行数小于此参数的SQL,将不会记录到慢查询日志中,默认为0.
log_slow_admin_statements:慢速管理语句是否写入慢日志中,管 ...
MySQL(十六)-Explain用法说明
参考文献
MySQL EXPLAIN结果集分析 - 附带大量案例
MySQL 性能优化神器 Explain 使用分析
新特性解读 | MySQL 8.0:explain analyze 分析 SQL 执行过程
MySQL排错指南
https://dev.mysql.com/doc/refman/8.0/en/explain-output.html
MySQL Explain简介
EXPLAIN:查看SQL语句的执行计划
EXPLAIN命令可以帮助我们深入了解MySQL基于开销的优化器,还可以获得很多可能被优化器考虑到的访问策略的细节,以及当运行SQL语句时哪种策略预计会被优化器采用,在优化慢查询时非常有用;
执行explain之后结果集包含如下信息
123456+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+| id | select_type | table | partitions | ...
MySQL(十五)-主从复制
参考文献
MySQL 主从复制
主从复制简介
复制是 MySQL 的一项功能,允许服务器将更改从一个实例复制到另一个实例.
1)主服务器将所有数据和结构更改记录到二进制日志中.
2)从属服务器从主服务器请求该二进制日志并在本地应用其内容.
3)IO:请求主库,获取上一次执行过的新的事件,并存放到relaylog
4)SQL:从relaylog中将sql语句翻译给从库执行
主从复制的方式
目前MySQL支持两种复制方式:
传统方式
基于主库的binlong将日志事件和事件位置复制到从库,从库加以应用来达到主从同步的目的;
GTID方式(MySQL>=5.7推荐使用)
基于GTID的复制中,从库会告知主库已经执行的事务的GTID的值,然后主库会将所有未执行的事务的GTID的列表返回给从库,并且可以保证同一个事务只在指定的从库执行一次;
多种复制类型
异步复制
一个主库,一个或多个从库,数据异步同步到从库.
同步复制
在MySQL cluster中特有的复制方式.
半同步复制
在异步复制的基础上,确保任何一个主库上的事物在提交之前至少 ...
MySQL(十四)-优化
参考文献
3万字总结,Mysql优化之精髓
高性能MySQL(第三版)
为什么要优化
系统的吞吐量瓶颈往往出现在数据库的访问速度上
随着应用程序的运行,数据库的中的数据会越来越多,处理时间会相应变慢
数据是存放在磁盘上的,读写速度无法和内存相比
影响数据库性能的因素
SQL查询速度
大量的并发和超高的CPU使用率
大量的并发
数据库连接数被占满(max_connections默认100)
超高的CPU使用率:
因CPU资源耗尽而出现宕机
磁盘IO
磁盘IO性能突然下降(使用更快的磁盘设备)
其他大量消耗磁盘性能的计划任务(调整计划任务)
网卡流量
网卡IO被占满(1000Mb/8==100MB)
避免无法连接数据库
减少从服务器的数量
进行分级缓存
避免使用"select *"进行查询
分离业务网络和服务器网络
大表
记录行数巨大,单表超过千万行
表数据文件巨大,表数据文件超过10G
带来的问题
慢查询:很难在一定的时间内过滤出所需要的数据
对DDL的影响
建立索引需要很长时间
在MySQL版本<5.5建立 ...
MySQL(十三)-缓冲池
参考文献
极客时间–SQL必知必会(陈旸)
数据库缓冲池
缓冲池是如何读取数据的?
缓冲池管理器会尽量将经常使用的数据保存起来,在数据库在进行页读操作的时候,首先会判断该页是否在缓冲池中,如果存在就直接读取,如果不存在,就会通过内存或磁盘将页存放到缓冲池中,再进行读取.
缓存在数据库中的结构和作用如下图
执行SQL语句的时候更新了缓冲池的数据,那么这些数据是否会同步到磁盘上?
对数据库中的记录进行修改的时候,首先会修改缓冲池中页里面的记录信息,然后数据库会以一定的频率刷新到磁盘上.注意并不是发生更新操作,都会立即进行磁盘回写.缓冲池会采用与一种叫做checkpoint的机制将数据写到磁盘上,这样做的好处就是提升了数据库的整体性能.
当缓冲池不够用时,相应释放掉一些不常用的页,就可以强行采用checkpoint的方式,将不常用的脏页写会到磁盘上,然后再从缓冲池中将这些页释放掉.
脏页指的是缓冲池中被修改过的页,与磁盘上的数据页不一致.
查看缓冲池的大小
MySQL MyISAM存储引擎,它只缓存索引,不缓存数据,对应的键缓存参数为key_buffer_s ...
MySQL(十二)-分库分表
参考文献
MySQL:互联网公司常用分库分表方案汇总!
为什么要分表
不管是IO瓶颈,还是CPU瓶颈,最终都会导致数据库的活跃连接数增加,进而逼近甚至达到数据库可承载活跃连接数的阈值.在业务Service来看就是,可用数据库连接少甚至无连接可用.接下来就可以想象了吧(并发量、吞吐量、崩溃).
IO瓶颈
磁盘读IO瓶颈,热点数据太多,数据库缓存放不下,每次查询时会产生大量的IO,降低查询速度 -> 分库和垂直分表.
网络IO瓶颈,请求的数据太多,网络带宽不够 -> 分库.
CPU瓶颈
SQL问题,如SQL中包含join,group by,order by,非索引字段条件查询等,增加CPU运算的操作 -> SQL优化,建立合适的索引,在业务Service层进行业务计算.
单表数据量太大,查询时扫描的行太多,SQL效率低,CPU率先出现瓶颈 -> 水平分表.
分库分表
水平分库
概念:
以字段为依据,按照一定策略(hash、range等),将一个库中的数据拆分到多个库中
将单张表的数据切分到多个服务器上去,每个服务器具有相应的库与表 ...
MySQL(十)-事务
参考文献
极客时间–MySQL实战45讲(林晓斌)
极客时间–SQL必知必会(陈旸)
解决死锁之路 - 学习事务与隔离级别
MySQL日志15连问,redo log与biglog
数据库事务隔离发展历史
MVCC 原理
图文解读MySQL InnoDB Undo log
The basics of the InnoDB undo logging and history system
ACID
Atomicity原子性
事务是最小的执行单位,不允许分割.事务的原子性确保动作要么全部完成,要么完全不起作用;
Consistency 一致性
一致性指事务将数据库从一种状态变为下一种一致的状态.在事务开始之前和事务结束以后,数据库的完整性约束没有被破坏.
例如数据库表中有个字段有唯一性约束,事务中对该字段进行了修改,但在事务提交或事务操作发生回滚后,该字段的值变得不唯一了,这就破坏了事务的一致性要求,即事务将数据库从一种状态变为了一种不一致的状态.
事务的一致性的单位,如果事务中某个动作失败了,系统可以自动撤销事务–返回初始化的状态.
Isolation 隔离性
...
MySQL(九)-索引(二)
参考文献
高性能MySQL(第三版)
极客时间–MySQL实战45讲
数据库索引设计与优化
表和索引结构
索引页和表页
表和索引行都被存储在页中.页的大小仅仅决定了一个页可以存储多少个索引行,表行,以及一共需要多少个页来存储表或者索引.
当表和索引被加载或重组时,每个页都会预留出一定比例的空闲空间,以满足向其添加新的表行或索引行的需求.
缓冲池和I/O活动都是基于页的.例如一次将一个完整的页读取到缓冲池.这意味着一次I/O会读入多条记录到缓冲池,而不仅仅是一条.
索引行
索引行在评估访问路径的时候是一个非常拥有的概念.
对于唯一索引,一个索引行等同于叶子页中的一个索引条目.字段的值从表中复制到索引上,并加上一个指向表中记录的指针.通常,表页的编号是这个指针的组成部分.
对于非唯一索引,一个特定的索引值对应的索引行一个被想象成独立的索引条目,每一个都含有相同的值,但是却有不同的指针.大多数情况下,非唯一索引的实际存储方式是一个值后带着多个指针.
索引结构
非叶子页通常包含着一个键值,以及一个指向下一层级的指针,该键值是下一层级页中的最大键值.
表行
每一个索 ...