SQL语句优化

最新推荐文章于 2024-08-07 20:46:27 发布

ghsticker

最新推荐文章于 2024-08-07 20:46:27 发布

阅读量350

点赞数

分类专栏：数据库文章标签：数据库 sql 优化

本文链接：https://blog.csdn.net/Balance_1/article/details/88823390

版权

数据库专栏收录该内容

1 篇文章 0 订阅

订阅专栏

SQL语句优化

1、函数Max()的优化

用途：查询最后支付时间-优化max（）函数

通过explain查询分析SQL的执行计划

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

可以看到显示的执行计划，并不是很高效，可以拖慢服务器的效率，如何优化了？

添加索引：

mysql> create index inx_paydate on payment(payment_date);

mysql> explain select max(payment_date) from payment\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: NULL
         type: NULL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: NULL
        Extra: Select tables optimized away
1 row in set (0.00 sec)

索引是顺序操作，不需要扫描表，执行效率比较恒定。

2、函数Count()的优化

需求：在一条SQL中同时查处2006年和2007年电影的数量

mysql> select count(release_year='2006' or release_year='2007') from film;
+---------------------------------------------------+
| count(release_year='2006' or release_year='2007') |
+---------------------------------------------------+
|                                              1000 |
+---------------------------------------------------+
1 row in set (0.00 sec)

这样不能查出2006年和2007年分别的数量；

mysql> select count(*) from film where release_year='2006' or release_year='2007';
+----------+
| count(*) |
+----------+
|     1000 |
+----------+
1 row in set (0.00 sec)

这样也不可以：

正确做法：

mysql> select count(release_year='2006' or null) as '06films',count(release_year='2007' or null) as '07films' from film;
+---------+---------+
| 06films | 07films |
+---------+---------+
|    1000 |       0 |
+---------+---------+
1 row in set (0.00 sec)

为什么count中要加or null

举个栗子：

mysql> create table t(id int);
Query OK, 0 rows affected (0.72 sec)


mysql>  insert into t values(1),(2),(null);
Query OK, 3 rows affected (0.01 sec)
Records: 3  Duplicates: 0  Warnings: 0

count(*):

mysql> select count(*)from t;
+----------+
| count(*) |
+----------+
|        3 |
+----------+

count(id):

mys ql> select count(id) from t;
+-----------+
| count(id) |
+-----------+
|         2 |
+-----------+
1 row in set (0.00 sec)

区别: count(*)：包括null

count(id)：不包括null

3、子查询的优化

子查询是我们在开发过程中经常使用的一种方式，在通常情况下，需要把子查询优化为join查询但在优化是需要注意关联键是否有一对多的关系，要注意重复数据。

查看我们所创建的t表

