[心得体会]mysql复习

1. 进入企业需要注意的事情

(1) 查看测试服和本地的mysql版本是否一致(2) 确认sql_mode是否和线上版本一致

show VARIABLES LIKE 'sql_mode';

(3) mysql sql_mode 常用设置详解:

ONLY_FULL_GROUP_BY：

对于GROUP BY聚合操作，如果在SELECT中的列，没有在GROUP BY中出现，那么这个SQL是不合法的，因为列不在GROUP BY从句中

NO_AUTO_VALUE_ON_ZERO：

该值影响自增长列的插入。默认设置下，插入0或NULL代表生成下一个自增长值。如果用户 希望插入的值为0，而该列又是自增长的，那么这个选项就有用了。

STRICT_TRANS_TABLES：

在该模式下，如果一个值不能插入到一个事务表中，则中断当前的操作，对非事务表不做限制

NO_ZERO_IN_DATE：

在严格模式下，不允许日期和月份为零

NO_ZERO_DATE：

设置该值，mysql数据库不允许插入零日期，插入零日期会抛出错误而不是警告。

ERROR_FOR_DIVISION_BY_ZERO：

在INSERT或UPDATE过程中，如果数据被零除，则产生错误而非警告。如 果未给出该模式，那么数据被零除时MySQL返回NULL

NO_AUTO_CREATE_USER：

禁止GRANT创建密码为空的用户

NO_ENGINE_SUBSTITUTION：

如果需要的存储引擎被禁用或未编译，那么抛出错误。不设置此值时，用默认的存储引擎替代，并抛出一个异常

PIPES_AS_CONCAT：

将"||"视为字符串的连接操作符而非或运算符，这和Oracle数据库是一样的，也和字符串的拼接函数Concat相类似

ANSI_QUOTES：

启用ANSI_QUOTES后，不能用双引号来引用字符串，因为它被解释为识别符

ORACLE：

设置等同：PIPES_AS_CONCAT, ANSI_QUOTES, IGNORE_SPACE, NO_KEY_OPTIONS, NO_TABLE_OPTIONS, NO_FIELD_OPTIONS, NO_AUTO_CREATE_USER.

2.GROUP BY需要注意

group by使用原则：select 后面只能放 函数 和group by后的字段

例如:

SELECT NAME,dept,MAX(age) FROM mytbl2 GROUP BY dept;

上面这句话是错误的, 下面这这样才是正确的查询方法

SELECT * FROM mytbl2 m INNER JOIN(
SELECT dept,MAX(age)maxage FROM mytbl2 GROUP BY dept
)ab ON ab.dept=m.dept AND m.age=ab.maxage

3. mysql的逻辑架构详解

1. 主题结构详解

和其它数据库相比，MySQL有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎的架构上，

插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。

(1).连接层

最上层是一些客户端和连接服务，包含本地sock通信和大多数基于客户端/服务端工具实现的类似于tcp/ip的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。

(2).服务层

2.1 Management Serveices & Utilities： 系统管理和控制工具2.2 SQL Interface: SQL接口 接受用户的SQL命令，并且返回用户需要查询的结果。比如select from就是调用SQL Interface2.3 Parser: 解析器 SQL命令传递到解析器的时候会被解析器验证和解析2.4 Optimizer: 查询优化器 SQL语句在查询之前会使用查询优化器对查询进行优化。 用一个例子就可以理解：

select uid,name from user where gender= 1;

优化器来决定先投影还是先过滤。

2.5 Cache和Buffer： 查询缓存。 如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。 这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等

(3).引擎层

存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。后面介绍MyISAM和InnoDB

(4).存储层

数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。

2. 利用show profile 查看sql的执行周期

修改配置文件/etc/my.cnf新增一行：query_cache_type=1重启mysql

先开启 show variables like '%profiling%';set profiling=1;

select * from xxx ;

show profiles; #显示最近的几次查询

show profile cpu,block io for query 编号 #查看程序的执行步骤

3. mysql的查询流程

