62.mysql优化之数据更新优化

---

highlight: arduino-light

字段过长如何优化

比如现在有一个article表,content字段非常大。

select content from article where content='很长的一段字符串';

如果对content字段加索引,也会很慢。

此时可以增加一个字段contentcrc,对content字段使用哈希生成索引存在contentarticle。

假设针对这个很长的一段字符串生成的索引是7x58c23x55。

select content from article where content='很长的一段字符串' and content_crc='7x58c23x55';

这样即使有多个哈希值相同的索引值,查找仍然很快！这样的缺陷是需要维护哈希值。

优化limit分页

在进行分页时，一般通过创建覆盖索引，能够比较好的提高性能。一个非常常见，而又非常头疼的分页场景就是 "limit 1000000,10" ，此时MySQL需要搜索出前1000010 条记录后，仅仅需要返回第 1000001 到 1000010 条记录，前1000000 记录会被抛弃，查询代价非常大。

当点击比较靠后的页码时，就会出现这个问题，查询效率非常慢。

优化SQL：

select * from operation_log limit 3000000 , 10;

将上述SQL优化为 :

select * from operation_log t , (select id from operation_log order by id limit 3000000,10) b where t.id = b.id ;

<select id="selectListByCondition" parameterType="map" resultType="operationLog"> select   id ,   operate_class as operateClass ,   operate_method as operateMethod,   return_class as returnClass,   operate_user as operateUser,   operate_time as operateTime,   param_and_value as paramAndValue,   cost_time as costTime,   return_value as returnValue from operation_log t,     (select id from operation_log  <where>    <include refid="oplog_where"/>  </where> order by id limit #{start},#{rows}) b where t.id = b.id   </select>

优先用Inner join，如果是left join，左边表尽量小

• Inner join 内连接，在两张表进行连接查询时，只保留两张表中完全匹配的结果集。
• left join 在两张表进行连接查询时，会返回左表所有的行。
• right join 在两张表进行连接查询时，会返回右表所有的行。

在where及orderBy的列建索引,避免全表扫描

连续的数值，能用 between 就不要用 in

对于单表查询来说，这个filtered列的值没什么意义，我们更关注在连接查询中驱动表对应的执行计划记录的filtered值。

exist & in的合理利用

外表小,内表大,EXISTS

外表大,内表小,IN

优化group by 语句:

优化:不需要排序:order by null

子查询临时表太慢

MySQL从4.1版本开始支持子查询，使用子查询进行SELECT语句嵌套查询，可以一次完成很多逻辑上需要多个步骤才能完成的SQL操作。

子查询虽然很灵活，但是执行效率并不高。

执行子查询时，MYSQL需要创建临时表，查询完毕后再删除这些临时表，所以，子查询的速度会受到一定的影响。

explain select * from t_user where id in (select user_id from user_role );

优化：

可以使用连接查询(JOIN)代替子查询，连接查询时不需要建立临时表，其速度比子查询快。

优化:使用join代替子查询

explain select * from t_user u , user_role ur where u.id = ur.user_id;

连接(Join)查询之所以更有效率一些 ，是因为MySQL不需要在内存中创建临时表来完成。

这个逻辑上需要两个步骤的查询工作。

优化深度分页查询

select *  from tb_item t  limit 2000000,10

一般分页查询时，通过创建覆盖索引能够比较好地提高性能。一个常见又非常头疼的问题就是 limit 2000000,10 ，此时需要MySQL排序前2000010 记录，仅仅返回2000000 - 2000010 的记录，其他记录丢弃，查询排序的代价非常大 。

优化思路一

在索引上完成排序分页操作，最后根据主键关联回原表查询所需要的其他列内容

select * from tb_item t ,(select id from tb_item order by id limit 2000000,10) as a where a.id = t.id

优化思路二

该方案适用于主键自增的表，可以把Limit 查询转换成某个位置的查询 。

select *  from tb_item t  where id> 2000000 limit 10

强制使用索引&忽略索引

引导使用索引:USE INDEX

SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。

在查询语句中表名的后面，添加 use index 来提供希望MySQL去参考的索引列表，就可以让MySQL不再考虑其他可用的索引。

SELECT * FROM single_table s1 USE INDEX ( idx_key1 ) WHERE s1.common_field = 'a';

强制使用索引:FORCE INDEX

SELECT * FROM single_table s1 FORCE INDEX ( idx_key1 ) WHERE s1.common_field = 'a';

强制忽略索引:IGNORE INDEX

SELECT * FROM single_table s1 IGNORE INDEX ( idx_key1 ) WHERE s1.common_field = 'a';

COUNT 优化

1. 数据库计数
2. Redis缓存

count(字段)

阿里巴巴开发手册明确规定：不能使用count(1)或者count(列名)代替count(*)。 count(*)是sql92统计行数的标准写法。