SQL优化（二）：MySQL索引失效的六种场景与优化方法

红太狼 2022-04-24 03:14 243阅读 0赞

### 一、概述 ###

*  以下基于用户订单表t\_order和订单清单条目表t\_order\_item来分析，二者通过order\_id来建立外键约束。
    
        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)

### 二. 最左前戳匹配 ###

### 三. 字符串使用 like % ###

### 四. 数据类型不匹配，隐式转换 ###

### 五. OR条件 ###

### 六. 列参与了MySQL函数的计算 ###

### 七. 查询返回的数据超过了表的30% ###

*  如果查询需要返回的数据超过了表所有的30%，则MySQL优化器会自动放弃使用索引而进行全表扫描，注意如果可以使用覆盖索引而不需要回表查询则MySQL会继续使用索引，如下t\_order表只包含5条记录，并且4条属于用户1，1条属于用户2：
    
        mysql> select * from t_order;
        +----------+---------+------+------------+
        | order_id | user_id | cost | buy_date   |
        +----------+---------+------+------------+
        |        1 |       1 |  100 | 2019-04-10 |
        |        2 |       1 |  102 | 2019-04-10 |
        |        3 |       1 |  103 | 2019-04-10 |
        |        4 |       1 |  104 | 2019-04-10 |
        |        5 |       2 | 1000 | 2019-04-14 |
        +----------+---------+------+------------+
        5 rows in set (0.00 sec)
 *  获取所有数据列：user\_id列包含索引，如果查询用户1的order则放弃使用索引，type显示ALL；如果查询用户2的order则继续使用索引，type显示ref：
    
        mysql> explain select * from t_order where user_id=1;
        +----+-------------+---------+------------+------+----------------------------------+------+---------+------+------+----------+-------------+
        | id | select_type | table   | partitions | type | possible_keys                    | key  | key_len | ref  | rows | filtered | Extra       |
        +----+-------------+---------+------------+------+----------------------------------+------+---------+------+------+----------+-------------+
        |  1 | SIMPLE      | t_order | NULL       | ALL  | idx_user_id,idx_user_id_buy_date | NULL | NULL    | NULL |    4 |   100.00 | Using where |
        +----+-------------+---------+------------+------+----------------------------------+------+---------+------+------+----------+-------------+
        1 row in set, 1 warning (0.00 sec)
        
        mysql> explain select * from t_order where user_id=2;
        +----+-------------+---------+------------+------+----------------------------------+-------------+---------+-------+------+----------+-------+
        | 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 | 4       | const |    1 |   100.00 | NULL  |
        +----+-------------+---------+------------+------+----------------------------------+-------------+---------+-------+------+----------+-------+
        1 row in set, 1 warning (0.00 sec)
 *  同样是查询用户1的所有订单，如果只需要返回user\_id和购买日期buy\_date，由于存在联合索引idx\_user\_id\_buy\_date，故可以进行索引覆盖，extra显示Using index，故使用该联合索引：
    
        mysql> explain select user_id,buy_date from t_order where user_id=1;
        +----+-------------+---------+------------+------+----------------------------------+----------------------+---------+-------+------+----------+-------------+
        | 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 |    4 |   100.00 | Using index |
        +----+-------------+---------+------------+------+----------------------------------+----------------------+---------+-------+------+----------+-------------+
        1 row in set, 1 warning (0.00 sec)

[MySQL_Innodb_]: https://blog.csdn.net/u010013573/article/details/89075248