MySQL 索引

索引

索引用于快速查找具有特定列值的行。如果沒有索引，MySQL必須從第一行開始，然后讀取整個表以查找相關行。表越大，成本越高。如果表中有相關??列的索引，MySQL可以快速確定要在數(shù)據(jù)文件中間尋找的位置，而無需查看所有數(shù)據(jù)。這比按順序讀取每一行要快得多。

類似于字典中的目錄，查找字典內(nèi)容時可以根據(jù)目錄查找到數(shù)據(jù)的存放位置，然后直接獲取即可。

本質上是告訴數(shù)據(jù)庫的存儲引擎如何快速找到我們所要的數(shù)據(jù)。所以 MySQL 的索引是在 MySQL 的存儲引擎層實現(xiàn)的，而不是在其服務器層實現(xiàn)。

MySQL中常見索引有：

• 普通索引
• 唯一索引
• 主鍵索引
• 組合索引

普通索引

普通索引僅有一個功能：加速查詢

image.png

/*創(chuàng)建表的同時創(chuàng)建索引*/
create table t1(
    id int not null auto_increment primary key,
    name varchar(32),
    email varchar(64),
    extra text,
    index ix_name(name)
    /*添加索引到列名 name, 索引名為 ix_name*/
)

/*單獨創(chuàng)建索引*/
create index index_name on 表名稱(列名稱)
/*Example*/
create index index_name on student(name);

/*查看索引*/
show index from 表名稱;
/*Example*/
show index from student;

/*刪除索引*/
DROP INDEX index_name on 表名稱;
/*Example*/
DROP INDEX index_name on student;

唯一索引

唯一索引有兩個功能：加速查詢 和 唯一約束（可含null）

/*創(chuàng)建表和唯一索引*/
create table t2(
    id int not null auto_increment primary key,
    name varchar(32),
    email varchar(64),
    unique index ix_name (name)
);

/*創(chuàng)建唯一索引*/
create unique index 索引名 on 表名(列名);

/*刪除唯一索引*/
ALTER TABLE 表名 DROP INDEX 索引名;

主鍵索引

主鍵有兩個功能：加速查詢 和 唯一約束（不可含null）

當一個列被創(chuàng)建為主鍵是，它就會被賦予主機索引的屬性。

/*創(chuàng)建表和創(chuàng)建主鍵*/
create table t3(
    id int ,
    name varchar(32) ,
    email varchar(64) ,
    primary key(name)
);

聯(lián)合索引

聯(lián)合索引是將n個列聯(lián)合成一個索引

其應用場景為：頻繁的同時使用 n 個列來進行查詢，如：where name = 'shark' and age = 18。

create table studens(
    id int not null auto_increment primary key,
    name varchar(32) not null,
    age int not null,
)

create index idx_name_age on students(name,age);

如上創(chuàng)建聯(lián)合索引之后，查詢時可以這么用：

• name and age -- 使用索引 where name='shark' and age=18;
• name -- 使用索引 where name='shark';

B樹索引類型的聯(lián)合索引使用限制

• 匹配最左前綴的查詢

對于聯(lián)合索引的使用上需要注意, where 自己的第一個條件的列名必須是組合索引列的最左邊的那個。

下面是可以有效使用的方式

where name='shark';
where name='shark' and age>18;
 where name = 'shark'  and (age >18 or age = 10);

但是, 不能是下面的用法

where age = 18;
where name='shark'  or   age=19;

注意：對于同時搜索n個條件時，組合索引的性能好于多個單一索引合并。

• 匹配列前綴查詢

name  like  'shark%'

• 匹配范圍值查詢

name > 'a' and name < 'c'

不可以使用 not in 和 <>
當有 3 列組成的索引時， 使用這個聯(lián)合索引時，所有的字段不能跳過。
order_sn, order_name,order_date
where order_sn = '8998' and order_date = '20191010';
只能使用到 order_sn 這一個字段度索引，不能使用的 order_sn, order_date 的聯(lián)合索引

SQl 執(zhí)行計劃

explain select name from t1 where   name='shark'\G

** EXPLAIN輸出列**

索引過多的缺點

1. 增加寫的壓力

2. 增加 MySQL 查詢優(yōu)化器的選擇時間。