【MYSQL】 mysql优化

1. Myisam和Innodb存储引擎特点

Myisam

表结构、数据、索引分别有对应的存储文件

写入数据非常快，安装自然顺序写入数据

数据稳定后可以压缩数据信息

支持全文索引

并发性：少低，锁表操作

Innodb

表结构有单独存储文件，数据和索引共享同一个存储文件(ibdata1、*.ibd)

ibdata1 是全部innodb表的数据和索引的存储文件

*.ibd 是每个innodb表的数据和索引的存储文件

支持事务和外键的

并发性：好，操作数据表时锁定记录(行)

Mysql5.6版本有支持全文索引

字段类型选择

1) 给数据分配的空间要尽量小

tinyint smallint mediumint int bigint

时间：datetime date year time timestamp

2) 数据最好整合为固定长度的信息

3) 数据最好变为整型信息存储(set enum 时间戳 ip)

逆范式

获得每个分类下商品总数量(连表查询Goods Category)

为了查询速度快，给给Category维护一个商品总数量的字段，可以使得查询变为一条sql的执行

4. 索引(四种类型、创建和删除、执行计划、使用场合、索引原则)

索引就是把数据表的某个字段获得出来，该字段作为关键字与记录物理地址进行对应，以便快速定位记录信息(索引内部有算法，可以快速定义信息)

四种类型：主键(auto_increment)、唯一、普通、全文

创建：

create table 表名(

……

primary key （字段）,

unique index 索引名 (字段),

index 索引名(字段)，

fulltext 索引名(字段)

)

alter table 表名 add primary key (id);

alter table 表名 add unique index 名称 (字段);

alter table 表名 add index 名称 (字段);

alter table 表名 add fulltext index 名称 (字段);

删除：

alter table 表名 drop primary key ;

alter table 表名 drop index 索引名 ; //唯一、普通、全文

执行计划：explain

针对查询语句起作用

查询语句在没有执行之前可以看下该语句消耗资源情况，例如sql语句是否使用索引

explain 查询语句\G

索引使用场合：

where order by 索引覆盖(复合索引) 连接查询

索引使用原则：

列独立左原则复合索引 OR原则

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的左右，只有一个有索引，导致整体都没有的使用：

二．索引

1. 索引设计依据

要估算每个数据表全部的查询sql语句类型

分析、统计每个sql语句的特点(where/order by/or等等)

原则：

① 被频繁执行的sql语句要设置

② 执行时间比较长的sql语句(可以统计)

③ 业务逻辑比较重要的sql语句(例如支付宝2小时内答应返现的业务逻辑)

2. 前缀索引

设计索引的字段，不使用全部内容，而只使用该字段前边一部分内容。

如果字段的前边N位的信息已经可以足够标识当前记录信息，就可以把前边N位信息设置为索引内容，好处：索引占据的物理空间小、运行速度就非常快。

举个例子：

石清清

李德升

许成宝

王伟聪

以上4条记录信息，通过前边一个字就可以唯一标识当前记录信息，创建索引的时候就使用前边第一个字即可，节省空间、运行速度快。

具体实现：

① 操作 alter table 表名 add index (字段(位数))

② 前边到底取得多少位，才是记录的唯一标识

总记录数目/前n位记录数目 = 比值;

select count(*) from 表名;

mysql字符串截取：substring(字段,开始位置1开始，长度)

从结果可以看出，密码的前9位就可以唯一标识当前记录信息：

现在给epassword创建索引，就可以只取得前9位即可：

3. 索引设计原则

字段内容需要足够花样

性别字段不适合做索引

4. 全文索引

Mysql5.5 Myisam存储引擎支持全文索引

Mysql5.6 Myisam和Innodb存储引擎都支持全文索引

目前中文不支持全文索引。

全文索引可以应用在 like ‘%XXX%’ 的操作上边。

创建articles数据表，并设置一个单列全文索引：

需要变形为match() against()才可以使用全文索引：

复合全文索引的使用：

5. 索引结构(了解)

索引内部有算法，算法可以保证查询速度比较快速。

算法的基础是数据结构。

索引的直接称谓就是“数据结构”

在Mysql数据库中，索引是存储引擎层面的技术。

不同的存储引擎使用的数据结构是不一样的。

两种索引结构

① 非聚集索引结构(Myisam)

② 聚集索引结构(Innodb)

5.1 Myisam非聚集索引结构

称为：B+Tree索引结构

Myisam存储引擎的索引结构为B+Tree：

上图为B+Tree索引结构，索引结构内部分为索引节点

节点从左到右是节点的“宽度”

节点从上到下的层数是结构的“高度”

宽度或高度太大都不适合快速索引查找

宽度和高度的设计会根据数据量的大小做适当的选择(mysql底层的算法)

该索引结构“叶子节点” 存储关键字和物理地址，非叶子节点存储关键字和指针，指针用于数据的比较、判断、向下个节点查找。

5.2 Innodb聚集索引结构

