高性能Mysql之创建高性能的索引

索引基础

如何理解MySQL中索引是如何工作的呢，最简单的就是我们可以参考图书的目录，他们就相当于”索引“，我们可以根据”索引“找到相应的页码。
例如要运行下面的查询：

SELECT first_name FROM sakila.actor WHERE actor=5

如果在actor_id列上建立索引，MySQL将使用该索引找到actor_id为5的行，也就是说，MySQL先在索引上按值进行查找，然后返回所有包含该值的数据行。

索引可以包含一个或多个列。如果索引（组合索引）包含多个列，那么列的顺序也十分重要，因为MySQL只能高效的使用索引的最左前缀列。创建一个包含两个列的索引，和创建两个只包含一列的索引是大不相同的。

• 索引的类型
  索引有多种类型，可以为不同场景提供更好的性能。在MySQL中，索引实在存储引擎层而不是在服务器层实现的。所以，并没有统一的索引标准。不同存储引擎的索引的工作方式也并不一样，也不是所有的存储引擎都支持所有类型的索引。即使多个存储引擎支持同一种类型的索引，其底层的实现也可能不同。
  B-Tree索引
  当我们使用索引的时候，如果没有特别指明类型，那多半说的是B-Tree索引，它使用B-Tree的数据结构来存储数据。大多数的MySQL都支持这种类型（Archive引擎除外，它不支持任何索引，知道5.1才开始支持单个自增列的索引），不过，底层的存储引擎也可能使用不同的存储结构，例如，NDB集群存储引擎内部实际时使用了T-Tree结构存储这种索引；InnoDB则使用的B+Tree。
  B-Tree通常意味着所有的值都是按照顺序存储的，并且每一个叶子页到根的距离相同。B-Tree索引能够加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点开始进行搜索。根节点的槽中存储了指向子节点的指针，存储引擎根据这些指针向下层查找。通过比较节点页的值和要查找的值可以找到最合适的指针进入下层子节点，这些指针实际上定义了子节点页中值的上限和下限。最终存储引擎要么找到对应的值要么该记录不存在。叶子节点比较特别，他们的指针指向的是被索引的数据而不是其他的节点页。
  B-Tree索引适用于全键值、键值范围或键前缀查找。其中键前缀查找只适用于根据最左前缀的查找。前面所述的索引对如下类型的查询有效

  #这里是模拟的数据 建立一个组合索引 (name,sex ,age)
  CREATE TABLE person(
  	name varchar(50) not null,
  	sex tinyint(2) not null  COMMENT '1-男 2-女',
  	age int(3) not null,
  	birthday date not null,
  	job varchar(50) not null,
  	key(name,sex ,age)
  )
  #模拟几个人的数据
  INSERT INTO 
  items(name,sex ,age ,birthday ,job ) 
  VALUES
  ('张三',1,15,'2005-01-01','程序员'),
  ('张三',2,16,'2004-11-11','扫地的');
  

全值匹配
全值匹配指的是和索引中的所有列进行匹配。例如前面提到的索引可用于查找到姓名为张三、性别为男、年龄为15的人
匹配最左原则
前面提到的索引可用于查找所有名字为张三的人，即只使用索引的第一列。
组合索引(name,sex ,age)对于下面的情况都会用到这个索引：
name= “张三”;
name= “张三” and sex = 1;
name= “张三” and sex = 1 and age= 15;
sex = 1 and name= “张三” and age= 15;
最后一条会MySQL会自动优化成第三条的样子。
对于下面的情况就不会用到索引：
sex = 1;
sex = 1 and age= 15;
匹配列前缀
匹配一列值的开头部分。例如前面提到的索引可用于查找以张开头的名字的人（like关键字："张%"走索引，”%张%“不走索引）
匹配范围值
例如前面提到的索引可以查找姓名张三到李四之间的人。
精确匹配某一列并范围匹配另一列
可以查询名字是张三，性别为2（女），年龄在15-20岁之间的人。、
只访问索引的查询
就是说这个查询只需要查询索引列的值，不再需要去回表查询，访问数据行。这个就是下面提到的覆盖索引