mysql> show create table t;
+-------+---------------------------------------------------------------------------------------+
| Table | Create Table                                                                          |
+-------+---------------------------------------------------------------------------------------+
| t     | CREATE TABLE `t` (
  `id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1 |
+-------+---------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> create table t1(tid int);
Query OK, 0 rows affected (0.19 sec)

创建t1表：

添加一条信息：

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

我们要进行一个子查询，需求：查询t表中id在t1表中tid的所有数据；

mysql> select * from t where t.id in (select t1.tid from t1);
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

接下来我们用join的操作来进行操作:

mysql> select id from t join t1 on t.id = t1.tid;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

通过上面结果来看，查询的结果是一致的，我们就将子查询的方式优化为join操作。

接下来，我们在t1表中再插入一条数据:

mysql> insert into t1 values (1);
Query OK, 1 row affected (0.00 sec)

在这种情况下，如果我们使用子查询方式进行查询，返回的结果就是:

mysql> select * from t where id in (select tid from t1);
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

如果使用join方式进行查找:

mysql> select id from t join t1 on t.id = t1.tid;
+------+
| id   |
+------+
|    1 |
|    1 |
+------+
2 rows in set (0.00 sec)

在这种情况下出现了一对多的关系，会出现数据的重复，我们为了方式数据重复，不得不使用distinct关键词进行去重操作

mysql> select distinct id from t join t1 on t.id = t1.tid;
+------+
| id   |
+------+
|    1 |
+------+
1 row in set (0.00 sec)

这个一对多的关系是我们开发过程中遇到的一个坑，出现数据重复.

4、group by的优化

最好使用同一表中的列，

需求：每个演员所参演影片的数量-（影片表和演员表）

mysql> explain select actor.first_name,actor.last_name,count(*) from sakila.film_actor inner join sakila.actor using(actor_id) group by film_actor.actor_id\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 200
        Extra: Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: film_actor
         type: ref
possible_keys: PRIMARY,idx_fk_film_id
          key: PRIMARY
      key_len: 2
          ref: sakila.actor.actor_id
         rows: 13
        Extra: Using index
2 rows in set (0.00 sec)

优化sql

mysql> explain select actor.first_name,actor.last_name,c.cnt from sakila.actor inner join ( select actor_id,count(*) as cnt from sakila.film_actor group by actor_id )as c using(actor_id)\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: actor
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 200
        Extra: NULL
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
         type: ref
possible_keys: <auto_key0>
          key: <auto_key0>
      key_len: 2
          ref: sakila.actor.actor_id
         rows: 27
        Extra: NULL
*************************** 3. row ***************************
           id: 2
  select_type: DERIVED
        table: film_actor
         type: index
possible_keys: PRIMARY,idx_fk_film_id
          key: PRIMARY
      key_len: 4
          ref: NULL
         rows: 5462
        Extra: Using index
3 rows in set (0.00 sec)

说明：从上面的执行计划来看，这种优化后的方式没有使用临时文件和文件排序的方式了，取而代之的是使用了索引。查询效率老高了。

这个时候我们表中的数据比较大，会大量的占用IO操作，优化了sql执行的效率，节省了服务器的资源，因此我们就需要优化。

注意：

1、mysql 中using关键词的作用：也就是说要使用using,那么表a和表b必须要有相同的列。

2、在用Join进行多表联合查询时，我们通常使用On来建立两个表的关系。其实还有一个更方便的关键字，那就是Using。

3、如果两个表的关联字段名是一样的，就可以使用Using来建立关系，简洁明了。

5、Limit查询的优化

Limit常用于分页处理，时长会伴随order by从句使用，因此大多时候回使用Filesorts这样会造成大量的IO问题。

例子：

需求：查询影片id和描述信息，并根据主题进行排序，取出从序号50条开始的5条数据。

mysql> select film_id,description from sakila.film order by title limit 50,5;
+---------+---------------------------------------------------------------------------------------------------------------------------------+
| film_id | description                                                                                                                     |
+---------+---------------------------------------------------------------------------------------------------------------------------------+
|      51 | A Insightful Panorama of a Forensic Psychologist And a Mad Cow who must Build a Mad Scientist in The First Manned Space Station |
|      52 | A Thrilling Documentary of a Composer And a Monkey who must Find a Feminist in California                                       |
|      53 | A Epic Drama of a Madman And a Cat who must Face a A Shark in An Abandoned Amusement Park                                       |
|      54 | A Awe-Inspiring Drama of a Car And a Pastry Chef who must Chase a Crocodile in The First Manned Space Station                   |
|      55 | A Awe-Inspiring Story of a Feminist And a Cat who must Conquer a Dog in A Monastery                                             |
+---------+---------------------------------------------------------------------------------------------------------------------------------+
5 rows in set (0.01 sec)

看一下执行计划：

mysql> explain select film_id,description from sakila.film order by title limit 50,5\G                                    *************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 1000
        Extra: Using filesort
1 row in set (0.00 sec)

使用的是Using filesort，执行效率过低

优化步骤1：

使用有索引的列或主键进行order by操作，因为大家知道，innodb是按照主键的逻辑顺序进行排序的。可以避免很多的IO操作。

mysql> explain select film_id,description from sakila.film order by film_id limit 50,5\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 2
          ref: NULL
         rows: 55
        Extra: NULL
1 row in set (0.00 sec)

那如果我们获取从500行开始的5条记录，执行计划又是什么样的了？

mysql> explain select film_id,description from sakila.film order by film_id limit 500,5\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film
         type: index
possible_keys: NULL
          key: PRIMARY
      key_len: 2
          ref: NULL
         rows: 505
        Extra: NULL
1 row in set (0.00 sec)

随着我们翻页越往后，IO操作会越来越大的，如果一个表有几千万行数据，翻页越后面，会越来越慢，因此我们要进一步的来优化。

优化步骤2：

记录上次返回的主键，在下次查询时使用主键过滤。（说明：避免了数据量大时扫描过多的记录）

上次limit是50,5的操作，因此我们在这次优化过程需要使用上次的索引记录值，

mysql> explain select film_id,description from sakila.film  where film_id >500 and film_id<=505 order by film_id limit 500,5\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: film
         type: range
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 2
          ref: NULL
         rows: 5
        Extra: Using where
1 row in set (0.00 sec)