【进阶】MySQL索引介绍

最新推荐文章于 2024-06-15 16:09:53 发布

醒醒起来学习

最新推荐文章于 2024-06-15 16:09:53 发布

阅读量382

点赞数

分类专栏： MySQL 文章标签： mysql 数据库 java

本文链接：https://blog.csdn.net/m0_67042480/article/details/129466584

版权

MySQL 专栏收录该内容

10 篇文章 0 订阅

订阅专栏

半个月没写mysql了，今天记录一下。。

了解到的索引有B+tree，Hash表（Memory存储引擎中），R-tree，Full-text等

MySql用的索引结构是B+tree，B+树所有节点都会出现在叶子节点中

索引介绍：

是有效的数据结构，位于存储引擎层，能够提高检索效率，降低数据库的io成本，但是同时索引是要占有空间的，索引会降低更新表的速度，如对表进行insert，update，delete等效率降低

索引分类：

市面上常见索引：主键索引（只能有1个），二级索引，唯一索引（可以有多个），常规索引，全文索引。

在MySQL默认存储引擎是Innodb，Innodb存储引擎中根据索引结构分为了聚集索引和二级索引。每张表都必须有且仅有1个聚集索引（id），聚集索引的叶子节点下面挂的是表格某一行的数据（row）；二级索引的叶子节点关联的是对应的主键，可存在多个。

聚集索引选取规则：

聚集索引在innodb中必须存在

1.若存在主键，主键索引就是聚集索引

2.若无主键，使用第一个唯一索引作为聚集索引

3.若无主键也无唯一索引，innodb自动生成一个rowid作为隐藏的聚集索引

索引语法

1.创建

create [unique/fulltext] index 索引名 on 表格名（索引的字段可多个，单个字段是单列索引，多个字段就变成联合索引）；

2.展示

show index from 表格名；

3.删除

drop index 索引名 on 表格名；

索引的性能分析

1.查看执行的频次,通过下面指令可以查看数据库的insert，update，delete，select访问频次

show global status like ’com_______'; #7个_

2.慢日志查询（这个要修改linux系统下的my.cnf，不太建议尝试，因为可能重启后连不上mysql，我就是这样）

慢日志记录了所有执行时间超过指定参数化（默认是10s）的所有sql语句

#查询慢日志是否开启

show variables like 'slow_query_log';

vim /etx/my.cnf

#开启慢日志查询，1为开启，0为关闭

show_query_log=1;

#重启mysql

systemctl restart mysqld

进入下面目录查看生成的日志localhost-slow.log

cd /var/lib/mysql

3.通过profiles文件

profiles能够帮助我们知道执行时间多耗费在哪些sql语句

#先要检查下是否支持profiles操作

select @@have_profiling;

#是否开启开关

select @@profiling; #0表示关

set profiling=1; #开启

#查看每条sql的基本耗时情况

show profiles;

#查看指定id的sql语句的基本耗时情况

show profile for query queryid;

4.通过explain关键字查看语句执行信息

explain或desc命令获取mysql如何执行select语句的信息

语法：explain select 字段列表 from 表名 where 条件；

explain各字段含义：

id：表示查询中执行select子句或操作表的顺序（id相同，从上往下执行）；id不同先执行id值大的

select_type：表示select的类表示连接的类型

type:表示连接类型，性能有好到差的连接类型为null，system，const，eq_ref,ref,range,index,all;

possible_keys:可能用到的索引

key:实际用到的索引

key_len:索引中使用的字节数，为索引字段最大可能长度，但并非实际使用长度

ref:子查询返回类型

rows:MySQL认为必须执行的行数，在innodb引擎的表中，是1个估计值

filtered:返回结果的行数占需要读取行数的百分比，该值越大越好

验证索引效率

假如表中有100w条数据，通过索引查询指定记录比通过普通字段查询指定记录要快很多

索引的使用规则

1.联合索引的最左前缀法则

3个字段的索引长度

例如针对3个字段建立索引，在查询数据时候最左边的字段必须存在(但是放的位置无所谓)，才会走联合索引；同时如果查询时只有最左边和最右边的字段，忽略中间的字段，那么虽然还是会走联合索引但只有最左边的索引字段生效的

2.范围查询

联合索引中，出现范围查询（>,<）,范围查询右侧的列索引会失效~~

例如前者使用范围查询>,因此and后面的索引字段失效了，后者使用范围查询>=但是 and后面的索引字段没有失效

索引失效情况

1.索引列运算（对某字段运用了函数）：

不要在索引列上进行运算操作，否则索引失效

如：我们对字段phone利用substrin截取查询后索引就失效了

2.字符串不加单引号，索引失效

如字段phone本来是varchar类型，查询时不加单引号后索引失效

3.模糊查询

如果仅仅是尾部模糊查询，索引不会失效，但是如果是头部模糊查询，索引失效；头尾都模糊也失效

4.or连接

如下面因为age没有索引，索引查询不会走索引，解决方法是给字段age增加索引

5.数据分布影响

如范围查询时，如果表中大部分数据都满足范围查询，mysql会自己去评估如果全表扫描比走索引更快些，就直接全表扫描；如果表中大部分数据都不满足范围查询，mysql会自己去评估全表扫描还是走索引更快些，然后走索引

再如由于表中没有null的字段，因此通过is null查询时走索引，通过is not null查询时走全表扫描

SQL提示

是优化数据库的重要手段，就是在sql语句中加入一些认为提示，如果一个字段既含有单列索引还有联合索引，可以人为干预让mysql走不走某个索引

use index：建议mysql走我们指定的索引(mysql不一定会接受我们的建议)

语法：select 字段名 from 表名 use index(索引名) where xx;

ignore index：让mysql忽略某个索引

语法：select 字段名 from 表名 ignore index(索引名) where xx;

force index：强制mysql走我们指定的索引

语法：select 字段名 from 表名 force index(索引名) where xx;

覆盖索引&回表查询

前者using where；using index说明使用了索引，且需要的数据在索引列都能找到，不需要回表查询

后者using index condition说明使用了索引，但也走了回表查询，效率较前者更低。

覆盖索引：查询使用到了索引，且不需要回表就能返回我们想要的列，在该索引中能全部找到，那么该索引为覆盖索引，我们应该尽量使用覆盖索引。

如果要查询的某些字段通过辅助索引拿不到，那就需要拿到辅助索引下面的主键id然后回表查询到叶子节点下的row(row中包含了此id记录的所有数据)

思考：

二级索引下面会存放主键id，所以针对字段username和password建立联合索引即可

前缀索引

如果字段类型为字符串(varchar,text等),有时候需要索引很长的字符串，这会让索引变得很大，查询时浪费大量的磁盘io，影响查询效率，此时可以只将字符串的一部分前缀建立索引，这样可以大大节约索引空间，从而提高索引效率。

语法：

create index 索引名 on 表名(字段名(n)) --n表示取字段前n位

创建前缀索引时，前缀长度可以考虑选择性和索引体积大小间的权衡问题，

如字段email的前5个字符和前六个字符的选择性都是0.9583，这种时候可以选择前五个字符创建前缀索引，节省索引体积大小

前缀索引查询流程：

主键会建立聚集索引，我们创建的前缀索引会建立辅助索引(也叫二级索引，也叫非聚集索引)，首先在辅助索引中查询到"lvbu6",拿到对应id后回表查询拿到row记录，然后mysql核实row中的数据是不是我们查询要的数据("lvbu666@163.com"),然后会再回到辅助索引中继续查找看还有没有"lvbu6"，有则再次回表然后核实row，最后将核实好的所有row组装返回。