MySQL優(yōu)化（超完整版）（二）

7. MySQL分庫分表

（1） 分庫分表概念介紹

  MySQL的分庫分表有兩種方式：垂直拆分和水平拆分。
  垂直拆分：垂直拆分就是要把表按模塊劃分到不同數(shù)據(jù)庫表中（當(dāng)然原則還是不破壞第三范式），這種拆分在大型網(wǎng)站的演變過程中是很常見的。當(dāng)一個網(wǎng)站還在很小的時候，只有小量的人來開發(fā)和維護(hù)，各模塊和表都在一起，當(dāng)網(wǎng)站不斷豐富和壯大的時候，也會變成多個子系統(tǒng)來支撐，這時就有按模塊和功能把表劃分出來的需求。其實(shí)，相對于垂直切分更進(jìn)一步的是服務(wù)化改造，說得簡單就是要把原來強(qiáng)耦合的系統(tǒng)拆分成多個弱耦合的服務(wù)，通過服務(wù)間的調(diào)用來滿足業(yè)務(wù)需求看，因此表拆出來后要通過服務(wù)的形式暴露出去，而不是直接調(diào)用不同模塊的表。（垂直拆分用于分布式場景）
   水平拆分：解決單表大數(shù)據(jù)量的問題，水平切分就是要把一個表按照某種規(guī)則把數(shù)據(jù)劃分到不同表或數(shù)據(jù)庫里。例如：在大型電商系統(tǒng)中，每天的會員人數(shù)不斷的增加。達(dá)到一定瓶頸后如何優(yōu)化查詢。通過將表數(shù)據(jù)水平分割成不同的表來實(shí)現(xiàn)優(yōu)化。（實(shí)現(xiàn)規(guī)則：hash、時間、不同的維度）
   通俗理解：水平拆分行，行數(shù)據(jù)拆分到不同表中， 垂直拆分列，表數(shù)據(jù)拆分到不同表中。

（2） 水平分表的案例

   分表原理：取模拆分（一致性hash），可以將數(shù)據(jù)分配的比較均勻。
這里我們以3張表為例：

案例：首先我創(chuàng)建三張表 user0 / user1 /user2 , 然后我再創(chuàng)建 uuid表，該表的作用就是提供自增的id。
代碼實(shí)現(xiàn)：

 -- 建表語句
create table user0(
id int unsigned primary key ,
name varchar(22) not null default '',
pwd  varchar(22) not null default '')
engine=myisam charset utf8;

create table user1(
id int unsigned primary key ,
name varchar(22) not null default '',
pwd  varchar(22) not null default '')
engine=myisam charset utf8;

create table user2(
id int unsigned primary key ,
name varchar(22) not null default '',
pwd  varchar(22) not null default '')
engine=myisam charset utf8;

create table uuid(
id int unsigned primary key auto_increment)engine=myisam charset utf8;

//分表邏輯
@Service
public class UserService {
    @Autowired
    private JdbcTemplate jdbcTemplate;
    public String regit(String name, String pwd) {
        // 1.先獲取到 自定增長ID
        String idInsertSQL = "INSERT INTO uuid VALUES (NULL);";
        jdbcTemplate.update(idInsertSQL);
        Long insertId = jdbcTemplate.queryForObject("select last_insert_id()", Long.class);
        // 2.判斷存儲表名稱
        String tableName = "user" + insertId % 3;
        // 3.注冊數(shù)據(jù)
        String insertUserSql = "INSERT INTO " + tableName + " VALUES ('" + insertId + "','" + name + "','" + pwd
                + "');";
        System.out.println("insertUserSql:" + insertUserSql);
        jdbcTemplate.update(insertUserSql);
        return "success";
    }

    public String get(Long id) {
        String tableName = "user" + id % 3;
        String sql = "select name from " + tableName + "  where id="+id;
        System.out.println("SQL:" + sql);
        String name = jdbcTemplate.queryForObject(sql, String.class);
        return name;
    }
}

8.MySQL的主從復(fù)制與讀寫分離

