[大数据]DB优化 终极宝典

无论是项目的日常开发还是维护升级都与数据库的操作使用密不可分，以MySQL为例分别从几个方面为大家介绍数据库的优化。

????????首先sql的优化是概率性问题，例如我们为sql语句增加索引，并不是每次增加索引之后都能达到预期效果,因为MySQL中存在有sql优化器会对我们的sql语句进行二次编译，故有可能导致索引失效。

sql的执行顺序为 from->on->join->where->group by->having->select

查询优化?

1、含有in关键字的范围查询要放到where的最后

2、进行多表关联查询时要做到小表驱动大表，也就是说小表放到关键字(join) 的左边,同时在添加索引时因与业务相结合尽量在左边的外键添加,这样做可以减少循环次数，提高查询效率。（通俗来讲就是左外链接时在左表添加索引，右外链接时在右表添加索引）

3、添加复合索引时不能跨列或无序使用,尽量做到全索引匹配

4、对于复合索引，若左侧索引字段失效则全部失效

5、复合索引不能使用> < 或is null 否则自身以及右侧索引全部失效

6、不能在索引字段上进行操作(例:加减乘除)

7、like 关键字在索引字段使用时要用常量查询,不要以%开头，否则索引失效

8、不能使用类型转换,否则索引失效

9、不能用or否则索引失效

10、子查询时主查询数据量大则用 in ,子查询数据量大时则用 exist

11、慢查询：日志信息,默认记录响应时间高于10S的SQL语句??

//查看慢查询日志是否开启
show rariables like '%show_quary_log%'
//临时开启
set global show quary log=1
/**永久开启的话需要修改配置文件信息*/

12、EXPLAIN?关键字，进行sql语句的优化可以使用该关键字查看当前sql的执行效率以便进行相应调整

EXPLAIN select * from table_name

排序优化

MySQL检索排序有2种算法: ?1.4版前为双路排序，1.4版之后为单路排序

单路排序: 只读一次(全部字段) 但是当数据量超出Buffer值会进行分片多次读取

Buffer值设置 set max_length_for_sort_ data = '1024'

为保证排序的一致性,多个字段排序时应全部升序或者全部降序

插入优化

InnoDB引擎:

1.禁用唯一性检查

2.禁用外键检查

//禁用
set foreign_key_checks=0
//启用
set foreign_checks=0

3.禁用自动提交

//禁用
set autocommint = 0 
//启用
set autocommint = 1

MyIsam引擎:

1.禁用索引

/**因为MyIsam引擎会根据插入的数据建立索引*/
//禁用
alter table table_name disable keys
//启用
alter table_table_name enable key

2.禁用唯一性检查

//禁用
set unique_checks = 0
//启用
set unique_checks = 1

3.批量插入时使用 load data inFile 代替 insert

数据库的结构设计:

设计原则

1、减少数据冗余(不能完全杜绝)避免数据维护中出现的更新，插入，和删除异常

2、节约数据存储空间

3、提高查询的效率 ???? ?

4、进行数据库设计时一般先根据范式进行设计，在充分了解业务之后再进行反范式化设计。

范式化设计：

第一范式:

1、数据库表中的所有字段都具有单一属性

?2、单一属性的列由基本数据类型所构成

3、设计出来的表都是简单的二维表

第二范式：

1、满足第一范式要求

?2、要求表中有具仅有唯一业务主键（也就是说表中不能存在非主键列只对部分主键的依赖关系(主键可以是复合主键 )

第三范式：

1、每一个非主属性既不传递依赖也不部分依赖于业务主键，也就是说在第二范式的基础上消除了非主属性对主键的传递依赖

优点：

1、可以尽量的减少数据冗余

2、数据表更新快体积小

3、范式化更新操作比反范式化效率更高

4、范式化的表通常比反范式化更小

缺点：

1、查询时需要关联多个表，遵循范式化更新操作快但是查询慢，对于 数据库来说进行表的关联操作很耗资源，但是在项目中需要对数据库进行查询的需求要远高于更新的需求所以完全范式化设计会增加 降低查询速度，降低效率

?2、增加索引优化难度,查询的关键字段分布到多个表中难以创建符合索引

反范式化设计：

优点：

1、可以减少表的关联，查询简单，速度更快

2、可以更好的进行索引优化，就可以使用复合索引，提高查询速度

缺点：

1、存在大量的数据冗余以及数据维护异常

2、对数据的修改需要更多的成本，反范式化的设计在更新数据时往往需要更新多列

反范式化的设计其实是空间换时间，用内存空间或者是CPU资源来换去更快的查询速度，故此在进行数据的设计时首先进行范式化设计，之后再根据需求进行反范式化的修改是最佳方案。

下一篇为大家分享数据库的物理设计以及索引底层实现原理；更多内容请关注钢镚小码农

?