0%

MySQL-2

MySQL高级.

两种Mysql引擎的对比:

对比项 MyISAM InnoDB
主外键 不支持 支持
事务 不支持 支持
行表锁 表锁 行锁
缓存 只缓存索引,不缓存真实数据 二者都缓存,对内存要求高
表空间
关注点 性能 事务
默认安装
1
2
3
4
5
6
7
8
9
10
11
12
13
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine | Support | Comment | Transactions | XA | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB | DEFAULT | Supports transactions, row-level locking, and foreign keys | YES | YES | YES |
| MRG_MYISAM | YES | Collection of identical MyISAM tables | NO | NO | NO |
| MEMORY | YES | Hash based, stored in memory, useful for temporary tables | NO | NO | NO |
| BLACKHOLE | YES | /dev/null storage engine (anything you write to it disappears) | NO | NO | NO |
| MyISAM | YES | MyISAM storage engine | NO | NO | NO |
| CSV | YES | CSV storage engine | NO | NO | NO |
| ARCHIVE | YES | Archive storage engine | NO | NO | NO |
| PERFORMANCE_SCHEMA | YES | Performance Schema | NO | NO | NO |
| FEDERATED | NO | Federated MySQL storage engine | NULL | NULL | NULL |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

查询SQL慢的原因:

  • SQL语句写的烂
  • 索引没做好或者没生效
  • 过多的join
  • 服务器本身问题
1
执行顺序:from,on,where,group by,having,select distincat,order by,limit.

索引

索引是为了更快的查找数据,底层大多数是B+树实现.但是索引会降低更新数据的速度,因为不仅要更改数据表,还要调整更新带来的键值变化后的索引信息.此外,索引也是一张表,保存了主键与索引字段信息,指向实体表记录.

聚簇索引和非聚簇索引:前者是id这样的主键索引,紧挨着排序.后者一般是自定义的索引,实际的数据并不是挨着的.以字典为例,是按照拼音排的,这种拼音索引就是聚簇索引,而笔画索引就不是,因为笔画一样的字并不挨在一起.

查看索引:show index from 表

新建索引:create index idx_字段名 on 表名(字段);这是一种单值索引.除了这样单独写一个索引,创建表时也可以创建索引.单值索引只包含单个列,一个表可以有多个单值suoy.唯一索引:索引值必须唯一,但是允许有空值.主键索引:设置主键后会自动创建.复合索引:针对多个索引创建索引.

频繁查询的字段应该建索引;关联的外键应该建一个索引.

多个单值索引和复合索引应该优先使用后者,因为MySQL只会在多个单值索引中找一个最优的.

要排序的字段应该建索引.

查询中统计或者分组的字段要建索引.

对于是否要索引,有这样一把”尺子”来衡量:explain.用法是直接加载SQL语句之前:

1
2
3
4
5
6
7
8
EXPLAIN SELECT
d.*,
e.employee_id
FROM
departments d
LEFT JOIN employees e ON d.department_id = e.department_id
WHERE
e.employee_id IS NULL;

结果为:

1
2
3
id	select_type	table	partitions	type	possible_keys	key			key_len	ref							rows	filtered	Extra
1 SIMPLE d ALL 27 100.00
1 SIMPLE e ref dept_id_fk dept_id_fk 5 myemployees.d.department_id 8 10.00 Using where; Not exists; Using index

各字段意义:

id:这里是执行顺序,当id相同,则顺序是从上到下:当id不同(子查询),从id大的开始执行;既有一样的,又有不一样的,先按不一样的从大到小,再从上到下.一个id代表一个独立的查询,一句SQL的查询数越少越好.

select_type:

  • SIMPLE:简单的select查询,不包含子查询或者union.
  • PRIMARY:查询中包含复杂的子查询,最外层的查询就是PRIMARY的.
  • DERIVED:在FROM列表中包含的子查询(衍生),Mysql会递归执行它们,把结果放在临时表.
  • SUBQUERY:在select或where中包含的子查询
  • DEPENDENT SUBQUERY:在select或where中包含的子查询,子查询位于外层.上面是等号,这里是in.
  • UNCACHEABLE SUBQUERY:不可缓存的子查询,查系统变量时,由于MySQL认为系统变量有可能被人更改不能缓存,所以这种查询放在子查询里就是不可缓存的子查询,select @@lower_case_table_name from DUAL;(DUAL是虚表,因为环境变量不存在表中)
  • UNION:若第二个select出现在UNION后则被标记为UNION
  • UNION RESULT:从UNION中获取结果的select

