大型网站核心优化之MySQL优化

Nginx

问题：输入IP地址无法访问

检查步骤：

1. 检查服务是否启动（ps -A | grep nginx） 脚下留心：nginx支持php （/php/server/php/sbin/php-fpm）
2. 检查防火墙例外是否添加（vi /etc/sysconfig/iptables service iptables stop）
3. 检查本机输入的网址是否指向linux服务器（在本机DOS窗口输入 ping 网址）

复习

集群技术/冗余技术：多台服务器实现相同的业务

负载均衡：将用户的请求分配给不同的服务处理

动静分离：将静态资源脱离项目/Public目录而是单独服务器存储

现在很多大公司都是通过对象存储存放静态资源

主从复制：一台服务器有增/改/删时候，其它服务器会自动同步数据

读写分离：通过php判断insert/update/delete开头则去主服务器，select则去从服务器

CDN加速：CDN服务器供应商在全国各个城市都有一个机房，用于后期缓存静态资源，网站老板购买了CDN后，其网站用户访问时第一次去源服务器访问并缓存到用户最近的机房，下次直接去最近的机房（节点）访问。

面试：问现在网站打开比较慢你如何优化？

• 获取网站慢SQL语句
• 读写分离、主从复制
• 开启opcode缓存
• 开启CDN加速、开启懒加载、搭建负载均衡
• 等

一、MySQL优化概述

1、基本概念

上一讲说了主从复制、读写分离都是从查询角度优化、但最终必不可免的还是得操作数据库、思考如何从设计数据库角度优化？

2、优化策略

架构：读写分离、主从复制

设计：三范式、存储引擎、字段类型

功能：索引、分区、缓存

二、MySQL表的设计（三范式）

1、概念

为了建立冗余较小、结构合理的数据库，设计数据库时必须遵循一定的规则（注：这个规则在关系型数据库中称之为三范式）

脚下留心：有些时候一味追求范式减少冗余，反而降低读写效率，这时候可以反三范式，利用空间来换时间。

概况

什么是三范式：就是设计数据库的规则

三范式的好处：减少冗余，加速读写数据

反三范式：不按规则出牌，通过冗余策略以空间换时间

2、确保每列/字段保持原子性（1NF）

1）概念

• 每列字段不可以再分

举例

• 案例：地区表

id（编号） address（地址） ? 不符合因为地区可以拆分成：省市区，一个字段后期可扩展性差

1 北京北京朝阳区

2 上海上海浦东新区

3 江苏泰州海陵区

2、有主键，非主键字段依赖主键（2NF）

1）概念

• 每个表必须有一个主键自增字段
• 一个表只描述一件事

2）举例

• 案例1：用户表

#用户表

id（用户编号） username（用户名） password（密码） 刚不符合因为一个表只描述一件事

#收货地址表

address（收货详细地址） tel（联系人电话） 用户编号

• 案例2：订单表

订单编号 订单总价 订单总数量 所属用户 产品ID 产品名称 产品单价 …

1 20 2 1 1 xxx 10

2 20 2 1 1 xxx 10

订单（主）

订单编号 订单总价 订单总数量 所属用户

订单（从） 产品

ID 订单主表编号 产品ID 产品单价 ………

3、消除传递依赖，非主键字段不能相互依赖（3NF）

1）概念

• 一个表不能包含其他表的非主键字段（一个表只能包含其他表的主键）

2）举例

#文章表

文章编号 文章标题 文章内容 用户ID 用户姓名 （文章表不能包含其他表的非主键字段，所以用户名移除，后期需要获取文章的作者，直接通过多表查询即可）

#用户表

用户ID 用户姓名 用户年龄 …

4、逆范式

1）概念

符合范式设计是为了减少冗余提高读写效率，但有时候我们需要反其道而为之，利用空间来换时间（数据冗余策略），该设计被称之为“逆范式”

2）举例

• 案例1：

【场景】查看文章详情，评论总数

【表】

#文章表

编号 标题 内容 评论总数…

#评论表

