MySQL(十四)-优化 | HoleLin's Blog

参考文献

3万字总结,Mysql优化之精髓
高性能MySQL(第三版)

为什么要优化

系统的吞吐量瓶颈往往出现在数据库的访问速度上
随着应用程序的运行,数据库的中的数据会越来越多,处理时间会相应变慢
数据是存放在磁盘上的,读写速度无法和内存相比

影响数据库性能的因素

SQL查询速度
大量的并发和超高的CPU使用率
- 大量的并发
  - 数据库连接数被占满(max_connections默认100)
- 超高的CPU使用率:
  - 因CPU资源耗尽而出现宕机
磁盘IO
- 磁盘IO性能突然下降(使用更快的磁盘设备)
- 其他大量消耗磁盘性能的计划任务(调整计划任务)
网卡流量
- 网卡IO被占满(1000Mb/8==100MB)
- 避免无法连接数据库
  - 减少从服务器的数量
  - 进行分级缓存
  - 避免使用"select *"进行查询
  - 分离业务网络和服务器网络
大表
- 记录行数巨大,单表超过千万行
- 表数据文件巨大,表数据文件超过10G
- 带来的问题
  - 慢查询:很难在一定的时间内过滤出所需要的数据
  - 对DDL的影响
    - 建立索引需要很长时间
      - 在MySQL版本<5.5建立索引会锁表
      - MySQL版本>=5.5虽然不会锁表但会引起主从延迟
    - 修改表结构需要长时间锁表
      - 会造成长时间的主从延迟
      - 影响正常的数据操作
- 处理办法
  - 分库分表,把一张大表分成多个大表
  - 难点
    - 分表主键的选择
    - 分表后跨分区数据的查询和统计
  - 大表的历史数据归档,减少对前后端业务的影响
  - 难点:
    - 归档时间的选择
    - 如何进行归档操作
大事务

优化的方向

设计数据库时:数据库表、字段的设计,存储引擎
利用好MySQL自身提供的功能,如索引等
横向扩展:MySQL集群、负载均衡、读写分离
SQL语句的优化(收效甚微)

字段优化

类型的选择,设计规范,范式,常见设计案例

原则:尽量使用整型表示字符串

原则:定长和非定长数据类型的选择

decimal不会损失精度,存储空间会随数据的增大而增大.

double占用固定空间,较大数的存储会损失精度.

非定长的还有varchar、text

字符串存储

定长char,非定长varchar、text(上限65535,其中varchar还会消耗1-3字节记录长度,而text使用额外空间记录长度)

原则:尽可能选择小的数据类型和指定短的长度

原则:尽可能使用 `NOT NULL`

数据完整性:使用 NOT NULL 约束可以保证表中的数据完整性,确保不会出现空值.空值可能会导致一些不可预测的行为,如计算错误、聚合函数返回错误结果等.
查询性能:如果表中的某个列没有使用 NOT NULL 约束,MySQL 在查询时可能需要使用额外的资源来处理空值.这可能会导致查询变慢,影响查询性能.
索引效率:对于包含空值的列,MySQL 在使用索引进行查询时可能需要进行额外的处理,这会降低索引的效率.如果将列定义为 NOT NULL,则 MySQL 可以更好地优化查询计划,提高查询性能.
存储空间:空值需要额外的存储空间.如果表中包含大量的空值,将会占用更多的存储空间.如果将列定义为 NOT NULL,则可以减少存储空间的占用.

如何存储？MySQL中每条记录都需要额外的存储空间,表示每个字段是否为null.因此通常使用特殊的数据进行占位,比如int not null default 0、string not null default ‘’

原则:字段注释要完整,见名知意

原则:单表字段不宜过多

二三十个就极限了

标识符(identifier)选择

整数类型
- 整数类型是标识列的最好选择,因为很快并且可以使用AUTO_INCREMENT
ENUM和SET类型
- 对于标识列来说,ENUM和SET类型不太适合,ENUM和SET列适合存储固定信息.
字符串类型
- 如果可能,应该避免使用字符串类型作为标识列,因为很消耗空间,通常比数字类型慢,尤其在MyISAM表中使用字符串作为标识列时要特别小心.MyISAM默认对字符串使用压缩索引,这回导致查询慢得多.
- 对于完全"随机"的字符串也需要多加注意,如MD5(),SHA1()或UUID()产生的字符串.这些函数生成的新值会任意分布在很大的空间内,这会导致INSERT以及一些SELECT语句变得很慢.
  - 因为插入值会随机地写到索引的不同位置,所以使得INSERT语句更慢.这会导致页分裂,磁盘随机访问,以及对于聚簇索引引擎产生聚簇索引碎片.
  - SELECT语句会变得更慢,因为逻辑上相邻的行会分布在磁盘和内存的不同地方.
  - 随机值导致缓存对所有类型的查询语句效果都很差,因为会使得缓存赖以工作的访问局部性原理失效.
    - 如果整个数据集都一样"热",那么缓存任何一部分特定数据到内存都没有好处
    - 如果工作集比内存大,缓存将会有很多刷新和不命中.
