MySQL 性能优化总结 - Dudashuang

`mysql` 索引原理

索引目的

索引的目的在于提高查询效率，可以类比字典，如果要查“mysql”这个单词，我们肯定需要定位到m字母，然后从下往下找到y字母，再找到剩下的sql。如果没有索引，那么你可能需要把所有单词看一遍才能找到你想要的，如果我想找到m开头的单词呢？或者ze开头的单词呢？是不是觉得如果没有索引，这个事情根本无法完成？

索引原理

除了词典，生活中随处可见索引的例子，如火车站的车次表、图书的目录等。它们的原理都是一样的，通过不断的缩小想要获得数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是我们总是通过同一种查找方式来锁定数据。数据库也是一样，但显然要复杂许多，因为不仅面临着等值查询，还有范围查询(>、<、between、in)、模糊查询(like)、并集查询(or)等等。数据库应该选择怎么样的方式来应对所有的问题呢？我们回想字典的例子，能不能把数据分成段，然后分段查询呢？最简单的如果1000条数据，1到100分成第一段，101到200分成第二段， 201到300分成第三段……这样查第250条数据，只要找第三段就可以了，一下子去除了90%的无效数据。但如果是1千万的记录呢，分成几段比较好？稍有算法基础的同学会想到搜索树，其平均复杂度是lgN，具有不错的查询性能。但这里我们忽略了一个关键的问题，复杂度模型是基于每次相同的操作成本来考虑的，数据库实现比较复杂，数据保存在磁盘上，而为了提高性能，每次又可以把部分数据读入内存来计算，因为我们知道访问磁盘的成本大概是访问内存的十万倍左右，所以简单的搜索树难以满足复杂的应用场景。

磁盘IO与预读

前面提到了访问磁盘，那么这里先简单介绍一下磁盘IO和预读，磁盘读取数据靠的是机械运动，每次读取数据花费的时间可以分为寻道时间、旋转延迟、传输时间三个部分，寻道时间指的是磁臂移动到指定磁道所需要的时间，主流磁盘一般在5ms以下；旋转延迟就是我们经常听说的磁盘转速，比如一个磁盘7200转，表示每分钟能转7200次，也就是说1秒钟能转120次，旋转延迟就是1/120/2 = 4.17ms；传输时间指的是从磁盘读出或将数据写入磁盘的时间，一般在零点几毫秒，相对于前两个时间可以忽略不计。那么访问一次磁盘的时间，即一次磁盘IO的时间约等于5+4.17 = 9ms左右，听起来还挺不错的，但要知道一台500 -MIPS的机器每秒可以执行5亿条指令，因为指令依靠的是电的性质，换句话说执行一次IO的时间可以执行40万条指令，数据库动辄十万百万乃至千万级数据，每次9毫秒的时间，显然是个灾难。

考虑到磁盘IO是非常高昂的操作，计算机操作系统做了一些优化，当一次IO时，不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，因为局部预读性原理告诉我们，当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到。每一次IO读取的数据我们称之为一页(page)。具体一页有多大数据跟操作系统有关，一般为4k或8k，也就是我们读取一页内的数据时候，实际上才发生了一次IO，这个理论对于索引的数据结构设计非常有帮助。

索引的数据结构

前面讲了生活中索引的例子，索引的基本原理，数据库的复杂性，又讲了操作系统的相关知识，目的就是让大家了解，任何一种数据结构都不是凭空产生的，一定会有它的背景和使用场景，我们现在总结一下，我们需要这种数据结构能够做些什么，其实很简单，那就是：每次查找数据时把磁盘IO次数控制在一个很小的数量级，最好是常数数量级。那么我们就想到如果一个高度可控的多路搜索树是否能满足需求呢？就这样，b+树应运而生。

详解B+树

如上图，是一颗b+树，关于b+树的定义可以参见b+树，这里只说一些重点，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。

B+树的查找过程

如图所示，如果要查找数据项29，那么首先会在内存中用二分查找确定29在17和35之间，（根节点常驻内存）锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第一次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第二次IO，同时内存中做二分查找找到29，结束查询，总计两次IO。真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要两次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