索引结构名称：B+Tree

1) 主键索引结构

重要一点：叶子节点的关键字(主键id值)对应整条记录信息

2) 非主键索引结构(唯一、普通等)

叶子节点的关键字对应主键id值

非主键索引-------------innodb的主键索引-------------整条记录

这样我们可以看到：索引和数据是在一起的

innodb表物理文件的索引和数据确实在一起：

(*.ibd集中存储order2数据表的索引和数据)

概念问题：

B-Tree、B+Tree、 Binary Tree

B+Tree是B-Tree的一个变形

B-Tree与B+Tree的明显区别是：B-Tree的每个节点的关键字都与“物理地址对应”

Binary Tree二进制树结构

三．查询缓存设置

一条查询sql语句有可能获得很多数据，并且有一定的时间消耗

如果该sql语句被频繁执行获得数据(这些数据还不经常发生变化)，为了使得每次获得的信息速度较快，就可以把“执行结果”给缓存起来，供后续的每次使用。

1. 查看并开启查询缓存

缓存大小为0，不能缓存

没有设置缓存之前，每次查询都消耗2多秒时间：

现在就开启缓存，设置缓存空间大小为64M：

开启缓存后，查询速度有明显的提升：

缓存失效

数据表或数据有变动(增加、减少、修改)，会引起缓存失效。

2.什么情况下不会使用缓存

sql语句有变动的信息，就不使用缓存

例如：时间信息、随机数

有时间信息的不给缓存：

有随机数的也不给缓存：

3. 生成多个缓存

注意：获得相同结果的sql语句，如果有空格、大小写等内容不同，也会分别进行缓存。

相同结果不同样子的sql语句会分别缓存：

4. 不进行缓存

针对特殊语句不需要缓存：

5. 查看缓存空间状态

缓存被使用后，空间有变小：

四．分表/分区

一个数据表里边可以存储许多记录信息，如果一个数据表里边存储的数据非常多(例如淘宝商城的商品表)，这样该商品表的相关工作量就很多(数据的增、删、改、查)

负载(工作量)高到一定程度，会造成把表锁死的情况发生。

为了降低商品表的负载/工作量，可以给该表拆分为多个数据表。这样每个数据表的工作量会有多降低。

Mysql5.1版本之后就支持分表分区的设计。

宏观拆分可以如下：

Goods数据表需要拆分：Goods_1 Goods_2 Goods_3.....Goods_10

数据表拆分为以后，需要考虑php如何操作这些数据表。

php------------([手动/mysql]算法)--------------数据表(分表)

手动算法：需要在php语言里边设计操作逻辑，增加php语言的代码工作量

mysql算法：php语言不需要做额外操作就可以像以往一样操作同一个数据表的不同分区，是mysql分表推荐的方式

1. 创建一个”分表/分区”数据表

Myisam和innodb数据表都可以做分表设计

推荐使用Myisam

设计分区的字段，需要是主键的一部分。

创建一个有10个分区的goods数据表：

可以看到goods数据表有10个分区：

上图每个分区表都独立的*.MYD数据文件和*.MYI索引文件

给该表存放信息，信息会平均分摊到各个数据表里边。

2. 四种分表分区算法

各种分区设计关联的字段必须是主键的一部分

或者是主键本身、或者是复合主键索引的从属主键部分

求余：

key 根据指定的字段进行分区设计

hash 根据指定的表达式进行分区设计

条件：

range 字段/表达式符合某个条件范围的分区设计

list 字段/表达式符合某个列表范围的分区设计

2.1 key的分区算法

2.2 hash分区算法

根据指定的表达式进行分区设计

设计分区的时候，分区字段必须是主键的一部分：

给数据表写入数据，数据会根据“月份”添加到对应的分区表中：

2.3 range() 分区算法

数据添加的时候根据年份，写入到对应的分区：

2.4 list 分区算法

key：该方式区分不明显(不一定会严格平均给分区分配数据)，但是大方向明显

hash/range/list:会根据业务特点把数据写入到对应的分区表里边。

3. 管理分区

增加、减少分区

3.1 求余(key、hash)算法管理

增加分区：alter table 表名 add partition partitions 数量;

减少分区：alter table 表名 coalesce partition 数量;

减少分区数据要丢失

3.2 条件(range、list)算法管理

增加分区：

alter table 表名 add partition(

partition 分区名 values less than[in] (常量[列表]),

....

)

减少分区：

alter table 表名 drop partition 分区名称;

减少分区，会丢失对应分区的数据。

增加：

减少：

七．慢查询日志

系统运行起来，内部需要执行许多sql语句

此时要把查询速度很慢的sql语句给统计出来，并做优化设计。

设定一个时间阀值，超过该时间，就说明sql语句很慢。

开启慢查询日志：

设置时间阀值(set后边没有global)：

posted @ 2017-09-12 12:54 丰study 阅读(154) 评论(0) 收藏举报

刷新页面返回顶部

丰study