• mysql客户端通过协议与mysql服务器建连接，发送查询语句，先检查查询缓存，如果命中，直接返回结果，否则进行语句解析,也就是说，在解析查询之前，服务器会先访问查询缓存(query cache)——它存储SELECT语句以及相应的查询结果集。如果某个查询结果已经位于缓存中，服务器就不会再对查询进行解析、优化、以及执行。它仅仅将缓存中的结果返回给用户即可，这将大大提高系统的性能。
• 语法解析器和预处理：首先mysql通过关键字将SQL语句进行解析，并生成一颗对应的“解析树”。mysql解析器将使用mysql语法规则验证和解析查询；预处理器则根据一些mysql规则进一步检查解析数是否合法。
• 查询优化器当解析树被认为是合法的了，并且由优化器将其转化成执行计划。一条查询可以有很多种执行方式，最后都返回相同的结果。优化器的作用就是找到这其中最好的执行计划。。
• 然后，mysql默认使用的BTREE索引，并且一个大致方向是:无论怎么折腾sql，至少在目前来说，mysql最多只用到表中的一个索引。

4. sql执行顺序优化前后可能存在的差别

手写:

机读:

随着Mysql版本的更新换代，其优化器也在不断的升级，优化器会分析不同执行顺序产生的性能消耗不同而动态调整执行顺序。 下面是经常出现的查询顺序：

5. MyISAM和InnoDB的区别

	MyISAM	InnoDB
事务	不支持	支持
锁	表锁	行表锁
外键	不支持	支持
缓存	只缓存索引, 不缓存真实数据	缓存索引和真实数据, 对内存的要求比较高, 影响最终的性能
关注点	节省资源, 消耗少, 简单业务使用	并发写, 事务, 更大资源

6. join的使用

7. mysql 底层原理

mysql底层使用的是平衡的多路搜索树 b+ tree 改良版

(1) b tree树

1) 基础介绍

一颗b树，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示）[其中红色方块代表这个key存在data指针]，如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。

2) 查找过程

如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。

真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

3) 优缺点

优点: 速度快, 效率高

缺点: IO频繁, 内存使用率低, 查询更稳定

如果这图看不懂可以看下面这个图

(2) b+tree树

怎么看, 像上面btree的说法, 区别在于蓝色方块是 key 但是data不在那个蓝色方块里面了, 比如我们现在要找key = 28的数据, 我们找到前面非叶子节点中的磁盘块时, 不能直接查找到这个key说对应的data, 所以需要使用 PX 指针去不断的往下面查找data, 知道到叶子节点才才能找得到我们想要的数据, 不想前面的btree, 直接在非叶子或者叶子节点找到对应的key就能立马查找到对应的data

看不懂可以直接看上面这张图片

(3) B+Tree与B-Tree 的区别

　1）B-树的关键字和记录是放在一起的，叶子节点可以看作外部节点，不包含任何信息；B+树的非叶子节点中只有关键字和指向下一个节点的索引，记录只放在叶子节点中。　 2）在B-树中，越靠近根节点的记录查找时间越快，只要找到关键字即可确定记录的存在；而B+树中每个记录的查找时间基本是一样的，都需要从根节点走到叶子节点，而且在叶子节点中还要再比较关键字。从这个角度看B-树的性能好像要比B+树好，而在实际应用中却是B+树的性能要好些。因为B+树的非叶子节点不存放实际的数据，这样每个节点可容纳的元素个数比B-树多，树高比B-树小，这样带来的好处是减少磁盘访问次数。尽管B+树找到一个记录所需的比较次数要比B-树多，但是一次磁盘访问的时间相当于成百上千次内存比较的时间，因此实际中B+树的性能可能还会好些，而且B+树的叶子节点使用指针连接在一起，方便顺序遍历（例如查看一个目录下的所有文件，一个表中的所有记录等），这也是很多数据库和文件系统使用B+树的缘故。 　思考：为什么说B+树比B-树更适合实际应用中操作系统的文件索引和数据库索引？ 1) B+树的磁盘读写代价更低 　　B+树的内部结点并没有指向关键字具体信息的指针。因此其内部结点相对B 树更小。如果把所有同一内部结点的关键字存放在同一盘块中，那么盘块所能容纳的关键字数量也越多。一次性读入内存中的需要查找的关键字也就越多。相对来说IO读写次数也就降低了。 2) B+树的查询效率更加稳定 　　由于非终结点并不是最终指向文件内容的结点，而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同，导致每一个数据的查询效率相当。

