处理一个数据类型问题想到的。先做MySQL的，Oracle PG的看情况也做一下。 mysql> create table w (id int,a int,b varchar(10),c decimal(10,0),primary key (id)); Query OK, 0 rows affected (0.03 sec) mysql> alter table w add d datetime; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> insert into w values (1,1,1,1,now()); Query OK, 1 row affected (0.00 sec) mysql> insert into w values (2,2,2,2,now()); Query OK, 1 row affected (0.00 sec) mysql> insert into w values (3,3,3,3,now()); Query OK, 1 row affected (0.00 sec) mysql> insert into w values (4,4,4,4,now()); Query OK, 1 row affected (0.01 sec) mysql> insert into w values (5,5,5,5,now()); Query OK, 1 row affected (0.00 sec) mysql> select * from w; +----+------+------+------+---------------------+ | id | a | b | c | d | +----+------+------+------+---------------------+ | 1 | 1 | 1 | 1 | 2022-01-06 15:55:27 | | 2 | 2 | 2 | 2 | 2022-01-06 15:55:35 | | 3 | 3 | 3 | 3 | 2022-01-06 15:55:45 | | 4 | 4 | 4 | 4 | 2022-01-06 15:55:54 | | 5 | 5 | 5 | 5 | 2022-01-06 15:56:01 | +----+------+------+------+---------------------+ 5 rows in set (0.00 sec) mysql> create index w1 on w (a); Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> create index w2 on w (b); Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> create index w3 on w (c); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> create index w4 on w (d); Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> explain select * from w where a=1; +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+ | 1 | SIMPLE | w | NULL | ref | w1 | w1 | 5 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec)
mysql> explain select * from w where a='1';
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
| 1 | SIMPLE | w | NULL | ref | w1 | w1 | 5 | const | 1 | 100.00 | NULL |
+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec) 这里注意，a是int，不管是=1还是=‘1’，他都自己转换了。还是可以用到索引 mysql> explain select * from w where b=1; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | w | NULL | ALL | w2 | NULL | NULL | NULL | 5 | 20.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ 1 row in set, 3 warnings (0.00 sec) mysql> explain select * from w where b='1'; +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+ | 1 | SIMPLE | w | NULL | ref | w2 | w2 | 43 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) 而b是字符串，字符串就是要输入字符串才行。 mysql> explain select * from w where c='1'; +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+ | 1 | SIMPLE | w | NULL | ref | w3 | w3 | 6 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.01 sec) mysql> explain select * from w where c=1; +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+ | 1 | SIMPLE | w | NULL | ref | w3 | w3 | 6 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.01 sec) mysql> c是数值型（浮点）。也继承了数值型的转换。 mysql> explain select * from w where d='2022-01-06 15:55:27'; +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+ | 1 | SIMPLE | w | NULL | ref | w4 | w4 | 6 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select * from w where d=2022-01-06 15:55:27; ERROR 1064 (42000): You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '15:55:27' at line 1 mysql> d是时间，时间只能这样写。Oracle的不允许这样，我们单独写一篇文章。 mysql> create table t as select * from w; Query OK, 5 rows affected (0.04 sec) Records: 5 Duplicates: 0 Warnings: 0 mysql> create index t1 on t (a); Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> create index t2 on t (b); Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> create index t3 on t (c); Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> create index t4 on t (d); Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 注意这里t表id没有主键没有索引。 mysql> explain select * from w,t where w.id=t.id; +----+-------------+-------+------------+--------+---------------+---------+---------+--------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+--------+---------------+---------+---------+--------+------+----------+-------+ | 1 | SIMPLE | t | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 100.00 | NULL | | 1 | SIMPLE | w | NULL | eq_ref | PRIMARY | PRIMARY | 4 | w.t.id | 1 | 100.00 | NULL | +----+-------------+-------+------------+--------+---------------+---------+---------+--------+------+----------+-------+ 2 rows in set, 1 warning (0.00 sec) 不带任何条件，可以看到t表全查，然后去找w表。 mysql> explain select * from w,t where w.id=t.id and w.id=1; +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | w | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | | 1 | SIMPLE | t | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 20.00 | Using where | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ 2 rows in set, 1 warning (0.00 sec) 如果w表id=1，那么用到索引，去找t表，t没有索引，全查。 mysql> explain select * from w,t where w.id=t.id and t.id=1; +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | w | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | | 1 | SIMPLE | t | NULL | ALL | NULL | NULL | NULL | NULL | 5 | 20.00 | Using where | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ 2 rows in set, 1 warning (0.01 sec) 如果t表id=1，那么优化器去改写 explain select * from w,t where w.id=t.id and t.id=1 and w.id=1那么和上一个执行计划是一样的。 mysql> explain select * from w,t where w.id=t.a and w.id=1; +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | 1 | SIMPLE | w | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | | 1 | SIMPLE | t | NULL | ref | t1 | t1 | 5 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ 2 rows in set, 1 warning (0.00 sec) 刚才是是关联列一个有索引一个没有，现在上面这个是都有索引。这个就是w找到1条，再去关联t，t的a也是int也有索引。所以也是1条。 很好。 mysql> explain select * from w,t where w.id=t.b and w.id=1; +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ | 1 | SIMPLE | w | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | | 1 | SIMPLE | t | NULL | ALL | t2 | NULL | NULL | NULL | 5 | 20.00 | Using where | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------------+ 2 rows in set, 3 warnings (0.00 sec) 现在关联列都有索引，但是数据类型不同一个是数值一个是字符串。这就不起作用了。因为触碰到了大忌。函数转换。 mysql> explain select * from w,t where w.id=t.c and w.id=1; +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------------+ | 1 | SIMPLE | w | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | | 1 | SIMPLE | t | NULL | ref | t3 | t3 | 6 | const | 1 | 100.00 | Using index condition | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------------+ 2 rows in set, 1 warning (0.00 sec) mysql> explain select * from w,t where w.id=t.d and w.id=1; +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------------+ | 1 | SIMPLE | w | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | | 1 | SIMPLE | t | NULL | ref | t4 | t4 | 6 | const | 1 | 100.00 | Using index condition | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-----------------------+ 2 rows in set, 3 warnings (0.00 sec) 再去关联不同类型的浮点型和日期型。其实也是类型不统一的、上面的c列和d列。 using index condition：搜索条件中虽然出现了索引列，但是有部分条件无法使用索引，会根据能用索引的条件先搜索一遍再匹配无法使用索引的条件。 实际上查查其实的确有的是差不到的。 mysql> select * from w,t where w.id=t.c and w.id=1; +----+------+------+------+---------------------+----+------+------+------+---------------------+ | id | a | b | c | d | id | a | b | c | d | +----+------+------+------+---------------------+----+------+------+------+---------------------+ | 1 | 1 | 1 | 1 | 2022-01-06 15:55:27 | 1 | 1 | 1 | 1 | 2022-01-06 15:55:27 | +----+------+------+------+---------------------+----+------+------+------+---------------------+ 1 row in set (0.00 sec) mysql> select * from w,t where w.id=t.d and w.id=1; Empty set, 2 warnings (0.00 sec) 结论：类型不一致关联，是会导致索引失效的。这没有问题，问题在于优化器对于两个关联列的左右顺序居然有不一样的效果。b列都是字符，c列都是数值。我猜想Oracle不会这样，后续去验证。 mysql> explain select * from w,t where w.b=t.c and w.b='1'; +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+ | 1 | SIMPLE | w | NULL | ref | w2 | w2 | 43 | const | 1 | 100.00 | Using index condition | | 1 | SIMPLE | t | NULL | ref | t3 | t3 | 6 | w.w.b | 1 | 100.00 | Using index condition | +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-----------------------+ 2 rows in set, 2 warnings (0.00 sec) mysql> explain select * from w,t where w.c=t.b and w.b='1'; +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+--------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+--------------------------------------------+ | 1 | SIMPLE | w | NULL | ref | w2,w3 | w2 | 43 | const | 1 | 100.00 | NULL | | 1 | SIMPLE | t | NULL | ALL | t2 | NULL | NULL | NULL | 5 | 20.00 | Using where; Using join buffer (hash join) | +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+--------------------------------------------+ 2 rows in set, 3 warnings (0.00 sec) 再次回到刚才。b是字符串只能这样。 mysql> explain select * from t where b='1';+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+| 1 | SIMPLE | t | NULL | ref | t2 | t2 | 43 | const | 1 | 100.00 | NULL |+----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+1 row in set, 1 warning (0.00 sec) 如果遇到整数索引会不可用 mysql> explain select * from t where b=1;+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+| 1 | SIMPLE | t | NULL | ALL | t2 | NULL | NULL | NULL | 5 | 20.00 | Using where |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+1 row in set, 3 warnings (0.00 sec) 建立函数索引。 mysql> create index t22 on t ( (cast(b as signed) )); Query OK, 0 rows affected (0.04 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select cast(b as signed) as bv from t; +------+ | bv | +------+ | 1 | | 2 | | 3 | | 4 | | 5 | +------+ 5 rows in set (0.00 sec) mysql> explain select * from t where cast(b as signed)=1; +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+ | 1 | SIMPLE | t | NULL | ref | t22 | t22 | 9 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+------+---------+-------+------+----------+-------+ 1 row in set, 1 warning (0.00 sec) mysql> 既然解决了这个。那么遇到类型转换就可以这样做了。 mysql> explain select * from w,t where w.id=cast(t.b as signed) and w.id=1; +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ | 1 | SIMPLE | w | NULL | const | PRIMARY | PRIMARY | 4 | const | 1 | 100.00 | NULL | | 1 | SIMPLE | t | NULL | ref | t22 | t22 | 9 | const | 1 | 100.00 | NULL | +----+-------------+-------+------------+-------+---------------+---------+---------+-------+------+----------+-------+ 2 rows in set, 1 warning (0.00 sec) Oracle和PG的后续发布。
