MySQL索引
往数据库里存储一万条数据
drop table if exists stu;
CREATE TABLE `stu` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`age` int DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
INSERT INTO `stu` VALUES (1, 'BNTang', 23);
INSERT INTO `stu` VALUES (2, '李四', 18);
drop table if exists stu2;
CREATE TABLE `stu2` (
`id` int NOT NULL AUTO_INCREMENT,
`name` varchar(20) DEFAULT NULL,
`age` int DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=3 DEFAULT CHARSET=utf8;
INSERT INTO `stu2` VALUES (1, 'BNTang', 23);
INSERT INTO `stu2` VALUES (2, '李四', 18);
实现方案
- 写一万条 insert into 语句
- 将 insert into 语句封装到存储过程或者函数中
将来怎么使用
- 是配合其它SQL语句使用, 还是单独使用
- 单独使用, 就可以是存储过程
- 配合其它SQL语句使用, 那就要使用自定义函数
create procedure add_stus(num int)
begin
declare currentId int default 0;
declare currentAge int default 0;
declare currentName varchar(255) default '';
while currentId < num do
set currentId = currentId + 1;
set currentAge = floor(rand() * 30);
set currentName = concat('it', currentAge);
insert into stu values(currentId,currentName,currentAge);
end while;
end;
call add_stus(10000);# 48.428s
注意点
- 以上封装存在的问题, 默认情况下每生成一条插入语句, 就会立即执行这条插入的语句
- 所以整个过程我们生成了一万条插入语句, 我们解析了一万条插入的语句, 我们编译了一万条插入的语句, 我们执行了一万条插入的语句
- 所以整个过程就比较耗时
create procedure add_stus2(num int)
begin
declare currentId int default 0;
declare currentAge int default 0;
declare currentName varchar(255) default '';
set autocommit = 0;
while currentId < num do
set currentId = currentId + 1;
set currentAge = floor(rand() * 30);
set currentName = concat('it', currentAge);
insert into stu values(currentId,currentName,currentAge);
end while;
commit;
end;
call add_stus2(10000);# 1.713s
🐤注意点
- 只要在循环前面加上 set autocommit = 0, 在循环后面加上 commit;
- 那么就不会生成一条插入语句就执行一条插入语句了
- 会等到所有的插入语句都生成之后, 再统一的解析, 统一的编译, 统一的执行
create procedure add_stus3(num int)
begin
set @currentId = 0;
set @currentAge = 0;
set @currentName = '';
prepare stmt from 'insert into stu values(?,?,?);';
while @currentId < num do
set @currentId = @currentId + 1;
set @currentAge = floor(rand() * 30);
set @currentName = concat('it', @currentAge);
execute stmt using @currentId, @currentName, @currentAge;
end while;
end;
call add_stus3(10000);# 2.048s
create procedure add_stus4(num int)
begin
set @currentId = 0;
set @currentAge = 0;
set @currentName = '';
prepare stmt from 'insert into stu values(?,?,?);';
set autocommit = 0;
while @currentId < num do
set @currentId = @currentId + 1;
set @currentAge = floor(rand() * 30);
set @currentName = concat('it', @currentAge);
execute stmt using @currentId, @currentName, @currentAge;
end while;
commit;
end;
索引
- 索引就相当于字典中的目录(拼音/偏旁部首手)
- 有了目录我们就能通过目录快速的找到想要的结果
- 但是如果没有目录(拼音/偏旁部首手), 没有索引
- 那么如果想要查找某条数据就必须从前往后一条一条的查找
- 所以索引就是用于帮助我们提升数据的查询速度的
索引的优缺点和使用原则
🐸优点
- 大大加快数据的查询速度
- 没有任何限制, 所有MySql字段都可以用作索引
🦄缺点
- 索引是真实存在的会占空间, 会增加数据库体积
- 如果对作为索引的字段进行增删修操作, 系统需要花费时间去更新维护索引
🐤原则
- 对经常用于查询的字段应该创建索引(作为where条件字段、作为 group by 分组的字段, 作为 order by 排序的字段)
- 对于主键和外键系统会自动创建索引, 无需我们手动创建
- 对于数据量小的表不需要刻意使用索引
索引分类
- 单值索引: 将某个字段的值作为索引
- 复合索引: 将多个字段的值作为索引
- 唯一索引(唯一键): 索引列中的值必须是唯一的，但是允许为空值
- 主键索引: 是一种特殊的唯一索引，不允许有空值
查看当前查询是否使用索引
查询没有索引的表
SELECT * FROM stu WHERE id= 999999;# 0.695s
查询有索引的表
SELECT * FROM stu2 WHERE id=999999;# 0.008s
如何查看当前的查询语句有没有用到索引
EXPLAIN 查询语句;
- 如果返回的结果集中的 key 有值, 那么就表示当前的查询语句中用到了索引
- 如果返回的结果集中的 key 没有值, 那么就表示当前的查询语句中没有用到索引
添加索引
- 给表设置主键, 只要设置了主键, 那么系统就会自动创建对应的索引
- 给表设置外键, 只要设置了外键, 那么系统就会自动创建对应的索引
- 给表设置唯一键, 只要设置了某一个字段的取值是唯一的, 也会自动创建对应的索引
创建表的时候指定给哪个字段添加索引
create table test1(
id int,
name varchar(20),
index idx_name(id)# 创建索引
);
创建好表之后再给指定字段添加索引
create table test2(
id int,
name varchar(20),
);
create index idx_name on test2(id);# 创建索引
create table test3(
id int,
name varchar(20),
);
alter table test3 add index idx_name(id);
删除索引
drop index 索引名称 on 表名;
索引算法
- 索引算法决定了如何创建索引
- 索引算法决定了如何查找索引对应的数据
传统查找
- 1、2、3、4、5
BTree查找
4
|-------------|
2 6
|----|----| |----|----|
1 3 5 7
BTree索引
- BTree 索引是基于平衡多叉排序树实现的, 能够缩短查找的次数
Hahs索引
- 哈希索引是基于哈希表实现的, 只能用于 memory 存储引擎, 可以一次性定位到指定数据
- https://dev.mysql.com/doc/refman/8.0/en/index-btree-hash.html
