Mysql索引

目录

• 1.索引：一种用于快速查询和检索数据的数据结构
  • 1.为什么要使用索引？
  • 2.为什么不对表中的每一个列创建一个索引
  • 3.索引的分类：聚集索引和非聚集索引
  • 4.Mysql索引主要使用的两种数据结构：哈希索引和BTree索引
  • 5.怎么设置索引？
  • 6.怎么查询是否使用了索引
  • 7.索引什么时候没用和没必要用（即失效）

---

1.索引：一种用于快速查询和检索数据的数据结构

1.为什么要使用索引？

• 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
• 可以大大加快数据的检索速度（大大减少的检索的数据量）, 这也是创建索引的最主要的原因。
  • 原因：Mysql的基本存储结构是页，各个数据页可以组成一个双向链表，每个数据页中的记录又可以组成一个单向链表，每个数据页都会为存储在它里边儿的记录生成一个页目录
  • 没有用索引：需要遍历双向链表来定位对应的页，再遍历所在页的单链表从所在的页内中查找相应的记录
  • 有索引：将无序的数据变成有序(相对)，通过主键查找某条记录，在页目录中使用二分法快速定位到对应的槽，然后再遍历该槽对应分组中的记录即可快速找到指定的记录（底层结构是B+树）
• 帮助服务器避免排序和临时表。
• 将随机IO变为顺序IO
• 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。

2.为什么不对表中的每一个列创建一个索引

• 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。
  • 原因：建立索引实际上就是建立一颗B+树，一棵普通的树在极端的情况下，是能退化成链表的(树的优点就不复存在了)，B+树是平衡树的一种，是不会退化成链表的，但对这颗树增删改的话，那肯定会破坏它的原有结构，要维持平衡树，就必须做额外的工作。正因为这些额外的工作开销，导致索引会降低增删改的速度
• 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
• 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加

3.索引的分类：聚集索引和非聚集索引

• 聚集索引

  • 以主键创建的索引
  • 在叶子节点存储的是表中的数据

• 非聚集索引

  • 以非主键创建的索引

  • 在叶子节点存储的是主键和索引列，使用非聚集索引查询出数据时，拿到叶子上的主键再去查到想要查找的数据，(拿到主键再查找这个过程叫做回表)

  • 在建立的时候也未必是单列的，可以多个列来创建索引（哪个列会走索引，哪个列不走索引涉及最左匹配原则）

    • 最左匹配原则：索引可以简单如一个列(a)，也可以复杂如多个列(a, b, c, d)，即联合索引。

      如果是联合索引，最左优先，以最左边的为起点任何连续的索引都能匹配上，遇到范围查询(>、<、between、like左匹配)等就不能进一步匹配了，后续退化为线性查找。

      因此，列的排列顺序决定了可命中索引的列数

• 覆盖索引

  • 把非聚集索引要查询出的列和索引是对应的，不做回表操作
  • 实现：将被查询的字段，建立到联合索引里去

4.Mysql索引主要使用的两种数据结构：哈希索引和BTree索引

• 哈希索引
  • 采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B+树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快。
  • 哈希索引也没办法利用索引完成排序
  • 不支持最左匹配原则
  • 在有大量重复键值情况下，哈希索引的效率也是极低的---->哈希碰撞问题。
  • 不支持范围查询

5.怎么设置索引？

• 创建索引:

• CREATE INDEX indexName ON table_name (column_name)
  

• 创建表的时候指定

  • CREATE TABLE mytable(  
     
    ID INT NOT NULL,   
     
    username VARCHAR(16) NOT NULL,  
     
    INDEX [indexName] (username(length))  
     
    );  
    

• 修改表结构(添加索引)

• ALTER table tableName ADD INDEX indexName(columnName)
  

• 删除索引

  • DROP INDEX [indexName] ON mytable; 
    

  • ALTER TABLE tableName DROP INDEX indexName;
    

• 1.添加PRIMARY KEY（主键索引）

  ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` ) 
  

  2.添加UNIQUE(唯一索引)

  ALTER TABLE `table_name` ADD UNIQUE ( `column` ) 
  

  3.添加INDEX(普通索引)

  ALTER TABLE `table_name` ADD INDEX index_name ( `column` )
  

  4.添加FULLTEXT(全文索引)

  ALTER TABLE `table_name` ADD FULLTEXT ( `column`) 
  

  5.添加多列索引

  ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )
  

6.怎么查询是否使用了索引

• 通过explain来查看,即将explain放在查询的sql前面：

  • explain SELECT * from `table_name` where condition
    

7.索引什么时候没用和没必要用（即失效）

• 没用：
  1. 有or必须全有索引;
  2. 复合索引未用左列字段;
  3. like模糊查询以%开头;
  4. 条件字段如果是varchar类型,必须用引号引起来,否则索引失效
  5. where中索引列有!= 或者 <> 等运算符时;
  6. where查询字段进行表达式操作,索引失效;
  7. 如果mysql觉得全表扫描更快时（数据少）;
  8. in 和 not in 会导致索引失效
• 没必要用：
  1. 唯一性差;
  2. 频繁更新的字段不用（更新索引消耗）;
  3. where中不用的字段;
  4. 索引使用<>时，效果一般;