MySQL索引

目录

一、概述

1.全表扫描：

2.索引访问：

二、索引的优缺点

三、索引分类

1.存储方式区分：

1) B+Tree索引：

MySQL为什么选择B+Tree?

MyISAM存储引擎：

 InnoDB存储引擎：

2）哈希索引：

2.逻辑区分

1）普通索引：

2）唯一索引：

3) 主键索引：

4) 全文索引：

 3.实际使用区分：

1）单列索引：

2）组合索引：

四、索引失效

---

一、概述

        在 MySQL 中，通常有以下两种方式访问数据库表的行数据：

1.全表扫描：

        顺序访问是在表中实行全表扫描，从头到尾逐行遍历，直到在无序的行数据中找到符合条件的目标数据。 顺序访问实现比较简单，但是当表中有大量数据的时候，效率非常低下。例如，在几千万条数据中查找少量的数据 时，使用顺序访问方式将会遍历所有的数据，花费大量的时间，显然会影响数据库的处理性能。

2.索引访问：

        索引是一种特殊的数据库结构，由数据表中的一列或多列组合而成，可以用来快速查询数据表中 有某一特定值的记录。通过索引，查询数据时不用读完记录的所有信息，而只是查询索引列。否则， 数据库系统将读取每条记录的所有信息进行匹配（全表扫描）。

        索引访问是通过遍历索引来直接访问表中记录行的方式。 使用这种方式的前提，是对表建立一个索引，在列上创建了索引之后，查找数据时可以直接根 据该列上的索引找到对应记录行的位置，从而快捷地查找到数据。索引存储了指定列数据值的指针， 根据指定的排序顺序对这些指针排序。 可以理解为索引就是根据表中的一列或若干列按照一定顺序建立的列值与记录行之间的对 应关系表，实质上是一张描述索引列的列值与原表中记录行之间一 一对应关系的有序表。 因此，索引是 MySQL 中十分重要的数据库对象，是数据库性能调优技术的基础，常用于实现 数据的快速检索。使用索引可以很大程度上提高数据库的查询速度，还有效的提高了数据库系统的性能。

二、索引的优缺点

优点：

• 可以大大加快数据的查询速度，这是使用索引最主要的原因。
• 在使用分组和排序子句进行数据查询时也可以显著减少查询中分组和排序的时间。
• 通过创建唯一索引可以保证数据库表中每一行数据的唯一性。

缺点：

• 创建和维护索引组要耗费时间，并且随着数据量的增加所耗费的时间也会增加。
• 索引需要占磁盘空间，除了数据表占数据空间以外，每一个索引还要占一定的物理空间。如果有大量的索引，索引文件可能比数据文件更快达到最大文件尺寸。
• 当对表中的数据进行增加、删除和修改的时候，索引也要动态维护，这样就降低了数据的维护速度。

        索引可以提高查询速度，但是会影响插入记录的速度。因为，向有索引的表中插入记录时，数据库系统会按照索引进行排序，这样就降低了插入记录的速度，插入大量记录时的速度影响会更加明显。这种情况下，最好的办法是先删除表中的索引，然后插入数据，插入完成后，再创建索引。

三、索引分类

        索引的类型和存储引擎有关，每种存储引擎所支持的索引类型不一定完全相同。MySQL 索引可以从存储方式、逻辑角度和实际使用的角度来进行分类：

1.存储方式区分：

        根据存储方式的不同，MySQL 中常用的索引在物理上分为 B+Tree索引 和 HASH索引两类，两种不同类型的索引各有其不同的适用范围。

1) B+Tree索引：

        B+Tree是B Tree的一种特殊变种。

        首先，来理解BTree，它是一个多路平衡查找树，所有的叶子节点在同一高度。

假设要从图中查找 id = X 的数据，BTREE 搜索过程如下： 1 取出根磁盘块，加载 40 和 60 两个关键字。 2 如果 X = 40 ，则命中；如果 X< 40 走 P1 ；如果 40 < X < 60 走 P2 ；如果 X = 60 ，则命中；如果 X > 60 走 P3 。 3 根据以上规则命中后，接下来加载对应的数据， 数据区中存储的是具体的数据或者是指向数据 的指针。

        其次，再来理解B+Tree，B+Tree在原有BTree的基础上补充了如下特性：

