mysql学习笔记之索引(持续更新中...)

参考资料

书籍<高性能mysql(第3版)>第5章:创建高性能的索引,博客园桦仔的mysql学习心得,链接http://www.cnblogs.com/lyhabc/p/3776739.html

创建索引时分为两种情况

1)在建表时创建

2)表建好后创建

语法

CREATE TABLE table_name[col_name data type]

[unique | fulltext | spatial] [index | key] [index_name] [col_name[length]][asc | desc]

• unique|fulltext|spatial为可选参数，分别表示唯一索引、全文索引和空间索引；
• index和key为同义词，两者作用相同，用来指定创建索引;
• col_name为需要创建索引的字段列，该列必须从数据表中该定义的多个列中选择；

• index_name指定索引的名称，为可选参数，如果不指定，MYSQL默认col_name为索引值；

• length为可选参数，表示索引的长度，只有字符串类型的字段才能指定索引长度;

• asc或desc指定升序或降序的索引值存储

CREATE TABLE book (
  bookid INT NOT NULL,
  bookname VARCHAR (255) NOT NULL,
  AUTHORS VARCHAR (255) NOT NULL,
  info VARCHAR (255) NULL,
  COMMENT VARCHAR (255) NULL,
  year_publication YEAR NOT NULL,
  INDEX (year_publication)
) ;

在已经存在的表中创建索引,可以使用ALTER TABLE或者CREATE INDEX语句

1.ALTER TABLE table_name ADD [UNIQUE|FULLTEXT|SPATIAL][INDEX|KEY]

[index_name](col_name[length],...)[ASC|DESC]

ALTER TABLE t1 ADD INDEX NameIdx(NAME);

2.CREATE [UNIQUE|FULLTEXT|SPATIAL]  INDEX index_name

ON table_name(col_name[length],...)  [ASC|DESC]

CREATE INDEX BkNameIdx ON book(bookname)

删除索引

MYSQL中使用ALTER TABLE或者DROP INDEX语句来删除索引，两者实现相同功能

1.ALTER TABLE table_name DROP INDEX index_name

ALTER TABLE book DROP INDEX UniqidIdx

2.DROP INDEX index_name ON table_name

DROP INDEX BkAuAndInfoIdx ON book

查看索引

使用SHOW INDEX

SHOW INDEX FROM table_name;

注意:InnoDB不支持全文索引

 

以上是索引的使用语法,适合快速入门上手,接下来正式开始索引的学习

首先我们要知道,索引它是什么?

索引是一种数据结构,是存储引擎用于快速找到记录的一种数据结构,mysql中也称为键'key'

索引对于良好的性能非常关键,索引能够轻易将查询性能提高几个数量级,尤其是当表中的数据量越来越大时,索引对性能的影响越发重要(不论是好的影响还是坏的影响).

1.基础

一般我们阅读一本书想要快速找到某些内容,我们会先在目录中找到对应的页码

在MySQL中,存储引擎用类似的方法使用索引,其先在索引中找到对应值,然后根据匹配的索引记录找到对应的数据行

例:mysql>SELECT first_name FROM sakila.actor WHERE actor_id = 5;

如果在actor_id列上建有索引,则MySQL将使用该索引找到actor_id为5 的行,也就是说,MySQL先在索引上按值

进行查找,然后返回所有包含该值的数据行

 

索引可以包含一个或多个列的值.这里也就涉及到面试时经常会问到的最左原则,因为MySQL只能高效的使用

索引的最左前缀列,因此列的顺序也十分重要.

创建一个包含两个列的索引,和创建两个只包含一列的索引是大不相同的

 

1.1 索引的类型

   索引有多种类型,可以为不同的场景提供更好的性能

   在MySQL中,索引是在存储引擎层而不是服务器层实现的.

  索引,并没有统一的索引标准:不同存储引擎的索引工作方式并不一样,也不是所有的存储引擎都支持所有类型的索引

即使多个存储引擎支持同一种类型的索引,其底层的实现也可能不同.

1.1.1 B-Tree索引

  一般没有特别指明类型,那平常说的索引是B-Tree索引,它使用B-Tree数据结构来存储数据.

大多数MySQL引擎都支持这种索引.

存储引擎以不同的方式使用B-Tree索引,性能也各有不同,各有优劣.

例如,MyISAM使用前缀压缩技术使得索引更小,但InnoDB则按照原数据格式进行存储.

再如MyISAM索引通过数据的物理位置引用被索引的行,而InnoDB则根据主键引用被索引 的行

B-Tree通常以意味着所有的值都是按顺序存储的,并且每一个叶子页到根的距离相同.

B-Tree索引能够加快访问数据的速度,因为存储引擎不再需要进行全表扫描来获取需要的数据,

取而代之的是从索引的根节点开始进行搜索.根节点的槽中存放了指向子节点的指针,存储引擎根据这些

指针向下层查找.通过比较节点页的值和要查找的值可以找到合适的指针进入下层子节点,这些指针

实际上定义了子节点页中值的上限和下限.最终存储引擎要么是找到对应的值,要么该记录不存在.

叶子节点比较特别,它们的指针指向的是被索引的数据,而不是其他的节点页.树的深度和表的大小直接相关

B-Tree对索引列是顺序组织存储的,所以很适合查找范围数据

例如,在一个基于文本域的索引树上,按字母顺序传递连续的值进行查找是非常合适的,

所以像"找出所有以I到K开头的名字" 这样的查找效率会非常高.

假设有如下数据表:

CREATE TABLE People (

last_name varchar(50) not null,

first_name varchar(50) not null,

dob date not null,

gender enum('m','f') not null,

key(last_name,first_name,dob)

);

