MySQL常见优化方案汇总
作者:一点光辉 发布时间:2024-01-23 05:29:35
mysql优化是我们日常工作经常遇到的问题,今天给大家说下MySQL常见的几种优化方案。
注:原始资料来自享学课堂,自己加上整理和思考
思考sql优化的几个地方,我把他做了个分类,方便理解
select [字段 优化1]:主要是覆盖索引
from []
where [条件 优化2]
union [联合查询 优化3]
新建表格
CREATE TABLE `student` (
`id` int(11) NOT NULL AUTO_INCREMENT COMMENT '主键',
`name` varchar(50) DEFAULT NULL COMMENT '姓名',
`age` int(11) DEFAULT NULL COMMENT '年龄',
`phone` varchar(12) DEFAULT NULL,
`create_time` datetime DEFAULT NULL COMMENT '创建时间',
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
添加索引,添加索引之后
key_len:根据这个值,就可以判断索引使用情况,特别是在组合索引的时候,判断所有的索引字段是否都被查询用到。
key_len计算方式简单介绍
latin1占用1个字节,gbk占用2个字节,utf8占用3个字节
不允许为空:
varchar(10):10*3
char(10):10*3+2
int:4
允许为空:
varchar(10):10*3+1
char(10):10*3+2+1
int:4+1
使用完全索引key_len=name(50*3+2+1=153)+age(4+1)+phone(12*3+2+1=39)
alter table studen add index name_age_phone(name, age, phone);
添加数据
insert into student(name,age,phone,create_time) values('赛文',1000,'15717177664',now());
insert into student(name,age,phone,create_time) values('雷欧',1200,'15733337664',now());
insert into student(name,age,phone,create_time) values('泰罗',800,'15714447664',now());
一、优化点1:字段优化
覆盖索引尽量用
简单解释解释,索引是哪几个列,就查询哪几个列: 覆盖索引的原因:索引是高效找到行的一个方法,但是一般数据库也能使用 索引找到一个列的数据,因此它 不必读取整个行。毕竟索引叶子节点存储了它们索引的数据; 当能通过读取索引就可以得到想要的数据,那就不需要读取行了。一个索引 包含了(或 覆盖了)满足查询结果的数据就叫做覆盖索引 注意:有索引尽量不要使用select *
#未覆盖索引
EXPLAIN SELECT * FROM student WHERE NAME = '泰罗' and age =1000 and phone='15717177664';
#覆盖了索引
EXPLAIN SELECT name,age,phone FROM student WHERE NAME = '泰罗' and age =1000 and phone='15717177664';
#包含了索引
EXPLAIN SELECT name FROM student WHERE NAME = '泰罗' and age =1000 and phone='15717177664';
#加上主键也还是覆盖索引
EXPLAIN SELECT id, name,age,phone FROM student WHERE NAME = '泰罗' and age =1000 and phone='15717177664';
未使用覆盖索引
使用完全覆盖索引
使用包含覆盖索引
加上主键还是覆盖索引
二、优化点2:where优化
1.尽量全值匹配
EXPLAIN SELECT * FROM student WHERE NAME = '赛文';
EXPLAIN SELECT * FROM student WHERE NAME = '雷欧' AND age = 1200;
EXPLAIN SELECT * FROM student WHERE NAME = '泰罗' AND age = 800 AND phone = '15714447664';
执行结果,三个都用到了索引,但是key_len是不同的,key_len=197,表示所有索引都使用到了
当建立了索引列后,能在 wherel 条件中使用索引的尽量所用。
2.最佳左前缀法则
最左前缀法则:指的是查询从索引的最左前列开始并且不跳过索引中的列。 我们定义的索引顺序是 name_age_phone ,所以查询的时候也应该从name开始,然后age,然后phone 情况1:从age、phone开始查询,tpye=All,key = null,没使用索引
情况2:从phone开始查询,type=All,key=null,未使用索引
情况3:从name开始,type=ref,使用了索引
3.范围条件放最后
没有使用范围查询,key_len=197,使用到了name+age+phone组合索引
EXPLAIN SELECT * FROM student WHERE NAME = '泰罗' AND age = 1000 AND phone = '15717177664';
使用了范围查询,key_len从197变为158,即除了name和age,phone索引失效了
EXPLAIN SELECT * FROM student WHERE NAME = '泰罗' AND age > 800 AND phone = '15717177664';
key_len=name(153)+age(5)
4.不在索引列上做任何操作
EXPLAIN SELECT * FROM student WHERE NAME = '泰罗';
EXPLAIN SELECT * FROM student WHERE left(NAME,1) = '泰罗';
不做计算,key_len有值,key_len=153,有使用name索引
做了截取结算,type=All,key_len=null,未使用索引
5.不等于要甚用
mysql 在使用不等于 (!= 或者 <>) 的时候无法使用索引会导致全表扫描
#有使用到索引
EXPLAIN SELECT * FROM student WHERE NAME = '泰罗';
#不等于查询,未使用到索引
EXPLAIN SELECT * FROM student WHERE NAME != '泰罗';
EXPLAIN SELECT * FROM student WHERE NAME <> '泰罗';
#如果定要需要使用不等于,请用覆盖索引
EXPLAIN SELECT name,age,phone FROM student WHERE NAME != '泰罗';
EXPLAIN SELECT name,age,phone FROM student WHERE NAME <> '泰罗';
使用不等于查询,跳过索引
使用不等于查询,同时使用覆盖索引,此时可以使用到索引
6.Null/Not null有影响
修改为非空
那么为not null,此时导致索引失效
EXPLAIN select * from student where name is null;
EXPLAIN select * from student where name is not null;
改为可以为空
查询为空,索引起作用了
查询非空索引失效
解决方法:
使用覆盖索引(覆盖索引解千愁)
7、Like 查询要当心 like
以通配符开头 ('%abc...')mysql 索引失效会变成全表扫描的操作
#like 以通配符开头('%abc...')mysql 索引失效会变成全表扫描的操作
#索引有效
EXPLAIN select * from student where name ='泰罗';
#索引失效
EXPLAIN select * from student where name like '%泰罗%';
#索引失效
EXPLAIN select * from student where name like '%泰罗';
#索引有效
EXPLAIN select * from student where name like '泰罗%';
解决方式:覆盖索引
EXPLAIN select name,age,phone from student where name like '%泰罗%';
使用覆盖索引能够解决
8.字符类型加引号
字符串不加单引号索引失效(这个看着有点鸡肋了,一般查询字符串都会加上引号)
使用覆盖索引解决
三、优化3
1.OR 改 UNION 效率高
未使用索引
EXPLAIN select * from student where name='泰罗' or name = '雷欧';
使用索引
EXPLAIN
select * from student where name='泰罗'
UNION
select * from student where name = '雷欧';
解决方式:覆盖索引
EXPLAIN select name,age from student where name='泰罗' or name = '雷欧';
使用or未使用到索引
使用union,使用了索引
解决方式:覆盖索引
来源:https://blog.csdn.net/qq_22701869/article/details/119651504
猜你喜欢
- 有的时候,我们为了保持网页的美观,需要将较长的文字在一定长度时截断。比如我们希望在列表中显示文章标题的前15个字,那么一个这样的标题:“rs
- 前言相信大家在最近的chatGPT的注册或者使用过程中都遇到了很多很多的报错,接下来的内容是关于chatGPT不管是注册还是使用过程中所有报
- 1.在使用MySQL和php的时候出现过中文乱码问题(1) 只要是gb2312,gbk,utf8等支持多字节编码的字符集都可以储存汉字,当然
- 一段非常简单代码普通调用方式def console1(a, b): print("进入函数")
- 用python进行线性回归分析非常方便,有现成的库可以使用比如:numpy.linalog.lstsq例子、scipy.stat
- 简单的设计思路利用pytest对一个接口进行各种场景测试并且断言验证配置文件独立开来(conf文件),实现不同环境下只需要改环境配置即可测试
- 一、创建虚拟环境(1)打开cmd命令窗口(2)创建虚拟环境 conda create -n mydjango_env(3)查看虚拟环境 co
- python中的turtle库是3.6版本中新推出的绘图工具库,那么如何使用呢?下面小编给大家分享一下。首先打开pycharm软件,右键单击
- 编写Python代码,大家都需要遵循PEP8,因此在pycharm中,如何设置每行最大长度限制,成为了一个小的知识盲点,在这里做一下记录,方
- 建造者模式的适用范围:想要创建一个由多个部分组成的对象,而且它的构成需要一步接一步的完成。只有当各个部分都完成了,这个对象才完整。建造者模式
- 安装conda activate ps pip install visdom激活ps的环境,在指定的ps环境中安装visdom开启pytho
- 单表操作增加数据auther_obj = {"auther_name":"崔皓然","au
- translate函数语法:translate(expr, from_strimg, to_string)简介:translate返回exp
- 选择题以下python代码输出什么?a = [2,3,1]sorted(a)print(a)A aB [3, 2, 1]C [2, 3, 1
- 集中式工作流(不常用)集中式工作流像SVN一样,以中央仓库作为项目所有修改的单点实体。所有修改都提交到 Master分支上。这种方式与 SV
- 假设有一名为"addnewuser"的存储过程,其内容如下:Create PROCEDURE dbo
- 在本文中,我挑选了15个最有用的软件包,介绍它们的功能和特点1. DashDash 是比较新的软件包,它是用纯 Python 构建数据可视化
- 这篇文章主要介绍了python使用opencv在Windows下调用摄像头实现解析,文中通过示例代码介绍的非常详细,对大家的学习或者工作具有
- 本文实例为大家分享了js实现QQ邮箱邮件拖拽删除的具体代码,供大家参考,具体内容如下步骤分析:根据数据结构生成HTML结构全选和单选功能的实
- 在抓取网络数据的时候,有时会用正则对结构化的数据进行提取,比如 href="https://www.1234.com"等