8. 哪些情况需要创建索引? 那些情况不能创建索引?

哪些情况需要创建索引:

(1) 主键自动建立唯一索引

(2) 频繁作为查询条件的字段应该创建索引

(3) 查询中与其它表关联的字段，外键关系建立索引

(4) 单键/组合索引的选择问题， 组合索引性价比更高

(5) 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度(6) 查询中统计或者分组字段

哪些情况不要创建索引:

4) 哪些索引被实际使用 --- type

**是较为重要的一个指标，结果值从最好到最坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

system>const>eq_ref>ref>range>index>ALL

一般来说，得保证查询至少达到range级别，最好能达到ref。

5) 实际使用的索引名称 --- key

6) 索引使用长度 --- key_len

8) 备注 --- Extra

①Using filesort: 排序的索引未加上

②Using temporary: 分组, 一般存在这个同时也会存在Using filesort

③using join buffer: 一般是join语句的条件判断没有加索引

9. 索引失效

1. 全值匹配我最爱
2. 最佳左前缀法则(从索引的左边开始匹配索引)
3. 不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描
4. 存储引擎不能使用索引中范围条件右边的列
5. mysql 在使用不等于(!= 或者<>)的时候无法使用索引会导致全表扫描
6. is not null 也无法使用索引,但是is null是可以使用索引的
7. like以通配符开头('%abc...')mysql索引失效会变成全表扫描的操作
8. 字符串不加单引号索引失效

总结:

假设索引是index(a,b,c)

Where语句	索引是否被使用
where a = 3	Y,使用到a
where a = 3 and b = 5	Y,使用到a，b
where a = 3 and b = 5 and c = 4	Y,使用到a,b,c
where b = 3 或者 where b = 3 and c = 4或者 where c = 4	N
where a = 3 and c = 5	使用到a， 但是c不可以，b中间断了
where a = 3 and b > 4 and c = 5	使用到a和b， c不能用在范围之后，b断了
where a is null and b is not null	is null 支持索引 但是is not null 不支持,所以 a 可以使用索引,但是 b不可以使用
where a <> 3	不能使用索引
where abs(a) =3	不能使用 索引
where a = 3 and b like 'kk%' and c = 4	Y,使用到a,b,c
where a = 3 and b like '%kk' and c = 4	Y,只用到a
where a = 3 and b like '%kk%' and c = 4	Y,只用到a
where a = 3 and b like 'k%kk%' and c = 4	Y,使用到a,b,c

• 没有where条件则没有索引
• order by 方向相反则索引失效, 比如: asc和desc同时存在的话, 索引失效
• order by 前面没有where 索引无法使用, 但是如果后面有limit的话, 则也相当于存在where

10. 一般性建议

1. 对于单键索引，尽量选择针对当前query过滤性更好的索引
2. 在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。
3. 在选择组合索引的时候，尽量选择可以能够包含当前query中的where字句中更多字段的索引
4. 在选择组合索引的时候，如果某个字段可能出现范围查询时，尽量把这个字段放在索引次序的最后面
5. 书写sql语句时，尽量避免造成索引失效的情况

11. 关联查询优化

1. 保证被驱动表的join字段已经被索引
2. left join 时，选择小表作为驱动表，大表作为被驱动表。

驱动表需要ALL全表扫描, 所以尽量使用小表作为驱动表, 大表作为被驱动表

1. inner join 时，mysql会自己帮你把小结果集的表选为驱动表。(使用inner join的话, 会被mysql自动优化)
2. 子查询尽量不要放在被驱动表，有可能使用不到索引。
3. 能够直接多表关联的尽量直接关联，不用子查询。

12. 子查询优化

尽量不要使用not in 或者 not exists, 可以转换成用left outer join on xxx is null 替代

SELECT SQL_NO_CACHE age,count(*)  FROM emp a WHERE id  NOT  IN(SELECT ceo FROM dept b2 WHERE ceo IS NOT NULL) group by age having count(*)<10000

优化后

