MySQL性能优化

Schema与数据类型优化

1. 选择数据类型原则

   • 更小的通常更好

     更小的数据类型通常更快，因为它们占用更少的磁盘、内存和CPU缓存，并且处理时需要的CPU周期也更少。
     - 简单就好
     > 简单数据类型的操作通常需要更少的CPU周期。
     - 尽量避免NULL
     > 如果查询中包含可为NULL的列，对MySQL来说更难优化，因为可为NULL的列使得索引、索引统计和值比较都更复杂。
     > 如果已有字段为NULL，在不做问题的情况下，也没有必要一定要将其转为NOT NULL。
     > 如果计划在列上建索引，应该尽量避免设计成可为NULL的列。

2. Schema设计的坏味道

   • 太多的列

     MySQL的存储引擎API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列。从行缓冲中将编码过的列转换成行数据结构的操作代价非常高。
     - 太多的关联
     > MySQL限制了每个关联操作最多只能有61张表
     > 如果希望查询执行得快速且并发性好，单个查询最好在12个表以内做关联。

3. 冗余、缓存与汇总
   冗余：反范式设计，添加冗余字段，防止关联产生的性能消耗
   缓存：存储可以比较简单地从其他表中的schema获取数据的表
   汇总：存储使用GROUP BY等聚合语句聚合数据的表

索引优化

索引（在MySQL中也叫做“键（key）”）是存储引擎用于快速找到记录的一种数据结构。

只有当索引帮助存储引擎快速查找到记录带来的好处大于其带来的额外工作时，索引才是有效的。

1. 索引的类型

   • B-Tree（B+Tree）索引

   B-Tree索引意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同。
   * 哈希索引
   > 哈希所有基于哈希表实现，只有精确匹配索引所有列的查询才有效。哈希索引只能用于等值比较。
   * 空间索引
   > MyISAM表支持空间索引，可以用作地理数据存储。
   MySQL5.7，添加了InnoDB的GIS支持。
   * 全文索引
   > 在相同的列上同时创建全文索引和基于值的B-Tree索引不会有冲突，全文索引适用于MATCH AGAINST操作，而不是普通的WHERE条件操作。

2. 索引的优点

   • 大大减少了服务器需要扫描的数据量
   • 帮助服务器避免排序和临时表
   • 将随机I/O变为顺序I/O

3. 高性能的索引策略

   • 独立的列

   索引列不能是表达式的一部分，也不能是函数的参数
   * 前缀索引和索引选择性
   > 索引的选择性是指，不重复的索引值（也称为基数，cardinality）和数据表的记录总数（#T）的比值，范围从1/#T到1之间。
   * 选择合适的索引列顺序
   > 正确的顺序依赖于使用该索引的查询，并且同时需要考虑如何更好地满足排序和分组的需要。

   当不需要考虑排序和分组时，将选择性最高的列放在前面通常有效，但是这只使用于优化WHERE条件的场景；不同场景对应不同的选择，没有一个通用的解决方案。

   • 聚簇索引

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

   术语“聚簇”表示数据行和相邻的键值紧凑地存储在一起。因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引（一般就是主键索引）。

   • 覆盖索引

   如果一个索引包含所有需要查询的字段的值，那么就称之为“覆盖索引”

   当发起一个被索引覆盖的查询时，使用EXPLAIN语句可以看到Extra列对应的值为“Using index”。

   • 使用索引扫描来排序

   MySQL有两种方式可以生成有序的结果：通过排序条件；或者按索引顺序扫描。

   当MySQL使用索引顺序扫描来排序，使用EXPLAIN语句可以看到type列对应的值为“index”

   • 索引和锁

   InnoDB只有在访问行的时候才会对其加锁，而索引能够减少InnoDB访问的行数，从而减少锁的数量。

   即使使用了索引，InnoDB也可能锁住一些不需要的数据。如果不能使用索引查找和锁定行的话可能会更糟，MySQL会做全表扫描并锁住所有的行，而不管是不是需要。

   InnoDB在二级索引上使用共享（读）锁，但访问主键索引使用排他（写）锁。这消除了使用覆盖索引的可能性，并使得SELECT FOR UPDATE操作比LOCK IN SHARE MODE或非锁定查询要慢很多。