Mysql

作者：洪斌爱可生南区负责人兼技术服务总监，MySQL  ACE，擅长数据库架构规划、故障诊断、性能优化分析，实践经验丰富，帮助各行业客户解决 MySQL 技术问题，为金融、运营商、互联网等行业客户提供 MySQL 整体解决方案。本文来源：转载自公众号-玩转MySQL*爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。MySQL 默认是启用 innodb_use_native_aio，使用异步 IO 操作，MySQL 启动时所需 aio slot 若超过系统当前 fs.aio-max-nr 设置，则无法启动报错 InnoDB: io_setup() failed with EAGAIN after 5 attempts. 通常在单机单实例环境下很少会遇到超出 aio-max-nr 的问题，若部署单机多实例，会大概率遇到此问题。我们来分析下该如何配置 fs.aio-max-nr 参数。关于 aio-nr 与 aio-max-nr aio-nr is the running total of the number of events specified on theio_setup system call for all currently active aio contexts. If aio-nrreaches aio-max-nr then io_setup will fail with EAGAIN. Note thatraising aio-max-nr does not result in the pre-allocation or re-sizingof any kernel data structures.启动 MySQL 时会分配多少个 event slot？使用 strace 观测 io_setup 调用情况， ...

说明：本文mysql下载目录 /home/apps/本文mysql安装目录 /home/apps/mysql 本文mysql数据目录 /home/apps/mysql/data下面是操作步骤：1.创建下载目录 # cd home# mkdir apps2.下载mysql5.7安装包 # cd /home/apps# wget ttps://downloads.mysql.com/archives/get/p/23/file/mysql-5.7.29-linux-glibc2.12-x86_64.tar.gz3.解压安装包 # tar -zxvf mysql-5.7.29-linux-glibc2.12-x86_64.tar.gz# lsmysql-5.7.29-linux-glibc2.12-x86_64mysql-5.7.29-linux-glibc2.12-x86_64.tar.gz4.复制解压后的mysql目录下的内容到系统的本地软件目录 # cp -r mysql-5.7.29-linux-glibc2.12-x86_64/* /home/apps/mysql/5.创建mysql用户组和mysql用户 # groupadd mysql# useradd -r -g mysql mysql6.操作mysql安装目录归属到mysql用户和用户组 # chown -R mysql:mysql /home/apps/mysql/7.更改mysql安装文件夹mysql/的权限 # chmod -R 755 /home/apps/mysql/8.初始化mysql命令 # cd /home/apps/mysql/bin# ./mysqld --user=mysql --basedir=/home/apps/mysql --datadir=/home/apps/mysql/data --initialize9.执行成功后最后一句内容如下，XXXXXX就是初始化之后的root用户的密码了 A temporary password is generated for root@localhost: XXXXXX10.修改Mysql配置文件 # vim /home/apps/mysql/support-files/mysql.server修改的内容如图11.将mysql脚本复制到/etc/init.d # cp /home/apps/mysql/support-files/mysql.server /etc/init.d/mysqld12.修改my.cnf文件 ...

大家都知道like的效率很低，数据量大就会很慢，今天发现了一个内置函数instr()效率比like高SELECT * FROM msg_list WHERE title LIKE '%涂山%'可以替换为： SELECT * FROM msg_list WHERE INSTR(title,'涂山') > 0在 ThinkPHP / Laravel中使用的方法有：使用 whereRaw()执行原生INSTR()修改构造查询器（不太推荐，不过用起来方便）项目根目录\thinkphp\library\think\db\Builder.php MsgList::where([ 'title' => ['instr','涂山']]) ->select();

以前学习了不少东西，都忘了不少，最近就想着总结一下，就想到想写博客文章来总结下自己这些年学习的东西，记录下各方面技术学习情况。 如果觉得好看，请给个赞 你有一个思想，我有一个思想，我们交换后，一个人就有两个思想If you can NOT explain it simply, you do NOT understand it well enough简单介绍下这篇文章的流程 1.为什么使用索引A：那还用说，肯定在某些方面有特长呗，比如你知道的【它很快，非常快】 我也很赞同这个答案，但说的不够具体，你得说明它为啥这么快 如果问你选择索引的原因就是一个【快】字，面试也就不会出那么多幺蛾子了。你有没有问过你自己 索引在所有场景下都是快的吗？知道它很快，何为快？怎样度量？索引（翻译官方文档）是帮助MySQL高效获取数据的排好序的数据结构拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。 实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。 2.索引数据结构详解在创建索引时，通常采用的数据结构有：Hash、二叉树、红黑树、B树以及B+树 可以在线查看数据结构的网页 Data Structure 二叉树:定义规则为左边节点值比根节点小，右边节点值比根节点大，并且左右子节点都是排序树 要是索引采取这种结构，数值递增那种，就要满足右边节点值比根节点大，导致检索数据会导致查了6遍磁盘 红黑树：（在jdk8之后，用数组+链表+红黑树来实现hashmap，当碰撞的元素个数大于8时 & 总容量大于64，会有红黑树的引入。）红黑树是一种自平衡二叉树，主要解决二叉搜索树在极端情况下退化成链表的情况，在数据插入的时候同时调整整个树，使其节点尽量均匀分布，保证平衡性，目的在于降低树的高度，提高查询效率。（右边的树的高度不会大于左边树的高度超过1，大于等于1级后会自动平衡，自己可在数据结构上插入试试） 特点： 节点是红色或者黑色根节点是黑色每个叶子的节点都是黑色的空节点（NULL）每个红色节点的两个子节点都是黑色的从任意节点到其每个叶子的所有路径都包含相同的黑色节点 优点：解决二叉搜索树的极端情况的退化问题。缺点：检索时间依旧与树的高度有关，当数据量很大时，树的高度就会很高，检索的次数就会比较多，检索的时间会比较久，效率低。从前面分析情况来看，减少磁盘IO的次数就必须要压缩树的高度，让瘦高的树尽量变成矮胖的树，所以B-Tree就在这样伟大的时代背景下诞生了 B-Tree 基于以上进行扩容，每个横向的节点变多了意味的存放的数据变多了，整个树的高度也变小了，减少磁盘io的搜索速度 特点1.叶节点具有相同的深度，叶节点的指针为空2.所有索引元素不重复3.节点中的数据索引从左到右递增排列 缺点：可以看到存放的数据类似key+value 的数据 要是InnoDB 的话data可能存放的是除了索引外的字段 页节点mysql默认推荐的是16k大小 ( show global status like 'Innodb_page_size';)，假如大节点的每个节点的data存的数据比较大，那么意味着横向能存储的索引就会变很少，大节点的能存储的索引变少意味着整颗树的高度受到限制 B+Tree(B-Tree变种 MySql默认使用索引结构) 1.非叶子节点不存储data，只存储索引(冗余)，可以放更多的索引2.叶子节点包含所有索引字段3.叶子节点用指针连接，提高区间访问的性能 （快速定位范围查询，例如查询大于20，第一次io从根节点查询三次定位到20，然后通过后面的指针查询大于20的数据，就不用再从根节点的重新再查询，提高性能，叶子节点开始结束节点也是用指针连接串起来的） Hash前面说的mysql默认索引结构是B+Tree，还有一种索引结构是Hash如果是hash 的话是通过 hash（值）运算然后在磁盘中快速查找对应的磁盘文件指针从而找到行数据hash 索引查数据是很方便也快的，但是不支持范围性查找 例如 >= < between and 也不支持排序Hash索引适合等值查询 ，不适合范围查询 总结为什么mysql索引结构默认使用B+Tree为什么mysql索引结构默认使用B+Tree，而不是Hash，二叉树，红黑树？B-tree：因为B树不管叶子节点还是非叶子节点，都会保存数据，这样导致在非叶子节点中能保存的指针数量变少（有些资料也称为扇出），指针少的情况下要保存大量数据，只能增加树的高度，导致IO操作变多，查询性能变低；Hash：虽然可以快速定位，但是没有顺序，IO复杂度高。二叉树：树的高度不均匀，不能自平衡，查找效率跟数据有关（树的高度），并且IO代价高。红黑树：树的高度随着数据量增加而增加，IO代价高。 索引是如何支持千万级表的快速查询索引可以把它想象跟旁边的指针的成对存在的（指针是指向下一个节点的磁盘位置（占用6字节））索引假设字段为数字类型 Bigint 8b+ 指针默认占用空间6b = 14b （索引跟旁边的指针的成对存在的大小总和）大节点能存放 16kb数据 那么最多能存放 16kb * 1024/ 14= 1170个索引  ...

简介： MySQL数据库优化技巧大全 MySQL优化三大方向 ① 优化MySQL所在服务器内核(此优化一般由运维人员完成)。 ② 对MySQL配置参数进行优化（my.cnf）此优化需要进行压力测试来进行参数调整。 ③ 对SQL语句以及表优化。 MySQL参数优化 1:MySQL 默认的最大连接数为 100，可以在 mysql 客户端使用以下命令查看 mysql> show variables like 'max_connections'; 2:查看当前访问Mysql的线程 mysql> show processlist; 3:设置最大连接数 mysql>set globle max_connections = 5000; 最大可设置16384,超过没用 4:查看当前被使用的connections mysql>show globle status like 'max_user_connections' 申请阿里云服务时，可以使用2000元阿里云代金券，阿里云官网领取网址：https://dashi.aliyun.com/site/yun/youhui （长期有效） 对MySQL语句性能优化的16条经验 ① 为查询缓存优化查询 ② EXPLAIN 我们的SELECT查询(可以查看执行的行数) ③ 当只要一行数据时使用LIMIT 1 ④ 为搜索字段建立索引 ⑤ 在Join表的时候使用相当类型的列，并将其索引 ⑥ 千万不要 ORDER BY RAND () ⑦ 避免SELECT * ⑧ 永远为每张表设置一个ID ⑨ 可以使用ENUM 而不要VARCHAR ⑩ 尽可能的使用NOT NULL ⑪ 固定长度的表会更快 ⑫ 垂直分割 ⑬ 拆分打的DELETE或INSERT语句 ⑭ 越小的列会越快 ⑮ 选择正确的存储引擎 ⑯ 小心 "永久链接" ...

本人从事java开发四年了，以前学习了不少东西，都忘了不少，最近就想着总结一下，突然就想到想写博客文章来总结下自己这些年学习的东西，记录下各方面技术学习情况，本篇文章是我看完mysql5.7从入门到精通一本书及其看了不少技术大牛的博客和一些教学视频总结出来的，自我感觉应该是全网mysql性能优化总结得最好的一篇文章，没有之一，哈哈哈，不接受反驳。 如果觉得好看，请给个赞 你有一个思想，我有一个思想，我们交换后，一个人就有两个思想If you can NOT explain it simply, you do NOT understand it well enough简单介绍下这篇文章的流程 1.索引是什么？ 为什么使用索引A：那还用说，肯定在某些方面有特长呗，比如你知道的【它很快，非常快】 我也很赞同这个答案，但说的不够具体，你得说明它为啥这么快 如果问你选择索引的原因就是一个【快】字，面试也就不会出那么多幺蛾子了。你有没有问过你自己 索引在所有场景下都是快的吗？知道它很快，何为快？怎样度量？索引（翻译官方文档）是帮助MySQL高效获取数据的排好序的数据结构拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。 实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。 2.索引数据结构详解在创建索引时，通常采用的数据结构有：Hash、二叉树、红黑树、B树以及B+树 可以在线查看数据结构的网页 Data Structure 二叉树:定义规则为左边节点值比根节点小，右边节点值比根节点大，并且左右子节点都是排序树 要是索引采取这种结构，数值递增那种，就要满足右边节点值比根节点大，导致检索数据会导致查了6遍磁盘 红黑树：（在jdk8之后，用数组+链表+红黑树来实现hashmap，当碰撞的元素个数大于8时 & 总容量大于64，会有红黑树的引入。）红黑树是一种自平衡二叉树，主要解决二叉搜索树在极端情况下退化成链表的情况，在数据插入的时候同时调整整个树，使其节点尽量均匀分布，保证平衡性，目的在于降低树的高度，提高查询效率。（右边的树的高度不会大于左边树的高度超过1，大于等于1级后会自动平衡，自己可在数据结构上插入试试） 特点： 节点是红色或者黑色根节点是黑色每个叶子的节点都是黑色的空节点（NULL）每个红色节点的两个子节点都是黑色的从任意节点到其每个叶子的所有路径都包含相同的黑色节点优点：解决二叉搜索树的极端情况的退化问题。缺点：检索时间依旧与树的高度有关，当数据量很大时，树的高度就会很高，检索的次数就会比较多，检索的时间会比较久，效率低。

最近在看阿里的数据库开发规范，正好看到说select *对查询效率的影响，正好想起来“多年”以前没有整理完成的数据库优化架构图，所以，一时激动，我就继续完成我的宏图伟业，不说别的，先上图，以证清白 有了图，接下来，就是对于我今天看的内容觉得比较好的分享，文末有福利 1、select * 对效率的影响在我们平时的代码编写或面试题中，很多人都会疑惑：select * 到底合理吗？ 如果说不合理，为什么？如果说合理，原因又是什么？ 1）、阿里规范 在阿里java规范中是强制不允许使用select * 这种操作的。 下面分析一下为什么不允许这种操作。 1、增加查询分析器解析成本。 这里主要是考虑了多余字段带来的更多成本消耗。 假设一张表有10个字段，你只需要其中的三个字段做分析。若使用select *，分析器就需要解析这十个字段，且将这是个字段查询出来也会增加多余的网络消耗，且若数据库和应用程序不在同一台服务器上，这种网络消耗更加明显。 2、增减字段容易与 resultMap 配置不一致。 采用select * 这种方式时，若表结构发生了改变，很容易导致操作失败。 2）、其他缺点 1、会查询出一些不需要的字段。这些字段很可能没有索引，这样就杜绝了覆盖索引的可能性，而索引覆盖又是速度极快，效率极高，业界极为推荐的查询方式。 2、多余字段会增大数据传输时间。 3、大字段，例如很长的 varchar，blob，text。准确来说，长度超过 728 字节的时候，会把超出的数据放到另外一个地方，因此读取这条记录会增加一次 io 操作。 科普：覆盖索引 解读1： 就是select的数据列只用从索引中就能够取得，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖。 解读2：索引是高效找到行的一个方法，当能通过检索索引就可以读取想要的数据，那就不需要再到数据表中读取行了。如果一个索引包含了（或覆盖了）满足查询语句中字段与条件的数据就叫 做覆盖索引。 2、索引的简单介绍1）、索引简介 索引用于快速找出在某个列中有一特定值的行。不使用索引，必须从第1条记录开始然后读完整个表直到找出相关的行。表越大，花费的时间越多。如果表中查询的列有一个索引，就能快速到达一个位置去搜寻到数据文件的中间，没有必要看所有数据。 2）、索引分类 索引分单列索引和组合索引。 ①、单列索引(主键索引,唯一索引,普通索引) 单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。 普通索引,这个是最基本的索引, 唯一索引,与普通索引类似,但是不同的是唯一索引要求所有的类的值是唯一的,这一点和主键索引一样.但是他允许有空值 主键索引,不允许有空值,(在B+TREE中的InnoDB引擎中,主键索引起到了至关重要的地位) ②、组合索引 组合索引，即一个索引包含多个列。 如果你建立了 组合索引(a_b_c) 那么他实际包含的是3个索引 (a) (a,b)(a,b,c) 在使用查询的时候遵循组合索引的"最左前缀",下面我们来分析一下 什么是最左前缀:及索引where时的条件要按照建立索引的时候字段的排序方式 1、不按索引最左列开始查询. ...

数据库-MySQL中for update的作用和用法一、for update定义for update是一种行级锁，又叫排它锁。 一旦用户对某个行施加了行级加锁，则该用户可以查询也可以更新被加锁的数据行，其它用户只能查询但不能更新被加锁的数据行。 如果其它用户想更新该表中的数据行，则也必须对该表施加行级锁．即使多个用户对一个表均使用了共享更新，但也不允许两个事务同时对一个表进行更新，真正对表进行更新时，是以独占方式锁表，一直到提交或复原该事务为止。 行锁永远是独占方式锁。 只有当出现如下之一的条件，才会释放共享更新锁： 执行提交（COMMIT）语句退出数据库（LOG　OFF）程序停止运行二、概念和用法通常情况下，select语句是不会对数据加锁，妨碍影响其他的DML和DDL操作。同时，在多版本一致读机制的支持下，select语句也不会被其他类型语句所阻碍。 而select … for update 语句是我们经常使用手工加锁语句。在数据库中执行select … for update ,大家会发现会对数据库中的表或某些行数据进行锁表，在mysql中，如果查询条件带有主键，会锁行数据，如果没有，会锁表。 由于InnoDB预设是Row-Level Lock，所以只有「明确」的指定主键，MySQL才会执行Row lock (只锁住被选取的资料例) ，否则MySQL将会执行Table Lock (将整个资料表单给锁住)。 举个例子: 假设有张表user ，里面有 id 和 name 两列，id是主键。 例1: (明确指定主键，并且数据真实存在，row lock) -- 开启事务begin;SELECT * FROM user WHERE id=3 FOR UPDATE;SELECT * FROM user WHERE id=3 and name='Tom' FOR UPDATE;-- 提交事务commit;例2: (明确指定主键，但数据不存在，无lock) begin;SELECT * FROM user WHERE id=0 FOR UPDATE;commit;注意： 上面的说法有误，InnoDB RR级别的锁算法是next-key lock ...

MySQL根据SQL查询条件的类型，归类了几种不同的索引访问方式(access method)1. const: 主键索引、唯一二级索引的等值比较，结果最多只能是一条记录。 1. 通过聚簇索引或唯一二级索引与常数的等值比较，来定位一条数据(唯一二级索引比较is NULL时，不是const，因为唯一二级索引不强制限制非NULL，因此查出的结果可能是多条，所以不是const访问方式) 2. ref: 对索引列的等值比较，结果可能是多条记录。 1. 普通二级索引来进行等值查询。关键点在于等值查询匹配到的结果的数量，由于查询期望的结果字段可能并不仅仅是二级索引所在的column，因此如果匹配到的结果集数量很小，那么回表的代价和很小，如果结果数量很大，则回表的代价就很大。 2. 查询二级索引列值为NULL的情况，因为普通或者唯一二级索引，都没有强制限制非NULL，因此查询到的结果可能会是多条的，所以是ref。 3. 对于包含多个column的索引，只要查询条件是最左边的连续的索引列的等值比较，也是ref3. ref\_or\_null: 查询二级索引值为某个常数或者NULL(有例外情况，如果结果太多，可能会直接全表扫描)4. range: 利用二级索引进行区间查询。5. index: where条件无法直接匹配任何索引，但是期望的结果集和查询条件都被某个联合索引全部覆盖到了，因此扫描了这个索引。6. all: 全表扫描注意事项: 通常一次查询只能使用一个索引，那么当where侯庙有多个条件时，MySQL如何选择索引呢？1. 二级索引 + 回表 1. 假定有两个条件A和B，且都有对应的索引，那么先判断使用哪个索引的开销较小，加入选择了A条件 2. 根据选择的索引定位记录 3. 回表，根据2中定位的结果进行回表操作，从主键索引取到完整的数据记录，然后再用B条件对完整的数据记录进行过滤2. 无法使用索引，例: WHERE 索引条件 OR 非索引条件 1. SELECT * FROM single\_table WHERE key2 > 100 AND common\_field = 'abc'; > key2 有二级索引，common\_field没有索引 2. 此时使用key2索引进行扫描，简化SQL=> SELECT \* FROM single\_table WHERE key2 > 100 AND TRUE，范围是(100,+∞\]，再用common\_field进行匹配 3. 此时使用key2索引进行扫描，简化SQL=> SELECT \* FROM single\_table WHERE key2 > 100 OR TRUE，范围是(-∞,+∞)，那么此时，不如直接全表扫描3. 非常复杂的查询条件时，如何进行分析？ 1. 观察WHERE后涉及到哪几个column 2. 观察有哪几个可用索引 3. 假设选择某个索引条件，简化SQL(注意，简化的思路就是把与被选中索引无关的查询条件全部替换为TRUE)，得到二级索引的扫描范围 4. 将扫描二级索引的到的结果，再用其余条件进行过滤虽然通常一次查询只会用到一个二级索引，但是在某些特定条件下，可能会用到多个索引，机索引合并1. Intersection合并2. Union合并3. Sort-Union合并4. 联合索引的使用关于联合索引的使用：~~~~凡凡一凡凡请问一下问什么说多列索引有范围查询只能用到第一个出现的范围查询作为索引，比如： select * from t where key1 > 100 and key2 = 'xxx' and key3 > 20; 如果key1,key2,key3是个联合索引，只能用到key1索引，为什么不同时使用三个条件去索引中查找呢？因为Key1的查询不是等值查询，查询出来的Key1值也不同，而只有当Key1值相同时，才能让Key2上，同理 只有所有记录中Key2值都相同时，才轮得到Key3，我是这么理解根据小册第8章，我的理解是“在某条件下，某列必须是有序的，该列才可能用上索引”。在这里就是key1 > 100时，key2是无序的，所以无法走key2的索引。而如果key1是等值查询，则key2有序，可以用上key2的索引。联合索引的使用，涉及到了联合索引的排序方式，它是按照索引列从左向右依次进行排序的。所以，当期望用到其中某个索引列时，该列必须是有序的，也就是说，这个列左侧的索引列，已经通过等值查询匹配过了。

原子性（atomicity) mysql实现原子性靠undo log实现,undo log和redo log记录物理日志不一样，它是逻辑日志。可以认为当delete一条记录时，undo log中会记录一条对应的 insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录。当执行rollback时，就可以从undo log中的逻辑记录读取到相应的内容并进行回滚一致性（consistency) mysql一致性靠redo log + undo log 二阶段提交实现. 先把数据写入redo log这个logbuffer缓冲区中, 写完状态改为prepare, 之后再写入到undo log 这个logbuffer缓冲区中, 之后再进行commit. 这样无论什么时候都能保证redo undo数据是一样隔离性（isolation） 通过mysql的一些锁机制完成持久性（durability） mysql持久需要redo log先将数据写入到缓冲区,之后(可以配置一些策略)将缓冲区的数据使用内核空间的fsync()函数写入到磁盘中.模拟简单事务提交-Undo+Redo 事务的简化过程 假设有A、B两个数据，值分别为1,2. A.事务开始. B.记录A=1到undolog. C.修改A=3. D.记录A=3到redolog. E.记录B=2到undolog. F.修改B=4. G.记录B=4到redolog. H.将redolog写入磁盘。 I.事务提交

慢SQL为什么会慢一条查询SQL的执行过程单表查询的访问方法多表查询时连接的原理慢SQL的解决方法直接加索引 索引的分类索引为什么会快优化SQL，更好的利用索引

作者：胡呈清爱可生 DBA 团队成员，擅长故障分析、性能优化，个人博客：https://www.jianshu.com/u/a95...，欢迎讨论。本文来源：原创投稿*爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。有这么一个 SQL，外查询 where 子句的 bizCustomerIncoming_id 字段，和子查询 where 字句的 cid 字段都有高效索引，为什么这个 SQL 执行的非常慢，需要全表扫描？ delete FROM biz_customer_incoming_path WHERE bizCustomerIncoming_id IN \(SELECT id FROM biz_customer_incoming WHERE cid='315upfdv34umngfrxxxxxx');我们从这么一个问题来引入接下来的内容，如果你知道答案就不用继续看下去了。 子查询优化策略对于不同类型的子查询，优化器会选择不同的策略。 对于 IN、=ANY 子查询，优化器有如下策略选择：semijoinMaterializationexists对于 NOT IN、<>ALL 子查询，优化器有如下策略选择：Materializationexists对于 derived 派生表，优化器有如下策略选择：derived_merge，将派生表合并到外部查询中（5.7 引入 ）；将派生表物化为内部临时表，再用于外部查询。注意：update 和 delete 语句中子查询不能使用 semijoin、materialization 优化策略 优化思路那么这些策略分别是什么意思？为什么会有这些优化策略？ 为方便分析，先建两张表： CREATE TABLE `t2` ( `id` int(11) NOT NULL, `a` int(11) DEFAULT NULL, `b` int(11) DEFAULT NULL, PRIMARY KEY (`id`), KEY `a` (`a`)) ENGINE=InnoDB;drop procedure idata;delimiter ;;create procedure idata()begin declare i int; set i=1; while(i<=1000)do insert into t2 values(i, i, i); set i=i+1; end while;end;;delimiter ;call idata();create table t1 like t2;insert into t1 (select * from t2 where id<=100)有以下子查询示例： ...

1、下载mysql 5.7镜像docker pull mysql:5.72、创建mysql容器并启动docker run -d -p 3306:3306 -e MYSQL_USER="ppx" -e MYSQL_PASSWORD="123456" -e MYSQL_ROOT_PASSWORD="123456" --name mysqltest1 mysql:5.7 --character-set-server=utf8 --collation-server=utf8_general_ci对上述参数进行解释： -e MYSQL_USER="" ：设置用户名-e MYSQL_PASSWORD=""：设置该用户的密码-e MYSQL_ROOT_PASSWORD=""：设置root用户密码--character-set-server=utf8：设置字符集为utf8--collation-server=utf8_general_cli：设置字符规则为utf8_general_cli3、对mysql进行挂载mkdir /optmkdir /opt/mysqlmkdir /opt/mysql/confmkdir /opt/mysql/data 创建并修改my.cnf配置文件vim /opt/mysql/conf/my.cnf my.cnf添加如下内容：[mysqld]user=mysqlcharacter-set-server=utf8default_authentication_plugin=mysql_native_password[client]default-character-set=utf8[mysql]default-character-set=utf8挂载目录可以按照自己想法建，但上下级关系要正确，即mysql下有conf 和 data。4、创建容器并启动docker run -d -p 3307:3306 --privileged=true -v /opt/mysql/conf/my.cnf:/etc/mysql/my.cnf -v /opt/mysql/data:/var/lib/mysql -e MYSQL_ROOT_PASSWORD=123456 --name mysql02 mysql:5.7参数说明： --privileged=true：容器内的root拥有真正root权限，否则容器内root只是外部普通用户权限 -v /opt/mysql/conf/my.cnf:/etc/my.cnf：映射配置文件 -v /opt/mysql/data:/var/lib/mysql：映射数据目录5、设置mysql字符编码vi /mydata/mysql/master/conf/my.cnf[client]default-character-set=utf8 [mysql]default-character-set=utf8 [mysqld]init_connect='SET collation_connection = utf8_unicode_ci'init_connect='SET NAMES utf8'character-set-server=utf8collation-server=utf8_unicode_ciskip-character-set-client-handshakeskip-name-resolve

MYSQL简介MySQL 是最流行的关系型数据库管理系统，在 WEB 应用方面 MySQL 是最好的 RDBMS(Relational Database Management System：关系数据库管理系统)应用软件之一。 数据库结构数据库有一个简单层级关系即，数据库-数据表-数据（行） 数据类型数值类型类型大小范围（有符号）范围（无符号）用途TINYINT1 byte(-128，127)(0，255)小整数值SMALLINT2 bytes(-32 768，32 767)(0，65 535)大整数值MEDIUMINT3 bytes(-8 388 608，8 388 607)(0，16 777 215)大整数值INT或INTEGER4 bytes(-2 147 483 648，2 147 483 647)(0，4 294 967 295)大整数值BIGINT8 bytes(-9,223,372,036,854,775,808，9 223 372 036 854 775 807)(0，18 446 744 073 709 551 615) 极大整数值FLOAT4 bytes(-3.402 823 466 E+38，-1.175 494 351 E-38)，0，(1.175 494 351 E-38，3.402 823 466 351 E+38)0，(1.175 494 351 E-38，3.402 823 466 E+38)单精度浮点数值DOUBLE8 bytes(-1.797 693 134 862 315 7 E+308，-2.225 073 858 507 201 4 E-308)，0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)0，(2.225 073 858 507 201 4 E-308，1.797 693 134 862 315 7 E+308)双精度浮点数值 DECIMAL对DECIMAL(M,D) ，如果M>D，为M+2否则为D+2依赖于M和D的值依赖于M和D的值小数值日期和时间类型类型大小用途DATE31000-01-01/9999-12-31YYYY-MM-DD日期值TIME3'-838:59:59'/'838:59:59'HH:MM:SS时间值或持续时间YEAR11901/2155YYYY年份值DATETIME81000-01-01 00:00:00/9999-12-31 23:59:59YYYY-MM-DD HH:MM:SS混合日期和时间值TIMESTAMP41970-01-01 00:00:00/2038 结束时间是第 2147483647 秒，北京时间 2038-1-19 11:14:07，格林尼治时间 2038年1月19日 凌晨 03:14:07YYYYMMDD HHMMSS混合日期和时间值，时间戳字符串类型类型大小用途CHAR0-255 bytes定长字符串VARCHAR0-65535 bytes变长字符串TINYBLOB0-255 bytes不超过 255 个字符的二进制字符串TINYTEXT0-255 bytes短文本字符串BLOB0-65 535 bytes二进制形式的长文本数据TEXT0-65 535 bytes长文本数据MEDIUMBLOB0-16 777 215 bytes二进制形式的中等长度文本数据MEDIUMTEXT0-16 777 215 bytes中等长度文本数据LONGBLOB0-4 294 967 295 bytes二进制形式的极大文本数据LONGTEXT0-4 294 967 295 bytes极大文本数据语法CREATECREATE 用于创建数据库或数据表 ...

对于InnoDB存储引擎来说，在单个页中查找某条记录分为两种情况：以主键为搜索条件，可以使用Page Directory通过二分法快速定位相应的用户记录。以其他列为搜索条件，需要按照记录组成的单链表依次遍历各条记录。没有索引的情况下，不论是以主键还是其他列作为搜索条件，只能沿着页的双链表从左到右依次遍历各个页。InnoDB存储引擎的索引是一棵B+树，完整的用户记录都存储在B+树第0层的叶子节点，其他层次的节点都属于内节点，内节点里存储的是目录项记录。InnoDB的索引分为两大种：聚簇索引以主键值的大小为页和记录的排序规则，在叶子节点处存储的记录包含了表中所有的列。 二级索引以自定义的列的大小为页和记录的排序规则，在叶子节点处存储的记录内容是列 + 主键。 MyISAM存储引擎的数据和索引分开存储，这种存储引擎的索引全部都是二级索引，在叶子节点处存储的是列 + 页号。