EXPLAIN SELECT SQL_NO_CACHE age,count(*) FROM emp a LEFT OUTER JOIN dept b ON a.id =b.ceo WHERE b.ceo IS NULL group by age having count(*)<10000

13. 索引的选择

explain
select * from emp e
where e.age = 30 and e.empno < 101000 order by e.name;

添加索引

create index idx_age_empno_name on emp(age, empno, name);

先看 rows, 比较小才扫描了24行

后key_len 发现用上了两个索引

9 == 4 + 5, 其中4是字段不能为空int, 5是字段可以为空int

create index idx_age_name on emp (age, name);

最后看extra, 发现存在using filesort

这个时候, 我们就要去选择了, 看看是先过滤掉大小条件判断快还是去掉using filesort快, 所以下面开始判断

发现扫描了2w+行, 而且key_len明显没用上两个字段, 只用上了age, 但是发现filesort还是去掉了, 看起来是有效果的

但我估计前一种速度快, 因为排序是条件过滤完毕之后的事情, 而下面这种条件过滤之后还存在很多很多的结果, 然后通过这个结果再去排序, 即使排序添加了索引, 但是速度很慢

但是前面一种通过条件过滤后剩下了几十行row, 所以拿着这个去排序速度铁定快, 即使排序未作索引

但是有些情况可能下面着这种速度更快, 要看具体情况

14. 如果不在索引列上，filesort有两种算法: mysql就要启动双路排序和单路排序

• 双路排序

(1) MySQL 4.1之前是使用双路排序,字面意思就是两次扫描磁盘，最终得到数据，读取行指针和orderby列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出

(2) 从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段。

取一批数据，要对磁盘进行了两次扫描，众所周知，IO是很耗时的，所以在mysql4.1之后，出现了第二种改进的算法，就是单路排序。

• 单路排序

从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO,但是它会使用更多的空间，因为它把每一行都保存在内存中了。

• 结论及引申出的问题

(1) 由于单路是后出的，总体而言好过双路

(2) 但是用单路有问题

在sort_buffer中，方法B比方法A要多占用很多空间，因为方法B是把所有字段都取出, 所以有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序（创建tmp文件，多路合并），排完再取取sort_buffer容量大小，再排……从而多次I/O。

本来想省一次I/O操作，反而导致了大量的I/O操作，反而得不偿失。

• 优化策略

(1) 增大sort_buffer_size参数的设置

(2) 增大max_length_for_sort_data参数的设置

(3) 减少select 后面的查询的字段。

(4) why?

提高Order By的速度

1. Order by时select * 是一个大忌只Query需要的字段， 这点非常重要。在这里的影响是：1.1 当Query的字段大小总和小于max_length_for_sort_data 而且排序字段不是 TEXT|BLOB 类型时，会用改进后的算法——单路排序， 否则用老算法——多路排序。1.2 两种算法的数据都有可能超出sort_buffer的容量，超出之后，会创建tmp文件进行合并排序，导致多次I/O，但是用单路排序算法的风险会更大一些,所以要提高sort_buffer_size。
2. 尝试提高 sort_buffer_size不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的 1M-8M之间调整
3. 尝试提高 max_length_for_sort_data提高这个参数， 会增加用改进算法的概率。但是如果设的太高，数据总容量超出sort_buffer_size的概率就增大，明显症状是高的磁盘I/O活动和低的处理器使用率. 1024-8192之间调整

15. GROUP BY关键字优化

group by 使用索引的原则几乎跟order by一致 ，唯一区别是groupby 即使没有过滤条件用到索引，也可以直接使用索引。

16. 最后使用索引的手段：覆盖索引

(1) 什么是覆盖索引？简单说就是，select 到 from 之间查询的列 <=使用的索引列+主键

17. 慢查日志(了解)

默认情况下，MySQL数据库没有开启慢查询日志, 当然，如果不是调优需要的话，一般不建议启动该参数, 影响性能

默认情况下slow_query_log的值为OFF，表示慢查询日志是禁用的，可以通过设置slow_query_log的值来开启

SHOW VARIABLES LIKE '%slow_query_log%';

使用set global slow_query_log=1;开启了慢查询日志只对当前数据库生效，如果MySQL重启后则会失效。