（1） 主從復(fù)制介紹

  上圖中192.168.8.40是主節(jié)點(diǎn)（MYSQL-A），192.168.8.41是從節(jié)點(diǎn)（MYSQL-B）。
  影響MySQL-A數(shù)據(jù)庫的操作，在數(shù)據(jù)庫執(zhí)行后，都會寫入本地的日志系統(tǒng)A中。 假設(shè)，實(shí)時的將變化了的日志系統(tǒng)中的數(shù)據(jù)庫事件操作，在MYSQL-A的3306端口，通過網(wǎng)絡(luò)發(fā)給MYSQL-B。 MYSQL-B收到后，寫入本地日志系統(tǒng)B，然后一條條的將數(shù)據(jù)庫事件在數(shù)據(jù)庫中完成。那么，MYSQL-A的變化，MYSQL-B也會變化，這樣就是所謂的MYSQL的復(fù)制，即MYSQL replication。
  MYSQL的日志類型中的二進(jìn)制日志，也就是專門用來保存修改數(shù)據(jù)庫表的所有動作，即bin log?！咀⒁釳YSQL會在執(zhí)行語句之后，釋放鎖之前，寫入二進(jìn)制日志，確保事務(wù)安全】
  日志系統(tǒng)B，并不是二進(jìn)制日志，由于它是從MYSQL-A的二進(jìn)制日志復(fù)制過來的，并不是自己的數(shù)據(jù)庫變化產(chǎn)生的，有點(diǎn)接力的感覺，稱為中繼日志，即relay log。
  主從復(fù)制，所產(chǎn)生的問題：
   - 主服務(wù)器如何實(shí)現(xiàn)負(fù)載均衡、高可用。
   - 如何保證數(shù)據(jù)不丟失。
   - 如何保證主從數(shù)據(jù)一致性。

（2） 讀寫分離介紹

  簡介：在數(shù)據(jù)庫集群架構(gòu)中，讓主庫負(fù)責(zé)處理事務(wù)性查詢，而從庫只負(fù)責(zé)處理select查詢，讓兩者分工明確達(dá)到提高數(shù)據(jù)庫整體讀寫性能。當(dāng)然，主數(shù)據(jù)庫另外一個功能就是負(fù)責(zé)將事務(wù)性查詢導(dǎo)致的數(shù)據(jù)變更同步到從庫中，也就是寫操作。
  寫分離的好處：分?jǐn)偡?wù)器壓力，提高機(jī)器的系統(tǒng)處理效率。增加冗余，提高服務(wù)器性能，當(dāng)一臺服務(wù)器宕機(jī)后可以從另一個庫以最快的方式恢復(fù)服務(wù)。

（3） Linux對MySQL主從復(fù)制配置

環(huán)境介紹：

① 安裝MySQL
#這里小編是通過rpm&&yum安裝的MySQL

#下載安裝包
$wget http://repo.mysql.com/mysql57-community-release-el7-8.noarch.rpm
#安裝
$rpm -ivh mysql57-community-release-el7-8.noarch.rpm
$yum install mysql-server
#啟動MySQL服務(wù)
$systemctl start mysqld
#查看root初始密碼
$grep 'temporary password' /var/log/mysqld.log

#重設(shè)root密碼
$mysql_secure_installation
注意：

這里配置N，不然的話無法使用root登錄MySQL。

#修改root密碼并設(shè)置可以遠(yuǎn)程訪問：
mysql> use mysql;
mysql> update user set authentication_string=password("123456") where user="root";
mysql> flush privileges;
mysql> select 'host','user' from user where user='root';

mysql> UPDATE user   SET grant_priv = 'Y'   WHERE       user = 'root';
mysql> select host,user from user;

mysql> update user set host = '%' where user = 'root';
mysql> select host,user from user;

mysql> flush privileges;
mysql> quit

#測試是否配置成功
$mysql -uroot -p -h 192.168.xxx.xxx

② 創(chuàng)建數(shù)據(jù)庫
分別在master和slave中創(chuàng)建一個數(shù)據(jù)庫：

mysql> create database test;

注意：這里從數(shù)據(jù)庫中一定要存在于主數(shù)據(jù)庫中同步的庫，否則在同步時會報錯：

③ 修改master中的配置

$vim /etc/my.cnf
#master 加入
server-id=1
log-bin=mysql-bin
log-slave-updates=1
#需要同步的數(shù)據(jù)庫
binlog-do-db=test
#被忽略的數(shù)據(jù)
binlog-ignore-db=mysql

④ 在master中創(chuàng)建salve同步賬號

