如何优化MySQL查询语句

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;