数据库应用：MySQL索引

目录

一、理论

1.MySQL三层逻辑架构

2.索引结构

3.MyISAM与InnoDB对比

4.sql优化

5.MySQL 索引

6.MySQL索引原理

二、实验

1.创建索引

三、总结

---

一、理论

1.MySQL三层逻辑架构

 MySQL的存储引擎架构将查询处理与数据的存储/提取相分离。

MySQL的逻辑架构图如下：

（1）第一层负责连接管理、授权认证、安全等等

每个客户端的连接都对应着服务器上的一个线程。服务器上维护了一个线程池，避免为每个连接都创建销毁一个线程。当客户端连接到MySQL服务器时，服务器对其进行认证。可以通过用户名和密码的方式进行认证,也可以通过SSL证书进行认证。登录认证通过后，服务器还会验证该客户端是否有执行某个查询的权限。

（2）第二层负责解析查询

编译SQL，并对其进行优化(如调整表的读取顺序，选择合适的索引等)。对于SELECT语句，在解析查询前,服务器会先检查查询缓存，如果能在其中找到对应的查询结果，则无需再进行查询解析、优化等过程，直接返回查询结果。存储过程、触发器、视图等都在这-层实现。

（3）第三层是存储引擎

存储引擎负责在MySQL中存储数据、提取数据、开启一个事务等等。 存储引擎通过API与上层进行通信，这些API屏蔽了不同存储弓|擎之间的差异，使得这些差异对，上层查询过程透明。存储引擎不会去解析SQL。

2.索引结构

（1）概述

MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同的索引结构，主要包含以下几种：

表1 索引结构

索引结构	描述
B+Tree索引	最常见的索引类型，大部分引擎都支持 B+ 树索引
Hash索引	底层数据结构是用哈希表实现的, 只有精确匹配索引列的查询才有效, 不支持范围查询
R-tree(空间索引）	空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少
Full-text(全文索引)	是一种通过建立倒排索引,快速匹配文档的方式。类似于Lucene,Solr,ES

（2）不同的存储引擎对于索引结构的支持情况

表2 存储引擎对于索引结构的支持情况

索引	InnoDB	MyISAM	Memory
B+tree索引	支持	支持	支持
Hash 索引	不支持	不支持	支持
R-tree 索引	不支持	支持	不支持
Full-text	5.6版本之后支持	支持	不支持

3.MyISAM与InnoDB对比

（1）存储结构

MyISAM:每个MyISAM在磁盘上存储成三个文件。分别为:表定义文件、数据文件、索引文件。第一一个文件的名字以表的名字开始，扩展名指出文件类型。.frm文件存储表定义。 数据文件的扩展名为.MYD (MYData)。索引文件的扩展名是.MYI (MYIndex)。

InnoDB:所有的表都保存在同一个数据文件中(也可能是多个文件，或者是独立的表空间文件)，InnoDB表的大小只受限于操作系统文件的大小，- -般为2GB。

 

 

（2）存储空间

MyISAM: MyISAM支持 E种不同的存储格式:静态表(默认,但是注意数据末尾不能有空格，会被去掉)、动态表、压缩表。当表在创建之后并导入数据之后，不会再进行修改操作，可以使用压缩表,极大的减少磁盘的空间占用。

（3）可移植性、备份及恢复

MyISAM:数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。

InnoDB:免费的方案可以是拷贝数据文件、备份binlog,或者用mysqldump,在数据量达到几十G的时候就InnoDB:免费的方案可以是拷贝数据文件、备份binlog,或者用mysqldump,在数据量达到几十G的时候就

（4）事务支持

MyISAM:强调的是性能，每次查询具有原子性，其执行数度比InnoDB类型更快，但是不提供事务支持。

InnoDB:提供事务支持事务，外部键等高级数据库功能。具有事务(commit)、 回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。

（5）AUTO_INCREMENT

MyISAM:可以和其他字段一起建立联合索引。引擎的自动增长列必须是索引,如果是组合索引，自动增长可以怀是第一-列， 他可以根据前面几列进行排序后递增。

InnoDB: InnoDB中必须包含只有该字段的索引。引擎的自动增长列必须是索引，如果是组合索引也必须是组合索引的第一列。

(6)表锁差异

MyISAM:只支持表级锁，用户在操作myisam表时， select, update, delete, insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。

InnoDB:支持事务和行级锁，是innodb的最 大特色。行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的。

（7）全文索引

MyISAM:支持FULLTEXT类型的全文索引InnoDB:不支持FULLTEXT类型的全文索引，但是innodb可以使用sphinx插件支持全文索引，并且效果更好。

（8）表主键

MyISAM:允许没有任何索引|和主键的表存在，索弓都是保存行的地址。

InnoDB:如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见)，数据是主索引的一部分,附加索引保存的是主索引的值。

