数据库优化(由浅到深)

数据库优化这块，先遵守基本原则

一、表的设计:采用三泛式和反三范式原则，在有些经常查询的表可以适当设置冗余字段，因为这样能减少关联外表查询，可以提高查询效率，根据业务不同设计表的引擎类型也不同，经常查询居多的可以设计成Myisamy不支持事务的引擎Myism引擎表，经常修改居多的可以设计成支持事务的引擎innodb表,根据唯一性很强的字段设定索引，设置索引适用于经常不频繁修改的字段；

二、变多次维护为一次维护，追加数据的时候可以禁用索引，添加完之后启用索引，多条插入语句可以写成一条。

三、sql语句优化，在where和order by的子句里面尽量避免使用null、in、not in、<>、or等关键字，前置模糊查询也会让索引失效，它们会让表自动放弃索引查询变成全文扫描，这样大大降低了查询效率；

四、分表，垂直分表和水平分表，水平分布常用的有三种方式，时间分表、区间分表、hash取余分表，时间分表多用于不经常查询以前记录，比如微博只展示三天内动态，区间分表用于那种累积形式的数据，id对100取余加1获取hash值；

五、分区，可以通过范围分区、list分区、key分区，分区只是把一张表存储数据的空间分成多个区域，可以支持大量的IO；

六、多机集群，大致分为三步，主从同步、读写分离、主备切换，一两个数据库肯定支持不了大量的数据访问请求的，所以要集成多个数据库，在多个数据库里确定一个主数据库用于插入数据，然后查询通过从数据库查询出来，主从同步就是在主数据库插入数据后把数据生成一个二进制的文件，并授权一个账号，然后从数据库利用账号把二进制文件解析到自己库里，发送请求到数据库，它不知道该访问哪个数据库，这时就要通过一个负载均衡器来分发请求给主从数据库，负载均衡器管理着主从数据库的ip和端口号，自己也向外暴露一个请求能访问的ip，负载均衡器的配置文件通过管理主从数据库的ip和端口号来指定数据库是查询还是添加修改，这就做到了读写分离，如果出现单点故障就是负载均衡器挂了，最好的解决办法是使用备用负载均衡器，主的均衡器和副的均衡器通过第三方插件keepalive来建立联系，它们之间会一直发送心跳查询，如果主的挂了马上切换到备用负载均衡器；

测试在没有索引的条件下测试查询速度和有索引之后

定位慢查询  

   查询系统定义的慢查询时间  show variables like  'long_query_time'

开启慢查询

   关闭原来的服务

   然后以支持慢查询的方式启动

   设置慢查询时间 set long_query_time=0.5

   构造慢查询(查询语句)

   查看有几条慢查询 show status like 'slow_query'

分析慢查询  查看data下面的slow.log

   explain  sql语句

   type如果为all 表示全表扫描 possible_keys 可能用到的索引 如果为null则没有索引 key表示实际使用的索引 如果为null则没有使用索引 全表扫描  rows 扫描行数

行锁优化建议

1、避免无索引行级升级为表级锁，尽可能让所有数据检索都通过索引来完成

2、合理设计索引，尽量缩小锁的范围

3、尽可能减少检索条件，避免间隙锁

4、尽量控制事务大小，减少锁定资源量和时间长度

5、尽可能使用低级别的事务隔离