- 如果存储UUID值,应该移除"-"符号,或者更好的做法是,用UNHEX()函数转UUID为16字节的数字,并存储在一个BINARY(16)列中.检索时可以通过HEX()函数来格式化为16进制格式.

存储引擎选择

MySQL可以将数据以不同的技术存储在文件(内存)中,这种技术称为存储引擎;

每一种存储引擎使用不同的存储机制,索引技巧,锁定水平,最终提供广泛且不同的功能

mysql> show engines;
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                               	    | Transactions | XA   | Savepoints |
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
| FEDERATED          | NO      | Federated MySQL storage engine                             | NULL         | NULL | NULL       |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables  | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES          | YES  | YES        |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                         | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                      | NO           | NO   | NO         |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine(anything you write to it disappears) | NO        | NO   | NO         |
| CSV                | YES     | CSV storage engine                                         | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                     | NO           | NO   | NO         |
+--------------------+---------+------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.13 sec)

各种存储引擎的特点

特点	MyISAM	InnoDB	Memory	MERGRE	NDB
存储限制	256TB	64TB	有	没有	有
事务安全	-	支持	-	-	-
支持索引	支持	支持	支持	支持	支持
锁颗粒	表锁	行锁	表锁	表锁	行锁
数据压缩	支持	-	-	-	-
支持外键	-	支持	-	-	-
B树索引	支持	支持	支持	支持	支持
哈希索引	-	-	支持	-	支持
全文索引	支持	-	-	-	-
集群索引	-	支持	-	-	-
索引缓存	支持	支持	支持	支持	支持
空间使用	低	高		低	低
内存使用	低	搞	中等	低	高
批量插入的速度	高	低	高	高	高

MyISAM

MyISAM 是MySQL 5.5.8版本之前的默认存储引擎.MyISAM 不支持事务、也不支持外键,其优势是访问的速度快,对事务完整性没有要求或者以SELECT、INSERT 为主的应用基本上都可以使用这个引擎来创建表.
InnoDB
InnoDB是MySQL5.5.8版本之后的默认存储引擎.

InnoDB 存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全.但是对比MyISAM的存储引擎,InnoDB 写的处理效率差一些并且会占用更多的磁盘空间以保留数据和索引.

对于InnoDB 表,自动增长列必须是索引.如果是组合索引,也必须是组合索引的第一列,但是对于MyISAM 表,自动增长列可以是组合索引的其他列,这样插入记录后,自动增长列是按照组合索引的前面几列进行排序后递增的.

MySQL 支持外键的存储引擎只有InnoDB,在创建外键的时候,要求父表必须有对应的索引,子表在创建外键的时候也会自动创建对应的索引.在创建索引的时候,可以指定在删除、更新父表时,对子表进行的相应操作,包RESTRICT、CASCADE、SET NULL 和NO ACTION.其中RESTRICT 和NO ACTION 相同,是指限制在子表有关联记录的情况下父表不能更新；CASCADE 表示父表在更新或者删除时,更新或者删除子表对应记录；SET NULL 则表示父表在更新或者删除的时候,子表的对应字段被SET NULL.

InnoDB引擎的4大特性
- 插入缓冲(insert buffer)
- 二次写(double write)
- 自适应哈希索引(ahi)
- 预读(read ahead)
MEMORY

MEMORY 存储引擎使用存在内存中的内容来创建表.每个MEMORY 表只实际对应一个磁盘文件,格式是.frm.MEMORY 类型的表访问非常得快,因为它的数据是放在内存中的,并且默认使用HASH 索引,但是一旦服务关闭,表中的数据就会丢失掉.

在启动MySQL 服务的时候使用–init-file 选项,把INSERT INTO … SELECT 或LOAD DATA INFILE 这样的语句放入这个文件中,就可以在服务启动时从持久稳固的数据源装载表.

服务器需要足够内存来维持所有在同一时间使用的MEMORY 表,当不再需要MEMORY表的内容之时,要释放被MEMORY 表使用的内存,应该执行DELETE FROM 或TRUNCATE TABLE,或者整个地删除表(使用DROP TABLE 操作).
MERGE

MERGE 存储引擎是一组MyISAM 表的组合,这些MyISAM 表必须结构完全相同,MERGE表本身并没有数据,对MERGE 类型的表可以进行查询、更新、删除的操作,这些操作实际上是对内部的实际的MyISAM 表进行的.对于MERGE 类型表的插入操作,是通过INSERT_METHOD 子句定义插入的表,可以有3 个不同的值,使用FIRST 或LAST 值使得插入操作被相应地作用在第一或最后一个表上,不定义这个子句或者定义为NO,表示不能对这个MERGE 表执行插入操作.

