MySQL 重複索引探討(持續更新中...)

標籤：

資料參考：http://xiezhenye.com/2015/01/%E6%89%BE%E5%88%B0-mysql-%E6%95%B0%E6%8D%AE%E5%BA%93%E4%B8%AD%E7%9A%84%E4%B8%8D%E8%89%AF%E7%B4%A2%E5%BC%95.html

<一> 建立‘有問題的‘表 1.建立表test1

CREATE TABLE test1 (  id int(11) NOT NULL,  f1 int(11) DEFAULT NULL,  f2 int(11) DEFAULT NULL,  f3 int(11) DEFAULT NULL,  PRIMARY KEY (id),  KEY k1 (f1,id),  KEY k2 (id,f1),  KEY k3 (f1),  KEY k4 (f1,f3),  KEY k5 (f1,f3,f2))

2.建立表 test2

CREATE TABLE test2 (  id1 int(11) NOT NULL DEFAULT 0,  id2 int(11) NOT NULL DEFAULT 0,  b int(11) DEFAULT NULL,  PRIMARY KEY (id1,id2),  KEY k1 (b))

<二> 存在問題的索引 1. 包含主鍵的索引

innodb 本身是聚簇表，每個二級索引本身就包含主鍵，類似f1,id 的索引，雖然實際沒什麼害處，但反映使用者對mysql 索引的不瞭解。而 id,f1 這種多餘索引，會浪費儲存空間，並影響資料更新效能。包含主鍵的索引用這樣一句sql 就能全部找出來：

select c.*, pk from        (select table_schema, table_name, index_name, concat(‘|‘, group_concat(column_name order by seq_in_index separator ‘|‘), ‘|‘) cols          from INFORMATION_SCHEMA.STATISTICS          where index_name != ‘PRIMARY‘ and table_schema != ‘mysql‘     group by table_schema, table_name, index_name) c,       (select table_schema, table_name, concat(‘|‘, group_concat(column_name order by seq_in_index separator ‘|‘), ‘|‘) pk          from INFORMATION_SCHEMA.STATISTICS          where index_name = ‘PRIMARY‘ and table_schema != ‘mysql‘     group by table_schema, table_name) p       where c.table_name = p.table_name and c.table_schema = p.table_schema and c.cols like concat(‘%‘, pk, ‘%‘);

結果：

2.重複的索引

包含重複首碼的索引，索引能由另一個包含該首碼的索引完全代替，是多餘索引。多餘的索引會浪費儲存空間，並影響資料更新效能。這樣的索引同樣用一句 sql 可以找出來。

select c1.table_schema, c1.table_name, c1.index_name,c1.cols,c2.index_name, c2.cols from       (select table_schema, table_name, index_name, concat(‘|‘, group_concat(column_name order by seq_in_index separator ‘|‘), ‘|‘) cols          from INFORMATION_SCHEMA.STATISTICS          where table_schema != ‘mysql‘ and index_name!=‘PRIMARY‘     group by table_schema,table_name,index_name) c1,          (select table_schema, table_name,index_name, concat(‘|‘, group_concat(column_name order by seq_in_index separator ‘|‘), ‘|‘) cols          from INFORMATION_SCHEMA.STATISTICS          where table_schema != ‘mysql‘ and index_name != ‘PRIMARY‘     group by table_schema, table_name, index_name) c2      where c1.table_name = c2.table_name and c1.table_schema = c2.table_schema and c1.cols like concat(c2.cols, ‘%‘) and c1.index_name != c2.index_name;

結果：

3. 低區分度索引

這樣的索引由於仍然會掃描大量記錄，在實際查詢時通常會被忽略。但是在某些情況下仍然是有用的。因此需要根據實際情況進一步分析。這裡是區分度小於 10% 的索引，可以根據需要調整參數。

select p.table_schema, p.table_name, c.index_name, c.car, p.car total from       (select table_schema, table_name, index_name, max(cardinality) car         from INFORMATION_SCHEMA.STATISTICS     where index_name != ‘PRIMARY‘     group by table_schema, table_name,index_name) c,       (select table_schema, table_name, max(cardinality) car         from INFORMATION_SCHEMA.STATISTICS     where index_name = ‘PRIMARY‘ and table_schema != ‘mysql‘     group by table_schema,table_name) p     where c.table_name = p.table_name and c.table_schema = p.table_schema and p.car > 0 and c.car / p.car < 0.1;

結果：

4. 複合主鍵

由於 innodb 是聚簇表，每個二級索引都會包含主索引值。複合主鍵會造成二級索引龐大，而影響二級索引查詢效能，並影響更新效能。同樣需要根據實際情況進一步分析。

sql 為：

select table_schema, table_name, group_concat(column_name order by seq_in_index separator ‘,‘) cols, max(seq_in_index) len        from INFORMATION_SCHEMA.STATISTICS        where index_name = ‘PRIMARY‘ and table_schema != ‘mysql‘        group by table_schema, table_name having len>1;

結果為：

MySQL 重複索引探討(持續更新中...)