MySQL 索引原理以及查询优化
一、索引简介
1.索引原理
2.磁盘 IO 与预读
3.索引的数据结构
二、MySQL 索引管理
1.功能
2.MySQL 的索引分类
3.创建索引
4.删除索引
5.验证索引
一、索引简介
- 索引：索引是对数据库中一列或多列的值进行排序的一种数据结构。
- 索引的作用：能够加快客户端的查询速度。
1.索引原理
通过不断的缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。
2.磁盘 IO 与预读
当一次 IO 时，不仅会把当前磁盘地址的数据读取到内存缓冲区内，还会把相邻的数据也读取到内存缓冲区内。
3.索引的数据结构
B+树性质：
- 索引字段要尽量的小。
- 索引的最左匹配特性（就是从左往右匹配）
二、MySQL 索引管理
1.功能
- 索引的功能就是加速查找。
- MySQL 中的 Primary Key（主键），Unique，联合唯一都是索引，这些索引除了加速查找以外，还有约束的功能。
2.MySQL 的索引分类
1）普通索引
- Index：加速查找。
2）唯一索引
- Primary Key（主键索引）：加速查找 + 约束（值不能为空且具有唯一性）
- Unique（唯一索引）：加速查找 + 约束（具有唯一性）
3）联合索引
- Primary Key(ID,Name)：联合主键索引。
- Unique(ID,Name)：联合唯一索引。
- Index(ID,Name)：联合普通索引。
4）全文索引
- Fulltext：在搜索很长一篇文章时，效果最好。
5）空间索引
- Spatial：几乎不用。
3.创建索引
创建表时
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); #添加联合唯一索引
4.删除索引
格式：drop index 索引名 on 表名
5.验证索引
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
7）删除存储过程
drop procedure B;
