《高性能MySql》笔记

一、MySQL架构与历史

A.并发控制

1.共享锁（shared lock，读锁）：共享的，相互不阻塞的
2.排他锁（exclusive lock，写锁）：排他的，一个写锁会阻塞其他的写锁和读锁
3.InnoDB采用两阶段锁定协议。.InnoDB会根据隔离级别在需要时自动加锁。

B.事务

1.事务ACID

• 原子性（atomicity）一个事务必须被视为一个不可分割的最小工作单元，整个事务中所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作
• 一致性（consistency）数据库总是从一个一致性的状态转换到另外一个一致性的状态
• 隔离性（isolation）一个事务所做的修改在最终提交以前，对其他事务是不可见的
• 持久性（durability）一旦事务提交，则其所做的修改就会永久保存到数据库中

2.四种隔离级别

• READ UNCOMMITTED（未提交读），事务中的修改，即使没有提交，对其他事务也都是可见的，事务可以读取未提交的数据，也被称为脏读（Dirty Read），这个级别会导致很多问题
• READ COMMITTED（提交读），大多数数据库系统的默认隔离级别，一个事务开始时，只能“看见”已经提交的事务所做的修改，一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的，也叫不可重复读（nonrepeatable read），有可能出现幻读（Phantom Read），指的是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生幻行（Phantom Row）
• REPEATABLE READ（可重复读），通过InnoDB和XtraDB存储引擎，是MySQL的默认事务隔离级别
• SERIALIZABLE（可串行化）最高级别，通过强制事务串行执行，避免了幻读问题，会在读取的每一行数据上都加锁，可能导致大量的超时和锁争用的问题
  3.死锁：指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象，innoDB处理方式为将持有最少行级排他锁的事务进行回滚。
  4.事务日志：存储引擎在修改表的数据时只需要修改其内存拷贝，再把该修改行为记录到持久在硬盘上的事务日志中，而不用每次都将修改的数据本身持久到磁盘。事务日志持久以后，内存中被修改的数据在后台可以慢慢地刷回到磁盘，称为预写式日志（Write-Ahead Logging）

C.多版本并发控制

1.多版本并发控制（MVCC）是行级锁的一个变种，但是它在很多情况下避免了加锁操作，因此开销更低。虽然实现机制有所不同，但大都实现了非阻塞的读操作，写操作也只锁定必要的行
2.MVCC的实现，是通过保存数据在某个时间点的快照来实现的，通过在每行记录后保存的两个隐藏的列来实现。一列保存了行的创建时间，一个保存行的过期时间。时间指的是系统版本号。有乐观和悲观两种，只在REPEATABLE READ和READ COMMITTED两个隔离级别下工作，另外两个一个总是读取最新行，一个对所有行加行锁。

D.MySQL的存储引擎

1.MySQL的.frm文件保存表的定义，SHOW TABLE STATUS显示表的相关信息
2.除非有非常特别的原因需要使用其他的存储引擎，否则应该优先考虑InnoDB引擎
3.不要轻易相信MyISAM比InnoDB快之类的经验之谈，这个结论并不是绝对的

五、创建高性能的索引

A.索引的基础

1.索引是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关键。尤其是表中数据量越来越大时，索引对性能的影响越发重要。
2.首先，B树不要和二叉树混淆，在计算机科学中，B树是一种自平衡树数据结构，它维护有序数据并允许以对数时间进行搜索，顺序访问，插入和删除。B树是二叉搜索树的一般化，因为节点可以有两个以上的子节点。[1]与其他自平衡二进制搜索树不同，B树非常适合读取和写入相对较大的数据块（如光盘）的存储系统。它通常用于数据库和文件系统。
3.B+树每个元素不保存数据，只用来索引。所有的叶子结点中包含了全部关键字的信息，及指向含有这些关键字记录的指针，且叶子结点本身依关键字的大小自小而大的顺序链接。 (而B 树的叶子节点并没有包括全部需要查找的信息)；
所有的非终端结点可以看成是索引部分，结点中仅含有其子树根结点中最大（或最小）关键字。 (而B 树的非终节点也包含需要查找的有效信息)；

