17. 燕十八MySQL优化笔记

mysql

本套笔记是2次通过燕十八视频听讲后整理的，视频出处，该视频目前还算比较新，也是学习PHP比较好的资源。自学it论坛有好几套传世经典视频，都录制的非常不错。我对PHP的很多概念和思维方法，都是从这里起步的。

面试过好几次，确实有这样的感受，面试官和我，很多回答都是大而化之，通用又无用的废话。比如提及MySQL优化，少取字段，建立合理的索引这种废话每次都不会少。这就跟平常说的做人是大方点好，还是小气点好一样。我认为该大方的时候要大方，女朋友要买包，此处该大方；该小气的时候要小气一样。

十八哥的建议是，不要说大话，回答要具体，具体到最小维度最好。不需要说多少，只要说出3条，就是成功的。如果此时面试官还感兴趣，那就继续往下说。这些知识点，你要是反问面试官，他未必就懂多少。公司也未必都用得上，但是如果你面试一个10K，15K以上的工作，总会要问一下这些。

1. 表的优化

1.1 定长与变长分离

如id int 占4个字节，char(4)占4个字符长度，也是定长，每一单元值占的字节是固定的。核心且常用的字段，宜建成定长放在一张表中。而varchar，text，blob这种变长字段，适当单独放一张表，用主键与核心表关联起来。

1.2 常用字段和不常用字段相互分离

需要结合网站具体的业务来分析，分析字段的查询场景，查询额度低的字段单独拆分开来。比如用户头像，昵称，邮箱可能使用比较频繁，但是用户其它信息，只有点击个人主页或者少量场景才会去取，就可以拆出来放入不常用表中。

1.3 在1对多，需要关联统计的字段增加冗余字段

比如论坛每个版块都需要统计新发帖数，已有帖子总数等字段，只需要将该统计字段添加到板块表中即可，每次新增时都对其进行递增，查询时无需再统计具体的表。

目前程序的优化，其实无非就是两种：以空间换时间，以时间换空间。在以前计算机硬件很受限制的时候，实现同样的功能，谁占用的内存最小，可能就是最实用。但是现在不同了，比如排序1G的数据，直接放到内存中排序即可，都不要去想优化策略，等你想好方案，都已经排序完成了。

2. 列选择原则

2.1 字段类型优先级

类型长度：

整形 > date, time > enum, char > varchar > text, blob

列的特点分析

  1. int: 定长，没有国家，地区之分，没有字符集差异。比如tinyint(1) 1, 2, 3, 4, 5 <-> char(1) a, b, c, d, e，从空间上都是占用1个字节，但是ORDER BY 排序，整形快。原因是字符类型需要考虑字符集和校对集（即是排序规则）；
  2. time: 定长，运算快，节省时间，考虑时区，SQL书写时不方便表达 WHERE >= ‘2017-08-17’ 这样的表达式，需要借助函数或者稍作转化；
  3. enum: 能起约束值的目的，内部用整形来存储，但与char联查时，内部要经历串与值的转化；
  4. char: 定长，考虑字符集和排序校对集；
  5. varchar: 不定长，要考虑字符集转换和排序校对集，速度慢；
  6. text/blob: 无法使用内存临时表等操作，糟糕时只能在磁盘上进行排序，良好时在内存进行排序等。

比如性别，字符集以utf-8为例:

char(1): 3个字节长度;

enum(‘男’, ‘女’): 内部转成数字来存储，多了一个转换过程;

tinyint(1): 0，1，2 定长1个字节。

2.2 够用就行，不要慷慨

比如smallint,varchar(N)。原因是大的字段消耗内存，影响速度。以年龄为例，tinyint(1) unsigned not null 可以存储最大255岁，用int浪费了三个字节。以varchar(10),varchar(300)存储的内容相同，但在表联查时要浪费更难多内存。

2.3 尽量避免使用NULL

