MySQL的优化策略

　　最近写了蛮多数据库相关的，不出意料这应该是近期最后一篇数据库相关了。今天，笔者就来总结一下，常见的数据库优化策略。

 

　　1.首先，应该从业务层开始考虑，尽量把涉及到业务逻辑的操作放到业务层进行处理，因为持久层的资源非常宝贵，业务层可以进行扩容，而涉及到数据库就非常麻烦。因此，尽量不要使用存储过程、函数、触发器、外键约束等。

 

　　2.合理设计表结构。合理地运行三范式和反范式，尽量减少连表查询的同时，也要做到尽量没有冗余索引、冗余字段。（MySQL5.7以后，可以通过查询sys库的schemal_redundant_indexes表来查看冗余索引）。由于B+树索引是由左往右顺序索引，因此设计表结构时需要格外注意，否则可能会放弃索引。尤其是主键，InnoDB是主键索引（也叫聚簇索引），因此不要用过长的字段（比如uuid）来做主键，也不要用非单调的字段作为主键，这样会造成主索引频繁分裂。应当尽可能用一个与业务无关的自增字段做主键，即使用逻辑主键，而不要用业务主键。在添加了索引的列里，需要加NOT NULL约束，否则在有null值的情况下，mysql会放弃使用索引。

　　对单表记录数很高的表，我们应当对其进行水平分区和垂直分区。垂直分区是指将一些字段取出来单独做分表，或者干脆放到单独的库里做分库。这样可以使每行的数据量减少，缺点是会产生冗余主键，也会让业务管理变得复杂。水平分区则是保持表结构不变，通过某种策略进行数据分片，比如按照某个时间段，某个订单段等进行分片，这样还可以分离热点数据，方便做缓存优化。但需要注意的是，分表其实只是解决了单一表数据量过多的问题，对表的并发问题无能为力，因此水平分区最好进行分库，Mysql提供的分区表也是水平分区。不过，一般来说，水平分区会造成逻辑、部署、运维的各种复杂度，一般的数据库应对千万以下的数据量已经足够了，因此，尽量不要使用水平分区，如果实在要用，尽量使用客户端分片。

　　MySQL的引擎都有各自的特点，比如myISAM读取速度快，InnoDB综合能力强（支持行级锁等，本文不详述，想了解的可以翻笔者以前的博文），memery可能会造成数据丢失，但由于使用了hash索引，读操作非常快。因此，我们应当根据业务需求灵活地选用数据库引擎（当然，当你不确定用什么引擎时，用InnoDB一般都是没错的）。此外，可以利用主从配置，做到读写分离，主库负责写入数据，从库负责读取数据。读写分离的优势还涉及到共享锁、排他锁等方面，由于不是本文重点，笔者在此也不对此详述。

　　此外，就是一些设计的小技巧，如合理的使用数据类型（同时指定宽度），可以使用TINYINT的就不要使用BIGINT，使用枚举或者整数类型代替字符串，尽量避免使用DECIMAL和DOUBLE\FLOAT,可以用BIGINT来代替，多使用状态判断位，用逻辑删除来取代物理删除。

　　3.编写SQL语句的优化，多使用EXPLAIN命令，慢慢就可以编写出简洁优美的SQL语句。这里需要注意的地方太多，因此笔者仅举几个常见的事例。比如尽量不要使用关联表查询；where条件后面尽量不要加n+1>5这种逻辑运算，而应该直接写成n>4,否则mysql会放弃使用索引；不要使用全属性选择器（*）；对连续条件的查询，应该使用between而不是in；避免使用负向查询（！和<>)和or，而使用in代替，in的查询效率是log（n），而负向查询和or会全表查询；除非确实需要，否则一律使用UNION ALL，不要使用UNION,UNION会给临时表加上DISTINCT选项，影响查询性能；拆开过长的SQL语句，分句查询，避免读锁堵死整张表；写SQL的时候用大写，可以节省编译器性能。

 

　　以上是笔者总结的一些SQL调优策略，水平有限，肯定有所疏漏，希望各位不吝赐教。