資料型別與函式索引-MySQL篇
處理一個資料型別問題想到的。先做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/，如需轉載，請註明出處，否則將追究法律責任。
相關文章
- 資料型別與函式索引-PostgreSQL篇資料型別函式索引SQL
- 資料型別與函式索引-Oracle篇資料型別函式索引Oracle
- MySQL系列：資料型別、運算子及函式（5）MySql資料型別函式
- MySQL基礎之----資料型別篇(常用資料型別)MySql資料型別
- 《MySQL 基礎篇》七：資料型別MySql資料型別
- MySQL索引的型別MySql索引型別
- string型別資料的find函式型別函式
- sql server 資料型別轉換函式SQLServer資料型別函式
- Sql Server 資料型別 轉換 函式SQLServer資料型別函式
- Sql Server資料型別轉換函式SQLServer資料型別函式
- MySQL資料型別操作(char與varchar)MySql資料型別
- Java 支援的資料型別與 MySQL 支援的資料型別對比Java資料型別MySql
- 第 8 節：函式-函式型別與作用域函式型別
- MYSQL 資料型別MySQL 資料型別
- [Mysql]資料型別MySql資料型別
- MySQL資料型別MySql資料型別
- java 方法修改主函式裡基本資料型別和引用資料型別的區別Java函式資料型別
- MYSQL資料庫型別與JAVA型別對應關係MySql資料庫型別Java
- Sql Server系列：資料型別轉換函式SQLServer資料型別函式
- CHAR型別函式索引導致結果錯誤型別函式索引
- H2資料庫函式及資料型別概述資料庫函式資料型別
- python 與 Mysql 資料型別轉換PythonMySQL 資料型別
- MySQL字元資料型別char與varchar的區別MySql字元資料型別
- Sql Server函式全解(3)：資料型別轉換函式和文字影象函式SQLServer函式資料型別
- Sql Server函式全解(三)資料型別轉換函式和文字影像函式SQLServer函式資料型別
- 服務端指南 資料儲存篇 | MySQL（01） 資料型別的使用與選擇服務端MySql資料型別
- PHP：與型別有關的函式PHP型別函式
- sqlite 資料庫 支援的資料型別 以及常用的函式SQLite資料庫資料型別函式
- MySQL 的資料型別MySql資料型別
- 理解MySQL資料型別MySql資料型別
- MySQL的資料型別MySql資料型別
- MySQL基本資料型別MySql資料型別
- Mysql 資料型別之整數型別MySQL 資料型別
- 【MySQL資料型別3之--字元型別】MySql資料型別字元
- [Mysql] 3.Mysql 資料型別MySQL 資料型別
- 資料型別之字串篇資料型別字串
- MySQL函式索引及優化MySql函式索引優化
- MySQL 索引的型別——《高效能MySQL》MySql索引型別