一、MySQL架构与历史

A.并发控制

1.共享锁（shared lock，读锁）：共享的，相互不阻塞的

2.排他锁（exclusive lock，写锁）：排他的，一个写锁会阻塞其他的写锁和读锁

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.死锁：指两个或多个事务在同一资源上相互占用，并请求锁定对方占用的资源，从而导致恶性循环的现象

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快之类的经验之谈，这个结论并不是绝对的

二、MySQL基准测试

A.为什么需要基准测试

1.基准测试可以观察系统在不同压力下的行为，评估系统的容量，掌握哪些是重要的变化，或者观察系统如何处理不同的数据

B.基准测试的策略

1.两种主要的策略：

  • 针对整个系统的整体测试（集成式full-stack）
  • 单独测试MySQL（单组件式single-component）

2.测试何种指标：

  • 吞吐量，指单位时间内的事务处理数，常用的测试单位是每秒事务数（TPS），或每分钟事务数（TPM）
  • 响应时间或者延迟，用于测试任务所需的整体时间，根据具体的应用，测试的时间单位可能是微秒、毫秒、秒或者分钟。通常使用百分比响应时间（percentile response time）来替代最大响应时间
  • 并发性，需要关注的是正在工作中的并发操作，或者是同时工作中的线程数或者连接数，在测试期间记录MySQL数据库的Threads_running状态值
  • 可扩展性，给系统增加一倍的工作，在理想情况下就能获得两倍的效果（即吞吐量增加一倍），对于容量规范非常有用，可以提供其他测试无法提供的信息，来帮助发现应用的瓶颈

C.基准测试方法

1.需要避免的一些常见错误：

  • 使用真实数据的子集而不是全集
  • 使用错误的数据分布
  • 使用不真实的分布参数
  • 在多用户场景中，只做单用户测试
  • 在单服务器上测试分布式应用
  • 与真实用户行为不匹配
  • 反复执行同一个查询
  • 没有检查错误
  • 忽略了系统预热（warm up）的过程
  • 使用默认的服务器配置
  • 测试时间太短

2.应该建立将参数和结果文档化的规范，每一轮测试都必须进行详细记录

3.基准测试应该运行足够长的时间，需要在稳定状态下测试并观察

4.在执行基准测试时，需要尽可能多地收集被测试系统的信息

5.自动化基准测试可以防止测试人员偶尔遗漏某些步骤，或者误操作，另外也有助于归档整个测试过程，可以选择shell、php、perl等，要尽可能使所有测试过程都自动化，包括装载数据、系统预热、执行测试、记录结果等

D.基准测试工具

1.集成式测试工具：

  • ab，测试HTTP服务器每秒最多可以处理多少请求
  • http_load，和ab类似，但更加灵活
  • jMeter，可以加载其他应用并测试其性能

2.单组件式测试工具

  • mysqlslap，可以模拟服务器的负载，并输出计时信息
  • MySQL Benchmark Suite(sql-bench)，单线程的，主要用于测试服务器执行查询的速度
  • Super Smack，提供压力测试和负载生成，是一个复杂而强大的工具，可以模拟多用户访问，可以加载测试数据到数据库，并支持使用随机数据填充测试表
  • Database Test Suite，类似某些工业标准测试的测试工具集
  • Percona’s TPCC-MySQWL Tool
  • sysbench，多线程系统压测工具，可以根据影响数据库服务器性能的各种因素来评估系统的性能

三、服务器性能剖析

A.性能优化简介

1.性能，为完成某件任务所需要的时间度量，性能即响应时间，这是非常重要的原则

2.如果目标是降低响应时间，就需要理解为什么服务器执行查询需要这么多时间，然后去减少或者消除那些对获得查询结果来说不必要的工作。无法测量就无法有效地优化

3.性能剖析（profiling）是测量和分析时间花费在哪里的主要方法，一般有两个步骤：测量任务所花费的时间，对结果进行统计和排序

B.对应用程序进行性能剖析

1.性能瓶颈可能的影响因素：

  • 外部资源
  • 应用需要处理大量的数据
  • 在循环中执行昂贵的操作
  • 使用了低效的算法
    2.PHP性能剖析工具：New Relic、xhprof、Ifp

C.剖析MySQL查询

1.剖析服务器负载

  • 慢查询日志：5.1后long_query_time为0可以捕获所有的查询，查询的响应时间单位可以做到微秒级
  • 生成剖析报告：pt-query-digest

2.剖析单条查询：

  • SHOW PROFILES;
  • SHOW [GLOBAL] STATUS;，返回一些计数器

D.诊断间歇性问题

1.尽量不要用试错的方式来解决问题，如果一时无法定位，可能是测量的方式不正确，或者测量的点选择有误，或者使用的工具不合适

2.确定单条查询问题还是服务器问题

  • 使用SHOW GLOBAL STATUS
  • 使用SHOW PROCESSLIST
  • 使用查询日志
  • 理解发现的问题：使得gnuplot或R，或其他绘图工具将结果绘制成图形

