MySQL进阶-索引

MySQL进阶-索引

Jason Lv3

1.基本语法

1
2
3
create index 索引名 on 表名(字段名)
drop index 索引名 on 表名
show index from 表名

2.索引结构

由存储引擎实现,包括:

  • B+tree索引

    所有元素都会出现在叶子结点,非叶结点起索引作用,叶子结点之间用指针连接。

  • hash索引,不支持范围查找
  • FullText索引
  • 空间数据索引

3.索引分类

  • 主键索引
  • 唯一索引 避免某数据列中的值重复
  • 普通索引
  • 全文索引
    根据存储形式又分为:
  • 聚集索引(聚簇索引) 数据与索引存在一块 只会有一个
  • 二级索引 数据与索引分开存储 存主键

    若存在主键 主键索引就是聚集索引
    若不存在主键 使用第一个唯一索引作为聚集索引
    若都无 INNODB自动生成一个rowid作为隐藏的聚集索引

  • 覆盖索引 要查的数据列正好有索引 无需回表
  • 联合索引 使用表中的多个字段创建索引

    在使用联合索引时,MySQL 会根据联合索引中的字段顺序,从左到右依次到查询条件中去匹配,如果查询条件中存在与联合索引中最左侧字段相匹配的字段,则就会使用该字段过滤一批数据,直至联合索引中全部字段匹配完成

联合索引,一定要扯最左匹配!

最左匹配

所谓最左原则指的就是如果你的 SQL 语句中用到了联合索引中的最左边的索引,那么这条 SQL 语句就可以利用这个联合索引去进行匹配,值得注意的是,当遇到范围查询(>、<、between、like)就会停止匹配。
假设,我们对(a,b)字段建立一个索引,也就是说,你where后条件为

a = 1
a = 1 and b = 2
是可以匹配索引的。但是要注意的是~你执行

b= 2 and a =1
也是能匹配到索引的,因为Mysql有优化器会自动调整a,b的顺序与索引顺序一致。
相反的,你执行

b = 2
就匹配不到索引了。
而你对(a,b,c,d)建立索引,where后条件为

a = 1 and b = 2 and c > 3 and d = 4
那么,a,b,c三个字段能用到索引,而d就匹配不到。因为遇到了范围查询

最左匹配的原理?
假设,我们对(a,b)字段建立索引,那么入下图所示
alt

如图所示他们是按照a来进行排序,在a相等的情况下,才按b来排序。

因此,我们可以看到a是有序的1,1,2,2,3,3。而b是一种全局无序,局部相对有序状态!
什么意思呢?
从全局来看,b的值为1,2,1,4,1,2,是无序的,因此直接执行b = 2这种查询条件没有办法利用索引。

从局部来看,当a的值确定的时候,b是有序的。例如a = 1时,b值为1,2是有序的状态。当a=2时候,b的值为1,4也是有序状态。
因此,你执行a = 1 and b = 2是a,b字段能用到索引的。而你执行a > 1 and b = 2时,a字段能用到索引,b字段用不到索引。因为a的值此时是一个范围,不是固定的,在这个范围内b值不是有序的,因此b字段用不上索引。
综上所示,最左匹配原则,在遇到范围查询的时候,就会停止匹配。

4.性能分析

4.1 查看执行频次

1
show global status like 'Com_______';

4.2 慢查询日志

定位sql语句进行优化

  • 开启慢查询日志
    1
    2
    3
    4
    5
    6
    set global slow_query_log=1;        
    ```
    - 查看慢查询日志
    - 设置慢查询日志的阈值
    ```sql
    set global long_query_time=1;

4.3 profiling

用来记录每一句sql执行信息 需要手动打开

4.4 explain

explain

type: 性能指标

5. 索引使用原则

Warning

  • 被频繁更新的字段应该慎重建立索引
  • 尽可能的考虑建立联合索引而不是单列索引
  • 冗余索引指的是索引的功能相同,能够命中索引(a, b)就肯定能命中索引(a) ,那么索引(a)就是冗余索引。

6. 索引失效

最左匹配:查询条件中要有联合索引最左边的字段,否则无法使用索引。

  • 范围查询:尽量使用大于等于否则右边条件失效
  • 缺失字段:索引会失效
  • 参与运算
  • 字符串不加引号
  • 模糊查询 xx% 不会失效 %xx失效 前面加%就会失效
  • or 前后都要有索引
  • 全盘扫描效率大于索引 则索引失效

7. 索引使用

  • 前缀索引 当为一个字符串长度较长的字段建立索引时,可以取前一部分建立索引,这样可以大大节约索引空间,从而提高索引效率。
    1
    alter table table_name add index index_name(column(n))
    至于前缀长度取到多少,可以使用
    1
    select count(distinct left(column, 长度))/count(*) from table_name;
    这个比例可以用来衡量前缀索引的合理性。
  • 单列索引VS联合索引
    联合索引
  • 覆盖索引 无需回表查询
  • Title: MySQL进阶-索引
  • Author: Jason
  • Created at : 2023-09-07 12:07:02
  • Updated at : 2023-09-10 16:00:46
  • Link: https://xxxijason1201.github.io/2023/09/07/MySQL/索引/
  • License: This work is licensed under CC BY-NC-SA 4.0.
 Comments