【MySQL】索引

索引

在关系数据库中，索引是一种单独的、物理的对数据库表中一列或多列的值进行排序的一种存储结构，它是某个表中一列或若干列值的集合和相应的指向表中物理标识这些值的数据页的逻辑指针清单。索引的作用相当于图书的目录，可以根据目录中的页码快速找到所需的内容。

索引提供指向存储在表的指定列中的数据值的指针，然后根据您指定的排序顺序对这些指针排序。数据库使用索引以找到特定值，然后顺指针找到包含该值的行。这样可以使对应于表的SQL语句执行得更快，可快速访问数据库表中的特定信息。

当表中有大量记录时，若要对表进行查询，第一种搜索信息方式是全表搜索，是将所有记录一一取出，和查询条件进行一一对比，然后返回满足条件的记录，这样做会消耗大量数据库系统时间，并造成大量磁盘I/O操作；第二种就是在表中建立索引，然后在索引中找到符合查询条件的索引值，最后通过保存在索引中的ROWID（相当于页码）快速找到表中对应的记录。

那么我们再来看看

什么是索引

索引是建立在表上的，是对数据库表中一列或多列的值进行排序的一种结构。索引可以提高查询速度。通过索引，查询数据时可以不必读完记录的所有信息，而只是查询索引列，有效的提高了数据库系统的性能。

实际上，索引也是一种数据结构，目前大部分数据库系统及文件系统都采用B-Tree(B树)或其变种B+Tree(B+树)作为索引结构。B+Tree是数据库系统实现索引的首选数据结构。在MySQL中,索引属于存储引擎级别的概念,不同存储引擎对索引的实现方式是不同的,本文主要讨论MyISAM和InnoDB两个存储引擎的索引实现方式。MyISAM索引实现MyISAM引擎使用B+Tree作为索引结构,叶节点的data域存放的是数据记录的地址。

为什么要有索引

索引的作用：

• 快速取数据；
• 保证数据记录的唯一性；
• 实现表与表之间的参照完整性；
• 在使用ORDER by、group by子句进行数据检索时，利用索引可以减少排序和分组的时间。

总的一句话就是：提高查询速度。

那么

索引建立在哪里

索引建立在表上，是对表中一列或者多列值进行排序的一种结构。索引和表中的属性是有很大的关系的，要通过属性排序之后的结果建立结构。索引是存储在文件中的：

• InnoDB：.frm 存储表的结果 .ibd 存储表中的数据和索引。
• MyISAM：.frm 存储表的结果 .myi 存储索引 .myd 存储数据。

索引的种类

• 普通索引：在创建普通索引时，不附加任何限制条件。这类索引可以创建在任何数据类型中，其值是否唯一和非空由字段本身二点完整性约束条件决定。建立索引以后，查询时可以通过索引进行查询。
• 唯一性索引：使用unique参数可以设置索引为唯一性索引。在创建唯一性索引时，限制该索引的值必须是唯一的。通过唯一性索引。可以更快速的确定某条记录。主键就是一种特殊唯一性索引。
• 全文索引：使用FULLTEXT参数可以设置索引为全文索引。全文索引只能创建在char,varchar或text类型的字段上。查询数据量较大的字符串类型的字段时，使用全文索引可以提高查询速度。MySQL数据库中3.23.23版开始支持全文索引，但只有MyISAM存储引擎支持全文索引。在默认情况下，全文索引的搜索执行方式不区分大小写。但索引的列使用二进制排序后，可以执行区分大小写的全文索引。
• 单列索引：在表中的单个字段上创建索引。单列索引只根据该字段进行索引。单列索引可以是普通索引，也可以是唯一性索引，还可以是全文索引。只要保证该索引只对应一个字段即可。
• 多列索引：多列索引是在表的多个字段上创建一个索引。该索引指向创建时对应的多个字段，可以通过这几个字段进行查询。但是，只有查询条件中使用了这些字段中第一个字段时，索引才会被使用。
• 空间索引：使用SPATIAL参数可以设置索引为空间索引。空间索引只能建立在空间数据类型上，这样可以提高系统获取空间数据的效率。MySQL中的空间数据类型包括GEOMETRY和POINT、LINESTRING和POLYGON等。目前只有MyISAM存储引擎支持空间检索，而且索引的字段不能为空值。

