AI智能
改变未来

MySQL之left join表查询中发生字符集转换导致表索引失效


一、出现的场景

研发leader突然给了1条sql说这条sql在测试环境库db-stage执行非常的慢,放到线上db-read从库上执行非常的快。而且线上库从库的表的数据远多于测试环境库的表数据。让我分析下是什么问题??

二、sql内容如下

select  odtl.id from db_order.t_order_device_trans_log odtl left join db_order.t_order_items_detail oid on odtl.order_id=oid.order_id  left join db_order.t_orders o on oid.order_id=o.order_id where oid.customer_id= 40933  group by o.order_id;

对此sql简单分析如下:
此sql包含了3个表的 left join 查询
db_order.t_order_device_trans_log db_order.t_order_items_detail 和db_order.t_orders 表连接组成,分别用了odtl、 oid、 o 别名表示。因为都是用的 LEFT JOIN,所以表连接顺序应该是 odtl–>oid–>o,和explain 执行计划中显示的一致。

三、验证explain计划

本以为测试环境库配置参数和线上db-read库配置参数都是一样的。研发的话不能全信。还是拿着sql在测试库db-stage和线上db-read1 (Mariadb库版本10.2.17),db-read4(mysql5.7.32) 跑了下

3.1、db-stage环境上的sql的执行计划:

mysql>explainselect  odtl.id from db_order.t_order_device_trans_log odtl left join db_order.t_order_items_detail oid on odtl.order_id=oid.order_id  left join db_order.t_orders o on oid.order_id=o.order_id where oid.customer_id= 40933  group by o.order_id;+----+-------------+-------+------------+--------+------------------------------------------------------------------------------------+-----------+---------+-----------------------+--------+----------+----------------------------------------------+| id | select_type | table | partitions | type   | possible_keys                                                                      | key       | key_len | ref                   | rows   | filtered | Extra                                        |+----+-------------+-------+------------+--------+------------------------------------------------------------------------------------+-----------+---------+-----------------------+--------+----------+----------------------------------------------+|  1 | SIMPLE      | odtl  | NULL       | index  | NULL                                                                               | idx_order | 62      | NULL                  | 785867 |   100.00 | Using index; Using temporary; Using filesort ||  1 | SIMPLE      | oid   | NULL       | ref    | order_id,customer_id                                                               | order_id  | 82      | func                  |      3 |     1.39 | Using index condition; Using where           ||  1 | SIMPLE      | o     | NULL       | eq_ref | PRIMARY,order_id,main_order_id,customer_id_bill,source_order_id,idx_is_sale_status | order_id  | 82      | db_order.oid.order_id |      1 |   100.00 | Using index                                  |+----+-------------+-------+------------+--------+------------------------------------------------------------------------------------+-----------+---------+-----------------------+--------+----------+----------------------------------------------+3 rows in set, 1 warning (0.00 sec)

分析:
发现别名表 odtl type 类型为index,说明一开始是选择了索引的 possible_keys为NULL key:存在索引idx_order,但是没走索引,并且进行了全表扫描。

3.2、线上从库db-read4环境上的sql的执行计划:

\'db-read4>explainselect  odtl.id from db_order.t_order_device_trans_log odtl left join db_order.t_order_items_detail oid on odtl.order_id=oid.order_id  left join db_order.t_orders o on oid.order_id=o.order_id where oid.customer_id= 40933  group by o.order_id;+----+-------------+-------+------------+--------+-----------------------------------------------------------------+-------------+---------+-----------------------+------+----------+----------------------------------------------+| id | select_type | table | partitions | type   | possible_keys                                                   | key         | key_len | ref                   | rows | filtered | Extra                                        |+----+-------------+-------+------------+--------+-----------------------------------------------------------------+-------------+---------+-----------------------+------+----------+----------------------------------------------+|  1 | SIMPLE      | oid   | NULL       | ref    | order_id,customer_id                                            | customer_id | 4       | const                 |  279 |   100.00 | Using where; Using temporary; Using filesort ||  1 | SIMPLE      | o     | NULL       | eq_ref | PRIMARY,order_id,main_order_id,customer_id_bill,source_order_id | order_id    | 82      | db_order.oid.order_id |    1 |   100.00 | Using index                                  ||  1 | SIMPLE      | odtl  | NULL       | ref    | idx_order                                                       | idx_order   | 82      | db_order.oid.order_id |    7 |   100.00 | Using index                                  |+----+-------------+-------+------------+--------+-----------------------------------------------------------------+-------------+---------+-----------------------+------+----------+----------------------------------------------+3 rows in set, 1 warning (0.00 sec)

