devbox
代码领袖开发者
这个屌丝很懒，什么也没留下！
关注作者
热门标签
热门文章
当前位置:   article > 正文

MySQL相关参数配置及性能优化_mysql 事务 相关 参数

作者：代码领袖开发者 | 2024-01-30 20:44:23

赞

踩

mysql 事务 相关 参数

MySQL及其优化

文章目录

数据库相关概念

  • 系统吞吐量： 一个系统的吞度量（承压能力）与request对CPU的消耗、外部接口、IO等等紧密关联。单个reqeust 对CPU消耗越高，外部系统接口、IO影响速度越慢，系统吞吐能力越低，反之越高。 系统吞吐量几个重要参数：QPS（TPS）、并发数、响应时间(平均响应时间)；

  • QPS：Queries Per Second，每秒钟处理SQL的数量；

  • TPS：Transactions Per Second，每秒处理的事务数量；包含了 用户请求服务器、服务器自己的内部处理、服务器返回给用户；如果每秒能够完成N次这三个过程，则TPS就是N；

    一个TPS可能包含多个QPS。

  • 并发数： 系统同时处理的请求数量；

事务的四大特性ACID

  • A-原子性， 一个事务必须被视为一个不可分割的最小工作单元,整个事务中的所有操作要么全部提交成功,要么全部失败,对于一个事务来说,不可能只执行其中的一部分操作。

  • C-一致性，一致性是指事务将数据库从一种一致性，状态转换到另外一种一致性状态,在事务开始之前和事务结束后数据库中数据的完整性没有被破坏。

  • I-隔离性，隔离性要求一个事务对数据库中数据的修改,在未提交完成前对于其它事务是不可见的。

    • 事务的隔离级别：

      • 读未提交：Read Uncommitted

      • 读已提交： Read Committed

      • 可重复读： Repeatable Read

      • 可串行化：Serializerable

        以上隔离级别：隔离性由低到高；并发性由高到低；其中InnoDB存储引擎默认的事务隔离级别是Repeatable Read 可重复读；

  • D-持久性，一旦事务提交，则其所做的修改就会永久保存到数据库中，此时即使系统崩溃，已经提交的修改数据也不会丢失；

影响mysql数据库性能的几个方面

  • 服务器硬件

    • CPU

      • 64位的CPU一定要工作在64位的系统下
      • 选择更快的CPU还是选择更多的CPU？
        • web类应用(并发较高)：核心数比频率重要；
        • CPU密集型场景和复杂SQL: 频率更重要；
      • 很多老版本的MySQL(<=5.5)无法发挥多核心CPU的性能；

    • 内存

      • 尽可能选择内存主频更高的内存；尽量选择相同品牌、存储颗粒、频率、电压等；

    • 网络

      • 网络带宽，1M， 10M…
      • 网络质量，丢包率
      • 网卡

    • I/O子系统 (PCIe > SSD > RAID10 > 传统机械硬盘 > SAN)

      • 磁盘的配置和选择

        • 使用传统机械硬盘；

        • 使用RAID增强传统机械因硬盘的性能；将多个小容量磁盘组成容量更大的磁盘，并提供数据冗余来保证数据完整性；

          • 常用的 RAID级别： RAID0、 RAID1、 RAID5、 RAID10

        • 使用固态存储SSD和PCIe卡；相比机械硬盘，有更好的随机读写性能、更好地支持并发、寿命较低(最大写入次数)

        • 使用网络存储NAS和SAN

  • 服务器操作系统

    • Windows， 操作系统文件大小写不敏感
    • FreeBSD
    • Solaris
    • Linux
      • CentOS
      • Redhat 企业版

  • 所用的MySQL版本

  • 数据库存储引擎

    • MyISAM: 不支持事务，表级锁；(MyISAM会将索引缓存到内存当中，数据缓存在操作系统)
    • InnoDB: 事务级存储引擎，完美支持行级锁；(InnoDB将索引和数据都缓存到内存中)

  • 数据库参数配置（影响较大）

  • 数据库结构设计和SQL语句（慢查询是大多数性能低的罪魁祸首）

数据库性能优化的重点

​ 良好的数据库逻辑设计和物理设计是数据库获得高性能的基础；

  • 数据库表结构设计
  • SQL语句的编写和优化

CentOS系统参数优化

内核相关参数(/etc/sysctl.conf)
# 网络相关参数
net.core.somaxconn=65535

net.core.netdev_max_backlog=65535

net.ipv4.tcp_max_syn_backlog=65535

net.ipv4.tcp_fin_timeout=10

net.ipv4.tcp_tw_reuse=1

net.ipv4.tcp_tw_recycle=1

# 等等...

# 内存相关参数
kernel.shmmax=4294967295  # 用于定义单个共享内存段的最大值
# 这个参数应该设置得足够大，以便能在一个共享内存段下容纳整个的InnoDB缓冲池的大小；
# 这个值的大小对于64位linux系统，可取的最大值为物理内存值减去1Byte，建议值为大于物理内存的一半，一般取值大于InnoDB缓冲池的大小即可；

vm.swappiness=0
# 在MySQL服务器所在的linux上保留交换分区还是有必要的，但是要控制何时使用交换分区；
# 当上面的参数设置为0时，即告诉linux内核，除非虚拟内存满了，否则不要使用交换分区；


  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14
  • 15
  • 16
  • 17
  • 18
  • 19
  • 20
  • 21
  • 22
  • 23
  • 24
增加资源限制(/etc/security/limit.conf)
# 控制打开文件的数量的限制,追加到limit.conf结尾即可；
* soft nofile 65535
* hard nofile 65535

# 解释
#	* 表示对所有用户有效
#	soft 指的是当前系统生效的设置
#	hard 表明系统中所能设定的最大值
# 	nofile 表示所限制的资源是打开文件的最大数目
# 	65535 即限制的数量

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
磁盘调度策略(/sys/block/devname/queue/scheduler)
# 查看当前所使用的磁盘调度策略
cat /sys/block/sda/queue/scheduler  

>>> noop anticipatory deadline [cfq]

# 默认策略是cfq，用于桌面操作系统还是比较合适，但是用于MySQL服务器不太合适；
# noop(电梯式调度策略)，适用于内存设备、RAM、嵌入式系统；
# deadline(截止时间调度策略)，适用于数据库类应用；
# anticipatory(预料I/O调度策略)，适用于写入较多的环境，如文件服务器；

# 改变磁盘调度策略
# 语法 echo <schedulername> > /sys/block/devname/queue/scheduler
# 如将sda盘的调度策略改为deadline：
echo deadline > /sys/block/sda/queue/scheduler

  • 1
  • 2
  • 3
  • 4
  • 5
  • 6
  • 7
  • 8
  • 9
  • 10
  • 11
  • 12
  • 13
  • 14

文件系统对性能的影响

Windows

  1. FAT
  2. NTFS

Linux

  1. EXT3

  2. EXT4

  3. XFS，性能较高，优先选择

    若使用EXT3/4系统，需要设置挂载参数(/etc/fstab)

    data=writeback | ordered | journal
noatime
nodiratime
/dev/sda1/ext4 noatime,nodiratime,data=writeback 1 1

    • 1
    • 2
    • 3
    • 4

MySQL体系结构

​ 整体架构分为：MySQL客户端、MySQL服务层、存储引擎层；

  • MySQL客户端

  • MySQL服务层

    • 连接管理器
    • 查询解析器
    • 查询优化器
    • 查询缓存器

  • 存储引擎层 （存储引擎针对表，而非库，即一个数据库中不同的表可以使用不同的存储引擎）

    • InnoDB

      • MySQL5.5(具体点是5.5.8)版本及之后默认的存储引擎；

      • InnoDB使用表空间进行数据存储，innodb_file_per_table=ON时，独立表空间tablename.ibd；为OFF时，系统(共享)表空间ibdataX，这里的X是具体的数值，比如ibdata1、ibdata2； 查看存储方式：show variables like 'innodb_file_per_table';强烈建议对于InnoDB存储引擎，使用独立表空间（MySQL5.6之后已经默认选用）；

      • 创建一个使用InnoDB引擎的表：create table tb_test(id int, c1 varchar(10)) engine='innodb';

      • 文件结构：myinnodb.frm，存储表结构；myinnodb.ibd，存储数据；

      • 实现事务的方式：Redo Log 和Undo Log；其中Redo Log 中存放了已经提交的事务【顺序写入】，Undo Log 中存放了未提交的事务(实现回滚)【随机读写】（MySQL5.6以后，Undo Log 可以独立存储在SSD等设备，以加快随机读写速度）；Redo Log 和 Undo Log实现了事务的原子性、一致性、持久性；

      • 支持行级锁，最大程度上支持并发；行级锁是由存储引擎层实现的；锁实现了事务的隔离性；

      • InnoDB状态检查：show engine innodb status;;

      • InnoDB存储引擎的适用场景：

        • InnoDB适合于大多数OLTP应用（On-Line Transaction Processing 在线事务处理）；

    • MyISAM

      • MySQL5.5之前版本默认的存储引擎；

      • 系统表、临时表采用MyISAM存储引擎；

      • 文件结构：frm、MYD、MYI， MYI是索引文件， frm是表结构，MYD是存储数据；

      • 特性：

        • 1.并发性与锁级别角度：

          • MyISAM支持表级锁，写入相对较慢，对读操作影响不算大；
          • 并发性较差；

        • 2.表损坏修复：

          • 由于不支持事务，可能造成数据的丢失；
          • 可以使用：check table 表名来检查表；
          • 可以使用：repair table 表名来修复；（不保证完全修复）
          • 也可使用命令行工具myisamchk进行检查与修复，注意：在使用该命令行工具时，要先停掉MySQL服务，否则可能造成更大的损坏；

        • 3.支持的索引类型

          • 1.支持全文索引，是MySQL5.7版本之前唯一一个原生就支持全文索引的官方存储引擎；
          • 2.支持text类型的大文本的前缀索引；

        • 4.支持数据压缩 mysisampack -b -f myIsam.MYI；-f表示强制压缩；如果强制压缩特别小的文件，可能会出现压缩后体积更大的情况；压缩后只能读，不能写；

        • 5.限制：MySQL5.0版本之前，默认单个表大小不能超过4G，如果需要存储大表需要修改MAX_Rows 和 AVG_ROW_LENGTH；这两个参数乘积即为单表最大值；

        • 6.适用场景：

          • 非事务型应用；
          • 只读类应用；
          • 空间类应用；（空间函数，5.7之前唯一支持空间运算的存储引擎）

    • XtraDB

    • CSV

      • 存储的文件格式就是普通的csv文件，文本格式，方便查看和编辑（其他存储引擎存储的是二进制，无法直接在文件中查看和编辑）；
      • .CSV文件存储表内容
      • .CSM文件存储表的元数据，如表的状态和数据量；
      • .frm文件存储表结构信息；
      • 以逗号为分隔符；所有列都不能出现NULL(建表时候需要增加约束not null)；不支持索引；
      • 适用场景
        • 适合做为数据交换的中间表，如将Excel表格数据导出为CSV，然后存储到数据库表中(反之亦可)；

    • Memory

      • 也称为HEAP(堆)存储引擎，数据保存在内存中，一旦MySQL数据库停止或重启，数据就会丢失，但是表结构会保存下来，原因是表结构保存在磁盘中，数据保存在内存中；
      • I/O效率较高；
      • 支持两种索引，HASH索引和BTree索引，默认使用HASH索引；HASH索引的特点:在做等值查询，效率很高，在做范围查询就无法使用了；创建默认索引：create index 索引名 on 表名(字段名);，创建BTree索引：create index 索引名 using btree on 表名(字段名);；查看索引：show index from 表名;
      • 索引的选用原则：如果等值查找较多，选择HASH索引，范围查找较多，选择BTree索引；
      • 所有字段都为固定长度，比如定义时候使用的是varchar(128)，则会变成char(128)，即使长度为5，也会占用128的空间，所以在定义的时候要尽量使用最小的长度，否则会非常占用内存空间；
      • 不支持BLOG和TEXT等大字段；
      • 使用的是表级锁，所以并发性能不一定高；
      • 表的最大大小是由max_heap_table_size参数决定的，默认值16M，对于已存在的表需要重建才能生效；
      • Memory存储引擎表和临时表容易混淆；临时表是系统使用的临时表，查询优化器为了优化查询时所使用；另一种临时表是使用create temporary 表名; 创建的临时表，临时表只对当前session可见；而Memory存储引擎表是使用Memory存储引擎所创建的普通表， 对所有可见；
      • 适用场景：
        • 等值查找性能高，用于查找或者是映射表，比如邮编和地区的映射表；
        • 用于保存数据分析中产生的中间表；
        • 用于缓存周期性聚合数据的结果表；

    • MRG_MYISAM

    • Archive

      • 以zlib对表数据进行压缩，磁盘I/O更少；
      • 只支持insert和select操作；
      • 支持行级锁；
      • 只允许在自增ID列上加索引；
      • 适用场景
        • 日志和数据采集类应用；

    • Federated

      • 特点
        • 提供了访问远程MySQL服务器上表的方法；
        • 本地不存储数据，数据全部存放到远程服务器上；
        • 本地需要保存表结构和远程服务器的连接信息；
      • 默认禁止该存储引擎，启用需要在创建表时增加federated参数；查看支持的引擎：show engines;；在my.cnf文件中增加一行federated=1然后重启MySQL服务器；
      • 适用场景：
        • 偶尔的统计分析及手工查询；关联关系查询效率低，不适合生产环境；

    • Tokudb

    • 等等等

  • 如果选择存储引擎？

    生产环境优先选择InnoDB存储引擎，除非InnoDB不能满足需求；

    选择存储引擎的参考条件：1.事务；2.备份；3.崩溃回复；4.存储引擎特性；

    除了必须，否则尽量不要使用混合存储引擎，不然很可能引发更多未知问题；

