MySQL数据库索引简记

1.B-Tree索引

例子

CREATE TABLE `test`.`student` (
`name` VARCHAR(45) NULL ,
`dob` DATE NULL ,
`desc` VARCHAR(45) NULL ,
INDEX `btree` USING BTREE (`name` ASC, `dob` ASC, `desc` ASC) )
ENGINE = MyISAM
DEFAULT CHARACTER SET = utf8
COLLATE = utf8_general_ci;

BTree索引起作用的情况

1)匹配全名：例如，可以帮助查找 name = "Jack" and dob = "1991-09-09" and  desc = "good" 的行

2)匹配最左前缀：例如，可以查找所有name = "Allen" 的人

3)匹配列前缀： 例如，可以查找desc以 g 开头的人

4)匹配最左前缀的范围值: 例如，可以查找 name like "Jac%"的人

5)匹配某列精确值和某列范围值：例如，可以查找 name = "Jack" and desc like "goo%"

BTree索引不起作用的情况

1)查找没有从索引最左边开始的行：例如，查找dob = "1991-09-09" 或者 desc = "good",或者 name like "%ack" 的行

2)跳过索引列：例如，查找name="Jack" and desc="good" 此处跳过了dob

3)存储引擎不优化第一个范围条件右边的列：例如，name like "Jack%" and dob = "1991-09-09" and desc = "good",此处dob和desc不会被优化

2.哈希索引

哈希索引使用一个hash表来保存索引经过hash以后的key，value指向实际数据地址，他是一种高效索引，但也有他的局限性：

1.哈希索引是无序的，所以他只适用于 =， <=>， IN()这样的精准查询，而不支持范围查询，如>,<，between。

2.哈希索引不同的值经过hash以后可能存在相同的key，这些相同的key将会使用一个链表保存在一个key对应的地址中，当查询有碰撞的值时，hash索引先通过hash值找到链表，在通过匹配链表里的值来找到对应的数据地址，而当碰撞量大时，hash索引的效率就会降低

一般Hash索引用于将做长字段列的索引，他会将长字段列的索引key压缩为短的hash值，例如：

要建立长的url索引，就可以使用hash索引，由于url比较长，直接建立索引会使索引变得巨大，我们可以为url建立一个索引列，这样就可以压缩索引长度

而如果数据库引擎不支持hash索引，我们可以自己模拟hash索引，

比如一个表中有 url 列 和 url_crc列，然后为url_crc建立btree索引

查询的时候使用如下查询 

SELECT * FROM url_table WHERE url = "www.baidu.com" AND url_crc = CRC32("www.baidu.com");

这样就能使hash索引发挥作用，并且得到精确结果

而直接使用url_crc查询，则有可能出现碰撞，例如

SELECT * FROM url_table WHERE url_crc = CRC32("www.baidu.com");

有可能查出两条不同的url，因为这两条url的crc32值是一样的

3.空间索引

4.全文索引：MyISAM独有的一种特殊索引(从MySQL5.6开始Innodb也支持全文索引)，用来检索大段文本，查找文本关键字，索引类型为FULLTEXT，使用match和against函数操作，例如

select * from news where match(content) against("The president");

此处先对content列简历全文索引，然后查找content中含有The president关键字的行

其他

MySQL的索引总是按照字符集的单位最大长度计算,例如engine的索引最大长度是999bytes,而使用了utf8字符集,那么索引的最大长度就是333个字符(utf8单个字符最大为3bytes)