MySQL优化知识点分类

MySQL优化

目录
一、设计方面
1、存储引擎
1.1、InnoDB
1.2、MyISAM
1.3、锁的概念
2、字段类型选择
3、范式与逆范式
3.1、范式
3.2、逆范式
二、功能方面
1、索引
1.1、索引概述
1.2、索引种类
1.3、总结
2、查询缓存query_cache
2.1、开启
2.2、注意事项
3、分区
3.1、分区算法
3.2、分区管理与选择
4、分表
4.1、水平分表
4.2、垂直分表
三、架构方面
1、主从复制
1.1、为什么做主从复制
1.2、主从复制的流程
2、读写分离
2.1、什么是读写分离
2.2、为什么要读写分离
2.3、什么时候读写分离
2.4、常见的读写分离方式
3、负载均衡
3.1、随机
3.2、轮询
3.3、哈希
3.4、最小压力
3.5、实际情况
四、SQL优化
1、对于并发性的SQL
2、大量数据的插入
3、分页
4、随机选一些数据，不要使用 Order by Rand（）
MySQL的优化主要分为四大方面：

设计：存储引擎、字段类型、范式与逆范式
功能：索引、缓存、分区分表
架构：主从复制、读写分离、负载均衡
SQL：测试、经验
一、设计方面
1、存储引擎
存储引擎是一种存储MySQL中对象（记录和索引）的一种特定的结构（文件结构），处于MySQL服务器的最底层，直接存储数据。

1.1、InnoDB
支持事务、行级锁定、外键约束，事务安全型存储引擎。更加注重数据的完整性和安全性。

①、存储格式：
数据、索引集中存储，存储于同一个表空间文件中。

独立表空间：

db.opt里存放了数据库的配置信息
table_name.frm 每个表都有一个.frm的描述文件，表结构文件
table_name.idb 每个表还有一个 .idb文件，表索引文件
每个表都有自己的独立表空间，存储了数据和索引；可以实现单表在不同的数据进行迁移，表空间可以回收。
表空间过大的时候可以使用分区表

共享表空间：（默认的表空间文件格式）
所有的表数据、索引文件都放在一个文件中，默认在data下的文件 ibdata1，初始化为10M

优点：可以将表空间分成多个文件存放在各个磁盘上（表空间文件大小不受表大小的限制，如同一个表可以分布在不同的文件上），数据和文件放在一起方便管理。
缺点：所有的数据和索引存放到一个文件中，将来会是一个很大的文件，虽然可以把一个大文件分成多个小文件，但是多个表及索引在表空间中混合存储，这样对一个表做了大量删除操作后表空间将有大量的空隙，特别是对统计分析、日志系统这类应用最不适合用共享表空间。
②、数据按照主键顺序存储

插入时做排序工作，效率低

③、特定功能
事务、外键约束：都是维护数据的完整性
并发处理：行级锁

④、总结
innodb擅长事务、数据完整性及高并发处理，不擅长快速插入（插入前需要排序，消耗时间）和检索

1.2、MyISAM
①、存储方式
数据和索引分别存储于不同的文件中。
②、数据的存储顺序为插入顺序
插入速度快（没有经过排序），空间占用量小。
③、功能
支持全文索引
数据压缩存储
④、InnoDB与MyISAM的取舍
InnoDB：数据完整性，并发性处理，擅长更新，删除
MyISAM：高速查询和插入。擅长插入和查询。
1.3、锁的概念
当客户端操作表时，为了保证操作的隔离性（多个客户端操作不能互相影响），通过加锁来处理
操作方面 ：

读锁：读操作时增加的锁，也叫共享锁，S-lock。特征是阻塞其他客户端的写操作，不阻塞读操作。（并发读）
写锁：写操作时增加的锁，也叫独占锁或排他锁，X-lock。特征是阻塞其他客户端的读、写操作。
锁定粒度（范围）：

行级：提升并发性，锁本身开销大。
表级：不利于并发性，锁本身开销小。
2、字段类型选择
字段类型应该满足要求：
尽可能小（占用存储空间少），尽可能定长（占用存储空间固定），尽可能使用整数

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

第一范式1NF，原子性
例如：地址拆分成 省 市 区 详细地址
第二范式2NF，消除部分依赖
第三范式3NF，消除传递依赖
例如：订单表中只存用户id，不存用户姓名、联系方式等
3.2、逆范式
逆范式是指打破范式，通过增加冗余或重复的数据来提高数据库的性能，
例如：
商品表 ：商品id 商品名称 商品分类
分类表：分类id 分类名称
查询某类别下商品的数量，就是逆范式