编号 所属用户 所属文章 评论内容 ….

正常流：select count(*) from 评论表 where article_id = 文章ID

逆范式：再文章表增加一个字段（评论总数），每次用户评论成功后，给文章表的评论总数字段+1

• 案例2：

收货地址表

编号 姓名 省 市/区 乡镇 详细地址 ….

1 你爸爸 阿富汗 xxx xxx

2 杨文杰

3 娇娇

订单（主表）

订单ID 总价 数量 订单状态 支付状态 收货地址表编号… （不合理，因为后期用户在发货的时候删除或者修改，再找商家麻烦，问题就大了，解决采用冗余策略）

1 1000000000 1 已发货，待收货 已支付 1

订单（从表）

商品ID 商品名称 商品价格 …

订单（收货地址表）

姓名 省 市/区 乡镇 详细地址

︴总结

• 什么是三范式：就是设计表的规则
• 三范式好处：减少冗余，提高读写效率
• 三范式有哪些：

1. 每个字段不可以拆分
2. 每个表必须有主键自增、每个表只描述一件事
3. 一个表不能包含其他表的非主键字段

• 逆范式：反三范式规则、以空间换时间（冗余策略）

︴说明

前期别想太多根据页面显示数据设计表（一般一个显示对应一个字段）

其次常用字段推荐：

1）编号，主键自增

2）创建于，便于后期数据赛选统计

3）更新于，便于后期数据赛选统计

4）是否显示，便于逻辑删除数据

三、存储引擎

1、什么是存储引擎

• 说明：

MySQL中按照一定的数据格式来保存最终的数据，通过存储引擎来指定哪种方式存储方案。

• 常用的存储引擎：innodb 和 myisam
• 查看当前MySQL支持的存储引擎列表：show engines;

2、myisam存储引擎

1）概念

<= 5.5 MySQL默认的存储引擎。

如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且

对事务的完整性要求不是很高；其优势是访问的速度快，擅长与处理高速读与写。

应用场景

对事务完整性要求不高，不涉及钱的表

3）存储方式

• 创建数据库：create database d1;
• 说明：创建数据库会在MySQL的data目录下创建同名文件夹

• 创建myisam表：
• create table t1(id int primary key auto_increment)engine=myisam charset=utf8;

3、innodb存储引擎

1）概念

>=5.5 默认的存储引擎，MySQL推荐使用的存储引擎。

提供事务，行级锁定，外键约束的存储引擎。

事务安全型存储引擎。更加注重数据的完整性和安全性。

2）应用场景

订单、账户、积分等

3）存储方式

• 创建innodb存储引擎数据库：
• create table t2 (id int primary key auto_increment)engine=innodb charset=utf8;

多学一招：

myisam因为结构、索引、数据都存放在数据库文件夹下，所以可以直接复制迁移，而innodb都存在ib_logfile数字文件中，在存储的时候记录了使那个数据的表，所以后期无法迁移。

Archive存储引擎（存档型）

1）说明

该类型只能插入和读取不能修改和删除。

举例

应用场景

网站管理员操作日志

5、Memory存储引擎（内存型）

简单了解，该类型将数据保存在内存（后期通过通过memcache、redis软件代替）

内存存储 > 文件缓存 > 数据库存储

6、MyIsam和InnoDB存储引擎区别（面试笔试）

• 存储结构：myisam三个文件，innodb一个文件
• 锁：myisam表级锁，innodb可表可行级锁
• 事物和外键支持：innodb都支持，myisam都不支持
• CURD操作：

MyISAM：如果执行大量的SELECT，MyISAM是更好的选择。

InnoDB：如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表。DELETE 从性能上InnoDB更优，但DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除，在innodb上如果要清空保存有大量数据的表，最好使用truncate table这个命令。

• 全文检索：MySQL5.6以下innodb不支持（都不支持中文）

列类型的选择

概念

1）“字节”的定义

• 字节（Byte）是一种计量单位，表示数据量多少，它是计算机信息技术用于计量存

储容量的一种计量单位。

