MySQL性能优化实战

  1. 云栖社区>
  2. 博客>
  3. 正文

MySQL性能优化实战

zxiaofan 2016-02-05 11:11:00 浏览1861
展开阅读全文
一、MySQL优化原则:
where子句哪些操作将导致MySQL引擎放弃索引而进行全表扫描?
  • 对字段进行where num is null判断。【给num设置默认值0】
  • 使用!=或<>。
  • 使用or连接条件,如:where num=10 or num=20【select……union all select……替代】
  • 使用in或not in,如:where num in(1,2,3)连续数值可用where num between 1 and 3替代
  • like‘李%’【考虑全文检索】
  • 使用参数

1、【只要一行数据】或【只需要确认是否包含该数据】: LIMIT 1
        查询时,你已经知道结果只会有一条,但因为可能需要去fetch游标,或是你会去检查返回的记录数。在这种情况下,加上 LIMIT 1 可以增加性能。这样,MySQL数据库引擎会在找到一条数据后停止搜索,而不是继续往后查少下一条符合记录的数据。
测试数据:
    MySQL自带数据库:sakila库rental表(数据量16W+),待查询数据在10467行。

SELECT FROM rental WHERE rental_date ='2005-08-01 09:45:58' # 耗时0.018s

LIMIT 1 # 耗时0.002s

;

2、为搜索字段建索引
        索引也有消耗,性别无需索引。
3、避免select * 
        读出数据越多,查询就越慢。如果数据库和web服务器独立,还会增加网络传输的负载。请求所有列再丢掉不需要的列?减少表结构的影响。

4、不要 ORDER BY RAND() 
    【随机挑选数据】【打乱返回数据】
看着很方便,但会让你的数据库性能指数级下降(MySQL不得不执行RAND()函数,耗CPU时间)。

SELECT rental_id FROM rental ORDER BY RAND() LIMIT 1;

这条要求似乎是针对就数据库,在MySQL 5.7.10测试时,仅第一次耗时达到几百毫秒,随后都是几毫秒。

5、用 ENUM 而不是 VARCHAR 
        ENUM 类型是非常快和紧凑的。在实际上,其保存的是 TINYINT,但其外表上显示为字符串。如果你有一个字段,比如“性别”,“国家”,“民族”,“状态”或“部门”,你知道这些字段的取值是有限而且固定的,那么,你应该使用 ENUM 而不是 VARCHAR。
测试数据:
    MySQL自带数据库:world库country表(暂略)

6、尽可能使用not null
①null是空值吗?不是,空值不占用空间,而数据库里的null占用空间,数据库里任何数跟NULL进行运算都是NULL, 判断值是否等于NULL,不能简单用=,而要用IS NULL关键字。

7、快速删除表
    先 truncate table ,然后 drop table ,这样可以避免系统表的较长时间锁定。

8、拒绝大SQL
        一条SQL叧能在一个CPU运算,5000+ QPS(每秒查询率Query Per Second)的高幵发中,1秒大SQL可能一条大SQL就把整个数据库堵死。拆解成多条简单SQL,简单SQL缓存命中率更高,减少锁表时间,特别是MyISAM,用上多CPU。

9、范围查找
同一字段:in代替or
or效率:O(n);in效率:O(Log n),建议n小于200。

select*FROM rental WHERE return_date='2005-08-04 10:49:24'or return_date='2005-08-08 06:46:39' # 8ms

select*FROM rental WHERE return_date IN('2005-08-04 10:49:24','2005-08-08 06:46:39');  # 7ms

不同字段:union代替or

select*FROM rental WHERE return_date='2005-08-04 10:49:24' or inventory_id=3157

select*FROM rental WHERE return_date='2005-08-04 10:49:24' UNION SELECT FROM rental WHERE inventory_id=3157


10、limit高效分页
Select * from table limit 10000,10;
改为:
Select * from table WHERE id>=23423 limit 11; #10+1 (每页10条) 

11、union all而不是union
        无需对结果进行去重,则用UNION ALL,UNION 要去重,有开销。【此处效果不是很明显,或许数据量不够16W+】

Select SQL_NO_CACHE *from study.rentalUNION SELECT FROM sakila.rental#0.74s

Select SQL_NO_CACHE *from study.rental UNION ALL SELECT FROM sakila.rental#0.72s

12、同类型比较
        数字比数字,字符比字符。
数值比字符:同时转为双精度再比对;字符比数值:字符整列转数值,无法使用索引。


参考资料:

提高mysql千万级大数据SQL查询优化30条经验(Mysql索引优化注意)


网友评论

登录后评论
0/500
评论
zxiaofan
+ 关注