MySQL查询语句的优化

最新推荐文章于 2024-07-24 17:50:00 发布

说文科技

最新推荐文章于 2024-07-24 17:50:00 发布

阅读量1.9k

点赞数 1

分类专栏： Mysql

喜欢文章？请私信联系作者。

本文链接：https://blog.csdn.net/liu16659/article/details/80634318

版权

Mysql 专栏收录该内容

122 篇文章 6 订阅

订阅专栏

MySQL语句的优化

1.使用explain命令

mysql> explain select * from student\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: student
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 6
        Extra: NULL
1 row in set (0.00 sec)

table：显示这一行的数据是哪个表
type：重要列，显示连接使用了何种类型？等级为：const,eq_reg,ref,range,index,all(表扫描类型)
key：实际使用的索引
key_len：使用索引的长度索引的长度越小越好
ref：显示索引的哪一列被使用了
rows：【？】
Using filesort 与Using temporary 看到上述两个时，就需要优化查询。

2.查询优化

2.1 索引优化

Count()和Max()的优化方法
explain select max(payment_date) from payment \G

//建立索引来优化查询
//索引是顺序排列
//覆盖索引
create index idx_paydate on payment(payment_date)
explain select max(payment_date) from payment \G

create table t(id int)
count(*)具体的行数 包括null行
count(id)不包括null行

select 
count(release_year='2006' or null) as '2006 filmNumber',
count(release_year='2007' or null) as '2007 filmNumber' 
from film;

如何选择合适的列建立索引？

1.在where、group by 、order by 、on从句出现的列
2.索引字段越小越好
3.离散度大的列放到联合索引的前面
4.不要把主键建成索引

2.2 子查询的优化：

通常需要把子查询优化为join查询，但在优化时，要注意是否存在一对多的关系。

create table t1(tid int);
insert into t1 values(1);

子查询如下：
查询t的id值在t1中相同的

select * 
from t 
where t.id in 
(select t1.tid from t1);

转换成使用连接查询，如下：

select t.id 
from t 
join t1 on t.id = t1.tid;

select distinct t.id
from t 
join t1 on t.id = t1.tid;

2.3 优化group by查询

2.4 优化Limit查询

limit常用于分页处理，时常会伴随order by 从句使用。
如果对于一张很大的表来说，全部查询这个数据则会很浪费时间，而且会占用内存。结合top-k的思想，我们可以只需要返回部分数据就好了。
Mysql：使用limit关键字来设定返回的记录数，limit 1,3返回第1条开始的3条数据【下标从0开始】
SQL server：使用top关键字

step1:使用有索引的主键排序
step2:记录上次返回的主键，在下次查询时使用主键过滤避免过多的扫描

select film_id,description 
from sakila.film 
order by film_id limit 50,5;

3.实战案例

先查看表结构及索引

mysql> show index from tie_shop_device_day;
+---------------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| Table               | Non_unique | Key_name     | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment |
+---------------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
| tie_shop_device_day |          1 | idx_tieday   |            1 | tie_day     | A         |         150 |     NULL | NULL   |      | BTREE      |         |               |
| tie_shop_device_day |          1 | idx_shopid   |            1 | shop_id     | A         |    31228358 |     NULL | NULL   |      | BTREE      |         |               |
| tie_shop_device_day |          1 | idx_deviceid |            1 | device_id   | A         |    86981768 |     NULL | NULL   |      | BTREE      |         |               |
+---------------------+------------+--------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
3 rows in set (0.01 sec)

mysql> desc tie_shop_device_day;
+-----------+-------------+------+-----+---------+-------+
| Field     | Type        | Null | Key | Default | Extra |
+-----------+-------------+------+-----+---------+-------+
| tie_day   | date        | NO   | MUL | NULL    |       |
| shop_id   | bigint(20)  | NO   | MUL | NULL    |       |
| device_id | varchar(64) | NO   | MUL | NULL    |       |
+-----------+-------------+------+-----+---------+-------+
3 rows in set (0.01 sec)

解析某查询

explain	
select
count(*)
from  tie_shop_device_day   cur
left join tie_shop_device_day  nt
  on  nt.tie_day =  DATE_ADD(cur.tie_day, INTERVAL 1 day)  
  and cur.device_id = nt.device_id
where nt.device_id is null
and cur.tie_day = '2018-05-02';

得到结果如下：

mysql> explain select count(*) from  tie_shop_device_day   cur left join tie_shop_device_day  nt   on  nt.tie_day =  DATE_ADD(cur.tie_day, INTERVAL 1 day)     and cur.device_id = nt.device_id where nt.device_id is null and cur.tie_day = '2018-05-02'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: cur
   partitions: part_20180502
         type: ref
