MySql索引

索引类型

MySql主要的索引类型有：FULLTEXT、HASH、BTREE、RTREE

• FULLTEXT即全文索引，主要用来解决WHERE name LIKE "%zhangsan"等针对文本的模糊查询效率低的问题。
• HASH即哈希索引，主要用于等值查询，时间复杂度为O(1)，效率高。缺点为不支持排序、范围查询和模糊查询。
• BTREE即B+树索引，优点是HASH的缺点并且性能稳定。
• RTREE即空间数据索引，多用于地理数据的存储，相比于其他索引，空间数据索引的优势在于范围查找。

数据库为什么使用B+树而不是B树作为索引

• B+树支持随机检索和顺序检索
• B+树内部节点只存储键，从而使得B+树的一个节点存储多个索引节点，减少I/O次数。
• B+树的叶子节点是连接在一起的，所以范围查找，顺序查找更方便。
• B+树的查询性能更稳定，每次都从根节点到叶子节点。

聚簇索引和非聚簇索引

聚、非簇索引的主要区别是数据和索引是否分开存储。

• 聚簇索引：将数据和索引放到一起存储，叶子节点保留了数据。
• 非聚簇索引：分开存储，叶子节点存放的是数据存放的地址。

  在InnoDB存储引擎中，默认的索引为B+树索引，利用主键创建的索引为聚簇索引（主索引），非主键创建的索引为非聚簇索引（辅助索引），该索引中的叶子节点存储的是主键。
  在MyISAM存储引擎中，默认的索引也是B+树索引，但主索引和辅助索引都是非聚簇索引，其索引结构的叶子节点存储的均是数据行的地址并且辅助索引检索无需访问主键索引。

索引的设计原则

• 尽量使用where后面出现的列来创建索引。
• 尽量使用区分度高的列来创建索引。
• 尽量使用短索引，对较长的字符串进行索引时应该指定一个较短的前缀长度。
• 尽量利用最左前缀。

索引失效的例子

最左匹配原则：从最左边为起点开始连续匹配，遇到范围查询（<、>、between、like）会停止匹配。

不满足最左匹配原则导致索引失效

假设已创建索引(a,b,c).
select * table_name where b = 1; # 索引失效，因为不是从最左边开始匹配的
select * table_name where b = 1 and c = 3; # 索引因为不是从最左边开始匹配的
select * table_name where a = 1 and c = 3; # a列用到了索引
select * table_name where a = 1 and b < 2 and c > 3; # c列没用到索引，

其他原因导致索引失效

• 1、索引列使用表达式。
• 2、条件中有or。
• 3、在索引列上进行数据类型隐形转换。
• 4、使用函数。
• 5、以%开头的模糊查询。
• 6、索引列上使用!、=、<>进行判断。
• 7、索引字段上使用is null / is not null 。

1、select * from table_name where a + 2 = 3
2、select * from table_name where a = 1 or b = 2
3、select * from table_name where age = '18' # 不会用到索引，只有age = 18才会
4、select * from table_name where abs(a)
5、select * from table_name where name like '%xixi'
6、select * from table_name where a != 1
7、select * from table_name where a is null