MySQL 架构与历史
MySQL架构可以在多种不同场景中应用,足够灵活。MySQL架构的设计将查询处理及其他系统任务和数据的存储/提取相分离。这种处理和存储分离的设计可以在使用时根据性能、特性,以及其他需求来选择数据存储的方式。
MySQL 逻辑架构
第一层负责连接处理、授权认证、安全等
第二层包括查询解析、分析、优化、缓存以及所有的内置函数,所有跨存储引擎的功能都在这一层实现:存储过程、触发器、视图等。
第三层包含了存储引擎(storage engine)。存储引擎负责MySQL中数据的存储和提取。服务器通过API(table handler)与储存引擎进行通信,这些API屏蔽了不同储存引擎之间的差异。
连接管理与安全性
每个客户端连接都会在服务器进程中拥有一个线程,这个连接的查询只会在这个单独的线程中执行,服务器会负责缓存线程以提供客户端连接。
优化与执行
MySQL会解析查询,并创建内部数据结构(解析树),然后对其进行各种优化,包括重写查询、决定表的读取顺序,以及选择合适的索引等。用户可以通过特殊的关键字提示(hint)优化器,影响它的决策过程。也可以请求优化器解释(explain)优化过程的各个因素,使用户可以知道服务器是如何进行优化决策的。
对于SELECT语句,在解析查询之前,服务器会先检查查询缓存,如果能够在其中找到对应的查询,服务器就不必再执行查询解析、优化和执行的整个过程,而是直接返回查询缓存中的结果集。
并发控制
MySQL包含在两个层面的并发控制:服务器层与存储引擎层。
读写锁
共享锁和排他锁,也叫读锁和写锁。
锁粒度
- 表锁
- 行级锁,是在储存引擎层实现
事务
ACID表示原子性(atomicity)、一致性(consistency)、隔离性(isolation)和持久性(durability)
隔离级别
- READ UNCOMMITTED(未提交读),事务中的修改,即使没有提交,对其他事务也都是可见的。事务可以读取未提交的数据,这也被称为脏读(Dirty Read)。
- READ COMMITTED(提交读),一个事务从开始直到提交之前,所做的任何修改对其他事务都是不可见的。这个级别有时候也叫做不可重复读(nonrepeatable read),因为两次执行同样的查询,可能会得到不一样的结果。
- REPEATABLE READ(可重复读),InnoDB默认级别。解决了脏读的问题。个别场景中(间隙锁可以避免幻读),该级别无法解决幻读,事务B在事务A读取范围记录时插入的记录叫幻行。InnoDB通过多版本并发控制解决幻读问题。
- SERIALIZABLE(可串行化)
死锁
数据库系统实现了各种死锁检测和死锁超时机制。InnoDB目前处理死锁的方法是,将持有最少行级排他锁的事务进行回滚
事务日志
事务日志可以帮助提高事务的效率。使用事务日志,存储引擎在修改表的数据时只需要修改其内存拷贝,再把该修改行为记录到持久在硬盘上的事务日志中,而不用每次都将修改的数据本身持久到磁盘。事务日志采用的是追加的方式写日志是顺序io,所以采用事务日志的方式相对来说要快得多。事务日志持久以后,内存中被修改的数据在后台可以慢慢地刷回到磁盘。目前大多数存储引擎都是这样实现的,我们通常称之为预写式日志(Write-Ahead Logging),修改数据需要写两次磁盘。如果数据的修改已经记录到事务日志并持久化,但数据本身还没有写回磁盘,此时系统崩溃,存储引擎在重启时能够自动恢复这部分修改的数据。
MySQL中的事务
MySQL默认采用自动提交模式。InnoDB采用两阶段提交协议(start,commit,rollback)。也支持显式锁定(select … lock in share mode;select … for update;lock tables/unlock tables)。
多版本并发控制(MVCC)
MVCC是行级锁的变种,是通过保存数据在某个时间点的快照来实现。实现方式典型的有乐观(optimistic)并发控制和悲观(pessimistic)并发控制。
InnoDB的MVCC,是通过在每行记录后面保存两个隐藏的列来实现的。这两个列,一个保存了行的创建时间,一个保存行的过期时间(或删除时间)。当然存储的并不是实际的时间值,而是系统版本号(system version number)。每开始一个新的事务,系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号,用来和查询到的每行记录的版本号进行比较。MVCC只在RC和RR级别有效。
InnoDB在RR级别下的MVCC:
- SELECT,InnoDB会根据以下两个条件检查每行记录:a. InnoDB只查找版本早于当前事务版本的数据行。b. 行的删除版本要么未定义,要么大于当前事务版本号。
- INSERT,InnoDB为新插入的每一行保存当前系统版本号作为行版本号。
- DELETE,InnoDB为删除的每一行保存当前系统版本号作为行删除标识。
- UPDATE,InnoDB为插入一行新记录,保存当前系统版本号作为行版本号,同时保存当前系统版本号到原来的行作为行删除标识。
MySQL的储存引擎
在文件系统中,MySQL将每个数据库(也可以称之为schema)保存为数据目录下的一个子目录。创建表时,MySQL会在数据库子目录下创建一个和表同名的.frm文件保存表的定义。
InnoDB
InnoDB采用MVCC来支持高并发,实现了四个标准的隔离级别。通过间隙锁(next-key locking)策略防止幻读的出现。
InnoDB表是基于聚簇索引建立的,它的二级索引(secondary index,非主键索引)中必须包含主键列,所以如果主键列很大的话,其他的所有索引都会很大。因此,若表上的索引较多的话,主键应当尽可能的小。
InnoDB内部做了很多优化,包括从磁盘读取数据时采用的可预测性预读,能够自动在内存中创建hash索引以加速读操作的自适应哈希索引(adaptive hash index),以及能够加速插入操作的插入缓冲区(insert buffer)。
MyISAM
MyISAM引擎设计简单,数据以紧密格式存储,所以在某些场景下的性能很好。
MyISAM提供了大量的特性,包括全文索引、压缩、空间函数(GIS)等,但MyISAM不支持事务和行级锁,而且有一个毫无疑问的缺陷就是崩溃后无法安全恢复。对于只读的数据,或者表比较小、可以忍受修复(repair)操作,则依然可以继续使用MyISAM。
MyISAM会将表存储在两个文件中:数据文件和索引文件,分别以.MYD和.MYI为扩展名。MyISAM表可以包含动态或者静态(长度固定)行。
MyISAM对整张表加锁,而不是针对行。读取时会对需要读到的所有表加共享锁,写入时则对表加排他锁。但是在表有读取查询的同时,也可以往表中插入新的记录(这被称为并发插入,CONCURRENT INSERT)。
对于MyISAM表,MySQL可以手工或者自动执行检查和修复操作,但这里说的修复和事务恢复以及崩溃恢复是不同的概念。执行表的修复可能导致一些数据丢失,而且修复操作是非常慢的。
对于MyISAM表,即使是BLOB和TEXT等长字段,也可以基于其前500个字符创建索引。MyISAM也支持全文索引,这是一种基于分词创建的索引,可以支持复杂的查询。
创建MyISAM表的时候,如果指定了DELAY_KEY_WRITE选项,在每次修改执行完成时,不会立刻将修改的索引数据写入磁盘,而是会写到内存中的键缓冲区(in-memorykey buffer),只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入到磁盘。这种方式可以极大地提升写入性能,但是在数据库或者主机崩溃时会造成索引损坏,需要执行修复操作。
如果表在创建并导入数据以后,不会再进行修改操作,那么这样的表或许适合采用MyISAM压缩表。
Schema与数据类型优化
选择优化的数据类型
选择可以满足需求的占用空间最小的数据类型,因为他们占用更少的磁盘、内存和cpu缓存,并且处理时需要的CPU周期也更少; 尽量使用内建类型和简单类型。 除了InnoDB需要存储稀疏数据(多数数据为空值)的场景下,避免NULL,因为可为NULL的列使得 索引、索引统计和值比较都更复杂; MySQL支持很多数据类型的别名,但是实际使用的类型都是一样。
整数类型
TINYINT(8),SMALLINT(16),MEDIUMINT(24),INT(32),BIGINT(64)。 分有符号和无符号。MySQL可以为整数类型指定宽度,它不会限制值的合法范围,只是规定了MySQL的一些交互工具用来显示字符的个数。
实数类型
可以使用DECIMAL 存储大整数。 对于DECIMAL列,可以指定小数点前后所允许的最大位数。这会影响列的空间消耗。MySQL 5.0和更高版本将数字打包保存到一个二进制字符串中(每4个字节存9个数字)。例如,DECIMAL(18,9)小数点两边将各存储9个数字,一共使用9个字节:小数点前的数字用4个字节,小数点后的数字用4个字节,小数点本身占1个字节。
字符串类
VARCHAR
VARCHAR类型用于存储可变长字符串,VARCHAR需要使用1或2个额外字节记录字符串的长度:如果列的最大长度小于或等于255字节,则只使用1个字节表示,否则使用2个字节。 如果UPDATE使长度变长,则InnoDB需要分裂页使行可以放进页内。
CHAR
定长,不需要额外记录长度。
BLOB和TEXT
BLOB和TEXT都是为存储很大的数据而设计的字符串数据类型,分别采用二进制和字符方式存储。 当BLOB和TEXT值太大时,InnoDB会使用专门的“外部”存储区域 来进行存储,此时每个值在行内需要1~4个字节存储一个指针,然后在外部存储区域存储实际的值。 只对每个列的最 前max_sort_length字节排序。
ENUM
枚举额外存储枚举值并在列存位置。所以枚举是按照位置整数来排序,并且修改的时候需要ALTER TABLE,如果数据量大枚举在映射到值时开销也会大。
日期和时间
DATETIME这个类型能保存大范围的值,从1001年到9999年,精度为秒。它把日期和时间封装到 格式为YYYYMMDDHHMMSS的整数中,与时区无关。使用8个字节的存储空间。
TIMESTAMP 类型保存了从1970年1月1日以来的秒数,它和UNIX时间戳相同。TIMESTAMP只使用4个字节的存储空间,因此只能表示从1970年到2038年。有时区。
位数据类型
BIT MyISAM可以节省存储空间,InnoDB不能节省存储空间。
SET 改变列定义代价高,不能使用索引,可以使用整型替代。
随机主键
会导致INSERT以及一些 SELECT语句变得很慢,这会导致页分裂、磁盘随机访问,以及对于聚簇存储引擎产生聚簇索引碎片。SELECT语句会变得更慢,因为逻辑上相邻的行会分布在磁盘和内存的不同地方。 随机值导致缓存失效。
MySQL schema设计中的陷阱
- 不宜太多的列,MySQL的存储引擎API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据,然后在服务器层将缓冲内容解码成各个列。
- 不宜太多的关联,MySQL最多JOIN 61张表。
范式和反范式
范式
- 范式化的更新操作通常比反范式化要快。
- 当数据较好地范式化时,就只有很少或者没有重复数据,所以只需要修改更少的数据。
- 范式化的表通常更小,可以更好地放在内存里,所以执行操作会更快。
- 范式会把一些信息单独存在一张表中,所以不需要使用DISTINCT或者GROUP BY就可以查询。
- 但是范式需要嵌套循环关联
反范式
反范式可以不使用关联,并且增加一个索引就可以查询。
范式和反范式不一定要极端,在范式化下也可以少许冗余以便查询。
缓存表或汇总表
可以使用缓存表或汇总表冗余统计数据。汇总表加锁会影响其他写入,所以可以分散汇总数据比如把汇总结果分散在100行,随机进行更新。最后取SUM
创建高性能的索引(又名键(key))
MySQL索引对多列索引使用最左前缀列
索引的类型
B-Tree索引
MyISAM使用前缀压缩技术使得索引更小,但InnoDB则按照原数据格式进行存储。MyISAM索引通过数据的物理位置引用被索引的行,而InnoDB则根据主键引用被索引的行。
B-Tree通常意味着所有的值都是按顺序存储的,并且每一个叶子页到根的距离相同。
使用B-Tree索引不需要全表扫描,通过比较节点页的值和要查找的值逐渐向下层查找。节点页左指针小于节点页值,右指针大于等于节点页值。
叶子节点指向的是被索引的数据而不是其他的节点页。因为B-Tree对索引列是顺序存储的,所以很适合范围查找。
下图标识索引last_name、frst_name和生日的储存形式
B-Tree索引适用于全值匹配、范围匹配、匹配左列、匹配列值的前缀,同样精确匹配左前列范围匹配后一列以及支持覆盖查询。同样B-Tree索引适用于Ordr By。
B-Tree的限制
- 如果不是按照索引的最左列开始查找,则无法使用索引。
- 不能跳过索引中的列。
- 如果查询中有某个列的范围查询,则右边的列不能利用索引。
哈希索引
把所有索引列计算一个哈希值,如果哈希冲突以链表方式解决。要在哈希冲突时准确找到数据,必须在WHERE条件中带入哈希值和对应列值。同时在哈希表中保存指向数据的指针。哈希索引只支持精确匹配。
限制:
- 哈希索引只包含哈希值和行指针,而不存储字段值,所以不能使用索引中的值来避免读取行。不过,访问内存中的行的速度很快,所以大部分情况下这一点对性能的影响并不明显。
- 哈希索引数据并不是按照索引值顺序存储的,所以也就无法用于排序。
- 哈希索引也不支持部分索引列匹配查找,因为哈希索引始终是使用索引列的全部内容来 计算哈希值的。
- 哈希索引只支持等值比较查询,包括=、IN()、<=>(注意<>和<=>是不同的操作,<>和NULL比较得出NULL,<=>安全比较可以和NULL比较得出0)。也不支 持任何范围查询
- 访问哈希索引的数据非常快,除非有很多哈希冲突
- 如果哈希冲突很多的话,在冲突多的索引上操作代价很大
InnoDB内建“自适应哈希索引”。当InnoDB注意到某些索引值被使用得非常频繁时,它会在内存中基于B-Tree索引之上再创建一个哈希索引,用户无法控制或者配置,不过可以关闭。
空间数据索引(R-Tree)
全文索引
索引的优点
索引可以让服务器快速地定位到表的指定位置。最常见的B-Tree索引,按照顺序存储数据,所以MySQL可以用来做ORDER BY和GROUP BY操作。因为数据是有序的,所以B-Tree也就会将相关的列值都存储在一起。因为索引中存储了实际的列值,所以某些查询只使用索引就能够完成全部查询(覆盖查询)。
- 索引大大减少了服务器需要扫描的数据量
- 索引可以帮助服务器避免排序和临时表
- 索引可以将随机I/O变为顺序I/O
高性能的索引策略
独立的列
不能在索引上使用表达式或者函数
前缀索引和索引选择性
前缀索引最好根据选择性设计索引的前缀长度,选择性是指不重复的索引值和数据表的记录总数的比值。前缀的选择在空间和效率上取个人认为的平衡即可,一般可以认为增加前缀长度对选择性影响已经很小的长度即可。
多列索引
如果在每个字段上都单独加索引,5.0版本之前需要扫描全表,5.0之后服务器能够同时使用这两个单列索引进行扫描,并将结果进行合并。这种算法有三个变种:OR条件的联合AND条件的相交组合前两种情况的联合及相交。
- 当出现服务器对多个索引做相交操作时(通常有多个AND条件),通常意味着需要一个包含所有相关列的多列索引,而不是多个独立的单列索引。
- 当服务器需要对多个索引做联合操作时(通常有多个OR条件),通常需要耗费大量CPU和内存资源在算法的缓存、排序和合并操作上。特别是当其中有些索引的选择性不高,需要合并扫描返回的大量数据的时候。
- 优化器不会把这些计算到“查询成本”中,优化器只关心随机页面读取。这会使得查询的成本被“低估”.
合适的索引列顺序
正确的索引列顺序需要考虑order by 和 group by 的需要。在不考虑排序和分组时,将选择性最高的列放到索引最前列,但是也要考虑索引的基数(比如guest的数量可能会比单个用户数量差几个数量级),还是要通过实验选择合适的顺序。
聚簇索引
聚簇表示数据行和相邻的键值紧凑地存储在一起。聚簇索引不只是索引的类型而且是一种数据存储方式。指索引和数据保存在同一个结构中。B-Tree的聚簇索引存放在叶子页中,一张表只能有一个聚簇索引。InnoDB通过主键聚簇数据。如果没有定义主键,InnoDB会选择一个唯一的非空索引代替。如果没有这样的索引,InnoDB会隐式定义一个主键来作为聚簇索引。
优点:
- 可以把相关数据保存在一起。
- 数据访问更快。聚簇索引将索引和数据保存在同一个B-Tree中,因此从聚簇索引中获取数据通常比在非聚簇索引中查找要快。
- 使用覆盖索引扫描的查询可以直接使用页节点中的主键值。
缺点:
- 聚簇数据最大限度地提高了I/O密集型应用的性能,但如果数据全部都放在内存中,则访问的顺序就没那么重要了,聚簇索引也就没什么优势了。
- 插入速度严重依赖于插入顺序。按照主键的顺序插入是加载数据到InnoDB表中速度最快的方式。但如果不是按照主键顺序加载数据,那么在加载完成后最好使用OPTIMIZETABLE命令重新组织一下表。
- 更新聚簇索引列的代价很高,因为会强制InnoDB将每个被更新的行移动到新的位置。
- 基于聚簇索引的表在插入新行,或者主键被更新导致需要移动行的时候,可能面临“页分裂”的问题。当行的主键值要求必须将这一行插入到某个已满的页中时,存储引擎会将该页分裂成两个页面来容纳该行,这就是一次页分裂操作。页分裂会导致表占用更多的磁盘空间。
- 聚簇索引可能导致全表扫描变慢,尤其是行比较稀疏,或者由于页分裂导致数据存储不连续的时候。
- 二级索引(非聚簇索引)可能比想象的要更大,因为在二级索引的叶子节点包含了引用行的主键列。
- 二级索引访问需要两次索引查找,而不是一次。二级索引保存的是主键值。
InnoDB和MyISAM的数据分布对比(聚簇索引和非聚簇索引的数据分布有区别)
MyISAM按照数据插入的顺序存储在磁盘上,索引只存了数据的地址。
MyISAM中主键索引和其他索引在结构上没有什么不同。主键索引就是一个名为PRIMARY的唯一非空索引。
InnoDB 数据和所以存储在一起,数据插入主键不过不是顺序会导致列分页和碎片,而二级索引存储的是主键。
区别:
聚集索引的顺序插入:
聚簇索引的非顺序插入:
- 写入的目标页可能已经刷到磁盘上并从缓存中移除,或者是还没有被加载到缓存中,InnoDB在插入之前不得不先找到并从磁盘读取目标页到内存中。这将导致大量的随机I/O。
- 因为写入是乱序的,InnoDB不得不频繁地做页分裂操作,以便为新的行分配空间。页分裂会导致移动大量数据,一次插入最少需要修改三个页而不是一个页。
- 由于频繁的页分裂,页会变得稀疏并被不规则地填充,所以最终数据会有碎片。
覆盖索引
MySQL也可以使用索引来直接获取列的数据,这样就不再需要读取数据行。如果一个索引包含(或者说覆盖)所有需要查询的字段的值,我们就称之为“覆盖索引”
优点:
- 索引条目通常远小于数据行大小,所以如果只需要读取索引,那MySQL就会极大地减少数据访问量。这对缓存的负载非常重要,因为这种情况下响应时间大部分花费在数据拷贝上。覆盖索引对于I/O密集型的应用也有帮助,因为索引比数据更小,更容易全部放入内存中
- 因为索引是按照列值顺序存储的(至少在单个页内是如此),所以对于I/O密集型的范围查询会比随机从磁盘读取每一行数据的I/O要少得多。
- 一些存储引擎如MyISAM在内存中只缓存索引,数据则依赖于操作系统来缓存,因此要访问数据需要一次系统调用。这可能会导致严重的性能问题,尤其是那些系统调用占了数据访问中的最大开销的场景。
- 由于InnoDB的聚簇索引,覆盖索引对InnoDB表特别有用。InnoDB的二级索引在叶子节点中保存了行的主键值,所以如果二级主键能够覆盖查询,则可以避免对主键索引的二次查询。
select * 不能使用覆盖索引,LIKE操作不能使用覆盖索引;如果无法使用覆盖索引,可以使用 延迟关联 (在join中使用子查询);InnoDB的二级索引会存储主键,所以select主键和二级索引的操作会覆盖查询。
使用索引扫描来做排序
MySQL有两种方式可以生成有序的结果:通过排序操作;或者按索引顺序扫描。
索引扫描的限制:
- 索引的列顺序和ORDER BY子句的顺序完全一致,并且所有列的排序方向(倒序或正序)都一样时。
- 如果查询需要关联多张表(即使是join自身),则只有当ORDER BY子句引用的字段全部为第一个表时。
- 需要满足索引的最左前缀的要求。
有一种情况下ORDER BY子句可以不满足索引的最左前缀的要求,就是前导列为常量的时候。(select a,b from table where a=1 order by b)
压缩(前缀压缩)索引
MyISAM使用前缀压缩来减少索引的大小,从而让更多的索引可以放入内存中,例如,索引块中的第一个值是“perform”,第二个值是“performance”,那么第二个值的前缀压缩后存储的是类似“7,ance”
冗余和重复索引
重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引。冗余索引和重复索引有一些不同。如果创建了索引(A,B),再创建索引(A)就是冗余索引
未使用的索引
删除即可
索引和锁
InnoDB只有在访问行的时候才会对其加锁,而索引能够减少InnoDB访问的行数,从而减少锁的数量。但这只有当InnoDB在存储引擎层能够过滤掉所有不需要的行时才有效。如果索引无法过滤掉无效的行,那么在InnoDB检索到数据并返回给服务器层以后,MySQL服务器才能应用WHERE子句(19)。
InnoDB在二级索引上使用共享(读)锁,但访问主键索引需要排他(写)锁。
索引碎片化
有三种类型的数据碎片:
- 行碎片,这种碎片指的是数据行被存储为多个地方的多个片段中。
- 行间碎片,行间碎片是指逻辑上顺序的页,或者行在磁盘上不是顺序存储的。
- 剩余空间碎片,剩余空间碎片是指数据页中有大量的空余空间。这会导致服务器读取大量不需要的数据,从而造成浪费。
查询性能优化
优化包含查询优化、索引优化、库表结构优化
为什么查询速度会慢
查询由一系列子任务组成,查询的生命周期包含:从客户端,到服务器,在服务器上进行解析,生成执行计划,执行,返回结果给客户端。其中“执行”包括为了检索数据到储存引擎的调用以及调用后的数据处理(排序,分组)。优化查询实际上是优化子任务,要么消除一些子任务,要么减少子任务的执行次数,要么让子任务运行的更快。
慢查询基础:优化数据访问
- 确认应用程序是否在检索大量超过需要的数据。这通常意味着访问了太多的行,但有时 候也可能是访问了太多的列。
- 确认MySQL服务器层是否在分析大量超过需要的数据行。
是否向数据库请求了不需要的数据
不要查询不需要的记录
MySQL使用半双工的通讯方式,会一次性返回所有数据,所以提前关闭结果集并不会少取数据。
多表关联时不要返回全部的列
不要select *
查询优化器无法使用覆盖扫描优化,还会带来额外的IO,内存,和cpu消耗。
重复数据使用缓存
MySQL是否再扫描额外的记录
衡量指标:
- 响应时间
- 扫描的行数
- 返回的行数
响应时间
响应时间包括服务时间和排队时间(等待io,等待锁),响应时间既可能是一个问题的结果也可能是一个问题的原因
扫描的行数和返回的行数
扫描的行数和访问类型
在EXPLAIN语句中的type列反应了访问类型。访问类型有很多种,从全表扫描到索引扫 描、范围扫描、唯一索引查询、常数引用等。这里列的这些,速度是从慢到快,扫描的行数也是 从大到小。
一般MySQL能够使用如下三种方式应用WHERE条件,从好到坏依次为:
- 在索引中使用WHERE条件来过滤不匹配的记录。这是在存储引擎层完成的。
- 使用索引覆盖扫描(在Extra列中出现了Using index)来返回记录,直接从索引中过滤不需要的记录并返回命中的结果。这是在MySQL服务器层完成的,但无须再回表查询记录。
- 从数据表中返回数据,然后过滤不满足条件的记录(在Extra列中出现Using Where)。这在MySQL服务器层完成,MySQL需要先从数据表读出记录然后过滤。
如果发现查询需要扫描大量的数据但只返回少数的行,那么通常可以尝试下面的技巧去优化它:
- 使用索引覆盖扫描,把所有需要用的列都放到索引中,这样存储引擎无须回表获取对应行就可以返回结果了。
- 改变库表结构。例如使用单独的汇总表。
- 重写这个复杂的查询,让MySQL优化器能够以更优化的方式执行这个查询。
重构查询的方式
一个复杂的查询还是多个简单的查询
在传统实现中,总是强调需要数据库层完成尽可能多的工作,这样做的逻辑在于以前总是认为网络通信、查询解析和优化是一件代价很高的事情。但是这样的想法对于MySQL并不适用,MySQL从设计上让连接和断开连接都很轻量级。但是要看实际情况。有时候,将一个大查询分解为多个小查询是很有必要的。
切分查询
例如删除过期数据,分开删除避免长时间锁表。
分解关联查询
使用多次分开查询代替join查询
- 让缓存的效率更高
- 可以减少锁的竞争
- 在应用层做关联,可以更容易对数据库进行拆分,更容易做到高性能和可扩展。
- 单个查询使用效率也会提升,比随机关联更高效
- 减少冗余查询
- 这样做相当于在应用中实现了哈希关联,而不是使用MySQL的嵌套循环关联
查询执行的基础
MySQL执行查询的过程
- 客户端发送一条查询给服务器。
- 服务器先检查查询缓存,如果命中了缓存,则立刻返回存储在缓存中的结果。否则进入下 一阶段。
- 服务器端进行SQL解析、预处理,再由优化器生成对应的执行计划。
- MySQL根据优化器生成的执行计划,调用存储引擎的API来执行查询。
- 将结果返回给客户端。
MySQL客户端/服务器通信协议
“半双工”通信,同一时刻只有一方可以发送数据,一旦一端开始发送消息,另一端要接收完整个消息才能响应。
查询状态
对于一个MySQL连接,或者说一个线程,任何时刻都有一个状态,该状态表示了MySQL当 前正在做什么。使用SHOW FULL PROCESSLIST命令
- Sleep 线程正在等待客户端发送新的请求。
- Locked 在MySQL服务器层,该线程正在等待表锁。在存储引擎级别实现的锁,例如InnoDB的行锁,并不会体现在线程状态中。对于MyISAM来说这是一个比较典型的状态,但在其他没有行锁的引擎中也经常会出现。
- Analyzing and statistics 线程正在收集存储引擎的统计信息,并生成查询的执行计划。
- Copying to tmp table [on disk] 线程正在执行查询,并且将其结果集都复制到一个临时表中,这种状态一般要么是在做GROUP BY操作,要么是文件排序操作,或者是UNION操作。如果这个状态后面还有“on disk”标记,那表示MySQL正在将一个内存临时表放到磁盘上。
- The thread is 线程正在对结果集进行排序。
- Sending data 这表示多种情况:线程可能在多个状态之间传送数据,或者在生成结果集,或者在向客户端返回数据。
查询缓存
在解析一个查询语句之前,如果查询缓存是打开的,那么MySQL会优先检查这个查询是否命中查询缓存中的数据。这个检查是通过一个对大小写敏感的哈希查找实现的。查询和缓存中的查询即使只有一个字节不同,那也不会匹配缓存结果,这种情况下查询就会进入下一阶段的处理。如果当前的查询恰好命中了查询缓存,那么在返回查询结果之前MySQL会检查一次用户权限。这仍然是无须解析查询SQL语句的,因为在查询缓存中已经存放了当前查询需要访问的表信息。如果权限没有问题,MySQL会跳过所有其他阶段,直接从缓存中拿到结果并返回给客户端。这种情况下,查询不会被解析,不用生成执行计划,不会被执行。
查询优化处理
查询的生命周期的下一步是将一个SQL转换成一个执行计划,MySQL再依照这个执行计划和存储引擎进行交互。这包括多个子阶段:解析SQL、预处理、优化SQL执行计划。这个过程中任何错误(例如语法错误)都可能终止查询。
语法解析器和预处理
MySQL通过关键字将SQL语句进行解析,并生成一棵对应的“解析树”。预处理器则根据一些MySQL规则进一步检查解析树是否合法,例如,这里将检查数据表和数据列是否存在,还会解析名字和别名,看看它们是否有歧义。
查询优化器
一条查询可以有很多种执行方式,最后都返回相同的结果。MySQL使用基于成本的优化器,它将尝试预测一个查询使用某种执行计划时的成本,并选择其中成本最小的一个。
有很多种原因会导致MySQL优化器选择错误的执行计划:
- 统计信息不准确。
- 执行计划中的成本估算不等同于实际执行的成本。例如有时候某个执行计划虽然需要读取更多的页面,但是它的成本却更小。因为如果这些页面都是顺序读或者这些页面都已经在内存中的话,那么它的访问成本将很小。MySQL层面并不知道哪些页面在内存中、哪些在磁盘上,所以查询实际执行过程中到底需要多少次物理I/O是无法得知的。
- MySQL的最优可能和你想的最优不一样。你可能希望执行时间尽可能的短,但是 MySQL只是基于其成本模型选择最优的执行计划,而有些时候这并不是最快的执行方式。
- MySQL从不考虑其他并发执行的查询。
- MySQL也并不是任何时候都是基于成本的优化。
- MySQL不会考虑不受其控制的操作的成本,例如执行存储过程或者用户自定义函数的成本。
- 优化器有时候无法去估算所有可能的执行计划,所以它可能错过实际上最优的执行计划。
优化策略可以简单地分为两种,一种是静态优化,一种是动态优化。
静态优化可以直接对解析树进行分析,并完成优化。例如,优化器可以通过一些简单的代数变换将WHERE 条件转换成另一种等价形式。静态优化不依赖于特别的数值,如WHERE条件中带入的一些常数等。静态优化在第一次完成后就一直有效,即使使用不同的参数重复执行查询也不会发生变化。可以认为这是一种“编译时优化”。
动态优化则和查询的上下文有关,也可能和很多其他因素有关,例如WHERE条件 中的取值、索引中条目对应的数据行数等。这需要在每次查询的时候都重新评估,可以认为这 是“运行时优化”。
下面是一些MySQL能够处理的优化类型:
- 重新定义关联表的顺序
- 将外连接转化成内连接
- 使用等价变换规则,。它可以合并和减少一些比较,还可以移除一些恒成立和一些恒不成立的判断。例如,(5=5 AND a>5)将被改写为a>5
- 优化COUNT()、MIN()和MAX(),例如,要找到某一列的最小值,只需要查询对应B-Tree索引最左端的记录,在B-Tree索引中,优化器会将这个表达式作为一个常数对待。如果MySQL使用了这种类型的优化,那么在EXPLAIN中就可以看到“Select tables optimized away”。类似的,没有任何WHERE条件的COUNT(*)查询通常也可以使用存储引擎提供的一些优化(例如,MyISAM维护了一个变量来存放数据表的行数)。
- 预估并转化为常数表达式,一个用户自定义变量在查询中没有发生变化时就可以转换为一个常数。数学表达式则是另一种典型的例子。
- 覆盖索引扫描
- 子查询优化
- 提前终止查询,在发现已经满足查询需求的时候,MySQL总是能够立刻终止查询。
- 等值传播,如果两个列的值通过等式关联,那么MySQL能够把其中一个列的WHERE条件传递到另一列上。
- 表IN()的比较,在MySQL中这点是不成立的,MySQL将IN()列表中的数据先进行排序,然后通过二分查找的方式来确定列表中的值是否满足条件
数据和索引的统计信息
统计信息由存储引擎实现,不同的存储引擎可能会存储不同的统计信息
MySQL如何执行关联查询
MySQL认为任何一个查询都是一次“关联”,MySQL对任何关联都执行嵌套循环关联操作,读取结果临时表也是一次关联。
执行计划
MySQL生成查询的一棵指令树,然后通过存储引擎执行完成这棵指令树并返回结果。
关联查询优化器
关联查询优化器则通过评估不同顺序时的成本来重排一个代价最小的关联顺序。由于嵌套递归关联操作,一般选记录数少的表在前。可以使用STRAIGHT_JOIN关键字防止关联顺序被优化。
排序优化
排序优化如果数据量小则在内存中进行,如果数据量大则需要使用磁盘,不过MySQL将这个过程统一称为文件排序(filesort),即使完全是内存排序不需要任何磁盘文件时也是如此。
MySQL有两种排序算法:
-
两次传输排序,读取行指针和需要排序的字段,对其进行排序,然后再根据排序结果读取所需要的 数据行。
-
单次传输排序,先读取查询所需要的所有列,然后再根据给定列进行排序,最后直接返回排序结果。
当查询需要所有列的总长度不超过参数max_length_for_sort_data时,MySQL使用“单次传输排序”,可以通过调整这个参数来影响MySQL排序算法的选择。如果ORDER BY子句中的所有列都来自关联的第一个表,那么MySQL在关联处理第一个表的时候就进行文件排序。如果是这样,那么在MySQL的EXPLAIN结果中可以看到Extra字段会有“Using filesort”。除此之外的所有情况,MySQL都会先将关联的结果存放到一个临时表中,然后在所有的关联都结束后,再进行文件排序。这种情况下,在MySQL的EXPLAIN结果的Extra字段可以看到“Using temporary;Using filesort”。如果查询中有LIMIT的话,LIMIT也会在排序之后应用,所以即使需要返回较少的数据,临时表和需要排序的数据量仍然会非常大。MySQL 5.6在这里做了很多重要的改进。当只需要返回部分排序结果的时候,例如使用了LIMIT子句,MySQL不再对所有的结果进行排序,而是根据实际情况,选择抛弃不满足条件的结果,然后再进行排序。
查询执行引擎
在解析和优化阶段,MySQL将生成查询对应的执行计划,MySQL的查询执行引擎则根据这个执行计划来完成整个查询。这里执行计划是一个数据结构,而不是和很多其他的关系型数据库那样会生成对应的字节码。
返回结果给客户端
查询执行的最后一个阶段是将结果返回给客户端。即使查询不需要返回结果集给客户端,MySQL仍然会返回这个查询的一些信息,如该查询影响到的行数。如果查询可以被缓存,那么MySQL在这个阶段也会将结果存放到查询缓存中。MySQL将结果集返回客户端是一个增量、逐步返回的过程。例如,我们回头看看前面的关联操作,一旦服务器处理完最后一个关联表,开始生成第一条结果时,MySQL就可以开始向客户端逐步返回结果集了。
MySQL查询优化器的局限性
关联子查询
UNION的限制
例如,取出两个表的数据把UNION的结果limit。MySQL会把两个表所有满足条件的数据取出来。所以最好是在两个表取数据时就加上limit,再结果集再limit一次。
索引合并优化
当WHERE子句中包含多个复杂条件的时候,MySQL能够访问单个表的多个索引以合并和交叉过滤的方式来定位需要查找的行。
等值传递
并行执行
MySQL无法利用多核特性来并行执行查询。
哈希关联
MySQL不支持哈希关联
松散索引扫描
MySQL不支持松散索引扫描,在MySQL 5.6之后的版本,关于松散索引扫描的一些限制将会通过“索引条件下推(indexcondition pushdow n)”的方式解决。
最大值和最小值优化
同一个表上查询和更新
不支持在同一个语句中进行查询和更新,但是可以通过join方式生成临时表绕过限制。
查询优化器的提示(hint)
可以使用优化器提供的几个提示(hint)来控制最终的执行计划。通过在查询中加入相应的提示,就可以控制该查询的执行计划。
- FOR UPDATE和LOCK IN SHARE MODE,这两个提示主要控制SELECT语句的锁机制,但只对实 现了行级锁的存储引擎有效。
优化特定类型的查询
优化COUNT()查询
count()只能统计非空的值。 如果count()>1可以优化成count(*)-count<1。 对于要求不精确的可是使用会汇总表。
优化关联查询
- 确保ON或者USING子句列有索引
- 确保GROUP BY和ORDER BY 表达式只涉及到一个表中的列
优化GROUP BY和DISTINCT
当无法使用索引的时候,GROUP BY使用两种策略来完成:使用临时表或者文件排序来做分组。
优化LIMIT分页
尽可能使用索引覆盖扫描,不然偏移量越大效率越低。 或者使用延迟关联
优化UNION查询
MySQL总是通过创建并填充临时表的方式来执行UNION查询。除非确实需要服务器消除重复的行,否则就一定要使用UNION ALL,这一点很重要。如果没有ALL关键字,MySQL会给临时表加上DISTINCT选项,这会导致对整个临时表的数据做唯一性检查。
MySQL高级特性
分布式(XA)事务
复制
复制原理
基于语句的复制
基于语句的复制模式下,主库会记录那些造成数据更改的查询,当备库读取并 重放这些事件时,实际上只是把主库上执行过的SQL再执行一遍。可能会节省带宽。收到其他因素(例如触发器)影响可能会失效。
基于行的复制
这种方式会将实际数据记录在二进制日志中,可以正确地复制每一行。
复制过滤器
有两种复制过滤方式:在主库上过滤记录到二进制日志中的事件,以及在备库上过滤记录到中继日志的事件。
复制拓扑
一主多备
主动-主动模式下的主-主复制
主动-被动模式下的主-主复制
拥有备库的主-主结构
环形复制
主库、分发主库以及备库
树或金字塔形
EXPLAIN
在查询中的SELECT关键字之前增加EXPLAIN这个词。MySQL会在查询上设置一个标记。当执行查询时,这个标记会使其返回关于在执行计划中每一步的信息,而不是执行它。如果查询在FROM子句中包括子查询,那么MySQL实际上会执行子查询。
EXPLAIN EXTENDED看起来和正常的EXPLAIN的行为一样,但它会告诉服务器“逆向编 译”执行计划为一个SELECT语句。可以通过紧接其后运行SHOW WARNINGS看到这个生成的语句。
EXPLAIN 只支持SELECT,不支持INSERT、UPDATE、DELETE。所以需要重写其他语句为SELECT。
EXPLAIN结果表示
id列
标识SELECT所属的行。内层的SELECT语句一般会顺序编号,对应于其在原始语句中的位置。
select_type列
SIMPLE值意味着查询不包括子查询和UNION。如果查询有任何复杂的子部分,则最外层部分标记为PRIMARY,其他部分标记如下
- SUBQUERY 包含在SELECT列表中的子查询中的SELECT(换句话说,不在FROM子句中)标记 为SUBQUERY。
- DERIVED 值用来表示包含在FROM子句的子查询中的SELECT,MySQL会递归执行 并将结果放到一个临时表中。服务器内部称其“派生表”,因为该临时表是从子查询中派 生来的。
- UNION 在UNION中的第二个和随后的SELECT被标记为UNION。第一个SELECT被标记就好 像它以部分外查询来执行。这就是之前的例子中在UNION中的第一个SELECT显示 为PRIMARY的原因。如果UNION被FROM子句中的子查询包含,那么它的第一个SELECT 会被标记为DERIVED。
- UNION RESULT 用来从UNION的匿名临时表检索结果的SELECT被标记为UNION RESULT。
table列
表,或是该表的别名,
当在FROM子句中有子查询时,table列是derivedN的形式,其中N是子查询的id。
当有UNION时,UNION RESULT的table列包含一个参与UNION的id列表。
type列
ALL
人们所称的全表扫描,通常意味着MySQL必须扫描整张表,从头到尾,去找到需要的 行。(这里也有个例外,例如在查询里使用了LIMIT,或者在Extra列中显示“Using distinct/not exists”。)
index
这个跟全表扫描一样,只是MySQL扫描表时按索引次序进行而不是行。它的主要优点是避免了排序;最大的缺点是要承担按索引次序读取整个表的开销。这通常意味着若是按随机次序访问行,开销将会非常大。如果在Extra列中看到“Using index”,说明MySQL正在使用覆盖索引,它只扫描索引的数据,而不是按索引次序的每一行。它比按索引次序全表扫描的开销要少很多。
range
范围扫描就是一个有限制的索引扫描,它开始于索引里的某一点,返回匹配这个值域的行。这比全索引扫描好一些,因为它用不着遍历全部索引。显而易见的范围扫描是带BETWEEN或在WHERE子句里带有>的查询。 当MySQL使用索引去查找一系列值时,例如IN()和OR列表,也会显示为范围扫描。然而,这两者其实是相当不同的访问类型,在性能上有重要的差异。此类扫描的开销跟索引类型相当。
ref
这是一种索引访问(有时也叫做索引查找),它返回所有匹配某个单个值的行。然而,它可能会找到多个符合条件的行,因此,它是查找和扫描的混合体。此类索引访问只有当使用非唯一性索引或者唯一性索引的非唯一性前缀时才会发生。把它叫做ref是因为索引要跟某个参考值相比较。这个参考值或者是一个常数,或者是来自多表查询前一个表里的结果值。 ref_or_null是ref之上的一个变体,它意味着MySQL必须在初次查找的结果里进行第 二次查找以找出NULL条目。
eq_ref
使用这种索引查找,MySQL知道最多只返回一条符合条件的记录。这种访问方法可 以在MySQL使用主键或者唯一性索引查找时看到,它会将它们与某个参考值做比较。 MySQL对于这类访问类型的优化做得非常好,因为它知道无须估计匹配行的范围或在找 到匹配行后再继续查找。
const, system
当MySQL能对查询的某部分进行优化并将其转换成一个常量时,它就会使用这些访 问类型。举例来说,如果你通过将某一行的主键放入WHERE子句里的方式来选取此行的主键,MySQL 就能把这个查询转换为一个常量。然后就可以高效地将表从联接执行中移除。
NULL
这种访问方式意味着MySQL能在优化阶段分解查询语句,在执行阶段甚至用不着再访问表或者索引。例如,从一个索引列里选取最小值可以通过单独查找索引来完成,不需要在执行时访问表。
possibIe_keys列
这一列显示了查询可以使用哪些索引
key列
这一列显示了MySQL决定采用哪个索引来优化对该表的访问。
key_len列
该列显示了MySQL在索引里使用的字节数。通过其长度可以推断使用了索引的左前缀多少列。
ref列
这一列显示了之前的表在key列记录的索引中查找值所用的列或常量。
rows列
这一列是MySQL估计为了找到所需的行而要读取的行数。这个数字是内嵌循环关联计划里的循环数目。也就是说它不是MySQL认为它最终要从表里读取出来的行数。通过把所有rows列的值相乘,可以粗略地估算出整个查询会检查的行数。
fiItered列
在使用EXPLAIN EXTENDED时出现。它显示的是针对表里符合某个条件(WHERE子句或联接条件)的记录数的百分比所做的一个悲观估算。如果你把rows列和这个百分比相乘,就能看到MySQL估算它将和查询计划里前一个表关联的行数。
Extra列
Using index
此值表示MySQL将使用覆盖索引,以避免访问表。不要把覆盖索引和index访问类型弄混了。
Using where
这意味着MySQL服务器将在存储引擎检索行后再进行过滤。
Using temporary
这意味着MySQL在对查询结果排序时会使用一个临时表。
Using filesort
这意味着MySQL会对结果使用一个外部索引排序,而不是按索引次序从表里读取行。
“Range checked for each record (index map: N)
这个值意味着没有好用的索引,新的索引将在联接的每一行上重新估算。