MySQL SQL查询语句的用法和优化

摘要转载自 「程序员考拉」 ( mp.weixin.qq.com ) By 程序员考拉

预计阅读时间 0 分钟(共 0 个字, 0 张图片, 0 个链接)


一、概述


MySQL的性能优化可以从机器硬件,如磁盘,内存等;MySQL服务器配置,如线程数,查询缓存等;MySQL的主从分离和分库分表等;SQL语句优化等。其中SQL语句优化是与日常开发密切相关的,而且也是MySQL优化中最重要的一个环节,因为MySQL服务器,机器等的资源是一定的,故当出现性能瓶颈时,首先需要排除是否为SQL执行问题,如通过开启MySQL慢日志统计执行慢的SQL,或者使用profile功能统计SQL执行涉及的CPU,内存,IO等资源开销。


定位到存在性能问题的SQL之后,则可以通过explain命令来分析该SQL的执行情况,如索引使用,排序等,然后是针对该SQL进行优化,优化主要从查询涉及的表,WHERE条件与是否使用和高效使用了索引,以及是否存在子查询等方面展开。


要进行SQL优化,首先需要理解SQL的执行过程,具体可以参考:SQL解析顺序与MySQL底层实现


以下分析以用户表t_user和用户订单表t_order来分析:在t_order表的user_id列是引用t_user的id列的外键。订单表和订单清单条目表t_order_item,通过order_id来建立外键约束。


