Mysql优化

一、为什么学习Mysql优化

1、Mysql的连接数过大的情况（这种情况下大部分都可以使用redis、memcache、mongodb等缓存系统减少mysql的连接，但是有部分业务逻辑是必要查询数据库的，不能依赖缓存，因此Mysql必须承担此压力）
2、Mysql本身SQL语句的执行效率很慢

二、Mysql优化的入口点

1、SQL语句层：更合适的SQL语句
2、存储层（数据）：数据表存储引擎的选择、数据表设计过程中列类型的选择、范式规范
3、设计层（单台数据库服务器）：索引、缓存、分区
4、架构层（多台服务器）：读写分离或分布式存储

三、SQL优化

1、索引

索引是把字段A的内容储存在一个独立区间中，里面只有这个字段的内容和其对应的物理地址（将数据表中字段A对应的所有数据全部保存在另一个文件中。该文件除了内容之外还保存了完整数据对应的物理地址）。在查找与这个字段A相关的内容时会直接从这个独立区间里查找，而不是云到数据表中查找。这种查找速度非常快，因为索引内部有排序算法。

索引不能盲目地建立

索引建得越多，数据表文件会越大，数据插入、更新会越缓慢，我们建立索引的目的在于提高查询的效率（增删改查中，查是使用得最多的），但是把插入、更新效率降低了，也不是我们想要的结果。

2、索引类型

①、主键索引：primary key，唯一且不能为空
②、唯一索引：unique key，唯一但是可以为空
③、普通索引：key
④、全文索引：只针对英文
⑤、复合索引：使用多个字段来创建一个索引

索引的选择需要根据业务逻辑来确定

3、索引结构

1）、Myisam非聚簇索引

对于myisam存储引擎而言，主键与非主键使用的格式一模一样，都是采用二叉树算法查找（二分查找）

2）、Innodb聚簇索引

①、主键索引

在innodb引擎中，主键索引最终保存的是完整数据而不是物理地址

②、非主键索引

在innodb在使用非主键索引查询，先查询到主键标识、然后再根据主键查询数据。如果没有自己增加主键字段，Mysql会自动地维护主键字段

4、执行计划

执行计划即mysql中所内置的优化器（决定具体数据查询方式），会智能分析SQL语句的执行方式

语法：

explain + select语句

示例：

explain select * from ls_apply_lawhelp where truename = '张三'

关于执行计划返回结果的说明：

id：执行的顺序，ID值越大越先执行，ID相同时，从上至下执行
select_type：表示查询的类型，常见的有simple，为简单SQL语句查询，primary表示复合的SQL语句查询
possible_keys：可能使用到的索引，此列应尽量有值
key：真实执行过程中会使用到的索引
rows：实际扫描的行数，此值应越小越好
type：表示查询SQL的语句性能，在下面所列出的值中，性能是逐步下降的

system：表示表中只有一条数据，觉在系统表中
const：表示使用主键作为条件进行查询
eq_ref:表示使用普通索引进行连表查询
range：当查询时使用范围查询
index：使用了索引，但查询全部
all：全表扫描

SQL优化的指标：
type：向const靠拢
possible_keys：尽量有值
keys：要求尽量有索引可以使用
rows：要求越小越好

使用主键查询：

使用索引查询：

使用索引+范围条件：

使用索引：

使用普通字段：

5、慢日志查询

①、查看慢日志是否开启

②、开启慢日志

③、查看慢日志临界时间点

即：执行时间超过10秒的SQL，才被识别为慢查询

④、修改慢查询临界时间点

四、存储优化

1、范式规范

第一范式：要求在业务逻辑能够满足的情况下，所有字段都是原子不可切割的
第二范式：在满足第一范式的基础之上，数据表要求有主键字段并且所有的字段都需要跟主键有关系
第三范式：在满足第二范式的基础之上，要求所有的字段都需要跟主键有直接关系
逆范式：当满足第三范式需要进行多次连表操作时，可以违背第三范式

2、存储引擎

MySQL中的数据用各种不同的技术存储在文件(或者内存)中。这些技术都使用不同的存储机制、索引技巧、锁定水平并且最终提供广泛的不同的功能和能力。通过选择不同的技术，你能够获得额外的速度或者功能，从而改善你的应用的整体功能。

例如，如果你在研究大量的临时数据，你也许需要使用内存MySQL存储引擎。内存存储引擎能够在内存中存储所有的表格数据。又或者，你也许需要一个支持事务处理的数据库(以确保事务处理不成功时数据的回退能力)。

这些不同的技术以及配套的相关功能在 MySQL中被称作存储引擎(也称作表类型)。

查看所支持的存储引擎

