centos mysql 最佳化 第十三節課

標籤：

centos mysql  最佳化  第十三節課

 

 

MySQL索引

主索引值不能重複，也不能是NULL
unique key + not null 業務主鍵用唯一索引代替

案例：某InnoDB表，沒有自增列主鍵，使用一段時間後，產生片段，重整資料表空間後，從13G變成了9G
重整資料表空間的3種方法
alter table xx engine=innodb;
optimize table xx;
create primary key on id 加主鍵叢集索引

mysql5.6之後依然無法online ddl做 加主鍵叢集索引

 

 

首碼索引
部分索引原因
char/varchar太長全部做索引，效率太差，存在浪費
blob/text類型不支援整列作為索引，所以使用首碼索引

統計平均值
遵循2/8原則

 

文法：alter table t add index k1 ( username(9) );
username欄位的前9個字元

 

部分索引選擇建議
最小：3
最大：15
平均：8
count(distinct left(x, 9)) / total
count(distinct left(x, 8)) / total ≈ 60%
count(distinct left(x, 9)) / total ≈ 70%
count(distinct left(x, 10)) / total ≈ 85%
count(distinct left(x, 11)) / total ≈ 95%

最大百分比佔80%上的，就用作首碼索引

 

缺點
無法利用首碼索引完成排序

 

 

show table status; 查看錶總記錄數
葉金榮(4700963) 21:10:35
slow log
init-file

外鍵約束
為關聯欄位建立外鍵
所有的鍵都必須唯一
避免使用複合鍵
外鍵總是關聯唯一的鍵欄位

 

 

 

全文索引
5.6前 只支援myisam引擎
5.5之後，支援innodb引擎
5.7 以前中文支援很差
優先使用lucene\solr\shpinx

 

 

索引管理

create index idx on t（c1） using BTREE
create index idx on t（c1） using HASH
HEAP/MEMORY表才能使用hash索引

pt-index-usage原理：查一下slow log或general log的執行計畫，給出建議，只是建議

 

 

 

 

 

索引如何提高sql效率的

倒數第二條

a表是驅動表
b表是被驅動表
然後以a表的d欄位為排序並且有索引，那麼無法利用d欄位的索引完成排序
a join b

 

人為指定驅動表有風險，可能導致出來的結果集不一樣

 

 

a. from之前
b. where之前
c. xxx之前
d. t1之前
e. xxx之後
f. xxx之前
g .t1之後
g和b
from xxx t1 use index(k1) where

 

 

 

 

 

myisam索引特點
myisam主鍵索引

 

 

myisam輔助索引

 

 

 

 

innodb主鍵索引

 

innodb輔助索引

 

myisam和innodb索引對比

 

 

 

 

myisam是可以用NFS作為共用檔案系統，兩個執行個體同時讀一個資料檔案
一個執行個體讀寫，另一個執行個體唯讀，做高可用，myisam檔案可以多個進程開啟myisam檔案控制代碼

 

f

centos mysql 最佳化 第十三節課