[mysql] 数据库优化

可以从两个角度来分析，一种是数据库的优化，另一种是sql的优化

数据库的优化

1、主从复制，读写分离，负载均衡

目前，大部分的主流关系型数据库都提供了主从复制的功能，通过配置两台（或多台）数据库的主从关系，可以将一台数据库服务器的数据更新同步到另一台服务器上。网站可以利用数据库的这一功能，实现数据库的读写分离，从而改善数据库的负载压力。

2、数据库分表、分区、分库

分区：把一张表的数据分成多个区块，这些区块可以在一个磁盘上，也可以在不同的磁盘上。分区后，表面上还是一张表，但数据散列在多个位置，这样一来，多块硬盘同时处理不同请求，从而提高磁盘IO读写性能

分表：把一张表按照功能或者一定规则拆分，存储到多个结构相同的表和不同的库中

分库：是根据业务不同把相关的表且分到不同的数据库中

expalin详解

sql优化

1、选取最适用的字段属性

• varchar(6)可以很好的完成任务就别定义varchar(255)了
• 尽量把字段设置为NOT NULL，这样在将来执行查询的时候，数据库不用去比较NULL值。（比较NULL值会不走索引）
• 对于某些文本字段，例如“省份”或者“性别”，我们可以将它们定义为ENUM类型。因为在MySQL中，ENUM类型被当作数值型数据来处理，而数值型数据被处理起来的速度要比文本类型快得多。

2、使用连接（JOIN）来代替子查询(Sub-Queries)
连接（JOIN）…之所以更有效率一些，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。

3、使用联合(UNION)来代替手动创建的临时表
union查询可以把需要使用临时表的两条或更多的select查询合并的一个查询中。在客户端的查询会话结束的时候，临时表会被自动删除，从而保证数据库整齐、高效。

4、事务
事务可以保持数据库中数据的一致性和完整性。事务以BEGIN关键字开始，COMMIT关键字结束。在这之间的一条SQL操作失败，那么，ROLLBACK命令就可以把数据库恢复到BEGIN开始之前的状态。

5、锁定表
由于在事务执行的过程中，数据库将会被锁定，因此其它的用户请求只能暂时等待直到该事务结束。其实，有些情况下我们可以通过锁定表的方法来获得更好的性能。下面的例子就用锁定表的方法来完成前面一个例子中事务的功能。

LOCK TABLE inventory WRITE SELECT Quantity FROM inventory WHERE Item='book';
UPDATE inventory SET Quantity=11 WHERE Item='book'; UNLOCKTABLES

6、使用外键
锁定表的方法可以维护数据的完整性，但是它却不能保证数据的关联性。这个时候我们就可以使用外键。

7、使用索引

• 为where、on、group by、order by后面的字段建立索引。注意order by必须使用索引的最左前列
• 尽量不要对数据库中某个含有大量重复的值的字段建立索引。
• 尽量的扩展索引，不要新建索引，索引过多那么更新表会比较耗时
• 如果索引字段的值很长，最好使用值的前缀来索引。

8、优化的查询语句

• 最好是在相同类型的字段间进行比较的操作。例如不能将一个建有索引的INT字段和BIGINT字段进行比较；但是作为特殊的情况，在CHAR类型的字段和VARCHAR类型字段的字段大小相同的时候，可以将它们进行比较。

• 在建有索引的字段上尽量不要使用函数进行操作。

• 减少使用LIKE关键字和通配符

WHERE name like "MySQL%"
//但是如果换用下面的查询，返回的结果一样，但速度就要快上很多：
WHERE name>="MySQL" andname <"MySQM"

• 减少使用IN或者NOT IN ,使用exists，not exists或者关联查询语句替代

• or 的查询尽量用 union或者union all 代替(在确认没有重复数据或者不用剔除重复数据时，union all会更好)

• 应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描

• 不要使用select *
  不需要的列会增加数据传输时间和网络开销
  对于无用的大字段，如 varchar、blob、text，长度超过 728 字节的时候，会先把超出的数据序列化到另外一个地方，因此读取这条记录会增加一次 io 操作
  增加了不必要的列可能会导致原来能走索引的查询现在不能走索引

• 永远小表驱动大表
  in 和 exists 选择：

// 工作原理，先查B表数据，然后查A的 id 
select * from A where id in (select id from B)
// 工作原理，先查A表的id，然后查B表的id
select * from A a where exists (select 1 from B b where a.id = b.id )
// 结论：当B表的数据小于A表时候用 in；当A表数据小时候用 exists