準(zhǔn)備

為了進(jìn)行Mysql失效的實(shí)踐鞍帝，我們首先創(chuàng)建一個(gè)表诫睬，執(zhí)行以下 sql 語句：

mysql> create table staff(
    -> id int(11) not null auto_increment,
    -> name varchar(20) not null,
    -> pos varchar(20),
    -> add_time datetime,
    -> grade int(10),
    -> weight int(10),
    -> age int(10),
    -> UNIQUE INDEX name_idx (name),
    -> primary key (id))engine = innodb default charset = utf8;

執(zhí)行插入語句，

insert into staff(name,pos,add_time,grade,weight,age) values("liyang", "dev","1998-5-20", 100, 23);
insert into staff(name,pos,add_time,grade,weight,age) values("zaolie", "manager","1994-5-20", 87,80, 28);
insert into staff(name,pos,add_time,grade,weight,age) values("yinhui", "dev_1","1997-5-20", 78,50, 27);
insert into staff(name,pos,add_time,grade,weight,age) values("shaoyu", "dev_2","1995-5-20", 65,78, 27);

執(zhí)行 select * from staff:

image

Explain關(guān)鍵字

mysql為我們提供了很有用的輔助武器 explain帕涌，它向我們展示了 mysql 接收到一條sql語句的執(zhí)行計(jì)劃摄凡。根據(jù)explain 返回的結(jié)果我們便可以知道我們的 sql 寫的怎么樣续徽，是否會(huì)造成查詢瓶頸，同時(shí)根據(jù)結(jié)果不斷的修改調(diào)整查詢語句架谎，從而完成 sql優(yōu)化的過程炸宵。

雖然 explain返回的結(jié)果項(xiàng)很多，這里我們只關(guān)注三種谷扣，分別是type土全，key，rows会涎。

其中 key 表示用到了什么索引裹匙，為NULL表示未用到索引；rows 表示查找數(shù)據(jù)掃描的行數(shù)末秃， type 表示連接類型概页；

type 字段出現(xiàn)不同的值表示的含義：

• all, 全表掃描；
• index,這種連接類型只是另外一種形式的全表掃描练慕，只不過它的掃描順序是按照索引的順序惰匙。這種掃描根據(jù)索引然后回表取數(shù)據(jù)，和all相比铃将，他們都是取得了全表的數(shù)據(jù)项鬼，而且index要先讀索引而且要回表隨機(jī)取數(shù)據(jù)，因此index不可能會(huì)比all快（取同一個(gè)表數(shù)據(jù)）劲阎，但為什么官方的手冊(cè)將它的效率說的比all好绘盟，唯一可能的原因在于，按照索引掃描全表的數(shù)據(jù)是有序的悯仙。
• range, 有范圍的索引掃描龄毡，使用了范圍索引，比如between, and 以及 ‘<', '>'锡垄， in 和 or 也是索引范圍優(yōu)掃描沦零。
• ref, 查找條件列使用了索引而且不為主鍵和unique。其實(shí)货岭，意思就是雖然使用了索引蠢终，但該索引列的值并不唯一琳猫，有重復(fù)搀擂。
• ref_eq, 使用了主鍵或者唯一性索引;
• const, 如果將一個(gè)主鍵放置到where后面作為條件查詢，mysql優(yōu)化器就能把這次查詢優(yōu)化轉(zhuǎn)化為一個(gè)常量逢艘。

索引

MySQL索引的建立對(duì)于MySQL的高效運(yùn)行是很重要的丈牢，索引可以大大提高M(jìn)ySQL的檢索速度祭钉。

拿漢語字典的目錄頁（索引）打比方，我們可以按拼音己沛、筆畫慌核、偏旁部首等排序的目錄（索引）快速查找到需要的字距境。

索引分單列索引和組合索引。

創(chuàng)建索引時(shí)垮卓，你需要確保該索引是應(yīng)用在 SQL 查詢語句的條件(一般作為 WHERE 子句的條件)垫桂。

實(shí)際上，索引也是一張表粟按，該表保存了主鍵與索引字段诬滩，并指向?qū)嶓w表的記錄。