（9）表的具体行数

MyISAM:保存有表的总行数， 如果select count() from table;会直接取出出该值。

 

InnoDB:没有保存表的总行数，如果使用select count(*) from table;就会遍历整个表，消耗相当大，但是在加了wehre条件后, myisam和innodb处理的方式都一样。

 

 

（10）CRUD操作

MyISAM:如果执行大量的SELECT, MyISAM是更好的选择。

InnoDB:如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表。

（11）外键

MyISAM:不支持。

InnoDB:支持。

4.sql优化

（1）背景

性能低、执行时间太长、等待时间太长、连接查询、索引失效。

（2）sql语句执行过程

① 编写过程

select distinct …… from …… join …… on   ……  where …… group by …… having  …… order by ……

② 解析过程

from …… on …… join …… where …… group by …… having …… select distinct …… order by …… 

(3) 优化索引

3sq|优化就是优化索引:
索引相当于书的目录。
索弓|的数据结构是B+树。
 

5.MySQL 索引

（1）概念

①索引是一个排序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址（类似于C语言的链表通过指针指向数据记录的内存地址）。

②使用索引后可以不用扫描全表来定位某行的数据，而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据，因此能加快数据库的查询速度。

③索引就好比是一本书的目录，可以根据目录中的页码快速找到所需的内容。

④索引是表中一列或者若干列值排序的方法。

⑤建立索引的目的是加快对表中记录的查找或排序。

 

（2）作用

① 设置了合适的索引之后，数据库利用各种快速定位技术，能够大大加快查询速度，这是创建索引的最主要的原因。

② 当表很大或查询涉及到多个表时，使用索引可以成千上万倍地提高查询速度。

③ 可以降低数据库的IO成本，并且索引还可以降低数据库的排序成本。

④ 通过创建唯一性索引，可以保证数据表中每一行数据的唯一性。

⑤ 可以加快表与表之间的连接。

⑥ 在使用分组和排序时，可大大减少分组和排序的时间。

 

 

（3）创建索引的原则依据

索引随可以提升数据库查询的速度，但并不是任何情况下都适合创建索引。因为索引本身会消耗系统资源，在有索引的情况下，数据库会先进行索引查询，然后定位到具体的数据行，如果索引使用不当，反而会增加数据库的负担。

表1 创建索引依据

序号	依据
1	表的主键、外键必须有索引。因为主键具有唯一性，外键关联的是子表的主键，查询时可以快速定位。
2	记录数超过300行的表应该有索引。如果没有索引，需要把表遍历一遍，会严重影响数据库的性能。
3	经常与其他表进行连接的表，在连接字段上应该建立索引。
4	唯一性太差的字段不适合建立索引。
5	更新太频繁地字段不适合创建索引。
6	经常出现在 where 子句中的字段，特别是大表的字段，应该建立索引。
7	在经常进行 GROUP BY、OPDER BY 的字段上建立索引
8	索引应该建在选择性高的字段上。
9	索引应该建在小字段上，对于大的文本字段甚至超长字段，不要建索引。

（4）优势与弊端

表2 索引优势与弊端

序号	索引优势	索引弊端
1	提高查询效率(降低IO使用率)	索引本身很大，可以存放在内存或硬盘上，通常存储在硬盘上。
2	降低CPU使用率	索引不是所有情况都使用，比如①少量数据②频繁变化的字段③很少使用的字段
3	/	索引会降低增删改的效率

（5）分类

表3 索引分类

