一．Mysql核心优化

1. 优化的方面

① 存储层：数据表"存储引擎"选取、字段选取、逆范式(3范式)

② 设计层：索引、分区/分表

③ 架构层：分布式部署(主从模式/共享)

④ sql语句层：结果一样的情况下，要选择效率高、速度快、节省资源的sql语句执行

2. 存储引擎

熟悉的存储引擎：Myisam、innodb

什么是存储引擎：

数据表存储数据的一种格式。

使用不同格式存储数据，不同格式的特性也是不一样的。例如innodb存储引擎的特性有：支持事务、支持行级锁、支持外键。

MySQL中的数据是通过各种不同的技术(格式)存储在文件（或者内存）中的。技术和本身的特性就称为"存储引擎"。

现实生活中，楼房、平房就是具体存储人的存储引擎，楼房、平房有自己独特的技术特性

例如楼房有楼梯、电梯、平房可以自己打井喝水等。

在进行项目开发的时候，要根据自己业务的特点，选择适合存储数据的存储引擎使用。

innodb存储引擎支持：事务、行级锁、外键

2.1 Myisam

① 结构、索引、数据文件

上图三个文件，支持物理复制、粘贴操作(就是数据的备份、还原)

给数据表添加记录信息：

此时student.MYD里边有填充数据：

② 数据存入的顺序

发现，数据的写入顺序与读出顺序保持一致。

启发：该Myisam数据表存入数据的时候，不给排序，按照写入的顺序进行存储。

这样做的好处，速度非常快。

③ 并发性

并发性稍低，多人同时做请求速度稍慢。

锁机制：每次都锁住整张数据表。

④ 压缩机制

如果一个myisam数据表存储的数据非常多，就会占据很大的硬盘空间，为了优化处理，可以把这个myisam数据表给进行压缩处理。这样节省"硬盘空间"资源。

具体压缩步骤：

A. 压缩技术：myisampack.exe 表名

B. 重建索引

根据压缩后的数据把索引重建建立起来。

重建索引的工具：myisamchk.exe -rq 表名

C. 只读特性

压缩的数据表不能再写数据了，因此对数据表进行压缩的时候，一定需要考虑好

a.有的数据表适合压缩，数据不频繁发生变化的数据适合，例如全国的邮编信息、用户的收货地址信息。

b.有的不适合，数据频繁发生变化的就不适合压缩

如果必须要对压缩的数据表进行"写"操作，就"解压"

具体解压：myisamchk.exe --unpack 表名

(解压缩的同时，索引会自动重建)

2.2 innodb

① 结构、索引、数据文件

innodb存储引擎数据表，有单独的"结构文件"

① 默认情况下，所有数据库所有innodb数据表的索引、数据文件都合并到一起

该集中的文件的数据如果遭到破坏，就不好恢复。最好给每个innodb数据表创建单独的
"数据/索引"文件。

一般该innodb数据表信息的备份、恢复要通过命令方式：

> mysqldump -uroot -p密码数据库名字 > f:/20151214.sql [备份]

> mysql -uroot -p密码数据库 < f:/20151214.sql [还原]

② 单独设置"数据/索引"文件

要为每个innodb数据单独设置其"数据/索引"文件

最后每个innodb数据表形成的两种格式文件：*.frm
数据/索引文件*.ibd

服务器重启之后，innodb_file_per_table的值要归位，不过不影响之前已经创建好的数据表结构。

③ 数据存入顺序

该innodb数据表，数据的写入顺序与存储的顺序不一致，需要按照主键的顺序把记录摆放到对应的位置上去，速度比Myisam的要稍慢。

④ 并发性

并发性高，多人同时请求，速度快、效率高。

锁机制：行锁，每次只锁住一条记录信息。

2.3 memory

内存存储引擎，

特点：内部数据运行速度非常快

缺点：服务器如果断电，就会清空该存储引擎的全部数据

字段选取

3.1 选取占据空间小的字段

int整型字段的选取

bigint int mediumint smallint tinyint

例如人的年龄适合使用tinyint类型

乌龟的年龄使用smallint类型

数据表主键id值在没有超过1600万的时候，就可以使用mediumint类型

int

3.2 内容长度固定字段

varchar(长度) 1--65535字节

内容范围：单字节内容(abc...2323..) 近65535个内容，内部要保留1-2个字节，保存内容的长度使用。