null不利于索引，要用特殊的字节来标注。在磁盘上占据空间其实更大，MySQL5.7里关于null已经做了优化，大小区别不明显。null也不便于查询，需要用单独的is null等子句。同时静态语言在未经处理时会将其处理成错误，比如NULL指针。

3. 索引优化

索引是一种高效查询的数据结构。良好的索引能够提高查询，排序，分组速度。

3.1 B-Tree索引

大的方面看，都用的平衡树，但具体的实现上，各引擎稍有不同，MyISAM,InnoDB默认使用B-Tree索引。但抽象一下，B-Tree树系统可理解为排好序的快速查找结构。

3.2 Hash索引

Memory表使用Hash索引，查询速度更快，理论复杂度O(1)。

3.3 既然哈希查找速度快，为什么不用哈希索引？

  1. Hash计算的结果是随机的，在磁盘上随机放置，地址可能有冲突，使用了拉链算法，磁盘会有很多空洞；
  2. 无法对范围查询进行优化；
  3. 无法利用前缀索引，因为哈希操作后前缀可能差别很大；
  4. 排序也无法优化，数据存储位置不连续，分散在磁盘上；
  5. 必须回行，就是说，通过索引拿到数据位置，必须回到表中取数；

3.4 B-Tree索引常见误区

在WHERE条件上常用的列都加上索引。

例如WHERE cat_id = 3 and price > 100，在cat_id和price上都加上索引，这样只能用上cat_id或price索引。独立的索引，同时只能用一个。实际使用中，更多的是联合索引，比如建立cat_price(cat_id, price)联合索引。

在多列上建立索引后，查询哪个列索引都将发生作用，多列索引上索引发生作用需要满足左前缀要求。

  1. INDEX(a, b, c)；
  2. WHERE a = 3 只用a索引；
  3. WHERE a = 3 AND b = 4 使用了a, b索引；
  4. WHERE a = 3 AND b = 4 AND c = 5 使用了a, b, c索引；
  5. WHERE b = 3 / WHERE c = 4 无法使用索引；
  6. WHERE a = 3 AND c = 4 能使用a索引；
  7. WHERE a = 3 AND b LIKE ‘xxx%’ 能使用a, b索引。

必须按顺序使用，否则不能使用索引，遵从左前缀原则。

3.5 聚族索引和非聚簇索引

MyISAM 非聚族索引，一级索引和次级索引都指向磁盘上的位置。

InnoDB 聚族索引，树上保存信息，不需要回行，即根据索引在此查找数据，次级索引指向对主键索引的地址引用，但是不规则数据会造成页分裂。

3.6 索引覆盖

如果查询的列刚好是索引的一部分，那么查询只需要在索引文件上进行，不需要回行在磁盘上找数据，这种查询速度非常快。

3.7 理想的索引

  1. 查询频繁；
  2. 区分度高，比如100万用户，使用性别做索引的区分度就不高；
  3. 长度小，索引长度直接影响索引文件的大小，影响增删改的速度和查询速度，合理使用左前缀；
  4. 尽量能覆盖常用查询字段。

3.8 针对列中的值，从左往右截取部分来建索引

截的越短，重复读越高，区分度越小，索引效果越不好；截的越长，重复度低，区分度高，索引效果越好，但带来的影响也越大，增删改变慢并影响查询速度。

比如单词word，从左往右截取，(select count(distinct left(word, 1)) from dict) / (select count(*) from dict)，即是计算做前缀去重后的条数与总条数的差异，找到趋于平衡的位置，就选择该处建立做前缀索引。对于一般的系统应用，区别度能达到0.1索引的性能就可以接受。

3.9 对于做前缀不易区分的列，建立索引的技巧

比如url列。

  1. https://www.baidu.com
  2. http://www.zixue.it

列的前面字符几乎是一样的，可以用以下两个办法来解决:

  1. 倒叙存取数据，区分度相对较大;比如网址，因为前面基本相同，com,cn,net,me等
  2. 伪哈希技巧，crc32(str)函数计算一个字符串的 crc32 多项式，能把字符串转为32位整数，一种哈希算法。然后给这个整形加索引,查询时也按该方法转化。数据也可能会有重复，但这是值得的，索引长度为int4个字节。

