MySQL--索引与字符集的关系--数据量对利用索引的影响

一 从实例入手

创建表,并做查询如下:

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多表连接算法的前提：小表优先）