数据库|MySQL学习数据库|MySQL

数据库概述
MySQL
MySQL 是最流行的关系型数据库管理系统，在WEB应用方面 MySQL 是最好的RDBMS(Relational Database Management System：关系数据库管理系统)应用软件之一
数据库
数据库就是存储数据的仓库，其本质是一个文件系统，数据按照特定的格式将数据存储起来，用户可以通过SQL对数据库中的数据进行增加，修改，删除及查询操作
关系型数据库
数据库中的记录是有行有列的数据库就是关系型数据库，与之相反的就是NoSQL数据库了
数据库和表

文章图片

数据库管理系统（DataBase Management System，DBMS）：指一种操作和管理数据库的大型软件，用于建立、使用和维护数据库，对数据库进行统一管理和控制，以保证数据库的安全性和完整性。用户通过数据库管理系统访问数据库中表内的数据。(记录)
常见的数据库管理系统
MYSQL：开源免费的数据库，小型的数据库.已经被Oracle收购了.MySQL5.5版本之后都是由Oracle发布的版本
Oracle：收费的大型数据库，Oracle公司的产品。Oracle收购SUN公司，收购MYSQL
DB2：IBM公司的数据库产品,收费的。常应用在银行系统中. 在中国的互联网公司，要求去IOE（IBM小型机、Oracle数据库、EMC存储设备）
SQLServer：MicroSoft 公司收费的中型的数据库。C#、.net等语言常使用
SyBase：已经淡出历史舞台。提供了一个非常专业数据建模的工具PowerDesigner
SQLite: 嵌入式的小型数据库，应用在手机端
MySQL发展历程
MySQL的历史可以追溯到1979年，一个名为Monty Widenius的程序员在为TcX的小公司打工，并且用BASIC设计了一个报表工具，使其可以在4MHz主频和16KB内存的计算机上运行。当时，这只是一个很底层的且仅面向报表的存储引擎，名叫Unireg
1990年，TcX公司的客户中开始有人要求为他的API提供SQL支持。Monty直接借助于mSQL的代码，将它集成到自己的存储引擎中。令人失望的是，效果并不太令人满意，决心自己重写一个SQL支持
1996年，MySQL 1.0发布，它只面向一小拨人，相当于内部发布
到了1996年10月，MySQL 3.11.1发布(MySQL没有2.x版本)，最开始只提供Solaris下的二进制版本。一个月后，Linux版本出现了。在接下来的两年里，MySQL被依次移植到各个平台
1999～2000年，MySQL AB公司在瑞典成立。Monty雇了几个人与Sleepycat合作，开发出了Berkeley DB引擎, 由于BDB支持事务处理，因此MySQL从此开始支持事务处理了
2000，MySQL不仅公布自己的源代码，并采用GPL(GNU General Public License)许可协议，正式进入开源世界。同年4月，MySQL对旧的存储引擎ISAM进行了整理，将其命名为MyISAM
2001年，集成Heikki Tuuri的存储引擎InnoDB，这个引擎不仅能持事务处理，并且支持行级锁。后来该引擎被证明是最为成功的MySQL事务存储引擎。MySQL与InnoDB的正式结合版本是4.0
2003年12月，MySQL 5.0版本发布，提供了视图、存储过程等功能
2008年1月，MySQL AB公司被Sun公司以10亿美金收购，MySQL数据库进入Sun时代。在Sun时代，Sun公司对其进行了大量的推广、优化、Bug修复等工作
2008年11月，MySQL 5.1发布，它提供了分区、事件管理，以及基于行的复制和基于磁盘的NDB集群系统，同时修复了大量的Bug
2009年4月，Oracle公司以74亿美元收购Sun公司，自此MySQL数据库进入Oracle时代，而其第三方的存储引擎InnoDB早在2005年就被Oracle公司收购
2010年12月，MySQL 5.5发布，其主要新特性包括半同步的复制及对SIGNAL/RESIGNAL的异常处理功能的支持，最重要的是InnoDB存储引擎终于变为当前MySQL的默认存储引擎。MySQL 5.5不是时隔两年后的一次简单的版本更新，而是加强了MySQL各个方面在企业级的特性。Oracle公司同时也承诺MySQL 5.5和未来版本仍是采用GPL授权的开源产品
MySQL安装
SQLyog 安装与使用
安装：
提供的SQLyog软件为免安装版，可直接使用

文章图片

输入用户名、密码，点击连接按钮，进行访问MySQL数据库进行操作

文章图片

在Query窗口中，输入SQL代码，选中要执行的SQL代码，按F8键运行，或按执行按钮运行
SQL语句
SQL语句介绍
什么是SQL
全称Structured Query Language，简称SQL，中文叫结构化查询语言
关系数据库语言的国际标准。SQL92、SQL99
不只是mysql还有其他数据库，在SQL92或者SQL99这些国际SQL标准基础之上，它们还扩展了自己的一些SQL语句，比如MySQL中的limit关键字
SQL分类：
数据定义语言：简称DDL(Data Definition Language)，用来定义数据库对象：数据库，表，列等。关键字：create，alter，drop等
数据操作语言：简称DML(Data Manipulation Language)，用来对数据库中表的记录进行更新。关键字：insert，delete，update等
数据控制语言：简称DCL(Data Control Language)，用来定义数据库的访问权限和安全级别，及创建用户；关键字：grant等
数据查询语言：简称DQL(Data Query Language)，用来查询数据库中表的记录。关键字：select，from，where等
数据库操作：database
创建数据库
create database 数据库名;
create database 数据库名 character set 字符集;

文章图片

查看数据库
查看数据库服务器中的所有的数据库:show databases;
查看某个数据库的定义的信息:show create database 数据库名;
删除数据库
drop database 数据库名称;
其他的数据库操作命令
切换数据库：use 数据库名;
查看正在使用的数据库:select database();
表操作：table
字段类型
常用的类型有：
数字型：int
浮点型：double
字符型：varchar（可变长字符串）
日期类型：date（只有年月日，没有时分秒）
datetime（年月日，时分秒）
boolean类型：不支持
创建表
create table 表名(
字段名类型(长度) 约束,
字段名类型(长度) 约束
);
单表约束：
主键约束：primary key
唯一约束：unique
非空约束：not null
注意：主键约束 = 唯一约束 + 非空约束
查看表
查看数据库中的所有表：show tables;
查看表结构：desc 表名;
删除表
drop table 表名;
修改表
alter table 表名 add 列名类型(长度) 约束; --修改表添加列.
alter table 表名 modify 列名类型(长度) 约束; --修改表修改列的类型长度及约束.
alter table 表名 change 旧列名新列名类型(长度) 约束; --修改表修改列名.
alter table 表名 drop 列名; --修改表删除列.
rename table 表名 to 新表名; --修改表名
alter table 表名 character set 字符集; --修改表的字符集
插入记录：insert
insert into 表 (列名1,列名2,列名3..) values(值1,值2,值3..); -- 向表中插入某些列
insert into 表 values (值1,值2,值3..); --向表中插入所有列
insert into 表 (列名1,列名2,列名3..) values select (列名1,列名2,列名3..) from 表
insert into 表 values select * from 表
注意：
列名数与values后面的值的个数相等
列的顺序与插入的值得顺序一致
列名的类型与插入的值要一致
插入值得时候不能超过最大长度
值如果是字符串或者日期需要加引号’’ （一般是单引号）
更新记录：update
语法：
update 表名 set 字段名=值,字段名=值;
update 表名 set 字段名=值,字段名=值 where 条件;
注意：
列名的类型与修改的值要一致
修改值得时候不能超过最大长度
值如果是字符串或者日期需要加’’
删除记录：delete
语法：
delete from 表名 [where 条件];
删除表中所有记录使用delete from 表名; 还是用truncate table 表名;
删除方式：delete 一条一条删除，不清空auto_increment记录数；truncate 直接将表删除，重新建表，auto_increment将置为零，从新开始
CMD中文乱码
修改my.ini文件，然后重启mysql服务器