上面都在說使用索引的好處灭将，但過多的使用索引將會(huì)造成濫用疼鸟。因此索引也會(huì)有它的缺點(diǎn)：雖然索引大大提高了查詢速度，同時(shí)卻會(huì)降低更新表的速度庙曙，如對(duì)表進(jìn)行INSERT空镜、UPDATE和DELETE。因?yàn)楦卤頃r(shí)捌朴，MySQL不僅要保存數(shù)據(jù)吴攒，還要保存一下索引文件。

建立索引會(huì)占用磁盤空間的索引文件砂蔽。

普通索引

查看索引

show index from staff: 查看表的索引

image

其中洼怔，

• Table: 表名，

• Non_unique : 表示如果索引不能包含重復(fù)項(xiàng)察皇，則返回 0 ；如果可以泽台，則返回 1.

• Key_name: 索引名稱什荣，或者鍵名；

• Seq_in_index: 索引中的序列號(hào)怀酷，從1開始稻爬。

• Column_name ：列名；

• Collation： 列在索引中的排序方式蜕依。升序值 A 或者 NULL 未排序桅锄；

• Cardinality: 索引中唯一值數(shù)量的估計(jì)。Cardinality是根據(jù)存儲(chǔ)為整數(shù)的統(tǒng)計(jì)信息進(jìn)行計(jì)數(shù)的.

• Sub_part: 索引前綴样眠，如果僅對(duì)列進(jìn)行部分索引友瘤，則為已索引字符的數(shù)目；如果對(duì)整個(gè)列索引則為null.

  Note

  前綴限制以字節(jié)為單位檐束。但是辫秧，對(duì)于CREATE TABLE，ALTER TABLE和CREATE INDEX語句中的索引規(guī)范被丧，前綴 length 被解釋為非二進(jìn)制字符串類型(CHAR盟戏，VARCHAR绪妹，TEXT)的字符數(shù)和二進(jìn)制字符串類型(BINARY，VARBINARY柿究，BLOB)的字節(jié)數(shù)邮旷。

• Packed: 指示密鑰的包裝方式。

• NULL: 如果該列可能包含NULL個(gè)值蝇摸，則包含YES婶肩；否則，則包含''探入。

• Index_type: 使用的索引方法(BTREE狡孔，FULLTEXT，HASH蜂嗽，RTREE)

• Comment: 關(guān)于索引的信息未在其自己的列中描述苗膝，例如disabled(如果禁用了索引)。

• Index_comment: 創(chuàng)建索引時(shí)植旧，為索引提供的具有COMMENT屬性的任何 Comments辱揭。

參考

創(chuàng)建索引

1. 創(chuàng)建索引

   CREATE INDEX indexName ON table_name (column_name)
   
   

   如果是CHAR，VARCHAR類型病附，length可以小于字段實(shí)際長(zhǎng)度问窃；如果是BLOB和TEXT類型，必須指定 length完沪。

2. 修改表結(jié)構(gòu)添加

   Alter table tableName ADD INDEX indexName(column)
   
   

3. 創(chuàng)建表時(shí)添加

   CREATE TABLE mytable(  
   ID INT NOT NULL,   
   username VARCHAR(16) NOT NULL,  
   INDEX [indexName] (username(length))  
   ); 
   
   

刪除索引

DROP INDEX [indexName] ON mytable; 

唯一索引

與前面的普通索引類似域庇，不同的就是：索引列的值必須唯一，但允許有空值覆积。如果是組合索引听皿，則列值的組合必須唯一。

創(chuàng)建索引

• 創(chuàng)建索引

  CREATE UNIQUE INDEX indexName ON mytable(username(length)) 
  
  

• 修改表結(jié)構(gòu)

  ALTER table mytable ADD UNIQUE [indexName] (username(length))
  
  

• 創(chuàng)建表時(shí)指定

  CREATE TABLE mytable(  
  ID INT NOT NULL,   
  username VARCHAR(16) NOT NULL,  
  UNIQUE [indexName] (username(length))  
  ); 
  
  

使用ALTER 命令添加和刪除索引

添加主鍵

ALTER TABLE tbl_name ADD PRIMARY KEY (column_list)