type:显示查询使用了何种类型.简要介绍六种:它们分别是all,index,range,ref,eq_ref,const。从左到右,它们的效率依次是增强的。撇开sql的具体应用环境以及其他因素,你应当尽量优化你的sql语句,使它的type尽量靠右,但实际运用中还是要综合考虑各个方面的。

all
这便是所谓的“全表扫描”,如果是展示一个数据表中的全部数据项,倒是觉得也没什么,如果是在一个查找数据项的sql中出现了all类型,那通常意味着你的sql语句处于一种最原生的状态,有很大的优化空间。

为什么这么说呢?因为all是一种非常暴力和原始的查找方法,非常的耗时而且低效。用all去查找数据就好比这样的一个情形:S学校有俩万人,我告诉你你给我找到小明,然后你怎么做呢!你当然是把全校俩万人挨个找一遍,即使你很幸运第一个人便找到了小明,但是你仍然不能停下,因为你无法确认是否有另外一个小明存在,直到你把俩万人找完为止。所以,基本所有情况,我们都要避免这样类型的查找,除非你不得不这样做。
以employee表为例,下面一种情形便是all类型的查找:

1
2
3
4
5
6
mysql> explain select * from employee where `no` = '20150001';
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+
| 1 | SIMPLE | employee | ALL | NULL | NULL | NULL | NULL | 5 | Using where |
+----+-------------+----------+------+---------------+------+---------+------+------+-------------+

这是因为no列既不是主键也不是索引,因此只能采用全表扫描来查找目标no。

index

遍历索引树查询.这种连接类型只是另外一种形式的全表扫描,只不过它的扫描顺序是按照索引的顺序。这种扫描根据索引然后回表取数据,和all相比,他们都是取得了全表的数据.

range

range指的是有范围的索引扫描,相对于index的全索引扫描,它有范围限制,因此要优于index。关于range比较容易理解,需要记住的是出现了range,则一定是基于索引的。同时除了显而易见的between,and以及’>’,’<’外,in和or也是索引范围扫描。

ref

出现该连接类型的条件是: 查找条件列使用了索引而且不为主键和unique。其实,意思就是虽然使用了索引,但该索引列的值并不唯一,有重复。这样即使使用索引快速查找到了第一条数据,仍然不能停止,要进行目标值附近的小范围扫描。但它的好处是它并不需要扫全表,因为索引是有序的,即便有重复值,也是在一个非常小的范围内扫描。下面为了演示这种情形,给employee表中的name列添加一个普通的key(值允许重复)

ref_eq

ref_eq 与 ref相比牛的地方是,它知道这种类型的查找结果集只有一个?什么情况下结果集只有一个呢!那便是使用了主键或者唯一性索引进行查找的情况,比如根据学号查找某一学校的一名同学,在没有查找前我们就知道结果一定只有一个,所以当我们首次查找到这个学号,便立即停止了查询。这种连接类型每次都进行着精确查询,无需过多的扫描,因此查找效率更高,当然列的唯一性是需要根据实际情况决定的。

const

通常情况下,如果将一个主键放置到where后面作为条件查询,mysql优化器就能把这次查询优化转化为一个常量。至于如何转化以及何时转化,这个取决于优化器。

possible_keys:可能用到的索引

key:实际使用的索引

key_len:索引长度(复合索引时,该值越大越好)

rows:实际扫描的行数

extra:

  • NULL: 没有用到额外的附加条件
  • Using filesort:查询语句中包含了oder by语句,索引无法完成排序,数据量小的时候在内存中完成排序,否则在磁盘中完成排序。
  • Using temporary:使用到了临时表。
  • Using Index:使用覆盖索引进行数据返回(Innodb引擎才存在),不会进行回表查找。
  • Using Where:查询语句中存在where范围查找,会回表查找数据。
  • Using Where Using Index:覆盖索引中存在范围查找,但是也不会进行回表,直接在索引上就能拿到数据。
  • Using Index Condition:跟Using Where Using Index有一些差别,它的查找用到了联合索引,查找的数据有不在索引中的字段,所以会进行回表查找数据。

