首页 > 技术文章 > mysql索引的建立和查询语句优化

zezhou 2019-09-16 20:24 原文

 

首先表内容展示:

# 300W条的模拟数据

 

# 并且没有建立任何索引

 

 

 

 

不建立索引的查询时间:

以id查询的(4秒多):

 

以email查询的(4秒多):

 

 

 

建立主键索引后:

alter table s1 modify id int primary key auto_increment;

 

 

建立后再查询:

# 以id查不到1秒,所以索引要建立正确且查询条件得适合

 

建立email字段的普通索引:

create index email_index on s1(email);

 

建立后查询:

 

# 瞬间快了非常多

 

 

mysql的索引介绍:

 

建立索引为了提高查找的效率,建立正确索引,以及sql查询语句正确,则可以提高用户体验。

 

 

mysql的索引:

primary key  # 唯一 + 非空 + 聚集索引

unique key   # 唯一 + 索引,辅助索引

index key    # 普通索引,能够加速查询,辅助索引

foreign key  # 本身没有索引的,但是它关联的外表中的字段是unique索引

 

注:

(1) 主键作为条件的查询如果能够让索引生效那么效率总是更高

(2) primary key 和unique 标识的字段不需要再添加索引,直接就可以利用索引加速查询

(3) 能用unique的时候尽量不用index,因为普通索引可以是建立再稍有重复的字段,但不可以重复过多的字段(大概重复10%以上不能建立普通索引了)

 

关于索引的建立和删除:

# 最好在建表时候考虑好

添加主键:

alter table tablename add primary key(列名);

删除主键:

alter table tablename drop primary key;

 添加普通索引:

create index 索引名 on 表名(字段名);

删除普通索引:

drop index 索引名 on 表名;

添加unique:

alter table 表名 add unique (字段);

删除unique:

alter table 表名 drop index字段;

 

 

mysql的语句优化:

(1) 查询条件字段一定是建立了索引的,如果查询的条件字段没有创建索引,那么无效

(2) 查询条件不能是一个范围条件,随着范围值的增大,索引发挥的作用越小

如:select * from s1 where id > 10000 and email='eva99999@oldboy';  # 这个语句不太好

(3) 使用like模糊查询,使用abc%形式可以命中索引,但是%abc%不能命中索引

如:

 

(4) 索引列不要在条件中参与计算,也不要使用函数

(5) 多个索引条件以and相连时候,优先选择区分度高的索引来进行查询

如:select * from s1 where id=999 and username='zezhou';

 

 

查看mysql语句的执行计划:

 # 可以看到该sql语句命中索引的情况

格式:explain sql语句;

 

没建立索引:

 

建立了索引后:

 

查看是否命中索引的例子:

 

 

推荐阅读