高性能Mysql阅读笔记（一）

1、创建高性能的索引

索引是存储引擎用于快速找到记录的一种数据结构，这是索引的基本功能。
索引对于良好的性能非常关键。尤其是当表中的数据量越来越大时，索引对性能的影响越发重要。在数据量较小且负载较低时，不恰当的索引对性能的影响可能还不明显，但是当数据量逐渐增大时，性能则会急剧下降。
索引可以包含一个或多个列的值。如果索引包含多个列，那么列的顺序也十分重要，因为Mysql只能高效地使用索引的最左前缀列。

2、索引的类型

在mysql中，索引是在存储引擎层而不是服务层实现的。索引，并没有统一的索引标准：不同的存储引擎的索引的工作方式不一样，也不是所有的存储引擎支持所有类型的索引。即使多个存储引擎支持同一种类型的索引，其底层的实现也可能不同。

2.1、B-Tree索引

B-Tree通常意味着所有的值都是按顺序存储的，并且每一个叶子页到根的距离相同。
B-Tree索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点开始进行搜索。根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找。通过比较节点页的值和要查找的值可以找到合适的指针进入下层子节点，这些指针实际上定义了子节点页中值的上限和下限。最终存储引擎要么找到对应的值，要么该记录不存在。
叶子节点比较特别，它们的指针指向的是被索引的数据，而不是其他的节点页，其实在根节点和叶子节点之间可能有很多层节点页。树的深度和表的大小直接相关。
B-Tree对索引列是顺序组织存储的，所以很适合查找范围数据。
可以使用B-Tree索引的查询类型。B-Tree索引适用于全键值、键值范围或键前缀查找。其中键前缀查找只适用于最左前缀的查找。

全值匹配：全值匹配指的是和索引中的所有列进行匹配。
匹配最左前缀
匹配列前缀
匹配范围值
精确匹配某一列并范围匹配另外一列
只访问索引的查询
因为索引树中的节点是有序的，所以除了按值查找之外，索引还可以用于查询中的ORDER BY操作（按顺序查找）。一般来说，如果B-Tree可以按照某种方式查找到值，那么也可以按照这种方式用于排序。所以，如果ORDER BY子句满足前面列出的几种查询类型，则这个索引也可以满足对应的排序需求。
关于B-Tree索引的限制
（1）、如果不是按照索引最左列开始查找，则无法使用索引。
（2）、不能跳过索引中的列
（3）、如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。
索引是最好的解决方案吗？
索引并不总是最好的工具。总的来说，只有当索引帮助存储引擎快速查找到记录带来的好处大于其带来的额外工作时，索引才是有效的。对于非常小的表，大部分情况下简单的全表扫描更高效。对于中到大型的表，索引就非常有效。但对于特大型的表，建立和使用索引的代价将随之增长。这种情况下，则需要一种技术可以直接区分出查询需要的一组数据，而不是一条记录一条记录地匹配。例如可以使用分区技术。

2.2、高性能的索引策略

（1）、独立的列：我们通常会看到一些查询不当地使用索引，或者使得Mysql无法使用已有的索引。如果查询中的列不是独立的，则Mysql就不会使用索引。独立的列是指索引列不能是表达式的一部分，也不能是函数的参数。
（2）、前缀索引和索引选择性
有时候需要索引很长的字符列，这会让索引变得大且慢，一个策略就是模拟哈希索引。
通常可以索引开始的部分字符，这样可以大大节约索引的空间，从而提高索引效率。但是这样也会降低索引的选择性。索引的选择性是指，不重复的索引值（也称为基数）和数据表的记录总数#T的比值，范围从1/#T到1之间。索引的选择性越高则查询效率越高，因为选择性高的索引可以让Mysql在查找时过滤掉更多的行，唯一的索引的选择性是1，这是最好的索引选择性，性能也是最好的。
一般情况下某个列前缀的选择性也是足够高的，足以满足查询性能。对于BLOB、TEXT或者很长的VARCHAR类型的列，必须使用前缀索引，因为Mysql不允许索引这些列的定整长度。
诀窍在于要选择足够长的前缀以保证较高的选择性，同时又不能太长。前缀应该足够长，以使得前缀索引的选择性接近于索引整个列。换句话说，前缀的基数应该接近于完整的列的基数。计算合适的前缀长度的另一个办法就是计算完整列的选择性，并使前缀的选择性接近于完整列的选择性。

mysql> SELECT COUNT(DISTINCT city)/COUNT(*) FROM city_demo;

创建前缀索引：

mysql> ALTER TABLE city_demo ADD KEY (city(7));