因为索引树中的节点是有序的，所以除了按值查找之外，索引还可以用于查询中的ORDER BY操作。一般来说，如果B-Tree可以按照某种方式查找到值，那么也可以按照这种方式用于排序。所以，如果ORDER BY子句满足前面列出的集中查询类型，则这个索引也可以满足对排序的要求。

下面是关于B-Tree索引（组合索引）的限制
1、如果不是按照索引的最左列开始查找，则无法使用索引。例如上面的例子索引无法用于查找sex为1的人，无法用于查询age在15岁的人，因为这两列都不是在最左数据列。
2、不能跳过索引中的列，也就说无法使用索引查找name为张三age为15的人。如果不加上sex，则MySQL只能使用索引的第一列。
3、如果查询有某个列的范围查询，则其右边的所有列都无法使用索引优化查询。例如查询WHERE name LIKE ”张%“ AND sex=1 AND age=15，这个查询只能使用name索引，因为这里的LIKE 是一个范围条件。

• 哈希索引
  哈希索引基于哈希表实现，只有精确匹配索引的列查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样，哈希索引将所有的哈希码存储在索引中，同时哈希表中保存每个数据行的指针。
  因为索引自身只需要存储对应的哈希值，所以索引的结构很紧凑，这也让哈希索引查找的速度非常快。然而，哈希索引也有它的限制：
  1、哈希索引只包含哈希值和行指针，而不是存储字段，所以不能使用索引中的值来避免读取行。不过，访问内存中的行速度很快，所以大部份情况下这一点对性能影响不是很明显。
  2、哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序。
  3、哈希索引也不支持部分索引列匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的。
  4、哈希索引只支持等值比较查询，包括=、IN()、<=>（注意<>和<=>是不同的操作，<>是不等于，<=>是安全等于），不支持任何的范围查询。
  5、访问哈希索引的数据非常快，除非有很多哈希冲突。当出现哈希冲突的时候，存储引擎必须遍历链表中的所有行指针，逐行进行比较，直到找到所有符合条件的行。
  6、如果哈希冲突很多的话，一些索引维护操作的代价也会很高。如果在某个哈希冲突很多的列上建立哈希索引，那么当从表中删除一行是，存储的引擎需要遍历对应哈希值的链表中的每一行，找到并删除对应行的引用，冲突越多，代价越大。

• 其他索引
  空间数据索引：空间索引会从所有的维度来索引数据，查询时可以有效地使用任意维度来组合查询。必须使用MySQL的GIS相关函数来维护数据，MySQL的GIS支持不是很完善，所以这个索引很少人使用。
  全文索引：一种特殊的索引，他查找的是文本中的关键词，而不是直接比较索引中的值。

索引的优点

索引可以让服务器快速的定位到表的指定位置。但是这并不是索引唯一的作用，到目前为止可以看到，根据创建的索引的数据结构不同，索引也有一些其他的附加作用。

譬如B-Tree索引，按照顺序存储数据，所以MySQL可以用来ORDER BY和GROUP BY。因为索引中存储了实际的列值，所以某些查询只是用索引就能完成全部的查询。根据这个特性，总结下来有以下几个优点：
1.索引大大减少了服务器需要扫描的数据量
2.所以可以帮助服务器避免排序和临时表
3.索引可以将随机I/O改变成顺序I/O

高性能的索引策略

正确的创建和使用索引是实现高性能查询的基础。

高效得选择和使用索引有很多种方式，其中有些是针对特殊案例的优化方法，有些则是针对特定行为的优化。使用哪个索引，以及如何评估选择不同索引的性能影响的技巧，则需要持续不断的学习，下面有几个小细节我们值得注意。

• 独立的列
  我们通常会看到一些查询不当地使用索引，或者使得MySQL无法使用已有的索引。如果查询中的列不是独立的，则MySQL就不会使用索引。”独立的列“是指索引列不能是表达式的一部分，也不能是函数的参数
  。
  例如下面的查询将无法使用索引

  #mysql无法自动解析这个方程式，这个属于用户的行为
  #我们要养成简化WHERE条件的习惯，始终将索引列单独放在比较符号的一边
  SELECT user_id FROM user WHERE user_id + 1 =5;
  #列作为函数的一个参数，MySQL无法解析
  SELECT * FROM user WHERE DATE_FORMAT(NOW(),'%Y-%m-%d') - 
  DATE_FORMAT(create_time,'%Y-%m-%d') <= 10
  