1字节（Byte）= 8位（bit）

1KB = 1024Byte（字节）

1MB = 1024KB

1GB = 1024MB

1TB = 1024GB

2）字节和字符的区别

https://zhidao.baidu.com/question/454436588167256525.html

英文：1个字符 = 1个字节

中文：1个字符 = 等于3个字节

MySQL类型字符和字节相关细节

整型 字节

char 范围：字符 括号中的数字：字符

varchar 范围：字节 括号中的数字：字符

1、为什么要学习列类型优化

以前的时候都是直接凭感觉选择字段，这样就造成了两个问题

1）浪费磁盘空间

2）降低读写效率

2、整型

1）说明

︴案例

• 存储人的年龄应该使用哪种数据类型？

答：tinyint（3）

整型括号中的数字仅仅只有增加zerofill属性时才生效，表示插入数据如果不够宽度则补零

误区1：tinyint（1） 不能插入1位以上数字 错

误区2：tinyint（3） 占1个字节

年龄：tinyint（3）

性别：tinyint（1）

声明如果没有明确zerofill属性则两者没有区别，但是为什么年龄写3，性别写1呢？目的：增强代码可读性

• 存储乌龟的年龄应该使用哪种数据类型？（千年王八万年龟）

答：smallint

• 存储一个1500万数据的数据表其主键id应该选择哪种数据类型？

答：int

3、字符串型

1）说明

char 固定字符串类型（0-255个字符）

varchar 可变字符串类型（0-65535个字节） utf8字符：65535/3=21845

char（字符）

varchar（字符） utf8=21845个字符 gbk=32767个字符

字符表示：任意中文、数字、英文组合插入不能超过指定字符即可，如：char（3）表示可以插入任意3个英文、数字、中文组合。

︴案例

• 存储11位手机号码，选择？

