一、MySQL 索引简介
1、 MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。
打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。
2、索引分单列索引和组合索引。
- 单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。
- 组合索引，即一个索引包含多个列。
3、索引的详细分类：
- 普通索引index :加速查找
- 唯一索引
- 主键索引：primary key ：加速查找+约束（不为空且唯一）
- 唯一索引：unique：加速查找+约束 （唯一）
- 联合索引
- primary key(id,name):联合主键索引
- unique(id,name):联合唯一索引
- index(id,name):联合普通索引
- 全文索引fulltext :用于搜索很长一篇文章的时候，效果最好。
- 空间索引spatial :了解就好，几乎不用
4、创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。
实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。
5、索引的两大类型hash与btree
- 我们可以在创建上述索引的时候，为其指定索引类型，分两类
- hash类型的索引：查询单条快，范围查询慢
- btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）
2、不同的存储引擎支持的索引类型也不一样
- InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
- MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
- Memory 不支持事务，支持表级别锁定，支持 B-tree、Hash 等索引，不支持 Full-text 索引；
- NDB 支持事务，支持行级别锁定，支持 Hash 索引，不支持 B-tree、Full-text 等索引；
- Archive 不支持事务，支持表级别锁定，不支持 B-tree、Hash、Full-text 等索引；
6、滥用索引的缺点：
第一，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。
创建UNIQUE | FULLTEXT | SPATIAL 一样的方法
方法一：创建表时创建索引
CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
[UNIQUE | FULLTEXT | SPATIAL ] INDEX | KEY [indexName] (username(length))
);
方法二：CREATE在已存在的表上创建索引
CREATE [UNIQUE | FULLTEXT | SPATIAL | INDEX ] indexName ON mytable(username(length));
方式三：修改表结构(添加索引)
ALTER table tableName ADD [UNIQUE | FULLTEXT | SPATIAL | INDEX ] indexName(columnName)
删除索引的语法
DROP INDEX [indexName] ON mytable;
三、实测索引的功效
1. 前期准备工作
-
创建一个名为text的数据库：
create database text charset utf8;
-
创建一张名为text的数据表
create table text(id int,name varchar(20))
-
通过创建存储过程，实现批量插入记录(大约需要半小时时间)
delimiter $$ #声明存储过程的结束符号为$$
create procedure insertinfo()
BEGIN
declare i int default 1;
while(i<1000000)do
insert into text values(i,concat('wangjifei',i));
set i=i+1;
end while;
END $$
delimiter ; #重新声明分号为结束符号为;
-
查看存储过程
show create procedure insertinfo\G
-
调用存储过程
call insertinfo();
2、在没有索引的前提下测试查询速度
mysql> select * from text where id = 1234;
+------+---------------+
| id | name |
+------+---------------+
| 1234 | wangjifei1234 |
+------+---------------+
1 row in set (0.39 sec)
mysql> select * from text where name = 'wangjifei12345';
+-------+----------------+
| id | name |
+-------+----------------+
| 12345 | wangjifei12345 |
+-------+----------------+
1 row in set (0.53 sec)
3、加上索引
//1. 一定是为搜索条件的字段创建索引，比如select * from t1 where age > 5;就需要为age加上索引
//2. 在表中已经有大量数据的情况下，建索引会很慢，且占用硬盘空间，插入删除更新都很慢，只有查询快
//比如create index myname on text(name);会扫描表中所有的数据，然后以name为数据项，
//创建索引结构，存放于硬盘的表中。建完以后，再查询就会很快了
//给name加上普通索引
mysql> create index myname on text(name);
Query OK, 0 rows affected (18.31 sec)
Records: 0 Duplicates: 0 Warnings: 0
//给id加上唯一索引
mysql> create unique index myid on text(id);
Query OK, 0 rows affected (10.83 sec)
Records: 0 Duplicates: 0 Warnings: 0
效果立竿见影,查询速度翻了几十倍
mysql> select * from text where id = 1234;
+------+---------------+
| id | name |
+------+---------------+
| 1234 | wangjifei1234 |
+------+---------------+
1 row in set (0.00 sec)
mysql> select * from text where name = 'wangjifei12345';
+-------+----------------+
| id | name |
+-------+----------------+
| 12345 | wangjifei12345 |
+-------+----------------+
1 row in set (0.01 sec)
mysql> select * from text where name = 'wangjifei823458';
+--------+-----------------+
| id | name |
+--------+-----------------+
| 823458 | wangjifei823458 |
+--------+-----------------+
1 row in set (0.00 sec)
四、批量添加测试数据的方法：
- 通过存储过程批量创建数据
1. 创建存储过程
delimiter $$ #声明存储过程的结束符号为$$
create procedure insertinfo()
BEGIN
declare i int default 1;
while(i<1000000)do
insert into text values(i,concat('wangjifei',i));
set i=i+1;
end while;
END $$
delimiter ; #重新声明分号为结束符号为;
2. 查看存储过程
show create procedure insertinfo\G;
3. 调用存储过程
call insertinfo();
五、正确使用索引
-
覆盖索引
select * from text where name = 'wangjifei882345';
该sql命中了索引，但未覆盖索引。利用name = 'wangjifei882345'到索引的数据结构中定位到该name在硬盘中的位置，或者说再数据表中的位置。但是我们select的字段为*，除了name以外还需要其他字段，这就意味着，我们通过索引结构取到name还不够，还需要利用该name再去找到该name所在行的其他字段值，这是需要时间的，
很明显，如果我们只select name，就减去了这份苦恼，如下select name from text where name = 'wangjifei882345';这条就是覆盖索引了，命中索引，且从索引的数据结构直接就取到了name在硬盘的地址，速度很快
mysql> select name from text where name = 'wangjifei882345';
+-----------------+
| name |
+-----------------+
| wangjifei882345 |
+-----------------+
1 row in set (0.00 sec)
- 联合索引
为了增加效果对比，在创建联合索引前将之前创建的普通索引删除掉了
mysql> select * from text where id = 2435353252 and name = 'wangjifei123333';
Empty set (0.58 sec) //普通查询
mysql> create index idname on text(id,name); //创建联合索引
Query OK, 0 rows affected (26.86 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> select * from text where id = 2435353252 and name = 'wangjifei123333';
Empty set (0.00 sec) // 联合索引查询