B+树性质

通过上面的分析，我们知道IO次数（h - 1）取决于b+数的高度h，假设当前数据表的数据为N，每个磁盘块的数据项的数量是m，则有h=㏒(m+1)N，当数据量N一定的情况下，m越大，h越小；而m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为什么每个数据项，即索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。当数据项等于1时将会退化成线性表,事实上，磁盘块通常很大，树的高度通常不超过3。
当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候， b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时， b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了，这个是非常重要的性质，即索引的最左匹配特性。

建立索引的几大原则

最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序， mysql的查询优化器会帮你优化成索引可以识别的形式
尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录
索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);
尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可

索引覆盖

当我们使用innodb引擎存储数据时，mysql 会建立一个聚簇索引，即以主键建立一个B+树，在叶子节点中存放实际数据。所以当我们基于主键查询时，速度会非常快。注意，聚簇索引并不是一种单独的索引类型，而是一种数据存储方式，即在同一结构中保存索引和数据行。
如果没有定义主键，innodb会选择一个唯一的非空索引代替，如果没有这样的索引，会隐式定义一个主键来作为聚簇索引。
而普通索引叶子节点中存放的为主键，并不是数据项的实际物理位置。所以基于普通索引的查询很有可能发生两次查找（1通过普通索引取主键，2通过主键查找数据也就是所谓的回表）。

如果我们需要select的数据刚好都在索引中时，则会省略第二步的查找，即索引覆盖，如下：

  #聚合索引 idex_cov(name, age)，一次查找
  select name, age from student where name = 'aa';
	
  ##单列索引 idex_name(name), 俩次查找
  select name, age from student where name = 'aa';

mysql `join` 原理

mysql 在 join 上只实现了一种算法Nested-Loop Join, 即嵌套循环，性能比较一般：

  Table   Join Type
  t1      range
  t2      ref
  t3      ALL

  for each row in t1 matching range {
    for each row in t2 matching reference key {
      for each row in t3 {
        if row satisfies join conditions, send to client
      }
    }
  }

具体过程是，mysql 优化器会对t1, t2, t3 3张表分别筛选，取出结果集较小t1的作为驱动表，然后遍历t1的结果集，依次判断是否存在t2结果集中，如果在则继续判断是否存在t3结果集中。如果不存在则跳出次轮循环继续下一轮,只针对于inner join。
```
  select o.paid_at, ab.name as branch_name, o.payment_type, o.payment_scene, ro.total_amount
  from receipt_orders as ro
  inner join orders as o on o.order_id = ro.orders_id
  left join app_branches as ab on ab.id = ro.branch_id
  where o.app_id = ?
  and o.paid_at between ? and ?;
```
如上例所示，当ro和o两张表都筛选出百万的结果集时，性能的压力将会变得巨大。解决方法：添加冗余字段，避免大表join。

慢查询优化步骤

先运行看看是否真的很慢，注意设置SQL_NO_CACHE
where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高
explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询）
order by limit 形式的sql语句让排序的表优先查
了解业务方使用场景
加索引时参照建索引的几大原则
观察结果，不符合预期继续从1分析

`explain` 使用说明

example
```
  explain select count(*) from a;
```

explain 列的解释,具体参考explain-out：

列	描述
`table`	显示这一行的数据是关于哪张表的。
`type`	这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为 `const`、`eq_reg`、`ref`、`range`、`index`和`ALL`。
`possible_keys`	显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从`WHERE`语句中选择一个合适的语句。
`key`	实际使用的索引。如果为`NULL`，则没有使用索引。很少的情况下，MySQL会选择优化不足的索引。这种情况下，可以在`SELECT`语句中使用`USE INDEX（indexname）` 来强制使用一个索引或者用`IGNORE INDEX（indexname）`来强制MySQL忽略索引。
`key_len`	使用的索引的长度。在不损失精确性的情况下，长度越短越好。
`ref`	显示索引的哪一列被使用了，如果可能的话，是一个常数。
`rows`	MySQL认为必须检查的用来返回请求数据的行数。
`Extra`	关于MySQL如何解析查询的额外信息。将在下表中讨论，但这里可以看到的坏的例子是`Using temporary`和`Using filesort`，意思MySQL根本不能使用索引，结果是检索会很慢。

