MySQL复习笔记(06)：索引

た入场券 2022-12-10 14:40 162阅读 0赞

**本文主要介绍MySQL 中关于索引的一些问题，例如：索引的作用；怎么创建索引；设计索引的原则；怎么优化索引等等**。

**一：索引概述**

索引一般是通过排序，然后查找时可以二分查找，这一特点来达到加速查找的目的的。

所有的MySQL列类型都能创建索引，良好设计的索引能够很好地提高查询的性能，但如果索引过多，由于每次更新操作都会对索引进行更新，反而会影响到数据库的整体性能。因而，遵循一定的原则，设计合适的索引是非常重要的。

(1)：创建索引的语法

CREATE \[UNIQUE|FULLTEXT|SPQTIAL\] INDEX index\_name \[USING index\_type\] ON table\_name(col\_name)

例子：建了一张user表，有属性 name, age, address, 下面图可以看出没建立索引和建立索引时查询的区别。

![a878bb905d6cddd6f9c4de52f55875cc.png][]

![25f6c4bb6920aa757dbbfdb7ef2ae301.png][]

![e1207f4a3e6b4747b38b732f2b0d5192.png][]

**二：设计索引的几个原则**

(1): where子句中的列比select中的列更适合做索引。

(2): 选择那些基数大的列，这要的索引效果更好，这要索引才能很好地区分不同值。

(3): 使用短索引，例如一个char(200)的列，如果前20个字符就能很好的区分不同的值时，就没必要对整个列进行索引，这样可以大大的减少索引的存储空间。

(4): 不要过度索引，只建立所需的索引。过多的索引会浪费磁盘空间，降低写的性能，也会给查询优化带来更多的工作，让MySQL选择不到最好的索引。

(5): InnoDB尽量自己指定主键：InnoDB 引擎存储的表会按照一定的顺序保存，例如主键，唯一索引，如果都没有则会自动生成一个内部列，按照这些进行访问是最快的，所以InnoDB尽量自己指定主键。当有多个列可以作为主键时，选择最常作为访问条件的列作为主键。

例子：InnoDB没创建索引，但建立了主键，会用主键进行查询。

![d6456c5316f50001b34bfff51f4c0f77.png][]

![aadaa815e601ea386ea4aca052c2852f.png][]

**三：BTREE索引和HASH索引**

MyISAM 和 InnoDB 默认创建的是BTREE索引，MEMORY引擎默认创建的是Hash索引，BTREE 用于全值匹配，匹配列前缀，范围匹配时非常有效。HASH索引存储的是hash值，并且是所有索引列的Hash值，只能用于精确匹配。

**BTREE 的几个限制：**

(1). 必须按照索引的最左列开始查找

(2). 不能跳过索引中的列

(3). 某个列使用了范围查询，其右边的列都无法使用索引。

索引列的位置顺序，非常重要，写ＳＱＬ时需要尤其注意。

**HASH索引的几个限制：**

(1). hash索引只存储哈希值和行指针，不存储字段值，结构紧凑。

(2). 数据不是按照索引值顺序存储的，所以无法用于排序。

(3). 不支持部分索引列的匹配查找，因为hash值是用所有的索引列计算的。

(4). 只支持精确查找

**四：索引的优化**

在遵循索引的设计原则后，设计索引和编写SQL时还需要注意索引使用时的几个特点：

(1). 前缀特性 : 当创建了多列索引时，只要用到了前面的列，索引就会起作用。例如创建了两列索引(a,b) ，当where条件语句中仅出现了a，索引也会被使用，但仅仅出现了b,索引将不会被使用。

(2). 使用like查询时，%不能出现在第一个字符，应该是 “ 常量 + %”，这样索引才可能会起作用。

(3). 对大文本进行搜索时，使用全文索引而不是使用 like '%...%, **更好的方法是在BTREE的基础上建立伪Hash索引，仅仅需要对这列计算hash值再存储，可以节省空间。缺陷是需要维护hash值，可以用触发器维护。**

(4). 用or分割开的条件，如果or前面的列有索引，后面的列没有索引，那么涉及到索引都不会被用到。

(5). 如果列类型是字符串，记得where条件中把字符串常量值用引号引起来。

查看索引的使用情况，用 show status like 'Handle\_read%' 查看Handle\_read\_key(值大说明索引得到了很好的使用，反之亦然) 和 Handle\_read\_rnd\_next(值很大说明进行了大量的全盘扫描，索引没得到很好地使用) 的值。

原文链接：http://www.cnblogs.com/zhanht/p/5450559.html

[a878bb905d6cddd6f9c4de52f55875cc.png]: /images/20221123/d61c164b8a194195be8727c5a2b74499.png
[25f6c4bb6920aa757dbbfdb7ef2ae301.png]: /images/20221123/0d3e35d33a224c409963b36ef96e2397.png
[e1207f4a3e6b4747b38b732f2b0d5192.png]: /images/20221123/446f210851e04c8ca37f3e52256dbd77.png
[d6456c5316f50001b34bfff51f4c0f77.png]: /images/20221123/d7e483c923a147729f024973fdac0e0f.png
[aadaa815e601ea386ea4aca052c2852f.png]: /images/20221123/c7fdc4e09e7a4cf38be602922dec8b2c.png