Mycat 使用問題一則 通過mycat插入，卻不能查詢

遊戲項目需要全球同服，做分布式架構。資料庫需要分庫，在使用 Mycat 中介軟體時，需要一個插入全域唯一id的問題，測試代碼如下：

import pymysqlimport datetimeimport randomimport socketimport timecon = pymysql.connect(host = "127.0.0.1", port = 8066, user = "root", passwd = "123456")local_ip = socket.gethostbyname(socket.gethostname())cur_mycat = con.cursor()cur_mycat.execute("use endlessworld_db")insert_test_str = "insert into `tb_test` (id,username) values(%s,%s)"num_list = list(range(1, 1000))random.shuffle(num_list)def insert_record():for i in num_list:str_i = str(i)#cur_mycat.execute("select next value for MYCATSEQ_GLOBAL")#next_id = cur_mycat.fetchall()[0][0]explain = cur_mycat.execute(insert_test_str, ("next value for MYCATSEQ_GLOBAL", "robot" + str_i))con_1 = pymysql.connect(host = "127.0.0.1", port = 3306, user = "root", passwd = "123456")cur_1 = con_1.cursor()cur_1.execute("use endlessworld_1")con_2 = pymysql.connect(host = "127.0.0.1", port = 3306, user = "root", passwd = "123456")cur_2 = con_2.cursor()cur_2.execute("use endlessworld_2")con_3 = pymysql.connect(host = "127.0.0.1", port = 3306, user = "root", passwd = "123456")cur_3 = con_3.cursor()cur_3.execute("use endlessworld_3")def query_test():cur_1.execute("select * from tb_test")record_result_1 = cur_1.fetchall()record_list_1 = [i[0] for i in record_result_1]cur_2.execute("select * from tb_test")record_result_2 = cur_2.fetchall()record_list_2 = [i[0] for i in record_result_2]cur_3.execute("select * from tb_test")record_result_3 = cur_3.fetchall()record_list_3 = [i[0] for i in record_result_3]print(len(record_list_1), len(record_list_2), len(record_list_3))record_list = record_list_1 + record_list_2 + record_list_3#print(len(record_list))not_count = 0for player_id in record_list:cur_mycat.execute("select * from tb_test where id = %s",(player_id))user_result = cur_mycat.fetchall()if not user_result:#print(player_id)not_count += 1print(not_count)start = time.time()insert_record()end = time.time()print("total insert cost time ", end - start)query_test()

採用的分庫方式為按照 id 作一致性雜湊 schema.xml 中分庫表的配置：

<table name="tb_test" primaryKey="id" autoIncrement="true" dataNode="dn1,dn2,dn3" rule="sharding-by-murmur" />

測試的結果如下：

通過mycat插入1000 條記錄，全部插入成功，可以在各個分庫中都查詢到，大致也是平衡均勻的。然後通過 mycat 查詢時，發現大部分記錄都不能通過mycat查詢到。

在命令列使用 explain 查詢插入的一條記錄，發現這條插敘記錄被路由到分庫dn1上。

而實際該條記錄被插入到了 dn3 中

修改插入的代碼，即放開注釋，更改擷取下一個全域唯一id的方式

cur_mycat.execute("select next value for MYCATSEQ_GLOBAL")next_id = cur_mycat.fetchall()[0][0]explain = cur_mycat.execute(insert_test_str, (next_id, "robot" + str_i))

再插入一百萬條記錄：

可以看到這回一百萬條記錄插入之後，也能通過mycat正常的查詢到。所以問題就在全域唯一id的地方，需要先擷取該id，整數表達的id才行。