存储汉字(utf-8字符集 3个字节=一个汉字) 1--2万多汉字

char(长度) 1--255字符

单字节：每个字节等于1个字符

汉字：3个字节等于1个字符

无论单字节内容、汉字都可以存储1-255个

char() 的运行速度快，例如char(20) 实际存储16个字符，分配20个空间

varchar()运行速度少慢，例如varchar(20) 实际存储16个字符，分配16个空间

内容最好固定住长度

手机号码存储：char(11)

存储邮箱：速度快char(40) 、空间节省 varchar(40)

3.3 整型存储

时间变为整型存储：

int

date time datetime timestamp

集合:set('篮球','排球','足球','棒球')

枚举:enum('男','女','保密')

建议使用集合、枚举类型，他们底层内部使用的整型进行存储

ip地址也可以转换为整型存储。

mysql： inet_aton(ip) inet_ntoa(数字)

php: ip2long(ip) long2ip(数字)

总结：

存储引擎
myisam
innodb

myisam：写入数据非常快，适合使用场合dedecms/phpcms/discuz等写入、读取操作多的系统

innodb: 适合业务逻辑比较强的系统，例如ecshop、crm、办公系统、商城系统

4. 逆范式

数据表的总体设计要遵守三范式，但是有的时候为了整体性能的考虑，就不遵守一点，这样就称为"逆范式"

需求：查询每个分类下商品的数量

goods商品表： id name cat_id(分类id)

category分类表： cat_id cat_name

select cat_id,count(g.id) from goods g left join category c on g.cat_id=c.cat_id group by g.cat_id

以上sql语句可以按照需求获得对应的信息，但是涉及到两个表连表查询，这样查询速度不是很快，如果这样需求比较多，则会影响mysql的性能。

现在做优化，把连表查询变为"单表查询"。

数据表做修改设计：

category分类表： cat_id cat_name number(当前分类下商品总数量)

(这样后期商品每增加、减少的时候就需要特殊维护number字段内容)。

这样相同需求的sql语句就可以如下执行：

select cat_id,number from category;

以上number字段的设计不符合三范式，其是一个"多余"的字段。

4.1 三范式

一范式：字段内容确保是原子性的，不能再分割

student数据表

字段：

学生信息(字段)

张三，25，北京

姓名

年龄

地址

张三

25

北京

二范式：每个字段与当前数据表的主键有关联

三范式：每个字段与当前数据表的主键是直接关联(非间接)

5. 索引

主键索引(primary key)

索引是进行数据库设计的时候，提升性能最有效的一个技术。

5.1 什么是索引

公交车站牌是索引

办公楼指示牌是索引

从技术本身的角度看索引，索引就是一种数据结构(寝室大楼)，数据结构内部有算法，可以帮组我们快速、准确定位需要的信息。

算法：获得一些信息的时候，这些信息都是有规律、有规则(602房间)的在某个地方放着，我们可以掌握规律、规则并获得需要的数据。

5.2 是否使用索引的差别

准备一个操作的信息数据表：

(Myisam表支持物理复制、粘贴的方式还原信息)

5.3 索引为什么速度快

索引是数据存储的一种机制，该机制里边可以简单理解有索引字段、字段对应记录的物理地址。

索引字段按照一定的规律、规则组织在一起(内部有数据结构/算法),可以加快信息的查找，

信息在索引内部被查找出来后，直接对应记录的"物理地址"，根据物理地址就可以快速定位需要的记录信息。

5.4 索引创建

数据表的全部字段都可以创建索引

索引类型：

① 主键索引

② 唯一索引(字段内容不能重复)

③ 普通索引

④ 全文索引(把一个文章的内容都给创建索引)

(mysql5.6.4之前只有Myisam支持全文索引，之后 Myisam和Innodb都支持。)

复合索引：多个字段组成索引

索引名称问题：

unique key [名称] (name),

key [名称] (height),

fulltext key [名称] (introduce)

索引名称可以不设置，默认与当前索引字段名称一致。
创建数据表同时设置索引

B. 给存在的数据表增加索引

C.设置复合索引

5.5 删除索引

注意：删除主键索引，必须先去除auto_inrement属性

5.6 执行计划explain

