mysql数据库中索引优化的一次刨析过程
首先想到的就是索引这块有没有缺失或者,索引的可用性会不会失效,下面简单简述索引的注意项;
查询语句的优化及注意事项:
- 对查询进行优化,要尽量避免全表扫描,首先应考虑where及order by 涉及的列上建立索引;
- 应尽量避免在where子句中对字段进行null值判断,尽可能的使用not null值填充数据库;
- 应尽量避免在where子句中使用!=或<>操作符;
- 应尽量避免在where子句中使用or来连接条件;
- 应尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致)),减少select**
以上几点稍有不慎都会造成全表扫描的悲剧
索引的分析和优化:
举例:
如果系统中发现慢 SQL 或者性能影响业务的 sql,可以通过 EXPLAIN 来判断 SQL 的执行计划。
查看执行计划会有如下信息:
关于 key_len 长度计算公式:
varchr(10) 变长字段且允许 NULL : 10_(Character Set:utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段)varchr(10) 变长字段且不允许 NULL : 10_(Character Set:utf8=3,gbk=2,latin1=1)+2(变长字段)char(10) 固定字段且允许 NULL : 10_(Character Set:utf8=3,gbk=2,latin1=1)+1(NULL)char(10) 固定字段且不允许 NULL : 10_(Character Set:utf8=3,gbk=2,latin1=1)
也就是说索引 key_len 长度过大,也会影响 SQL 性能。所以为什么也不能默认 null,会占用字节,索引长度哟。
下面这条语句就是通过开启mysql数据库慢查,查询出的一条慢sql,使用explain解析,查看其执行计划,发现未添加有效索引。
- key 是指用到的索引。
- key_len 是索引的长度,在不影响查询精度的情况下,值越小越好。
- ref 是指索引的那一列被使用了。一般会是个常数。
- rows 是指有多少行。
- extra 是指额外的信息。也是比较重要的。如果值为 distinct ,说明mysql 找到了域行联合匹配的行,就不再查找了。
- 如果值为 not exits : mysql优化了 left join ,一旦找到了 left join 匹配的行,便不再进行搜索了。
- 如果值为 rang checked for each : 没有找到理想的索引。
- 如果为 using filesort ,则需要改进sql了。这说明 mysql执行 需要 文件排序。这是比较影响效率的。
- 如果为 using temporary , 这是使用了 临时表。 这种情况也比较影响效率,sql需要改进。或者从应用层进行改进。
- 如果为 where used 说明使用了where语句。如果 type为 all 或者 index ,一般会出现这样的结果。这样的问题,一般是查询需要改进。
- 在一般稍大的系统中,基本尽可能的减少 join ,子查询 等等。mysql就使用最简单的查询,这样效率最高。至于 join 等,可以放在应用层去解决。