单表索引优化

create index idx_age on emp(age),为单表中某字段建索引,select * from emp where emp.age=30,性能显著提高.

如果查询时where后面有两个字段条件,可以为这两个字段建个索引,create index idx_age_deptid on emp(age,deptid)

对于多个字段构成的复合索引,它的结构类似于技能树,age字段按照值的大小构成一棵树,每个值展开后又是一颗deptid的树.所以,最佳的索引命中策略是按照建索引时的顺序命中,如果没有按照顺序来,索引的性能也会变差.

在写SQL时用函数,计算,类型转换等操作,索引会失效.

对于范围查询字段,需要把该字段放在最后,如果放在前面,则后面的索引字段失效.

此外,is not null也会导致索引失效.

使用like做模糊查询时,例如查名字中间包含字母a这种的,首字母不确定也会导致索引失效.因为字符的索引是按照首字母顺序排的.

当查询名字时条件是123这样的数字,由于类型不一致,有类型转换,索引失效.

一般性建议:

  • 对于单键索引,尽量选择过滤性更好的字段
  • 对于组合索引,尽量将过滤性好的字段放前面
  • 选择组合索引时应该尽量选择包含当前query中where后更多字段的索引
  • 若某个字段有范围查询应该放在最后

其它查询优化

如果两个表关联查询,第一个表叫驱动表,第二个表叫被驱动表.第一个无论如何都会是全表扫描的.所以对第二个表的关键字建立索引是有效的.即在被驱动表建立索引是有效的.此外,如果采用inner join,将由MySQL决定哪个是被驱动表.决定依据就是那个表有索引,有索引的哪个会被当做被驱动表.

此外,子查询尽量不要放到被驱动表.因为子查询的虚拟表没法建索引优化.多表之间能直接关联的尽量直接关联,不用子查询.

关于Order by有几句口诀:无过滤,不索引(分组后没有过滤条件就不会使用索引),顺序错,必排序(order by后面的字段顺序和索引中顺序不一致),方向反,必排序(要么都是升序,要么都是降序).

而Group by与Order by类似,只是不遵循第一条原则,没有过滤也能用索引.

还有一种覆盖索引的手段:select到from之间的字段<=索引字段+主键.

查询截取分析

慢查询日志

set global slow_query_log=1;开启慢查询日志,set global long_query_time=0.1;设置超过0.1的sql语句视为慢查询.

杀进程

show processlist显示查询进程.

主从复制

主从复制(也称 AB 复制)允许将来自一个MySQL数据库服务器(主服务器)的数据复制到一个或多个MySQL数据库服务器(从服务器)。

复制是异步的 从站不需要永久连接以接收来自主站的更新。

根据配置,您可以复制数据库中的所有数据库,所选数据库甚至选定的表。

MySQL中复制的优点包括:

  • 横向扩展解决方案 - 在多个从站之间分配负载以提高性能。在此环境中,所有写入和更新都必须在主服务器上进行。但是,读取可以在一个或多个从设备上进行。该模型可以提高写入性能(因为主设备专用于更新),同时显着提高了越来越多的从设备的读取速度。
  • 数据安全性 - 因为数据被复制到从站,并且从站可以暂停复制过程,所以可以在从站上运行备份服务而不会破坏相应的主数据。
  • 分析 - 可以在主服务器上创建实时数据,而信息分析可以在从服务器上进行,而不会影响主服务器的性能。
  • 远程数据分发 - 您可以使用复制为远程站点创建数据的本地副本,而无需永久访问主服务器。

原理

原理

前提是作为主服务器角色的数据库服务器必须开启二进制日志

主服务器上面的任何修改都会通过自己的 I/O tread(I/O 线程)保存在二进制日志 Binary log 里面。

从服务器上面也启动一个 I/O thread,通过配置好的用户名和密码, 连接到主服务器上面请求读取二进制日志,然后把读取到的二进制日志写到本地的一个Realy log(中继日志)里面。

