簡單分析MySQL中的primary key功能
在5.1.46中最佳化器在對primary key的選擇上做了一點改動:
Performance: While looking for the shortest index for a covering index scan, the optimizer did not consider the full row length for a clustered primary key, as in InnoDB. Secondary covering indexes will now be preferred, making full table scans less likely。
該版本中增加了find_shortest_key函式,該函式的作用可以認為是選擇最小key length的
索引來滿足我們的查詢。
該函式是怎麼工作的:
複製程式碼 程式碼如下:What find_shortest_key should do is the following. If the primary key is a covering index
and is clustered, like in MyISAM, then the behavior today should remain the same. If the
primary key is clustered, like in InnoDB, then it should not consider using the primary
key because then the storage engine will have to scan through much more data.
呼叫Primary_key_is_clustered(),當返回值為true,執行find_shortest_key:選擇key length最小的覆蓋索引(Secondary covering indexes),然後來滿足查詢。
首先在5.1.45中測試:
?
123456789101112131415161718192021 |
$mysql -V mysql Ver 14.14 Distrib 5.1.45, for unknown-linux-gnu (x86_64) using EditLine wrapper root@ test 03:49:45>create table test ( id int,name varchar(20),name2 varchar(20),d datetime,primary key( id )) engine=innodb; Query OK, 0 rows affected (0.16 sec) root@ test 03:49:47>insert into test values(1, 'xc' , 'sds' ,now()),(2, 'xcx' , 'dd' ,now()),(3, 'sdds' , 'ddd' ,now()),(4, 'sdsdf' , 'dsd' ,now()),(5, 'sdsdaa' , 'sds' ,now()); Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 root@ test 03:49:51> root@ test 03:49:51>insert into test values(6, 'xce' , 'sdsd' ,now()),(7, 'xcx' , 'sdsd' ,now()),(8, 'sdds' , 'sds' ,now()),(9, 'sdsdsdf' , 'sdsdsd' ,now()),(10, 'sdssdfdaa' , 'sdsdsd' ,now()); Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0
|
建立索引ind_1:
?
12345678910111213141516171819 |
root@test 03:49:53> alter table test add index ind_1( name ,d); Query OK, 0 rows affected (0.09 sec) Records: 0 Duplicates: 0 Warnings: 0 root@test 03:50:08>explain select count (*) from test; +—-+————-+——-+——-+—————+———+———+——+——+————-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +—-+————-+——-+——-+—————+———+———+——+——+————-+ | 1 | SIMPLE | test | index | NULL | PRIMARY | 4 | NULL | 10 | Using index | +—-+————-+——-+——-+—————+———+———+——+——+————-+ 1 row in set (0.00 sec)
|
新增ind_2:
?
12345678910111213141516171819 |
root@test 08:04:35> alter table test add index ind_2(d); Query OK, 0 rows affected (0.07 sec) Records: 0 Duplicates: 0 Warnings: 0 root@test 08:04:45>explain select count (*) from test; +—-+————-+——-+——-+—————+———+———+——+——+————-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +—-+————-+——-+——-+—————+———+———+——+——+————-+ | 1 | SIMPLE | test | index | NULL | PRIMARY | 4 | NULL | 10 | Using index | +—-+————-+——-+——-+—————+———+———+——+——+————-+ 1 row in set (0.00 sec)
|
上面的版本【5.1.45】中,可以看到最佳化器選擇使用主鍵來完成掃描,並沒有使用ind_1,ind_2來完成查詢;
接下來是:5.1.48
?
12345678910111213141516171819 |
$mysql -V mysql Ver 14.14 Distrib 5.1.48, for unknown-linux-gnu (x86_64) using EditLine wrapper root@ test 03:13:15> create table test ( id int,name varchar(20),name2 varchar(20),d datetime,primary key( id )) engine=innodb; Query OK, 0 rows affected (0.00 sec) root@ test 03:48:04>insert into test values(1, 'xc' , 'sds' ,now()),(2, 'xcx' , 'dd' ,now()),(3, 'sdds' , 'ddd' ,now()),(4, 'sdsdf' , 'dsd' ,now()),(5, 'sdsdaa' , 'sds' ,now()); Query OK, 5 rows affected (0.00 sec) Records: 5 Duplicates: 0 Warnings: 0 root@ test 03:48:05>insert into test values(6, 'xce' , 'sdsd' ,now()),(7, 'xcx' , 'sdsd' ,now()),(8, 'sdds' , 'sds' ,now()),(9, 'sdsdsdf' , 'sdsdsd' ,now()),(10, 'sdssdfdaa' , 'sdsdsd' ,now()); Query OK, 5 rows affected (0.01 sec) Records: 5 Duplicates: 0 Warnings: 0
|
建立索引ind_1:
?
123456789101112131415161718192021222324252627282930313233343536373839 |
root@test 03:13:57> alter table test add index ind_1( name ,d); Query OK, 0 rows affected (0.01 sec) Records: 0 Duplicates: 0 Warnings: 0 root@test 03:15:55>explain select count (*) from test; +—-+————-+——-+——-+—————+——-+———+——+——+————-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +—-+————-+——-+——-+—————+——-+———+——+——+————-+ | 1 | SIMPLE | test | index | NULL | ind_1 | 52 | NULL | 10 | Using index | +—-+————-+——-+——-+—————+——-+———+——+——+————-+ root@test 08:01:56> alter table test add index ind_2(d); Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 新增ind_2: root@test 08:02:09>explain select count (*) from test; +—-+————-+——-+——-+—————+——-+———+——+——+————-+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +—-+————-+——-+——-+—————+——-+———+——+——+————-+ | 1 | SIMPLE | test | index | NULL | ind_2 | 9 | NULL | 10 | Using index | +—-+————-+——-+——-+—————+——-+———+——+——+————-+ 1 row in set (0.00 sec)
|
版本【5.1.48】中首先明智的選擇ind_1來完成掃描,並沒有考慮到使用主鍵(全索引掃描)來完成查詢,隨後新增ind_2,由於 ind_1的key長度是大於ind_2 key長度,所以mysql選擇更優的ind_2來完成查詢,可以看到mysql在選擇方式上也在慢慢智慧了。
觀察效能:
?
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263 |
5.1.48 root@test 08:49:32> set profiling =1; Query OK, 0 rows affected (0.00 sec) root@test 08:49:41> select count (*) from test; +———-+ | count (*) | +———-+ | 5242880 | +———-+ 1 row in set (1.18 sec) root@test 08:56:30>show profile cpu,block io for query 1; +——————————–+———-+———-+————+————–+—————+ | Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | +——————————–+———-+———-+————+————–+—————+ | starting | 0.000035 | 0.000000 | 0.000000 | 0 | 0 | | checking query cache for query | 0.000051 | 0.000000 | 0.000000 | 0 | 0 | | Opening tables | 0.000014 | 0.000000 | 0.000000 | 0 | 0 | | System lock | 0.000005 | 0.000000 | 0.000000 | 0 | 0 | | Table lock | 0.000010 | 0.000000 | 0.000000 | 0 | 0 | | init | 0.000015 | 0.000000 | 0.000000 | 0 | 0 | | optimizing | 0.000007 | 0.000000 | 0.000000 | 0 | 0 | | statistics | 0.000015 | 0.000000 | 0.000000 | 0 | 0 | | preparing | 0.000012 | 0.000000 | 0.000000 | 0 | 0 | | executing | 0.000007 | 0.000000 | 0.000000 | 0 | 0 | | Sending data | 1.178452 | 1.177821 | 0.000000 | 0 | 0 | | end | 0.000016 | 0.000000 | 0.000000 | 0 | 0 | | query end | 0.000005 | 0.000000 | 0.000000 | 0 | 0 | | freeing items | 0.000040 | 0.000000 | 0.000000 | 0 | 0 | | logging slow query | 0.000002 | 0.000000 | 0.000000 | 0 | 0 | | logging slow query | 0.000086 | 0.000000 | 0.000000 | 0 | 0 | | cleaning up | 0.000006 | 0.000000 | 0.000000 | 0 | 0 | +——————————–+———-+———-+————+————–+—————+
|
對比效能:
?
123456789101112131415161718192021222324252627282930313233343536373839404142434445464748495051525354555657585960616263 |
5.1.45 root@test 08:57:18> set profiling =1; Query OK, 0 rows affected (0.00 sec) root@test 08:57:21> select count (*) from test; +———-+ | count (*) | +———-+ | 5242880 | +———-+ 1 row in set (1.30 sec) root@test 08:57:27>show profile cpu,block io for query 1; +——————————–+———-+———-+————+————–+—————+ | Status | Duration | CPU_user | CPU_system | Block_ops_in | Block_ops_out | +——————————–+———-+———-+————+————–+—————+ | starting | 0.000026 | 0.000000 | 0.000000 | 0 | 0 | | checking query cache for query | 0.000041 | 0.000000 | 0.000000 | 0 | 0 | | Opening tables | 0.000014 | 0.000000 | 0.000000 | 0 | 0 | | System lock | 0.000005 | 0.000000 | 0.000000 | 0 | 0 | | Table lock | 0.000008 | 0.000000 | 0.000000 | 0 | 0 | | init | 0.000015 | 0.000000 | 0.000000 | 0 | 0 | | optimizing | 0.000006 | 0.000000 | 0.000000 | 0 | 0 | | statistics | 0.000014 | 0.000000 | 0.000000 | 0 | 0 | | preparing | 0.000012 | 0.000000 | 0.000000 | 0 | 0 | | executing | 0.000007 | 0.000000 | 0.000000 | 0 | 0 | | Sending data | 1.294178 | 1.293803 | 0.000000 | 0 | 0 | | end | 0.000016 | 0.000000 | 0.000000 | 0 | 0 | | query end | 0.000004 | 0.000000 | 0.000000 | 0 | 0 | | freeing items | 0.000040 | 0.000000 | 0.001000 | 0 | 0 | | logging slow query | 0.000002 | 0.000000 | 0.000000 | 0 | 0 | | logging slow query | 0.000080 | 0.000000 | 0.000000 | 0 | 0 | | cleaning up | 0.000006 | 0.000000 | 0.000000 | 0 | 0 | +——————————–+———-+———-+————+————–+—————+
|
從上面的profile中可以看到在Sending data上,差異還是比較明顯的,mysql不需要掃描整個表的頁塊,而是掃描表中索引key最短的索引頁塊來完成查詢,這樣就減少了很多不必要的資料。
PS:innodb是事務引擎,所以在葉子節點中除了儲存本行記錄外,還會多記錄一些關於事務的資訊(DB_TRX_ID ,DB_ROLL_PTR 等),因此單行長度額外開銷20個位元組左右,最直觀的方法是將myisam轉為innodb,儲存空間會明顯上升。那麼在主表為t(id,name,pk(id)),二級索引ind_name(name,id),這個時候很容易混淆,即使只有兩個欄位,第一索引還是比第二索引要大(可以透過innodb_table_monitor觀察表的的內部結構)在查詢所有id的時候,最佳化器還是會選擇第二索引ind_name。
來自 “ ITPUB部落格 ” ,連結:http://blog.itpub.net/758/viewspace-2805476/,如需轉載,請註明出處,否則將追究法律責任。
相關文章
- mysql中key 、primary key 、unique key 與index區別MySqlIndex
- sql primary key procedureSQL
- MySQL修復表的簡單分析MySql
- unique index與primary key的區別Index
- oracle資料庫primary key和unique key的異同Oracle資料庫
- SQL Server Primary Key ConstraintsSQLServerAI
- 關於primary key和foreign key的問題處理
- Object.keys()的簡單理解Object
- Flutter key簡單介紹Flutter
- 對 MySQL 慢查詢日誌的簡單分析MySql
- MySQL斷電恢復的一點簡單分析MySql
- 簡單解析MySQL中的cardinality異常MySql
- 簡單瞭解 MySQL 中相關的鎖MySql
- MySQL實戰 | 06/07 簡單說說MySQL中的鎖MySql
- 停止MySQL服務hang的問題簡單分析(一)MySql
- ITEM中的UNIT OF PRIMARY.
- ALTER TABLE causes auto_increment resulting key 'PRIMARY'REM
- MySQL中的事務和鎖簡單測試MySql
- Java 實現《編譯原理》簡單詞法分析功能Java編譯原理詞法分析
- mysql_to_mysql的gg簡單配置MySql
- MySQL explain 中 key_len的詳解MySqlAI
- mysql的簡單基本操作MySql
- 簡單的mysql查詢MySql
- 如何證明INNODB輔助索引葉子結點KEY值相同的按照PRIMARY KEY排序索引排序
- C語言呼叫mysql資料庫API實現簡單的mysql客戶端的功能C語言MySql資料庫API客戶端
- 簡單的UrlDns鏈分析DNS
- mysql的event的簡單使用MySql
- 打造簡單的依賴注入功能依賴注入
- ExplosionField簡單分析
- ORA-02429: cannot drop index used for enforcement of unique/primary keyIndex
- .Text中的TrackBack功能簡介
- 兩種簡單分析和優化MySQL資料庫表的方法優化MySql資料庫
- Angularjs製作簡單的路由功能簡單程式碼例項AngularJS路由
- 簡單扯扯PyCharm4.5中新加的Python Profiler功能PyCharmPython
- 原生小程式最最簡單的分享功能
- 關於簡單的js計步功能JS
- mysql 簡單安裝MySql
- Mysql 簡單入門MySql