索引的使用

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

索引的优缺点

• 索引的优点是可以提高检索数据的速度，这是创建索引的最主要的原因；对于有依赖关系的子表和父表之间的联合查询时，可以提高查询速度；使用分组和排序子句进行数据查询时，同样可以显著节省查询中分组和排序的时间。

• 索引的缺点是创建和维护索引需要耗费时间，耗费时间的数量随着数据量的增加而增加；索引需要占用物理空间，每一个索引要占一定的物理空间；增加、删除和修改数据时，要动态维护索引，造成数据的维护速度降低。

索引的建立

创建表的时候创建索引：

CREATE TABLE 表名 (
	属性名 数据类型 [完整性约束条件],
	属性名 数据类型 [完整性约束条件],
	......
	属性名 数据类型
	[UNIQUE|FULLTEXT|SPATIAL] INDEX|KEY [别名] (属性名1 [(长度)] [ASC|DESC])
	);

• 创建普通索引:

CREATE TABLE index1(
	id INT,
	name VARCHAR(20),
	sex BOOLEAN,
	INDEX(id)
	);

• 创建唯一性索引:

CREATE TABLE index2(
	id INT UNIQUE,
	name VARCHAR(20),
	UNIQUE INDEX index2_id(id ASC)
	);

• 创建全文索引:

CREATE TABLE index3(
	id INT,
	info VARCHAR(20),
	FULLTEXT INDEX index3_info(info)
	)ENGINE = MyISAM;

• 创建单列索引:

CREATE TABLE index4(
	id INT,
	subject VARCHAR(30),
	INDEX index4_st(subject(10))
	);

• 创建多列索引:

CREATE TABLE index5(
	id INT,
	name VARCHAR(20),
	sex CHAR(4),
	INDEX index5_ns(name,sex)
	);

• 创建空间索引:

CREATE TABLE index6(
	id INT,
	space GEOMETRY NOT NULL,
	SPATIAL INDEX index6_sp(space)
	)ENGINE = MyISAM;

在已经存在的表上创建索引:

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名 ON 表名 (属性名 [(长度)] [ASC|DESC]);

• 创建普通索引:

CREATE INDEX index7_id ON example0(id);

• 创建唯一性索引:

CREATE UNIQUE INDEX index8_id ON index8(course_id);

• 创建全文索引:

CREATE FULLTEXT INDEX index9_info ON index9(info);

• 创建单列索引:

CREATE INDEX index10_addr ON index10(address(4));

• 创建多列索引:

CREATE INDEX index11_na ON index11(name,address);

• 创建空间索引:

CREATE SPATIAL INDEX index12_line ON index12(line);

用ALTER TABLE语句来创建索引:

ALTER TABLE 表名 ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名 (属性名 [(长度)] [ASC|DESC]);

• 创建普通索引

ALTER TABLE example0 ADD INDEX index13_name(name(20));

• 创建唯一性索引

ALTER TABLE index14 ADD UNIQUE INDEX index14_id(course_id);

• 创建全文索引

ALTER TABLE index15 ADD FULLTEXT INDEX index15_info(info);

• 创建单列索引

ALTER TABLE index16 ADD INDEX index16_addr(address(4));

• 创建多列索引

ALTER TABLE index17 ADD INDEX index17_na(name,address);

• 创建空间索引

ALTER TABLE index18 ADD SPATIAL INDEX index18_line(line);

删除索引:

DROP INDEX 索引名 ON 表名;

索引的设计原则

索引可以当作目录 表的目录

• 1、数据量比较低的时候不需要建立索引。
• 2、选择唯一性索引。属性是不重复的。索引的价值是百分之百的。
  • 属性：建立索引时你所选择的属性
  • 索引的价值问题：索引的价值 = distinct（属性） / count（属性）
    • Count（属性）：属性的总数.
    • Distinct（属性）：属性是不重复的数量。
• 3、经常使用的属性上建立索引。
  • 建立索引时选择的哪一个属性，只有使用时也选择，这个属性才能用到索引。
• 4、限制索引的数目。删除不再使用或者使用很少的索引。
• 5、针对在字符串类型上建立的索引而言。
  • Index(name(20)); // 长度字段是影响比较结果的，也就是影响B+树的结构。
  • 长度字段还影响比较时间：长度越长比较的时间越长。

为何使用B+树作为索引结构

那么MySQL最终为什么要采用B+树存储索引结构呢，那么先看看B树和B+树在存储结构上有什么不同？

• B树的每一个结点，存了关键字和对应的数据地址，而B+树的非叶子结点只存关键字，不存放数据地址。因此B+树的每一个非叶子结点存储的关键字是远远多于B树的，B+树的叶子节点存放关键字和数据，因此，从树的高度上来说，B+树的高度要小于B树，使用的磁盘1/0次数少，因此查询会更快一些。
• B树由于每个节点都存储关键字和数据，因此离根节点近的数据，查询的就快，离根节点远的数据，查询的就慢；B+树的所有的数据都存放在叶子节点上，因此在B+树上搜索关键字，找到对应数据的时间是比较平均的，没有快慢之分。
• 在B树上如果做区间查找，遍历的节点是非常多的；B+树所有的叶子节点都被链接成了有序链表结构，因此做整表遍历和区间查找是非常容易的。

索引的分类

• 主键索引：索引建立在主键上。MySQL会默认创建主键索引（所有的表一定会有主键）。
  • 如果我们不自己指定主键，MySQL将以行号作为主键。
• 辅助索引：索引建立在非主键上。

MyISAM存储引擎中索引的加载

MyISAM：数据和索引分别在两个文件中存储。索引：.myi；数据：.myd.

主键索引：

• 要将B+树的结构加载到内存中才能使用。注意：并不是将整个B+树的结构加载到内存中，而是一个节点一个节点的加载。
• 首先应该加载的就是根节点，剩下的就要根据大小比较后的结果判断应该再去加载哪一个节点。
• 叶子节点：存储的是主键（建立索引的属性）和数据的地址。

辅助索引：与主键索引形式相同；

多列辅助索引：index(col1,col2)；

• 比较原则：由左向右原则（最左前缀原则）
  • 先比较第一个属性，如果第一个属性值相同再比较第二个属性。
  • 先以第一个属性建立B+树，如果第一个属性相同再以第二个属性建立B+树。
• 叶子节点存储：建立索引的属性和数据对应的地址。（无论是单列还是多列索引）。
  • 无非是建立索引的属性从一个变成了多个。

Index(name,age) select age from student where name = Bob; //只查询一次

根据B+树就可以将结果查询出来，先查name再查age

Index(name,age) select name from student where age = 15; //无法用到索引

Index(name) select age from student where name = Bob; //要查询两次

根据B+树可以将数据的地址查询出来，再根据数据的地址查询数据

MyISAM下的索引称为：非聚簇（聚集）型索引。

InnoDB存储引擎中索引的加载

叶子节点存储：主键和所有的数据（数据和索引在同一个文件下）.ibd

主键索引：

• 叶子节点存储：主键和这个主键所对应的所有数据。

辅助索引：

• 叶子节点存储：建立索引的属性和它对应的主键值。

InnoDB下的索引称为：聚簇（聚集）型索引。

聚集和非聚集的区别：叶子节点存储的是数据本身还是数据的地址。

两种存储引擎下索引的区别

select * from user where 主键 = 0;

InnoDB:

• 查询一次就可以，原因是主键索引树的叶子节点存储主键和这个主键所对应的所有数据。

MyISAM：

• 查询两次。原因是叶子节点存储建立索引的属性和数据的地址。还需根据地址再去查询出所有的数据。

select age from user where 主键 = 10;

InnoDB：查询一次就可以。

MyISAM：查询两次。

• 如果想让MyISAM也查询一次：必须建立联合索引[index(id,age)]；

Index(name)
Select age from user where name = Bob;

InnoDB：

• 查询两次，第一次是在name所建立的B+树上去查找数据。叶子节点存储name的值和这个name对应的主键的值，然后我们再根据主键的值去主键索引这棵B+树上查询所有的数据的值，最终把age的值返回。

MyISAM：