从服务器上面同时开启一个 SQL thread 定时检查 Realy log(这个文件也是二进制的),如果发现有更新立即把更新的内容在本机的数据库上面执行一遍。

每个从服务器都会收到主服务器二进制日志的全部内容的副本。

从服务器设备负责决定应该执行二进制日志中的哪些语句。

除非另行指定,否则主从二进制日志中的所有事件都在从站上执行。

如果需要,您可以将从服务器配置为仅处理一些特定数据库或表的事件。

每个从站(从服务器)都会记录二进制日志坐标:

  • 文件名
  • 文件中它已经从主站读取和处理的位置。

由于每个从服务器都分别记录了自己当前处理二进制日志中的位置,因此可以断开从服务器的连接,重新连接然后恢复继续处理。

一主多从

如果一主多从的话,这时主库既要负责写又要负责为几个从库提供二进制日志。此时可以稍做调整,将二进制日志只给某一从,这一从再开启二进制日志并将自己的二进制日志再发给其它从。或者是干脆这个从不记录只负责将二进制日志转发给其它从,这样架构起来性能可能要好得多,而且数据之间的延时应该也稍微要好一些。工作原理图如下:
一主多从

配置

配置步骤:

  1. 在主服务器上,您必须启用二进制日志记录并配置唯一的服务器ID。需要重启服务器。
    编辑主服务器的配置文件my.cnf,添加如下内容:
1
2
3
[mysqld]
log-bin=/var/log/mysql/mysql-bin
server-id=1

Windows上与之类似.server-id用来指定唯一id,log-bin指定位置和名字,binlog-do-db指定需要复制的数据库.(该数据库要等到主从复制完成后搭建).binlog_format指定格式:statement(默认),row,mixed.

Linux上还需要创建日志目录并赋予权限

1
2
shell> mkdir /var/log/mysql
shell> chown mysql.mysql /var/log/mysql

然后重启服务.

注意

如果省略server-id(或将其显式设置为默认值0),则主服务器拒绝来自从服务器的任何连接。

为了在使用带事务的InnoDB进行复制设置时尽可能提高持久性和一致性,
应该在master my.cnf文件中使用以下配置项:

1
2
innodb_flush_log_at_trx_commit = 1
sync_binlog = 1

确保在主服务器上skip_networking选项处于OFF关闭状态, 这是默认值。
如果是启用的,则从站无法与主站通信,并且复制失败。

1
2
3
4
5
6
7
mysql> show variables like '%skip_networking%';
+-----------------+-------+
| Variable_name | Value |
+-----------------+-------+
| skip_networking | OFF |
+-----------------+-------+
1 row in set (0.00 sec)
  1. 应该创建一个专门用于复制数据的用户.每个从服务器需要使用MySQL主服务器上的用户名和密码连接到主站。
1
GRANT REPLICATION SLAVE ON *.* TO 'zhangsan'@'从机IP' IDENTIFIED BY '123456'

.表示所有库所有表,从机IP可以用%,表示所有远程用户.

  1. 从机配置.在[mysqld]下添加server-id,要和主机不一样.
1
2
server-id=2
relay-log=mysql-relay

然后重启服务.

接下来用show master status查看主机状态.注意看Position(接入点)和mysqlbin.后面的数字.此时主机就不要再变动了.

  1. 从机连接主机
1
2
3
4
CHANGE MASTER TO MASTER_HOST='主机IP',
MASTER_USER='zhangsan',
MASTER_PASSWORD='123456',
MASTER_LOG_FILR='mysqlbin.具体数字',MASTER_LOG_POS=具体值;

在从机的MySQL中执行上述语句.

  1. 启动

在从机中启动

1
start slave;

show slave status\G;查看从机的执行状态.

如果从机上一步配置出现了问题可以使用stop slavereset master重新配置.

Mycat

Mycat是数据库中间件.一端是Java程序,另一端是mycat.mycat可以用来做读写分离,数据分表(分库,分表),多数据源整合.

首先下载压缩吧,然后解压即可.然后修改配置文件:

  • schema.xml,定义逻辑库(即Java程序认为的库,实际的库还是另一头的数据库),表,分片节点等内容
  • rule.xml,定义分片规则
  • server.xml,定义用户及系统相关变量,如端口

