Mysql优化一（存储引擎、列类型、范式规范）

Mysql核心优化

在大部分的动态网站中，需要读取数据从mysql中，很多时间也是浪费在查询数据上，如果数据量特别大的，数据文件占用空间也同时变大，造成查询数据返回时间变慢。但是很多数据，还必须要读取mysql数据库，所以进行mysql的相关优化是很有必要的。

优化方面

1)存储层(数据)： 存储引擎、列类型、范式规范

2)设计层(单台服务器)： 索引、缓存、分区分表

3)sql语句层： 更合适的sql语句

比如：select * from student；

select name，sex from student；

显然第二条语句，会更加节省资源，内存的使用和带宽资源。

如果数据表特别的大的时候，不建议进行连表查询。普通查询没有索引，需要扫描全表。

连表查询 扫描次数笛卡尔积 两个表的条数相乘。

连表查询比子查询快

4)架构层(多台服务器)： 读写分离(主从复制)

存储引擎

存储引擎就是存储数据表的方式，不同的存储引擎有不同的特性，可以根据实际业务需要选择不同的存储引擎。

 

MySQL常见的四种存储引擎 MyISAM InnoDB Memory Archive 

Memory

数据置于内存的存储引擎，拥有极高的插入，更新和查询效率。但是会占用和数据量成正比的内存空间。并且其内容会在Mysql重新启动时丢失

Archive

归档存储引擎，只支持数据的查询和写入。

经常用于存储日志等相关信息。

没有说，哪个存储引擎是最好，是完美。要根据实际业务需求，去选择。

常用的2种 MyISAM InnoDB

MyISAM

文件结构

创建一个MyISAM表 包含结构、数据、索引的物理文件三个文件

CREATE TABLE student(
  id INT UNSIGNED not NULL auto_increment COMMENT 'id',
  name VARCHAR(32) not NULL COMMENT '姓名',
  height DECIMAL(10,2) NOT NULL DEFAULT 0 COMMENT '身高',
  age INT NOT NULL DEFAULT 1 COMMENT '年龄',
  introduce text COMMENT '介绍',
  PRIMARY key (id)
)ENGINE=Myisam charset=utf8;

数据存储顺序 

理论上，不进行排序操作，插入速度更加快速。

insert into student VALUES (22,'张三',181.25,18,'自我介绍');
insert into student VALUES (11,'李四',181.25,19,'自我介绍');
insert into student VALUES (23,'王五',181.25,20,'自我介绍');
insert into student VALUES (8,'刘六',181.25,21,'自我介绍');
insert into student VALUES (1,'赵七',181.25,17,'自我介绍');

功能：压缩机制 

当数据表不断存入数据，数据表就会很占空间，所以myisam可以进行表的压缩，节省存储空间。

第一步：插入多条数据，使数据表文件变大。

#复制表

insert into student select null,name,height,age,introduce from student;
FLUSH table;

 

第二步：进行表压缩

语法：cmd> myisampack 表路径   //压缩表

通过压缩之后，可以看到MYD文件明显变小。 原来为200kb 现在为71kb  之前的数据文件删除

第三步：重新生成myisam表的索引

语法：cmd> myisamchk –rq 表路径        重新生成索引

 第四步：测试是否可以正常使用

 

 注意 myisam表压缩之后，只能过读，不能够写，所以要先进行表的解压操作，然后再进行写。

第五步：解压缩操作

语法：cmd> myisamchk --unpack 表路径      解压 

解压后文件又变换三个

在进行表压缩和解压缩是，有时候表数据不能够及时的，保存到数据文件，所以需要flush table 表名，进行表的刷新操作。

因为myisam压缩之后不能够写，所以压缩的表适合压缩存储不太经常变动的数据，但是数据量巨大的。邮政地区编码，城市编号等等。压缩和解压操作，需要接触服务相关文件，很多时候没有权限接触，压缩和解压缩操作，一般是由数据库管理员（DBA）或者系统管理员（服务器运维）操作。

 并发性：表级锁(并发写入)

并发性，就是指数据库同一时间被写入或者查询操作，myisam会进行表锁，表锁之后，所有数据，必须等操作完成之后，才能够继续其它操作。所以并发写上，会进行表级锁。

大量数据被同时请求的时候，会造成并发的锁表操作。

InnoDB

文件结构

创建一个InnoDB表 包含结构、数据索引的物理文件两个文件

CREATE TABLE `student1` (
`id`  int(10) NOT NULL AUTO_INCREMENT COMMENT 'id' ,
`name`  varchar(32) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL COMMENT '姓名' ,
`height`  decimal(10,2) NOT NULL DEFAULT 0.00 COMMENT '身高' ,
`age`  int(11) NOT NULL DEFAULT 1 COMMENT '年龄' ,
`introduce`  text CHARACTER SET utf8 COLLATE utf8_general_ci NULL COMMENT '介绍' ,
PRIMARY KEY (`id`)
)ENGINE=INNODB CHARSET=utf8;

 

 注意

查看InnoDB表创建时候是否时根据表进行数据索引文件拆分

 innodb_file_per_table为OFF时，所有的innodb表的里的数据和索引文件都在一起，这样不太利于管理。可能文件损坏所有的innodb就都不能用了。所以需要进行拆分

mysql > show variables like 'innodb_file_per_table';

mysql > set global innodb_file_per_table=1;  1为开启  0为关闭

数据主键顺序存储 

插入数据