• 前缀索引和索引选择性
  有时候需要索引很长的字符列，这会让字符变得大且慢。这时候我们该怎么做呢？

  通常可以索引开始的部分字符，这样可以大大节约索引空间，从而提高索引效率。但这样也会降低索引的选择性。索引的选择性是指，不重复的索引值（cardinality）和数据表的总数(#T)比值，范围从1/#T到1之间。索引的选择性越高则查询的效率越高，因为选择性高的索引可以让MySQL在查找时过滤掉更多的行。唯一索引的选择性时1，这是最好的索引选择性，性能也是最好的。

  一般情况下某个列前缀的选择性也是足够高的，足以满足查询的性能，对于BLOB、TEXT或者很长的VARCHAR类型的列，必须使用前缀索引，MySQL不允许索引完这些列的完整长度。

• 多列索引（组合索引）
  在多个列上建立独立的单列索引大部分情况下并不能提高MySQL的查询性能。MySQL5.0和更新版本引入了一种叫”索引合并“的策略。一定程度上可以使用单个索引来定位指定的行。
  索引合并策略有时候是一种优化的结果，但实际上更多时候说明了表上的索引建的很糟糕：
  1、当出现服务器对多个索引做相交操作时，通常意味着需要一个包含所有相关列的多列索引，而不是多个单列索引。
  2、当服务器对多个索引做联合操作时，通常需要耗费大量CPU和内存资源在算法的缓存、排序和合并操作上。特别是当其中有些索引的选择性不高，需要合并扫描返回大量数据的时候。
  3、更重要的是，优化器不会吧这些计算到”查询成本“中，优化器只关心页面读取，这会使得查询的成本被”低估“，导致该执行计划还不如走全表扫描。这样做不仅会消耗更多的CPU和内存资源，还可能影响查询的并发性。
  所以综合上述情况，我们可以适当的建立多列索引（组合索引）。

• 选择合适的索引列顺序
  这个问题只会出现在B-Tree索引中，在一个多列B-Tree索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列，等等。所以，索引可以按照升序或者降序进行扫描，以满足精确符合顺序的ORDER BY、GROUP BY和DISTINCT等字句的查询。
  所以多列索引的列顺序至关重要。列顺序也决定了一个索引是否能成为一个真正的”三星索引“（索引将相关记录放到一起叫一星；如果索引中的数据顺序和查找中的排列顺序一致则获得二星；如果索引中的列包含了查询中所需要的全部列则获得三星）。
  对于如何选择索引的列顺序有一个经验法则：将选择性最高的列放在索引最前列。当不需要考虑排序和分组时。将选择性最高的列放在前面通常是很好的。这时候的索引只是作用于WHERE条件的查询。这种情况下，这样设计的索引确实能够最快的过滤出需要的行。然而，性能不只是依赖于所有索引的选择性，也和查询体条件的具体值有关，也就是值的分布有关。这和之前介绍的选择浅醉长度需要考虑的地方一样，可能需要根据那些运行频率最高的查询来调整索引列的顺序，让这种情况下索引的选择性最高。

• 聚簇索引
  聚簇索引并不是一种单独的索引，而是一种数据存储方式。具体依赖于其他实现方式，但是InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。
  当表中有聚簇索引时，他的数据行其实存放在索引的叶子页中，把数据行和相邻的键值紧凑的存储在一起。因为无法把两个数据行存在不同地方，所以一个表只能有一个聚簇索引。
  聚簇索引的优点：
  1、可以把相关数据保存在一起。例如查询一个人的订单，可以根据用户的ID来聚集数据，这样只需要从磁盘独取少量数据页就能获取用户的全部订单。
  2、数据访问更快。聚簇索引把索引和数据保存在同一个B-Tree中，所以从聚簇索引中获取数据通常比在非聚簇索引中查找要快。
  3、使用覆盖索引扫描的查询可以直接使用页节点的主键值。
  聚簇索引的缺点
  1、聚簇索引最大限度提高了I/O密集型应用的性能，但是如果数据全部都放在内存中，则访问的顺序就没那么重要了。
  2、插入速度严重依赖插入顺序。按照主键的顺序插入是加载数据到InnoDB表中最快的方式。但如果不是按照主键顺序加载数据，那么加载完成后最好使用OPTIMIZE TABLE命令重新组织一下表。
  3、更新聚簇索引的代价非常高，因为会强制InnoDB将每个更新好的行移动到新的位置。
  4、基于聚簇索引的表，在删除新增行的时候需要移动行，这可能会产生一次页分裂操作。页分裂会导致表占用更多的磁盘空间。
  5、聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候。

• 覆盖索引
  优秀的索引应该考虑到整个查询，而不单单是WHERE条件部分。索引确实是一种查询数据的高效方式，但是MySQL也可以使用索引来直接获取列的数据，这样就不再需要读取数据行。如果叶子节点中已经包含要查询的数据，那么还有什么必要再次回表查询呢？如果一个索引包含所有查询的字段和值，我们就称之为”覆盖索引“。
  覆盖索引是非常有用的工具，能够极大地提高性能。考虑一下如果查询只是需要扫描索引而无须回表，会带来多少好处。
  1、索引条目通常远小于数据行大小，所以只需要读取所有，那么MySQL就会极大地减少数据访问量。这对缓存的负载非常重要，因为这种情况下响应时间大部分花费在数据拷贝上。索引比数据更小，更容易全部放在内存中。
  2、索引是按照列值顺序存储的，所以对I/O密集型的范围查询会比随机从磁盘读取每一行数据的I/O要少得多。
  3、一些存储引擎的内存只缓存索引，数据则需要依赖于操作系统来缓存，因此访问数据需要一次系统调用。这可能会导致严重的性能问题，尤其是那些系统调占了数据访问中的最大开销的场景。

   不是所有类型的索引都可以成为覆盖索引。覆盖索引需要存储索引列的值，而哈希索引、空间索引和全文索引都不存储索引列的值，所以MySQL只能使用B-Tree索引做覆盖索引。（不是所有引擎都支持覆盖索引）
  

  #我们EXPLAIN下面的语句可以发现，（user_id,user_name）是user	表的
  #一个组合索引，如果我们只需要访问这两列，就可以使用这个索引做	覆盖索引。
  SELECT user_id,user_name 
  FROM user 
  WHERE user_id=1 AND user_name = 'lisa' 
  
  #如果索引覆盖了WHERE条件中的字段，但不是整个查询设计的字	段，那么条件就为false,MySQL总是会回表获取数据行。
  #可以看看以下例子,这里的索引无法覆盖该查询，有两个原因
  #1、没有任何索引能覆盖这个查询，因为查询从表中选择了所有的	列，而没有任何索引覆盖了所有的列。
  #2.	MySQL不能在索引中执行任何LIKE操作。MySQL能在索引中做	最左前缀匹配的LIKE操作，因为改操作可以转换为简单的比较操作，但	是如果是以通配符开头的LIKE查询，存储引擎无法做比较匹配。
  SELECT * 
  FROM user 
  WHERE user_id=1 AND user_name LIKE '%lisa%' 
  #当然我们有办法解决上面的两个问题，需要重写查询并且巧妙的设计	索引
  #这种方法叫做延迟关联，因为延迟了对列的访问。在查询的第一阶段	MySQL可以用覆盖索引，这样优化的效果取决于WHERE条件返回的行	数，如果WHERE条件将大部分不符合查询结果的数据都过滤掉了，减少	了数据的传输这对整个查询都有很大的性能提升
  SELECT * 
  FROM user JOIN (SELECT user_id FROM user WHERE user_id=1 	AND user_name LIKE '%lisa%' ) AS t1 
  ON user.user_id=t1.user_id 
  

• 压缩（前缀压缩）索引
  MyISAM使用前缀压缩来减少索引的大小，从而让更多的索引可以放入内存中，这在某些情况下能极大地提高性能。默认只压缩字符串，但通过参数设置也可以对整数做压缩。（一般不怎么用，我就不多做介绍了）

• 冗余和重复索引
  MySQL允许在相同列上创建多个索引，无论是有意或者无意，MySQL需要单独维护重复索引，并且优化器在优化的时候也需要逐个的进行考虑，这会影响性能。我们应该避免在相同列创建重复索引，发现之后也应该立即移除。

  有时候会在不经意间创建了重复的索引，例如下面这一段代码：

  #MySQL的唯一限制和主键限制都是通过索引实现的，因此，上面的写法实际上在相同的列上创建了三个重复的索引。通常没有里有这样做，除非是在同一列上创建不同类型的索引来满足不同的查询需求。
  CREATE TABLE user(
  	user_id int not null primary key,
  	user_name varchar(50) not null,
  	...
  	unique(user_id),
  	index(user_id)
  )
  

  冗余索引和重复索引有一些不同。如果创建了组合索引（A，B），再创建（A）索引就是冗余索引，但是如果创建的是（B，A），则不是冗余索引，（B）也不是冗余索引，如果不理解可以看看B-Tree索引的规则。
  大多数情况下我们都不需要冗余索引，应该尽量扩展已有的索引而不是创建新的索引。但是也不是绝对的，有时候我们出于性能方面的要求，需要冗余索引，因为扩展已有的索引会导致其变得太大，从而影响其他使用该索引的查询性能。

• 未使用的索引
  除了重复和冗余索引，可能还会有一些服务器永远用不到的索引。这样的索引完全就是累赘，建议考虑删除。

• 索引和锁
  索引可以让查询锁定更少的行。如果你的查询从不访问那些不需要的行，那么就会锁定更少的行，从两个方面来说这对性能都有好处。其一，虽然InnoDB的行锁效率很高，内存占用也很少，但是锁定行的时候仍然会带来额外的开销；其二，锁定超过的行会增加锁争用并减少并发性。
  InnoDB只有在访问行的时候才会对其加锁他，而索引能够减少InnoDB访问的行数，从而减少锁的数量。但是这只有当InnoDB在存储引擎层过滤掉所有不需要的行时才有效。

  我们来看一个例子：

  #这条查询仅仅会返回2~4之间的行，但是实际上获取了1-4之间的行的排它锁（独占锁）。InnoDB会锁住第一行，这是因为MySQL为该查询选择的执行计划是索引范围搜索。
  SET AUTOCOMMIT=0;
  BEGIN;
  SELECT user_id FROM user WHERE user_id <> 1 and user_id < 5 FOR UPDATE;
  #通过下面的查询我们可以验证第一行确实已经被锁定了，尽管我们返回的数据里面没有第一行。保持上面的查询打开，然后开启第二个连接并且执行下面的查询,这个查询将会挂起，直到第一个事务释放掉第一行的独占锁。
  SET AUTOCOMMIT=0;
  BEGIN;
  SELECT user_id FROM user WHERE user_id = 1  FOR UPDATE;
  #就像这个例子现实的，即使使用了索引，InnoDB也可能锁住一些不需要的数据。如果不能使用索引查找和锁定行的话问题可能会更加的糟糕，MySQL会做全表扫描并且锁住所有的行，而不管是不是需要
  

  总结

  在MySQL中大多数情况下都会使用B-Tree索引。其他类型索引大多只适用于特殊的目的。如果在合适的场景中使用索引，将大大提高查询的响应时间。
  在选择索引和编写利用这些索引查询时，有三个原则始终需要记住。
  1、单行访问是很慢的。如果服务器从存储中读取一个数据块只是为了获取其中的一行，那么就浪费了很多工作。最好读取块中能包含尽可能多所需要的行。使用索引可以创建位置引用以提升效率。
  2、按顺序访问范围数据是很快的，这有两个原因。第一，顺序I/O不需要太多次自磁盘寻道，所以比随机I/O要快很多。第二，如果服务器能够按需求顺序读取数据，那么就不再需要额外的排序操作，并且GROUP BY查询也无需再做排序和将行按组进行聚合计算了。
  3、索引覆盖查询是很快的。如果一个索引包含了查询所需要的全部列，那么存储引擎就不需要再回表去查找行了。这避免了大量的单行访问。

总的来说，编写查询语句时应该尽可能地选择合适的索引来避免单行查找，尽可能地使用数据原生顺序从而避免额外的排序操作，并尽可能使用索引覆盖查询。