Mysql优化上

#

一. Mysql数据库优化

1. 优化概述

存储层:存储引擎、字段类型选择、范式设计

设计层:索引、缓存、分区(分表)

架构层:多个mysql服务器设置,读写分离(主从模式)

sql语句层:多个sql语句都可以达到目的的情况下,要选择性能高、速度快的sql语句

2. 存储引擎

什么是存储引擎:

我们使用的数据是通过一定的技术存储在数据当中的,数据库的数据是以文件形式组织的硬盘当中的。技术不只一种,并且每种技术有自己独特的性能功能体现。

存储数据的技术和其功能的合并就称为”存储引擎”。

在mysql中经常使用的存储引擎:Myisam或Innodb等等。


数据库的数据存储在不同的存储引擎里边,所有的特性就与当前的存储引擎有一定关联。

需要按照项目的需求、特点选择不同的存储引擎。

查看mysql中支持的全部存储引擎:


2.1 innodb

数据库每个数据表的数据设计三方面信息:表结构、数据、索引


技术特点:支持事务、行级锁定、外键

1)表结构、数据、索引的物理存储

创建一个innodb数据表:


表结构文件:


该类型 数据、索引 的物理文件位置:

所有innodb表的数据和索引信息都存储在以下ibdata1文件中


给innodb类型表 的数据和索引创建自己对应的存储空间:

默认情况下每个innodb表的 数据和索引 不会创建单独的文件存储


设置变量,使得每个innodb表有独特的数据和索引 存储文件:


重新创建order2数据表:


此时order2数据表有单独的数据和索引存储文件:

(后期无论innodb_file_per_table的设置状态如何变化,order2的数据和索引都有独立的存储位置)


数据存储顺序

</div>

innodb表数据的存储是按照主键的顺序排列每个写入的数据。 该特点决定了该类型表的写入操作较慢

3)事务、外键

该类型数据表支持事务、外键 事务:把许多写入(增、改、删)的sql语句捆绑在一起,要么执行、要么不执行 事务经常用于与"<span style="color: blue;">钱</span>"有关的方面。 四个特性:原子、一致、持久、隔离 具体操作: <span style="color: blue;">start transaction; </span>

许多写入sql语句

<span style="font-family: 宋体; font-size: 12pt;"><span style="color: blue;">sql语句有问题 </span>

<span style="font-family: 宋体; font-size: 12pt;"><span style="color: blue;">rollback;回滚 </span>

<span style="color: blue;">commit;提交 </span>rollback和commit只能执行一个 外键:两个数据表A和B,B表的主键是A表的<span style="color: blue;">普通字段</span>,在A表看这个普通的字段就是该表的"外键",外键的使用有"约束"。 约束:以上两个表,必须先写B表的数据,再写A表的数据

并且 A表的外键取值必须来之B表的主键id值,不能超过其范围。

真实项目里边很少使用"外键",因为有约束。

4) 并发性

该类型表的并发性非常高 多人同时操作该数据表 为了操作数据表的时候,数据内容不会随便发生变化,要对信息进行"锁定" 该类型锁定级别为:<span style="color: blue;">行锁</span>。只锁定被操作的当前记录。

2.2 Myisam

#### 1) 结构、数据、索引独立存储

该类型的数据表 表结构、数据、索引 都有独立的存储文件: 创建Myisam数据表 ![](http://www.cuidmm.cn/wp-content/uploads/2016/10/100516_1253_Mysql14.png)*.frm:表结构文件 *.MYD:表数据文件 *.MYI:表索引文件 ![](http://www.cuidmm.cn/wp-content/uploads/2016/10/100516_1253_Mysql15.png)

每个myisam数据表的 结构、数据、索引 都有独立的存储文件

特点:独立的存储文件可以单独备份、还原。

2) 数据存储顺序

myisam表数据的存储是按照自然顺序排列每个写入的数据。


该特点决定了该类型表的写入操作较快

3) 并发性

该类型并发性较低

该类型的锁定级别为:表锁

4)压缩机制

如果一个数据表的数据非常多,为了节省存储空间,需要对该表进行压缩处理。

复制当前数据表的数据:


不断复制使得order3数据表的数据变为200多万条:


对应的存储该200万条信息的文件的物理大小为40多兆:


开始压缩order3数据表的数据

压缩工具:myisampack.exe 表名


重建索引:myisamchk.exe -rq 表名


解压缩工具:myisamchk.exe –unpack 表名


order3表信息被压缩的60%的空间:


order3数据表有压缩,但是索引没有了:


重建索引:


索引果然被重建完毕:


刷新数据表:flush table 表名


出现情况:

压缩的数据表是只读表,不能写信息:


压缩的数据表有特点:不能频繁的写入操作,只是内容固定的数据表可以做压缩处理

存储全国地区信息的数据表

收货地址信息数据表

如果必须要写数据:就解压该数据表,写入数据,再压缩

解压order3数据表,使得其可以写入数据:

(解压同时索引自动重建)