1. 每一个叶子节点，都持有一个指向下一个叶子节点的指针，形成了有序链表；
2. B+Tree 根节点和支节点没有数据区，数据 data 全部存储在叶子节点中；
3. 叶子节点中，包含了所有节点元素；

        假设为字段 ID 添加索引，搜索 X = 1 的数据，B+TREE 搜索过程如下：

1. 取出根磁盘块，加载 1 ， 28 ， 66 三个关键字。
2. X <= 1 走 P1 ，取出磁盘块，加载 1 ， 10 ， 20 三个关键字。
3. X <= 1 走 P1 ，取出磁盘块，加载 1 ， 8 ， 9 三个关键字。
4. 已经到达叶子节点，命中 1 ，接下来加载对应的数据，图中数据区中存储的是具体的数据。

MySQL为什么选择B+Tree?

1. B+Tree全表扫描能力更强。如果我们要根据索引去进行数据表的扫描，如果基于 BTREE 进行 扫描，需要把整棵树遍历一遍，而 B+TREE 只需要遍历所有叶子节点即可（叶子节点之间形成有序列表）。
2. B+Tree 排序能力更强。上面的图中可以看出，B+Tree天然具有排序功能。
3. B+TREE 磁盘读写能力更强。他的根节点和枝节点不保存数据区，所以根节点和枝节点同样大 小的情况下，保存的关键字要比 BTREE 要多。所以， B+TREE 读写一次磁盘加载的关键字比 BTR EE 更多。
4. B+Tree 查询性能稳定。 B+Tree 数据只保存在叶子节点，每次查询数据，查询 IO 次数一 定是稳定的。

MyISAM存储引擎：

        MyISAM是MySQL中常见的存储引擎，曾经是MySQL的默认存储引擎。MyISAM是基于ISAM引擎发展起来的，增加了许多有用的扩展。 基于MyISAM存储引擎的表支持3种不同的存储格式。包括静态型、动态型和压缩型。其中，静态型是MyISAM的默认存储格式，它的字段是固定长度的；动态型包含变长字段，记录的长度不是固定的；压缩型需要用到myisampack工具，占用的磁盘空间较小。

        MyISAM的表存储成3个文件 .frm .myd .myi：

• 文件的名字与表名相同。拓展名为frm、MYD、MYI。
• frm文件存储表的结构；
• myd文件存储数据，是MYData的缩写；
• myi文件存储索引，是MYIndex的缩写。

        MyISAM的优势缺点：

        MyISAM的优势在于占用空间小，处理速度快。 缺点是不支持事务的完整性和并发性。 不是事务安全的，而且不支持外键，如果执行大量的select，insert MyISAM比较适合 myisam只支持表级锁。

 InnoDB存储引擎：

        InnoDB给MySQL的表提供了事务处理、回滚、崩溃修复能力和多版本并发控制的事务安全。在MySQL从3.23.34a开始包含InnnoDB。它是MySQL上第一个提供外键约束的表引擎。而且InnoDB对事务处理的能力，也是其他存储引擎不能比拟的。靠后版本的MySQL的默认存储引擎就是InnoDB。

        存储 .frm .ibd：

• InnoDB中，创建的表的表结构存储在.frm文件中；
• 数据和索引存储在innodb_data_home_dir和innodb_data_file_path定义的表空间中。

        InnoDB的优势缺点：

1. 提供了良好的事务处理、崩溃修复能力和并发控制。
2. 支持事务安全的引擎，支持外键、行锁、事务是他的最大特点。
3. 如果有大量的update和insert，建议使用InnoDB，特别是针对多个并发和QPS较高的情况。
4. Innodb支持事务和行级锁，是innodb的最大特色。
5. 缺点是读写效率较差，占用的数据空间相对较大。

2）哈希索引：

        哈希索引也称为散列索引或 HASH 索引。MySQL 目前仅有 MEMORY 存储引擎和 HEAP 存储 引擎支持这类索引。哈希（Hash）一般翻译为“散列”，也有直接音译成“哈希”的，就是把任意 长度的 key 通过散列算法变换成固定长度的输出，该输出就是散列值。

        哈希索引的特点：

• 无法用于排序与分组；
• 只支持精确查找，无法用于部分查找和范围查找；
• InnoDB 存储引擎有一个特殊的功能叫“自适应哈希索引”，当某个索引值被使用的非常频繁 时，会在 B+Tree 索引之上再创建一个哈希索引，这样就让 B+Tree 索引具有哈希索引的一些优 点，比如快速的哈希查找。

