Mysql-索引

1.介绍

索引是数据库管理系统中用于提高查询速度的一种数据结构。在MySQL中，索引可以看作是一种特殊的表，其中包含了对数据表中特定列的值及其在数据表中的位置信息。通过使用索引，MySQL可以在不需要扫描整个表的情况下快速找到与查询条件匹配的记录。

常见索引分为： 

• 主键索引(primary key)
• 唯一索引(unique)
• 普通索引(index)
• 全文索引(fulltext)--解决中文索引问题。 

2.建立共识 

• MySQL 中的数据文件，是以page为单位保存在磁盘当中的。
• MySQL 的 CURD 操作，都需要通过计算，找到对应的插入位置，或者找到对应要修改或者查询的数据。
• 而只要涉及计算，就需要CPU参与，而为了便于CPU参与，一定要能够先将数据移动到内存当中。
• 所以在特定时间内，数据一定是磁盘中有，内存中也有。后续操作完内存数据之后，以特定的刷新策略，刷新到磁盘。而这时，就涉及到磁盘和内存的数据交互，也就是IO了。而此时IO的基本单位就是Page。
• 为了更好的进行上面的操作， MySQL 服务器在内存中运行的时候，在服务器内部，就申请了被称为 Buffer Pool 的的大内存空间，来进行各种缓存。其实就是很大的内存空间，来和磁盘数据进行IO交互。
• 为了更高的效率，一定要尽可能的减少系统和磁盘IO的次数。

3.优点 

（1）类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本，这也是创建索引最主要的原因。

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

（3）在实现数据的参考完整性方面，可以加速表和表之间的连接。换句话说，对于有依赖关系的子表和父表联合查询时，可以提高查询速度。

（4）在使用分组和排序子句进行数据查询时，可以显著减少查询中分组和排序的时间，降低了CPU的消耗。

4.缺点

（1）创建索引和维护索引要耗费时间，并且随着数据量的增加，所耗费的时间也会增加。

（2）索引需要占磁盘空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间存储在磁盘上，如果有大量的索引，索引文件就可能比数据文件更快达到最大文件尺寸。

（3）虽然索引大大提高了查询速度，同时却会降低更新表的速度。当对表中的数据进行增加、删除和修改的时候，索引也要动态地维护，这样就降低了数据的维护速度。

5.页目录 

• 本质上，书中的目录，是多花了纸张的，但是却提高了效率。
• 所以，目录，是一种“空间换时间的做法”。

5.1 单页情况 

 

通过键值，Mysql进行自动排序，可以很方便引入目录。

 5.2 多页情况

存在一个目录页来管理页目录，目录页中的数据存放的就是指向的那一页中最小的数据。有数据，就可通过比较，找到该访问那个Page ，进而通过指针，找到下一个 Page 。

其实目录页的本质也是页，普通页中存的数据是用户数据，而目录页中存的数据是普通页的地址。

 下图是InnoDB 的索引结构---B+树，以此来管理数据。

 5.3 比较B+树与其他数据结构差异

• 链表？线性遍历
• 二叉搜索树？退化问题，可能退化成为线性结构
• AVL &&红黑树？虽然是平衡或者近似平衡，但是毕竟是二叉结构，相比较多阶B+，意味着树整体过高，大家都是自顶向下找，层高越低，意味着系统与硬盘更少的IO Page交互。
• Hash？官方的索引实现方式中， MySQL 是支持HASH的，不过 InnoDB 和 MyISAM 并不支持.Hash跟进其算法特征，决定了虽然有时候也很快(O(1))，不过，在面对范围查找就明显不行，另外还有其他差别。

5.3.1 与B树的比较

B树的结构： 

• B树节点，既有数据，又有Page指针，而B+，只有叶子节点有数据，其他目录页，只有键值和Page指针。
• B+树叶子节点全部相连，而B树没有。

为何选择B+？

• 节点不存储data，这样一个节点就可以存储更多的key。可以使得树更矮，所以IO操作次数更少。
• 叶子节点相连，更便于进行范围查找。

6.聚簇索引与非聚簇索引 

MyISAM 存储引擎 - 主键索引

MyISAM 引擎同样使用 B+ 树作为索引结果，叶节点的 data 域存放的是数据记录的地址。

