MySQL学习（九）：SQL查询语句的用法和优化

阳光穿透心脏的1/2处 2022-04-24 04:10 272阅读 0赞

### 一、概述 ###

*  MySQL的性能优化可以从机器硬件，如磁盘，内存等；MySQL服务器配置，如线程数，查询缓存等；MySQL的主从分离和分库分表等；SQL语句优化等。其中SQL语句优化是与日常开发密切相关的，而且也是MySQL优化中最重要的一个环节，因为MySQL服务器，机器等的资源是一定的，故当出现性能瓶颈时，首先需要排除是否为SQL执行问题，如通过开启MySQL慢日志统计执行慢的SQL，或者使用profile功能统计SQL执行涉及的CPU，内存，IO等资源开销。
 *  定位到存在性能问题的SQL之后，则可以通过explain命令来分析该SQL的执行情况，如索引使用，排序等，然后是针对该SQL进行优化，优化主要从查询涉及的表，WHERE条件与是否使用和高效使用了索引，以及是否存在子查询等方面展开。
 *  要进行SQL优化，首先需要理解SQL的执行过程，具体可以参考[：SQL解析顺序与MySQL底层实现  
    ][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)

### 二、拼表优化：FROM和JOIN ###

*  [MySQL多表查询：FROM和JOIN的用法与性能优化  
    ][MySQL_FROM_JOIN_]

### 三、WHERE查询优化 ###

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

#### 1. 索引的利用 ####

*  通过给查询列增加索引可以避免全表扫描加快数据检索速度，同时覆盖索引还可以避免回表查询，只通过索引即可返回所需要的数据，索引相关的内容可以参考[：MySQL学习（七）：Innodb存储引擎索引的实现原理  
    ][MySQL_Innodb_]
 *  如果WHERE查询条件中的索引列使用方法不当，则会导致索引失效，从而进行全表扫描，以下来分析索引失效的情况：  
    [MySQL索引失效的六种场景与案例分析  
    ][MySQL_]

#### 2. 查询条件的顺序 ####

### 四、子查询的使用和优化 ###

*  [SQL优化（三）：子查询和IN，EXISTS用法和优化方法  
    ][SQL_IN_EXISTS_]

### 五、ORDER BY 排序优化 ###

### 六、LIMIT分页优化 ###

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

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

##### 2. 子查询优化 #####

### 七、GROUP BY 分组优化 ###

[SQL_MySQL_]: https://blog.csdn.net/u010013573/article/details/88834130
[MySQL_FROM_JOIN_]: https://blog.csdn.net/u010013573/article/details/89199397
[MySQL_Innodb_]: https://blog.csdn.net/u010013573/article/details/89075248
[MySQL_]: https://blog.csdn.net/u010013573/article/details/89299016
[SQL_IN_EXISTS_]: https://blog.csdn.net/u010013573/article/details/89303796