目录
前言
今天学习了Mysql中索引相关的知识,感觉有些复杂,特此在这里记录一下。
一、索引概述
索引(index)是帮助MySQL高效获取数据的数据结构(有序)。在数据之外,数据库系统还维护着满足 特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据, 这样就可以在这些数据结构上实现高级查找算法,这种数据结构就是索引。
这里举个例子,当没有索引的时候需要去执行这么一条语句:select * from user where age =18;数据库只能去进行全表扫描。而当有一个索引结构类似于二叉排序树的时候,他的查询速率就会很高。
二、索引优缺点
优点:
- 可以提高数据检索的效率,降低数据库的IO成本。
- 通过索引列对数据进行排序,降低CPU的消耗。
缺点:
- 索引是要占用磁盘空间的。
- 索引虽然提高了查询的效率,但同时也增加了增删改操作的时间
三、索引的结构
下图中展示了mysql中的索引
由于索引是在存储引擎层实现的,因此不同的存储引擎会有不同的索引结构。
3.1 B+Tree
下面可以看下Mysql中的B+Tree的结构,提示:Mysql是对B+Tree经过优化的
也可以分析一下他的特点
- 所有的数据都会出现在叶子节点
- 所有的叶子节点形成一个双向循环链表
- 所有非叶子节点仅仅起到索引作用
3.2 Hash
哈希结构索引,就是通过哈希算法,把键值换算到新的hash值,直接进行映射,然后存储再hash表中。这里就不多赘述了。
特点:
- hash索引只可以用于对等比较,例如(=,in),不支持范围查询(between,>,<,...)
- 无法完成排序
- 查询的效率很高,在没有hash冲突时,只需要一次检索就可以了。
在mysql中支持hash索引的是Memory存储引擎。InnoDB中也具有自适应hash功能,hash索引是InnoDB存储引擎根据B+Tree索引在特定的条件下自动构成的。
四、索引分类
在Mysql数据库中,按照索引的具体类型分类:
而根据索引的存储形式,又分类为:
聚集索引选取规则:
- 如果存在主键,主键就是聚集索引
- 如果不存在主键,将使用第一个唯一索引作为聚集索引
- 如果表没有主键,也没有唯一索引,那么InnoDB会自动生成一个rowid作为隐藏的聚集索引
特点:
- 聚集索引的叶子节点下挂的是这一行的数据
- 二级索引的叶子节点下挂的是该字段值对应的主键值
4.1 回表查询
当查找数据时先去二级索引查找数据,找到对应的主键值,然后再去聚集索引,根据主键值获取数据。把这样的过程称为回表查询。
因此可以知道,直接使用聚集索引的效率更高一点。
五、索引语法
(1)创建索引
create [unique|fulltext] index index_name on table_name (index_col_name,...);
(2)查看索引
show index from table_name;
(3)删除索引
drop index index_name on table_name;
六、SQL性能分析
6.1 sql执行频率
-- session 是查看当前的会话;
-- global 是查询全局数据;
show global status like 'Com_______'; -- 7个下划线
通过这个表格,可以得到该数据库是以查询为主还是以增删改为主。
6.2 慢查询日志
Mysql中的慢查询日志 默认是不开启的。可以先去查询一下:
show variables like 'slow_query_log';
没有开启的话要去,mysql的配置文件(/etc/my.cnf)中配置如下信息;
# 开启Mysql慢日志查询开关
slow_query_log=1;
#设置慢日志的时间为2秒,SQL语句执行时间超过两秒,就会被视为慢查询,记录到慢查询日志
long_query_time=2;
配置完毕后,重新启动Mysql服务器进行测试
systemctl restart mysqld
6.3 profile详情
show profiles 能够在SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling参数,能够看到当前Mysql是否支持profile操作
select @@have_profiling;
如果没有开启
set profiling =1;
当你进行了一系列的业务SQL操作后,可以通过如下指令查看执行耗时
-- 查看每一条SQL的耗时基本情况
show profiles;
--查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;
--查看指定query_id的SQL语句各个阶段的cpu使用情况
show profile cpu for query query_id;
6.4 explain
explain是在日常中经常会使用到的,它可以展示更多的信息,让你可以直观的看到具体的SQL执行情况。
-- 直接在select语句之前加上关键字 explain /desc
explain select 字段列表 from 表名 where 条件;
explain中各个字段的含义
七、索引使用
使用索引会大大的提高查询的效率。
7.2 最左前缀法则
如果索引了多列(联合索引),要遵守最左前缀法则。
最左前缀法则指的是从索引的最左列开始,并且不跳过索引中的列。如果跳过某一列,索引会部分失效。(后面的字段索引失效)
例如在 tb_user 表中,有一个联合索引,这个联合索引涉及到三个字段,顺序分别为:profession, age,status。 对于最左前缀法则指的是,查询时,最左变的列,也就是profession必须存在,否则索引全部失效。 而且中间不能跳过某一列,否则该列后面的字段索引将失效。
具体希望大家自己下去多尝试,多发现;
通过explain语句,调换顺序,观察索引字段的长度的变化。
explain select * from tb_user where profession ='高等数学' and age > 30 and status ='0';
7.3 范围查询
在联合索引中,出现范围查询(> , < ) 范围查询右侧的列索引失效。
因此在业务允许的情况下,尽可能的使用>= 或 <=这样的查询 。
7.4 索引失效情况
- 在索引列上进行运算操作
- 字符串类型字段使用时,不加引号,索引将会失效。
- 出现头部模糊匹配查询,索引失效
- or连接的条件,如果or的前面又索引,后面没有索引,那么涉及的索引都不会用到。
- 数据分布影响,当全表查询比走索引快时,不会使用索引。
7.5 SQL提示
(1) use index : 建议使用哪一个索引来完成此次查询(只是建议,不一定会使用,mysql内部会进行评估后再选择)
explain select * from tb_user use index(idx_user_pro) where profession='高等数学';
(2) ignore index :忽略指定的索引
(3)force index: 强制使用索引
7.6 覆盖索引
覆盖索引是指查询使用了索引,并且需要返回的列,再该索引中全部可以找到。
这里可以通过explain的extra清晰的得到是否是覆盖索引。
7.7 前缀索引
一般是字段的类型为字符串,且字段很长,这会让索引变得很大,浪费大量的磁盘IO,这时,可以把字符串的一部分前缀建立索引,从而提高索引效率。
create index idx_xxxx on table_name(column(n));
那么如何确定适合的长度呢?
select count (distinct substring(字段 , 1,5)) / count(*) from tb_ user;
-- 5是你要的前缀长度
7.8 单列索引与联合索引
这里直接建议,存在多个查询条件时,能建立联合索引就建立联合索引。
哪怕有两个单列索引,在一同查询时,只会使用一个,还是会走回表查询.
八、索引设计原则
- 针对数据量较大,且查询比较频繁的表建立索引
- 针对于常作为查询条件,排序,分组操作的字段建立索引
- 尽量选择区分度高的列作为索引,尽量建立唯一索引,区分度越高,则索引的效率越高
- 如果时字符串类型,字段的长度较长时,可以使用前缀索引
- 尽量使用联合索引,减少单列索引,查询时,联合索引很多时候可以覆盖索引,节省存储空间,避免回表
- 要控制索引的数量,并不是多多益善。
- 如果索引不能存储null值,请用非空约束,让mysql内部的优化器知道每列是否包含null值,可以更好的确定哪个索引更加有效。