答：char(11） 说明：从效率占用空间选择bigint，但是从可扩展性选择char

bigint 8个字节 （int不能4个字节，最大长度10位）

char(11) 代表11个字符，utf8=因为数字11个字节

• 存储md5加密的密码，选择？

答：char（32） 因为php的md5加密32位

http://www.cmd5.com/ 在线破解

• 存储标题或姓名，选择？

答：varchar（45）

标题推荐80有利于seo优化（seo优化指：在百度输入你的名字可以找到你）

http://seo.chinaz.com/?q=www.itcast.cn

• 存储描述信息（不超过250个汉字），选择？

答：varchar(200）

• 存储文章内容，选择？

答：text

时间类型

数据类型	描述	格式	占用字节
datetime	日期时间	年-月-日 小时:分钟:秒	8
date	日期	年-月-日	4
time	时间	小时:分钟:秒	3
year	年份	年	1
timestamp	时间戳	年-月-日 小时:分钟:秒	4

• 在实际工作中推荐使用int类型

原因1：空间（int占4 datetime占8个字节）

原因2：格式（2013年11月、2013/11/11、2013-11-4、【刚刚 1分钟前 一年前】）

多说两句

1. 任然有部分小公司使用datetime类型
2. int类型最大存储时间2038年

5、枚举类型与集合类型

在实际应用中，如果程序中有单选或多选情况，不建议使用varchar类型，而建议使用枚举类型或集合类型 或 用整型代替。

enum: 枚举单选 enum(‘男’, ‘女’, ‘保密’)

set: 集合多选 set(‘语文’, ‘英语’, ‘物理’)

说明：实际工作中推荐使用int

原因：因为enum是mysql独有的，可扩展想不强（不能迁移）

性别：tinyint（1） 性别:1-男,2-女

场景：查询user表所有男生

以前：select * from user where sex = 1;

框架：D(‘user’)->where(‘sex = 1’)->select();

优化：

步骤1：在模型中定义常量

const SEX_MAN = 1；

const SEX_WOMEN = 2;

步骤2：查询

D(‘user’)->where(‘sex = ‘.USER::SEX_MAN)->select();

<?php

//订单类

class Order

{

const STATE_WAIT_TO_PAY = 1; // 待支付

const STATE_PAID_SUCCESS = 2; // 已支付（成功）

const STATE_PAID_TIMEOUT = 5; // 已支付（超时）

const STATE_PAID_CANCEL = 8; // 已付款（但在通知到来前取消）

const STATE_TIMEOUT = 7; // 超时未支付

const STATE_CANCELED = 6; // 已取消

const STATE_REFUNDED = 9; // 已退款

}

state：1-xxx,2-xxxx,3-xxx

select * from Order where state = 2 需要打开数据库看

select * from Order where state = Order::STATE_WAIT_TO_PAY 增强代码可读性

6、数值型（小数）

• decimal、float、double都是小数，decimal相对后两个更精确但是占用空间
• 未涉及到资金小数可以用浮点型（float或double） 优先用float
• 涉及资金用decimal（注：decimal小数位一般2位如果没有小数会用0自动补充）
• decimal（10, 2）

7、IP类型数据的存储

• ip选择整型

XXX.XXX.XXX.XXX char（15字符串 15字节）

ip2long

long2ip int 4字节

五、慢查询日志（查询需要优化的SQL语句）

1、作用

思考：新人加入，项目中编写SQL语句可能存在问题，问如何检查

解决：通过慢查询日志（在MySQL中修改配置文件，从而可以记录查询找过指定是按的SQL语句）

2、测试SQL语句

#创建表

create table php_slow_log2 (

name varchar(300),

name2 varchar(300)

)engine=innodb charset=utf8;

#插入数据

insert into php_slow_log2 values

(‘zzzzzz’,’zzzzzz’),

(‘zzzzzz’,’zzzzzz’),

(‘zzzzzz’,’zzzzzz’),

(‘zzzzzz’,’zzzzzz’);

#Mysql蠕虫复制

insert into php_slow_log2(name,name2) select name,name2 from php_slow_log2;

insert into php_slow_log2(name,name2) select name,name2 from php_slow_log2;

insert into php_slow_log2(name,name2) select name,name2 from php_slow_log2;

insert into php_slow_log2(name,name2) select name,name2 from php_slow_log2;

insert into php_slow_log2(name,name2) select name,name2 from php_slow_log2;

insert into php_slow_log2(name,name2) select name,name2 from php_slow_log2;

insert into php_slow_log2(name,name2) select name,name2 from php_slow_log2;

insert into php_slow_log2(name,name2) select name,name2 from php_slow_log2;

insert into php_slow_log2(name,name2) select name,name2 from php_slow_log2;

3、实际操作

1） 使用如下指令查看慢查询日志是否开启

2） 手工开启慢查询日志

3） 设置查询的临界时间（查询超过指定时间后纪录）

4） 使用查询语句（超时）

︴总结

步骤1：开启慢查询日志

set global slow_query_log = 1;

查看是否开启：show variables like ‘slow_query%’;

步骤2：设置时间

set global long_query_time = 2; #注：2秒

查看时间：show variables like ‘%query_time’;

步骤3：关闭当前DOS窗口重新登录执行慢SQL语句，查看文件是否记录即可

六、数据库设计中的索引

1、什么是索引

索引相当于书目录，加快访问速度

2、现实生活中的索引

3、索引的好处

• 好处：加快访问速度
• 坏处：

1. 索引占用磁盘空间
2. 索引会影响SQL语句执行速度，因为增加/修改都需要更新索引

索引可以加快查询效率，但是也不能滥用索引，否则适得其反

4、四种索引形式

普通索引（index） – 仅仅为了加快查询速度

唯一索引（unique） – 保证数据唯一性

主键索引（primary key） – 既保证数据唯一不能为null

全文检索（fulltext） – 提取指定字段的关键字，添加索引

组合索引 – 同时给两个字段设置相同的索引

5、建表时创建索引

#【需求】

编号 – 主键索引

姓名 – 唯一索引

密码 – 普通索引

a和b – 复合/组合索引

info – 全文索引

#【创建索引表】

create table stu1(

id int unsigned auto_increment,

uname varchar(30) not null comment ‘用户名’,

pwd char(32) not null comment ‘密码’,

a varchar(30) not null,

b varchar(30) not null,

info text,

？？？？？

)engine=myisam charset=utf8;

#【查看索引表】

create table stu1(

id int unsigned auto_increment,

uname varchar(30) not null comment ‘用户名’,

pwd char(32) not null comment ‘密码’,

a varchar(30) not null,

b varchar(30) not null,

info text,

primary key(id),

unique (uname),

index (pwd),

index (a,b),

fulltext (info)

)engine=myisam charset=utf8;

修改表的结构增加索引 和 删除索引

1）需求

create table user2 (

id int unsigned ,

uname varchar(30) not null comment ‘姓名’,

pwd char(32) not null comment ‘密码’,

a varchar(10) not null comment ‘a’,

b varchar(10) not null comment ‘b’,

info text

)engine=MyISAM charset=utf8;

2）语法

• 修改表添加索引：alter table 表名 add 索引类型 索引名(待添加索引字段)
• 删除表指定索引：drop index 索引名 on 表名

索引名字推荐使用字段名+索引名

3）SQL语句

#【需求】

编号 – 主键索引

姓名 – 唯一索引

密码 – 普通索引

a和b – 复合/组合索引

info – 全文索引

#【创建索引】

alter table user2 add primary key id(id);

alter table user2 add unique uname(uname);

alter table user2 add index pwd(pwd);

alter table user2 add index a_b(a,b);

alter table user2 add fulltext info(info);

#【删除索引】

7、创建索引原则

用于频繁搜索的列

用于排序的字段

做条件查询的列

给字段数据尽量不重复的字段添加索引，如：编号

七、千万级数据量根据索引优化查询速度

八、MySQL中的执行计划

SQL

#创建数据库

create database php666;

#选择数据库

use php666;

#创建表

create table stu (

id int unsigned primary key auto_increment comment ‘编号’,

name varchar(10) not null comment ‘姓名’,

sex char(2) not null default ‘男’ comment ‘性别’,

age tinyint(3) not null default 0 comment ‘年龄’,

money decimal(10,2) not null default 0 comment ‘资金’,

address varchar(200) default ” comment ‘地址’

)engine=myisam charset=utf8;

#学生数据插入

insert into stu

values

(null,’小泽’,’男’,18,30.56,’北京’),

(null,’苍苍’,’男’,40,80,’日本’),

(null,’小白’,’男’,30,10,’哈尔滨’),

(null,’小黑’,’男’,18,76,’黑龙江’),

(null,’小弟弟’,’女’,18,76,’北京’),

(null,’老陆’,’女’,88,44,’上海’),

(null,’校长’,’男’,100,20,’上海’);

#分数表

create table score (

stu_id int unsigned comment ‘关联学生表ID’,

id int unsigned primary key auto_increment comment ‘分数表ID’,

math int unsigned not null comment ‘数学’,

chinese int unsigned not null comment ‘语文’,

english int unsigned not null comment ‘英语’

)engine=myisam charset=utf8;

#分数数据插入

insert into score

values

(1,null,80,58,99),

(2,null,11,99,29),

(3,null,32,53,89),

(4,null,99,28,99),

(5,null,77,68,19),

(6,null,33,18,29);

1、什么是MySQL中执行计划

就是通过explain关键词分析SQL语句

2、explain语法

使用：直接explain后面写SQL语句

语法：explain SQL语句 \G

说明：\G后面不需要加分号

3、使用explain执行计划分析

1）说明

select_type – 查询类型

table – 表名

type – 连接所使用的类型

possible_keys – 可能使用的索引

key – 实际使用索引

key_len – 索引长度

rows – 可能需要检索的行数

2）分析，查询类型（select_type）

#情况1：simple

#情况2：subquery

#情况3：derived

#情况4：union

3）分析，表名（table）

#情况1：实际表名

#情况2：表别名

#情况3：derived （from型子查询）

#情况4：null

4）分析，额外项（extra）

index: 是指用到了索引覆盖,效率非常高

using where 是指光靠索引定位不了,还得where判断一下

extra 中出现以下 2 项意味着 MYSQL 根本不能使用索引,效率会受到重大影响。应尽可能对此进行优化

using temporary

表示 MySQL 在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。

using filesort

表示 MySQL 会对结果使用一个外部索引排序,而不是从表里按索引次序读到相关内容。可能在内存或者磁盘上进行排序。MySQL 中无法利用索引完成的排序操作称为“文件排序”

5）分析，连接所使用的类型（type）【重要的项分析】

• 这一列的常见值有：const、all、range、system、index

最好的是 system ， 一般在表中只有一行记录的或者查询系统表的时候出现。

其次就是 const， 一般在使用主键索引的时候会出现。

其其次range， 一般在做范围查询的时候会使用 id < 100。

index， 代表可以使用索引做一些优化，一般在查询表的总行数的时候出现。

all， 一般使用不上索引的时候出现。

ref， 意思是指 通过索引列,可以直接引用到某些数据行

eq_ref， 意思是指 通过索引列,可以直接引用某1行数据

• 场景

#情况1：system

#情况2：const

#情况3：range

#情况4：all

4、索引的使用原则（哪些常见情况不能用索引?）

说明

• like查询（“%”和“_”开头）索引失效 （sphinx网站中全站检索）
• or运算都要具有索引否则索引失效
• where条件字符串必须加引号
• 组合索引字段单独使用，左边生效，右边失效（左原则）

验证

• like查询（“%”和“_”开头）索引失效 （sphinx网站中全站检索）

• or运算都要具有索引否则索引失效

• where条件字符串必须加引号

• 组合索引字段单独使用，左边生效，右边失效（左原则）

总结

MySQL如何优化？

架构：读写分离、主从复制

设计：三范式、逆范式、存储引擎的选择、字段类型

功能：索引、缓存、分区

——————————

什么是三范式：就是设计表的规则

三范式好处：减少冗余，提高读写效率

三范式有哪些：

1）每个字段不可以拆分

2）每个表必须有主键自增、每个表只描述一件事

3）一个表不能包含其他表的非主键字段

逆范式：反三范式规则、以空间换时间（冗余策略）

多学一招：强烈推荐一个表有哪些基本字段（id、created_at、updated_at、display:1-显示,2-隐藏）

————————————–

存储引擎：myisam 、 innodb 、 archive 、 memory

myisam和innodb区别

存储结构：myisam三个文件，innodb一个文件

锁：myisam表级锁，innodb可表可行级锁

事物和外键支持：innodb都支持，myisam都不支持

CURD操作：

MyISAM：如果执行大量的SELECT，MyISAM是更好的选择。

InnoDB：如果你的数据执行大量的INSERT或UPDATE，出于性能方面的考虑，应该使用InnoDB表。DELETE 从性能上InnoDB更优，但DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除，在innodb上如果要清空保存有大量数据的表，最好使用truncate table这个命令。

全文检索：MySQL5.6以下innodb不支持（都不支持中文）

——————————————–

什么是索引：相当于书的目录，加快访问速度

索引的好处：加快访问速度

瑕疵：占用磁盘空间，影响读写速度，因为需要更新索引

创建索引：alter table 表名 add 索引类型 索引名（字段名）;

删除索引：drop index 索引名 on 表名

——————————————-

普通索引（index） – 仅仅为了加快查询速度

唯一索引（unique） – 保证数据唯一性

主键索引（primary key） – 既保证数据唯一不能为null

全文检索（fulltext） – 提取指定字段的关键字，添加索引

组合索引 – 同时给两个字段设置相同的索引

——————————————-

什么是MySQL中的执行计划：通过explain关键字分析SQL语句

索引使用规则

like查询（“%”和“_”开头）索引失效 （sphinx网站中全站检索）

or运算都要具有索引否则索引失效

where条件字符串必须加引号

组合索引字段单独使用，左边生效，右边失效（左原则）