mysql性能优化

一、SSA

本系列综合mysql的知识点。

---

二、GTTP

1. 创建Mysql索引：

ALTER TABLE 表名 ADD （可选，UNIQUE/FULLTEXT/SPATIAL/INDEX/KEY） INDEX ’别名‘ (’字段名1‘, ’字段名2‘);

• UNIQUE:可选。表示索引为唯一性索引。
• FULLTEXT:可选。表示索引为全文索引。
• SPATIAL:可选。表示索引为空间索引。
• INDEX和KEY:用于指定字段为索引，两者选择其中之一就可以了，作用是一样的

说明：

• 如果index后的（）里有多个字段名即建立了符合索引/联合索引，这种索引查询的时候必须是从左到右用起来，没有左边的，右边的索引将失效。
• 索引只有在where后指定了这个字段的使用前提下才会用到，统计分页的时候依然需要全表查还是很慢。
• mysql默认为主键创建索引。所以主键一般建议不用uuid，即减少主键的复杂度提高查询速度。

2. 查看已创建的索引：

show index from 表名;

3. 索引的删除：

drop index 索引名 ON 表名

---

MySQL千万级数据的表如何优化

1. 首先肯定是建立上面说的索引，在条件查询的时候使用索引。
2. 分页无条件查询的时候索引就没用了，因为走索引还是要全表所有数据都查到

1. 对应每个表的总数count我们可以单独存储到一个表里面，有数据新增删除的时候就根据表名查出来维护这个表对应的总记录数count，这样就很快。
2. 如果查询的count是有带条件查询的就走索引来计算count。

上面的count是没问题了，分页查询优化要如何优化呢？这里可以使用子查询来优化

select * from user where id>=(select id from user limit 9000000,1) limit 10 

子查询这种写法，判断id，就是通过覆盖索引来查询。效率会大大增加。

总结： 如果数据量实在太大了，就用elasticsearch

三 底层原理

Mysql的索引是B+树，结构示范图如下：

1. 非叶子节点不存储data数据，只存储冗余的索引，这样一行就可以放更多的索引
2. 叶子节点存放了所有的索引字段，以及索引字段对应的数据。
3. 叶子节点之间用指针连接，这个指针是双向的，即范围查找时大于小于均可在叶子节点间直接获取所要的结果，不用返回父节点查找。提高区间访问的性能。
4. 另：b树的缺点相对于b+树就是没有冗余的索引元素，所有的索引都附带对应的数据，导致一个节点因为数据过大放不了多少索引。

Mysql对所有叶子节点和非叶子节点的大小限制为16Kb。经过计算，三层节点能存储的数据大概是1170x1170x16=2190万 2400条数据。Mysql一般的是节点是2~4层节点。

---

聚集索引和非聚集索引原理（mysql默认的InnoDB存储引擎是聚集索引）

MyISAM 解析：
从上面的图可以看到MyISAM 存储引擎的test的表有test.MYD 和 test.MYI 两种文件。

• test.MYD 为表test的数据文件
• test.MYI 为表test的索引文件

由于MyISAM存储引擎的表的数据文件和索引文件分开在两个文件，所以他是非聚集索引。

解读：如果一条sql语句根据用户的id查找id=15的数据，此处的id已经建立了索引，即B+树的结构。那么会先在MYI文件的索引树下查找对应的15和18的节点，将整个节点数据load到内存，在内存中找到id=15这个叶子节点，再根据叶子节点下的0x07这样的地址去MYD文件中去查找这行数据的所有字段的值，load到内存。

---

主键索引 如上图所示，为聚集索引的结构，是InnoDB数据库引擎所采用的B+树结构。
意思就是在叶子节点，索引和data数据连在一起，在磁盘文件中属于同一个.ibd文件。即聚簇索引。

1.为什么InnoDB表必须有主键，且推荐主键是整型的自增主键？

• 因为mysql会自动为主键创建索引，自增的整型主键索引，在新增的时候会正好在最后新增，如果不是自增的出现在中部插入新索引的情况，由于索引的每个节点的最大空间是16kb，中间新加入的索引会导致原先分配好的索引分组全部重构，非常的损耗性能。
• 主键是整型原因：在比对索引值的时候整型的比对相较于字符串和uuid要快很多。

这里是非主键索引的结构

为什么非主键索引的叶子节点存储的是主键的值？

• 因为在存在非主键索引的情况下，当新增或者删除数据的时候，会先去维护索引，如果每个B+树的索引都有一份数据，那就涉及到多份数据的一致性问题，事务就必须做，但是事务会影响性能，所以只维护一份数据就好。
• 其次存在多个非主键索引的话，就要存好多份相同的数据，这样是非常占用空间的，不建议这样做。

---

联合索引/复合索引

说白了就是根据多个字段建立的一个索引
ALTER TABLE 表名 ADD （可选，UNIQUE/FULLTEXT/SPATIAL/INDEX/KEY） INDEX ’别名‘ (’字段名1‘, ’字段名2‘);- ---- -------这里的字段名就应该是多个。

• 联合索引的好处就是节省了Ibd文件的空间，不用分别给每个字段建立单值索引，在数据更新的时候维护索引的成本大大下降。
• 在使用联合索引的时候，where后面必须是字段名1 ，字段名2依次查找，因为从上面的图就可以看出，mysql的排序是字段名1，字段名2依次排序的（先排1 再排2 ）不依次使用索引的话什么，索引将失效。
• 另：使用索引的时候不要用模糊的%张开头匹配，从上面的图可以看出这将直接导致失去索引，后面的字段全乱了不走索引。
• or的两侧要么都索引列，要么都不是索引列。索引列不要参与计算。把过滤掉最多数据的条件放在最前面。mysql的函数用在索引上将导致索引失效（left（字段名，长度数值）会直接导致不知道是哪个索引了。等）