目前版本所支持的存储引擎有8种、其中默认的为innodb存储引擎。并且只有innodb存储引擎才支持事物功能。在实际开发过程中使用的存储引擎最多的为innodb跟myisam。

1）innodb与myisam的区别

① 、innodb:

支持事物、行锁、表锁、外键，低版本不支持全文索引

②、myisam：

仅支持表锁、支持压缩功能、读写速度更快、支持全文索引（英文）

2）存储机制

对于myisam、innodb存储引擎而言，数据都是使用磁盘进行存储。具体保存地址受控制于配置文件（my.in文件）

①、innodb：

table.frm文件记录数据表的结构信息，默认innodb引擎对应的存储数据及索引保存在basedir目录下的ibdata1文件中，此方式叫共享存储。也可配置innodb存储引擎使用单独文件存储

②、myisam:

table.frm文件记录数据表的结构信息
table.MYD保存数据
table.MYI记录字段的索引信息

上述table表示表名

3）innodb存储引擎单文件存储

查询当前使用的存储方式

设置为单文件存储

再次查看

此时table.frm文件存储表结构，table.ibd文件存储数据和索引

4）myisam存储引擎的压缩功能

在cmd窗口，进入mysql数据库，执行下列命令：

myisampach.exe "数据表存储目录地址\table" #table表示表名称，

5）数据备份还原
①、备份还原的实现方式

使用第三方工具来实现，如phpmyadmim、nvaicate等，但是在数据量比较大时，应使用mysql内置的工具实现备份还原

②、myisam存储引擎数据的备份与还原

myisam中数据索引及结构都是单独采用文件存储，因此可以直接复制粘贴，也可以通过mysql内置的工具备份

③、innodb存储引擎的备份

对于innodb而言，无论是单独文件存储还是共享方式，都必须使用mysql的内置命令进行备份还原

备份：mysqldump -u user -p pwd table > 目录地址/文件名.sql

还原：mysql.exe -u user -p pwd table < 目录地址/文件名.sql

6）其他存储引擎

Memory
数据置于内存的存储引擎，拥有极高的插入，更新和查询效率。但是会占用和数据量成正比的内存空间。并且其内容会在Mysql重新启动时丢失。当实际使用内存存储数据时选择使用专业级别的内存数据库(缓存系统)

Archive
归档存储引擎，只支持数据的查询和写入。
经常用于存储日志等相关信息。

3、列类型选择

1）数据类型选择的作用

对于不同的字段如果选择一个较大的存储空间的类型会导致空间浪费。当数据表在使用过程中，如果发现字段类型不合适，修改数据表结构，会导致mysql中查询缓存失效

2）整型选择

整形的选择依据是使用数字大小决定

3）浮点型选择

Float:浮点类型精度比较低

Decimal:定点类型精度比较高

具体在选择类型上依据精度进行选择 要求高精度可以使用decimal类型。有部分情况对应精度要求比较高可以使用整形代替(降低单位)

4）字符串类型选择

字符串的类型有 char、varchar、text

①、对于存储的字符串长度大于255 选择使用text。否则在char与varchar中进行选择
②、当存储的字符串长度小于255，根据存储的内容是否为定长决定。如果为固定长度选择使用char否则使用varchar 。也可以针对变化长度的内容选择时char(使用空间换时间)

char与varchar之间的区别

①、char属于固定长度而varchar属于变化长度。char设置长度为10最多存储10个字符长度，如果存储的内容低于10个字符长度也会占用10个字符空间。varchar根据内容变化长度,设置varchar最大的长度为10个字符如果存储的只有5个字符串最终的存储空间是约6个

②、char与varchar最大的存储的字符长度不一致。Char最多只能存储255个，varchar最多存储 65535/3-1（以utf8编码）

③、char与varchar对末尾的空格处理不一致。Char会丢失末尾空格，varchar会保留末尾空格

5）时间类型选择

Datetime：日期时间格式 2018-03-21 17:24:10

Date：日期格式2018-03-21

Time：时间格式 17:24:10

Year：2018

Timestamp: 时间戳

在实际使用过程中会使用int类型记录时间戳

6）枚举与集合

枚举类型（单选）：常用于性别操作，如enum（’男’，‘女’，‘保密’）

集合类型（多选）：常用于爱好操作，如set（’吃饭’，‘睡觉’，‘打豆豆’）

枚举类型往往使用tinyint替换enum

集合类型往往使用字符串替换set，使用字符串多个自减使用特殊符号进行分割即可

7）IP地址数据转换

对于IP地址存储应该使用字符串存储但是实际都是在使用整形数字进行存储

①、mysql内置函数

inet_aton(ip) ：将ip地址转换为整形

inet_ntoa(数字)：整形转换为数字

②、php内置函数

