知識點：Mysql 索引最佳化實戰（3）

標籤：冗餘   null   esc   mysq   www   公式   default   rem   date   

 知識點：Mysql 索引原理完全手冊(1)知識點：Mysql 索引原理完全手冊(2)知識點：Mysql 索引最佳化實戰(3) 索引原理知識回顧索引的效能分析和最佳化

通過 EXPLAIN 來判斷 SQL 的執行計畫，發現慢 SQL 或者效能影響業務的 sql

explain [EXTENDED] SELECT...

 

查看執行計畫會有如下資訊：

id:1select_type:simpletable:tpossible_keys:primarykey:primarykey_len:4ref:constrows:1filtered:100.00extra:using index

 

關於 key_len 長度計算公式：

varchar(10) 變長欄位且允許 NULL：10_(Character Set:utf-8,gbk=2,latin1=1)+1(NULL)+2(變長欄位)varchar(10) 變長欄位且不允許 NULL：10_(Character Set:utf-8,gbk=2,latin1=1)+2(變長欄位)char(10) 變長欄位且允許 NULL：10_(Character Set:utf-8,gbk=2,latin1=1)+1(NULL)char(10) 變長欄位且不允許 NULL：10_(Character Set:utf-8,gbk=2,latin1=1)

 

預設 null，會佔用位元組，索引長度。 也就是說索引 key_len 長度過大，也會影響 SQL 效能。

4.1 索引提高 SQL 效率的方法

• 利用索引加快查詢速度
• 行記錄檢索
• 從索引記錄中直接返回結果（聯合索引）

min(), max()order by group by distinct

 

如果列定義為 DEFAULT NULL 時，NULL 值也會有索引，存放在索引樹的最前端部分。 案例 1：

CREATE TABLE `base_assets` (  `ID` int(11) unsigned NOT NULL AUTO_INCREMENT,  `ASSETS1` int(11) DEFAULT ‘0‘,  `ASSETS2` int(10) unsigned DEFAULT NULL,  `ASSETS5` int(10) unsigned NOT NULL DEFAULT ‘0‘,  `ASSETS3` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_STAMP,  `ASSETS4` varchar(200) NOT NULL DEFAULT,  PRIMARY KEY (`ID`)  KEY ‘idx_A1‘ (`ASSETS1`),  KEY `key_c2` (`ASSETS2`)) ENGINE=InnoDB AUTO_INCREMENT=512976 DEFAULT CHARSET=utf8;

 

表說明：

• 500 萬行記錄，ASSETS11、ASSETS12、ASSETS15 三個列值完全一樣，但定義不一樣：
• ASSETS1 列定義為 NOT NULL DEFAULT 0，有索引
• ASSETS2 列定義為 DEFAULT NULL，有索引
• ASSETS5 列定義為 NOT NULL DEFAULT 0，無索引

# 查詢explain select ASSETS1 from base_assets where ASSETS1 = 100000 limit 1;# 對比explain select ASSETS5 from base_assets where ASSETS5 = 100000 limit 1;# 統計類業務：explain select max(ASSETS2) from base_assets;# 求平均值，有索引時，掃描索引即可，無需全表掃描（避免回表）explain select avg(ASSETS1) from base_assets;

 

4.2 利用索引提高排序效率

# 查詢explain select ASSETS5 from base_assets where ASSETS5 > 100000  order by ASSETS5 limit 100;# 有索引，可以快速排序完成explain select ASSETS5 from base_assets where ASSETS1 > 100000  order by ASSETS1 limit 100;# 讀寫的列改成c1explain select ASSETS1 from base_assets where ASSETS1 > 100000  order by ASSETS1 limit 100;

 

結果可以再次表明不同的執行計畫效能差距。（圖略）

4.3 NOT NULL 和 DEFAULT NULL 的區別

desc select count(ASSETS1) from base_assets;desc select count(ASSETS2) from base_assets;desc select count(ASSETS1) from base_assets where ASSETS1 is null;desc select count(ASSETS2) from base_assets where ASSETS2 is null;

 

4.4 利用 index merge - Using union

desc select * form base_assets where ASSETS1 = 2333 or ASSETS2 = 6666

 

案例 2:

# 測試索引寫入效率create bable base_assets_test (id int unsigned not null auto_increment,assets1 int not null default ‘0‘,assets2 int not null default ‘0‘,assets3 int not null default ‘0‘,assets4 int not null default ‘0‘,assets5 timestamp null,assets6 varchar(200) not null default ‘‘,primary key(‘id‘),KEY `idx_c2`(`assets2`),KEY `idx_c3` (`assets3`));# 測試有無索引對比寫入效率預存程序delimiter $$$create procedure ‘insert_test‘(in row_num int)begin declare i int default 1;while i <= row_num do insert into base_assets_test(id,assets1,assets2,assets3,assets4,assets5,assets6)     values(i,floor(rand()_row_num),floor(rand()_row_num),floor(rand()_row_num),            now(),repeat(‘wubx‘,floor(rand()*)20)));set i = i + 1;end while;end $$$

 

用戶端調用：call insert_test (1 000 000);

插入初始化資料：

1	模式	耗時
innodb	無索引	110
innodb	只有主鍵索引	110
innodb	下全部索引	110
myisam	無任何索引	24
myisam	只有主鍵索引	27
myisam	全部索引	31

小結

• 建議低選擇性的列不加索引，如性別，姓名；
• 選擇性高的欄位放在前面，常用的欄位放在前面；
• 需要經常排序的欄位，可加到索引中，列順序和最常用的排序一致；
• 對較長的字元資料類型的欄位建索引，優先考慮首碼索引，如 index(url(64))
• 只建立需要的索引，避免冗餘索引，如：index(a,b)，index(a)

InnoDB 表主鍵、索引

• Innodb 表每一個表都要顯式設定主鍵；
• 主鍵越短越好，最好是自增類型；如果不能使用自增，則應考慮構造使用單向遞增型主鍵，禁止使用隨機類型值用於主鍵；
• 主鍵最好由一個欄位構成，組合主鍵不允許超過 3 個欄位。如果業務需求，則可以建立一個自增欄位作為主鍵，再添加一個唯一索引；
• 選擇作為主鍵的列必須在插入後不再修改或者極少修改，否則需考慮使用自增列作為主鍵；
• 如果一個業務上存在多個 (組) 唯一鍵，以查詢最常用的唯一鍵作為主鍵。

over

 

知識點：Mysql 索引最佳化實戰（3）