星沙网站建设,软文小故事200字,营销系统网站源码,衡阳市住房和城乡建设网站学习目标#xff1a;
高性能Mysql第3版
学习内容#xff1a;
MySQL架构与历史Mysql基座测试服务器性能Schema与数据类型优化创建高性能的索引查询性能优化Mysql高级特性Explain 学习时间#xff1a;
周一至周五晚上 9点—晚上10点周六晚上9点-10点周日晚上9 点-10点 学习…学习目标
高性能Mysql第3版
学习内容
MySQL架构与历史Mysql基座测试服务器性能Schema与数据类型优化创建高性能的索引查询性能优化Mysql高级特性Explain 学习时间
周一至周五晚上 9点—晚上10点周六晚上9点-10点周日晚上9 点-10点 学习产出 总结每章内容 连续输出CSDN文档并会提供其他C友的链接进行参考 文章目录 学习目标高性能Mysql第3版 学习内容学习时间学习产出1 Mysql逻辑架构1.1 Mysql逻辑架构1.1.1 连接管理与安全性1.1.2 性能优化和执行 1.2 并发控制1.2.1 锁粒度 1.3 事务1.3 .1 隔离级别1.3.2 死锁1.3.3事务日志1.4 多版本并发控制(MVCC)1.5 Mysql存储引擎1.5.1 InnoDB概述1.5.2 MyISAM概述 1.6 Mysql压缩表1.7 Mysql其他存储引擎1.8 第三方存储引擎1.8.1 选择合适的引擎1.8.2 转换表的引擎 1.9 Mysql的数据线1.10 Mysql的开发模式1.11 总结 2 基准测试2.1 为什么需要基准测试2.2 基准测试的策略2.3 测试指标2.4 设计和规划基准测试2.5 获取系统性能和状态2.6 获取基于结果测试2.7 运行基准测试并分析结果2.8 基准测试工具2.9 基准测试案例Percona的TPCC-MySQL测试工具2.10 总结 3 服务性能优化解析3.1 性能优化简介3.1.1 通过性能剖析进行优化3.1.2 理解性能剖析 3.2 对应用程序进行性能解析3.3 剖析Mysql查询3.3.1 使用性能剖析 3.4 诊断间歇性问题3.4.1 解决间接性问题的方法使用 show global status 3.5 使用show processlist3.6 使用查询日志3.7 理解发现的问题Making sense of the findings3.8 捕获诊断数据3.8.1 诊断触发器3.8.2 需要收集什么样的数据3.8.3 解释结果数据 3.9 其他剖析工具3.9.1 使用user_statistics表3.9.2 使用strace 3.10 总结 4 Schema与数据类型优化4.1 选择可优化的数据类型4.1.1 整数类型4.1.2 整数4.1.3 实数4.1.4 字符串类型4.1.5 日期和时间类型4.1.6 位数据类型4.1.7 选择标识符4.1.8 特殊类型数据 4.2 MySQL schema设计中的陷阱4.3 范式和反范式4.4 缓存表和汇总表4.4.1 物化视图4.4.2 计数器表 4.5 加快ALTER TABLE操作的速度4.5.1 只修改.frm文件4.5.2 快速创建MyISAM索引 4.6 总结 5 创建高性能的索引5.1 索引基础5.1.1 索引的类型5.1.2 B-Tree5.1.3 BTree5.1.4 哈希索引5.1.5 空间数据索引R-Tree5.1.6 全文索引5.1.7 其他索引类别 5.2 索引的优点和缺点5.3 高性能的索引策略5.3.1 索引选择性和前缀索引5.3.2 多列索引5.3.3 选择合适的索引列顺序5.3.4 聚簇索引5.3.5 覆盖索引5.3.6 使用索引扫描来做排序5.3.7 压缩前缀压缩索引5.3.8 冗余和重复索引5.3.9 未使用的索引5.3.10 索引和锁 5.4 索引案例学习5.4.1 支持多种过滤条件5.4.2 避免多个范围条件5.4.3 优化排序 5.5 维护索引和表5.5.1 找到并修复损坏的表5.5.2 更新索引统计信息5.5.3 减少索引和数据的碎片 5.6 总结 6 查询性能优化6.1 为什么查询速度会慢6.2 慢查询基础: 优化数据访问6.2.1 向数据库请求了不需要的数据?6.2.2 Mysql是否扫描了额外的数据 6.3 重构查询的方式6.3.1 一个复杂查询还是多个简单查询6.3.2 切分查询6.3.3 分解关联查询 6.4查询执行基础6.4.1 Mysql clien / server 通信协议6.4.1 查询缓存6.4.2 查询优化处理6.4.3 查询执行引擎6.4.4 返回结果给客户端 6.5 MySQL查询优化器的局限性6.5.1 关联子查询6.5.2 UNION的限制6.5.3 索引合并优化6.5.4 等值传递6.5.5 并行执行6.5.6 哈希关联6.5.7 最大值和最小值优化6.5.8 在同一个表上查询和更新 6.6 查询优化器的提示hint6.7 优化特定类型的查询6.7.1 优化COUNT()查询6.7.2 优化关联查询6.7.3 优化子查询6.7.4 优化GROUP BY和DISTINCT6.7.5 优化LIMIT分页6.7.6 优化SQL_CALC_FOUND_ROWS6.7.7 优化UNION查询6.7.8 静态查询分析6.7.9 使用用户自定义变量 6.9 总结 7 Mysql的高级特性7.1 分区表7.1.1 分区表的原理7.1.2 分区表的类型7.1.3 如何使用分区表7.1.4 什么情况下会出问题7.1.5 查询优化7.1.6 合并表在Mysql5.0版本之后被删除了 7.2 视图7.2.1 可更新视图7.2.2 视图对性能的影响7.2.3 视图的限制 7.3 外键约束7.4 在MySQL内部存储代码7.4.1 存储过程和函数7.4.2 触发器7.4.3 事件 7.5 游标7.6 绑定变量7.7 用户自定义函数7 .8 插件7.9 字符集和校对7.10 全文索引7.10.1 布尔全文索引 7.11 分布式XA事务7.12 查询缓存7.12.1 MySQL如何判断缓存命中7.12.2 查询缓存如何使用内存7.12.3 什么情况下查询缓存能发挥作用7.12.4 如何配置和维护查询缓存7.12.5 InnoDB和查询缓存7.12.6 通用查询缓存优化 7.13 总结附录D ExplainEXPLAIN案例EXPLAIN的两个变种重新SELECT语句 1 Mysql逻辑架构
1.1 Mysql逻辑架构 客户端这方面明显不是Mysql独有的大多数都是基于C/S架构即 Client/Server 架构服务器端程序使用的mysqld所以不论是客户端进程还是服务器端进程是采用哪种方式进行通信最后实现的效果都是: 客户端进程向服务端进程 发送一段SQL语句服务端进程处理后向客户端进程返回一段处理文本内容
第二层结构包含了Mysql的核心功能包含查询解析、分析、优化、缓存等功能、并且跨储存引擎的功能都在这层实现的比如 存储过程触发器视图等功能
第三层包括了存储引擎也就是底层数据存取操作实现的部分由多种存储引擎共同组成。它们负责存储和获取所有存储在MySQL中的数据。就像Linux众多的文件系统 一样。
1.1.1 连接管理与安全性
连接管路何安全性可以这样理解服务器内部每个Clinet都会连接的一个线程这个连接的查询就会被此线程单独执行并且这些线程都是轮流运行在某一个CPU内核(多核CPU)或者CPU中服务器缓存了线程就不需要在重新创建和销毁线程了(线程的复用机制) 当clients(application program连接到了MySQL服务器。服务器需要对它进行认证(Authenticate)。认证是基于用户名主机以及密码,如果使用到了SSL连接方式就要用到X.509证书clients一连接上服务器就验证它的权限
1.1.2 性能优化和执行
MySQL会解析查询并创建了一个内部数据结构解析树。然后对其进行各种优化。这些优化包括了查询语句的重写读表的顺序索引的选择等等。但优化要考虑的问题不止这些还要从多方面的环境去考虑比如要从硬件系统配置数据库表结构SQL及索引方面 硬件 CPU、内存、存储、网络设备等系统配置 服务器系统、数据库服务参数等数据库表结构高可用、分库分表、读写分离、存储引擎、表设计等Sql及索引 sql语句、索引使用等我们要优化的话就不止对一个单纯的环境进行优化并且会有很大的风险 但在优化时要一定考虑系统的数据安全性和处理高性能方面 附上链接 Mysql性能优化
1.2 并发控制
只要有多个查询需要在同一时刻修改数据都会产生并发控制的问题。本章目的讨论 MySQL 在两个层面的并发控制服务器层与存储引擎层。
存储引擎层存储引擎都可以实现自己的锁策略和锁粒度不同存储引擎实现锁机制方式不同。
并发控制一般都会涉及到事务。MySQL服务层不管理事务事务是由存储引擎实现的。同个事务中避免使用多种存储引擎在处理并发控制问题我们可以使用读写锁处理并发时读或者写时可以加读锁(共享锁)和写锁(排他锁)也可以通过Mysql的事务隔离级别来解决并发问题或者MVCC
1.2.1 锁粒度
为了解决加锁后如果系统出现了花大量的时间来管理锁而不是存储数据那么资源的消耗会很大影响系统的性能,为了让系统有更多的时间来存取数据Mysql锁粒度提供了两种锁策略分别是行级锁(row lock)和表锁(table lock) 表锁: 开销小加锁快不会出现死锁锁定力度大
行锁: 开销大加锁慢会出现死锁锁定粒度小
InnoDB行锁和表锁都支持、MyISAM只支持表锁
InnoDB只有通过索引条件检索数据才使用行级锁否则InnoDB使用表锁也就是说InnoDB的行锁是基于索引的
1.3 事务
事务是指一组SQL语句他们被当作一个独立的工作单元来执行,要么全部执行成功要让全部执行失败在这过程中发生错误的话已经执行的语句都要被撤销回滚到事务开始前的状态, Mysql中的事务具有ACID特性原子性、一致性、隔离性、持久性
原子性事务中的所有操作要么全部完成要么全部不完成. 一致性事务执行前后数据库的状态必须保持一致。 隔离性多个事务并发执行时每个事务都应该感觉不到其他事务的存在。 持久性事务完成后对数据库的修改应该永久保存。
事务的ACID特性可以确保数据库不会弄丢你的数据而根据线上环境中的应用逻辑中要完全实现这点很难因为一个完全兼容并实现ACID的数据库系统需要更强的CPU处理能力更大的内存和磁盘空间,所以很多情况下不会完全遵守ACID的特性来为系统节省开销, 这因为如此存储引擎就可以发挥优势了我们就可以根据业务是否需要事务处理来选择合适的存储引擎了对于一些不需要事务类的查询应用可以选择非事务型的存储引擎可以获取更高的性能了即便存储引擎不支持事务但可以通过 lock tables 提供一些保护
1.3 .1 隔离级别
为了达到事务的四大特性Mysql数据库定义了四种事务隔离级别: READ-UNCOMMITTED读未提交最低的隔离级别允许脏读也就是可能读取到其他会话中未提交事务修改的数据可能会导致脏读、幻读或不可重复读。
READ-COMMITTED读已提交 只能读取到已经提交的数据。Oracle 等多数数据库默认都是该级别 不重复读可以阻止脏读但是幻读或不可重复读仍有可能发生。
REPEATABLE-READ可重复读对同一字段的多次读取结果都是一致的除非数据是被本身事务自己所修改可以阻止脏读和不可重复读但幻读仍有可能发生。
SERIALIZABLE可串行化最高的隔离级别完全服从 ACID 的隔离级别。所有的事 务依次逐个执行这样事务之间就完全不可能产生干扰也就是说该级别可以防止脏读、不可重复读以及幻读。MySQL 默认采用的 REPEATABLE_READ 隔可重复读离级别。
1.3.2 死锁
死锁是指两个或者多个事务在同一资源上相互占用并请求锁定对方占用的资源从而导致恶性循环的现象。当多个事务试图以不同的顺序锁定资源时就可能会产生死锁。死锁发生以后只有部分或者完全回滚其中一个事务才能打破死锁
1.3.3事务日志
事务日志可以帮助我们提供事务的效率使用事务日志存储引擎在修改表的数据时只需要修改内存拷贝在把该修改行为记录到硬盘文件中的事务日志而不是每次都将修改的数据本身持久到磁盘内存中 附上链接
1.4 多版本并发控制(MVCC)
MVCC可以说是行级锁中的一个变种它在很多情况下避免了加锁操作因此开销更低虽然Mysql Oracle PosgreSql 实现的机制不同但大多数都实现了非阻塞的读操作写操作也只锁定必要的行。MVCC的实现其实是通过数据某个时间点的快照来实现的也就是说不管你执行多少时间每次看到的数据都是一致的, 每个事务对同一张表同一时段看到的数据有可能不一样前面提到MVCC实现的机制不同典型的有乐观/悲观并发控制我们可以通过InnoDb的行为来说明MVCC是怎么工作的MVCC通过在每一行数据后面加上两个隐藏的列来实现一个是创建时间戳一个是删除时间戳。当一个事务开始时它会记录当前的系统版本号并且只能看到在该版本号之前已经提交的数据。如果其他事务在该版本号之后修改了同一行数据那么该事务就需要回滚并重新开始。并且只有不可重复读和读已提交的隔离级别下才能兼容MVCC MVCC具体操作 Select innodb会根据两个条件检查每行记录 1. Innodb会查找早于当前事务多版本的数据行(行的版本号会小于等于事务的版本号)来保证事务开始之前已经存在了 2. 行的删除版本没定义 只有符合这两个二条件才能返回查询结果 Insert Innodb为插入的数据保存当前系统版本号作为行版本号 delete innodb为删除每一行保存当前版本号作为行删除标识 update innodb插入一条新记录会保存当前系统版本号行系统版本号
1.5 Mysql存储引擎
在文件系统中Mysql将每个数据库保存为数据目录下的一个子目录创表时Mysql会在数据库子目录创建一个和表同名的.frm文件来保存表的定义。 可以使用 show table status (mysql5.0版本以后可以使用 informmation_schema)命令显示表的相关信息。 使用 show TABLE status可以查看当前数据库中所有表的基本信息包括表名、引擎类型、行数、创建时间等。例如可以使用以下语句查看表 mytable 的基本信息
SHOW TABLE STATUS LIKE ‘mytable’; 使用 information_schema 可以查看更加详细的表结构信息包括列名、数据类型、约束条件等。例如可以使用以下语句查看表 mytable 的列信息
SELECT COLUMN_NAME, DATA_TYPE, COLUMN_DEFAULT, IS_NULLABLE, COLUMN_KEY, EXTRA FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_NAME ‘mytable’;‘’’
select TABLE_NAME from information_schema.TABLES where TABLE_SCHEMA‘test’ limit 5,1
1.5.1 InnoDB概述
InnoDB的数据存储在表空间中表空间是由InnoDB管理的一个黑盒子由一系列的数据文件组成在Mysql4.1以后的版本中InnoDB可以将每个表的数据和索引存放在单独的文件中InnoDB也可以使用一些设备来作为表空间的存储介质。 InnoDB采用的是MVCC来支持高并发的并实现了四个管理级别默认值是REPEATABLE READ(可重复度)并且通过间接锁来防止幻读的出现间接锁使得InnoDB不仅仅锁定了查询涉及的行还会对索引中的间接锁进行锁定用来防止幻影行的插入.
1.5.2 MyISAM概述
Mysql5.1版本之前默认的是MySAM存储引擎为什么在Mysql5.1之后的版本中不推荐使用MySIM了 除了不支持事务和行级锁外最主要的原因是崩溃后无法安全恢复数据在存储方面MySAM会将表存储在两个文件中(数据文件 、索引文件)分别以.MYD和.MYI为扩展名。 MySAM是最早的存储引擎之一它包含了很多年的特性可以满足用户的一些实际需求
加锁与并发对整张表加锁。读时会对需读到的所有表加共享锁写入时则对表加排他锁。 修复对于MyISAM表MySQL可以手工或者自动执行检查和修复操作非崩溃恢复 索引特性对BLOB和TEXT等长字段也可基于其前500个字符创建索引支持全文索引支持复杂的查询 延迟更新索引键Delayed Key Write不立刻将修改的索引数据写入磁盘而是写到内存中的键缓冲区只在清理键缓冲区或者关闭表的时候将对应的索引块写入到磁盘。
1.6 Mysql压缩表
表在创建并导入数据后不会进行改操作的场景就可以使用MyISAM压缩表可以使用myisapack对MyISAM表进行压缩压缩后可以减少磁盘的I/O提高查询性能
1.7 Mysql其他存储引擎
Archive引擎支持INSET和SELECT操作支持行级锁和缓冲区但是每次SELECT查询都要全表扫描所以Archive表适合日志和数据采集的作用.
Blackhole引擎会丢弃所用的插入数据服务器会记录Blackhole表的日志所用一般用于数据复制的备库.
CSV引擎可将普通的CSV文件作为MySQL的表来处理,可在数据库运行时拷入或者拷出文件。可以将Excel等电子表格软件中的数据存储为CSV文件然后复制到MySQL数据目录下就能在MySQL中打开使用,可以作为一种数据交换的机制.
1.8 第三方存储引擎
OLTP类引擎支持MVCC、ACID、事务 Percona的XtraDB存储引擎是基于InnoDB引擎的一个改进版本主要改进点在性能方面可以兼容的读写InnoDB的数据文件和所用查询
TokuDB引擎使用了一种新的叫做分形树(Fractal Trees)的索引数据结构。该结构是缓存无关的因此即使其大小超过内存性能也不会下降也就没有内存生命周期和碎片的问题
1.8.1 选择合适的引擎
无特定需求使用默认InnoDB.。有特殊需求如只涉及insert/select考虑使用MyISAM。其他场景结合存储引擎特性考虑
1.8.2 转换表的引擎
三种方式 直接ALTER TABLE: 语法简单但消耗系统的所有I/O能力 导出与导入先导出结构和数据改表名和存储引擎再执行。相当于新建表 创建与查询CREATE SELECT: 先创建新表结构再用INSERT SELECT语法复制数据。还是新建表方法
1.9 Mysql的数据线
版本323 2001: 引入MylSAM代EISAM引擎 版本40 2003: 支持新的浯法重写了复制lnnoDB成为标配查询缓存 版本41 2005: 引入了更多新的语法 版本50 2006: 一些“企业级特性 视图、触发器、存储过程和存储函数 版本51 2008:入了分区、基于行的复制事务存储弓摩 版本55 2010: 改善、性能、扩展性、复制、分区、对Windows的支持 版本56查询优化器的改进
1.10 Mysql的开发模式
开发测试的版本只是为了获取数据和反馈GA版本才是可用的版本。 Mysql遵循GPL协议全部的源代码(除了一些商业版本插件)都会开放给社区
1.11 总结
mysql分层的结构上层是服务器层和查询执行引擎下层是存储引擎。服务器层通过API调用存储引擎层不同存储引擎对数据的存储方式以及API的实现方式不同。 存储引擎发展简述ISAM - MyISAM - InnoDB
2 基准测试
2.1 为什么需要基准测试
基准测试是对针对系统设计的一种压力测试可以测试当前系统在各种运行时的情况比如可以测试出系统的在什么压力下到达系统的扩展瓶颈和验证出不同的硬件、软件、操作系统的配置会对我们读写数据的性能由什么帮助最后证明我们买的新设备配置是否正常。
2.2 基准测试的策略
集成式 要测试整个应用系统包含Web服务器应用代码、网络和数据不过整体的应用基准测试很难搞至少本博主没有测试过单组件式: 针对某个组件的测试可以是Mysql、Redis、服务器等
2.3 测试指标
看吞吐量、响应时间和延迟、并发行、可扩展性。 吞吐量单位时间内的事务处理数。 响应时间和延迟测试任务的整体时间。 并发行很多人都有个误解本人也有这样的误解常常把它表示成多少个用户量在同一时间浏览一个Web站点经常使用的指标是多少会话可是HTTP协议是无状态的大多数用户只是在简单的读取浏览器上显示的信息这并不是Web服务器的并发性而且Web服务器的并发现并不等于数据库的并发性但在不同环节下的并发性Web服务器的高并发一般也会导致数据库的高并发所以并发性基准测试要注意的是正在工作中的并发操作就是工作中的核心线程和连接数但我们并发增加时需要测试吞吐量(下降)和响应时间(变长)如果是这样就有可能无法处理峰值压力。 可扩展性理想状态下给系统一倍的资源(比如提升硬件、服务器等)获取两倍的吞吐量
2.4 设计和规划基准测试
首先需要大量的生产数据集快照、如果针对查询可以在高峰期间的一小时内进行记录测试
2.5 获取系统性能和状态
在执行基准测试前需要收集系统的一些信息(CPU使用率、磁盘I/O、网络流量统计等)在测试前最好为基准测试建立一个目标用来保存测试结果、配置文件、测试指标、脚本等。 收集Mysql测试数据的脚本
2.6 获取基于结果测试
获得准确测试结果的最好办法是回答一些关于基准测试的基本问题是否选择了正砖 的基准测试是否为问题收集了相关的数据是否采用了错误的测试标准; 基于MySQL的默认配置的测试没有什么意义因为默认配置是基于消耗很少内存的极小应用的所以MySQL之前一直被认为是小型应用专用数据库
2.7 运行基准测试并分析结果
1.可以使用自动化(易操作、易分析、易采取) 2。QPS:数据库每秒查询次数 TPS: 数据库每秒执行事务数
2.8 基准测试工具
集成式测试工具(针对整个应用系统) ab 测试Http服务器每秒可处理请求数 http_load同为Http服务器比ab更灵活 JMeterJava应用程序加载其他应用并测试其性能 单组件式测试工具针对MySQL和基于MySQL的系统进行测试 mysqlslap模拟服务器负载输出计时信息 MySQL Benchmark Suite在不同数据库服务器上进行比较测试 Database Test Suite工业标准测试 Percona’s TPCC-MySQL Tool评估大压力时MySQL的一些行为 sysbench多线程系统压测工具评估系统性
2.9 基准测试案例
1 http_load
基于Linux平台的软件。 安装地址 完成后将文件解压 tar xzvfhttp_load-12mar2006.tar.gz 进入文件编译需要C环境 make install 写一个文件里面放入的为要测试的网址 如下为书中给出的网址 http://www.mysqlperformanceblog.com/ http://www.mysqlperformanceblog.com/page/2/ http://www.mysqlperformanceblog.com/mysql-patches/ http://www.mysqlperformanceblog.com/mysql-performance-presentations/ http://www.mysqlperformanceblog.com/2006/09/06/slow-query-log-analyzes-tools/ 然后可以执行如下命令 ./http_ load -parallel 并发数目 -seconds 时长 文件名 ./http_ load -rate 频率 -seconds 时长 文件名
-parallel 并发进程数。
-fetches 总计访问次数
-rate 每秒访问频率
-seconds 总计访问时间2 sysbench sysbench可以执行多种类型的基准测试它不仅设计用来测试数据库的性能也可以 测试运行数据库的服务器的性能
Percona的TPCC-MySQL测试工具
尽管sysbench的测试很简单并且结果也具有可比性但毕竟无法模拟真实的业务压 力。相比而言TPC-C测试则能模拟真实压力。
2.10 总结
首先正确地描述问题之后选择合适的基准测试来回答问题。设置基准测试的持续时间和参数运行测试收集数据分析结果数据
3 服务性能优化解析
3.1 性能优化简介
因为关于性能问题5个人回答都会有不同的答案所以本章是把性能定义成响应时间这里有有个原则我们是通过任务和时间来测量性能的而不是就是搞资源来测量性能因为数据库服务器的目的是执行SQL语句所以它关注的是查询或者语句哪如何优化性能了我们可以假设下就是在一定工作负责的情况下降低响应时间,比如很多人认为性能优化是降低CPU的利益率那就可以减少对资源的使用率但这里有个误区资源是用来消耗并来工作中的所以有时会消耗更多的资源是可以加快查询速度但是那是老版本的Mysql的InnoDB引擎了如果升级后那么CPU利用率会上升的很快这不会代表性能出现了问题只是说明了InnoDB对资源的利用率上升了因为升级后会出现一些bug所以不能用CPU利用率来度量目标如果想要降低响应时间就要理解服务器执行查询为什么需要怎么多时间后在解决对获取查询结果不必要的工作就引出了无法度量就无法优化,在优化是很少人会把精力放在精确的测量上性能优化 测量 修改完成一项任务需要执行时间等待时间所以任务时间 执行时间 等待时间。
3.1.1 通过性能剖析进行优化
性能剖析是种方法用于测量和分析时间花费在哪里。 两个步骤 1、测量任务所花费的时间 2、对结果进行统计和排序 性能剖析工具的工作方式基本相同在任务开始时启动计时器在任务结束时停止计时器两者相减得到响应时间。 讨论两种类型的性能剖析基于执行时间的分析和基于等待的分析。
3.1.2 理解性能剖析
MySQL的性能剖析(profile)将会将最重要的任务展示在前面但有时候没显示出来的信息也很重要
值得被优化的查询优化的成本大于收益就要停止优化异常情况某些功能即使没有出现在性能剖析的报告中也需要优化因为每次执行它都非常慢会严重影响用户体检未知的未知“丢失的时间”任务的总时间和实际测量到的时间之间的差。被掩藏的细节性能剖析会隐藏很多信息而且无法表达全部情况直方图、百分比、标准差、偏差指数等较大波动因取平均值会被隐藏
3.2 对应用程序进行性能解析
Mysql服务器的性能解析推荐系统自上而下进行这样可以追踪用户发起到服务器响应的整个流程, 虽然性能问题大多数情况下都和数据库有关但应用导致的性能问题也不少。 性能瓶颈可见的因素 外部资源比如调用了外部的Wb服务或者搜索引擎。 应用需要处理大量的数据比如分析一个超大的XML文件。 在循环中执行昂贵的操作比如滥用正则表达式。 使用了低效的算法比如使用暴力搜索算法(naive search algorithm)来查找列表中的项。 建议在所有的新项目中都考虑包含性能剖析的代码。性能剖析收益 成本,建议使用new Relic
3.3 剖析Mysql查询
MySQL查询性能剖析两种方式
剖析整个数据库服务器、 使用慢查询日志和通用日志用来记录执行服务器上的所用的查询但建议用慢查询日志好点因为可以在业务高峰期内记录一个小时的查询如果业务没在高峰期也可以在短时间内捕获需要优化的低效SQL语句生成剖析报告pt-query-digest对这些查询进行单独的剖析。 使用show profile(比较稳定的查询剖析工具)默认是禁用的但可以通过 set profiling 1(可以在服务器上进行会话/全局级别) 进行修改 SHOW STATUS命令返回了一些计数器。既有服务器级别的全局计数器 使用慢查询和Performance Schema
3.3.1 使用性能剖析
好的剖析报告能够将潜在的问题显示出来但最终的解决方案还需要用户来决定尽管报告可能会给出建议。
优化查询时用户需要对服务器如何执行查询有较深的了解。剖析报告能够尽可能多地收集需要的信息、给出诊断问题的正确方向以及为其他诸如EXPLAIN等工具提供基础信息。
3.4 诊断间歇性问题
间歇性的问题比如系统偶尔停顿或者慢查询很难诊断。只有在问题发生的地方通过观察资源的使用情况并尽可能地测量出数据才能避免在没有问题的地方耗费精力。
3.4.1 解决间接性问题的方法
单条查询问题 指服务器整体没有问题只是某条查询变慢。服务器问题 指系统里的所用功能和接口查询突然表慢又突然表好。如果出现了服务器问题可以试试升级Mysql版本。 那如何判断是是单查询问题和服务器问题可以通过三种技术。
使用 show global status
它是已较高的效率执行show global status 命令来获取数据有问题时可以通过某些计算器(如Threads_running,Threads_connected、Questions和Queries)来发现。 列
$ mysqladmin ext -i1 | awk /Queries/{q$4-qp;qp$4} /Threads_connected/{tc$4} /Threads_running/{printf %5d %5d %5d\n, q, tc, $4}这命令可以每秒捕获一次 show global status 的数据并输出给awk计算出结果每秒的查询数、Threads_connected、Threads_running并可以长时间运行后将结果绘制出图像来更加准确的定位问题
3.5 使用show processlist
这个方法可以不断地捕获show processlist的输出来观察大量的线程是否处于正常状态的特性在使用show processlist命令时可以在结尾加上\G来显示输出结果
$ mysql -e SHOW PROCESSLIST\G | grep State: | sort | uniq -c | sort -rn3.6 使用查询日志
如果通过查询日志发现问题需要开启查询日志并在全局设置long_query_time为0并确认连接都采用了新的设置附上链接
3.7 理解发现的问题Making sense of the findings
可视化的数据最具有说服力。诊断问题时先使用前两种开销低的方法SHOW STATUS和SHOW PROCESSLIST并结合绘图展示。 在尝试解决其他人提出的问题之前要先明确两件是。并记录下来
问题是什么一定要清晰的描述出来但花时间去解决一个错误的问题是常有的事情(注在公司里不可能给你花时间用在错误的方法上所以一般是先解决问题然后自己在花时间复盘下)为解决这个问题用过什么方法做个什么操作
3.8 捕获诊断数据
当出现间歇性问题时需要尽可能多地收集所有数据而不只是问题出现时的数据。需要搞清楚两件事
3.8.1 诊断触发器
选择一个参数的适当阀值做为收集数据的开始时间点。
选择一个合适的阈值很重要既要足够高以确保在正常时不会被触发又不能太高要确保问题发生时不会错。
3.8.2 需要收集什么样的数据
现在需要收集什么样的数据呢尽可能收集所有能收集的数据包括系统的状态、CPU利用率、磁盘使用率和可用空间、ps的输出采样内存利用率以及可以从Mysql获取信息如status processlist, show innodb status。可以用pt-collect工具来收集数据一般都是通过pt-stalk来调用因为要收集重要数据一般都是用root权限启动后会自动收集30秒的数据然后退出
3.8.3 解释结果数据
如果已经正确地设置好触发条件并且长时间运行pt-stalk则只需要等待足够长的时间来捕获几次问题就能够得到大量的数据来进行筛选。
3.9 其他剖析工具
基本在工作中用到的不多除非在一些特定的场景下但可以了解下。
3.9.1 使用user_statistics表
Percona Server 和MaiaDB都引入了一些对象级别的统计表 informtaion_schema本是由Google开发的在一些情况下DBA很少有开发话语权那就可以通过这些表实现对数据库的测量和统计就可有强制执行的策略并且可查找到用的索引最多和最少的表也可以找到没用过的索引考虑要不要删除在Performmance Schema中有很多类似的功能。
3.9.2 使用strace
strace工具可以调查系统调用的情况。有好几种可以使用的方法其中一种是计算系统调用的时间并打印出来
3.10 总结
基本知识点
定义性能最有效的是响应时间无法测量就无法优化所以优化就要整体的响应时间测量测量的开始点是应用程序而不是数据库。很多系统无法完整的测量并且结果也会有错误但可以饶过一些限制等到一些好的结果正在的测量需要产生大量分析的数据可以用剖析器并且要把重要的问题发到最前面。优化和提升是两回事当提升的成本超过收益时、应该停止优化注意自己的直觉不能完成凭借经验主义还要注意到数据的方面 解决性能问题的方法首先是要澄清问题然后选择合适的技术来答问题。pt-query-digest工具
4 Schema与数据类型优化
良好的逻辑设计和物理设计是高性能的基石应该根据系统将要执行的查询语句来设计 schema,这往往需要权衡各种因素。在这三章中学习的是逻辑设计物理设计查询执行他们之间的关系。
4.1 选择可优化的数据类型
Mysql支持的数据类型很多选择合适的数据类型可以获取更高的性能。主要原则可以从这几方面选择 1、更小的通常好 一般情况下可以使用正存储最小数据类型比如 tinyint, unsigned 为什么要更小的因为更小的通常更快占用更少的内存、cpu缓存、磁盘IO。 2、简单就好 简单的数据类型的操作可以减少CPU的周期 3 尽量避免NULL 很多表包含null并且有的列以null为默认属性但系统并不需要保存NULL值并且如果查询中有包含有NULL的列对Mysql就很难优化因为NULL会加重对索引的计算所以通常把列指定为NOT NULL。
4.1.1 整数类型
4.1.2 整数
整数类型有可选的UNSIGNED属性表示不允许负值这大致可以使正数的上限提高一倍。 例如TINYINT.UNSIGNED可以存储的范围是0~255而TINYINT的存储范围是-128-127。
4.1.3 实数
实数FLOAT、DOUBLE、DECIMALFLOAT4个字节DOUBLE8个字节。DECIMAL字节可变最多65个数字。
4.1.4 字符串类型
VARCHAR和CHAR是两种最主要的字符串类型 VARCHAR长度可变节省空间但UPDATE时可能会导致行空间变大导致额外工作。VARCHAR的长度字段最大为2字节故VARCHAR最大长度为65536.。 CHAR: 长度固定空间可能浪费但不易产生碎片效率也高。 VARCHAR(5)和VARCHAR(200)在存储时效果一样但在查询时由于内存会按长列分配固定长度内存所以查询效率不一致。
4.1.5 日期和时间类型
dataTime和TimeStamp是最常用的时间类型。 dataTime可以存储范围比较大的值边界是1001-9999年精度为秒默认封装类型YYYYMMDDHHMMSS默认8个字节存储空间。不会根据地区改变时间 TimeStamp保存范围为1970年1月1日存储4个字节存储空间会根据不同的时区显示不同的时间。 通常尽量使用TimeStamp因为它比dataTime的空间效率更高。
4.1.6 位数据类型
Mysql的位数据类型是比较紧凑的存储数据从技术上说是字符串类型。 bit:mysql5.0之前和tinyint是同类型bit类型可以存储多个true和false值。 set:如果需要保存N个true/false可以合并到set数据类型中。这些了解即可
4.1.7 选择标识符
物理主键最好使用整数, 可使用AUTO_INCREMEN
4.1.8 特殊类型数据
IPv4地址经常被VARCHAR15来存储IP地址但它们实际上是32位无符号整数不是字符串。MySQL提供INET_ATON()和INET_NTOA()函数在这两种表示方法之间转换。
4.2 MySQL schema设计中的陷阱
有一些问题是由MySQL的实现机制导致的 太多的列 太多的关联EAV(实体-属性-值)设计模式并不是一个很好的模式因为Mysql限制了每个关联操作只能是61张表但EAV模式的数据库有可能会超过这个限制所以为了查询执行的效率好建议单个查询做到12表以内的查询 全能的枚举 变相的枚举 非此发明Not Invent Here的NULL不一定需要NULL值可以通过某种特殊值来做位代替
4.3 范式和反范式
范式
第一范式列不可再分 第二范式有主键且所有非主键属性都依赖主键且不能依赖主键部分针对联合主键 第三范式属性不依赖其他非主键属性即无传递性 范式优点数据简洁、无冗余修改快
范式缺点关联查询效率低下。
完全的范式化和完全的反范式化schema都是实验室里才有的东西在真实世界中很少会这么极端地使用。
4.4 缓存表和汇总表
在数据量大的表按某些条件汇总数据至小表。 缓存表每次获取查询比较慢的数据表。 汇总表保存使用group by语句聚合数据的表。 当重建汇总表和缓存表时要保证数据在操作时依然可用。通过使用“影子表”来实现“影子表”指的是一张在真实表“背后”创建的表。当完成了建表操作后通过一个原子的重命名操作切换影子表和原表。例如如果需要重建my_summary则可以先创建my_summary_new然后填充好数据最后和真实表做切换temp a , a b , b temp
4.4.1 物化视图
物化视图实际上是预先计算并且存储在磁盘上的表可以通过各种各样的策略刷新和更新
4.4.2 计数器表
有一个计数器表只有一行数据记录网站的点击次数
CREATE TABLE hit_couter (
cnt int unisgned not null
)ENGINEInnoDB;
update hit_counter set cnt cnt1;要获得更高的并发更新性能也可以将计数器保存在多行中每次随机选择一行进行更新。然后预先在这张表增加100行数据。现在选择一个随机的槽slot进行更新 CREATE TABLE hit_counter ( slot tinyint unsigned not null primary key, cnt int unsigned not null
) ENGINEInnoDB;
UPDATE hit_counter SET cnt cnt 1 WHERE slot RAND() * 100;
SELECT SUM(cnt) FROM hit_counter;4.5 加快ALTER TABLE操作的速度
MySQL的ALTER TABLE操作的性能对大表来说是个大间题。MySQL执行大部分修改表结构操作的方法是用新的结构创建一个空表从旧表中查出所有数据插入新表然后删除旧表。这样操作很费时间。一般来说Alter table操作有可能会导致Mysql服务中断对一般的场景可以使用两种技巧1、在另一个不提供服务的服务器上执行Alter table操作然后和提供服务的主库进行切换。2、影子拷贝插件结构相同的新表然后通过重命名和删除操作进行表交换t通过工具 online schema change 实现影子拷贝操作。
4.5.1 只修改.frm文件
修改表的.frm文件是很快的但MySQL有时候会在没有必要的时候也重建表。如果愿意冒一些风险可以让MySQL做一些其他类型的修改而不用重建表 建完新表后直接交换新表与旧表的.frm文件
4.5.2 快速创建MyISAM索引
为了高效地载入数据到MyISAM表中有一个常用的技巧是先禁用索引、载入数据然后重新启用索引
ALTER TABLE test.load.data DISABLE KEYS;
-- Lood data
ALTER TABLE test.load_data ENABLE KEYS;这个办法对唯一索引无效因为DISABLE KEYS只对非唯一索引有效。MyISAM会在内存中构造唯一索引并且为载入的每一行检查唯一性。一旦索引的大小超过了有效内存大小载入操作就会变得越来越慢。
4.6 总结
尽量避免过度设计使用小而简单的合适数据类型避免使用NULL值。尽量使用相同的数据类型存储相似或相关的值尤其是要在关联条件中使用的列。注意可变长字符串其在临时表和排序时可能导致悲观的按最大长度分配内存。尽量使用整型定义标识列。小心使用ENUM和SET。最好避免使用BIT。范式是好的但是反范式有时也是必需的并且能带来好处。
5 创建高性能的索引
索引优化应该是对查询性能优化最有效的手段了。
5.1 索引基础
索引可以包含一个或多个列如果包含多个列那么就要满足最左匹配原则 如索引为index_Aindex_Bindex_C那么查询时也要保证必须先有A的值再有B的值。例子select * from table where index_A ‘xiao’ and index_B ‘sql’
5.1.1 索引的类型
目前大部分数据库系统都是采用了B-Tree何和Tree所以要深入了解这两种类型其他的索引类型了解即可。
5.1.2 B-Tree
B-Tree的索引结构通常意味着所有的值都是按顺序执行的并且每个叶子节点相同所以B-Tree是从索引的根节点开始进行搜索根节点的槽中存放了指向子节点的指针存储引擎根据这些指针向下层查找。 叶子节点比较特别它们的指针指向的是被索引的数据而不是其他的节点页树的深度和表的大小又直接相关。所以很适合查找范围数据。但不适合插入和修改操作因为执行这些操作它的指针需要移动当我们的树中的根节点很深时那边移动指针的代价就很大。所以这时就用到BTree
5.1.3 BTree
BTree是在B-Tree的基础上进行了优化把B-Tree储存的指针改成每个节点就储存关键字并且插入和删除操作只需要进行节点的分裂和合并不需要移动指针因此效率更高。
5.1.4 哈希索引
哈希索引基于哈希表实现对于每一行数据储存引擎都会对所有的索引列计算出一个哈希码所有一般用在精确匹配索引列的查询场景。只有Menory引擎才支持哈希索引其他引擎都不支持。如果多个列的哈希值都相同的话索引会以链表的方式储存在哈希条目中。
CREATE TABLE testhash
(
fname VARCHAR(50)NOT NULL,
1name VARCHAR(50)NOT NULL,
KEY USING HASH(fname)
)
ENGINEMEMORY;select * from texthash;查询的数据如下: MySQL先计算’Peter’的哈希值并使用该值寻找对应的记录指针。因为f(‘Peter’) 8784,所以MySQL在索引中查找8784可以找到指向第3行的指针最后一步是比较 第三行的值是否为’Peter’,以确保就是要查找的行。同时InnoDB引擎有一个特殊的功能叫做“自适应哈希索引(adaptive hash index)”。当InnoDB注意到某些索引值被使用得非常频繁时它会在内存中基于B-Tree索引之上再创建一个哈希索引这样就让B-Tree索引也具有哈希索引的一些优点
5.1.5 空间数据索引R-Tree
MyISAM表支持空间索引可以用作地理数据存储。和B-Tree索引不同这类索引无须前缀查询。空间索引会从所有维度来索引数据。查询时可以有效地使用任意维度来组合查询。必须使用MySQL的GIS相关函数如MBRCONTAINS()等来维护数据。(了解就好…)
5.1.6 全文索引
**全文索引是一种特殊类型的索引它查找的是文本中的关键词而不是直接比较索引中的值。全文搜索和其他几类索引的匹配方式完全不一样。**它有许多需要注意的细节如停用词、词干和复数、布尔搜索等。全文索引更类似于搜索引擎做的事情而不是简单的WHERE条件匹配。(下章节介绍…)
5.1.7 其他索引类别
TokuDB使用分形树索引fractal tree index
ScaleDB使用Patricia tries…
5.2 索引的优点和缺点
索引大大减少了服务器需要扫描的数据量。索引可以帮助服务器避免排序和临时表。索引可以将随机I/O变为顺序I/O。 缺点当表中的数据发生变化时索引也要更新这会增大写操作的开销索引只能加速特定类型的查询对于其他类型的查询可能没有任何帮助。
5.3 高性能的索引策略
独立的列指不是表达式的一部分也不是函数的参数负责Mysql就不会使用索引 此语句中不会使用索引actor_id:,因为它是表达式的一种。
SELECT actor_id FROM sakila.actor WHERE actor_id 1 55.3.1 索引选择性和前缀索引
索引的选择性是指不重复的索引值也称为基数cardinality和数据表的记录总数#T的比值范围从1/#T到1之间。索引的选择性越高则查询效率越高选择性高的索引可以在查找时过滤掉更多的行。唯一索引的选择性是1这是最好的索引选择性性能也是最好的。
前缀索引是指将列截取开头合适长度后的最为索引是一种能使索引更小、更快的有效办法一般用到的场景是Text,blob,varchar三种类型前两种是必须使用前缀索引的最后一种看情况但另一方面也有其缺点MySQL无法使用前缀索引做ORDER BY和GROUP BY也无法使用前缀索引做覆盖扫描。
5.3.2 多列索引
在多个列上建立独立的单列索引大部分情况下并不能提高MySQL的查询性能。MySQL 5.0和更新版本引入了一种叫“索引合并”index merge的策略一定程度上可以使用表上的多个单列索引来定位指定的行。
查询能够同时使用这两个单列索引进行扫描并将结果进行合并。这种算法有三个变种OR条件的联合unionAND条件的相交intersection组合前两种情况的联合及相交。
5.3.3 选择合适的索引列顺序
关于选择性和基数的经验法则值得肯定但不能忘记排序、分组和范围条件等其他因素造成的影响。
5.3.4 聚簇索引
聚簇索引并不是一种单独的索引类型而是一种数据存储方式。InnoDB的聚簇索引是在同一个结构中保存了B-Tree索引和数据行。当表有聚簇索引时它的数据行实际上存放在索引的叶子页中。术语“聚簇”表示数据行和相邻的键值紧凑地存储在一起所以一个表只能有一个聚簇索引。
选择哪个索引作为聚簇索引?
InnoDB将通过主键聚集数据如果没有定义主键InnoDB会选择一个唯一的非空索引代替如何没有唯一非空索引InnoDB会隐式定义一个主键来作为聚簇索引
聚簇索引的优缺点:
优点 1.数据访问更快因为聚簇索引将索引和数据保存在同一个B树中因此从聚簇索引中获取数据比非聚簇索引更快 2.聚簇索引对于主键的排序查找和范围查找速度非常快 缺点
1.插入速度严重依赖于插入顺序按照主键的顺序插入是最快的方式否则将会出现页分裂严重影响性能。因此对于InnoDB表我们一般都会定义一个自增的ID列为主键 2.更新主键的代价很高因为将会导致被更新的行移动。因此对于InnoDB表我们一般定义主键为不可更新。 3.二级索引访问需要两次索引查找第一次找到主键值第二次根据主键值找到行数据
5.3.5 覆盖索引
如果一个索引包含或者说覆盖所有需要查询的字段的值我们就称之为“覆盖索引”。作用可以避免根据索引得到的地址后再回表查询。 -- 索引为A
select A from table where A 6;-- 索引为A, B, C
select A, C from table where A 6 and B 7;Using index 说明索引覆盖了查询结果避免了回表查询
5.3.6 使用索引扫描来做排序
MySQL有两种方式可以生成有序的结果通过排序操作或者按索引顺序扫描。如果Explan出来的type列为 ‘index’,就说明了Mysql使用了索引扫描来做排序(不要和Extra列的’Using index’搞混了)。同时只有索引的列的顺序和order by 之句中的顺序一样Mysql才能使用索引对结果进行排序。 有一种情况下ORDER BY子句可以不满足索引的最左前缀的要求就是前导列为常量的时候。如果WHERE子句或者J0IN子句中对这些列指定了常量就可以“弥补”索引的不足。
最好能直接使用索引的排序避免额外排序操作。
5.3.7 压缩前缀压缩索引
MyISAM使用前缀压缩来减少索引的大小从而让更多的索引可以放入内存中这在某些情况下能极大地提高性能。
5.3.8 冗余和重复索引
重复索引相同的列上按照相同的顺序创建相同的索引类型 冗余索引冗余索引和重复索引有一些不同。如果创建了索引(AB),再创建索引(A)就是冗余索引因为这只是前一个索引的前缀索引。因此索引(A,)也可以当作索A来使用。 不要有重复索引根据需求可适当创建冗余索引。
5.3.9 未使用的索引
删除不会用到的索引可以使用Percona Server 或者Maria DB打开userstats服务器变量(默认关闭的)然后先运行一下在通过information_schema_index_statistics查看每个索引的使用频率。 第二种使用Percona ToolKit 中的pt-index-usage 工具来读取查询日志对日志每条查询使用Explan操作后打印用关于索引和查询的报告。
5.3.10 索引和锁
索引可以减少不必要数据读取从一个角度较少了锁竞争。InnoDB只有在访问行的时候才会对其加锁而索引能够减少InnoDB访问的行数从而减少锁的数量。 如果索引无法过滤掉无效的行那么在InnoDB检索到数据并返回给服务器层以后MySQL服务器才能应用WHERE子句Using where 说明服务器层还需额外过滤数据。
5.4 索引案例学习
5.4.1 支持多种过滤条件
现在需要看看哪些列拥有很多不同的取值哪些列在WERE子句中出现得最频繁。在有更多不同值的列上创建索引的选择性会更好。一般来说这样做都是对的因为可以让MySQL更有效地过滤掉不需要的行。
5.4.2 避免多个范围条件
5.4.3 优化排序
5.5 维护索引和表
日常中还要对表和索引进行维护维护表有三个主要的目的找到并恢复损话的表维护准确的索引统计信息减少碎片
5.5.1 找到并修复损坏的表
表损坏corruption是很糟糕的事情。会导致查询返回错误的结果或者莫须有的主键冲突等问题严重时甚至还会导致数据库的崩溃。
CHECK TABLE检查表是否损坏通常能够找出大多数的表和索引的错误。
REPAIR TABLE修复损坏的表。也可通过一个不做任何操作no-op的ALTER操作来重建表ALTER TABLE innodb_tbl ENGINEINNODB;
InnoDB一般不会出现损坏如果出现了损坏那么一定是发生了严重的错误需要立刻调查一下原因。
5.5.2 更新索引统计信息
MySQL的查询优化器会通过两个API来了解存储引擎的索引值的分布信息以决定如何使用索引。
records_in_range()通过向存储引擎传入两个边界值获取在这个范围大概有多少条记录。MyISAM返回精确值InnoDB返回估算值info()该接口返回各种类型的数据包括索引的基数每个键值有多少条记录。 如果表没有统计信息或者统计信息不准确可以通过运行ANALYZE TABLE来重新生成统计信息。
InnoDB会在几个场景触发索引统计信息的更新。可以通过innodb_stats_on_metadata关闭自动生成统计信息。
一旦关闭索引统计信息的自动更新那么就需要周期性地使用ANALYZE TABLE来手动更新。否则索引统计信息就会永远不变。
5.5.3 减少索引和数据的碎片
B-Tree索引可能会碎片化这会降低查询的效率。有三种类型的数据碎片。
行碎片Row fragmentation指的是数据行被存储为多个地方的多个片段中。即使查询只从索引中访问一行记录行碎片也会导致性能下降。行间碎片Intra-row fragmentation行间碎片是指逻辑上顺序的页或者行在磁盘上不是顺序存储的。对诸如全表扫描和聚簇索引扫描之类的操作有很大的影响剩余空间碎片Free space fragmentation剩余空间碎片是指数据页中有大量的空余空间。这会导致服务器读取大量不需要的数据从而造成浪费。 对于MyISAM表这三类碎片化都可能发生**。但InnoDB不会出现短小的行碎片**InnoDB会移动短小的行并重写到一个片段中。但在BTree索引中已经可以帮组我们解决数据的碎片的问题了。
可以通过执行OPTIMIZE TABLE或者导出再导入的方式来重新整理数据。
5.6 总结
MySQL中多数情况会使用B-Tree索引。回顾一下B-Tree索引。
在选择索引和编写利用索引的查询时有三个原则要记住
单行访问是很慢的。使用索引可以创建位置引用以提升效率。按顺序访问范围数据是很快的。无磁盘I/O, 也没有排序和分组索引覆盖查询是很快的。直接从索引获取结果无需再回表查询
总的来说编写查询语句时应该尽可能选择合适的索引以避免单行查找、尽可能地使用数据原生顺序从而避免额外的排序操作并尽可能使用索引覆盖查询。
6 查询性能优化
好的表结构和适合的索引对于高性能是必不可少的但这些还不够还需要合理的设计查询。这章会从查询设计原则开始(也会发现效率不高的情况首先要考虑的一些因素)理解Mysql如果执行查询并明白高效和低效的原因在那。
6.1 为什么查询速度会慢
在写SQL查询时真正重要的是响应时间例如果把查询看作是一个任务那么它由一系列的子任务组成每个任务都会消耗一些时间如果要优化查询实际上是优化子任务要么就消除一些子任务要么减少子任务的执行次数。查询的生命周期中 执行是最重要的阶段一般花费时间最多的地方就在这个阶段因为查询需要在不同的地方花费时间网络CPU计算生成信息执行计划等,总结出来就是访问并不需要的数据大多和扫描到额外的数据
6.2 慢查询基础: 优化数据访问
优化数据访问 查询时减少访问的数据量 mysql服务层是否存在分析大量的数据行
6.2.1 向数据库请求了不需要的数据?
我们写的查询语句时要注意几点
查询不需要的记录常认为Mysql会返回我需要的数据但实际上Mysql是先返回全部结果集后进行计算可以使用limit多表关联时返回全部列避免使用select *,真要需要的时候也要考虑提供的原型中是否需要这些字段如果全部需要就可以加上select *,这样的好处是可以避免一些列被修改和提高代码的服用性但不要忘记它的代价是会消耗点数据库资源重复查询相同的数据解决方案初次查询时把数据缓存起来需要时就取
6.2.2 Mysql是否扫描了额外的数据
衡量查询开销的三个指标
响应时间: 服务时间(处理sql查询总共花了多长时间)和排队时间没有执行查询的等待时间扫描的行数: 查看扫描行数能说明找到需要的数据效率高不高返回的行数返回的行数有可能不一致 查看explain 中的type访问类型(ALL) 全表扫描Extra Using where 通过where 条件筛选出来返回的行数 这三个指标可以在慢日志里可以查看慢日志找出扫描行数多的查询。 一般在Mysql能够使用下面三种方式应用where条件从好到坏为 1、索引中使用了where条件过滤不匹配的记录在存储引擎层完成的 2、使索引覆盖扫描(Extar 列出现 Using index)从索引中过滤不需要的记录返回命中的结果在Mysql服务器层完成的 3、从数据返数据Extar 列出现 Using where)然后过滤掉不满足的条件
6.3 重构查询的方式
在优化有问题的查询时目标是找到一个更优的方法获取实际需要的结果。有时候可以让查询转换成另外一种写法获取结果但是性能更好。
6.3.1 一个复杂查询还是多个简单查询
设计查询要考虑的问题是否需要将一个复杂查询分成多个简单的查询 最开始时强调数据库尽可能的完成工作查询解析和优化是一件代价很高的事情Mysql从设计方面连接和断开都是轻量级的。
6.3.2 切分查询
将大查询切分成多个小查询每个查询功能完成一样只完成一小部分每次返回一小部分查询结果。 删除旧的数据是一个很好的例子定期清除大量数据时用一个大的语句一次性完成的话就有可能需要一次锁住很多数据占满事务日志消耗更多的资源如果将一个大的Delete 语句切分成多个较小的查询就能更小的影响Mytysql性能 比如
delete from messages where createdDATA_SUB(now(),interval 3 month)可以改为
rows affected 0
do{
rows_affected do_query(
DELETE FROM messages WHERE created DATE_SUB(NOW,INTERVAL 3 MONTH) LIMIT 10000)
}while rows_affected 06.3.3 分解关联查询
很多时候都会用到对关联查询进行分解如
select * from tag JOIN tag_post ON tag_post.tag_idtag.id
JOIN post ON tag_pos.post_id post.id
where tag.tag mysql可以分解成
select * from tag where tag mysql;
select * from tag_post where tag_id 1234
select * from post where post.id in (,,,)用分解关联查询的方式重构查询有如下好处
让缓存效率更高执行单个查询减少锁的竞争在应用层做关联更容易做好高性能和可扩展使用In() 代替关联查询可以让Mysql 按照 id顺序进行查询
6.4查询执行基础
客户端发送一条查询给服务器。 服务器先检查查询缓存如果命中了缓存则立刻返回存储在缓存中的结果。否则进入下一阶段。 服务器端进行SQL解析、预处理再由优化器生成对应的执行计划。 MySQL根据优化器生成的执行计划调用存储引擎的API来执行查询。 将结果返回给客户端。
6.4.1 Mysql clien / server 通信协议
通信协议是“半双工”的意味着任何一个时刻要么是由服务器向客户端发送数据要么就是客户端向服务器发送数据这两个动作不饿同时发送 查询状态 可以使用show full processlits 命令 max_allowed_packet参数决定客户端请求长度。 SHOW FULL PROCESSLIST命令可查看连接状态
Sleep线程正在等待客户端发送新的请求。 Query线程正在执行查询或者正在将结果发送给客户端。 Locked在MySQL服务器层该线程正在等待表锁。 Analyzing and statistics线程正在收集存储引擎的统计信息并生成查询的执行计划。 Copying to tmp table [on disk]线程正在执行查询并且将其结果集都复制到一个临时表中 Sorting result线程正在对结果集进行排序。 Sending data线程可能在多个状态之间传送数据、或者在生成结果集、或者在向客户端返回数据。
6.4.1 查询缓存
解析一个查询语句之前如果查询缓存是打开的MySQL会优先检查查询是否命中查询缓存。检查是通过一个对大小写敏感的哈希查找实现的。查询和缓存中的查询只要有一个字节不同那么就不会匹配结果如果当前查询刚好命中查询缓存那么在查询结果之前Mysql会检查一次用户权限
6.4.2 查询优化处理
查询的生命周期的下一步是将一个SQL转换成一个执行计划 Mysql在根据这个执行计划和出处引擎进行交互。 语法解析和预处理 Mysql通过关键字将Sql语句进行解析并生产对于的解析树在解析阶段中Mysql会对我们 的Sql语法进行解析是否有误其中会包含验证关键字或者对应的顺序 而预处理器则根据一些MySQL规则进一步检查解析树是否合法例如这里将检查数据表和数据列是否存在还会解析名字和别名看看它们是否有歧义。 查询优化器 SQL查询解析合法了那么就会交给优化器将其转化为执行计划一条Sql查询可以有很多种执行方式最后会返回同样的结果优化器的作用就是找到最好(成本最小)的执行计划。可以通过查看当前会话的last_query_cost t的值获取当前Mysql计算查询的成本、
select Sql_NO_cache count(*) from sakila.film_actor;show status like last_query_cost这个结果表示Mysql的优化器需要做1045个数据页的随机查询才能完成上面的查询这是根据一系列的统计信息计算得来的每个表或者索引的页面个数、分布情况。 MySQL优化器选择错误的执行计划
统计信息不准确 mysql依赖存储引擎提供的统计信息来评估成本当有时统计信息的偏差很大比如 innodb的MVCC架构。Mysql的最优解可能和我们想的不一样我们自己希望执行时间和响应时间尽可能的短但是Mysql只是基于优化器来选择执行计划。MySQL从不考虑其他并发执行的查询这可能会影响到当前查询的速度 优化器策略 1、 静态优化编译时优化: 直接对解析树优化比如可以在where中使用另一种等价形式、 2、动态优化运行时优化跟查询的上下文有关或者其他因素如where中的取值
Mysql能够处理的类型 重新定义关联表的顺序数据表的关联并不总是按照在查询中指定的顺序进行优化器会按优化方式重新排序
将外连接转化成内连接MySQL能够识别并重写查询OUTER JOIN语句可以调整关联顺序让外连接等价于一个内连接。使用等价变换规则MySQL可以使用一些等价变换来简化并规范表达式合并和减少一些比较移除一些恒成立和一些恒不成立的判断。优化COUNT()、MIN()和MAX()索引和列是否可为空通常可以帮助MySQL优化这类表达式。预估并转化为常数表达式当MySQL检测到一个表达式可以转化为常数的时候就会一直把该表达式作为常数进行优化处理。覆盖索引扫描当索引中的列包含所有查询中需要使用的列的时候MySQL就可以使用索引返回需要的数据而无须查询对应的数据行子查询优化MySQL在某些情况下可以将子查询转换一种效率更高的形式从而减少多个查询多次对数据进行访问。提前终止查询在发现已经满足查询需求的时候MySQL总是能够立刻终止查询。等值传播如果两个列的值通过等式关联那么MySQL能够把其中一个列的WHERE条件传递到另一列上。列表IN()的比较在很多数据库系统中IN()完全等同于多个OR条件的子句因为这两者是完全等价的。
数据和索引的统计信息MySQL查询优化器在生成查询的执行计划时需要向存储引擎获取相应的统计信息页面、索引基数、数据行、索引长度等优化器根据这些信息来选择一个最优的执行计划。
MySQL如何执行关联查询并不仅仅是一个查询需要到两个表匹配才叫关联每一个查询每一个片段包括子查询甚至基于单表的SELECT都可能是关联.
执行计划MySQL的执行计划是一棵左测深度优先的树.
关联查询优化器MySQL优化器最重要的一部分就是关联查询优化。多表关联的时候有多种不同的关联顺序来获得执行结果。关联查询优化器会选择一个代价最小的关联顺序.
排序优化应避免排序或者尽可能避免对大量数据进行排序filesort。数据量小于“排序缓冲区”则使用内存如果内存不够排序则分块后排序将各块的排序结果存放在磁盘上然后合并merge
两次传输排序旧版本使用读取行指针和需要排序的字段对其进行排序然后再根据排序结果读取所需要的数据行。单次传输排序新版本使用先读取查询所需要的所有列然后再根据给定列进行排序最后直接返回排序结果
6.4.3 查询执行引擎
相对于查询优化阶段查询执行阶段不是那么复杂MySQL只是简单地根据执行计划给出的指令逐步执行。服务层按步骤调用存储引擎接口获取需要的数据
6.4.4 返回结果给客户端
查询执行的最后一个阶段是将结果返回给客户端。即使查询不需要返回结果集给客户端MySQL仍然会返回这个查询的一些信息如该查询影响到的行数。 如果查询可以被缓存那么MySQL在这个阶段也会将结果存放到查询缓存中。
MySQL将结果集返回客户端是一个增量、逐步返回的过程。
6.5 MySQL查询优化器的局限性
MySQL查询优化器对少部分查询不适用而我们可以通过改写查询让MySQL高效地完成工作。
6.5.1 关联子查询
关联子查询会被优化器转换成exist语句或连接语句但转换后的语句执行效率反而可能会变低
-- 原sql
SELECT * FROM sakila.film WHERE film_id IN (SELECT film_id FROM sakila.film_actor WHERE actor_id 1);-- 转换后等效exist语句
SELECT * FROM sakila.film WHERE EXISTS (SELECT * FROM sakila.film_actor WHERE actor_id 1AND film_actor.film_id film.film_id);-- 转换后的内连接语句
SELECT film.* FROM sakila.film INNER JOIN sakila.film_actor USING(film_id) WHERE actor_id 1;6.5.2 UNION的限制
有时,Mysql无法将限制条件从外层“下推”到内层这使得原本能够限制部分返回结果的条件无法应用到内层查询的优化上。
– 原sql查询量大但返回结果却很少优化器无法处理
(SELECT first name,last name FROM sakila.actor ORDER BY last name)
UNION ALL
(SELECT first name,last name FROM sakila.customer ORDER BY last name)
LIMIT 20;这条sql查询会先先等到actor, custormer的结果集然后在从临时表取出前20条数据可以通过union 的 两个子查询中加水limit 20 来优化减少查询命中的响应时间
(SELECT first name,last name FROM sakila.actor ORDER BY last name limit 20)
UNION ALL
(SELECT first name,last name FROM sakila.customer ORDER BY last name limit 20)
LIMIT 20;union all 与 union 的区别和效率
6.5.3 索引合并优化
在5.0和更新的版本中当WHERE子句中包含多个复杂条件的时候MySQL能够访问单个表的多个索引以合并和交叉过滤的方式来定位需要查找的行
6.5.4 等值传递
某些时候等值传递会带来一些意想不到的额外消耗。例如有一个非常大的IN()列表而MySQL优化器发现存在WHERE、ON或者USING的子句将这个列表的值和另一个表的某个列相关联。那么优化器会将IN()列表都复制应用到关联的各个表中但如果这个列表非常大则会导致优化和执行都会变慢。
6.5.5 并行执行
MySQL无法利用多核特性来并行执行查询。很多其他的关系型数据库能够提供这个特性但是MySQL做不到不要花时间去尝试寻找并行执行查询的方法。
6.5.6 哈希关联
MySQL并不支持哈希关联——MySQL的所有关联都是嵌套循环关联。
6.5.7 最大值和最小值优化
对于MIN()和MAX()查询MySQL的优化做得并不好。如果该字段上并没有索引MySQL将会进行一次全表扫描然后再排序 不过可以尝试改写sql
-- 获取名字为PENELOP记录对应的最下actor_id-- 原sql 全表扫描后再分组获取最小值
SELECT MIN(actor_id) FROM sakila.actor WHERE first_namePENELOPE;-- 使用索引项actor_id获取的第一条即满足条件
SELECT actor_id FROM sakila.actor USE INDEX(PRIMARY) WHERE first_name PENELOPE LIMIT 1;6.5.8 在同一个表上查询和更新
MySQL不允许对同一张表同时进行查询和更新。 原sql语句把两个表中的数据记录到cont字段中Mysql提示不能在 FROM 子句中指定目标表‘ outer _ tbl’进行更新
UPDATE tbl AS outer_tbl set cnt (select count(*) from
tb1 inner_tb1 where inner_tb1.type outer_th1.type)可以通过使用生成表的形式来绕过上面的限制因为MySQL只会把这个表当作一个临时表来处理。 改sql语句
update tb1 inner join (
select type,count(1) as cnt from tb1 group by type
) as der using(type) set tb1.cnt der.cnt;unsing的概念
6.6 查询优化器的提示hint
如果对优化器选择的执行计划不满意可以使用优化器提供的几个提示(hit)来控制 最终的执行计划 附上链接
6.7 优化特定类型的查询
介绍如何优化特定类型的查询
6.7.1 优化COUNT()查询
count(*) count(1): 获取所有行数 count(column): 获取该列有值的数量排除NULL MyISAM会将总行数存在常量中故没有任何条件的count(*)在该引擎中非常快可以利用这个特性做点优化 可以了解下Mysql5.6版本innodb对count()的优化不过在mysql5.6以上的版本已经解决了
6.7.2 优化关联查询
这个 话题基本都在上一章但要特别注意的情况
确保ON或者USING子句中的列上有索引。在创建索引的时候就要考虑到关联的顺序。当表A和表B用列C关联的时候如果优化器的关联顺序是B、A,那么就不需要在 B表的对应列上建上索引。确保任何的GROUP BY和ORDER BY中的表达式只涉及到一个表中的列这样MySQL才有可能使用索引来优化这个过程。当升级MySQL的时候需要注意关联语法、运算符优先级等其他可能会发生变化的地方。因为以前是普通关联的地方可能会变成笛卡儿积不同类型的关联可能会生成不同的结果等
6.7.3 优化子查询
尽可能使用关联查询代替子查询。
6.7.4 优化GROUP BY和DISTINCT
在Mysql中无法使用索引时可通过group by的两种策略来完成使用临时表或者文件排序来做分组。可以通过使用提示SQL_BIG_RESULT和SQL_SMALL_RESULT来让优化器按照你希望的方式运行。 优化GROUP BY WITH ROLLUP最好的办法是尽可能的将WITH ROLLUP功能转移到应用程序中处理。
6.7.5 优化LIMIT分页
在偏移量非常大的时候例如可能是LIMIT 1000,20这样的查询这时MySQL需要查询10 020条记录然后只返回最后20条前面10000条记录都将被抛弃这样的代价非常高。 优化此类分页查询的一个最简单的办法就是尽可能地使用索引覆盖扫描而不是查询所有的列。然后根据需要做一次关联操作再返回所需的列。
-- 原sql
SELECT film_id, description FROM sakila.film ORDER BY title LIMIT 50, 5;-- 优化后先根据索引项film_id筛选数据再做关联获取需要的数据
SELECT film.film_id, film.descriptionFROM sakila.filmINNER JOIN (SELECT film_id FROM sakila.film ORDER BY title LIMIT 50, 5) AS lim USING(film_id);有时候也可以将LIMIT查询转换为已知位置的查询让MySQL通过范围扫描获得到对应的结果。例如如果在一个位置列上有索引并且预先计算出了边界值上面的查询就可以改写为
-- 也可直接将limit语句转换为范围查询前提是该字段是索引项
SELECT film_id, description FROM sakila.film WHERE position BETWEEN 50 AND 54 ORDER BY position;关于offset 问题其实是Mysql扫描大量不需要的行然后在抛弃掉可以通过使用书签记录上次取数据的位置那下次就可以直接从改书签的位置开始扫描这样就可以避免offset问题。
-- 原sql 通过租借记录做翻页那可以根据最新一条租借记录向后追湖
select * from sakila.rental order by rental_id desc limit 20;-- 假设上面查询返回的主键为16049 - 16030 的租借 改 sql
select * from sakila.rental where rental_id 16030 order by rental_id desc limit 20; 6.7.6 优化SQL_CALC_FOUND_ROWS
分页的时候一个常用的技巧是在LIMIT语句中加上SQL_CALC_FOUND_ROWS提示hint这样就可以获得去掉LIMIT以后满足条件的行数因此可以作为分页的总数。MySQL只有在扫描了所有满足条件的行以后才会知道行数所以加上这个提示以后不管是否需要MySQL都会扫描所有满足条件的行然后再抛弃掉不需要的行而不是在满足LIMIT的行数后就终止扫描。所以该提示的代价可能非常高。
一个更好的设计是将具体的页数换成“下一页”按钮假设每页显示20条记录那么我们每次查询时都是用LIMIT返回21条记录并只显示20条如果第21条存在那么我们就显示“下一页”按钮否则就说明没有更多的数据也就无须显示“下一页”按钮了。
另一种做法是先获取并缓存较多的数据——例如缓存1000条——然后每次分页都从这个缓存中获取。这样做可以让应用程序根据结果集的大小采取不同的策略如果结果集少于1000就可以在页面上显示所有的分页链接因为数据都在缓存中所以这样做性能不会有问题。如果结果集大于1000则可以在页面上设计一个额外的“找到的结果多于1000条”之类的按钮。这两种策略都比每次生成全部结果集再抛弃掉不需要的数据的效率要高很多。
有时候也可以考虑使用EXPLAIN的结果中的rows列的值来作为结果集总数的近似值实际上Google的搜索结果总数也是个近似值。当需要精确结果的时候再单独使用COUNT*来满足需求这时如果能够使用索引覆盖扫描则通常也会比SQL_CALC_FOUND_ROWS快得多。
6.7.7 优化UNION查询
MySQL总是通过创建并填充临时表的方式来执行UNION查询。因此很多优化策略在UNION查询中都没法很好地使用。经常需要手工地将WHERE、LIMIT、ORDER BY等子句“下推”到UNION的各个子查询中以便优化器可以充分利用这些条件进行优化例如直接将这些子句冗余地写一份到各个子查询。
除非确实需要服务器消除重复的行否则就一定要使用UNION ALL这一点很重要。如果没有ALL关键字MySQL会给临时表加上DISTINCT选项这会导致对整个临时表的数据做唯一性检查。这样做的代价非常高。即使有ALL关键字MySQL仍然会使用临时表存储结果。事实上MySQL总是将结果放入临时表然后再读出再返回给客户端。
6.7.8 静态查询分析
Percona Toolkit中的pt-query-advisor能够解析查询日志、分析查询模式然后给出所有可能存在潜在问题的查询并给出足够详细的建议。这像是给MySQL所有的查询做一次全面的健康检查它能检测出许多常见的问题。
6.7.9 使用用户自定义变量
用户自定义变量如果能够用好发挥其潜力可以写出非常高效的查询语句。
用户自定义变量是一个用来存储内容的临时容器在连接MySQL的整个过程中都存在。可以使用下面的SET和SELECT语句来定义它们
SET one : 1;
SET min_actor : (SELECT MIN(actor_id) FROM sakila.actor);
SET last_week : CURRENT_DATE-INTERVAL 1 WEEK;然后可以在任何可以使用表达式的地方使用这些自定义变量
SELECT ... WHERE collast_week;在了解自定义变量的强大之前我们再看看它自身的一些属性和限制看看在哪些场景下我们不能使用用户自定义变量。
使用自定义变量的查询无法使用查询缓存。不能在使用常量或者标识符的地方使用自定义变量例如表名、列名和LIMIT子句中5.0之前的版本是大小写敏感的所以要注意代码在不同MySQL版本间的兼容性问题赋值符号:的优先级非常低所以需要注意赋值表达式应该使用明确的括号 优化排名语句 用户自定义变量一个重要特性是你可以在给一个变量赋值的同时使用这个变量。换句话说用户自定义变量的赋值具有“左值”特性。 来看一个更复杂的用法先编写一个查询获取演过最多电影的前10位演员然后根据他们的出演电影次数做一个排名如果出演的电影数量一样则排名相同。我们先编写一个查询返回每个演员参演电影的数量 再把排名加上去这里看到有四名演员都参演了35部电影所以他们的排名应该是相同的。
使用三个变量来实现一个用来记录当前演员参演的电影数量一个用来记录前一个演员的电影数量一个用来记录当前的排名。只有当前演员参演的电影的数量和前一个演员不同时排名才变化。 避免重复查询刚刚更新的数据 例如我们的一个客户希望能够更高效地更新一条记录的时间戳同时希望查询当前记录中存放的时间戳是什么。简单地可以用下面的代码来实现
UPDATE t1 SET lastUpdated NOW() WHERE id 1;
SELECT lastUpdated FROM t1 WHERE id 1;使用变量我们可以按如下方式重写查询
UPDATE t1 SET lastUpdated NOW() WHERE id 1 AND now : NOW();
SELECT now;上面看起来仍然需要两个查询但这里的第二个查询无须访问任何数据表所以会快非常多。
统计更新和插入的数量 当使用了INSERT ON DUPLICATE KEY UPDATE插入转更新的时候如果想知道到底插入了多少行数据到底有多少数据是因为冲突而改写成更新操作的实现办法的本质如下
INSERT INTO t1(c1, c2) VALUES(4, 4), (2, 1), (3, 1) ON DUPLICATE KEY UPDATE c1 VALUES(c1) ( 0 * ( x : x 1 ) );当每次由于冲突导致更新时对变量x自增一次。然后通过对这个表达式乘以0来让其不影响要更新的内容。另外MySQL的协议会返回被更改的总行数所以不需要单独统计这个值。 用户自定义变量的其他用处
用户自定义变量能够做的有趣的事情
查询运行时计算总数和平均值。模拟GROUP语句中的函数FIRST()和LAST()。对大量数据做一些数据计算。编写一个样本处理函数当样本中的数值超过某个边界值的时候将其变成0。模拟读/写游标。
6.9 总结
理解查询是如何被执行的以及时间都消耗在哪些地方这依然是前面我们介绍的响应时间的一部分。
优化通常都需要三管齐下不做、少做、快速地做。
除了这些基础的手段包括查询、表结构、索引等MySQL还有一些高级的特性可以帮助你优化应用例如分区分区和索引有些类似但是原理不同。
MySQL还支持查询缓存它可以帮你缓存查询结果当完全相同的查询再次执行时直接使用缓存结果。
7 Mysql的高级特性
7.1 分区表
分区表本身是独立的逻辑表底层是由多个物理子表组成索引也是按照分区的子表定义的而没有全局索引实现分区的代码是通过底层表的句柄对象(Handler Object)的封装。 大致流程对于分区表的请求都会通过句柄对象转化成存储引擎接口的调用所以分区对SQL是封闭的对应用是透明的。
MySQL在创建表时使用PARTITION BY子句定义每个分区存放的数据。在执行查询的时候优化器会根据分区定义过滤那些没有我们需要数据的分区
分区的一个主要目的是将数据按照一个较粗的粒度分在不同的表中。这样做可以将相关的数据存放在一起 可以用到分区的场景
表非常大无法全部发放在内存中或者在表的最后部分是热点数据其他都是历史数据。要批量删除大量数据可以用清除整个分区的方式分区表更容易维护可以备份和恢复独立的分区
分区的限制 1 一个表最多只能有1024个分区 2 分区表达式必须是整数 3 字段中有主键和唯一索引的列必须包含进去
7.1.1 分区表的原理
如前所述分区表由多个相关的底层表实现这些底层表也是由句柄对象Handler object)表示所以我们也可以直接访问各个分区。存储引擎管理分区的各个底层表和 管理普通表一样所有的底层表都必须使用相同的存储引擎。 分区表上的操作逻辑 select insert delete update 操作
SELECT查询当查询一个分区表的时候分区层先打开并锁住所有的底层表优化器先判断是否可以过滤部分分区然后再调用对应的存储引擎接口访问各个分区的数据。
INSERT操作当写入一条记录时分区层先打开并锁住所有的底层表然后确定哪个分区接收这条记录再将记录写入对应底层表。
DELETE操作当删除一条记录时分区层先打开并锁住所有的底层表然后确定数据对应的分区最后对相应底层表进行删除操作。
UPDATE操作当更新一条记录时分区层先打开并锁住所有的底层表MySQL先确定需要更新的记录在哪个分区然后取出数据并更新再判断更新后的数据应该放在哪个分区最后对底层表进行写入操作并对原数据所在的底层表进行删除操作。
7.1.2 分区表的类型
MySQL支持多种分区表。我们看到最多的是根据范围进行分区每个分区存储落在某个范围的记录分区表达式可以是列也可以是包含列的表达式。 例如下表就可以将每一年的销售额存放在不同的分区里
CREATE TABLE sales ( order_date DATETIME NOT NULL, -- Other columns omitted
) ENGINEInnoDB PARTITION BY RANGE(YEAR(order_date)) ( PARTITION p_2010 VALUES LESS THAN (2010), PARTITION p_2011 VALUES LESS THAN (2011), PARTITION p_2012 VALUES LESS THAN (2012), PARTITION p_catchall VALUES LESS THAN MAXVALUE );partition分区子句中可以使用各种函数。但有一个要求表达式返回的值要是一个确定的整数且不能是一个常数。
7.1.3 如何使用分区表
假设我们希望从一个非常大的表中查询出一段时间的记录而这个表中包含了很多年的历史数据数据是按照时间排序的比如我们希望查询最近几个月的数据大约有5亿条记录硬件设备方面都是平常的那种原表的数据中有5TB的数据这个数据超过了内存。这种大数据量的情况下肯定不鞥走扫描全表了维护成本大高同时也不能大依赖索引因为会产生大量的索引碎片最终走一次查询会产生千万条的随机I/O程序会直接崩掉所以为了保证大数据量的可扩展性一般有下面两个策略
全量扫描数据不要任何索引可以使用简单的分区方式存放表不要任何索引根据分区的规则大致定位需要的数据位置。
索引数据并分离热点如果数据有明显的“热点”,而且除了这部分数据其他数据很少被访问到那么可以将这部分热点数据单独放在一个分区中让这个分区的数据能够有机会都缓存在内存中。
7.1.4 什么情况下会出问题
上面的两个分区策略都基于两个非常重要的假设查询都能够过滤掉很多额外的分区、分区本身并不会带来很多额外的代价。这两个假设在某些场景下会有问题介绍一些可能会遇到的问题 NULL值会使分区过滤无效 有两种策略可以解决 1、 对null使用其他符号来代替 2 创建第一个分区如果插入的数据都是有效的那么第一个分区就是空的检测的成本很低Mysql5.5版本已弃用 分区列和索引列不匹配 选择分区的成本可能很高 打开并锁住所有底层表的成本可能很高 维护分区的成本可能很高
7.1.5 查询优化
分区最大的优点就是优化器可以根据分区函数来过滤一些分区。可以使用EXPLAIN PARTITION来观察优化器是否执行了分区过滤。 例如执行以下查询语句
EXPLAIN PARTITIONS
SELECT * FROM mytable WHERE date 2021-01-01;将会显示类似以下的结果
*************************** 1. row ***************************id: 1select_type: SIMPLEtable: mytablepartitions: p20210101,p20210102,p20210103 // 访问的分区type: const
possible_keys: date_idxkey: date_idxkey_len: 4ref: constrows: 1filtered: 100.00Extra: Using index condition; Using where; Using MRR其中partitions列显示了查询语句访问的分区列表type列为const表示使用了索引查询在key列显示了使用的索引名称filtered列显示了执行分区过滤后查询结果的行数占总行数的百分比。如果分区过滤得当该值将为100%否则可能会较低。
7.1.6 合并表在Mysql5.0版本之后被删除了
合并表Merge table是一种早期的、简单的分区实现和分区表相比有一些不同的限制并且缺乏优化。分区表严格来说是一个逻辑上的概念用户无法访问底层的各个分区对用户来说分区是透明的。但是合并表允许用户单独访问各个子表。
7.2 视图
视图本身是一个虚拟表不存放任何数据。在使用SQL语句访问视图的时候它返回的数据是MySQL从其他表中生成的。 MySQL可以使用两种办法中的任何一种来处理视图 1、逐层查询(临时表算法TEMPTABLE)这种方法通过在视图上进行查询然后再使用该查询结果创建另一个视图来处理视图。
CREATE VIEW view_name AS
SELECT column1, column2, …
FROM table_name
WHERE condition;2、内嵌查询合并算法MERGE 会尽可能地使用合并算法 这种方法使用 SELECT 语句中的子查询来处理视图这个子查询可以在 FROMJOIN 和 WHERE 子句中使用。
CREATE VIEW view_name AS
SELECT column1, column2, …
FROM (SELECT column1, column2, …FROM table_nameWHERE condition
) AS view_table;如果是采用临时表算法实现的视图EXPLAIN中会显示为派生表DERIVED
7.2.1 可更新视图
可更新视图updatable view是指可以通过更新这个视图来增删改视图涉及的相关表。 更新视图限制 1、视图定义中包含了GROUP BY、UNION.、聚合函数以及其他一些特殊情况就不能被更新了。 2、更新视图的查询也可以是一个关联语句但是有一个限制被更新的列必须来自同一个表中。 3、使用临时表算法实现的视图都无法被更新。
7.2.2 视图对性能的影响
合理利用视图可提高性能使用视图实现基于列的权限控制却不需要真正的系统中创建权限因此没有额外的开销。
使用临时表算法实现的视图在某些时候性能会很糟糕虽然可能比直接使用等效查询
语句要好一点。MySQL以递归的方式执行这类视图先会执行外层查询即使外层查
询优化器将其优化得很好但是MySQL优化器可能无法像其他的数据库那样做更多的
内外结合的优化。外层查询的WHERE条件无法“下推”到构建视图的临时表的查询中
临时表也无法建立索引7.2.3 视图的限制
MySQL还不支持物化视图物化视图是指将视图结果数据存放在一个可以查看的表中并定期从原始表中刷新数据到这个表中。但可以使用构建缓存表或者汇总表的办法来模拟物化视图和索引。 如果打算重新修改一个视图并且没法找到视图的原始的创建语句的话可以通过使用视图的.frm文件的最后一行获得一些信息。如果有FILE权限甚至可以直接使用SQL语句中的L0AD_FILE() 来读取.frm中的视图创建信息。再加上一些字符处理工作就可以获得一个完整的视图创建语句了
7.3 外键约束
1、同步更新则可使用外键
2、数值约束可使用触发器
3、只是简单约束应用程序内约束即可
7.4 在MySQL内部存储代码
MySQL允许通过触发器、存储过程、函数、事件的形式来存储代码。 这四种存储代码都使用特殊的SQL语句扩展它包含了很多过程处理语法例如循环和条件分支等存储过程和存储函数都可以接收参数然后返回值但是触发器和事件却不行。 在MySQL内部存储代码最常用的可能是SQL语言和存储过程。 例以下是一个简单的存储过程的例子其目的是统计一个表中某个字段的平均值
DELIMITER $$
CREATE PROCEDURE calculate_avg_value()
BEGINSELECT AVG(column_name) FROM table_name;
END $$
DELIMITER ;该存储过程的名称是calculate_avg_value它没有传入任何参数通过SELECT AVG(column_name)语句计算了table_name表中column_name字段的平均值。最后用DELIMITER语句将分隔符设置为$$并用END语句结束存储过程代码块
存储代码的优点 1、它在服务器内部执行离数据最近节省带宽和网络延迟。 2、它可以简化代码的维护和版本更新。
存储代码的缺点 1、存储代码效率方面比其他的程序代码要差点 2、MySQL并没有什么选项可以控制存储程序的资源消耗
7.4.1 存储过程和函数
一般在数据量较大、访问频繁、需求复杂的应用场景才会用到存储过程和存储函数 下面是一个简单的存储过程的例子
CREATE PROCEDURE get_customer_orders(IN cust_id INT)
BEGINSELECT * FROM orders WHERE customer_id cust_id;
-- 调用存储过程
call get_customer_orders(1);
END;上面的存储过程接受一个参数cust_id然后根据该参数从orders表中查询该客户的订单。
下面是一个简单的存储函数的例子
CREATE FUNCTION get_customer_order_total(IN cust_id INT)
RETURNS DECIMAL(10,2)
BEGINDECLARE total DECIMAL(10,2);SELECT SUM(price) INTO total FROM orders WHERE customer_id cust_id;RETURN total;-- 调用存储函数
SELECT get_customer_order_total(1);
END;上面的存储函数接受一个参数cust_id然后根据该参数从orders表中查询该客户的订单总价并返回结果。在这个例子中我们还使用了一个DECLARE语句来声明一个变量total用于保存订单总价。
7.4.2 触发器
触发器可以让你在执行INSERT、UPDATE或者DELETE的时候执行一些特定的操作。可以在MySQL中指定是在SQL语句执行前触发还是在执行后触发。 触发器的限制 1、对每一个表的每一个事件最多只能定义一个触发器。 2、Mysql只能基于行触发。 如果仅考虑性能那么MySQL触发器的实现中对服务器限制最大的就是它的“基于行 的触发”设计。因为性能的原因很多时候无法使用触发器来维护汇总和缓存表。使用 触发器而不是批量更新的一个重要原因就是使用触发器可以保证数据总是一致的。
CREATE TRIGGER myInsert AFTER INSERT ON teacher FOR EACH ROW BEGIN
update student SET name triggerName WHERE id 1;
END;当新行插入teacher表时触发器将更新student表中id为1的记录的name字段的内容。这个触发器在每次插入新的数据时都会触发并且只会影响student表中id为1的记录
7.4.3 事件
Mysql中的事件类似于Linux的定时任务。我们可以通过创建事件指定Mysql在某个时段执行Sql代码。用到的场景包含定时备份数据库、清理无用数据。
CREATE EVENT eventDemo ON SCHEDULE EVERY 1 WEEK DO CALL getInfo(1);这条SQL语句创建了一个名为eventDemo的定时任务每周执行一次执行的操作是调用一个名为getInfo的存储过程传入参数1。具体解释如下
CREATE EVENT创建一个事件。
eventDemo事件的名称。
ON SCHEDULE EVERY 1 WEEK定时任务每周执行一次。
DO CALL执行的操作为调用一个存储过程。
getInfo(1)调用名为getInfo的存储过程并传递参数1。总的来说这条语句用于创建一个定时任务以便在每周指定的时间点自动执行指定的存储过程以完成特定的操作。
7.5 游标
游标Cursor是一种用于对结果集进行逐行处理的机制。在MySQL中通过游标可以在存储过程或函数中逐行处理结果集并对每一行进行特定的操作。
游标的基本操作包括声明游标、打开游标、获取游标中的数据、关闭游标、释放游标等。 例用于遍历一个名为 employees 的表中的数据并将每一行的 employee_id 和 employee_name 列的值输出
CREATE PROCEDURE myProcedure()
BEGINDECLARE done INT DEFAULT FALSE;DECLARE employeeId INT;DECLARE employeeName VARCHAR(50);DECLARE cur CURSOR FOR SELECT employee_id, employee_name FROM employees;DECLARE CONTINUE HANDLER FOR NOT FOUND SET done TRUE;OPEN cur;read_loop: LOOPFETCH cur INTO employeeId, employeeName;IF done THENLEAVE read_loop;END IF;-- Do something with employeeId and employeeNameSELECT CONCAT(Employee ID: , employeeId, , Employee Name: , employeeName);END LOOP;CLOSE cur;
END;在上述示例中首先通过 DECLARE cur CURSOR FOR SELECT employee_id, employee_name FROM employees; 声明了名为 cur 的游标该游标用于遍历 employees 表中的数据。然后通过 OPEN cur; 打开游标进入一个名为 read_loop 的循环中通过 FETCH cur INTO employeeId, employeeName; 获取游标中的下一行数据并存储到 employeeId 和 employeeName 变量中然后可以对这些变量进行特定操作。最后通过 CLOSE cur; 关闭游标并释放其内存空间。
需要注意的是使用游标会增加数据库系统的负担因此应该尽量避免在大型的数据集上使用游标以免影响系统性能。
7.6 绑定变量
当创建一个绑定变量SQL时客户端向服务器发送了一个SQL语句的原型。服务器端收到这个SQL语句框架后解析并存储这个SQL语句的部分执行计划返回给客户端一个SQL语句处理句柄。以后每次执行这类查询客户端都指定使用这个句柄。
绑定变量的SQL使用问号标记可以接收参数的位置当真正需要执行具体查询的时候则使用具体值代替这些问号。
例如假设有一个Web应用程序允许用户根据年龄范围查询某个城市的人口数量。程序通过接收用户输入的 min_age 和 max_age 参数并将它们绑定到一个SQL查询语句中如下所示
SELECT COUNT(*) FROM population
WHERE city New York
AND age :min_age
AND age :max_age;在上述查询中:min_age 和 :max_age 是绑定变量它们的值会在查询执行时从程序中的变量中动态地获取。
使用绑定变量的好处是可以避免SQL注入攻击并且可以提高查询的性能因为查询计划可以在查询第一次执行时就被缓存下来。
绑定变量的限制包括
1、绑定变量只能在查询中使用。
2、绑定变量是会话级别的所以连接之间不能共用绑定变量句柄
3、绑定变量的名称必须以冒号开头。
7.7 用户自定义函数
用户自定义函数是指用户自己定义的函数可以在SQL查询中使用。用来提高查询效率。
例子
假设我们需要查询一个订单的总价可以定义一个用户自定义函数来计算订单的总价
CREATE FUNCTION calculate_total_price(order_id INT) RETURNS DECIMAL
BEGINDECLARE total DECIMAL(10,2);SELECT SUM(price * quantity) INTO total FROM order_items WHERE order_id order_id;RETURN total;
END;在上面的例子中我们定义了一个名为calculate_total_price的函数。这个函数接受一个订单号作为参数并返回一个DECIMAL类型的值表示该订单的总价。
使用这个函数可以很方便地查询订单的总价例如
SELECT order_id, calculate_total_price(order_id) AS total_price FROM orders;这将返回所有订单的订单号和总价。
优点 提高查询效率用户自定义函数可以优化查询过程提高查询效率并减少数据冗余。 提高代码复用性用户自定义函数可以在多个查询中重复使用提高代码复用性。 扩展SQL功能用户自定义函数可以扩展SQL的功能使得它能够处理更加复杂的查询问题。
缺点 复杂度增加用户自定义函数的编写需要一定的编程知识和技能对于普通用户来说可能比较困难。 可移植性问题不同数据库管理系统对用户自定义函数的支持程度可能不同这可能导致可移植性问题。 安全问题用户自定义函数可能会受到SQL注入攻击等安全问题的影响需要特别注意安全问题。
总之用户自定义函数是一种非常有用的SQL扩展方式可以在一定程度上提高查询效率但需要注意安全问题和可移植性问题并且需要投入一定的开发精力来编写。
7 .8 插件
MySQL还支持各种各样的插件。这些插件可以在MySQL中新增启动选项和状态值还可以新增INFORMATION_SCHEMA表或者在MySQL的后台执行任务等等。简单的插件列表
存储过程插件 存储过程插件可以帮你在存储过程运行后再处理一次运行结果。这 后台插件 后台插件可以让你的程序在MySQL中运行可以实现自己的网络监听、执行自己的定期任务。 INFORMATION_SCHEMA插件 提供新的内存INFORMATION_SCHEMA表。 全文解析插件这个插件提供一种处理文本的功能可以根据自己的需求来对一个文档进行分词所以如果给定一个PDF文档目录可以使用这个插件对这个文档进行分词处理。也 审计插件 审计插件在查询执行的过程中的某些固定点被调用所以它可以用作例如记录MySQL的事件日志。 认证插件 认证插件既可以在MySQL客户端也可在它的服务器端可以使用这类插件来扩展MySQL的认证功能例如可以实现PAM和LDAP认证。
7.9 字符集和校对
字符集是指一种从二进制编码到某类字符符号的映射校对”是指一组用于某个字符集的排序规则
每种字符集都可能有多种校对规则并且都有一个默认的校对规则。每个校对规则都是针对某个特定的字符集的和其他的字符集没有关系。校对规则和字符集总是一起使用的所以后面我们将这样的组合也统称为一个字符集。
MySQL的设置可以分为两类创建对象时的默认值、在服务器和客户端通信时的设置。
7.10 全文索引
MySQL提供了全文搜索的功能使用全文索引可以快速地在大型数据集中进行复杂的文本搜索全文索引跟普通的索引不同普通的索引只支持单个值的搜索而全文索引可以支持多个单词或短语的搜索需要使用FULLTEXT关键字来定义一个全文索引然后在where条件中使用MATCH AGAINST语句来进行搜索。
假设我们有一个包含文章内容的表article我们可以为该表的content列创建全文索引然后使用MATCH AGAINST语句进行搜索。
首先我们需要在content列上创建全文索引
ALTER TABLE article ADD FULLTEXT(content);然后我们可以使用MATCH AGAINST语句进行搜索如
SELECT * FROM article WHERE MATCH(content) AGAINST(MySQL full-text search);以上语句将会搜索包含MySQL full-text search这个短语的文章并将搜索结果返回。
需要注意的是MATCH AGAINST语句中的搜索条件可以是一个或多个完整的单词不需要使用通配符也可以是一个或多个短语需要使用双引号包围。此外还可以使用特殊字符进行高级搜索如、-、*等。
7.10.1 布尔全文索引
布尔全文索引它支持使用布尔运算符进行搜索例如AND、OR、NOT等。与普通的全文索引不同布尔全文索引可以对多个关键词进行精确匹配。
SELECT * FROM article WHERE MATCH(content) AGAINST(MySQL -full-text IN BOOLEAN MODE);以上语句将返回包含MySQL关键词但不包含full-text关键词的文章
布尔全文索引的优点在于可以进行更准确的搜索可以通过使用布尔运算符精确匹配多个关键词但是它的缺点在进行布尔全文索引搜索时需要特别注意关键词之间的逻辑关系。
7.11 分布式XA事务
分布式事务是指跨越多个节点或者多个数据库的一种事务处理机制。在分布式环境下不同的节点或者数据库之间需要进行协调和通信以确保事务的一致性和完整性。X/Open公司制定了XA事务规范它是分布式事务协议的一种标准支持在分布式环境下实现原子性、一致性、隔离性和持久性等ACID属性。
XA事务是通过两阶段提交2PC实现的。在2PC协议中事务被分为两个阶段准备阶段和提交阶段。 在准备阶段协调者节点也称为事务管理器会向参与者节点也称为资源管理器发送prepare请求参与者节点会把事务的执行结果写入一个“prepare装备 log”并返回一个prepared消息。在所有参与者节点都返回prepared消息后协调者节点会发送commit请求。
在提交阶段中参与者节点会将prepare log中的执行结果提交并删除然后返回commit消息。当所有参与者节点都返回commit消息后协调者节点才能向客户端返回commit消息否则会向所有参与者节点发送rollback请求回滚事务操作。
分布式XA事务适用于需要在多个事务性数据源之间进行操作的场景。比如 1、分布式系统中的事务处理 2、 金融、电商、物流等领域的业务处理 3、多系统集成在多个系统集成的场景下
7.12 查询缓存
查询缓存指的是查询完整的select查询结果MySQL查询缓存保存查询返回的完整结果。当查询命中该缓存MySQL会立刻返回结果跳过了解析、优化和执行阶段。
7.12.1 MySQL如何判断缓存命中
MySQL判断缓存命中的方法很简单缓存存放在一个引用表中通过一个哈希值引用这个哈希值包括了如下因素即查询本身、当前要查询的数据库、客户端协议的版本等一些其他可能会影响返回结果的信息。但有个缺陷如果查询语句中包含一些不确定的结果例如包含 NOW()或者 data()的查询不会被缓存事实上如果查询中包含任何用户自定义函数、存储函数、用户变量、临时表、mysq1库中的系统表或者任何包含列级别权限的表都不会被缓存。
7.12.2 查询缓存如何使用内存
查询缓存是完全存储在内存中的。
除了查询结果之外需要缓存的还有维护相关的数据这些管理维护数据结构大概需要40KB的内存资源。
除此之外MySQL用于查询缓存的内存被分成一个个的数据块数据块是变长的。每一个数据块中存储了自己的类型、大小和存储的数据本身还外加指向前一个和后一个数据块的指针当服务器启动的时候它先初始化查询缓存需要的内存。这个内存池初始是一个完整的空闲块。这个空闲块的大小就是你所配置的查询缓存大小再减去用于维护元数据的数据结构所消耗的空间。
当有查询结果需要缓存的时候MySQL先从大的空间块中申请一个数据块用于存储结果。这个数据块需要大于参数query_cache_min_res_unit的配置即使查询结果远远小于此仍需要至少申请query_cache_min_res_unit空间。
因为需要先锁住空间块然后找到合适大小数据块所以相对来说分配内存块是一个非常慢的操作。MySQL尽量避免这个操作的次数。当需要缓存一个查询结果的时候它先选择一个尽可能小的内存块然后将结果存入其中。如果数据块全部用完但仍有剩余数据需要存储那么MySQL会申请一块新数据块继续存储结果数据。当查询完成时如果申请的内存空间还有剩余MySQL会将其释放并放入空闲内存部分。
7.12.3 什么情况下查询缓存能发挥作用
只有当缓存带来的资源节约大于其本身的资源消耗时才会给系统带来性能提升。
7.12.4 如何配置和维护查询缓存
旦理解查询缓存工作的原理配置起来就很容易了。只有很少的参数可供配置
query_cache_type是否打开查询缓存。可以设置成OFF、ON或DEMAND。 query_cache_size查询缓存使用的总内存空间单位是字节。这个值必须是1024的整数倍。 query_cache_min_res_unit在查询缓存中分配内存块时的最小单位。 query_cache_limitMySQL能够缓存的最大查询结果。如果查询结果大于这个值则不会被缓存。 query_cache_wlock_invalidate如果某个数据表被其他的连接锁住是否仍然从查询缓存中返回结果。这个参数默认是OFF。
7.12.5 InnoDB和查询缓存
因为InnoDB有自己的MVCC机制所以相比其他存储引擎InnoDB和查询缓存的交互要更加复杂
事务是否可以访问查询缓存取决于当前事务ID以及对应的数据表上是否有锁。每一个InnoDB表的内存数据字典都保存了一个事物ID号如果当前事务ID小于该事务ID则无法访问查询缓存。
原则上在InnoDB的MVCC架构下当某些修改不影响其他事务读取一致的数据时是可以使用查询缓存的。InnoDB就做了一个简化让所有有加锁操作的事务都不使用任何查询缓存。
但是InnoDB 存储引擎和查询缓存之间存在着一些冲突。在使用 InnoDB 存储引擎时由于它使用了行级锁和 MVCC 等机制来保证事务的隔离性和并发性能查询缓存将无法使用。这是因为每个查询可能会访问不同的数据行而查询缓存只能缓存完全相同的查询结果所以InnoDB存储引擎不建议用查询缓存来提高性能
7.12.6 通用查询缓存优化 用多个小表代替一个大表对查询缓存有好处。 批量写入时只需要做一次缓存失效所以相比单条写入效率更好 避免缓存不必要的查询结果。如果某些查询结果很少被使用可以将其从缓存中删除以便给更常用的查询结果腾出空间。 可以增加查询缓存的大小以便缓存更多的查询结果。可以通过修改 query_cache_size 参数来设置缓存大小
7.13 总结
回顾一下MySQL高级特性。
分区表
分区表是一种粗粒度的、简易的索引策略适用于大数据量的过滤场景。最适合的场景是在没有合适的索引时对其中几个分区进行全表扫描或者是只有一个分区和索引是热点而且这个分区和索引能够都在内存中限制单表分区数不要超过150个并且注意某些导致无法做分区过滤的细节分区表对于单条记录的查询并没有什么优势需要注意这类查询的性能。
视图
对好几个表的复杂查询使用视图有时候会大大简化问题。当视图使用临时表时无法将WHERE条件下推到各个具体的表也不能使用任何索引需要特别注意这类查询的性能。如果为了便利使用视图是很合适的。
外键
外键限制会将约束放到MySQL中这对于必须维护外键的场景性能会更高。不过这也会带来额外的复杂性和额外的索引消耗还会增加多表之间的交互会导致系统中更多的锁和竞争。外键可以被看作是一个确保系统完整性的额外的特性但是如果设计的是一个高性能的系统那么外键就显得很臃肿了。很多人在更在意系统的性能的时候都不会使用外键而是通过应用程序来维护。抱歉您没有提供第七章的主题或内容无法为您提供总结。请提供更多信息。抱歉您没有提供第七章的主题或内容无法为您提供总结。请提供更多信息。
存储过程
MySQL本身实现了存储过程、触发器、存储函数和事件老实说这些特性并没什么特别的。而且对于基于语句的复制还有很多问题。通常使用这些特性可以帮你节省很多的网络开销——很多情况下减少网络开销可以大大提升系统的性能。在某些经典的场景下你可以使用这些特性例如中心化业务逻辑、绕过权限系统等等但需要注意在MySQL中这些特性并没有别的数据库系统那么成熟和全面。
绑定变量
当查询语句的解析和执行计划生成消耗了主要的时间那么绑定变量可以在一定程度上解决问题。因为只需要解析一次对于大量重复类型的查询语句性能会有很大的提高。另外执行计划的缓存和传输使用的二进制协议这都使得绑定变量的方式比普通SQL语句执行的方式要更快。
插件
使用C或者C编写的插件可以让你最大程度地扩展MySQL功能。插件功能非常强大我们已经编写了很多UDF和插件在MySQL中解决了很多问题。
字符集
字符集是一种字节到字符之间的映射而校对规则是指一个字符集的排序方法。很多人都使用Latin1默认字符集对英语和某些欧洲语言有效或者UTF-8。如果使用的是UTF-8那么在使用临时表和缓冲区的时候需要注意MySQL会按照每个字符三个字节的最大占用空间来分配存储空间这可能消耗更多的内存或者磁盘空间。注意让字符集和MySQL字符集配置相符否则可能会由于字符集转换让某些索引无法正常使用。
全文索引
在本书编写的时候只有MyISAM支持全文索引不过据说从MySQL 5.6开始 InnoDB也将支持全文索引。MyISAM因为在锁粒度和崩溃恢复上的缺点使得在大型全文索引场景中基本无法使用。这时我们通常帮助客户构建和使用Sphinx来解决全文索引的问题。
XA事务
很少有人用MySQL的XA事务特性。除非真正明白参数innodb_support_xa的意义否则不要修改这个参数的值并不是只有显式使用XA事务时才需要设置这个参数。InnoDB和二进制日志也是需要使用XA事务来做协调的从而确保在系统崩溃的时候数据能够一致地恢复。
查询缓存
完全相同的查询在重复执行的时候查询缓存可以立即返回结果而无须在数据库中重新执行一次。根据我们的经验在高并发压力环境中查询缓存会导致系统性能的下降甚至僵死。如果一定要使用查询缓存那么不要设置太大内存而且只有在明确收益的时候才使用。那该如何判断是否应该使用查询缓存呢建议使用Percona Server观察更细致的日志并做一些简单的计算。还可以查看缓存命中率并不总是有用、“INSERTS和SELECT比率”这个参数也并不直观、或者“命中和写入比率”这个参考意义较大。查询缓存是一个非常方便的缓存对应用程序完全透明无须任何额外的编码但是如果希望有更高的缓存效率我们建议使用memcached或者其他类似的解决方案。
附录D Explain
EXPLAIN关键字用于分析MySQL执行查询的方式并返回一个解释结果集。 调用EXPLAIN,只需要在查询中的 select 关键字之前增加EXPLAIN。
EXPLAIN SELECT * FROM table_name WHERE column_name value;执行以上语句后MySQL会返回一个结果集包含了查询执行计划的详细信息。该结果集中的每一行对应查询执行计划中的一步操作你可以通过分析这些数据来确定查询的性能瓶颈以及优化的方向
id: 查询执行计划中每个操作的唯一标识
select_type: 操作类型如简单查询、联合查询等
table: 操作涉及的表名
type: 表访问的方式如全表扫描、索引扫描等
possible_keys: 可能使用的索引
key: 实际使用的索引
key_len: 实际使用的索引长度
ref: 与索引比较的列或常量值
rows: 预估的结果集行数
Extra: 额外的信息如排序、临时表等。
通过分析这些信息你可以更好地理解查询的执行过程并且采取相应的优化措施例如创建合适的索引、优化查询语句等以提高MySQL查询的性能。
EXPLAIN案例
EXPLAIN命令执行后会输出查询执行计划的各个参数列。下面我们介绍每个参数列的作用并结合一个示例来说明。
假设我们有一个包含两个表的数据库students和scores。其中students表包含学生个人信息scores表包含学生每门课的成绩。现在需要查询某个学生的总分和平均分。
我们可以通过以下语句来完成查询
EXPLAIN SELECT s.*, SUM(sc.score) AS total_score, AVG(sc.score) AS avg_score
FROM students s JOIN scores sc ON s.id sc.student_id
WHERE s.name Tom;该查询使用了JOIN和WHERE操作需要对多表进行关联和过滤。我们通过EXPLAIN命令来查看该查询的执行计划
id select_type table partitions type possible_keys key key_len ref rows filtered Extra
1 SIMPLE s NULL ref PRIMARY PRIMARY 4 const 1 100.00 Using index
1 SIMPLE sc NULL ref student_id_idx student_id_idx 4 exam.s.id 1 100.00 Using index condition下面我们具体介绍每个参数列的作用 id每个子查询或分段查询都有一个唯一的标识符可以根据此标识符区分不同的查询。 select_type表示查询的类型它的值包含以下几种 SIMPLE简单查询不包含子查询或者UNION查询等。 PRIMARY最外层的查询包含多个子查询或者UNION查询等。 UNIONUNION查询。 DEPENDENT UNIONUNION查询依赖于外部查询中的值。 SUBQUERY子查询。 DEPENDENT SUBQUERY依赖于外部查询的子查询。 DERIVED派生表包含子查询和UNION查询等。 table表示查询使用的数据库表。 partitions表示查询使用的分区。 type表示连接类型它的值包含以下几种 NULL MySQL不访问任何表索引直接返回结果 system表只有一行。 const通过索引或者主键定位到唯一一条记录。 eq_ref使用唯一索引或者主键的 JOIN 查询对于每个匹配到的行只有一条。 ref使用普通索引的JOIN查询。 range使用索引范围检索数据。 index使用索引进行全表扫描。 all进行全表扫描。 结果值从最好到最坏以此是null system const eq_ref ref range index ALL 一般来说 我们需要保证查询至少达到 range 级别 最好达到ref 。 possible_keys表示所有可能使用的索引可用于优化查询性能。 key表示实际使用的索引。 key_len表示索引的长度。 ref表示使用索引的哪个列或者常量值来与表中的数据进行匹配。 rows表示需要扫描的行数也就是MySQL估算出来的大致扫描行数。 filtered表示结果集的过滤比例百分比。 Extra表示额外的信息比如 Using index该查询使用了覆盖索引。 Using temporary该查询需要创建临时表以完成查询通常出现在ORDER BY和GROUP BY查询中。 Using filesort该查询需要对结果集进行文件排序。 Using where该查询使用了WHERE条件过滤数据。 Using join buffer该查询使用了连接缓存。 Using index condition该查询使用了索引条件过滤数据。 Impossible where该查询的WHERE条件不可能被满足例如WHERE 10。 Select tables optimized away该查询的结果可以直接从索引中获取无需访问表。 通过上面的查询结果我们可以分析出以下信息 查询中使用了两个表students和scores。 查询使用了索引加速查询其中students表使用了PRIMARY索引scores表使用了student_id_idx索引。 查询使用了连接缓存可以有效提高查询性能。 查询的结果集非常小使用了索引加速和连接缓存等优化技术执行效率和性能比较高。
总之通过EXPLAIN命令我们可以了解查询的执行计划和优化方案以便更好地优化查询性能和效率。
EXPLAIN的两个变种
分别是EXPLAIN EXTENOED 和EXPLAIN PRATITION 前者的作用 可以让我们知道查询优化器是如何转换语句的后者会显示将访问的分区
假设有一个包含百万条记录的user表现在我们想查询age在25岁以下的用户且按照id降序排序。我们可以使用以下SQL语句进行查询
EXPLAIN EXTENDED
SELECT * FROM user
WHERE age 25
ORDER BY id DESC;执行完以上语句后我们可以使用SHOW WARNINGS命令查看查询执行计划信息。输出的结果类似下面这样
-----------------------------------------------------------------------------------------------------------------------------------------
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
-----------------------------------------------------------------------------------------------------------------------------------------
| 1 | SIMPLE | user | NULL | range | age | age | 4 | NULL | 5000 | 50.00 | Using where; Backward index scan; Descending |
-----------------------------------------------------------------------------------------------------------------------------------------从输出结果可以看出查询使用了age列的索引扫描了5000行记录。Using where说明查询使用了WHERE过滤条件Backward index scan说明索引被反向扫描Descending说明排序是降序的。这些信息可以帮助我们更好地理解查询的执行过程发现潜在的性能瓶颈并在必要时优化查询语句。
注在Mysql5.7以前的版本中想要显示 partitions 需要使用 explain partitions 命令想要显示filtered 需要使用 explain extended 命令。在5.7版本后默认explain直接显示partitions和 filtered中的信息。
重新SELECT语句
在·MySQL 5.6.3·以前并不支持对INSERT、UPDATE、DELETE等语句解释那时只能将这项语句转换成一个等价的SELECT语句非常不方便但更新到MYSQL 5.6.3以后就可以 EXPLAIN SELECTUPDATEDELETE
总结经过一个月的时间终于把第一阶段完成了高性能Mysql的第二阶段我准备2024年在学习
