MySQL索引
索引
索引概述
索引,是帮助mysql高效获取数据的数据结构(有序);在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法,这种数据结构就是索引。
优缺点:
优点:提高数据检索的效率,降低数据库的IO成本;通过索引对数据进行排序,降低数据排序的成本,降低CPU的消耗;
缺点:
索引会占用额外空间;索引提高查询效率的同时,也降低了更新表的速度,如对表进行insert、update、delete时,效率低下;
索引结构
索引分类
在innodb存储引擎中,根据索引的存储形式,又可以分为以下两种;
聚集索引:Clustered Index ,将数据与索引放到一块,索引结构的叶子节点保存了行数据,(必须有,且只有一个)
二级索引;Secondary Index ,将数据与索引分开存储,索引结构的叶子节点关联的是对应的主键(可以存在多个)
聚集索引选取规则:
- 如果存在主键,主键索引就是聚集索引
- 如果不存在主键,将使用第一个唯一索引作为聚索引
- 如果表没有主键,或没有合适的唯一索引则,innodb会生成一个rowid作为隐藏的聚集索引
3、索引语法
# 创建索引
create [unique | fulltext] index index_name on table_name(index_col_nam,...)
# 常规索引
show index from table_name;
# 删除索引
drop index index_name on table_name;
4、SQL性能分析
SQL执行频次
Mysql客户端连接成功后,通过show [session | global] status 命令,可以提供服务器状态信息。通过如下命令,可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次;
show global status like 'com_______'; # 7个_
慢查询日志
慢查询日志记录了所有执行时间超过指定参数(long_query_time,单位:秒,默认10秒)的所有SQL语句的日志;
MYSQL的慢查询日志默认没有开启,需要在mysql的配置文件(/etc/my.cnf)中配置如下信息:
# 开启MySQL慢查询日志开关
slow_query_log=1
# 设置慢查询日志的时间为2秒,SQL语句执行时间超过2秒
long_query_time=2
配置完毕后,重启mysql服务器进行测试,查看慢查询日志文件中记录的信息/var/lib/mysql/localhost-slow.log
SQL性能分析
1、profile详情
show profiles 能够在做SQL优化时帮助我们了解时间都耗费在哪里了,通过have_profiling参数,能够看到当前mysql是否支持profile操作;
slect @@have_profiling;
默认profiling是关闭的,可以通过set语句在session/global级别开启profiling
set profiling = 1;
select @@profiling
查看是否开启开关
show profiles; # 查看每一条SQL耗时基本情况
show profile for query query_id; # 查看指定query_id 的SQL语句各个阶段的耗时情况
show profile cpu for query_id; # 查看指定query_id的SQL语句CPU的使用情况
2、explain执行计划
explain或desc命令获取mysql如何执行select语句的信息,包括在select语句执行过程中,表如何连接和连接的顺序;
explain执行计划,各字段含义
select查询的序列号,表示查询中执行select字句或者操作表的顺序(ID相同,执行顺序从上到下;ID值不同,值越大,越先执行)
表示select类型,simple(简单表,即不使用表连接或子查询)、primary(主查询,外层的查询)、UNION(union中的第二个或者后面的查询语句)、subquery(select/where之后包含的子查询)
表示连接类型,性能由好到差的连接类型,null(不访问表),system(主键、系统表),const(主键、唯一索引),eq_ref,ref(非唯一性索引),range,index,all
显示可能应用在这张表上的索引,一个或多个
实际使用的索引如果没有为null,则没有使用索引
表示索引中使用的字节数,该值为索引字段最大可能长度,并非实际使用的长度,在不损失精度的前提下,长度越短越好;
mysql 认为必须要执行查询的行数,在innodb引擎的表中,是一个估计值,可能并不总是准确的
表示返回结果的行数占,需读取行数的百分比,filtered的值越大越好。
5、索引使用
- 最左前缀法则
如果索引了多列(联合索引),要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始(存在即可,不一定完全按照顺序写在where条件中),并且不跳过索引中的列。
如果跳跃某一列,索引将部分失效(后面的字段索引失效)
- 范围查询
联合索引中,出现范围查询(>,< ),范围查询右侧的列索引失效
业务允许的情况下,尽量使用>=,<=,来进行范围查询,这种情况下全部索引都可以生效
- 索引列运算
不要再索引列上进行运算操作,索引将失效
- 字符串不加引号
字符串类型字段使用时,不加引号,索引将失效。
- 模糊查询
如果仅仅是尾部模糊匹配,索引不会失效,如果是头部模糊匹配,索引将失效
- or连接的条件
用or分割开的条件,如果or前的条件中有的列有索引,而后面的列中没有索引,那么设计的索引都不会被用到。
explain select * from tb_user where id = 10 or age = 23;
explain slect * from tb_user where phone = '1700000' or age=23;
由于age没有索引,所以即使id,phone有索引,索引也会失效,所以需要针对于age也要建立索引。
- 数据分布影响
如果mysql评估使用索引比全表更慢,则不使用索引
is null,is not null
id_num >='1200'
- SQL提示
SQL提示,是优化数据库的一个重要手段,是在SQL语句中加入一些人为的提示来达到优化操作的目的;
explain select * from tb_user use index(id_user_pro) where profession = '软工'; # 建议使用该索引,不一定mysql会接受
explain select * from tb_user ignore index(idx_user_pro) where profession =
'软工'; # 忽略某索引
explain select * from tb_user force index(idx_user_pro) where profession = '软软工'; # 强制使用某索引
- 覆盖索引
尽量使用覆盖索引(查询使用了索引,并且需要返回的列,在该索引中科院全部能够找到),减少select *
explian返回结果中最后一列,extra不用版本的返回内容不同
using index condition;查找使用了索引,但是需要回表查询数据;
using where;using index :查找使用了索引,但是需要的数据都在索引列中能够找到,所以不需要回表查询数据
思考题:一张表,有四个字段(id,username,password,status),由于数据量大,需要对以下SQL语句进行优化,该如何进行才是最优方案;
select id,username,password from tb_user where username = 'itcast';
答:建立关于username,password联合索引,id作为索引的叶子节点,规避了回表查询
- 前缀索引
当字段类型为字符串(varchar,text等)时,有时候需要索引很长的字符串,这会让索引变得很大,查询时,浪费大量的磁盘IO,影响查询效率,此时可以只将字符串的一部分前缀,建立索引,这样可以大大节约索引空间,从而提高索引效率。
语法:
create index idx_xxx on table_name(column(n));
前缀长度:
可以根据索引的选择性来决定,而选择性是指不重复
select count(distinct email) / count(*) from tb_user;
select count(distinct substring(email,1,5)) / count(*) from tb_user;
- 单列索引与联合索引
单列索引:一个索引只包含单个列
联合索引:一个索引包含多个列
在业务场景中,如果存在多个查询条件,考虑对于查询字段建立索引,建议建立联合索引,而非单列索引;
对两个查询条件,建立单列索引,mysql会自动判断那个索引,更高效从而只使用一个索引,从而进行回表查询;
索引设计原则
- 针对于数据量较大,且查询比较频繁的表建立索引(百万数据)
- 针对于常用于查询条件where,排序order by,分组(group by)操作的字段建立索引
- 尽量选择区分度高的列作为索引,尽量建立唯一索引,区分度越高,使用索引的效率越高
- 如果是字符串类型的字段,字段的长度较长,可以针对于字段的特点,建立前缀索引
- 尽量使用联合索引,减少单列索引,查询时,联合索引很多时候可以覆盖索引,节省存储空间,避免回表,提高查询效率
- 要控制索引的数量,索引并不是多多益善,索引越多,维护索引结构的代价也就越大,会影响增删改的效率
- 如果索引不能存储null值,请在创建表时使用not null约束它。当优化器知道每列是否包含null值时,它可以更好地确定哪个索引最有效地用于查询
SQL优化-插入数据
insert
批量插入 500-1000,每次插入一条,都要经历建立连接,断开连接的过程
insert into tb_test values(1,'tom'),(2,'ketty');
手动提交事务 vs 频繁事务关闭开启
start transcation;
insert into tb_test values(),();
insert into tb_test values(),();
insert into tb_test values(),();
commit;
主键顺序插入
大批量插入数据
如果一次性需要插入大批量数据(百万),使用insert语句插入性能较低,此时可以使用mysql数据库提供的load指令进行操作;
# 客户端连接服务器时,加上参数 --local-infile
mysql --local-infile -u root -p
# 设置全局参数local_infile为1,开启从本地加载文件导入数据的开关
set global local_infile = 1;
# 执行load指令将准备好的数据,加载到表结构中
load data local infile '/root/sql1.log' into table `tb_user` fields terminated by ',' lines terminated by '\n';
主键优化
- 数据组织方式
在innodb存储引擎中,表数据都是根据主键顺序组织存放的,这种存储方式的表称为索引组织表(index organized table IOT)
Innodb : 表空间 > 段 > 区 > 页 > 行
页合并
当删除一行记录,实际上记录并没有被物理删除,只是被标记(flaged)为删除,并且它的空间变得允许被其他记录声明使用。当页中删除的记录达到MERGE_THRESHOLD(默认为页的50%),Innodb会开始寻找最靠近的页(前或后)看看是否可以将两个页合并以优化
主键设计原则:
- 在满足业务需求的情况下,尽量降低主键的长度
- 插入数据时,尽量选择顺序插入,选择auto_increatement自增主键
- 尽量不要使用UUID做主键或者其他自然主键,如身份证号
- 业务操作时,避免对主键的修改
order by 优化
- Using filesort:通过表的索引或全表扫描,读取满足条件的数据行,然后再排序缓冲区sort buffer中完成排序操作,所有不是通过索引直接返回排序结果的排序都叫FileSort排序
- Using index :通过有序索引排序扫描直接返回有序数据,这种情况即为using index,不需要额外排序
Extra
Backward index scan;
order by A,B; # 如果建立联合索引时,先B,A,则违背最左前缀法则,索引失效
create index idx_user_age_pho_ad on tb_user(age asc,phone desc);
- 根据排序字段建立合适的索引,多字段排序时,也遵循最左前缀法则
- 尽量使用覆盖索引
- 多字段索引,一个升序,一个降序,此时需要注意联合索引在创建时的规则(asc/desc)
- 如果不可避免的出现filesort,大数据量排序时,可以适当增大缓冲区大小sort_buffer_size(莫仍256K
group by优化
在分组操作时,可以通过索引来提高效率;分组操作时,索引的使用也是满足最左前缀法则的;
create index idx_user_pro_age_sta on tb_user(profession,age,status);
explain select age,count(1) from tb_user where profession = '软件工程' group age; # 依然会使用到联合索引
Limit优化
一个常见又头疼的问题,limit 2000000,10,此时需要mysql排序前20000010记录,仅仅返回20000000-200000010的记录,其他记录丢弃,查询排序的代价非常大
优化思路:一般分页查询,通过创建 覆盖索引,能够比较好的提高性能,可以通过覆盖索引加子查询形式进行优化:
explain select * from tb_sku b , (select id from tb_sku order by id limist2000000,10) a where b.id = a.id;
count优化
MyISAM 引擎把一个表的总行数,存在了磁盘上,因此执行count(*)时,会直接返回这个数,效率很高;
Innodb引擎,执行count(*)时,需要把数据一行一行地从引擎里读取出来,然后累积计数。
优化思路:自己计数,借助redis,较复杂
count(*) Innodb引擎不会把全部字段取出来,而是专门做了优化,不取值,服务层直接进行按行累加;
count(1):Innodb引擎遍历整张表,但不取值,服务层对于返回的每一行,放一个数字1进行,直接进行累加;
count(字段):没有not null约束,innodb引擎会遍历整张表把每一行的字段值都取出来,返回给服务层,服务层判断是否为null,不为null,计数累加;
有not null约束,innodb引擎遍历整张表,把每一行的字段值取出来,返回给服务层,直接按行进行累加;
count(主键):innodb引擎遍历整张表,把每一行的主键id取出来,返回给服务层;服务层拿到主键后,按行进行累加
update优化
innodb的行锁是针对索引加的锁,不是针对记录加的锁,并且该索引不能失效,否则会从行锁升级为表锁;
update student set no = '2022001' where id = 1;
update sutdent set no = '2022001' where name = '韦小宝';
【推荐】编程新体验,更懂你的AI,立即体验豆包MarsCode编程助手
【推荐】凌霞软件回馈社区,博客园 & 1Panel & Halo 联合会员上线
【推荐】抖音旗下AI助手豆包,你的智能百科全书,全免费不限次数
【推荐】博客园社区专享云产品让利特惠,阿里云新客6.5折上折
【推荐】轻量又高性能的 SSH 工具 IShell:AI 加持,快人一步
· DeepSeek “源神”启动!「GitHub 热点速览」
· 微软正式发布.NET 10 Preview 1:开启下一代开发框架新篇章
· C# 集成 DeepSeek 模型实现 AI 私有化(本地部署与 API 调用教程)
· DeepSeek R1 简明指南:架构、训练、本地部署及硬件要求
· 2 本地部署DeepSeek模型构建本地知识库+联网搜索详细步骤