3.10 多列索引

列的查询频率，区分度，结合具体的业务来查找出最好的索引。

3.11 索引与排序

排序可能发生2种情况：

  1. 对于覆盖索引，直接在索引上索引时，就是有顺序的；
  2. 在InnoDB引擎中，沿着索引字段排序，也是自然有序的；
  3. 对于MyISAM引擎，如果按某索引字段排序，如id，但取出的字段中，有未索引字段，则需要回行，在排序。

先取出数据，形成临时表做filesort文件排序，但文件可能在磁盘上也可能在内存中。我们要争取，取出来的数据本身就是有序的，用索引来排序。

3.12 重复索引

在同一个列，或者顺序相同的几个列，建立了多个索引。重复索引没有任何帮助，只会增大索引文件，拖慢更新速度，去掉。

3.13 冗余索引

2个索引所覆盖的列有重叠，顺序不一样的视情况而定。

3.14 索引碎片和维护

在长期的数据更改过程中，索引文件和数据文件，都将产生空洞，形成碎片，我们可以通过一个nop操作（不产生对数据实质影响的操作）来修改表。比如表引擎innodb，可以alter table xxx engine innodb，optimize table 表名，也可以修复。

修复表的数据及索引碎片，就会把所有的数据文件重新整理一遍，使之对齐。这个过程，如果表的行数比较大，也是非常耗时的操作，所以不能频繁修复。

如果表的update操作很频繁，可以按周，按月来修复，如果不频繁，可以更长的周期来做修复。

3.15 查询大原则

sql的时间花在哪里？

等待，执行时间。这两个时间并非孤立的，如果单条语句执行快了，对其它语句的锁定也就少了其它语句受到干扰，关键在执行上。

执行时间耗费？

  1. 查找，沿着索引查找，慢者可能拖慢全表扫描；
  2. 取出，查到行后，把数据取出来。

如何查找快？

  1. 联合索引；
  2. 索引覆盖；
  3. 传输少，更少的行和列。

切分查询，按数据拆成多次；分解查询，按逻辑把多个表连接查询分成多个简单SQL。

SQL语句优化思路：

  1. 不查，通过业务逻辑来计算，比如观察长期的数据变化，论坛会员注册数，适当增加，而不需要精确计算数据库中的值；
  2. 少查，尽量精准的查，少取行，一般一次性取10-30条左右；
  3. 必须要查，高效的查，尽量从索引上查，取时，少取行和列。

4. Explain

explain是MySQL的语句执行计划，用来查看索引的使用情况。以下内容来源自MySQL中EXPLAIN结果的参数详解。

mysql> 
mysql> explain select * from user \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1
     filtered: 100.00
        Extra: NULL
1 row in set, 1 warning (0.00 sec)

mysql> 
mysql>

4.1 id

显示语句的执行顺序，多个select时递增。

4.2 select_type

  1. SIMPLE：简单的SELECT，不实用UNION或者子查询。
  2. PRIMARY：最外层SELECT。
  3. UNION：第二层，在SELECT之后使用了UNION。
  4. DEPENDENT UNION：UNION语句中的第二个SELECT，依赖于外部子查询。
  5. UNION RESULT：UNION的结果。
  6. SUBQUERY：子查询中的第一个SELECT。
  7. DEPENDENT SUBQUERY：子查询中的第一个SELECT，取决于外面的查询。
  8. DERIVED：导出表的SELECT（FROM子句的子查询）

通常都是简单查询SIMPLE。

4.3 table

使用的表名，使用别名时显示别名，无表是NULL。

4.4 partitions

分区情况。

4.5 type

这是优化首先需要关注的地方，这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型：const、eq_reg、ref、range、index和all。

