MySQL

参考文献 何登成–管中窥豹——MySQL(InnoDB)死锁分析之道 手把手教你解数据库死锁 Mysql并发时经典常见的死锁原因,Mysql死锁问题分析及解决方法 并发insert死锁验证 死锁和死锁检测 当并发系统在不同现场出现循环资源依赖,涉及的线程都在等待别的线程释放资源时,就会导致这几个线程都进入无限等待状态,称为死锁. 死锁出现的条件 多个并发事务(两个或者两个以上的事务); 每个事务都持有锁(或者是已经在等待锁); 每个事务都需要再继续持有锁(为了完成事务逻辑,还必须更新更多的行); 事务之间产生加锁的循环等待,形成死锁. 若A事务需要B的资源,B事务需要A的资源,这就是典型的AB-BA死锁. 死锁的危害 死锁,即表明有多个事务之间需要互相争夺资源而互相等待。 如果没有死锁检测,那么就会互相卡死,一直耗死 如果有死锁检测机制,那么数据库会自动根据代价来评估出哪些事务可以被回滚掉,用来打破这个僵局 所以说:死锁并没有啥坏处,反而可以保护数据库和应用 那么出现死锁,而且非常频繁,我们应该调整业务逻辑,让其避免产生死锁方为上策 MySQL出现死 ...

参考文献 MySQL锁系列(一)之锁的种类和概念 15.7.1 InnoDB Locking MySQL · 引擎特性 · InnoDB隐式锁功能解析 MySQL 中锁的各种模式与类型 MySQL技术内幕 InnoDB存储引擎 15.6.1.6 AUTO_INCREMENT Handling in InnoDB 锁的种类 在数据库中,通常使用锁机制来协调多个线程并发访问某一资源.MySQL的锁类型分为表锁和行锁,表示对整张表加锁,主要用在DDL场景中,也可以由用户指定,主要由server层负责管理； 而行锁指的是锁定某一行或几行,或者是行与行之间的间隙,行锁由存储引擎管理,例如最常使用的InnoDB. 表锁占用系统资源小,实现简单,但锁定粒度大,发生锁冲突概率高,并发度比较低.行锁占用系统资源大,锁定粒度小,发生锁冲突概率低,并发度比较高. InnoDB将锁分为**锁模式(lock_mode)和锁类型(lock_type)**两类.锁模式通常和锁类型结合使用. 锁类型描述了锁的粒度,也就是把锁具体加到什么地方. 锁类型包括表锁和行锁,而行锁还细分为记录锁、间隙锁、插 ...

参考文献 高性能MySQL(第三版) MySQL是怎样运行的 字符集和比较规则 字符集是指一种二进制编码到某类字符符号的映射. 比较规则是指一组用于某个字符集的排序规则. MySQL有很多的选项用于控制字符集.这些选项和字符集很容易混淆,一定要记住:只有基于字符的值才真正的"有"字符集的概念.对于其他类型的值,字符集只是一个设置,指定用哪一种字符集来做比较或者其他操作.基于字符的值能存放在某列中,查询的字符串中,表达式的计算结果中或者某个用户变量中,等等. MySQL的设置可以分为两类: 创建对象时的默认值; 在服务器和客户端通信时的设置; 一些重要的字符集 ASCII字符集:共收录128个字符集,包括空格,标点符号,数字,大小写字母和一些不可见字符. ISO 8859-1字符集: 共收录256个字符,它在ASCII字符集的基础上又扩充了128个西欧常用字符.ISO 8859-1字符集也可以使用一个字节来进行编码(这个字符集也有一个别名Latin1) GB2312字符集: 这个字符集同时又兼容ASCII字符集,所以在编码方式上显得有些奇怪: 如果该字 ...

参考文献 高性能MySQL(第三版) 分区 MySQL在创建表时使用PARTITION BY子句定义每个分区存放的数据.在执行查询的时候,优化器会根据分区定义过滤那些没有我们需要数据的分区,这样查询就无需扫描所有分区,只需要查找包含需要数据的分区就可以了. 在下面的场景中,分区可以起到非常大的作用: 表非常大以至于无法全部都放在内存中,或者只在表的最后部分有热点数据,其他均是历史数据. 分区表的数据更容易维护.例如,想批量删除大量数据可以使用清除整个分区的方式,另外,还可以对一个独立分区进行优化,检查,修复等操作. 分区表的数据可以分布在不同的物理设备上,从而高效地利用多个硬件设备. 可以使用分区表来避免某些特殊的瓶颈,例如InnoDB的单个索引的互斥访问,ext3文件系统的inode锁竞争等. 如果需要,还可以备份和恢复独立的分区,这在非常大的数据集的场景下效果非常好. 分区表本身也有一些限制 一个表最多只能有1024分区. 在MySQL5.1中,分区表达式必须是整数,或者是返回整数的表达式.在MySQL5.5中,某些场景中可以直接是用列来进行分区. 如果分区字段中有主 ...

参考文献 高性能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技术内幕 InnoDB存储引擎 慢查询 慢查询日志(slow query log) 慢查询日志可以帮助定位存在问题的SQL语句,从而进行SQL语句层面的优化.在MySQL启动时,设置一个阈值,将运行超过这个值的所有SQL语句都会记录到慢查询日志文件中.该阈值可以通过参数long_query_time来设置,默认值为10,单位为秒. 1234567891011121314151617181920212223mysql> show variables like 'long_query_time';+-----------------+-----------+| Variable_name | Value |+-----------------+-----------+| long_query_time | 10.000000 |+-----------------+-----------+1 row in set (0.01 sec)mysql> show variab ...

参考文献 高性能MySQL(第三版) MySQL实战45讲 MySQL查询语句的执行流程 大体来说,MySQL可分为服务层和存储引擎层两部分 Server层包括连接器,查询缓存,分析器,优化器,执行器等,涵盖MySQL的大多数核心服务功能,以及所有的内置函数(如日期,时间,数字和加密函数等) 而在存储引擎层负责的数据存储和提取.其架构模式是插件式的,支持InnoDB,MyISAM,Memory等多个存储引擎.现在最常用的存储引擎是InnoDB,它是从MySQL5.5.5版本看是称为默认存储引擎. MySQL通信协议 MySQL客户端和服务端之间的通信协议是半双工的,这意味着,在任何一个时刻,要么是由服务器向客户端发送数据,要么是有客户端向服务器发送数据,这两个动作不能同时发生,所以我们无法将一个消息切成小块独立来发送. 这种协议让MySQL通信简单快速,但是也从很多地方限制了MySQL.一个明显的限制是,没办法进行流量控制.一旦一段开始发送消息,另外一端要接收完整个消息才能响应它. 客户端用一个单独的数据包将查询传给服务器.这也是为什么当查询的语句很长的时候 ...

参考文献 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的列表返回给从库,并且可以保证同一个事务只在指定的从库执行一次; 多种复制类型 异步复制 一个主库,一个或多个从库,数据异步同步到从库. 用于非核心业务场景,不要求数据一致性 半同步复制 在异步复制的基础上,确保任何一个主库上的事物在提交之前至少有一个从库已经收到该事物 ...

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