MySQL服务器参数

  1. MySQL获取配置信息的路径

    1. 命令行参数

      mysqld_safe –datadir=/data/sql_data

      • 1

    2. 配置文件(不同操作系统不一定相同)

      mysqld –help --verbose | grep -A 1 'Default options'

# /etc/my.cnf /etc/mysql/my.cnf /home/mysql/my.cnf ~/.my.cnf

      • 1
      • 2
      • 3

  2. MySQL配置参数的作用域

    1. 全局参数（需要在MySQL客户端内执行）

      # set global 参数名=参数值;
# set @@global.参数名=参数值;

# 例如：
SET GLOBAL  wait_timeout = 604800;
SET @@GLOBAL.wait_timeout = 604800;

SET GLOBAL event_scheduler = ON; 
SET @@global.event_scheduler = ON; 
SET GLOBAL event_scheduler = 1; 
SET @@global.event_scheduler = 1; 

# 配置之后需要重新登录mysql客户端才能生效；

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8
      • 9
      • 10
      • 11
      • 12
      • 13

    2. 会话参数

      # set [session] 参数名=参数值;
# set @@session.参数名=参数值;

      • 1
      • 2

  3. 内存配置相关参数

    1. 确定可以使用的内存上限

      第一前提是不能超过物理内存；在32位操作系统中，单进程的最大内存为3GB，所以在32位操作系统上，单进程MySQL也不能超过3GB；

    2. 确定MySQL的每个连接线程使用的内存

      对于每个连接单独分配缓存，当连接数量越来越多时，占用内存也就越多；并不是在连接初始化时为每个连接分配缓存区，而是在需要有查询操作时才会每个缓冲区分配内存；

      sort_buffer_size排序缓冲区大小，一旦查询需要排序，MySQL会为这个连接分配指定排序缓存区大小sort_buffer_size的全部内存，尽管该连接可能用不到那么多；

      join_buffer_size连接缓冲区大小，每个连接使用的缓冲区大小，如果一个查询中关联到多张表，会分配多个缓冲区；

      read_buffer_size读缓冲区大小，对一个MyISAM表进行全表扫描时分配的扫描缓冲区大小；MySQL只会在有查询需要的时候为其分配内存，也是一次性分配全部大小；另外，该参数的参数值是4K的整数倍数；

      read_rnd_buffer_size索引缓冲区大小，MySQL也只会在有查询需要时为其分配内存，但是分配的大小是所需大小，而不是参数指定的大小；

      以上参数都是针对每个连接线程分配的数值，如果连接数量太大，可能会造成内存溢出；

    3. 确定需要为操作系统保留多少内存

      生产环境建议专机专用，MySQL服务器专门使用一台服务器，不要与其他应用服务器放在一起；同时，也不建议一台数据库服务器中运行多个MySQL实例；

    4. 如何为缓冲池分配内存

      Innodb_buffer_pool_size，InnoDB缓冲池大小，该参数对InnoDB存储引擎的性能影响很大；InnoDB引擎会使用缓冲池延迟写入，将多个写入操作一起顺序地写入磁盘；

      InnoDB缓冲池大小 = 总内存 - (每个线程所需内存 * 连接数) - 系统保留内存

      MySQL手册推荐缓冲池内存大小为数据库服务器的内存容量的75%以上（实际中还需考虑其他因素）；

      key_buffer_size，MyISAM存储引擎用的缓冲区大小；

I/O相关配置参数

​ 这部分数据决定了MySQL数据库如何将缓冲池中的数据同步到磁盘上，以实现持久化保存；如果是在数据发生变化之后立即写入到磁盘上，是比较低效的， I/O成本很高；

​ Innodb_log_file_size控制InnoDB存储引擎单个事务日志文件大小，即Redo Log文件；如果业务非常繁忙，建议将该数值设置的尽量大一些；

​ Innodb_log_files_in_group控制InnoDB存储引擎事务日志文件的个数；

​ 事务日志的总大小即上述两个参数值的乘积；一般来说，事务日志的总大小应该能记录1个小时左右的事务信息；

​ 事务日志并不是每次提交都写入到文件中，而是先写入到事务日志的缓冲区，Innodb_log_buffer_size的大小控制了事务日志的缓冲区大小；该数值不应设置太大，一般这个缓冲区至少保留1秒左右数据即可，参考范围(32MB~128MB)；

​ 事务日志刷新的频繁程度Innodb_flush_log_at_trx_commit，这个参数有3个选择：0表示每秒进行一次log写入cache，并flush到磁盘，在MySQL进程崩溃时，至少会丢失1秒钟的事务；1是默认值，表示在每次事务提交执行log写入cache，并flush到磁盘，性能较差；2是建议值，表示每次事务提交，执行log数据写入到cache，每秒执行一次flush log到磁盘。其中0与2的区别是，0会在数据库进程崩溃时，丢失至少1秒的事务，而2则不会，只有在整个数据库服务器宕机时才会丢失至少1秒的事务；所以建议参数设置为2；

​ Innodb_flush_method=O_DIRECT，InnoDB刷新的方式，这个参数决定了InnoDB的日志文件和数据文件如何跟文件系统进行交互，不仅影响InnoDB怎么写入数据，还影响InnoDB怎么读数据；对于linux操作系统，建议将该参数的值设置为O_DIRECT方式，这个方式会告诉操作系统不要缓存数据，也不要预读，也就是说这个方式完全关闭了操作系统的缓存，并且使所有的读和写都直接通过存储设备来完成，来避免InnoDB和操作系统对数据的双重缓存；

​ Innodb_file_per_table=1，这个参数控制InnoDB如何使用表空间，如果设置了这个参数，InnoDB会为每个表设置单独的表空间，否则就会将InnoDB所有的表都存到系统表空间；建议使用这个参数；

​ Innodb_doublewrite=1，这个参数控制InnoDB是否使用双写缓存，是为了避免数据没有写完整导致数据损坏，建议使用这个参数；

​ InnoDB存储引擎是事务型存储引擎，为了减少在提交事务时产生的I/O开销，InnoDB采用了预写日志的方式，每次在提交事务的时候，先将数据写入到事务日志中，而不是将数据立即刷新到数据文件中，这样做是为了提高I/O性能，因为事务的修改使事务的数据和索引通常映射到表空间随机的位置，所以刷新数据变更到数据文件就会产生大量的随机I/O，而记录日志是顺序写入；所以相对于立即刷新数据到数据文件中，预写日志的方式提高了I/O性能。而且，一旦事务日志安全写入到磁盘中，事务就算是持久化了，这时即使数据的变更还没写入数据文件发生宕机情况，后来也能通过事务日志来恢复已经提交的事务；

MyISAM的I/O相关配置：

​ delay_key_write：控制关键字缓冲中的脏块什么时候刷新到磁盘中；OFF表示每次写操作后刷新键缓冲中的脏块到磁盘，这是最安全的操作，但是性能较差；ON只对在建表时指定了delay_key_write选项的表使用延迟刷新；ALL对所有MyISAM表都使用延迟键写入；需要注意的是：如果启用延迟键写入，当服务器崩溃并且缓存中有的块没有写入到磁盘文件，这就会造成MyISAM表索引文件的损坏，这是就需要使用repair table这个命令进行修复；

安全相关配置参数

​ expire_logs_days指定自动清理binlog(二进制日志)的天数；这个参数的设置应该至少可以覆盖2次全备所间隔的天数；

​ max_allowed_packet控制MySQL可以接收的包的大小；同时也会影响一个用户定义的变量的最大容量；参考值如32MB；如果存在主从配置，而从数据库配置的该值小于主数据库该值的配置，可能会出现同步失败的情况；

​ skip_name_resolve禁用DNS查找；当连接服务器的时候，默认情况下MySQL试图对连接的客户端主机所使用的主机的域名，进行域名的正向及反向查找，如果DNS服务器出现了问题，会出现大量的堆积和延时，严重降低性能，建议开启禁用DNS查找功能；

​ sysdata_is_now确保sysdate()返回确定性日期；建议增加该参数；

​ read_only禁止非super权限的用户写入权限，在主从同步架构中使用较多，避免从服务器的数据被破坏；

​ skip_slave_start禁用slave自动恢复，也用于主从同步架构中。建议启动该功能以防止在出现问题后从服务器自动恢复同步，因为这时候还可能存在不确定的问题；

