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

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

2021-04-15 599

一、出现的场景

研发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>explain 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;
+----+-------------+-------+------------+--------+------------------------------------------------------------------------------------+-----------+---------+-----------------------+--------+----------+----------------------------------------------+
| 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>explain 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;
+----+-------------+-------+------------+--------+-----------------------------------------------------------------+-------------+---------+-----------------------+------+----------+----------------------------------------------+
| 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)]> explain 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;
+------+-------------+-------+--------+----------------------+-------------+---------+-----------------------+------+---------------------------------+
| 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 字符集居然是utf8
mysql> show create table db_order.t_order_device_trans_log\G                                                                                                                                
*************************** 1. row ***************************
       Table: t_order_device_trans_log
Create 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=utf8
1 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_log
Create 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=utf8
1 row in set (0.00 sec)

占用磁盘的大小:

mysql> select count(*) from   db_order.t_order_device_trans_log\G
*************************** 1. row ***************************
count(*): 962234
1 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: 0

mysql> show create table db_order.t_order_device_trans_log\G
*************************** 1. row ***************************
       Table: t_order_device_trans_log
Create 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=utf8mb4
1 row in set (0.00 sec)

执行计划报错：
mysql> explain 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;
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>   explain 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;

 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.cnf
default-character-set=utf8mb4
#character-set-server=utf8
character_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_log
Create 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_ci
1 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 names
You can turn off this feature to get a quicker startup with -A

Database changed
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 |
+-----------------+
|    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的优化

六、过程总结:

通过这次问题排查，发现了字符集不同是也会导致索引失效。这个问题有点类似于 int=varchar 隐式转换问题，等号左边为 int 类型，右边为 varchar 类型，那么 MySQL 会自动转换类型为一致，因而无法走索引。
另外还有一个注意的点就是，列的字符集也有可能与表的字符集不同！这种情况也是会导致索引失效问题的。所以一定要严格要求MySQL建表规范，要求表必须有主键，且表的字符集和字段的字符集必须一致。这样才能避免踩坑。字符集不一致，字符集转换导致索引失效的问题

微信关注我们

原文链接：https://blog.51cto.com/wujianwei/2709849

转载内容版权归作者及来源网站所有！

低调大师中文资讯倾力打造互联网数据资讯、行业资源、电子商务、移动互联网、网络营销平台。持续更新报道IT业界、互联网、市场资讯、驱动更新,是最及时权威的产业资讯及硬件资讯报道平台。

【博客大赛】由浅入深，一篇文章带你读懂Dubbo

一、基础知识 1、分布式基础理论 1.1）什么是分布式系统《分布式系统原理与泛型》定义：分布式系统是若干独立计算机的集合，这些计算机对于用户来说就像单个相关系统（比如京东商城等）分布式系统（distributed system）是建立在网络之上的软件系统随着互联网的发展，网站应用的规模不断扩大，常规的垂直应用架构已无法应对，分布式服务架构以及流动计算架构势在必行，急需一个治理系统确保架构有条不乱的演进。 1.2）、发展演变单一应用架构当网站流量很小是，只需一个应用，将所有功能都部署在一起，以减少部署节点和成本，此时，用于简化增删改查工作量的数据访问框架（ORM）是关键。单一应用架构有易于开发、易于测试、易于部署的优点，但是也有很明显的缺陷，那就是不易扩展、不易于协同开发，后期维护等缺陷垂直应用架构当随着应用规模不断扩大，单体应用放在一个服务器上，那么单台服务器的压力就扛不住了，这个时候就需要垂直应用架构。就是将单体应用架构拆分成一个个小应用，而且每一个应用都是完整的，当某一块应用访问量比较大时，就可以多放几台服务器上。优点是分工合作容易，每个人只负责自己的模块，互不...

2021-04-16

614

嵌入式软件工程师笔试面试指南-C/C++

哈喽，大家好。最近几天，我把去年秋招总结的笔试面试的一些内容，又进行了重新规划分类。详细分成了简历书写，面试技巧，面经总结，笔试面试八股文总结等四个部分。其中，八股文又分成了C/C++，数据结构与算法分析，Arm体系与架构，Linux驱动开发，操作系统，网络编程，名企笔试真题等七个部分。本次八股文更新，对于部分不合适的内容进行了删减，新增了C++相关内容。以上七个部分的内容，会同步更新在github，链接https://github.com/ZhongYi-LinuxDriverDev/EmbeddedSoftwareEngineerInterview 。希望大家能给个star支持下，让我有继续更新下去的动力。所有内容更新完成后，会将这些内容整合成PDF。话不多说，看下目录。预警：本文内容很长，很长，很长！没耐心看完的建议直接跳转github，获取PDF下载方式。 C/C++ 关键字 C语言宏中"#“和”##"的用法（#）字符串化操作符作用：将宏定义中的传入参数名转换成用一对双引号括起来参数名字符串。其只能用于有传入参数的宏定义中，且必须置于宏定义体中的参数名前。如： #d...

2021-04-16

545

发表评论

资源下载

更多资源

腾讯云软件源

为解决软件依赖安装时官方源访问速度慢的问题，腾讯云为一些软件搭建了缓存服务。您可以通过使用腾讯云软件源站来提升依赖包的安装速度。为了方便用户自由搭建服务架构，目前腾讯云软件源站支持公网访问和内网访问。

Nacos

Nacos /nɑ:kəʊs/ 是 Dynamic Naming and Configuration Service 的首字母简称，一个易于构建 AI Agent 应用的动态服务发现、配置管理和AI智能体管理平台。Nacos 致力于帮助您发现、配置和管理微服务及AI智能体应用。Nacos 提供了一组简单易用的特性集，帮助您快速实现动态服务发现、服务配置、服务元数据、流量管理。Nacos 帮助您更敏捷和容易地构建、交付和管理微服务平台。

Spring

Spring框架（Spring Framework）是由Rod Johnson于2002年提出的开源Java企业级应用框架，旨在通过使用JavaBean替代传统EJB实现方式降低企业级编程开发的复杂性。该框架基于简单性、可测试性和松耦合性设计理念，提供核心容器、应用上下文、数据访问集成等模块，支持整合Hibernate、Struts等第三方框架，其适用范围不仅限于服务器端开发，绝大多数Java应用均可从中受益。

Rocky Linux

Rocky Linux（中文名：洛基）是由Gregory Kurtzer于2020年12月发起的企业级Linux发行版，作为CentOS稳定版停止维护后与RHEL（Red Hat Enterprise Linux）完全兼容的开源替代方案，由社区拥有并管理，支持x86_64、aarch64等架构。其通过重新编译RHEL源代码提供长期稳定性，采用模块化包装和SELinux安全架构，默认包含GNOME桌面环境及XFS文件系统，支持十年生命周期更新。