文章图片

SQL查询
查询语句，在开发中使用的次数最多，此处使用“products”商品表
主键类型，本案例采用int类型，实际案例采用是varchar类型
时间类型，本案例采用date类型，实际案例采用datetime类型
准备工作
创建商品表：
CREATE TABLE products (
pid INT PRIMARY KEY AUTO_INCREMENT, #自增加 AUTO_INCREMENT
pname VARCHAR(20),#商品名称
price DOUBLE, #商品价格
pdate DATE, # 日期
sid VARCHAR(20) #分类ID
);

INSERT INTO products VALUES(NULL,'泰国大榴莲', 98, NULL, 's001');
INSERT INTO products VALUES(NULL,'新疆大枣', 38, NULL, 's002');
INSERT INTO products VALUES(NULL,'新疆切糕', 68, NULL, 's001');
INSERT INTO products VALUES(NULL,'十三香', 10, NULL, 's002');
INSERT INTO products VALUES(NULL,'老干妈', 20, NULL, 's002');
查询语法
SELECT [DISTINCT]*[列名,列名] FROM表 [WHERE 条件]
SQL语法关键字：
SELECT
FROM
案例：
1.查询所有的商品.select * from product;
2.查询商品名和商品价格.select pname,price from product;
3.别名查询，使用的as关键字，as可以省略的.
3.1表别名: select * from product as p;
3.2列别名：select pname as pn from product;
4.去掉重复值.select distinct price from product;
5.查询结果是表达式（运算查询）：将所有商品的价格+10元进行显示.
select pname,price+10 from product;
条件查询
SQL语法关键字：
WHERE
案例：
1.查询商品名称为十三香的商品所有信息：
select * from product where pname = '十三香';
2.查询商品价格>60元的所有的商品信息:
select * from product where price > 60;
where后的条件写法：
> ,<,=,>=,<=,<>
like 使用占位符 _ 和 %_代表一个字符 %代表任意个字符
select * from product where pname like '%新%'
in在某个范围中获得值（exists）
select * from product where pid in (2,5,8)

文章图片

文章图片

排序
SQL语法关键字：
ORDER BY
ASC（升序） DESC（降序）
案例：
1.查询所有的商品，按价格进行排序.(asc-升序,desc-降序)
select * from product order by price;
2.查询名称有新的商品的信息并且按价格降序排序
select * from product where pname like '%新%' order by price desc;
聚合函数（组函数）
特点：只对单列进行操作
常用的聚合函数：
sum()：求某一列的和
avg()：求某一列的平均值
max()：求某一列的最大值
min()：求某一列的最小值
count()：求某一列的元素个数
案例：
1.获得所有商品的价格的总和：select sum(price) from product;
2.获得所有商品的平均价格：select avg(price) from product;
3.获得所有商品的个数：select count(*) from product;
分组
SQL语法关键字：
GROUP BY
HAVING
案例：
1.根据cno字段分组，分组后统计商品的个数
select cid,count(*) from product group by cid;
2.根据cno分组，分组统计每组商品的平均价格，并且平均价格> 60;
select cid,avg(price) from product group by cid having avg(price)>60;
注意事项：
select语句中的列（非聚合函数列），必须出现在group by子句中
group by子句中的列，不一定要出现在select语句中
聚合函数只能出现select语句中或者having语句中，一定不能出现在where语句中
其他查询语句
union 集合的并集（不包含重复记录）
unionall集合的并集（包含重复记录）
SQL查询之语法顺序和执行顺序（重要）
MySQL查询语法顺序
1.SELECT
2.FROM
3.LEFT JOIN
4.ON
5.WHERE
6.GROUP BY
7.HAVING
8.ORDER BY
9.LIMIT
执行顺序
示例SQL：
SELECT * FROM user LEFT JOIN order ON user.id = order.uid WHERE order.price > 1000 GROUP BY user.name HAVING count(1) > 5 ORDER BY user.name LIMIT 0,10
1.FROM（将最近的两张表，进行笛卡尔积）---VT1
2.ON（将VT1按照它的条件进行过滤）---VT2\
3.LEFT JOIN（保留左表的记录）---VT3
4.WHERE（过滤VT3中的记录）--VT4…VTn
5.GROUP BY（对VT4的记录进行分组）---VT5
6.HAVING（对VT5中的记录进行过滤）---VT6
7.SELECT（对VT6中的记录，选取指定的列）--VT7
8.ORDER BY（对VT7的记录进行排序）--游标
9.LIMIT（对排序之后的值进行分页）
WHERE条件执行顺序（影响性能）
MYSQL：从左往右去执行WHERE条件的
Oracle：从右往左去执行WHERE条件的
结论：写WHERE条件的时候，优先级高的部分要去编写过滤力度最大的条件语句
多表之间的关系

文章图片

如上图所示，实际业务数据库中的表之间都是有关系的
表与表之间的关系
表与表之间的关系，说的就是表与表之间数据的关系
一对一关系
常见实例：一夫一妻
一对多关系
常见实例：会员和订单
多对多关系（需要中间表实现）
常见实例：商品和订单
外键
如何表示表与表之间的关系呢？就是使用外键来约束的
表的角色：主表和从表（需要建立关系才有了主从表的角色区分）
主从表的理解

文章图片

商品应该有所属的分类，这个时候需要将分类表和商品表建立关系，如何建立
按照以上需求分析：
主表是：商品表。主表中，应该有一个字段去关联从表，而这个关联字段就是外键
从表是：分类表。从表中，应该有一个字段去关联主表，而这个关联字段就是主键
主键外键的理解

文章图片

如何操作外键
主表添加外键的格式：
alter table 表名 add [constraint][约束名称] foreign key (主表外键字段) references 从表(从表主键)
主表删除外键的格式：
alter table 表名 drop foreign key 外键约束名称
使用外键目的：
保证数据完整性（数据保存在多张表中的时候）
在互联网项目中，一般情况下，不建议建立外键关系
一对一关系（了解）
在实际工作中，一对一在开发中应用不多，因为一对一完全可以创建成一张表
案例：一个丈夫只能有一个妻子
建表语句：
CREATE TABLE wife(
idINT PRIMARY KEY ,
wnameVARCHAR(20),
sexCHAR(1)
);

CREATE TABLE husband(
idINT PRIMARY KEY ,
hnameVARCHAR(20),
sexCHAR(1)
);
一对一关系创建方式1之外键唯一：
添加外键列wid，指定该列的约束为唯一（不加唯一约束就是一对多关系）
ALTER TABLE husband ADD widINTUNIQUE;
添加外键约束
alter table husband add foreign key (wid) references wife(id);
一对一关系创建方式2之主键做外键：
使用主表的主键作为外键去关联从表的主键
一对多关系
案例：一个分类对应多个商品

文章图片

总结：有外键的就是多的一方
注意事项：
一对多关系和一对一关系的创建很类似，唯一区别就是外键不唯一
一对多关系创建：
添加外键列
添加外键约束
案例：
在商品表中添加一条记录，该记录的cid在分类表中不存在
在分类表中，删除一条记录，这条记录在商品表中有外键关联
多对多关系
案例：同一个商品对应多个订单，一个订单对应多个商品