​ sql_mode设置MySQL所使用的SQL模式；在默认的模式下，MySQL对于SQL语句的要求是比较宽松的，比如在执行分组查询时，允许查询中使用的非聚合函数的列不全部出现在group_by从句中，这其实不符合SQL的规范，如果修改sql_mode为严格模式，会使这样的语句出现语法错误的情况；可选选项：1. strict_trans_tables，在这种模式下，如果给定的数据不能插入到事务型存储引擎中，会中断此次操作，但对非事务型存储引擎无影响；2. no_engine_subtitution，在这种模式下，如果在进行创建表时指定存储引擎，而指定的存储引擎不可用，则不会使用默认的存储引擎来建表，即创建表失败而不是创建默认存储引擎的表；3. no_zero_date，在这种模式下，不能将类似于”0年0月0日”这种日期写入到数据库中日期对应的字段上；4. no_zero_in_date，在这种模式下，不能将含有0的日期写入到数据库表中日期对应的字段上；5. only_full_group_by，在这种模式下，查询中使用的非聚合函数的列需要全部写在group_by从句中，否则SQL语句不能执行；不建议改动生产环境的sql_mode参数值；

其他常用配置参数

​ sync_binlog控制MySQL如何向磁盘刷新binlog；默认值为0，表示MySQL不会主动刷新，而是由操作系统来决定什么时候刷新cache到磁盘；如果这个数值大于0，表示的是两次刷新之间间隔了几次二进制日志的写入操作；如果数值设置过大，可能会导致主从同步的数据库在出现问题时，数据不同步，而且很难恢复；

​ tmp_table_size和max_heap_table_size这两个蚕食一起使用，用于控制使用Memory存储引擎的内存临时表的大小；这两个参数的值应该保持一致，且都不要太大，以防内存溢出；

​ max_connections，控制允许的最大连接数；默认值只有100，参考值：2000，具体根据实际业务环境；

数据库设计对性能的影响

  1. 数据库设计对性能的影响

    1. 过分地反范式化为表建立了太多的列（很不符合范式化原则）

      虽然MySQL允许为一个数据表建立很多列，但是由于MySQL的插件式架构的原因，MySQL服务器层和存储引擎层是分离的，MySQL的存储引擎层在工作时需要把在服务器层和存储引擎层之间通过缓冲格式来拷贝数据，然后在服务器层将缓冲的内容解析成各个列，这个过程成本是非常高的，特别是对于MyISAM这个变长结构，InnoDB这种行结构进行解析时还必须进行转换，这个转换的成本取决于列的数量，所以一个表的列太多，使用这个表时就会带来额外的CPU消耗，所以在设计表的时候，不要把所有的相关的列放在一个表中，而是要按照范式化对表进行拆分。简而言之，一个表有太多的列，会对性能有很大的影响；

    2. 过分地范式化造成太多的表关联

      关联查询非常消耗性能，所以性能会随着关联表数量的增加而下降；MySQL限制一张表最多关联61张表，这个数量对于多数人来说足够了，但是为了提升性能，尽量做到关联表数量在10个以下，这个时候可以不必严格遵循范式化设计原则；比如常用的两张关联表可以设计成一张表；

    3. 在OLTP环境中使用不恰当的分区表

      分区表可以将一张大表从物理存储上按照分区键分成多个小表，这里说的分区表与常说的分库分表还是有差别的；分区表是在同一个数据库实例下所进行的，而在物理存储上分成了多个小表，但是在使用时逻辑上还是一个表；而分库分表不仅是在物理上进行拆分，在逻辑上也进行了拆分，而且分库分表后，一般这些小表不是在同一个数据库实例下；

      建立分区表时，分区键的选择非常关键，选择的不好，会造成查询的时候需要跨多个分区进行查询，反而降低了性能；

    4. 使用外键保证数据的完整性

      使用外键约束来保证数据的完整性，但是这样的效率是非常低的，因为在对使用外键的表进行数据修改时，MySQL都要对外键进行检查，这样就带来了额外的锁的开销，降低了数据库的修改的效率，另外，在进行数据库备份恢复、归档维护时，如果存在外键，不能对表进行快速清空操作，只能使用delete来执行，这就加大了对大表的清理复杂度；所以，尽量少使用外键约束；

总结

性能优化顺序

  1. 数据库结构设计和SQL语句；（这一优化可能会随着业务的升级，需要持续地进行优化）
  2. 数据库存储引擎的选择和参数的配置；
  3. 系统选择及优化；
  4. 硬件升级；

MySQL性能测试

​ 如何知道是否该进行数据库优化？需要先对MySQL性能进行测试。

MySQL基准测试

  1. 定义

    基准测试是一种测量和评估软件性能指标的活动，用于建立某个时刻的性能基准，以便当系统发生硬件变化时重新进行基准测试以评估变化对性能的影响；

  2. 基准测试与压力测试的关联及区别

    基准测试是针对系统设置的一种压力测试，可以检验改变配置参数后对性能的影响；可以观察系统在不同压力的情况下的行为、评估系统的容量；但是基准测试与通常所说的压力测试还是有一定的区别的；基准测试的特点是：直接、简单、易于比较，基准测试的结果通常是用测试工具所生成的，只能用于评估服务器的处理能力， 一般不关心业务逻辑，所使用的的查询和业务的真实性可能没关系；而压力测试则是通过对真实的业务数据进行测试，获得真实系统所能承受的压力， 一般需要针对不同的主题，比如对购物车模块进行压力测试，所使用的的数据和查询是真实业务中用到的；也可以说基准测试是简化的压力测试；

  3. 基准测试的目的

    建立MySQL服务器的性能基准线，主要是为了测试MySQL服务器的当前运行状况；可以检验改变配置参数后对性能的影响；

    模拟比当前系统更高的负载，以找出系统随着压力的增加所遇到的扩展瓶颈；通过增加数据库并发，观察QPS、TPS变化，确定并发量与性能的最优关系；

    测试不同的硬件、软件和操作系统配置；比如测试linux系统下不同磁盘分区格式对于数据库性能是否由影响；

    判断新的硬件设备是否配置正确；

  4. 如何进行基准测试

    1. 对整个系统进行基准测试

      从系统入口进行测试，比如网站的web前端，手机APP前端；

      优点：能够测试整个系统的性能，包括web服务器缓存、数据库等；

      缺点：测试设计复杂、消耗时间长；

    2. 单独对某一组件进行基准测试，如只对MySQL服务器进行必要的基准测试；

      优点：测试设计简单、所需耗费时间短；

      缺点：无法全面了解整个系统的性能基线；

  5. MySQL基准测试的常见指标

    1. 单位时间内所处理的事务数(TPS)
    2. 单位时间内所处理的查询数(QPS)
    3. 响应时间
    4. 并发量：同时处理的查询请求的数量(不等于同时在线人数等指标)，正在工作中的并发的操作数或同时工作的数量，web服务器的并发量也不一定等于MySQL数据库的并发量；

  6. 基准测试的步骤

    1. 计划和设计基准测试
      1. 是对整个系统还是对某一组件进行基准测试；
      2. 使用什么样的数据来进行基准测试，比如希望基准测试能反映实际生产环境的情况，那么最好使用生产环境的数据库的完全备份来进行；而如果只想知道某个配置参数的调整对于性能的影响，也可以使用简单的数据进行测试；
      3. 准备基准测试的数据及数据搜集脚本；包括CPU、IO、网络流量、状态与计数器信息等；
      4. 运行基准测试；
      5. 保存及分析基准测试结果；最好能以可视化展示分析结果；

  7. 基准测试的工具（常用）

    1. mysqlslap，MySQL5.1版本之后自带的基准测试工具，无需额外安装；可以模拟服务器负载，并输出相关统计信息；可以指定也可以自动生成查询语句；

      1. 常用参数说明：

        1. –auto-generate-sql，由系统自动生成SQL脚本进行测试；
        2. –auto-generate-sql-add-autoincrement，在生成的表中增加自增ID；
        3. –auto-generate-sql-load-type，指定测试中使用的查询类型；默认使用混合类型同时包括删除、查询、更新等；
        4. –auto-generate-sql-write-number，指定初始化数据时生成的数据量；
        5. –concurrency，指定并发线程的数量；
        6. –engine，指定要测试表的存储引擎，可以用逗号分隔多个存储引擎；
        7. –no-drop，指定不清理测试数据，默认是测试完成后清理测试数据的；
        8. --iterations，指定测试运行的次数；
        9. --number-of-queries，指定每一个线程执行的查询数量；
        10. --debug-info，指定输出额外的内存及CPU统计信息；
        11. --number-int-cols，指定测试表中包含的int类型列的数量；
        12. –number-char-cols，指定测试表中包含的varchar类型的数量；
        13. –create-schema，指定了用于执行测试的数据库的名字；注意在生产环境中测试时，不要将数据生成到生产环境中；
        14. –query，指定自定义的SQL脚本；
        15. –only-print，并不运行测试脚本，而是把生成的脚本打印出来；

      2. 示例：（在终端命令行下执行）

        mysqlslap -u用户 -p密码 --concurrency=1,50,100,200 --iterations=3 --number-int-cols=5 --number-char-cols=5 --auto-generate-sql --auto-generate-sql-add-autoincrement --engine=myisam,innodb --number-of-queries=10 --create-schema=test


# 输出结果
# Benchmark
# 	Running for engine myisam
# 	Average number of seconds to run all queries: 0.036 seconds
# 	Minimum number of seconds to run all queries: 0.025 seconds
# 	Maximum number of seconds to run all queries: 0.043 seconds
# 	Number of clients running queries: 1
# 	Average number of queries per client: 10
# 
# Benchmark
# 	Running for engine myisam
# 	Average number of seconds to run all queries: 1.121 seconds
# 	Minimum number of seconds to run all queries: 0.948 seconds
# 	Maximum number of seconds to run all queries: 1.339 seconds
# 	Number of clients running queries: 50
# 	Average number of queries per client: 0
#   ......
		
		
# 当出现如下显示时，表示已经超出最大连接数，需要调整最大连接数
# mysqlslap: Error when connecting to server: 1040 Too many connections
# mysqlslap: Error when connecting to server: 1040 Too many connections
# mysqlslap: Error when connecting to server: 1040 Too many connections
# mysqlslap: Error when connecting to server: 1040 Too many connections
# mysqlslap: Error when connecting to server: 1040 Too many connections


        • 1
        • 2
        • 3
        • 4
        • 5
        • 6
        • 7
        • 8
        • 9
        • 10
        • 11
        • 12
        • 13
        • 14
        • 15
        • 16
        • 17
        • 18
        • 19
        • 20
        • 21
        • 22
        • 23
        • 24
        • 25
        • 26
        • 27
        • 28
        • 29

    2. sysbench，需要额外安装

      1. 安装， github地址： https://github.com/akopytov/sysbench

      分为编译安装和直接使用包管理工具安装

      # 下载最新版本，注意现在可能更新了
cd /home/
wget https://github.com/akopytov/sysbench/archive/1.0.19.zip

# 解压，文件名可能不一样
unzip sysbench-0.5.zip

# 编译
./autogen.sh

# 具体路径视情况而定
./configure --with-mysql-includes=/usr/local/mysql/include/ --with-mysql-libs=/usr/local/mysql/lib/

# 安装
make && make install


      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8
      • 9
      • 10
      • 11
      • 12
      • 13
      • 14
      • 15
      • 16
      # 根据该工具github说明：
# The easiest way to download and install sysbench on Linux is using binary package repositories hosted by packagecloud. The repositories are automatically updated on each sysbench release. Currently x86_64, i386 and aarch64 binaries are available.

# Multiple methods to download and install sysbench packages are available and described at https://packagecloud.io/akopytov/sysbench/install.

# RHEL/CentOS 
curl -s https://packagecloud.io/install/repositories/akopytov/sysbench/script.rpm.sh | sudo bash

