简单分析MySQL中的primary key功能_MySQL-mysql教程-PHP中文網

首頁

資料庫

mysql教程

简单分析MySQL中的primary key功能_MySQL

WBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWBOYWB

Jun 01, 2016 pm 01:00 PM

mysql

在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中测试：

$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：

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：

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

$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：

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在选择方式上也在慢慢智能了。

观察性能：

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 |

+——————————–+———-+———-+————+————–+—————+

登入後複製

对比性能：

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。

本網站聲明

本文內容由網友自願投稿，版權歸原作者所有。本站不承擔相應的法律責任。如發現涉嫌抄襲或侵權的內容，請聯絡admin@php.cn

熱AI工具

Undresser.AI Undress

人工智慧驅動的應用程序，用於創建逼真的裸體照片

AI Clothes Remover

用於從照片中去除衣服的線上人工智慧工具。

Undress AI Tool

免費脫衣圖片

Clothoff.io

AI脫衣器

AI Hentai Generator

免費產生 AI 無盡。

熱工具

記事本++7.3.1

好用且免費的程式碼編輯器

SublimeText3漢化版

中文版，非常好用

禪工作室 13.0.1

強大的PHP整合開發環境

Dreamweaver CS6

視覺化網頁開發工具

SublimeText3 Mac版

神級程式碼編輯軟體(SublimeText3)

熱門話題

gmail信箱登陸入口在哪裡

7554

CakePHP 教程

1382

steam的賬戶名稱是什麼格式

win11激活密鑰永久

NYT連接提示和答案

Related knowledge

mysql：簡單的概念，用於輕鬆學習 Apr 10, 2025 am 09:29 AM

MySQL是一個開源的關係型數據庫管理系統。 1）創建數據庫和表：使用CREATEDATABASE和CREATETABLE命令。 2）基本操作：INSERT、UPDATE、DELETE和SELECT。 3）高級操作：JOIN、子查詢和事務處理。 4）調試技巧：檢查語法、數據類型和權限。 5）優化建議：使用索引、避免SELECT*和使用事務。

phpmyadmin怎麼打開 Apr 10, 2025 pm 10:51 PM

可以通過以下步驟打開 phpMyAdmin：1. 登錄網站控制面板；2. 找到並點擊 phpMyAdmin 圖標；3. 輸入 MySQL 憑據；4. 點擊 "登錄"。

MySQL：世界上最受歡迎的數據庫的簡介 Apr 12, 2025 am 12:18 AM

MySQL是一種開源的關係型數據庫管理系統，主要用於快速、可靠地存儲和檢索數據。其工作原理包括客戶端請求、查詢解析、執行查詢和返回結果。使用示例包括創建表、插入和查詢數據，以及高級功能如JOIN操作。常見錯誤涉及SQL語法、數據類型和權限問題，優化建議包括使用索引、優化查詢和分錶分區。

為什麼要使用mysql？利益和優勢 Apr 12, 2025 am 12:17 AM

選擇MySQL的原因是其性能、可靠性、易用性和社區支持。 1.MySQL提供高效的數據存儲和檢索功能，支持多種數據類型和高級查詢操作。 2.採用客戶端-服務器架構和多種存儲引擎，支持事務和查詢優化。 3.易於使用，支持多種操作系統和編程語言。 4.擁有強大的社區支持，提供豐富的資源和解決方案。

redis怎麼使用單線程 Apr 10, 2025 pm 07:12 PM

Redis 使用單線程架構，以提供高性能、簡單性和一致性。它利用 I/O 多路復用、事件循環、非阻塞 I/O 和共享內存來提高並發性，但同時存在並發性受限、單點故障和不適合寫密集型工作負載的局限性。

MySQL和SQL：開發人員的基本技能 Apr 10, 2025 am 09:30 AM

MySQL和SQL是開發者必備技能。 1.MySQL是開源的關係型數據庫管理系統，SQL是用於管理和操作數據庫的標準語言。 2.MySQL通過高效的數據存儲和檢索功能支持多種存儲引擎，SQL通過簡單語句完成複雜數據操作。 3.使用示例包括基本查詢和高級查詢，如按條件過濾和排序。 4.常見錯誤包括語法錯誤和性能問題，可通過檢查SQL語句和使用EXPLAIN命令優化。 5.性能優化技巧包括使用索引、避免全表掃描、優化JOIN操作和提升代碼可讀性。

MySQL的位置：數據庫和編程 Apr 13, 2025 am 12:18 AM

MySQL在數據庫和編程中的地位非常重要，它是一個開源的關係型數據庫管理系統，廣泛應用於各種應用場景。 1）MySQL提供高效的數據存儲、組織和檢索功能，支持Web、移動和企業級系統。 2）它使用客戶端-服務器架構，支持多種存儲引擎和索引優化。 3）基本用法包括創建表和插入數據，高級用法涉及多表JOIN和復雜查詢。 4）常見問題如SQL語法錯誤和性能問題可以通過EXPLAIN命令和慢查詢日誌調試。 5）性能優化方法包括合理使用索引、優化查詢和使用緩存，最佳實踐包括使用事務和PreparedStatemen

怎樣建立sql數據庫 Apr 09, 2025 pm 04:24 PM

構建 SQL 數據庫涉及 10 個步驟：選擇 DBMS；安裝 DBMS；創建數據庫；創建表；插入數據；檢索數據；更新數據；刪除數據；管理用戶；備份數據庫。

See all articles

简单分析MySQL中的primary key功能_MySQL

熱AI工具

Undresser.AI Undress

AI Clothes Remover

Undress AI Tool

Clothoff.io

AI Hentai Generator

熱門文章

熱工具

記事本++7.3.1

SublimeText3漢化版

禪工作室 13.0.1

Dreamweaver CS6

SublimeText3 Mac版

熱門話題