MySQL优化方案

原文地址

MYSQL优化主要分为以下四大方面：

设计：存储引擎，字段类型，范式与逆范式

功能：索引，缓存，分区分表。

架构：主从复制，读写分离，负载均衡。

合理SQL：测试，经验。

一、存储引擎

​ Create table tableName () engine=myisam|innodb; 使用show engines命令来查看当前MySQL支持的存储引擎列表

关于Innodb 和myisam的取舍：

​ Innodb ：数据完整性，并发性处理，擅长更新，删除。

​ myisam：高速查询及插入。擅长插入和查询。

二、字段类型选择

​ 字段类型应该要满足需求，尽量要满足以下需求:

1. 尽可能小（占用存储空间少）
2. 尽可能定长（占用存储空间固定）
3. 尽可能使用整数。

三、范式与逆范式

​ 为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则。在关系型数据库中这种规则就称为范式。范式是符合某一种设计要求的总结。要想设计一个结构合理的关系型数据库，必须满足一定的范式。

• 第一范式1NF，原子性

• 第二范式2NF，消除部分依赖

• 第三范式3NF，消除传递依赖

四、索引

索引的种类：

• 普通索引,index：对关键字没有要求。
• 唯一索引,unique index：要求关键字不能重复。同时增加唯一约束。
• 主键索引,primary key：要求关键字不能重复，也不能为NULL。同时增加主键约束。
• 全文索引,fulltext key：关键字的来源不是所有字段的数据，而是从字段中提取的特别关键词。

索引的使用应遵循如下规则：

1. 不要过度索引。索引越多，占用空间越大，反而性能变慢；
2. 只对WHERE子句中频繁使用的建立索引；
3. 尽可能使用唯一索引，重复值越少，索引效果越强；
4. 使用短索引，如果char(255)太大，应该给它指定一个前缀长度，大部分情况下前10位或20位值基本是唯一的，那么就不要对整个列进行索引；
5. 充分利用左前缀，这是针对复合索引，因为WHERE语句如果有AND并列，只能识别一个索引(获取记录最少的那个)，索引需要使用复合索引，那么应该将WHERE最频繁的放置在左边；
6. 索引存在，如果没有满足使用原则，也会导致索引无效。

五、查询缓存query_cache

1. 查询缓存存在判断是严格依赖于select语句本身的：严格保证SQL一致。
2. 如果查询时包含动态数据，则不能被缓存。
3. 一旦开启查询缓存，MySQL会将所有可以被缓存的select语句都缓存。如果存在不想使用缓存的SQL执行，则可以使用 SQL_NO_CACHE语法提示达到目的。
4. 这里的缓存仅当数据表的记录改变时，缓存才会被删除。而不是依靠过期时间的。

六、分区分表

​ 分区，partition，分区是将数据分段划分在多个位置存放，可以是同一块磁盘也可以在不同的机器。分区后，表面上还是一张表，但数据散列到多个位置了。app读写的时候操作的还是大表名字，db自动去组织分区的数据。

​ 分表是将一个大表按照一定的规则分解成多张具有独立存储空间的实体表，我们可以称为子表，每个表都对应三个文件，MYD数据文件，.MYI索引文件，.frm表结构文件。这些子表可以分布在同一块磁盘上，也可以在不同的机器上。app读写的时候根据事先定义好的规则得到对应的子表名，然后去操作它。分表技术是比较麻烦的，需要手动去创建子表，app服务端读写时候需要计算子表名。采用merge好一些，但也要创建子表和配置子表间的union关系。（需要手动分表）。

​ 分表是分区之前用的，MYSQL5.1后，就开始用分区代替分表了。分表很少用了。

七、服务器架构

1、主从复制：

​ Mysql服务器内部支持复制功能，仅仅需要通过配置完成下面的拓扑结构。一主多从典型结果：主服务器负责写数据。从服务器负责读数据。复制功能mysql会自带。

2、读写分离，负载均衡：

​ php不再操作MYSQL数据库服务器，而是去操作读写分离、负载均衡服务器，只要服务器安装了mysql proxy或Ameoba软件就可以实现读写分离和负载均衡，读写分离是指该服务器会判断客户端的操作是读还是写，从而选择操作mysql主服务器还是从服务器。负载均衡算法是指，客户端读操作时，该服务器会根据取余算法去选择一台从服务器。

八、SQL优化

1.对于并发性的SQL

少用（不用）多表操作（子查询，联合查询），而是将复杂的SQL拆分多次执行。如果查询很原子（很小），会增加查询缓存的利用率。

2.大量数据的插入

多条 insert或者Load data into table（从文件里载入数据到表里）

建议，先关闭约束及索引，完成数据插入，再重新生成索引及约束。

针对于myisam，步骤：

Alter table table_name disable keys; 禁用索引约束

大量的插入

Alter table table_name enable keys; 启用

针对innodb，步骤：

Drop index, drop constraint 删除索引及约束，要保留主键

Begin transaction|set autocommit=0; 开启事务，不让他自动提交

[数据本身已经按照主键值排序]

大量的插入

Commit;

Add index, add constraint

3.分页

分页假定Limit offset, size; size = 10;

Page	offset
5	40, 10
50	490, 10
5000	4990, 10
500000	499990, 10

Limit 的使用，会大大提升无效数据的检索（被跳过），因为是先检索，检索会检索全部，再取得想要的。好的做法是使用条件等过滤方式，将检索到的数据尽可能精确定位到需要的数据上。

4.随机选一些数据，不要使用Order by Rand()