MySQL常见优化方案汇总
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,使用了索引
解决方式:覆盖索引
到此这篇关于MySQL常见优化方案汇总的文章就介绍到这了,更多相关MySQL优化方案内容请搜索猪先飞以前的文章或继续浏览下面的相关文章希望大家以后多多支持猪先飞!
原文出处:https://blog.csdn.net/qq_22701869/article/details/119651504
相关文章
- 这篇文章主要介绍了MySQL性能监控软件Nagios的安装及配置教程,这里以CentOS操作系统为环境进行演示,需要的朋友可以参考下...2015-12-14
- 新版 Mysql 中加入了对 JSON Document 的支持,可以创建 JSON 类型的字段,并有一套函数支持对JSON的查询、修改等操作,下面就实际体验一下...2016-08-23
深入研究mysql中的varchar和limit(容易被忽略的知识)
为什么标题要起这个名字呢?commen sence指的是那些大家都应该知道的事情,但往往大家又会会略这些东西,或者对这些东西一知半解,今天我总结下自己在mysql中遇到的一些commen sense类型的问题。 ...2015-03-15- 这篇文章主要介绍了MySQL 字符串拆分操作(含分隔符的字符串截取),具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2021-02-22
- 一、先说一下为什么要分表:当一张的数据达到几百万时,你查询一次所花的时间会变多,如果有联合查询的话,有可能会死在那儿了。分表的目的就在于此,减小数据库的负担,缩短查询时间。根据个人经验,mysql执行一个sql的过程如下:1...2014-05-31
- 我们自己鼓捣mysql时,总免不了会遇到这个问题:插入中文字符出现乱码,虽然这是运维先给配好的环境,但是在自己机子上玩的时候咧,总得知道个一二吧,不然以后如何优雅的吹牛B。...2015-03-15
- 这几天在centos下装mysql,这里记录一下安装的过程,方便以后查阅Mysql5.5.37安装需要cmake,5.6版本开始都需要cmake来编译,5.5以后的版本应该也要装这个。安装cmake复制代码 代码如下: [root@local ~]# wget http://www.cm...2015-03-15
- 宿主机使用网线的时候,客户机在Bridged Adapter模式下,使用Atheros AR8131 PCI-E Gigabit Ethernet Controller上网没问题。 宿主机使用无线的时候,客户机在Bridged Adapter模式下,使用可选项里唯一一个WIFI选项,Microsoft Virtual Wifi Miniport Adapter也无法上网,故弃之。...2013-09-19
- 首先要声明一点,大部分情况下,修改MySQL密码是需要有mysql里的root权限的...2013-09-11
- MySQL命令行导出数据库: 1,进入MySQL目录下的bin文件夹:cd MySQL中到bin文件夹的目录 如我输入的命令行:cd C:/Program Files/MySQL/MySQL Server 4.1/bin (或者直接将windows的环境变量path中添加该目录) ...2013-09-26
- 一、连接Mysql格式: mysql -h主机地址 -u用户名 -p用户密码1、连接到本机上的MYSQL。首先打开DOS窗口,然后进入目录mysql/bin,再键入命令mysql -u root -p,回车后提示你输密码.注意用户名前可以有空格也可以没有空格,但是密...2015-11-08
- Navicat for MySQL注册码用来激活 Navicat for MySQL 软件,只要拥有 Navicat 注册码就能激活相应的 Navicat 产品。这篇文章主要介绍了Navicat for MySQL 11注册码\激活码汇总,需要的朋友可以参考下...2020-11-23
- 这篇文章主要介绍了mysql IS NULL使用索引案例讲解,本篇文章通过简要的案例,讲解了该项技术的了解与使用,以下就是详细内容,需要的朋友可以参考下...2021-08-14
- 这篇文章主要介绍了基于PostgreSQL和mysql数据类型对比兼容,具有很好的参考价值,希望对大家有所帮助。一起跟随小编过来看看吧...2020-12-25
- 一、准备编译环境,安装所需依赖包yum groupinstall 'Development' -y yum install openssl openssl-devel zlib zlib-devel -y yum install readline-devel pcre-devel ncurses-devel bison-devel cmake -y二、编译安...2015-10-21
Mysql中 show table status 获取表信息的方法
这篇文章主要介绍了Mysql中 show table status 获取表信息的方法的相关资料,需要的朋友可以参考下...2016-03-12- 这篇文章主要为大家分享了20分钟MySQL基础入门教程,快速掌握MySQL基础知识,真正了解MySQL,具有一定的参考价值,感兴趣的小伙伴们可以参考一下...2016-12-02
- 传统的关系数据库一般由数据库(database)、表(table)、记录(record)三个层次概念组成,MongoDB是由数据库(database)、集合(collection)、文档对象(document)三个层次组成。MongoDB对于关系型数据库里的表,但是集合中没有列、行和关...2013-09-11
- 这篇文章主要介绍了node.js如何操作MySQL数据库,帮助大家更好的进行web开发,感兴趣的朋友可以了解下...2020-10-29
- 这篇文章主要介绍了Delphi远程连接Mysql的实现方法,需要的朋友可以参考下...2020-06-30