文章图片

注意事项：
需要中间表去完成多对多关系的创建
多对多关系其实就是两个一对多关系的组合
多对多关系创建：
创建中间表，并在其中创建多对多关系中两张表的外键列
在中间表中添加外键约束
在中间表中添加联合主键约束
多表关联查询
使用 MySQL 的 JOIN 在两个或多个表中查询数
在 SELECT, UPDATE 和 DELETE 语句中使用 MySQL 的 JOIN 来联合多表查询
JOIN 按照功能大致分为如下三类：
CROSS JOIN（交叉连接）
INNER JOIN（内连接或等值连接）
OUTER JOIN（外连接）
建表语句：
insert into category (cid,cname) values ('c001','家电');
insert into category (cid,cname) values ('c002','服饰');
insert into category (cid,cname) values ('c003','化妆品');

insert into product(pid,pname,price,cid) values ('p001','联想',5000,'c001');
insert into product(pid,pname,price,cid) values ('p002','海尔',3000,'c001');
insert into product(pid,pname,price,cid) values ('p003','雷神',5000,'c001');

insert into product(pid,pname,price,cid) values ('p004','阿迪',1000,'c002');
insert into product(pid,pname,price,cid) values ('p005','耐克',1200,'c002');
insert into product(pid,pname,price,cid) values ('p006','NB',800,'c002');
insert into product(pid,pname,price,cid) values ('p007','彪马',600,'c002');

insert into product(pid,pname,price,cid) values ('p008','雪花秀',1500,'c003');
insert into product(pid,pname,price,cid) values ('p009','悦诗风吟',1100,'c003');
交叉连接
交叉连接的关键字：CROSS JOIN
交叉连接也叫笛卡尔积连接
笛卡尔积是指在数学中，两个集合X和Y的笛卡尓积（Cartesian product），又称直积，表示为X*Y，第一个对象是X的成员而第二个对象是Y的所有可能有序对的其中一个成员
交叉连接的表现：行数相乘、列数相加
隐式交叉连接
SELECT*FROMA, B
显式交叉连接
SELECT*FROMACROSSJOINB
内连接
内连接的关键字：INNER JOIN
内连接也叫等值连接，内联接使用比较运算符根据每个表共有的列的值匹配两个表中的行
隐式内连接
SELECT*FROMA,BWHEREA.id = B.id
显式内连接
SELECT*FROMAINNER JOINB ON A.id = B.id
外连接
外联接可以是左向外联接、右向外联接或完整外部联接。也就是说外连接又分为：左外连接、右外连接、全外连接
外连接需要有主表或者保留表的概念
在 FROM子句中指定外联接时，可以由下列几组关键字中的一组指定：
左外连接：LEFT JOIN 或者 LEFT OUTER JOIN
SELECT*FROMALEFTJOINB ON A.id = B.id
右外连接:：RIGHT JOIN 或者 RIGHT OUTER JOIN
SELECT*FROMARIGHTJOINB ON A.id = B.id
全外连接（MySQL不支持）：FULLJOIN 或 FULL OUTER JOIN
SELECT*FROMAFULL JOINB ON A.id = B.id
外连接总结：
通过业务需求，分析主从表
如果使用LEFT JOIN，则主表在它左边
如果使用RIGHT JOIN，则主表在它右边
查询结果以主表为主，从表记录匹配不到，则补null
分页查询
MySQL的分页关键字是：LIMIT
LIMIT关键字不是SQL92标准提出的关键字，它是MySQL独有的语法
通过Limit关键字，MySQL实现了物理分页
分页分为逻辑分页和物理分页
逻辑分页：将数据库中的数据查询到内存之后再进行分页
物理分页：通过LIMIT关键字，直接在数据库中进行分页，最终返回的数据，只是分页后的数据
格式：
SELECT * FROM table LIMIT [offset,] rows
offset：编译量
rows：每页多少行记录
子查询
定义
子查询允许把一个查询嵌套在另一个查询当中
子查询，又叫内部查询，相对于内部查询，包含内部查询的就称为外部查询
子查询可以包含普通select可以包括的任何子句，比如：distinct、 group by、order by、limit、join和union等；
但是对应的外部查询必须是以下语句之一：select、insert、update、delete
位置
select 中、from 后、where 中
group by 和order by 中无实用意义
查询原理
第一、单表查询：根据WHERE条件过滤表中的记录，形成中间表（这个中间表对用户是不可见的）；然后根据SELECT的选择列选择相应的列进行返回最终结果
第二、两表连接查询：对两表求积（笛卡尔积）并用ON条件和连接连接类型进行过滤形成中间表；然后根据WHERE条件过滤中间表的记录，并根据SELECT指定的列返回查询结果
第三、多表连接查询：先对第一个和第二个表按照两表连接做查询，然后用查询结果和第三个表做连接查询，以此类推，直到所有的表都连接上为止，最终形成一个中间的结果表，然后根据WHERE条件过滤中间表的记录，并根据SELECT指定的列返回查询结果
理解SQL查询的过程是进行SQL优化的理论依据
MySQL架构
逻辑架构图

文章图片

Connectors
指的是不同语言中与SQL的交互
Management Serveices & Utilities
系统管理和控制工具
Connection Pool: 连接池
管理缓冲用户连接，线程处理等需要缓存的需求
负责监听对 MySQL Server 的各种请求，接收连接请求，转发所有连接请求到线程管理模块。每一个连接上 MySQL Server 的客户端请求都会被分配（或创建）一个连接线程为其单独服务
而连接线程的主要工作就是负责 MySQL Server 与客户端的通信，接受客户端的命令请求，传递 Server 端的结果信息等。线程管理模块则负责管理维护这些连接线程。包括线程的创建，线程的 cache 等
SQL Interface: SQL接口
接受用户的SQL命令，并且返回用户需要查询的结果。比如select from就是调用SQL Interface
Parser: 解析器
SQL命令传递到解析器的时候会被解析器验证和解析
主要功能：
a . 将SQL语句进行语义和语法的分析，分解成数据结构，然后按照不同的操作类型进行分类，然后做出针对性的转发到后续步骤，以后SQL语句的传递和处理就是基于这个结构的
b.如果在分解构成中遇到错误，那么就说明这个sql语句是不合理的
Optimizer: 查询优化器
SQL语句在查询之前会使用查询优化器对查询进行优化
它使用的是“选取-投影-联接”策略进行查询
用一个例子就可以理解： select uid,name from user where gender = 1;
这个select 查询先根据where 语句进行选取，而不是先将表全部查询出来以后再进行过滤
这个select查询先根据uid和name进行属性投影，而不是将属性全部取出以后再进行过滤
将这两个查询条件联接起来生成最终查询结果
Cache和Buffer：查询缓存
主要功能是将客户端提交给MySQL的 select请求的返回结果集 cache 到内存中，与该 query 的一个 hash 值做一个对应。该 Query 所取数据的基表发生任何数据的变化之后， MySQL 会自动使该 query 的Cache 失效。在读写比例非常高的应用系统中， Query Cache 对性能的提高是非常显著的。当然它对内存的消耗也是非常大的。如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等
存储引擎接口
存储引擎接口模块可以说是 MySQL 数据库中最有特色的一点了。目前各种数据库产品中，基本上只有 MySQL 可以实现其底层数据存储引擎的插件式管理。这个模块实际上只是一个抽象类，但正是因为它成功地将各种数据处理高度抽象化，才成就了今天 MySQL 可插拔存储引擎的特色
从图还可以看出，MySQL区别于其他数据库的最重要的特点就是其插件式的表存储引擎。MySQL插件式的存储引擎架构提供了一系列标准的管理和服务支持，这些标准与存储引擎本身无关，可能是每个数据库系统本身都必需的，如SQL分析器和优化器等，而存储引擎是底层物理结构的实现，每个存储引擎开发者都可以按照自己的意愿来进行开发
注意：存储引擎是基于表的，而不是数据库
逻辑架构图