序号	索引分类	含义	特点	关键字
1	普通索引（单值索引）	快速定位特定数据	可以有多个
2	唯一索引	避免同一个表中某数据列中的值重复	可以有多个	UNIQUE
3	组合索引	单列索引与多列索引
4	主键索引	针对于表中主键创建的索引	默认自动创建, 只能有一个	PRIMARY
5	全文索引	全文索引查找的是文本中的关键词，而不是比较索引中的值	可以有多个	FULLTEXT
备注	备注:唯一索引和主键索引唯一的区别:主键索引不能为null

①普通索引

普通索引概念：最基本的索引类型，没有唯一性之类的限制。

直接创建索引：

CREATE INDEX 索引名 ON 表名 (列名[(length)]);
#(列名(length))：length是可选项，下同。如果忽略 length 的值，则使用整个列的值作为索引。如果指定使用列前的 length 个字符来创建索引，这样有利于减小索引文件的大小。
#索引名建议以“_index”结尾。

修改表方式创建：

ALTER TABLE 表 名 ADD INDEX 索引名 (列名);

创建表的时候指定：

CREATE TABLE 表名 (字段1 数据类型,字段2 数据类型[,...],UNIQUE 索引名 (列名));

② 唯一索引

唯一索引概念：与普通索引类似，但区别是唯一索引列的每个值都唯一。唯一索引允许有空值（注意和主键不同）。如果是用组合索引创建，则列值的组合必须唯一。添加唯一键将自动创建唯一索引。

直接创建唯一索引：

CREATE UNIQUE INDEX 索引名 ON 表名(列名);

修改表方式创建：

ALTER TABLE 表 名 ADD UNIQUE 索引名 (列名);

创建表的时候指定唯一索引：

CREATE TABLE 表名 (字段1 数据类型,字段2 数据类型[,...],UNIQUE 索引名 (列名));

③ 主键索引

 

主键索引的概念：是一种特殊的唯一索引，必须指定为“PRIMARY KEY”。一个表只能有一个主键，不允许有空值。 添加主键将自动创建主键索引。

直接创建主键索引：

CREATE TABLE 表名 (字段1 数据类型,字段2 数据类型[,...],primary key (字段1));

修改表方式创建：

ALTER TABLE 表名 ADD PRIMARY KEY (列名); 

④组合索引

组合索引（单列索引与多列索引）：可以是单列上创建的索引，也可以是在多列上创建的索引。需要满足最左原则，因为 select 语句的 where 条件是依次从左往右执行的，所以在使用 select 语句查询时 where 条件使用的字段顺序必须和组合索引中的排序一致，否则索引将不会生效。

 

CREATE TABLE 表名 (列名1 数据类型,列名2 数据类型,列名3 数据类型,INDEX 索引名 (列名1,列名2,列名3));
 
select * from 表名 where 列名1='...' AND 列名2='...' AND 列名3='...';

⑤全文索引
全文索引（FULLTEXT）：适合在进行模糊查询的时候使用，可用于在一篇文章中检索文本信息。在 MySQL5.6 版本以前。

FULLTEXT 索引仅可用于 MyISAM 引擎，在 5.6 版本之后 innodb 引擎也支持 FULLTEXT 索引。全文索引可以在 CHAR、VARCHAR 或者 TEXT 类型的列上创建。每个表只允许有一个全文索引。
直接创建全文索引：

CREATE FULLTEXT INDEX 索引名 ON 表名 (列名);

修改表方式创建：

ALTER TABLE 表名 ADD FULLTEXT 索引名 (列名);

创建表的时候指定索引：

CREATE TABLE 表名 (字段1 数据类型[,...],FULLTEXT 索引名 (列名));
#数据类型可以为 CHAR、VARCHAR 或者 TEXT

使用全文索引查询

SELECT * FROM 表名 WHERE MATCH(列名) AGAINST('查询内容');

（6）聚集索引&二级索引

而在在 InnoDB 存储引擎中，根据索引的存储形式，又可以分为以下两种：

分类	含义	特点
聚集索引(Clustered Index)	将数据存储与索引放到了一块，索引结构的叶子节点保存了行数据	必须有,而且只有一个
二级索引(Secondary Index)	将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键	可以存在多个