前缀索引是一种能是索引更小、更快的有效办法，但另一方面也有缺点：Mysql无法使用前缀索引做ORDER BY和GROUP BY，也无法使用前缀索引做覆盖扫描。
（3）、多列索引
先来看第一个问题，为每个列创建独立的索引，从SHOW CREATE TABLE中很容易看到：

CREATE TABLE t(
  c1 INT,
  c2 INT,
  c3 INT,
  KEY(c1),
  KEY(c2),
  KEY(c3)
);

这种索引策略，一般是由于“把WHERE条件里面的列都建上索引”这样的模糊的建议导致的。实际上这个建议是非常错误的。这样一来最好的情况也只能是一星索引，其性能比起真正的最有索引可能差几个数量级。有时如果无法设计一个三星索引，那么不如忽略掉WHERE子句，集中精力优化索引列的顺序，或者创建一个全覆盖索引。
在多个列上创建独立的单列索引大部分情况下并不能提高Mysql的查询性能。Mysql5.0和更新版本引入了一种叫索引合并的策略，一定程度上可以使用表上的多个单列索引来定位指定的行。更早版本的Mysql只能使用其中某一单列索引，然而这种情况下没有哪一个独立的单列索引是非常有效的。
索引合并策略有时候是一种优化的结果，但实际上更多的时候表明了表上的索引键得很糟糕：
1、当出现服务器对多个索引做相交操作时（通常有多个AND条件），通常意味着需要一个包含所有相关列的多列索引，而不是多个独立的单列索引。
2、当服务器需要对多个索引做联合操作时（通常有多个OR条件），通常需要耗费大量CPU和内存资源在算法的缓存、排序和合并操作上。特别是当其中有些索引的选择性不高，需要合并扫描返回的大量数据的时候。
3、更重要的是，优化器不会把这些计算到查询成本中，优化器只关心随机页面读取，这会使得查询的成本被低估，导致该执行计划还不如直接走全表扫描。
如果在EXPLAIN中看到有索引合并，应该好好检查以下查询和表的结构，看是不是已经是最优的。也可以通过参数optimizer_switch来关闭索引合并功能。也可以使用IGNORE INDEX提示让优化器忽略掉某些索引。
（4）、选择合适的索引列顺序
正确的顺序依赖于使用该索引的查询，并且同时需要考虑如何更好的满足排序和分组的需要。
在一个多列B-Tree索引中，索引列的顺序意味着首先按照最左列进行排序，其次是第二列，等等。所以，索引可以按照生序或者降序进行扫描，以满足精确符合列顺序的ORDER BY、GROUP BY和DISTINCT等子句的查询需求。
当不需要考虑排序和分组时，将选择性最高的列放在前面通常是很好的。这时候索引的作用只是用于优化WHERE条件查找。在这种情况下，这样的设计的索引确实能够最快地过滤出需要的行，对于在WHERE子句中只使用了索引部分前缀列的查询来说选择性也更高。然而，性能不只是依赖于所有索引列的选择性，也和查询条件的具体值有关，也就和值的分部有关。
（5）、冗余和重复索引
Mysql允许在相同列上创建多个索引，无论是有意的还是无意的。Mysql需要单独维护重复的索引，并且优化器在优化查询的时候也需要逐个地进行考虑，这会影响性能。
重复索引是指在相同的列上按照相同的顺序创建的相同类型的索引。应该避免这样创建重复索引，发现以后应该立即移除。
冗余索引通常发生在为表添加新索引的时候。例如，有人可能会增加一个新的索引（A,B）而不是扩展已有的索引（A）。还有一种情况是将一个索引扩展为（A,ID），其中ID是主键，对于Innodb来说主键列已经包含在二级索引中了，所以这也是冗余的。
大多数情况下都不需要冗余索引，应该尽量扩展已有的索引而不是创建新索引。但也有时候出于性能方面的考虑需要冗余索引，因为扩展已有的索引会导致其变得太大，从而影响其他使用该索引的查询的性能。
（6）、索引和锁
索引可以让查询锁定更少的行，如果查询从不访问那些不需要的行，那么就会锁定更少的行，从两个方面来看这对性能都有好处。首先，虽然Innodb的行锁效率很高，内存使用也很少，但是锁定行的时候仍然会带来额外开销；其次，锁定超过需要的行会增加锁争用并减少并发行。
Innodb只有在访问行的时候才会对其加锁，而索引能够减少Innodb访问的行数，从而减少锁的数量。但这只有当Innodb在存储引擎层能够过滤掉所有不需要的行时才有效。如果索引无法过滤掉无效的行，那么在Innodb检索到数据并返回给服务器层以后，Mysql服务器才能应用WHERE子句。在早期的Mysql版本中，Innodb只有在事务提交后才能释放锁。