文章图片

执行流程图

文章图片

存储引擎介绍
多存储引擎是mysql有别于其他数据库的一大特性
存储引擎是针对表的
MySQL 5.5之后，默认的存储引擎由MyISAM变为InnoDB
查看存储引擎：show engines

文章图片

MySQL物理结构
MySQL是通过文件系统对数据进行存储和管理的
MySQL从物理结构上可以分为日志文件和数据文件
日志文件
MySQL通过日志记录了数据库操作信息和错误信息。常用的日志文件包括错误日志、二进制日志、查询日志、慢查询日志和 InnoDB 引擎在线 Redo 日志、中继日志等
错误日志（err log）：
默认是开启的，而且从5.5.7以后无法关闭错误日志
记录了运行过程中遇到的所有严重的错误信息,以及 MySQL每次启动和关闭的详细信息
默认的错误日志名称：hostname.err( system hostname)
错误日志所记录的信息是可以通过log-error和log-warnings来定义的，其中log-err是定义是否启用错误日志的功能和错误日志的存储位置，log-warnings是定义是否将警告信息也定义至错误日志中
log_error可以直接定义为文件路径，也可以为ON|OFF；log_warings只能使用1|0来定义开关启动
二进制日志（bin log）：
默认是关闭的，需要通过配置：log-bin=mysql-bin进行开启。其中mysql-bin是binlog日志文件的basename，binlog日志文件的名称：mysql-bin-000001.log
binlog记录了数据库所有的ddl语句和dml语句，但不包括select语句内容，语句以事件的形式保存，描述了数据的变更顺序，binlog还包括了每个更新语句的执行时间信息，binlog主要作用是用于恢复数据，因此binlog对于灾难恢复和备份恢复来说至关重要
如果是DDL语句，则直接记录到binlog日志，而DML语句，必须通过事务提交才能记录到binlog日志中
binlog还用于实现mysql主从复制
binlog还用于数据恢复
通用查询日志（general query log）：
默认情况下通用查询日志是关闭的
由于通用查询日志会记录用户的所有操作，其中还包含增删查改等信息，在并发操作大的环境下会产生大量的信息从而导致不必要的磁盘IO，会影响mysql的性能的。如若不是为了调试数据库的目的建议不要开启查询日志
慢查询日志（slow query log）:
默认是关闭的。需要通过设置：slow_query_log=ON进行开启
记录执行时间超过long_query_time秒的所有查询，便于收集查询时间比较长的SQL语句
事务日志：
事务日志（InnoDB特有的日志）也叫redo日志
文件名为"ib_logfile0"和“ib_logfile1”，默认存放在表空间所在目录
还有一个日志文件叫undo 日志，默认存储在ib_data目录下
中继日志：
是在主从复制环境中产生的日志
主要作用是为了从机可以从中继日志中获取到主机同步过来的SQL语句，然后执行到从机中
数据文件
查看MySQL数据文件：SHOW VARIABLES LIKE ‘%datadir%’
.frm文件：主要存放与表相关的数据信息,主要包括表结构的定义信息
.ibd和.ibdata文件：用来存储InnoDB存储引擎的表数据和索引信息
.myd文件：主要用来存储使用MyISAM存储引擎的表数据信息
.myi文件：主要用来存储使用MyISAM存储引擎的表数据文件中任何索引的数据树
MySQL索引
介绍
使用索引的主要目的是为了优化查询速度
索引是一种特殊的文件或者叫数据结构(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度
索引的分类
索引是在存储引擎中实现的，也就是说不同的存储引擎，会使用不同的索引
MyISAM和InnoDB存储引擎：只支持BTREE索引，也就是说默认使用BTREE，不能够更换
MEMORY/HEAP存储引擎：支持HASH和BTREE索引
索引的分类
单列索引：
普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点
唯一索引：索引列中的值必须是唯一的，但是允许为空值
主键索引：是一种特殊的唯一索引，不允许有空值
组合索引：
在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合
全文索引：
全文索引，只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引
空间索引：不做介绍，一般使用不到
索引的使用
创建索引
单列索引之普通索引
CREATE INDEX index_name ON table(column(length))
ALTER TABLE table_name ADD INDEX index_name ON (column(length))
单列索引之唯一索引
CREATE UNIQUE INDEX index_name ON table(column(length))
单列索引之全文索引
CREATE FULLTEXT INDEX index_name ON table(column(length))
联合索引
ALTER TABLE article ADD INDEX index_titme_time (title(50),time(10))
删除索引
DROP INDEX index_name ON table
索引的存储结构
https://www.cs.usfca.edu/~galles/visualization/Algorithms.html
B Tree和B+ Tree的特点与区别
树的高度一般都是在2-4这个高度，树的高度直接影响IO读写的次数
如果是三层树结构---支撑的数据可以达到20G，如果是四层树结构---支撑的数据可以达到几十T
B Tree和B+ Tree的最大区别在于非叶子节点是否存储数据的问题。B Tree是非叶子节点和叶子节点都会存储数据。而B+ Tree只有叶子节点才会存储数据，而且存储的数据都是在一行上，而且这些数据都是有指针指向的，也就是有顺序的
非聚集索引
叶子节点只会存储数据行的指针，简单来说数据和索引不在一起，就是非聚集索引
主键索引和辅助索引都会存储指针的值
聚集索引（InnoDB）
主键索引（聚集索引）的叶子节点会存储数据行，也就是说数据和索引是在一起，这就是聚集索引
辅助索引只会存储主键值
如果没有没有主键，则使用唯一索引建立聚集索引；如果没有唯一索引，MySQL会按照一定规则创建聚集索引

文章图片

使用索引的注意事项
尽量创建组合索引（组合索引其实会默认按照最左前缀原则帮我们创建多组索引）组合索引（id,name,sex）
索引最左前缀原则
索引覆盖：要查询的列，也要使用索引覆盖住
MySQL性能优化之查看执行计划explain
MySQL 提供了一个 EXPLAIN 命令, 它可以对 SELECT 语句进行分析, 并输出 SELECT 执行的详细信息, 以供开发人员针对性优化
使用explain这个命令来查看一个这些SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有做全表扫描，这都可以通过explain命令来查看
可以通过explain命令深入了解MySQL的基于开销的优化器，还可以获得很多可能被优化器考虑到的访问策略的细节，以及当运行SQL语句时哪种策略预计会被优化器采用
EXPLAIN 命令用法十分简单, 在 SELECT 语句前加上 explain 就可以了, 例如:

文章图片

参数说明
expain出来的信息有10列，分别是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra,下面对这些字段进行解释：
id: SELECT 查询的标识符. 每个 SELECT 都会自动分配一个唯一的标识符
select_type: SELECT 查询的类型
table: 查询的是哪个表
partitions: 匹配的分区
type: join 类型
possible_keys: 此次查询中可能选用的索引
key: 此次查询中确切使用到的索引
ref: 哪个字段或常数与 key 一起被使用
rows: 显示此查询一共扫描了多少行. 这个是一个估计值
filtered: 表示此查询条件所过滤的数据的百分比
extra: 额外的信息
select_type列说明
SIMPLE, 表示此查询不包含 UNION 查询或子查询
PRIMARY, 表示此查询是最外层的查询
UNION, 表示此查询是 UNION 的第二或随后的查询
DEPENDENT UNION, UNION 中的第二个或后面的查询语句, 取决于外面的查询
UNION RESULT, UNION 的结果
SUBQUERY, 子查询中的第一个 SELECT
DEPENDENT SUBQUERY: 子查询中的第一个 SELECT, 取决于外面的查询. 即子查询依赖于外层查询的结果
type列说明
通常来说, 不同的 type 类型的性能关系如下:
ALL < index < range ~ index_merge < ref < eq_ref < const < system
system：表只有一行
const：表最多只有一行匹配，通用用于主键或者唯一索引比较时
eq_ref：每次与之前的表合并行都只在该表读取一行，这是除了system，const之外最好的一种，特点是使用=，而且索引的所有部分都参与join且索引是主键或非空唯一键的索引
ref：如果每次只匹配少数行，那就是比较好的一种，使用=或<=>，可以是左覆盖索引或非主键或非唯一键
fulltext：全文搜索
ref_or_null：与ref类似，但包括NULL
index_merge：表示出现了索引合并优化(包括交集，并集以及交集之间的并集)，但不包括跨表和全文索引。这个比较复杂，目前的理解是合并单表的范围索引扫描（如果成本估算比普通的range要更优的话）
unique_subquery：在in子查询中，就是value in (select...)把形如“select unique_key_column”的子查询替换。PS：所以不一定in子句中使用子查询就是低效的！
index_subquery：同上，但把形如”select non_unique_key_column“的子查询替换
index：
a.当查询是索引覆盖的，即所有数据均可从索引树获取的时候（Extra中有Using Index）
b.以索引顺序从索引中查找数据行的全表扫描（无 Using Index）
c.如果Extra中Using Index与Using Where同时出现的话，则是利用索引查找键值的意思
d.如单独出现，则是用读索引来代替读行，但不用于查找
all：全表扫描
参考网站
https://segmentfault.com/a/1190000008131735
https://blog.csdn.net/rewiner120/article/details/70598797
MySQL性能优化之慢查询
性能优化的思路
首先需要使用慢查询功能，去获取所有查询时间比较长的SQL语句
其次使用explain命令去查看有问题的SQL的执行计划
最后可以使用show profile[s] 查看有问题的SQL的性能使用情况
数据库查询快慢是影响项目性能的一大因素，对于数据库，除了要优化 SQL，更重要的是得先找到需要优化的 SQL
MySQL 数据库有一个“慢查询日志”功能，用来记录查询时间超过某个设定值的SQL，这将极大程度帮助我们快速定位到症结所在，以便对症下药
至于查询时间的多少才算慢，每个项目、业务都有不同的要求
传统企业的软件允许查询时间高于某个值，但是把这个标准放在互联网项目或者访问量大的网站上，估计就是一个bug，甚至可能升级为一个功能性缺陷
MySQL的慢查询日志功能，默认是关闭的，需要手动开启
开启慢查询功能
查看是否开启慢查询功能

文章图片

参数说明：
slow_query_log：是否开启慢查询日志，ON 为开启，OFF 为关闭，如果为关闭可以开启
log-slow-queries：旧版（5.6以下版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log
slow-query-log-file：新版（5.6及以上版本）MySQL数据库慢查询日志存储路径。可以不设置该参数，系统则会默认给一个缺省的文件host_name-slow.log
long_query_time ：慢查询阈值，当查询时间多于设定的阈值时，记录日志，单位为秒
临时开启慢查询功能
在 MySQL 执行 SQL 语句设置，但是如果重启 MySQL 的话将失效
set global slow_query_log = ON;
set global long_query_time = 1;

文章图片

永久开启慢查询功能
修改/etc/my.cnf配置文件，重启 MySQL, 这种永久生效
[mysqld]
slow_query_log = ON
slow_query_log_file = /var/log/mysql/slow.log
long_query_time = 1

文章图片

慢日志格式

文章图片

文章图片

格式说明：
第一行,SQL查询执行的时间
第二行,执行SQL查询的连接信息，用户和连接IP
第三行,记录了一些我们比较有用的信息，如下解析
Query_time,这条SQL执行的时间,越长则越慢
Lock_time,在MySQL服务器阶段(不是在存储引擎阶段)等待表锁时间
Rows_sent,查询返回的行数
Rows_examined,查询检查的行数，越长就当然越费时间
第四行,设置时间戳，没有实际意义，只是和第一行对应执行时间
第五行及后面所有行（第二个# Time:之前）,执行的sql语句记录信息，因为sql可能会很长
分析慢查询日志
MySQL自带的mysqldumpslow
mysqldumpslow /var/lib/mysql/localhost-slow.log
常用参数说明：
-s：是表示按照何种方式排序

文章图片

-t：是top n的意思，即为返回前面多少条的数据
-g：后边可以写一个正则匹配模式，大小写不敏感的
示例：
得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g “left join” /var/lib/mysql/localhost_slow.log
使用mysqlsla 工具
mysqlsla工具，功能非常强大。数据报表，非常有利于分析慢查询的原因，包括执行频率，数据量，查询消耗等
不过此工具已停止维护，项目 github 介绍页面推荐使用 percona-toolkit
mysqlsla -lt /var/log/mysql/slow.log
使用percona-toolkit工具
percona-toolkit是一组高级命令行工具的集合，可以查看当前服务的摘要信息，磁盘检测，分析慢查询日志，查找重复索引，实现表同步等等
下载
https://www.percona.com/downloads/percona-toolkit/3.0.11/binary/tarball/percona-toolkit-3.0.11_x86_64.tar.gz
安装
tar -xf percona-toolkit-3.0.11_x86_64.tar.gz
cd percona-toolkit-3.0.11
perl Makefile.PL
make
make install
调错
Can't locate ExtUtils/MakeMaker.pm in @INC 错误的解决方式
yum install -y perl-ExtUtils-CBuilder perl-ExtUtils-MakeMaker
Can't locate Time/HiRes.pm in @INC
yum install -y perl-Time-HiRes
使用pt-query-digest查看慢查询日志
pt-query-digest /var/lib/mysql/localhost-slow.log
MySQL性能分析语句show profile
Query Profiler是MYSQL自带的一种query诊断分析工具，通过它可以分析出一条SQL语句的性能瓶颈在什么地方
通常是使用的explain,以及slow query log都无法做到精确分析，但是Query Profiler却可以定位出一条SQL语句执行的各种资源消耗情况，比如CPU，IO等，以及该SQL执行所耗费的时间等。不过该工具只有在MYSQL 5.0.37以及以上版本中才有实现
默认的情况下，MYSQL的该功能没有打开，需要自己手动启动
语句使用
show profile 和 show profiles 语句可以展示当前会话(退出session后,profiling重置为0) 中执行语句的资源使用情况
show profiles :以列表形式显示最近发送到服务器上执行的语句的资源使用情况.显示的记录数由变量:profiling_history_size 控制,默认15条

文章图片

show profile: 展示最近一条语句执行的详细资源占用信息,默认显示 Status和Duration两列

文章图片

show profile 还可根据 show profiles 列表中的 Query_ID ,选择显示某条记录的性能分析信息

文章图片

开启Profile功能
Profile 功能由MySQL会话变量 : profiling控制,默认是OFF关闭状态
查看是否开启了Profile功能:
select @@profiling;
show variables like '%profil%';

文章图片

开启profile功能
set profiling=1; --1是开启、0是关闭
示例
查看是否打开了性能分析功能
select @@profiling;

文章图片

打开 profiling 功能
set profiling=1;

文章图片

执行sql语句

文章图片

执行 show profiles 查看分析列表

文章图片

查询第二条语句的执行情况
show profile for query 2;

文章图片

可指定资源类型查询
show profile cpu,swaps for query 2;

文章图片

MySQL性能优化细节
合理的创建及使用索引（考虑数据的增删情况）
合理的冗余字段（尽量建一些大表，考虑数据库的三范式和业务设计的取舍）
使用SQL要注意一些细节：select语句中尽量不要使用*、count(*)，WHERE语句中尽量不要使用1=1、in语句（建议使用exists）、注意组合索引的创建顺序按照顺序组着查询条件、尽量查询粒度大的SQL放到最左边、尽量建立组合索引
合理利用慢查询日志、explain执行计划查询、show profile查看SQL执行时的资源使用情况
MySQL事务处理
事务概述
在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
事务用来管理DDL、DML、DCL操作，比如 insert,update,delete 语句，默认是自动提交的。
一般来说，事务是必须满足4个条件（ACID）：
Atomicity（原子性）
Consistency（稳定性、一致性）
Isolation（隔离性）
Durability（可靠性、持久性）
对于ACID的解释如下：
原子性：构成事务的的所有操作必须是一个逻辑单元，要么全部执行，要么全部不执行
稳定性（一致性）：数据库在事务执行前后状态都必须是稳定的或者是一致的
隔离性：事务之间不会相互影响
可靠性（持久性）：事务执行成功后必须全部写入磁盘
MySQL事务支持
常见的操作有一下三个：
BEGIN或START TRANSACTION；显式地开启一个事务；
COMMIT；也可以使用COMMIT WORK，不过二者是等价的。COMMIT会提交事务，并使已对数据库进行的所有修改称为永久性的；
ROLLBACK；有可以使用ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交
事务并发问题
在事务的并发操作中可能会出现一些问题：
丢失更新：一个事务更新之后，另一个事务也更新了，但是第二个事务回滚了，则第一个事务也被回滚了
脏读：一个事务读取到另一个事务未提交的数据
不可重复读：一个事务因读取到另一个事务已提交的数据。导致对同一条记录读取两次以上的结果不一致。update操作
幻读：一个事务因读取到另一个事务已提交的数据。导致对同一张表读取两次以上的结果不一致。insert、delete操作
事务隔离级别
为了避免上面出现的几种情况，在标准SQL规范中，定义了4个事务隔离级别，不同的隔离级别对事务的处理不同
四种隔离级别：
Read uncommitted (读未提交)：最低级别，任何情况都无法保证
Read committed (读已提交)：可避免脏读的发生
Repeatable read (可重复读)：可避免脏读、不可重复读的发生
Serializable (串行化)：可避免脏读、不可重复读、幻读的发生
默认隔离级别
大多数数据库的默认隔离级别是Read committed，比如Oracle、DB2等
MySQL数据库的默认隔离级别是Repeatable read
如何查看和设置隔离级别：
在MySQL数据库中查看当前事务的隔离级别：
select @@tx_isolation;
在MySQL数据库中设置事务的隔离级别：
set[glogal | session]transaction isolation level 隔离级别名称;
set tx_isolation=’隔离级别名称;
注意事项：
隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大
对于多数应用程序，可以优先考虑把数据库系统的隔离级别设为Read Committed。它能够避免脏读取，而且具有较好的并发性能。尽管它会导致不可重复读、幻读这些并发问题，在可能出现这类问题的个别场合，可以由应用程序采用悲观锁或乐观锁来控制
MySQL锁
数据库锁定机制简单来说就是数据库为了保证数据的一致性而使各种共享资源在被并发访问访变得有序所设计的一种规则
对于任何一种数据库来说都需要有相应的锁定机制，所以MySQL自然也不能例外
MySQL数据库由于其自身架构的特点，存在多种数据存储引擎，每种存储引擎所针对的应用场景特点都不太一样，为了满足各自特定应用场景的需求，每种存储引擎的锁定机制都是为各自所面对的特定场景而优化设计，所以各存储引擎的锁定机制也有较大区别
总的来说，MySQL各存储引擎使用了三种类型（级别）的锁定机制：行级锁定，页级锁定和表级锁定。下面我们先分析一下MySQL这三种锁定的特点和各自的优劣所在
按照锁的粒度来分：行级锁和表级锁
按照锁的功能来分：共享读锁和排他写锁
悲观锁（排他写锁）和乐观锁（使用某一版本列或者唯一列进行逻辑控制）
MySQL的InnoDB和MyISAM存储引擎最大的区别：事务的支持、行级锁的支持
行级锁定（row-level）
行级锁定最大的特点就是锁定对象的颗粒度很小，也是目前各大数据库管理软件所实现的锁定颗粒度最小的。由于锁定颗粒度很小，所以发生锁定资源争用的概率也最小，能够给予应用程序尽可能大的并发处理能力而提高一些需要高并发应用系统的整体性能。
虽然能够在并发处理能力上面有较大的优势，但是行级锁定也因此带来了不少弊端。由于锁定资源的颗粒度很小，所以每次获取锁和释放锁需要做的事情也更多，带来的消耗自然也就更大了。此外，行级锁定也最容易发生死锁
表级锁定（table-level）
和行级锁定相反，表级别的锁定是MySQL各存储引擎中最大颗粒度的锁定机制。该锁定机制最大的特点是实现逻辑非常简单，带来的系统负面影响最小。所以获取锁和释放锁的速度很快。由于表级锁一次会将整个表锁定，所以可以很好的避免困扰我们的死锁问题。
当然，锁定颗粒度大所带来最大的负面影响就是出现锁定资源争用的概率也会最高，致使并大度大打折扣
页级锁定（page-level）
页级锁定是MySQL中比较独特的一种锁定级别，在其他数据库管理软件中也并不是太常见。页级锁定的特点是锁定颗粒度介于行级锁定与表级锁之间，所以获取锁定所需要的资源开销，以及所能提供的并发处理能力也同样是介于上面二者之间。另外，页级锁定和行级锁定一样，会发生死锁
总的来说，MySQL这3种锁的特性可大致归纳如下：
表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低；
行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高；
页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般；
表级锁
MySQL的表级锁定有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）
MySQL 实现的表级锁定的争用状态变量：show status like 'table%';

文章图片

table_locks_immediate：产生表级锁定的次数；
table_locks_waited：出现表级锁定争用而发生等待的次数；
手动增加表锁
lock table 表名称 read(write),表名称2 read(write)，其他;
查看表锁情况
show open tables;
删除表锁
unlock tables;
InnoDB引擎的锁机制
共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁
排他锁（X)：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁
意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的IS锁
意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的IX锁
说明：
共享锁和排他锁都是行锁，意向锁都是表锁，应用中我们只会使用到共享锁和排他锁，意向锁是mysql内部使用的，不需要用户干预
对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁（X)；对于普通SELECT语句，InnoDB不会加任何锁，事务可以通过以下语句显示给记录集加共享锁或排他锁
共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
排他锁（X)：SELECT * FROM table_name WHERE ... FOR UPDATE
InnoDB行锁是通过给索引上的索引项加锁来实现的，因此InnoDB这种行锁实现特点意味着：只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁
InnoDB的行级锁定同样分为两种类型，共享锁和排他锁，而在锁定机制的实现过程中为了让行级锁定和表级锁定共存，InnoDB也同样使用了意向锁（表级锁定）的概念，也就有了意向共享锁和意向排他锁这两种