set global slow_query_log=1;

查看多长时间算慢查询时间

show variables like '%long_query_time%';

对全局生效, 但是不对当前session生效

set global long_query_time = 1;

对当前session会话生效

set session long_query_time = 1;

在mysql源码里是判断大于long_query_time，而非大于等于。

如果要永久生效，就必须修改配置文件my.cnf（其它系统变量也是如此）

修改my.cnf文件，[mysqld]下增加或修改参数slow_query_log 和slow_query_log_file后，然后重启MySQL服务器。也即将如下两行配置进my.cnf文件

slow_query_log=1 slow_query_log_file=/var/lib/mysql/query-slow.log

关于慢查询的参数slow_query_log_file ，它指定慢查询日志文件的存放路径，系统默认会给一个缺省的文件host_name-slow.log（如果没有指定参数slow_query_log_file的话）

查询当前系统中有多少条慢查询记录

show global status like '%Slow_queries%';

慢查询配置

my.cnf【mysqld】下配置：

slow_query_log=1 slow_query_log_file=/var/lib/mysql/atguigu-slow.log long_query_time=3 log_output=FILE

mysql日志分析工具mysqldumpslow

查看mysqldumpslow的帮助信息

得到返回记录集最多的10个SQL

mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log

得到访问次数最多的10个SQL

mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log

得到按照时间排序的前10条里面含有左连接的查询语句

mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log

另外建议在使用这些命令时结合 | 和more 使用 ，否则有可能出现爆屏情况

mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more

日志方面还有一个全局日志, 这个更加消耗内存, 主要是mysql执行的时候莫名其妙出现的未知错误时开始来跑一跑查一查就知道了

18. SHOW PROCESSLIST用户状态查询列表(了解)

SHOW PROCESSLIST;

能干什么：查询所有用户正在干什么

如果出现不顺眼的直接kill [id]

比如查看什么链接是否被死锁

19. 主从复制

• 是什么?
  

  slave会从master读取binlog来进行数据同步

MySQL复制过程分成三步： 1 master将改变记录到二进制日志（binary log）。这些记录过程叫做二进制日志事件，binary log events； 2 slave将master的binary log events拷贝到它的中继日志（relay log）； 3 slave重做中继日志中的事件，将改变应用到自己的数据库中。 MySQL复制是异步的且串行化的

• 复制的基本原则
  

  每个slave只有一个master

  每个slave只能有一个唯一的服务器ID

  每个master可以有多个salve

• 复制的最大问题
  

  延时

• 主机配置my.ini(Master配置)

[mysql] default-character-set=utf8 [mysqld] port = 3306 basedir=D:programenvmysql-5.7.28-winx64 datadir=D:programenvmysql-5.7.28-winx64data max_connections=200 character-set-server=utf8 default-storage-engine=INNODB query_cache_type=1 # The TCP/IP Port the MySQL Server will listen on server-id=1 log-bin=D:/program/env/mysql_log/data/mysqlbin log-err=D:/program/env/mysql_log/data/mysqlerr # Path to installation directory. All paths are usually basedir=D:/program/env/mysql_log/ tmpdir=D:/program/env/mysql_log/ # Path to the database root datadir=D:/program/env/mysql_log/data read-only=0 binlog-ignore-db=mysql binlog-do-db=ww

mysql主从复制起始时，从机不继承主机数据

上面的二进制格式文字有三种方式:

① statement: 主要方法是记录sql语句, 但是缺点是: 如果sql语句中存在函数, 比如时间函数的话, 那么主机和从机执行这个函数的时间不同, 得到的结果也不相同

② row: 主要方式是通过记录行的方式进行复制的, 但是也存在一个问题, 就是复制的数据很多很多, 所以很慢

③ mixed: 解决了上面的两种问题, 会主动取判断是否存在函数, 如果存在则选择row, 如果不存在则使用statement, 但是还是存在问题, 就是 @@host name这中变量无法使用

• 从机配置
  

  从机配置文件修改my.cnf的[mysqld]栏位下

  注意my.cnf 中有server-id = 1

  

  因修改过配置文件，请主机+从机都重启后台mysql服务