可以对MERGE 表进行DROP 操作,这个操作只是删除MERGE 的定义,对内部的表没有任何的影响

如何选择合适的存储引擎

参考条件
- 事务
- 备份
- 崩溃恢复
- 存储引擎的特有特性

在选择存储引擎时,应根据应用特点选择合适的存储引擎,对于复杂的应用系统可以根据实际情况选择多种存储引擎进行组合

MyISAM:默认的MySQL 插件式存储引擎.**如果应用是以读操作和插入操作为主,只有很少的更新和删除操作,并且对事务的完整性、并发性要求不是很高,那么选择这个存储引擎是非常适合的.**MyISAM 是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一.

InnoDB:用于事务处理应用程序,支持外键.如果应用对事务的完整性有比较高的要求,在并发条件下要求数据的一致性,数据操作除了插入和查询以外,还包括很多的更新、删除操作,那么InnoDB 存储引擎应该是比较合适的选择.InnoDB 存储引擎除了有效地降低由于删除和更新导致的锁定,还可以确保事务的完整提交(Commit)和回滚(Rollback),对于类似计费系统或者财务系统等对数据准确性要求比较高的系统,InnoDB 都是合适的选择.

MEMORY:将所有数据保存在RAM 中,在需要快速定位记录和其他类似数据的环境下,可提供极快的访问.MEMORY 的缺陷是对表的大小有限制,太大的表无法CACHE 在内存中,其次是要确保表的数据可以恢复,数据库异常终止后表中的数据是可以恢复的.MEMORY 表通常用于更新不太频繁的小表,用以快速得到访问结果.

MERGE:用于将一系列等同的MyISAM 表以逻辑方式组合在一起,并作为一个对象引用它们.MERGE 表的优点在于可以突破对单个MyISAM 表大小的限制,并且通过将不同的表分布在多个磁盘上,可以有效地改善MERGE 表的访问效率.这对于诸如数据仓储等VLDB环境十分适合

MyISAM索引与InnoDB索引的区别

InnoDB索引是聚簇索引,MyISAM索引是非聚簇索引.
InnoDB的主键索引的叶子节点存储着行数据,因此主键索引非常高效.
MyISAM索引的叶子节点存储的是行数据地址,需要再寻址一次才能得到数据.
InnoDB非主键索引的叶子节点存储的是主键和其他带索引的列数据,因此查询时做到覆盖索引会非常高效.
InnoDB不支持全文索引,MyISAM支持全文索引；

索引

关键字与数据的映射关系称为索引(包含关键字和对应的记录在磁盘中的地址).关键字是从数据当中提取的用于标识、检索数据的特定内容.

见MySQL索引

查询缓存

缓存select语句的查询结果

配置缓存

在配置文件中开启缓存,在[mysqld]段中配置query_cache_type
- 0:不开启
- 1:开启,默认缓存所有,需要在SQL语句中增加select sql-no-cache提示来放弃缓存
  1
  select sql-no-cache * from user;
- 2:开启,默认都不缓存,需要在SQL语句中增加select sql-cache来主动缓存(常用)
  1
  select sql_cache * from user;
更改配置后需要重启以使配置生效,重启后可通过show variables like ‘query_cache_type’;来查看:

设置客户端缓存

通过配置项query_cache_size来设置,set global query_cache_size=64*1024*1024;

重置缓存

1	reset query cache;

缓存失效问题

当数据表改动时,基于该数据表的任何缓存都会被删除.(表层面的管理,不是记录层面的管理,因此失效率较高)
注意事项
- 应用程序,不应该关心query cache的使用情况.可以尝试使用,但不能由query cache决定业务逻辑,因为query cache由DBA来管理.
- 缓存是以SQL语句为key存储的,因此即使SQL语句功能相同,但如果多了一个空格或者大小写有差异都会导致匹配不到缓存.

参考文献

为什么要优化

影响数据库性能的因素

优化的方向

字段优化

原则:尽量使用整型表示字符串

原则:定长和非定长数据类型的选择

原则:尽可能选择小的数据类型和指定短的长度

原则:尽可能使用 NOT NULL

原则:字段注释要完整,见名知意

原则:单表字段不宜过多

标识符(identifier)选择

存储引擎选择

各种存储引擎的特点

InnoDB引擎的4大特性

如何选择合适的存储引擎

MyISAM索引与InnoDB索引的区别

索引

查询缓存

配置缓存

设置客户端缓存

重置缓存

缓存失效问题

分区

查询优化

原则:尽可能使用 `NOT NULL`