4.B-树按中序遍历查找，B+树按链表查找
5.B-Tree索引的限制：

• 如果不是按照索引的最左列开始查找，则无法使用索引
• 不能跳过索引中的列
• 如果查询中有某个列的范围查询，则其右边所有列都无法使 用索引优化查找

6.哈希索引（hash index）基于哈希表实现，只有精确匹配索引所有列的查询才有效，只有Memory引擎显式支持哈希索引
7.哈希索引的限制：

• 哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行
• 哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序
• 哈希索引也不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的
• 只支持等值比较查询，不支持任何范围查询
• 访问哈希索引的数据非常快，除非有很多哈希冲突
• 如果哈希冲突很多的话，一些索引维护操作的代价也会很高

B.索引的优点

1.三个优点：

• 索引大大减少了服务器需要扫描的数据量
• 索引可以帮助服务器避免排序和临时表
• 索引可以将随机I/O变为顺序I/O
  2.索引三星系统：
• 索引将相关的记录放到一起则获得一星
• 如果索引中的数据顺序和查找中的排序一致则获得二星
• 如果索引中的列包含了查询中需要的全部列则获得三星

C.高性能的索引策略

1.独立的列：如果查询中的列不是独立的，则MySQL不会使用索引。“独立的列”是指索引列不能是表达式的一部分，也不能是函数的参数
2.前缀索引和索引选择性

• 通常可以索引开始的部分字符，可以大大节约索引空间，但也会降低索引的选择性
• 索引的选择性是指，不重复的索引值（也称为基数，cardinality）和数据表的记录总数（#T）的比值，范围从1/#T到1之间，选择性越高则查询效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行
• MySQL无法使用前缀索引做ORDERY BY和GROUP BY，也无法做覆盖扫描

3.选择合适的索引列顺序

• 正确的索引列顺序依赖于使用该索引的查询，并且同时需要考虑如何更好地满足排序和分组的需要
• 在一个多列B-Tree索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列
• 将选择性最高的列放到索引最前列

4.聚簇索引：并不是一种单独的索引类型，而是一种数据存储方式

• 一个表只有一个，因为行只存在一个地方
• 最好避免随机的（不连续且值的分布范围非常大）聚簇索引，特别是对于I/O密集型的应用（UUID）
• 非聚簇索引查找时需要两次索引查找。因为其叶子节点存储的是主键，还需去聚簇索引中查找。

5.覆盖索引：如果一个索引包含（或者说覆盖）所有需要查询的字段的值，就称为覆盖索引

• 覆盖索引必须要存储索引列的值
• 当发起一个被索引覆盖的查询(也叫作索引覆盖查询)时，在EXPLAIN的Extra列可以看到“Using index”的信息

6.如果EXPLAIN出来的type列的值为“index”，则说明MySQL使用了索引扫描来做排序
7.索引可以让查询锁定更少的行

六、查询性能优化

A.为什么查询速度会慢

1.如果要优化查询，实际上要优化其子任务，要么消除其中一些子任务，要么减少子任务的执行次数，要么让子任务运行得更快
2.查询的生命周期大致可以按照顺序来看：从客户端，到服务器，然后在服务器上进行解析，生成执行计划，执行，并返回结果给客户端

B.慢查询基础：优化数据访问

1.两个分析步骤：

• 确认应用程序是否在检索大量超过需要的数据
• 确认MySQL服务器层是否在分析大量超过需要的数据行
  2.是否向数据库请求了不需要的数据
• 查询不需要的记录
• 多表关联并返回全部列
• 总是取出全部列
• 重复查询相同的数据
  3.MySQL是否在扫描额外的记录
