深入浅出MySQL索引

发布时间:2023年12月27日

1. 什么是索引

索引(index)是帮助MySQL进行高效数据查找的一种数据结构,在数据库中,除了数据本身之外,还维护着满足特定查找算法的数据结构,这些数据结构通过某种方式指向数据,从而可以通过这些数据结构实现高效的查找,这种数据结构就是索引。

1.1 索引的优缺点

  • 优点:
    1. 大大提高数据的查找效率,提升查找速度,降低MySQL的IO成本;
    2. 可以通过索引列进行数据排序,降低MySQL的排序成本,也减少CPU等的消耗
  • 缺点:
    1. 索引也需要占用空间,所以,在添加索引后,除数据占用的空间外,还需要占用一部分空间来保存索引
    2. 索引提升了查寻( select) 的效率,但是降低了增 (insert)、删(delete)、改(update)的效率

1.2 索引结构

MySQL的索引是在存储引擎层实现的,不同的存储引擎有不同的结构
InnoDB引擎则是采用的B+Tree索引

索引结构描述
B+Tree索引常见的索引类型,大部分存储引擎都支持B+Tree索引
Hash索引底层的数据结构是使用哈希表实现,只有在精确匹配到索引列的查询时才有效,不支持范围查询(如:>、<、between 等)
R-Tree空间索引空间索引时MyISAM存储引擎中的一种索引类型,主要用于地理空间数据类型,使用较少
Full-text全文索引是通过简历倒排索引,快速匹配或查询文档的方式,类似于Elasticsearch

1.3 MySQL的存储引擎

这里介绍MySQL3种常见的存储引擎,如:InnoDB、MyISAM、Memory

1.3.1 InnoDB引擎

  • 在MySQL5.5版本之后,是默认的存储引擎,是一种高可靠性、高性能的存储引擎
  • innoDB支持事务、行级锁、外键约束

1.3.2 MyISAM引擎

MyISAM是MySQL早期的存储引擎,现在已经采用InnoDB做为默认存储引擎

  • 访问速度块
  • 不支持事务、行级锁、外键
  • 支持表级锁

1.3.3 Memory引擎

是MySQL种基于内存的存储引擎,将数据保存到内存中,在发生断电或者其他故障的情况下,内存中的数据将会丢失,所以,一般作为缓存或者临时表使用

  • 在内存中存放
  • 默认使用Hash索引

1.3.4 存储引擎如何选择

  • InnoDB:MySQL的默认存储引擎,支持事务、表级锁、外键,如果应用对事务的完整性具有一定要求,且在高并发下对数据的一致性也有要求,除了插入、查询等操作外,还包含很多删除、更新的操作,InnoDB是很合适的选择
  • MyISAM:不支持事务、外键,只支持表级锁,不支持行级锁,如果对读、插入操作为主,对更新、删除的操作比较少,以及对事务的完整性、并发性要求不是很高,可以选择MyISAM(常被NoSQL:MongoDB替代)
  • Memory:数据保存在内存中,访问速度快,常用来做为缓存使用,缺点是内存对空间大小有限制,太大的表无法保存到内存中,而且数据的安全性、完整性也无法保证,如果用作缓存,可以选择(常被NoSQL:Redis替代)

2. 索引的分类

分类描述特点关键字
主键索引针对表中的主键创建的索引默认自动创建,有且仅有一个PRIMARY
唯一索引避免一列中的值重复,强制要求值不相同可以有多个UNIQUE
常规索引快速查找特定的数据可以有多个
全文索引针对文本中的关键词查找,而不是比较索引的值可以有多个FULLTEXT

在向字段添加了唯一约束后,会自动创建唯一索引
其中,索引又可以分为:聚簇索引、非聚簇索引(二级索引)
聚簇索引一般指代主键索引,主键索引默认为聚簇索引,其他非主键索引,是二级索引或非聚簇索引

聚簇索引的选取规则

  • 默认为表的主键,自动为主键创建聚簇索引
  • 如果表中没有主键,自动为第一个创建的唯一索引做为聚簇索引
  • 如果表中没有主键,没有唯一索引,那么InnoDB会自动生成一个隐藏的 rowid 作为聚簇索引

3. 索引的语法

查看索引

-- table 表示 表的名称
show index from table

创建索引

create [UNIQUE | FULLTEXT] index 索引名称 on table(字段名称)

删除索引

-- table 表示 表的名称
drop index 索引名称 on table

4. SQL性能分析

4.1 查看SQL的执行频率

使用 show status 命令查询 select、insert、update、delete等语句的执行频率

show [global | session] status like 'Com_______';

命令查询结果
如图,select 执行了18次

4.2 慢查询日志

在MySQL中,可通过配置来记录执行较慢的SQL,从而来对SQL进行分析
通过以下命令来查询,慢查询日志功能是否开启

show variables like '%slow_query_log%';

命令查询结果

  • slow_query_log:OFF是关闭
  • slow_query_log_file:表示日志的存储路径

慢查询日志记录了所有的SQL执行时间超过了指定参数(long_query_time 单位是秒,默认为10秒)的所有SQL语句的日志

在配置文件 /etc/my.cnf 中进行配置

# 开启慢查询日志开关
slow_query_log=1

# 设置慢日志的时间为2秒,如果SQL执行的时间超过了2秒,就会被当做慢SQL,就会进行慢查询日志的记录
long_query_time=2

