【Mysql中间件之kingshard】

Kingshard 是一个由珠海金山WPS 云平台团队陈非（@flikecn ）使用Go开发高性能MySQL Proxy项目，kingshard在满足基本的读写分离的功能上，致力于简化MySQL分库分表操作；能够让DBA通过kingshard轻松平滑地实现MySQL数据库扩容。

 

陈非（@flikecn ）。 2013年硕士毕业于电子科技大学，同年加入奇虎360的Web平台部，从事Atlas数据库中间件的设计与研发工作。2015年初加入了珠海金山WPS 云平台团队。新团队主要以Go作为开发语言，正是由于在新的工作中体会到了Go语言的开发效率很高，才萌发了写一个简单好用的Go版本的MySQL Proxy的念头。

 

sharding支持的操作

目前kingshard sharding支持insert, delete, select, update和replace语句, 所有这五类操作都支持跨子表。但写操作仅支持单node上的跨子表，select操作则可以跨node，跨子表。

 

sharding主要功能：

1.读写分离。

2.跨节点分表。

3.客户端IP访问控制。

4.平滑上线DB或下线DB，前端应用无感知。

 

 

kingshard is a high-performance proxy for MySQL powered by Go. Just like other mysql proxies, you can use it to split the read/write sqls. Now it supports basic SQL statements (select, insert, update, replace, delete). The most important feature is the sharding function. Kingshard aims to simplify the sharding solution of MySQL. The Performance of kingshard is about 80% compared to connecting to MySQL directly.

 

 

Feature

1. Basic Function

• Splits reads and writes
• Client's ip ACL control.
• Transaction in single node.
• Support limitting the max count of connections to MySQL database.
• Support setting the backend database online or offline dynamically.
• Supports prepared statement: COM_STMT_PREPARE, COM_STMT_EXECUTE, etc.
• Support multi slaves, and loading banlance between slaves.
• Support reading master database forcely.
• Support last_insert_id().
• Support MySQL backends HA.
• Support set the charset of proxy.
• Support SQL blacklist.
• Support dynamically changing the config value of kingshard.

2. Sharding Function

 

• Support hash,range and date sharding across multiple nodes.
• Support sending sql to the specified node.
• Support most commonly used functions, such as max, min, count, sum, and also support join, limit, order by,group by.

 

基于kingshard的子表迁移方案

通过kingshard可以非常方便地动态迁移子表，从而保证MySQL节点的不至于负载压力太大。大致步骤如下所述：

1)通过自动数据迁移工具开始数据迁移。

2)数据差异小于某一临界值，阻塞老子表写操作（read-only）

3)等待新子表数据同步完毕

4)更改kingshard配置文件中的对应子表的路由规则。

 

 

5)删除老节点上的子表。

 

 

Now more and more Internet companies still in heavy use MySQL to store various types of relational data. With the amount of data and traffic increasing, developers had to consider some new MySQL-related problems.

 

1)读写分离Read/Write Splitting. With the increasing traffic sent by the front-end applications, one instance of MySQL can not hold all the queries. At this time, we have to send the read queries to the slaves for load balance.

2)单表容量The capacity of one table in MySQL. If in the begining of system design, you have not considered the table sharding, that will make it difficult to keep your system high-performance.

3)MySQL的维护MySQL maintenance operation. Without proxy you should configure the master and slaves host in your source code. When you upgrade the MySQL server, the front-end applications have to make relevant regulation.

4)连接池Connection pool. The front-end applications send queries by creating a new connection with MySQL, and close the connection when they don't need to send queries any more. The extra performance cost of these operations can not be ignored. If a connection pool is added between the front-end applications and MySQL, and the front-end applications can pick a connection from the connection pool, it will enhance the performance of your system.

5)SQL日志SQL logs. When the program has problems, usually we want to get some SQL logs sent by the program. For example, We want to know which SQL was sent to which DB backend. By checking the log, it can help us locate the problem more quickly.

Faced with these problems, we can implement every function in the client code. But this also makes the client less flexible. I have been working on database development for years, and I believe we can use a MySQL proxy to solve the problems more effectively, which is why I created this project. In this document, I will show you how kingshard solve the above problems.

 

1)读写分离问题。由于前端应用访问量增加，单台MySQL不足以支撑整个系统的写入和查询操作。这时候，我们不得不将一些耗时的查询操作分散到多个slave上。

2)单表容量问题。如果在系统设计之初，没有考虑到分表问题。随着数据量的增长，单表容量越来越大。作者见过单表容量5亿条记录，然后一个简单的delete操作都会引起系统慢日志，而且有可能导致MySQL IO瞬发性的飙升。很多同学可能会想到，在查询的字段上加上索引，但当数据量增长到这么大的时候，即使加上索引效果也不明显了。归根结底，就是单表数据量太大，导致MySQL即使通过索引定位数据，仍然需要扫描很多记录。

3)数据库的运维问题。如果在代码中配置主库和从库host，系统运行当然也是没问题的。但这样大大增加了运维工作的压力，比如：MySQL数据库IO压力由于访问量的增加居高不下，DBA需要添加一台slave，这时候就不得不修改代码，然后打包并上线。还有很多非常实际的例子，在这就不一一列举。

4)连接池。前端应用频繁连接MySQL，由此给MySQL带来的额外性能消耗也是不容忽视的。如果通过增加一个连接池，每个DB缓存一定数量的MySQL连接，当有应用需要连接后端的MySQL，直接从连接池里取出一个已建好的连接来发送SQL请求，这样会大大加快数据查询速度。而且可以降低MySQL的性能消耗。

 

5)SQL日志。在程序出现问题时，我们希望得到一些SQL日志，比如，什么时刻哪条SQL发送到哪一台DB上了。通过查看这种日志能够帮助我们快速定位问题。