天天看點
傳回

mysql分表詳解

到底為什麼要分表？

難道資料量大就要分表？

mysql資料量對索引的影響

本人mysql版本為5.7

新增資料測試

為了測試mysql索引查詢是否和資料量有關，本人做了以下的測試準備：

建立4個表article1，article2，article3，article4，article5 每個表分别插入20萬，50萬，100萬，200萬，1500萬的資料，資料都是随機生成

create table test.article1(

  id          int auto_increment comment 'id'

    primary key,

  user_id     int          not null comment '使用者id',

  title       varchar(64)  not null comment '标題',

  add_time    datetime     null comment '新增時間',

  update_time int          null comment '更新時間',

  description varchar(255) null comment '簡介',

  status      tinyint(1)   null comment '狀态 1正常 0隐藏'

)

  charset = utf8;

 

create index article_title_index

  on test.article1 (title);

生成資料腳本，使用easyswoole，多協程插入：

<?php

include "./vendor/autoload.php";

\EasySwoole\EasySwoole\Core::getInstance()->initialize();

for ($i = 0; $i <= 2000; $i++) {//協程最多3000,建立1000個協程

    go(function () use ($i) {

        \App\Utility\Pool\MysqlPool::invoke(function (\App\Utility\Pool\MysqlPoolObject $mysqlObject) use ($i) {

            for ($y = 0; $y <= 1000; $y++) {//每個協程插入100條資料

                $data = [

                    'user_id'     => mt_rand(1, 2500),

                    'title'       => \EasySwoole\Utility\Random::character(32),//随機生成32位字母的标題

                    'add_time'    => date('Y-m-d H:i:s', mt_rand(strtotime('2018-01-01'), strtotime('2019-01-01'))),//随機生成日期

                    'update_time' => mt_rand(strtotime('2018-01-01'), strtotime('2019-01-01')),//随機生成日期

                    'description' => getChar(mt_rand(8, 64)),//随機生成8-64位漢字,

                    'status'      => mt_rand(0, 1),

                ];

                $mysqlObject->insert('article2', $data);

            }

            echo "協程$i 插入完成\n";

        }, -1);

    });

}

 

function getChar($num)  // $num為生成漢字的數量

{

    $b = '';

    for ($i = 0; $i < $num; $i++) {

        // 使用chr()函數拼接雙位元組漢字，前一個chr()為高位位元組，後一個為低位位元組

        $a = chr(mt_rand(0xB0, 0xD0)) . chr(mt_rand(0xA1, 0xF0));

        // 轉碼

        $b .= iconv('GB2312', 'UTF-8', $a);

    }

    return $b;

}

生成的資料如圖：

資料庫總條數預覽：

select (select count(1) from article1) as "1" , (select count(1) from article2) as "2", (select count(1) from article3) as "3", (select count(1) from article4) as "4", (select count(1) from article5) as "5";

查詢時間測試

查詢腳本

<?php

/**

 * Created by PhpStorm.

 * User: tioncico

 * Date: 19-5-11

 * Time: 下午7:20

 */

include "./vendor/autoload.php";

\EasySwoole\EasySwoole\Core::getInstance()->initialize();

go(function () {

    /**

     * @var $db \App\Utility\Pool\MysqlPoolObject

     */

    $db = \App\Utility\Pool\MysqlPool::defer();

    $startTime = microtimeFloat();

    //查詢1000次

    for ($i = 0; $i < 10000; $i++) {

        $str =\EasySwoole\Utility\Random::character(32);//随機生成字元串，用于查詢

        $data = $db->where('title',$str)->getOne('article1');

    }

 

    echo "1耗時" . (microtimeFloat() - $startTime) . '秒'.PHP_EOL;

    $startTime = microtimeFloat();

    //查詢1000次

    for ($i = 0; $i < 10000; $i++) {

        $str =\EasySwoole\Utility\Random::character(32);//随機生成字元串，用于查詢

        $data = $db->where('title',$str)->getOne('article2');

    }

 

    echo "2耗時" . (microtimeFloat() - $startTime) . '秒'.PHP_EOL;

    $startTime = microtimeFloat();

    //查詢1000次

    for ($i = 0; $i < 10000; $i++) {

        $str =\EasySwoole\Utility\Random::character(32);//随機生成字元串，用于查詢

        $data = $db->where('title',$str)->getOne('article3');

    }

 

    echo "3耗時" . (microtimeFloat() - $startTime) . '秒'.PHP_EOL;

    $startTime = microtimeFloat();

    //查詢1000次

    for ($i = 0; $i < 10000; $i++) {

        $str =\EasySwoole\Utility\Random::character(32);//随機生成字元串，用于查詢

        $data = $db->where('title',$str)->getOne('article4');

    }

 

    echo "4耗時" . (microtimeFloat() - $startTime) . '秒'.PHP_EOL;

    $startTime = microtimeFloat();

    //查詢1000次

    for ($i = 0; $i < 10000; $i++) {

        $str =\EasySwoole\Utility\Random::character(32);//随機生成字元串，用于查詢

        $data = $db->where('title',$str)->getOne('article5');

    }

 

    echo "5耗時" . (microtimeFloat() - $startTime) . '秒'.PHP_EOL;

});

