MySQL索引

索引类型

• 普通索引：没有任何限制；
• 唯一索引：索引列必须唯一，但允许为空；
• 主键索引：唯一且不许为空；
• 全文索引：用于搜索较长的文章时效果较好，like全文搜索效率太低(只支持MyISAM引擎和5.6版本后的InnoDB)；
• 组合索引：一个索引包含多列，遵循“最左前缀”原则；

组合索引(A/B/C)与多个单列索引(A、B、C)区别

• 使用组合索引(A/B/C)相当于分别创建了索引ABC，AB，A；
• 使用三个单列索引，但是查询时Mysql只会用到其中他认为最有效的一个索引；

导致索引失效的场景

• 模糊查询like以通配符%开头；
• 索引列上存在NULL值；
• 索引列条件使用了函数、<>、NOT IN、IN、!=操作符；
• 使用or时，若or前面列有索引后面列没有索引，则索引都不会被使用；

MySql的存储引擎

• MyISAM：不支持事务和外键，索引与数据是分开的文件，适合以SELECT和INSERT为主的应用；
• InnoDB：支持事务和外键，数据文件本身就是索引文件，相比MyISAM来说写的效率要低一些，且占用更多的磁盘空间；
• MEMORY：把表的数据存在内存中，访问速度非常快，使用HASH索引；
• MEGRE：一组MyISAM表的组合，真实的操作实际是对内部MyISAM表的操作；

B+树和B-树

1、B+树（mysql中InnoDB、MyISAM和MEMORY都可使用B+树作为索引结构）

• B+树非叶节点的子树指针与关键字个数相同；
• 非叶节点的子树指针P[i]指向关键字属于[K[i]，K[i+1])的子树，其中p[m]指向大于等于K[m]的子树；
• 所有关键字都会出现在叶子节点的链表中；
• 只有到叶子节点才命中，然后结束搜索；

2、B-树

• B-树非叶节点的子树指针个数等于关键字个数+1；
• 非叶节点的子树指针P[i]的范围是(K[i-1],K[i])，其中P[1]指向小于K[1]的子树，P[m+1]指向大于K[m]的子树；
• 任何关键字只会出现一次；
• 有可能在非叶节点就命中，然后结束搜索；

注意：为什么Mysql选用B+Tree而不是B-Tree

因为MySQL的索引能达到亿级别，会被存储在磁盘上。为了提高查询速度，因此要减少磁盘IO次数。B-树的每个节点都有data域（是一个指针），这无疑增大了节点大小，说白了增加了磁盘IO次数（磁盘IO一次读出的数据量大小是固定的，单个数据变大，每次读出的就少，IO次数增多，一次IO多耗时啊！），而B+树除了叶子节点其它节点并不存储数据，节点小，磁盘IO次数就少

3、Hash索引(只有MEMORY可使用hash作为索引)

• 对于每一行数据，针对索引都会算出一个哈希码，哈希表中保存每行数据的指针；
• 只支持等值查询；
• 不支持部分索引；

聚簇索引(InnoDB的索引)和非聚簇索引(MyISAM的索引)

• 聚簇索引的叶子节点上的data就是数据本身，key为主键；

• 非聚簇索引叶子节点上的data不是数据本身，而是存放数据的地址；