mysql数据库
mysql的SQL优化
SQL优化主要有三点:表字段设计、索引的创建、正确SQL编写防止索引失效以及查看执行计划
表字段设计
- 字段的类型选择要合适。按照整型 > 日期类型 > char、枚举类型 > varchar > text、blob。
- 字段选择长度要合适,不要慷慨。
- 字段尽量不要使用NULL值,这样查询数据时使用
== null
或者使用!= null
数据会查询不到,必须使用is null
或者is not null
才可以,而且NULL也不利于索引的维护。 - 使用日期类型时要根据需求去使用。
dateTime类型占用8个字节存储,和时区无关。date类型只占用3个字节进行存储年月,可用做生日以及相关日期的存储。timeStamp类型使用4个字节存储,但是只能使用到2038-1-19号
- char和varchar,对于char来说是固定长度,varchar是变长。相对来说char性能更高。如果字段固定长度可以使用的话推荐使用char,反之使用varchar。
索引的设计
- 用于表于表之间链接的字段创建索引。
- 用于where条件后面的字段创建索引。
- 外键必须创建索引。
- 对于text类型或者比较长的varchar类型,建议创建前缀索引。
- 对于频繁更新的字段,不要创建索引。
- 对于字段意义不大的字段不需要创建索引,比如说性别。
- 索引并不是越多越好,能扩展索引尽量对索引进行扩展。
- 对于散列性不好的列要放到散列性较好的列的前面。
- 表数据量较小,不需要创建索引。
防止索引失效
- 查询条件的值必须和对应列的类型一致,防止发生隐式类型转换。
(varchar --> int)
- 查询条件中不要使用函数。
- 查询条件总遵循最左原则。
- 查询条件不要使用表达式计算。
- 不要对索引进行左模糊查询。
- 查询条件中带有or的话,or左右两边的列都有索引。
- 查询条件中范围查询后的条件索引不生效。
- 查询条件中null值判断,有可能导致放弃走索引而进行全表扫描。
常见的书写sql的规则
- 尽量用小表驱动大表。
查看执行计划
查看索引计划时使用explain命令。例:explain select * from t1;
查看索引计划时需要关注的几列: type、key、key_len、rows、filtered、extra
-
type
性能从左往右依次降低。
system > const >eq_ref > ref > range > index > all
system
:表中只存有一条数据。const
: 通过索引一次命中,匹配一行数据。eq_ref
: 使用唯一索引扫描。ref
: 非唯一索引扫描。range
:范围查询。index
:遍历索引树。all
:全表查询。
尽量避免index和all。
-
key:表示查询时真正使用到的索引。
-
key_len: 表示优化器使用索引的字节数,通过此字段可以评估组合索引是否完全被使用。
-
rows:表示执行sql所扫描的行数。
-
filtered:表示返回结果占扫描到行的百分比,值越大说明查询的越准确。
-
extra:扩展域
using filesort
:表示mysql对结果集进行外部排序,不能通过索引顺序达到排序效果,一半有using filesort都建议优化掉,消耗cpu资源,延时大。using index
:覆盖索引扫描,表示查询索引树就可以,不需要回表。性能不错。using temporary
:查询有使用临时表,一般出现于排序、分组和多表join的情况,查询效率不高,建议优化。using where
:sql使用了where过滤,效率较高。