MySQL 优化

索引

分库

分表

一、索引

1. 普通索引 NORMAL
2. 唯一索引 UNIQUE
3. 主键索引 PRIMARY
4. 全文索引 FULLTEXT
5. 空间索引 SPATIAL
6. 组合索引/复合索引

1. 普通索引、主键索引、唯一索引（PRIMARY、INDEX、UNIQUE）分为一类

   索引	特点
   普通索引 NORMAL	索引列没有任何限制
   主键索引PRIMARY	索引列唯一且不能为空；一张表只能有一个主键索引（主键索引通常在建表的时候就指定）
   唯一索引UNIQUE	索引列值必须是唯一的，但允许为空
   全文索引FULLTEXT	全文搜索的索引。FULLTEXT 用于搜索很长一篇文章的时候，效果最好。
   空间索引SPATIAL	5.7以后支持空间索引，支持二进制大对象的索引（一般用于几何数据）

   组合索引就是如上将索引添加到多列之上，其限制类比到单列

2. 索引方法的选择

   • BTREE : 用于范围搜索
   • HASH : 用于精确搜索 （不能加速ORDER BY）

3. 注意

   • 查询条件使用函数时，应创建基于函数的索引
   • 索引会加速查询，在insert，update 会锁表，有可能会重建索引，会减慢增删改，同时会增加内存消耗。

   4. 语句优化

      where 子句中的“=”左边进行函数、算术运算或其他表达式运算,否则系统将可能无法正确使用索引。查询条件使用函数时，应创建基于函数的索引

      1. 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。

         索引失效的情况

         1. where 子句中对字段进行 null 值判断
         2. where 子句中使用!=或<>操作符
         3. where 子句中使用 or 来连接条件
         4. in 和 not in 也要慎用，对于连续的数值使用between
         5. like %aa%
         6. where 子句中的“=”左边进行函数、算术运算或其他表达式运算,例如：select id from t where num/2=100 可改为 select id from t where num=100*2

         优化

         1. exists 代替in
         2. 不要写select *
         3. 尽量使用数字型字段；
         4. 尽可能的使用 varchar 代替 char
         5. 避免频繁创建和删除临时表，以减少系统表资源的消耗。

二、分表

1. 水平分表
2. 垂直分表

表结构是一个二维表格

1. 水平分表

   目的：为了我解决单表数据量大的问题。

   水平分表是在同一个数据库内，把同一个表的数据按一定规则拆到多个表中。

   水平分表是分割行，将一个表的记录分配到两个表，不会更改表结构。

   提升：

   • 优化单一表数据量过大而产生的性能问题

   • 避免IO争抢并减少锁表的几率

     库内的水平分表，解决了单一表数据量过大的问题，分出来的小表中只包含一部分数据，从而使得单个表的数据量变小，提高检索性能。

2. 垂直分表

   目的：将冷数据（访问频次较低的数据）提取出来，提高热数据（访问频次较高的数据）的操作效率。

   将一个表按照字段分成多表，每个表存储其中一部分字段。

   垂直分表分割列数据，生成新的表，会改变表结构

   提升：

   1.为了避免IO争抢并减少锁表的几率，查看详情的用户与商品信息浏览互不影响

   2.充分发挥热门数据的操作效率，商品信息的操作的高效率不会被商品描述的低效率所拖累。

三、分库

索引和分表基本上都是在单机上的操作，当达到服务器的性能瓶颈时，索引和分表的意义就不大了，这时候就需要分库

1. 水平分库
2. 垂直分库

1. 水平分库

   水平分库是把同一个表的数据按一定规则拆到不同的数据库中，每个库可以放在不同的服务器上。

   水平分库是把不同表拆到不同数据库中，它是对数据行的拆分，不影响表结构

   提升：

   • 解决了单库大数据，高并发的性能瓶颈。
   • 提高了系统的稳定性及可用性。

2. 垂直分库

   垂直分库是指按照业务将表进行分类，分布到不同的数据库上面，每个库可以放在不同的服务器上，它的核心理念是专库专用。

   提升：

   • 解决业务层面的耦合，业务清晰
   • 能对不同业务的数据进行分级管理、维护、监控、扩展等
   • 高并发场景下，垂直分库一定程度的提升IO、数据库连接数、降低单机硬件资源的瓶颈

读写分离，主从复制

随着业务量的扩展、如果是单机部署的MySQL，会导致I/O频率过高。采用

主从复制、读写分离可以提高数据库的可用性。

主从复制、读写分离就是为了数据库能支持更大的并发。

主从复制的原理

①当Master节点进行insert、update、delete操作时，会按顺序写入到binlog中。

②salve从库连接master主库，Master有多少个slave就会创建多少个binlog dump线程。

③当Master节点的binlog发生变化时，binlog dump 线程会通知所有的salve节点，并将相应的binlog内容推送给slave节点。

④I/O线程接收到 binlog 内容后，将内容写入到本地的 relay-log。