文章图片

Innodb的锁定是通过在指向数据记录的第一个索引键之前和最后一个索引键之后的空域空间上标记锁定信息而实现的。Innodb的这种锁定实现方式被称为“NEXT-KEYlocking”（间隙锁），因为Query执行过程中通过过范围查找的话，会锁定整个范围内所有的索引键值，即使这个键值并不存在
Innodb所使用的行级锁定争用状态查看：show status like 'innodb_row_lock%'

文章图片

Innodb_row_lock_current_waits：当前正在等待锁定的数量
Innodb_row_lock_time：从系统启动到现在锁定总时间长度
Innodb_row_lock_time_avg：每次等待所花平均时间
Innodb_row_lock_time_max：从系统启动到现在等待最常的一次所花的时间
Innodb_row_lock_waits：系统启动后到现在总共等待的次数
对于这5个状态变量，比较重要的主要是：
Innodb_row_lock_time_avg（等待平均时长）
Innodb_row_lock_waits（等待总次数）
Innodb_row_lock_time（等待总时长）这三项。
尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手指定优化计划
集群搭建之主从复制
主从复制原理

文章图片

binlog介绍和relay日志
查看binlog日志：mysqlbinlog 文件名称
主从复制实践
关闭主从机器的防火墙策略
主服务器配置
修改my.conf文件
在[mysqld]段下添加：
#启用二进制日志
log-bin=mysql-bin
#服务器唯一ID，一般取IP最后一段
server-id=129
重启mysql服务
service mysqld restart
建立帐户并授权slave（登录到MySQL）
mysql>GRANT FILE ON *.* TO 'root'@'%' IDENTIFIED BY 'root';
mysql>GRANT REPLICATION SLAVE, REPLICATION CLIENT ON *.* to 'root'@'%' identified by 'root';