执行计划：一条sql语句在没有执行之前，先把需要的资源都计划好，例如cpu、内存等资源的分配预计，该行为就称为"执行计划"

我们就只研究一条sql语句是否使用到"索引"。

① 使用到索引，耗费资源少，查询速度快

② 没有使用到索引，耗费资源多，查询速度慢

通俗说，只有关联到索引的sql语句，才是优化的sql语句

目前，只有"查询sql"语句支持做执行计划。

上图通过执行计划，可以看到有真实使用索引，因此执行速度会很快。

5.7 索引适合场合

注意：有的字段重复内容很多(例如性别字段)，则不要设置索引，因为他不会起作用。

① where条件后边的字段都可以设置索引

② 排序字段适合做索引

③ 索引覆盖

做数据查询，查询的字段就是索引的内容

这样索引本身就支持数据的提供，其已经不获得具体其他字段信息。

数据查询只通过索引内容就获得需要的信息，就称为"索引覆盖"

我们需要的信息(ename/deptno)，单纯从索引内部就提供好了,其不会去记录中获取其他不相关的信息，因此索引本身就满足我们对数据的获取，这样的索引和查询结合起来运行速度是最快的，也可以称为是"黄金索引"。

④ 连接查询

select from 表1 join 表2 on 表1.字段=表2.字段

上边sql语句的表1.字段也称为"外键字段"

外键字段也适合做索引。

5.8 索引原则

① 字段独立原则

select * from emp where empno=1345629; //字段独立，可以使用索引

select * from emp where empno+2=1345629; //字段不独立(不能使用索引)

② 左原则

注意：数字内容不要使用like(没有索引可用)

模糊查询的时候，有可能用到索引，也有可能不能使用索引
例如：

select * from emp where ename like "%内容%";

select * from emp where ename like "tom_"; //toma、tomb、tomtt

%符号：代表对应位置出现1或多个其他内容

_符号：代表对应位置出现1个其他内容

模糊查询左边内容固定的时候可以用到索引，否则不能使用

select * from emp where ename like "内容%"; //能用索引

select * from emp where ename like "%内容%"; //不能用索引

select * from emp where ename like "内容__"; //能用索引

select * from emp where ename like "_内容__"; //不能用索引

③ 复合索引

复合索引的第一个字段，单独作为条件可以使用到该索引。

复合索引的第二个字段，单独作为条件不可以使用到该索引

复合索引的两个字段都作为条件，则可以使用该索引

④ or原则

两边都有索引，则会全部都分别使用：

一个有索引，另一个没有索引(与顺序无关)，导致结果一个索引都没有被使用：

总结：
逆范式

不要太遵守三范式，称为"逆范式"

在分类表中记录对应商品的数目，方便后期查询

三范式：

1范式：字段设计原子型，不能分割

2范式：字段与主键id关联

3范式：字段与主键id直接关联
索引

索引就是数据结构，数据结构里边有算法

创建索引：创建表同时设置、后期给已经存在数据表设置

删除索引：

删除主键索引，先取出主键的auto_increment属性

alter table 表名 drop primary key;

alter table 表名 drop key 索引名称;

执行计划：explain

分析sql语句是否使用到索引

只针对select查询语句起作用

索引使用场合：

① where后边字段

② order by 排序

③ 索引覆盖

④ 连表查询 (外键设置索引)

索引原则

① 字段独立

② 左原则(模糊查询时)

③ 复合索引

④ or原则

作业：
练习索引的创建和删除
Mysiam和innodb数据的不同特性分析出来

巴特西

MySQL 第七天（核心优化一）

一．Mysql核心优化

1. 优化的方面

2. 存储引擎

2.1 Myisam

① 结构、索引、数据文件

② 数据存入的顺序

③ 并发性

④ 压缩机制

2.2 innodb

① 结构、索引、数据文件

② 单独设置"数据/索引"文件

③ 数据存入顺序

④ 并发性

2.3 memory

字段选取

3.1 选取占据空间小的字段

3.2 内容长度固定字段

3.3 整型存储

4. 逆范式

4.1 三范式

5. 索引

5.1 什么是索引

5.2 是否使用索引的差别

5.3 索引为什么速度快

5.4 索引创建

5.5 删除索引

5.6 执行计划explain

5.7 索引适合场合

5.8 索引原则

最新文章

热门文章

姓名	年龄	地址
张三	25	北京