MySQL 第七天（核心优化一）
一．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数据的不同特性分析出来
