一、mysql update主鍵更新很慢的原因

Analyze Table

MySQL 的Optimizer（優(yōu)化元件）在優(yōu)化SQL語句時(shí)，首先需要收集一些相關(guān)信息，其中就包括表的cardinality（可以翻譯為“散列程度”），它表示某個(gè)索引對(duì)應(yīng)的列包含多少個(gè)不同的值——如果cardinality大大少于數(shù)據(jù)的實(shí)際散列程度，那么索引就基本失效了。

我們可以使用SHOW INDEX語句來查看索引的散列程度：

SHOW INDEX FROM PLAYERS;

TABLE KEY_NAME COLUMN_NAME CARDINALITY

PLAYERS PRIMARY PLAYERNO 14

因?yàn)榇藭r(shí)PLAYER表中不同的PLAYERNO數(shù)量遠(yuǎn)遠(yuǎn)多于14，索引基本失效。

下面我們通過Analyze Table語句來修復(fù)索引：

ANALYZE TABLE PLAYERS;

SHOW INDEX FROM PLAYERS;

結(jié)果是：

TABLE KEY_NAME COLUMN_NAME CARDINALITY

PLAYERS PRIMARY PLAYERNO 1000

此時(shí)索引已經(jīng)修復(fù)，查詢效率大大提高。

需要注意的是，如果開啟了binlog，那么Analyze Table的結(jié)果也會(huì)寫入binlog，我們可以在analyze和table之間添加關(guān)鍵字local取消寫入。

Checksum Table

數(shù)據(jù)在傳輸時(shí)，可能會(huì)發(fā)生變化，也有可能因?yàn)槠渌驌p壞，為了保證數(shù)據(jù)的一致，我們可以計(jì)算checksum（校驗(yàn)值）。

使用MyISAM引擎的表會(huì)把checksum存儲(chǔ)起來，稱為live checksum，當(dāng)數(shù)據(jù)發(fā)生變化時(shí)，checksum會(huì)相應(yīng)變化。

在執(zhí)行Checksum Table時(shí)，可以在最后指定選項(xiàng)qiuck或是extended；quick表示返回存儲(chǔ)的checksum值，而extended會(huì)重新計(jì)算checksum，如果沒有指定選項(xiàng)，則默認(rèn)使用extended。

Optimize Table

經(jīng)常更新數(shù)據(jù)的磁盤需要整理碎片，數(shù)據(jù)庫也是這樣，Optimize Table語句對(duì)MyISAM和InnoDB類型的表都有效。

如果表經(jīng)常更新，就應(yīng)當(dāng)定期運(yùn)行Optimize Table語句，保證效率。

與Analyze Table一樣，Optimize Table也可以使用local來取消寫入binlog。

Check Table

數(shù)據(jù)庫經(jīng)常可能遇到錯(cuò)誤，譬如數(shù)據(jù)寫入磁盤時(shí)發(fā)生錯(cuò)誤，或是索引沒有同步更新，或是數(shù)據(jù)庫未關(guān)閉MySQL就停止了。

遇到這些情況，數(shù)據(jù)就可能發(fā)生錯(cuò)誤：

Incorrect key file for table: ’ ‘. Try to repair it.

此時(shí)，我們可以使用Check Table語句來檢查表及其對(duì)應(yīng)的索引。

譬如我們運(yùn)行

CHECK TABLE PLAYERS;

結(jié)果是

TABLE OP MSG_TYPE MSG_TEXT

TENNIS.PLAYERS check status OK

MySQL會(huì)保存表最近一次檢查的時(shí)間，每次運(yùn)行check table都會(huì)存儲(chǔ)這些信息：

執(zhí)行

SELECT TABLE_NAME, CHECK_TIME

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_NAME = ‘PLAYERS’

AND TABLE_SCHEMA = ‘TENNIS’; /TENNIS是數(shù)據(jù)庫名/

結(jié)果是

TABLE_NAME CHECK_TIME

PLAYERS 2006-08-21 16:44:25

Check Table還可以指定其它選項(xiàng)：

UPGRADE：用來測(cè)試在更早版本的MySQL中建立的表是否與當(dāng)前版本兼容。

QUICK：速度非常快的選項(xiàng)，在檢查各列的數(shù)據(jù)時(shí)，不會(huì)檢查鏈接（link）的正確與否，如果沒有遇到什么問題，可以使用這個(gè)選項(xiàng)。

FAST：只檢查表是否正常關(guān)閉，如果在系統(tǒng)掉電之后沒有遇到嚴(yán)重問題，可以使用這個(gè)選項(xiàng)。

CHANGED：只檢查上次檢查時(shí)間之后更新的數(shù)據(jù)。

MEDIUM：默認(rèn)的選項(xiàng)，會(huì)檢查索引文件和數(shù)據(jù)文件之間的鏈接正確性。

EXTENDED：最慢的選項(xiàng)，會(huì)進(jìn)行全面的檢查。

Repair Table

用于修復(fù)表，只對(duì)MyISAM和ARCHIVE類型的表有效。

這條語句同樣可以指定選項(xiàng)：

QUICK：非常快的選項(xiàng)，只修復(fù)索引樹。

EXTENDED：最慢的選項(xiàng)，需要逐行重建索引。

USE_FRM：只有當(dāng)MYI文件丟失時(shí)才使用這個(gè)選項(xiàng)，全面重建整個(gè)索引。

與Analyze Table一樣，Repair Table也可以使用local來取消寫入binlog。

延伸閱讀：

二、Navicat是什么

Navicat是一套快速、可靠和全面的數(shù)據(jù)庫管理工具，專門用于簡(jiǎn)化數(shù)據(jù)庫管理和降低管理成本。Navicat圖形界面直觀，提供簡(jiǎn)便的管理方法，設(shè)計(jì)和操作MySQL、MariaDB、SQL Server、Oracle、PostgreSQL和SQLite的數(shù)據(jù)。

Navicat提供一個(gè)直觀和設(shè)計(jì)完善的用戶界面，用于創(chuàng)建、修改和管理資料庫的所有對(duì)象，例如表、視圖、函數(shù)或過程、索引、觸發(fā)器和序列。我們的表設(shè)計(jì)器幫助用戶創(chuàng)建和修改數(shù)據(jù)庫的表，讓設(shè)置高級(jí)選項(xiàng)，如關(guān)系、限制、觸發(fā)器和更多。

使用Navicat瀏覽和修改數(shù)據(jù)，插入、編輯、刪除數(shù)據(jù)或復(fù)制和粘貼記錄到數(shù)據(jù)表形式的數(shù)據(jù)編輯器，Navicat將運(yùn)行相應(yīng)的命令（例如INSERT或UPDATE），免除寫復(fù)雜的SQL。廣泛的數(shù)據(jù)編輯工具令編輯工作更為方便，例如外鍵查找、set/enum選擇器和記錄篩選。