数据解压完毕:


执行flush操作,更新解压后的数据:flush table 表名;

该操作同时会删除order3.MYD.00996D46.deleted的压缩备份文件


此时允许给order3继续写入数据:


innodb存储引擎:适合做修改、删除

Myisam存储引擎:适合做查询、写入

3.3 Archive

归档型存储引擎,该引擎只有写入、查询操作,没有修改、删除操作

比较适合存储”日志”性质的信息。

3.4 memory

内存型存储引擎,操作速度非常快速,比较适合存储临时信息,

服务器断电,给存储引擎的数据立即丢失。

3. 存储引擎的选择

Myisam和innodb

网站大多数情况下”读和写”操作非常多,适合选择Myisam类型

例如 dedecms、phpcms内容管理系统(新闻网站)、discuz论坛

网站对业务逻辑有一定要求(办公网站、商城)适合选择innodb

Mysql5.5默认存储引擎都是innodb的

4. 字段类型选择

4.1 尽量少的占据存储空间

int整型

年龄:tinyint(1) 0-255之间

乌龟年龄: smallint(2)

mediumint(3)

int(4)

bigint(8)


时间类型date

time() 时分秒

datetime() 年月日 时分秒

year() 年份

date() 年月日

timestamp() 时间戳(1970-1-1到现在经历的秒数)

根据不同时间信息的范围选取不同类型的使用

4.2 数据的整合最好固定长度

char(长度)

固定长度,运行速度快

长度:255字符限制

varchar(长度)

长度不固定,内容比较少要进行部位操作,该类型要保留1-2个字节保存当前数据的长度

长度:65535字节限制

存储汉字,例如字符集utf8的(每个汉字占据3个字节),最多可以存储65535/3-2字节

存储手机号码:char(11)

4.3 信息最好存储为整型的

时间信息可以存储为整型的(时间戳)

select from_unixstamp(时间戳) from 表名

set集合类型 多选:set(‘篮球’,’足球’,’棒球’,’乒乓球’);

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

推荐使用set和enum类型,内部会通过整型信息参数具体计算、运行。

ip地址也可以变为整型信息进行存储(mysql内部有算法,把ip变为数字):


mysql: inet_aton(ip) inet_ntoa(数字)

php: ip2long(ip) long2ip(数字)

总结:

  1. 存储引擎

    数据存储技术格式 Myisam innodb
  2. 字段类型选择

    原则:占据空间小、数据长度最好固定、数据内容最好为整型的

5. 逆范式

数据库设计需要遵守三范式。

两个数据表:商品表Goods、分类表Category

Goods: id name cat_id price

101 iphone6s 2003 6000

204 海尔冰箱 4502 2000

……

Category: cat_id name goods_num

2003 手机

4502 冰箱

…..

需求:

计算每个分类下商品的数量是多少?

select c.cat_id,c.name,count(c.*) from category as c left join goods as g on g.cat_id=c.cat_id;

上边sql语句是一个多表查询,并且还有count的聚合计算。

如果这样的需求很多,类似的sql语句查询速度没有优势,

如果需要查询速度提升,最好设置为单表查询,并且没有聚合计算。

解决方法是:给Category表增加一个商品数量的字段goods_num

那么优化后的sql语句:

select cat_id,name,goods_num from category;

但是需要维护额外的工作:goods商品表增加、减少数据都需要维护goods_num字段的信息。

以上对经常使用的需求做优化,增加一个goods_num字段,该字段的数据其实通过goods表做聚合计算也可以获得,该设计不满足三范式,因此成为”逆范式“.

三范式:

① 一范式:原子性,数据不可以再分割

② 二范式:数据没有冗余

order goods

ida 编号1 下单时间 商品信息1 商品价格
商品描述
商品产地

idb 编号1 下单时间 商品信息2 商品价格 商品描述 商品产地

idb 编号1 下单时间 商品信息3 商品价格 商品描述 商品产地

订单表 id 编号1 下单时间 g1,g2,g3

③ 三范式

数据表每个字段与当前表的主键产生直接关联(非间接关联)

userid name height weight orderid 编号 订单时间

优化:

userid name height weight

userid orderid

orderid 编号 订单时间

6. 索引index

索引是优化数据库设计,提升数据库性能非常显著的技术之一。

各个字段都可以设计为索引,经常使用的索引为主键索引primary key

索引可以明显提升查询sql语句的速度

6.1 是否使用索引速度的差别

直接复制文件到数据库文件目录:


被复制到shop0407的数据库文件目录里:


数据库有体现emp数据表:


对一个没有索引的数据表进行数据查询操作:


没有索引,查询一条记录消耗1.49s的时间:


一旦设置索引,再做数据查询,时间提升是百倍至千倍级的:


6.2 什么是索引

索引本身是一个独立的存储单位,在该单位里边有记录着数据表某个字段和字段对应的物理空间。

索引内部有算法支持,可以使得查询速度非常快。


有了索引,我们根据索引为条件进行数据查询速度就非常快