二、功能方面
1、索引
1.1、索引概述
利用关键字，就是记录的部分数据（某个字段。某些字段，某个字段的一部分），建立与记录位置的对应关系，就是索引。
索引的关键字一定是排序的。索引本质上是表字段的有序子集，它是提高查询速度最有效的方法。

1.2、索引种类
从索引的定义方式和用途来看：

主键索引（primary key）：要求关键字不能重复，也不能为null。同时增加主键约束。
唯一索引（unique key）：要求关键字不能重复。同事增加唯一约束
普通索引（index）：对关键字没有要求。
全文索引（fulltext key）：关键字来源不是所有字段的数据，而是从字段中提取的特别关键词。
create table student (
stu_id int unsigned not null auto_increment,
xing varchar(8) not null default ‘’,
ming varchar(32) not null default ‘’,
stu_sn char(10) not null default ‘’,
stu_desc text,
primary key (stu_id), – 主键索引
unique index ui (stu_sn), – 唯一索引
index xingming (xing,ming),-- 复合，普通索引
fulltext index desc (stu_sesc) – 全文索引
) engine=myisam charset=utf8;
1
2
3
4
5
6
7
8
9
10
11
1.3、总结
不要过度索引。索引越多，占用空间越大，反而性能变慢；
只对where子句频繁使用的字段建立索引；
尽可能使用唯一索引，重复值越少，索引效果越强；
使用短索引；
充分利用左前缀，这是针对复合索引，因为where语句如果有and并列，只能识别一个索引（获取记录最少的那个），所以需要使用复合索引，将查询最频繁的放到左边；
索引存在，如果没有满足使用原则，也会导致索引无效。
2、查询缓存query_cache
将select 的结果，存取起来供二次使用的缓存区域：

2.1、开启
开启查询缓存，通过变量控制

show VARIABLES LIKE ‘query_cache%’;
1
Variable_name Value
query_cache_limit 1048576
query_cache_min_res_unit 4096
query_cache_size 0 （缓冲区大小）
query_cache_type ON （开关）
query_cache_wlock_invalidate OFF
set GLOBAL query_cache_type = 1;

set GLOBAL query_cache_size = 1024102432;
1
2
3
Variable_name Value
query_cache_limit 1048576
query_cache_min_res_unit 4096
query_cache_size 33554432（缓冲区大小）
query_cache_type ON （开关）
query_cache_wlock_invalidate OFF
2.2、注意事项
查询缓存存在判断是严格依赖于select 语句本身的：严格保证SQL一致。

如果查询时包含动态数据，则不能被缓存。

一旦开启查询缓存，MySQL会将所有可以被缓存的select语句都缓存。如果存在不想使用缓存的SQL执行，则可以使用 SQL_NO_CACHE语法提示达到目的：

select SQL_NO_CACHE * from table where …；
1
注意：这里的缓存仅当数据表的记录改变时，缓存才会删除。而不是依靠过期时间的。

3、分区
日常开发中会经常遇到大表的情况。所谓大表是指存储了百万级乃至千万级条记录的表。这样的表过于庞大，导致数据库在插入的时候耗时太长，性能低下，如果涉及联合查询的情况，性能会更加糟糕。分表和表分区的目的就是减少数据库的负担，提高数据库的效率，通常来讲就是提高表的增删改差效率。

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

其实每个分区，就是独立的表。都要存储该分区数据的数据、索引等信息。

**创建分区：**在创建表的时候，指定分区的选项：
Create table table_name(定义)
Partition by 分区算法 （参数）分区选项。
例如 Partition by key （id） partitions 5;

create table table_name(
id int unsigned not null auto_increment,
title varchar(255),
primary key (id)
) engine=innodb
charset=utf8
partition by key (id) partitions 5;
1
2
3
4
5
6
7
采用key取余算法，根据id的值进行取余，即对5取余，然后分配到5个分区里。

注：分区与存储引擎无关，是MySQL逻辑层完成的。
可以通过变量查看当前MySQL是否支持分区：

show variables like ‘have_partitioning’;
1
3.1、分区算法
MySQL提供4种分区算法，

取余：key ，Hash
条件：list ，range
参与分区的参数字段需要为主键的一部分。

key – 取余，按照某个字段进行取余

partition by key (id) partitions 5; 分成五个区，就是对5取余。将id对5取余。