---

覆盖索引

指如果一个索引包含了（或覆盖了）满足查询语句中字段与条件的数据就叫 做覆盖索引。

---

说明

• 当一个字段添加了索引，数据库只有1w条数据，在sql查询的时候范围查找给到的范围是2w，mysql会认为走索引比不走索引更消耗性能，会直接全表扫描这1w条数据。故在sql层面调用了索引，但执行计划显示依然没用索引。
• 特别说明：sql写了用索引，最后究竟用不用，用哪个索引是mysql内部对多个方案进行测试出结构后采用最优的方案来执行。

• 什么是回表？
  简单来说就是数据库根据索引（非主键）找到了指定的记录所在行后，还需要根据主键再次到数据块里获取数据。
  “回表”一般就是指执行计划里显示的“TABLE ACCESS BY INDEX ROWID”。
• 如何避免回表
  将需要的字段放在索引中去。查询的时候就能避免回表
• 回表并不一定效率低，如果回表的数据就一两条，那效率也是很高的。
  覆盖索引（covering index）指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取。也可以称之为实现了索引覆盖。 当一条查询语句符合覆盖索引条件时，MySQL只需要通过索引就可以返回查询所需要的数据，这样避免了查到索引后再返回表操作，减少I/O提高效率。 如，表covering_index_sample中有一个普通索引 idx_key1_key2(key1,key2)。当我们通过SQL语句：select key2 from covering_index_sample where key1 = ‘keytest’;的时候，就可以通过覆盖索引查询，无需回表。

---

优化总结

• MySQL支持两种方式的排序filesort和index，Using index是指MySQL扫描索引本身完成排序。index
  效率高，filesort效率低。

• order by满足两种情况会使用Using index。

  1. order by语句使用索引最左前列。
  2. 使用where子句与order by子句条件列组合满足索引最左前列。

• 尽量在索引列上完成排序，遵循索引建立（索引创建的顺序）时的最左前缀法则。

• 如果order by的条件不在索引列上，就会产生Using filesort(文件排序)。

• 能用覆盖索引尽量用覆盖索引

• group by与order by很类似，其实质是先排序后分组，遵照索引创建顺序的最左前缀法则。对于group
  by的优化如果不需要排序的可以加上order by null禁止排序。注意，where高于having，能写在where中
  的限定条件就不要去having限定了。

Using filesort文件排序原理详解

Using filesort：将用外部排序而不是索引排序，数据较小时从内存排序，否则需要在磁盘
完成排序。这种情况下一般也是要考虑使用索引来优化的。

filesort文件排序方式

• 单路排序：是一次性取出满足条件行的所有字段，然后在sort buffer中进行排序；用trace工具可
  以看到sort_mode信息里显示< sort_key, additional_fields >或者< sort_key,
  packed_additional_fields >
• 双路排序（又叫回表排序模式）：是首先根据相应的条件取出相应的排序字段和可以直接定位行
  数据的行 ID，然后在 sort buffer 中进行排序，排序完后需要再次取回其它需要的字段；用trace工具
  可以看到sort_mode信息里显示< sort_key, rowid >
  MySQL 通过比较系统变量 max_length_for_sort_data(默认1024字节) 的大小和需要查询的字段总大小来
  判断使用哪种排序模式。
• 如果 max_length_for_sort_data 比查询字段的总长度大，那么使用 单路排序模式；
• 如果 max_length_for_sort_data 比查询字段的总长度小，那么使用 双路排序模式。

---

执行计划参数的意义

id：

• id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行。
• id相同，执行顺序由上至下

select_type：

• SIMPLE 简单的select查询，查询中不包含子查询或者UNION

• PRIMARY 查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY

• SUBQUERY 在SELECT或WHERE列表中包含了子查询

• DERIVED 在FROM列表中包含的子查询被标记为DERIVED（衍生），MySQL会递归执行这些子查询，把结果放在临时表中

• UNION 若第二个SELECT出现在UNION之后，则被标记为UNION：若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED

• UNION RESULT 从UNION表获取结果的SELECT

table：

• 指当前执行的表。

type：

• type所显示的是查询使用了哪种类型。

system > const > eq_ref > ref > range > index > all
all：就是查了所有而且还没有走索引。index：是查了所有走了索引较all强了一点点。
range是索引的范围查找，至少不是查所有了。ref：是非唯一的索引。
eq_ref：指的是例如主键unique的索引刚好匹配到一条。const：指的的匹配到了常量。
system：指的是系统特例一般不会出现。

一般来说，得保证查询至少达到range级别，最好能达到ref。

• range 只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引，一般就是在你的where语句中出现between、< 、>、in等的查询，这种范围扫描索引比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。
• index Full Index Scan，Index与All区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和Index都是读全表，但index是从索引中读取的，而all是从硬盘读取的）

possible_keys 和 key:

• possible_keys 显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。

• key 实际使用的索引，如果为NULL，则没有使用索引。（可能原因包括没有建立索引或索引失效）

key_len：

• 这个长度和该索引字段的类型（字符串int或者char，date等）有关，联合索引可以根据key_len来判断是哪个几个索引（联合索引的使用都是从左到右）
• 表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，在不损失精确性的情况下，长度越短越好。key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的