分析:
发现别名表 odtl type类型为ref ,说明选择了ref级别索引; possible_keys为 idx_order; key:存在索引idx_order ,而且走了索引的。

3.3、线上从库db-read1环境上的sql的执行计划:

root@db-read1 13:17:  [(none)]> explainselect  odtl.id from db_order.t_order_device_trans_log odtl left join db_order.t_order_items_detail oid on odtl.order_id=oid.order_id  left join db_order.t_orders o on oid.order_id=o.order_id where oid.customer_id= 40933  group by o.order_id;+------+-------------+-------+--------+----------------------+-------------+---------+-----------------------+------+---------------------------------+| id   | select_type | table | type   | possible_keys        | key         | key_len | ref                   | rows | Extra                           |+------+-------------+-------+--------+----------------------+-------------+---------+-----------------------+------+---------------------------------+|    1 | SIMPLE      | oid   | ref    | order_id,customer_id | customer_id | 4       | const                 |  279 | Using temporary; Using filesort ||    1 | SIMPLE      | o     | eq_ref | order_id             | order_id    | 82      | db_order.oid.order_id |    1 | Using index                     ||    1 | SIMPLE      | odtl  | ref    | idx_order            | idx_order   | 82      | db_order.oid.order_id |    3 | Using index                     |+------+-------------+-------+--------+----------------------+-------------+---------+-----------------------+------+---------------------------------+3 rows in set (0.01 sec)

分析:
发现别名表 odtl type类型为ref ,说明选择了ref级别索引; possible_keys为 idx_order; key:存在索引idx_order ,而且走了索引的。

结果:db-stage库确实是全表扫描了。确实比较奇怪。到底是什么问题导致的呢?
检查了下这条sql涉及到的这3个表的索引结构(测试环境和线上环境)。没发现有任何不同。太奇怪了。

3.4、采用explain format=json 方式查看执行计划

换了一种方式查看sql的执行计划:找到了线索

指定explain执行计划的线上格式为JSON。可以更详细的显示出执行计划的具体信息(建议生产上多多使用此方法分析sql执行计划)

在db-stage库上执行分析:

mysql> explain format=json select  odtl.id from db_order.t_order_device_trans_log odtl left join db_order.t_order_items_detail oid on odtl.order_id=oid.order_id  left join db_order.t_orderss o on oid.order_id=o.order_id where oid.customer_id= 40933  group by o.order_id\\G*************************** 1. row ***************************EXPLAIN: {\"query_block\": {\"select_id\": 1,\"cost_info\": {\"query_cost\": \"3634954.72\"},\"grouping_operation\": {\"using_temporary_table\": true,\"using_filesort\": true,\"cost_info\": {\"sort_cost\": \"39293.35\"},\"nested_loop\": [{\"table\": {\"table_name\": \"odtl\",\"access_type\": \"index\",\"key\": \"idx_order\",\"used_key_parts\": [\"order_id\"],\"key_length\": \"62\",\"rows_examined_per_scan\": 785867,\"rows_produced_per_join\": 785867,\"filtered\": \"100.00\",\"using_index\": true,\"cost_info\": {\"read_cost\": \"4007.00\",\"eval_cost\": \"157173.40\",\"prefix_cost\": \"161180.40\",\"data_read_per_join\": \"179M\"},\"used_columns\": [\"id\",\"order_id\"]}},{\"table\": {\"table_name\": \"oid\",\"access_type\": \"ref\",\"possible_keys\": [\"order_id\",\"customer_id\"],\"key\": \"order_id\",\"used_key_parts\": [\"order_id\"],\"key_length\": \"82\",\"ref\": [\"func\"],\"rows_examined_per_scan\": 3,\"rows_produced_per_join\": 39293,\"filtered\": \"1.39\",\"index_condition\": \"(convert(`db_order`.`odtl`.`order_id` using utf8mb4) = `db_order`.`oid`.`order_id`)\",\"cost_info\": {\"read_cost\": \"2822774.13\",\"eval_cost\": \"7858.67\",\"prefix_cost\": \"3548509.35\",\"data_read_per_join\": \"218M\"},\"used_columns\": [\"id\",\"order_id\",\"customer_id\"],\"attached_condition\": \"(`db_order`.`oid`.`customer_id` = 40933)\"}},{\"table\": {\"table_name\": \"o\",\"access_type\": \"eq_ref\",\"possible_keys\": [\"PRIMARY\",\"order_id\",\"main_order_id\",\"customer_id_bill\",\"source_order_id\",\"idx_is_sale_status\"],\"key\": \"order_id\",\"used_key_parts\": [\"order_id\"],\"key_length\": \"82\",\"ref\": [\"db_order.oid.order_id\"],\"rows_examined_per_scan\": 1,\"rows_produced_per_join\": 39293,\"filtered\": \"100.00\",\"using_index\": true,\"cost_info\": {\"read_cost\": \"39293.35\",\"eval_cost\": \"7858.67\",\"prefix_cost\": \"3595661.37\",\"data_read_per_join\": \"474M\"},\"used_columns\": [\"id\",\"order_id\"]}}]}}}1 row in set, 1 warning (0.00 sec)

执行计划分析:

计划index_condition条件中显示如下:发现了别名表odtl字段order_id =匹配别名表oid字段order_id时出现了utf8mb4字符集的转换

\"index_condition\": \"(convert(`db_order`.`odtl`.`order_id` using utf8mb4) = `db_order`.`oid`.`order_id`)\",

豁然开朗,很有可能是表db_order.t_order_device_trans_log 字段order_id字符集和db_order.t_order_items_detail表字段order_id字符集不一样,导致执行sql过程中,使得原本的索引失效,造成db_order.t_order_device_trans_log全表的扫描

于是在db-read1和db-read4库也同样执行json格式的explain计划:指标都是正常走索引的

\'db-read4\' root@localhost 23:08:05 (none)>explain format=json select  odtl.id from db_order.t_order_device_trans_log odtl left join db_order.t_order_items_detail oid on odtl.order_id=oid.order_id  left join db_order.t_orders o on oid.order_id=o.order_id where oid.customer_id= 40933  group by o.order_id\\G*************************** 1. row ***************************EXPLAIN: {\"query_block\": {\"select_id\": 1,\"cost_info\": {\"query_cost\": \"3395.51\"},\"grouping_operation\": {\"using_temporary_table\": true,\"using_filesort\": true,\"cost_info\": {\"sort_cost\": \"2023.29\"},\"nested_loop\": [{\"table\": {\"table_name\": \"oid\",\"access_type\": \"ref\",\"possible_keys\": [\"order_id\",\"customer_id\"],\"key\": \"customer_id\",\"used_key_parts\": [\"customer_id\"],\"key_length\": \"4\",\"ref\": [\"const\"],\"rows_examined_per_scan\": 279,\"rows_produced_per_join\": 279,\"filtered\": \"100.00\",\"cost_info\": {\"read_cost\": \"279.00\",\"eval_cost\": \"55.80\",\"prefix_cost\": \"334.80\",\"data_read_per_join\": \"1M\"},\"used_columns\": [\"id\",\"order_id\",\"customer_id\"],\"attached_condition\": \"(`db_order`.`oid`.`order_id` is not null)\"}},{\"table\": {\"table_name\": \"o\",\"access_type\": \"eq_ref\",\"possible_keys\": [\"PRIMARY\",\"order_id\",\"main_order_id\",\"customer_id_bill\",\"source_order_id\"],\"key\": \"order_id\",\"used_key_parts\": [\"order_id\"],\"key_length\": \"82\",\"ref\": [\"db_order.oid.order_id\"],\"rows_examined_per_scan\": 1,\"rows_produced_per_join\": 279,\"filtered\": \"100.00\",\"using_index\": true,\"cost_info\": {\"read_cost\": \"279.00\",\"eval_cost\": \"55.80\",\"prefix_cost\": \"669.60\",\"data_read_per_join\": \"3M\"},\"used_columns\": [\"id\",\"order_id\"]}},{\"table\": {\"table_name\": \"odtl\",\"access_type\": \"ref\",\"possible_keys\": [\"idx_order\"],\"key\": \"idx_order\",\"used_key_parts\": [\"order_id\"],\"key_length\": \"82\",\"ref\": [\"db_order.oid.order_id\"],\"rows_examined_per_scan\": 7,\"rows_produced_per_join\": 2023,\"filtered\": \"100.00\",\"using_index\": true,\"cost_info\": {\"read_cost\": \"297.96\",\"eval_cost\": \"404.66\",\"prefix_cost\": \"1372.22\",\"data_read_per_join\": \"616K\"},\"used_columns\": [\"id\",\"order_id\"]}}]}}}1 row in set, 1 warning (0.00 sec)
root@db-read1 13:38:  [(none)]> explain format=json select  odtl.id from db_order.t_order_device_trans_log odtl left join db_order.t_order_items_detail oid on odtl.order_id=oid.order_id  left join db_order.t_orders o on oid.order_id=o.order_id where oid.customer_id= 40933  group by o.order_id\\G*************************** 1. row ***************************EXPLAIN: {\"query_block\": {\"select_id\": 1,\"filesort\": {\"sort_key\": \"db_order.o.order_id\",\"temporary_table\": {\"table\": {\"table_name\": \"oid\",\"access_type\": \"ref\",\"possible_keys\": [\"order_id\", \"customer_id\"],\"key\": \"customer_id\",\"key_length\": \"4\",\"used_key_parts\": [\"customer_id\"],\"ref\": [\"const\"],\"rows\": 279,\"filtered\": 100},\"table\": {\"table_name\": \"o\",\"access_type\": \"eq_ref\",\"possible_keys\": [\"order_id\"],\"key\": \"order_id\",\"key_length\": \"82\",\"used_key_parts\": [\"order_id\"],\"ref\": [\"db_order.oid.order_id\"],\"rows\": 1,\"filtered\": 100,\"using_index\": true},\"table\": {\"table_name\": \"odtl\",\"access_type\": \"ref\",\"possible_keys\": [\"idx_order\"],\"key\": \"idx_order\",\"key_length\": \"82\",\"used_key_parts\": [\"order_id\"],\"ref\": [\"db_order.oid.order_id\"],\"rows\": 3,\"filtered\": 100,\"using_index\": true}}}}}1 row in set (0.00 sec)

四、查看表字符集确定最终的问题

问题就是db-stage 库 表db_order.t_order_device_trans_log 字符集居然是utf8导致的索引失效。

于是查看测试库db-stage  表db_order.t_order_device_trans_log 字符集居然是utf8mysql> show create table db_order.t_order_device_trans_log\\G*************************** 1. row ***************************Table: t_order_device_trans_logCreate Table: CREATE TABLE `t_order_device_trans_log` (`id` bigint(20) NOT NULL AUTO_INCREMENT,`order_id` varchar(20) NOT NULL COMMENT \'订单号\',`order_type` tinyint(2) NOT NULL COMMENT \'订单类型\',`rent_type` tinyint(2) NOT NULL COMMENT \'租赁类型(1.随租随还2.固定租期3.租完即送4.无期限随租随还5.短期租赁)\',`item_detail_id` bigint(20) NOT NULL COMMENT \'订单设备id\',`sn` varchar(50) NOT NULL COMMENT \'序列号\',`before_status` tinyint(2) DEFAULT NULL COMMENT \'变更前设备状态\',`change_type` tinyint(2) NOT NULL COMMENT \'变更类型:0:已发货,1:起租,2:退租,3:融租完成,4:挂应收,5:换货, 99:取消发货\',`cdate` datetime NOT NULL,PRIMARY KEY (`id`),KEY `idx_order` (`order_id`)) ENGINE=InnoDB AUTO_INCREMENT=3794673 DEFAULT CHARSET=utf81 row in set (0.00 sec)

db-stage库其他2个表db_order.t_order_items_detail 和db_order.t_orders 字符集都是 utf8mb4

于是查看测试库db-read1 表db_order.t_order_device_trans_log db_order.t_order_items_detail 和db_order.t_orders 字符集utf8mb4
于是查看测试库db-read4 表db_order.t_order_device_trans_log db_order.t_order_items_detail 和db_order.t_orders 字符集utf8mb4

五、修改表字符集utf8为utf8mb4

切记:修改表字符集前一定要对表提前做下备份

mysqldump -uroot -p\'UEgzFO\' -h 127.0.0.1 db_order t_order_device_trans_log > /data/dump/2021-04-13.t_order_device_trans_log.sql

5.1 修改表字符集前的建表sql:

mysql> show create table db_order.t_order_device_trans_log\\G*************************** 1. row ***************************Table: t_order_device_trans_logCreate Table: CREATE TABLE `t_order_device_trans_log` (`id` bigint(20) NOT NULL AUTO_INCREMENT,`order_id` varchar(20) NOT NULL COMMENT \'订单号\',`order_type` tinyint(2) NOT NULL COMMENT \'订单类型\',`rent_type` tinyint(2) NOT NULL COMMENT \'租赁类型(1.随租随还2.固定租期3.租完即送4.无期限随租随还5.短期租赁)\',`item_detail_id` bigint(20) NOT NULL COMMENT \'订单设备id\',`sn` varchar(50) NOT NULL COMMENT \'序列号\',`before_status` tinyint(2) DEFAULT NULL COMMENT \'变更前设备状态\',`change_type` tinyint(2) NOT NULL COMMENT \'变更类型:0:已发货,1:起租,2:退租,3:融租完成,4:挂应收,5:换货, 99:取消发货\',`cdate` datetime NOT NULL,PRIMARY KEY (`id`),KEY `idx_order` (`order_id`)) ENGINE=InnoDB AUTO_INCREMENT=3794673 DEFAULT CHARSET=utf81 row in set (0.00 sec)

占用磁盘的大小:

mysql> select count(*) from   db_order.t_order_device_trans_log\\G*************************** 1. row ***************************count(*): 9622341 row in set (0.19 sec)mysql> SELECT (`DATA_LENGTH`+ `INDEX_LENGTH`)/1024/1024  as `table_data_size`  from `TABLES` WHERE TABLE_NAME =\'t_order_device_trans_log\' and TABLE_SCHEMA=\'db_order\';+-----------------+| table_data_size |+-----------------+|     62.60937500 |+-----------------+1 row in set (0.00 sec)

5.2修改db-stage测试库表字符集为utf8mb4:

下面的操作是错误的,才导致后面执行sql报错

mysql > ALTER TABLE db_order.t_order_device_trans_log CONVERT TO CHARACTER SET utf8mb4;Query OK, 962234 rows affected (17.99 sec)Records: 962234  Duplicates: 0  Warnings: 0mysql> show create table db_order.t_order_device_trans_log\\G*************************** 1. row ***************************Table: t_order_device_trans_logCreate Table: CREATE TABLE `t_order_device_trans_log` (`id` bigint(20) NOT NULL AUTO_INCREMENT,`order_id` varchar(20) NOT NULL COMMENT \'订单号\',`order_type` tinyint(2) NOT NULL COMMENT \'订单类型\',`rent_type` tinyint(2) NOT NULL COMMENT \'租赁类型(1.随租随还2.固定租期3.租完即送4.无期限随租随还5.短期租赁)\',`item_detail_id` bigint(20) NOT NULL COMMENT \'订单设备id\',`sn` varchar(50) NOT NULL COMMENT \'序列号\',`before_status` tinyint(2) DEFAULT NULL COMMENT \'变更前设备状态\',`change_type` tinyint(2) NOT NULL COMMENT \'变更类型:0:已发货,1:起租,2:退租,3:融租完成,4:挂应收,5:换货, 99:取消发货\',`cdate` datetime NOT NULL,PRIMARY KEY (`id`),KEY `idx_order` (`order_id`)) ENGINE=InnoDB AUTO_INCREMENT=3794673 DEFAULT CHARSET=utf8mb41 row in set (0.00 sec)执行计划报错:mysql> explainselect  odtl.id from db_order.t_order_device_trans_log odtl left join db_order.t_order_items_detail oid on odtl.order_id=oid.order_id  left join db_order.t_orders o on oid.order_id=o.order_id where oid.customer_id= 40933  group by o.order_id;ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation \'=\'
mysql> ALTER TABLE t_order_device_trans_log  DEFAULT CHARACTER SET utf8mb4  COLLATE utf8mb4_unicode_ci;Query OK, 0 rows affected (0.01 sec)Records: 0  Duplicates: 0  Warnings: 0

查看执行计划,还在报错:

 mysql>   explainselect  odtl.id from db_order.t_order_device_trans_log odtl left join db_order.t_order_items_detail oid on odtl.order_id=oid.order_id  left join db_order.t_orders o on oid.order_id=o.order_id where oid.customer_id= 40933  group by o.order_id;ERROR 1267 (HY000): Illegal mix of collations (utf8mb4_general_ci,IMPLICIT) and (utf8mb4_unicode_ci,IMPLICIT) for operation \'=\'

5.3解决办法就是还原成原来的表字符集utf8。然后重新修改表字符集为utf8mb4:

重要提示:下面的参数可以不重启MySQL服务进行动态设置。同时也要主要my.cnf配置文件字符集参数的设置

[root@db-stage ~]# egrep \"character|collation_server\" /etc/my.cnfdefault-character-set=utf8mb4#character-set-server=utf8character_set_server = \'utf8mb4\'collation_server = \'utf8mb4_unicode_ci\'#collation_server = \'utf8mb4_general_ci\'

5.4下面的操作是还原一开始表db_order.t_order_device_trans_log的字符集utf8

下面是还原原来表字符集 utf8:

mysql> ALTER TABLE db_order.t_order_device_trans_log CONVERT TO CHARACTER SET utf8;Query OK, 962234 rows affected (17.99 sec)Records: 962234  Duplicates: 0  Warnings: 0

再次执行下面的修改表字符集utf8mb4为命令:

mysql>   ALTER TABLE db_order.t_order_device_trans_log CONVERT TO CHARACTER SET utf8mb4 COLLATE utf8mb4_unicode_ci;Query OK, 962784 rows affected (18.99 sec)Records: 962784  Duplicates: 0  Warnings: 0
mysql> show create table  db_order.t_order_device_trans_log\\G*************************** 1. row ***************************Table: t_order_device_trans_logCreate Table: CREATE TABLE `t_order_device_trans_log` (`id` bigint(20) NOT NULL AUTO_INCREMENT,`order_id` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT \'订单号\',`order_type` tinyint(2) NOT NULL COMMENT \'订单类型\',`rent_type` tinyint(2) NOT NULL COMMENT \'租赁类型(1.随租随还2.固定租期3.租完即送4.无期限随租随还5.短期租赁)\',`item_detail_id` bigint(20) NOT NULL COMMENT \'订单设备id\',`sn` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL COMMENT \'序列号\',`before_status` tinyint(2) DEFAULT NULL COMMENT \'变更前设备状态\',`change_type` tinyint(2) NOT NULL COMMENT \'变更类型:0:已发货,1:起租,2:退租,3:融租完成,4:挂应收,5:换货, 99:取消发货\',`cdate` datetime NOT NULL,PRIMARY KEY (`id`),KEY `idx_order` (`order_id`)) ENGINE=InnoDB AUTO_INCREMENT=3795223 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci1 row in set (0.00 sec)mysql> show variables where Variable_name like \'collation%\';+----------------------+--------------------+| Variable_name        | Value              |+----------------------+--------------------+| collation_connection | utf8mb4_general_ci || collation_database   | utf8mb4_unicode_ci || collation_server     | utf8mb4_unicode_ci |+----------------------+--------------------+3 rows in set (0.01 sec)

查看表占用磁盘物理空间大小:

mysql> use information_schema;Reading table information for completion of table and column namesYou can turn off this feature to get a quicker startup with -ADatabase changedmysql> SELECT (`DATA_LENGTH`+ `INDEX_LENGTH`)/1024/1024  as `table_data_size`  from `TABLES` WHERE TABLE_NAME =\'t_order_device_trans_log\' and TABLE_SCHEMA=\'db_order\';+-----------------+| table_data_size |+-----------------+|    142.35937500 |+-----------------+1 row in set (0.00 sec)

此时查看db-stage json格式的执行计划:

mysql> explain   format=json  select odtl.id from db_order.t_order_device_trans_log odtl left join db_order.t_order_items_detail oid on odtl.order_id=oid.order_id  left join db_order.t_ordeers o on oid.order_id=o.order_id where oid.customer_id= 40933  group by o.order_id\\G*************************** 1. row ***************************EXPLAIN: {\"query_block\": {\"select_id\": 1,\"cost_info\": {\"query_cost\": \"1073.53\"},\"grouping_operation\": {\"using_temporary_table\": true,\"using_filesort\": true,\"cost_info\": {\"sort_cost\": \"589.89\"},\"nested_loop\": [{\"table\": {\"table_name\": \"oid\",\"access_type\": \"ref\",\"possible_keys\": [\"order_id\",\"customer_id\"],\"key\": \"customer_id\",\"used_key_parts\": [\"customer_id\"],\"key_length\": \"4\",\"ref\": [\"const\"],\"rows_examined_per_scan\": 106,\"rows_produced_per_join\": 106,\"filtered\": \"100.00\",\"cost_info\": {\"read_cost\": \"106.00\",\"eval_cost\": \"21.20\",\"prefix_cost\": \"127.20\",\"data_read_per_join\": \"602K\"},\"used_columns\": [\"id\",\"order_id\",\"customer_id\"],\"attached_condition\": \"(`db_order`.`oid`.`order_id` is not null)\"}},{\"table\": {\"table_name\": \"o\",\"access_type\": \"eq_ref\",\"possible_keys\": [\"PRIMARY\",\"order_id\",\"main_order_id\",\"customer_id_bill\",\"source_order_id\",\"idx_is_sale_status\"],\"key\": \"order_id\",\"used_key_parts\": [\"order_id\"],\"key_length\": \"82\",\"ref\": [\"db_order.oid.order_id\"],\"rows_examined_per_scan\": 1,\"rows_produced_per_join\": 106,\"filtered\": \"100.00\",\"using_index\": true,\"cost_info\": {\"read_cost\": \"106.00\",\"eval_cost\": \"21.20\",\"prefix_cost\": \"254.40\",\"data_read_per_join\": \"1M\"},\"used_columns\": [\"id\",\"order_id\"]}},{\"table\": {\"table_name\": \"odtl\",\"access_type\": \"ref\",\"possible_keys\": [\"idx_order\"],\"key\": \"idx_order\",\"used_key_parts\": [\"order_id\"],\"key_length\": \"82\",\"ref\": [\"db_order.oid.order_id\"],\"rows_examined_per_scan\": 5,\"rows_produced_per_join\": 589,\"filtered\": \"100.00\",\"using_index\": true,\"cost_info\": {\"read_cost\": \"111.26\",\"eval_cost\": \"117.98\",\"prefix_cost\": \"483.64\",\"data_read_per_join\": \"179K\"},\"used_columns\": [\"id\",\"order_id\"]}}]}}}1 row in set, 1 warning (0.00 sec)mysql>

到此处完成sql的优化

赞(0) 打赏
未经允许不得转载:爱站程序员基地 » MySQL之left join表查询中发生字符集转换导致表索引失效