先设置server.xml:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<user name="mycat" defaultAccount="true">//用户名
<property name="password">123456</property>//密码
<property name="schemas">TESTDB</property>//这里选一个schema.xml中的schema
<property name="defaultSchema">TESTDB</property>
<!--No MyCAT Database selected 错误前会尝试使用该schema作为schema,不设置则为null,报错 -->

<!-- 表级 DML 权限设置 -->
<!--
<privileges check="false">
<schema name="TESTDB" dml="0110" >
<table name="tb01" dml="0000"></table>
<table name="tb02" dml="1111"></table>
</schema>
</privileges>
-->
</user>

接着配置schema.xml:

  • schema标签
    <schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100"></schema>schema 标签用于定义 MyCat 实例中的逻辑库,MyCat 可以有多个逻辑库,每个逻辑库都有自己的相关配置。可以使用 schema 标签来划分这些不同的逻辑库。

  • table标签

<table name="travelrecord" dataNode="dn1,dn2,dn3" rule="auto-sharding-long" ></table>

Table 标签定义了 MyCat 中的逻辑表,所有需要拆分的表都需要在这个标签中定义。

table 标签的相关属性:

table

  • childTable标签
1
2
3
<table name="customer" primaryKey="ID" dataNode="dn1,dn2" rule="sharding-by-intfile"> 
<childTable name="c_a" primaryKey="ID" joinKey="customer_id" parentKey="id" />
</table>

childTable 标签用于定义 E-R 分片的子表。通过标签上的属性与父表进行关联。

childtable

  • dataNode标签

dataNode 标签定义了 MyCat 中的数据节点,也就是我们通常说所的数据分片。一个 dataNode 标签就是一个独立的数据分片。

<dataNode name="dNode1" dataHost="dHost128" database="db1" ></dataNode>

示例中所表述的意思为:使用名字为 dHost128数据库实例上的 db1 物理数据库,这就组成一个数据分片,最后,我们使用名字 dNode1标识这个分片。
dataNode 标签的相关属性:

dataNode

  • dataHost标签

该标签定义了具体的数据库实例、读写分离配置和心跳语句。

1
2
3
4
5
<dataHost name="localhost1" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native">
<heartbeat>select user()</heartbeat>
<writeHost host="hostM1" url="localhost:3306" user="root" password="123456">
</writeHost>
</dataHost>

dataHost 标签的相关属性:

dataHost

里面有一个非常重要的属性:balance,设置负载均衡类型.

  • writeHost和readHost标签

这两个标签都指定后端数据库的相关配置,用于实例化后端连接池。唯一不同的是,writeHost 指定写实例、readHost 指定读实例。

属性说明:

writeread

下面是一个最简单的schema配置:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<!--逻辑库 name名称, checkSQLschema sqlMaxLimit 末尾是否要加 limit xxx-->
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1"> </schema>
<!--逻辑库 name名称, dataHost 引用的哪个dataHost database:对应mysql的database-->
<dataNode name="dn1" dataHost="localhost1" database="db1" />
<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="localhost:3306" user="root"
password="123456">
</writeHost>
</dataHost>
</mycat:schema>

接着是rule.xml配置

rule.xml 里面就定义了我们对表进行拆分所涉及到的规则定义。

1
2
3
4
5
6
7
8
9
10
11
<tableRule name="date">
<rule>
<columns>indate</columns>
<algorithm>sharding-by-date</algorithm>
</rule>
</tableRule>
<function name="sharding-by-date" class="io.mycat.route.function.PartitionByDate">
<property name="dateFormat">yyyy-MM-dd</property>
<property name="sBeginDate">2019-01-01</property>
<property name="sPartionDay">30</property>
</function>

tableRule标签

属性 name指定唯一的名字,用于标识不同的表规则。

内嵌的 rule 标签则指定对物理表中的哪一列进行拆分和使用什么路由算法。

rule

function标签

name属性:指定算法名字

class属性:制定路由算法的类名

property标签:具体算法所需要用到的一些属性

配置完成后,使用mysql登录命令分别登录两台机器测试连接情况.

