开发中高质量MySQL技巧

    总结开发中MySQL提升性能的一些小技巧。

下面例子参考这张表

create table student(
id int(11) not null AUTO_INCREMENT,
user_id int(11) not null,
name varhcar(255) default null,
age int(3) default null,
date datetime default null,
set int(1) default null,
primary key(id)
KEY idx_userId(userId)
)ENGINE=InnoDB DEFAULT CHARSET=utf8;
复制

一、查询中尽量用select 字段 代替 select *

   反例：

select * from student; 
复制

   正例：

selec id,name from student;
复制

解析：

• 只取需要的字段，节省资源、减少网络开销；

• select * 进行查询时，很可能就不会使用到覆盖索引了，就会造成回表查询。

二、如果知道查询结果条数，查询语句加上limit

反例：

select id,name from student where name='azb';
复制

    正例：

select id,name from student where name ='azb' limit 1;
复制

解析：

• 加上limit 1后,只要找到了对应的一条记录,就不会继续向下扫描了,效率将会大大提高；

• 当然，如果name是唯一索引的话，是不必要加上limit 1了，因为limit的存在主要就是为了防止全表扫描，从而提高性能,如果一个语句本身可以预知不用全表扫描，有没有limit ，性能的差别并不大。

三、where后尽量避免or

    反例：

select id,name from student where user_id=1 and name='azb';
复制

正例

select id,name from student where user_id=1
union all
select id,name from student where name = 'azb';
复制

或

select id,name from student where user_id=1;
select id,name from student where name = 'azb';
复制

解析：

    使用or可能会导致索引失效，导致全表扫描

对于or+没有索引的name这种情况，假设它走了user_id的索引，但是走到name查询条件时，它还得全表扫描，也就是需要三步过程：全表扫描+索引扫描+合并。

如果它一开始就走全表扫描，直接一遍扫描就完事。mysql是有优化器的，处于效率与成本考虑，遇到or条件，索引可能失效，看起来也合情合理。

四、优化limit分页查询

    当我们用limit做分页查询时，当偏移量过大时，要采取优化措施。

反例：

select id,name from student limit 20000,8;
复制

正例1：返回上次查询的最大记录(偏移量)

select id,name from student where id>20000 limit 8;
复制

正例2：order by+索引

select id,name from student order by id limit 20000,8;
复制

正例3：在业务允许的情况下，限制页面数量。

解析：

• 当偏移量最大的时候，查询效率就会越低，因为Mysql并非是跳过偏移量直接去取后面的数据，而是先把偏移量+要取的条数，然后再把前面偏移量这一段的数据抛弃掉再返回的；

• 如果使用优化方案一，返回上次最大查询记录（偏移量），这样可以跳过偏移量，效率提升不少；

• 方案二使用order by+索引，也是可以提高查询效率的；

• 方案三的话，建议跟业务讨论，有没有必要查这么后的分页啦。因为绝大多数用户都不会往后翻太多页。

五、like语句的优化

反例：

select user_id,age from student where user_id like '%888';
复制

正例：

select user_id,age from student where user_id like '888%';
复制

解析：‘%’在前面，不走索引，放后面，走索引，所以开发时注意like用法，尽量不要导致索引失效。

六、索引上尽量不要使用内置函数，这样会导致索引失效。

七、不要where子句中对字段做表达式操作，这样会导致索引失效

反例：

select user_id,name from student where user_id-1 = 99;
复制

八、where子句尽量不要使用<>或!=，这样会导致索引失效。

九、left join,right join,inner join中尽量使用inner join ，如果使用left join,左边结果表尽量小。

解析：

• 如果inner join是等值连接，或许返回的行数比较少，所以性能相对会好一点；

• 同理，使用了左连接，左边表数据结果尽量小，条件尽量放到左边处理，意味着返回的行数可能比较少。

十、在使用联合索引时，要按顺序，一般是左匹配原则

上述示例表加入联合索引

KEY idx_userid_age (user_id,age) USING BTREE
复制

反例：

select user_id,name from student where age=22;
复制

正例一：

select user_id,name from student where user_id=111 and age=22;
复制

正例二：

select user_id,name from student where user_id=111;
复制

解析：

• 当我们创建一个联合索引的时候，如(k1,k2,k3)，相当于创建了（k1）、(k1,k2)和(k1,k2,k3)三个索引，这就是最左匹配原则；

• 联合索引不满足最左原则，索引一般会失效，但是这个还跟Mysql优化器有关的。

十一、数据量大时，可以where或order by涉及的字段上加索引。提高查询效率。

十二、distinct

解析：带distinct的语句cpu时间和占用时间都高于不带distinct的语句。因为当查询很多字段时，如果使用distinct，数据库引擎就会对数据进行比较，过滤掉重复数据，然而这个比较、过滤的过程会占用系统资源，cpu时间。

十三、删除重复冗余索引

十四、优化大数据量的删除和修改语句。

    同时删除和修改大量的数据，会导致cpu负载过高，影响他人对数据的访问。

    反例：

delete from sutdent where user_id <888888;
复制

正例一：

//循环删除。放缓时间
for（Student student：list）{
   delete from student；
}
复制

正例二：

//分批进行删除
delete student where id<500 
delete student where id>=500 and id<1000；
复制

解析：一次性删除太多数据，可能会有lock wait timeout exceed的错误，所以建议分批操作。

十五、索引尽量不要建在有大量重复数据的字段上。

解析：因为SQL优化器是根据表中数据量来进行查询优化的，如果索引列有大量重复数据，Mysql查询优化器推算发现不走索引的成本更低，很可能就放弃索引了。