SQL优化

Mysql可以从四个层面进行优化：

1.硬件和操作系统的优化

从硬件层面来讲，影响Mysql性能的原因主要有: cpu ,可用内存大小 ,磁盘读写速度,网络带宽

从操作系统层面来讲，应用文件句柄数，操作系统的网络配置，都会影响到mysql 的性能,这部分的优化一般是有DBA 或者 运维工程师来完成,在硬件资源的优化中，我们重点应该关注服务本身所承载的体量，然后提出合理的指标要求，避免出现资源浪费。
2.架构设计层面的优化

Mysql是一个硬盘IO访问非常频繁的关系型数据库，在高并发和高性能的场景中，mysql数据库必然会承受巨大的并发压力，此时我们优化的方式主要分为这几个部分：

2.1 搭建Mysql主从集群，单个Mysql服务容易导致单点故障，一旦服务器宕机将会导致依赖Mysql的应用全部复发响应，主从集群或者主主集群都可以保证服务的高可用性。

2.2 进行读写分离的设计，在读多写少的场景中，通过读写分离的方案可以避免读写冲突，导致磁性能问题

2.3 引入分库分表的机制，通过分库可以降低单个服务器节省的一个IO压力。通过分表的方式可以降低单表的数据量，从而去提成sql的查询效率。

2.4 针对热点数据可以引入更为高效的分布式数据库，比如Redis,MongoDB等，他们可以很好的缓解Mysql的访问压力同时还能提升数据的检索性能
3.Mysql程序配置的优化

3.1 对于Mysql数据库本身的优化一般可以通过配置文件 “my.cnf” 来完成。例如mysql5.7版本中默认最大连接数的151个，这个值可以再my.cnf文件中去修改

3.2 binlog日志默认是不开启的我们也可以在这个文件中去修改开启

3.3 缓存池BufferPool默认大小配置

这些配置一般都是用户的安装环境以及使用的场景有关系，因此这些配置官方只会提供一些默认的配置，具体的情况还得使用者去根据实际情况去修改。

关于配置项的修改我们应该关注两个层面，第一个是配置的作用域，他可以分为会话级别和全局范围。第二个是是否支持热加载，因此针对这两个点我们需要注意的是全局参数的设定对于已经存在的会话是无法生效的。会话参数的设定会随着会话的销毁而失效。第三个是全局类的统一配置建议配置在默认的配置文件中，否则重启服务会导致配置失效的问题
4.Sql执行优化

4.1.慢sql的定位和排查，我们可以通过慢查询日志和慢查询日志工具分析得到有问题的sql列表

4.2.执行计划分析，针对慢的sql可以使用关键字explain来去查看当前sql的执行计划，可以重点关注type，key，rows，filterd 等字段从而定位SQL执行慢的根本原因，再去放矢进行优化

4.3.使用show profile工具 他是mysql 提供的可以用来分析当前会话中SQL语句资源消耗情况的工具 可以用于SQL调优的测量，在当前会话中默认情况下show profile是关闭状态，打开之后会保存最近15次的运行结果，针对运行慢的SQL通过profile工具进行详细分析可以得到SQL执行的过程中所有资源的开销情况，比如 IO开销，CPU开销，内存开销等等
5.索引优化

5.1  尽量避免在字段开头模糊查询，会导致数据库引擎放弃索引进行全表扫描

5.2 .尽量避免使用in和not in           in相当于多个or的叠加
    优化方式：如果是连续数值，可以用between代替，
    如果是不连续子查询：可以用exists     
    in 只能返回一个字段，  exists可以返回多个字段，子表大用exists，小用in ，not in 和not exists      选择not exists

5.3  避免使用or会导致数据库引擎放弃索引进行全表扫描        可以用union代替

5.4  尽量避免进行null值判断，          可以给字段添加默认值0  对0进行判断

5.5  避免在where条件中等号的左侧进行表达式函数操作

5.6  .数据量大时，避免使用where1=1的条件，通常为了方便拼装查询条件，用代码拼装sql是进行判断，没where就去掉where，有where就加and

5.7  不要用<>    !=      改有查询条件中其他索引字段代替

5.8  where条件仅包含复合索引非前置列

5.9  隐式转换会导致不使用索引

5.10  order by条件要和where中条件一致，否则order by不会利用索引进行排序

5.11  正确使用hint优化语句，  use index加在查询语句的表名后面  指定索引。ignone index  加在查询语句的表名后面 忽略一个或多个索引

6.select 其他优化

6.1.  避免出现select * ,聚集索引和辅助索引

6.2  避免出现不确定结果的函数，  很容易导致主库从库数据不一致，sql语句无法利用query cache

6.3  多表查询是mysql小表在前   oracle相反

6.4  使用表别名，       减少解析时间并减少列名歧义引起的语法错误

6.5  用where替换having。having只会在检索出所有记录之后才对结果集进行过滤  where在聚合前刷选记录    having 一般用于聚合函数

6.6  mysql采用从左往右 自上而下，解析where字句，过滤条件多的往前放
7.增删改查语句优化

7.1  大批量插入语句        连接少， 解析少，网络传输少

7.2  适当使用commit释放事务占用的资源  

    undo log（回滚日志）
    redo log（重做日志）  innoDB层    
    binlog（归档日志）    Mysql server层

7.3  避免重复查询更新的数据   @now：=now（）

7.4  默认写入优先级高               low_priority   低优先级  insert  into之间  high_priority  高优先级    inserthigh_ priority  
8.查询条件优化

8.1  对于复杂的查询，可以使用中间临时表暂存数据

8.2  优化group by 语句。group by 会默认对分组字段进行排序，后面加用order by null  可以去掉

8.3  优化join语句

8.4  union  会去重，union all 不会去重

8.5  拆分复杂sql为多个小sql 避免大事务

8.6  使用truncate  （不会rollback  自增值归零）代替delete

8.7  使用合理分页方式提高分页效率 。如果中间数据量过大先分页查询id  在利用id，关联本表id查出所有字段
9.建表优化

9.1  在表中建立索引，优先考虑，where  orderby 后的字段

9.2  尽量使用数字型字段   例如性别

9.3  查询执行顺序：

from   -  on  - join  -  where - group by  - having  -  select  - distinct  -  order by - limit