mysql> show create table t_user;
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t_user | CREATE TABLE `t_user` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(64) NOT NULL,
  `password` varchar(64) NOT NULL,
  `email` varchar(64) NOT NULL,
  `phone` varchar(32) DEFAULT NULL,
  PRIMARY KEY (`id`),
  UNIQUE KEY `name` (`name`),
  UNIQUE KEY `email` (`email`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 |
+--------+------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table t_order;
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t_order | CREATE TABLE `t_order` (
  `order_id` int(11) NOT NULL AUTO_INCREMENT,
  `user_id` int(11) NOT NULL,
  `cost` double DEFAULT NULL,
  `buy_date` date NOT NULL,
  PRIMARY KEY (`order_id`),
  KEY `idx_order_buy_date` (`order_id`,`buy_date`),
  KEY `idx_user_id` (`user_id`),
  KEY `idx_user_id_buy_date` (`user_id`,`buy_date`),
  CONSTRAINT `user_refrence` FOREIGN KEY (`user_id`) REFERENCES `t_user` (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8 |
+---------+-----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.00 sec)

mysql> show create table t_order_item;
+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| Table | Create Table |
+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
| t_order_item | CREATE TABLE `t_order_item` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `product_id` int(11) NOT NULL,
  `price` double NOT NULL,
  `num` double NOT NULL,
  `order_id` int(20) NOT NULL,
  `remark` varchar(64) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `idx_remark` (`remark`),
  KEY `idx_num` (`num`),
  KEY `order_reference` (`order_id`),
  CONSTRAINT `order_reference` FOREIGN KEY (`order_id`) REFERENCES `t_order` (`order_id`)
) ENGINE=InnoDB AUTO_INCREMENT=2 DEFAULT CHARSET=utf8 |
+--------------+----------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+
1 row in set (0.01 sec)


二、WHERE查询优化


WHERE查询条件优化是SQL语句优化最重要的一个环节,WHERE子句的优化主要从索引的利用和条件的顺序两个方面。


1. 索引的利用


通过给查询列增加索引可以避免全表扫描加快数据检索速度,同时覆盖索引还可以避免回表查询,只通过索引即可返回所需要的数据。


如果WHERE查询条件中的索引列使用方法不当,则会导致索引失效,从而进行全表扫描。


2. 查询条件的顺序


WHERE的查询条件的顺序主要是针对联合索引而言,即联合索引遵循最左前戳匹配规则,故需要保证在where中列从左到右,如联合索引(a,b,c),则需要保证where a=xx and b=xx(注意,如果是where b=xx and a=xx,也可以继续使用该联合索引),而如果是where b=xx,则无法使用索引。如下对t_order_item表建立了联合索引idx_product_id_buy_date:当同时包含product_id和num或者只包含product_id时,可以使用该联合索引,如果只包含num则无法使用联合索引。


mysql> alter table t_order_item add index idx_product_id_num(product_id, num);
Query OK, 0 rows affected (0.04 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from t_order_item where num=2 and product_id=1;
+----+-------------+--------------+------------+------+----------------------------+---------+---------+-------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+------+----------------------------+---------+---------+-------+------+----------+-------------+
|
  1 | SIMPLE | t_order_item | NULL | ref | idx_num,idx_product_id_num | idx_num | 8 | const | 1 |   100.00 | Using where |
+----+-------------+--------------+------------+------+----------------------------+---------+---------+-------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

mysql> alter table t_order_item drop index idx_num;
Query OK, 0 rows affected (0.01 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> explain select * from t_order_item where num=2 and product_id=1;
+----+-------------+--------------+------------+------+--------------------+--------------------+---------+-------------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+------+--------------------+--------------------+---------+-------------+------+----------+-------+
|
  1 | SIMPLE | t_order_item | NULL | ref | idx_product_id_num | idx_product_id_num | 12 | const,const | 1 |   100.00 | NULL |
+----+-------------+--------------+------------+------+--------------------+--------------------+---------+-------------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from t_order_item where product_id=1;
+----+-------------+--------------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
|
  1 | SIMPLE | t_order_item | NULL | ref | idx_product_id_num | idx_product_id_num | 4 | const | 1 |   100.00 | NULL |
+----+-------------+--------------+------------+------+--------------------+--------------------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from t_order_item where num=2;
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------------+
|
  1 | SIMPLE | t_order_item | NULL | ALL | NULL | NULL | NULL | NULL | 1 |   100.00 | Using where |
+----+-------------+--------------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)


针对单列索引的情况,如果where的and条件中的列都包含索引或者某些没有索引,都是由MySQL自行选择使用其中一个MySQL优化器认为效率最高的索引,如下:user_id,order_id, buy_date均包含索引,则MySQL选择使用了order_id这个主键索引:


mysql> explain select * from t_order where user_id=1 and order_id>2 and buy_date=curdate();
+----+-------------+---------+------------+-------+--------------------------------------------------------------------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+--------------------------------------------------------------------------+---------+---------+------+------+----------+-------------+
|
  1 | SIMPLE | t_order | NULL | range | PRIMARY,idx_order_buy_date,idx_user_id,idx_user_id_buy_date,idx_buy_date | PRIMARY | 4 | NULL | 3 |    64.00 | Using where |
+----+-------------+---------+------------+-------+--------------------------------------------------------------------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.01 sec)


三、ORDER BY 排序优化


ORDER BY的排序优化主要是利用索引的有序性来进行排序,从而减少MySQL服务器的排序操作,因为在服务端进行排序通常需要额外的内存空间,通常通过sort_buffer_size来控制每个MySQL服务器线程的sort buffer的大小,如果内存空间不够,则需要通过磁盘文件来辅助。所以如果能利用索引的有序性来完成排序而可以提高性能。

    

所以ORDER BY的优化就转变为避免索引失效的优化了,即尽可能使用主键进行排序;如果不能使用主键来排序,则对于order by的列加上索引,并且如果可以使用覆盖索引,则通过建立联合索引来实现直接从索引返回数据;对于联合索引需要注意最左前戳匹配规则。如下,查询某个用户的所有订单并且根据购买日期排序,由执行计划可知,使用了联合索引idx_user_id_buy_date。


mysql> select * from t_order where user_id=2 order by buy_date;
+----------+---------+------+------------+
| order_id | user_id | cost | buy_date |
+----------+---------+------+------------+
|
        5 | 2 | 1000 | 2019-04-14 |
+----------+---------+------+------------+
1 row in set (0.00 sec)

mysql> explain select * from t_order where user_id=2 order by buy_date;
+----+-------------+---------+------------+------+----------------------------------+----------------------+---------+-------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+----------------------------------+----------------------+---------+-------+------+----------+-----------------------+
|
  1 | SIMPLE | t_order | NULL | ref | idx_user_id,idx_user_id_buy_date | idx_user_id_buy_date | 4 | const | 1 |   100.00 | Using index condition |
+----+-------------+---------+------------+------+----------------------------------+----------------------+---------+-------+------+----------+-----------------------+
1 row in set, 1 warning (0.00 sec)


如果存在联合索引,但是不遵循最左前戳规则,则无法使用索引来排序,如下将user_id和buy_date反过来则无法使用联合索引idx_user_id_buy_date了,由Using filesort可知需要在MySQL服务器进行排序。


mysql> explain select * from t_order where buy_date=curdate() order by user_id;
+----+-------------+---------+------------+------+---------------+--------------+---------+-------+------+----------+---------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+--------------+---------+-------+------+----------+---------------------------------------+
|
  1 | SIMPLE | t_order | NULL | ref | idx_buy_date | idx_buy_date | 3 | const | 1 |   100.00 | Using index condition; Using filesort |
+----+-------------+---------+------------+------+---------------+--------------+---------+-------+------+----------+---------------------------------------+
1 row in set, 1 warning (0.01 sec)


四、LIMIT分页优化


LIMIT分页查询优化主要是针对LIMIT index, count形式的SQL,即从index下标开始的count条记录,如 LIMIT 10000, 50,取出第10000到10050这50条记录,但是对于MySQL来说需要扫描前面的10000条记录。所以可以基于以下思路来优化:


1. 记录上一页的有序的最大ID


记录上一页的最大ID,通常为递增的主键值,或者递增的索引列,则可以利用索引来进行过滤,主要是基于B+树索引的特性来快速过滤掉大部分数据,如下:普通的limit index, count为全表扫描,使用order_id列则可以使用主键索引。


mysql> explain select * from t_order limit 10000, 10;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
|
  1 | SIMPLE | t_order | NULL | ALL | NULL | NULL | NULL | NULL | 4 |   100.00 | NULL |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from t_order where order_id > 100000 order by order_id limit 10;
+----+-------------+---------+------------+-------+----------------------------+---------+---------+------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+-------+----------------------------+---------+---------+------+------+----------+-------------+
|
  1 | SIMPLE | t_order | NULL | range | PRIMARY,idx_order_buy_date | PRIMARY | 4 | NULL | 1 |   100.00 | Using where |
+----+-------------+---------+------------+-------+----------------------------+---------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)