sudo yum -y install sysbench

      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8
      • 9

      1. 常用参数

        1. –test，用于指定索要执行的测试类型，支持以下参数：
          1. Fileio，文件系统I/O性能测试；
          2. cpu，CPU性能测试；
          3. memory，内存性能测试；
          4. Oltp，测试要指定具体的lua脚本；lua脚本位于sysbench-0.5/sysbench/tests/db；
          5. –mysql-db，用于指定执行基准测试的数据库名；
        2. –mysql-table-engine，用于指定所使用的存储引擎；
        3. –oltp-tables-count，执行测试的表的数量；
        4. –oltp-table-size，指定每个表中的数据行数；
        5. –max-time，指定最大的测试时间；
        6. –report-interval，指定间隔多长时间输出一次统计信息；
        7. –mysql-user，指定执行测试的MySQL用户；
        8. –mysql-password，指定执行测试的MySQL用户的密码；
        9. prepare，用于准备测试数据；
        10. run，用于实际进行测试；
        11. cleanup，用于清理测试数据；

      2. sysbench基准测试示例

        # 测试cpu
sysbench --test=cpu --cpu-max-prime=10000 run
	
# 输出结果如下
# sysbench 1.0.19 (using bundled LuaJIT 2.1.0-beta2)
# 
# Running the test with following options:
# Number of threads: 1
# Initializing random number generator from current time
# 
# 
# Prime numbers limit: 10000
# 
# Initializing worker threads...
# 
# Threads started!
# 
# CPU speed:
#     events per second:   869.93
# 
# General statistics:
#     total time:                          10.0001s
#     total number of events:              8701
# 
# Latency (ms):
#          min:                                    1.12
#          avg:                                    1.15
#          max:                                   12.88
#          95th percentile:                        1.21
#          sum:                                 9994.71
# 
# Threads fairness:
#     events (avg/stddev):           8701.0000/0.00
#     execution time (avg/stddev):   9.9947/0.00


        • 1
        • 2
        • 3
        • 4
        • 5
        • 6
        • 7
        • 8
        • 9
        • 10
        • 11
        • 12
        • 13
        • 14
        • 15
        • 16
        • 17
        • 18
        • 19
        • 20
        • 21
        • 22
        • 23
        • 24
        • 25
        • 26
        • 27
        • 28
        • 29
        • 30
        • 31
        • 32
        • 33
        • 34
        • 35
        # 测试文件IO， 文件总大小1G
# 准备数据，生成总大小为1G的多个文件
sysbench --test=fileio --file-total-size=1G prepare

# 输出如下
# sysbench 1.0.19 (using bundled LuaJIT 2.1.0-beta2)

# 128 files, 8192Kb each, 1024Mb total
# Creating files for the test...
# Extra file open flags: (none)
# Creating file test_file.0
# Creating file test_file.1
# Creating file test_file.2
# ......
# Creating file test_file.127
# 1073741824 bytes written in 8.51 seconds (120.26 MiB/sec).

        • 1
        • 2
        • 3
        • 4
        • 5
        • 6
        • 7
        • 8
        • 9
        • 10
        • 11
        • 12
        • 13
        • 14
        • 15
        • 16
        # 开始测试,每隔1s输出一个统计信息，测试模式混合，包含随机读写和顺序读写
sysbench --test=fileio --num-threads=8 --init-rng=on --file-total-size=1G --file-test-mode=rndrw --report-interval=1 run

# 新版本可能会提示 --init-rng=on 无效

# 输出内容如下：
# WARNING: --num-threads is deprecated, use --threads instead
# sysbench 1.0.19 (using bundled LuaJIT 2.1.0-beta2)
# 
# Running the test with following options:
# Number of threads: 8
# Report intermediate results every 1 second(s)
# Initializing random number generator from current time
# 
# 
# Extra file open flags: (none)
# 128 files, 8MiB each
# 1GiB total file size
# Block size 16KiB
# Number of IO requests: 0
# Read/Write ratio for combined random IO test: 1.50
# Periodic FSYNC enabled, calling fsync() each 100 requests.
# Calling fsync() at the end of test, Enabled.
# Using synchronous I/O mode
# Doing random r/w test
# Initializing worker threads...
# 
# Threads started!
# 
# [ 1s ] reads: 54.35 MiB/s writes: 36.24 MiB/s fsyncs: 7384.73/s latency (ms,95%): 1.759
# [ 2s ] reads: 44.91 MiB/s writes: 29.87 MiB/s fsyncs: 6046.54/s latency (ms,95%): 1.996
# [ 3s ] reads: 44.09 MiB/s writes: 29.45 MiB/s fsyncs: 6127.80/s latency (ms,95%): 1.667
# [ 4s ] reads: 41.25 MiB/s writes: 27.42 MiB/s fsyncs: 5536.19/s latency (ms,95%): 1.759
# [ 5s ] reads: 43.12 MiB/s writes: 28.75 MiB/s fsyncs: 5871.15/s latency (ms,95%): 2.106
# [ 6s ] reads: 42.04 MiB/s writes: 28.11 MiB/s fsyncs: 5751.68/s latency (ms,95%): 1.791
# [ 7s ] reads: 40.48 MiB/s writes: 26.98 MiB/s fsyncs: 5631.02/s latency (ms,95%): 1.759
# [ 8s ] reads: 28.06 MiB/s writes: 18.70 MiB/s fsyncs: 3719.03/s latency (ms,95%): 1.791
# [ 9s ] reads: 48.80 MiB/s writes: 32.45 MiB/s fsyncs: 6660.93/s latency (ms,95%): 1.759
# [ 10s ] reads: 43.94 MiB/s writes: 29.36 MiB/s fsyncs: 6005.30/s latency (ms,95%): 1.925
# 
# File operations:
#     reads/s:                      2727.33
#     writes/s:                     1817.69
#     fsyncs/s:                     5906.41
# 
# Throughput:
#     read, MiB/s:                  42.61
#     written, MiB/s:               28.40
# 
# General statistics:
#     total time:                          10.1189s
#     total number of events:              104750
# 
# Latency (ms):
#          min:                                    0.00
#          avg:                                    0.76
#          max:                                  898.02
#          95th percentile:                        1.82
#          sum:                                79958.46
# 
# Threads fairness:
#     events (avg/stddev):           13093.7500/292.10
#     execution time (avg/stddev):   9.9948/0.00

        • 1
        • 2
        • 3
        • 4
        • 5
        • 6
        • 7
        • 8
        • 9
        • 10
        • 11
        • 12
        • 13
        • 14
        • 15
        • 16
        • 17
        • 18
        • 19
        • 20
        • 21
        • 22
        • 23
        • 24
        • 25
        • 26
        • 27
        • 28
        • 29
        • 30
        • 31
        • 32
        • 33
        • 34
        • 35
        • 36
        • 37
        • 38
        • 39
        • 40
        • 41
        • 42
        • 43
        • 44
        • 45
        • 46
        • 47
        • 48
        • 49
        • 50
        • 51
        • 52
        • 53
        • 54
        • 55
        • 56
        • 57
        • 58
        • 59
        • 60
        • 61
        • 62
        • 63
        # 测试数据库
# 先创建一个数据库，并创建一个用于测试的用户且授权访问
create database test charset=utf8;

grant all privileges on test.* to user_test@'localhost' identified by 'PassWord123';

        • 1
        • 2
        • 3
        • 4
        • 5
        # 准备好测试的数据库之后，在命令行输入：
sysbench --test=测试脚本名 --mysql-table-engine=innodb --oltp-table-size=10000 --mysql-db=test --mysql-user=user_test --mysql-password=PassWord123 --oltp-tables-count=10 --mysql-socket=/usr/local/mysql/data/mysql.sock run

        • 1
        • 2

    ​

数据库结构优化的目的

  1. 尽量减少数据的冗余；
  2. 尽量避免数据维护中出现更新、插入和删除异常；
    1. 更新异常：更改表中的某个实体的单独属性时，需要对多行进行更新；
    2. 插入异常：表中的某个实体随着另一个实体而存在，即缺少了一个实体，就无法表示另一个实体；
    3. 删除异常：删除表中的某一个实体，会导致其他实体的消失；
  3. 节约数据存储空间；
  4. 提高数据查询效率；

数据库结构设计的步骤

  1. 需求分析

    全面了解产品设计的存储需求，数据处理需求，数据的安全性和完整性；

  2. 逻辑设计

    设计数据的逻辑存储结构；搞清楚数据实体之间的逻辑关系，解决数据冗余和数据维护异常等问题；

  3. 物理设计

    根据所使用的数据库特点进行表结构设计；

    根据需求选择关系型数据库(Oracle/SQLServer/Mysql/PostgresSQL)、非关系型数据库(MongoDB/Redis/Hadoop)

    选择存储引擎：InnoDB、MyISAM等；

  4. 维护优化

    根据实际情况对索引、表结构等进行优化；

数据库设计范式

  1. 数据库设计的第一范式

    1. 数据库表中的所有字段都只具有单一属性；
    2. 单一属性的列是由基本的数据类型所构成的；如整型、浮点型、字符型等；
    3. 设计出来的表都是简单的二维表；

  2. 数据库设计的第二范式

    ​ 要求一个表中只具有一个业务主键(不包含联合主键的情况)，也就是说符合第二范式的表中不能存在非主键列只对部分主键的依赖关系；

  3. 数据库设计的第三范式

    ​ 指的是每一个非主属性既不部分依赖于也不传递依赖于业务主键，也就是在第二范式的基础上消除了非主属性对主键的传递依赖；

设计案例：

需求：按下面的需求设计一个电子商务网站的数据库结构

  1. 该网站只销售图书类商品；
  2. 该网站具有以下功能：用户登录、用户管理、商品展示、商品管理、供应商管理、在线销售；