• 主机从机都关闭防火墙
  

  关闭虚拟机linux防火墙 systemctl stop firewalld

  windows手动关闭

  安全工具关上：腾讯管家360不影响

• 在Windows主机上建立帐户并授权slave

1. GRANT REPLICATION SLAVE ON . TO 'zhangsan'@'从机器数据库IP' IDENTIFIED BY '123456';

1. 查询master的状态

执行完此步骤后不要再操作主服务器MYSQL，防止主服务器状态值变化

• 在Linux从机上配置需要复制的主机

1. 

CHANGE MASTER TO MASTER_HOST='主机IP',MASTER_USER='zhangsan',MASTER_PASSWORD='123456',MASTER_LOG_FILE='File名字',MASTER_LOG_POS=Position数字;

1. 启动从服务器复制功能

start slave;

1. show slave statusG;
   

   下面两个参数都是Yes，则说明主从配置成功！

   Slave_IO_Running: Yes Slave_SQL_Running: Yes

• 主机新建库、新建表、insert记录，从机复制
• 如何停止从服务复制功能
  
  stop slave;

• 如何重新配置主从
  
  stop slave; reset master;

20. mycat

1. Mycat介绍

(1) 是什么

数据库中间件, 前身是阿里的cobar

(2) 干什么的

1、读写分离

2、数据分片

垂直拆分

水平拆分

垂直+水平拆分

3、多数据源整合

(3) 原理

“拦截”

Mycat 的原理中最重要的一个动词是“拦截”，它拦截了用户发送过来的 SQL 语句，首先对 SQL 语句做了一些特定的分析：如分片分析、路由分析、读写分离分析、缓存分析等，然后将此 SQL 发往后端的真实数据库，并将返回的结果做适当的处理，最终再返回给用户

这种方式把数据库的分布式从代码中解耦出来，程序员察觉不出来后台使用mycat还是mysql。

2. 安装启动

(1) 解压缩文件拷贝到linux下 /usr/local/(2) 三个文件

(3) 启动前先修改schema.xml

<?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>

(4) 再修改server.xml

<user name="root"> <property name="password">654321</property> <property name="schemas">TESTDB</property> </user>

(5) 验证数据库访问情况

mysql -uroot -p123123 -h 192.168.154.1 -P 3306mysql -uroot -p123123 -h 192.168.154.154 -P 3306

如本机远程访问报错，请建对应用户grant all privileges on . to root@'缺少的host' identified by '123123';

(6) 启动程序

(7) 启动时可能出现报错

1) 域名解析失败

①、用vim 修改 /etc/hosts 文件

②、修改后重新启动网络服务

(7) 登录

1) 后台管理窗口

mysql -uroot -p654321 -P9066 -h192.168.67.131

2) 数据窗口

mysql -uroot -p654321 -P8066 -h192.168.67.131

3. 读写分离

(1) 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" dataNode="dn1"> </schema> <dataNode name="dn1" dataHost="host1" database="atguigu_mc" /> <dataHost name="host1" maxCon="1000" minCon="10" balance="2" 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>

1) balance

负载均衡类型，目前的取值有4 种：

1. balance="0", 不开启读写分离机制，所有读操作都发送到当前可用的 writeHost 上。
2. balance="1"，全部的 readHost 与 stand by writeHost 参与 select 语句的负载均衡，简单的说，当双主双从模式(M1->S1，M2->S2，并且 M1 与 M2 互为主备)，正常情况下，M2,S1,S2 都参与 select 语句的负载均衡。
3. balance="2"，所有读操作都随机的在 writeHost、readhost 上分发。
4. balance="3"，所有读请求随机的分发到 readhost 执行，writerHost 不负担读压力

(2) 读写分离

1) 创建表

create table t_replica ( id int auto_increment , name varchar(200) );

分别在两个库下插入：

insert into t_replica(name) values (@@hostname)

然后再mycat下执行select * from t_replica能够

4. 分库

(1) 如何选择分库表

