11 数据库优化

数据库优化

数据库设计范式

设计关系数据库时，遵从不同的规范要求，设计出合理的关系型数据库，这些不同的规范要求被称为不同的范式。

目前关系数据库有六种范式：第一范式（1NF）、第二范式（2NF）、第三范式（3NF）、巴斯-科德范式（BCNF）、第四范式(4NF）和第五范式（5NF，又称完美范式）。

各种范式呈递次规范，越高的范式数据库冗余越小。但是范式越高也意味着表的划分更细，一个数据库中需要的表也就越多，此时多个表联接在一起的花费是巨大的，尤其是当需要连接的两张或者多张表数据非常庞大的时候，表连接操作几乎是一个噩梦，这严重地降低了系统运行性能。所以通常数据库设计遵循第一第二第三范式，以避免数据操作异常,又不至于表关系过于复杂。

范式简介：

• 第一范式： 数据库表的每一列都是不可分割的原子数据项，而不能是集合，数组，记录等组合的数据项。简单来说要求数据库中的表示二维表，每个数据元素不可再分。

  例如： 在国内的话通常理解都是姓名是一个不可再拆分的单位，这时候就符合第一范式；但是在国外的话还要分为FIRST NAME和LAST NAME，这时候姓名这个字段就是还可以拆分为更小的单位的字段，就不符合第一范式了。

• 第二范式： 第二范式（2NF）要求数据库表中的每个实例或记录必须可以被唯一地区分，所有属性依赖于主属性。即选取一个能区分每个实体的属性或属性组，作为实体的唯一标识，每个属性都能被主属性筛选。其实简单理解要设置一个区分各个记录的主键就好了。

• 第三范式： 在第二范式的基础上属性不传递依赖，即每个属性不依赖其他非主属性。要求一个表中不包含已在其它表中包含的非主关键字信息。其实简单来说就是合理使用外键，使不同的表中不要有重复的字段就好了。

MySQL存储引擎

• 定义： mysql数据库管理系统中用来处理表的处理器
• 基本操作

1、查看所有存储引擎
   mysql> show engines;
2、查看已有表的存储引擎
   mysql> show create table 表名;
3、创建表指定
   create table 表名(...)engine=MyISAM;
4、已有表指定
   alter table 表名 engine=InnoDB;

• 常用存储引擎特点

  InnoDB

  1. 支持行级锁,仅对指定的记录进行加锁，这样其它进程还是可以对同一个表中的其它记录进		行操作。
  2. 支持外键、事务、事务回滚
  3. 表字段和索引同存储在一个文件中
  		 1. 表名.frm ：表结构
   		 2. 表名.ibd : 表记录及索引文件
  
  

  MyISAM

  1. 支持表级锁,在锁定期间，其它进程无法对该表进行写操作。如果你是写锁，则其它进程则		读也不允许
  2.  表字段和索引分开存储
        	 1. 表名.frm ：表结构
           2. 表名.MYI : 索引文件(my index)
           3. 表名.MYD : 表记录(my data)
  
  

• 如何选择存储引擎

  1. 执行查操作多的表用 MyISAM(使用InnoDB浪费资源)
  2. 执行写操作多的表用 InnoDB
  
  CREATE TABLE user(
  id int(11) NOT NULL AUTO_INCREMENT,
  name varchar(30) DEFAULT NULL,
  sex varchar(2) DEFAULT NULL,
  PRIMARY KEY (id)
  )ENGINE=MyISAM;
  
  alter table hobby engine=myisam;
  

字段数据类型和键的选择

• 数据类型优先程度 数字类型 --> 时间日期类型 --> 字符串类型
• 同一级别 占用空间小的 --> 占用空间大的

字符串在查询比较排序时数据处理慢
占用空间少，数据库占磁盘页少，读写处理就更快

• Innodb如果不设置主键也会自己设置隐含的主键，所以最好自己设置
• 尽量设置占用空间小的字段为主键
• 建立外键会自动建立索引，在表关联查询时建议使用外键子段作为关联条件
• 外键虽然可以保持数据完整性，但是会降低数据导入和操作效率，增加维护成本

explain语句

使用 EXPLAIN 关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。这可以帮你分析你的查询语句或是表结构的性能瓶颈。通过explain命令可以得到:

• 表的读取顺序
• 数据读取操作的操作类型
• 哪些索引可以使用
• 哪些索引被实际使用
• 表之间的引用
• 每张表有多少行被优化器查询

explain select * from class where id <5;

EXPLAIN主要字段解析：

• table：显示这一行的数据是关于哪张表的
• type：这是最重要的字段之一，显示查询使用了何种类型。从最好到最差的连接类型为system、const、eq_reg、ref、range、index和ALL，一般来说，得保证查询至少达到range级别，最好能达到ref。

type中包含的值：
- system、const： 可以将查询的变量转为常量. 如id=1; id为 主键或唯一键.
- eq_ref： 访问索引,返回某单一行的数据.(通常在联接时出现，查询使用的索引为主键或唯一键)
- ref： 访问索引,返回某个值的数据.(可以返回多行) 通常使用=时发生 
- range： 这个连接类型使用索引返回一个范围中的行，比如使用>或<查找东西，并且该字段上建有索引时发生的情况
- index： 以索引的顺序进行全表扫描，优点是不用排序,缺点是还要全表扫描 
- ALL： 全表扫描，应该尽量避免

• possible_keys：显示可能应用在这张表中的索引。如果为空，表示没有可能应用的索引。
• key：实际使用的索引。如果为NULL，则没有使用索引。
• key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好
• rows：MySQL认为必须检索的用来返回请求数据的行数

SQL优化

• 尽量选择数据类型占空间少，在where ，group by，order by中出现的频率高的字段建立索引

• 尽量避免使用 select * …;用具体字段代替 * ,不要返回用不到的任何字段

• 尽量控制使用自定义函数

• 查询最后添加 LIMIT 会停止全表扫描

• 尽量避免 NULL 值判断,否则会进行全表扫描，默认值为空时可以用默认0代替

  优化前：select number from t1 where number is null;

  优化后：select number from t1 where number=0;

• 尽量避免 or 连接条件,否则会放弃索引进行全表扫描，可以用union代替

  优化前：select id from t1 where id=10 or id=20;

  优化后： select id from t1 where id=10 union all select id from t1 where id=20;

• 尽量避免使用 in 和 not in,否则会全表扫描

  优化前：select id from t1 where id in (1,2,3,4);

  优化后：select id from t1 where id between 1 and 4;

表的拆分

垂直拆分 ： 表中列太多，分为多个表，每个表是其中的几个列。将常查询的放到一起，blob或者text类型字段放到另一个表

水平拆分 ： 减少每个表的数据量，通过关键字进行划分然后拆成多个表