insert into student1 VALUES (22,'张三',181.25,18,'自我介绍');
insert into student1 VALUES (11,'李四',181.25,19,'自我介绍');
insert into student1 VALUES (23,'王五',181.25,20,'自我介绍');
insert into student1 VALUES (8,'刘六',181.25,21,'自我介绍');
insert into student1 VALUES (1,'赵七',181.25,17,'自我介绍');

功能：事务、外键

事务功能，有多个操作，在执行过程中，如果有其中的操作没有执行成功，就所有操作不能够执行成功。会执行回滚操作。

比如：银行转账操作

A 给 B 转100 元

拆分两个步骤：

1. A扣100元
2. B增加100元

失败操作：A的钱扣了，B的钱没有加。

事务就可以保证，如果A的钱扣了，但是B没有加，那么就把A的钱再加回去。

 外键：需要两个表之间建立一种关系，一个表的主键和另外一表的字段主成外键。

 外键的使用条件：
1.两个表必须是InnoDB表，MyISAM表暂时不支持外键（据说以后的版本有可能支持，但至少目前不支持）；
2.外键列必须建立了索引，MySQL 4.1.2以后的版本在建立外键时会自动创建索引，但如果在较早的版本则需要显示建立； 
3.外键关系的两个表的列必须是数据类型相似，也就是可以相互转换类型的列，比如int和tinyint可以，而int和char则不可以；

外键的好处：可以使得两张表关联，保证数据的一致性和实现一些级联操作；

student表

学生id   name  age  系别id

1        tom    18    100

系别表

系id    系名称

1       计算机

.....      ....

15      考古

如果建立的外键关系，系别为100的id的数据，是不允许插入的。

并发性：擅长并发

myisam是表级锁，会锁表，需要操作完成才能够进行下一次操作。innodb是一个行级锁，在进行大并发写入操作时，只锁当前行，所以不影响其他行，说innodb更加适合并发性。

如果数据范围没有确定，并且也没有加索引，查询表还是要检索整个表，innodb也就锁整个表了。

 

如何选择myisam和innodb

关于数据库表的导入和导出操作

myisam是三个文件 可以直接复制到数据目录（data）即可，直接进行备份和恢复操作。

innodb需要导出mysql文件，然后再进行导出操作。

如果进行一般的写入和查询操作，myisam是比较适合的，它是简单的一个存储引擎，有很快的接入和查询速度。使用count查询表的数量，内部有一个计数器，可以直接返回数据条数的总数。返回快速。表锁

innodb拥有高级功能，事务和外键功能，所以如果要使用到事务和外键的功能，那么就需要选用innodb表。行锁，在大并发进行写入的时候，更适合innodb。

 列类型 字段选取

 1.选取占据空间小的字段 

比如：

人类的年龄   200年左右 可以选择tinyint

乌龟的年龄   千年龟万年龟   可以选择smallint

如果数据量不超过1600w的话，就可以选择mediumint

以上选择都是可以选择占用存储空间更小的字段代替。

2.内容长度固定字段

char和varchar

char（255字符 ），定长  分配了多少，就占用多少。如果使用不完，就用其他字符去补全。

varchar（65535字节) ，变长，使用多少，占用多少，但是需要有例外的长度进行记录当前字符串的一个长度。如果是UTF则最大65535/3 - 1个字符，因为要预留空间存放该字段的字符数目

可以进行定长的话，就选择定长char，可以提高查询速度。如果每次查询还需要判断字符串的长度，就增加了资源的消耗。

比如：

手机号码  11位  定长 可以使用char

邮箱地址   admin@qq.com    sfsfsfsfsf23236@163.com

邮箱地址长度不能够确定，如果是为了节省空间，可以使用varchar变长。

 3.整型存储

一般存储时间使用时间戳和ip地址，存储为int类型。

时间戳使用

unix_timestamp()  当前时间戳信息

from_unixtime()     读取一个时间戳信息

 

ip存储使用

mysql里转化ip和数字互相转化

inet_aton()   

inet_ntoa()

能过使用整型存储的数据，尽量使用整型存储，在节省空间的同时也方便计算。

通过合适的列类型，也就是字段的各种类型，提高数据库的性能。

 范式规范

范式是数据设计的一种规则规范，如果符合规范和规则，被认为是良好的数据设计。

范式规则总共有三种。

1.三范式

第一范式   数据字段设计符合原子性，字段是相对独立的。业务上不可再分割。

第二范式   表字段拥有主键（唯一，自增），字段相关信息都是和主键有关系的字段信息

第三范式   表字段之间都是独立而且是互相联系紧密的，不能够出现冗（rong）【重复的无用的】余字段。

范式规则：是逐层满足的，满足第一范式才能够满足第二范式，满足第二范式才能够满足第三方式，可以表示为1NF<2NF<3NF 

2逆范式

但是不是绝对的，很多时候，为了方便使用数据库，也并不是完全遵照范式的。

比如：

博客项目里，文章需要单独创建一个表，里面包含了title，content，author等等字段

文章表

id title  content   author

点击量表

a_id    hit_count()

一般情况下，在调取博客文章内容的时候，会调取点击量的。正常点击量会存储其他表里，由一个文章id和点击数量进行存储。

在查询点击量需要进行连表查询。连表查询会进行笛卡尔积计算，所以在遍历表上会扫描更多的表数据，返回数据变慢。

可以设计为 把hit_count()字段，再文章表里也存储一份。这样，在调取的时候，就只需要写一条sql语句，提高查询速度。

使用冗余字段，要注意数据的一致性，保障多个表的字段数值是相同的。