mysql> grant replication slave on *.* to 'user1'@'192.168.130.133' identified by 'Zy.123456';
mysql> flush  privileges;

⑤ 重啟master并查看日志情況

$systemctl restart mysqld
mysql> show master status;

⑥ 修改slave中MySQL的配置

#slave 加入
server-id=2
log-bin= mysql-bin
relay-log= mysql-relay-bin
read-only=1
log-slave-updates=1
#要同步的數(shù)據(jù)庫,不寫本行表示同步所有數(shù)據(jù)庫
replicate-do-db=test

⑦ 重啟slave并驗(yàn)證是否可以連接master

$systemctl restart mysqld
$mysql -uuser1 -p123456 -h 192.168.130.133
mysql> show grants for user1@192.168.130.133;

⑧ 設(shè)置slave復(fù)制 并啟動slave

mysql>change master to master_host='192.168.130.134',master_user='user1',master_password='Zy.123456';
-- 啟動slave
mysql> start slave;
mysql> SHOW SLAVE STATUS;

主要查看Slave_IO_Running和Slave_SQL_Running 兩列是否都為YES。

⑨測試主從服務(wù)是否同步
在主服務(wù)器中執(zhí)行：

mysql> use test;
mysql> create table test(id int,name char(10));
mysql> insert into test values(1,'zaq');
mysql> insert into test values(1,'xsw');
mysql> select * from test;

此時查看從服務(wù)器：

到此主從復(fù)制就配置完成！

（4） 補(bǔ)充

  主庫中已有數(shù)據(jù)的解決方案：
   - 方案一：選擇忽略主庫之前的數(shù)據(jù)，不做處理。這種方案只適用于不重要的可有可無的數(shù)據(jù)，并且業(yè)務(wù)上能夠容忍主從庫數(shù)據(jù)不一致的場景。
   - 方案二：對主庫的數(shù)據(jù)進(jìn)行備份，然后將主數(shù)據(jù)庫中導(dǎo)出的數(shù)據(jù)導(dǎo)入到從數(shù)據(jù)庫，然后再開啟主從復(fù)制，以此來保證主從數(shù)據(jù)庫數(shù)據(jù)一致。
這里小編介紹如何使用方案二進(jìn)行數(shù)據(jù)同步。
① 備份數(shù)據(jù)
假設(shè)這里我們有一個庫：weibo并且數(shù)據(jù)庫中有相應(yīng)的數(shù)據(jù)：

#鎖定主表
mysql> flush tables with read lock;  #保證表只能做讀操作

#查看此時主數(shù)據(jù)庫狀態(tài)，并記錄bin-file和pos

mysql>show master status;

#在/etc/my.cnf中加入:

[mysqldump]
user = root
password = rootpassword

#重啟服務(wù)：
[root@zzy ~]# systemctl restart mysqld

#備份數(shù)據(jù)庫
mysqldump weibo > weibo_back.sql
然后將備份數(shù)據(jù)文件傳入從機(jī)器中，
在mysql中執(zhí)行：source /path/ weibo_back.sql

此時主從數(shù)據(jù)庫的數(shù)據(jù)已經(jīng)同步！
② 修改配置
這里主從服務(wù)器的配置同上一節(jié)說道的相同。
③ 啟動slave

mysql>stop slave;
mysql>reset slave; change master to 
mysql>master_host='192.168.130.134',
>master_user='user2',
>master_password='Zy.123456',
>master_log_file='mysql-bin.000004',
>master_log_pos=154;
mysql>start slave;
mysql>SHOW SLAVE STATUS;

注意：這里的master_log_file和master_log_pos一定要和從主數(shù)據(jù)庫中通過“show master status”命令查到的相同。
④ 解鎖主數(shù)據(jù)庫

mysql>unlock tables;

然后我們就可以測試，在主數(shù)據(jù)庫中插入一條記錄，看看從數(shù)據(jù)庫是否有數(shù)據(jù)同步。

9. Mycat介紹與使用

