MySQL SQL优化方法是什么
发布时间：2021-12-17 11:12:04 所属栏目：MySql教程 来源：互联网
导读：这篇文章主要讲解了MySQL SQL优化方法是什么，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习MySQL SQL优化方法是什么吧！ slowlog里查到一条sql执行了10秒钟以上 # Time: 2017-03-02T18:41:50.201376+08:00
|
这篇文章主要讲解了“MySQL SQL优化方法是什么”，文中的讲解内容简单清晰，易于学习与理解，下面请大家跟着小编的思路慢慢深入，一起来研究和学习“MySQL SQL优化方法是什么”吧！ slowlog里查到一条sql执行了10秒钟以上 # Time: 2017-03-02T18:41:50.201376+08:00 # User@Host: oms_readonly[oms_readonly] @ [10.xx.xx.xx] Id: 54124620 # Query_time: 11.005894 Lock_time: 0.000223 Rows_sent: 20 Rows_examined: 1838114 SET timestamp=1488451310; select * from customers where `type` = 1 AND `status` < 7 AND `isarea` = 6 AND `into_time`>='2016-12-01'AND `into_time`<='2017-01-02 23:59:59' order by score desc limit 40,20; 查看执行计划 mysql> explain select * from customers where `type` = 1 AND `status` < 7 AND `isarea` = 6 AND `into_time`>='2016-12-01'AND `into_time`<='2017-01-02 23:59:59' order by score desc limit 40,20; +----+-------------+-----------+------------+-------+-----------------------------------------------------------------+-------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+-------+-----------------------------------------------------------------+-------+---------+------+------+----------+-------------+ | 1 | SIMPLE | customers | NULL | index | type,into_time,isarea,status,idx_isarea_renew_owner,type_status | score | 2 | NULL | 603 | 0.21 | Using where | +----+-------------+-----------+------------+-------+-----------------------------------------------------------------+-------+---------+------+------+----------+-------------+ 1 row in set, 1 warning (0.00 sec) 看下列的选择性，很明显isarea列的选择性更好 mysql> select count(*) from customers where `type` = 1; +----------+ | count(*) | +----------+ | 2373175 | +----------+ 1 row in set (1.53 sec) mysql> select count(*) from customers where `status` < 7; -> ; +----------+ | count(*) | +----------+ | 2494232 | +----------+ 1 row in set (3.16 sec) mysql> select count(*) from customers where `isarea` = 6; +----------+ | count(*) | +----------+ | 135347 | +----------+ 1 row in set (0.15 sec) mysql> select count(*) from customers where `into_time`>='2016-12-01'AND `into_time`<='2017-01-02 23:59:59'; +----------+ | count(*) | +----------+ | 106158 | +----------+ 1 row in set (0.12 sec) isarea有索引，强制用这个列的索引 mysql> explain select * from customers use index(isarea) where `type` = 1 AND `status` < 7 AND `isarea` = 6 AND `into_time`>='2016-12-01'AND `into_time`<='2017-01-02 23:59:59' order by score desc limit 40,20; +----+-------------+-----------+------------+------+---------------+--------+---------+-------+--------+----------+----------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-----------+------------+------+---------------+--------+---------+-------+--------+----------+----------------------------------------------------+ | 1 | SIMPLE | customers | NULL | ref | isarea | isarea | 5 | const | 249438 | 1.85 | Using index condition; Using where; Using filesort | +----+-------------+-----------+------------+------+---------------+--------+---------+-------+--------+----------+----------------------------------------------------+ 1 row in set, 1 warning (0.00 sec) 优化前10几秒出结果，优化后0.5秒出结果。 感谢各位的阅读，以上就是“MySQL SQL优化方法是什么”的内容了，经过本文的学习后，相信大家对MySQL SQL优化方法是什么这一问题有了更深刻的体会，具体使用情况还需要大家实践验证。这里是亿速云，小编将为大家推送更多相关知识点的文章，欢迎关注！ （编辑：成都站长网） 【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容! |
站长推荐