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实战45讲 MySQL查询语句的执行流程 大体来说,MySQL可分为服务层和存储引擎层两部分 Server层包括连接器,查询缓存,分析器,优化器,执行器等,涵盖MySQL的大多数核心服务功能,以及所有的内置函数(如日期,时间,数字和加密函数等) 而在存储引擎层负责的数据存储和提取.其架构模式是插件式的,支持InnoDB,MyISAM,Memory等多个存储引擎.现在最常用的存储引擎是InnoDB,它是从MySQL5.5.5版本看是称为默认存储引擎. 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 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 的一项功能,允许服务器将更改从一个实例复制到另一个实例. 1）主服务器将所有数据和结构更改记录到二进制日志中. 2）从属服务器从主服务器请求该二进制日志并在本地应用其内容. 3）IO：请求主库,获取上一次执行过的新的事件,并存放到relaylog 4）SQL：从relaylog中将sql语句翻译给从库执行 主从复制的方式 目前MySQL支持两种复制方式: 传统方式 基于主库的binlong将日志事件和事件位置复制到从库,从库加以应用来达到主从同步的目的; GTID方式(MySQL>=5.7推荐使用) 基于GTID的复制中,从库会告知主库已经执行的事务的GTID的值,然后主库会将所有未执行的事务的GTID的列表返回给从库,并且可以保证同一个事务只在指定的从库执行一次; 多种复制类型 异步复制 一个主库,一个或多个从库,数据异步同步到从库. 同步复制 在MySQL cluster中特有的复制方式. 半同步复制 在异步复制的基础上,确保任何一个主库上的事物在提交之前至少 ...

参考文献 3万字总结,Mysql优化之精髓 高性能MySQL(第三版) 为什么要优化 系统的吞吐量瓶颈往往出现在数据库的访问速度上 随着应用程序的运行,数据库的中的数据会越来越多,处理时间会相应变慢 数据是存放在磁盘上的,读写速度无法和内存相比 影响数据库性能的因素 SQL查询速度 大量的并发和超高的CPU使用率 大量的并发 数据库连接数被占满(max_connections默认100) 超高的CPU使用率: 因CPU资源耗尽而出现宕机 磁盘IO 磁盘IO性能突然下降(使用更快的磁盘设备) 其他大量消耗磁盘性能的计划任务(调整计划任务) 网卡流量 网卡IO被占满(1000Mb/8==100MB) 避免无法连接数据库 减少从服务器的数量 进行分级缓存 避免使用"select *"进行查询 分离业务网络和服务器网络 大表 记录行数巨大,单表超过千万行 表数据文件巨大,表数据文件超过10G 带来的问题 慢查询:很难在一定的时间内过滤出所需要的数据 对DDL的影响 建立索引需要很长时间 在MySQL版本<5.5建立 ...

参考文献 极客时间–SQL必知必会(陈旸) 数据库缓冲池 缓冲池是如何读取数据的? 缓冲池管理器会尽量将经常使用的数据保存起来,在数据库在进行页读操作的时候,首先会判断该页是否在缓冲池中,如果存在就直接读取,如果不存在,就会通过内存或磁盘将页存放到缓冲池中,再进行读取. 缓存在数据库中的结构和作用如下图 执行SQL语句的时候更新了缓冲池的数据,那么这些数据是否会同步到磁盘上? 对数据库中的记录进行修改的时候,首先会修改缓冲池中页里面的记录信息,然后数据库会以一定的频率刷新到磁盘上.注意并不是发生更新操作,都会立即进行磁盘回写.缓冲池会采用与一种叫做checkpoint的机制将数据写到磁盘上,这样做的好处就是提升了数据库的整体性能. 当缓冲池不够用时,相应释放掉一些不常用的页,就可以强行采用checkpoint的方式,将不常用的脏页写会到磁盘上,然后再从缓冲池中将这些页释放掉. 脏页指的是缓冲池中被修改过的页,与磁盘上的数据页不一致. 查看缓冲池的大小 MySQL MyISAM存储引擎,它只缓存索引,不缓存数据,对应的键缓存参数为key_buffer_s ...

参考文献 MySQL:互联网公司常用分库分表方案汇总！ 为什么要分表 不管是IO瓶颈,还是CPU瓶颈,最终都会导致数据库的活跃连接数增加,进而逼近甚至达到数据库可承载活跃连接数的阈值.在业务Service来看就是,可用数据库连接少甚至无连接可用.接下来就可以想象了吧(并发量、吞吐量、崩溃). IO瓶颈 磁盘读IO瓶颈,热点数据太多,数据库缓存放不下,每次查询时会产生大量的IO,降低查询速度 -> 分库和垂直分表. 网络IO瓶颈,请求的数据太多,网络带宽不够 -> 分库. CPU瓶颈 SQL问题,如SQL中包含join,group by,order by,非索引字段条件查询等,增加CPU运算的操作 -> SQL优化,建立合适的索引,在业务Service层进行业务计算. 单表数据量太大,查询时扫描的行太多,SQL效率低,CPU率先出现瓶颈 -> 水平分表. 分库分表 水平分库 概念: 以字段为依据,按照一定策略(hash、range等),将一个库中的数据拆分到多个库中 将单张表的数据切分到多个服务器上去,每个服务器具有相应的库与表 ...

参考文献 极客时间–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实战45讲 数据库索引设计与优化 表和索引结构 索引页和表页 表和索引行都被存储在页中.页的大小仅仅决定了一个页可以存储多少个索引行,表行,以及一共需要多少个页来存储表或者索引. 当表和索引被加载或重组时,每个页都会预留出一定比例的空闲空间,以满足向其添加新的表行或索引行的需求. 缓冲池和I/O活动都是基于页的.例如一次将一个完整的页读取到缓冲池.这意味着一次I/O会读入多条记录到缓冲池,而不仅仅是一条. 索引行 索引行在评估访问路径的时候是一个非常拥有的概念. 对于唯一索引,一个索引行等同于叶子页中的一个索引条目.字段的值从表中复制到索引上,并加上一个指向表中记录的指针.通常,表页的编号是这个指针的组成部分. 对于非唯一索引,一个特定的索引值对应的索引行一个被想象成独立的索引条目,每一个都含有相同的值,但是却有不同的指针.大多数情况下,非唯一索引的实际存储方式是一个值后带着多个指针. 索引结构 非叶子页通常包含着一个键值,以及一个指向下一层级的指针,该键值是下一层级页中的最大键值. 表行 每一个索 ...