#客户表 rows:20万 CREATE TABLE customer( id INT AUTO_INCREMENT, NAME VARCHAR(200), PRIMARY KEY(id) ); #订单表 rows:600万 CREATE TABLE orders( id INT AUTO_INCREMENT, order_type INT, customer_id INT, amount DECIMAL(10,2), PRIMARY KEY(id) ); #订单详细表 rows:600万 CREATE TABLE orders_detail( id INT AUTO_INCREMENT, detail VARCHAR(2000), order_id INT, PRIMARY KEY(id) ); #订单状态字典表 rows:20 CREATE TABLE dict_order_type( id INT AUTO_INCREMENT, order_type VARCHAR(200), PRIMARY KEY(id) ); select o.*,od.detail,d.order_type from orders o inner join orders_detail od on o.id =od.order_id inner join dict_order_type d on o.order_type=d.id where o.customer_id=xxxx

(2) schema.xml

<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="atguigu_mc" /> <dataNode name="dn2" dataHost="host2" database="atguigu_sm" /> <dataHost name="host1" maxCon="1000" minCon="10" balance="2" 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>

5. 水平分表

(1)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" dataNode="dn1"> <table name="customer" dataNode="dn2" ></table> <table name="orders" dataNode="dn1,dn2" rule="mod_rule" ></table> </schema> <dataNode name="dn1" dataHost="host1" database="atguigu_mc" /> <dataNode name="dn2" dataHost="host2" database="atguigu_sm" /> <dataHost name="host1" maxCon="1000" minCon="10" balance="2" 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>

(2)rule.xml

<tableRule name="mod_rule"> <rule> <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>

(3)跨库join

1) ER表

为了相关联的表的行尽量分在一个库下

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" dataNode="dn1"> <table name="customer" dataNode="dn2" ></table> <table name="orders" dataNode="dn1,dn2" rule="mod_rule" > <childTable name="orders_detail" primaryKey="id" joinKey="order_id" parentKey="id" /> </table> <table name="dict_status" dataNode="dn1,dn2" type="global" ></table> </schema> <dataNode name="dn1" dataHost="host1" database="atguigu_mc" /> <dataNode name="dn2" dataHost="host2" database="atguigu_sm" /> <dataHost name="host1" maxCon="1000" minCon="10" balance="2" 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>

建相关表

2) 全局表

设定为全局的表，会直接复制给每个数据库一份，所有写操作也会同步给多个库。

所以全局表一般不能是大数据表或者更新频繁的表

一般是字典表或者系统表为宜。

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" dataNode="dn1"> <table name="customer" dataNode="dn2" ></table> <table name="orders" dataNode="dn1,dn2" rule="mod_rule" ></table> <table name="dict_order_type" dataNode="dn1,dn2" type="global" ></table> </schema> <dataNode name="dn1" dataHost="host1" database="atguigu_mc" /> <dataNode name="dn2" dataHost="host2" database="atguigu_sm" /> <dataHost name="host1" maxCon="1000" minCon="10" balance="2" 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>

(4) 全局序列

1) 三种方式

①本地文件

不推荐

②数据库方式

Ⅰ数据库序列方式原理

利用数据库一个表 来进行计数累加。

但是并不是每次生成序列都读写数据库，这样效率太低

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

问：那如果mycat崩溃了 ，那内存中的序列岂不是都没了？是的。如果是这样，那么mycat启动后会向数据库申请新的号段，原有号段会弃用。也就是说如果mycat重启，那么损失是当前的号段没用完的号码，但是不会因此出现主键重复。

Ⅱ建库序列脚本

win10 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; 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; SELECT * FROM MYCAT_SEQUENCE TRUNCATE TABLE MYCAT_SEQUENCE ##增加要用的序列 INSERT INTO MYCAT_SEQUENCE(NAME,current_value,increment) VALUES ('ORDERS', 400000, 100);

Ⅲ修改mycat配置

sequence_db_conf.properties

server.xml

然后重启

Ⅳ插入语句

insert into `orders`(id,amount,customer_id,order_type) values(next value for MYCATSEQ_ORDERS,1000,101,102);

③时间戳方式

18位

比较长

④自主生成

Ⅰ根据业务逻辑组合Ⅱ可以利用 redis的单线程原子性 incr来生成序列

来自为知笔记(Wiz)

附件列表