該語句添加一個(gè)主鍵宽档，這意味著索引值必須是唯一的尉姨，且不能為NULL。

添加唯一索引

ALTER TABLE tbl_name ADD UNIQUE index_name (column_list)

這條語句創(chuàng)建索引的值必須是唯一的（除了NULL外吗冤，NULL可能會(huì)出現(xiàn)多次）又厉。

添加普通索引

ALTER TABLE tbl_name ADD INDEX index_name (column_list)

添加普通索引，索引值可出現(xiàn)多次椎瘟。

添加全文索引

ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list)

該語句指定了索引為 FULLTEXT 覆致，用于全文索引。

刪除表中的索引

ALTER 命令中使用 DROP 子句來刪除索引:

ALTER table tableName DROP INDEX c;

使用ALTER 命令添加和刪除主鍵

主鍵作用于列上（可以一個(gè)列或多個(gè)列聯(lián)合主鍵）肺蔚，添加主鍵索引時(shí)篷朵，你需要確保該主鍵默認(rèn)不為空（NOT NULL）。

添加主鍵：

 ALTER TABLE testalter_tbl MODIFY c INT NOT NULL;
 ALTER TABLE testalter_tbl ADD PRIMARY KEY (c)

使用 ALTER 命令刪除主鍵:

ALTER TABLE testalter_tbl DROP PRIMARY KEY;

索引失效

索引失效主要原因有以下：

1. 違反最佳最前綴法則
2. 在索引列上做操作
3. 索引范圍條件右邊的列
4. 使用不等于
5. like以通配符開頭
6. 字符串不加單引號(hào)
7. or連接
8. order by: 違反最左前綴法則，含非索引字段排序声旺，導(dǎo)致文件排序笔链；
9. group by: 違反最左前綴法則，含非索引字段腮猖，會(huì)導(dǎo)致產(chǎn)生臨時(shí)表鉴扫；

違反最左前綴法則

在age, weight, grade列創(chuàng)建復(fù)合索引：

create index age_weight_grade_idx on staff (age,weight,grade);

image

如果索引有多列，要遵守最左前綴法則：即查詢從索引的最左前列開始并且不跳過索引中的列澈缺。

正確姿勢(shì):

explain select * from staff where age = 23 and weight = 70 and grade = 100;

image

錯(cuò)誤方式：

image

這也就是說坪创，我們創(chuàng)建索引一定是基于某種原因或者基于特定的sql語句去做優(yōu)化，而不是一上來就創(chuàng)建索引姐赡。

在索引列上操作

執(zhí)行計(jì)算莱预、函數(shù)、自動(dòng)轉(zhuǎn)換類型等项滑，或?qū)е滤饕А?/p>

比如我們執(zhí)行以下語句依沮，

explain select * from staff where id + 1 = 3;

image

范圍索引右邊的列失效

復(fù)合索引中，如果有一列使用了范圍控制符枪狂，則右邊的索引會(huì)失效危喉。

explain select * from staff where age = 23 and weight > 70 and grade = 100;

image

使用不等于

使用 ‘!=' 或者’<>' 導(dǎo)致索引失效，全表掃描州疾；

explain select * from staff where age != 1;

image

like以通配符開頭（"%abc")

explain select * from staff where name like '%ang';

image

字符串不加單引號(hào)

explain select * from staff where name = 200;

image

使用or連接

// 少用or 連接
explain select * from staff where age = 23 or grade = 70;

image

order by

正常使用辜限， 索引有兩個(gè)作用：排序和查找

explain select * from staff where age = 23 order by age,weight;

image

違反最左前綴法則或者使用非索引字段會(huì)導(dǎo)致索引失效。

explain select * from staff where age = 23 order by age,grade;

image

group by

正常使用（分組之前必須排序）

explain select * from staff where age = 23 group by age;

image

違反最左前綴法則或者使用非索引字段會(huì)導(dǎo)致索引失效严蓖。

 explain select * from staff where age = 23 group by grade;
 explain select * from staff where age = 23 group by add_time;

image

image

分類: Mysql

標(biāo)簽: mysql

在這里插入圖片描述