① 索引本身有”算法“支持,可以快速定位我们要找到的关键字(字段)

② 索引字段物理地址有直接对应,帮助我们快速定位要找到的信息

一个数据表的全部字段都可以设置索引

6.3 索引类型

四种类型:

① 主键 primary key

auto_increment必须给主键索引设置

信息内容要求不能为null,唯一

② 唯一 unique index

信息内容不能重复

③ 普通 index

没有具体要求

④ 全文 fulltext index

myisam数据表可以设置该索引

复合索引:索引关联的字段是多个组成的,该索引就是复合索引


1) 创建索引

创建:① 创建表时

创建一个student数据表,并设置各种索引:


查看student表结构可以看到各种索引是成功的:

show create table student;


② 给现有的数据表添加索引:




创建一个复合索引:

索引没有名称,默认把第一个字段取出来当做名称使用。



删除索引

</div>

alter table 表名 drop primary key; //删除主键索引 注意:该主键字段如果存在auto_increment属性,需要先删除之 alter table 表名 modify 主键 int not null comment ‘主键’; 去除数据表主键字段的auto_increment属性: 禁止删除主键,原因是内部有auto_increment属性: alter table 表名 drop index 索引名称; //删除其他索引(唯一、普通、全文) 删除主键: 删除其他索引: 此时数据表没有任何索引:

6.4 执行计划explain

针对<span style="color: blue;">查询语句</span>设置执行计划,当前数据库只有查询语句支持执行计划。 每个select查询sql语句执行之前,需要把该语句需要用到的各方面资源都计划好 例如:cpu资源、内存资源、索引支持、涉及到的数据量等资源 查询sql语句真实<span style="color: blue;">执行之前</span>所有的资源计划就是<span style="color: blue;">执行计划</span>。 我们讨论的执行计划,就是看看一个查询sql语句是否可以使用上索引。 具体操作: <span style="color: blue;">explain 查询sql语句\G</span>;
  1. sql语句在没有执行之前,可以看一下执行计划。

主键索引删除后,该查询语句的执行计划就没有使用索引(执行速度、效率低)

6.5 索引适合场景

1) where查询条件

where 之后设置的查询条件字段都适合做索引。

2) 排序查询

order by 字段 //排序字段适合做索引 排序字段没有索引,做排序查询就没有使用: 给排序字段设置索引,做排序查询就会使用: where 和 order by后边的条件字段都可以适当设置索引

3) 索引覆盖

给ename和job设置一个复合索引: 索引覆盖:我们查询的全部字段(ename,job)已经在索引里边存在,就直接获取即可

不用到数据表中再获取了。因此成为”索引覆盖”

该查询速度非常快,效率高,该索引也称为”黄金索引”

索引本身需要消耗资源的(空间资源、升级维护困难):

4) 连接查询

join join on goods : id name cat_id … category: cat_id name … 在Goods数据表中给外键/约束字段cat_id设置索引,可以提高联表查询的速度

6.6 索引原则

1)字段独立原则

select * from emp where empno=1325467; //empno条件字段独立 select * from emp where empno+2=1325467; //empno条件字段不独立 只有独立的条件字段才可以使用索引 独立的条件字段可以使用索引: 不独立的条件字段不给使用索引:

2) 左原则

模糊查询,like % _ %:关联多个模糊内容 : 关联一个模糊内容 select from 表名 like “beijing%”; //使用索引 select from 表名 like “beijing“; //索引索引 查询条件信息在左边出现,就给使用索引 XXX% YYY_ 使用索引 %AAA% ABC %UUU 不使用索引 没有使用索引(中间条件查询):

3) 复合索引

ename复合索引 内部有两个字段(ename,job) ① ename(前者字段)作为查询条件可以使用复合索引 ② job(后者字段)作为查询条件不能使用复合索引 复合索引的第一个字段可以使用索引: 复合索引的其余字段不能使用索引: 如果第一个字段的内容已经确定好,第二个字段也可以使用索引:

4) OR原则

OR左右的关联条件必须都具备索引 才可以使用索引: or的左右,只有一个有索引,导致整体都没有的使用: 总结:

  • 逆范式
  • 索引

    索引是数据表的某个字段作为关键字,该关键字与信息的物理地址进行对应 通过索引查找信息,内部有算法,速度有保证,索引信息找到就找到记录的物理地址 进而获得该记录信息 索引类型:主键、唯一、普通、全文 创建:创建数据表时设置索引 给已有数据表设置索引 删除:alter table 表名 drop primary key;

//删除主键,需要先删除auto_incremenet

<span style="font-family: 宋体; font-size: 12pt;">alter table 表名 drop index 索引名

  • exlain执行计划

    针对 查询sql语句 可以设置执行计划
  • 索引适合场景:where、order by、索引覆盖、连接查询(约束字段)
  • 索引使用原则:左原则、字段独立、复合索引、or原则

作业:

  • 练习索引的设置(创建、删除)
  • 存储引擎的特点(Myisam/innodb)