MySQL核心优化（一）

一、优化

浏览器=>web服务器=>后端脚本（php）=>数据库（mysql）

①提高并发量　　负载均衡　　并发量更高的软件（nginx）

②页面静态化　　不经常发生数据变动的动态网页生成为静态页面，提高访问速度

③内存缓存优化　　把数据缓存到内存中，提高数据响应速度

④数据库优化　　一般数据读写频繁，可以缓存到内存中。但是内存容量有限，不能够把大量数据缓存，所有优化数据库软件本身是很有必要的。

二、优化方向

①存储层　　存储引擎　　列表型选择　　范式

②设计层　　索引　　缓存　　分区表

③sql层　　使用执行效率高的sql语句　　explain 执行计划   慢查询日志记录

④架构层　　分布式数据库架构　　使用多台数据库服务器，解决数据库访问并发的问题　　主从复制        从服务器复制主服务器数据

三、存储引擎

 

3.1 myisam

1)创建数据

生成三种文件：.frm结构文件　　.MYD数据文件　　.MYI索引文件

2)数据存储顺序

存储顺序是插入顺序，没有进行排序操作

3)功能

①复制备份：数据表生成文件有三个，如果进行数据备份和恢复，直接复制粘贴三个文件恢复即可。

②压缩机制：数据表进行压缩，节省存储空间，提高查询速度

语法：

压缩：cmd> myisampack tablename 路径  
解压：cmd>myisamchk --unpack tablename 路径 
重新生成索引：cmd>myisamchk -rq tablename 路径    

4)并发性：表级锁（并发写入）

myisam 如果进行并发写入时，为了保证数据的一致性，加锁，只能使用表锁。锁表会影响到整个数据表的操作。

3.2 innodb

1)创建数据

可以选择把innodb的数据和索引文件，根据表名进行分离，

生成两种文件：.frm 结构文件　　.ibd 数据和索引文件

2)数据存储顺序

根据主键进行排序操作

3)功能

innodb表不能直接复制粘贴，备份恢复，需要导出sql文件，再导入恢复。

事务：保证数据的一致性，原子性。

如果有多个业务操作，需要把所有操作都执行成功才可以。如果有不成功，则所有操作都不能够执行成功，把已经执行的操作进行回滚（rollback）。

4)并发性：

innodb在进行并发操作时，为了数据的一致性，可以使用行锁机制（锁表粒度）。影响数据只为当前行。并发性较好一些。

3.3 选择myisam 和 innodb

①一般使用 普通读写较多的　　备份恢复方便　　myisam

cms（内容管理系统，快速搭建网站） myisam

②并发写入（行锁）事务 外键  innodb

订单系统（并发）  innodb

3.3 其他存储引擎

1)memory

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

2)Archive

归档存储引擎，只支持数据的查询和写入。经常用于存储日志等相关信息。

四、列类型选择

4.1选取占据空间小的字段

整型 int 

字符串 文本类型 char varchar text 

4.2 内容长度固定字段

char 定长 255 字符

varchar 变长 65535字节  如果使用utf-8的编码 3个字节一个字符，可以存储最大的字符数65535/3-1个字符  使用1个字符保存长度

如果数据不定长，使用varchar会更节省空间。

如果追求查询效率，就需要使用char

4.3 整型存储

尽量使用能整型存储数据

1)时间戳使用

使用int类型存储，节省空间，时间范围更好计算确定。

mysql相关函数：

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

php中时间戳相关函数：

time()          获取当前时间戳
date（“Y-m-d H:i:s”,time()）  时间戳格式为时间格式
strtotime（时间格式）  时间格式转为时间戳            

2)ip存储使用

mysql:ip转为 int 进行存储

inet_aton()     ip转为整型    
inet_ntoa()     整型转为ip

php:

ip2long()     ip转为int
long2ip()     int转为ip

五、范式

5.1三范式

 

范式是一种规范或者约束。

第一范式：数据字段具有原子性，业务上不可再分割。

第二范式：数据具有唯一性（主键id）。

第三范式：数据字段和主键具有紧密联系，不允许出现冗余字段，

5.2 逆（反）范式

真实业务的环境，为了能够实现更好的数据库表的性能，会选择不遵守范式的操作。

遵守第三范式，查询数据，需要连表操作，如果数据表数据很多，连表操作，会耗费大量时间。为了提高查询效率。可以选择把数据字段存储到同一个表中。

建立了冗余字段之后，一定要注意维护数据的一致性。