SQL优化 1

最新推荐文章于 2023-05-30 10:01:43 发布

mysql_leon

最新推荐文章于 2023-05-30 10:01:43 发布

阅读量294

点赞数

文章标签： sql 优化 null user download collation

本文链接：https://blog.csdn.net/mysql_leon/article/details/2303784

版权

一个很简单的SQL：

select * from point_site_download_queue where status = 0 order by id asc limit 1;

select * from point_site_download_queue where status = 0 order by id asc limit 1;

表 user

+-------------+--------------+------+-----+-------------------+----------------+
| Field       | Type         | Null | Key | Default           | Extra          |
+-------------+--------------+------+-----+-------------------+----------------+
| id          | int(11)      | NO   | PRI | NULL              | auto_increment |
| name        | varchar(40)  | YES  |     | NULL              |                |
| email       | varchar(255) | YES  |     | NULL              |                |
| address     | varchar(255) | YES  |     | NULL              |                |
| age         | int(11)      | YES  | MUL | NULL              |                |
| regist_time | timestamp    | NO   | MUL | CURRENT_TIMESTAMP |                |
+-------------+--------------+------+-----+-------------------+----------------+

index

+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| user  |          0 | PRIMARY  |            1 | id          | A         |     2260431 |     NULL | NULL   |      | BTREE      |         |
| user  |          1 | usertime |            1 | regist_time | A         |          18 |     NULL | NULL   |      | BTREE      |         |
| user  |          1 | userae   |            1 | age         | A         |          18 |     NULL | NULL   | YES  | BTREE      |         |
| user  |          1 | userae   |            2 | email       | A         |          18 |     NULL | NULL   | YES  | BTREE      |         |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

表中有200万数据。执行

select * from user where address = 'ttt' order by id asc limit 1;

select * from user where address = 'ttt' order by id asc limit 1;

select * from user where regist_time = '2007-03-01 13:50:45' order by id asc limit 1;

select * from user  where regist_time = '2007-03-01 13:50:45' order by id asc limit 1;

+----+-------------+-------+------+---------------+----------+---------+-------+------+-----------------------------+
| id | select_type | table | type | possible_keys | key      | key_len | ref   | rows | Extra                       |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-----------------------------+
|  1 | SIMPLE      | user  | ref  | usertime      | usertime | 4       | const |    1 | Using where; Using filesort |
+----+-------------+-------+------+---------------+----------+---------+-------+------+-----------------------------+

用时0秒，这里用到了regist_time上的索引。但是否有索引就一定好？再看这个

select * from user where regist_time = '2007-02-25 17:36:22' order by id asc limit 1;

select * from user  where regist_time = '2007-02-25 17:36:22' order by id asc limit 1;

select * from user ignore key(usertime) where regist_time = '2007-02-25 17:36:22' order by id asc limit 1;

select * from user ignore key(usertime)  where regist_time = '2007-02-25 17:36:22' order by id asc limit 1;

+----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows    | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+
|  1 | SIMPLE      | user  | index | NULL          | PRIMARY | 4       | NULL | 2260431 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+---------+-------------+

用时0秒。这是因为

select count(*), regist_time from user group by regist_time;

 select count(*), regist_time from user group by regist_time;

+----------+---------------------+
| count(*) | regist_time         |
+----------+---------------------+
|   592318 | 2007-02-25 17:20:21 |
|   592318 | 2007-02-25 17:32:28 |
|   592319 | 2007-02-25 17:36:22 |
|        1 | 2007-03-01 13:50:45 |
|   483097 | 2007-03-01 17:09:17 |
+----------+---------------------+

为2007-02-25 17:36:22的值很多，用索引全部选出来后再按id排序会比较慢。而regist_time为这个值的记录id很靠前，从id检索就可以很快找到。因此SQL的优化需要考虑数据的分布。此例中如果时间是2007-03-01 17:09:17，即多其对应的id又不靠前，那么使用这样的SQL会比较好。

select * from user where id = (select min(id) from user regist_time = '2007-03-01 17:09:17');

select * from user  where id  = (select min(id) from user regist_time = '2007-03-01 17:09:17');

+----+-------------+-------+-------+---------------+----------+---------+-------+--------+-------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref   | rows   | Extra       |
+----+-------------+-------+-------+---------------+----------+---------+-------+--------+-------------+
|  1 | PRIMARY     | user  | const | PRIMARY       | PRIMARY  | 4       | const |      1 |             |
|  2 | SUBQUERY    | user  | ref   | usertime      | usertime | 4       |       | 759200 | Using index |
+----+-------------+-------+-------+---------------+----------+---------+-------+--------+-------------+

当然对于本例，如果能添加索引(regist_time,id)，则速度是最好的，缺点是索引比较大。

在最前面的SQL中，根据业务情况，status为0的值比较少，而且其id总是最大的几个，所以用索引会比较好。给status加上索引后，用下面两个SQL性能都会提高。

select * from point_site_download_queue where status = 0 order by id asc limit 1;
select * from point_site_download_queue where id = (select min(id) from user where status = 0);