接着启动:控制台启动:在mycat/bin目录下mycat console,后台启动:在该目录mycat start

启动后直接使用MySQL的登录命令登录,账户和密码是server.xml中配置的,另外端口号有所区别.9066是mycat的管理端口,8066是数据使用的窗口.

进入后show databases;发现会只有一个逻辑库.

读写分离

在schema.xml中设置:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">

<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
</schema>
<dataNode name="dn1" dataHost="host1" database="db1" />
<dataHost name="host1" maxCon="1000" minCon="10" balance="3"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostm1" url="192.168.67.1:3306" user="root"
password="123123">
<!--读库(从库)的配置 -->
<readHost host="hosts1" url="192.168.67.131:3306" user="root"
password="123123">
</readHost>
</writeHost>
</dataHost>
</mycat:schema>

现在只有一个dataNode,名字为dn1.修改balance.

具体架构如下:
结构

分库

分库有水平分库和垂直分库两类,下面演示的是垂直分库.

重要原则:要分库的表不能和另外的库里的表有关联.因为在不同机器上的不同库中的表不能join关联.

设置分库:将库中的某一张表分离到另一个库中.

具体配置如下:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
<mycat:schema xmlns:mycat="http://io.mycat/">

<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
<table name="customer" dataNode="dn2" ></table>
</schema>
<dataNode name="dn1" dataHost="host1" database="test_mc" />
<dataNode name="dn2" dataHost="host2" database="test_sm" />
<dataHost name="host1" maxCon="1000" minCon="10" balance="3"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostm1" url="192.168.67.1:3306" user="root"
password="123123">
<readHost host="hosts1" url="192.168.67.131:3306" user="root"
password="123123">
</readHost>
</writeHost>
</dataHost>
<dataHost name="host2" maxCon="1000" minCon="10" balance="0"
writeType="0" dbType="mysql" dbDriver="native" switchType="1" slaveThreshold="100">
<heartbeat>select user()</heartbeat>
<writeHost host="hostm2" url="192.168.67.1:3306" user="root"
password="123123">
</writeHost>
</dataHost>

</mycat:schema>

总体逻辑是:将customer表放到另一个库中,即dataNode名字为dn2的地方,该数据节点信息放在host2中.并且dn1节点设置读写分离.

分库

图中没画dn1的readHost

当配置完成后再去创建相关的库.此外,建表的操作要在mycat中进行,mycat会拦截建表操作,发现建的是customer表就会在dn2(test_sm)中创建(假定为Linux),其他表就在dn1(test_mc)中创建.如果绕过mycat直接去查看两个数据库,则会在test_sm中看到只有customer表.所谓的分库是对于逻辑库来说,一个逻辑上的库的表放在物理上的不同库中

而水平分库则是将一张大表拆分到两个数据库中.

分表

分表也分为水平分表和垂直分表.

对于order订单表进行分表操作,首先在schema.xml的schema标签的table子标签添加这张表,并且dataNode写明表被分到哪去.

1
2
3
4
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100" dataNode="dn1">
<table name="customer" dataNode="dn2" ></table>
<table name="orders" dataNode="dn1,dn2" rule="mod_rule" ></table>
</schema>

注意还要指定规则.根据customer_id来分,具体是用customer_id对节点数取模,将这张表里的数据放在几个节点内.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
 
<tableRule name="mod_rule">
<rule>//根据customer_id分片
<columns>customer_id</columns>
//分片算法为取模
<algorithm>mod-long</algorithm>
</rule>
</tableRule>


<function name="mod-long" class="io.mycat.route.function.PartitionByMod">
<!-- how many data nodes -->
<property name="count">2</property>
</function>

配置完成后在dn1和dn2中新建表(直接在物理库中,不是mycat中).

插入数据则在mycat中进行.

上面的分表严格来说叫水平分库分表,因为表的数据被拆分后放在不同的数据库中.

现在有一张新表,订单详情表,该表含有order表的id作为外键.订单详情表作为订单表的”小弟”.

如果在dn2中新建这张表并且插入数据,然后查询时join这张表和订单表,结果是出现错误.因为mycat拦截查询后,在dn2中有一部分订单表和全部订单详情表,可以正常查到,而在dn1中只有另一部分订单表,查不到,报错,因此mycat就会返回错误信息.