具体设计思路：

  1. 需求分析及逻辑设计（不一定符合实际生产环境）

    1. 用户登录及用户管理功能；

      1. 用户必须注册并登录系统才能进行网上交易，用户名作为用户信息的业务主键；

      2. 为了保证用户账户安全，同一时间一个用户只能在一个地方登录；（需要在表中记录用户登录状态）

      3. 用户信息：{用户名，密码，手机号，姓名(网名)，注册日期，在线状态，出生日期}；

        只有一个业务主键，一定是符合第二范式；没有属性和业务主键存在传递依赖的关系，符合第三范式；

    2. 商品展示及商品管理功能

      1. 商品信息：{商品名称，分类名称，出版社名称，图书价格，图书描述，作者}；
      2. 为了避免数据维护异常，如果将上述的信息都存放在一张表，会造成添加一个分类的时候，没有添加对应的图书，就会出现异常情况，所以将上述信息拆分到多张表中；
      3. 如拆分到3张表：商品信息：{商品名称，出版社名称，图书价格，图书描述，作者}，分类信息：{分类名称，分类描述}， 商品分类(对应关系表)：{商品名称，分类名称}

    3. 供应商管理功能

      1. 供应商信息：{出版社名称，地址，电话，联系人，银行账号}

    4. 在线销售功能

      1. 在线销售：{订单编号， 下单用户名，下单日期，订单金额，订单商品分类，订单商品名，订单商品单价，订单商品数量，支付金额，物流单号}；
      2. 只存在一个业务主键“订单编号”，符合第二范式；
      3. 订单商品单价、订单商品数量、订单编号之间存在着传递关系，不符合第三范式；
      4. 存在数据冗余，订单表的商品信息和商品信息表的数据重复；所以要进行拆分；
      5. 将在线销售表中的数据拆分为两个表：订单表：{订单编号，下单用户名，下单日期，支付金额，物流单号}，订单商品关联表：{订单编号，订单商品分类，订单商品名，商品数量}

  2. 由于严格按照范式化设计得到的数据库表结构不一定符合实际生产中的高效SQL，所以要进行一定的反范式化设计以优化查询效率；

    1. 比如范式化设计的商品信息包含这三张表：

      商品信息：{商品名称，出版社名称，图书价格，图书描述，作者}，

      分类信息：{分类名称，分类描述}，

      商品分类(对应关系表)：{商品名称，分类名称}

      上述的设计每次都要进行多次关联查询，反范式化改造后的表结构：(只需2张表，减少了关联查询)

      商品信息：{商品名称，分类名称，出版社名称，图书价格，图书描述，作者}，

      分类信息：{分类名称，分类描述}

    2. 在线销售功能数据库表：

      订单表：{订单编号，下单用户名，下单日期，支付金额，物流单号}，

      订单商品关联表：{订单编号，订单商品分类，订单商品名，商品数量}

      由于可能出现历史订单因价格的变化而发生变化、下单之后手机号与用户信息不同的情况，所以需要添加冗余字段，于是将这两张表反范式化设计成：

      订单表：{订单编号， 下单用户名，手机号，下单日期，支付金额，物流单号，订单金额}

      订单商品关联表：{订单编号， 订单商品分类，订单商品名，商品数量，商品单价}

    范式化设计与反范式化设计的优缺点

    范式化设计的优点：

    1. 可以尽量减少数据冗余；
    2. 范式化的更新操作比反范式化更快；
    3. 范式化的表通常比反范式化更小；

    范式化设计的缺点：

    1. 对于查询需要对多个表进行关联；
    2. 更难进行索引优化；

    反范式化设计的优点：

    1. 可以减少表的关联查询；（数据量特别大的时候优势更明显）
    2. 可以更好地进行索引优化；

    反范式化设计的缺点：

    1. 存在数据冗余及数据维护异常；
    2. 对数据的修改需要更多的成本；

  3. 数据表的物理设计

    1. 定义数据库、表及字段的命名规范；

      1. 数据库、表及字段的命名要遵守可读性原则；按统一规定的大小写命名，单词之间下划线隔开；
      2. 数据库、表及字段的命名要遵守表意性原则；能做到见名知意，尽量不存在歧义；
      3. 数据库、表及字段的命名要遵守长名原则；尽量不缩写，尽量将表的作用表达完整；

    2. 选择合适的存储引擎；

      擎事务锁粒度主要应用忌用
      SAM不支持支持并发插入的表级锁SELECT，INSERT读写操作频繁
      _MYISAM不支持支持并发插入的表级锁分段归档，数据仓库全局查找过多
      oDB支持支持MVCC的行级锁事务处理无
      hive不支持行级锁日志记录，仅insert，select随机读取、更新、删除
      cluster支持行级锁高可用性大部分应用

      除了特殊需求，尽量优先选择InnoDB存储引擎；

    3. 为表中的字段选择合适的数据类型；

      当一个列可以选择多种数据类型时，应该优先考虑数字类型，其次是日期或二进制类型，最后是字符类型。对于想同级别的数据类型，应该优先选择占用空间小的数据类型；

      1. 整数类型及其取值范围：

        占用空间取值范围(SIGNED)取值范围(UNSIGNED)
        t1字节-128~127
        nt2字节-32768~32767
        int3字节-223~223-1 (约正负838万)
        4字节-231~231-1 (约正负21亿)0~232-1
        8字节-263~263-1 (约正负9*1018)0~264-1

      2. 实数类型的选择

        存储空间是否精确类型
        4字节否
        8字节否
        l每4个字节存9个数字，小数点占1个字节

        注意：设计到金钱或其他精确计算的，只能使用decimal；如DECIMAL(18,9)需要9个字节来存储；

      3. char 和 varchar类型

        1. varchar：用于存储可变长度的字符串，只占用必要的存储空间，单位是字符数，而不是字节数；当列的最大长度小于255，则会占用1个额外的字节用于记录字符串长度；当列的最大长度大于255则要占用2个额外字节用于记录字符串长度；
        2. char：用于固定长度的字符串，占用的存储空间即为该固定长度；char类型的列会自动删除末尾的空格；char类型的最大长度为255；适用于存储固定长度或者长度近似的值，比如存储HASH后或者MD5转换后的定长的值；char类型适合存储短字符串或者经常会更新的字符串列；
        3. 例如：定义的是varchar(128)，实际存储的是”mysql”，那么就只会占用5个字符的空间；如果定义的是char(128)，即使存储的是”mysql”，也会占用128个字符长度；字符和字节有区别，utf8中一个汉字通常占用2个字节；
        4. varchar长度的选择：
          1. 使用最小的符合需求的长度；
          2. varchar(5)和varchar(200)分别存储“mysql”字符串的性能是不同的；这两个长度都没有超过255，所以都只会占用一个额外的字节来存储，即两个都占用的是6个字节，但是MySQL为了优化查询，在内存中对字符串使用的是固定的宽度，如果把长度定义的太长，就会消耗更多的内存；
        5. varchar的适用场景：
          1. 字符串列的最大长度比平均长度大得多；比如某个字段就只有一个值特别长，其他都比较短；
          2. 字符串列很少被更新；
          3. 使用了多字节字符集存储字符串；

      4. 日期类型

        1. DATETIME类型，占用8个字节；
          1. 以YYYY-MM-DD HH:MM:SS[.fraction]格式存储日期时间；datetime = YYYY-MM-DD HH:MM:SS， datetime(6) = YYYY-MM-DD HH:MM:SS.fraction；
          2. 需要注意的是DATETIME类型与时区无关，不会根据时区的变化而变化；
          3. 时间范围：1000-01-01 00:00:00~9999-13-31 23:59:59；
        2. TIEMSTAMP，时间戳类型，占用4个字节；
          1. 存储了由格林尼治时间1970年1月1日0点到当前时间的秒数；以YYYY-MM-DD HH:MM:SS[.fraction]的格式显示；
          2. 时间戳类型保存的时间范围是：1970-01-01~2038-01-19；
          3. timestamp类型显示依赖于所指定的时区；
          4. 在行的数据修改时可以自动修改timestamp列的值；通常使用这个功能记录某个数据最后修改时间；
        3. date类型
          1. 用于存储日期，比如存储生日；
          2. 只需要3个字节，占用的字节数比使用字符串、datetime、int存储要少；
          3. 使用date类型还可以利用日期时间函数进行日期之间的计算；
          4. 取值范围：1000-01-01~9999-12-31；
        4. time类型
          1. 用于存储时间，格式为HH:MM:SS；
        5. 注意：不要使用字符串类型或int类型来存储日期时间数据；其一：日期类型占用空间通常比字符串小；其二：查找时可以利用日期时间函数进行快速过滤查询以及计算；

    4. 建立数据库结构

      1. 主键应该尽可能的小；
      2. 主键应该是顺序增长的，提升插入效率；
      3. InnoDB的主键和业务主键可以不同；

MySQL架构层优化

  1. MySQL的复制功能

    1. MySQL的复制功能可以分担读负载(简单的复制无法分担写负载)

      横向增加数据库服务器的数量，增加一个或多个备库，能够分担数据库的读负载，同时也为高可用、容灾、备份提供了更多的选择；

    2. MySQL的复制功能可以实现在不同服务器上的数据分布，利用二进制日志增量进行，不需要占用太多的带宽，但是使用基于行的复制在进行大批量更改时会对带宽带来一定的压力，在跨IDC机房的情况下影响更大，应该分批进行；MySQL的复制是基于主库上的二进制日志文件来实现的，所以同一时刻，可能会出现主从数据库的数据并不同步的情况；

    3. 实现数据读取的负载均衡需要配合其他组件完成；利用DNS轮询的方式将程序的读取连接到不同的备份数据库；使用LVS、haproxy等代理方式；

    4. 非共享架构，同样的数据分布在多台服务器上，可以有效减少因某一数据库服务器数据丢失造成的损失；

    5. 复制与备份概念不同；备份是十分有必要的；

  2. MySQL服务层日志，包括二进制日志、慢查询日志、通用日志等；(不同于存储引擎层日志)

    1. 二进制日志binlog

    2. 二进制日志记录了所有对MySQL数据库的修改事件，包括增删改查事件和对表结构修改事件；二进制日志中记录的都是已经成功执行过的事件，语法错误或者回滚的事件不会记录在与二进制日志中；

    3. MySQL提供了binlog命令行工具来对二进制日志进行相关配置；

     如设置二进制日志的格式：
 

 3. 查看二进制日志的格式:`show variables like 'binlog_format';`，默认使用的是ROW格式，即基于行的日志，这种格式会记录具体到每一行的数据前后变化情况，在数据没有备份却被破坏时，有时候可以通过查看二进制日志，对数据进行反向处理来达到恢复数据的目的；除了基于行的格式ROW，另外还支持基于段的格式STATEMENT、混合模式MIXED；其中推荐使用ROW或MIXED；

 4. MySQL二进制日志格式对复制的影响

  5. 基于SQL语句的复制(SBR)，对应的是STATEMENT；优点是生成的日志量少，节约网络传输I/O；不强制要求主从数据库的表的定义完全相同；相比于基于行的复制方式更为灵活；缺点是对于非确定性事件，无法保证主从复制数据的一致性，比如UUID()、user()等函数；相比于基于行复制的方式在从服务器上执行时需要更多的行锁；
  6. 基于行的复制(RBR)，对应的是ROW；优点是可以应用与任何SQL的复制包括非确定性函数如UUID()、user()等；可以完全保证主从数据一致；可以减少数据库锁的使用，增加并发性能；缺点是要求主从数据库的表结构必须相同，否则可能中断复制；无法在从服务器上单独执行触发器；

    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9

  3. MySQL复制的工作方式

    文字描述：首先主服务器中数据发生变化，会被记录到二进制日志binlog中，接着从服务器的I/O线程与主服务器建立普通的客户端连接，然后在主服务器上启动一个特殊的二进制转储线程binlog_dump，从服务器通过这个转储线程对主服务器中二进制日志进行读取，然后将该日志写入到从服务器的中继日志relay_log中，这时SQL线程会读取中继日志relay_log中的内容来对从服务器进行复制；当主从服务器中的数据相同时，转储线程不会对事件进行轮询，而是进入休眠状态，只有当主服务器数据发生变化时，会发送一个信号，重新唤醒该线程；

    上述复制的前提是主服务器开启了二进制日志，有些版本的MySQL服务器默认不会开启二进制日志，需要手动开启；

  4. 基于日志点的复制配置步骤

    1. 设置复制账号

      # 在主服务器上建立复制账号,用户名repl
create user repl@'IP' identified by 'PassWord123';

# 授予复制权限
grant replication slave on 数据库名.表名 to 'repl'@'IP';


      • 1
      • 2
      • 3
      • 4
      • 5
      • 6

    2. 配置主服务器

      必须的参数(配置文件中修改)：

      ​ bin_log = mysql-bin

      ​ server_id = 100

    3. 配置从服务器

      bin_log = mysql-bin

      server_id = 101

      relay_log = mysql-relay-bin # 中继日志，(默认使用主机名，可能会变，所以需要自己定)

      log_slave_update = on [可选]

      read_only = on [可选]

    4. 初始化从服务器数据

      # 选择mysql自带的命令行工具（并发性能影响较大）