hash – 取余，按照某个表达式的值进行取余
示例：学生表分区，按照生日的月份，划分到12个表中。

– hash
create table student_hash(
id int unsigned not null auto_increment,
birthday date,
primary key (id,birthday)
)
engine = myisam
partition by hash (mouth(birthday)) partitions 12;
1
2
3
4
5
6
7
8
注意：key、hash都是取余算法，要求分区参数（括号里的），返回的数据必须为整数。

list – 条件 – 列表，需要指定的每个分区数据的存储条件。
示例：按照生日中的月份，分成春夏秋冬四个分区。

– list
create table student_list(
id int unsigned not null auto_increment,
birthday date,
primary key (id,birthday)
)
engine = myisam
partition by list (mouth(birthday)) (
partitions chun values in (3,4,5),
partitions xia values in (6,7,8),
partitions qiu values in (9,10,11),
partitions dong values in (12,1,2)
);
1
2
3
4
5
6
7
8
9
10
11
12
13
list,条件依赖的数据是列表形式。

range – 条件 – 范围，条件依赖的数据是一个条件表达式。
逻辑：按照生日的年份分成不同的年龄段。

– range
create table student_list(
id int unsigned not null auto_increment,
birthday date,
primary key (id,birthday)
)
engine = myisam
partition by range (year(birthday)) (
partitions p_70 less than (1980),
partitions p_80 less than (1990),
partitions p_90 less than (2000),
partitions p_00 less than maxvalue
);
1
2
3
4
5
6
7
8
9
10
11
12
13
3.2、分区管理与选择
取余：key，hash

增加分区数量 ： add partition partitions N
alter table student_hash add partition partitions 5;
1
减少分区数量 ：coalesce partition N
alter table student_hash coalesce partition 7;
1
采用取余算法的分区数量的修改，不会导致已有分区数据的丢失，因为会重新分配数据到新的分区。

条件：list，range

添加分区 ：
alter table student_range add partition (
partition p_new00 values less than (2010)
);
1
2
3
删除分区 ：drop partition 分区名称
alter table student_range drop partition p_00;
1
注意：删除条件算法的分区，会导致分区数据丢失。添加分区不会。

选择分区算法

平均分配：就按照主键进行key（primary key）即可。（非常常见）

按照某种业务逻辑分区：选择那种最容易被筛选的字段，整数型。

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

分表是分区之前用的，MySQL 5.1后，就开始用分区代替分表了。分表很少用了。

4.1、水平分表
创建结构相同的N个表；

– 表1
create table student_0 (
id int not null auto_increment,
name varchar(16),
primary key(id)
);
– 表2
create table student_1 (
id int not null auto_increment,
name varchar(16),
primary key(id)
);
– 表3
create table student_2 (
id int not null auto_increment,
name varchar(16),
primary key(id)
);
– 表4
create table student_3 (
id int not null auto_increment,
name varchar(16),
primary key(id)
);

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
再创建用于管理学生ID的表student_id：（该表是为了提供自增的id）

create table student_id (
id int auto_increment,
primary key(id)
);
1
2
3
4
PHP客户端逻辑

$student_table_list = array(0=>‘student_0’,1=>‘student_1’,2=>‘student_2’,3=>‘student_3’);
$studen{t}_{t}abl{e}_{c}ount=count($student_table_list);

演示以主键平均分配比例，完成人为分表

$sql_id = ‘insert into student_id values(null)’;
$student_id = last_insert_id();
$student_table = $studne{t}_{t}abl{e}_{l}ist[$student_id%$student_table_count];

$sql = “insert into $studen{t}_{t}ablevalues($student_id,‘学生姓名’)”;
1
2
3
4
5
6
7
8
9
4.2、垂直分表
一张表中存在多个字段。这些字段可以分为常用字段和非常用字段，为了提高查表速度，我们可以把这两类字段分开来存储。主要目的是减少每条记录的长度。

通常我们按照以下原则进行垂直拆分：

把不常用的字段单独放在一张表；

把text、blog等大字段拆分出来放到附表中；

经常组合查询的列放在一张表中；

例如学生表可以分成：
基础表（student_base）和额外表（student_extra），两张表中记录为1:1的关系。

基础信息表 student_base
id name age

额外信息表 student_extra
id 籍贯 政治面貌

三、架构方面
服务器架构，不仅仅是用一台MySQL。

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