possible_keys: idx_tieday
          key: idx_tieday
      key_len: 3
          ref: const
         rows: 204599
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: nt
   partitions: part_init,part_20180501,part_20180502,part_20180503,part_20180504,part_20180505,part_20180506,part_20180507,part_20180508,part_20180509,part_20180510,part_20180511,part_20180512,part_20180513,part_20180514,part_20180515,part_20180516,part_20180517,part_20180518,part_20180519,part_20180520,part_20180521,part_20180522,part_20180523,part_20180524,part_20180525,part_20180526,part_20180527,part_20180528,part_20180529,part_20180530,part_20180531,part_20180601,part_20180602,part_20180603,part_20180604,part_20180605,part_20180606,part_20180607,part_20180608,part_20180609,part_20180610,part_20180611,part_20180612,part_20180613,part_20180614,part_20180615,part_20180616,part_20180617,part_20180618,part_20180619,part_20180620,part_20180621,part_20180622,part_20180623,part_20180624,part_20180625,part_20180626,part_20180627,part_20180628,part_20180629,part_20180630,part_20180701,part_20180702,part_20180703,part_20180704,part_20180705,part_20180706,part_20180707,part_20180708,part_20180709,part_20180710,part_20180711,part_20180712,part_20180713,part_20180714,part_20180715,part_20180716,part_20180717,part_20180718,part_20180719,part_20180720,part_20180721,part_20180722,part_20180723,part_20180724,part_20180725,part_20180726,part_20180727,part_20180728,part_20180729,part_20180730,part_20180731,part_20180801,part_20180802,part_20180803,part_20180804,part_20180805,part_20180806,part_20180807,part_20180808,part_20180809,part_20180810,part_20180811,part_20180812,part_20180813,part_20180814,part_20180815,part_20180816,part_20180817,part_20180818,part_20180819,part_20180820,part_20180821,part_20180822,part_20180823,part_20180824,part_20180825,part_20180826,part_20180827,part_20180828,part_20180829,part_20180830,part_20180831,part_20180901,part_20180902,part_20180903,part_20180904,part_20180905,part_20180906,part_20180907,part_20180908,part_20180909,part_20180910,part_20180911,part_20180912,part_20180913,part_20180914,part_20180915,part_20180916,part_20180917,part_20180918,part_20180919,part_20180920,part_20180921,part_20180922,part_20180923,part_20180924,part_20180925,part_20180926,part_20180927,part_20180928,part_20180929,part_20180930,part_20181001,part_20181002,part_20181003,part_20181004,part_20181005,part_20181006,part_20181007,part_20181008,part_20181009,part_20181010,part_20181011,part_20181012,part_20181013,part_20181014,part_20181015,part_20181016,part_20181017,part_20181018,part_20181019,part_20181020,part_20181021,part_20181022,part_20181023,part_20181024,part_20181025,part_20181026,part_20181027,part_20181028,part_20181029,part_20181030,part_20181031
         type: ref
possible_keys: idx_tieday,idx_deviceid
          key: idx_deviceid
      key_len: 258
          ref: stagedb.cur.device_id
         rows: 1
     filtered: 100.00
        Extra: Using where; Not exists
2 rows in set, 1 warning (0.00 sec)

但是如果把上述的SQL换成如下的样子，再次执行可以得到如下的解析结果：

mysql> explain
    -> select
    -> count(*)
    -> from  tie_shop_device_day   cur
    -> left join tie_shop_device_day  nt
    ->   on  nt.tie_day =  DATE_ADD('2018-05-02', INTERVAL 1 day)  
    ->   and cur.device_id = nt.device_id
    -> where nt.device_id is null
    -> and cur.tie_day = '2018-05-02'\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: cur
   partitions: part_20180502
         type: ref
possible_keys: idx_tieday
          key: idx_tieday
      key_len: 3
          ref: const
         rows: 204599
     filtered: 100.00
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: nt
   partitions: part_20180503
         type: ref
possible_keys: idx_tieday,idx_deviceid
          key: idx_deviceid
      key_len: 258
          ref: stagedb.cur.device_id
         rows: 1
     filtered: 100.00
        Extra: Using where; Not exists
2 rows in set, 1 warning (0.01 sec)

可以看到这次仅仅只检索了一个分区，并且使用了索引，较上一个SQL，查询速度提升很多倍。
根据上两个SQL可以看出：on nt.tie_day = DATE_ADD(cur.tie_day, INTERVAL 1 day) 与on nt.tie_day = DATE_ADD('2018-05-01', INTERVAL 1 day)两个SQL有很大的性能差异。这是因为mysql解析会把cur.tie_day当成一个泛型值【遍历所有分区】，而’2018-05-01’（或者某个具有值的变量）是一个具体值【只拿一个分区】。