页是MySQL管理存储空间的基本单位，一个页的大小一般是16KB，记录是被存放在页中的，如果记录占用的空间太大还可能造成行溢出现象，这会导致一条记录被分散存储在多个页中。 页的本质就是一块16KB大小的存储空间，InnoDB为了不同的目的而把页分为不同的类型，其中用于存放记录的页也称为数据页，我们先看看这个用于存放记录的页长什么样。数据页代表的这块16KB大小的存储空间可以被划分为多个部分，不同部分有不同的功能，各个部分如图所示： 记录在页中的存储在页的7个组成部分中，存储的记录会按照指定的行格式存储到User Records部分。但是在一开始生成页的时候，其实并没有User Records这个部分，每当插入一条记录，都会从Free Space部分，也就是尚未使用的存储空间中申请一个记录大小的空间划分到User Records部分，当Free Space部分的空间全部被User Records部分替代掉之后，也就意味着这个页使用完了，如果还有新的记录插入的话，就需要去申请新的页了，这个过程的图示如下： 记录头信息的秘密 delete_mask 这个属性标记着当前记录是否被删除，占用1个二进制位，值为0的时候代表记录并没有被删除，为1的时候代表记录被删除掉了。 这些被删除的记录之所以不立即从磁盘上移除，是因为移除它们之后把其他的记录在磁盘上重新排列需要性能消耗，所以只是打个删除 标记而已，而且这部分存储空间之后还可以重用，也就是说之后如果有新记录插入到表中的话，可能把这些被删除的记录占用的存储空 间覆盖掉。`optimize table 表名;` 执行这个命令后服务器会重新规划表中记录的存储方式，把被标记为删除的记录从磁盘上移除。 min_rec_mask 这个属性标记该记录是否为B+树的非叶子节点中的最小记录。 n_owned 这个暂时保密，稍后它是主角～ heap_no 这个属性表示当前记录在本页中的位置。InnoDB自动给每个页里加了两个记录，由于这两个记录并不是自己插入的，所以有时候也称为伪记录或者虚拟记录。这两个伪记录一个代表最小记录，一个代表最大记录。对于一条完整的记录来说，比较记录的大小就是比较主键的大小。比方说插入的4行记录的主键值分别是：1、2、3、4，这也就意味着这4条记录的大小依次递增。 但是不管我们向页中插入了多少自己的记录，InnoDB都定义的两条伪记录分别为最小记录与最大记录。这两条记录的构造十分简单，都是由5字节大小的记录头信息和8字节大小的一个固定的部分组成的 由于这两条记录不是我们自己定义的记录，所以它们并不存放在页的User Records部分，他们被单独放在一个称为Infimum + Supremum的部分，如图所示： 从图中我们可以看出来，最小记录和最大记录的heap_no值分别是0和1，也就是说它们的位置最靠前。 record_type 这个属性表示当前记录的类型，一共有4种类型的记录，0表示普通记录，1表示B+树非叶节点记录，2表示最小记录，3表示最大记录。从图中可以看出来，自己插入的记录就是普通记录，它们的record_type值都是0，而最小记录和最大记录的record_type值分别为2和3。至于record_type为1的情况，索引的时候会重点强调。 `next_record 这个非常重要，它表示从当前记录的真实数据到下一条记录的真实数据的地址偏移量。比方说第一条记录的next_record值为36，意味着从第一条记录的真实数据的地址处向后找36个字节便是下一条记录的真实数据。这其实是个链表，可以通过一条记录找到它的下一条记录。但是需要注意注意再注意的一点是，下一条记录指得并不是按照插入顺序的下一条记录，而是按照主键值由小到大的顺序的下一条记录。而且规定 **_最小记录_** 的下一条记录就本页中主键值最小的记录，而本页中主键值最大的记录的下一条记录就是 **_最大记录_** ，为了更形象的表示一下这个next_record起到的作用，用箭头来替代一下next_record`中的地址偏移量： 记录按照从小到大的顺序形成了一个单链表。最大记录的next_record的值为0，这也就是说最大记录是没有下一条记录了，它是这个单链表中的最后一个节点。如果从中删除掉一条记录，这个链表也是会跟着变化的，比如把第2条记录删掉： 从图中可以看出来，删除第2条记录前后主要发生了这些变化： 所以，不论我们怎么对页中的记录做增删改操作，InnoDB始终会维护一条记录的单链表，链表中的各个节点是按照主键值由小到大的顺序连接起来的。 第2条记录并没有从存储空间中移除，而是把该条记录的delete_mask值设置为1。 第2条记录的next_record值变为了0，意味着该记录没有下一条记录了。 第1条记录的next_record指向了第3条记录。最大记录的n_owned值从5变成了`4 主键值为2`的记录被我们删掉了，但是存储空间却没有回收 mysql> INSERT INTO page_demo VALUES(2, 200, 'bbbb');存储情况： 从图中可以看到，InnoDB并没有因为新记录的插入而为它申请新的存储空间，而是直接复用了原来被删除记录的存储空间。 页目录     记录在页中按照主键值由小到大顺序串联成一个单链表，比如查询语句：SELECT * FROM ... WHERE c = 3; 从最小记录开始，沿着链表一直往后找，总会找到（或者找不到），在找的时候还能投机取巧，因为链表中各个记录的值是按照从小到大顺序排列的，所以当链表的某个节点代表的记录的主键值大于你想要查找的主键值时，就可以停止查找了，因为该节点后边的节点的主键值依次递增。 从一本书中查找某个内容的时候，一般会先看目录，找到需要查找的内容对应的书的页码，然后到对应的页码查看内容。InnoDB为记录也制作了一个类似的目录，制作过程是这样的：     将所有正常的记录（包括最大和最小记录，不包括标记为已删除的记录）划分为几个组。     每个组的最后一条记录的头信息中的n_owned属性表示该组内共有几条记录。     将每个组的最后一条记录的地址偏移量按顺序存储起来，每个地址偏移量也被称为一个槽（英文名：Slot）。     这些地址偏移量都会被存储到靠近页的尾部的地方，页中存储地址偏移量的部分也被称为Page Directory（可以看前边数据页的组成示意图）。比方说现在的表中正常的记录共有6条，InnoDB会把它们分成两组，第一组中只有一个最小记录，第二组中是剩余的5条记录，看下边的示意图： ...

id列数字越大越先执行，如果说数字一样大，那么就从上往下依次执行，id列为null的就表是这是一个结果集，不需要使用它来进行查询。 select_type列常见的有： A：simple：表示不需要union操作或者不包含子查询的简单select查询。有连接查询时，外层的查询为simple，且只有一个 B：primary：一个需要union操作或者含有子查询的select，位于最外层的单位查询的select_type即为primary。且只有一个 C：union：union连接的两个select查询，第一个查询是dervied派生表，除了第一个表外，第二个以后的表select_type都是union D：dependent union：与union一样，出现在union 或union all语句中，但是这个查询要受到外部查询的影响 E：union result：包含union的结果集，在union和union all语句中,因为它不需要参与查询，所以id字段为null F：subquery：除了from字句中包含的子查询外，其他地方出现的子查询都可能是subquery G：dependent subquery：与dependent union类似，表示这个subquery的查询要受到外部表查询的影响 H：derived：from字句中出现的子查询，也叫做派生表，其他数据库中可能叫做内联视图或嵌套select table显示的查询表名，如果查询使用了别名，那么这里显示的是别名，如果不涉及对数据表的操作，那么这显示为null，如果显示为尖括号括起来的<derived N>就表示这个是临时表，后边的N就是执行计划中的id，表示结果来自于这个查询产生。如果是尖括号括起来的<union M,N>，与<derived N>类似，也是一个临时表，表示这个结果来自于union查询的id为M,N的结果集。 type依次从好到差：system，const，eq_ref，ref，fulltext，ref_or_null，unique_subquery，index_subquery，range，index_merge，index，ALL，除了all之外，其他的type都可以使用到索引，除了index_merge之外，其他的type只可以用到一个索引 A：system：表中只有一行数据或者是空表，且只能用于myisam和memory表。如果是Innodb引擎表，type列在这个情况通常都是all或者index B：const：使用唯一索引或者主键，返回记录一定是1行记录的等值where条件时，通常type是const。其他数据库也叫做唯一索引扫描 C：eq_ref：出现在要连接过个表的查询计划中，驱动表只返回一行数据，且这行数据是第二个表的主键或者唯一索引，且必须为not null，唯一索引和主键是多列时，只有所有的列都用作比较时才会出现eq_ref D：ref：不像eq_ref那样要求连接顺序，也没有主键和唯一索引的要求，只要使用相等条件检索时就可能出现，常见与辅助索引的等值查找。或者多列主键、唯一索引中，使用第一个列之外的列作为等值查找也会出现，总之，返回数据不唯一的等值查找就可能出现。 E：fulltext：全文索引检索，要注意，全文索引的优先级很高，若全文索引和普通索引同时存在时，mysql不管代价，优先选择使用全文索引 F：ref_or_null：与ref方法类似，只是增加了null值的比较。实际用的不多。 G：unique_subquery：用于where中的in形式子查询，子查询返回不重复值唯一值 H：index_subquery：用于in形式子查询使用到了辅助索引或者in常数列表，子查询可能返回重复值，可以使用索引将子查询去重。 I：range：索引范围扫描，常见于使用>,<,is null,between ,in ,like等运算符的查询中。 J：index_merge：表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引，官方排序这个在ref_or_null之后，但是实际上由于要读取所个索引，性能可能大部分时间都不如range K：index：索引全表扫描，把索引从头到尾扫一遍，常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组的查询。 L：Index Condition Pushdown (ICP)是MySQL 5.6 版本中的新特性，是一种在存储引擎层使用索引过滤数据的一种优化方式。 当关闭ICP时，index 仅仅是data access 的一种访问方式，存储引擎通过索引回表获取的数据会传递到MySQL Server 层进行where条件过滤。 当打开ICP时，如果部分where条件能使用索引中的字段，MySQL Server 会把这部分下推到引擎层，可以利用index过滤的where条件在存储引擎层进行数据过滤，而非将所有通过index access的结果传递到MySQL server层进行where过滤。 优化效果：ICP能减少引擎层访问基表的次数和MySQL Server 访问存储引擎的次数,减少io次数，提高查询语句性能 M：all：这个就是全表扫描数据文件，然后再在server层进行过滤返回符合要求的记录。 possible_keys查询可能使用到的索引都会在这里列出来 key查询真正使用到的索引，select_type为index_merge时，这里可能出现两个以上的索引，其他的select_type这里只会出现一个。 key_len用于处理查询的索引长度，如果是单列索引，那就整个索引长度算进去，如果是多列索引，那么查询不一定都能使用到所有的列，具体使用到了多少个列的索引，这里就会计算进去，没有使用到的列，这里不会计算进去。留意下这个列的值，算一下你的多列索引总长度就知道有没有使用到所有的列了。要注意，mysql的ICP特性使用到的索引不会计入其中。另外，key_len只计算where条件用到的索引长度，而排序和分组就算用到了索引，也不会计算到key_len中。 ref如果是使用的常数等值查询，这里会显示const，如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段，如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func rows这里是执行计划中估算的扫描行数，不是精确值 extra这个列可以显示的信息非常多，有几十种，常用的有 ...

本篇为理清字符集的续篇（上一篇：第06期：梳理 MySQL 字符集的相关概念），重点讲述字符集涉及到的 sql 语句用法。一、character introducer翻译过来就是字符引导。也就是针对字符串，显式的给定一个字符编码和排序规则，不受系统参数的影响。 语法很简单： [_charset_name]'string' [COLLATE collation_name] 示例：字符串 "北京加油❤!" -- 字符集 utf8mb4，排序规则 utf8mb4_binselect _utf8mb4 "北京加油❤!" collate utf8mb4_bin as result;+------------------+| result |+------------------+| 北京加油❤! |+------------------+1 row in set (0.00 sec)-- 字符集 utf8mb4,collate 字句缺失，此时对应排序规则为utf8mb4_w0900_ai_ciselect _utf8mb4 "北京加油❤!" as result;+------------------+| result |+------------------+| 北京加油❤! |+------------------+1 row in set (0.00 sec)-- 字符集缺失，此时字符集按照参数 @@character_set_connection 值来指定。mysql> select "北京加油❤!" collate gb18030_chinese_ci as result;ERROR 1253 (42000): COLLATION 'gb18030_chinese_ci' is not valid for CHARACTER SET 'utf8mb4'-- 查看变量 @@character_set_connection，确认其字符集不包含排序规则 gb18030_chinese_ci，所以以上语句报错。mysql> select @@character_set_connection;+----------------------------+| @@character_set_connection |+----------------------------+| utf8mb4 |+----------------------------+1 row in set (0.00 sec)-- 那给下正确的排序规则 utf8mb4_bin，执行正确。mysql> select "北京加油❤!" collate utf8mb4_bin as result;+------------------+| result |+------------------+| 北京加油❤! |+------------------+1 row in set (0.00 sec)-- 字符集和排序规则都不指定，此时字符串对应的字符集和排序规则和参数 @@character_set_connection 一致。select "北京加油❤!" as result;-- 那这条语句其实被 MySQL 解释为select _utf8mb4 "北京加油❤!" collate utf8mb4_0900_ai_ci as result;总结 Introducer 使用规则：字符集排序规则字符串确认规则指定指定按照指定确认指定缺失按照指定字符集对应的默认排序规则确认缺失指定按照 @@character_set_connection 的值确认，排序规则与字符集不匹配则报错缺失缺失按照 @@character_set_connection 的值确认二、字符集转换函数1. convert 函数convert 函数类似于 introducer，不过只能指定字符集。 ...

行级锁是 MySQL 中锁定粒度最细的一种锁，行级锁能大大减少数据库操作的冲突，行级锁分为共享锁和排他锁两种。 共享锁（Share Lock）共享锁又称读锁，是读取操作创建的锁。其他用户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁。 如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不能加排他锁。获准共享锁的事务只能读数据，不能修改数据。 用法 SELECT ... LOCK IN SHARE MODE; 在查询语句后面增加LOCK IN SHARE MODE，MySQL 就会对查询结果中的每行都加共享锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请共享锁，否则会被阻塞。其他线程也可以读取使用了共享锁的表，而且这些线程读取的是同一个版本的数据。 排他锁（Exclusive Lock）排他锁又称写锁、独占锁，如果事务T对数据A加上排他锁后，则其他事务不能再对A加任何类型的封锁。获准排他锁的事务既能读数据，又能修改数据。 用法 SELECT ... FOR UPDATE; 在查询语句后面增加FOR UPDATE，MySQL 就会对查询结果中的每行都加排他锁，当没有其他线程对查询结果集中的任何一行使用排他锁时，可以成功申请排他锁，否则会被阻塞。 意向锁（Intention Lock）意向锁是表级锁，其设计目的主要是为了在一个事务中揭示下一行将要被请求锁的类型。InnoDB 中的两个表锁： 意向共享锁（IS）：表示事务准备给数据行加入共享锁，也就是说一个数据行加共享锁前必须先取得该表的IS锁；意向排他锁（IX）：类似上面，表示事务准备给数据行加入排他锁，说明事务在一个数据行加排他锁前必须先取得该表的IX锁。意向锁是 InnoDB 自动加的，不需要用户干预。 对于INSERT、UPDATE和DELETE，InnoDB 会自动给涉及的数据加排他锁；对于一般的SELECT语句，InnoDB 不会加任何锁，事务可以通过以下语句显式加共享锁或排他锁。 共享锁：SELECT ... LOCK IN SHARE MODE; 排他锁：SELECT ... FOR UPDATE;

如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。 MySQL锁概述相对其他数据库而言，MySQL的锁机制比较简单，其最显著的特点是不同的存储引擎支持不同的锁机制。     比如 MyISAM和MEMORY存储引擎采用的是表级锁（table-level locking）。 InnoDB存储引擎既支持行级锁（row-level locking），也支持表级锁，但默认情况下是采用行级锁。 MySQL这3种锁的特性可大致归纳如下 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。 仅从锁的角度来说：表级锁更适合于以查询为主，只有少量按索引条件更新数据的应用，如Web应用；而行级锁则更适合于有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理（OLTP）系统。下面几节我们重点介绍MySQL表锁和 InnoDB行锁的问题。 MyISAM表锁MyISAM存储引擎只支持表锁，这也是MySQL开始几个版本中唯一支持的锁类型。随着应用对事务完整性和并发性要求的不断提高，MySQL才开始开发基于事务的存储引擎，后来慢慢出现了支持页锁的BDB存储引擎和支持行锁的InnoDB存储引擎（实际 InnoDB是单独的一个公司，现在已经被Oracle公司收购）。但是MyISAM的表锁依然是使用最为广泛的锁类型。本节将详细介绍MyISAM表锁的使用。 查询表级锁争用情况 可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定争夺： mysql> show status like 'table%'; | Variable_name         | Value | | Table_locks_immediate | 2979  | | Table_locks_waited    | 0     | 2 rows in set (0.00 sec)) 如果Table_locks_waited的值比较高，则说明存在着较严重的表级锁争用情况。 MySQL表级锁的锁模式 MySQL的表级锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。 MyISAM表的读操作，不会阻塞其他用户对同一表的读请求，但会阻塞对同一表的写请求；     MyISAM表的写操作，则会阻塞其他用户对同一表的读和写操作；     MyISAM表的读操作与写操作之间，以及写操作之间是串行的；     当一个线程获得对一个表的写锁后，只有持有锁的线程可以对表进行更新操作。其他线程的读、写操作都会等待，直到锁被释放为止。 获得表film_text的WRITE锁定 mysql> lock table film_text write; Query OK, 0 rows affected (0.00 sec) ...

