MySql索引

本文主要总结了一下MySql索引的基础知识和常见的索引。比较简洁，可能会有些晦涩。不明白的地方，可以参考以下几篇博客学习。

参考博客 :
MySql数据库索引原理
Mysql学习-索引总结
B-Tree百度百科

一、为什么使用索引

当数据量较大的时候，如果不适用索引，则对整表扫描，效率较低。如果创建了索引，则根据算法优化排序指定列，能快速定位到数据的地址，提高查询速度。

二、索引列数据类型的选择

不同数据类型在创建索引时，磁盘、CPU、内存开销不同，应选择较为合适类型

• 越小的类型越好。占用字节少的类型在计算、存储等过程中更为迅速。
• 简单数据类型更好。简单数据类型的比较要比字符串比较的性能高得多。用MySql的时间类型存储时间而不是字符串。用整型存储IP（这个不太理解）。
• 尽量避免null。MySql中含有空值的列很难进行查询优化，应用0或者其他字符代替null。

三、索引类型

索引是在存储引擎实现的，而不是服务层。不同的存储引擎所支持的索引也不一样。

1.B-Tree索引

• 索引原理
  

B-Tree索引，将索引列数据组成一个m阶h层B-Tree，每个非叶子节点存放m/2-m个数据以及对应子节点的指针，且这些数据都在一块磁盘块里。在查找时，将根节点读进内存，IO次数+1，进行二分查找，二分查找为内存处理，时间忽略不计；然后查找对应的子节点，将子节点的数据读进内存，IO次数+1。所以最差的搜索时间是h次IO。一般B-Tree只有2-4层，也就是只有2-4次IO，而没有索引时需要每一条数据读一次IO，非常消耗时间。

简言之，普通查询需要逐行匹配数据，IO次数较多，效率很低。于是将经常查询的列创建B-Tree类型的索引，根据B-Tree的特性，大大降低IO次数，达到提高性能的目的。

B-tree索引适用于全值匹配、匹配最左前缀、匹配列前缀、匹配范围值

• 效率
  最低效率为全局二分查找的结果
  

  B-Tree最低搜索性能

• 原则

当明白了B-Tree索引的原理之后，我们就可以根据他的原理，正确的使用它。

a.最左前缀原则

复合索引虚满足最左前缀原则。即所以在查询是从最左边列开始匹配，匹配到范围查询时停止。如(a,b,c,d)复合索引，如果查询时不提供a列，无法查询；如果b列为like,>,<,between，则c,d列条件无效。另=和in可以乱序，MySql的优化器会将其优化为正确顺序。

复合索引，即多列组成的索引（我理解他们是把多列绑定在一起），如果你左边的列都不提供，怎么能匹配后续列呢？

b.尽量选择字段较小、区分度高的列做索引

由B-Tree的原理我们可以知道，B-Tree的性能很大部分依赖于树的高度h，而h=log(m+1)N，也就是每个磁盘块的数据项的个数越多，h越小，性能越高。磁盘块的大小是固定的，则数据项的大小越小，m越大，h越小，性能越好。所以要选择尽量小的数据列

区分度高是为了更好的筛选数据。

c.索引不能参与计算

索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引。B-Tree中存储的都是数据表中的值，如果进行计算以后再匹配的话，需要把所有结果都进行运算以后才能检索，检索成本增加。

索引的目的就是当我们检索某些数据的时候通过检索索引列提高效率。如果把索引列的数据进行计算以后再进行检索，那就索引列的意义就不在了。

d.尽量扩展索引，不要新建索引

2.Hash索引

Hash索引只在Memory存储引擎中显示支持

顾名思义，Hash索引时对相应列的hash值进行存储，那么它就包含了一些hash的特性。

• a. 索引中不存储数据

索引中存储的都是对应列的hash值，只能定位到对应数据的位置进行读取。

• b. 不能进行排序
• c. 不能进行部分字段匹配
• d. 只能进行等值匹配，不能进行范围匹配
• e. 匹配速度较快

3.其他索引

Mysql常见的索引：普通索引、主键索引、唯一索引、组合索引、全文索引

主键索引
一种特殊的唯一索引，且不允许有空值

唯一索引
列的值唯一，可以为空。如果是组合索引，多列组成的值必须唯一。

组合索引
多列组合创建的索引，主要为了满足平时SQL中的一些组合查询条件

全文索引
全文索引时可以支持字符内搜索的索引。如实现like '%hello%'的功能。同时它还支持自然语言搜索和布尔搜索。

注：当数据量较大的时候，生成全局索引会很耗时、耗空间。具体使用的时候再进行详细的调研。

• 自然语言搜索
  自然语言索引引擎将计算每一个文档对象和查询的相关度。这里，相关度是指基于匹配的关键词个数，以及关键词在文档中出现的个数。
  自然语言索引是默认的。
  如match()方法等

• 布尔搜索
  布尔搜索其实是采用一些计算符来进行筛选然后搜索。
  +word：表示word必须存在
  -word：表示word必须不存在
  (no operator) 表示word是可选的。但是如果是可选的，其相关性会更高

select *from fts where match(body) against("+django -python" IN BOOLEANMODE);

>表示出现该word增加相关性
<表示出现该word降低相关性

select *,match(body) against("flask >python <django" in boolean mode) as rf from fts;

@distance表示查询的多个单词之间的距离是否在distance之内

select *from fts where match(body) against('"django flask"@3' IN BOOLEANMODE)

~允许出现该单词，但是出现时相关性为负
*表示以该单词开头的单词，如lik*，表示可以是like，likes和lik

select *from fts where match(body) against("p*" IN BOOLEAN MODE);

“表示短语

select *from fts where match(body) against('"hello world"'IN BOOLEAN MODE);