資料型別與函式索引-MySQL篇

xuexiaogang發表於2022-01-06

處理一個資料型別問題想到的。先做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的後續釋出。





來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/637517/viewspace-2850998/,如需轉載,請註明出處,否則將追究法律責任。

相關文章