mysql>GRANT REPLICATION SLAVE ON *.* to 'root'@'%' identified by 'root';

#一般不用root帐号，“%”表示所有客户端都可能连，只要帐号，密码正确，此处可用具体客户端IP代替，如192.168.145.226，加强安全
刷新权限
mysql> FLUSH PRIVILEGES;
查看mysql现在有哪些用户
mysql>select user,host from mysql.user;
查询master的状态
mysql> show master status;

文章图片

从服务器配置
修改my.conf文件
[mysqld]
server-id=133
删除UUID文件
错误处理：
如果出现此错误：
Fatal error: The slave I/O thread stops because master and slave have equal MySQL server UUIDs; these UUIDs must be different for replication to work.
因为是mysql是克隆的系统所以mysql的uuid是一样的，所以需要修改
解决方法：
删除/var/lib/mysql/auto.cnf文件，重新启动服务
重启并登录到MySQL进行配置从服务器
mysql>change master to master_host='192.168.148.129',master_port=3306,master_user='root',master_password='root',master_log_file='mysql-bin.000002',master_log_pos=602
注意语句中间不要断开，master_port为mysql服务器端口号(无引号)，master_user为执行同步操作的数据库账户，“602”无单引号(此处的602就是show master status 中看到的position的值，这里的mysql-bin.000002就是file对应的值)
启动从服务器复制功能
mysql>start slave;
检查从服务器复制功能状态
mysql> show slave status
……………………(省略部分)
Slave_IO_Running: Yes //此状态必须YES
Slave_SQL_Running: Yes //此状态必须YES
……………………(省略部分)
注：Slave_IO及Slave_SQL进程必须正常运行，即YES状态，否则都是错误的状态(如：其中一个NO均属错误)
以上操作过程，从服务器配置完成
集群搭建之读写分离
读写分离的理解
HAC：High Availability Cluster
MySQL的主从复制，只会保证主机对外提供服务，而从机是不对外提供服务的，只是在后台为主机进行备份