mysqldump --master-data=2 -single-transation

# 或者选择另外的工具（并发性能影响较小）
xtrabackup --slave-info


      • 1
      • 2
      • 3
      • 4
      • 5
      • 6

      也可以使用xtrabackup –slave-info(并发影响较小)

    5. 启动复制链路

      # 在从服务器上执行
CHANGE MASTER TO MASTER_HOST='master_host_ip', 
				 MASTER_USER='repl', 
				 MASTER_PASSWORD='PassWord123',
                 MASTER_LOG_FILE='mysql_log_file_name', 
                 MASTER_LOG_POS=4;                 

start slave;


      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8
      • 9

  5. 基于GTID复制

    GTID即全局事务ID，其保证了为每一个在主服务器上提交的事务在复制集群中可以生成一个唯一的ID；

    基于GTID复制的步骤

    1. 建立复制账号(同上)

      # 在主服务器上建立复制账号,用户名repl
create user repl@'IP' identified by 'PassWord123';

# 授予复制权限
grant replication slave on 数据库名.表名 to 'repl'@'IP';


      • 1
      • 2
      • 3
      • 4
      • 5
      • 6

    2. 配置主服务器

      bin_log = /usr/local/mysql/log/mysql-bin

      server_id = 100

      gtid_mode = on

      enforce-gtid-consistency = on # 注意：启用该参数之后，一些操作就不能再进行，比如：不能通过查询来建立一个新的表；不能再事务中使用create temporary table建立临时表；不能使用关联更新事务表和非事务表；

      log-slave-updates = on

    3. 配置从服务器

      server_id = 101

      relay_log = /usr/local/mysql/log/relay_log

      gtid_mode = on

      enforce-gtid-consistency = on

      log-slave-updates = on

      read_only = on [建议加上]

      master_info_repository = TABLE [建议加上]

      relay_log_info_repository = TABLE[建议加上]

    4. 初始化从服务器

      # 选择mysql自带的命令行工具（并发性能影响较大）
mysqldump --master-data=2 -single-transation

# 或者选择另外的工具（并发性能影响较小）
xtrabackup --slave-info


      • 1
      • 2
      • 3
      • 4
      • 5
      • 6

      另外要记录备份时最后的事务的GTID值；

    5. 启动基于GTID的复制

      # 在从服务器上执行
CHANGE MASTER TO MASTER_HOST='master_host_ip', 
				 MASTER_USER='repl', 
				 MASTER_PASSWORD='PassWord123',
                 MASTER_AUTO_POSITION=1;
                 
start slave;


      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8

    6. 基于GTID复制的优缺点

      优点：可以很方便地进行故障转移；从库不会丢失主库上的任何修改；

      缺点：故障处理比较复杂；对执行的SQL有一定的限制；(步骤2)

  6. 选择复制模式要考虑的问题

    1. 所使用的MySQL版本；
    2. 复制架构及主从切换的方式；
    3. 所使用的高可用管理组件；
    4. 对应用的支持程度；

  7. MySQL复制拓扑

    在不考虑其他影响的情况下，MySQL可以设置任意多个主/从，可以一主多从，可以多主多从；在MySQL5.7版本之前，一个从库只能有一个主库，但是在MySQL5.7之后，就支持一从多主架构；

    1. 一主多从的复制拓扑

      优点：配置简单；可以用多个从库分担读负载；

      缺点：由于MySQL的复制是异步的，可能会出现同一时刻，主从数据不一致的情况；

      应用：为不同业务使用不同的从库，比如前后台的数据分别放在不同的从库中；将一台从库放到远程IDC，用作容灾备份恢复；分担主库的读负载；

    2. 主–主复制拓扑

      1. 主备模式的主主复制：在一台出现问题时，才会启用另一台对外提供服务；
      2. 主主模式的主主复制：两个主同时对外提供服务，往某一个中写入，会同步到另一个主中，并不能分担写负载；

    3. 级联复制

      1. 一个主库、一个分发主库、多个从库；分发主库是用于从主库上读取二进制日志，然后分发给各个从服务器；分发主库一定要启用slave_log_updates这个参数；

  8. MySQL复制性能的优化

    由于MySQL是先执行事务之后，才会记录日志，所以当遇到大事务(耗时较长)的操作时，会导致从库数据更新较慢，导致主从数据不一致；另外，默认情况下，从服务器只有一个SQL线程，会导致主服务器上并发修改的操作变成了串行操作；

    优化的重点是分割大事务和缩短二进制日志传输的时间；

    如，使用MIXED日志格式或设置binlog_row_image=minimal；从服务器使用多线程复制(在MySQL5.7版本之后可以按照逻辑时钟的方式来分配SQL线程， 默认是数据库模式DATABASE)，具体实现步骤：1.停止复制链路stop slave; ，2.设置逻辑时钟方式：set global slave_parallel_type = 'logical_clock;'， 3.设置线程数量：set global slave_parallel_workers=4;， 4.启动复制链路：start slave;；

  9. MySQL复制常见的问题

    1. 主库或从库意外宕机引起的错误；二进制日志没有及时写入等情况、或master_info文件没有及时同步；
    2. 主库上二进制日志损坏；
    3. 备库上的中继日志损坏；
    4. 从库上进行数据修改造成主从复制错误；因此一定要设置read_only参数来避免这种问题；
    5. 不唯一的server_id或server_uuid；这种错误不容易被发现；
    6. max_allow_packet设置的不同引起主从复制失败；

  10. MySQL复制无法解决的问题

    1. 无法分担数据库的写负载，只能通过分库分表才能分担写负载；
    2. 无法自动进行故障转移及主从切换；
    3. 无法提供读写分离的功能；

高可用架构

  1. 高可用的定义

    高可用性(High Availability)指的是通过尽量缩短日常维护操作(计划)和突发的系统崩溃(非计划)所导致的停机时间，以提高系统和应用的可用性；简言之，就是通过系统的不可用时间的长短来衡量的；一般用系统的正常可用时间与全年时间的百分比作为高可用的程度；比如高可用性为5个9,即99.999%，则不可用时间为：(365*24*60)*(1-0.99999)=5.256分钟；也就是说，当高可用性为99.999%时，系统只允许全年有5分钟15秒左右的时间处于不可用状态，这个高可用程度已经属于很高了；

  2. 如何实现高可用

    1. 避免导致系统的不可用的因素，减少系统不可用的时间；

      1. 服务器磁盘空间耗尽
      2. 性能糟糕的SQL
      3. 表结构和索引没有优化
      4. 主从数据不一致
      5. 认为的操作失误
      6. 等等……

      针对上诉问题，解决办法有：

      1. 建立完善的监控及报警系统，避免错报和漏报；
      2. 对备份文件进行恢复测试；
      3. 正确配置数据库环境；从服务器一定要设置为只读read_only；
      4. 对不需要的数据进行归档和清理；比如日志文件等；

    2. 增加系统的冗余，保证发生系统不可用的时候可以尽快恢复；

      1. 避免存在单点故障；比如将所有的服务器都放在一个机房可能会出现这个机房故障；
      2. 主从切换及故障转移；

      针对上述问题，解决办法有：

      1. 利用SUN共享存储或者DRDB(磁盘镜像)磁盘复制来解决MySQL单点故障；

      2. 利用多写集群(如Percona公司提供的pxc集群)或者NDB集群来解决MySQL的单点故障；

      3. 利用MySQL的复制功能来减少MySQL的单点故障；

        但是，如何解决主服务器的单点故障问题？比如，主服务器切换后，如何通知应用新的主服务器的ＩＰ地址？如何检查ＭｙＳＱＬ主服务器是否可用？如何处理从服务和新主服务器之间的复制关系？详见下文；

      4. 3M架构，也就是ＭＭＭ(Multi-Master Replication Manager)MySQL的多主复制管理器的简称；使用Perl语言开发的主主同步的一种工具集；其作用是监控和管理MySQL的主主复制拓扑，并在当前的猪服务器失效时，进行主和主备服务器之间的主从切换和故障转移等工作；

      5. MHA架构(Master High Availability)，也是由Perl语言开发，用于管理MySQL主从复制，从而实现MySQL的高可用的一套相对比较成熟的工具套装；MHA关注更多的是主从复制架构中的主服务器，当主服务器发生故障，会自动地从从服务器中选取一台作为新的主服务器，一般能够在30秒之内完成主从切换，而且能最大程度上保证数据的一致性；MHA只能监控主数据库服务器是否可用；

读写分离与负载均衡

  1. 读写分离

    1. 把对数据库的读操作和写操作分开；

      进行数据库复制的目的一般是为了分担数据库的读负载，一般数据库的读操作会远远多于写操作；写操作只能在主库上完成，而读操作既可以在主库上完成，也可以在从库上完成，当然，为了让主库专注于写操作，会尽量将读操作放在从库上进行；

    2. 实现读写分离的方式

      1. 由程序实现读写分离

        适用于大多数自主开发的系统中，由开发人员控制什么样的查询在从库中执行，比较灵活；另外，由于程序直接连接数据库，所以性能损耗比较小；

      2. 由中间件来实现读写分离

        常用的中间件：mysql-proxy、maxScale(推荐)

        由中间件根据查询语法分析，自动完成读写分离；而且maxScale还支持负载均衡；缺陷在于增加了中间层之后，对查询效率有影响，尤其是在大事务高并发时候影响更大；

  2. 负载均衡

    1. 主要是解决具有相同角色的数据库如何共同分担相同的负载的问题；比如存在多个从服务器，负载均衡要做的就是将请求均衡地分配给每一个从服务器，从而降低某一从服务器的压力；

    2. 如何实现读的负载均衡

      1. 程序轮询方式
      2. 软件：LVS、Haproxy、maxScale；
      3. 硬件：F5

    3. maxScale实现负载均衡示例

      1. maxScale的插件：

        1. Authentication认证插件
        2. Protocal协议插件
        3. Monitor监控插件
        4. Routing路由插件
        5. Filter&Logging日志和过滤插件

      2. 安装

        下载maxScale的RPM包；（需要MariaDB账号）

        yum安装相关依赖；yum install libaio.x86_64 libaio-devel.x86_64 novacom-server.x86_64 -y

        rpm安装maxScale；rpm -ivh maxscale-1.3.0-1.rhel6.x86_64.rpm

      3. 推荐架构

        MySQL客户端连接到maxScale，maxScale连接主服务器和从服务器，MHA负责监控；

MySQL的索引