聚集索引选取规则 :

① 如果存在主键，主键索引就是聚集索引。

② 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引。

③ 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索 引

④ 聚集索引的叶子节点下挂的是这一行的数据 。

⑤ 二级索引的叶子节点下挂的是该字段值对应的主键值。

聚集索引和二级索引的具体结构如下：

 执行如下的 SQL 语句时，具体的查找过程：

 具体过程如下 :
① . 由于是根据 name 字段进行查询，所以先根据 name='Arm' 到 name 字段的二级索引中进行匹配查
找。但是在二级索引中只能查找到 Arm 对应的主键值 10 。
② . 由于查询返回的数据是 * ，所以此时，还需要根据主键值 10 ，到聚集索引中查找 10 对应的记录，最终找到10 对应的行 row 。
③ . 最终拿到这一行的数据，直接返回即可。
 

（7）查看索引

show index from 表名;
show keys from 表名;

各字段含义如下：

表4 各字段含义

Table	表的名称
Non_unique	如果索引不能包括重复词，则为 0；如果可以，则为 1。
Key_name	索引的名称。
Seq_in_index	索引中的列序号，从 1 开始。
Column_name	列名称。
Collation	列以什么方式存储在索引中。在 MySQL 中，有值‘A’（升序）或 NULL（无分类）。
Cardinality	索引中唯一值数目的估计值。
Sub_part	如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为 NULL。
Packed	指示关键字如何被压缩。如果没有被压缩，则为 NULL。
Null	如果列含有 NULL，则含有 YES。如果没有，则该列含有 NO。
Index_type	用过的索引方法（BTREE, FULLTEXT, HASH, RTREE）。
Comment	备注。

（8）删除索引

①直接删除索引

DROP INDEX 索引名 ON 表名;

②修改表方式删除索引

ALTER TABLE 表名 DROP INDEX 索引名;

③删除主键索引

ALTER TABLE 表名 DROP PRIMARY KEY;

6.MySQL索引原理

（1）原理

MySQL索引的底层数据结构是B+树。

B+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储弓|擎就是用B+Tree实现其索引结构。

B-Tree结构图中每个节点中不仅包含数据的key值,还有data值。而每一个页的存储空间是有限的， 如果data数据较大时将会导致每个节点(即一 一个页)能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大,增大查询时的磁盘I/O次数,进而影响查询效率。在B+Tree中， 所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+ Tree的高度。

B+Tree相对于B-Tree有几点不同:

非叶子节点只存储键值信息。

所有叶子节点之间都有一个链指针。

数据记录都存放在叶子节点中。

将上一节中的B-Tree优化，由于B+Tree的非叶子节 点只存储键值信息，假设每个磁盘块能存储4个键值及指针信息，则变成B+ Tree后其结构如下图所示:

二、实验

1.创建索引

创建数据库及数据表

 插入数据

（1）普通索引

①直接创建索引

 ② 修改表方式创建

③ 创建表的时候指定索引

 （2）唯一索引

① 直接创建唯一索引

 

②修改表方式创建

③创建表的时候指定

 （3）主键索引

①创建表的时候指定

 

②修改表方式创建

ALTER TABLE 表名 ADD PRIMARY KEY (列名); 

（4）组合索引（单列索引与多列索引)

 

 （5）全文索引

① 直接创建索引

② 修改表方式创建

ALTER TABLE 表名 ADD FULLTEXT 索引名 (列名);

③ 创建表的时候指定索引

CREATE TABLE 表名 (字段1 数据类型[,...],FULLTEXT 索引名 (列名)); 

④使用全文索引查询

 或者：

三、总结

索引分为：
① 普通索引 ：针对所有字段，没有特殊的需求/规则
② 唯一索引 : 针对唯一性的字段，仅允许出现一次空值
③ 组合索引 (多列/多字段组合形式的索引)  从左往右
④ 全文索引（varchar char text）
⑤ 主键索引 ：针对唯一性字段、且不可为空，同时一张表只允许包含一个主键索引
 

创建索引：
① 在创建表的时候，直接指定index
② alter修改表结构的时候，进行add 添加index
③ 直接创建索引index
PS:主键索引——》直接创建主键即可