Mysql索引


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. 索引使用<>时,效果一般;

posted @ 2020-11-27 14:42  一路向暖  阅读(102)  评论(0编辑  收藏  举报