创建表,并做查询如下:
mysql> CREATE TABLE t1 (
-> a int(11) DEFAULT NULL,
-> b varchar(10) DEFAULT NULL,
-> KEY b (b)
-> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.08 sec)
mysql>
mysql> CREATE TABLE t3 (
-> a int(11) DEFAULT NULL,
-> b char(15) CHARACTER SET utf8 DEFAULT NULL,
-> KEY b (b)
-> ) ENGINE=InnoDB DEFAULT CHARSET=latin1;
Query OK, 0 rows affected (0.03 sec)
mysql> explain select * from t1,t3 where t1.b=t3.b;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------+
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 1 | 100.00 | NULL |
| 1 | SIMPLE | t3 | NULL | ref | b | b | 46 | func | 1 | 100.00 | Using index condition |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-----------------------+
2 rows in set, 1 warning (0.01 sec)
分析:
1 t1和t3表使用b列进行连接,但b列的字符集不同
2 执行计划显示,对于t3表的b列,使用了其上的索引
问题: MySQL官方手册却说:
For comparisons between nonbinary string columns, both columns should use the same character set. For example, comparing a utf8 column with a latin1 column precludes(阻止,排除) use of an index.
这表明: 对于字符串类型作条件子句,只有使用同样的字符集,才能利用到索引.
但是,执行计划却明确显示索引可以被使用,似乎官方手册描述不正确.
二 继续以实例说明情况
插入一些数据分别到t1和t2表,然后做同样的查询.
mysql> insert into t1 values (1, 'b11'),(2, 'b12');
Query OK, 2 rows affected (0.05 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> insert into t3 values (1, 'b13'),(2, 'b13');
Query OK, 2 rows affected (0.00 sec)
Records: 2 Duplicates: 0 Warnings: 0
mysql> explain select * from t1,t3 where t1.b=t3.b;
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------
--------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra
|
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------
--------+
| 1 | SIMPLE | t1 | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | NULL
|
| 1 | SIMPLE | t3 | NULL | ALL | b | NULL | NULL | NULL | 2 | 50.00 | Using where; Using join buffer (Block Neste
d Loop) |
+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+--------------------------------------------
--------+
2 rows in set, 1 warning (0.03 sec)
分析:
1 查看执行计划,与标题一中使用完全相同的SQL语句,得到的却是不同的执行计划。新的执行计划显示,t2表b列没有利用到索引。这点和MySQL官方文档描述的一致。所以官方手册说明的是有数据存在的情况。
2 进一步分析:
2.1 执行计划的选择,与表中有无数据有关
2.2 如果给t1表插入更多的数据,执行计划会发生变化(可自己做实验验证)。这说明数据量不同,同样影响着执行计划的选择(MySQL多表连接算法的前提:小表优先)