• 查询开销三个指标：响应时间、扫描的行数、返回的行数
• 响应时间：服务时间和排队时间之和，“快速上限估计”法
• 扫描的行数：较短的行的访问速度更快，内存中的行也比磁盘中的行的访问 速度要快得多
• 访问类型：EXPLAIN中的type列反应了访问类型；通过增加合适的索引；
• 三种方式应用WHERE条件：在索引中使用WHERE条件来过滤不匹配的记录；使用索引覆盖扫描（Extra中出现Using index）来返回记录，直接从索引中过滤不需要的记录并返回命中结果；从数据表中返回数据，然后过滤不满足条件的记录（Extra中出现Using Where）
• 需要扫描大量数据但只返回少数的行的优化技巧：使用索引覆盖扫描，改变库表结构，重写复杂的查询

C.重构查询的方式

1.复杂查询变为几个简单查询。MySQL从设计上让连接和断开连接都很轻量级，在返回一个小的查询结果方面很高效
2.切分查询，将大查询切分成小查询，每个查询功能完全一样，只完成一小部分，每次只返回一小部分查询结果，可以避免锁住很多数据、占满事务日志、耗尽系统资源、阻塞很多小的但重要的查询
3.分解关联查询优势：

• 让缓存的效率更高
• 将查询分解后，执行单个查询可以减少锁的竞争
• 在应用层做关联，可以更容易对数据库进行拆分，更容易做到高性能和可扩展
• 查询本身效率也可能会有所提升
• 可以减少冗余记录的查询
• 相当于在应用中实现了哈希关联，而不是使用MySQL的嵌套循环关联
  4.分解关联查询的场景：
• 当应用能够方便地缓存单个查询的结果的时候
• 当可以将数据分布到不同的MySQL服务器上的时候
• 当能够使用IN()的方式代替关联查询的时候
• 当查询中使用同一个数据表的时候

D.查询执行的基础

1.查询执行路径

• 客户端发送一条查询给服务器
• 服务器先检查查询缓存，如果命中则立刻返回，否则进入下一阶段
• 服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划
• MySQL根据优化器生成的执行计划，调用存储引擎的API来执行查询
• 将结果返回给客户端

2.MySQL客户端和服务器之间的通信协议是“半双工”的，无法将一个消息切成小块独立来发送，没法进行流量控制，一旦一端开始发生消息，另一端要接收完整个消息才能响应它

3.MySQL通常需要等所有的数据都已经发送给客户端才能释放这条查询所占用的资源，所以接收全部结果并缓存通常可以减少服务器的压力
4.查询状态，SHOW FULL PROCESSLIST命令查看：

• Sleep，线程正在等待客户端发送新的请求
• Query，线程正在执行查询或者正在将结果发送给客户端
• Locked，在MySQL服务器层，该线程正在等待表锁
• Analyzing and statistics，线程正在收集存储引擎的统计信息，并生成查询的执行计划
• Copying to tmp table [on disk]，线程正在执行查询，并且将其结果集都复制到一个临时表中，要么是在做GROUP BY操作，要么是文件排序操作，或者是UNION操作
• Sorting result，线程正在对结果集进行排序
• Sending data，线程可能在多个状态之间传送数据，或者在生成结果集，或者在向客户端返回数据

5.语法解析器和预处理，通过关键字将SQL语句进行解析，并生成一棵对应的“解析树”，解析器将使用MySQL语法规则验证和解析查询，预处理器则根据一些MySQL规则进一步检查解析树是否合法
6.查询优化器，找到最好的执行计划，使用基本成本的优化器，将尝试预测一个查询使用某种执行计划时的成本，并选择其中成本最小的一个，使用SHOW STATUS LIKE ‘Last_query_cost’;查看需要多少个数据页的随机查找
7.导致MySQL查询优化器选择错误的原因：

• 统计信息不准确，Innodb不能维护一个数据表的行数的精确统计信息
• 执行计划中的成本估算不等同于实际执行的成本
• MySQL的最优可能和你想的最优不一样
• MySQL从不考虑其他并发执行的查询
• MySQL也并不是任何时候都是基于成本的优化
• MySQL不会考虑不受其控制的操作的成本
• 优化器有时候无法去估算所有可能的执行计划
  8.MySQL能处理的优化类型：
