Mysql数据库优化方案

这两天面试被问到了，回答的不是很去全面，小小总结一下

1.sql语句方面的优化

1.select后查询具体字段，避免全表查询

2.用连接查询代替子查询

3.增量查询

4.join连接的表不宜过多，尽量使用inner join而不是left join,right join

5.使用limit来限制查询结果

6.尽量用小表来驱动大表，使用in或者exists关键字，左表小，右表大

7.避免在where字句查询中使用函数,！=，<>运算符，会导致索引失效

8.在使用like模糊查询时，尽量使用右%

9.尽量使用数值类型代替字符串类型（），varchar代替char

10.避免在where字句中使用or作为连接条件，会导致索引失效进行全表扫描，可以用union all

11.使用union all代替union   (union对查询结果有排序和去重逻辑，数据量大时比较耗时)

2.索引优化

1.在创建索引时，只对查询操作频繁的列建立索引

2.要确保索引不会失效

3.字符串较长，可以建立前缀索引，降低索引的体积，减少磁盘IO

4.避免含有NULL值

3.架构优化

1.使用缓存，把数据量大，比较复杂的数据放入缓存系统，减轻数据库压力

2.消息队列削峰，通过使用队列降低mysql压力，不管多少请求进来，先存入队列，再从队列中去消费

3.读写分离（集群，主从复制）

   使用多台服务器，master主节点负责写操作，读的请求平均分到各个slave节点上，实现读写分离，主从复制保证多个节点的数据一致性（binlog是实现主从复制的核心，master将所有写操作记录到binlog,slave节点会有专门的线程读取master节点的binlog,将写操作同步到当前节点）

    若业务越来越多，单张表的数据激增，查询性能下降，读写分离无法解决怎么办？

    分库分表

3.分库分表

     1.垂直分库：在数据库中根据业务不同分成不同的数据库

     2.垂直分表：在单表的基础上把多个字段分成若干小表，根据业务来判断，把常用的字段  分成一个，不常用的分成一个

     3.水平分库分表：把同一个表的数据按照一定规则拆分到不同的数据库

                 水平拆分解决存储瓶颈，垂直拆分减轻并发压力，一般都是组合使用

4.SQL分析优化，慢查询，explain

1.explain关键字查看sql执行计划

     重要参数：  id: 跟sql执行顺序有关，复杂sql会拆分多个子句先后执行，id大的先执行

                         type:对查询语句执行效率进行评级，从慢到快 all<index<range<ref,const

                         key:实际命中的索引

2.慢查询优化

      可以通过慢查询的日志分析定位到慢查询的sql语句对应的表（默认关闭慢查询日志）

       slow_query_log_life慢查询日志文件位置

     select @@slaw_query_log;查询慢日志是否开启（默认0不开启，1开启）

      set @@global.slow_query_log=1;开启慢查询日志

        1.如果是sql本身比较慢，就去优化SQL语句

        2.如果是单表数据量过大，就进行分库分表

5.连接配置优化

1.增加可用连接数，修改环境变量max_connections，mysql默认最大连接数151

      (mysql5.5,5.6,5.7默认最大连接数都是151，5.0版本最大连接数为100)

2.及时释放不活动的连接，默认客服端超时为8小时，可以调小一点（不推荐）