• 欢迎大家分享资料!前往留言板评论即可!

mysql的索引和优化

合宙 模组资料网 7个月前 (05-15) 82次浏览 已收录 0个评论 扫描二维码
  • 索引
    • 主键索引
      • 加速查询 + 列值唯一 + 不可以为null + 表中只有一个
      • 主键索引是在主键上的索引,设置key的时候,sql会自动创建主键索引。
    • 普通索引
      • 作用:仅加速查询
      • 创建非主键列上字段的索引。
    • 聚合索引
      • 多列组合成一个索引,用于加速查询
      • 一张表有多个索引,也成为复合索引。
    • 唯一索引
      • 作用:加速查询 + 列值唯一 + 可以为null + 表中只有一个
    • 全文索引
索引的语法

查看某张表的索引:SHOW INDEX FROM 表名;

创建普通索引:ALTER TABLE 表名 ADD INDEX 索引名 (加索引的列)

创建聚合索引:ALTER TABLE 表名 ADD INDEX 索引名 (加索引的列1,加索引的列2)

删除某张表的索引:DROP INDEX 索引名 ON 表名;

索引的注意事项和优化

1、应尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描。

解释:

不等于 也是一个很大的范围,和全盘扫描没啥区别。

2、对查询进行优化,应尽量避免全表扫描,首先应考虑在 where 及 order by 涉及的列上建立索引。

3、应尽量避免在 where 子句中对字段进行 null 值判断,否则将导致引擎放弃使用索引而进行全表扫描。

解释:

如果项目有非要用空值的话,可以在字段默认0。

4、尽量避免在 where 子句中使用 or 来连接条件,否则将导致引擎放弃使用索引而进行全表扫描 如:

select id from t where num=10 or num=20

优化为;

select * from t where num=10 union select * from t where num=20;

解释:

在查询条件中使用OR连接多个条件会导致索引失效,这时应该改为两次查询,然后用UNION ALL连接起来。

5、下面的查询也将导致全表扫描:

select id from t where name like "%zheng"

优化为;

select id from t where name like "zheng%"

解释:

%在mysql中作为一个通配符,既然作为通配符,模糊查询时需要一个一个进行匹配 如A,B,C,D,E,…. 匹配完了,才开始zheng这个模糊查询,就像在上海坐地铁一样,准备要做中科路,自己还要一条一条线去找那条线是去中科路的,如果一开始知道13号线通中科路的话,就会快的很多。

6、不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。

7、 最左前缀集合,如:聚合索引 (a,b,c)

正确使用的索引语句

select * from aaa where a = "" and b = "" and c = ""

select * from aaa where a = "" and b = ""

select * from aaa where a = ""

不正确使用索引

select * from aaa where c = "" and b = ""

select * from aaa where c = "" and a = ""

看过以上sql可以看出来,创建了三个字段的索引等于创建了a、a,b、a,b,c三个索引

8、短索引

问题

以前遇到一个问题,在数据量大的时候,因有一个字段是str类型,而我查询的时候是用int类型查询

select * from aaa where a = 123

查询一条数据在10s以上,明明有索引为什么那么慢,然后改成

select * from aaa where a = "123"

查询一条数据在1s之内,查阅资料才知道:

当MySQL中字段为int类型时,搜索条件where num=”111″ 与where num=111都可以使用该字段的索引。

当MySQL中字段为str类型时,搜索条件where num=”111″ 可以使用索引,where num=111 不可以使用索引。

总结:

如果=和in一起使用的话,mysql会帮助优化成最佳索引

尽量不要新建索引,适当扩展索引,索引也不是越多越好,对于更新,插入有所影响

explain命令,准备运行一个sql时,先用explain命令看下sql的执行计划

不能再where的左边进行函数或者表达式运算

避免全盘查询


转载请注明原文链接:mysql的索引和优化
喜欢 (0)
发表我的评论
取消评论

表情 贴图 加粗 删除线 居中 斜体

Hi,您需要填写昵称和邮箱!

  • 昵称 (必填)
  • 邮箱 (必填)
  • 网址