• 查询两次。一次：name 的索引树，第二次：根据查出的地址再去找所有的数据。

有没有办法优化成一次：

• 联合索引：index(name,age)。叶子节点都要存储建立索引的属性的值。InnoDB还存储主键值，MyISAM还存储数据地址。注意：建立索引的顺序。

区别：聚集和非聚集的区别：叶子节点存储的是数据本身还是数据的地址。

• 主键索引：
  • InnoDB 叶子节点存储主键和这个主键所对应的所有数据。
  • MyISAM 叶子节点存储的是主键（建立索引的属性）和数据的地址。
• 辅助索引：
  • InnoDB 叶子节点存储建立索引的属性和属性所对应的主键值。
  • MyISAM 叶子节点存储建立索引的属性和属性对应的数据的地址。

两种引擎下的索引哪个更好

根据具体情况分析那个索引更好。

只考虑查询效率：InnoDB引擎下的索引更好。（数据和索引在同一文件下。）（缺点：难以维护。数据和索引在同一个文件下，那么数据的修改必然引起索引的修改。）（MyISAM：数据和索引分开存储，更好维护。缺点：查询效率低。）

如何选择引擎

根据实际开发场景。

• 要求更高查询效率并且数据的修改不频繁。InnoDB。
• 数据修改频繁。MyISAM。
• 如果需要的功能更多（使用事务、外键）。这时只能使用InnoDB。（外键和事务的维护实际上也是耗费时间的。）

如何查看是否使用索引

1、插入100w以上的数据，建立索引的时候执行一次，不建立索引的时候执行一次，可以从执行的速度看出是否使用索引。

2、通过explain关键字查看是否真的用到了索引。

• 通过explain所查询出来的关键字：关键字
  • id:选择标识符
  • select_type:表示查询的类型。
  • table:输出结果集的表
  • partitions:匹配的分区
  • type:表示表的连接类型
  • possible_keys:表示查询时，可能使用的索引
  • key:表示实际使用的索引
  • key_len:索引字段的长度
  • ref:列与索引的比较
  • rows:扫描出的行数(估算的行数)
  • filtered:按表条件过滤的行百分比
  • Extra:执行情况的描述和说明

MySQL在执行SQL语句的时候会对SQL语句进行优化，根据自己所建立的索引调整SQL语句的形式（前提是不改变SQL语句本身的结果）。

例如：key(‘name’,’cid’,’school’);
则：
explain select * from student where cid = 123 and name = ‘abc’; 
可优化为：
explain select * from student where name = ‘abc’ and cid = 123;

联合查询中如何使用索引

MySQL会首先判断rb1（别名表a）和rb2（别名表b）哪个表小，这里表小主要指的是行数少，很显然rb2表小，MySQL会对rb2表进行整表遍历，然后在a表上根据stu_id字段进行查询，所以rb2表就是小表，无论如何都是要整表遍历的，是使用不到索引的，但是大表rb1表的stu_id字段创建索引，就能使用到了！所以在链接查询的时候，小表总是要整表搜索的，建立索引没有用，大表创建索引是能提高查询效率的，小表决定查询次数，大表决定查询时间（有索引就快，没有索引就慢）能不能用到索引，用到的是那张表的索引。

explain select * from rb1 a,rb2 b where a.stu_id = b.stu_id\G

假设rb1中有6条数据，rb2中有3条数据

where a.stu_id = b.stu_id底层处理：

• 把b中的所有的stu_id取出来，分别和a中的stu_id作比较；
  • a.stu_id in (23,24,25)；比较三次
• 把a中的所有的stu_id取出来，分别和b中的stu_id作比较；
  • b.stu_id in (23,24,25,40,41,42)；比较六次

MySQL采用第一种做法：把小表中的所有的数据取出来然后和大表中的数据做对比；

• Rb1使用到索引；Rb2没有使用到索引；即大表能用到索引，小表使用不到索引；

explain select * from rb1 a join rb2 b on a.stu_id = b.stu_id where a.stu_id < 25\G

假设这次rb1为小表（<25的数据只有2条）

此时rb1是小表：（<25）的数据只有两行，rb2的数据有三行；所以理应情况下应该是：Rb2使用到索引；Rb1没有使用到索引；但是真实情况是两张表都使用到了索引，原因是：

