mysql的sql优化

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过滤，效率较高。