![e2734d1cdfa41de26ae8e3c4217b300f.png](https://i-blog.csdnimg.cn/blog_migrate/8394d5fcb855c0b8ef33413c2688eb55.jpeg)
1.如果内容能被转化为数字类型,尽量使用数字类型而不是字符类型
如果要保存IPv4地址, 反例
`ip_address` VARCHAR(15) NOT NULL
正例。
`ip_address` INT UNSIGNED NOT NULL
原因 - 因为IPv4地址可以转化成一个int类型的十进制整数。转化方法:ip中的每一段转化为两位的十六进制整数。例如,192.160.98.123
转化为十六进制数是,C0
,A0
,62
,7B
,C0A0627B
转化为十进制数是3231736443。
2.不要用select *
,而是要select具体的字段
反例
select * from employee;
正例
select id,name from employee;
原因 - 通过选择需要的字段,能够节约资源和减少网络开销
3.预先知道只有一条返回结果,推荐使用limit 1
反例
select id,name from employee where name='jay';
正例
select id,name from employee where name='jay' limit 1;
原因 - 通过加上limit 1
,当一条相关的记录被查询到时,数据库不会继续扫表,而是返回结果
4.在where
条件中避免使用or
以下面的user
表为例子,usedId
作为索引。
CREATE TABLE `user` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`userId` int(11) NOT NULL,
`age` int(11) NOT NULL,
`name` varchar(255) NOT NULL,
PRIMARY KEY (`id`),
KEY `idx_userId` (`userId`)
)
如果你想查询用户id为1或者,年龄为18的用户,你可能使用以下sql
语句。 反例
select * from user where userid = 1 or age = 18;
正例
select * from user where userid=1
union all
select * from user where age = 18;
// 或者使用两条独立的sql
select * from user where userid=1;
select * from user where age = 18;
原因 - or
的使用可能导致全表扫表,导致没有使用索引
5.优化limit分页
当使用limit
去分页的时候,offset
的值可能非常大,查询的效率就会下降。 反例
select id,name,age from employee limit 10000,10;
正例
// 方案1
select id,name from employee where id>10000 limit 10;
// 方案2
select id,name from employee order by id limit 10000,10;
原因 - 使用方案1,返回的是最后的查询记录,这里跳过了偏移,所以能提高查询效率 - 使用方案2,使用order by
和主键索引,也能提高查询效率
6.优化LIKE
语句
模糊查询的时候,如果不是前缀查询,会使索引失效。 反例
select userId,name from user where userId like '%Patrick';
正例
select userId,name from user where userId like 'Patrick%';
7.where
语句中避免使用!=
或<>
反例
select age,name from user where age <>18;
正例
select age,name from user where age > 18;
select age,name from user where age < 18;
- 原因 使用
!=
或者<>
有可能使索引失效
8.需要插入大量数据的时候,使用批量插入
反例
for(User u :list){
INSERT into user(name,age) values(#name#,#age#)
}
正例
// 500个插入,将插入语句拼接成一个sql
<foreach collection="list" item="item" index="index" separator=",">
(#{item.name},#{item.age})
</foreach>
原因 - 批量插入能节省每次插入数据库表的结构调整(例如索引等),从而节省时间
9.注意distinct
的使用
distinct
一般用来过滤重复的记录。当时查询单个或者少量的字段时,能够提高查询的效率。 但是,当对很多字段使用distinct
时,会降低查询的效率。 反例
SELECT DISTINCT * from user;
正例
select DISTINCT name from user;
原因 - 当对很多字段使用distinct
时,CPU需要花费大量的时间进行去重。
10.去掉冗余的索引
反例
KEY `idx_userId` (`userId`)
KEY `idx_userId_age` (`userId`,`age`)
正例
KEY `idx_userId_age` (`userId`,`age`)
原因 - 冗余的索引需要数据库进行维护,当优化器选择索引时,需要一个个地选择。
11.如果数据量太大,优化delete
语句
当删除大量的数据时,因为删除记录需要对表进行加锁。删除大量的数据,需要占用较多的时间,从而会导致其他事务处于等待锁的阶段,从而超时。 反例
// 一次删除1百万条记录
delete from user where id <100000;
// 在一个循环里面删除单条记录
for(User user:list){ delete from user; }
正例
// 批量删除,每次删除500条记录
delete product where id>=500 and id<1000;
12.不要使用NULL
,而是使用默认值,
反例
select * from user where age is not null;
正例
select * from user where age>0; // 将0作为默认值
原因 - MySQL中,NULL
会占用空间,并且MySQL对含有NULL
的列很难进行查询优化。
13.使用union all
替代union
反例
select * from user where userid=1
union
select * from user where age = 10
正例
select * from user where userid=1
union all
select * from user where age = 10
原因 - 使用union
, 在shuMySQL会对查询结果进行去重操作,而去重操作涉及到排序,这可能会影响性能 - 使用union all
没有对查询结果进行去重。如果确定查询结果没有重复的记录,可以使用union all
而不是union
14.使用explain
去分析你的sql语句
explain select * from user where userid = 10086 or age =18;
更多内容,可以访问我的博客。博客www.liangyaopei.com