（1） mycat介紹

  mycati一個開源的分布式數(shù)據(jù)庫系統(tǒng)，但是因?yàn)閿?shù)據(jù)庫一般都有自己的數(shù)據(jù)庫引擎，而mycat沒有屬于自己獨(dú)有的數(shù)據(jù)庫引擎，所以嚴(yán)格意義上來講并不能算是一個完整的數(shù)據(jù)庫系統(tǒng)，只能是一個在應(yīng)用和數(shù)據(jù)庫之間的服務(wù)中間件。
  在mycat中間件出現(xiàn)之間，MySQL主從復(fù)制集群，如果要實(shí)現(xiàn)讀寫分離，一般是在程序段實(shí)現(xiàn)，這樣就帶來了一個問題，即數(shù)據(jù)段和程序的耦合度太高，如果數(shù)據(jù)庫的地址發(fā)生了改變，那么我的程序也要進(jìn)行相應(yīng)的修改，如果數(shù)據(jù)庫不小心掛掉了，則同時也意味著程序的不可用，而對于很多應(yīng)用來說，并不能接受；引入Mycat中間件能很好地對程序和數(shù)據(jù)庫進(jìn)行解耦，這樣，程序只需關(guān)注數(shù)據(jù)庫中間件的地址，而無需知曉底層數(shù)據(jù)庫是如何提供服務(wù)的，大量的通用數(shù)據(jù)聚合、事務(wù)、數(shù)據(jù)源切換等工作都由中間件來處理；Mycat中間件的原理是對數(shù)據(jù)進(jìn)行分片處理，從原有的一個庫，被切分為多個分片數(shù)據(jù)庫，所有的分片數(shù)據(jù)庫集群構(gòu)成完成的數(shù)據(jù)庫存儲，有點(diǎn)類似磁盤陣列中的RAID0。
  通過使用mycat可以很輕松的實(shí)現(xiàn)數(shù)據(jù)庫的讀寫分離，并且由于其特點(diǎn)，不僅實(shí)現(xiàn)負(fù)載均衡，而且提高了集群的安全性。

（2） mycat的安裝

  注意，這里mycat只是做了負(fù)載均衡，主從復(fù)制并沒有做，所以，如果想讓MySQL集群可以讀寫分離，就需要使用mycat并沿用原有的主從復(fù)制的配置。
  這里小編遇到的一個坑就是，最好使用mycat1.5即以上的版本，不然對一些鏈接工具會報錯：出現(xiàn)no mycat database selected問題！
這里小編使用的是1.6Linux版本。
首先下載相應(yīng)的版本：http://dl.mycat.io/1.6-RELEASE/
① 保證MySQL的機(jī)器環(huán)境中有JDK1.8

需要配置JAVA_HOME！
這里注意，經(jīng)驗(yàn)推薦配置在相應(yīng)用戶的~/.bashrc下，不要都配置在/etc/profile中。
② 配置mycat
這里需要配置4個文件：server.xml 、schema.xml、 rule.xml 、log4j2.xml
這里小編就簡單介紹下：
server.xml：配置用戶
schema.xml：配置關(guān)聯(lián)的表和庫，以及連接MySQL的host和密碼
rule.xml：配置相應(yīng)的分片規(guī)則
log4j2.xml：配置日志級別。

#server.xml加入：
<user name="mycat">
                <property name="password">123456</property>
                <property name="schemas">mycat</property>
</user>
<user name="mycat_read">
                <property name="password">123456</property>
                <property name="schemas">mycat</property>
                <property name="readOnly">true</property>
</user>

#schema.xml修改為：
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

    <schema name="mycat" checkSQLschema="true" sqlMaxLimit="100" dataNode="dn1" />
    <dataNode name="dn1" dataHost="localhost1" database="weibo" />
    <dataHost name="localhost1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" 
        dbDriver="native" switchType="1"  slaveThreshold="100">
        <heartbeat>select user()</heartbeat>
        <!-- can have multi write hosts -->
        <writeHost host="hostM1" url="192.168.130.134:3306" user="root" password="123456">
        </writeHost>
        <writeHost host="hostM2" url="192.168.130.133:3306" user="root" password="123456"/>
    </dataHost>
</mycat:schema>

#rule.xml基本不變

# log4j2.xml將日志修改為debug
<Loggers>
        <asyncRoot level="debug" includeLocation="true">
            <AppenderRef ref="Console" />
            <AppenderRef ref="RollingFile"/>
        </asyncRoot>
</Loggers>

③ 啟動mycat
進(jìn)入mycat的bin下

#運(yùn)行 
$sh mycat start /sh startup_nowrap.sh

④ 測試連接
這里可以通過MySQL進(jìn)行連接：

