Mysql 在LONGTEXT欄位上作like操作的消耗;
# 先提供一些表的資訊:
===================================================================
: trac_apsara 17:18:46> show create table wiki G
*************************** 1. row ***************************
Table: wiki
Create Table: CREATE TABLE `wiki` (
`name` longtext COLLATE utf8_bin,
`version` int(11) DEFAULT NULL,
`time` bigint(20) DEFAULT NULL,
`author` longtext COLLATE utf8_bin,
`ipnr` longtext COLLATE utf8_bin,
`text` longtext COLLATE utf8_bin,
`comment` longtext COLLATE utf8_bin,
`readonly` int(11) DEFAULT NULL,
KEY `wiki_time_idx` (`time`),
KEY `name_ver_ind` (`name`(200),`version`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_bin
1 row in set (0.00 sec)
: trac_apsara 17:19:04> select count(*) from wiki;
+----------+
| count(*) |
+----------+
| 76514 |
+----------+
1 row in set (0.03 sec)
: trac_apsara 17:19:08> select count(distinct name ) from wiki;
+-----------------------+
| count(distinct name ) |
+-----------------------+
| 40369 |
+-----------------------+
1 row in set (0.59 sec)
: trac_apsara 17:19:21> show variables like 'innodb_buffer%';
+-------------------------+------------+
| Variable_name | Value |
+-------------------------+------------+
| innodb_buffer_pool_size | 1073741824 |
+-------------------------+------------+
1 row in set (0.00 sec)
: trac_apsara 17:21:08> show table status like 'wiki' G
*************************** 1. row ***************************
Name: wiki
Engine: InnoDB
Version: 10
Row_format: Compact
Rows: 336009
Avg_row_length: 4458
Data_length: 1498120192
Max_data_length: 0
Index_length: 10551296
Data_free: 7340032
Auto_increment: NULL
Create_time: 2010-09-29 14:49:20
Update_time: NULL
Check_time: NULL
Collation: utf8_bin
Checksum: NULL
Create_options:
Comment:
1 row in set (0.01 sec)
===================================================================
#下面我們來看一下SQL和資料:
## SQL1 :
SELECT w1.name,w1.time,w1.author,w1.text
FROM wiki w1,
(SELECT name,max(version) AS ver FROM wiki GROUP BY name) w2
WHERE w1.version = w2.ver AND w1.name = w2.name
AND (w1.name LIKE '%RpcRequestPtr%' ESCAPE '/'
OR w1.author LIKE '%RpcRequestPtr%' ESCAPE '/'
OR w1.text LIKE '%RpcRequestPtr%' ESCAPE '/'
);
## SQL2 :
SELECT w1.name,w1.time,w1.author,w1.text
FROM wiki w1,
(SELECT name,max(version) AS ver FROM wiki GROUP BY name) w2
WHERE w1.version = w2.ver AND w1.name = w2.name
AND (w1.name LIKE '%RpcRequestPtr%' ESCAPE '/'
OR w1.author LIKE '%RpcRequestPtr%' ESCAPE '/'
###### OR w1.text LIKE '%RpcRequestPtr%' ESCAPE '/'
);
兩個SQL僅一個WHERE條件之差。
: trac_apsara 17:24:08> explain SELECT w1.name,w1.time,w1.author,w1.text
-> FROM wiki w1,
-> (SELECT name,max(version) AS ver FROM wiki GROUP BY name) w2
-> WHERE w1.version = w2.ver AND w1.name = w2.name
-> AND (w1.name LIKE '%RpcRequestPtr%' ESCAPE '/'
-> OR w1.author LIKE '%RpcRequestPtr%' ESCAPE '/'
-> OR w1.text LIKE '%RpcRequestPtr%' ESCAPE '/'
-> );
+----+-------------+------------+------+---------------+--------------+---------+----------------+--------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+--------------+---------+----------------+--------+---------------------------------+
| 1 | PRIMARY |
| 1 | PRIMARY | w1 | ref | name_ver_ind | name_ver_ind | 608 | w2.name,w2.ver | 3 | Using where |
| 2 | DERIVED | wiki | ALL | NULL | NULL | NULL | NULL | 445724 | Using temporary; Using filesort |
+----+-------------+------------+------+---------------+--------------+---------+----------------+--------+---------------------------------+
3 rows in set (1.04 sec)
: trac_apsara 17:22:26> explain SELECT w1.name,w1.time,w1.author,w1.text
-> FROM wiki w1,
-> (SELECT name,max(version) AS ver FROM wiki GROUP BY name) w2
-> WHERE w1.version = w2.ver AND w1.name = w2.name
-> AND (w1.name LIKE '%RpcRequestPtr%' ESCAPE '/'
-> OR w1.author LIKE '%RpcRequestPtr%' ESCAPE '/'
-> # OR w1.text LIKE '%RpcRequestPtr%' ESCAPE '/'
-> );
+----+-------------+------------+------+---------------+--------------+---------+----------------+--------+---------------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+------------+------+---------------+--------------+---------+----------------+--------+---------------------------------+
| 1 | PRIMARY |
| 1 | PRIMARY | w1 | ref | name_ver_ind | name_ver_ind | 608 | w2.name,w2.ver | 3 | Using where |
| 2 | DERIVED | wiki | ALL | NULL | NULL | NULL | NULL | 445724 | Using temporary; Using filesort |
+----+-------------+------------+------+---------------+--------------+---------+----------------+--------+---------------------------------+
3 rows in set (1.03 sec)
### 從執行計劃來看,兩個SQL一模一樣; 處理的行數也是一樣的;
: trac_apsara 17:25:39> reset query cache ;
Query OK, 0 rows affected (0.00 sec)
: trac_apsara 17:25:52> SELECT w1.name,w1.time,w1.author,w1.text
-> FROM wiki w1,
-> (SELECT name,max(version) AS ver FROM wiki GROUP BY name) w2
-> WHERE w1.version = w2.ver AND w1.name = w2.name
-> AND (w1.name LIKE '%RpcRequestPtr%' ESCAPE '/'
-> OR w1.author LIKE '%RpcRequestPtr%' ESCAPE '/'
-> # OR w1.text LIKE '%RpcRequestPtr%' ESCAPE '/'
-> );
Empty set (1.31 sec)
: trac_apsara 17:26:12> reset query cache ;
Query OK, 0 rows affected (0.00 sec)
: trac_apsara 17:26:15> SELECT w1.name,w1.time,w1.author,w1.text
-> FROM wiki w1,
-> (SELECT name,max(version) AS ver FROM wiki GROUP BY name) w2
-> WHERE w1.version = w2.ver AND w1.name = w2.name
-> AND (w1.name LIKE '%RpcRequestPtr%' ESCAPE '/'
-> OR w1.author LIKE '%RpcRequestPtr%' ESCAPE '/'
-> OR w1.text LIKE '%RpcRequestPtr%' ESCAPE '/'
-> );
13 rows in set (3.50 sec)
## 從執行時間來看,
## SQL1 : 3.50 sec , SQL2: 1.31 sec
## 從這裡我們基本可以判斷出來,MYSQL 用了 2.19 sec 在記憶體中處理40369次TEXT欄位的LIKE模糊查詢操作;
## 而從WIKI表INDEX查詢40369次,卻只用了1.31秒(可能更少),當然資料已經在CACHE裡。
## 我們以後做SQL可要注意了。不光是讀硬碟會消耗時間,在記憶體中的LIKE模糊查詢操作,也很費時間;
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/703656/viewspace-1039115/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- mysql表操作(alter)/mysql欄位型別MySql型別
- PHP 操作 mysql blob 資料型別的欄位PHPMySql資料型別
- mysql++在64位作業系統上的安裝指南MySql作業系統
- MySQL:count(*) count(欄位) 實現上區別MySql
- MYSQL SET型別欄位的SQL操作知識介紹MySql型別
- 【MySql】mysql 欄位個數的限制MySql
- MySQL欄位的取值範圍MySql
- mysql的text欄位長度MySql
- MySQL 按照指定的欄位排序MySql排序
- MySQL 欄位約束MySql
- mysql中文欄位排序MySql排序
- MySQL欄位新增註釋,但不改變欄位的型別MySql型別
- mysql正則匹配解決查詢一個欄位是否在另一個欄位中MySql
- MySQL-刪除欄位MySql
- MySQL 大欄位問題MySql
- mysql表最多欄位數MySql
- MySQL 欄位擷取拼接MySql
- 要慎用mysql的enum欄位的原因MySql
- MongoDB(13)- 查詢操作返回指定的欄位MongoDB
- MySQL-去掉不為null的欄位MySqlNull
- MySQL中JSON欄位的使用技巧MySqlJSON
- MySQL 中 JSON 欄位的使用技巧MySqlJSON
- 5_MySQL 表的欄位約束MySql
- ArcGIS對欄位分割查詢操作
- MySQL欄位型別最全解析MySql型別
- MySQL-建立計算欄位MySql
- MySQL欄位型別小記MySql型別
- [MYSQL-10]計算欄位MySql
- MySQL修改欄位預設值MySql
- 在InfoPakcage 中消失的語言欄位
- 欄位修改記錄操作日誌的實現
- MySQL的欄位數量以及長度限制MySql
- 查mysql欄位中的數字記錄MySql
- mysql修改表、欄位、庫的字符集MySql
- MySQL中NULL欄位的比較問題MySqlNull
- mysql去除某些欄位重複的紀錄MySql
- MySQL 更新一個表裡的欄位等於另一個表某欄位的值MySql
- 使用不含萬用字元的like運算子遇到char型別的欄位時要注意!字元型別