function microtimeFloat()

{

    list($usec, $sec) = explode(" ", microtime());

    return ((float)$usec + (float)$sec);

}

該腳本是一個執行個體腳本，在後面的其他測試中依舊使用該腳本，修改下字段和邏輯

title全索引查詢一條時間情況：（為了準确，本人運作了多次）

可以看出，資料量在200萬以下時，查詢時間幾乎沒有差别，隻是在資料量1400萬時，查詢1萬次的時間增加了1秒

注：本人在之前測試，和之後測試時，查詢article5時時間大概是2.1-2.5秒左右，可能mysql有其他知識點本人未掌握，是以沒法詳細解釋

title全索引查詢不限制條數時間情況：（為了準确，本人運作了多次）

可以看出，在200萬資料之前 查詢時間并沒有太大的差距，1400萬有一點點的差距

title like 左字首 索引查詢不限制條數時間情況：（為了準确，本人運作了多次）

根據這次測試，我們可以發現

1：mysql的查詢和資料量的大小關系并不大（微乎其微）

2：mysql隻要是命中索引，不管資料量有多大，都會非常快（快的一批，由于本人比較懶，并且本人之前也測試過單表1.5億速度一樣很快，就懶得繼續新增2億測試資料了，太累）

什麼情況需要分表

從上面的章節可以發現，資料量的多少和查詢速度其實關系不是很大，那麼為什麼要分表呢？原因有以下幾種：

1:   單表 不涉及索引的操作太多，無法直接命中索引的

2：模糊查找範圍過大，無法直接命中索引的，例如日志表查時間區間

3：單表資料量過大，操作繁忙的

4：資料量過大，有大部分資料很少通路的（冷熱資料）

5：裝逼，需要用分表裝逼的

分表優缺點

在上面，我們已經知道了為什麼要分表，分表該怎麼分呢？

首先，我們需要先搞懂分表的意義

資料分表有着以下好處：

1：分散表壓力，使其響應速度提高

2：資料降維，提升查詢速度

3：分冷熱資料，更好管理，備份

4：支援分布式部署資料庫，将壓力分擔到其他伺服器中

同時，缺點如下：

1：分表之後較難管理多表

2：join表時可能需要join多個

3：查詢模糊資料時需要全部的表一起查

是以，資料量不大時候，不建議分表。

水準分表

根據資料的不同規則作為一個分表條件，區分資料以資料之間的分表叫做水準分表

水準分表是比較常見的分表方法，也是解決資料量大時候的分表方法，在水準分表中，也根據場景的不同而分表方法不同

取模分表

假設有個使用者表（1000w使用者）需要分表，那麼我們可以根據該使用者表的唯一辨別（id ，使用者賬号）進行取模分表

重新建立n個表。例如5個， user1，user2，user3....uesr5

取出所有使用者，根據 使用者賬号進行取模，例如：

<?php

$userAccount ='tioncico';

$num =  (crc32($userAccount)%5);

$tableName = 'user'.($num+1);

echo "{$userAccount}應該存儲到{$tableName}表";

//tioncico應該存儲到user3表

 不建議使用id分表，因為一般情況下，我們是使用賬号，或者其他唯一辨別 來進行區分某個人的，如果你表設計像qq号一樣，那完全可以将id命名為其他的字段，用于區分，自增id同樣需要

取模分表法會使資料盡量的均衡分布，壓力均衡，非常适合于需要通過特定辨別字段查找資料的表（會員表）

冷熱資料分表

冷熱資料大多數展現在跟時間有關的 日志表，訂單表上面

在冷熱資料分表時，我們應該遵循以下幾種分表規則

1：資料冷熱分表，需要注意冷熱資料的界限

例如，商城訂單表，每天增加100萬的訂單，一年就會增加到3.6億的訂單數，而大多數情況下，使用者隻會查詢近1-3個月的資料，我們可以

通過訂單時間進行分表，隻需要按照月份進行分表即可

2：通過取模分表，需要注意取模字段，

垂直分表

區分一條資料的不同字段，叫做垂直分表

垂直分表其實我們在設計資料庫時，可能已經是用到了的，比如會員金額表，關聯會員表的userId，這個時候，其實就可以叫做是垂直分表

把會員金額的字段分到了其他的表中（會員金額表）

垂直分表較為簡單，有以下幾種分法：

1：字段意義和表其他字段意義不同，可以嘗試分表

2：字段占用空間太大，不常用或隻在特定情況使用，可以嘗試分表

3：字段與其他字段更新時間不同，可以嘗試分表

php 分表 資料
上一篇: ipython的變量,如何在ipython中通路shell變量
下一篇: SAP HANA學習

繼續閱讀