4.3 profile详情

show profiles 能够帮助我们在做SQL优化时,告诉我们在SQL执行的过程中,时间具体耗费到哪里去了

查看自己的MySQL是否支持profile,使用以下命令:

select @@have_profiling

命令查询结果

  • Yes表示支持,如果查询出来不支持,很可能是MySQL版本过旧

查看此功能是否已开启,使用以下命令:

-- 查看是否开启,0:关闭;1:开启
select @@profiling;

命令查询结果

  • 1:表示已开启

如果没有开启,使用以下命令开启:

-- 开启
set profiling = 1;

在执行了一系列的SQL之后,使用 show profiles 查看耗时情况

命令查询结果
查看指定 Query_ID 的各阶段耗时情况

# 查看指定的query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;

如:show profile for query 32 ,查询query_id是32的各阶段耗时

命令执行结果

4.4 explain执行计划

查看SQL语句的执行计划,只需要在SQL的最前方添加 explain 关键字运行即可

-- 如下
explain select * from table

执行计划中各字段表示的含义:

  • id:select查询的序列号,表示查询中操作表,或者select的执行顺序;id相同,从上到下执行,id不同,id越大越先执行
  • select_type:查询类型,常见的有:
    • SIMPLE:简单查询,即不使用连接查询或子查询
    • PRIMARY:主查询
    • UNION:union中后面的查询语句
    • SUBQUERY:select、where中包含了子查询
  • type:连接类型,性能:null > system > const > eq_ref > ref > range > index > all。null一般在业务中不会出现(在查询不涉及到表时,会出现),const在查询时涉及到了主键索引或者唯一索引会出现
  • possible_key:可能用到的索引,可能一个,可能多个
  • key:实际用到的索引,如果没有用到索引,值为null
  • key_len:索引的长度
  • rows:MySQL认为必须要执行的查询行数,是一个估计值,可能不总是准确
  • filtered:返回结果的行数与读取行数的百分比,值越大越好
  • Extra:额外信息
    • using index condition:查询使用了索引,但是需要回表查询数据
    • using where,using index:查询使用了索引,但是需要的数据全部能在索引列中找到,不需要回表查询

5. 索引的使用

5.1 最左前缀法则

最左前缀法则是指:查询从最左列开始,并且中间不跳过索引列,如果跳过了某一列,后面的索引字段将会失效。如果建立了联合索引(多列索引),要遵循最左前缀法则

5.2 索引失效的情况

  • 使用了范围查询(>、<),范围查询右侧的索引将会失效,为避免失效,在业务允许的范围下,使用 >=、<= 的方式
  • 在索引列上使用了函数运算,导致索引失效
  • 字符串类型的数据,在查询时不加引号,索引失效
  • 模糊查询,尾部模糊查询,索引不会失效;头部模糊查询,索引失效
  • or分隔开的条件,如果一侧有索引,一侧没有索引,索引失效
  • 数据分布影响,如果MySQL评估使用索引会比使用全表扫描更慢,则不会使用索引,会直接使用全表扫描

5.3 SQL提示

SQL提示是优化数据库的一个重要的手段,就是在SQL语句中,加入一些人为的操作,来达到优化的目的

  • use index(索引名称):建议MySQL使用指定的索引,实际使不使用在MySQL评估后,进行选择
  • ignore index(索引名称):忽略指定的索引,将指定的索引进行忽略
  • force index(索引名称):强制使用指定的索引,强制MySQL使用指定的索引

例如:

select * from table use index(idx_table_id) where id = 1;

select * from table ignore index(idx_table_id) where id = 1;

select * from table force index(idx_table_id) where id = 1;

5.4 覆盖索引

覆盖索引:查询使用了索引,并在查询返回的字段列上,在索引字段中全部能找到
注意: 如果查询返回的字段,没有在索引字段中找到,则需要回表查询,回表查询的效率会比直接通过索引查询的效率低
所以,在查询中,减少使用 select * ,因为 select * 非常容易造成回表查询

5.5 单列索引&联合索引

单列索引:一个索引只包含一列

联合索引:一个索引包含多个列

在业务场景中,如果根据多个查询条件进行查询,考虑建立联合索引,而非单列索引

在多条件联合查询时(如果多个条件都各自建立了单列索引),MySQL优化器会评估哪个索引字段的效率更高,从而选择该索引完成查询,也就是只用到其中一个索引,进而导致回表查询

6. 索引设计原则

  • 针对数据量大的数据,且查询比较频繁的字段创建索引
  • 常作为查询条件(where)、排序条件(order by)、分组条件(group by)的字段建立索引
  • 选择区分度高的字段建立索引,尽量建立唯一索引,区分度越高,效率越高
  • 如果是字符串类型,且长度比较长,可以考虑建立前缀索引
  • 尽量使用联合索引,减少单列索引,联合索引很多时候可以避免回表查询
  • 控制索引的数量,不是越多越好,索引越多,维护代价越大,增删改的效率越低
  • 如果索引列不能存储null值,在创建表的时候,使用 not null 非空约束。当MySQL的优化器直到每列是否包含null值时,可以更好地确定哪个索引对查询更有效
文章来源:https://blog.csdn.net/Small_Yogurt/article/details/135249224
本文来自互联网用户投稿,该文观点仅代表作者本人,不代表本站立场。本站仅提供信息存储空间服务,不拥有所有权,不承担相关法律责任。