当数据库中数据量比较大的时候，建立正确的索引对数据库的性能提升是很大的；

  1. MySQL支持的索引类型（索引是在存储引擎层实现的）

    1. B-tree索引（最常见的索引类型）

      1. 使用B+树的结构来存储数据，每一个叶子节点都包含了指向下一个节点的指针，方便了叶子节点的遍历；

      2. 使用B-tree索引的情况：

        1. 全值匹配的查询，和索引中所有的列进行匹配，如：order_no = ‘469654318‘;
        2. 匹配最左前缀的查询，比如在order_no上没有建立索引，而是在order_no和order_date这两个列上建立了联合索引（注意左右顺序），对于上面的查询还是可以利用这个索引来提升查询效率的，也就是说联合索引的第一个列符合查询条件，这个联合索引就会被利用到，但是如果联合索引的第二个列符合查询条件，这个联合索引就不会被用到，比如使用order_date = ‘2000-01-01’，这就无法使用到联合索引；
        3. 匹配列前缀查询，匹配某一列的开头部分，比如order_no like ‘4696%’
        4. 匹配范围值的查询，比如 order_no > ‘469654310‘ and order_no < ‘469654319’；
        5. 精确匹配左前列并范围匹配另外一列；
        6. 只访问索引的查询；

      3. 使用B-tree索引的限制

        1. 如果不是按照索引最左列开始查找，则无法使用索引；
        2. 使用索引时不能跳过索引中的列；
        3. not in 和 不等于< >操作无法使用索引；
        4. 如果查询中有某个列的范围查询，则其右边所有的列都无法使用索引；

    2. Hash索引

      1. Hash索引是基于Hash表实现的，只有查询条件精确匹配Hash索引中的所有列时，才能够使用到Hash索引；也就是说Hash索引适用于等值查询，而不适合范围查询或者模糊查询；
      2. 对于Hash索引中的所有列，存储引擎都会为每一行计算一个Hash码，Hash索引中存储的就是Hash码；
      3. 使用Hash索引查询需要进行两次读取，Hash索引中只包含键值、Hash码以及对应行的指针索引中并没有保存字段的值，使用Hash索引时，必须先通过Hash索引找到对应的行，然后对行的记录进行读取；
      4. Hash索引的限制
        1. Hash索引必须进行二次查找；
        2. Hash索引无法用于排序；
        3. Hash索引不支持范围查找也不支持部分索引查找；
        4. Hash索引中Hash码的计算可能存在Hash冲突，比如在性别这种重复率很高的列就不适合建立Hash索引，对于身份证这种数据唯一性较高的列，适合用Hash索引；

    3. 为什么使用索引？

      1. 索引大大减少了存储引擎需要扫描的数据量；InnoDB一次I/O，以页为最小单位，每页默认为16k，一页能存储的信息越多，读取的也就越快，减少了存储引擎需要扫描的数量，加快了查询速度；
      2. 利用B-tree索引可以进行排序以避免使用临时表，有效降低磁盘I/O消耗；
      3. 索引可以将随机I/O转换为顺序I/O；

    4. 索引对数据库性能的损耗

      1. 索引会增加写操作的成本，插入数据时需要添加索引；
      2. 太多的索引会增加查询优化器的选择时间；

    5. 安装演示数据库来练习索引优化策略

      1. 下载演示数据库压缩包，链接：http://downloads.mysql.com/docs/sakila-db.tar.gz；
      2. 解压缩：tar -zxvf sakila-db.tar.gz；
      3. 导入数据库表结构和数据库数据：mysql -uroot -p < sakila-schema.sql、mysql -uroot -p < sakila-data.sql；

    6. 索引优化策略

      1. 索引上不能使用表达式或函数，比如

        select * from product where to_days(out_date)-to_days(current)date)<=30;


        • 1
        • 2

        其中to_days()是函数，out_date是索引列；这样的SQL语句无法使用到索引，可以改为：

        select * from product where out_date<=date_add(current_date, interval 30 day);


        • 1
        • 2

      2. 对text类型使用前缀索引的长度尽量短

        create index 索引名 on 表名(列名(n));


        • 1
        • 2

      3. 索引的选择性尽量高

        索引的选择性是不重复的索引值和表的记录数的比值，联合索引的顺序；

        在选择前缀索引的长度时候，尽可能小，但是不能让索引的选择性太差，比如有’abcd’、 ‘abde‘、’bcdef’、’bcaef’这四个字符串，如果选择前2个字母建立索引时，会有两个不同值，当选择前3个字母建立索引时，不同值会有4个，查询时候效率更高；

      4. 联合索引的顺序

        1. 经常会被使用到的列优先；
        2. 选择性高的列优先；
        3. 宽度小的列优先；

      5. 覆盖索引

        1. 包含了查询的所有值的索引称为覆盖索引，如B-tree的叶子节点包含了对应的值；
        2. 优点：可以优化缓存，减少磁盘I/O操作；可以减少随机I/O，变随机I/O为顺序I/O操作；可以避免对InnoDB主键索引的二次查询；可以避免MyISAM表对系统调用；
        3. 局限性：存储引擎不支持覆盖索引的无法使用；查询中使用了太多的列无法使用；使用了两个%的模糊查询；

    7. 使用索引来优化查询

      1. 利用B-tree索引扫描来优化排序，两种方式：1.通过排序操作；2.按照索引顺序扫描数据；要求：
        1. 索引的列顺序和order_by子句的顺序完全一致；
        2. 索引中所有列的方向(升序/降序)和order_by子句完全一致；
        3. order_by中的字段全部在关联表中的第一张表中；
      2. Hash索引优化查询
        1. 只能处理键值的全值匹配查找；
        2. 所使用的Hash函数决定索引键的大小；
      3. 利用索引来优化锁
        1. InnoDB支持行级锁，使用索引可以减少锁定的行数；
        2. 索引可以加快处理速度，同时也加快了锁的释放速度；

    8. 索引的维护和优化

      1. 删除重复和冗余的索引

        1. primary key(id)、unique key(id)、index(id)，这种属于重复索引；
        2. index(a)和index(a,b)，或者 primary key(id)和index(c, id)，这种都属于冗余索引；
        3. 如何知道索引是重复或冗余？可以使用这个工具来检查：pt-duplicate-key-checker h=127.0.0.1，这个工具需要下载并安装才能使用；

      2. 查找未被使用过的索引

        SELECT
OBJECT_SCHEMA,
OBJECT_NAME,
INDEX_NAME
FROM table_io_waits_summary_by_index_usage
WHERE INDEX_NAME IS NOT NULL
AND COUNT_STAR = 0
AND OBJECT_SCHEMA <> 'mysql'
ORDER BY OBJECT_SCHEMA,OBJECT_NAME;

# 输出如下：
# *************************** 1. row ***************************
# OBJECT_SCHEMA: chuck
# OBJECT_NAME: test_icp
# INDEX_NAME: idx_y_z


        • 1
        • 2
        • 3
        • 4
        • 5
        • 6
        • 7
        • 8
        • 9
        • 10
        • 11
        • 12
        • 13
        • 14
        • 15
        • 16

      3. 更新索引统计信息及减少索引碎片

        analyze table table_name;

optimize table table_name;  # 该语句慎用，使用不当会导致锁表；


        • 1
        • 2
        • 3
        • 4

MySQL的SQL性能优化

  1. 如何获取有性能问题的SQL？

    1. 通过用户反馈，或者测试人员测试结果，找到对应的存在性能问题的SQL；
    2. 通过慢查询日志获取存在性能问题的SQL；
    3. 实时获取存在性能问题的SQL；

  2. 使用慢查询日志获取有性能问题的SQL（最常用）；

    1. 启用慢查询日志，默认情况下不会启用慢查询日志；

      开启：配置文件中将slow_query_log参数的值设置为on；

      或者在已经运行的MySQL服务中启用慢查日志，可使用set global slow_query_log='on';；

    2. 指定慢查询日志的存储路径及文件

      slow_query_log_file，如果没有指定，默认保存在MySQL的数据目录中；

    3. 指定记录慢查日志SQL执行时间的阈值，即超过多少毫秒的SQL记录到慢查日志中；

      long_query_time，单位是秒，默认是10秒，但是设置的值可以精确到微秒，比如设置值为0.000001；推荐值0.001秒，即1毫秒；

      与二进制日志不同，慢查日志会记录所有符合条件的SQL，包括查询语句、数据修改语句、已经回滚的SQL；

    4. log_queries_not_using_indexes，是否记录未使用索引的SQL；

    5. 常用的慢查询日志分析工具：mysqldumpslow、pt-query-digest

      1. 自带的mysqldumpslow可以汇总除查询条件外其他完全相同的SQL，并将分析结果按照参数中所制定的顺序输出；

        mysqldumpslow -s r -t 10 slow-mysql.log

# 其中-s 表示指定按照那种排序方式输出结果，可选参数有：
# c: 总次数
# t: 总时间
# l: 锁的时间
# r: 总数据行数
# at,al,ar: t,l,r的平均值，如at即:总时间/总次数

# -t top
# 指定取前几条作为结果输出


        • 1
        • 2
        • 3
        • 4
        • 5
        • 6
        • 7
        • 8
        • 9
        • 10
        • 11
        • 12

      2. pt-query-digest的使用

        pt-query-digest --explain h=127.0.0.1, u=root, p=PassWord123 slow-mysql.log > slow.rep

# 将分析结果保存在slow.rep文件中；
# --explain 是否在分析结果中包括SQL的执行计划；


        • 1
        • 2
        • 3
        • 4
        • 5

  3. 如何实时获取有性能问题的SQL

    通过information_schema数据库下的PROCESSLIST表来获取：

    SELECT id, user, host, DB, command, time, state, info FROM information_schema.PROCESSLIST WHERE TIME>=0.001;


    • 1
    • 2

  4. 查询速度为什么会慢?

    1. MySQL服务器处理查询请求的整个过程

      1. 客户端发送SQL请求给服务器；
      2. 服务器检查是否可以在查询缓存中命中该SQL，如果能命中，校验用户权限后直接返回结果；
      3. 如果未能命中该SQL，则服务器端进行SQL解析，预处理，再由优化器生成对应的执行计划；
      4. 根据执行计划，调用存储引擎的API来查询数据，然后存储引擎层将结果返回给MySQL服务器；
      5. MySQL服务器将结果返回给客户端；

    2. 查询缓存

      1. 在解析一个查询语句之前，如果查询缓存功能是开启的，MySQL服务器首先会检查这个查询是否命中查询缓存中的数据，这个缓存是通过一个对大小写敏感的哈希查找实现的(哈希查找只能进行全值匹配，即使只有一个字节不同，也不会命中)，如果能命中，MySQL服务器会校验用户权限，这时候依然没有进行SQL语句解析，通过校验之后，就会直接将数据返回给客户端，这个过程中，是没有进行SQL解析的；
      2. 设置查询缓存是否可用：query_cache_type，可选值有：ON,OFF,DEMAND；如果设置为DEMAND，则表示只有在查询语句中使用SQL_CACHE和SQL_NO_CACHE来控制是否需要缓存；
      3. 设置查询缓存的内存大小：query_cache_size；
      4. 设置查询缓存可用存储的最大值：query_cache_limit；
      5. 设置数据表被锁后是否返回缓存中的数据：query_cache_wlock_invalidate；
      6. 设置查询缓存分配的内存块最小单位：query_cache_min_res_unit；

    3. 执行计划

      1. MySQL依照这个执行计划和存储引擎进行交互，这个过程包括了：

        1. 解析SQL

          MySQ解析器将使用MySQL语法规则验证和解析查询，包括检查语法是否使用了正确的关键字，关键字顺序是否正确等；通过关键字对MySQL语句进行解析，并生成一棵对应的解析树；

        2. 预处理

          根据MySQL规则进一步检查解析树是否合法；如检查查询中所涉及的表和数据列是否存在名字或别名，是否存在歧义等等；语法全部通过后，查询优化器就可以生成查询计划了；

        3. 优化SQL执行计划

          1. 会造成MySQL生成错误的执行计划的原因：
            1. 统计信息不准确；
            2. 执行计划中的成本估算并不等同于实际执行计划的成本；比如MySQL服务器层并不知道哪些页面在内存中，哪些页面在磁盘上，哪些需要顺序读取，哪些需要随机读取；MySQL服务器层只考虑理论上的最优；
            3. MySQL查询优化器基于其成本模型选择最优执行计划，可能与实际执行计划不同；
            4. MySQL从不考虑其他并发的查询，这个能会导致因锁的存在降低查询效率；
            5. MySQL不会考虑不受其控制的成本，比如存储过程，用户自定义的函数等；

    4. MySQL优化器可优化的SQL类型

      1. 重新定义表的关联顺序；优化器会根据统计信息来决定表的关联顺序；
      2. 一定条件下会将查询中的外连接转化为内连接，比如where条件和库表结构都可能会使外连接等价于内连接，比如前面将a,b两张表外连接，后面的where子句又将其中一张表进行了过滤，查询优化器能够自动识别并重新关联这两张表；
      3. 使用等价变换规则；比如会将a>5 and a>8这样的条件改写为a>8；
      4. 优化count()、min()、max()；
      5. 将一个表达式转化为场数表达式；
      6. 将子查询转化为关联查询；
      7. 提前终止查询；比如在使用了LIMIT，MySQL发现已经满足了查询之后，会立即终止，不再继续查询；再如发现了一个不成立的条件，也会提前终止；
      8. 对in()条件进行优化，MySQL会对in列表中的数据先进行排序，再使用二分查找的方式来确定列表中的值是否满足条件；

  5. 如何确定查询处理各个阶段所消耗的时间

    1. 使用profile
      1. set profiling=1;，来启动profile，这是一个session级别的配置；
      2. 执行查询：show profiles;;
      3. 查询每个阶段所消耗的时间：show profile for query N;，其中N表示上一个命令中的query_id的值；
      4. 使用show profile;会警告：后续版本profile可能不支持，需要使用performance_schema来代替；启用performance_schema需要启用相关的监控和历史记录表；

