MySql索引总结

MySQL 索引底层数据结构

  Mysql索引使用的数据结构主要有 BTree索引 和 Hash索引。对于Hash索引来说，底层数据结构就是哈希表，因此在绝大多数需求为单条记录查询的时候，使用Hash索引查询性能最快。其余大多数场景建议使用BTree索引。

为什么索引能够提高查询速度

  MySQL的基本存储结构为 页 ，页与页之间使用双向链表进行链接，页内记录使用单向链表进行链接。查询数据时，先使用双向链表查询到数据所在的页，页内查询时，where子句中如果是主键则根据二分查找进行查询，如果为非主键则使用遍历链表的方式进行查询。整个时间复杂度为O(n)。

  使用索引之后，可以利用BTree的数据结构进行查找数据，时间复杂度可以做到O(log n)。

使用索引的缺点

• 创建索引和维护索引需要耗费时间，这种时间随着数据量的增加而增加。
• 索引需要占用物理空间，数据量越大，占用的空间越大。
• 在进行增删改的过程中，由于需要维护索引，因此会降低效率，对于增删改操作比较多的表，不适合建立太多索引。

MySQL索引类型

1. 主键索引：特殊的索引，唯一的标识一条记录，不能为空，一般用primary key来约束。

       ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 
   复制代码

2. 唯一索引：索引列的值必须唯一，且不能为空，如果是组合索引，则列值的组合必须唯一。

       ALTER TABLE `table_name` ADD UNIQUE ( `column` ) 
   复制代码

3. 普通索引：最基本的索引，它没有任何限制。

       ALTER TABLE `table_name` ADD INDEX index_name ( `column` )
   复制代码

4. 全文索引：全文索引（FULLTEXT）仅可以适用于MyISAM引擎的数据表；作用于CHAR、VARCHAR、TEXT数据类型的列。

       ALTER TABLE `table_name` ADD FULLTEXT ( `column`) 
   复制代码

5. 联合索引：将几个列作为一条索引进行检索，使用最左匹配原则。

       ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )
   复制代码

联合索引的 最左匹配原则

简单来讲，建立联合索引时，当最左边的一个或多个索引确定之后，紧跟着的索引才是有序的。（如下图,建立a,b的联合索引后，a的值为[1,1,2,2,3,3]有序，b的值为[1,2,1,4,1,2]）当不确定a的值时，b的值在结构上是无序的，这时仅where子查询中如果只使用b进行查找则会全表查找。 所以当我们用 where a = 1 and b = 2 进行查询时，由于a确定之后b的值有序，所以b字段查询时使用上索引。当我们用 where a > 1 and b = 2 进行查询时，a字段可以使用索引，但b字段由于无序所以无法使用索引。

• 总结
• 最左前缀匹配原则。MySQL会一直向右匹配直到遇到范围查询（>,<,BETWEEN,LIKE）就停止匹配，比如： a = 1 AND b = 2 AND c > 3 AND d = 4，如果建立 （a,b,c,d）顺序的索引，d是用不到索引的，如果建立（a,b,d,c）的索引，则都可以用到，a,b,d的顺序可以任意调整。
• 等于（=）和in 可以乱序。比如，a = 1 AND b = 2 AND c = 3 建立（a,b,c）索引可以任意顺序，MySQL的查询优化器会帮你优化成索引可以识别的模式

聚集索引和非聚集索引

简单概括

• 聚集索引就是以主键创建的索引
• 非聚集索引就是以非主键创建的索引

二者区别

• 聚集索引中，叶子节点存储的是表中的数据
• 非聚集索引中，叶子节点存储的是主键和索引列
• 使用非聚集索引查询出数据时，拿到叶子上的主键再去查到想要查找的数据。(拿到主键再查找这个过程叫做回表)
• 创建多个非聚集索引，每个聚集索引会生成一个索引树，因此建立多个索引会占用更多的磁盘空间。

索引和查询优化的注意事项

• 前导的模糊查询无法使用索引，如 where like '%索引' 无法使用索引
• Union、in、or可以命中索引，建议使用in
• 负条件查询不能使用索引，可以优化为in查询，其中负条件有！=、<>、not in、not exists、not like等
• 建立联合查询时，区分度最高的字段在最左边
• 把计算放到业务层而不是数据库层，因为在字段上计算不能命中索引
• 强制类型转换会全表扫描，如果phone字段是varcher类型，则下面的SQL不能命中索引。 Select * fromuser where phone=13800001234
• 建立索引的列不能为null，使用not null约束及默认值
• 如果明确知道查询结果只要一条，limit 1能够提高效率，比如验证登录的时候
• 使用合理的分页提高效率。 select id,name from product limit 866613, 20使用上述SQL语句做分页的时候，可能有人会发现，随着表数据量的增加，直接使用limit分页查询会越来越慢。优化的方法如下：可以取前一页的最大行数的id，然后根据这个最大的id来限制下一页的起点。比如此列中，上一页最大的id是866612。SQL可以采用如下的写法： select id,name from product where id> 866612 limit 20。

面试经历

Q：请说说索引的注意事项，或者说说索引的优缺点。

参考资料：

• 数据库两大神器【索引和锁】
• MySQL学习之——索引(普通索引、唯一索引、全文索引、索引匹配原则、索引命中等)
• MySQL索引详解（优缺点，何时需要/不需要创建索引，索引及sql语句的优化）