# 性能优化

当数据库受到高并发访问时,可以使用以下方案缓解数据库的访问压力:

  • 使用消息队列缓存写入的数据,将并发写入变成异步写入。
  • 按主从集群部署,实现数据备份、读写分离。
  • 按集群架构部署,实现高可用。

可采用以下方法提高数据库的查询效率:

  • 将数据库尽量分表、分库(比如不同业务的分开),减少每张表的大小。
  • 正确地设置索引。
  • 用 Redis 缓存用户经常查询的数据,既能提高用户的查询速度,又能降低数据库的访问压力。

TODO:待补充

# 内存开销

  • MySQL 服务器占用的总内存主要包括:

    • 全局共享的内存:
      innodb_buffer_pool
      innodb_log_buffer
      temptable_max_ram
      key_buffer_size       # 用于缓冲索引,默认为 16M
      query_cache
      
    • 每个客户端线程独享一份的内存:
      join_buffer_size      # 默认为 256K
      read_buffer_size      # 默认为 128K
      sort_buffer_size      # 默认为 512K
      read_rnd_buffer_size  # 默认为 256K
      
  • performance_schema 数据库的以下数据表,记录了各种事件占用的内存:

    memory_summary_by_account_by_event_name   # 各个 user@host 占用的内存
    memory_summary_by_thread_by_event_name    # 各个客户端线程占用的内存
    memory_summary_global_by_event_name       # 各个 event 占用的内存
    

    其中的主要字段:

    COUNT_ALLOC                   # 累计分配内存的次数
    COUNT_FREE                    # 累计释放内存的次数
    CURRENT_COUNT_USED            # 当前分配但未释放的内存次数,等于 COUNT_ALLOC - COUNT_FREE
    HIGH_COUNT_USED               # CURRENT_COUNT_USED 的历史最大值
    
    SUM_NUMBER_OF_BYTES_ALLOC     # 累计分配的内存量(包括已释放的)
    CURRENT_NUMBER_OF_BYTES_USED  # 当前分配但未释放的内存量,等于 SUM_NUMBER_OF_BYTES_ALLOC - SUM_NUMBER_OF_BYTES_FREE
    HIGH_NUMBER_OF_BYTES_USED     # CURRENT_NUMBER_OF_BYTES_USED 的历史最大值
    
  • 相关命令:

    SHOW ENGINE INNODB STATUS     -- 查看 Innodb 引擎的状态,包括事务、内存等
    select * from sys.memory_global_total;    -- 查看 MySQL 占用的内存总量
    select event_name,current_alloc from sys.memory_global_by_current_bytes limit 10; -- 查看各种事件占用的内存
    select thread_id,user,current_allocated from sys.memory_by_thread_by_current_bytes limit 10;  -- 查看各个客户端占用的内存
    
    • 刚启动且 buf_buf_pool 为空时, MySQL 大概占用 300MB 内存。
    • sys.memory_global_total 是 MySQL 自我观测的内存占用,可能比 MySQL 进程的 RSS 内存偏小,主要原因:
      • MySQL 未统计启动时加载的 C++ 动态链接库。
      • MySQL 默认采用 ptmalloc 内存分配器,调用 free() 可能不会立即释放内存。

# mysqlslap

:MySQL 官方提供的一个命令行工具,用于进行性能测试。

  • 工作原理:

    1. 建立单个连接到 MySQL 服务器,创建用于测试的数据库、数据表,插入随机数据。
    2. 建立多个连接到 MySQL 服务器,模拟多个客户端并发。执行自动生成的 SQL 语句,进行测试。
    3. 建立单个连接到 MySQL 服务器,删除用于测试的数据库。
  • 命令:

    mysqlslap
            -a                # --auto-generate-sql ,自动生成 SQL 语句进行测试
            -x 1              # --number-char-cols  ,在测试表中定义多少个 int(32) 类型的字段
            -y 1              # --number-int-cols   ,在测试表中定义多少个 varchar(128) 类型的字段
            --auto-generate-sql-add-autoincrement         # 在测试表中增加一个 autoincrement 的主键字段,默认不启用
            --engine=innodb                               # 用于创建测试表的引擎
    
            --auto-generate-sql-write-number 100          # 在测试表中,插入 n-1 行测试数据
            --auto-generate-sql-unique-write-number 10    # 测试数据中,有多少行是不重复的
    
            --auto-generate-sql-execute-number 10         # 插入测试数据之后,执行多少个 query 操作
            --auto-generate-sql-unique-query-number 10    # query 操作中,有多少个是不重复的
            --auto-generate-sql-load-type mixed           # query 操作的类型,可以是 read(即 SELECT 语句)、write(即 INSERT 语句)、mixed(read、write 各占一半)、key(读取主键)、update(修改主键),默认为 mixed
    
            -c 1              # --concurrency ,模拟多少个客户端并发访问,每个客户端都会将上述测试执行一次
            -i 1              # --iterations ,整个测试过程重复执行多少次
    
            --only-print      # 不实际执行测试,而是打印用于测试的所有 SQL 语句
            --no-drop         # 测试之后不要删除测试库。不过重复次测试时,会因为测试库已存在而报错
    
    • 例:打印测试过程
      [root@CentOS ~]# mysqlslap -u root -p   -a --auto-generate-sql-write-number 3  --auto-generate-sql-execute-number 2 --only-print
      DROP SCHEMA IF EXISTS `mysqlslap`;
      CREATE SCHEMA `mysqlslap`;                                      # 创建测试数据库
      use mysqlslap;
      CREATE TABLE `t1` (intcol1 INT(32) ,charcol1 VARCHAR(128));     # 创建测试数据表
      INSERT INTO t1 VALUES (1804289383,'mxvtvmC9127qJNm06sGB8R92q2j7vTiiITRDGXM9ZLzkdekbWtmXKwZ2qG1llkRw5m9DHOFilEREk3q7oce8O3BEJC0woJsm6uzFAEynLH2xCsw1KQ1lT4zg9rdxBL');  # 插入测试数据
      INSERT INTO t1 VALUES (822890675,'97RGHZ65mNzkSrYT3zWoSbg9cNePQr1bzSk81qDgE4Oanw3rnPfGsBHSbnu1evTdFDe83ro9w4jjteQg4yoo9xHck3WNqzs54W5zEm92ikdRF48B2oz3m8gMBAl11W');   # 插入测试数据
      INSERT INTO t1 VALUES (100669,'qnMdipW5KkXdTjGCh2PNzLoeR0527frpQDQ8uw67Ydk1K06uuNHtkxYBxT5w8plb2BbpzhwYBgPNYX9RmICWGkZD6fAESvhMzH3yqzMtXoH4BQNylbK1CmEIPGYlC6');      # 执行 query
      SELECT intcol1,charcol1 FROM t1;                                                                                                                                      # 执行 query
      DROP SCHEMA IF EXISTS `mysqlslap`;
      
    • 例:执行并发测试
      [root@CentOS ~]# mysqlslap -u root -p   -a --auto-generate-sql-add-autoincrement  --auto-generate-sql-execute-number 40 -c 100 -i 10
      Benchmark
              Average number of seconds to run all queries: 0.160 seconds
              Minimum number of seconds to run all queries: 0.124 seconds
              Maximum number of seconds to run all queries: 0.202 seconds
              Number of clients running queries: 100
              Average number of queries per client: 40