⑤SQL线程读取I/O线程写入的relay-log，并且根据 relay-log 的内容对从数据库做对应的操作。

Master配置

1.模拟环境

docker-compose 安装mysql：5.7 ，部分版本可能没有my.cnf文件

version: '3.1'
services:
  mysql1:
    image: mysql:5.7
    container_name: mysql-master
    ports: 
     - "3306:3306"
    environment:
      MYSQL_ROOT_PASSWORD: "123456"
      TZ: Asia/Shanghai
    volumes: 
     - ./mysql1/data:/var/lib/mysql
    #  - ./mysql1/conf:/etc/mysql
    restart: always
    network_mode: "bridge"
  mysql2:
    image: mysql:5.7
    container_name: mysql-slave1
    ports: 
     - "3307:3306"
    environment:
      MYSQL_ROOT_PASSWORD: "123456"
      TZ: Asia/Shanghai
    volumes: 
     - ./mysql2/data:/var/lib/mysql
    # - ./mysql2/conf/mysql/my.cnf:/etc/my.cnf
    restart: always
    network_mode: "bridge"
  mysql3:
    image: mysql:5.7
    container_name: mysql-slave2
    ports: 
     - "3308:3306"
    environment:
      MYSQL_ROOT_PASSWORD: "123456"
      TZ: Asia/Shanghai
    volumes: 
     - ./mysql3/data:/var/lib/mysql
     #- ./mysql3/conf/mysql/my.cnf:/etc/my.cnf
    restart: always
    network_mode: "bridge"
    
 。。。。。。

2. mysql 配置

1 . master 配置

使用命令行进入mysql：

mysql -u root -p

接着输入root用户的密码(密码忘记的话就网上查一下重置密码吧~)，然后创建用户：

//192.168.0.106是slave从机的IP 添加一个可以slave 权限账号 也可以直接root账号
GRANT REPLICATION SLAVE ON *.* to 'root'@'192.168.0.106' identified by 'Java@1234';
//192.168.0.107是slave从机的IP
GRANT REPLICATION SLAVE ON *.* to 'root'@'192.168.0.107' identified by 'Java@1234';
//刷新系统权限表的配置
FLUSH PRIVILEGES;

创建的这两个用户在配置slave从机时要用到。

接下来在找到mysql的配置文件/etc/my.cnf，增加以下配置：

[mysqld] #组
# 开启binlog
log-bin=mysql-bin
server-id=104
# 需要同步的数据库，如果不配置则同步全部数据库
binlog-do-db=test_db
# binlog日志保留的天数，清除超过10天的日志
# 防止日志文件过大，导致磁盘空间不足
expire-logs-days=10 

配置完成后，重启mysql：

service mysql restart

查看当前binlog日志的信息(后面有用)：

mysql> show master status;
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |     6274 | test_db      |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)


#注意 Position 参数下文要用到


-- 或
mysql> show master status\G;
*************************** 1. row ***************************
             File: mysql-bin.000002
         Position: 7076
     Binlog_Do_DB: test_db
 Binlog_Ignore_DB: 
Executed_Gtid_Set: 
1 row in set (0.00 sec)

ERROR: 
No query specified

2 . 配置slave

Slave配置相对简单一点。从机肯定也是一台MySQL服务器，所以和Master一样，找到/etc/my.cnf配置文件，增加以下配置：

# 不要和其他mysql服务id重复即可
[mysqld]
server-id=106

接着使用命令行登录到mysql服务器：

mysql -u root -p

然后输入密码登录进去。

进入到mysql后，再输入以下命令：

-- 设置启动参数
mysql> CHANGE MASTER TO 
    -> MASTER_HOST='192.168.121.128',
    -> MASTER_USER='root',
    -> MASTER_PASSWORD='123456',
    -> MASTER_LOG_FILE='mysql-bin.000002',
    -> MASTER_LOG_POS=6274, # 与上文中Position 参数有关联关系
    -> master_port=3306;
Query OK, 0 rows affected, 2 warnings (0.00 sec)

还没完，设置完之后需要启动：

# 启动slave服务
mysql> start slave;
Query OK, 0 rows affected (0.01 sec)

# 查看启动状态
mysql> show slave status\G;
*************************** 1. row ***************************
               Slave_IO_State: Waiting for master to send event
                  Master_Host: 192.168.121.128
                  Master_User: root
                  Master_Port: 3306
                Connect_Retry: 60
              Master_Log_File: mysql-bin.000002
          Read_Master_Log_Pos: 6274
               Relay_Log_File: 4020cf02bc1a-relay-bin.000002
                Relay_Log_Pos: 320
        Relay_Master_Log_File: mysql-bin.000002
             Slave_IO_Running: Yes
            Slave_SQL_Running: Yes
              Replicate_Do_DB: 
          Replicate_Ignore_DB: 
           Replicate_Do_Table: 
       Replicate_Ignore_Table: 
      Replicate_Wild_Do_Table: 
  Replicate_Wild_Ignore_Table: 
                   Last_Errno: 0
                   Last_Error: 
                 Skip_Counter: 0
          Exec_Master_Log_Pos: 6274
              Relay_Log_Space: 534
              Until_Condition: None
               Until_Log_File: 
                Until_Log_Pos: 0
           Master_SSL_Allowed: No
           Master_SSL_CA_File: 
           Master_SSL_CA_Path: 
              Master_SSL_Cert: 
            Master_SSL_Cipher: 
               Master_SSL_Key: 
        Seconds_Behind_Master: 0