2. 子查询优化


可以通过子查询来对表的索引进行查找,获取这个范围的id,从而避免对数据表进行扫描,然后在数据表中取出匹配的数据行,如下:由于一页数据通常较少,故子查询


mysql> select * from t_order inner join (select order_id from t_order limit 10000, 10) as b on t_order.order_id=b.order_id;
Empty set (0.02 sec)

mysql> explain select * from t_order inner join (select order_id from t_order limit 10000, 10) as b on t_order.order_id=b.order_id;
+----+-------------+------------+------------+-------+----------------------------+--------------+---------+---------------------------+------+----------+-------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+------------+------------+-------+----------------------------+--------------+---------+---------------------------+------+----------+-------------+
|
  1 | PRIMARY | t_order | NULL | ALL | PRIMARY,idx_order_buy_date | NULL | NULL | NULL | 4 |   100.00 | NULL |
| 1 | PRIMARY | <derived2> | NULL | ref | <auto_key0> | <auto_key0> | 4       | easy_web.t_order.order_id |    2 | 100.00 | Using index |
|
  2 | DERIVED | t_order | NULL | index | NULL | idx_buy_date | 3 | NULL | 4 |   100.00 | Using index |
+----+-------------+------------+------------+-------+----------------------------+--------------+---------+---------------------------+------+----------+-------------+
3 rows in set, 1 warning (0.00 sec)


五、GROUP BY 分组优化


GROUP BY操作通常会进行排序操作,而通过GROUP BY一般是与聚集函数,如SUM,COUNT,MAX等来结合使用从而完成统计任务,故一般不需要进行排序,如下:统计每个用户今天的订单总金额:


mysql> select user_id, SUM(cost) from t_order where buy_date=curdate() group by user_id;
+---------+-----------+
| user_id | SUM(cost) |
+---------+-----------+
| 2 | 1000 |
+---------+-----------+
1 row in set (0.01 sec)


执行计划如下:由extra的 Using filesort 可知,需要在MySQL服务器进行排序,但是此时并不需要该排序操作。


mysql> explain select user_id, SUM(cost) from t_order where buy_date=curdate() group by user_id;
+----+-------------+---------+------------+------+-----------------------------------------------+--------------+---------+-------+------+----------+--------------------------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+-----------------------------------------------+--------------+---------+-------+------+----------+--------------------------------------------------------+
|
  1 | SIMPLE | t_order | NULL | ref | idx_user_id,idx_user_id_buy_date,idx_buy_date | idx_buy_date | 3 | const | 1 |   100.00 | Using index condition; Using temporary; Using filesort |
+----+-------------+---------+------------+------+-----------------------------------------------+--------------+---------+-------+------+----------+--------------------------------------------------------+


所以可以通过 ORDER BY NULL 来禁止排序操作,如下:extra不再包含Using filesort信息,故在MySQL服务器不再进行排序操作。


mysql> explain select user_id, SUM(cost) from t_order where buy_date=curdate() group by user_id order by null;
+----+-------------+---------+------------+------+-----------------------------------------------+--------------+---------+-------+------+----------+----------------------------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+-----------------------------------------------+--------------+---------+-------+------+----------+----------------------------------------+
|
  1 | SIMPLE | t_order | NULL | ref | idx_user_id,idx_user_id_buy_date,idx_buy_date | idx_buy_date | 3 | const | 1 |   100.00 | Using index condition; Using temporary |
+----+-------------+---------+------------+------+-----------------------------------------------+--------------+---------+-------+------+----------+----------------------------------------+
1 row in set, 1 warning (0.00 sec)



原文链接:https://blog.csdn.net/u010013573/java/article/details/89304301



精彩推荐

mysql 如何实现循环批量插入?

实战MySQL多表关联查询技巧

mysql 乐观锁和悲观锁

常用的MySQL图形化管理工具



扫下方二维码关注“程序员考拉”,每日推荐优秀好文!




如果感觉推送内容不错,不妨右下角点个在看,感谢支持!



more_vert