
MySQL进阶-索引

1.基本语法
1 | create index 索引名 on 表名(字段名) |
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)字段建立索引,那么入下图所示
如图所示他们是按照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
6set global slow_query_log=1;
```
- 查看慢查询日志
- 设置慢查询日志的阈值
```sql
set global long_query_time=1;
4.3 profiling
用来记录每一句sql执行信息 需要手动打开
4.4 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.