处理一个数据类型问题想到的。先做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的后续发布。