1、表结构：
mysql>show create table temp3daysdata\G*************************** 1. row *************************** Table: temp3daysdataCreate Table: CREATE TABLE `temp3daysdata` ( `id` int(11) NOT NULL AUTO_INCREMENT, `borrow_id` int(11) DEFAULT NULL, `borrow_bill_id` int(11) NOT NULL, `paid_amount` decimal(10,2) NOT NULL, `deduct_amount` decimal(10,2) NOT NULL DEFAULT '0.00', `alipay_order_no` varchar(45) DEFAULT NULL, `deduct_info` varchar(120) DEFAULT NULL, `created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `created_by` varchar(45) NOT NULL, PRIMARY KEY (`id`), KEY `fk_borrow_bill_paid_history_borrow_bill1_idx_temp` (`borrow_bill_id`) USING BTREE, KEY `fk_borrow_idx_temp` (`borrow_id`) USING BTREE, KEY `idx_alipay_order_no_temp` (`alipay_order_no`) USING BTREE) ENGINE=InnoDB AUTO_INCREMENT=2539968 DEFAULT CHARSET=utf8共返回 1 行记录,花费 5 ms.mysql>show create table temp0706\G*************************** 1. row *************************** Table: temp0706Create Table: CREATE TABLE `temp0706` ( `id` bigint(20) NOT NULL, `created_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP, `actual_repayment_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00', `paid_amount` decimal(10,2) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8共返回 1 行记录,花费 5 ms. 2、count mysql>select count(*) from temp0706;+--------------------+| count(*) |+--------------------+| 10078 |+--------------------+共返回 1 行记录,花费 6 ms.mysql>select count(*) from temp3daysdata;+--------------------+| count(*) |+--------------------+| 63639 |+--------------------+共返回 1 行记录,花费 13 ms. dba_jingjing@3306>[rds_test]>explain extended SELECT count(*) from `temp0706`a LEFT JOIN `temp3daysdata` b on a.`id` = b.`alipay_order_no` ;+----+-------------+-------+-------+--------------------------+---------+---------+------+-------+----------+------------------------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+-------+--------------------------+---------+---------+------+-------+----------+------------------------------------------------+| 1 | SIMPLE | a | index | NULL | PRIMARY | 8 | NULL | 10048 | 100.00 | Using index || 1 | SIMPLE | b | ALL | idx_alipay_order_no_temp | NULL | NULL | NULL | 53721 | 100.00 | Range checked for each record (index map: 0x8) |+----+-------------+-------+-------+--------------------------+---------+---------+------+-------+----------+------------------------------------------------+2 rows in set, 2 warnings (0.03 sec)dba_jingjing@3306>[rds_test]>show warnings\G*************************** 1. row *************************** Level: Warning Code: 1739Message: Cannot use ref access on index 'idx_alipay_order_no_temp' due to type or collation conversion on field 'alipay_order_no'*************************** 2. row *************************** Level: Note Code: 1003Message: /* select#1 */ select count(0) AS `count(*)` from `rds_test`.`temp0706` `a` left join `rds_test`.`temp3daysdata` `b` on((`rds_test`.`a`.`id` = `rds_test`.`b`.`alipay_order_no`)) where 12 rows in set (0.03 sec)dba_jingjing@3306>[rds_test]>explain extended SELECT count(*) from `temp0706`a LEFT JOIN `temp3daysdata` b on a.`id` = b.`alipay_order_no` WHERE a.id=b.`alipay_order_no`;+----+-------------+-------+--------+--------------------------+--------------------------+---------+----------------------------+-------+----------+--------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+--------+--------------------------+--------------------------+---------+----------------------------+-------+----------+--------------------------+| 1 | SIMPLE | b | index | idx_alipay_order_no_temp | idx_alipay_order_no_temp | 138 | NULL | 53721 | 100.00 | Using where; Using index || 1 | SIMPLE | a | eq_ref | PRIMARY | PRIMARY | 8 | rds_test.b.alipay_order_no | 1 | 100.00 | Using where; Using index |+----+-------------+-------+--------+--------------------------+--------------------------+---------+----------------------------+-------+----------+--------------------------+2 rows in set, 3 warnings (0.03 sec)dba_jingjing@3306>[rds_test]>show warnings\G*************************** 1. row *************************** Level: Warning Code: 1739Message: Cannot use ref access on index 'idx_alipay_order_no_temp' due to type or collation conversion on field 'alipay_order_no'*************************** 2. row *************************** Level: Warning Code: 1739Message: Cannot use ref access on index 'idx_alipay_order_no_temp' due to type or collation conversion on field 'alipay_order_no'*************************** 3. row *************************** Level: Note Code: 1003Message: /* select#1 */ select count(0) AS `count(*)` from `rds_test`.`temp0706` `a` join `rds_test`.`temp3daysdata` `b` where ((`rds_test`.`a`.`id` = `rds_test`.`b`.`alipay_order_no`) and (`rds_test`.`a`.`id` = `rds_test`.`b`.`alipay_order_no`))3 rows in set (0.03 sec) mysql>show warnings\G *************************** 1. row *************************** Level: Note Code: 1003 Message: /* select#1 */ select count(0) AS `count(*)` from `bi`.`temp0706` `a` left join `bi`.`temp3daysdata` `b` on((cast(`bi`.`a`.`id` as char charset utf8mb4) = convert(`bi`.`b`.`alipay_order_no` using utf8mb4))) where 1 共返回 1 行记录,花费 5 ms. SELECT count(*) from `temp0706`a LEFT JOIN `temp3daysdata` b on CAST( a.`id` AS CHAR charset utf8 ) = b.`alipay_order_no` mysql>explain extendedSELECT count(*) from `temp0706`a LEFT JOIN `temp3daysdata` b on CAST( a.`id` AS CHAR) = b.`alipay_order_no`;+--------------+-----------------------+-----------------+----------------+-------------------------+--------------------------+-------------------+---------------+----------------+--------------------+-----------------------------------------------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+--------------+-----------------------+-----------------+----------------+-------------------------+--------------------------+-------------------+---------------+----------------+--------------------+-----------------------------------------------------------------+| 1 | SIMPLE | a | index | | PRIMARY | 8 | | 10236 | 100 | Using index || 1 | SIMPLE | b | index | | idx_alipay_order_no_temp | 138 | | 63361 | 100 | Using where; Using index; Using join buffer (Block Nested Loop) |+--------------+-----------------------+-----------------+----------------+-------------------------+--------------------------+-------------------+---------------+----------------+--------------------+-----------------------------------------------------------------+共返回 2 行记录,花费 5 ms.mysql>show warnings\G*************************** 1. row *************************** Level: Note Code: 1003Message: /* select#1 */ select count(0) AS `count(*)` from `bi`.`temp0706` `a` left join `bi`.`temp3daysdata` `b` on((cast(`bi`.`a`.`id` as char charset utf8mb4) = convert(`bi`.`b`.`alipay_order_no` using utf8mb4))) where 1共返回 1 行记录,花费 5 ms.mysql>explain SELECT count(*) from `temp0706`a LEFT JOIN `temp3daysdata` b on CAST( a.`id` AS CHAR charset utf8 ) = b.`alipay_order_no`+--------------+-----------------------+-----------------+----------------+--------------------------+--------------------------+-------------------+---------------+----------------+--------------------------+| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |+--------------+-----------------------+-----------------+----------------+--------------------------+--------------------------+-------------------+---------------+----------------+--------------------------+| 1 | SIMPLE | a | index | | PRIMARY | 8 | | 10236 | Using index || 1 | SIMPLE | b | ref | idx_alipay_order_no_temp | idx_alipay_order_no_temp | 138 | func | 1 | Using where; Using index |+--------------+-----------------------+-----------------+----------------+--------------------------+--------------------------+-------------------+---------------+----------------+--------------------------+共返回 2 行记录,花费 5 ms.mysql>SELECT count(*) from `temp0706`a LEFT JOIN `temp3daysdata` b on CAST( a.`id` AS CHAR charset utf8 ) = b.`alipay_order_no`+--------------------+| count(*) |+--------------------+| 10078 |+--------------------+共返回 1 行记录,花费 39 ms. dba_jingjing@3306>[rds_test]>explain extended -> SELECT count(*) -> from `temp0706`a -> LEFT JOIN `temp3daysdata` b on CAST( a.`id` AS CHAR ) = b.`alipay_order_no` -> -> ; +----+-------------+-------+-------+--------------------------+--------------------------+---------+------+-------+----------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+-------+--------------------------+--------------------------+---------+------+-------+----------+--------------------------+ | 1 | SIMPLE | a | index | NULL | PRIMARY | 8 | NULL | 10048 | 100.00 | Using index | | 1 | SIMPLE | b | ref | idx_alipay_order_no_temp | idx_alipay_order_no_temp | 138 | func | 1 | 100.00 | Using where; Using index | +----+-------------+-------+-------+--------------------------+--------------------------+---------+------+-------+----------+--------------------------+ 2 rows in set, 1 warning (0.04 sec) dba_jingjing@3306>[rds_test]>show warnings\G *************************** 1. row *************************** Level: Note Code: 1003 Message: /* select#1 */ select count(0) AS `count(*)` from `rds_test`.`temp0706` `a` left join `rds_test`.`temp3daysdata` `b` on((convert(cast(`rds_test`.`a`.`id` as char charset latin1) using utf8) = `rds_test`.`b`.`alipay_order_no`)) where 1 1 row in set (0.04 sec) dba_jingjing@3306>[rds_test]> SELECT count(*) from `temp0706`a LEFT JOIN `temp3daysdata` b on CAST( a.`id` AS CHAR ) = b.`alipay_order_no`; +----------+ | count(*) | +----------+ | 10000 | +----------+ 1 row in set (0.07 sec) dba_jingjing@3306>[rds_test]> SELECT count(*) from `temp0706`a LEFT JOIN `temp3daysdata` b on CAST( a.`id` AS CHAR charset utf8 ) = b.`alipay_order_no`; +----------+ | count(*) | +----------+ | 10000 | +----------+ 1 row in set (0.06 sec)