1.1、为什么做主从复制
在业务复杂的系统中，有这么一个情景，有一句SQL需要锁表，导致暂时不能使用读的服务，那么就很影响运行中的业务，使用主从复制，让主库负责写，从库负责读，这样，即使主库出现了锁表的情景，通过读从库也可以保证业务的正常运行。
做热数据的备份，主库宕机后能及时替换主库，保证业务可用性。
架构的扩展。业务量越来越大，I/O访问频率过高，单机无法满足，此时做多库的存储，降低磁盘I/O访问的频率，提高单个机器的I/O性能。
1.2、主从复制的流程

①、主库db的更新事件（update、insert、delete）被写到binlog（日志文件）；
②、主库创建一个binlog dump thread（日志转储线程），把binlog的内容发送到从库；
③、从库启动并发起连接，连接到主库；
④、从库启动后，创建一个I/O线程，读取主库传过来的binlog内容并写入到relay log（中继日志）；
⑤、从库启动之后，创建一个SQL线程，从relay log 里面读取内容，从Exec_Master_Log_Pos位置开始执行读取到的更新事件，将更新内容写入到slave的db
2、读写分离
2.1、什么是读写分离
读写分离，基本原理是让主服务器处理事务性增、删、改操作（insert、delete、update），而从数据库处理 select查询操作。

数据库复制被用来把事务操作导致的变更同步到集群中的从数据库。

2.2、为什么要读写分离
因为数据库的“写” （写10000条数据到MySQL可能要3分钟）操作是比较耗时的；
但是数据库的“读” （从MySQL读10000条数据可能只要3秒钟）。

所以读写分离，解决的是，数据库的写入，影响了查询的效率

2.3、什么时候读写分离
数据库不一定要读写分离，如果程序使用数据库较多时，而更新少，查询多的情况下会考虑使用，利用数据库 主从同步 。可以减少数据库压力，提高性能。

2.4、常见的读写分离方式
基于程序代码内部实现
在代码中根据select 、 insert 进行路由分类，这类方法也是目前生产环境下应用最广泛的。

优点是性能好，因为程序在代码中实现，不需要增加额外的硬件开支。
缺点是需要开发人员来实现，运维人员无从下手。
基于中间代理层实现
代理一般介于应用服务器和数据服务器之间，代理数据库服务器接收到应用服务器的请求后根据判断后转发到 后端数据库。

只要服务器安装了 mysql proxy 或 Ameoba 软件就可以实现读写分离，该服务器会判断客户端的操作是读还是写，从而选择操作MySQL主服务器还是从服务器。

3、负载均衡
在建立数据库连接的时候，会同时与每台数据库服务器建立连接，之后针对客户端的每次请求，都会根据负载均衡算法，独立地选出某个数据库节点来执行这个请求。

负载均衡算法：

3.1、随机
随机就是没有规律的，随便从负载中获得一台，又分为完全随机和加权随机

完全随机：当有充足的请求次数时，每台服务器被请求的次数都是平均的。

优点：最简单的负载均衡算法。
缺点：因为服务器有好有坏，处理能力是不同的。当希望性能好的服务器处理更多请求的时候，就需要用到加权随机。
加权随机：也是采用的随机算法，但是每台服务器设置了权重，权重大的服务器获得的概率大一些，权重小的服务器获得的概率小一些。

3.2、轮询
轮询又分为三种：

完全轮询：比较简单，问题和完全随机一样
加权轮询：加权轮询和加权随机类似
平滑加权轮询： 平滑加权 是一种算法，根据这个算法计算每次轮询的服务器
3.3、哈希
根据某个值生成一个哈希值，然后对应到某台服务器上。（可以根据用户，请求参数等，如果根据用户，就巧妙的解决了负载均衡下session共享的问题，因为 小a永远是去 A服务器，小b永远去B服务器）

比如根据服务器的 IP，计算出IP的哈希值，然后放到哈希环里；来了一个请求，根据请求的某个值进行哈希，计算出来的哈希值在那个区间内，就按顺时针选择服务器。

哈希环：环（圆）的每个点都是由计算出的哈希值构成的

使用虚拟节点避免某个服务器故障的问题。

3.4、最小压力
最小压力负载均衡算法就是选择一台最“悠闲”的服务器。

例如：A服务器100个请求，B服务器10个请求，C服务器3个请求，那就选择C服务器。

3.5、实际情况
在实际的负载均衡下，可能会将多个负载均衡算法合在一起实现，比如先根据最小压力算法，当有几台服务器压力一样小的时候，再根据权重取出一台服务器，如果权重也一样，再随机取一台，等等。

四、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、分页
4、随机选一些数据，不要使用 Order by Rand（）