SQL优化案例1--去掉双%%后，SQL速度从5秒到0.01秒
一、问题现象
开发说以下SQL执行15秒，不可以接受。你把他优化到0.1秒以下吧。(跟客户交流过，SQL需要经过处理)
SELECT * FROM orde left join user on orde.user_id = user.id left join car on orde.car_id = car.id left join ocm on orde.charge_stand_encod = ocm.charge_stand_encod left join admin on orde.update_userid = admin.id LEFT JOIN scl on scl.id = orde.coupon_id LEFT JOIN sct on scl.coupon_type_id = sct.id WHERE 1 = 1 AND user.phone like '%17885004325%' AND orde.operator_id in (10, 7, 1048, 8, 9, 998, 1001, 1002, 1005, 1006, 1007, 1009, 1010, 1011, 1024, 1012, 1020, 1013, 1014, 1015) ORDER BY orde.create_time DESC limit 0, 20
二、信息收集
查看表数据量
+------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+--------------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+--------------+ | orde | InnoDB | 10 | Dynamic | 1551182 | 984 | 1527758848 | 0 | 787513344 | 7340032 | 10336667 | 2018-09-05 20:31:17 | 2018-10-30 17:21:57 | NULL | utf8_general_ci | NULL | | 订单总表 | +------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+--------------+ +------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+-----------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment |y +------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+-----------+ | user | InnoDB | 10 | Dynamic | 1537890 | 371 | 571457536 | 0 | 335577088 | 4194304 | 12112311 | 2018-10-19 23:01:12 | 2018-10-30 17:21:57 | NULL | utf8_general_ci | NULL | | 会员表 | +------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+-----------+ +--------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+--------------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +--------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+--------------+ | car | InnoDB | 10 | Dynamic | 7057 | 807 | 5701632 | 0 | 5226496 | 4194304 | 42404 | 2018-10-01 00:14:35 | 2018-10-30 17:21:57 | NULL | utf8_general_ci | NULL | | 车辆信息 | +--------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+----------------+--------------+ +-----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+--------------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +-----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+--------------------+---------+ | ocm | InnoDB | 10 | Compact | 153 | 321 | 49152 | 0 | 16384 | 0 | 811 | 2018-06-13 01:03:40 | 2018-10-30 15:00:42 | NULL | utf8_general_ci | NULL | row_format=COMPACT | | +-----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+--------------------+---------+ +-----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------+----------+--------------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +-----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------+----------+--------------------+---------+ | admin | InnoDB | 10 | Compact | 631 | 207 | 131072 | 0 | 32768 | 0 | 1686 | 2018-06-13 01:03:41 | 2018-10-30 16:27:31 | NULL | utf8_bin | NULL | row_format=COMPACT | | +-----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------+----------+--------------------+---------+ +-----------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+--------------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +-----------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+--------------------+---------+ | scl | InnoDB | 10 | Compact | 6498194 | 163 | 1061142528 | 0 | 533102592 | 7340032 | 44548696 | 2018-10-18 23:02:45 | 2018-10-30 17:21:57 | NULL | utf8_general_ci | NULL | row_format=COMPACT | | +-----------------+--------+---------+------------+---------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+--------------------+---------+ +-----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+--------------------+---------+ | Name | Engine | Version | Row_format | Rows | Avg_row_length | Data_length | Max_data_length | Index_length | Data_free | Auto_increment | Create_time | Update_time | Check_time | Collation | Checksum | Create_options | Comment | +-----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+--------------------+---------+ | sct | InnoDB | 10 | Compact | 6456 | 246 | 1589248 | 0 | 98304 | 4194304 | 12356 | 2018-07-19 19:34:17 | 2018-10-30 16:58:35 | NULL | utf8_general_ci | NULL | row_format=COMPACT | | +-----------------+--------+---------+------------+------+----------------+-------------+-----------------+--------------+-----------+----------------+---------------------+---------------------+------------+-----------------+----------+--------------------+---------+
查看表上面的索引
+------------+------------+-------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +------------+------------+-------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | orde | 0 | PRIMARY | 1 | id | A | 1634857 | NULL | NULL | | BTREE | | | | orde | 1 | update_userid | 1 | update_userid | A | 167598 | NULL | NULL | YES | BTREE | | | | orde | 1 | oper_orders_update_time | 1 | update_time | A | 1410960 | NULL | NULL | YES | BTREE | | | | orde | 1 | ope_orders_uid | 1 | user_id | A | 159562 | NULL | NULL | | BTREE | | | | orde | 1 | order_id_index | 1 | order_id | A | 1634865 | NULL | NULL | YES | BTREE | | | | orde | 1 | begin_time_index | 1 | begin_time | A | 1277148 | NULL | NULL | YES | BTREE | | | | orde | 1 | end_time | 1 | end_time | A | 1531872 | NULL | NULL | YES | BTREE | | | | orde | 1 | create_time_index | 1 | create_time | A | 1628670 | NULL | NULL | YES | BTREE | | | | orde | 1 | car_id_index | 1 | car_id | A | 8313 | NULL | NULL | YES | BTREE | | | | orde | 1 | idx_operator_id | 1 | operator_id | A | 18 | NULL | NULL | YES | BTREE | | | | orde | 1 | idx_status | 1 | status | A | 8 | NULL | NULL | YES | BTREE | | | | orde | 1 | idx_user_id | 1 | user_id | A | 162152 | NULL | NULL | | BTREE | | | +------------+------------+-------------------------+--------------+---------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ +------------+------------+-----------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +------------+------------+-----------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | user | 0 | PRIMARY | 1 | id | A | 1320970 | NULL | NULL | | BTREE | | | | user | 0 | web_user_phone_unique_index | 1 | phone | A | 1320979 | NULL | NULL | YES | BTREE | | | | user | 0 | web_user_phone_unique_index | 2 | top_operator_id | A | 1320979 | NULL | NULL | YES | BTREE | | | | user | 1 | user_name | 1 | user_name | A | 205 | NULL | NULL | YES | BTREE | | | | user | 1 | user_type | 1 | user_type | A | 2 | NULL | NULL | YES | BTREE | | | | user | 1 | province | 1 | province | A | 29 | NULL | NULL | YES | BTREE | | | | user | 1 | is_have_order | 1 | is_have_order | A | 1 | NULL | NULL | YES | BTREE | | | | user | 1 | rfid | 1 | rfid | A | 1 | NULL | NULL | YES | BTREE | | | | user | 1 | idx_operator_id | 1 | operator_id | A | 19 | NULL | NULL | YES | BTREE | | | | user | 1 | idx_create_time | 1 | create_time | A | 1232045 | NULL | NULL | YES | BTREE | | | | user | 1 | invitation_code | 1 | invitation_code | A | 566384 | NULL | NULL | YES | BTREE | | | | user | 1 | i_imei | 1 | imei | A | 469607 | NULL | NULL | YES | BTREE | | | +------------+------------+-----------------------------+--------------+-----------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ +--------------+------------+--------------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +--------------+------------+--------------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | car | 0 | PRIMARY | 1 | id | A | 7121 | NULL | NULL | | BTREE | | | | car | 0 | idx_plate | 1 | plate | A | 7121 | NULL | NULL | | BTREE | | | | car | 1 | city_code | 1 | city_code | A | 1 | NULL | NULL | YES | BTREE | | | | car | 1 | user_id | 1 | user_id | A | 2390 | NULL | NULL | YES | BTREE | | | | car | 1 | order_id | 1 | order_id | A | 2402 | NULL | NULL | YES | BTREE | | | | car | 1 | car_is_in_rail | 1 | car_is_in_rail | A | 1 | NULL | NULL | YES | BTREE | | | | car | 1 | amap_id | 1 | amap_id | A | 1 | NULL | NULL | YES | BTREE | | | | car | 1 | ope_car_info | 1 | device_num | A | 7121 | NULL | NULL | YES | BTREE | | | | car | 1 | idx_deposit_rail_draw_id | 1 | deposit_rail_draw_id | A | 1165 | NULL | NULL | YES | BTREE | | | | car | 1 | idx_deposit_rail_draw_id | 2 | car_now_status | A | 1553 | NULL | NULL | YES | BTREE | | | +--------------+------------+--------------------------+--------------+----------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ +-----------------+------------+-----------------------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-----------------+------------+-----------------------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | ocm | 0 | PRIMARY | 1 | id | A | 101 | NULL | NULL | | BTREE | | | | ocm | 1 | ope_charge_main_charge_code | 1 | charge_stand_encod | A | 101 | NULL | NULL | | BTREE | | | +-----------------+------------+-----------------------------+--------------+--------------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ +-----------------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-----------------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | admin | 0 | PRIMARY | 1 | id | A | 732 | NULL | NULL | | BTREE | | | | admin | 0 | user_name_unique | 1 | user_name | A | 732 | NULL | NULL | YES | BTREE | | | | admin | 1 | user_code | 1 | user_code | A | 670 | NULL | NULL | YES | BTREE | | | +-----------------+------------+------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ +-----------------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-----------------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | scl | 0 | PRIMARY | 1 | id | A | 5043168 | NULL | NULL | | BTREE | | | | scl | 1 | is_delete | 1 | is_delete | A | 1 | NULL | NULL | YES | BTREE | | | | scl | 1 | scl_user_id | 1 | user_id | A | 611029 | NULL | NULL | YES | BTREE | | | | scl | 1 | idx_create_time | 1 | create_time | A | 856759 | NULL | NULL | YES | BTREE | | | +-----------------+------------+-------------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ +-----------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-----------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | sct | 0 | PRIMARY | 1 | id | A | 5590 | NULL | NULL | | BTREE | | | | sct | 1 | is_delete | 1 | is_delete | A | 2 | NULL | NULL | YES | BTREE | | | +-----------------+------------+-----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+
查看SQL执行计划
+----+-------------+-------+------------+--------+--------------------------------------------+-----------------------------+---------+--------------------------------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+--------------------------------------------+-----------------------------+---------+--------------------------------------+------+----------+-------------+ | 1 | SIMPLE | orde | NULL | index | ope_orders_uid,idx_operator_id,idx_user_id | create_time_index | 6 | NULL | 19 | 100.00 | Using where | | 1 | SIMPLE | user | NULL | eq_ref | PRIMARY | PRIMARY | 4 | bq_bjev_gxqc.orde.user_id | 1 | 100.00 | Using where | | 1 | SIMPLE | car | NULL | eq_ref | PRIMARY | PRIMARY | 4 | bq_bjev_gxqc.orde.car_id | 1 | 100.00 | NULL | | 1 | SIMPLE | ocm | NULL | ref | ope_charge_main_charge_code | ope_charge_main_charge_code | 8 | bq_bjev_gxqc.orde.charge_stand_encod | 1 | 100.00 | NULL | | 1 | SIMPLE | admin | NULL | eq_ref | PRIMARY | PRIMARY | 4 | bq_bjev_gxqc.orde.update_userid | 1 | 100.00 | Using index | | 1 | SIMPLE | scl | NULL | eq_ref | PRIMARY | PRIMARY | 8 | bq_bjev_gxqc.orde.coupon_id | 1 | 100.00 | Using where | | 1 | SIMPLE | sct | NULL | eq_ref | PRIMARY | PRIMARY | 4 | bq_bjev_gxqc.scl.coupon_type_id | 1 | 100.00 | NULL | +----+-------------+-------+------------+--------+--------------------------------------------+-----------------------------+---------+--------------------------------------+------+----------+-------------+
三、分析思路
id都是1，表连接顺序从上往下
orde 表数据量 1551182，用到索引 create_time_index，type 是 index，表示是全索引扫描。extra是using where ，表示使用了 where 过滤。实际统计结果如下
select count(*) from orde where orde.operator_id in (10,7,1048,8,9,998,1001,1002,1005,1006,1007,1009,1010,1011,1024,1012,1020,1013,1014,1015) +----------+ | count(*) | +----------+ | 1717046 | +----------+
结果是 170万行，比原来的150万还要大，很明显统计信息不准确。MySQL实际肯定会拿着170万的数据去跟下一个表做关联的，这将会很恐怖。
接下来是user表，用到索引 primary，type 是 eq_ref，表示是唯一索引扫描。多表连接中使用主键或者唯一索引作为关联条件。extra也是using where，实际统计结果如下
select count(*) from user where user.phone like '%17885004325%'; +----------+ | count(*) | +----------+ | 1 | +----------+
结果只有一条。而且 user 表，phone字段上面是有索引的，基数也很高。为什么没有用到索引，细心的朋友应该发现了。用了两个%%，这是走不了索引的。
把两个%%去掉之后，执行速度在0.01秒。接下来看一下执行计划
+----+-------------+-------+------------+--------+--------------------------------------------+-----------------------------+---------+--------------------------------------+------+----------+--------------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+--------------------------------------------+-----------------------------+---------+--------------------------------------+------+----------+--------------------------------------------------------+ | 1 | SIMPLE | user | NULL | range | PRIMARY,web_user_phone_unique_index | web_user_phone_unique_index | 42 | NULL | 1 | 100.00 | Using index condition; Using temporary; Using filesort | | 1 | SIMPLE | orde | NULL | ref | ope_orders_uid,idx_operator_id,idx_user_id | ope_orders_uid | 4 | bq_bjev_gxqc.user.id | 8 | 100.00 | Using where | | 1 | SIMPLE | car | NULL | eq_ref | PRIMARY | PRIMARY | 4 | bq_bjev_gxqc.orde.car_id | 1 | 100.00 | NULL | | 1 | SIMPLE | ocm | NULL | ref | ope_charge_main_charge_code | ope_charge_main_charge_code | 8 | bq_bjev_gxqc.orde.charge_stand_encod | 1 | 100.00 | NULL | | 1 | SIMPLE | admin | NULL | eq_ref | PRIMARY | PRIMARY | 4 | bq_bjev_gxqc.orde.update_userid | 1 | 100.00 | Using index | | 1 | SIMPLE | scl | NULL | eq_ref | PRIMARY | PRIMARY | 8 | bq_bjev_gxqc.orde.coupon_id | 1 | 100.00 | Using where | | 1 | SIMPLE | sct | NULL | eq_ref | PRIMARY | PRIMARY | 4 | bq_bjev_gxqc.scl.coupon_type_id | 1 | 100.00 | NULL | +----+-------------+-------+------------+--------+--------------------------------------------+-----------------------------+---------+--------------------------------------+------+----------+--------------------------------------------------------+
执行计划改变了，由 user 表作为最开始的驱动表，由上面可知，过滤条件后只剩下 1 条记录。1 条记录去关联ope_orders，这个速度还是很快的。但是 user 表为什么会排序暂时还没研究明白，order by 是在 orde 表上。有大神看到的话，可以互相讨论下。SQL已经优化到0.01秒了，也没有继续再优化下去的必要。
四、总结
如果列上面有索引的话，不要在列两端加上% ，类似这种 %aaa%，大部分场景会引起索引失效。aaa% 这种情况也是可以用到索引的，话。
