MySQL复习资料(五)——MySQL-索引

📋导读📋

💝博客主页：红目香薰_CSDN博客-大数据,MySQL,Java领域博主💝

✍本文由在下【红目香薰】原创，首发于CSDN✍

🤗2022年最大愿望：【服务百万技术人】🤗

📋前言📋

本文属于系列文章，分别为：

MySQL复习资料(一)——MySQL环境安装

MySQL复习资料(二)——MySQL-DDL语句

MySQL复习资料(三)——MySQL-DML语句

MySQL复习资料(四)——MySQL-聚合函数

MySQL复习资料(五)——MySQL-索引

MySQL复习资料(六)——MySQL-多表联合查询

MySQL复习资料(七)——MySQL-存储过程

MySQL复习资料(八)——MySQL-事务

MySQL复习资料(九)——MySQL-图形化工具使用

📋正文📋

MySQL复习资料(五)——MySQL-索引

索引：一般的应用系统，读写比例在10:1左右，而且插入操作和一般的更新操作很少出现性能问题，在生产环境中，我们遇到最多的，也是最容易出问题的，还是一些复杂的查询操作，因此对查询语句的优化显然是重中之重。说起加速查询，就不得不提到索引了。

目录

索引分类

创建索引

未添加索引查询效果

索引添加语句

已添加索引查询效果

索引的优缺点

---

索引分类

1.普通索引index :加速查找
2.唯一索引
    主键索引：primary key ：加速查找+约束（不为空且唯一）
    唯一索引：unique：加速查找+约束 （唯一）
3.联合索引
    -primary key:主键索引
    -unique:唯一索引
    -index:普通索引
4.全文索引fulltext :用于搜索很长一篇文章的时候，效果最好。
5.空间索引spatial :了解就好，几乎不用

创建索引

给userName添加一个名字为【userName_index】的普通索引

alter table users add index userName_index (userName) using btree;

索引效率测试：

mysql索引_效率测试(包含测试sql脚本300万条数据)，可用作教学案例。_红目香薰-CSDN博客

内容是针对索引进行完整对照实验的文章，很清晰的理解是否添加索引的查询效率区别。

#1. 准备表
create table test_table(
id int,
name varchar(20),
sex int(2),
introduce varchar(50)
);
 
#2. 创建存储过程，实现批量插入记录
delimiter $$ #声明存储过程的结束符号为$$
create procedure auto_insert1()
BEGIN
    declare i int default 1;
    while(i<3000000)do
        insert into test_table values(i,concat('shuai',i),1,concat('mei',i,'mei'));
        set i=i+1;
    end while;
END$$ #$$结束
delimiter ; #重新声明分号为结束符号
 
#3. 查看存储过程
show create procedure auto_insert1;
 
#4. 调用存储过程
call auto_insert1();

共计添加2999999条数据

未添加索引查询效果

仅仅测试查询1个非主键非索引的值

select * from test_table where introduce='mei2999010mei';

主键查询:

select * from test_table where id =2999007

待增加索引列查询：

select * from test_table where name='shuai2999005';

分页查询测试：

看看分页走的什么查询：

全文查询

测试结束，在没有添加索引的情况差，单个查询都需要1.3s多的时间。效率太低了，一般接口要求200毫秒，这肯定不行的。

索引添加语句

alter table test_table add index name_index (name) using btree;

添加完毕。

已添加索引查询效果

select * from test_table where name='shuai2999010';

55毫秒已经快很多了。 

ALL：全表扫描，应当避免该类型
index：索引全局扫描，index与ALL区别为index类型只遍历索引树
range：检索索引一定范围的行
ref：非唯一性索引扫描，返回匹配某个单独值的所有行
eq_ref：唯一索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见主键或唯一索引扫描
const：表示通过一次索引就找到了结果，常出现于primary key或unique索引
system：system是const类型的特例，当查询的表只有一行的情况下，使用system
NULL：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，是最高的登记

EXPLAIN select * from test_table where name='shuai2999010';

走的是ref，效率要比全文搜索高很多很多了，一般只要能在range内就是比较快了。 

非索引的查询就是全文查询

消耗时间是1.323s，对比有索引的55毫秒，差的就太多了，所以索引的效率还是很高的。 

索引的优缺点

索引最大的好处是提高查询速度，
缺点是更新数据时效率低，因为要同时更新索引
对数据进行频繁查询进建立索引，如果要频繁更改数据不建议使用索引。