特定SQL的查询优化

  1. 大表的数据修改最好要分批处理

    1. 比如1000万行记录的表中删除/更新100万行记录，一次只删除/更新5000行记录；

      DELIMITER $$
USE `test_db`$$
DROP PROCEDURE IF EXISTS `p_delete_rows`$$
CREATE DEFINER=`root`@`127.0.0.1` PROCEDURE `p_delete_rows`()
BEGIN
	DECLARE v_rows INT;
	SET v_rows = 1;
	WHILE v_rows > 0
	DO
		DELETE FROM tb_test WHERE id >= 90000 AND id <= 190000 LIMIT 5000;
		SELECT ROW_COUNT() INTO v_rows;
		SELECT SLEEP(5);
	END WHILE;
END$$
DELIMITER ;


      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8
      • 9
      • 10
      • 11
      • 12
      • 13
      • 14
      • 15
      • 16

  2. 将not in 或 不等于<>改写为关联查询

    # 改写前：
SELECT customer_id,first_name,last_name,email 
FROM customer 
WHERE customer_id 
NOT IN (SELECT customer_id FROM payment);

# 改写后：
SELECT a.customer_id,a.first_name,a.last_name,a.email 
FROM customer as a
LEFT JOIN payment as p 
ON a.customer_id=p.customer_id 
WHERE p.customer_id IS NULL;


    • 1
    • 2
    • 3
    • 4
    • 5
    • 6
    • 7
    • 8
    • 9
    • 10
    • 11
    • 12
    • 13

  3. 使用汇总表优化查询

    汇总表就是提前将要统计的数据进行汇总并记录到表中，以备后续的查询使用；

    SELECT COUNT(*) FROM product_comment WHERE product_id=999;


    • 1
    • 2

    建立汇总表：

    CREATE TABLE product_comment_count(product_id INT, cnt INT);
# 可以设计定时任务，每天或每几天汇总一次；


    • 1
    • 2
    • 3

MySQL数据库的分库分表

  1. 分库分表分方式：

    1. 把一个实例中的多个数据库拆分到不同的实例

      比如某个数据库节点/集群中，包含了【订单】、【用户】、【促销】三个数据库，将其分别拆分到3个节点中，第一个节点只存放【订单】，第二个节点只存放【用户】、第三个节点只存放【促销】

    2. 把一个库中的表分离到不同的数据库中

      比如一个数据库中有[订单表]、[商品表]、 [购物车]这三张表，将其分别拆分到三个数据库中；

    3. 对一个数据库的相关表进行水平拆分到不同实例的数据库中（常说的分库分表）

      比如一个数据库中有一个[订单表]，将其水平拆分到3个数据库中分别存放

  2. 数据库分片前的准备

    1. 如何选择分区键
      1. 分区键要能尽量避免跨分片查询的发生；完全避免也不可能；
      2. 分区键要能尽量是各个分片中的数据平均；
    2. 如何存储无需分片的表
      1. 每个分片中存储一份相同的冗余数据；
      2. 使用额外的节点同一存储；
    3. 如何在节点上部署分片；
      1. 每个分片使用单一数据库，并且数据库名也不相同；
      2. 将多个分片表部署存储在同一个数据库中，并在表名上加入分片号后缀；
      3. 在一个节点中部署多个数据库，每个数据库包含一个分片；
    4. 如何分配分片中的数据
      1. 按分区键的Hash值取模来分配分片数据；
      2. 按分区键的范围来分配分片数据；（日期类型，数值类型）
      3. 利用分区键和分片的映射表来分配分片数据；
    5. 如何生成全局唯一ID
      1. 使用auto_increment_increment和auto_increment_offset参数；（有局限性）
      2. 使用全局节点来生成ID；
      3. 在Redis等缓存服务器中创建全局ID；

数据库分片示例

  1. 场景：

    一个数据库包含了3张表，分别是[订单表]、[订单商品表]、[商品分类表]；由于订单表和订单商品表数据量较大，要将这两张表分片，而商品分类表经常要与这两张表关联查询，所以分片中都要包含商品分类表；分片后的结果是：【db_1】:[订单表1]、[订单商品表1]、[商品分类表]；【db_2】：[订单表2]、[订单商品表2]、[商品分类表]；

  2. 所用的数据库分片工具oneProxy

    1. 下载并解压

      # 下载
wget http://www.onexsoft.cn/software/oneproxy-rhel6-linux64-v5.8.1-ga.tar.gz

# 解压缩
tar -zxvf oneproxy-rhel6-linux64-v5.8.1-ga.tar.gz

# 修改proxy.cnf配置文件
vim proxy.cnf

# 配置文件中添加
# mysql-version = 5.7.9-log
# proxy-address = :3306
# proxy-master-addresses.1 = 192.168.0.101:3306@tb_oder1
# proxy-master-addresses.2 = 192.168.0.102:3306@tb_oder2
# ......

# 运行解压后oneproxy目录下的demo.sh脚本即可启动oneproxy
cd /oneproxy  && ./demo.sh

# 默认用户 admin  密码 OneProxy
mysql -P4041 -uadmin -pOneProxy -h127.0.0.1

# 具体使用省略


      • 1
      • 2
      • 3
      • 4
      • 5
      • 6
      • 7
      • 8
      • 9
      • 10
      • 11
      • 12
      • 13
      • 14
      • 15
      • 16
      • 17
      • 18
      • 19
      • 20
      • 21
      • 22
      • 23
      • 24

数据库监控

  1. 数据库监控

    1. 对数据库服务可用性进行监控；

      只监控数据库进程或端口是否存在，并不意味着数据库就是可用的；需要通过网络连接到数据库并且确定数据库是可以对外提供服务的，比如发送请求、写SQL、脚本等；

      如何确认数据库是否可以通过网络连接？

      MySQL本地的SQL文件能连接上数据库服务器，并不意味着就能通过网络TCP/IP协议连接到MySQL服务器，这是因为还可能存在防火墙或者iptable一类的工具，对服务器有些端口做了一定的限制，另外，还有可能会出现TCP/IP连接被占满，无法建立新的连接的情况；可以使用mysqladmin -umonitor_user -p -h ping在远程上来测试是否可以连接，需建立监控账号monitor_user；或者使用telnet ip db_port；

      判断数据库是否可读，使用select @@version;，查看版本号，通用语法；

    2. 对数据库的性能进行监控；

      最常见的就是监控数据库的QPS、TPS、并发线程数量等；对InnoDB存储引擎阻塞和死锁进行监控；

      如何计算QPS和TPS？

      QPS = (Queries2 - Queries1) / (Uptime_since_flush_status2 - Uptime_since_flush_status1)，两次采样值的差值与时间差的比值；

      TPS = ((Com_insert2 + Com_update2 + Com_delete_2) - (Come_insert1 + Com_update1 + Com_delete1)) / (Uptime_since_flush_status2 - Uptime_since_flush_status1)

    3. 对主从复制进行监控

      1. 主从复制链路状态的监控
      2. 主从复制延迟的监控
      3. 定期地确认主从复制的数据是否一致

    4. 对服务器资源的监控

      1. CPU使用情况
      2. 带宽使用情况
      3. 内存使用情况等

  • 扩展：什么是锁？

    • 锁主要作用是管理共享资源的并发访问；锁用于实现事务的隔离性；

    • 锁的分类

      • 共享锁（也称读锁）

      • 独占锁/排他锁（也称写锁 ）

        同一个数据，可以多个同时读，但是若有其中一个在写入数据，其他都不可读、不可写；简而言之：读读可以，读写不行，写写不行；

        举例：两个客户端同时对某一张表的某一条记录进行操作，如果其中一个客户端对该数据进行事务操作，比如原来的数据为1，要修改为2，事务未提交，这时候另一个客户端读取的数据还是1，这个1是从在Undo Log中读取到的；

    • 锁的粒度（被加锁的资源的最小单位，比如行级锁、页级锁、表级锁；粒度越小，并发越高）

      • 表级锁（MySQL服务器层实现）
        • 为某张表(如：tb_stu)增加表级锁（InnoDB引擎也行）: lock table tb.stu write;，这时候其他的客户端去查询的时候就会被阻塞，直到锁被释放unolock tables;；
        • 系统资源开销小，并发性能弱；
      • 行级锁（存储引擎层所实现）
        • 系统资源开销大，并发性能强；

    • 阻塞和死锁

      • 阻塞
        • 阻塞是因为不同锁之间的兼容关系导致，在有些时刻，一个事务中的锁需要等待另一个事务中的锁释放，这就形成了阻塞；
        • 阻塞是为了事务的并发且正常运行，大量的阻塞表明系统出现了问题，可能是在一个频繁更新的表出现了慢查询；也可能是其他的一些操作，如表备份等，在一些频繁被访问的表上加了排它锁；
      • 死锁
        • 相互占用了对方阻塞的资源可能出现死锁的情况；
        • 系统可以自动识别少量死锁的出现，并回滚占用资源最少的事务，来解决死锁的情况；
声明：本文内容由网友自发贡献，不代表【wpsshop博客】立场，版权归原作者所有，本站不承担相应法律责任。如您发现有侵权的内容，请联系我们。转载请注明出处：https://www.wpsshop.cn/article/detail/49354?site
推荐阅读
相关标签

Copyright © 2003-2013 www.wpsshop.cn 版权所有，并保留所有权利。

  

闽ICP备14008679号