2.逻辑区分

1）普通索引：

        普通索引是 MySQL 中最基本的索引类型，它没有任何限制，唯一任务就是加快系统对数据的 访问速度。普通索引允许在定义索引的列中插入重复值和空值。

创建普通索引时，通常使用的关键字是 INDEX 。

CREATE INDEX index_id ON tb_student(id);

2）唯一索引：

        唯一索引与普通索引类似，不同的是创建唯一性索引的目的不是为了提高访问速度，而是为了避 免数据出现重复。唯一索引列的值必须唯一，允许有空值。如果是组合索引，则列值的组合必须唯 一。

创建唯一索引通常使用 UNIQUE 关键字。

CREATE UNIQUE INDEX index_id ON tb_student(id);

3) 主键索引：

        主键索引就是专门为主键字段创建的索引，也属于索引的一种。主键索引是一种特殊的唯一索 引，不允许值重复或者值为空。

创建主键索引通常使用 PRIMARY KEY 关键字。不能使用 CREATE INDEX 语句创建主键索引。

ALTER TABLE tb_student ADD PRIMARY KEY (id)

4) 全文索引：

        全文索引主要用来查找文本中的关键字，只能在 CHAR 、 VARCHAR 或 TEXT 类型的列上 创建。全文索引允许在索引列中插入重复值和空值。

创建全文索引使用 FULLTEXT 关键字。

CREATE FULLTEXT INDEX index_info ON tb_student(info);

 3.实际使用区分：

        在实际使用中，索引通常被创建成单列索引和组合索引。

1）单列索引：

        单列索引就是索引只包含原表的一个列。在表中的单个字段上创建索引，单列索引只根据该字段 进行索引。单列索引可以是普通索引，也可以是唯一性索引，还可以是全文索引。只要保证该索引只 对应一个字段即可。

2）组合索引：

        组合索引也称为复合索引或多列索引。相对于单列索引来说，组合索引是将原表的多个列共同组 成一个索引。多列索引是在表的多个字段上创建一个索引。该索引指向创建时对应的多个字段，可以 通过这几个字段进行查询。但是，只有查询条件中使用了这些字段中第一个字段时，索引才会被使用。

四、索引失效

1.查询条件中带有or,除非所有的查询条件都建有索引，否则索引失效

2.like查询是以%开头

3.如果列类型是字符串，那在查询条件中需要将数据用引号引用起来，否则不走索引

4.索引列上参与计算会导致索引失效

5.违背最左匹配原则

6.如果mysql估计全表扫描要比使用索引要快，会不适用索引

1) 没有查询条件，或者查询条件没有建立索引 

2) 在查询条件上没有使用引导列 

3) 查询的数量是大表的大部分，应该是30％以上。 

4) 索引本身失效

5) 查询条件使用函数在索引列上，或者 对索引列进行运算， 运算包括(+，-，*，/，! 等) 错误的例子：select * from test where id-1=9; 正确的例子：select * from test where id=10; 

6) 对小表查询 

7) 提示不使用索引

8) 统计数据不真实 

9) CBO计算走索引花费过大的情况。其实也包含了上面的情况，这里指的是表占有的block要比索引小。 

10)隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误. 由于表的字段tu_mdn定义为varchar2(20),但在查询时把该字段作为number类型以where条件传给Oracle,这样会导致索引失效. 错误的例子：select * from test where tu_mdn=13333333333; 正确的例子：select * from test where tu_mdn='13333333333'; 

11) 1,<> 2,单独的>,<,(有时会用到，有时不会) 

12)like "%_" 百分号在前. 

13)表没分析. 

14)单独引用复合索引里非第一位置的索引列. 

15)字符型字段为数字时在where条件里不添加引号. 

16)对索引列进行运算.需要建立函数索引. 

17)not in ,not exist. 

18)当变量采用的是times变量，而表的字段采用的是date变量时.或相反情况。 

19)B-tree索引 is null不会走,is not null会走,位图索引 is null,is not null 都会走 

20)联合索引 is not null 只要在建立的索引列（不分先后）都会走, in null时 必须要和建立索引第一列一起使用,当建立索引第一位置条件是is null 时,其他建立索引的列可以是is null（但必须在所有列 都满足is null的时候）,或者=一个值； 当建立索引的第一位置是=一个值时,其他索引列可以是任何情况（包括is null =一个值）,以上两种情况索引都会走。其他情况不会走。