MySQL 索引原理以及查询优化

2）唯一索引

• Primary Key（主键索引）：加速查找 + 约束（值不能为空且具有唯一性）

• Unique（唯一索引）：加速查找 + 约束（具有唯一性）

3）联合索引

• Primary Key(ID,Name)：联合主键索引。

• Unique(ID,Name)：联合唯一索引。

• Index(ID,Name)：联合普通索引。

4）全文索引

• Fulltext：在搜索很长一篇文章时，效果最好。

5）空间索引

• Spatial：几乎不用。

3.索引的两大类型

---

• Hash 类型的索引：查询单条快，范围查询慢。

• B-tree 类型的索引：b+ 数，层数越多，数据量指数级增长。

不同的存储引擎支持的索引类型也不一样：

• NDB：支持事务，支持行级别锁定，不支持 B-tree Full-text Hash 等索引。

• InnoDB：支持事务，支持行级别锁定，支持 B-tree Full-text 等索引，不支持 Hash 索引。

• MyISAM：不支持事务，支持表级别锁定，支持 B-tree Full-test 等索引，不支持 Hash 索引。

• Memory：不支持事务，支持表级别锁定，支持 B-tree Hash 等索引，不支持 Full-test 等索引。

4.创建索引

---

创建表时

mysql> create table A(ID int,Name char(12),Age int,Email varchar(30),primary key(ID));

mysql> show index from A\G

create 在已存在的表上创建索引

• 格式：CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名 [index_type] ON 表名(字段名)

mysql> create index name on A(Name); #创建普通索引

mysql> create index zhangsan on A(Age,Email); #创建普通联合索引

alter table 在已存在的表上创建索引

• 格式：alter table 表名 add [UNIQUE|FULLTEXT|SPATIAL] index 索引名 (字段名)

mysql> alter table A add unique index(Age); #添加唯一索引

mysql> alter table A add unique index(ID,Name); #添加联合唯一索引

5.删除索引

---

格式：drop index 索引名 on 表名

6.测试索引

---

1）准备表

create table test(ID int,Name varchar(20),Email varchar(30));

2）创建存储过程，实现批量插入记录

• 什么是存储过程：存储过程可以理解为 Shell 函数，里边包含多条 SQL 语句。

delimiter $$

create procedure B()

BEGIN

declare A int default 1;

while(A<3000000)do

insert into test values(A,concat(“LiSa”,A),concat(“LiSa”,A,“@163.com”));

set A=A+1;

END while;

END $$

delimiter ;

3）查看存储过程

show create procedure B\G

4）调用存储过程

call B(); #因为数据量较大所以比较慢

5）在没有索引的前提下测试查询速度

select * from test where ID=2888888;

6）加上索引

• 主要是为了搜索条件的字段创建索引，比如：select * from A where ID=2888888 就需要为 ID 加上索引。

mysql> create index idx on test(ID);

• 注意：InnoDB 表的索引会存放于 data/库/表.ibd 文件中，而 MyISAM 表的索引则会有单独的索引文件 data/库/表.MYI