• 重新定义关联表的顺序
• 将外链接转化成内链接
• 使用等价变换规则
• 优化COUNT()、MIN()和MAX()，在EXPLAIN中可以看到“Select tables optimized away”
• 预估并转化为常数表达式，当检测到一个表达式可以转化为常数的时候，就会一直把该表达式作为常数进行优化处理
• 覆盖索引扫描，当索引中的列包含所有查询中需要使用的列的时候，就可以使用索引返回需要的数据，而无须查询对应的数据行
• 子查询优化
• 提前终止查询，在发现已经满足查询需求的时候，MySQL总是能够立刻终止查询
• 等值传播，如果两个列的值通过等式关联，那么MySQL能够把其中一个列的WHERE条件传递到另一列上
• 列表IN()的比较，MySQL将IN()列表中的数据先进行排序，然后通过二分查找的方式来确定列表中的值是否满足条件

9.在服务器层有查询优化器，却没有保存数据和索引的统计信息，统计信息由存储引擎实现，不同的存储引擎可能会存储不同的统计信息
10.在MySQL中，每一个查询，每一个片段（包括子查询，甚至基于单表的SELECT）都可能是关联
11.对于UNION查询，MySQL先将一系列的单个查询结果放到一个临时表中，然后再重新读出临时表数据来完成UNION查询
12.MySQL对任何关联都执行“嵌套循环关联”操作，即MySQL先在一个表中循环取出单条数据，然后再嵌套到下一个表中寻找匹配的行，依次下去，直到找到所有表中匹配的行为止
13.全外连接就无法通过嵌套循环和回溯的方式完成，当发现关联表中没有找到任何匹配行的时候，则可能是因为关联恰好从一个没有任何匹配的表开始，MySQL不支持全外连接
14.关联查询优化器，会尝试在所有的关联顺序中选择一个成本最小的来生成执行计划树，如果可能，优化器会遍历每一个表然后逐个做嵌套循环计算每一棵可能的执行树的成本，最后返回一个最优的执行计划
15.如果有超过n个表的关联，那么需要检查n的阶乘关联顺序，称为“搜索空间”，搜索空间的增长速度非常快
16.无论如何排序都是一个成本很高的操作，所以从性能角度考虑，应尽可能避免排序或者尽可能避免对大量数据进行排序
17.当不能使用索引生成排序结果的时候，MySQL需要自己进行排序，如果数据量小则在内存中进行，如果数据量大则需要使用磁盘，MySQL将这个过程称为文件排序（filesort），即使完全是内存排序不需要任何磁盘文件时也是如此

E.MySQL查询优化器的局限性

1.关联子查询：MySQL的子查询实现得非常糟糕，最糟糕的一类查询是WHERE条件中包含IN()的子查询语句，使用GROUP_CONCAT()在IN()中构造一个由逗号分隔的列表，或者使用EXISTS()来改写
2.UNION的限制：有时，MySQL无法将限制条件从外层“下推”到内层，这使得原本能够限制部分返回结果的条件无法应用到内层查询的优化上
3.MySQL无法利用多核特性来并行执行查询
4.MySQL不支持哈希关联，MariaDB已经实现了哈希关联
5.MySQL不支持松散索引扫描，5.0后版本在分组查询中需要找到分组的最大值和最小值时可以使用松散索引扫描
6.对于MIN()和MAX()查询，MySQL的优化做得并不好（可以强制使用主键索引，limit 1）

F.查询优化器的提示（hint）