文章图片

MySQL-Proxy下载
MySQL-Proxy安装
准备一台机器作为MySQL Proxy
上传压缩包
解压缩
tar -zxvf mysql-proxy-0.8.5-linux-el6-x86-32bit.tar.gz -C
创建mysql-proxy.cnf文件
[mysql-proxy]
user=root
admin-username=root
admin-password=root
proxy-address=192.168.148.128:4040
proxy-backend-addresses=192.168.148.129:3306
proxy-read-only-backend-addresses=192.168.148.133:3306
proxy-lua-script=/root/architect/mysql-proxy-0.8.5-linux-el6-x86-32bit/share/doc/mysql-proxy/rw-splitting.lua
log-file=/root/architect/mysql-proxy-0.8.5-linux-el6-x86-32bit/logs/mysql-proxy.log
log-level=debug
keepalive=true
daemon=true
修改mysql-proxy.cnf文件的权限
chmod
修改rw-splitting.lua脚本

文章图片

MySQL-Proxy启动域测试
启动命令
./mysql-proxy--defaults-file=mysql-proxy.cnf配置文件的地址
注意事项：如果没有配置profile文件的环境变量，则需要去拥有mysql-proxy命令的目录通过./mysql-proxy进行启动
在其他客户端，通过mysql命令去连接MySQL Proxy机器
mysql -uroot -proot -h192.168.148.128 -P4040
分库分表之MyCat实现
分库分表介绍
前提：当数据库表数据特别大时，比如说上亿的记录，数据库本身的优化基本上无法解决性能问题了，根本原因是数据库本身的io读写到了一个瓶颈，这个时候，需要海量数据的存储和查询问题。此时需要利用分库分表的思想去进行数据库的架构优化
随着微服务这种架构的兴起，应用从一个完整的大的应用，切分为很多可以独立提供服务的小应用，每个应用都有独立的数据库

文章图片

垂直切分：按照业务模块进行切分，将不同模块的表切分到不同的数据库中
水平切分：将一张大表按照一定的切分规则，按照行切分到不同的表或者不同的库中
MyCAT介绍
官方网站：http://www.mycat.org.cn/
简单的说，MyCAT就是：
一个彻底开源的，面向企业应用开发的“大数据库集群”
支持事务、ACID、可以替代Mysql的加强版数据库
一个可以视为“Mysql”集群的企业级数据库，用来替代昂贵的Oracle集群
一个融合内存缓存技术、Nosql技术、HDFS大数据的新型SQL Server
结合传统数据库和新型分布式数据仓库的新一代企业级数据库产品
一个新颖的数据库中间件产品
MyCAT的目标是：低成本的将现有的单机数据库和应用平滑迁移到“云”端，解决数据存储和业务规模迅速增长情况下的数据瓶颈问题
MyCAT的关键特性
支持 SQL 92标准
支持Mysql集群，可以作为Proxy使用
支持JDBC连接ORACLE、DB2、SQL Server，将其模拟为MySQLServer使用
支持galera for mysql集群，percona-cluster或者mariadb cluster，提供高可用性数据分片集群
自动故障切换，高可用性
支持读写分离，支持Mysql双主多从，以及一主多从的模式
支持全局表，数据自动分片到多个节点，用于高效表关联查询
支持独有的基于E-R 关系的分片策略，实现了高效的表关联查询
多平台支持，部署和实施简单
MyCAT架构

