[大数据]mysql 分库分表实战

1、生成1000w条数据。

如果用代码，会很久。1w条用了1分钟，1000w条数据估计1000分钟。

下面直接用存储过程。

?发现插入10000条数据用了0.9s, 插入10w条数据用了4.7s, 插入100w条数据用了58s左右,1000w条数，参考文章https://www.jb51.net/article/207999.htm

?事先先创建一个表

CREATE TABLE `users` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(255) DEFAULT NULL,
  `home` varchar(255) DEFAULT NULL,
  `balance` varchar(255) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=100001 DEFAULT CHARSET=utf8mb4;

登录mysql客户端 依次输入如下。
use test
delimiter "$"

 create procedure bathInsert(in args int)
    -> begin
    -> declare i int default 1;
    -> start transaction;
    -> while i <= args do
    -> insert into users(id,name,home,balance) value(i,"dgx","shengzheng","level-go");
    -> set i = i+1;
    -> end while;
    -> commit;
    -> end
    -> $

call bathInsert(10000000);$

即快速生成了1000w条数据。耗时1 min 14.77 sec)。

Query OK, 0 rows affected (1 min 14.77 sec)

如果name不一样，用concat函数，也可以在navicat那里编辑输入

CREATE DEFINER=`root`@`localhost` PROCEDURE `bathInsert2`(in args int)
begin
declare i int default 1;
start transaction;
while i <= args do
insert into users(id,name,home,balance) value(i,concat("dgx",i),"shengzheng","level-go");
 set i = i+1;
end while;
commit;
end

SELECT * FROM users WHERE `name` ='dgx1214000'? 没有用索引 耗时 2s.035多一点点。

解决方案一：

如果是sql语句，添加上：

UNIQUE KEY `name_index` (`name`) USING BTREE COMMENT '名字索引'

解决方案二：

import (
	"database/sql"
	"fmt"
	_ "github.com/go-sql-driver/mysql" //执行init(),注册mysql驱动
	"hash/crc32"
	"strconv"
	"time"
)
func main() {
	//方案二：用go语言官方自带驱动
	dsn := "root:root@tcp(127.0.0.1:3306)/goinsum"
	db, err := sql.Open("mysql", dsn)
	if err != nil {
		panic(error.Error(err))
	}
	fmt.Println("parameter verification succeeded！")
	//MakeHashTrans(db)
	ShardingSingle(db)

}

func MakeHashTrans(db *sql.DB) {
	rows, err := db.Query("select * from users")
	cloumns, err := rows.Columns()
	if err != nil {
		fmt.Println(err)
	}
	// 定义10个变量，分别存储不同的拼接内容，分别计数，如果到了10000，就批量插入数据库。
	var resultkey = [10]string{}
	var resultkeylast = [10]string{}
	var count = [10]int{}

	fmt.Println(resultkey)
	for rows.Next() {
		err := rows.Scan(&cloumns[0], &cloumns[1], &cloumns[2], &cloumns[3])
		if err != nil {
			fmt.Println(err)
		}
		result := "(" + cloumns[0] + ",'" + cloumns[1] + "','" + cloumns[2] + "'," + "'" + cloumns[3] + "'" + ")" + ","

		fmt.Println(cloumns[0])
		hashValue := crc32.ChecksumIEEE([]byte(cloumns[1]))
		tableId := hashValue % 10
		fmt.Println(tableId)
		a := int(tableId)
		count[a] = count[a] + 1
		fmt.Println(count[a])
		resultkey[a] = resultkey[a] + result
		// 积累到了10000,就批量插入数据库
		if count[a] == 100000 {
			resultkeylast[a] = resultkey[a][0 : len(resultkey[a])-1]
			qry := "insert into user_" + strconv.Itoa(a) + " (`uid`, `name`, `home`, `balance`) values" + resultkeylast[a]
			//fmt.Println(qry)
			_, err = db.Exec(qry)
			if err != nil {
				fmt.Println(err)
			}
			// 清零处理
			count[a] = 0
			resultkey[a] = ""
		}
		fmt.Printf("a ==%d,count[a]==%d", a, count[a])

	}

}

// 性能大PK，
func ShardingSingle(db *sql.DB) {
	//未分表搜索
	//SELECT * from users WHERE name = 'dgx51322'
	name := "dgx51322"

	st1 := time.Now()
	row, _ := db.Query("select * from users where name = '" + name + "'")
	fmt.Println("single query unsharding spend time:", time.Since(st1))
	cloumn1, err := row.Columns()
	if err != nil {
		fmt.Println(err)
	}
	for row.Next() {
		row.Scan(&cloumn1[0], &cloumn1[1], &cloumn1[2], &cloumn1[3])
		fmt.Println(cloumn1)
	}

	//分表搜索
	a := crc32.ChecksumIEEE([]byte(name)) % 10
	qryString := "select * from user_" + strconv.Itoa(int(a)) + " where name = '" + name + "'"
	st2 := time.Now()
	row2, _ := db.Query(qryString)
	fmt.Println("single query sharding spend time:", time.Since(st2))
	cloumn2, _ := row2.Columns()
	for row2.Next() {
		row2.Scan(&cloumn2[0], &cloumn2[1], &cloumn2[2], &cloumn2[3], &cloumn2[4])
		fmt.Println(cloumn2)
	}
	return
}

性能大pk，比较：

parameter verification succeeded！
single query unsharding spend time: 2.1368947s
[51322 dgx51322 shengzheng level-go]
single query sharding spend time: 1.5661ms
?