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: WarningCode: 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: NoteCode: 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: WarningCode: 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: WarningCode: 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: NoteCode: 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`aLEFT 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: NoteCode: 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>explainSELECT count(*)from `temp0706`aLEFT 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`aLEFT 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)
