優化大表上的簡單查詢
DB：MySQL 5.5.20（WampServer，預設配置）
作業系統：Win 7
硬碟：Western Digital 3TB Caviar Green，3.5"，IntelliPower，64MB，Sata3 (WD30EZRX)
記憶體：8GB
MySQL my.ini：http ://pastie.org /private/go9kaxlmlvirati2txbaa
有問題的查詢：
SELECT name.id AS name_id, name.name, cast_info.id,
cast_info.role_id, cast_info.movie_id
FROM cast_info
LEFT JOIN name ON name.id = cast_info.person_id
WHERE cast_info.movie_id = 1000000
ORDER BY cast_info.movie_id ASC
它會獲取所有為給定電影工作的人。問題是它可能需要 0.1 秒到近 2.0 秒。那太長了。當使用者需要執行它 10k 次時，他們不妨解除安裝應用程序。甚至我都沒有耐心等待它完成。
編輯：執行查詢所需的時間取決於處理它的人數。每 10 人大約 0.1 秒。
解釋：
****************************** 1. 行 ************************ *******
id: 1
select_type: SIMPLE
table: cast_info
type: ref
possible_keys: idx_mid,mpi
key: idx_mid
key_len: 4
ref: const
rows: 15
Extra:
************ ****************** 2. 行 ******************************
id: 1
select_type:簡單
表：名稱
類型：eq_ref
possible_keys：PRIMARY，id_name_idx
鍵：PRIMARY
key_len：4
ref：imdb.cast_info.person_id
行：1
額外的：
表：
CREATE TABLE
cast_info(
idint(11) NOT NULL AUTO_INCREMENT,
person_idint(11) NOT NULL,
movie_idint(11) NOT NULL,
person_role_idint(11) DEFAULT NULL,
notetext,
nr_orderint(11) DEFAULT NULL,
role_idint(11) NOT NULL,PRIMARY KEY (
id),KEY
idx_pid(person_id),KEY
idx_mid(movie_id),KEY
idx_cid(person_role_id),KEY
cast_info_role_id_exists(role_id),KEY
mpi(movie_id,person_id,id)) ENGINE=MyISAM AUTO_INCREMENT=33261692 預設字元集=utf8
創建表
name(
idint(11) NOT NULL AUTO_INCREMENT,
namevarchar(110) NOT NULL,
imdb_indexvarchar(12) DEFAULT NULL,
imdb_idint(11) DEFAULT NULL,
gendervarchar(1) DEFAULT NULL,
name_pcode_cfvarchar(5) DEFAULT NULL,
name_pcode_nfvarchar(5) DEFAULT NULL,
surname_pcodevarchar(5) DEFAULT NULL,
md5sumvarchar(32) DEFAULT NULL,PRIMARY KEY(
id),KEY
idx_name(name(6)),KEY
idx_imdb_id(imdb_id),KEY
idx_pcodecf(name_pcode_cf),KEY
idx_pcodenf(name_pcode_nf),KEY
idx_pcode(surname_pcode),KEY
idx_md5(md5sum),KEY
id_name_idx(id,name)) 引擎=MyISAM AUTO_INCREMENT=4287972 預設字元集=utf8
謝謝！
編輯：使用 MyISAM 是因為這是一個本地數據庫，由一個本地應用程序使用，由一個使用者使用。一次只執行一個查詢。也因為 IMDbPy 至少需要一個月的時間來使用 InnoDB 建構數據庫……
編輯：轉換為 InnoDB 後查詢 EXPLAIN：
****************************** 1. 行 ************************ *******
id: 1
select_type: SIMPLE
table: cast_info
type: ref
possible_keys: mpi
key: mpi
key_len: 3
ref: const
rows: 23
Extra:
************** ************* 2. 行 ***************************
id: 1
select_type: SIMPLE
table ：名稱
類型：eq_ref
possible_keys：PRIMARY，id_name_idx
鍵：PRIMARY
key_len：4
ref：imdb.cast_info.person_id
行：1
額外的：
使用 InnoDB 會執行得更快，
PRIMARY KEY是“集群的” ；這使得查找name速度更快。- 一個合適的設置
innodb_buffer_pool_size，比如說，5G（對於你的 8GB 機器）會記憶體很多東西，從而最小化 10K 操作的 I/O。您還可以在適當的情況下使用
MEDIUMINT UNSIGNED或來縮小數據（並減少 I/O） 。SMALLINT UNSIGNED有可以（應該）刪除的冗餘索引；這將加快負載。也
name(6)可能是沒用的。