3.捕获诊断数据

  • 诊断触发器：在问题出现时能够捕获数据的基础，有两个常见问题可能导致无法达到预期的结果：误报（false positive）或者漏检（false negative）,pt-stalk工具
  • 收集数据：尽可能收集所有能收集的数据，但只在需要的时间段内收集，oprofile、strace、tcpdump、GDB堆栈跟踪、pt-collect、pt-stalk
  • 解释结果数据：pt-mysql-summary、pt-summary输出结果打包，pt-sift得到样本汇总信息，pt-pmp

E.其他剖析工具

1.使用USER_STATISTICS表
2.使用strace，可以调查系统调用的情况

四、Schema与数据类型优化

A.选择优化的数据类型

1.数据类型的选择原则：

  • 更小的通常更好
  • 简单就好
  • 尽量避免NULL

2.应该尽量只在对小数进行精确计算时才使用DECIMAL，使用int类型通过程序控制单位效果更好

3.使用VARCHAR合适的情况：字符串列的最大长度比平均长度大很多；列的更新很少，所以碎片不是问题；使用了像UTF-8这样复杂的字符集，每个字符都使用不同的字节数进行存储

4.CHAR适合存储很短的字符串，或者所有值都接近同一个长度；不容易产生碎片，在存储空间上更有效率

5.通常应该尽量使用TIMESTAMP，它比DATETIME空间效率更高

B.MySQL schema设计中的陷阱

1.不好的设计：

  • 太多的列
  • 太多的关联
  • 全能的枚举
  • 变相的枚举
  • 非此发明（Not Invent Here）的NULL

C.范式和反范式

1.范式的优点：

  • 范式化的更新操作通常比反范式化要快
  • 当数据较好地范式化时，就只有很少或者没有重复数据，所以只需要修改更少的数据
  • 范式化的表通常更小，可以更好地放在内存里，所以执行操作会更快
  • 很少有多余的数据意味着检索列表数据时更少需要DISTINCT或者GROUP BY语句

2.范式化设计的缺点是通常需要关联

3.反范式的优点：避免关联，避免了随机I/O，能使用更有效的索引策略

D.缓存表和汇总表

1.有时提升性能最好的方法是同一张表中保存衍生的冗余数据，有时也需要创建一张完全独立的汇总表或缓存表

2.物化视图，MySQL并不原生支持，Flexviews

3.如果应用在表中保存计数器，则在更新计数器时可能踫到并发问题，创建一张独立的表存储计数器，可以帮助避免缓存失效

  • 解决独立表并发问题可以建多行，根据id随机更新，然后统计时sum()
  • 按天或小时可以单独建行，旧时间可定时任务合并到统一的一行

E.加快ALTER TABLE操作的速度

1.两种方式：

  • 一是在一台不提供服务的机器上执行ALTER TABLE操作，然后和提供服务的主库进行切换
  • 二是通过“影子拷贝”，创建一张新表，然后通过重命名和删表操作交换两张表及里面的数据

2.快速创建MyISAM索引，先禁用索引，导入数据，然后重新启用索引

五、创建高性能的索引

A.索引基础

1.索引可以包含一个或多个列的值，如果索引包含多个列，那么列的顺序也十分重要，因为MySQL只能高效地使用索引的最左前缀列

2.ORM工具能够产生符合逻辑的、合法的查询，除非只是生成非常基本的查询，否则它很难生成适合索引的查询

3.在MySQL中，索引是在存储引擎层而不是服务器层实现的，所以，并没有统一的索引标准：不同存储引擎的索引的工作方式并不一样，也不是所有的存储引擎都支持所有类型的索引

4.B-Tree意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同，能够加快访问数据的速度，从索引的根节点开始进行搜索，适用于全键值、键值范围或键前缀查找

5.B-Tree索引的限制：

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

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

7.哈希索引的限制：

  • 哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行
  • 哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序
  • 哈希索引也不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的
  • 只支持等值比较查询，不支持任何范围查询
  • 访问哈希索引的数据非常快，除非有很多哈希冲突
  • 如果哈希冲突很多的话，一些索引维护操作的代价也会很高
    8.空间数据索引（R-Tree），MyISAM表支持空间索引，可以用作地理数据存储，开源数据库系统中对GIS的解决方案做得比较好的是PostgreSQL的PostGIS
    9.全文索引，适用于MATCH AGAINST操作，而不是普通的WHERE条件操作

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密集型的应用

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

  • 覆盖索引必须要存储索引列的值，

6.如果EXPLAIN出来的type列的值为“index”，则说明MySQL使用了索引扫描来做排序

7.压缩（前缀）索引，默认只压缩字符串，减少索引大小，对于CPU密集型应用，因为扫描需要随机查找，压缩索引在MyISAM上要慢好几倍

8.重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引，应该避免这样创建重复索引

9.索引可以让查询锁定更少的行

D.维护索引和表

1.CHECK TABLE检查表是否损坏，ALTER TABLE innodb_tb1 ENGINE=INNODB;修复表

2.records_in_range()通过向存储引擎传入两个边界值获取在这个范围大概有多少条记录，对于innodb不精确
http://3.info()返回各种类型的数据，包括索引的基数

4.可以使用SHOW INDEX FROM命令来查看索引的基数

5.B-Tree索引可能会碎片化，这会降低查询的效率

六、查询性能优化

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的优化做得并不好

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