MYSQL索引
1、索引作用
2、索引分类
3、索引原理
4、B+Tree数据结构
5、索引原则
6、索引缺点
1、索引作用
索引存储在存储文件中，存储文件是在磁盘中。
（1）索引对单个表的影响。索引被用来快速找出在一个列上用一特定值的行。没有索引，MySQL不得不首先以第一条记录开始并然后读完整个表直到它找出相关的行。表越大，花费时间越多。如果表对于查询的列有一个索引，MySQL能快速到达一个位置去搜寻到数据文件的中间，没有必要考虑所有数据。如果一个表有1000行，这比顺序读取至少快100倍。
（2）索引对多个表的影响。索引对多个表的影响更大。 假如有三个未索引的表 t1、t2、t3，分别只包含列 c1、c2、c3，每个表分别由含有数值 1 到 1000 的 1000 行组成。查找对应值相等的表行组合的查询如下所示：
此查询的结果应该为 1000 行，每个组合包含 3 个相等的值。如果我们在无索引的情况下处理此查询，则不可能知道哪些行包含那些值。因此，必须寻找出所有组合以便得出与 WHERE 子句相配的那些组合。可能的组合数目为 1000×1000×1000（十亿），比匹配数目多一百万倍。很多工作都浪费了，并且这个查询将会非常慢，即使在如像 MySQL 这样快的数据库中执行也会很慢。而这还是每个表中只有 1000 行的情形。如果每个表中有一百万行时，将会怎样？很显然，这样将会产生性能极为低下的结果。如果对每个表进行索引，就能极大地加速查询进程，因为利用索引的查询处理如下：
1) 如下从表 t1 中选择第一行，查看此行所包含的值。
2) 使用表 t2 上的索引，直接跳到 t2 中与来自 t1 的值匹配的行。类似，利用表 t3 上的索引，直接跳到 t3 中与来自 t1 的值匹配的行。
3) 进到表 t1 的下一行并重复前面的过程直到 t1 中所有的行已经查过。
在此情形下，我们仍然对表 t1 执行了一个完全扫描，但能够在表 t2 和 t3 上进行索引查找直接取出这些表中的行。从道理上说，这时的查询比未用索引时要快一百万倍。
2、索引分类
从数据结构上划分：B+tree索引，B tree索引、哈希索引、全文索引；
从存储角度划分：聚簇索引、二级索引；
从逻辑角度划分：主键索引、唯一索引、普通索引、联合索引；
索引类型主要有B+TREE索引、哈希索引、全文索引等等。如果不指明类型，多半指的是B+TREE索引。索引是在存储引擎层实现的，像InnoDB主要使用的是B+Tree索引。
首先说一下哈希索引。
哈希索引， 顾名思义，就是一种基于哈希表实现的索引。目前只有Memory引擎支持哈希索引。当然，现在的InnoDB也有自适应哈希索引，他是在B-TREE索引之上创建的一个哈希索引。
哈希索引最大的有点就是查询速度快，O(1)查找时间复杂度。但是我们在实际应用中还不是怎么用哈希索引。这还是因为其自身的限制导致的。它本身有很多的缺点，导致其并不十分适合做索引：
- 他是基于哈希值和行指针的，因此他没法做范围查询，只支持等值比较查询。
- 哈希索引数据也不是按照顺序存储的，因此也没法进行排序；
- 他也不支持部分索引列查找，因为他是拿着全部索引列简历的索引，没办法查找复合索引的部分列；
- 最大的问题是容易出现哈希冲突。如果哈希冲突比较多的话，增删改查都是个麻烦事。因为他需要循环遍历链表。
上文提到了InnoDB会创建一个自适应哈希索引，这是引擎内部自己实现的，当某些数据被访问得非常频繁，InnoDB会在B-TREE索引基础上创建哈希索引，这样就可以提高查询效率。
当Mysql发现某些sql总是能命中相同的页面，她就会将在内存缓冲区建立自适应哈希，以便加快速度。
其中key是索引键值，value是对应的页。
全文索引
使用全文索引的字段类型只能是char,varchar和text类型。自5.6.4之后，InnoDB也支持全文索引了。
一个列上既可以建立B-TRE索引，又可以建立全文索引完全不冲突。全文索引查询是使用MATCH AGAINST查询。
全文索引和普通B-TREE索引是不同的概念。我们可能在实际应用中，使用like进行查询，但当左侧也做模糊匹配的时候，如果不是查询索引字段，是用不到索引的，类似like %***%。全文索引匹配的还是关键词的相关度。例子：
mysql> explain select * from shop_good where name like "%小米%"\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: shop_good
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1404
filtered: 11.11
Extra: Using where
1 row in set, 1 warning (0.00 sec)
mysql> explain select name from shop_good where name like "%小米%"\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: shop_good
partitions: NULL
type: index
possible_keys: NULL
key: goodindexname
key_len: 765
ref: NULL
rows: 1404
filtered: 11.11
Extra: Using where; Using index
1 row in set, 1 warning (0.00 sec)
如果只查询name这个索引值，它可以只遍历索引数。如果不是，就要全表扫描。
说起全文索引，想起了ES，都是做搜索引擎的，需要对比一下。之前在学习ES的时候，有过简单的记录： Elasticsearch学习初篇
Mysql也会将全文索引列根据基准长度将文本进行分词，比如：
- innodb_ft_min_token_size
- innodb_ft_max_token_size
innodb_ft_min_token_size就指定了最小分词长度是3.
看到这就会明白，全文索引针对的是搜索词的相关度。相关度越高的就会排到前面，这点和ES的思想还是比较类似的。
Mysql的全文索引有两种模式，一个是自然语言模式和一个布尔模式。
自然语言就是正常的普通文本的匹配；布尔模式是在自然语言的基础上做了一些限制条件。
比如：
+ haibo 必须包含 haibo;
-haibo 必须不包含haibo;
haibo haibo开头的排序rank值更高;
~haibo haibo开头的排序rank值更低;
*haibo haibo开头的rank值更高。
至于选择哪个，只需要在AGINST中指出Mode即可，默认是使用自然语言模式的。
select * from c_group where MATCH(groupname) AGAINST("hehe");
select * from c_group where MATCH(groupname) AGAINST('+haibo ' in BOOLEAN MODE);
到此为止，如果学过ES的都会有疑问，Mysql全文索引是怎么处理中文的，因为默认也是只处理英文的。
ES中需要引入单独的分词器。
Mysql5.7开始，引入了一个组件叫做ngram，它不仅可以处理中文，还可以处理其他语言，可以自己配置分词颗粒度。不得不说，Mysql真鸡儿的很强大。有了这个组件就可以处理中文了。
select * from c_group where MATCH(groupname) AGAINST('海波 -dw' in BOOLEAN MODE);
当我们在一个查询语句中既使用了全文索引，又使用了B-TREE索引时。如果这个索引不是PRIMARY KEY，那么就不会使用该索引，而是只使用全文索引，然后在匹配结果中搜索是否有相关的数据。
mysql> explain select gid,name from shop_good where MATCH(name) AGAINST('小米 电视' in boolean mode) and gid=101314\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: shop_good
partitions: NULL
type: const
possible_keys: PRIMARY,goodinformation
key: PRIMARY
key_len: 8
ref: const
rows: 1
filtered: 11.11
Extra: Using where
1 row in set, 1 warning (0.16 sec)
mysql> explain select gid,name from shop_good where MATCH(name) AGAINST('小米 电视' in boolean mode) and summary=101314\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: shop_good
partitions: NULL
type: fulltext
possible_keys: goodinformation,goodsummary
key: goodinformation
key_len: 0
ref: const
rows: 1
filtered: 10.00
Extra: Using where; Ft_hints: no_ranking
1 row in set, 2 warnings (0.01 sec)
全文索引的原理其实也是使用了倒排索引，它在数据库中会建立相应的临时表。临时表中主要存储的是单次和单词所在行位置的映射关系。InnoDB模式使用full inverted index,临时表存储的是单词，单次所在文档，单次所在文档中的位置。
为了提高检索效率，Mysql还有一个全文索引缓存，它是一个红黑树的结构，当有新数据插入时，首先是将索引数据更新到缓存中，再写入临时表中。
我草，我看到这儿感觉，Lucence内置的倒排索引就是Mysql全文索引的概念啊。
目前是ES和Solar用的比较多。但是我觉得对于中小公司，业务规模不是特别大，且不需要太复杂的查询的情况下，用Mysql的全文索引进行检索就够了。毕竟ES的维护以及数据同步需要一定的成本。
当然，如果数据量特别大时，如果使用Mysql索引时，需要对数据进行分区，然后进行并发检索，这也是一个非常复杂的工作。因此一般也都必须使用专业的搜索引擎去做，比如Solar，Lucence等等。
BTREE、B+TREE索引
索引有如下的几种情况：
l INDEX索引：通常意义的索引，某些情况下KEY是它的一个同义词。索引的列可以包括重复的值。
l UNIQUE索引：唯一索引，保证了列不包含重复的值，对于多列唯一索引，它保证值的组合不重复。 PRIMARY KEY索引：也UNIQUE索引非常类似。事实上，PRIMARY KEY索引仅是一个具有PRIMARY名称
UNIQUE索引。这表示一个表只能包含一个PRIMARY KEY。
创建索引：
可以用alter table add 添加索引，名字可选
也可以用create index indexname on tablename(columnlist);
columnlist是几个列的集合，即多个列构成一个索引。
ALTER TABLE tbl_name ADD INDEX index_name (column_list)
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list)
ALTER TABLE tbl_name ADD PRIMARY KEY index_name (column_list)
名字是必须写的。CREATE INDEX可对表增加普通索引或UNIQUE索引，不能用CREATE INDEX语句创建PRIMARY KEY索引。
删除索引： drop index indexname on tablename
mysql> ALTER TABLE student DROP PRIMARY KEY, DROP INDEX index_name ON tbl_name
或者：
alter table tablename drop index ........
3、索引原理
Mysql读数据是以磁盘快为单位，一个磁盘块内的数据会一次性地读出来。而InnoDB引擎的最小磁盘管理单元是页，一个页可能由多个磁盘块组成。在B-TREE中，一个节点就相当于一个页。
考虑如下情况，假设数据库中一个表有10^6条记录，DBMS的页面大小为4K，并存储100条记录。如果没有索引，查询将对整个表进行扫描，最坏的情况下，如果所有数据页都不在内存，需要读取10^4个页面，如果这10^4个页面在磁盘上随机分布，需要进行10^4次I/O，假设磁盘每次I/O时间为10ms(忽略数据传输时间)，则总共需要100s(但实际上要好很多很多)。如果对之建立B-Tree索引，则只需要进行log100(10^6)=3次页面读取，最坏情况下耗时30ms。这就是索引带来的效果，很多时候，当你的应用程序进行SQL查询速度很慢时，应该想想是否可以建索引。
也就是说，索引减少了I/O的读取次数，也可以将随即IO变成顺序IO，加快数据的查询。
B-TREE在Mysql中是一种比较重要的数据结构，InnoDB在其基础上使用了B+TREE结构。
下图是一个B-TREE索引
如上图，是一颗B-TREE，所有叶子节点到根节点的高度都是一致的，数据是顺序存储的。m阶的TREE每个节点最多有m个子节点。
浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示，非主键的数据）和指针（黄色所示）以及主键值(红色），如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。
模拟查找文件29的过程：
(1) 根据根结点指针找到文件目录的根磁盘块1，将其中的信息导入内存。【磁盘IO操作1次】
(2) 此时内存中有两个文件名17，35和三个存储其他磁盘页面地址的数据。根据算法我们发现17<29<35，因此我们找到指针p2。
(3) 根据p2指针，我们定位到磁盘块3，并将其中的信息导入内存。【磁盘IO操作2次】
(4) 此时内存中有两个文件名26，30和三个存储其他磁盘页面地址的数据。根据算法我们发现26<29<30，因此我们找到指针p2。
(5) 根据p2指针，我们定位到磁盘块8，并将其中的信息导入内存。【磁盘IO操作3次】
(6) 此时内存中有两个文件名28，29。根据算法我们查找到文件29，并定位了该文件内存的磁盘地址。
分析上面的过程，发现需要3次磁盘IO操作和3次内存查找操作。关于内存中的文件名查找，由于是一个有序表结构，可以利用折半查找提高效率。至于3次磁盘IO操作时影响整个B-tree查找效率的决定因素。
实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。
但是如果仔细观察B-TREE可以发现，每个节点既存储了主键值，也存储了value，然而每一页的大小空间是有限的，默认是16key，如果数据较大，势必会导致整个TREE的深度增大，这样就会一定程度增加磁盘IO，降低查询效率。
基于此，出现了B+TREE，它是对B-TREE的一种优化。相对于B-TREE，它主要具有以下特点：
- 每个非叶子节点只存储主键值；
- 数据值只存储在叶子节点上；
- 每一个叶子节点都包含指向下一个叶子节点的指针，类似一个链表。
B+TREE索引根据叶子节点是只存储主键还是所有数据值还分为聚簇索引和辅助索引。聚簇索引就是存储所有的值；辅助索引存储的相应聚集索引键，然后再根据聚集索引键获得主键在聚簇索引中获得数据。
一般情况下，Mysql会使用当前表的自增主键去创建聚簇索引，如果没有自增主键，InnoDB会选择非空的唯一索引，如果没有这样的索引，它就会隐士生成一个主键。
一般情况下我们建立的索引都是辅助索引，目的都是去找对应的聚簇索引。
InnoDB的聚簇索引会根据主键顺序进行插入。所以最好不要用UUID作为主键，这样的话会导致聚簇索引的插入变得非常随机，增加了写入时间， 以及索引的占用空间。一般都要选择自增主键。
说完辅助索引，还有一个概念叫做覆盖索引，但这个和上面说的聚簇索引和辅助索引并不是同一个维度的。
覆盖索引指的是查询方式，如果查询的列通过索引就可以获取到，那么我们就把这个叫做覆盖索引。因为要存储索引列值，所以全文索引和哈希索引不能成为覆盖索引。
看下面的例子，一个shop_prop表，sku_id是一个普通的索引。
mysql> explain select * from shop_prop where sku_id=2323\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: shop_prop
partitions: NULL
type: ref
possible_keys: sku_id
key: sku_id
key_len: 4
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.01 sec)
mysql> explain select sku_id from shop_prop where sku_id=2323\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: shop_prop
partitions: NULL
type: ref
possible_keys: sku_id
key: sku_id
key_len: 4
ref: const
rows: 1
filtered: 100.00
Extra: Using index
1 row in set, 1 warning (0.00 sec)
当只查询sku_id列时，Extra是 Using index，这表明这是覆盖索引。
再看关于联合索引的两个概念：索引下推和MRR(Multi-range read）
MRR主要是一种查询优化的方法，一种变随机IO为顺序IO 的方法。
对于辅助索引，我们正常是在辅助索引中查询到对应的主键值，然后回表（巨簇索引）进行访问，由于主键值是随机的，导致回表查询经过多次的随机访问。而MRR就是对这种场景的优化。
当获取到主键值时，会把他们放在内存缓冲区，并将主键值进行排序，再回表查询。
索引下推，ICP
就是在辅助索引中回直接进行索引列的过滤，不用全都回表查询再过滤。
b+树性质
1.通过上面的分析，我们知道IO次数取决于b+数的高度h，假设当前数据表的数据为N，每个磁盘块的数据项的数量是m，则有h=㏒(m+1)N，当数据量N一定的情况下，m越大，h越小；而m = 磁盘块的大小 / 数据项的大小，磁盘块的大小也就是一个数据页的大小，是固定的，如果数据项占的空间越小，数据项的数量越多，树的高度越低。这就是为什么每个数据项，即索引字段要尽量的小，比如int占4字节，要比bigint8字节少一半。这也是为什么b+树要求把真实的数据放到叶子节点而不是内层节点，一旦放到内层节点，磁盘块的数据项会大幅度下降，导致树增高。当数据项等于1时将会退化成线性表。
2.当b+树的数据项是复合的数据结构，比如(name,age,sex)的时候，b+数是按照从左到右的顺序来建立搜索树的，比如当(张三,20,F)这样的数据来检索的时候，b+树会优先比较name来确定下一步的所搜方向，如果name相同再依次比较age和sex，最后得到检索的数据；但当(20,F)这样的没有name的数据来的时候，b+树就不知道下一步该查哪个节点，因为建立搜索树的时候name就是第一个比较因子，必须要先根据name来搜索才能知道下一步去哪里查询。比如当(张三,F)这样的数据来检索时，b+树可以用name来指定搜索方向，但下一个字段age的缺失，所以只能把名字等于张三的数据都找到，然后再匹配性别是F的数据了， 这个是非常重要的性质，即索引的最左匹配特性。
5、创建索引原则
1）最左前缀匹配原则
假设你在表的state、city和zip数据列上建立了复合索引。索引中的数据行按照state/city/zip次序排列， 因此它们也会自动地按照state/city和state次序排列。这意味着，即使你在查询中只指定了state值， 或者指定state和city值，MySQL也可以使用这个索引。因此，这个索引可以被用于搜索如下所示的数据列组合：
state, city, zip
state, city
state
MySQL 不能使用不涉及左前缀的搜索。例如，如果按 city 或 zip 进行搜索，则不能使用该索引。如果要搜索某个州以及某个 zip 代码（索引中的列1和列3），则此索引不能用于相应值的组合。但是，可利用索引来寻找与该州相符的行，以减少搜索范围。
2）不能过度使用索引
要以为索引“越多越好”，什么东西都用索引是错的。每个额外的索引都要占用额外的磁盘空间，并降低写操作的性能，这一点我们前面已经介绍过。在修改表的内容时，索引必须进行更新，有时可能需要重构，因此，索引越多，所花的时间越长。如果有一个索引很少利用或从不使用，那么会不必要地减缓表的修改速度。此外，MySQL 在生成一个执行计划时，要考虑各个索引，这也要费时间。创建多余的索引给查询优化带来了更多的工作。索引太多，也可能会使 MySQL 选择不到所要使用的最好索引。只保持所需的索引有利于查询优化。
如果想给已索引的表增加索引，应该考虑所要增加的索引是否是现有多列索引的最左索引。如果是，则就不要费力去增加这个索引了，因为已经有了。
3）使用唯一索引或者区分度较高的
考虑某列中值的分布。对于惟一值的列，索引的效果最好，而具有多个重复值的列，其索引效果最差。例如，存放年龄的列具有不同值，很容易区分各行。而用来记录性别的列，只含有“M”和“F”，则对此列进行索引没有多大用处（不管搜索哪个值，都会得出大约一半的行）。区分较高意思及时重复的不多。
4）给指定的列加索引
给where语句及联查指定的列添加索引
5）索引列排序
MySQL查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
6）不在索引进行计算
select * from users where YEAR(adddate)<2007; 将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成select * from users where adddate<‘2007-01-01’;
7）like语句
一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
6、索引缺点
索引文件要占磁盘空间。如果有大量的索引，索引文件可能会比数据文件更快地达到最大的文件尺寸。其次，索引文件加快了检索，但增加了插入和删除，以及更新索引列中的值的时间（即，降低了大多数涉及写入的操作的时间），因为写操作不仅涉及数据行，而且还常常涉及索引。一个表拥有的索引越多，则写操作的平均性能下降就越大。
参考资料:
微信分享/微信扫码阅读