所有订单详情表也要分表,而且这个表和订单表关系紧密,所以要用childtable标签把该表作为订单表的子表.

1
2
3
<table name="orders" dataNode="dn1,dn2"  rule="mod_rule" >
<childTable name="orders_detail" primaryKey="id" joinKey="order_id" parentKey="id" />
</table>

primaryKey是该表主键,joinKey是关联字段,parentKey是父表主键.

然后在dn1中建另一个订单详情表即可.这种有关联的分表叫做ER表.

还有一种全局表.比如订单状态字典表.该表数据量不大,但是每个数据节点都要有一个,不然就会某个节点的订单表无法查询订单状态.设定为全局的表,会直接复制给每个数据库一份,所有写操作也会同步给多个库。所以全局表一般不能是大数据表或者更新频繁的表.

1
<table name="dict_order_type" dataNode="dn1,dn2" type="global" ></table>

配置一个global类型即可.

还有一种垂直分表,一般在数据库设计之初就进行,把一些字段分为两部分,不放在一个表里.

总结:分库就是把数据拆分到多个数据库,分表就是把一个表拆为多个表;垂直拆分就是拆开表里的字段分开放或者拆开库里的表分开放,水平拆分就是拆分表里的数据.

全局序列

id这种应该自增,但是分表后不能自增的字段,需要全局序列.这个序列生成有多种方式,其中本地文件和时间戳方式不推荐.可以使用数据库方式:

利用数据库一个表 来进行计数累加。但是并不是每次生成序列都读写数据库,这样效率太低

mycat会预加载一部分号段到mycat的内存中,这样大部分读写序列都是在内存中完成的。
如果内存中的号段用完了 mycat会再向数据库要一次。

问:那如果mycat崩溃了 ,那内存中的序列岂不是都没了?

是的。如果是这样,那么mycat启动后会向数据库申请新的号段,原有号段会弃用。
也就是说如果mycat重启,那么损失是当前的号段没用完的号码,但是不会因此出现主键重复。

先在某个数据节点,如db2中建表:

1
2
3
4
5
6
CREATE TABLE MYCAT_SEQUENCE (
NAME VARCHAR(50) NOT NULL,
current_value INT NOT NULL,
increment INT NOT NULL DEFAULT 100,
PRIMARY KEY(NAME)
) ENGINE = INNODB;

然后执行官方提供的三个函数:

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
DELIMITER $$
CREATE FUNCTION mycat_seq_currval(seq_name VARCHAR(50)) RETURNS VARCHAR(64) DETERMINISTIC BEGIN
DECLARE retval VARCHAR(64);
SET retval = "-999999999,null";
SELECT CONCAT(
CAST(current_value AS CHAR),
",",
CAST(increment AS CHAR)
) INTO retval
FROM MYCAT_SEQUENCE
WHERE NAME = seq_name;
RETURN retval;
END $$ DELIMITER;

DELIMITER $$
CREATE FUNCTION mycat_seq_setval(seq_name VARCHAR(50), VALUE INTEGER) RETURNS VARCHAR(64) DETERMINISTIC BEGIN
UPDATE MYCAT_SEQUENCE
SET current_value = VALUE
WHERE NAME = seq_name;
RETURN mycat_seq_currval(seq_name);
END $$ DELIMITER;

DELIMITER $$
CREATE FUNCTION mycat_seq_nextval(seq_name VARCHAR(50)) RETURNS VARCHAR(64) DETERMINISTIC BEGIN
UPDATE MYCAT_SEQUENCE
SET current_value = current_value + increment
WHERE NAME = seq_name;
RETURN mycat_seq_currval(seq_name);
END $$ DELIMITER;

上述操作完成后执行INSERT INTO MYCAT_SEQUENCE(NAME,current_value,increment) VALUES ('ORDERS',400000,100);,三个值依次代表全局序列名字,起始值,每次给mycat多少.

然后配置mycat,首先配置sequence_db_conf.properties

序列

Orders=dn1表示这个全局序列在哪个数据节点.

接着在server.xml中告诉mycat选用的是数据库方式.数字1单表数据库方式.

方式

接着重启即可.