• Rb2使用到索引：此时rb2表是大表，能使用到索引；
• Rb1使用到索引：因为SQL语句执行（where a.stu_id < 25）时对rb1表使用了索引；

索引例题

一、根据表结构，判断当分别执行如下5条语句时想要使用索引，分别应该如何建立。

创建表

create table stu_index (
	id int primary key,
	name varchar(20),score double
	);

insert stu_index values(1,'liu',89);

insert stu_index values(2,'sam',78);

insert stu_index values(3,'jim',95);

1.explain select * from stu_index where id=3\G

• InnoDB：
  • 由于主键索引默认创建，所以不需要任何操作；
  • 叶子节点：存储主键和所有的数据；
• MyISAM：
  • 能用到索引就行，由于主键索引默认创建，所以不需要任何操作；
  • 要求只查询一次：index(id,name,score)；
  • 叶子节点：存储建立索引的属性和数据相应的地址；

2、explain select * from stu_index where name=‘liu’\G

• InnoDB：index(name)；（查询两次）
  • 叶子节点：存储建立索引的属性的值和所对应的主键的值；
  • 要求只查询一次：index(name,id,score)；
• MyISAM：index(name)；（查询两次）
  • 叶子节点：存储建立索引的属性和数据相应的地址；
  • 要求只查询一次：index(name,id,score)；

3、explain select * from stu_index where name = ‘liu’ and score = 89;

• InnoDB：index(name,score)；index(score,name)；（查询一次）
  • 叶子节点：存储建立索引的属性的值和所对应的主键的值；
• MyISAM：index(name,score)；index(score,name)；（查询两次）
  • 叶子节点：存储建立索引的属性和数据相应的地址；
  • 要求只查询一次：index(name,score,id)；

4、explain select id from stu_index where name=‘liu’\G

• InnoDB：index(name)；（查询一次）
  • 叶子节点：存储建立索引的属性的值和所对应的主键的值；
• MyISAM：index(name)；（查询两次）
  • 叶子节点：存储建立索引的属性和数据相应的地址；
  • 求只查询一次：index(name,id)；

5、explain select score from stu_index where name=‘liu’\G

• InnoDB/MyISAM：index(name,score)；（查询一次）
  • 原因：两个引擎下的叶子节点都存储建立索引的属性的值；

二、根据表结构，判断当分别执行如下语句时想要使用索引，应该如何建立。

mysql> create table orderlist(
	name varchar(20) not null,
	productid int not null,
	date timestamp
	);

1、explain select * from orderlist where name =‘Tom’ order by date\G

• InnoDB：index(name,data)；对；index(data,name)；错；（查询两次）
• MyISAM：index(name,data)；对；index(data,name)；错；（查询两次）

三、根据以下表结构和建立的索引分别判断驶入下sql语句能否使用到所建立的索引，能用到回答能，不能用到则回答不能。

CREATE TABLE `student_index` (
	`id` int(11) NOT NULL,
	`name` varchar(255) DEFAULT NULL,
	`cid` int(11) DEFAULT NULL,
	`school` char(20) NOT NULL DEFAULT '',
	KEY `name_cid_INX` (`name`,`cid`,`school`)
	) ENGINE=InnoDB DEFAULT CHARSET=utf8;

insert into student_index values
	(1,'weixin',12,'caijing'),
	(2,'weixin',13,'ligong'),
	(3,'weixin',14,'gongye');

1、explain select * from student_index where name = ‘abc’ and cid = 123 and school = ‘ligong’ \G

• 能用到

2、explain select * from student_index where name = ‘abc’ and cid = 123\G

• 能用到

explain select * from student_index where cid = 123 and name = ‘abc’ \G

• 能用到，因为MySQL会根据索引对SQL语句进行优化；该语句会被优化为与上面SQL语句相同的语句；

3、explain select * from student_index where name = ‘abc’\G

• 能用到

4、.explain select * from student_index where name = ‘abc’ and school=‘ligong’\G

• 能用到

5、explain select * from student_index where cid = 123 and school = ‘ligong’\G

• 不能用到