【MySQL】索引相关 𱌑
时间:2022-03-15 15:34
原文:
目录
普通索引
作用:加速查找
- # 创建表 + 普通索引
- create table userinfo(
- nid int not null auto_increment primary key,
- name varchar(20) not null,
- index ix_name(name) # 定义索引
- );
-
- # 添加普通索引
- create index 索引名 on 表名(列名);
-
- # 删除索引
- drop index 索引名 on 表名;
唯一索引
作用:加速查找和唯一约束(包含null)
- # 创建表 + 唯一索引
- create table userinfo(
- nid int not null auto_increment primary key,
- name varchar(20) not null,
- unique index ix_name(name) # 添加索引
- );
-
- # 添加唯一索引
- create unique index 索引名 on 表名(列名);
-
- # 删除索引
- drop index 索引名 on 表名;
主键索引
作用:加速查找和唯一约束(不包含null)
- # 添加表 + 主键索引
- # 方法1:
- create table userinfo(
- nid int not null auto_increment primary key, # 添加主键索引
- name varchar(32) not null
- );
-
- # 方法2:
- create table userinfo(
- nid int not null auto_increment,
- name varchar(32) not null,
- primary key(nid) # 添加主键索引
- );
-
- # 添加主键索引
- alter table 表名 add primary key(列名);
-
- # 删除主键索引
- alter table 表名 modify 列名 int, drop primary key;
组合索引
组合索引是将n个列组合成一个索引
应用场景:频繁的同时使用n列来进行查询(如:where name = ‘zyk‘ and sex = ‘boy‘)
- # 添加联合普通索引
- create index 索引名 on 表名(列名1, 列名2);
正确使用索引的情况
数据库中添加索引后确实会让查询速度起飞,但前提必须是正确的使用索引来查询,如果以错误的方式使用,则即使建立索引也不会奏效.
使用索引,我们必须知道:
(1) 创建索引
(2) 命中索引
(3) 正确使用索引
——————————————
准备100万条数据:
- #1. 准备表
- create table userinfo(
- id int,
- name varchar(20),
- gender char(6),
- email varchar(50)
- );
-
-
- # 创建存储过程,实现批量插入记录
- delimiter $$ # 声明存储过程的结束符号为$$
- create procedure auto_insert1() # 定义存储过程名为:auto_insert1()
- begin
- declare i int default 1; # 定义i为1
- while (i<1000000) do
- insert into userinfo
- values(i, concat(‘zyk‘, i), ‘male‘, concat(‘zyk‘, i, ‘@oldboy‘));
- set i = i + 1;
- end while;
- end $$
- delimiter ; 重新声明分号为结束符号
-
-
- # 查看存储过程
- show create procedure auto_insert\G
-
- # 调用存储过程
- call auto_insert1();
测试:
什么是最左前缀:
如果使用组合索引如上,name和email组合索引之后,查询:
(1)name and email # 使用索引
(2)name # 使用索引
(3)email # 不使用索引
对于同时搜索n个条件,组合索引的性能好于多个单列索引.
索引的注意事项
- 不要使用select * # 对性能的损耗太大
应使用 count(1) 或 count(列名) 代替 count(*)后在查资料发现这些现在都没有区别了- 创建表时尽量使用char代替varchar # 查询速度
- 表的字段顺序:固定长度的字段优先
- 经常使用多个条件查询时,应建立组合索引代替多个单列索引
- 尽量使用短索引(create index ix_title on tb(title(16)); 特殊的数据类型,如text类型)
- 使用链接 join 来代替子查询
- 连表时注意条件类型需一致
- 索引散列(重复少)不适用于建索引,例如:性别不适合建索引
执行计划 axplain
explain + 查询SQL:用于显示SQL执行信息参数,根据参考信息可以进行SQL优化.
各字段参数说明: