索引
-
索引
- MySQL索引的建立對(duì)于MySQL的高效運(yùn)行是很重要的缨睡，索引可以大大提高M(jìn)ySQL的檢索速度燃少。因此恬吕，數(shù)據(jù)庫(kù)的數(shù)據(jù)表中頻繁查找的列，需要?jiǎng)?chuàng)建索引遥诉。
- 索引分為兩類：hash索引和btree(balance tree)索引拇泣。常用的是btree索引。
- 在創(chuàng)建主鍵（primary key）時(shí)矮锈，會(huì)自帶聚集索引+非空約束+唯一約束霉翔。
- 在創(chuàng)建唯一約束（unique）時(shí)，會(huì)自帶輔助索引+唯一約束苞笨。
- 重復(fù)率大于10%的字段不適合創(chuàng)建索引债朵。
-
普通索引
-
普通索引僅有一個(gè)功能：加速查詢
# 在創(chuàng)建表時(shí)+索引 create table in1( nid int unsigned not null auto_increment primary key, name varchar(32) not null, email varchar(64) not null, extra text, index index_name (name) ) # 為已經(jīng)創(chuàng)建的表添加索引 create index <索引名稱> on <表名稱>(字段) create index index_name on table_name(column_name) # 刪除索引 drop <索引名稱> on <表名稱>; drop index_name on table_name; # 查看索引 show index from <表名稱>; show index from table_name; # 注意：對(duì)于創(chuàng)建索引時(shí)如果是BLOB 和 TEXT 類型，必須指定length瀑凝。 create index index_extra on in1(extra(32));
-
-
唯一索引
-
唯一索引的作用是約束不能重復(fù)和加速查找
# 創(chuàng)建表 + 唯一索引 create table in1( nid int not null auto_increment primary key, name varchar(32) not null, email varchar(64) not null, extra text, unique ix_name (name) ) # 創(chuàng)建唯一索引 create unique index 索引名 on 表名(列名) # 刪除唯一索引 drop unique index 索引名 on 表名 -
示例
create table table1( id bigint not null auto_increment primary key, name char(64), sex enum('male','female'), id_card bigint, email varchar(64) ) engine=innodb default charset=utf8; create table table2( id bigint not null auto_increment primary key, username char(64) not null, password char(64) not null, user_id bigint not null, constraint t1_t2_id foreign key(user_id) references table1(id), # 唯一約束 unique uq (user_id) )engine=innodb default charset=utf8;
-
-
主鍵索引
-
主鍵有三個(gè)功能：加速查詢序芦、不能為空和不能重復(fù)
# 創(chuàng)建表 + 創(chuàng)建主鍵 create table in1( nid int not null auto_increment primary key, name varchar(32) not null, email varchar(64) not null, extra text, index ix_name (name) ) # OR create table in1( nid int not null auto_increment, name varchar(32) not null, email varchar(64) not null, extra text, primary key(nid), index ix_name (name) ) # 創(chuàng)建主鍵 alter table 表名 add primary key(列名); # 刪除主鍵 alter table 表名 drop primary key; alter table 表名 modify 列名 int, drop primary key;
-
-
聯(lián)合索引（組合索引）
組合索引是將n個(gè)列組合成一個(gè)索引
-
其應(yīng)用場(chǎng)景為：頻繁的同時(shí)使用n列來進(jìn)行查詢，如：
where n1 = 'python' and n2 = 666# 創(chuàng)建表 create table in3( nid int not null auto_increment primary key, name varchar(32) not null, age int not null, email varchar(64) not null, extra text ) # 創(chuàng)建聯(lián)合索引 create index ix_name_email on in3(name,age,email); 最左前綴匹配粤咪，如上創(chuàng)建組合索引之后谚中，查詢： name and age and email -- 使用索引 name and age -- 使用索引 name and email -- 使用索引 age and email -- 不使用索引 name -- 使用索引 age -- 使用索引 email -- 不使用索引 注意：對(duì)于同時(shí)搜索n個(gè)條件時(shí)，組合索引的性能好于多個(gè)單一索引合并寥枝。
-
聯(lián)合普通索引
create index 索引名稱 on 表名(列名,列名,...)
-
聯(lián)合唯一索引
create unique index 索引名稱 on 表名(列名,列名,...);
-
聯(lián)合主鍵索引
alter table 表名 add primary key(列名,列名,...);
-
專有名詞
覆蓋索引：在索引文件中直接獲取數(shù)據(jù)宪塔。
索引合并：把多個(gè)單列索引合并使用。索引合并沒有組合索引的效率高囊拜。
-
正確使用索引
select * from 表名稱 where 索引列名=索引值;
-
無法命中索引的情況
數(shù)據(jù)庫(kù)表中添加索引后確實(shí)會(huì)讓查詢速度起飛某筐，但前提必須是正確的使用索引來查詢，如果以錯(cuò)誤的方式使用冠跷，則即使建立索引也會(huì)不奏效南誊。
-
以下情況，即使建立索引蜜托，索引也不會(huì)生效：
# 模糊匹配抄囚，無法命中索引 select * from tb where name like '%cn'; # 使用函數(shù)，無法命中索引 select * from tb where reverse(name) = 'python'; # 使用范圍盗冷，無法命中索引 select * from tb where name > 'python'; select * from tb where name != 'python'; # 雖然使用范圍會(huì)無法命中索引怠苔，但如果是主鍵或索引是整數(shù)類型，則還是會(huì)走索引 select * from tb where nid > 123; select * from tb where num > 123; select * from tb where nid != 123; # 使用邏輯運(yùn)算符or仪糖，當(dāng)or條件中有未建立索引的列時(shí)柑司，無法命中索引 select * from tb where nid = 1 or email = 'seven@live.com'; # 但or的兩個(gè)條件都是索引迫肖，或者使用and中有一個(gè)條件使用了索引，索引會(huì)生效 select * from tb where nid = 1 or name = 'seven'; select * from tb where nid = 1 or email = 'seven@live.com' and name = 'alex'; # 類型不一致攒驰，如果列是字符串類型蟆湖，傳入條件是必須用引號(hào)引起來 select * from tb where name = 999; # 使用order by，select字段必須是索引字段玻粪，否則無法命中索引 select email from tb order by name desc; # 但如果對(duì)主鍵排序隅津，則還是走索引： select * from tb order by nid desc; # 組合索引最左前綴原則，必須帶著最左邊的列作為條件劲室，索引才會(huì)生效伦仍，如果出現(xiàn)了范圍，無法命中索引 如果組合索引為：(name,email) name and email -- 使用索引 name -- 使用索引 email -- 不使用索引
-
其他注意事項(xiàng)
- 避免使用select * - count(1)或count(列) 代替 count(*) - 創(chuàng)建表時(shí)盡量使 char 代替 varchar - 表的字段順序固定長(zhǎng)度的字段往前放很洋，變長(zhǎng)字段往后放 - 組合索引代替多個(gè)單列索引（經(jīng)常使用多個(gè)條件查詢時(shí)） - 盡量使用短索引（使用字段短的列作為索引） - 使用連接（JOIN）來代替子查詢(Sub-Queries) - 連表時(shí)注意條件類型需一致 - 索引散列值（重復(fù)少）不適合建索引充蓝，例：性別不適合做索引
-
執(zhí)行計(jì)劃
-
查看SQL語(yǔ)句有沒有按照預(yù)計(jì)執(zhí)行，查看索引的使用情況喉磁，以及type等級(jí)
explain select 語(yǔ)句
-
-
慢查詢優(yōu)化
- 首先從SQL的角度優(yōu)化
- 把每一句SQL單獨(dú)執(zhí)行谓苟，找到效率低的表，優(yōu)化這條SQL語(yǔ)句协怒；
- 了解業(yè)務(wù)場(chǎng)景涝焙，適當(dāng)創(chuàng)建索引，幫助查詢孕暇；
- 盡量用連表代替子查詢仑撞；
- 確認(rèn)命中索引的情況；
- 考慮修改表結(jié)構(gòu)
- 拆表芭商；
- 把固定在的字段網(wǎng)前調(diào)整派草；
- 使用執(zhí)行計(jì)劃搀缠，觀察SQL的type通過以上調(diào)整是否有所提高铛楣。
- 首先從SQL的角度優(yōu)化
-
慢日志
- 在MySQL的配置中開啟并設(shè)置當(dāng)一條SQL語(yǔ)句運(yùn)行超過一定時(shí)間后，會(huì)被記錄下來艺普，一邊我們定位到這條SQL語(yǔ)句簸州，對(duì)其進(jìn)行優(yōu)化。