ip2long(ip)：将ip地址转换为整形

long2ip(数字)：整形转换为数字

五、设计优化

1、Mysql中使用索引的原则

频繁出现在select语句中的where条件、group by条件、order by条件字段设置索引，而非select 字段

执行时间长的SQL语句，要适当的创建索引解决执行缓存的问题

查询慢日志，查看执行效率低的SQL语句

尽量规避连表查询，先在php中把逻辑理顺

①、使用单独的字段查询

列不够独立，需要参与运算才能得出结果，因此不能使用到索引，建议在开发过程中，应尽量将运算在php中完成

②、like左原则

在like查询中，尽量不要在like条件左侧使用%通配符，如果在左侧使用，会导致不能执行索引

③、or原则

or条件的顺序不影响索引的使用

在or中，想要使用到索引，必须针对每一个条件都创建单独的索引或者使用复合索引中的第一个条件

④、and原则

在and中，除了复合索引外，每一个有索引的条件都能使用到索引。

复合索引：
单独使用复合索引字段时，只有第一个字段才可以使用到索引（左原则）
使用and连接时，包含第一个字段才可以使用到索引（左原则）

2、查询缓存

Mysql每次查询结束后，会将结果集缓存到一个hash表中,通过查询的SQL语句,查询数据库,客户端协议等作为key，在判断是否命中前，MySQL不会解析SQL，而是直接使用上述的key去查询缓存，SQL任何字符上的不同，如空格，注释，都会导致缓存不命中.

如果查询中有不确定数据,例如CURRENT_DATE()和NOW()函数,那么查询完毕后则不会被缓存.所以,包含不确定数据的查询是肯定不会找到可用缓存的。

查看是否开启查询缓存：

query_cache_type：查询缓存类型,是否打开缓存

可选项

0(OFF)：关闭 Query Cache 功能，任何情况下都不会使用 Query Cache；
1(ON)：开启 Query Cache 功能，但是当SELECT语句中使用SQL_NO_CACHE提示后，将不使用Query Cache；
2(DEMAND)：开启Query Cache 功能，但是只有当SELECT语句中使用了SQL_CACHE 提示后，才使用Query Cache。
如果query_cache_type为on而又不想利用查询缓存中的数据，可以用下面的SQL：
SELECT SQL_NO_CACHE * FROM my_table WHERE condition;
如果值为2，要使用缓存的话，需要使用SQL_CACHE开关参数：
SELECT SQL_CACHE * FROM my_table WHERE condition;

query_cache_size: 缓存使用的总内存空间大小,单位是字节,这个值必须是1024的整数倍,否则MySQL 会自动调整降低最小量以达到1024的倍数；(感觉这个应该跟文件系统的blcok大小有关)

query_cache_min_res_unit: 分配内存块时的最小单位大小，设置查询缓存Query Cache每次分配内存的最小空间大小，即每个查询的缓存最小占用的内存空间大小；

query_cache_limit: 允许缓存的单条查询结果集的最大容量，默认是1MB，超过此参数设置的查询结果集将不会被缓存；

query_cache_wlock_invalidate: 如果某个数据表被锁住,是否仍然从缓存中返回数据,默认是OFF,表示仍然可以返回。控制当有写锁定发生在表上的时刻是否先失效该表相关的Query Cache，如果设置为 1(TRUE)，则在写锁定的同时将失效该表相关的所有Query Cache，如果设置为0(FALSE)则在锁定时刻仍然允许读取该表相关的Query Cache。

3、分区分表

1）分区分表的作用

分区分表主要作用在于将数据进行拆分。例如可以使用分表将数据拆分到不同的数据表中。因此可以提升效率

2）分区分表的分类

①、垂直分表：垂直分表可以理解为将数据表中的内容垂直进行切割。对于拆分之后的两张数据表每张数据表中的字段不一样

②、水平分表：水平分表可以理解为将表中的数据按照水平进行切割。切割之后的数据表字段一模一样。

3）垂直分表分类

①、根据字段是否固定长度进行拆分 固定的存储在一张表不固定的存储另外一张

②、根据字段的使用频率拆分，将经常使用的字段拆分到一张表 将不经常使用的字段拆分到另外一张表

4）水平分表分类

①、逻辑分表

mysql内置功能

求余算法：将拆分的字段转换为整形数字之后在根据要拆分的数据表的个数进行求余数，有Key算法、Hash算法等

取值范围算法：
Range算法：表示某一个范围
list算法：表示为固定的某些值

②、物理分表

自定义分表的规则。 没有固定算法。

如id为奇数的单独成表，id为偶数的单独成表

5）水平分表原理

①、逻辑分表

②、物理分表

六、架构优化

1、读写分离

2、分布式存储