Mysql索引优化

/ 技术 / 无站内评论 / 375浏览
segmentfault.com上看到的,看完之后受益良多,实际工作中索引的合理创建也是一门学问,sql优化不好DBA那边还不会给通过~。

存储引擎

InnoDB

MyISAM

InnoDB与MyISAM的比较

InnoDB:支持事物、在线热备份、行锁
MyISAM:支持全文索引、地理空间索引

索引

索引是帮助MySQL高效获取数据的数据结构,所以索引本质上是一种数据结构

索引分类

索引的优点

索引的缺点

创建索引的两种方式

操作索引

以articles的type字段为例

-- 创建索引
CREATE INDEX idx_type ON articles (type);

-- 删除索引 drop index idx_type on articles

-- 查看索引 show index from articles

使用索引的时机

一般情况下,在where或join子句中出现的列需要添加索引。但是,因为MySQL只对<,<=,=,>,>=,between,in,以及某些时候的like才会使用索引(使用like时,以通配符%_查询时,MySQL不会使用索引)

  1. 哪些情况下需要创建索引

    • 主键自动建立唯一索引
    • 频繁作为查询条件的字段应该创建索引
    • 查询中与其他表关联的字段,外键关系建立索引
    • 单键/组合索引的选择问题(在高并发下倾向组合索引)
    • 查询中排序的字段,排序字段通过索引去访问将大大提高排序速度
    • 查询中统计或分组字段
  2. 哪些情况下不需要创建索引

    • 表记录太少,网上有建议2000为界限,2000以下不创建
    • 经常增删改的表(因为每次增删改不仅要操作数据还要操作索引)
    • where条件用不到的字段
    • 数据重复且分布平均的字段,索引的选择性较低,即当前字段不重复的索引值与表中当前字段的记录数比值,值越大越不建议建索引

创建索引技巧

使用索引的注意事项

索引优化

join语句的优化

left join是由左边决定的,左边一定都有,所以右边是我们的关键点,建立索引要建右边的。当然如果索引在左边,可以用右连接。
尽可能减少Join语句中的NestedLoop的循环次数:“永远用小结果集驱动大的结果集”

避免索引失效

  1. 最佳左前缀法则:如果查询中使用了多个索引列,要遵循最左前缀法则,指的是查询从索引的最左前列开始并且不跳过索引中列。
  2. 不在索引列上做任何操作(计算、函数、(自动/手动)类型转换),会导致索引失效而转向全表扫描。
  3. where条件的列=的判断放在比较运算符><等的左边,放在比较运算符右边的索引会失效
    比如:

    select * from user where username="saboran" and age > 18 and mobile = "18862612345"

    其中username、age、mobile都有索引,但是只有username和age的索引会生效,mobile索引用不到

  4. select查询时尽量减少select  操作,用需要的字段代替
  5. 在使用!=或者<>的时候无法使用索引,会导致全表扫描
  6. is null 和 is not null 也无法使用索引
  7. like 以通配符开头,mysql索引会失效变成全表扫描
    所以最好用右边通配符匹配like 'tssk%'
    如果要使用两边通配符匹配,则将like条件放在最后一个
    比如:

    select age from users where a = 3 and b = 4 and c like "%abcd%";

    这样a、b、c都有索引的话,a、b用的上,c用不上

  8. 字符串不加单引号索引会失效
  9. 少用or,用它连接时会索引失效
  10. 避免子查询,使用join

一般性建议

  1. 对于单键索引,尽量选择针对当前查询语句过滤性更好的索引作为查询条件
  2. 在选择组合索引时,当前query中过滤性最好的索引放在where条件的位置越靠前越好
  3. 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的

查询性能优化

Explain

用来分析SQL语句,分析结果中比较重要的字段有:

减少返回的列

慢查询主要是因为访问了过多数据,除了访问过多行之外,也包括访问了过多列。最好不要使用select * 语句,要根据需要选择查询的列

减少查询的行

最好使用limit语句取出想要的那些行,还可以建立索引来减少条件语句的全表扫描

转自:https://segmentfault.com/a/1190000013562810

召唤蕾姆
琼ICP备18000156号

鄂公网安备 42011502000211号