MySQL优化技巧

转载请说明源地址：MySQL优化技巧_drsbbbl的博客-CSDN博客

关于数据库的优化也看了那么多，今天总结一下，数据库优化方面的知识:主要分为几个方面

1.关于数据库语句的查询的写法

（1）select * 的问题，避免写，全部使用列来代替，不然触发不了覆盖索引 关于innoDB的索引的看着文章

mysql覆盖索引与回表 - 简书

（2）在where 条件的中的条件索引列不进行计算会导致不触发索引

计算能放后面的放后面

（3）在 where子句中的条件 假如有索引 顺序一定按着组合索引的顺序进行 拼接 比如 组合索引的

顺序为 id name  在条件上因该为 id =? and  name=?  也可以为 name=? and id=？ 也可以是id=?

直接name=?不会触发索引哦（最左匹配的原则）

 where  id name age  索引顺序也是这个  所以id=?  ; id =？ and name=?  ;

id=？ and  name=?  and  age=?    id=? and age=?  ;  这些都可以触发索引。 但是 

name=?  and  age=? 触发不了索引哦~

 

（4）在 order by 子句中的条件，建立索引 这样查询速度会有很大提高

（5）尽量采用连接查询 而非 子查询 因为在大多数的情况下 连接查询的速度是优于子查询的

因为链接查询是更容易控制，不是说一定大于。因为大表的情况下 可能查询多次。子查询需要遍历多次。就显的很慢了。但是链接查询只需一次就可以处理出想要的结果。

子查询：

SELECT * FROM  a   WHERE  x IN (
     
    SELECT  x  FROM b  WHERE y  IN(
        
        SELECT y  FROM location)）

内连接查询。。。

SELECT a.*, b.* FROM x a INNER JOIN y  b ON a.id=b.id

（6）在join连接表的时候 因为 小表  join  大表 

（7） 在 on 的条件 建立索引

（8） 在like 条件中 '%' '_'开头 不会命中索引

 

 

（9）纠正网络上的错误 

in  可以命中索引

索引为 整形数字的

索引为字符的：不会命中索引

not in 情况：

整形数字的情况 ：  不会命中索引

为字符的情况 可以命中索引

（10）  在肯定知道查询结果为一条的时候 可以使用 limit 1 这样 提高查询效率  但是要记得 是肯定查询结果为一条 没有其他的可能，不然可能和需求结果不一致的情况 (我验证了没什么区别 )

（11）在写查询语句 的时候 要保持前后一致  就是 select  ..... from a where b=? and c=?  不要下次写 变成了select  ..... from a where c=? and b=? 幸好 mybatis 提供很好的解决方案 但是在动态sql 中要注意 因为缓存的sql ，不一致没法命中缓存导致查询时间变长了。

第一次查询时间：

第二次时间：

（12）  类型为字符的时候少用<  > 这种符号

为id 的时候可以命中索引 

为 字符不会命中索引 

但是 当 我们数据中有null 字段的时候 会出现不一样的情况  会导致 都会命中索引 但是不推荐这样 因为这样导致 MySQL的执行策略还是不对的

（13）使用limit 进行分页查询,当数据量特别大的时候 超过 100万 想办法进行优化，不然卡死人。 可以进行 id 的判断，分表策略，组合索引， order by 索引

（14）一定要避免 where  name is null 这样的操作 因为 MySQL 对null 要做一些特殊的优化 使表结构更加复杂并不会节省空间。 索引也更加复杂 ， is null 会走索引。 is not 不会走索引

（15） 避免 使用or 关键字 ，可以使用 union all 代替 or 语句

就算你主键索引也不走了。走了全表扫描 ，除非你给or的字段都建立索引才可以。 

 

（16） 避免三表以上的关联查询，除非你有非常必要的理由，并且 三表以上比其他的任何方案，查询的效率都高

2.关于数据库语句的建表的时候

（1） 能确定为非空的字段 加上not null  为空的字段 加上 empty string 因为对于nullMySQL 索引 会更加复杂

（2） 使用tyint 类型 来代替int类型在一些 状态位上

（3） 使用定长的字符串比非定长字符串 性能更加优越

3.关于数据库技术方面

（1） 对于经常查询的数据，使用缓存进行命中策略，但是要记得对于脏读的处理。可以采用@Caheable 注解 

（2） 可以使用像ehcache第三方缓存技术 对mybatis 开启二级缓存策略 ，因为mybatis 的默认的二级缓存 太简陋，当然你也可以自己实现 Cache接口，进行覆写

（3）像excel表大数据的插入的时候，使用批处理语句，excel 插入过程中的 数据库校验 ，对校验数据进行缓存

4.关于配置文件问题

只会两个常用的 缓存区大小和最大连接数参考一下连接

MySQL性能优化之参数配置 - 愤怒的码农 - 博客园

5.关于日志问题

开启慢日志模式，通过查询慢日志 的sql 来定位sql语句并且进行优化，

二进制日志(BLog)可以做数据库的备份问题

学会使用 MySQL的执行计划 查看学会使用mysql执行计划