就像是一本书的目录一样，Mysql提供了索引来提高我们对于数据的查询速度，而如何用好它，就需要了解它的一些规则和原理。
测试数据
索引的类型
索引有很多种类型，可以为不同的场景提供更好的性能，索引是在存储引擎实现的而不是服务层，所以即使是同一种索引类型在不同的引擎中的实现可能也会有差异。
常见的索引类别:
- B-Tree 索引，最常用的索引类型，现在默认都是用这个
- 哈希索引，通过对行数据进行哈希处理的一种索引类型
- 空间数据索引，用于地理位置的索引
- 全文索引，用于关键字搜索的索引
索引的优点
- 索引大大减少了Mysql服务器层需要扫描的的数据量
- 索引可以帮助服务器避免排序和使用临时表
- 索引可以将随机I/O变为顺序I/O
高性能的索引策略
想要正确的创建和使用索引，我们需要先知道索引的一些高性能策略
独立的列
"独立的列"是指索引列不能是表达式的一部分或者函数的参数。
下图为测试结果:
独立的列
通过上图，我们知道id为索引列，当它是表达式的一部分时，没有使用到索引。但是当它单独在比较符号的一侧是，使用了索引。性能上也有明显的差距。
前缀索引和索引选择性
有时候我们需要索引很长的字符列，这会让索引变得大而且慢，除了将字符列的数据进行哈希之外，我们还可以使用前缀索引来进行优化。
前缀索引是指取整个字符列的前面部分数据来作为索引，这样可以减低索引的空间大小，从而提高的索引效率。但是取字符列的前面几位是一个问题，如果位数太少，那么就会降低索引的选择性。索引的选择性是指:不重复的索引值（也称为基数,cardinality）和数据表的记录总数的比值，选择性越高则查询效率越高。当基数/记录总数=1时，该索引的选择性最好，例如主键。
在不同的场景下，因为存的不同的数据，所以需要选择的位数也不同，所以需要我们自己通过测试来选择使用的位数应该是多少，可以通过下面的sql语句来进行计算。
select count(distinct left(字符列,位数))/count(*) from 表名;
也可以通过下面的语句，在不同位数之间进行比较，当发现随着位数的提升，但是选择性没有明显增加时，这个位数可能就比较适合改字符列了。
select
count(distinct left(字符列,1))/count(*),
count(distinct left(字符列,2))/count(*),
count(distinct left(字符列,3))/count(*),
count(distinct left(字符列,4))/count(*)
from 表名;
下面我将根据《高性能Mysql》书中例子进行演示
完整字符列的选择性:
完整字符列的选择性
不同位数字符列的选择性:
不同位数字符列的选择性
通过上面两张图的对比我们可以知道，当位数到6的时候，选择性的提升幅度已经很微小了，所以这时候我们就可以在city字段上创建长度为6的前缀索引:
alter table city_demo add key(city(6));
但是我们不能通过前缀索引进行order by，下图并没有使用到索引
前缀索引排序
避免为各个列建立单独索引
在Mysql的最新版本中，Mysql能够同时使用单独列索引，并将结果进行算法合并，也将这个称为索引合并策略。这种算法有三种变种: OR条件的联合(union)，AND条件的相交(intersection)，组合前面两种情况的联合及相交)。
union
索引合并策略通常会消耗大量的cpu和内存资源，而这些消耗比较难通过mysql自身提供的工具进行监控，只能通过针对系统或者进程的监控程序才能知道。
选择合适的索引列顺序
不同类型的索引，它的存储结构是不一样的，限制也不一样，所以也决定了我们在定义索引时，需要选择合适的索引列顺序，才能真正的将索引的功能发挥出来，下面主要介绍B-Tree。
B-Tree限制
- 如果不是按照索引的最左列开始查询，则无法使用索引
- 不能跳过索引的列
- 如果查询中有某个列的范围查询，则其右边所有列都无法使用索引优化查找。
- 只有当索引的列顺序和order by子句的顺序完全一致，并且所有列的排序方向（倒序或正序）都一样时，才能使用索引来对结果进行排序。
通过这些B-Tree的限制应该能明白，定义索引列的顺序决定了你将如何使用索引。而这些规则试用于所有涉及索引的操作，例如基本查找、where子句、order by子句等
表结构和索引定义为:
索引定义
下面我们将主要使用rental_date这个索引进行举例:
例子1:
例子1
索引rental_date包含了rental_date、inventory_id、customer_id字段，在第一条语句中，按我们正常的理解来说，应该使用rental_date这个索引才对，但是并没有，因为该索引的最左列为rental_date，但是在where语句中，并没有该字段，所以不会使用rental_date这个索引。而第二条语句就符合索引的最左列的规则。
例子2:
例子2
这里将and换成or，结果并没有使用rental_date索引了，而是使用了上面提到的索引合并策略。我们自己细想一下，这样是合理的，因为B-Tree是一棵树，他只能只能先匹配最左字段，然后再匹配下一个字段。但是or要求的是rental_date和inventory_id一起找，这样单个B-Tree索引是无法完成这样的工作的。只能使用索引合并策略了，所以在用or的时候，可能需要注意下索引合并策略可能引起的一些问题。
例子3:
例子3
当语句的查询结果和搜索条件以及排序条件与索引项匹配，那么可以直接通过索引进行排序
例子4:
例子4
在这个例子使用的索引是rental_date，虽然order by只用到inventory_id，但是因为前面where子句中rental_date是一个常量条件，所以也可以使用相应的索引进行排序。
例子5:
例子5
这个例子中将inventory_id换成customer_id，还是使用了rental_date索引，但是并没有使用它进行排序，因为这条语句违反不能跳过索引的列这个规则。
例子6:
例子6
这个例子中两种排序字段的排序方向不一致，所以无法使用索引进行排序。
例子7:
例子7
在这个例子中where子句使用了范围查询，所以导致了无法使用索引进行排序
冗余和重复索引
Mysql允许在相同列上创建多个索引，通过上面的那些例子中就能发现一些字段存在于不同的索引中。如果一个索引是另一个索引的最左前缀，那么这个索引就冗余索引，这样的索引是没有什么意义的。过多的索引会导致数据库在修改表数据时变慢，并且增加资源的占用。通过上面的例子也可以发现，索引太多也会提升使用索引的复杂度。
索引和锁
下面有两条语句
begin;
select actor_id from actor where actor_id < 5 and actor_id != 1 for update;
begin;
select actor_id from actor where actor_id=1 for update;
按照预期来说，上面那条语句应该只会锁2-4行的数据，那么下面那条语句应该不会阻塞，但是在我当前的版本，还是出现了阻塞。原因在actor_id < 5是一个索引范围查询， 导致InnoDB只会锁定actor_id<5的行，而不会执行后面的条件，所以导致actor_id != 1是在服务层通过where进行处理的。
例子8
通过上面的实例也能知道，这条语句先用索引过滤出<5的数据，然后在服务层用where进行二次过滤，在索引层会将对应的行锁住，导致第二条语句在执行被阻塞。
结尾
大部分人都知道当Mysql查询性能慢时，可以通过加索引的方式来提高性能。但是如何正确使用索引却并没有想象的那么简单，需要考虑未来会如何使用，然后定义相应的索引，也需要对查询语句进行修改来保证正确能使用相应的索引，也需要随着业务的发展，数据的变化，对索引进行维护来保证索引的高性能。
网友评论