MySQL索引

1.索引是什么

索引是一个单独的、存储在磁盘上的数据库结构 ，包含着对数据表里所有记录的引用指针。简单来讲，数据库索引就像是书前面的目录，能加快数据库的查询速度。

（1）索引的存储类型

MySQL中索引的存储类型有两种，即 BTree 和 Hash。

hash类型的索引：查询单条快，范围查询慢
btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）

（2）MySQL的存储引擎

MySQL 的存储引擎有：InnoDB、MyISAM、Memory、Heap。InnoDB / MyISAM 只支持 BTree 索引，Memory / Heap 都支持 BTree 和 Hash 索引。

什么是存储引擎：https://blog.csdn.net/bbj12345678/article/details/120786449

2.索引的分类

MySQL数据库一共支持5种类型的索引，分别是普通索引、唯一性索引、主键索引、复合索引和全文索引。

（1）普通索引：MySQL中基本索引类型，添加普通索引的列对数据没有特殊要求，允许在定义索引的列中插入重复值和空值。

创建表是添加普通索引，语法如下：

create table test(
id int not null  primary key,
tname varchar(30),
gender char(6),
email varchar(50),
key 索引名称(字段名)
)

或者把key关键字换成index。

例1：创建一个名为test的表，有id，tname，gender和email四个字段，将tname字段设置为普通索引。

create table test(
id int not null  primary key,
tname varchar(30),
gender char(6),
email varchar(50),
key index_tname(tname) 
)

查看表结构

 

  创建表之后向表内的字段添加普通索引的sql语法：

alter table 表名 add index 索引名称(字段);

例2：在test表中添加一个名为time的字段，数据类型为timestamp类型，将其设置为普通索引

alter table test add column time TIMESTAMP;
alter table test add index index_time(time);

 删除普通索引语法：

alter table 表名 drop index `索引名称`;

查看指定表的索引的语法：

show index from 表名;

 （2）唯一索引：要求索引列的值必须唯一，但允许有空值，主键索引是一种特殊的唯一索引，不允许空值。

唯一性索引常用语添加在注入身份证号、学号、电子邮箱等字段中，不可以添加在注入名字、性别、年龄等字段中。

创建表的时候添加唯一索引的语法（与普通索引相似），在创建表的时候，最后一行添加：

unique key(字段名称);

例3：在test表中添加一个名为mail的字段，数据类型为varchar(50)，将其设置为唯一索引。 

alter table test add column mail varchar(50);
alter table test add  UNIQUE key(mail);

（3）主键索引：一种特殊的唯一索引，不允许有空值。是数据库的所有索引中查询速度最快的，并且每个数据表只能有1个主键索引列。语法与唯一索引相似，将unique key换为primary key。

（4）复合索引：包含不同的字段的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用复合索引时遵循最左前缀集合。

如：将IP和端口号作为标识数据包的依据，这时就可以把IP地址的列和端口号的列创建为复合索引。

注意：复合索引相当于一个多列的主键索引，因此，添加复合索引的任何一个列都不允许数据为空，并且这些列不允许数据完全相同。

（5）全文索引：用于解决大数据量的情况下模糊匹配的问题。如果数据库中某个字段的数据量非常大，那么如果我们想要使用like+通配符的方式进行查找，速度就会变得非常慢。

全文索引的原理便是通过分词技术，分析处文本中关键字及其出现的频率，并依次建立索引。主要限制如下：

1>MySQL5.6以后的版本，MyISAM引擎和InnoDB引擎都支持全文索引。
2>只有字段数据类型为char、varchar、以及text的字段才支持添加全文索引。

语法如下：

create table intro(id int,content text(500))ENGINE=MyISAM;
create fulltext index id_con on intro(content);

创建表以后添加全文索引，删除索引语法：

alter table 表名 add fulltext index(字段名);
alter table 表名 drop index 索引名称;

创建了全文索引后，也不能够使用like+通配符的方式进行模糊查询，全文索引的使用有其特定的语法，如下所示：

select * from test where match(tname) against ('三');

其中，match后面的括号里是含有全文索引的字段，against后面的括号里是要模糊匹配的内容。
此外，全文索引的作用并不是唯一的，在很多场景下，我们并不会使用MySQL数据库内置的全文索引，而是使用第三方类似的索引以实现相同的功能。

 3.索引的测试

向例1的test表中插入100万条数据进行测试

#创建存储过程，实现批量插入记录
delimiter $$ #声明存储过程的结束符号为$$
create procedure insertToTest()
BEGIN
    declare i int default 1;
    while(i<300000)do
        insert into test values(i,concat('york',i),'男',concat('york',i,'@qq.com'));
        set i=i+1;
    end while;
END$$ #$$结束
delimiter ; #重新声明分号为结束符号

#调用存储过程
call insertToTest();

测试之前，先查询当前表的索引情况，将所有的索引删除，包括主键（唯一索引）

 

测试一条查询语句

select * from s1 where id=555;

 

 

参考文章：

https://www.jb51.net/article/232312.htm

https://www.cnblogs.com/bypp/p/7755307.html

https://blog.codinglabs.org/articles/theory-of-mysql-index.html