对于表中的每一行数据,索引中包含了last_name,first_name和dob列的值,

(受限于编辑方式不能放图片,只能粗略的表达一下)

数据:Allen Cuba 1960-01-01          Astalre Angellna 1980-03-04        Barrymare Julla 2000-05-16	索引指针	数据	索引指针
数据	数据	数据	索引指针
数据	数据	数据	索引指针

请注意,索引对多个值进行排序的依据是CREATE TABLE语句中定义索引时列的顺序.

如表,如果两个人的姓和名都一样,则根据他们的出生日期来排列顺序.

可以使用B-Tree索引的查询类型.B-Tree索引适用于全键值,键值范围或键前缀查找.

其中键前缀查找只适用于根据最左前缀的查找.

前面所述的索引对如下类型的查询有效

全值匹配

全值匹配指的是和索引中的所有列进行匹配,例如前面提到的索引可用于查找姓名为

Cuba Allen,出生于1960-01-01的人

匹配最左前缀

前面提到的索引可用于查找所有姓为Allen的人,即只使用索引的第一列

匹配列前缀

也可以只匹配某一列的值的开头部分,例如前面提到的索引可用于查找所有以J开头的姓的人,这里也只使用了

索引的第一列

匹配范围值

例如前面的提到的索引可用于查找姓在Allen和Barrymore之间的人.这里也只是用了索引的第一列.

精确匹配某一列并范围匹配另外一列

前面提到的索引也可用于查找所有姓为Allen,并且名字是字母K开头(比如Kim,Karl等)的人.

即第一列last_name全匹配,第二列first_name范围匹配.

只访问索引的查询

B-Tree通常可以支持"只访问索引的查询",即查询只需要访问索引,而无需访问数据行,暂时不提这种

"覆盖索引"的优化

索引树中的节点是有序的,所以除了按值查找之外,索引还可以用于查询中的ORDER BY操作(按顺序查找),

一般来说,如果B-Tree可以按照某种方式查找到值,那么也可以按照这种方式用于排序,所以,如果

ORDER BY 子句满足前面列出的几种查询类型,则这个索引也可以满足对应的排序需求.

B-Tree索引的限制:

1.如果不是按照索引的最左列开始查找,则无法使用索引.例如上面例子中的索引无法用于查找名字为Bill的人

,也无法查找某个特定生日的人,因为这两列都不是最左数据列.类似的,也无法查找姓氏以某个字母结尾的人

2.不能跳过索引中的列.也就是说,前面所述的索引无法用于查找姓为Smith并且在某个特定日期出生的人,如果不指定名

(first_name),则MySQL只能使用索引的第一列

3.如果查询中有某个列的范围查询,则其右边所有列都无法使用索引优化查找,例如有查询WHERE last_name='Smith''

AND first_name LIKE 'J%' AND dob = '1976-12-23',这个查询只能使用索引的前两列,因为这里LIKE是一个范围条件

(但是服务器可以把其余列用于其他目的).如果范围查询列值的数量有限,name可以通过使用多个等于条件来代替范围

条件.

到这里读者应该可以明白,前面提到的索引列的顺序是多么的重要:这些限制都和索引列的顺序有关

在优化性能的时候,可能需要使用相同的列单顺序不同的索引来满足不同类型的查询需求.

 

1.1.2 哈希索引

哈希索引(hash index) 基于哈希表实现,只有精确匹配索引所有列的查询才有效

对于每一行数据,存储引擎都会对所有的索引列计算一个哈希码(hash code),哈希码是一个较小的值,并且不同键值的行

计算出来的哈希码也不一样.哈希索引将所有的哈希码存储在索引中,同时在哈希表中保存指向每个数据行的指针

在MySQL中只有Memory引擎显式支持哈希索引.这也是Memory引擎表的默认索引类型,同时也支持B-Tree索引.

值得一提的是,Memory引擎是支持非唯一哈希索引的,这在数据库世界里面是比较与众不同的.

如果多个列的哈希值相同,索引会以链表的方式存放多个记录指针到同一个哈希条目中

下面来看一个例子.假设有如下表:

CREATE TABLE testhash (

faname VARCHAR(50) NOT NULL,

lname VARCHAR(50) NOT NULL,

KEY USING HASH(fname)

)ENGINE=MEMORY;

表中包含如下数据:

mysql>SELECT * FROM testhash;

 

fname	lname
Arjen Baron Peter Vadim	Lentz Schwartz Zaitsev Tkachenko

假设索引使用假想的哈希函数f(),它返回下面的值(示例数据,非真实数据):

f('Arjen')=2323

f('Baron')=7437

f('Peter')=8784

f('Vadim')=2458

则哈希索引的数据结构如下:

槽 (Slot)        值(Value)

2323             指向第1行的指针

2458             指向第4行的指针

7437             指向第2行的指针

8784            指向第3行的指针

------------------------------------

注意:每个槽的编号是顺序的,但是数据行不是.现在,来看如下查询:

mysql>SELECT lname FROM testhash WHERE fname='Peter';

MySQL先计算'Peter'的哈希值,并使用该值寻找对应的记录指针.

因为f('Peter')=8784,所以MySQL在索引中查找8784,可以找到指向第3行的指针,最后一步是比较第三行的值

是否为'Peter',以确保就是要查找的行.

因为索引自身只需存储对应的哈希值,所以索引的结构十分紧凑,这也让哈希索引查找的速度非常快.然而,哈希索引也有它的限制:

• 哈希索引只包含哈希值和行指针,而不存储字段值,所以不能使用索引中的值来避免读取行.不过,访问内存中的行的速度很快,所以大部分情况下这一点对性能的影响并不明显