extra 列返回的描述的意义：

值	意义
`Distinct`	一旦MySQL找到了与行相联合匹配的行，就不再搜索了。
`Not exists`	MySQL优化了`LEFT JOIN`，一旦它找到了匹配`LEFT JOIN`标准的行，就不再搜索了。
`Range checked for each Record（index map:#）`	没有找到理想的索引，因此对于从前面表中来的每一个行组合，MySQL检查使用哪个索引，并用它来从表中返回行。这是使用索引的最慢的连接之一。
`Using filesort`	看到这个的时候，查询就需要优化了。MySQL需要进行额外的步骤来发现如何对返回的行排序。它根据连接类型以及存储排序键值和匹配条件的全部行的行指针来排序全部行。
`Using index`	列数据是从仅仅使用了索引中的信息而没有读取实际的行动的表返回的，这发生在对表的全部的请求列都是同一个索引的部分的时候。
`Using temporary`	看到这个的时候，查询需要优化了。这里，MySQL需要创建一个临时表来存储结果，这通常发生在对不同的列集进行`ORDER BY`上，而不是`GROUP BY`上。
`Where used`	使用了`WHERE`从句来限制哪些行将与下一张表匹配或者是返回给用户。如果不想返回表中的全部行，并且连接类型`ALL`或`index`，这就会发生，或者是查询有问题不同连接类型的解释（按照效率高低的顺序排序）。
`system`	表只有一行 `system` 表。这是const连接类型的特殊情况。
`const`	表中的一个记录的最大值能够匹配这个查询（索引可以是主键或惟一索引）。因为只有一行，这个值实际就是常数，因为MySQL先读这个值然后把它当做常数来对待。
`eq_ref`	在连接中，MySQL在查询时，从前面的表中，对每一个记录的联合都从表中读取一个记录，它在查询使用了索引为主键或惟一键的全部时使用。
`ref`	这个连接类型只有在查询使用了不是惟一或主键的键或者是这些类型的部分（比如，利用最左边前缀）时发生。对于之前的表的每一个行联合，全部记录都将从表中读出。这个类型严重依赖于根据索引匹配的记录多少—越少越好。
`range`	这个连接类型使用索引返回一个范围中的行，比如使用>或<查找东西时发生的情况。
`index`	这个连接类型对前面的表中的每一个记录联合进行完全扫描（比`ALL`更好，因为索引一般小于表数据）。
`ALL`	这个连接类型对于前面的每一个记录联合进行完全扫描，这一般比较糟糕，应该尽量避免。

杂记

select count(*)要比select count(column)快，即使column为主键， mysql在解析count(*)时会忽略所有列，直接统计行数，事实上你可以select count(0), select count(1)…
select a.id, a.name, b.age from a inner join b on a.b_id = b.id limit 10;当a,b表都很大时，在业务层分两步查询，往往会更快。
```
  select a.id, a.name, a.b_id from a limit 10;
  select b.age, b.id from b where b.id in (b.ids);
```
在分页时，limit 10 offset 3000000可以换成where id > ? limit 10,适用于id顺序增长。
通常来说把可为null的列改为not null不会对性能提升有多少帮助，只是如果计划在列上创建索引，就应该将该列设置为nut null。
对整数类型指定宽度，比如int(11)，没有任何卵用。int使用32位（4个字节）存储空间，那么它的表示范围已经确定，所以int(1)和int(20)对于存储和计算是相同的。
unsigned表示不允许负值，大致可以使正数的上限提高一倍。比如tinyint存储范围是-128 ~ 127，而unsigned tinyint存储的范围却是0 - 255。
schema的列不要太多。原因是存储引擎的API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列，这个转换过程的代价是非常高的。如果列太多而实际使用的列又很少的话，有可能会导致CPU占用过高。
大表添加新字段非常耗时，可以在创建之初就预留几个备用字段，但不宜过多。

mysql 索引原理