Master_SSL_Verify_Server_Cert: No
                Last_IO_Errno: 0
                Last_IO_Error: 
               Last_SQL_Errno: 0
               Last_SQL_Error: 
  Replicate_Ignore_Server_Ids: 
             Master_Server_Id: 104
                  Master_UUID: fa885b15-e168-11eb-a690-0242ac110002
             Master_Info_File: /var/lib/mysql/master.info
                    SQL_Delay: 0
          SQL_Remaining_Delay: NULL
      Slave_SQL_Running_State: Slave has read all relay log; waiting for more updates
           Master_Retry_Count: 86400
                  Master_Bind: 
      Last_IO_Error_Timestamp: 
     Last_SQL_Error_Timestamp: 
               Master_SSL_Crl: 
           Master_SSL_Crlpath: 
           Retrieved_Gtid_Set: 
            Executed_Gtid_Set: 
                Auto_Position: 0
         Replicate_Rewrite_DB: 
                 Channel_Name: 
           Master_TLS_Version: 
1 row in set (0.00 sec)

这俩值都为 true时 则为成功

        Slave_IO_Running: Yes # io线程 此线程为NO 则可能是 数据过大等原因
        Slave_SQL_Running: Yes # sql 线程 此线程为NO 就是SQL 同步异常 根据Position 参数解决

3. 测试

在master主机执行sql：

CREATE TABLE `tb_commodity_info` (
  `id` varchar(32) NOT NULL,
  `commodity_name` varchar(512) DEFAULT NULL COMMENT '商品名称',
  `commodity_price` varchar(36) DEFAULT '0' COMMENT '商品价格',
  `number` int(10) DEFAULT '0' COMMENT '商品数量',
  `description` varchar(2048) DEFAULT '' COMMENT '商品描述',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='商品信息表';

4. 监控 主从复制是否正常

可使用一些第三方软件 zabbix 、nagios。建议用相语言自己写，不必为了监控功能为系统增加一个可能的故障节点

mysql> show master status; #查看主节点状态
+------------------+----------+--------------+------------------+-------------------+
| File             | Position | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+----------+--------------+------------------+-------------------+
| mysql-bin.000002 |     6274 | test_db      |                  |                   |
+------------------+----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)


-- 设置启动参数
mysql> CHANGE MASTER TO 
    -> MASTER_HOST='192.168.121.128',
    -> MASTER_USER='root',
    -> MASTER_PASSWORD='123456',
    -> MASTER_LOG_FILE='mysql-bin.000002',
    -> MASTER_LOG_POS=6274, # 与上文中Position 参数有关联关系
    -> master_port=3306;


mysql> show slave status;# 查看从节点状态

mysql>start slave; #开启复制

mysql>stop slave; #停止复制

ShardingSphere Apache 顶级项目，稳定持续更新

读写分离，分库分表，分布式事务，数据分片，分布式治理 等操作---------

读写分离 {ShardingSphere}

• ShardingSphere Apache 顶级项目，稳定持续更新

使用ShardingSphere实现读写分离，和分库分表操作 ，mybatis-plus 多数据源也有相似功能，但是国内开源后台支持不足，功能有限

官方文档地址 官方文档

1. 引入依赖

        <dependency>
            <groupId>org.apache.shardingsphere</groupId>
            <artifactId>sharding-jdbc-spring-boot-starter</artifactId>
            <version>4.1.1</version>
        </dependency>

SpringBoot 配置

server:
  port: 9003
spring:
  shardingsphere:
    datasource:
      names: master,slave0 # 真实数据源
      master:
        type: com.zaxxer.hikari.HikariDataSource 
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://192.168.121.128:3306/test_db?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
        username: root
        password: 123456
      slave0:
        type: com.zaxxer.hikari.HikariDataSource
        driver-class-name: com.mysql.cj.jdbc.Driver
        jdbc-url: jdbc:mysql://192.168.121.128:3307/test_db?useUnicode=true&characterEncoding=utf8&tinyInt1isBit=false&useSSL=false&serverTimezone=GMT
        username: root
        password: 123456
    props:
      sql.show: true # 打印sql
    masterslave:
      name: ds_ms #逻辑数据源名称
      master-data-source-name: master # 写数据源名称
      slave-data-source-names: slave0 # 读数据源名称
      load-balance-algorithm-type: ROUND_ROBIN #负载均衡算法

基本按文档来吧---------

分库分表操作