MyISAM 最大的特点是，将索引Page和数据Page分离，也就是叶子节点没有数据，只有对应数据的地址。  其中， MyISAM 这种用户数据与索引数据分离的索引方案，叫做非聚簇索引。

相较于 InnoDB 索引， InnoDB 是将索引和数据放在一起的。 其中， InnoDB 这种用户数据与索引数据在一起索引方案，叫做聚簇索引 。

MySQL 除了默认会建立主键索引外，我们用户也有可能建立按照其他列信息建立的索引，一般这种索引可以叫做辅助（普通）索引。对于 MyISAM , 建立辅助（普通）索引和主键索引没有差别，无非就是主键不能重复，而非主键可重复。

所以通过辅助（普通）索引，找到目标记录，需要两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。这种过程，就叫做回表查询。

7.索引的操作 

7.1 创建主键索引  

• 第一种方式  

-- 在创建表的时候，直接在字段名后指定 primary key

create table user1(id int primary key, name varchar(30));

• 第二种方式：  

-- 在创建表的最后，指定某列或某几列为主键索引

create table user2(id int, name varchar(30), primary key(id));

• 第三种方式：  

create table user3(id int, name varchar(30));

-- 创建表以后再添加主键

alter table user3 add primary key(id)；

主键索引的特点：  

• 一个表中，最多有一个主键索引，当然可以使符合主键。
• 主键索引的效率高（主键不可重复）。
• 创建主键索引的列，它的值不能为null，且不能重复。
• 主键索引的列基本上是int。

 7.2 唯一索引的创建

• 第一种方式 

-- 在表定义时，在某列后直接指定 unique 唯一属性。

create table user4(id int primary key, name varchar(30) unique);

• 第二种方式  

-- 创建表时，在表的后面指定某列或某几列为 unique

create table user5(id int primary key, name varchar(30), unique(name));

•  第三种方式

create table user6(id int primary key, name varchar(30) ）；

alter table user6 add unique(name);

唯一索引的特点：

• 一个表中，可以有多个唯一索引。
• 查询效率高。
• 如果在某一列建立唯一索引，必须保证这列不能有重复数据。
• 如果一个唯一索引上指定not null，等价于主键索引。

7.3 普通索引的创建  

• 第一种方式 

create table user8(id int primary key,

name varchar(20),

email varchar(30),

index(name) -- 在表的定义最后，指定某列为索引

);

• 第二种方式  

create table user9(id int primary key, name varchar(20), email varchar(30));

alter table user9 add index(name); -- 创建完表以后指定某列为普通索引

• 第三种方式 

create table user10(id int primary key, name varchar(20), email varchar(30));

-- 创建一个索引名为 idx_name 的索引

create index idx_name on user10(name);

普通索引的特点：  

• 一个表中可以有多个普通索引，普通索引在实际开发中用的比较多。
• 如果某列需要创建索引，但是该列有重复的值，那么我们就应该使用普通索引。 

 7.4 全文索引的创建

当对文章字段或有大量文字的字段进行检索时，会使用到全文索引。 MySQL 提供全文索引机制，但是有要求，要求表的存储引擎必须是MyISAM ，而且默认的全文索引支持英文，不支持中文。如果对中文进行全文检索，可以使用sphinx的中文版 (coreseek) 。

CREATE TABLE articles (

id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,

title VARCHAR(200),

body TEXT,

FULLTEXT (title,body)

)engine=MyISAM;

 7.5 查询索引

第一种方法： show keys from 表名；

第二种方法： show index from 表名 ;

第三种方法： desc 表名；

 7.6 删除索引

第一种方法 - 删除主键索引： alter table 表名 drop primary key ;

第二种方法 - 其他索引的删除： alter table 表名 drop index 索引名； 索引名就是 show keys from 表名中的 Key_name 字段 alter table user10 drop index idx_name ;

第三种方法方法： drop index 索引名 on 表名  drop index name on user8;

8.索引创建原则 

• 比较频繁作为查询条件的字段应该创建索引。
• 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件。
• 更新非常频繁的字段不适合作创建索引。
• 不会出现在 where 子句中的字段不该创建索引。