天津建设工程信息网网_武汉平面设计公司_软件外包网_seo教学视频教程

背景概念

索引可以显著提高查询速度，特别是在处理大量数据时效果更为明显，它类似于书籍的目录，而不必进行全表扫描。不用加内存，不用改程序，不用调sql，只要执行正确的 create index ，查询速度就可能提高成百上千倍。但是天下没有免费的午餐，查询速度的提高是以插入、更新、删除的速度为代价的，这些写操作，增加了大量的IO 。常见索引分为：

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

数据库文件，本质其实就是保存在硬盘，其均存储在 /var/lib/mysql 路径下。我们可以通过指令ll /var/lib/mysql 来查看此时MySQL下的文件。

当要高频的查询硬盘中数据，就不可避免的要进行频繁的IO，硬盘I/O（Input/Output）是指数据在硬盘和内存之间的传输过程，包括数据的读取和写入，索引的出现就是为了减少IO次数，更加高效的查询。MySQL数据库的性能很大程度上依赖于硬盘的I/O操作。

局部性原理：

时间局部性：如果一个数据被访问，在不久后它很可能再次被访问
空间局部性：如果一个数据被访问，其周围的数据不久后很有可能再次被访问

对操作系统和MySQL来说，其加载数据时会把该数据周围一部分数据都加载进去，因为周围的数据访问的概率很高，后续访问时不必重新去硬盘读取数据，减少了IO次数。

磁盘的一个扇区大小是512 byte，对于操作系统来说，不会以扇区为单位读取，而是以块为单位读取，一般来说基本单位是4 kb。

而 MySQL 作为一款应用软件，可以想象成一种特殊的文件系统，IO单位小意味着单次读取到的数据少，进而导致IO次数多，查询效率低。它有着更高的IO 场景，所以，为了提高基本的IO 效率， MySQL 进行 IO 的基本单位是 16KB ( 后面统一使用 InnoDB 存储引擎讲解)。这个基本数据单元，在 MySQL 这里叫做 page。

我们可以通过以下指令来查看一个page的大小。

show global status like 'innodb_page_size';

wAAACH5BAEKAAAALAAAAAABAAEAAAICRAEAOw==

聚簇索引

在 InnoDB 中，用户数据与索引数据放在一起，即索引底层的叶子节点存储数据，这种索引方案叫做聚簇索引。

例如，我们使用如下代码建表。

create table innodb_test( id int primary key, name varchar(10) not null)engine=innodb;

建好表后在数据库中就会出现如下文件

后缀为ibd的文件存储表的索引和数据。

非聚簇索引

MyISAM 引擎同样使用 B+树作为索引结果，索引底层的叶子节点存储指向数据的指针，即索引和数据是分离存储的，这种索引方案叫做非聚簇索引。。

例如，我们使用如下代码建表。

create table myisam_test( id int primary key, name varchar(10) not null)engine=myisam;

建好表后在数据库中就会出现如下文件

sdi：存储表的结构
MYD：存储数据
MYI：存储索引

索引操作

创建主键索引

第一种方式：在创建表的时候，直接在字段名后指定 primary key

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

第二种方式：在创建表的最后，指定某列或某几列为主键索引

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

第三种方式：创建表以后再添加主键

create table user3(id int, name varchar(10));
alter table user3 add primary key(id);

由于主键不可重复，所以主键索引的效率高。

创建唯一键索引

第一种方式：在表定义时，在某列后直接指定unique唯一属性

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

第二种方式：创建表时，在表的后面指定某列或某几列为unique

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

第三种方式：创建表以后再添加唯一键

create table user6(id int primary key, name varchar(10)）；
alter table user6 add unique(name);

创建辅助索引

第一种方式：在表的定义最后，指定某列为索引

create table user8(id int primary key, 
name varchar(10),
email varchar(30),
index(name));

第二种方式：创建完表以后指定某列为辅助索引

create table user9(id int primary key, 
name varchar(10), 
email varchar(30));
alter table user9 add index(name);

第三种方式：创建一个索引名为 idx_name 的索引

create table user10(id int primary key, 
name varchar(10), email varchar(30));
create index idx_name on user10(name);

我们知道InnoDB采用聚簇索引方案，即其索引的叶子节点存储的是数据的值，那么如果创建多个索引，岂不是每一个索引都要保存一份数据？

其实不是，对于聚簇索引而言，其辅助索引存储的是主键的值：

当通过辅助索引查询时，会通过key找到对应主键，然后再通过主键去主键索引查找正行数据。

查询索引

show index/keys from 表名\G

例如，我们使用以下代码来创建表user

create table user(id int primary key, 
name varchar(10) unique,
email varchar(30),
index(email));

我们可以看到三行索引介绍。

删除索引

第一种方法：删除主键索引

alter table 表名 drop primary key;

第二种方法：其他索引的删除，语法中索引名就是show index from 表名中的 Key_name 字段

alter table 表名 drop index 索引名;

第三种方法方法

drop index 索引名 on 表名;

创建全文索引

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

例如，我们创建表articles如下：

CREATE TABLE articles (
id int auto_increment primary key,
title varchar(200),
body text,
fulltext (title,body)
)engine=MyISAM;

INSERT INTO articles (title,body) VALUES
('MySQL Tutorial','DBMS stands for DataBase ...'),
('How To Use MySQL Well','After you went through a ...'),
('Optimizing MySQL','In this tutorial we will show ...'),
('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
('MySQL vs. YourSQL','In the following database comparison ...'),
('MySQL Security','When configured properly, MySQL ...');

如果我们想要查询有关 database 的数据，使用如下查询方式，虽然查询出数据，但是没有使用到全文索引。

select * from articles where body like '%database%';

可以用 explain 工具看一下，是否使用到索引

如果想要使用索引，我们需要使用如下语句：

select * from articles where match(title,body) against ('database');