Type：告诉我们对表使用的访问方式，主要包含如下集中类型。

  1. all：全表扫描。
  2. const：读常量，最多只会有一条记录匹配，由于是常量，实际上只须要读一次。
  3. eq_ref：最多只会有一条匹配结果，一般是通过主键或唯一键索引来访问。
  4. fulltext：进行全文索引检索。
  5. index：全索引扫描。
  6. index_merge：查询中同时使用两个（或更多）索引，然后对索引结果进行合并（merge），再读取表数据。
  7. index_subquery：子查询中的返回结果字段组合是一个索引（或索引组合），但不是一个主键或唯一索引。
  8. rang：索引范围扫描。
  9. ref：Join语句中被驱动表索引引用的查询。
  10. ref_or_null：与ref的唯一区别就是在使用索引引用的查询之外再增加一个空值的查询。
  11. system：系统表，表中只有一行数据；
  12. unique_subquery：子查询中的返回结果字段组合是主键或唯一约束。

4.6 possible_keys

显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句，这个可以不用关注。

4.7 key

实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MYSQL会选择优化不足的索引。这种情况下，可以在SELECT语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MYSQL忽略索引。

4.8 key_len

使用的索引的长度。在不损失精确性的情况下，长度越短越好。

4.9 ref

显示索引的哪一列被使用了，如果可能的话，是一个常数。

4.10 rows

MYSQL认为必须检查的用来返回请求数据的行数，该数值是一个相对准确的数据，不是实际表中存有的数据。

4.11 Extra

关于MYSQL如何解析查询的额外信息，坏的例子是Using temporary和Using filesort，意思MYSQL根本不能使用索引，结果是检索会很慢。

Extra字段解释

Extra：查询中每一步实现的额外细节信息，主要会是以下内容。

  1. Distinct：查找distinct 值，当mysql找到了第一条匹配的结果时，将停止该值的查询，转为后面其他值查询。
  2. Full scan on NULL key：子查询中的一种优化方式，主要在遇到无法通过索引访问null值的使用。
  3. Range checked for each record (index map: N)：通过 MySQL 官方手册的描述，当 MySQL Query Optimizer 没有发现好的可以使用的索引时，如果发现前面表的列值已知，部分索引可以使用。对前面表的每个行组合，MySQL检查是否可以使用range或 index_merge访问方法来索取行。
  4. SELECT tables optimized away：当我们使用某些聚合函数来访问存在索引的某个字段时，MySQL Query Optimizer 会通过索引直接一次定位到所需的数据行完成整个查询。当然，前提是在 Query 中不能有 GROUP BY 操作。如使用MIN()或MAX()的时候。
  5. Using filesort：当Query 中包含 ORDER BY 操作，而且无法利用索引完成排序操作的时候，MySQL Query Optimizer 不得不选择相应的排序算法来实现。
  6. Using index：所需数据只需在 Index 即可全部获得，不须要再到表中取数据。
  7. Using index for group-by：数据访问和 Using index 一样，所需数据只须要读取索引，当Query 中使用GROUP BY或DISTINCT 子句时，如果分组字段也在索引中，Extra中的信息就会是 Using index for group-by。
  8. Using temporary：当 MySQL 在某些操作中必须使用临时表时，在 Extra 信息中就会出现Using temporary 。主要常见于 GROUP BY 和 ORDER BY 等操作中。
  9. Using where：如果不读取表的所有数据，或不是仅仅通过索引就可以获取所有需要的数据，则会出现 Using where 信息。
  10. Using where with pushed condition：这是一个仅仅在 NDBCluster存储引擎中才会出现的信息，而且还须要通过打开 Condition Pushdown 优化功能才可能被使用。控制参数为 engine_condition_pushdown 。
  11. Impossible WHERE noticed after reading const tables：MySQL Query Optimizer 通过收集到的统计信息判断出不可能存在结果。
  12. No tables：Query 语句中使用 FROM DUAL或不包含任何 FROM子句。
  13. Not exists：在某些左连接中，MySQL Query Optimizer通过改变原有 Query 的组成而使用的优化方法，可以部分减少数据访问次数。