mysql系统架构
逻辑模块组成
mysql由两层架构组成
1.第一层sqllayer处理底层数据之前所有工作，包括权限判断，sql解析，执行计划优化
2.第二层就是存储引擎层，我们通常叫做Storage EngineLayer，也就是底层数据存取操作实现部分，由多种存储引擎共同组成
各模块工作配合
常用引擎介绍
MyISAM
MyISAM 存储引擎的表在数据库中，每一个表都被存放为三个以表名命名的物理文件。首先肯定会有任何存储引擎都不可缺少的存放表结构定义信息的.frm 文件，另外还有.MYD和.MYI 文件，分别存放了表的数据（.MYD）和索引数据（.MYI）。每个表都有且仅有这样三个文件做为 MyISAM 存储类型的表的存储，也就是说不管这个表有多少个索引，都是存放在同一个.MYI 文件中。
Innodb
在物理存储方面，Innodb 存储引擎也和 MyISAM 不太一样，虽然也有.frm 文件来存放表结构定义相关的元数据，但是表数据和索引数据是存放在一起的。
对比
索引
SELECT * FROM my_table WHERE col2 = '77' 可以从表“my_table”中获得“col2”为“77”的数据记录。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引。
索引类型分类
B+tree
使用平衡树实现索引，是mysql中使用最多的索引类型
；在innodb中，存在两种索引类型
《第一种是主键索引（primary key），索引内容中直接保存数据的地址》
《第二种是其他索引，在索引内容中保存的是指向主键索引的引用；》
所以在使用innodb的时候，要尽量的使用主键索引，速度非常快；
hash
把索引的值做hash运算，并存放到hash表中，使用较少，一般是memory引擎使用；因为使用hash表存储，按照常理，hash的性能比B-TREE效率高很多。
hash索引的缺点：
1，hash索引只能适用于精确的值比较，=，in，或者<>, 因为只需要经过一次算法即可找到相应的键值；
2，无法使用索引排序,因为原先是有序的键值，经过哈希算法后，有可能变成不连续的了，就没办法再利用索引完成范围查询检索；
3，组合hash索引无法使用部分索引,以及like ‘xxx%’ 这样的部分模糊查询（这种部分模糊查询，其实本质上也是范围查询）；
4，如果大量索引hash值相同，性能较低；
fulltext
全文检索索引，效率低，限制多
B-tree
针对空间数据索引，使用很少
mysql索引实现
1.MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。
2.虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。
第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道，MyISAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。
3聚集索引：
是指数据库表行中数据的物理顺序与键值的逻辑（索引）顺序相同。
聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据
非聚簇索引：将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应
索引的利弊
1，索引的好处：
1，提高表数据的检索效率；
2，如果排序的列是索引列，大大降低排序成本；
3，在分组操作中如果分组条件是索引列，也会提高效率；
2，索引的问题：索引需要额外的维护成本；
sql优化原则
1 优先优化高并发低消耗的SQL；
1，1小时请求1W次，1次10个IO；
2，1小时请求10次，1次1W个IO；
从IO消耗，优化难度，CPU消耗进行比较；
定位性能瓶颈；
1，SQL运行较慢有两个影响原因，IO和CPU，明确性能瓶颈所在；
2，明确优化目标；
2.用小结果集驱动大结果集，减少外层循环的数据量：
如果小结果集和大结果集连接的列都是索引列，mysql在内连接时也会选择用小结果集驱动大结果集，因为索引查询的成本是比较固定的，这时候外层的循环越少，join的速度便越快。
3在索引中完成排序
4.使用最小的cloumn量
5。使用最有效的过滤条件
数据库中间件
mycat
<met<meta charset="utf-8">
SpringBoot+Mycat+MySQL实现分表分库案例
关于分库分表，Mycat已经帮我们在内部实现了路由的功能，我们只需要在Mycat中配置以下切分规则即可，对于开发者来说，我们就可以把Mycat看做是一个数据库，接下来我们开始搭建环境:
步骤一:
Mycat是使用java写的数据库中间件，所以要运行Mycat前要准备要jdk的环境，要求是jdk1.7以上的环境。所以需要在系统中配置JAVA_HOME的环境变量.
步骤二:
从官网下载Mycat，http://dl.mycat.io/1.6-RELEASE/ 我们是基于CentOS7来搭建Mycat环境的，所以下载版本：
Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
步骤三:
将下载好的安装包上传到服务器上并解压.解压之后目录结构如下：
步骤四:
配置切分规则：
将如下配置复制粘贴覆盖mycat/conf/schema.xml的内容。
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
<table name="user" primaryKey="id" dataNode="dn01,dn02" rule="rule1" />
</schema>
<!-- 设置dataNode 对应的数据库,及 mycat 连接的地址dataHost -->
<dataNode name="dn01" dataHost="dh01" database="db01" />
<dataNode name="dn02" dataHost="dh01" database="db02" />
<!-- mycat 逻辑主机dataHost对应的物理主机.其中也设置对应的mysql登陆信息 -->
<dataHost name="dh01" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native">
<heartbeat>select user()</heartbeat>
<writeHost host="server1" url="127.0.0.1:3306" user="root" password="WolfCode_2017"/>
</dataHost>
</mycat:schema>
<schema>:表示的是在mycat中的逻辑库配置，逻辑库名称为:TESTDB
<table>:表示在mycat中的逻辑表配置，逻辑表名称为:user,映射到两个数据库节点dataNode中,切分规则为:rule1(在rule.xml配置)
<dataNode>:表示数据库节点,这个节点不一定是单节点，可以配置成读写分离.
<dataHost>:真实的数据库的地址配置
<heartbeat>:用户心跳检测
<writeHost>:写库的配置
将如下配置复制粘贴覆盖mycat/conf/rule.xml的内容。
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:rule SYSTEM "rule.dtd">
<mycat:rule xmlns:mycat="http://io.mycat/">
<tableRule name="rule1">
<rule>
<columns>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>
</mycat:rule>
这里定义的是切分规则，是按照id列进行切分，切分规则是采取取模的方式,
<property name="count">2</property>:这里配置了我们有拆分了多个库(表)，需要和前面配置
<table name="user" primaryKey="id" dataNode="dn01,dn02" rule="rule1" />
中的dataNode个数一致，否则会出错.
步骤五:
在数据库中创建两个数据库db01,db02.
每个库中执行如下建表语句:
CREATE TABLE `user` (
`id` bigint(20) NOT NULL,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
步骤六:
启动mycat,执行mycat/bin/startup_nowrap.sh
步骤七:
项目已经上传到github
https://github.com/javalanxiongwei/springboot-mycat
搭建SpringBoot环境，执行插入语句.
application.properties配置如下：
#配置数据源
spring.datasource.druid.driver-class-name=com.mysql.jdbc.Driver
#这里配置的是Mycat中server.xml中配置账号密码，不是数据库的密码。
spring.datasource.druid.username=root
spring.datasource.druid.password=123456
#mycat的逻辑库 端口也是mycat的
spring.datasource.druid.url=jdbc:mysql://192.168.142.129:8066/TESTDB
UserMapper.java代码如下：
@Mapper
public interface UserMapper {
@Insert("insert into user(id,name) value (#{id},#{name})")
int insert(User user);
@Select("select * from user")
List<User> selectAll();
}
UserController.java代码如下:
@RestController
@RequestMapping("/user")
public class UserController {
@Autowired
private UserMapper userMapper;
@RequestMapping("/save")
public String save(User user){
userMapper.insert(user);
return "保存成功";
}
@RequestMapping("/list")
public List<User> list(){
return userMapper.selectAll();
}
}
步骤八:
测试:
在地址栏输入:
http://localhost:8080/user/save?id=1&name=tom
http://localhost:8080/user/save?id=2&name=jack
查看数据库发现：
id为1的数据插入到数据库db02中的user表。
id为2的数据插入到数据库db01中的user表。
在地址栏输入:
http://localhost:8080/user/list
是可以看到刚刚插入的两条记录.
好到这一步我们就已经完成了分表分库了.
链接：//www.greatytc.com/p/f81422b1c915
来源：简书
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。
a charset="utf-8">
SpringBoot+Mycat+MySQL实现分表分库案例
关于分库分表，Mycat已经帮我们在内部实现了路由的功能，我们只需要在Mycat中配置以下切分规则即可，对于开发者来说，我们就可以把Mycat看做是一个数据库，接下来我们开始搭建环境:
步骤一:
Mycat是使用java写的数据库中间件，所以要运行Mycat前要准备要jdk的环境，要求是jdk1.7以上的环境。所以需要在系统中配置JAVA_HOME的环境变量.
步骤二:
从官网下载Mycat，http://dl.mycat.io/1.6-RELEASE/ 我们是基于CentOS7来搭建Mycat环境的，所以下载版本：
Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
步骤三:
将下载好的安装包上传到服务器上并解压.解压之后目录结构如下：
步骤四:
配置切分规则：
将如下配置复制粘贴覆盖mycat/conf/schema.xml的内容。
<?xml version="1.0"?>
<!DOCTYPE mycat:schema SYSTEM "schema.dtd">
<mycat:schema xmlns:mycat="http://io.mycat/">
<schema name="TESTDB" checkSQLschema="false" sqlMaxLimit="100">
<table name="user" primaryKey="id" dataNode="dn01,dn02" rule="rule1" />
</schema>
<!-- 设置dataNode 对应的数据库,及 mycat 连接的地址dataHost -->
<dataNode name="dn01" dataHost="dh01" database="db01" />
<dataNode name="dn02" dataHost="dh01" database="db02" />
<!-- mycat 逻辑主机dataHost对应的物理主机.其中也设置对应的mysql登陆信息 -->
<dataHost name="dh01" maxCon="1000" minCon="10" balance="0" writeType="0" dbType="mysql" dbDriver="native">
<heartbeat>select user()</heartbeat>
<writeHost host="server1" url="127.0.0.1:3306" user="root" password="WolfCode_2017"/>
</dataHost>
</mycat:schema>
<schema>:表示的是在mycat中的逻辑库配置，逻辑库名称为:TESTDB
<table>:表示在mycat中的逻辑表配置，逻辑表名称为:user,映射到两个数据库节点dataNode中,切分规则为:rule1(在rule.xml配置)
<dataNode>:表示数据库节点,这个节点不一定是单节点，可以配置成读写分离.
<dataHost>:真实的数据库的地址配置
<heartbeat>:用户心跳检测
<writeHost>:写库的配置
将如下配置复制粘贴覆盖mycat/conf/rule.xml的内容。
<?xml version="1.0" encoding="UTF-8"?>
<!DOCTYPE mycat:rule SYSTEM "rule.dtd">
<mycat:rule xmlns:mycat="http://io.mycat/">
<tableRule name="rule1">
<rule>
<columns>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>
</mycat:rule>
这里定义的是切分规则，是按照id列进行切分，切分规则是采取取模的方式,
<property name="count">2</property>:这里配置了我们有拆分了多个库(表)，需要和前面配置
<table name="user" primaryKey="id" dataNode="dn01,dn02" rule="rule1" />
中的dataNode个数一致，否则会出错.
步骤五:
在数据库中创建两个数据库db01,db02.
每个库中执行如下建表语句:
CREATE TABLE `user` (
`id` bigint(20) NOT NULL,
`name` varchar(255) DEFAULT NULL,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;
步骤六:
启动mycat,执行mycat/bin/startup_nowrap.sh
步骤七:
项目已经上传到github
https://github.com/javalanxiongwei/springboot-mycat
搭建SpringBoot环境，执行插入语句.
application.properties配置如下：
#配置数据源
spring.datasource.druid.driver-class-name=com.mysql.jdbc.Driver
#这里配置的是Mycat中server.xml中配置账号密码，不是数据库的密码。
spring.datasource.druid.username=root
spring.datasource.druid.password=123456
#mycat的逻辑库 端口也是mycat的
spring.datasource.druid.url=jdbc:mysql://192.168.142.129:8066/TESTDB
UserMapper.java代码如下：
@Mapper
public interface UserMapper {
@Insert("insert into user(id,name) value (#{id},#{name})")
int insert(User user);
@Select("select * from user")
List<User> selectAll();
}
UserController.java代码如下:
@RestController
@RequestMapping("/user")
public class UserController {
@Autowired
private UserMapper userMapper;
@RequestMapping("/save")
public String save(User user){
userMapper.insert(user);
return "保存成功";
}
@RequestMapping("/list")
public List<User> list(){
return userMapper.selectAll();
}
}
步骤八:
测试:
在地址栏输入:
http://localhost:8080/user/save?id=1&name=tom
http://localhost:8080/user/save?id=2&name=jack
查看数据库发现：
id为1的数据插入到数据库db02中的user表。
id为2的数据插入到数据库db01中的user表。
在地址栏输入:
http://localhost:8080/user/list
是可以看到刚刚插入的两条记录.
好到这一步我们就已经完成了分表分库了.
链接：//www.greatytc.com/p/f81422b1c915
来源：简书
著作权归作者所有。商业转载请联系作者获得授权，非商业转载请注明出处。