[root@zzy bin]# mysql -umycat -P8066 -p123456 -h227.0.0.1

這里的user和password就是在server.xml中配置的。
也可以通過工具連接，但是需要注意，這里連接的數(shù)據(jù)庫一定要是mycat。

（3） 相應(yīng)問題：

1）Error: Could not create the Java Virtual Machine. OpenJDK Server VM warning: ignoring option MaxPermSize=64M; support was removed in 8.0 .Invalid maximum heap size: -Xmx4G

這個問題是因?yàn)椋簃ycat啟動時需要的內(nèi)存默認(rèn)最大為4G，最小為1G，小編這里使用的虛擬機(jī)沒有這么大內(nèi)存，所以需要修改$MYCAT_HOMT/conf/ wrapper.conf中：

把配置調(diào)整下就行。

2）Error: Exception thrown by the agent : java.net.MalformedURLException: Local host name unknown: java.net.UnknownHostException: zzy: zzy: Name or service not known

這個問題就是需要在/etc/hosts中配置當(dāng)前主機(jī)與IP的映射：

（4） 相應(yīng)測試

  這里我們配置了mycat和主從復(fù)制，這里我們只需要連接mycat的8066端口，通過mycat用戶就可以對數(shù)據(jù)庫進(jìn)行CRUD，操作。這里我們連接mycat：

在weibo庫下的t_message表中插入一條記錄：

然后分別查看master數(shù)據(jù)庫和slave數(shù)據(jù)庫：

到此，MySQL的負(fù)載均衡高可用版的讀寫分離就完成了！
以上兩張測試表的語句：

DROP TABLE IF EXISTS `t_message`;

CREATE TABLE `t_message` (
  `messages_id` varchar(64) NOT NULL COMMENT '微博ID',
  `user_id` varchar(64) NOT NULL COMMENT '發(fā)表用戶',
  `messages_info` varchar(255) DEFAULT NULL COMMENT '微博內(nèi)容',
  `messages_time` datetime DEFAULT NULL COMMENT '發(fā)布時間',
  `messages_commentnum` int(12) DEFAULT NULL COMMENT '評論次數(shù)',
  `message_deleteflag` tinyint(1) NOT NULL COMMENT '刪除標(biāo)記 1：已刪除 0：未刪除',
  `message_viewnum` int(12) DEFAULT NULL COMMENT '被瀏覽量',
  PRIMARY KEY (`messages_id`),
  KEY `user_id` (`user_id`),
  CONSTRAINT `t_message_ibfk_1` FOREIGN KEY (`user_id`) REFERENCES `t_users` (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

LOCK TABLES `t_message` WRITE;
;
INSERT INTO `t_message` VALUES ('0001','1001','isfnesnfw','2019-09-01 00:00:00',2,1,2),('0002','1002','isfnesnfw','2019-08-21 00:00:00',2,1,2),('0003','1002','isfnesnfw','2019-08-21 00:00:00',2,1,2);

UNLOCK TABLES;

DROP TABLE IF EXISTS `t_users`;

CREATE TABLE `t_users` (
  `user_id` varchar(64) NOT NULL COMMENT '注冊用戶ID',
  `user_email` varchar(64) NOT NULL COMMENT '注冊用戶郵箱',
  `user_password` varchar(64) NOT NULL COMMENT '注冊用戶密碼',
  `user_nikename` varchar(64) NOT NULL COMMENT '注冊用戶昵稱',
  `user_creatime` datetime NOT NULL COMMENT '注冊時間',
  `user_status` tinyint(1) NOT NULL COMMENT '驗(yàn)證狀態(tài)  1：已驗(yàn)證  0：未驗(yàn)證',
  `user_deleteflag` tinyint(1) NOT NULL COMMENT '刪除標(biāo)記  1：已刪除 0：未刪除',
  PRIMARY KEY (`user_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

LOCK TABLES `t_users` WRITE;

INSERT INTO `t_users` VALUES ('1001','www.415511@qq.com','123456','zsa','2019-08-09 00:00:00',1,2),('1002','www.2345@qq.com','4578964','zsa','2019-07-09 00:00:00',2,3),('1003','www.41we11@qq.com','123456','zsa','2019-08-09 00:00:00',1,2),('1004','www.41523511@qq.com','1456','zasdsa','2018-08-09 00:00:00',1,2);

UNLOCK TABLES;