字段过长如何优化

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

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

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

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

假设针对这个很长的一段字符串生成的索引是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 优化

数据库计数
Redis缓存

count(字段)<count(主键id)<count(1)≈count(*)，所以我建议你，尽量使用count(*)。

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

64.mysql优化之数据查询优化