1.HIGH_PRIORITY和LOW_PRIORITY，当多个语句同时访问某一个表的时候，哪些语句的优先级相对高些、哪些语句的优先级相对低些
2.DELAYED，对INSERT和REPLACE有效，会将使用该提示的语句立即返回给客户端，并将插入的行数据放入到缓冲区，然后在表空闲时批量将数据写入，并不是所有的存储引擎都支持，并且该提示会导致函数LAST_INSERT_ID()无法正常工作
3.STRAIGHT_JOIN，可以放置在SELECT语句的SELECT关键字之后，也可以放置在任何两个关联表的名字之间。第一个用法是让查询中所有的表按照在语句中出现的顺序进行关联，第二个用法则是固定其前后两个表的关联顺序
4.SQL_SMALL_RESULT和SQL_BIG_RESULT，只对SELECT语句有效，它们告诉优化器对GROUP BY或者DISTINCT查询如何使用临时表及排序
5.SQL_BUFFER_RESULT，告诉优化器将查询结果放入到一个临时表，然后尽可能快地释放表锁
6.SQL_CACHE和SQL_NO_CACHE，告诉MySQL这个结果集是否应该缓存在查询缓存中
7.SQL_CALC_FOUND_ROWS，会计算除去LIMIT子句后这个查询要返回的结果集的总数，而实际上只返回LIMIT要求的结果集，可以通过函数FOUND_ROW()获得这个值
8.FOR UPDATE和LOCK IN SHARE MODE，主要控制SELECT语句的锁机制，但只对实现了行级锁的存储引擎有效，仅InnoDB支持
9.USE INDEX、IGNORE INDEX和FORCE INDEX，告诉优化器使用或者不使用哪些索引来查询记录
10.MySQL5.0后新增的用来控制优化器行为的参数：

• optimizer_search_depth，控制优化器在穷举执行时的限度
• optimizer_prune_level，让优化器会根据需要扫描的行数来决定是否跳过某些执行计划
• optimizer_switch，包含了一些开启/关闭优化器特性的标志位

G.优化特定类型的查询

1.优化COUNT()查询

• COUNT()是一个特殊的函数，有两种非常不同的作用：可以统计某个列值的数量，也可以统计行数，在统计列值时要求列值是非空的（不统计NULL）
• COUNT(*)并不是会像我们猜想的那样扩展成所有的列，实际上，它会忽略所有的列而直接统计所有的行数，当MySQL确认括号内的表达值不可能为空时，实际上就是在统计行数
• MyISAM的COUNT()函数只有没有任何WHERE条件下的COUNT(*)才非常快
• 使用近似值，如EXPLAIN出来的优化器估算行数
• 使用索引覆盖
• 使用汇总表
• 使用外部缓存系统

2.优化关联查询

• 确保ON或者USING子句中的列上有索引
• 确保任何的GROUP BY和ORDER BY中的表达式只涉及到一个表中的列
• 当升级MySQL的时候需要注意：关联语法、运算符优先级等其他可能会发生变化的地方

3.优化子查询：尽可能使用关联查询代替，如果使用MySQL5.6以上或MariaDB则可以忽略这个建议
4.优化GROUP BY和DISTINCT

• 使用索引优化
• 当无法使用索引时，GROUP BY使用两种策略来完成：使用临时表或者文件排序来做分组
• 尽可能的将WITH ROLLUP（超级聚合）功能移动应用程序中处理

5.优化LIMIT分页

• 最简单的办法是尽可能地使用索引覆盖扫描，而不是查询所有的列，然后根据需要做一次关联操作再返回所需的列，select id,name,…… from table innert join (select id from table order by xxx limit 5000,5) as table1 USING(id);
• offset会导致MySQL扫描大量不需要的行然后再抛弃掉，如果可以记录上次取数据的位置，下次就可以直接从该记录的位置开始扫描，可以避免使用offset
• 使用预先计算的汇总表，或者关联到一个冗余表

6.优化UNION查询

• 通过创建并填充临时表的方式来执行UNION查询，因此很多优化策略在UNION查询中都没法很好地使用，经常需要手工地将WHERE、LIMIT、ORDER BY等子句下推到UNION的各个子查询中
• 除非确实需要服务器消除重复的行，否则就一定要使用UNION ALL