文章图片

文章图片

核心概念
Schema：由它指定逻辑数据库
Table：逻辑表
DataNode：真正存储节点
DataHost：真正的数据库主机
如图所示：MyCAT使用Mysql的通讯协议模拟成了一个Mysql服务器，并建立了完整的Schema（数据库）、Table （数据表）、User(用户)的逻辑模型，并将这套逻辑模型映射到后端的存储节点DataNode（MySQL Instance）上的真实物理库中，这样一来，所有能使用Mysql的客户端以及编程语言都能将MyCAT当成是Mysql Server来使用，不必开发新的客户端协议
Mycat存在的问题
跨库join问题
通过业务分析，将不同库的join查询拆分成多个select
建立全局表（每个库都有一个相同的表）
冗余字段（不符合数据库三范式）
E-R分片（将有关系的记录都存储到一个库中）
最多支持跨两张表跨库的join
分布式事务（弱事务）
强一致性事务（同步）
最终一致性事务（异步思想）
分布式主键
redis incr命令
数据库（生成主键）
UUID
snowflake算法
Mycat解决的问题
海量数据存储
查询优化

文章图片

Mycat对多数据库的支持

文章图片

分片策略
MyCAT支持水平分片与垂直分片：
水平分片：一个表格的数据分割到多个节点上，按照行分隔
垂直分片：一个数据库中多个表格A，B，C，A存储到节点1上，B存储到节点2上，C存储到节点3上

文章图片

MyCAT通过定义表的分片规则来实现分片，每个表格可以捆绑一个分片规则，每个分片规则指定一个分片字段并绑定一个函数，来实现动态分片算法
Schema：逻辑库，与MySQL中的Database（数据库）对应，一个逻辑库中定义了所包括的Table
Table：表，即物理数据库中存储的某一张表，与传统数据库不同，这里的表格需要声明其所存储的逻辑数据节点DataNode。在此可以指定表的分片规则
DataNode：MyCAT的逻辑数据节点，是存放table的具体物理节点，也称之为分片节点，通过DataSource来关联到后端某个具体数据库上
DataSource：定义某个物理库的访问地址，用于捆绑到Datanode上
Mycat的下载及安装
下载mycat
github地址：https://github.com/MyCATApache
Mycat安装
安装rz上传文件工具
yum install -y lrzsz
把MyCat的压缩包上传到linux服务器
解压缩，得到mycat目录
tar -zxvf Mycat-server-1.6-RELEASE-20161028204710-linux.tar.gz
进入mycat/bin，启动MyCat
启动命令：./mycat start
停止命令：./mycat stop
重启命令：./mycat restart
注意：可以使用mysql的客户端直接连接mycat服务。默认服务端口为8066
Mycat分片
需求
把商品表分片存储到三个数据节点上
MyCat安装到节点192.168.148.133上
配置schema.xml
Schema.xml作为MyCat中重要的配置文件之一，管理着MyCat的逻辑库、表、分片规则、DataNode以及DataSource
schema 标签用于定义MyCat实例中的逻辑库
Table 标签定义了MyCat中的逻辑表
dataNode 标签定义了MyCat中的数据节点，也就是我们通常说所的数据分片。
dataHost标签在mycat逻辑库中也是作为最底层的标签存在，直接定义了具体的数据库实例、读写分离配置和心跳语句
注意：若是LINUX版本的MYSQL，则需要设置为Mysql大小写不敏感，否则可能会发生表找不到的问题。在MySQL的配置文件中my.ini [mysqld] 中增加一行lower_case_table_names = 1
Schema.xml配置

writeType="0" dbType="mysql" dbDriver="native" switchType="1"slaveThreshold="100">
select user()

password="root">

writeType="0" dbType="mysql" dbDriver="native" switchType="1"slaveThreshold="100">
select user()

password="root">

配置server.xml
server.xml几乎保存了所有mycat需要的系统配置信息。最常用的是在此配置用户名、密码及权限

root
TESTDB
true

配置rule.xml
rule.xml里面就定义了对表进行拆分所涉及到的规则定义。可以灵活的对表使用不同的分片算法，或者对表使用相同的算法但具体的参数不同。这个文件里面主要有tableRule和function这两个标签。在具体使用过程中可以按照需求添加tableRule和function。
此配置文件可以不用修改，使用默认即可
分片测试
创建数据库和表
由于配置的分片规则为“auto-sharding-long”，所以mycat会根据此规则自动分片。
每个datanode中保存一定数量的数据。根据id进行分片
经测试id范围为：
Datanode1：1~5000000
Datanode2：5000000~10000000
Datanode3：10000001~15000000
当15000000以上的id插入时报错：
[Err] 1064 - can't find any valid datanode :TB_ITEM -> ID -> 15000001
此时需要添加节点了
Mycat读写分离
MyCat的读写分离是建立在MySQL主从复制基础之上实现的
数据库读写分离对于大型系统或者访问量很高的互联网应用来说，是必不可少的一个重要功能。对于MySQL来说，标准的读写分离是主从模式，一个写节点Master后面跟着多个读节点，读节点的数量取决于系统的压力，通常是1-3个读节点的配置

文章图片

Mycat读写分离和自动切换机制，需要mysql的主从复制机制配合
Mycat配置
Mycat 1.4 支持MySQL主从复制状态绑定的读写分离机制，让读更加安全可靠，配置如下：
"dn1" dataHost="localhost1" database="db1" />
"dn2" dataHost="localhost1" database="db2" />
"dn3" dataHost="localhost1" database="db3" />
"localhost1" maxCon="1000" minCon="10" balance="1"
writeType="0" dbType="mysql" dbDriver="native" switchType="2"slaveThreshold="100">
show slave status
"hostM" url="192.168.148.129:3306" user="root"
password="root">
"hostS" url="192.168.148.133:3306" user="root"
password="root" />

（1）设置 balance="1"与writeType="0"
Balance参数设置：
balance=“0”, 所有读操作都发送到当前可用的writeHost上
balance=“1”，所有读操作都随机的发送到readHost
balance=“2”，所有读操作都随机的在writeHost、readhost上分发
WriteType参数设置：
writeType=“0”, 所有写操作都发送到可用的writeHost上
writeType=“1”，所有写操作都随机的发送到readHost
writeType=“2”，所有写操作都随机的在writeHost、readhost分上发
readHost是从属于writeHost的，即意味着它从那个writeHost获取同步数据，因此，当它所属的writeHost宕机了，则它也不会再参与到读写分离中来，即“不工作了”，这是因为此时，它的数据已经“不可靠”了。基于这个考虑，目前mycat 1.3和1.4版本中，若想支持MySQL一主一从的标准配置，并且在主节点宕机的情况下，从节点还能读取数据，则需要在Mycat里配置为两个writeHost并设置banlance=1。”
（2）设置 switchType="2" 与slaveThreshold="100"
switchType 目前有三种选择：
-1：表示不自动切换
1 ：默认值，自动切换
2 ：基于MySQL主从同步的状态决定是否切换
Mycat心跳检查语句配置为 show slave status ，dataHost 上定义两个新属性： switchType="2" 与slaveThreshold="100"，此时意味着开启MySQL主从复制状态绑定的读写分离与切换机制。Mycat心跳机制通过检测 show slave status 中的 "Seconds_Behind_Master", "Slave_IO_Running", "Slave_SQL_Running" 三个字段来确定当前主从同步的状态以及Seconds_Behind_Master主从复制时延。“

【数据库|MySQL学习】