作者：赵黎明爱可生 MySQL DBA 团队成员，Oracle 10g OCM，MySQL 5.7 OCP，擅长数据库性能问题诊断、事务与锁问题的分析等，负责处理客户 MySQL 及我司自研 DMP 平台日常运维中的问题，对开源数据库相关技术非常感兴趣。本文来源：原创投稿*爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。什么是半一致性读先看下官方的描述： 是一种用在 Update 语句中的读操作（一致性读）的优化，是在 RC 事务隔离级别下与一致性读的结合。当 Update 语句的 where 条件中匹配到的记录已经上锁，会再次去 InnoDB 引擎层读取对应的行记录，判断是否真的需要上锁（第一次需要由 InnoDB 先返回一个最新的已提交版本）。只在 RC 事务隔离级别下或者是设置了 innodb_locks_unsafe_for_binlog=1 的情况下才会发生。innodb_locks_unsafe_for_binlog 参数在 8.0 版本中已被去除（可见，这是一个可能会导致数据不一致的参数，官方也不建议使用了）。测试案例InnoDB 引擎的强大之处就在于它能完美地支持事务，而事务的一致性则是由事务隔离级别和并发事务锁来保证的。接下来，我们先通过 2 个测试案例来观察半一致性读会对事务产生哪些影响。 案例 1RC 隔离级别，3 个 Session 执行事务语句-- 创建测试表root@localhost:mysqld.sock[zlm] <5.7.30-log>create table zlm.t(id int,sal int) engine innodb default character set utf8mb4;Query OK, 0 rows affected (0.06 sec)root@localhost:mysqld.sock[zlm] <5.7.30-log>show create table zlm.t\G*************************** 1. row *************************** Table: tCreate Table: CREATE TABLE `t` ( `id` int(11) DEFAULT NULL, `sal` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8mb41 row in set (0.01 sec)-- 写入测试数据（创建插入数据的存储过程）root@localhost:mysqld.sock[zlm] <5.7.30-log>drop procedure if exists zlm.proc_t;Query OK, 0 rows affected (0.00 sec)root@localhost:mysqld.sock[zlm] <5.7.30-log>delimiter $$root@localhost:mysqld.sock[zlm] <5.7.30-log>create procedure zlm.proc_t() -> begin -> declare i int default 1; -> declare j int default 100; -> while i<11 do -> insert into t(id,sal) values(i,j); -> set i=i+1; -> set j=j+100; -> end while; -> end $$Query OK, 0 rows affected (0.01 sec)root@localhost:mysqld.sock[zlm] <5.7.30-log>delimiter ;root@localhost:mysqld.sock[zlm] <5.7.30-log>select * from t;+------+------+| id | sal |+------+------+| 1 | 100 || 2 | 200 || 3 | 300 || 4 | 400 || 5 | 500 || 6 | 600 || 7 | 700 || 8 | 800 || 9 | 900 || 10 | 1000 |+------+------+10 rows in set (0.00 sec)-- 开启RC隔离级别root@localhost:mysqld.sock[zlm] <5.7.30-log>set @@global.tx_isolation='read-committed';Query OK, 0 rows affected, 1 warning (0.00 sec)注意，从8.0.3版本开始，去掉了tx_isolation参数，参数名只支持transaction_isolation ...

前言： 在学习和使用MySQL的过程中，难免会遇到各种问题。不知道当你遇到相关问题时会怎么做，我在工作或写文章的过程中，遇到不懂或需要求证的问题时通常会去查阅官方文档。慢慢的，阅读文档也有了一些经验，本篇文章将手把手教你阅读MySQL官方文档，希望可以给到各位一点小收获。 1.初识官方文档如果你还没有仔细的看过MySQL官方文档，可以跟着我一步步体验下哦。首先打开参考文档首页，这里以5.7版本为例，首页地址：https://dev.mysql.com/doc/refman/5.7/en/ 首页左侧边是目录，目录上方有个搜索框，首页右上角可以切换版本，比如你想看8.0版本的话可以切换到对应版本。接下来我们认识下目录内容，从侧边目录栏可以看出参考手册按照分类分为不同章节，建议大家首先阅读下第一章节General Information，该章节的主要内容如下： 关于该参考手册的使用方法及适用版本。介绍MySQL的历史及数据库系统概述。列出5.7版本新增的功能。介绍5.7版本新增及不建议使用的一些参数变量。可以看出，第一章节从整体上介绍了该版本MySQL相关的内容，读读第一章有助于我们更好的使用此文档。下面目录中比较重要的章节有安装和升级、MySQL服务管理、备份与恢复、优化、字段类型、SQL语句、InnoDB存储引擎、主从复制等。至此，我们大概认识了MySQL官方文档，下面继续看下该如何使用。 2.读活的文档一般情况下，我们都是遇到问题或者有想知道的知识时，会去翻阅官方文档。此时我们是带着目标去阅读的，这种情况下应该速战速决。如果你对该文档目录很熟悉，那么可以很快找到相关内容，如果你对文档不熟悉，这时候要善用搜索，比如你想了解如何创建索引，在搜索框输入index，然后即可搜索出索引相关章节。 有的同学可能说了，这文档都是英文也看不太懂啊。其实我想说，参考文档中的英文大多是浅显易懂的，通读一遍能大概知道什么意思，实在看不懂可以借助谷歌翻译或其他插件进行翻译。推荐大家开两个相同的标签页，一个英文另一个借助浏览器翻译成中文，这样可以两边对照着阅读。 我们应该读“活”的文档，阅读文档要灵活运用，推荐边阅读边测试。同时也要读全面，有的参数变量在不同版本可能表现不同。不建议毫无目的的去阅读文档，推荐有计划的阅读，比如说你想了解InnoDB引擎，那么你可以根据自己的时间安排阅读InnoDB存储引擎这一章节。 3.几个小技巧除了上面善用搜索框搜索的技巧外，下面再分享几个小技巧。不知道你有没有遇到过这种情况，就是某个语法你以前用过，但这次写着写着却发现想不起来了。此时你可以去网上搜索或翻阅官方文档，但更简便的做法是利用命令行自带的帮助文档，比如说你想了解create database参数，直接在MySQL命令行输入 ? create database 即可，命令行会自动输出相关内容并且给出文档链接，是不是方便了很多。 mysql> ? create database Name: 'CREATE DATABASE'Description:Syntax:CREATE {DATABASE | SCHEMA} [IF NOT EXISTS] db_name [create_specification] ...create_specification: [DEFAULT] CHARACTER SET [=] charset_name | [DEFAULT] COLLATE [=] collation_nameCREATE DATABASE creates a database with the given name. To use thisstatement, you need the CREATE privilege for the database. CREATESCHEMA is a synonym for CREATE DATABASE.URL: http://dev.mysql.com/doc/refman/5.7/en/create-database.htmlMySQL文档中有很多语句示例，其中也有些规范，比如说以上面create database语句为例，中括号[]中的内容可以省略，花括号{}中的内容是任选其一的，不同选项以竖线|分隔。了解这些有助于我们快速明白相关语句用法。 ...

问题业务上有一张回传状态记录数据，随着业务及时间的发展，这张表的数据量达1400W条数;应需求做归档操作；应数据量大使用delete操作效率低，不现实；方案操作记录如下： ##锁表---->备份新数据---->重命名表；----锁表LOCK TABLES node_flow read local; 将当前表设置为只读，不能进行插入或更新操作。UNLOCK TABLES;锁住表了，使用UNLOCK进行释放。----备份数据INSERT INTO node_flow_copy SELECT * FROM node_flow WHERE create_time > '2020-05-01'----重命名表ALTER TABLE node_flow RENAME TO node_flow_20200603;RENAME TABLE node_flow_copy TO node_flow;因表数据比较大，在执行INSERT语句时出现报错；"ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction"的问题。 ###解决； 1）在执行insert操作时,运行的进程被卡住，分析原因； ----查看数据库进程列表； Show processlist； kill id; 操作发现command显示killed，sql被阻塞了；----查看数据库锁及锁等待；select * from information_schema.innodb_locks;select * from information_schema.innodb_lock_waits;----查看数据进程 及锁、触发器show processlist;show engine innodb status\G;Show triggers from DB_NAME;-----查看系异常统进程；Iotopperf top -p `pidof mysqld`2）确定调整innodb内存使用大小解决卡住问题 ----查看数据库内存大小的问题show variables like 'innodb_buffer_pool%';发现innodb_buffer_pool_size只有5M，感觉原因就是因为buffer pool过小，增加buffer\_pool的大小到20G select 20\*1024\*1024\*1024; set global innodb\_buffer\_pool\_size=21474836480; 再使用show processlist;等待一下killd状态不在了；

mysql日志分割大致有三种方案 定时把日志重命名为历史文件，然后通过mysqladmin 执行flush-logs将日志文件描述符定位到新的文件;通过命令定时的更换日志文件的路径set global slow_query_log_file=;使用logrotate.两种都要通过mysql的账号密码去登录mysql,第二种不用mv文件,所以我认为第二种方式更优雅一些.以下是我通过第二种方式进行日志分割的脚本： #!/bin/bashtime=`date "+%Y-%m-%d"`host="127.0.0.1"user="root"passwd="******"mysql -h$host -u$user -p$passwd -e "set global slow_query_log_file='/data/mysql_data/logs/slow-$time.log';"

字段类型长度latin1gbkUTF8允许为null不允许为nullkey_lencharLk=1k=2k=3n=1n=0Lk+1nvarcharLk=1k=2k=3n=1n=0Lk+1n+2tinyint1 n=1n=01+1*nsmallint2 n=1n=02+1*nmediumint3 n=1n=03+1*nint4 n=1n=04+1*nbigint8 n=1n=08+1*ndatetime(mysql5.5及以前)8 n=1n=08+1*ndatetime(mysql5.6及以后)5 n=1n=05+1*ndate3 n=1n=03+1*ntime3 n=1n=03+1*nyear1 n=1n=01+1*ntimestamp4 n=1n=04+1*n1.整数类型，浮点数类型，时间类型的索引长度NOT NULL=字段本身的字段长度NULL=字段本身的字段长度+1，因为需要有是否为空的标记，这个标记需要占用1个字节datetime类型在5.6中字段长度是5个字节2.字符类型varchr(n)变长字段且允许NULL = n * ( utf8=3,gbk=2,latin1=1)+1(NULL)+2varchr(n)变长字段且不允许NULL = n * ( utf8=3,gbk=2,latin1=1)+2char(n)固定字段且允许NULL = n * ( utf8=3,gbk=2,latin1=1)+1(NULL)char(n)固定字段且允许NULL = n * ( utf8=3,gbk=2,latin1=1)变长字段需要额外的2个字节（VARCHAR值保存时只保存需要的字符数，另加一个字节来记录长度(如果列声明的长度超过255，则使用两个字节)，所以VARCAHR索引长度计算时候要加2），固定长度字段不需要额外的字节。而null都需要1个字节的额外空间,所以索引字段最好不要为NULL，因为NULL让统计更加复杂，并且需要额外的存储空间。这个结论在此得到了证实，复合索引有最左前缀的特性，如果复合索引能全部使用上，则是复合索引字段的索引长度之和，这也可以用来判定复合索引是否部分使用，还是全部使用。

定义和用法FROM_UNIXTIME - 返回UNIX时间戳的日期形式。 语法FROM_UNIXTIME(unix_timestamp)，FROM_UNIXTIME(unix_timestamp,format)返回unix_timestamp参数的表示形式，其值为“YYYY-MM-DD HH：MM：SS”或YYYYMMDDHHMMSS格式，具体取决于该函数是在字符串还是在数字上下文中使用。 该值以当前时区表示。 unix_timestamp是内部时间戳值，例如UNIX_TIMESTAMP()函数产生的值。如果指定了format，则将根据格式字符串对结果进行格式化，该格式字符串的使用方式与DATE_FORMAT()函数的条目中列出的方式相同。 示例mysql> SELECT FROM_UNIXTIME(875996580);+---------------------------------------------------------+| FROM_UNIXTIME(875996580) |+---------------------------------------------------------+| 1997-10-04 22:23:00 |+---------------------------------------------------------+1 row in set (0.00 sec)相关资料MySQL 日期和时间函数

定义和用法LENGTH - 返回字符串长度。 语法LENGTH(str)返回字符串str的长度，以字节为单位。 多字节字符计为多个字节。 这意味着对于包含五个两个字节的字符串，LENGTH()返回10，而CHAR_LENGTH()返回5。 示例mysql> SELECT LENGTH('text');+---------------------------------------------------------+| LENGTH('text') |+---------------------------------------------------------+| 4 |+---------------------------------------------------------+1 row in set (0.00 sec)相关资料MySQL 字符串函数

Mysql锁机制简介一、 锁分类（按照锁的粒度分类）Mysql为了解决并发、数据安全的问题，使用了锁机制。 可以按照锁的粒度把数据库锁分为表级锁和行级锁。 表级锁Mysql中锁定 粒度最大 的一种锁，对当前操作的整张表加锁，实现简单 ，资源消耗也比较少，加锁快，不会出现死锁 。其锁定粒度最大，触发锁冲突的概率最高，并发度最低，MyISAM和 InnoDB引擎都支持表级锁。 行级锁 Mysql中锁定 粒度最小 的一种锁，只针对当前操作的行进行加锁。 行级锁能大大减少数据库操作的冲突。其加锁粒度最小，并发度高，但加锁的开销也最大，加锁慢，会出现死锁。 InnoDB支持的行级锁，包括如下几种。 Record Lock：对索引项加锁，锁定符合条件的行。其他事务不能修改和删除加锁项；Gap Lock： 对索引项之间的“间隙”加锁，锁定记录的范围（对第一条记录前的间隙或最后一条将记录后的间隙加锁），不包含索引项本身。其他事务不能在锁范围内插入数据，这样就防止了别的事务新增幻影行。Next-key Lock： 锁定索引项本身和索引范围。即Record Lock和Gap Lock的结合。可解决幻读问题。虽然使用行级索具有粒度小、并发度高等特点，但是表级锁有时候也是非常必要的： 事务更新大表中的大部分数据直接使用表级锁效率更高；事务比较复杂，使用行级索很可能引起死锁导致回滚。二 、锁分类（按照是否可写分类）表级锁和行级锁可以进一步划分为共享锁（s）和排他锁（X）。 共享锁（s）:共享锁（Share Locks，简记为S）又被称为读锁，其他用户可以并发读取数据，但任何事务都不能获取数据上的排他锁，直到已释放所有共享锁。 共享锁(S锁)又称为读锁，若事务T对数据对象A加上S锁，则事务T只能读A；其他事务只能再对A加S锁，而不能加X锁，直到T释放A上的S锁。这就保证了其他事务可以读A，但在T释放A上的S锁之前不能对A做任何修改。 排他锁（X）：排它锁（(Exclusive lock,简记为X锁)）又称为写锁，若事务T对数据对象A加上X锁，则只允许T读取和修改A，其它任何事务都不能再对A加任何类型的锁，直到T释放A上的锁。它防止任何其它事务获取资源上的锁，直到在事务的末尾将资源上的原始锁释放为止。在更新操作(INSERT、UPDATE 或 DELETE)过程中始终应用排它锁。 两者之间的区别： 共享锁（S锁）：如果事务T对数据A加上共享锁后，则其他事务只能对A再加共享锁，不 能加排他锁。获取共享锁的事务只能读数据，不能修改数据。排他锁（X锁）：如果事务T对数据A加上排他锁后，则其他事务不能再对A加任任何类型的封锁。获取排他锁的事务既能读数据，又能修改数据。三 、另外两个表级锁：IS和IX当一个事务需要给自己需要的某个资源加锁的时候，如果遇到一个共享锁正锁定着自己需要的资源的时候，自己可以再加一个共享锁，不过不能加排他锁。 但是，如果遇到自己需要锁定的资源已经被一个排他锁占有之后，则只能等待该锁定释放资源之后自己才能获取锁定资源并添加自己的锁定。 而意向锁的作用就是当一个事务在需要获取资源锁定的时候，如果遇到自己需要的资源已经被排他锁占用的时候，该事务可以需要锁定行的表上面添加一个合适的意向锁。 如果自己需要一个共享锁，那么就在表上面添加一个意向共享锁。而如果自己需要的是某行（或者某些行）上面添加一个排他锁的话，则先在表上面添加一个意向排他锁。意向共享锁可以同时并存多个，但是意向排他锁同时只能有一个存在。 InnoDB另外的两个表级锁： 意向共享锁（IS）： 表示事务准备给数据行记入共享锁，事务在一个数据行加共享锁前必须先取得该表的IS锁。意向排他锁（IX）： 表示事务准备给数据行加入排他锁，事务在一个数据行加排他锁前必须先取得该表的IX锁。注意： 这里的意向锁是表级锁，表示的是一种意向，仅仅表示事务正在读或写某一行记录，在真正加行锁时才会判断是否冲突。意向锁是InnoDB自动加的，不需要用户干预。IX，IS是表级锁，不会和行级的X，S锁发生冲突，只会和表级的X，S发生冲突。InnoDB的锁机制兼容情况如下： 首先把四个名字的含义理解了： X理解为独占一个表（exclusive）S理解为共享一个表（整个表共享读）IX理解为有意向写这张表的某一行（有意向独占某一行、先占一个坑）IS理解为有意向读这张表的某一行所以锁机制兼容情况可以表达为： X是独占整个表，当表中已经存在了X、S、IX、IS任意一种锁，X锁一定加不上了，因为无法满足独占。同理，表中已经存在X，其他锁都加不上了（必须独占）IX和S锁不兼容，IX是独占某一行，既然IX占领了某一行，那么就不能S整个表共享读了IX和IS兼容，某一行有意向独占和另一行有意向共享读是可以成立的IX和IX之间兼容，可以存在IX想要独占row_a，另一个IX想要独占row_b，只要a≠b即可成立整个兼容表其实是对称的 (行列顺序都按照 X、S、IX、IS)四、 死锁和避免死锁InnoDB的行级锁是基于索引实现的，如果查询语句为命中任何索引，那么InnoDB会使用表级锁。 此外，InnoDB的行级锁是针对索引加的锁，不针对数据记录，因此即使访问不同行的记录，如果使用了相同的索引键仍然会出现锁冲突，还需要注意的是，在通过 SELECT ...LOCK IN SHARE MODE;或 SELECT ...FOR UPDATE;使用锁的时候，如果表没有定义任何索引，那么InnoDB会创建一个隐藏的聚簇索引并使用这个索引来加记录锁。 此外，不同于MyISAM总是一次性获得所需的全部锁，InnoDB的锁是逐步获得的，当两个事务都需要获得对方持有的锁，导致双方都在等待，这就产生了死锁。 发生死锁后，InnoDB一般都可以检测到，并使一个事务释放锁回退，另一个则可以获取锁完成事务，我们可以采取以上方式避免死锁： 通过表级锁来减少死锁产生的概率；多个程序尽量约定以相同的顺序访问表（这也是解决并发理论中哲学家就餐问题的一种思路）；同一个事务尽可能做到一次锁定所需要的所有资源。五、 总结与补充MyISAM和InnoDB存储引擎使用的锁： MyISAM采用表级锁(table-level locking)。InnoDB支持行级锁(row-level locking)和表级锁,默认为行级锁。表级锁和行级锁对比： ...

转自 运维开发_西瓜甜https://www.jianshu.com/p/faf0127f1cb2最常见的集群方案 MySQL Replication主从复制（也称 AB 复制）允许将来自一个MySQL数据库服务器（主服务器）的数据复制到一个或多个MySQL数据库服务器（从服务器）。 复制是异步的 从站不需要永久连接以接收来自主站的更新。根据配置，您可以复制数据库中的所有数据库，所选数据库甚至选定的表。 MySQL中复制的优点包括： 横向扩展解决方案 - 在多个从站之间分配负载以提高性能。在此环境中，所有写入和更新都必须在主服务器上进行。但是，读取可以在一个或多个从设备上进行。该模型可以提高写入性能（因为主设备专用于更新），同时显着提高了越来越多的从设备的读取速度。数据安全性 - 因为数据被复制到从站，并且从站可以暂停复制过程，所以可以在从站上运行备份服务而不会破坏相应的主数据。分析 - 可以在主服务器上创建实时数据，而信息分析可以在从服务器上进行，而不会影响主服务器的性能。远程数据分发 - 您可以使用复制为远程站点创建数据的本地副本，而无需永久访问主服务器。Replication 的原理 前提是作为主服务器角色的数据库服务器必须开启二进制日志主服务器上面的任何修改都会通过自己的 I/O tread(I/O 线程)保存在二进制日志 Binary log 里面。从服务器上面也启动一个 I/O thread，通过配置好的用户名和密码, 连接到主服务器上面请求读取二进制日志，然后把读取到的二进制日志写到本地的一个Realy log（中继日志）里面。从服务器上面同时开启一个 SQL thread 定时检查 Realy log(这个文件也是二进制的)，如果发现有更新立即把更新的内容在本机的数据库上面执行一遍。每个从服务器都会收到主服务器二进制日志的全部内容的副本。 从服务器设备负责决定应该执行二进制日志中的哪些语句。 除非另行指定，否则主从二进制日志中的所有事件都在从站上执行。 如果需要，您可以将从服务器配置为仅处理一些特定数据库或表的事件。 重要: 您无法将主服务器配置为仅记录特定事件。 每个从站(从服务器)都会记录二进制日志坐标： 文件名文件中它已经从主站读取和处理的位置。由于每个从服务器都分别记录了自己当前处理二进制日志中的位置，因此可以断开从服务器的连接，重新连接然后恢复继续处理。 一主多从 如果一主多从的话，这时主库既要负责写又要负责为几个从库提供二进制日志。此时可以稍做调整，将二进制日志只给某一从，这一从再开启二进制日志并将自己的二进制日志再发给其它从。或者是干脆这个从不记录只负责将二进制日志转发给其它从，这样架构起来性能可能要好得多，而且数据之间的延时应该也稍微要好一些。工作原理图如下： image.png 关于二进制日志mysqld将数字扩展名附加到二进制日志基本名称以生成二进制日志文件名。每次服务器创建新日志文件时，该数字都会增加，从而创建一系列有序的文件。每次启动或刷新日志时，服务器都会在系列中创建一个新文件。服务器还会在当前日志大小达到max_binlog_size参数设置的大小后自动创建新的二进制日志文件 。二进制日志文件可能会比max_binlog_size使用大型事务时更大， 因为事务是以一个部分写入文件，而不是在文件之间分割。 为了跟踪已使用的二进制日志文件， mysqld还创建了一个二进制日志索引文件，其中包含所有使用的二进制日志文件的名称。默认情况下，它具有与二进制日志文件相同的基本名称，并带有扩展名'.index'。在mysqld运行时，您不应手动编辑此文件。 术语二进制日志文件通常表示包含数据库事件的单个编号文件。 术语 二进制日志 表示含编号的二进制日志文件集加上索引文件。 SUPER 权限的用户可以使用SET sql_log_bin=0语句禁用其当前环境下自己的语句的二进制日志记录 配置 Replication配置步骤：在主服务器上，您必须启用二进制日志记录并配置唯一的服务器ID。需要重启服务器。编辑主服务器的配置文件 my.cnf，添加如下内容 [mysqld]log-bin=/var/log/mysql/mysql-binserver-id=1创建日志目录并赋予权限 shell> mkdir /var/log/mysqlshell> chown mysql.mysql /var/log/mysql重启服务 ...

作者：杨涛涛资深数据库专家，专研 MySQL 十余年。擅长 MySQL、PostgreSQL、MongoDB 等开源数据库相关的备份恢复、SQL 调优、监控运维、高可用架构设计等。目前任职于爱可生，为各大运营商及银行金融企业提供 MySQL 相关技术支持、MySQL 相关课程培训等工作。本文来源：原创投稿*爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。最近客户咨询了我一个关于如何更改 MySQL 库名的问题。其实如何安全的更改数据库名，是个非常棘手的问题，特别是针对 MySQL 来数据库来说。今天梳理出来，供大家参考。 被取消的命令MySQL 之前提供了一个 rename database db_old to db_new 的命令来直接对数据库改名，可能由于实现的功能不完备（比如，这条命令可能是一个超大的事务，或者是由于之前的表很多还是 MyISAM 等），后来的版本直接取消了这条命令。更改数据库名大致上有以下几种方案： 一、mysqldump 导入导出要说最简单的方法，就是直接用 mysqldump 工具，在旧库导出再往新库导入（最原始、最慢、最容易想到）的方法：旧库 yttdb_old 导出（包含的对象：表、视图、触发器、事件、存储过程、存储函数） root@debian-ytt1:/home/ytt# time mysqldump --login-path=root_ytt --set-gtid-purged=off \> --single-transaction --routines --events yttdb_old > /tmp/yttdb_old.sqlreal 2m24.388suser 0m5.422ssys 0m1.120s新库 yttdb_new 导入 root@debian-ytt1:/home/ytt# time mysql --login-path=root_ytt -D yttdb_new < /tmp/yttdb_old.sqlreal 12m27.324suser 0m3.778ssys 0m0.947s以上结果是在我个人笔记本的虚拟机上测试，时间上花费了 12 分 27 秒，这里源库 yttdb_old 上的表个数为 2002，总共也就 826M，不到 1G，并且包含了视图，触发器，存储过程，存储函数，事件等都有。 ...

1.查询指定用户的所有最新的订单信息 数据库表结构iduser_idorder_statusupdate_date0001adminORDER_BUILD2020-06-010002adminORDER_PAYED2020-06-020003adminORDER_DELEVERY2020-06-030004rootORDER_BUILD2020-06-010005adminORDER_ARRIVEL2020-06-04 查询语句select * from (     select * from tc_order      where user_id = 'admin' ) p1 inner join(     select          id, user_id, order_status, max(update_date) UpdateDate    from tc_order      where          user_id = 'admin'     group by id ) p2      on p1.id = p2.id     and p1.update_date = p2.UpdateDate where      1 = 1 order by p1.update_date desc 查询结果iduser_idorder_statusupdate_date0004rootORDER_BUILD2020-06-010005adminORDER_ARRIVEL2020-06-04

在上一篇文章《MySQL常见加锁场景分析》中，我们聊到行锁是加在索引上的，但是复杂的 SQL 往往包含多个条件，涉及多个索引，找出 SQL 执行时使用了哪些索引对分析加锁场景至关重要。 比如下面这样的 SQL： mysql> delete from t1 where id = 1 or val = 1其中 id 和 val 都是索引，那么执行时使用到了哪些索引，加了哪些锁呢？为此，我们需要使用 explain 来获取 MySQL 执行这条 SQL 的执行计划。 什么是执行计划呢？简单来说，就是 SQL 在数据库中执行时的表现情况，通常用于 SQL 性能分析、优化和加锁分析等场景，执行过程会在 MySQL 查询过程中由解析器，预处理器和查询优化器共同生成。 MySQL 查询过程如果能搞清楚 MySQL 是如何优化和执行查询的，不仅对优化查询一定会有帮助，还可以通过分析使用到的索引来判断最终的加锁场景。 下图是MySQL执行一个查询的过程。实际上每一步都比想象中的复杂，尤其优化器，更复杂也更难理解。本文只给予简单的介绍。 MySQL查询过程如下： 客户端发送一条查询给服务器。服务器先检查查询缓存，如果命中了缓存，则立刻返回存储在缓存中的结果。否则进入下一阶段。服务器端进行SQL解析、预处理，再由优化器生成对应的执行计划。MySQL根据优化器生成的执行计划，再调用存储引擎的API来执行查询。将结果返回给客户端。执行计划MySQL会解析查询，并创建内部数据结构（解析树），并对其进行各种优化，包括重写查询、决定表的读取顺序、选择合适的索引等。 用户可通过关键字提示（hint）优化器，从而影响优化器的决策过程。也可以通过 explain 了解 数据库是如何进行优化决策的，并提供一个参考基准，便于用户重构查询和数据库表的 schema、修改数据库配置等，使查询尽可能高效。 下面，我们依次介绍 explain 中相关输出参数，并以实际例子解释这些参数的含义。 select_type查询数据的操作类型，有如下 simple 简单查询，不包含子查询或 union，如下图所示，就是最简单的查询语句。 primary 是 SQL 中包含复杂的子查询，此时最外层查询标记为该值。derived 是 SQL 中 from 子句中包含的子查询被标记为该值，MySQL 会递归执行这些子查询，把结果放在临时表。下图展示了上述两种类型。 ...

前言MySQL 为关系型数据库(Relational Database Management System)，一个关系型数据库由一个或数个表格组成, 表格一般包括以下： 表头(header): 每一列的名称;列(col): 具有相同数据类型的数据的集合;行(row): 每一行用来描述某个人/物的具体信息;值(value): 行的具体信息, 每个值必须与该列的数据类型相同;键(key): 表中用来识别某个特定的人物的方法, 键的值在当前列中具有唯一性。但是就是这些简简单单的表格，却衍生出了相当多的问题，尤其是随着互联网时代的发展，没得办法，为了用户体验更加流程，对于数据库的优化等问题成为了在面试的过程中被经常问起的话题，但是，道高一尺魔高一丈，程序猿会被屈服吗？不存在的 7张图（2xmind+5张知识），总结mysql从架构一直到使用和调优的相关知识点 平台原因，上传图片有点模糊，需要高清图的老铁，关注+转发，私信“资料”即可mysql架构 mysql数据结构 mysql索引系统 B+树添加和删除数据图解 红黑树 mysql的xmind图学习怎么能没有系统的梳理，程序猿梳理知识点靠什么，就是知识脑图，反正我是很喜欢这种方式，上面的图里面的知识，看起来没什么顺序的话，没关系，xmind图我已经为大家准备好了 因为展开后这张图实在太大了，所以就关闭了 mysql优化xmind图在一开始的时候，我也说了，常规的sql编写，其实在笔试中考察比较多，这个就是个人的一个技术能力的考察了，不是说能够取巧的，但是对于优化，更多的是理论的考察，那这些怎么办呢？关于mysql索引优化的相关知识图谱奉上 同样的。因为展开后这张图实在太大了，所以就截取了一部分 同样的，害怕有些老铁可能刚接触这些东西，看的不是特别清晰，那么针对这些图片中的知识，我也录制了这样的一套视频，我不信还会有人说学不明白 需要上面视频和知识图的老铁，关注公众号：Java架构师联盟，后台回复mysql即可

前言本文主要受众为开发人员,所以不涉及到MySQL的服务部署等操作,且内容较多,大家准备好耐心和瓜子矿泉水. 前一阵系统的学习了一下MySQL,也有一些实际操作经验,偶然看到一篇和MySQL相关的面试文章,发现其中的一些问题自己也回答不好,虽然知识点大部分都知道,但是无法将知识串联起来. 因此决定搞一个MySQL灵魂100问,试着用回答问题的方式,让自己对知识点的理解更加深入一点. 索引相关关于MySQL的索引,曾经进行过一次总结,文章链接在这里 Mysql索引原理及其优化. 1. 什么是索引?索引是一种数据结构,可以帮助我们快速的进行数据的查找. 2. 索引是个什么样的数据结构呢?索引的数据结构和具体存储引擎的实现有关, 在MySQL中使用较多的索引有Hash索引,B+树索引等,而我们经常使用的InnoDB存储引擎的默认索引实现为:B+树索引. 3. Hash索引和B+树所有有什么区别或者说优劣呢?首先要知道Hash索引和B+树索引的底层实现原理:hash索引底层就是hash表,进行查找时,调用一次hash函数就可以获取到相应的键值,之后进行回表查询获得实际数据.B+树底层实现是多路平衡查找树.对于每一次的查询都是从根节点出发,查找到叶子节点方可以获得所查键值,然后根据查询判断是否需要回表查询数据.那么可以看出他们有以下的不同: hash索引进行等值查询更快(一般情况下),但是却无法进行范围查询.因为在hash索引中经过hash函数建立索引之后,索引的顺序与原顺序无法保持一致,不能支持范围查询.而B+树的的所有节点皆遵循(左节点小于父节点,右节点大于父节点,多叉树也类似),天然支持范围. hash索引不支持使用索引进行排序,原理同上.hash索引不支持模糊查询以及多列索引的最左前缀匹配.原理也是因为hash函数的不可预测.AAAA和AAAAB的索引没有相关性.hash索引任何时候都避免不了回表查询数据,而B+树在符合某些条件(聚簇索引,覆盖索引等)的时候可以只通过索引完成查询.hash索引虽然在等值查询上较快,但是不稳定.性能不可预测,当某个键值存在大量重复的时候,发生hash碰撞,此时效率可能极差.而B+树的查询效率比较稳定,对于所有的查询都是从根节点到叶子节点,且树的高度较低.因此,在大多数情况下,直接选择B+树索引可以获得稳定且较好的查询速度.而不需要使用hash索引. 4. 上面提到了B+树在满足聚簇索引和覆盖索引的时候不需要回表查询数据,什么是聚簇索引?在B+树的索引中,叶子节点可能存储了当前的key值,也可能存储了当前的key值以及整行的数据,这就是聚簇索引和非聚簇索引. 在InnoDB中,只有主键索引是聚簇索引,如果没有主键,则挑选一个唯一键建立聚簇索引.如果没有唯一键,则隐式的生成一个键来建立聚簇索引.当查询使用聚簇索引时,在对应的叶子节点,可以获取到整行数据,因此不用再次进行回表查询. 5. 非聚簇索引一定会回表查询吗?不一定,这涉及到查询语句所要求的字段是否全部命中了索引,如果全部命中了索引,那么就不必再进行回表查询.举个简单的例子,假设我们在员工表的年龄上建立了索引,那么当进行select age from employee where age < 20的查询时,在索引的叶子节点上,已经包含了age信息,不会再次进行回表查询. 6. 在建立索引的时候,都有哪些需要考虑的因素呢?建立索引的时候一般要考虑到字段的使用频率,经常作为条件进行查询的字段比较适合.如果需要建立联合索引的话,还需要考虑联合索引中的顺序.此外也要考虑其他方面,比如防止过多的所有对表造成太大的压力.这些都和实际的表结构以及查询方式有关. 7. 联合索引是什么?为什么需要注意联合索引中的顺序?MySQL可以使用多个字段同时建立一个索引,叫做联合索引.在联合索引中,如果想要命中索引,需要按照建立索引时的字段顺序挨个使用,否则无法命中索引.具体原因为:MySQL使用索引时需要索引有序,假设现在建立了"name,age,school"的联合索引那么索引的排序为: 先按照name排序,如果name相同,则按照age排序,如果age的值也相等,则按照school进行排序.当进行查询时,此时索引仅仅按照name严格有序,因此必须首先使用name字段进行等值查询,之后对于匹配到的列而言,其按照age字段严格有序,此时可以使用age字段用做索引查找,以此类推.因此在建立联合索引的时候应该注意索引列的顺序,一般情况下,将查询需求频繁或者字段选择性高的列放在前面.此外可以根据特例的查询或者表结构进行单独的调整. 8. 创建的索引有没有被使用到?或者说怎么才可以知道这条语句运行很慢的原因?MySQL提供了explain命令来查看语句的执行计划,MySQL在执行某个语句之前,会将该语句过一遍查询优化器,之后会拿到对语句的分析,也就是执行计划,其中包含了许多信息. 可以通过其中和索引有关的信息来分析是否命中了索引,例如possilbe_key,key,key_len等字段,分别说明了此语句可能会使用的索引,实际使用的索引以及使用的索引长度. 9. 那么在哪些情况下会发生针对该列创建了索引但是在查询的时候并没有使用呢?使用不等于查询列参与了数学运算或者函数在字符串like时左边是通配符.类似于'%aaa'.当mysql分析全表扫描比使用索引快的时候不使用索引.当使用联合索引,前面一个条件为范围查询,后面的即使符合最左前缀原则,也无法使用索引.以上情况,MySQL无法使用索引. 事务相关1. 什么是事务?理解什么是事务最经典的就是转账的栗子,相信大家也都了解,这里就不再说一边了.事务是一系列的操作,他们要符合ACID特性.最常见的理解就是:事务中的操作要么全部成功,要么全部失败.但是只是这样还不够的. 2. ACID是什么?可以详细说一下吗?A=Atomicity原子性,就是上面说的,要么全部成功,要么全部失败.不可能只执行一部分操作. C=Consistency系统(数据库)总是从一个一致性的状态转移到另一个一致性的状态,不会存在中间状态. I=Isolation隔离性: 通常来说:一个事务在完全提交之前,对其他事务是不可见的.注意前面的通常来说加了红色,意味着有例外情况. D=Durability持久性,一旦事务提交,那么就永远是这样子了,哪怕系统崩溃也不会影响到这个事务的结果. 3. 同时有多个事务在进行会怎么样呢?多事务的并发进行一般会造成以下几个问题: 脏读: A事务读取到了B事务未提交的内容,而B事务后面进行了回滚.不可重复读: 当设置A事务只能读取B事务已经提交的部分,会造成在A事务内的两次查询,结果竟然不一样,因为在此期间B事务进行了提交操作.幻读: A事务读取了一个范围的内容,而同时B事务在此期间插入了一条数据.造成"幻觉".4. 怎么解决这些问题呢?MySQL的事务隔离级别了解吗?MySQL的四种隔离级别如下: 未提交读(READ UNCOMMITTED)这就是上面所说的例外情况了,这个隔离级别下,其他事务可以看到本事务没有提交的部分修改.因此会造成脏读的问题(读取到了其他事务未提交的部分,而之后该事务进行了回滚).这个级别的性能没有足够大的优势,但是又有很多的问题,因此很少使用. 已提交读(READ COMMITTED)其他事务只能读取到本事务已经提交的部分.这个隔离级别有 不可重复读的问题,在同一个事务内的两次读取,拿到的结果竟然不一样,因为另外一个事务对数据进行了修改. REPEATABLE READ(可重复读)可重复读隔离级别解决了上面不可重复读的问题(看名字也知道),但是仍然有一个新问题,就是幻读当你读取id> 10 的数据行时,对涉及到的所有行加上了读锁,此时例外一个事务新插入了一条id=11的数据,因为是新插入的,所以不会触发上面的锁的排斥那么进行本事务进行下一次的查询时会发现有一条id=11的数据,而上次的查询操作并没有获取到,再进行插入就会有主键冲突的问题. SERIALIZABLE(可串行化)这是最高的隔离级别,可以解决上面提到的所有问题,因为他强制将所以的操作串行执行,这会导致并发性能极速下降,因此也不是很常用. 5. Innodb使用的是哪种隔离级别呢?InnoDB默认使用的是可重复读隔离级别. 6. 对MySQL的锁了解吗?当数据库有并发事务的时候,可能会产生数据的不一致,这时候需要一些机制来保证访问的次序,锁机制就是这样的一个机制.就像酒店的房间,如果大家随意进出,就会出现多人抢夺同一个房间的情况,而在房间上装上锁,申请到钥匙的人才可以入住并且将房间锁起来,其他人只有等他使用完毕才可以再次使用. 7. MySQL都有哪些锁呢?像上面那样子进行锁定岂不是有点阻碍并发效率了?从锁的类别上来讲,有共享锁和排他锁.共享锁: 又叫做读锁. 当用户要进行数据的读取时,对数据加上共享锁.共享锁可以同时加上多个.排他锁: 又叫做写锁. 当用户要进行数据的写入时,对数据加上排他锁.排他锁只可以加一个,他和其他的排他锁,共享锁都相斥.用上面的例子来说就是用户的行为有两种,一种是来看房,多个用户一起看房是可以接受的. 一种是真正的入住一晚,在这期间,无论是想入住的还是想看房的都不可以.锁的粒度取决于具体的存储引擎,InnoDB实现了行级锁,页级锁,表级锁.他们的加锁开销从大大小,并发能力也是从大到小. ...

一、准备工作两台CentOS7服务器，安装好Mysql(CentOS 7安装MySQL)并设置静态IP，我这里两台服务器IP地址分别设置为192.168.0.150，192.168.0.151。150作为master,151作为slave。 在MySQL主上创建数据库users，在users下面创建表user,user表有两个字段id和name，并随意插入几条记录。 二、主从同步配置1、编辑MySQL主上的/etc/my.cnf vim /etc/my.cnf添加以下内容 log-bin=151_mysqlserver-id=1log-bin ：MySQL的bin-log的名字server-id : MySQL实例中全局唯一，并且大于0。 2、编辑MySQL从上的/etc/my.cnf server-id=2server-id : MySQL实例中全局唯一，并且大于0。与主上的 server-id 区分开。 3、在MySQL主上创建用于备份账号 mysql> CREATE USER 'repl'@'%' IDENTIFIED` WITH mysql_native_password BY 'password'; mysql> GRANT REPLICATION SLAVE ON *.* TO 'repl'@'%'; 4、MySQL主上加锁，阻止所有的写入操作 mysql> FLUSH TABLES WITH READ LOCK;5、MySQL主上，查看bin-log的文件名和位置 mysql> SHOW MASTER STATUS; 前面已经设置log-bin=151_mysql，这里File还是binlog.000001，所以需要刷新一下log mysql> flush logs;再次执行show master status，此时File变为我们前面设定好的150_mysql 6、MySQL主上dump所有数据，用于同步现有主上的数据。注意，前面主上已经锁表，因此我们不能在主上退出mysql客户端，需重新开启一个服务器的连接来执行以下命令。执行完毕后，会生成一个dbdump.db的文件。 mysqldump --all-databases --master-data > dbdump.db -uroot -p 7、将dbdump.db文件传送到从上。 scp dbdump.db root@192.168.0.151:~ 从上也有了dbdump文件 ...

此篇介绍 MySQL 字符集、排序规则、相关的元数据、参数等设置以及使用情况。概念字符集的内容包含：字符集（character set）和排序规则（collation rule）每种字符集可对应一到多个排序规则，每种排序规则对应一种字符集字符集是一套字符与一套编码的映射集合，像这样：字符编码A0B1……排序规则是字符集内用来比较每个字符的一套规则，也就是字符的排序方式比如要比较字符 A 和 B 的大小，最简单直观的方法就是对比他们对应的编码。显然编码 0 < 1，这种规则下 A < B。那么类似这样的规则集合就是排序规则。单字节字符编码如此，多字节的编码排序也以此类推。 那么接下来我来详细介绍下字符集相关的介绍以及使用场景。 一、字符编码的分类1、 ASCII用途：用来映射简单的单字节字符，比如大小写英文字母，阿拉伯数字，及常用的标点符、运算符、控制字符等。 编码范围： U+0000 - U+007F 注意：对于用这类字符的场景够用了，但是却无法表达比如汉字，日文等编码。 2、UNICODE用途：用来映射包含 ASCII 以内的其他的所有字符。 编码范围： U+0000 - U+10FFFF 注意：ASCII 是 UNICODE 的子集，ASCII 编码的字符可以无损转换为 UNICODE 编码的字符。 二、MySQL 常用字符集1、Latin1Latin1 是 cp1252 或者 ISO-8859-1 的别名。ISO-8859-1 编码是单字节编码，向下兼容 ASCII。 编码范围：U+0000 - U+00FF 范围字符内容说明U+0000 - U+007F单字节字符与 ASCII 完全一致U+0080 - U+009F控制字符 U+0080 - U+00FF文字符号 ISO-8859-1 收录的字符除 ASCII 收录的字符外，还包括西欧语言、希腊语、泰语、阿拉伯语、希伯来语对应的文字符号。 单字节内的空间都被 ISO-8859-1 编码占用，所以能够用 ISO-8859-1 编码存储、传输其他任何编码的字节流。 ...

该处不罗列出每种情况，以减法、加法、乘法为例，说明该情况：展示emp表： 1、减法： select empno,ename,sal-200 from emp;2、乘法： select empno,ename,sal*12 from emp;                               3、加法（不对空值做处理） select empno,sal+comm from emp;4、加法（对空值做处理） select empno,sal+ifnull(comm,0) from emp;                                                5、总结      a、查询时可以对字段做加减乘除的处理；但不改变数据库真实的数据，只是显示操作结果；      b、和null做四则运算为null，可以用 ifnull(字段名、指定null的默认值)，解决这个问题，如上面说的 ifnull(comm,0)。

扫码下面二维码关注公众号并回复100003获取下载资源本书目录结构如下:第1章 mysql体系结构和存储引擎.......................1 1.1 定义数据库和实例..............................11.2 mysql体系结构.................................31.3 mysql存储引擎.................................5 1.3.1 InnoDB存储引擎........................6 1.3.2 MyISAM存储引擎........................7 1.3.3 NDB存储引擎...........................7 1.3.4 Memory存储引擎........................8 1.3.5 Archive存储引擎.......................9 1.3.6 Federated.............................9 1.3.7 Maria存储引擎.........................9 1.3.8 其他存储引擎..........................91.4 各存储引擎之间的比较..........................101.5 连接MySQL.....................................13 1.5.1 TCP/IP................................13 1.5.2 命名管道和共享内存....................15 1.5.3 UNIX域套接字..........................151.6 小结..........................................15第2章 InnoDB存储引擎................................17 2.1 InnoDB存储引擎概述............................172.2 InnoDB存储引擎的版本..........................182.3 InnoDB体系结构................................19 2.3.1 后台线程..............................19 2.3.2 内存..................................222.4 Chekpoint技术.................................322.5 Master Thread工作方式.........................36 2.5.1 InnoDB1.0.x版本之前的Master Thread....36 2.5.2 InnoDB1.2.x版本之前的Master Thread....41 2.5.3 InnoDB1.2.x版本的Master Thread........452.6 InnoDB关键特性................................45 2.6.1 插入缓冲..............................46 2.6.2 两次写................................53 2.6.3 自适应哈希索引........................55 2.6.4 异步IO................................57 2.6.5 刷新邻接页............................582.7 启动关闭与恢复................................582.8 小结..........................................61第3章 文件 ...

一.插入数据insert into table_name (field1,field2,...,fieldN) values (value1,value2,...,valueN)如果数据是字符型，必须使用单引号或者双引号，如："value"。id字段在创建表的时候已经设置它为 AUTO_INCREMENT(自动增加) 属性，该字段会自动递增而不需要我们去设置，因此insert语句中没有提供id字段的数据值。 二.查询数据select column_name,column_name from table_name [where Clause] [limit N] [OFFSET M] 查询语句中你可以使用一个或者多个表，表之间使用逗号(,)分割，并使用WHERE语句来设定查询条件。SELECT 命令可以读取一条或者多条记录。可以使用星号(*)来代替其他字段，SELECT语句会返回表的所有字段数据可以使用 WHERE 语句来包含任何条件。可以使用 LIMIT 属性来设定返回的记录数。可以通过OFFSET指定SELECT语句开始查询的数据偏移量。默认情况下偏移量为0。 三.where子句如需有条件地从表中选取数据，可将 WHERE 子句添加到 SELECT 语句中。select field1,field2,...fieldN from table_name1,table_name2 ...where condition1 [and [or]] condition2 ... 查询语句中你可以使用一个或者多个表，表之间使用逗号, 分割，并使用WHERE语句来设定查询条件。你可以在 WHERE 子句中指定任何条件。你可以使用 AND 或者 OR 指定一个或多个条件。WHERE 子句也可以运用于 SQL 的 DELETE 或者 UPDATE 命令。WHERE 子句类似于程序语言中的 if 条件，根据 MySQL 表中的字段值来读取指定的数据。以下操作符列表可用于where子句中： 操作符描述=等号，检测两个值是否相等，如果相等返回true<> ,!=不等于，检测两个值是否相等，如果不相等返回true>大于号，检测左边的值是否大于右边的值, 如果左边的值大于右边的值返回true<小于号，检测左边的值是否小于右边的值, 如果左边的值小于右边的值返回true>=大于等于号，检测左边的值是否大于或等于右边的值, 如果左边的值大于或等于右边的值返回true<=小于等于号，检测左边的值是否小于或等于右边的值, 如果左边的值小于或等于右边的值返回true如果给定的条件在表中没有任何匹配的记录，那么查询不会返回任何数据。实例：MySQL 的 WHERE 子句的字符串比较是不区分大小写的。 用 BINARY 关键字来设定 WHERE 子句的字符串比较是区分大小写的。 ...

一.创建数据库mysql -u root -p登录后执行以下命令CREATE DATABSE db_name; 使用mysqladmin在终端创建数据库因为mysqladmin -u root -p create yisen2Enter password: 以上命令执行成功后会创建 MySQL 数据库yisen2 二.删除数据库mysql -u root -p登录后执行以下命令drop database db_name; 使用mysqladmin在终端删除数据库mysqladmin -u root -p drop db_name 三.选择数据库登录mysql后，执行以下命令：use db_name;执行以上命令后，你就已经成功选择了yisen 数据库，在后续的操作中都会在yisen数据库中执行。 注意:所有的数据库名，表名，表字段都是区分大小写的。所以在使用SQL命令时需要输入正确的名称。 四.创建数据表CREATE TABLE test2(id INT NOT NULL AUTO_INCREMENT, title VARCHAR(100) NOT NULL,b_author VARCHAR(40) NOT NULL,sub_date DATE,PRIMARY KEY(id)) ENGINE=InnoDB DEFAULT CHARSET=utf8;实例解析： 如果你不想字段为NULL，可以设置字段的属性为NOT NULL， 在操作数据库时如果输入该字段的数据为NULL，就会报错。AUTO_INCREMENT定义列为自增的属性，一般用于主键，数值会自动加1。PRIMARY KEY关键字用于定义列为主键。您可以使用多列来定义主键，列间以逗号分隔。ENGINE 设置存储引擎，CHARSET设置编码。五.删除数据库表drop table table_name;

一、Metadata lockMySQL使用DML来管理对数据库对象的并发访问，并确保数据一致性。DML不仅适用于表，还适用于模式和存储程序(过程、函数、触发器和计划的事件) 1.1 MDL简述为了在并发环境下维护表元数据的数据一致性，在表上有活动事务（显式或隐式）的时候，不可以对元数据进行写入操作。因此从MySQL5.5版本开始引入了MDL锁，来保护表的元数据信息，用于解决或者保证DDL操作与DML操作之间的一致性。 元数据锁的获取不依赖于使用的引擎，无论使用的是设置autocommit=0的MyISAM引擎还是用begin或start transaction语句显示声名的事务，连接都会获取元数据锁。一旦出现Metadata Lock Wait等待现象，后续所有对该表的访问都会阻塞在该等待上，导致连接堆积，业务受影响。 1.2 MDL解决的问题Metadata lock 是MySQL在5.5.3版本后引入了，为的是防止5.5.3以前的一个bug的出现： 当一个会话在主库执行DML操作还没提交时，另一个会话对同一个对象执行了DDL操作如drop table，而由于MySQL的binlog是基于事务提交的先后顺序进行记录的，因此在slave上应用时，就出现了先drop table，然后再向table中insert的情况，导致从库应用出错。 对于引入MDL，其主要解决了2个问题： 一个是事务隔离问题，比如在可重复隔离级别下，会话A在2次查询期间，会话B对表结构做了修改，两次查询结果就会不一致，无法满足可重复读的要求； 另外一个是数据复制的问题，比如会话A执行了多条更新语句期间，另外一个会话B做了表结构变更并且先提交，就会导致slave在重做时，先重做alter，再重做update时就会出现复制错误的现象。所以在对表进行上述操作时，如果表上有活动事务（未提交或回滚），请求写入的会话会等待在Metadata lock wait 。 二、常见MDL锁场景：①当前有执行DML操作（DML未执行完成）时，执行DDL操作 ② 当前有对表的长时间查询或使用mysqldump/mysqlpump时，执行DDL会被堵住 ③ 显示或者隐式开启事务后未提交或回滚，比如查询完成后未提交或者回滚，DDL会被堵住 ④ 表上有失败的查询事务，比如查询不存在的列，语句失败返回，但是事务没有提交，此时DDL仍然会被堵住 三、例子mysql版本：5.6.29 隔离级别：READ COMMITTED 3.1 场景1(1) 现象模拟事务1事务2事务3begin; select * from base_code; -alter table base_code modify column code varchar(64) DEFAULT NULL COMMENT '编码';——执行被阻塞 --select * from base_code;——执行被阻塞(2) show processlist查看结果如下:mysql> show processlist;+-------+-----------------+-----------+---------+---------+----------+---------------------------------+------------------------------------------------------------------------------------+| Id | User | Host | db | Command | Time | State | Info |+-------+-----------------+-----------+---------+---------+----------+---------------------------------+------------------------------------------------------------------------------------+| 1 | event_scheduler | localhost | NULL | Daemon | 20309270 | Waiting on empty queue | NULL || 98456 | root | localhost | lcl_abc | Sleep | 85 | | NULL || 98459 | root | localhost | lcl_abc | Query | 79 | Waiting for table metadata lock | alter table base_code modify column code varchar(64) DEFAULT NULL COMMENT '编码' || 98461 | root | localhost | lcl_abc | Query | 51 | Waiting for table metadata lock | select * from base_code || 98462 | root | localhost | NULL | Query | 0 | init | show processlist |+-------+-----------------+-----------+---------+---------+----------+---------------------------------+------------------------------------------------------------------------------------+5 rows in set (0.00 sec)select * from base_code; 再次查询base_code表也是 Waiting for table metadata lock状态，说明由于 metadata lock的存在，会导致后面正常的查询都会因为等待锁而阻塞。 ...

一、SQLAdvisor 介绍SQLAdvisor 是由美团点评公司北京DBA团队开发维护的 SQL 优化工具：输入SQL，输出索引优化建议。 它基于 MySQL 原生词法解析，再结合 SQL 中的 where 条件以及字段选择度、聚合条件、多表 Join 关系等最终输出最优的索引优化建议。目前 SQLAdvisor 在公司内部大量使用，较为成熟、稳定。项目 GitHub 地址：https://github.com/Meituan-Di... SQLAdvisor架构流程图SQLAdvisor 包含了如下的处理方式：Join 处理、where 处理、计算区分度、添加备选索引、Group 与 Order 处理、驱动表选择、添加被驱动表备选索引、输出建议，具体的流程图可以参考https://tech.meituan.com/2017... 二、SQLAdvisor 的优点基于 MySQL 原生词法解析，充分保证词法解析的性能、准确定以及稳定性；支持常见的 SQL(Insert/Delete/Update/Select)；支持多表 Join 并自动逻辑选定驱动表；支持聚合条件 Order by 和 Group by；过滤表中已存在的索引。 三、 SQLAdvisor安装3.1 拉取最新代码#git clone https://github.com/Meituan-Dianping/SQLAdvisor.git3.2 安装依赖项yum install -y cmake libaio-devel libffi-devel glib2 glib2-devel bisonyum install -y Percona-Server-server-56 Percona-Server-client-56//设置软链cd /usr/lib64/ls -l libperconaserverclient_r.so.18ln -s libperconaserverclient_r.so.18 libperconaserverclient_r.so注意 跟据glib安装的路径，修改SQLAdvisor/sqladvisor/CMakeLists.txt中的两处include_directories针对glib设置的path。glib yum 安装默认不需要修改路径编译sqladvisor时依赖perconaserverclient_r, 因此需要安装Percona-Server-shared-56。有可能需要配置软链接,例如:1. cd /usr/lib64/ 2. ln -s libperconaserverclient_r.so.18 libperconaserverclient_r.so有可能需要配置percona56 yum源:yum install https://repo.percona.com/yum/percona-release-latest.noarch.rpmyum -y install Percona-Server-shared-563.3 编译依赖项sqlparser1. cmake -DBUILD_CONFIG=mysql_release -DCMAKE_BUILD_TYPE=debug -DCMAKE_INSTALL_PREFIX=/usr/local/sqlparser ./2. make && make install注意 ...

一.安装后启动MySQL在Centos7系统下安装MySQL后启动systemctl start mysqld 二.查询MySQL运行状态：systemctl status mysqld 三.MariaDB简介也可以使用 MariaDB 代替，MariaDB 数据库管理系统是 MySQL 的一个分支，主要由开源社区在维护，采用 GPL 授权许可。开发这个分支的原因之一是：甲骨文公司收购了 MySQL 后，有将 MySQL 闭源的潜在风险，因此社区采用分支的方式来避开这个风险。 MariaDB的目的是完全兼容MySQL，包括API和命令行，使之能轻松成为MySQL的代替品。 MariaDB数据库的相关命令： systemctl start mariadb #启动Mariadbsystemctl stop mariadb #停止Mariadbsystemctl restart mariadb #重启mariadbsystemctl enable mariadb #设置开机启动四.登录MySQL当 MySQL 服务已经运行时, 我们可以通过 MySQL 自带的客户端工具登录到 MySQL 数据库中, 首先打开命令提示符, 输入以下格式的命名:mysql -h 主机名 -u 用户名 -p -h：指定客户端所要登录的MySQL主机名，登录本机（local host或者127.0.0.1）该参数可以省略-u：登录的用户名-p:告诉服务器将会使用一个密码来登录，如果所要登录的用户名密码为空，可以忽略此选项然后命令提示符会一直以 mysq> 加一个闪烁的光标等待命令的输入, 输入 exit或 quit退出登录。 五.使用 MySQL Client(Mysql客户端) 执行简单的SQL命令登录后输入以下命令可以展示数据库show databases;

一.启动和关闭数据库1.检查mysql是否启动ps -ef|grep mysql如果MySql已经启动，以上命令将输出mysql进程列表 2.启动mysqlcd /usr/bin ./mysqld_safe & 3.关闭mysqlcd /usr/bin ./mysqladmin -u root -p shutdown 二.mysql用户设置通过mysql的grant命令给指定的数据库database_name添加用户test，密码为test123： grant select,insert,update,detele,create,drop on database_name.* to 'test'@'localhost' identified by 'test123';以上命令会在mysql数据库中的user表创建一条用户信息记录。 三./etc/my.cnf一般情况下，不需要修改该配置文件 四.管理mysql命令1）use 数据库名;选中要操作的数据库，使用该命令后所有的Mysql命令都只针对该数据库。2）show tables;显示指定数据库的所有表，使用该命令前需要使用 use 命令来选择要操作的数据库。3）show columns from table_name;显示数据表的属性，属性类型，主键信息 ，是否为 NULL，默认值等其他信息。4）show index from table_name;显示数据表的详细索引信息，包括PRIMARY KEY（主键）。5)show table status from db_name like 'time%'G;显示数据库中以time开头的表的信息，加上G表示查询结果按列打印。

定义简单来说，就是为以后的使用而保存的一条或多条MySQL语句的集合。 为什么要使用存储过程简单、安全、高性能（因为使用存储过程比使用单独的SQL语句要快）

一.简介1.MySQL是最流行的关系型数据库管理系统（Relational DataBase Management System简称RDBMS）。2.数据库是按照数据结构来组织、存储和管理数据的仓库。所谓关系型数据库，是建立在关系模型基础上的数据库，借助于集合代数等数学概念和方法来处理数据库中的数据。3.RDBMS即关系数据管理系统(Relational Database Management System)的特点： 1）数据以表格的形式出现2）每行为各种记录名称3）每列为记录名称所对应的数据域4）许多的行和列组成一张表单5）若干的表单组成datebase4.RDBMS的一些术语： 1）数据库：一些关联表的集合2）数据表：表是数据的矩阵。3）列：一列（数据元素）包含了相同类型的数据。4）行：一行（=元组，或记录）是一组相关的数据。5）冗余：存储两倍数据，冗余降低了性能，但提高了数据的安全性。6）主键：主键是唯一的，一个数据表中只能包含一个主键。7）外键：用于关联两个表。8）复合键：复合键（组合键）将多个列作为一个索引键，一般用于复合索引。9）索引：使用索引可快速访问数据库表中的特定信息。索引是对数据库表中的一列或多列的值进行排序的一种结构，类似于书籍的目录。10）参照完整性：要求关系中不允许引用不存在的实体。与实体完整性是关系模型必须满足的完整性约束条件，目的是保证数据的一致性。5.一个数据表的组成： 1）表头（header)：每一行的名称2）列(col):具有相同数据类型的数据的集合3）行(row):每一行用来描述某条数据的具体信息4）值(value):行的具体信息，每个值必须与该列的数据类型相同5）键(key):键的值在当前列中具有唯一性6.其余介绍 MySQL 是开源的，所以你不需要支付额外的费用。MySQL 支持大型的数据库。可以处理拥有上千万条记录的大型数据库。MySQL 使用标准的 SQL 数据语言形式。MySQL 可以运行于多个系统上，并且支持多种语言。这些编程语言包括 C、C++、Python、Java、Perl、PHP、Eiffel、Ruby 和 Tcl 等。MySQL 对PHP有很好的支持，PHP 是目前最流行的 Web 开发语言。MySQL 支持大型数据库，支持 5000 万条记录的数据仓库，32 位系统表文件最大可支持 4GB，64 位系统支持最大的表文件为8TB。MySQL 是可以定制的，采用了 GPL 协议，你可以修改源码来开发自己的 MySQL 系统。

引言之前在面试准备期间写过相关的笔记，但时间仓促，未进行详述，对大家参考意义不大。 在MySQL数据库面试/开发中，事务一直是重中之重，这些知识在之前面试的时候学过，到现在也记得不太清楚了，本文带领大家一起来回顾MySQL的事务知识。 精讲前置知识事务通常并发执行，为了避免事务并发造成的一系列问题，数据库设计四大事务隔离级别： READ-UNCOMMITTEDREAD-COMMITTEDREPEATABLE-READSERIALIZABLEMySQL默认的事务隔离级别是REPEATABLE-READ，Oracle默认的事务隔离级别是READ-COMMITTED。 在MySQL的众多引擎中，只有InnoDB引擎支持事务与外键，而另一常用的MyISAM引擎虽不支持事务、外键，但访问速度快。 下图来自：《深入浅出MySQL数据库开发优化与管理维护》 场景我们聊聊最难理解的REPEATABLE-READ级别。 在READ-COMMITED隔离级别下，会出现以下问题： 事务A事务B开启事务开启事务查询账户余额为3000元//更新账户余额为3500元/提交事务使用相同的条件去查询账户余额，发现余额变为了3500元/提交事务/事务A就很蒙圈，两次查询结果不一样，我以哪个为准呢？要是以3500为准，那如果3500也被人改了呢？事务A不知道咋办了。 事务中多次读取结果不一致，这种现象为不可重复读。 实践新建一张基于InnoDB引擎的表account。 通过SELECT @@tx_isolation;查询当前事务隔离级别。 去Navicat开启用于测试的查询Session： 事务A事务B事务CSTART TRANSACTION;//SELECT * FROM account WHERE id = 1;/////START TRANSACTION;//UPDATE account SET balance = 3500 WHERE id = 1;//COMMIT;///START TRANSACTION;SELECT * FROM account WHERE id = 1;/SELECT * FROM account WHERE id = 1;/COMMIT;/COMMIT;通过本示例说明，普通的SELECT：SELECT * FROM account WHERE id = 1，没有加锁。因为如果加了锁，InnoDB行级锁下会拒绝写操作。 出现问题了，虽然解决了不可重复读的问题，但是以上帝视角来看，以3000进行运算，最终的结果还是错误的。 这时候就需要锁了。 锁共享锁(读锁)：读时加锁，允许其他事务共享锁，但不允许其他事务获取排它锁。 排它锁(写锁)：写时加锁，不允许其他事务获取共享、排它锁。 共享锁、排它锁，也成为读、写锁，锁为什么这么设计不用详细描述相信大家也能理解。 DML：数据库操作语言(不要小看概念，有人面试就挂在DML上了)，也就是我们常用的SELECT、INSERT、UPDATE、DELETE等操作语句。 在InnoDB引擎下，默认INSERT、UPDATE、DELETE会对相关记录加行级排它锁，默认SELECT不加锁。 ...

mysql执行执行时提示：Data truncation: Division by 0 解决方法: 设置sql_mod='',主要是取消sqlmode_error_for_division_by_zero

作者：NotFound9链接： https://www.cnblogs.com/notfo... 这是在网上找到的一张流程图，写的比较好，大家可以先看图，然后看详细阅读下面的各个步骤。 执行流程：1.连接验证及解析客户端与MySQL Server建立连接，发送语句给MySQL Server，接收到后会针对这条语句创建一个解析树，然后进行优化，（解析器知道语句是要执行什么，会评估使用各种索引的代价，然后去使用索引，以及调节表的连接顺序）然后调用innodb引擎的接口来执行语句。 2.写undo loginnodb 引擎首先开启事务，对旧数据生成一个UPDATE的语句(如果是INSERT会生成UPDATE语句)，用于提交失败后回滚，写入undo log，得到回滚指针，并且更新这个数据行的回滚指针和版本号(会设置为更新的事务id)。 3.从索引中查找数据根据查询条件去B+树中找到这一行数据（如果是唯一性索引，查到第一个数据就可以了（因为有唯一性约束），如果是普通索引，会把所有数据查找出来。） 4.更新数据首先判断数据页是否在内存中？ 4.1 如果数据页在内存中先判断更新的索引是普通索引还是唯一性索引？ 4.1.1 普通索引如果更新的索引是普通索引，直接更新内存中的数据页 4.1.2 唯一性索引如果更新的索引是唯一性索引，判断更新后是否会破坏数据的唯一性，不会的话就更新内存中的数据页。 4.2 如果数据页不在内存中先判断更新的索引是普通索引还是唯一性索引？ 4.2.1 普通索引如果是更新的索引是普通索引，将对数据页的更新操作记录到change buffer，change buffer会在空闲时异步更新到磁盘。 4.2.2 唯一性索引如果是更新的索引是唯一性索引，因为需要保证更新后的唯一性，所以不能延迟更新，必须把数据页从磁盘加载到内存，然后判断更新后是否会数据冲突，不会的话就更新数据页。 5.写undo log（prepare状态）将对数据页的更改写入到redo log，将redo log设置为prepare状态。 6.写bin log（commit状态），提交事务通知MySQL server已经更新操作写入到redo log 了，随时可以提交，将执行的SQL写入到bin log日志，将redo log改成commit状态，事务提交成功。（一个事务是否执行成功的判断依据是是否在bin log中写入成功。写入成功后，即便MySQL Server崩溃，之后恢复时也会根据bin log， redo log进行恢复。具体可以看看下面的崩溃恢复原则） 补充资料：二段提交制是什么？更新时，先改内存中的数据页，将更新操作写入redo log日志，此时redo log进入prepare状态，然后通知MySQL Server执行完了，随时可以提交，MySQL Server将更新的SQL写入bin log，然后调用innodb接口将redo log设置为提交状态，更新完成。如果只是写了bin log就提交，那么忽然发生故障，主节点可以根据redo log恢复数据到最新，但是主从同步时会丢掉这部分更新的数据。如果只是写binlog，然后写redo log，如果忽然发生故障，主节点根据redo log恢复数据时就会丢掉这部分数据。MySQL崩溃后，事务恢复时的判断规则是怎么样的？（以redolog是否commit或者binlog是否完整来确定）如果 redo log 里面的事务是完整的，也就是已经有了 commit 标识，则直接提交； 如果 redo log 里面的事务只有完整的 prepare，则判断对应的事务 binlog 是否存在并完整：a. 如果是，则提交事务；b. 否则，回滚事务。 ...

张喜硕学长以前讲过一篇MySQL RR 与 锁，在本周又看到了RR的问题，里面提到了RR是通过MVCC实现的，但是自己对此却没什么印象，翻了翻学长的博客也没讲过，就学习一下，做个记录。 MVCCMVCC 即多版本并发控制技术，简单的理解就是一份数据保存了多份。 用于多事务环境下，对数据读写在不加读写锁的情况下实现互不干扰，从而实现数据库的隔离性，在事务隔离级别为Read Commit 和 Repeatable read中使用到。 在InnoDB中，MVCC其实是通过undo log来实现的，但使用undo log解释起来较为复杂，所以普遍的解释是：每行记录的后面保存了两个隐藏的列,DB_TRX_ID(数据行的版本号)和DB_ROLL_PT(删除版本号),这两列保存的是系统版本号,每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较。下面看看进行不同的操作时(以下内容取RR隔离级别，当然RC也是同理，只不过select的选定范围不同)，InnoDB的行为： SELECT InnoDB会根据以下两个条件检查每行记录： InnoDB只查找版本早于当前事务版本的数据行（也就是，行的系统版本号小于或等于事务的系统版本号），这样可以确保事务读取的行，要么是在事务开始前已经存在的，要么是事务自身插入或者修改过的。行的删除版本要么未定义，要么大于当前事务版本号。这可以确保事务读取到的行，在事务开始之前未被删除。INSERTInnoDB为新插入的每一行保存当前系统版本号作为数据行版本号。 DELETEInnoDB为删除的每一行保存当前系统版本号作为行删除版本号。 UPDATEInnoDB插入一条新记录，保存当前系统版本号作为数据行版本号，同时保存当前系统版本号到原来的行作为删除版本号。 保存这两个额外系统版本号，使大多数读操作都可以不用加锁。这样设计使得读数据操作很简单，性能很好，并且也能保证只会读取到符合标准的行，不足之处是每行记录都需要额外的存储空间，需要做更多的行检查工作，以及一些额外的维护工作。 光看概念肯定还是看的不太明白的，我们用一个例子来展示一下 例子先创建一个用户表 create table user( id int primary key auto_increment, name varchar(20));打开navicat，新建一个查询,执行以下sql begin; # 开始一个新的事务, 事务的版本号为1insert into user values(NULL,'zhangsan');insert into user values(NULL,'lisi');commit;此时数据库中的数据应该是这样，因为新插入的每一行会保存当前系统版本号作为数据行版本号 IdnameDB_TRX_ID(数据行版本号)DB_ROLL_PT(删除版本号)1zhangsan1null2lisi1null此时, 我们打开一个新的查询, 把它称作Query1 begin; # 开始一个新的事务，事务版本号为2select * from user; # 1 select * from user; # 2commit;此时，执行Query1中的1 我们再打开一个查询, 把它称作Query2 begin; # 开始一个新的事务，事务版本号为3update user set name = 'yuzhi' where id = 1;commit;执行Query2，之后我们在执行Query1的2 ...

引言因标签过多，在实际的应用过程中，对标签表的结构进行了变更。 从过去的标签随意选择，现在需要对标签进行分类，简化选择难度。添加科目分类之后，需要对历史上已经被使用过的标签添加科目信息，进行数据的迁移工作。 在数据迁移时，使用到了存储过程，遇到了诸多问题，特此记录，分享爬坑过程。 存储过程存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。 通俗来说，存储过程就是数据库中被编译的SQL函数，和普通函数一样有函数体，有顺序结构、条件结构、循环结构，有参数，有输入输出。 参考文献请先阅读以下两篇文章，本文在此基础上对某些细节做了补充。 MYSQL如何对数据进行自动化升级--以如果某数据表存在并且某字段不存在时则执行更新操作为例 - 河北工业大学梦云智软件开发团队 数据库编程实战 - 鲸冬香 标识符替换因定义存储过程时;遇到分号会进行语句的执行，需要对;进行转义。 -- 标识符替换DELIMITER $$-- 存储过程定义-- 重新定义标识符DELIMITER ;定义存储过程-- 存储过程CREATE PROCEDURE procedure_name(IN param BIGINT)BEGIN -- 存储过程具体实现END$$游标示例请重点注意以下示例代码中的顺序问题，一定是先声明变量，再声明游标，再处理，否则MySQL会抛错误。 -- 声明变量DECLARE tag_id BIGINT;DECLARE done INT DEFAULT FALSE;-- 声明游标DECLARE cur CURSOR FOR SELECT `id` FROM `tag`;-- 声明 not found 处理DECLARE CONTINUE HANDLER FOR NOT FOUND SET done = TRUE;OPEN cur;FETCH cur INTO tag_id;WHILE NOT done DO -- 遍历标签表 逐条迁移数据 CALL migrate_tag_info_by_id(tag_id); FETCH cur INTO tag_id;END WHILE;HANDLER需要重点注意以下代码： ...

巡检工作是保障系统平稳有效运行必不可少的一个环节，目的是能及时发现系统中存在的隐患。我们生活中也随处可见各种巡检，比如电力巡检、消防检查等，正是这些巡检工作，我们才能在稳定的环境下进行工作、生活。巡检对于数据库或者其他IT系统来说也同样至关重要，特别是在降低风险、提高服务稳定性方面起到了非常关键作用。 本文介绍了美团MySQL数据库巡检系统的框架和巡检内容，希望能够帮助大家了解什么是数据库巡检，美团的巡检系统架构是如何设计的，以及巡检系统是如何保障MySQL服务稳定运行的。 一、背景为了保障数据库的稳定运行，以下核心功能组件必不可少： 其中，数据库巡检作为运维保障体系最重要的环节之一，能够帮助我们发现数据库存在的隐患，提前治理，做到防患于未然。对于大规模集群而言，灵活健壮的自动化巡检能力，至关重要。 任何系统都会经历一个原始的阶段，最早的巡检是由中控机+定时巡检脚本+前端展示构成的。但是，随着时间的推移，老巡检方案逐渐暴露出了一些问题： 巡检定时任务执行依赖中控机，存在单点问题；巡检结果分散在不同的库表，无法进行统计；巡检脚本没有统一开发标准，不能保证执行的成功率；每个巡检项都需要单独写接口取数据，并修改前端用于巡检结果展示，比较繁琐；巡检发现的隐患需要DBA主动打开前端查看，再进行处理，影响整体隐患的治理速度；......所以我们需要一个灵活、稳定的巡检系统来帮助我们解决这些痛点，保障数据库的稳定。 二、设计原则巡检系统的设计原则，我们从以下三个方面进行考虑： 稳定：巡检作为保证数据库稳定的工具，它自身的稳定性也必须有所保证；高效：以用户为中心，尽量化繁为简，降低用户的使用成本，让新同学也能迅速上手治理和管理隐患；提高新巡检部署效率，随着架构、版本、基础模块等运维环境不断变化，新的巡检需求层出不穷，更快的部署等于更早的保障；可运营：用数据做基础，对巡检隐患进行运营，包括推进隐患治理，查看治理效率、趋势、薄弱点等。 三、系统架构美团MySQL数据库巡检系统架构图设计如下。接下来，我们按照架构图从下到上的顺序来对巡检系统主要模块进行简单的介绍： 1. 执行层巡检执行环境：由多台巡检执行机组成，巡检任务脚本会同时部署在所有执行机上。执行机会定时从巡检Git仓库拉取最新的脚本，脚本使用Python Virtualenv + Git进行管理，方便扩充新的执行机。 任务调度：巡检任务使用了美团基础架构部研发的分布式定时任务系统Crane进行调度，解决传统定时任务单点问题。Crane会随机指派某一台执行机执行任务，假如这台执行机出现故障，会指派其他执行机重新执行任务。一般一个巡检任务对应着一个巡检项，巡检任务会针对特定的巡检目标根据一定的规则来判断是否存在隐患。 巡检目标：除了对生产数据库进行巡检以外，还会对高可用组件、中间件等数据库周边产品进行巡检，尽可能覆盖所有会引发数据库故障的风险点。 2. 存储层巡检数据库：主要用来保存巡检相关数据。为了规范和简化流程，我们将巡检发现的隐患保存到数据库中，提供了通用的入库函数，能够实现以下功能： 自动补齐隐患负责人、隐患发现时间等信息；入库操作幂等；支持半结构化的巡检结果入库，不同巡检的隐患结果包括不同的属性，比如巡检A的隐患有“中间件类型”，巡检B有“主库CPU核数”，以上不同结构的数据均可解析入库；针对表粒度的隐患项，如果分库分表的表出现隐患，会自动合并成一个逻辑表隐患入库。巡检脚本Git仓库：用来管理巡检脚本。为了方便DBA添加巡检，在系统建设过程中，我们增加了多个公共函数，用来降低开发新巡检的成本，也方便将老的巡检脚本迁移到新的体系中。 3. 应用层集成到数据库运维平台：作为隐患明细展示、配置巡检展示、管理白名单等功能的入口。为了提高隐患治理效率。我们做了以下设计。 隐患明细展示页面会标注每个隐患出现的天数，便于追踪隐患出现原因。配置新的巡检展示时必须要同时制定隐患解决方案，确保隐患治理有章可循，避免错误的治理方式导致“错上加错”。隐患运营后台：这个模块主要目的是推进隐患的治理。 运营报表，帮助管理者从全局角度掌握隐患治理进展，报表包括隐患趋势、存量分布、增量分布、平均治理周期等核心内容，进而由上到下推动隐患治理；报表数据同样是通过crane定时任务计算获得。隐患治理催办功能，用来督促DBA处理隐患。催办内容中会带有隐患具体内容、出现时长、处理方案等。催办形式包括大象消息、告警，具体选用哪种形式可根据巡检关键程度做相应配置。外部数据服务：主要是将巡检隐患数据提供给美团内部其他平台或项目使用，让巡检数据发挥更大的价值。 对接先知平台（美团SRE团队开发的主要面向RD用户的风险发现和运营平台），平台接收各服务方上报的隐患数据，以RD视角从组织架构维度展示各服务的风险点，并跟进RD处理进度。巡检系统会把需要RD参与治理的隐患，比如大表、无唯一键表等，借助先知平台统一推送给RD进行治理。运维周报，主要面向业务线RD负责人和业务线DBA，以静态报告形式展示业务线数据库运行情况以及存在的问题，巡检隐患是报告内容之一。四、巡检项目巡检项目根据负责方分为DBA和RD，DBA主要负责处理数据库基础功能组件以及影响服务稳定性的隐患。RD主要负责库表设计缺陷、数据库使用不规范等引起的业务故障或性能问题的隐患。也存在需要他们同时参与治理的巡检项，比如“磁盘可用空间预测”等。目前巡检项目共64个，类目分布情况如下图所示： 集群：主要检查集群拓扑、核心参数等集群层面的隐患；机器：主要检查服务器硬件层面的隐患；Schema/SQL：检查表结构设计、数据库使用、SQL质量等方面的隐患；高可用/备份/中间件/报警：主要检查相关核心功能组件是否存在隐患。 下面，我们通过列举几个巡检任务来对巡检项做简单的说明： 五、成果美团MySQL巡检系统已稳定运行近一年时间，基于新巡检体系上线的巡检项49个。通过巡检体系持续运行，在团队的共同努力下，我们共治理了8000+核心隐患，近3个月隐患治理周期平均不超过4天，将隐患总数持续保持在极小的量级，有效地保障了数据库的稳定。 下面的隐患趋势图，展示了近一年中隐患的个数，数量突然增长是由于新的巡检项上线。从整体趋势上看，隐患存量有非常明显的下降。 除了推动内部隐患治理之外，我们还通过对接先知平台，积极推动RD治理隐患数量超过5000个。 为了提升用户体验，我们在提升准确率方面也做了重点的投入，让每一个巡检在上线前都会经过严格的测试和校验。 对比其他先知接入方，DBA上报隐患在总量、转化率、反馈率几个指标上都处于较高水平，可见我们上报的隐患风险也得到了RD的认可。 指标说明： 反馈率 = 截止到当前时刻反馈过的风险事件数量/截止到当前时刻产生的风险事件总量 * 100%；反馈准确率 = 截止到当前时刻反馈准确的风险事件数量/截止到当前时刻反馈过的风险事件总量 * 100%；转化率 = 截止到当前时刻用户反馈准确且需要处理的风险事件数量 / 截止到当前时刻产生的风险事件总量 * 100%。六、未来规划除了继续完善补充巡检项以外，未来巡检系统还会在以下几个方向继续探索迭代： 提高自动化能力，完善CI和审计；加强运营能力，进一步细化每个隐患的重要程度，辅助决策治理优先级；隐患自动修复。作者简介王琦，基础架构部DBA组成员，2018年加入美团，负责MySQL数据库运维/数据库巡检系统/监控/自动化运维周报/运维数据集市建设等工作。 阅读更多技术文章，请扫码关注微信公众号-美团技术团队！

前言： 在项目开发中，一些业务表字段经常使用日期和时间类型，而且后续还会牵涉到这类字段的查询。关于日期及时间的查询等各类需求也很多，本篇文章简单讲讲日期及时间字段的规范化查询方法。 1.日期和时间类型概览MySQL支持的日期和时间类型有 DATETIME、TIMESTAMP、DATE、TIME、YEAR，几种类型比较如下： 涉及到日期和时间字段类型选择时，根据存储需求选择合适的类型即可。 2.日期和时间相关函数处理日期和时间字段的函数有很多，有的经常会在查询中使用到，下面介绍下几个相关函数的使用方法。 CURDATE 和 CURRENT_DATE 两个函数作用相同，返回当前系统的日期值。CURTIME 和 CURRENT_TIME 两个函数作用相同，返回当前系统的时间值。NOW() 和 SYSDATE() 两个函数作用相同，返回当前系统的日期和时间值。UNIX_TIMESTAMP 获取UNIX时间戳函数，返回一个以 UNIX 时间戳为基础的无符号整数。FROM_UNIXTIME 将 UNIX 时间戳转换为时间格式，与UNIX_TIMESTAMP互为反函数。TO_DAYS() 提取日期值并返回自公元0年到现在的天数。DAY() 获取指定日期或时间中的天值。DATE() 获取指定日期或时间中的日期。TIME() 获取指定日期或时间中的时间。MONTH 获取指定日期中的月份。WEEK 获取指定日期是一年中的第几周。YEAR 获取年份。QUARTER 获取日期所在的季度值。DATE_ADD 和 ADDDATE 两个函数功能相同，都是向日期添加指定的时间间隔。DATE_SUB 和 SUBDATE 两个函数功能相同，都是向日期减去指定的时间间隔。ADDTIME 时间加法运算，在原始时间上添加指定的时间。SUBTIME 时间减法运算，在原始时间上减去指定的时间。DATEDIFF 获取两个日期之间间隔，返回参数 1 减去参数 2 的值。DATE_FORMAT 格式化指定的日期，根据参数返回指定格式的值。一些使用示例： mysql> select CURRENT_DATE,CURRENT_TIME,NOW();+--------------+--------------+---------------------+| CURRENT_DATE | CURRENT_TIME | NOW() |+--------------+--------------+---------------------+| 2020-06-03 | 15:09:37 | 2020-06-03 15:09:37 |+--------------+--------------+---------------------+mysql> select TO_DAYS('2020-06-03 15:09:37'),TO_DAYS('2020-06-03')-TO_DAYS('2020-06-01');+--------------------------------+---------------------------------------------+| TO_DAYS('2020-06-03 15:09:37') | TO_DAYS('2020-06-03')-TO_DAYS('2020-06-01') |+--------------------------------+---------------------------------------------+| 737944 | 2 |+--------------------------------+---------------------------------------------+mysql> select MONTH('2020-06-03'),WEEK('2020-06-03'),YEAR('2020-06-03');+---------------------+--------------------+--------------------+| MONTH('2020-06-03') | WEEK('2020-06-03') | YEAR('2020-06-03') |+---------------------+--------------------+--------------------+| 6 | 22 | 2020 |+---------------------+--------------------+--------------------+# DATEDIFF(date1，date2) 返回起始时间 date1 和结束时间 date2 之间的天数mysql> SELECT DATEDIFF('2017-11-30','2017-11-29') AS COL1, -> DATEDIFF('2017-11-30','2017-12-15') AS col2;+------+------+| COL1 | col2 |+------+------+| 1 | -15 |+------+------+3.日期和时间字段的规范查询上面的内容都是为我们的查询需求做准备，在项目需求中，经常会以日期或时间为条件进行筛选查询。有时候这类需求多种多样，下面我们来学习下关于日期和时间字段的查询写法。 ...

相信大部分入门数据库的朋友都是从数据库的“增删改查”学起的。其实，对于很多搞业务的非专业技术人员而言，可能基本的增删改查也够用了，因为目的并不是要写的多好，只要能正确查到自己想要的分析的数据就可以了。 但是，对于一个专业搞数据分析的人而言，可就没那么简单了。这个自己平时跑个小数可能也没啥感觉，但现实工作中当公司业务数据量达到百万甚至千万级以上时，一个查询语句写的好坏所造成的影响就尤为明显了。所以也就不难理解为什么面试的时候面试官喜欢问一些关于优化的问题。 为了了解自己写的SQL是好是坏，MySql提供了Explain执行计划功能。它对优化SQL语句尤为的重要，通过它可以看清执行过程的细节，分析查询语句或是结构的性能瓶颈，找到问题所在。 如何使用Explain？explain的使用很简单，就是在select 语句之前增加 explain关键字就ok了。MySQL 会在查询上设置一个标记，执行查询时，会返回执行计划的信息，而不是执行这条SQL。比如这样： # explain + sqlexplain select * from table where a = 1;Explain执行计划能做什么？确定表的读取顺序数据读取操作的操作类型哪些索引可以使用哪些索引被实际使用表之间的引用每张表有多少行被优化器查询可以看出执行计划给我们提供的信息是非常有帮助的。只有读懂了这些内容，才能定位问题点在哪，进而去解决。下面东哥给大家介绍一下explain执行计划的内容。 因为有些字段光看很难理解，因此建立三个表作为例子来说明，感兴趣的朋友也可以自己跑下试试。 DROP TABLE IF EXISTS `actor`;CREATE TABLE `actor` ( `id` int(11) NOT NULL, `name` varchar(45) DEFAULT NULL, `update_time` datetime DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `actor` (`id`, `name`, `update_time`) VALUES (1,'a','2017-12-22 15:27:18');INSERT INTO `actor` (`id`, `name`, `update_time`) VALUES (2,'b','2017-12-22 15:27:18');INSERT INTO `actor` (`id`, `name`, `update_time`) VALUES (3,'c','2017-12-22 15:27:18');DROP TABLE IF EXISTS `film`;CREATE TABLE `film` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(10) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_name` (`name`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `film` (`id`, `name`) VALUES (3,'film0');INSERT INTO `film` (`id`, `name`) VALUES (1,'film1');INSERT INTO `film` (`id`, `name`) VALUES (2,'film2');DROP TABLE IF EXISTS `film_actor`;CREATE TABLE `film_actor` ( `id` int(11) NOT NULL, `film_id` int(11) NOT NULL, `actor_id` int(11) NOT NULL, `remark` varchar(255) DEFAULT NULL, PRIMARY KEY (`id`), KEY `idx_film_actor_id` (`film_id`,`actor_id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;INSERT INTO `film_actor` (`id`, `film_id`, `actor_id`) VALUES (1,1,1);INSERT INTO `film_actor` (`id`, `film_id`, `actor_id`) VALUES (2,1,2);INSERT INTO `film_actor` (`id`, `film_id`, `actor_id`) VALUES (3,2,1);注意：上面三张表中，actor主键为id；film主键为id，以name字段为索引；film_actor表中id为主键，以film_id和actor_id为联合索引。 ...

最近从正式环境导入整个db，查看view是提示 The user specified as a definer ('root'@'%') does not exist解析：权限问题，授权 给 root 所有sql 权限解决方法： mysql> grant all privileges on *.* to root@"%" identified by ".";Query OK, 0 rows affected, 1 warning (0.00 sec)mysql> flush privileges;Query OK, 0 rows affected (0.00 sec)

数据库设计三范式设计数据库表的时候所依据的规范，共三个规范： 第一范式：要求有主键，并且要求每一个字段原子性不可再分第二范式：要求所有非主键字段完全依赖主键，不能产生部分依赖第三范式：所有非主键字段和主键字段之间不能产生传递依赖第一范式数据库表中不能出现重复记录，每个字段是原子性的不能再分 不符合第一范式的实例： 第一范式1.PNG 存在问题: 最后一条记录和第一条重复（不唯一，没有主键） 联系方式字段可以再分，不是原子性的 第一范式2.PNG 关于第一范式，每一行必须唯一，也就是每个表必须有主键，这是数据库设计的最基本要求，主要采用数值型或定长字符串表示，关于列不可再分，应该根据具体的情况来决定。如联系方式，为了开发上的便利可能就采用一个字段。 第二范式第二范式是建立在第一范式基础上的，另外要求所有非主键字段完全依赖主键，不能产生部分依赖 实例： 二1.PNG 确定主键： 二2.PNG 以上虽然确定了主键，但此表会出现大量的冗余，主要涉及到的冗余字段为“学生姓名”和“教师姓名”，出现冗余的原因在于，学生姓名部分依赖了主键的一个字段学生编号，而没有依赖教师编号，而教师姓名部分依赖了主键的一个字段教师编号，这就是第二范式部分依赖。 解决： 解决.PNG 如果一个表是单一主键，那么它就是复合第二范式，部分依赖和主键有关系 以上是典型的“多对多”设计 第三范式建立在第二范式基础上的，非主键字段不能传递依赖于主键字段（不要产生传递依赖） 三1.PNG 上表中，班级名称字段存在冗余，因为班级名称字段没有直接依赖于主键，班级名称字段依赖于班级编号，班级编号依赖于学生编号，这就是传递依赖，解决的办法就是将冗余字段单独拿出来建立表： 解决三.PNG 以上设计是典型的一对多的设计，一存储在一张表中，多存储在一张表中，在多的那张表中添加外键指向一的一方 几个经典的设计：一对一： 第一种方案：分两张表存储，共享主键第二种方案：分两张表存储，外键唯一一对多： 分两张表存储，在多的一方添加外键，这个外键字段引用一的一方中的主键字段多对多： 分三张表存储，在学生表中存储学生信息，在课程表中存储课程信息，在学生选课表中存储学生和课程的关系信息 共享主键.PNG 外键唯一.PNG 实际开发中，数据库设计尽量遵循三范式，但是还是根据实际情况进行取舍，有时可能会拿冗余换速度，最终目的是要满足客户需求

查询优化： （1） 覆盖索引如果一个索引包含(或覆盖)所有需要查询的字段的值,称为‘覆盖索引’，不需要回表，减少树的搜索次数（2）联合索引---最左前缀原则：如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找，但不支持 b,c进行查找 。对于a,c, 只走a字段索引，不会走c字段。（3）前缀索引：前缀索引用来作为字符串的索引，1.倒叙存储；2.使用hash字段，作为校验码；案例：查询一个广告文案下面对应的关键词内容，广告文案内容长度一般会有30到50这样子的中文字符。我们采取的做法是 新增一个hash字段，将广告文案在代码层面进行md5，然后存储到hash字段中，可能会出现hash冲突，我们在代码层面再做一层 ”数据的完全匹配“；减少索引的长度，同时复杂计算放在代码层面。 （4）尽量选择区分度高的列作为索引（5）把复杂计算放到业务层而不是数据库层（6）分页查询优化（7）字段的属性要设置为not null，使用一个特殊的值作为默认值，例如0或者一个空串索引失效的情况： （1）索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’)。（2）负向条件查询不能使用索引。select * from order where status!=0 and stauts!=1not in/not exists都不是好习惯。查询条件中使用了不等于操作符（<>、!=）的select语句执行慢 原因：SQL中，不等于操作符会限制索引，引起全表扫描，即使比较的字段上有索引（3）前导模糊查询不能使用索引。select * from order where desc like '%XX'（4）隐式的强制类型转换会全表扫描select name from user where phone=13800001234你以为会命中phone索引么？大错特错了！！！（5）OR语句使用不当 使用or分割的条件，如果or前后条件的列存在没有索引，那么涉及到的索引都不会使用，导致全表扫描。 例如：例如：where A=1 or B=2，A上有索引，B上没索引，则会全表查询。为什么推荐使用NOT NULL 允许为null的列，查询有潜在大坑。 1.null字段的数据，只能通过 is null查询出来 2.当用count函数进行统计时，NULL 列不会计入统计。 SELECT count(name) from t 而且，null是一个属性，额外占有1个字节 分页查询优化： select * from auth_user limit offset,len;mysql 底层是从第一行开始找到offset+len行，再抛弃前面的offset行。 ...

基本知识点： 索引是在存储引擎层实现的MyISAM引擎和Innodb引擎都使用B+Tree作为索引结构，但是底层实现还是有些不同的索引一经创建不能修改，如果要修改索引，只能删除重建。Innodb索引和数据存储在同一个文件，MyISAM索引文件和数据文件是分离的。索引按照功能分为： 主键索引，唯一索引，普通索引，前缀索引，全文索引 创建主键索引： alter table table_name add PRIMARY KEY ( `column` ) //主键索引可以包含多个列 创建普通索引: alter table table_name add index index_name(column);创建唯一索引： alter table table_name add UNIQUE index index_name(column);创建前缀索引 ：alter table table_name add index index_name(columns(6)); //只能是字符串字段 创建全文索引： alter table table_name add FULLTEXT index index_name (column);//Mysql 全文索引是专门为了解决模糊查询提供的，但是开销很大 删除索引： drop index index_name on table_name;按照索引的字段个数： 单列索引，联合索引；其中联合索引满足最左匹配原则如索引是key index (a,b,c). 可以支持a | a,b| a,b,c 3种组合进行查找，但不支持 b,c进行查找 。对于a,c, 只走a字段索引，不会走c字段。 按照磁盘存储来区分：聚簇索引，非聚簇索引聚簇索引可认为是磁盘将实际数据按照定物理地址进行顺序存放，并且与索引的顺序是一致的。那么当索引是相邻的，对应的数据一定也是按照相邻的顺序存放。 【MyISAM的主键索引和辅助索引】由上图可以看到： 1.在MyISAM中，索引的数据结构是一颗B+Tree2.在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，只是主索引要求key是唯一的，而辅助索引的key可以重复。3.在MyISAM中，叶节点的data域存放的是数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。MyISAM的索引方式也叫做“非聚簇”的。 ...

检测mysql中sql语句的效率的方法（一）查看 ”慢查询日志“slow_query_log这个参数设置为ON，可以捕获执行时间超过一定数值的SQL语句 long_query_time当SQL语句执行时间超过此数值时，就会被记录到日志中，单位是秒,默认值是10。建议设置为1或者更短。 slow_query_log_file慢查询日志的文件名 log_queries_not_using_indexes这个参数设置为ON，可以捕获到所有未使用索引的SQL语句，尽管这个SQL语句有可能执行得挺快。（注意：如果只是将log_queries_not_using_indexes设置为ON，而将slow_query_log设置为OFF，此时该设置也不会生效，即该设置生效的前提是slow_query_log的值设置为ON）查询MySQL慢查询的输出日志格式为文件还是表，或者两者都有？ （二）show processlist 命令show processlist 是显示用户正在运行的线程，需要注意的是，除了 root 用户能看到所有正在运行的线程外，其他用户都只能看到自己正在运行的线程，看不到其它用户正在运行的线程。除非单独个这个用户赋予了PROCESS 权限。主要有两个作用： (1)查看慢查询的sql是哪个 (2)查看出现锁的sql是哪个 show processlist 显示的信息都是来自MySQL系统库 information_schema 中的 processlist 表。所以使用下面的查询语句可以获得相同的结果： select \* from information\_schema.processlistId: 就是这个线程的唯一标识，当我们发现这个线程有问题的时候，可以通过 kill 命令，加上这个Id值将这个线程杀掉。前面我们说了show processlist 显示的信息时来自information_schema.processlist 表，所以这个Id就是这个表的主键。User: 就是指启动这个线程的用户。Host: 记录了发送请求的客户端的 IP 和 端口号。通过这些信息在排查问题的时候，我们可以定位到是哪个客户端的哪个进程发送的请求。DB: 当前执行的命令是在哪一个数据库上。如果没有指定数据库，则该值为 NULL 。Command: 是指此刻该线程正在执行的命令。这个很复杂，下面单独解释Time: 此这个状态持续的时间，单位是秒。State: 线程的状态，和 Command 对应，下面单独解释。Info: 一般记录的是线程执行的语句。默认只显示前100个字符，也就是你看到的语句可能是截断了的，要看全部信息，需要使用 show full processlist下面我们单独看一下 Command 主要的值： Sleep: 正在等待客户端向它发送执行语句（已经建立了connect，涉及到数据库连接池） Query: 该线程正在执行一个语句 state列主要的值： Locked：被其他查询锁住了。（存在Locked就说明当前读写操作存在被阻塞的情况） 例如： | 124912 | hdpic_read  | 192.168.1.39:18862 | hdpic | Query | 845 | Locked | select * from a where ((ID = 16031) AND (STATE != 0))应用场景 （1）找出所有执行时间超过 5 分钟的线程，拼凑出 kill 语句，方便后面查杀。time字段的单位是秒~~~~ select concat('kill ', id, ';') from information_schema.processlist where Command != 'Sleep' and Time > 300 order by Time desc;select concat('kill ', id, ';’) from information_schema.processlist where COMMAND = 'query' and Time > 60*5 order by Time desc;（2）查看死锁 ...

Linux下添加账户和设置密码useradd 添加用户useradd –d /usr/sam -m test此命令创建了一个用户test，其中-d和-m选项用来为登录名test产生一个主目录/usr/test（/usr为默认的用户主目录所在的父目录）。 设置密码passwd test给test用户设置密码 删除账户userdel test 删除test的用户 授权给test账户添加root权限vim etc/passwd chmod  777 /etc/squid 运行命令后，squid文件夹（目录）的权限就被修改为777（可读可写可执行）,一般都是在需要权限的目录下执行此命令，pwd显示当前目录路径。 systemctl 为Linux 7.x以上系统的命令。 Mysql下创建账号和分配权限创建账户CREATE USER 'username'@'host' IDENTIFIED BY 'password';//创建账号username：用户名 host：本地访问，如果需要远程访问，改成% password：密码 例子：CREATE USER 'test'@'host' IDENTIFIED BY 'test' //创建了一个test的用户，密码也是test,这个账号只能本地访问数据库 授权GRANT privileges ON databasename.tablename TO 'username'@'host' privileges：用户的操作权限，如SELECT，INSERT，UPDATE等，如果要授予所的权限则使用ALL databasename：数据库名 tablename：表名，如果要授予该用户对所有数据库和表的相应操作权限则可用表示，如.* 例子： GRANT all ON test TO 'test'@'host' //给test账户访问本地test库的所有权限。

一、约定1、如无特殊需求，所有表使用innodb引擎 2、如无特殊需求，所有主键均为自增类型 3、如无特殊需求，所有字段均为NOT NULL，并给定默认值 4、所有字段均设置备注，枚举字段需要说明每个枚举值的意义 5、在能满足取值范围的情况下，选择占用存储空间最小的数据类型。如布尔值使用tinyint，时间类型使用timestamp二、命名规范1、数据库名：与系统名相同 2、表名：系统名称缩写+_+表名。表名必须描述该表的用途，由单个或多个名词组成，首字母小写，后续单词首字母大写。 3、字段名：字段名必须描述该字段的用途，由单个或多个名词组成，单词之间用下划线连接 4、主键字段：表名+Id 5、外键字段：与主表主键字段完全一样 6、主键：pk_+表名 7、外键：fk_+从表名+_+主表名 8、视图：view_+名名称 9、存储过程：prcd_+名称 10、函数：fun_+名称 11、触发器：trg_+名称 12、索引：idx_+名称————————————————版权声明：本文为CSDN博主「learsea」的原创文章，遵循CC 4.0 BY-SA版权协议，转载请附上原文出处链接及本声明。原文链接：https://blog.csdn.net/learsea...

为了进行复杂信息的存储和查询，服务端系统往往需要数据库操作。数据库分为关系型数据库和非关系型数据库，关系型数据库有MySQL、Oracle、SQL Server等，非关系型数据库有Redis（常用来做缓存）、MongoDB等。MySQL是目前很流行的数据库，本文将要介绍如何在node服务中进行MySQL数据库操作。一、 安装依赖npm install mysql --save或者 yarn add mysql二、建立连接要想进行数据库操作就需要和数据库建立连接，然后通过连接进行数据库的操作。MySQL的数据库连接方式有以下几种： mysql.createConnection() 每次请求建立一个连接mysql.createPool() 创建连接池，从连接池中获取连接mysql.createPoolCluster() 创建连接池集群，连接池集群可以提供多个主机连接mysqljs文档中推荐使用第一种方式：每次请求建立一个连接，但是由于频繁的建立、关闭数据库连接，会极大的降低系统的性能，所以我选择了使用连接池的方式，如果对性能有更高的要求，安装了MySQL 集群，可以选择使用连接池集群。 1. 数据库配置将数据库相关的配置添加到公用的配置文件中，方便项目的初始化。 config.jsmodule.exports = { … // mysql数据库配置 mysql: { // 主机 host: 'localhost', // 端口 port: 3306, // 用户名 user: 'root', // 密码 password: '123456', // 数据库名 database: 'server-demo', // 连接池允许创建的最大连接数，默认值为10 connectionLimit: 50, // 允许挂起的最大连接数,默认值为0,代表挂起的连接数无限制 queueLimit: 0 }};connectionLimit 和 queueLimit 是数据连接池特有的配置项。 connectionLimit 是指连接池允许创建的最大连接数，默认值为10。当获取连接时，如果连接池中有空闲的连接则直接返回一个空闲连接。如果所有连接都被占用，则判断连接池中的连接数是否达到了允许的最大数，如果未达到则创建新的连接，如果已达到则获取连接的请求挂起，等待其他请求完成操作后释放的连接。queueLimit 是指允许挂起的最大连接数，默认值为0，代表挂起的连接数无限制。当连接池中允许创建的所有连接都被占用时，获取连接的请求挂起，等待可用的连接，所有挂起的请求形成一个队列，queueLimit则是指这个队列的最大长度。需要注意的是，当queueLimit为0时并不表示不允许挂起，而是表示对挂起的数目没有限制。2. 创建连接池db/pool.js/** * 数据库连接池 */const mysql = require('mysql');const config = require('../config');// 创建数据库连接池const pool = mysql.createPool(config.mysql);pool.on('acquire', function (connection) { console.log(`获取数据库连接 [${connection.threadId}]`);});pool.on('connection', function (connection) { console.log(`创建数据库连接 [${connection.threadId}]`);});pool.on('enqueue', function () { console.log('正在等待可用数据库连接');});pool.on('release', function (connection) { console.log(`数据库连接 [${connection.threadId}] 已释放`);});module.exports = pool;创建数据库连接池pool后，就可以通过pool获取数据库连接了，另外通过监听连接池的事件可以了解连接池中连接的使用情况。如果将connectionLimit 设为2，queueLimit 设为0，当同时有5个请求获取数据库连接时，线程池的事件日志如下： ...

创建用户create user 'test1'@'localhost' identified by '密码';Mysql8.0默认采用caching-sha2-password加密，目前好多客户端不支持，可改为mysql_native_password; create user 'test'@'%' identified with mysql_native_password BY '密码';其中localhost指本地才可连接 可以将其换成%指任意ip都能连接 也可以指定ip连接 修改密码Alter user 'test1'@'localhost' identified by '新密码';flush privileges;授权grant all privileges on *.* to 'test1'@'localhost' with grant option;with gran option表示该用户可给其它用户赋予权限，但不可能超过该用户已有的权限 比如a用户有select,insert权限，也可给其它用户赋权，但它不可能给其它用户赋delete权限，除了select,insert以外的都不能 这句话可加可不加，视情况而定。 all privileges 可换成select,update,insert,delete,drop,create等操作 如：grant select,insert,update,delete on . to 'test1'@'localhost'; 第一个*表示通配数据库，可指定新建用户只可操作的数据库 如：grant all privileges on 数据库.* to 'test1'@'localhost'; 第二个*表示通配表，可指定新建用户只可操作的数据库下的某个表 如：grant all privileges on 数据库.指定表名 to 'test1'@'localhost'; 刷新权限flush privileges;查看用户授权信息show grants for 'test1'@'localhost;撤销权限revoke all privileges on *.* from 'test1'@'localhost';用户有什么权限就撤什么权限 ...

首先应认真看下阿里云官方文档：RDS MySQL 物理备份文件恢复到自建数据库 本人实践的数据库版本是5.6，恢复数据的Ubuntu版本是14.04和16.04两个版本，这两个版本默认数据库都不是5.6，需要单独安装；另外Percona Xtrabackup的版本也请按照阿里云的提示下载对应版本。我使用的是2.3.7apt-get install software-properties-common sudo add-apt-repository 'deb http://archive.ubuntu.com/ubuntu trusty universe' sudo apt-get update sudo apt install mysql-server-5.6# 建议这里直接安装5.6.36+版本，可能需要通过源码，或者去mysql官方下载Ubuntu安装包，官方没有Ubuntu 16.04的包，可以选择Ubuntu 14的预编译包前面步骤如阿里云官方文档所示即可，但是在恢复数据库时遇到了问题 关闭selinuxsetenforce 0调整apparmor对mysql目录限制## vim /etc/apparmor.d/usr.sbin.mysqld/root/mysql/data/ r,/root/mysql/data/** rwk,## 增加数据目录权限即可，如果安装了5.6.36可能要调整配置文件目录权限，看错误提示操作即可/etc/mysql/mysql.conf.d/ r,/etc/mysql/mysql.conf.d/* r,在启动过程中，可能会遇到某些未知问题，可以通过添加innodb_force_recovery = 6参数来尝试修复，如果仍然出现下面错误，请升级mysql版本： It is possible that mysqld could use up to key_buffer_size + (read_buffer_size + sort_buffer_size)*max_threads = 338332 K bytes of memoryHope that's ok; if not, decrease some variables in the equation.Thread pointer: 0x0Attempting backtrace. You can use the following information to find outwhere mysqld died. If you see no messages after this, something wentterribly wrong...stack_bottom = 0 thread_stack 0x40000这个问题，在mysql后续版本中解决掉了，保证你的mysql版本在5.6.36+以上，我是升级到5.6.44解决的问题 ...

序本文主要研究一下SpinalTap的Transaction TransactionSpinalTap/spinaltap-model/src/main/java/com/airbnb/spinaltap/mysql/Transaction.java @Value@RequiredArgsConstructorpublic class Transaction { private final long timestamp; private final long offset; private final BinlogFilePos position; private final String gtid; public Transaction(long timestamp, long offset, BinlogFilePos position) { this.timestamp = timestamp; this.offset = offset; this.position = position; this.gtid = null; }}Transaction定义了timestamp、offset、position、gtid属性MysqlMutationMetadataSpinalTap/spinaltap-model/src/main/java/com/airbnb/spinaltap/mysql/mutation/MysqlMutationMetadata.java @Value@ToString(callSuper = true)@EqualsAndHashCode(callSuper = true)public class MysqlMutationMetadata extends Mutation.Metadata { private final DataSource dataSource; private final BinlogFilePos filePos; private final Table table; private final long serverId; private final Transaction beginTransaction; private final Transaction lastTransaction; /** The leader epoch of the node resource processing the event. */ private final long leaderEpoch; /** The mutation row position in the given binlog event. */ private final int eventRowPosition; public MysqlMutationMetadata( DataSource dataSource, BinlogFilePos filePos, Table table, long serverId, long id, long timestamp, Transaction beginTransaction, Transaction lastTransaction, long leaderEpoch, int eventRowPosition) { super(id, timestamp); this.dataSource = dataSource; this.filePos = filePos; this.table = table; this.serverId = serverId; this.beginTransaction = beginTransaction; this.lastTransaction = lastTransaction; this.leaderEpoch = leaderEpoch; this.eventRowPosition = eventRowPosition; }}MysqlMutationMetadata定义了dataSource、filePos、table、serverId、beginTransaction、lastTransaction、leaderEpoch、eventRowPosition属性MysqlMutationMapperSpinalTap/spinaltap-mysql/src/main/java/com/airbnb/spinaltap/mysql/event/mapper/MysqlMutationMapper.java ...

MVCC：多版本并发控制，不加锁就让多个事务并发读写，提高并发的读写性能当前读&&快照读？？ 当前读：就是它读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁；像 select .....lock in share mode (共享锁), select ..... for update (排他锁) ;  update, insert ,delete (排他锁) 这些操作都是一种当前读。当前读实际上是一种加锁的操作，是悲观锁的实现。 快照读：快照读的实现是基于多版本并发控制，既然是基于多版本，即快照读可能读到的并不一定是数据的最新版本，而有可能是之前的历史版本。快照读的前提是隔离级别不是串行级别，串行级别下的快照读会退化成当前读；像不加锁的select * from 操作就是快照读，即不加锁的非阻塞读，不涉及其他锁之间的冲突；事务ID 当每个事务开启时，都会被分配一个ID, 这个ID是递增的，所以最新的事务，ID值越大 MVCC模型在MySQL中的具体实现原理： 1.是由  3个隐式字段，undo日志 ，Read View 等去完成的2.旧数据存储在UNDO中，再通过DB_ROLL_PTR 回溯查找历史版本3.通过read view判断行记录是否可见隐藏字段 每行记录除了我们自定义的字段外，还有数据库隐式定义的字段                     DB_TRX_ID                    6byte，事务ID：记录创建这条记录/最后一次修改该记录的事务ID                     DB_ROLL_PTR                     7byte，回滚指针，指向这条记录的上一个版本                     DB_ROW_ID                     6byte，隐含的自增ID（隐藏主键），如果数据表没有主键，InnoDB会自动以DB_ROW_ID产生一个聚簇索引 例如下图： （新insert记录的DB_ROLL_PTR指针为NULL。修改新值后，记录的 DB_ROLL_PTR 回滚指针指向原始值在Undo Log 日志的位置） 以一条SQL为例子： 事务隔离级别的区别：read view （一致性视图） § RR隔离级别下，在每个事务开始的时候，会将当前系统中的所有的活跃事务拷贝到一个列表中(read view)。 § RC隔离级别下，在事务中的每个语句开始（select)时，会将当前系统中的所有的活跃事务拷贝到一个列表中(read view) 然后按照以下逻辑判断事务的可见性 如果上面的还是没有看懂，可以参看下面的逻辑图： 如果查询出来的结果是，数据不可见，那么就需要去undo log中查找最新的已提交的数据，通过undolog 中记录的事务id 和 read view做比较，不在readview且最大的事务id就是满足条件的数据了。! 总结：说白了MVCC就是为了实现 读(select)-写冲突 不加锁，而这个读指的就是快照读。在读(select)操作时不用阻塞写操作，写操作也不用阻塞读操作。 ...

留坑: 是否了解 rpm/yum/repo之间的关系 1. 官网下载rpm源MySQL Community Downloads 2. rz -be 上传下载的 mysql80-community-release-el7-3.noarch.rpm 文件到 centos7 某个任一目录下:3. 将rpm -ivh mysql80-community-release-el7-3.noarch.rpm4. 而后进入: cd /etc/yum.repos.d/ 发现多了mysql的:[root@niewj yum.repos.d]# ll总用量 20-rw-r--r-- 1 root root 1023 5月 29 15:37 CentOS-Base.repo-rw-r--r-- 1 root root 323 5月 29 15:37 epel.repo-rw-r--r-- 1 root root 2076 4月 25 2019 mysql-community.repo-rw-r--r-- 1 root root 2108 4月 25 2019 mysql-community-source.repo-rw-r--r-- 1 root root 182 6月 1 13:10 nginx.repo5. 然后 yum clean all yum makecache6. yum install mysql-community-server完成即可;7. 加入systemd管理:开机启动: systemctl enable mysqld.service停止服务: systemctl stop mysqld.service启动服务: systemctl start  mysqld.service重启服务: systemctl restart  mysqld.service查看状态: systemctl status mysqld.service上面的".service"都是可以省略的 ...

(一) 引言当你登录你的QQ账号和别人吹水的时候，当你期末交作业去度娘复制的时候，当你在工作中登录一些网站的时候，再比如当你查看自己几乎没有什么余额的银行卡的时候（捂脸），或许自己也没注意，（非需要，大部分人也不想注意哈哈哈）我们一直都在以某种方式与数据库打交道，或许你是一个开发人员，也或许你也只是一个普通的用户，数据库与我们的生活息息相关，作为一名程序员，现在学习 MySQL 和 Oracle 的会多一些，像微软的 SQL Server 以前配合 asp 的时候用的还是挺多的，不过现在就没那么流行了， 所以我们先从 MySQL 讲起，同时文章中穿插一些 SQL 的基本概念，我觉得能更好的理解这几种 SQL 型数据库哈 关于 MySQL 的几篇文章，在前一年左右大概更新过几篇，但是都差强人意，所以最近加班加点，重新将 MySQL 相关的，例如语法、约束、事务等等重新整理一遍，第一篇先来看一下入门的一些语法和操作 既然你都打开这篇文章了，那你就注定不是一个村民，这一局我们跳 Java 程序员！ 开发学习中，想满足一些需求，与数据打交道这可太频繁了，如何存储这些数据就是你考虑的问题了，当然不考虑性能，管理、安全等等，你的选择可就多了，存个 txt，存个xml，甚至还可以存个excel等等，有位刚入门朋友曾经问过我这样一个问题 “他在学习IO的一些技术的时候，常常会简单的保存一些数据到 .txt 文件中，为什么还需要数据库呢？”，这样的一些小Demo，大家在Java基础的学习中应该都是做过的，那么我们下面就从这个问题讲起~ (二) 为什么使用数据库？(1) 查询速度假定我们将数据全部存储在一个普通文件中(data.txt)_(以逗号间隔) ......"Eminem",male,1972,"USA""Rihanna",female,1988,"Barbados""Taylor Swift",1989,female,"USA""Aavril Lavigne",1984,female,"Canada"......如果在我们的数据极为庞大的情况下，我们需要查询其中的一些数据，例如，查询Eminem的国籍，我们一般会使用遍历，但是毫无疑问，这个响应时间会变得极其缓慢，但是使用数据库后，它所提供的一些索引技术等就可以解决这样的问题 (2) 数据完整有效"Rihanna",female,1988,"Barbados""Rihanna",female,1995,"USA"如果数据中出现了重名的情况，又如何判断是不是同一个人呢？ 如果data.txt文件中的数据被错误的修改，例如出生年份被修改为其他类型字符串这种无效表达怎么办？ 或许你可以在程序中写一些逻辑判断语句进而筛选处理这些问题，但是仍旧在数据较为庞大的情况下，会出现各种各样的问题，增加了开发者的开发难度，而数据库本身就制定了一些约束，从而保证了数据的完整且，有效，从而使开发者只需要更加注重于程序本身的设计，而不用花费过多的时间去处理数据上的一些细节问题 (3) 数据共享常用来简单存储数据的 txt/excel 等均属于单一文件，并且都是无法共享的，只支持当前用户使用并且修改 而数据库则允许用户共享，不同的用户可以同时存取数据库中的数据，用户也可以用各种方式通过接口使用数据库，并提供数据共享 (4) 数据的安全性在我们前者中，数据的修改是很随意的但是在实际开发中，我们有时候需要面临，多个用户检索、修改同一文件中的数据，或者在并发情况下，写同一文件或者记录，而数据库基于锁等的一些技术便可以帮助我们解决这些问题 (5) 故障恢复由于逻辑或者物理上的错误，导致了系统的错误操作，从而使得数据被破坏，如何快速恢复数据，我们上面的单一文件系统显然无法帮助我们解决问题，而数据库却有相关机制去弥补、处理相关问题 上面我们仅仅从几个常见的点分析了为什么使用数据库，当然远远不止这些，所以总的来说就是数据库其特殊的存储以及管理方式，既提高了效率，也极大的减少了开发人员的负担 总结一下：数据库 em... 是个好东西！ (三) MySQL 基本操作至于安装的部分，这里就不提及了，否则篇幅会过于的长，无论是选择安装版本也好，免安装的压缩包也好，选对版本，装好就行了，可以去网上参考一些教程，至于版本 5.5 -> 5.6 -> 5.7 -> 8 都是可以的，毕竟只是单纯的学习 MySQL，不用过多的考虑和 Java 等的版本或者配置问题，我这台机器版本为 5.7 哈 ...

MySQL的索引入门真的很难吗MySQL的索引入门真的很难吗 索引存在的意义索引的类型 哈希索引二叉树跳表B+Tree索引的分类 功能上区分 普通索引主键索引唯一索引前缀索引全文索引从索引个数上区分 联合索引最左前缀从磁盘角度区分 聚簇索引，非聚簇索引 回表索引下推 * [总结](#%E6%80%BB%E7%BB%93) 经常在开发中碰到同事说，数据查询好慢，第一个反应就是给表加个索引。从而引发想去探索下我们常说的索引究竟是什么？难道只需要加个索引就能解决数据库查询问题吗？ 带着这个问题我们开始探究MySQL中的索引究竟是什么，它能帮助我们做些什么。 脑图 索引存在的意义在现有程序业务中，数据库作为存储的重要一环，不可或缺，而对于数据库的操作无外乎是增删改查，但随着数据量的增加，数据库的性能就成为最重要的一环，数据查询不能慢，数据查询一慢，用户体验就会差。 如何在保证数据存储中的增删改查效率呢？就成了一个必不可少的设计。 在Mysql这样的数据存储中总是少不了一个东西--->索引，索引就类似于我们看书的目录，使用书籍的目录可以帮助快速的定位到知识点的页数，而索引也是同样的目的，快速检索到数据。 那就可以总结出索引的目的：「提高数据的检索速度」。 索引的类型既然索引有提高检索的速度，那就给数据库的查询操作都加上索引，让他们飞快的运行，这事还真不能急，为啥？数据库的索引种类有好多种，万一索引用的不对，引发的不是加快数据库的运行，而是众多的慢查询会将整个数据库拖垮。 用于提高读写效率的数据结构种类这么多，那我们来了解下数据库中常见的索引类型都有哪些。 索引类型 哈希索引 二叉树 跳表 B+Tree 哈希索引哈希索引简单来说就是Key-Value模型，我们只要通过给定的Key就可以查找到对应的Value，十分快速方便。 不过你要明白哈希索引是通过哈希函数对Key进行计算，换算成数据存储位置，随着数据量的增加，不可避免会出现不同的Key经过哈希函数计算后出现相同的数据存储位置。 这种情况怎么解决呢？业界通用的方式是当出现位置一样的数据结果，会在该结果后面链接一个链表，将相同的数据放入到链表中。 更进一步当相同位置中的数据越来越多，查询数据时会将链表中的数据遍历，速度也是慢，这时候可以采用将链表进行树化，二叉树的查找速度还是很快的。 ⬇️图是数据举例说明: 哈希索引只适合用来查找等值的数据，而不是适合范围索引，排序等操作。常见的哈希索引是在Redis中。 二叉树数据结构中存在「树」数据结构，虽然树的结构多种多样，但是常用的数据结构是二叉树，二叉树是拥有两个分叉的树，分别为左子节点与右子节点。以此类推，动物中有八爪鱼，同样的也存在八叉树，你可以想象八叉树是什么样子。 二叉树的特点是，左节点的值<父节点<右节点。如果要查找到一个值就可以按照子节点的顺序进行查找. 随着数据量的增大，二叉树的高度也会主键递增，数据库存储的数据并不是都放到内存中，而是要放到磁盘上，磁盘的访问速度是比内存慢几十倍。 现在假如一个树高30,每次搜索树一次就需要访问一次硬盘,一次访问磁盘速度假设是10ms，树高30至少需要访问磁盘30次才能获取到数据，30*10=300ms。 如果数据更多，树高到100，获取一次数据成本就很高了。 为了解决这个问题，可以使用N叉树的方式来降低树的高度，「减少访问磁盘的次数，这样就能提高效率。」 跳表跳表是建立在多层级链表上的数据结构，通过一层层的链表查询就提高了检索数据的效率。 B+TreeMysql中索引的实现是建立在数据库引擎上的，而在Mysql中有多个数据库引擎，常用的数据库引擎是InnoDB. InnoDB引擎索引实现是使用B+Tree索引模型，其实还有一种BTree模型，B+Tree是建立在BTree基础上发展的。 B+Tree可以认为是BTree的改进版本： ❝注意子节点与叶子节点是不同的概念。把没有子节点的节点叫做「叶子节点」 ❞ 在B+Tree中子节点只存储索引，而在B树中是存储数据的。B树中的叶子节点并不需要使用链表连串联，而B+树中是用链表连接起来的。B+树中的叶子节点存储数据.数据库中每一个索引都能对应到一颗B+树，一个表是可以存在多个B+树。 不管是B+Tree还是BTree都是利用多叉树(「该树有多少叉是根据页的大小进行计算好的，索引会涉及到新增删除，同样的就会涉及到页的分裂与合并」)，保证不把所有的索引数据放入到内存上，降低磁盘的访问次数加快数据访问。 索引的分类Mysql中常用Innodb引擎,组织数据库索引的方式就是B+Tree。 B+Tree是索引组织表，那在B+Tree有多少种索引的类型呢？ 从不同的方向划分可以划分为不同的类型。 功能上区分主要为普通索引，主键索引，唯一索引，前缀索引，全文索引，哈希索引。 普通索引普通索引就是我们常用的索引创建-> 创建单个索引，相关语句如下 alter table table_name add index index_name(column);drop index index_name on table_name;ALTER TABLE table_name DROP INDEX index_name主键索引主键索引是在普通索引的基础上增加两种约束条件分别为唯一和不能为空。主键索引在Innodb中用来维护索引组织的性质，所以，在使用Innodb引擎时，建议你的表都设置主键。 创建主键可以在创建的表的时候指定 primary key('id'),也可以创建联合主键primary key ('id','name'). 创建主键的相关SQL # 当表里面没有主键索引时，增加主键索引ALTER TABLE table_name ADD PRIMARY KEY ( `id` )# 删除主键索引ALTER TABLE table_name DROP INDEX name_index唯一索引唯一索引时在普通索引的基础上增加唯一的约束，在插入相关数据时，会检查该索引数据是否已经存在数据库中。 使用下面的创建语句创建: # 创建唯一索引ALTER TABLE table_name ADD UNIQUE (`column`)# 删除索引drop index index_name on table_name;前缀索引字符串在编程中经常遇到的，比如常用的邮箱,一些业务场景中需要对某些字符串的前缀进行匹配。 这就涉及到一个问题，不能使用索引的话，就只能进行全表扫描。数据量一大，该方式就会成为性能的瓶颈。 数据库中的前缀索引就是解决字符串前缀匹配的问题。 # 创建前缀索引alter table table_name  add index index_name(columns(6));# 删除索引drop index index_name on table_name;# 怎么计算前缀索引设计几个字符 使用下列语句进行估算select count(distinct 列名)/count(*)as a,COUNT(DISTINCT left(列名,100)) as b, COUNT(DISTINCT left(列名,110)) as c from 表名前缀索引有一个缺点就是无法使用覆盖索引的优化，必须回表查询。 全文索引全文索引是用来解决Mysql中文本匹配慢的问题，常使用like模糊搜索%内容%，没法用到前面列举的索引，这时候就可以尝试使用全文索引来解决该问题。 相关SQL文件看下? ...

序本文主要研究一下SpinalTap的MysqlEventFilter MysqlEventFilterSpinalTap/spinaltap-mysql/src/main/java/com/airbnb/spinaltap/mysql/event/filter/MysqlEventFilter.java public abstract class MysqlEventFilter implements Filter<BinlogEvent> { public static Filter<BinlogEvent> create( @NonNull final TableCache tableCache, @NonNull final Set<String> tableNames, @NonNull final AtomicReference<SourceState> state) { return ChainedFilter.<BinlogEvent>builder() .addFilter(new EventTypeFilter()) .addFilter(new TableFilter(tableCache, tableNames)) .addFilter(new DuplicateFilter(state)) .build(); }}MysqlEventFilter提供了create方法，使用ChainedFilter来构造BinlogEvent的Filter，默认添加了EventTypeFilter、TableFilter、DuplicateFilterEventTypeFilterSpinalTap/spinaltap-mysql/src/main/java/com/airbnb/spinaltap/mysql/event/filter/EventTypeFilter.java @RequiredArgsConstructorfinal class EventTypeFilter extends MysqlEventFilter { @SuppressWarnings("unchecked") private static final Set<Class<? extends BinlogEvent>> WHITELISTED_EVENT_TYPES = ImmutableSet.of( TableMapEvent.class, WriteEvent.class, UpdateEvent.class, DeleteEvent.class, XidEvent.class, QueryEvent.class, StartEvent.class, GTIDEvent.class); public boolean apply(@NonNull final BinlogEvent event) { return WHITELISTED_EVENT_TYPES.contains(event.getClass()); }}EventTypeFilter继承了MysqlEventFilter，其apply方法通过WHITELISTED_EVENT_TYPES来进行过滤TableFilterSpinalTap/spinaltap-mysql/src/main/java/com/airbnb/spinaltap/mysql/event/filter/TableFilter.java ...

as;dkakda;dkakdakdadjsdkaksdnasdnadsadkandakndkvadnkadsnakdakdnakdakda;ddkakdnk;adna;dwwieenkdnakna;ksddkna;kd;adsdkjnasdjalskdmaklda

B-Tree 平衡多路查找树B-Tree是为磁盘等外存储设备设计的一种平衡查找树。因此在讲B-Tree之前先了解下磁盘的相关知识。系统从磁盘读取数据到内存时是以磁盘块（block）为基本单位的，位于同一个磁盘块中的数据会被一次性读取出来，而不是需要什么取什么。 InnoDB存储引擎中有页（Page）的概念，页是其磁盘管理的最小单位。InnoDB存储引擎中默认每个页的大小为16KB，可通过参数innodb_page_size将页的大小设置为4K、8K、16K，在MySQL中可通过如下命令查看页的大小： mysql> show variables like 'innodb_page_size';而系统一个磁盘块的存储空间往往没有这么大，因此InnoDB每次申请磁盘空间时都会是若干地址连续磁盘块来达到页的大小16KB。InnoDB在把磁盘数据读入到内存时会以页为基本单位，在查询数据时如果一个页中的每条数据都能有助于定位数据记录的位置，这将会减少磁盘I/O次数，提高查询效率。 B-Tree结构的数据可以让系统高效的找到数据所在的磁盘块。为了描述B-Tree，首先定义一条记录为一个二元组[key, data] ，key为记录的键值，对应表中的主键值，data为一行记录中除主键外的数据。对于不同的记录，key值互不相同。 B-Tree中的每个节点根据实际情况可以包含大量的关键字信息和分支，如下图所示为一个3阶的B-Tree： 每个节点占用一个盘块的磁盘空间，一个节点上有两个升序排序的关键字和三个指向子树根节点的指针，指针存储的是子节点所在磁盘块的地址。两个关键词划分成的三个范围域对应三个指针指向的子树的数据的范围域。以根节点为例，关键字为17和35，P1指针指向的子树的数据范围为小于17，P2指针指向的子树的数据范围为17~35，P3指针指向的子树的数据范围为大于35。 模拟查找关键字29的过程： 根据根节点找到磁盘块1，读入内存。【磁盘I/O操作第1次】比较关键字29在区间（17,35），找到磁盘块1的指针P2。根据P2指针找到磁盘块3，读入内存。【磁盘I/O操作第2次】比较关键字29在区间（26,30），找到磁盘块3的指针P2。根据P2指针找到磁盘块8，读入内存。【磁盘I/O操作第3次】在磁盘块8中的关键字列表中找到关键字29。分析上面过程，发现需要3次磁盘I/O操作，和3次内存查找操作。由于内存中的关键字是一个有序表结构，可以利用二分法查找提高效率。而3次磁盘I/O操作是影响整个B-Tree查找效率的决定因素。B-Tree相对于AVLTree缩减了节点个数，使每次磁盘I/O取到内存的数据都发挥了作用，从而提高了查询效率。 B+TreeB+Tree是在B-Tree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构。 从上一节中的B-Tree结构图中可以看到每个节点中不仅包含数据的key值，还有data值。而每一个页的存储空间是有限的，如果data数据较大时将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数据量很大时同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响查询效率。在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子节点上只存储key值信息，这样可以大大加大每个节点存储的key值数量，降低B+Tree的高度。 B+Tree相对于B-Tree有几点不同： 非叶子节点只存储键值信息。所有叶子节点之间都有一个链指针。数据记录都存放在叶子节点中。将上一节中的B-Tree优化，由于B+Tree的非叶子节点只存储键值信息，假设每个磁盘块能存储4个键值及指针信息，则变成B+Tree后其结构如下图所示 通常在B+Tree上有两个头指针，一个指向根节点，另一个指向关键字最小的叶子节点，而且所有叶子节点（即数据节点）之间是一种链式环结构。因此可以对B+Tree进行两种查找运算：一种是对于主键的范围查找和分页查找，另一种是从根节点开始，进行随机查找。 可能上面例子中只有22条数据记录，看不出B+Tree的优点，下面做一个推算： InnoDB存储引擎中页的大小为16KB，一般表的主键类型为INT（占用4个字节）或BIGINT（占用8个字节），指针类型也一般为4或8个字节，也就是说一个页（B+Tree中的一个节点）中大概存储16KB/(8B+8B)=1K个键值（因为是估值，为方便计算，这里的K取值为〖10〗^3）。也就是说一个深度为3的B+Tree索引可以维护10^3 * 10^3 * 10^3 = 10亿 条记录。 实际情况中每个节点可能不能填充满，因此在数据库中，B+Tree的高度一般都在2-4层。MySQL的InnoDB存储引擎在设计时是将根节点常驻内存的，也就是说查找某一键值的行记录时最多只需要1-3次磁盘I/O操作。 数据库中的B+Tree索引可以分为聚集索引（clustered index）和辅助索引（secondary index）。上面的B+Tree示例图在数据库中的实现即为聚集索引，聚集索引的B+Tree中的叶子节点存放的是整张表的行记录数据。辅助索引与聚集索引的区别在于辅助索引的叶子节点并不包含行记录的全部数据，而是存储相应行数据的聚集索引键，即主键。当通过辅助索引来查询数据时，InnoDB存储引擎会遍历辅助索引找到主键，然后再通过主键在聚集索引中找到完整的行记录数据。

1.背景由于一个业务表越来越大，大概有550万。突然某天出现了慢查询13s+，排查下来，发现是因为order by 没有走索引，为什么，以及如何处理，请看接下来的分析 1.1 表结构已简化 CREATE TABLE `test` (`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT COMMENT 'row id',`content_id` bigint(20) unsigned NOT NULL DEFAULT '0' COMMENT 'contentID',`type` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',`province` varchar(100) CHARACTER SET utf8 NOT NULL DEFAULT '' COMMENT '地区',`time_created` bigint(20) NOT NULL DEFAULT '0' COMMENT '创建时间',PRIMARY KEY (`id`),KEY `idx_province_type_time_created_content_id` (`province`,`type`,`time_created`,`content_id`)) ENGINE =InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci COMMENT='test表'1.2 sql语句select content_id,time_created from test where province = 'shanghai' and type = 'aa' and time_created > 12345 order by province,type,time_created limit 101.3 mysql版本5.6 ...

序本文主要研究一下SpinalTap的BinlogEvent BinlogEventSpinalTap/spinaltap-mysql/src/main/java/com/airbnb/spinaltap/mysql/event/BinlogEvent.java @Getter@ToStringpublic abstract class BinlogEvent extends SourceEvent { private final long tableId; private final long serverId; private final BinlogFilePos binlogFilePos; public BinlogEvent(long tableId, long serverId, long timestamp, BinlogFilePos binlogFilePos) { super(timestamp); this.tableId = tableId; this.serverId = serverId; this.binlogFilePos = binlogFilePos; } public long getOffset() { return (binlogFilePos.getFileNumber() << 32) | binlogFilePos.getPosition(); } public boolean isMutation() { return this instanceof WriteEvent || this instanceof DeleteEvent || this instanceof UpdateEvent; }}BinlogEvent继承了SourceEvent，它定义了tableId、serverId、binlogFilePos属性StartEventSpinalTap/spinaltap-mysql/src/main/java/com/airbnb/spinaltap/mysql/event/StartEvent.java ...

1 启动mysql通过服务开启： 启动 service mysqld start停止 service mysqld stop 重启 service mysqld restart 2 连接数据库mysql -u 用户名 -p密码注意：-u和用户名之间可以用空格隔开，但是-p和密码必须连在一起。 -p与后面的字符串隔开，那么这个字符串就是数据库的名字了。 3 对库的操作1 创建库CREATE DATABASE [IF NOT EXISTS] 库名 CHARSET utf8[]中的内容是可以选择的 IF NOT EXIST表示如果这个表不存在就建立这个表。 2 查看库SHOW DATABASES SHOW CREATE DATABASE 库名 查看建库时的详细信息 3 修改库ALTER DATABASE [IF NOT EXISTS] 库名 [DEFAULT] CHARACTER SET 字符名4 删除库DROP DATABASE [IF EXISTS] 库名4 对表的操作1 增加表CREATE TABLE 表名( 列名 类型 ) 2 修改表①：修改表名RENAME TABLE old_table_name TO new_table_name;旧表( old_table_name)必须存在，而新表( new_table_name)一定不存在。如果新表 new_table_name 确实存在，该语句将失败。 ...

序本文主要研究一下SpinalTap的BinlogEventListener BinlogEventListenerSpinalTap/spinaltap-mysql/src/main/java/com/airbnb/spinaltap/mysql/binlog_connector/BinaryLogConnectorSource.java @Slf4jpublic final class BinaryLogConnectorSource extends MysqlSource { //...... private final class BinlogEventListener implements BinaryLogClient.EventListener { public void onEvent(Event event) { Preconditions.checkState(isStarted(), "Source is not started and should not process events"); final EventHeaderV4 header = event.getHeader(); final BinlogFilePos filePos = new BinlogFilePos( binlogClient.getBinlogFilename(), header.getPosition(), header.getNextPosition(), binlogClient.getGtidSet(), serverUUID); BinaryLogConnectorEventMapper.INSTANCE .map(event, filePos) .ifPresent(BinaryLogConnectorSource.super::processEvent); } } //......}SpinalTap的BinlogEventListener实现了BinaryLogClient.EventListener接口，其onEvent方法创建BinlogFilePos，然后使用BinaryLogConnectorEventMapper.INSTANCE进行转换，最后执行BinaryLogConnectorSource的processEvent方法BinlogFilePosSpinalTap/spinaltap-model/src/main/java/com/airbnb/spinaltap/mysql/BinlogFilePos.java @Slf4j@Getter@EqualsAndHashCode@NoArgsConstructor@JsonDeserialize(builder = BinlogFilePos.Builder.class)public class BinlogFilePos implements Comparable<BinlogFilePos>, Serializable { private static final long serialVersionUID = 1549638989059430876L; private static final Splitter SPLITTER = Splitter.on(':'); private static final String NULL_VALUE = "null"; public static final String DEFAULT_BINLOG_FILE_NAME = "mysql-bin-changelog"; @JsonProperty private String fileName; @JsonProperty private long position; @JsonProperty private long nextPosition; @JsonProperty private GtidSet gtidSet; @JsonProperty private String serverUUID; public BinlogFilePos(long fileNumber) { this(fileNumber, 4L, 4L); } public BinlogFilePos(String fileName) { this(fileName, 4L, 4L); } public BinlogFilePos(long fileNumber, long position, long nextPosition) { this(String.format("%s.%06d", DEFAULT_BINLOG_FILE_NAME, fileNumber), position, nextPosition); } public BinlogFilePos( String fileName, long position, long nextPosition, String gtidSet, String serverUUID) { this.fileName = fileName; this.position = position; this.nextPosition = nextPosition; this.serverUUID = serverUUID; if (gtidSet != null) { this.gtidSet = new GtidSet(gtidSet); } } public BinlogFilePos(String fileName, long position, long nextPosition) { this(fileName, position, nextPosition, null, null); } public static BinlogFilePos fromString(@NonNull final String position) { Iterator<String> parts = SPLITTER.split(position).iterator(); String fileName = parts.next(); String pos = parts.next(); String nextPos = parts.next(); if (NULL_VALUE.equals(fileName)) { fileName = null; } return new BinlogFilePos(fileName, Long.parseLong(pos), Long.parseLong(nextPos)); } @JsonIgnore public long getFileNumber() { if (fileName == null) { return Long.MAX_VALUE; } if (fileName.equals("")) { return Long.MIN_VALUE; } String num = fileName.substring(fileName.lastIndexOf('.') + 1); return Long.parseLong(num); } @Override public String toString() { return String.format("%s:%d:%d", fileName, position, nextPosition); } @Override public int compareTo(@NonNull final BinlogFilePos other) { if (shouldCompareUsingFilePosition(this, other)) { return getFileNumber() != other.getFileNumber() ? Long.compare(getFileNumber(), other.getFileNumber()) : Long.compare(getPosition(), other.getPosition()); } if (this.gtidSet.equals(other.gtidSet)) { return 0; } if (this.gtidSet.isContainedWithin(other.gtidSet)) { return -1; } return 1; } /** Check if two BinlogFilePos are from the same source MySQL server */ private static boolean isFromSameSource(BinlogFilePos pos1, BinlogFilePos pos2) { return pos1.getServerUUID() != null && pos1.getServerUUID().equalsIgnoreCase(pos2.getServerUUID()); } /** Whether we can compare two BinlogFilePos using Binlog file position (without GTIDSet) */ public static boolean shouldCompareUsingFilePosition(BinlogFilePos pos1, BinlogFilePos pos2) { return isFromSameSource(pos1, pos2) || pos1.getGtidSet() == null || pos2.getGtidSet() == null; } //......}BinlogFilePos定义了fileName、position、nextPosition、serverUUID属性BinaryLogConnectorEventMapperSpinalTap/spinaltap-mysql/src/main/java/com/airbnb/spinaltap/mysql/binlog_connector/BinaryLogConnectorEventMapper.java ...

问题我们都知道 MySQL 的 Table Cache 是表定义的缓存，江湖上流传着各种对这个参数的调优方法。本期我们通过实验来验证 Table Cache 的作用。 实验我们先创建一个测试数据库： 建一张空表： 建立一个连接，检查一下会话的初始状态： 在另一个窗口，开启 strace 追踪 MySQL 服务器的文件操作： 在 MySQL 中 select 新创建的表： 检查状态： 看到该操作没命中 table cache。查看 strace，确实发现 mysqld 进程打开了表结构文件（test_tbl.frm），如果我们在 strace 中也抓获 read 事件（参数改为 "-e file,read"），那可以看到 mysqld 确实读取了表结构文件。 我们再次在该会话中进行 select： 可以看到开始命中 table cache 了。在 strace 的输出中，也没有抓到新的文件操作。 可以看出 table cache 的作用，就是节约读取表结构文件的开销。 那不命中 table cache，一定会有读取表结构文件的开销么？ 我们开一个新的会话，这里增加了一个标识来区分会话： 在新会话中进行 select，查看状态： ...

Explain有什么用当Explain 与 SQL语句一起使用时，MySQL 会显示来自优化器关于SQL执行的信息。也就是说，MySQL解释了它将如何处理该语句，包括如何连接表以及什么顺序连接表等。 表的加载顺序sql 的查询类型可能用到哪些索引，哪些索引又被实际使用表与表之间的引用关系一个表中有多少行被优化器查询 .....Explain有哪些信息Explain 执行计划包含字段信息如下：分别是 id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、Extra 12个字段。下边我们会结合具体的SQL示例，详细的解读每个字段以及每个字段中不同参数的含义，以下所有示例数据库版本为 MySQL.5.7.17。 mysql> select version() from dual; +------------+ | version()  | +------------+ | 5.7.17-log | +------------+我们创建三张表 one、two、three，表之间的关系 one.two_id = two.two_id AND two.three_id = three.three_id。 Explain执行计划详解一、idid： ：表示查询中执行select子句或者操作表的顺序，id的值越大，代表优先级越高，越先执行。 id大致会出现 3种情况： 1、id相同看到三条记录的id都相同，可以理解成这三个表为一组，具有同样的优先级，执行顺序由上而下，具体顺序由优化器决定。 mysql> EXPLAIN SELECT * FROM one o,two t, three r WHERE o.two_id = t.two_id AND t.three_id = r.three_id; +----+-------------+-------+------------+--------+---------------+---------+---------+----------------------+------+----------+----------------------------------------------------+ | id | select_type | table | partitions | type   | possible_keys | key     | key_len | ref                  | rows | filtered | Extra                                              | +----+-------------+-------+------------+--------+---------------+---------+---------+----------------------+------+----------+----------------------------------------------------+ |  1 | SIMPLE      | o     | NULL       | ALL    | NULL          | NULL    | NULL    | NULL                 |    2 |      100 | NULL                                               | |  1 | SIMPLE      | t     | NULL       | ALL    | PRIMARY       | NULL    | NULL    | NULL                 |    2 |       50 | Using where; Using join buffer (Block Nested Loop) | |  1 | SIMPLE      | r     | NULL       | eq_ref | PRIMARY       | PRIMARY | 4       | xin-slave.t.three_id |    1 |      100 | NULL                                               | +----+-------------+-------+------------+--------+---------------+---------+---------+----------------------+------+----------+----------------------------------------------------+2、id不同如果我们的 SQL 中存在子查询，那么 id的序号会递增，id值越大优先级越高，越先被执行 。当三个表依次嵌套，发现最里层的子查询 id最大，最先执行。 mysql> EXPLAIN select * from one o where o.two_id = (select t.two_id from two t where t.three_id = (select r.three_id from three r where r.three_name='我是第三表2'));+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+| 1 | PRIMARY | o | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 50 | Using where || 2 | SUBQUERY | t | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 50 | Using where || 3 | SUBQUERY | r | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 50 | Using where |+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+3、以上两种同时存在将上边的 SQL 稍微修改一下，增加一个子查询，发现 id的以上两种同时存在。相同id划分为一组，这样就有三个组，同组的从上往下顺序执行，不同组 id值越大，优先级越高，越先执行。 ...

前言： 分区是一种表的设计模式，通俗地讲表分区是将一大表，根据条件分割成若干个小表。但是对于应用程序来讲，分区的表和没有分区的表是一样的。换句话来讲，分区对于应用是透明的，只是数据库对于数据的重新整理。本篇文章给大家带来的内容是关于MySQL中分区表的介绍及使用场景，有需要的朋友可以参考一下，希望对你有所帮助。 1.分区的目的及分区类型MySQL在创建表的时候可以通过使用PARTITION BY子句定义每个分区存放的数据。在执行查询的时候，优化器根据分区定义过滤那些没有我们需要的数据的分区，这样查询就可以无需扫描所有分区，只需要查找包含需要数据的分区即可。 分区的另一个目的是将数据按照一个较粗的粒度分别存放在不同的表中。这样做可以将相关的数据存放在一起，另外，当我们想要一次批量删除整个分区的数据也会变得很方便。 下面简单介绍下四种常见的分区类型： RANGE分区：最为常用，基于属于一个给定连续区间的列值，把多行分配给分区。最常见的是基于时间字段。LIST分区：LIST分区和RANGE分区类似，区别在于LIST是枚举值列表的集合，RANGE是连续的区间值的集合。HASH分区：基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL中有效的、产生非负整数值的任何表达式。KEY分区：类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL服务器提供其自身的哈希函数。必须有一列或多列包含整数值。上述四种分区类型中，RANGE分区即范围分区是最常用的。RANGE分区的特点是多个分区的范围要连续，但是不能重叠，默认情况下使用VALUES LESS THAN属性，即每个分区不包括指定的那个值。 2.分区操作示例本节内容以RANGE分区为例，介绍下分区表相关的操作。 # 创建分区表mysql> CREATE TABLE `tr` ( -> `id` INT, -> `name` VARCHAR(50), -> `purchased` DATE -> ) ENGINE=InnoDB DEFAULT CHARSET=utf8 -> PARTITION BY RANGE( YEAR(purchased) ) ( -> PARTITION p0 VALUES LESS THAN (1990), -> PARTITION p1 VALUES LESS THAN (1995), -> PARTITION p2 VALUES LESS THAN (2000), -> PARTITION p3 VALUES LESS THAN (2005), -> PARTITION p4 VALUES LESS THAN (2010), -> PARTITION p5 VALUES LESS THAN (2015) -> );Query OK, 0 rows affected (0.28 sec)# 插入数据mysql> INSERT INTO `tr` VALUES -> (1, 'desk organiser', '2003-10-15'), -> (2, 'alarm clock', '1997-11-05'), -> (3, 'chair', '2009-03-10'), -> (4, 'bookcase', '1989-01-10'), -> (5, 'exercise bike', '2014-05-09'), -> (6, 'sofa', '1987-06-05'), -> (7, 'espresso maker', '2011-11-22'), -> (8, 'aquarium', '1992-08-04'), -> (9, 'study desk', '2006-09-16'), -> (10, 'lava lamp', '1998-12-25');Query OK, 10 rows affected (0.03 sec)Records: 10 Duplicates: 0 Warnings: 0创建后可以看到，每个分区都会对应1个ibd文件。上面创建语句还是很好理解的，在此分区表中，通过YEAR函数取出DATE日期中的年份并转化为整型，年份小于1990的存储在分区p0中，小于1995的存储在分区p1中，以此类推。请注意，每个分区的定义顺序是从最低到最高。为了防止插入的数据因找不到相应分区而报错，我们应该及时创建新的分区。下面继续展示关于分区维护的其他操作。 ...

楔子由于之前电脑上安装的MySQL版本是比较老的了，大概是5.1的版本，不支持JSON字段功能。而最新开发部门开发的的编辑器产品，使用到了JSON字段的功能。 因此需要升级MySQL版本，升级的目标版本是MySQL 5.7.30（虽然最新版本已经到8.x，但是5.7基本够用了）。 发现在升级安装过程中，会有一些坑，所以使用本文记录一下。 卸载老版本首先需要卸载老的版本，卸载其实也挺简单，只需要移除MySQL服务即可，首先打开CMD，然后cd到MySQL的bin目录，然后输入下面的命令移除MySQL服务： mysqld --remove mysql//需要注意的是： “msyql” 是服务名称，安装的时候如果没有指定服务名称，//默认名称是MySQL（windows下不区分大小写）；// 如果安装的时候指定了名称，上面的名称需要修改为该指定的名称。 需要注意的一点是，我们删除了MySQL服务之后，有可能在windows的服务的列表里面还有，保留在哪儿也没啥影响，但是如果你又洁癖，一定要删除，就需要去删除注册表中对应键，参考下图。当然对于我们升级来说，后续还需要安装windows服务，可以直接覆盖。注册表的图 然后删除老的MySQL相关文件夹，以及删除相关的环境变量的配置。 需要注意的是，如果数据文件和MySQL程序在一个文件夹下，需要保留数据安装5.7.30版本接下来就是安装5.7.30版本,第一步是下载。 下载MySQL下载地址是：https://dev.mysql.com/downloads/mysql/点击上面链接，进入到下载页面，由于最新的版本是8.x，所以默认是8.x的版本。可以通过点击“Looking for the latest GA version ”选择版本，然后下载免安装版本： 解压MySQL下载完成后，解压到你想要安装的目录，比如我的目录是：D:Program Files (x86)mysql-5.7.30-winx64 配置环境变量在系统变量path后面追加D:Program Files (x86)mysql-5.7.30-winx64bin，如图所示。 创建配置文件my.ini5.7.30版本的安装包默认是不带配置文件my.ini的。所以需要创建自己创建一个my.ini的文件。手动创建my.ini文件，然后输入如下内容： [mysql]# 设置mysql客户端默认字符集default-character-set=utf8[mysqld]# 设置3306端口port = 3306# 设置mysql安装目录basedir=D:\Program Files (x86)\mysql-5.7.30-winx64# 设置mysql数据库的数据存放目录datadir=D:\Program Files (x86)\mysql-5.7.30-winx64\data# 运行最大连接数max_connections=200# 服务端使用的字符集默认为8比特编码的latin1字符集character-set-server=utf8# 参加新表时将使用的默认存储引擎default-storage-engine=INNODB此处会有一些坑，需要避免。 my.ini注意事项一my.ini必须保存为ANSI格式(配置文件默认就是是ANSI编码格式，可能会不小心保存为其他格式，比如UTF-8)，否正会出现后续的服务不能启动的情况。 如果你不确定my.ini是什么格式，可以另存为： my.ini注意事项二如果是从低版本升级到5.7的版本，可能为了保留之前的配置，就直接把之前的配置文件拷贝过来。这个时候就要注意了，因为以前很多低版本的参数，已经不适合高版本的。如果不修改过来，就会导致后续的服务启动失败。 比如笔者遇到的一个导致错误的参数是： #table_cache=256 // 低版本的table_open_cache=256 // 高版本的如果一定要保留老版本的配置，又对于新版本的参数修改不是很熟悉，那么可以再配置文件中把日志功能假设，这样再报错的时候，可以通过查看日志了解详情。 如下配置日志路径即可： log_error=D:/mysql-5.7/error.log然后启动失败后，可以通过日志查看报错，比如： 更多可能的错误参数： unknown variable 'key-buffer=256M' //去掉改项即可。unknown variable 'table-cache=512' //修改为table_open_cache即可。unknown variable 'thread-concurrency=8' //去掉改项，5.7已结废弃。windows 服务启动失败，不会把错误详情标识出来，只会简单说一句服务启动失败。 只能通过配置日志文件来查看。安装MySQL服务最重要的一步就是安装MySQL服务。 首先进入cmd界面，CD到MySQL的bin目录（注意此处一定要到bin目录下去执行），执行如下命令： ...

在上一篇文章《锁的类型以及加锁原理》主要总结了 MySQL 锁的类型和模式以及基本的加锁原理，今天我们就从原理走向实战，分析常见 SQL 语句的加锁场景。了解了这几种场景，相信小伙伴们也能举一反三，灵活地分析真实开发过程中遇到的加锁问题。 如下图所示，数据库的隔离等级，SQL 语句和当前数据库数据会共同影响该条 SQL 执行时数据库生成的锁模式，锁类型和锁数量。 下面，我们会首先讲解一下隔离等级、不同 SQL 语句 和 当前数据库数据对生成锁影响的基本规则，然后再依次具体 SQL 的加锁场景。 隔离等级对加锁的影响MySQL 的隔离等级对加锁有影响，所以在分析具体加锁场景时，首先要确定当前的隔离等级。 读未提交（Read Uncommitted 后续简称 RU）：可以读到未提交的读，基本上不会使用该隔离等级，所以暂时忽略。读已提交（Read Committed 后续简称 RC）：存在幻读问题，对当前读获取的数据加记录锁。可重复读（Repeatable Read 后续简称 RR）：不存在幻读问题，对当前读获取的数据加记录锁，同时对涉及的范围加间隙锁，防止新的数据插入，导致幻读。序列化（Serializable）：从 MVCC 并发控制退化到基于锁的并发控制，不存在快照读，都是当前读，并发效率急剧下降，不建议使用。这里说明一下，RC 总是读取记录的最新版本，而 RR 是读取该记录事务开始时的那个版本，虽然这两种读取的版本不同，但是都是快照数据，并不会被写操作阻塞，所以这种读操作称为 快照读（Snapshot Read） MySQL 还提供了另一种读取方式叫当前读（Current Read），它读的不再是数据的快照版本，而是数据的最新版本，并会对数据加锁，根据语句和加锁的不同，又分成三种情况： SELECT ... LOCK IN SHARE MODE：加共享(S)锁SELECT ... FOR UPDATE：加排他(X)锁INSERT / UPDATE / DELETE：加排他(X)锁当前读在 RR 和 RC 两种隔离级别下的实现也是不一样的：RC 只加记录锁，RR 除了加记录锁，还会加间隙锁，用于解决幻读问题。 不同 SQL 语句对加锁的影响不同的 SQL 语句当然会加不同的锁，总结起来主要分为五种情况： SELECT ... 语句正常情况下为快照读，不加锁；SELECT ... LOCK IN SHARE MODE 语句为当前读，加 S 锁；SELECT ... FOR UPDATE 语句为当前读，加 X 锁；常见的 DML 语句（如 INSERT、DELETE、UPDATE）为当前读，加 X 锁；常见的 DDL 语句（如 ALTER、CREATE 等）加表级锁，且这些语句为隐式提交，不能回滚。其中，当前读的 SQL 语句的 where 从句的不同也会影响加锁，包括是否使用索引，索引是否是唯一索引等等。 ...

1. 碎碎念最近在业务中遇到一个问题，业务是这样的：在插入新用户时需要校验用户的某些信息是否唯一，而在程序中校验结果永远是不唯一的。然后我把 MyBatis 打印的执行 SQL 语句拿了出来在数据库中执行，发现没有数据。 然后我就奇怪了，数据库是同一个啊、SQL 是同一个啊、查询结果都没有变啊，为什么执行的结果在程序里面是 1，而在数据库中是0。 难道是因为 MyBatis 和数据库执行的结果不一样？ 后来我才明白不一致的原因。 我编写了一个与实际业务类似的代码，用来模拟上述的问题。 2. 复现问题2.1. 表结构MySQL 数据库中创建了一张用户表，只有4个字段。 CREATE TABLE `user` ( `user_id` varchar(36) NOT NULL COMMENT '用户主键id', `user_name` varchar(55) NULL DEFAULT NULL COMMENT '账号', `password` varchar(55) NULL DEFAULT NULL COMMENT '密码', `email` varchar(55) NULL DEFAULT NULL COMMENT '邮箱', PRIMARY KEY (`user_id`) USING BTREE);2.2. 项目依赖示例项目是一个 SpringBoot 工程，pom 文件中除了 web 依赖还有 mysql 的驱动、MyBatis 和 lombok。 <dependencies> <dependency> <groupId>org.springframework.boot</groupId> <artifactId>spring-boot-starter-web</artifactId> <version>2.2.6.RELEASE</version> </dependency> <dependency> <groupId>mysql</groupId> <artifactId>mysql-connector-java</artifactId> <version>5.1.47</version> </dependency> <dependency> <groupId>org.projectlombok</groupId> <artifactId>lombok</artifactId> <version>1.18.8</version> </dependency> <dependency> <groupId>org.mybatis.spring.boot</groupId> <artifactId>mybatis-spring-boot-starter</artifactId> <version>1.3.0</version> </dependency></dependencies>2.2. 业务业务流程是这样：新建一个用户，在新建用户之前首先校验邮箱是否已在数据库中存在，然后执行一些其他的业务，然后执行 insert 方法插入数据库，然后执行一些其他的业务，最后再校验 user_name 是否已存在。 ...

作者：洪斌爱可生南区负责人兼技术服务总监，MySQL  ACE，擅长数据库架构规划、故障诊断、性能优化分析，实践经验丰富，帮助各行业客户解决 MySQL 技术问题，为金融、运营商、互联网等行业客户提供 MySQL 整体解决方案。本文来源：转载自公众号-玩转MySQL*爱可生开源社区出品，原创内容未经授权不得随意使用，转载请联系小编并注明来源。MySQL Shell 现在不只是用来部署 InnoDB cluster 和 Replica set 了，在 8.0.17 之后有了自定义扩展能力，DBA 可以用 javascript 和 python 编写扩展了，把一些常用的 SQL 脚本封装成自己的工具箱，甚至可以围绕它构建 DBA 的 DevOps 作业平台。 为 MySQL Shell 添加扩展并不复杂，现有两种扩展方式，一种是 report，这种主要用来做各种查询，可以使用内建的两个命令调用自定义的 report，另一种是 plugin，可以定义任意功能函数。 show 就是普通一次性输出，已经内建了好多常用指标，参见脑图。watch 类似 top 方式，持续输出信息，写些简单的监控脚本方便多了。自定义扩展将自定义的 js 或 py 脚本放在 ~/.mysqlsh/plugin 和~/.mysqlsh/init.d 目录下，建议是放在 plugin 目录下，目录可按功能类别命名，目录中必须有 init.py 或 init.js 文件用来初始化扩展，代码中可约定顶级目录名作为全局对象，二级目录作为成员对象。 hongbin@MBP ~/.m/plugins> tree.└── ext└── table└── init.py2 directories, 1 file下面是一段示例代码 # init.py# -------# 演示注册report和plugin两种方式# 定义一个查询函数，获取没有主键或唯一索引表def report_table_without_pk(session): query = '''SELECT tables.table_schema , tables.table_name FROM information_schema.tables LEFT JOIN ( SELECT table_schema , table_name FROM information_schema.statistics GROUP BY table_schema, table_name, index_name HAVING SUM( case when non_unique = 0 and nullable != 'YES' then 1 else 0 end ) = count(*) ) puks ON tables.table_schema = puks.table_schema and tables.table_name = puks.table_name WHERE puks.table_name is null AND tables.table_type = 'BASE TABLE' AND Engine="InnoDB";''' result = session.run_sql(query) report = [] if (result.has_data()): report = [result.get_column_names()] for row in result.fetch_all(): report.append(list(row)) # 注册为report，需要返回字典类型 return {"report": report}# 功能同上，这里为演示以Pluginf方式重新定义函数，两者report和plugin差异主要在于输出方式def plugin_table_without_pk(session): query = '''SELECT tables.table_schema , tables.table_name FROM information_schema.tables LEFT JOIN ( SELECT table_schema , table_name FROM information_schema.statistics GROUP BY table_schema, table_name, index_name HAVING SUM( case when non_unique = 0 and nullable != 'YES' then 1 else 0 end ) = count(*) ) puks ON tables.table_schema = puks.table_schema and tables.table_name = puks.table_name WHERE puks.table_name is null AND tables.table_type = 'BASE TABLE' AND Engine="InnoDB";''' result = session.run_sql(query) shell.dump_rows(result) return# 注册一个function，用来给表添加主键字段，变更类操作通常以functiondef _add_pk(table, columns, session=None): query = 'ALTER TABLE %s ADD PRIMARY KEY (%s)' % (table, columns) if session is None: session = shell.get_session() if session is None: print("No session specified. Either pass a session object to this " "function or connect the shell to a database") return # session = shell.get_session() result = session.run_sql(query)# 这里注册上面定义的report函数，report名称，返回格式类型，函数名，描述shell.register_report("table_without_pk", "list", report_table_without_pk, { "brief": "Lists the table without primary key."})# 这里定义全局对象，可以看做一个命名空间，ext是默认社区扩展插件的对象名，或者其他自定义对象名称if 'ext' in globals(): global_obj = extelse: # Otherwise register new global object named 'ext' global_obj = shell.create_extension_object() shell.register_global("ext", global_obj, {"brief": "MySQL Shell extension plugins."})# 可以按类别在全局对象下添加子对象try: plugin_obj = global_obj.tableexcept IndexError: plugin_obj = shell.create_extension_object() shell.add_extension_object_member(global_obj, "table", plugin_obj, {"brief": "Utility object for table operations."})# 添加功能函数到自定义插件对象中，父级对象名，调用函数名，定义的函数，描述，函数接受的参数名/类型/是否必选/描述try: shell.add_extension_object_member(plugin_obj, "add_pk", _add_pk, {"brief": "Add a primary key to the table", "parameters": [ { "name": "table", "type": "string", "required": True, "brief": "table name." }, { "name": "columns", "type": "string", "required": True, "brief": "column name." }, { "name": "session", "type": "object", "class": "Session", "required": False,# 若不想单独传session参数，可以在函数中获取当前会话对象 "brief": "The session to be used on the operation." } ] })except Exception as e: shell.log("ERROR", "Failed to register ext.table.add_pk ({0}).".format( str(e).rstrip()))# 添加plugin_table_without_pktry: shell.add_extension_object_member(plugin_obj, "get_without_pk", plugin_table_without_pk, {"brief": "Lists the table without primary key.", })except Exception as e: shell.log("ERROR", "Failed to register ext.table.get_without_pk ({0}).".format( str(e).rstrip()))使用方法登录 mysqlsh 后自动搜索并初始化插件，指定 --log-level 参数时可记录详细调试信息到 ~/.mysqlsh/mysqlsh.log ，如果加载失败，可以查看日志分析原因。 ...

比如随着业务的发展，订单表的数据量越来越大，这个时候查询变慢了，我们可以采取什么措施来提升查询性能呢？ 1、存档历史数据当单表的订单数据太多，多到影响性能的时候，首选的方案是，归档历史订单。所谓归档，其实也是一种拆分数据的策略。简单地说，就是把大量的历史订单移到另外一张历史订单表中。为什么这么做呢？因为像订单这类具有时间属性的数据，都存在热尾效应。大多数情况下访问的都是最近的数据，但订单表里面大量的数据都是不怎么常用的老数据。因为新数据只占数据总量中很少的一部分，所以把新老数据分开之后，新数据的数据量就会少很多，查询速度也就会快很多。老数据虽然和之前比起来没少多少，查询速度提升不明显，但是，因为老数据很少会被访问到，所以慢一点儿也问题不大。这样拆分的另外一个好处是，拆分订单时，需要改动的代码非常少。大部分对订单表的操作都是在订单完成之前，这些业务逻辑都是完全不用修改的。即使像退货退款这类订单完成后的操作，也是有时限的，那这些业务逻辑也不需要修改，原来该怎么操作订单表还怎么操作。基本上只有查询统计类的功能，会查到历史订单，这些需要稍微做一些调整，按照时间，选择去订单表还是历史订单表查询就可以了。很多电商大厂在它逐步发展壮大的过程中，都用这种订单拆分的方案撑了好多年。 2、分库分表在考虑到底是分库还是分表之前，我们需要先明确一个原则，那就是能不拆就不拆，能少拆不多拆。原因也很简单，你把数据拆分得越散，开发和维护起来就越麻烦，系统出问题的概率就越大。分库分表的目的是解决两个问题：1、数据量太大查询慢。解决查询慢，只要减少每次查询的数据总量就可以了，也就是说，分表就可以解决问题。2、应对高并发。一个数据库实例撑不住，就把并发请求分散到多个实例中去，所以，解决高并发的问题是需要分库的。简单地说，数据量大，就分表；并发高，就分库。一般情况下，我们的方案都需要同时做分库分表，这时候分多少个库，多少张表，分别用预估的并发量和数据量来计算就可以了。 如何选择 Sharding Key？选择Sharding Key最重要的参考因素是，我们的业务是如何访问数据的。选择Sharding key的时候，一定要能兼容业务最常用的查询条件，让查询尽量落在一个分片中，分片之后无法兼容的查询，可以把数据同步到其他存储中去，来解决这个问题。 如何选择分片算法？1. 范围分片比如订单表中分12个分片，每个月一个分片，这样对查询的兼容要好多，毕竟查询条件中带上时间范围，让查询只落到某一个分片上，还是比较容易的，我在查询界面上强制用户必须指定时间范围就行了。这种做法有个很大的问题，比如现在是 3 月份，那基本上所有的查询都集中在 3 月份这个分片上，其他 11 个分片都闲着，这样不仅浪费资源，很可能你 3 月那个分片根本抗不住几乎全部的并发请求。这个问题就是“热点问题”。基于范围来分片容易产生热点问题，不适合作为订单的分片方法，但是这种分片方法的优点也很突出，那就是对查询非常友好，基本上只要加上一个时间范围的查询条件，原来该怎么查，分片之后还可以怎么查。范围分片特别适合那种数据量非常大，但并发访问量不大的 ToB 系统。比如说，电信运营商的监控系统，它可能要采集所有人手机的信号质量，然后做一些分析，这个数据量非常大，但是这个系统的使用者是运营商的工作人员，并发量很少。这种情况下就很适合范围分片。2. 哈希分片哈希分片比较容易把数据和查询均匀地分布到所有分片中。一般来说，订单表都采用更均匀的哈希分片算法。比如说，我们要分 24 个分片，选定了 Sharding Key 是用户 ID，那我们决定某个用户的订单应该落到那个分片上的算法是，拿用户 ID 除以 24，得到的余数就是分片号。这是最简单的取模算法，一般就可以满足大部分要求了。当然也有一些更复杂的哈希算法，像一致性哈希之类的，特殊情况下也可以使用。需要注意的一点是，哈希分片算法能够分得足够均匀的前提条件是，用户 ID 后几位数字必须是均匀分布的。比如说，你在生成用户 ID 的时候，自定义了一个用户 ID 的规则，最后一位 0 是男性，1 是女性，这样的用户 ID 哈希出来可能就没那么均匀，可能会出现热点。 实际案例：对于订单表进行分库分表，一般按照用户 ID 作为 Sharding Key，采用哈希分片算法来均匀分布用户订单数据。为了能支持按订单号查询的需求，需要把用户 ID 的后几位放到订单号中去。3. 查表法查表法决定某个 Sharding Key 落在哪个分片上，全靠人为来分配，分配的结果记录在一张表里面。每次执行查询的时候，先去表里查一下要找的数据在哪个分片中。查表法的好处就是灵活，怎么分都可以，你用上面两种分片算法都没法分均匀的情况下，就可以用查表法，人为地来把数据分均匀了。查表法还有一个特好的地方是，它的分片是可以随时改变的。比如我发现某个分片已经是热点了，那我可以把这个分片再拆成几个分片，或者把这个分片的数据移到其他分片中去，然后修改一下分片映射表，就可以在线完成数据拆分了。查表法相对其他两种分片算法来说，缺点是需要二次查询，实现起来更复杂，性能上也稍微慢一些。但是，分片映射表可以通过缓存来加速查询，实际性能并不会慢很多。 思考：“归档历史订单”的数据拆分方法，和直接进行分库分表相比，比如说按照订单创建时间，自动拆分成每个月一张表，两种方法各有什么优点和缺点？ 按月自动拆分订单的好处是，不需要做数据搬运，相对实现比较简单，数据分得更碎，缺点是跨月查询比较麻烦，但好处是容量也更大（因为分片更多）。归档历史订单的方法，实现起来更复杂，容量要小一些，但是对查询更加友好。

大家久等啦！ 爱可生第二期线上公开课要来咯！这次我们要和大家分享的是让人光是听到就“虎躯一震”的MySQL hash sharding 扩容！！！ 爱可生将于6 月 2 日正式开播！【爱可生公开课】如何应对让大家头疼的 MySQL hash sharding 扩容？届时将为您带来如何才能简单、稳定、高效地搞定 MySQL hash sharding 扩容，围绕扩容、运维、架构、规划四个方向，深度解析 DBLE 中的复杂性查询性能调优、分片扩容、高可用联动、监控告警等功能，提供高效便捷的实施解决方案。互动环节也将分享超多经典行业案例。 主讲人：明溪源 主讲人 title：爱可生技术方案总监、MySQL 认证专家 主讲人介绍：拥有多年数据存储、云计算系统运维和技术支持经验，擅长 MySQL 数据库高可用架构设计及数据库自服务的整体规划设计，曾为众多银行、保险等金融行业用户提供数据库实施建设方案。 直播议程：DBLE 介绍：什么场景下适合用MySQL sharding 方案？DBLE 企业版特性——扩容：hash sharding 扩容一定要全量导入导出吗？DBLE 企业版还有哪些特性？课后互动，经典行业案例分析。报名方式：识别图中二维码进行报名。 如果您有更多想要了解的内容，或者针对这个版块的内容建议，欢迎在下方留言告诉我们。本次活动解释权归上海爱可生所有。 6 月 2 日，来爱可生直播课，了解更多企业数据库解决方案！ 注意事项：1、开课前请关注爱可生小助手微信号(Actionsky-zhushou)，小助手会提前向大家提醒并发送直播链接，以免错过分享会； 2、分享会结束后，视频回放将发布在爱可生云数据库官方公众号。若您无法参加本次分享会，可提前加关注。 关于爱可生爱可生成立于 2003 年，依托于融合、开放、创新的数据处理技术和服务能力，为大型行业用户的特定场景提供深度挖掘数据价值的解决方案。公司持续积累的核心关键技术，覆盖到分布式数据库集群、云数据平台、数据库大体量运管平台、海量数据集成于存储、清洗与治理、人工智能分析挖掘、可视化展现、安全与隐私保护等多个领域。公司已与多个行业内的专业公司建立了长期伙伴关系，不断促进新技术与行业知识相结合，为用户寻求新的数据驱动的价值增长点。公司已在金融、能源电力、广电、政府等行业取得了众多大型用户典型成功案例，获得了市场的认可和业务的持续增长。

这篇主要说明表属性 - 外键。外键的设计初衷是为了在数据库端保证对逻辑上相关联的表数据在操作上的一致性与完整性。 外键在大部分企业写的开发规范里会直接规避掉！外键有优缺点，也并不是说每种场景都不适用，完全没有必要一刀切。外键到底能不能用？下面会针对不同的场景来告诉你答案。 一、外键的优缺点优点：精简关联数据，减少数据冗余避免后期对大量冗余处理的额外运维操作。 降低应用代码复杂性，减少了额外的异常处理相关数据管理全由数据库端处理。 增加文档的可读性特别是在表设计开始，绘制 ER 图的时候，逻辑简单明了，可读性非常强。 缺点：性能压力外键一般会存在级联功能，级联更新，级联删除等等。在海量数据场景，造成很大的性能压力。比如插入一条新记录，如果插入记录的表有 10 个外键，那势必要对关联的 10 张表逐一检查插入的记录是否合理，延误了正常插入的记录时间。并且父表的更新会连带子表加上相关的锁。 其他功能的灵活性不佳比如，表结构的更新等。 二、外键的使用外键参照动作列表：CASCADE：级联，子表跟随父表更新外键值SET NULL：子表更随主表更新外键值为 NULLRESTRICT/ NO ACTION：默认，限制父表改动外键值SET DEFAULT：目前产生的效果和 RESTRICT 相同。那先来简单看看 MySQL 里外键的用法。MySQL 外键仅有 InnoDB 和 NDB 两种引擎支持，这里只关注 InnoDB。 本次示例 MySQL 的版本为最新版 8.0.19示例下面 f1 是父表，f2、f3、f6 分别代表不同类型的外键表，也就是子表。 -- 引用基础表，也就是父表mysql-(ytt_fk/3305)->create table f1(id int primary key, r1 int, r2 int, r3 int,key idx_r1(r1),key idx_u1 (r2,r3));Query OK, 0 rows affected (0.02 sec)-- 随着参照表级联更新外键表，也就是父表更新的话，会级联更新子表的外键mysql-(ytt_fk/3305)->create table f2(id int primary key, f1_r1 int, mark int, constraint f1_fk_r1 foreign key (f1_r1) references f1(r1) on update cascade);Query OK, 0 rows affected (0.02 sec)-- 随着参照表更新外键值为 NULL，也就是父表更新的话，会级联更新子表的外键为 NULLmysql-(ytt_fk/3305)->create table f3 (id int primary key, f1_id int, foreign key (f1_id) references f1(id) on update set null);Query OK, 0 rows affected (0.02 sec)-- 多个键值外键。子表的可以引用父表非主键的其他键mysql-(ytt_fk/3305)->create table f6 ( id int auto_increment primary key, f1_r2 int, f1_r3 int, foreign key (f1_r2,f1_r3) references f1(r2,r3));Query OK, 0 rows affected (0.02 sec)场景一：强烈要求数据一致性，程序弱化，数据库端强化，表结构改动小，并发不高的场景。用一条记录验证表 f2 和 f6。从功能性角度来看，外键的优势很明显，在数据库端完全满足了数据完整性校验。 ...

序本文主要研究一下rocketmq-mysql的ColumnParser ColumnParserrocketmq-externals/rocketmq-mysql/src/main/java/org/apache/rocketmq/mysql/schema/column/ColumnParser.java public abstract class ColumnParser { public static ColumnParser getColumnParser(String dataType, String colType, String charset) { switch (dataType) { case "tinyint": case "smallint": case "mediumint": case "int": return new IntColumnParser(dataType, colType); case "bigint": return new BigIntColumnParser(colType); case "tinytext": case "text": case "mediumtext": case "longtext": case "varchar": case "char": return new StringColumnParser(charset); case "date": case "datetime": case "timestamp": return new DateTimeColumnParser(); case "time": return new TimeColumnParser(); case "year": return new YearColumnParser(); case "enum": return new EnumColumnParser(colType); case "set": return new SetColumnParser(colType); default: return new DefaultColumnParser(); } } public static String[] extractEnumValues(String colType) { String[] enumValues = {}; Matcher matcher = Pattern.compile("(enum|set)\\((.*)\\)").matcher(colType); if (matcher.matches()) { enumValues = matcher.group(2).replace("'", "").split(","); } return enumValues; } public abstract Object getValue(Object value);}ColumnParser定义了getValue抽象方法；它提供了getColumnParser方法用于根据dataType获取对应的ColumnParser实现类IntColumnParserrocketmq-externals/rocketmq-mysql/src/main/java/org/apache/rocketmq/mysql/schema/column/IntColumnParser.java ...

缓冲池的用处对于使用 InnoDB 作为存储引擎的表来说，不管是用于存储用户数据的索引，还是各种系统数据，都是以页的形式存放在表空间中的，而所谓的表空间只是 InnoDB 对文件系统上一个或几个实际文件的抽象，也就实际数据说到底还是存储在磁盘上的。 磁盘的速度很慢，怎么能配得上“快如闪电”的CPU 呢？ InnoDB 存储引擎在处理客户端的请求时，当需要访问某个页的数据时，就会把完整的页的数据全部加载到内存中。 也就是说即使我们只需要访问一个页的一条记录，那也需要先把整个页的数据加载到内存中。 缓冲池内部组成缓冲池中默认的缓存页大小和在磁盘上默认的页大小是一样的，一般是16KB。 为了更好的管理这些在缓冲池中的缓存页，InnoDB为每一个缓存页都创建了一些所谓的控制信息。 这些控制信息包括该页所属的表空间编号、页号、缓存页在缓冲池中的地址、链表节点信息、一些锁信息。 缓冲池的一些参数：SHOW VARIABLES LIKE 'innodb_buffer_pool%';free 链表当最初启动MySQL服务器的时候，此时并没有真实的磁盘页被缓存到缓冲池中，之后随着程序的运行，会不断的有磁盘上的页被缓存到缓冲池中。 从磁盘上读取一个页到缓冲池中的时候该放到哪个缓存页的位置呢？ 思路：区分缓冲池中哪些缓存页是空闲的，哪些已经被使用了。 把所有空闲的缓存页对应的控制块作为节点放到一个链表中，这个链表叫作 free 链表。 flush 链表如果我们修改了缓冲池中某个缓存页的数据，那它就和磁盘上的页不一致了，这样的缓存页也被称为脏页（dirty page）。 最简单的做法就是每发生一次修改就立即同步到磁盘上对应的页上，但是频繁的往磁盘中写数据会严重的影响程序的性能。 所以，Innodb 创建了一个存储脏页的链表，凡是修改过的缓存页对应的控制块都会作为一个节点加入到一个链表中，在未来的某个时间点进行同步。这个链表叫做 flush 链表。 缓存不够的窘境缓冲池对应的内存大小毕竟是有限的，如果需要缓存的页占用的内存大小超过了缓冲池大小，也就是已经没有多余的空闲缓存页的时候怎么办？ 把某些旧的缓存页从缓冲池中移除，然后再把新的页放进来。 移除哪些缓存页？这就需要引入缓存淘汰机制了。 缓存淘汰机制缓存淘汰有以下两个目的： 实现淘汰使缓存命中率高缓存淘汰机制比较常用的是用 LRU （Least recently used）算法。 传统LRU LRU 的两种情况： （1）页已经在缓冲池里，那就只做“移至”LRU头部的动作，而没有页被淘汰； （2）页不在缓冲池里，除了做“放入”LRU头部的动作，还要做“淘汰”LRU尾部页的动作； 在 InnoDB 中，传统的 LRU 会遇到两个问题： （1）预读失效； （2）缓冲池污染； 什么是预读失效？ 由于预读 (Read-Ahead)，提前把页放入了缓冲池，但最终 MySQL 并没有从页中读取数据，称为预读失效。 如何对预读失效进行优化？ 要优化预读失效，思路是： （1）让预读失败的页，停留在缓冲池 LRU 里的时间尽可能短； ...

写文章 InnoDB中的页合并与分裂 胖懒鸭 Python Web后端 / 努力成为Redis砖家 15 人赞同了该文章 原文标题：InnoDB Page Merging and Page Splitting 原文链接：https://www.percona.com/blog/2017/04/10/innodb-page-merging-and-page-splitting/ 作者：Marco Tusa 译者：2014BDuck 博客地址：https://blog.2014bduck.com/archives/260 翻译时间：2019-12-22如果你找过任何一位MySQL顾问，问他对你的语句和/或数据库设计的建议，我保证他会跟你讲主键设计的重要性。特别是在使用InnoDB引擎的情景，他们肯定会给你解释索引合并和页分裂这些。这两个方面与性能息息相关，你应该在任何设计索引（不止是主键索引）的时候都将他们考虑在内。 你可能觉得这些听起来挺莫名其妙，没准你也没错。这不是容易的事，特别是讲到关于内部实现的时候。通常你都不会需要处理这些事情，并且你也不想去着手他们。 但是有时候这些问题又是必须搞清楚的。如果有这种情况，那这篇文章正适合你。 我尝试用这篇文章将一些最不清晰、InnoDB内部的操作解释清楚：索引页的创建、页合并和页分裂。 在InnoDB中，数据即索引（译注：索引组织数据）。你可能听过这种说法，但它具体是什么样的？ 文件表（File-Table）结构假设你已经装好了MySQL最新的5.7版本（译注：文章发布于17年4月），并且你创建了一个windmills库（schema）和wmills表。在文件目录（通常是/var/lib/mysql/）你会看到以下内容： data/ windmills/ wmills.ibd wmills.frm这是因为从MySQL 5.6版本开始innodb_file_per_table参数默认设置为1。该配置下你的每一个表都会单独作为一个文件存储（如果有分区也可能有多个文件）。 目录下要注意的是这个叫wmills.ibd的文件。这个文件由多个段（segments）组成，每个段和一个索引相关。 文件的结构是不会随着数据行的删除而变化的，但段则会跟着构成它的更小一级单位——区的变化而变化。区仅存在于段内，并且每个区都是固定的1MB大小（页体积默认的情况下）。页则是区的下一级构成单位，默认体积为16KB。 按这样算，一个区可以容纳最多64个页，一个页可以容纳2-N个行。行的数量取决于它的大小，由你的表结构定义。InnoDB要求页至少要有两个行，因此可以算出行的大小最多为8000 bytes。 听起来就像俄罗斯娃娃（Matryoshka dolls）一样是么，没错！下面这张图能帮助你理解： 根，分支与叶子每个页（逻辑上讲即叶子节点）是包含了2-N行数据，根据主键排列。树有着特殊的页区管理不同的分支，即内部节点（INodes）。 上图仅为示例，后文才是真实的结构描述。 具体来看一下： ROOT NODE #3: 4 records, 68 bytes NODE POINTER RECORD ≥ (id=2) → #197 INTERNAL NODE #197: 464 records, 7888 bytes NODE POINTER RECORD ≥ (id=2) → #5 LEAF NODE #5: 57 records, 7524 bytes RECORD: (id=2) → (uuid="884e471c-0e82-11e7-8bf6-08002734ed50", millid=139, kwatts_s=1956, date="2017-05-01", location="For beauty's pattern to succeeding men.Yet do thy", active=1, time="2017-03-21 22:05:45", strrecordtype="Wit")下面是表结构： ...

序本文主要研究一下rocketmq-mysql的Replicator Replicatorrocketmq-externals/rocketmq-mysql/src/main/java/org/apache/rocketmq/mysql/Replicator.java public class Replicator { private static final Logger LOGGER = LoggerFactory.getLogger(Replicator.class); private static final Logger POSITION_LOGGER = LoggerFactory.getLogger("PositionLogger"); private Config config; private EventProcessor eventProcessor; private RocketMQProducer rocketMQProducer; private Object lock = new Object(); private BinlogPosition nextBinlogPosition; private long nextQueueOffset; private long xid; public static void main(String[] args) { Replicator replicator = new Replicator(); replicator.start(); } public void start() { try { config = new Config(); config.load(); rocketMQProducer = new RocketMQProducer(config); rocketMQProducer.start(); BinlogPositionLogThread binlogPositionLogThread = new BinlogPositionLogThread(this); binlogPositionLogThread.start(); eventProcessor = new EventProcessor(this); eventProcessor.start(); } catch (Exception e) { LOGGER.error("Start error.", e); System.exit(1); } } public void commit(Transaction transaction, boolean isComplete) { String json = transaction.toJson(); for (int i = 0; i < 3; i++) { try { if (isComplete) { long offset = rocketMQProducer.push(json); synchronized (lock) { xid = transaction.getXid(); nextBinlogPosition = transaction.getNextBinlogPosition(); nextQueueOffset = offset; } } else { rocketMQProducer.push(json); } break; } catch (Exception e) { LOGGER.error("Push error,retry:" + (i + 1) + ",", e); } } } public void logPosition() { String binlogFilename = null; long xid = 0L; long nextPosition = 0L; long nextOffset = 0L; synchronized (lock) { if (nextBinlogPosition != null) { xid = this.xid; binlogFilename = nextBinlogPosition.getBinlogFilename(); nextPosition = nextBinlogPosition.getPosition(); nextOffset = nextQueueOffset; } } if (binlogFilename != null) { POSITION_LOGGER.info("XID: {}, BINLOG_FILE: {}, NEXT_POSITION: {}, NEXT_OFFSET: {}", xid, binlogFilename, nextPosition, nextOffset); } } public Config getConfig() { return config; } public BinlogPosition getNextBinlogPosition() { return nextBinlogPosition; }}Replicator提供了start、commit、logPosition方法；start方法会创建RocketMQProducer、BinlogPositionLogThread及EventProcessor，然后执行其start方法；commit方法会通过rocketMQProducer将transaction.toJson()发送出去，对于isComplete为true的会更新xid、nextBinlogPosition、nextQueueOffset；logPosition方法会打印binlogFilename、nextPosition、nextOffsetRocketMQProducerrocketmq-externals/rocketmq-mysql/src/main/java/org/apache/rocketmq/mysql/productor/RocketMQProducer.java ...

Mysql性能优化神神器explain。一文通透Mysql性能优化神神器explain。一文通透 前言数据准备 创建数据表插入数据explain命令使用 select_typetyperowsrefextra总结前言SQL语句在不同的人手中会写出不同的语句形式，比如经常遇到的SQL慢查询，这时候往往需要针对SQL进行优化。而Mysql中为保证SQL语句能够高效的运行，提供了一个Explain的命令，用来对SQL语句进行语义分析，供开发者来针对SQL进行优化。 数据准备为了方便整个流程的执行，首先创建好测试数据。 创建数据表SQL中的执行涉及到单表与多表的联合执行，本次创建两张表用来模拟该情况，更多的多表联合执行与两张表执行计划是一样的。 CREATE TABLE `users` ( `id` int(11) NOT NULL AUTO_INCREMENT, `create_time` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP, `name` varchar(20) NOT NULL DEFAULT '' COMMENT '用户名', `sex` tinyint(4) NOT NULL DEFAULT '1' COMMENT '性别', `phone` varchar(11) NOT NULL COMMENT '手机号', `desc` varchar(200) NOT NULL DEFAULT '' COMMENT '介绍', primary key (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT '用户表';CREATE TABLE `order`( `id` int(11) NOT NULL AUTO_INCREMENT, `phone` varchar(11) NOT NULL COMMENT '手机号', `name` varchar(20) NOT NULL COMMENT '用户名', primary key (`id`)) ENGINE =InnoDB default CHARSET=utf8 COMMENT '订单';插入数据为了方便本次没有使用SQL语句，而是使用存储过程创建数据，简单快速也方便。 ...

使用IntelliJ IDEA创建一个完整的Spring Boot项目流程以IntelliJ IDEA2019.3为例 准备工作 本地安装好JDK电脑已经联网IDEA已经安装IDEA配置好了Maven环境1.打开IDEA开发工具，点击Create New Project创建一个新项目 2.左侧选择Spring Initializr，右侧选择连接https://start.spring.io ,点击next 3.此过程等待加载 4.选择新建项目的相关信息参考如下 Group公司或者组织域名的倒序Artifact 项目或者模块名Type:maven部署方式(一般不更改)packaging:打包方式(一般不更改)Java Version:Java版本(如果你的电脑上有多个JDK版本，请选择)Version:项目或者模块版本(一般不更改)Name:项目名Description:项目描述package:包名(根据需求设置)注意:所有的内容都必须是小写字母，否则IDEA不予通过！ 设置完成后，点击Next 5.选择模块和Spring Boot的版本版本建议选择稳定版模块根据需要选择，我这里选择web,Mybatis,MySQL等右边可以看到选择的模块项目 6.填写项目名和项目路径Project name:项目名Project location:存放路径选择好之后，点击Finish. 7.等待Maven部署项目，最好不要乱动项目结构 8.运行项目运行项目方式 左下角的Terminal终端输入命令:mvn spring-boot:run运行运行主程序入口main方法直接运行Spring Boot项目 9.配置数据源运行项目之后，发现有错误，是因为添加了数据库的模块，但是没有配置数据源 数据源配置在项目中依次找到src/main/resources/application.properties文件，将下面的配置信息复制进去，修改为自己的版本 #配置数据库的项目信息(MySQL数据库)#配置数据库的驱动spring.datasource.driver-class-name=com.mysql.cj.jdbc.Driver# 旧版使用的驱动#spring.datasource.driver-class-name=com.mysql.jdbc.Driver#配置数据库连接URLspring.datasource.url=jdbc:mysql://127.0.0.1:3306/cms?characterEncoding=utf8&serverTimezone=UTC#配置数据库额度用户名spring.datasource.username=lele#配置数据库的密码spring.datasource.password=lele# 配置服务器的相关信息# 配置服务器的端口server.port=80注:数据库的驱动在不同的版本上不同，注意区分版本，因为还添加了web模块，可能存在服务器端口占用问题，我这里顺便将端口改成了80 10.配置完成后，再次运行项目使用命令:mvn spring-boot:run 运行之后光标闪烁，程序正常启动

mysql数据库操作

前言MyBatis 多数据源配置，最近在项目建设中，需要在原有系统上扩展一个新的业务模块，特意将数据库分库，以便减少复杂度。本文直接以简单的代码示例，如何对 MyBatis 多数据源配置。 准备创建数据库db_test SET NAMES utf8mb4;SET FOREIGN_KEY_CHECKS = 0;-- ------------------------------ Table structure for t_user-- ----------------------------DROP TABLE IF EXISTS `t_user`;CREATE TABLE `t_user` ( `id` int(8) NOT NULL AUTO_INCREMENT COMMENT 'ID', `user_name` varchar(20) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '用户姓名', `user_sex` char(1) CHARACTER SET utf8 COLLATE utf8_general_ci DEFAULT NULL COMMENT '用户性别', PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=23 DEFAULT CHARSET=utf8;-- ------------------------------ Records of t_user-- ----------------------------BEGIN;INSERT INTO `t_user` VALUES (1, '刘备', '男');INSERT INTO `t_user` VALUES (2, '孙尚香', '女');INSERT INTO `t_user` VALUES (3, '周瑜', '男');INSERT INTO `t_user` VALUES (4, '小乔', '女');INSERT INTO `t_user` VALUES (5, '诸葛亮', '男');INSERT INTO `t_user` VALUES (6, '黄月英', '女');INSERT INTO `t_user` VALUES (7, '关羽', '男');INSERT INTO `t_user` VALUES (8, '张飞', '男');INSERT INTO `t_user` VALUES (9, '赵云', '男');INSERT INTO `t_user` VALUES (10, '黄总', '男');INSERT INTO `t_user` VALUES (11, '曹操', '男');INSERT INTO `t_user` VALUES (12, '司马懿', '男');INSERT INTO `t_user` VALUES (13, '貂蝉', '女');INSERT INTO `t_user` VALUES (14, '吕布', '男');INSERT INTO `t_user` VALUES (15, '马超', '男');INSERT INTO `t_user` VALUES (16, '魏延', '男');INSERT INTO `t_user` VALUES (17, '孟获', '男');INSERT INTO `t_user` VALUES (18, '大乔', '女');INSERT INTO `t_user` VALUES (19, '刘婵', '男');INSERT INTO `t_user` VALUES (20, '姜维', '男');INSERT INTO `t_user` VALUES (21, '廖化', '男');INSERT INTO `t_user` VALUES (22, '关平', '男');COMMIT;SET FOREIGN_KEY_CHECKS = 1;dbb_test2 ...

docker-compose构建mysql主从复制集群docker-compose构建 mysql 主从复制(读写分离)集群 MySQL master-slave replication with using Docker. 源码在github上: https://github.com/docker-box... 运行git clone https://github.com/docker-box/mysql-cluster.gitcd mysql-cluster./build.sh可以在build.sh内自定义对应参数提示: 运行前需要确保安装了docker和docker-compose,具体安装方法请参考官网 如果想手动安装, 则可以按照build.sh内的命令来手动执行即可 测试看看效果给主库创建一个表, 并添加两条数据docker exec mysql_master sh -c "export MYSQL_PWD=111; mysql -u root mydb -e 'create table code(code int); insert into code values (100), (200)'"查看两个从库是否同步了该表以及数据docker exec mysql_slave sh -c "export MYSQL_PWD=111; mysql -u root mydb -e 'select * from code \G'"docker exec mysql_slave2 sh -c "export MYSQL_PWD=111; mysql -u root mydb -e 'select * from code \G'"如果前边的安装正确的话, 就可以看到第一步插入的两条数据了相关命令查看docker-compose运行日志docker-compose logs查看运行的docker容器docker-compose ps查看主库运行状态docker exec mysql_master sh -c 'mysql -u root -p111 -e "SHOW MASTER STATUS \G"'查看从库运行状态docker exec mysql_slave sh -c 'mysql -u root -p111 -e "SHOW SLAVE STATUS \G"'docker exec mysql_slave2 sh -c 'mysql -u root -p111 -e "SHOW SLAVE STATUS \G"'进入主库docker exec -it mysql_master bash进入从库docker exec -it mysql_slave bashdocker exec -it mysql_slave2 bash

使用 MySql 建表出现的问题在使用 Navicat Premium 运行 sql 语句进行建表时，MySQL 报错如下： 建表语句：DROP DATABASE IF EXISTS javaweb;CREATE DATABASE javaweb DEFAULT CHARACTER SET utf8;USE javaweb;CREATE TABLE user( id int primary key auto_increment, name varchar(20) not null, gender varchar(5), age int, address varchar(32), qq varchar(20), email varchar(50), username varchar(32), password varchar(32));出现错误提示[ERR] 1064 - You have an error in your SQL syntax; check the manual.......此问题是 MySql 语法上的错误，在 MySQL 中，为了区分 MySQL 的关键字与普通字符，MySQL 引入了一个反引号。 在上述的 sql 语句中，列名称没有使用反引号或者列名称使用单引号，都会报这个错误出来。 ...

1、设置 Linux 为静态网络配置使用 VMWare 安装好 CentOS 后，将网络适配器设置为 NAT 模式。为了防止 IP 关机重启时候经常变动，需要将网卡信息设置为静态。修改 /etc/sysconfig/network-scripts 下的网卡配置文件 在此文件夹下我的网卡配置文件是 ：ifcfg-ens33（一般都是 ifcfg-ensXX 文件，自己修改时候注意）。所以只需要使用 vi 命令编辑此文件即可，将文件信息修改为以下即可。TYPE="Ethernet"BOOTPROTO="static"IPADDR=192.168.197.129NETMASK=255.255.255.0GATEWAY=192.168.197.2DNS1=8.8.8.8DEFROUTE="yes"PEERDNS="yes"PEERROUTES="yes"IPV4_FAILURE_FATAL="no"IPV6INIT="yes"IPV6_AUTOCONF="yes"IPV6_DEFROUTE="yes"IPV6_PEERDNS="yes"IPV6_PEERROUTES="yes"IPV6_FAILURE_FATAL="no"IPV6_ADDR_GEN_MODE="stable-privacy"NAME="ens33"UUID="f50c1acb-829e-4c6c-a9d7-3d9c5e6ea0d5"DEVICE="ens33"ONBOOT="yes"上面主要配置信息解释如下（其他信息复制即可） 主要信息图中已经标识，切记网关地址一定要一致，否则不能上网。2、解决MySQL 远程连接错误：2003 - Cant't connect to MySQL server on 'ip'(10060 "Unknown error")问题描述： 使用 VMWare 搭建服务器后，然后安装好 MySQL ，成功启动。在本地尝试使用 Navicat 远程连接出现错误如下：2003 - Cant't connect to MySQL server on 'ip'(10060 "Unknown error")原因分析： 安装好的 MySQL 不允许远程登陆，所以需要 设置防火墙开放 MySQL 的 3306端口解决方法： 开放 3306 端口即可 ： firewall-cmd --zone=public --add-port=3306/tcp --permanent。重启防火墙 （以下命令选择其中一个即可），重新连接即可 systemctl restart firewalld.service。 ...

含义：出现在其他语句中的select语句，称为子查询或内查询，外部的查询语句，称为主查询或外查询。分类：按子查询出现的位置： select 后面： 仅仅支持标量子查询from 后面 支持表子查询where或having后面 标量子查询 列子查询 行子查询exists后面（相关子查询） 表子查询按结果集的行列数不同： 标量子查询（结果集只有一行一列）列子查询（结果集有一列多行）行子查询（结果集有一行多列）表子查询（结果集一般为多行多列）一、where或having后面特点： 子查询放在小括号内子查询一般放在条件的右侧标量子查询，一般搭配单行操作符使用> < >= <= = <>列子查询，一般搭配着多行操作符使用IN、ANY/SOME、ALL 标量子查询 查询谁的工资比Abel高 select salary from employees where last_name='Abel' // 一行一列 11000select last_name,salary from employees where salary > (select salary from employees where last_name='Abel')查询最低工资大于50号部门最低工资的部门id和其最低工资 #查询50号部门最低工资select min(salary) from employees where department_id=50 // 2100#按条件查询select department_id,min(salary) from employees GROUP BY department_id HAVING min(salary)> (select min(salary) from employees where department_id=50) 列子查询 返回location_id 是1400或1700的部门中所有员工姓名 #查询所有部门idselect department_id from departments where location_id=1400 or location_id=1700 ...

一、记录一次准备给客户预演示出现的问题 事故的背景：当所以功能开发完成后，开发人员在本地进行了测视已经没问题了。就把所有开发的功能模块合并到 dev 分支，进行打包，发布到预演示的线上环境。当在给相关人员进行演示的时候，出现了问题。 我们使用 https 调用对方的接口发送 Json 数据，对方进行校验马上返回校验的响应结果。问题出现在我们每次发送数据都是成功的，但是对方发送回来的数据，一直不能正常插入 DB（使用的是 Oracle）。 事故的真正原因： 因为有个同事在进行了 delete 后没有进行 commit 提交。导致表一直被锁住，不能被其他人使用。 但是杀死进程和本地 commit 几次后，依旧无法插入数据，提示还是 DB 被这个同事锁住。 最后查出的真正原因是，这个同事首先使用了 无线网络 进行了 DB 的操作，当时并没有 commit 提交操作。而后又插上网线继续工作，问题就出现在这里。 首先，当我们使用无线网络操作 DB 时，Oracle 会默认这是一次会话（session）,当开发人员对 DB 进行操作后（没有 commit ），又切换到了有线网络状态下，而这 2 种状态下的本机 IP 是不一样的（有兴趣的朋友可以试试使用无线和有线连接状态下，同一台电脑的 IP 地址是否一样）。 Oracle 会认为第一次 session 没有关闭，会将表锁住，等待这次 session 会话的提交直到结束。 所以当同事再连接上网线，使用有线网络进行 commit 时候会提示操作失败。因为 Oracle 数据库认为这又是一次新的 session 会话。而第一次的 session 会话并没有结束，就会导致出现了 DB 一直被锁的情况。 事故的解决办法： 首先使用语句查询（只有 Admin 用户才可以）出被锁住的表和锁表的开发人员的工号。 然后杀死这个进程或者进行 DB 重启即可。 ...

作者：杨涛涛今天有客户问起：如何汇总多台 MySQL 数据到一台上？ 我回答：可以尝试下 MySQL 的多源复制。 我们知道 MySQL 单主一从，单主多从，或者级联的主从架构我们都见的很多了。但是多主一从这种使用场景比较少，比如图1： 这种架构一般用在以下三类场景1.备份多台 Server 的数据到一台如果按照数据切分方向来讲，那就是垂直切分。 比如图 2， 业务 A、B、C、D 是之前拆分好的业务，现在需要把这些拆分好的业务汇总起来备份，那这种需求也很适用于多源复制架构。 实现方法我大概描述下：业务 A、B、C、D 分别位于4台 Server，每台 Server 分别有一个数据库来隔离前端的业务数据，那这样，在从库就能把四台业务的数据全部汇总起来，而不需要做额外的操作。 那没有多源复制之前，要实现这类需求，只能在汇总机器上搭建多个 MySQL 实例，那这样势必会涉及到跨库关联的问题，不但性能急剧下降，管理多个实例也没有单台来的容易。 2.用来聚合前端多个 Server 的分片数据。同样，按照数据切分方向来讲，属于水平切分。 比如图3，按照年份拆分好的数据，要做一个汇总数据展现， 那这种架构也非常合适。 实现方法稍微复杂些：比如所有 Server 共享同一数据库和表，一般为了开发极端透明，前端配置有分库分表的中间件，比如爱可生的 DBLE。 3. 汇总并合并多个 Server 的数据第三类和第一种场景类似。不一样的是不仅仅是数据需要汇总到目标端，还得合并这些数据，这就比第一种来的相对复杂些。 比如图4，那这样的需求，是不是也适合多源复制呢？ 答案是YES。 那具体怎么做呢？ 我举个例子，比如下面一张表 A，字段分表为 ID（主键）、F1、F2、F3... 、F100。那按照这样的分法，前端 4 台 Server 的表分别为： A1(ID,F1,F2,...,F25)A2(ID,F26,F27,...,F50)A3(ID,F51,F52,...,F75)A4(ID,F76,F77,...,F100)那上面几张表的数据如果要合并到表 A，可以建立一个 Event，定时的来给表 A 里插入数据。涉及到的核心 SQL 为： insert ignore into A select A1.ID,F1,F2,...,F100 \from A1 natural join A2 natural join A3 natural join A4;那我们发现这个和第一个类似，只不过，所有的表最后到复制到了相同的数据库里。 ...