mysql索引ab和ba_MySQL索引总结

索引的分类

BTree索引(实际上有B-Tree,B+Tree(Innodb),T-Tree等类型(NDB)，各种实现细节有区别，但是统称BTree索引)

hash索引(Memory引擎默认)

空间数据索引

全文索引

其他索引类别

实际使用中常见的是BTree索引(Innodb和Myisam都是)，所以下面的主要总结的是BTree索引

索引策略

单独的列

就是在一个单独的列上建立索引

alter table table_name add index index_name (col)

这种索引理论上没有什么说的，但是实际使用中很容易犯错，下面就列举几个容易出现的错误使用情况：

select * from table_name where id + 1 = 4  (id字段有索引)

这种查询，索引无法发挥作用，索引不能处理索引列包含在表达式中的情况，还有一种很隐秘的写法：

select * from table_name where username= 123  (username字段有索引)

这个查询中 ，索引同样不能发挥作用，因为 username是 varchar类型，而后面的123是int型，所以这句话等价于：

select * from table_name where convert(username,signed)= 123

函数同样是一种表达式，也意味着下面这种写法同样不会用到索引：

select * from table_name where to_days(date)= 7872384  (即便date字段有索引)

前缀索引，前缀长度

如果一个字段的长度很长，比如varchar(1000)，首先mysql对索引有长度限制，在myisam表上索引长度最大 3072bytes，

utf8mb4 编码下一个字符占4bytes,也就是索引实际长度是768个字符，innodb索引最大长度191个字符

还有一点，索引是要占用磁盘空间的，如果索引太长，索引文件就会越大，不要小看索引文件的大小，搞不好有你数据库的一半大，

索引变大随之带来维护索引的成本上升，太长的索引后期可能会严重拖慢数据库的性能。

介于以上原因，如果数据太长，我们应该采用前缀索引，比如我们在varchar(30)的字段中只索引前几个字符，

添加前缀索引前还有一个问题需要考虑，比如varchar(30)的字段，前缀索引多长合适，这个没有标准，但是有个公式可以计算，

下面是我本地的一张表，数据结构如下：

现在我要给 RevisionGUID 字段添加前缀索引，通过下面的公式计算应该加多长：

SELECT COUNT(DISTINCT RevisionGUID) / COUNT(*) FROM PostHistory;

计算结果是 0.6454，这句话的意思其实就是计算出本表不重复数据的比列，这里就是 64.53%  (我把这个结果叫做基数，下面统一用基数)

SELECT COUNT(DISTINCT LEFT(RevisionGUID, len)) / COUNT(*) FROM PostHistory; (结果也是一个基数)

这条语句中，len是一个变量，你可以从1一直往后面试，基数会越来越大，当基数逼近上面的基数时，这时len就确定下来了，

注意：虽然len越大基数越大，但是前面说了，索引越长带来的成本越高，所以这个地方应该是要取个中间平衡点，

这个平衡点就是 当增加len基数变化不是太明显的时候，就没必要在去增加len带来那一丢丢基数了

以我本地测试结果为例：

len=5 (0.3455)， len=6 (0.6177)， len=7(0.6436)， len=8 (0.6453)

看以看出当len增加到8时，提升的基数已经很小了，所以len=6或者7比较好

创建前缀索引：(其实就跟单列索引一样，增加指定了索引长度)

alter table table_name add index index_name (col(length))

前缀索引和其他索引在是用上没有却别，需要注意的是：前缀索引用在排序和分组中不会发挥作用(下面会讲到其他索引在排序和分组中会发挥作用)

多列索引，索引顺序

多列索引就是在几个字段上建立一个索引，多列索引又叫复合索引，语法如下

alter table table_name add index index_name (col1(length),col2(length))

多列索引在实际中用的很多，如果你的查询语句中有多个查询条件，那你就应该审视以下是否该采用多列索引

注意：在多个列上建立单独的索引不叫多列索引

举一个常见的案例，网站要通过身高，体重，年龄来筛选会员，这就是一个典型的多列索引场景。

注意：关于上面这个案例，有一种错误的做法，在身高体重和年龄字段上分别建立独立索引，这种做法只能用到一个索引，另外两个索引只是累赘(增加索引维护成本)。

实际上，在单个(子查询在内部会被拆分成多个)查询语句中mysql最多只能用到一个索引(多列索引算一个，不是多个)，具体使用哪一个由mysql优化器选择。

特殊说明：在新版的mysql中，可以同时用到多个独立索引，但这属于mysql内部的hack方式，举个例子：

select * from user where first_name='123' and last_name='abc' (first_name和last_name上有独立的索引)

mysql内部会把这条语句 拆分为:

select * from user where first_name='123'

select * from user where last_name='abc'

mysql在内部将结果合并，这个过程会消耗更多的cpu和内存，但是explain不会统计这部分开销，往往会导致查询成本被低估

多列索引还有一个规则需要注意，在我之前的一篇文章已将讲过了，这里就不重复了

聚簇索引

聚簇索引这一块篇幅比较大，我单独整理了一篇文章：MySQL聚簇索引和非聚簇索引的对比

利用索引排序

索引可以加快order by 排序，举个例子：

select age,height,weight from user where age > 18 order by age (age上有索引)

select age,height,weight from user where age > 18 order by height(age上有索引)

上面两个查询语句中，都使用到了age索引，所以查询速度都一样，然后在看排序速度(不要小看了排序的成本)，第一个语句中排序用到了索引字段,

explain查看可以看到 type 为index，extra为 Using index

第二条语句中 height没有索引，在explain中可以看到extra 包含 filesort(文件排序)。

索引可以减少锁

在某些情况下索引可以减少锁， 比如 select for update，通过索引可以过滤掉很多不需要的行，mysql不会对这些行进行加锁。

加锁的开销是很大的

注： 如果查询语句是范围查询，即便索引使用正确，也不保证能够用到，MySQL内部的优化器做了很多工作，举个例子：

select * from user where age>30 ,(age 有索引)

如果在这张表中 age>30的数据占了大部分，那么这个时候MySQL会放弃索引而使用全表扫描