《数据库超全笔记》玩转数据库

2022-11-21 19:14:32 浏览数 (2)

一:什么是数据库 数据库是一个长期存储在计算机内，有组织的，有共享的，统一化管理数据集合。 它简便而言之就是一个数据存储仓库，为了方便数据存储和管理，它将数据按照特定的规律存储在磁盘上。通过数据库管理系统，可以有效的组织和管理存储在数据中的数据。

二:数据库的现状 数据库类型: 1.层次数据库 2.网格状数据库 3.关系型数据库 4.NOSQL（非关系型数据库）

三:数据库系统 (1)数据库:用于存储数据的地方。 (2)数据库管理系统:为了提高数据库系统的处理能力所使用的管理数据库的软件 (3)数据库应用程序:数据库管理系统的软件补充。 数据库（database）提供了一个存储空间用来存储数据，可以把数据库看成是以存放数据的容器。一个数据库肯定能包含许多文件，一个数据库系统中包含多个数据库 数据库管理系统（database management system.dbms）:是用户创建，管理和维护数据库时使用的软件，位于用户与操作系统之间，对数据库进行统一的管理，dbms能定义数据存储结构，提供数据的操作机制，维护数据库的安全性，完整性和可靠性。 数据库应用程序：（database application）：虽然已经有数据库管理系统，但是在很多情况下，dbms无法满足对数据管理要求，数据库的应用程序算是dbms的一个软件补充，它能够满足对数据管理的更高要求，还可以使数据库管理过程更加直观，数据库应用呈现负责与dbms进行通信，访问和管理dbms中存储和数据，用户插入，修改，删除DB中的数据

mysql 1.什么是mysql mysql是一个开源的数据管理系统（DBMS），他是由mysql AB公司开发的。 mysql是一个跨平台开源关系数据库管理系统，广泛的应用在小型的互联网公司的生产和开发环境中。 mysql是一个小型的关系型数据库，与其他的大型数据库管理系统如：oracle，db2，sql server相比，mysql的规模更小，功能有限，但是体积小，速度快，成本低，并且它的功能对一些小企业或者复杂一点应用来说已经足够用了，所以mysql就为目前最受欢迎的开源数据库。

2.mysql的版本 针对不同的用户mysql分为Community server(社区版)，enterprise server(企业版) mysql的命名机制由3个数字和1个后缀组成，例如:mysql-5.5.13. 第一个5.是主版本号，描述了文件格式，所有版本5的发型版都有相同的文件格式。 第二个5.是发型级别，主版本号和发型别组合在一起便构成了发行序列号 第三个数字13.是发型序列的版本号

3.mysql的优势: (1)速度:运行速度快； (2)价格:开源免费 (3)容易使用:它的配置和管理非常简单，易于学习。 (4)可移植性强:能够在众多不同的系统平台运行。比如:widows，linux，ios等等。 (5)丰富的接口:提供了c c ，Java，Perl，PHP (6)支持查询语言:MySQL可以使用sql语法支持ODBC(开放式数据库链接)的应用程序 (7)安全性和连续性:十分灵活和安全的权限和密码系统，允许基于主机的验证，连接到服务器时，所有密码传输均采用加密的方式，从而保证安全性。并且由于mysql是网络化的，可以在因特网上的任何地方访问，提高了数据的共享效率。

4.mysql的服务架构: client/server–C/S架构（客户端/服务器）

5.mysql的表:database数据库（最大的数据存储单位） table表（数据存储的基本单在关系型数据库中，数据库的表是一系列二维数组的集合，用来存储数据和操作数据的逻辑的逻辑结构。 它是由纵向的列和横向的行组成，行被称为记录，是组织数据的单位；列被称为字段，每一列表示记录的一个属性，都有相应的描述信息，如数据类型，数据宽度等。 6.数据类型: 数据类型决定了数据子计算机中的存储格式，代表不同信息类型，通常的数据类型有:整数数据类型 浮点数数据类型，二进制数据类型，字符串数据类型，日期/时间数据类型等。

7.sql语言: 对数据库进行查询和修改操作的语言。SQL的含义(structured query language)结构化查询语言 他有3个主要的标准:ANSI（美国国家标准机构）SQL，ANSI对SQL在1992进行了修改，SQL-92或者SQL2。近年又进行了修改，SQL-99。 SQL语言的4个部分: 1.数据定义语言data definition language(DDL):drop(删除)，create(创建)，alter()等语句。 2.数据的操作语言data manipulation language(DML):insert,update(修改)delete(删除)语句。 3.数据查询语言data query language(DQL):select(查找)语句。 4.数据控制语言data control language(DCL):grant，revoke，commit，rollba等语句

8.MySQL的命令行工具:

9.语句： 1.show databases:用于查看当前mysql服务器中包含的库，

2.show tables:用于查看当前所在的库中包含的表。需要先使用use语句切换到所使用的库use mysql mysql数据库文件存放在/usr/local/mysql/data 目录下，每个数据库对应一个子目录，用于存储数据表文件，每个数据表对应为三个文件，扩展名分 别为".frm",".myd",".myi"

3.create database语句：用于创建一个新的库，需要指定数据库名称作为参数(auth数据库名称) 例：（create database auth）

4.create table语句：用于在当前库中创建新的表，需要指定数据表名称作为参数(zhy表名称)例：（create database zhy）

5.drop table语句：用于删除数据库中的表，需要指定’库名 表名’作为参数，若只指定表明参数，则需要通过执行use语句切换到目标库，执行一下操作 可以删除users表 例：（drop table auth.users）

6.drop database语句：用于删除指定的库，需要指定库名作为参数 例：（drop database auth）

7.Insert into语句：用于向表中插入新的数据记录 例：（insert into 表名）执行以下操作将会向auth库中的users表插入一条记录

8.Select语句：用于从指定的表中查找符合条件的数据记录

9.Describe:用于显示表结构，则需先通过use语句切换到目标库（use mysql切换到mysql数据库）查看mysql库中的user表结构（describe mysql.user） 或者（describe user）这俩语句相同

10.Update语句:用于修改，更新表中的数据记录。

11.Delete:用于删除表中指定的数据记录。例如:Delete * from 表名 where 条件表达式

12.insert into语句：用于向表中插入数据记录，例:insert into 表名(字段1，字段2，…) values(字段1的值，字段2的值，…)

注意！ 【在执行Update Delete语句时，通常都带where条件，不带条件的Update语句和Delete语句会修改或删除所有记录，是非常危险的行为】

12.Crant语句:专门设置数据库用户的访问权限，当指定的用户不存在时，Crant语句将会创建新的用户，否则Crant语句用户信息。

13.show global varlables like’�tadir%’;查看mysql数据存储路径

14.执行MySQL操作语句 验证成功以后将会进入提示符“mysql>”的数据操作环境，用户可以输入各种操作语句对数据库进行管理，每条MySQL操作语句以分号“；”表示结束，输入时不区分大小写。

第二章 数据库的基本操作

一.mysql的4个默认自带的4个库: 1.information_schema:保存了关于数据的信息。例如:数据库名，数据库中的表名。 2.mysql:记录数据库的用户，密码，权限，关键字等，还有mysql自己需要使用的控制和管理信息。 3.performance_schema:5.5版本之后新增的一个库，用于收集服务器的性能参数。该库中所有的表的一个引擎均为performance_schema。 4.tsst:测试库，所有的用户在该数据库中都拥有root权限(一般不会存储有用的数据)。

二.1.创建数据库:create database databasename; databasename是指数据库名称 2.移动到指定的数据库里:use databasename; 3.删除数据库:drop database databasename;

其它用法

1、使用SHOW语句找出在服务器上当前存在什么数据库：

mysql> SHOW DATABASES;

2、创建一个数据库MYSQLDATA

mysql> CREATE DATABASE MYSQLDATA;

3、选择你所创建的数据库

mysql> USE MYSQLDATA; (按回车键出现Database changed 时说明操作成功！)

4、查看现在的数据库中存在什么表

mysql> SHOW TABLES;

5、创建一个数据库表

mysql> CREATE TABLE MYTABLE (name VARCHAR(20), sex CHAR(1));

6、显示表的结构：

mysql> DESCRIBE MYTABLE;

7、往表中加入记录

mysql> insert into MYTABLE values (”hyq”,”M”);

8、用文本方式将数据装入数据库表中（例如D:/mysql.txt）

mysql> LOAD DATA LOCAL INFILE “D:/mysql.txt” INTO TABLE MYTABLE;

9、导入.sql文件命令（例如D:/mysql.sql）

mysql>use database;

mysql>source d:/mysql.sql;

三，数据库的存储引擎: 1.什么是存储引擎:数据库的存储引擎是数据库的底层软件组件，数据库管理系统(Dbms)就是依赖存储引擎来对数据表进行创建，查询，更新和删除操作的。不同的存储引擎提供了不同的存储机制，索引技巧和锁定水平等功能。还可以获得某些特定的功能。现在不同的数据库的管理系统都支持多种不同的存储引擎。mysql的核心就是存储引擎。

2.MySQL的存储引擎，包括处理事务安全表的引擎和处理非事物安全表的引擎。在MySQL中不需要所有的表都使用同一种引擎，针对具体的需求每一张表都可以选择不同的存储引擎。 MySQL5.5支持的存储引擎有:InnoDB，MyiSAM，Memory，CVS等。 查看mysql中所有的存储引擎的命令:show enginesg

1.myisam存储引擎的特点: (1)myisam引擎读取速度快，占用资源少，不支持事务，不支持外键约束，但支持全文索引 （2）读写相互阻塞，也就是说读数据的时候就不能写数据，写数据的时候就不能读数据; (3)myisam引擎只能缓存索引，而不能缓存数据; (4)mysql5.5之前的默认引擎。

使用场景： （1）适用于读数据比较多的业务，不适用于读写频繁的业务； （2）并发相对较低的业务(纯读或者纯写的高并发也可以)，数据修改相对较少的业务； （3）硬件资源比较差的机器可以考虑多使用myisam引擎。

2.InnoDB存储引擎的特点： （1）事物类数据表的首选引擎，支持事物安全表，支持行级别锁定和外键，mysql5.5之后的默认引擎； （2）具有提交，回滚和崩溃恢复能力的事物安全存储引擎，能处理巨大的数据量，性能及效率高，完全支持外键完整约束条件； （3）具有非常高的效的缓存特性，能缓存索引也能缓存数据，对硬件要求高， （4）使用InnoDB时，将在mysql数据目录创建一个名为ibdata的10M带大小的自动扩展文件，以及两个名为ib_logfile0和ib_logfile1的5M带大小的日志文件。

使用场景: （1）需要事物支持的业务，高并发的业务； （2）数据更新较为频繁的场景，比如:BBS(美国论坛网站)，SNS,微博等； （3）数据一致性要求较高的业务，比如充值转账，银行卡转账等。k

memory存储引擎的特点: （1）memory存储引擎将表中的数据存储内存中，为查询和引用其他表数据提供快速访问； （2）memory存储引擎执行HASH和BETREE索引，不支持BLOB和TEXT列，支持AUTO_INCREMENT列，和对可包括NULL值的列的索引； （3）当不在需要memory表的内容时，要释放memory表占用的内存，可以执行delete from或者truncate table，或者删除整个表。

CSV:将数据保存为CSV格式文件，可以导入到其它数据库中；

ARCHIVE:归档，将数据zlib进行压缩，被当做仓库使用，一般对他只进行insert和select操作，他适合存储日志。

mrg_myisam：相当于将多个myisam的合并版，将多个myisam表合并为一个。

MySQL 修改root密码的四种方法 1 用set password命令 首先登陆MySQL 格式 MySQL > set password for 用户名@localhost=password（‘新密码’） 例子 MySQL > set password for root@localhost=password(‘123’); 2 在命令行用 mysqladmin 格式 mysqladmin -u 用户名 -p 旧密码 password 新密码 列子mysqladmin -uroot -p123456 password 123

方法3 用update直接编辑user表 首先登陆MySQL MySQL>use MySQL； MySQL> update user set password=password（‘123’)where user=‘root’ and host=‘localhost’; mysql>flush privileges; 方法4 ：忘记root密码的时候 vim /etc/my.cnf 在[mysqld]下面添加一行skip-grant-tables 然后重启，在登陆MySQL（此时密码为空）使用命令 update mysql.user set password=password(‘密码’)where user=root host=‘localhost’； 回到配置文件，删除刚刚添加的那行，在重启服务就可以用新密码登陆了。

第三章:数据表的基本操作

我们要进行表的操作，我们首选需要进入一个库里面。 1.创建表的命令格式: create table <表名> （ –>字段1 数据类型 [完整性约束条件] –>字段2 数据类型 [完整性约束条件] … ）； 2.删除表：drop table表名； 3.如何在表中插入数据:insert into表名 [（插入数据的字段）] values（字段一的值，字段二的值，字段n的值)； 4.擦汗寻表数据:select */字段 from 表明；

完整性约束条件: 1.什么是完整性约束条件: 完整性约束条件时用来对字段进行限制，要求用户只能向该字段中写入符合条件的数据，如果不满足条件数据将会不执行该操作。 2.常用的完整性约束条件: primary key 主键 标识该表的主键，可以唯一的标识数据。（特点: 非空，唯一） FOREIGN KEY 外键 标识该字段为该表的外键，是与之联系的某表的主键； NOT NULL 非空 标识该字段的值不能为空； UNIQUE 唯一 标识该字段的值是唯一的； AUTO_INCREMENT 标识该字段的值自动增加，一般该字段的数据类型是int； DEFAULT 默认值 为该字段设置一个默认值，在不插入值的时候使用默认值。 给字段添加主键的方法: 1.单字段主键: 语法: 创建表的时候添加主键 1.字段名 数据类型 PRIMARY KEY, 2.在定义完所有的字段之后添加主键 PRIMARY KEY(字段名)； 多字段的联合主键: RIMARY KEY（字段1，字段2，字段3…）

使用修改表的命令也可以添加主键: LTER TABLE 数据表名 ADD PRIMARY KEY（字段名）

使用外键:外键用来在两个表数据之间建立关系，它可以是一系列或多系列 创建外键的语法: 在创建表的时候添加在所有字段后面 [CONSTRAINT<外键名>]FOREIGN KEY(字段名1，字段2…)REFERENCES 父表(主键1，主键2…)

用修改表的语法来添加一个外键: ALTER TABLE 子表名 ADD CONSTRAINT 外键名 FOREIGN KEY（子表外键列）REFERENCES父表名（父表主键列）； 子表外键列的数据类型必须和父表的主键列的数据类型一致，否则无法创建

删除外键: ALTER TABLE 表名 DROP FOREIGN KEY(外键)

NO NULL 创建表的时候: 字段 数据类型 NOT NULL

UNIQUE 字段 数据类型 UNIQUE

[CONSTRAINT(约束名称)]UNIQUE(字段名)

DEFAULT 默认值 字段名 数据类型 DEFAULT(默认值)

AUTO_INCREMENT 自增 字段名 数据类型 AUTO_INCREMENT

所有的约束条件都是可选的，每一个字段可以设置多个约束条件。

查看数据表结构语法;desc 表名； 2.show create table 表明；

二，修改数据表: 1.给表改名： alter table<旧表名>rename[to] <新表名>； 使用这条命令还可以将数据表移动到其他库中 alter table<旧表名> rename to <库名>.<表名>

2.修改字段的数据类型 alter table<表名>modify<字段名><新的数据类型>；

3.修改字段名: alter table<表名>change<旧的字段> <新字段> <新数据类型>；

4.添加字段: alter table<表名> add <新字段> <数据类型> [完整性约束条件] [first/after已经存在的字段]（first移动到第一）（after移动到其他字段后面）

5.修改字段的所在位置: alter table <表名> modify <新字段名> <数据类型> [完整性约束条件] after <旧字段>;

6。删除字段的语法: alter table<表名>drop <字段名称>

7.更改表的引擎: alter table <表名> engine=引擎名称；

8.删除主键: alter table <表名> drop promary key;

9.删除外键: alter table <表名> drop foreign key <外键名>；

10.删除外键: ALTER TABLE 表名 DROP FOREIGN KEY(外键)

如果表b的外键关联表a的主键 ，那么我们无法删除表a，必须删除表b的外键或者是直接删除表b，才可以删除表a； NOT NULL 非空 UNIQUE唯一 PRIMARY KEY主键 FOREIGN KEY外键 t_name老师名字 t_birthday性别 t_prof出生年月日 t_depart职称 t_id编号

数据类型； (1)数据表由多列字段构成，每个字段指定不同的数据类型，指定数据类型后，也就决定向字段插入数据的内容； (2)不同的数据类型也决定了MySQL在存储他们的时候使用方式，以及在使用他们的时候选择什么运算符号进行运算； （3）数据类型分为:数值类数据类型，日期/时间数据类型，字符串数据类型。 数值数据类型: （1）整数 （2）浮点数 （3）定点数； 日期/时间数据类型:DATE ,TIME,DATETIME,TIMESTAMP等等； 字符串数据类型:文本字符串，二进制字符串。

一，数值类数据类型：

浮点数和定点数： （1）他们都是用来表示小数的，浮点数有两种类型：单精度浮点数（FLOAT）和双精度浮点数（DOUBLE），定点数只有DECIMAL (2)浮点数和定点数都可以用（M,D）来表示，其中M是精度，表示总共的位数（不算点号），D是标数，表示小数的位数； （3）DECIMAL 实际是以字符串的形式存放的，在对精度要求比较高的时候（比如货币，科学数据等使用DECIMAL类型比较好；

（4）浮点数相对于定点数的优点是在长度一定的情况下，浮点数能够表示更大的数据范围，它的缺点是精度没有定点数高。

类型名称 日期格式 日期范围 存储需求 YEAR YYYY 1901 ~ 2155 1字节 TIME HH:MM:SS -838:59:59 ~ 838:59:59 3字节 DATE YYYY-MM-DD 1000-01-01 ~ 9999-12-31 3字节 DATETIME YYYY-MM-DD HH:MM:SS 1000-01-01 00:00:00 ~ 9999-12-31 23:59:59 8字节 TIMESTAMP YYYY-MM-DD HH:MM:SS 1970-01-01 00:00:01 UTC ~ 2038-01-19 03:14:07 UTC 4字节

2、TIME (1) TIME 类型的格式为 HH:MM:SS ，HH 表示小时，MM 表示分钟，SS 表示秒 (2) 格式：以 ‘HHMMSS’ 格式表示的 TIME ，例如 ‘101112’ 被理解为 10:11:12 ，但如果插入不合法的时间，如 ‘109712’ ，则被存储为 00:00:00 (3) 格式：以 ‘D HH:MM:SS’ 字符串格式表示的 TIME ，其中 D 表示日，可以取 0 ~ 34 之间的值，在插入数据库的时候 D 会被转换成小时，如 ‘2 10:10’ 在数据库中表示为 58:10:10 ，即 2x24 10 = 58

3、DATE (1) DATE 类型的格式为 YYYY-MM-DD ，其中，YYYY 表示年，MM 表示月，DD 表示日 (2) 格式：‘YYYY-MM-DD’ 或 ‘YYYYMMDD’ ，取值范围为 ‘1000-01-01’ ~ ‘9999-12-31’ (3) 格式：‘YY-MM-DD’ 或 ‘YYMMDD’ ，这里 YY 表示两位的年值，范围为 ‘00’ ~ ‘99’ ，其中，‘00’ ~ ‘69’ 被转换为 2000 ~ 2069 ，‘70’ ~ ‘99’ 被转换为 1970 ~ 1999

(4) 格式：YY-MM-DD 或 YYMMDD ，数字格式表示的日期，其中 YY 范围为 00 ~ 99 ，其中，00 ~ 69 被转换为 2000 ~ 2069 ，70 ~ 99 被转换为 1970 ~ 1999

查询： 查询的基础语法： SELECT{*|字段} [FROM表1，表2，… ] [WHERE条件判断] [GROUP BY 字段] [having expr] [order by 字段] [limit…(偏移量，行数)] ORDER BY 字段

我们知道从MySQL表中使用SQL SELECT 语句来读取数据。 1.如需有条件的从表中选取数据，可以将WHERE子句添加到SELECT语句中。 2.查询语句中你可以使用一个或多个表，表之间用逗号隔开，并使用WHERE语句来设定查询条件； 3.你可以用WHERE语句来指定任何条件 4.你可以用AND或者OR指定多个条件； 5.WHRE子句也可以用在DELECT或者UPDATE命令里面。 WHERE中常用到的： （1）=，<>, !=, <, >, <=, >=, （2）BETWEEN AND 介于两者之间用来限制一个范围 （3）LIKE 通配符查询（%匹配任意个数字符，_匹配任意单个字符） （4）REGEXP正则表达式查询（^表示以什么开头，$表示以什么结尾，[…]表示匹配方括号内的任意字符串 | 表示匹配管道符前后字符串） （5）IN判断是否在IN列表之中； （6）AND满足AND前后所有条件，OR满足OR前后任意添加； （7）在WHERE子句中我们binary这个参数可以区分大小写，默认不区分； （8）ORDER BY 字段 ASC：（升序排列）默认就是升序排列； （9）ORDER BY 字段 DESC（降序排列）； 可以同时给多个字段进行排序，排序依然依照第一个字段进行排序，在第一个字段排序过后的基础上，在给其他进行排序，后面的排序不影响第一个字段的顺序；

分组查询：GROUP BY GROUP BY 是对select查询出来结果按照某个字段或者表达式进行分组，获取一组组的集合，然后从每组中取出一个指定的字段或者表达式的值。 having：用于where和group by 查询出来的分组进行过滤，查出满足条件的分组结果，他是一个过滤声明，是在查询返回结果集以后对查询结果进行过滤操作。 [GROUP BY 字段] [HAVING 条件表达式] 分组经常和聚合函数一起使用，MAX(), MIN(), COUNT(), AVG(), SUM() SUM() 求和 MAX() 求最大值 MIN() 求最小值 AVG() 求平均数 COUNT() 表示统计数量 （行数）可以用*表示所有记录的行数，字段来显示特定字段的行数； GROUP_CONCAT（字段）将分组过后的数据显示完整。

LIMIT限制查询结果数量： LIMIT[位置偏移量]， 行数 默认偏移量从0开始，行数限制了显示结果输出多少行；列：从3行到7行，就是2，5 列：select * from zhy LIMIT 2，5 连接查询：UNION [all | distinct] 用来连接两个查询语句，将两个查询的结果集，合并为一个结果集； 结果集：不加参数all的时候默认去掉重复的结果，相同的值只显示一次，加上参数all，显示所有查询内容，不会去掉重复的值。

联合查询： 主要进行多表查询的时候使用。 INNER JOIN 内连接 连接两个表，输出符合条件的数据； 语法：表1 [INNER] JOIN 表2 ON 限定条件 如果连接的是同一个表，叫做自连接。 LEFT 表1 [OUTER] JOIN 表2 ON 限定条件 “左连接”它会显示左表）表1）的全部内容，和右表（表2） 限定条件的内容；

RIGHT 表1 [OUTER] JOIN 表2 ON 限定条件 “右连接” 它显示右表 的全部内容和左表的限定条件的内容；

子查询：指一个查询语句嵌套在另一个查询语句的内部，，子查询在版本4.1版本开始上线。 子查询在执行查询的过程中会出现一个零时表，查询完成后会自动消失。 ALL 需要符合所有条件才能输出； ANY 只需要符合其中一个条件就可以输出 EXITS　会将子查询中的所有数据进行判断是否存在，如果存在，外层查询语句进行查询，如果不存在，则停止查询，返回NULL。 NOT　EXITS　对子查询的数据进行判断是否不存在，如果不存在，执行外部查询，存在则返回NULL

索引： 　索引用于快速找出在某个列中有一特定值的行，不使用索引，MySQL必须从第一条记录开始读完整个表，指导找出相关的行，表越大，查询数据所花费的时间就越多，如果表中查询的列有一个索引，MySQL能够快速到达一个位置去搜索数据文件，而不必查看所有数据，那么将会节省很大一部分时间。 　其中MySQL中的索引的存储类型有两种：BTREE，HASH。 在MySQL中最常用的存储引擎就是InnoDB和ＭｙISAM这两个存储引擎主要支出的索引的存储方就是BTREE，MEMORY引擎支持 HASH HASH　主要以KEY－value的方式进行存储数据，多个ｋｅｙ可以指向一个value，但是同一个ｋｅｙ不可以指向多个值。

索引的分类： １.唯一索引和普通索引 ２.单列索引和组合索引 ３.全文索引fulltext ４.空间索引spatial 单列索引指给单行的数据添加索引，组合索引指，将多个字段添加为一个索引； 唯一索引用来限制我们的数据的唯一性UNIQUE　INDEXT，可以包含NULL，PRIMARY　KEY 是一个特殊的唯一索引，唯一，非空； 全文索引：可以用于全文搜索，只有MｙISAM存储引擎支持，并且只能为CHAR，VARCHAR，TEXT列添加； 空间索引：只有MYISAM引擎支持，添加空间索引的字段必须非空； 空间类型支持的数据类型：GEOMETRY，POINT，LINESTRING，POLYGON

组合索引默认的索引名是我们定义的最左侧的第一个字段的名称，组合索引遵循最左侧原则，就是说

索引的添加方式： １.在创建表时添加： 　CREATE　TABLE 表名 （字段名 数据类型） （UNIQUE 唯一索引 | fulltext 全文索引 | spatial 空间索引）［INDEX｜KEY］［索引名（len）］ ２.修改表时添加索引： 　ALTER TABLE　表名　ADD　UNIQUE |FULLTEXT|SPATLAL [INDEX|KEY]　[索引名] （索引字段）

3.CREATE UNIQUE | FULLTEXT | SPATIAL [INDEX|KEY] [索引名] ON 表名 [索引字段] ；

查看创建的索引： show create table 表名G 删除索引： ALTER TABLE DROP INDEX 索引名； DROP INDEX 索引名 ON 表名；

创建索引注意： （1）创建索引并非越多越好； （2）数据量小的表没有必要创建索引 （3）避免对经常更新的数据创建索引 （4）在条件表达式中经常使用的字段可以创建索引 （5）当唯一性是某种数据本身的特征时，我们创建唯一索引 （6）在频繁进行排序或者分组的列上创建索引，如果排序的列有多个，可以创建全文索引

索引作用： 1.加快查询速度 2.创建唯一索引来保证表中数据的唯一性 3.实现数据的完整性，加速表和表之间的连接 4.介绍分组和排序时间

EXPLAIN 查询语句 用来测试我们的索引有没有生效或者有没有在工作；

EXPLAIN列的解释 table 显示这一行的数据是关于哪张表的 type 这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、indexhe和ALL possible_keys 显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句 key 实际使用的索引。如果为NULL，则没有使用索引。 key_len 使用的索引的长度。在不损失精确性的情况下，长度越短越好 ref 显示索引的哪一列被使用了，如果可能的话，是一个常数 rows MYSQL认为必须检查的用来返回请求数据的行数 Extra 关于MYSQL如何解析查询的额外信息。

视图优点

• 简单 – 使用视图的用户完全不需要关心视图中的数据是通过什么查询得到的。 – 视图中的数据对用户来说已经是过滤好的符合条件的结果集。 • 安全 – 用户只能看到视图中的数据。 • 数据独立 – 一旦视图的结构确定了,可以屏蔽表结构变化对用户的影响。

视图：视图是一张虚拟表; 他和我们真实的表的区别：视图它里面的数据是通过查询语句从一张或者多张真实的表里面获取到的； 我们可以通过insert，update, delete来操作视图（和操作表是一样的）。当通过视图修改数据时，对应的原表数据也会被修改，同样，我们修改表数据时，也会自动反映到视图中。

视图他会真实的保存到我们的磁盘里，随时都能够查询里面的数据； 视图对应的真实表，我们叫做基表；

创建视图： CREATE　[algorithm=undefind|merge|temptable]　VIEW　视图名　[视图的字段名] AS SELEST [*|字段] FROM 表名|视图名 WHERE… 例：create view zhy as select * from;

algorithm 用来定义视图的算法； undefined 未定义 默认使用merge merge 替换； temptable 零时表；

with check option 默认cascaded with cascaded check option 更新视图的时候，满足视图以及相关的基表或视图的限制条件； with local check option 更新视图的时候，只需要满足本身的条件即可；

查看视图结构：DESC 视图名称； SHOW FILEDS FROM 视图名称；

查看视图的创建详情：SHOW CREATE　VIEW　视图名称；

查看视图的基本信息： SHOW　TABLE　STATUS　LIKE　‘视图名’G 在MySQL中，information_schema数据库下的views表中存储了所有视图的定义，通过对views表的查询，可以查看数据库中所有视图的详细信息；

修改视图： CREATE OR REPLACE VIEW 视图名 AS　SELECT… ‘用来修改视图’ ALTER VIEW 视图名 AS　SELECT…

更新视图内容：update UPDATE 视图名 set 字段名=‘值’ WHERE …

试图存在以下情况时，更新操作无法执行： 1.视图中不包含基表中被定义为非空的列； 2.在定义视图的select语句的字段列表中使用了数学表达式或者聚合函数，不接受更新操作； 3.select中，使用了union、group by、having无法接受更新。

删除视图： DROP VIEW [IF EXISTS] 视图名1，视图名2，…

加上IF EXISTS　可以使假如没有要删除的这个视图存在，也不会报错，但是会返回１条warning；

视图和真实表的区别； （１）视图是已经编译好的SQL语句，是基于SQL语句的结果的可视化表，而表不是； （２）视图没有实际的记录，而表有； （３）表是内容，试图可以看成是窗口； （４）表和视图虽然都占用物理空间，但是视图只是逻辑概念存在，而表可以及时对数据进行修改，但是视图只能用创建语言来修改； （５）视图是查看数据表的一种方法，可以查询数据表中的某些字段构成的数据，只是一些SQL语句的集合，从安全角度来说，视图可以防止用户接触数据表，因而不知道表结构； （６）表属于全局模式的表，是实表，而视图属于局部模式的表，是虚表； （７）视图的建立和删除只影响视图本身，而不影响对应的基本表。

视图和基本表的联系： 　视图是在基本表之上建立的表，他的结构和内容都来自基本表，它依赖基本表存在而存在。 一个视图可以对应一个基本表，也可以对应多个基本表，视图是基本表的抽象和逻辑意义上建立的关系。

事务： 什么叫事务：多条SQL语句，要么全部成功，要么全部失败，MySQL的事务是在存储引擎层实现 事务主要用于处理操作量大，复杂度高的数据，比如说，在人员管理系统中，你要删除一个人员，你既要删除人员的基本资料，也要删除该人员的相关信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！

事务的四大特性：ACID A.原子性（atomicity）：一个事务必须视为完整不可分割的单位，其中的操作要么都做，要么都不做；如果事务中的一个SQL语句执行失败，则已执行的语句必须回滚，数据库退回到事务开始前的状态。 C：一致性（consistency）:是指事物执行结束后，数据库的完整性约束没有被破坏，事物执行的前后都是合法的数据状态，数据库的完整性约束包括不限于：实体完整性（如字段的类型，大小，长度要符合要求），外键约束，用户自定义完整性（如转账前后，两个账户余额的和应该不变）。事物支持的操作主要有：INSERT，UPDATE，DELETE等； D：持久性（durability）:一旦事物提交，所有修改的数据将永久保存到数据库中，即使系统崩溃也不会改变或丢失； I：隔离性:与原子性，持久性侧重于研究事物本身不同，隔离性研究的是不同事物之间的相互影响，隔离性是指，事物内部的操作与其他事物是隔离的，并发执行的各个事物之间不能相互干扰，严格的隔离性，对应了事物隔离级别中的serializable（可串行化），但实际应用中出于性能方面的考虑很少会使用可串行化。

事物操作命令: 开启事务：begin，start transaction 事务提交：commit 事务回滚：rollback

我们用begin或者start transaction 开启的事务叫做显式事务。

查看自动提交模式是否开启：SHOW VARIABLES LIKE’AUTOCOMMIT‘ 关闭自动提交模式：SET AUTOCOMMIT=0 开启自动提交模式：SET AUTOCOMMIT=1

事务的4种隔离级别： 1.read uncommitted（未提交读)； 2.read committed（已提交读）； 3.repreatable read（可重复读）； 4.serealizable（可串行化）； 这4种隔离级别是由低到高的，隔离级别越低，并发能力越强，性能越好，但是安全性越差； 隔离级别也高，并发越差，但是安全性好；

1.未提交读：事务中修改没有提交对其他事务也是可见的，俗称脏读； 2.以提交读：许多数据库默认为此级别呢（MySQL不是）。已提交读一个事务只有在提交了之后，另一个事务才对他可见；

以提交读可以解决脏读的问题，但是会产生不可重复读的问题；

3.可重复读：能够解决脏读和不可重复读的问题；

4.可串行读：是最高隔离级别，强制事务串行执行，执行串行也就解决问题了，这个级别只有在对数据一致要求非常严格并且没有并发的情况下使用。 当我们在事务a里面查询id<10的时候，我们在事务b里执行insert语句被阻塞执行了，原因是事务a执行了查询stud同时满足id<10，以被锁定，如果查询表stud同时满足id<3，则新增的语句执行成功

触发器（trigger）：是一个特殊的存储过程，都是嵌入到MySQL的一段程序，触发器是由事件来触发某个操作，由于MySQL只支持行级别的触发器功能，所以这些事件只能是SQL语言DML语句，也就是inster、delete、update这三种，如果定义了触发程序，当数据库执行这些语句的时候就会激发触发器执行相应的操作，触发器可以查询其他的表，而且可以包含复杂的SQL语句，不需要手动启动，只要一个预定义事件发生时，就会被MySQL自动调用。

简单来理解：触发器就是你执行一条DML SQL语句，这条SQL执行会自动触发执行其他的SQL语句。

sql ->触发 ->sqln, 一条触发一个或多个sql。

触发器的4个要素： （1）监视点（table） （2）监视事件（insert、update、delete） （3）触发时机（before/after） （4）触发事件（sql语句）

NEW和OLD详解： MySQL中定义了new和old，用来表示触发器的所在表中，触发了触发器的那一行数据，来引用触发器中发生变化的记录内容，具体的： （1）在insert型触发器中，NEW用来表示将要（before）或已经（after）插入或修改后的新数据，old表示没有插入或修改之前的数据； （2）在uptate型触发器中，new表示将要（before）或已经（after）修改后的新数据，old表示没有修改之前的数据； （3）在delete型触发器中，old表示已经被删除或将要被删除的数据； 使用方法：NEW.字段名 OLD.字段名

创建语法： CREATE TRIGGER 触发器名 before(之前)或after(之后) 触发事件（insert、delete、update） ON 表名 for each row begin 要执行的sql语句 end

DELIMITER // 这个命令是将我们的sql语句结束符从’;‘改为’//’

触发单条sql语句： mysql> CREATE TABLE goods(num INT); Query OK, 0 rows affected (0.01 sec)

mysql> CREATE TABLE sale(snum INT); Query OK, 0 rows affected (0.06 sec)

mysql> INSERT INTO goods VALUES(100); Query OK, 1 row affected (0.01 sec)

mysql> INSERT INTO sale VALUES(0); Query OK, 1 row affected (0.01 sec)

mysql> DELIMITER // mysql> CREATE TRIGGER gs1 AFTER UPDATE ON goods FOR EACH ROW BEGIN UPDATE sale SET snum=OLD.num-NEW.num; END// Query OK, 0 rows affected (0.07 sec)

mysql> DELIMITER ; mysql> UPDATE goods SET num=num-20; Query OK, 1 row affected (0.01 sec) Rows matched: 1 Changed: 1 Warnings: 0

mysql> SELECT * FROM goods; ±----- | num | ±----- | 80 | ±----- 1 row in set (0.00 sec)

mysql> SELECT * FROM sale; ±----- | snum | ±----- | 20 | ±----- 1 row in set (0.00 sec)

触发多条sql语句：DELIMITER

mysql> CREATE TRIGGER testref BEFORE INSERT ON test1 FOR EACH ROW BEGIN INSERT INTO test2 SET a2=NEW.a1; DELETE FROM test3 WHERE a3=NEW.a1; UPDATE test4 SET a4=a4 1 WHERE a4=NEW.a1; END

Query OK, 0 rows affected (0.01 sec)

查看触发器： show triggers；或者show triggersG 由于触发器的信息统一存储在information_schema.triggers 这个表里，所以我们查看这个表就能看到所有的触发器信息了； SELECT * from information_schema.triggers WHERE TRIGGER_NAME=‘tsetref’G

删除触发器：DROP TRIGGERS 触发器名：

MySQL用户与权限: 1.是否允许用户连接MySQL的server端；usre host password 2.用户是否拥有对于MySQL库表的操作权限；privileges

这些权限都记录在mysql.user这个表里

1.用户列： user表里用户列包含Host、User、password，分别表示主机名、用户名、密码。其中user和host为user表里的联合主键。当用户与服务器之间建立连接时，输入的账户信息中的用户名称，主机名，和密码必须匹配user表里对应的字段值，才能够建立连接，我们修改用户实际上就是修改user表里password列对应的值；

2.权限列： 权限列的字段决定了用户的权限，描述了在全局范围内允许用户对 数据进行的操作，包括 查询权限，修改权限等普通权限，还包括了关闭服务器，超级权限和加载用户等高级权限，普通的权限用来操作数据库，高级权限用来管理数据库；user表中对应的权限是针对所有用户数据库的。 这些字段值的类型为ENUM，可以取的值只有Y和N,Y表示该用户没有对应的权限，修改权限，可以使用GRANT语句或者UPDATE语句更改user表的这些字段对应的值，来修改对应的权限。

3.安全列 ： 安全列只有6个字段，其中两个是SSL相关的，2个是x509相关的，另外2个事授权插件相关的。SSI用于加密，x509标准可用于标识用户，Plugin字段标识可以用于验证用户身份的插件，如果该字段为空，服务器使用内建授权验证机制验证用户 身份。可以通过SHOW　VARIABLES LIKE　'have_openssl’语句来查询服务器是否支持SSL功能；

4.资源控制列 ： 资源控制列的字段用来限制用户使用的资源，包含4个字段，分别为： （1）Max_questions用户每小时运行执行的查询操作次数； （2）Nax_updates用户每小时允许执行更新操作的次数； （3）Max_connections用户每小时允许执行的连接操作次数； （4）Max_user_connections用户允许同时建立的连接次数；

db表和host表 这两个表是MySQL数据中非常重要的权限表，db表中存储了用户对某个数据库的操作权限，决定用户能从哪个主机存取哪个数据库，host表中存储某个主机对数据库的操作权限，配合db权限表对给主机上数据库级操作权限做更细致的控制，这个权限表不守GRANT和REVOKE语句的影响，db表比较常用，host表一般很少使用，db表和host表结构相似，字段大致可以分为两类：用户列和权限列。

新建普通用户： 1.使用CREATE　USER语句来创建新用户： CREATE　USER　user@host [IDENTIFIED BY PASSWORD’password’] IDENTIFIED BY 表示用来设置用户密码；[password]:表示使用哈希值设置密码；‘password’:用于登录的明文密码；

2.使用GRANT语句创建用户： GRANT’privileges’ ON db.table TO user@host [IDENTIFIED BY’password’] [with grant option]

FLUSH PRIVILEGES：这个命令 privileges 表示赋予用户的权限类型；db.table：表示用户的权限锁作用的数据库中的表； with grant option 可选项，表示对新建立的用户赋予grant权限；

3.直接操作mysql.user: mysql> INSERT INTO mysql.user(Host,User,Password) VALUES(‘localhost’,‘wangwu’,‘123.com’); Query OK, 1 row affected, 3 warnings (0.00 sec)

mysql> SHOW warnings; ±--------±-----±-------------------------------------------------- | Level | Code | Message | ±--------±-----±-------------------------------------------------- | Warning | 1364 | Field ‘ssl_cipher’ doesn’t have a default value | | Warning | 1364 | Field ‘x509_issuer’ doesn’t have a default value | | Warning | 1364 | Field ‘x509_subject’ doesn’t have a default value | ±--------±-----±-------------------------------------------------- 3 rows in set (0.00 sec)

mysql> SELECT * FROM user WHERE user=‘wangwu’G *************************** 1. row *************************** Host: localhost User: wangwu Password: 123.com Select_priv: N Insert_priv: N Update_priv: N Delete_priv: N Create_priv: N

通过上面三种方法创建的新用户还不能直接登录到MySQL，我们需要刷新权限才可以登录：FLUSH PRIVLEGES；

INSERT 这种方法创建用户，我们最好把密码提前进行加密，然后把加密过的密码插入到user表里， mysql> SELECT password(‘123.com’); ±------------------------------------------ | password(‘123.com’) | ±------------------------------------------ | *AC241830FFDDC8943AB31CBD47D758E79F7953EA | ±------------------------------------------ 1 row in set (0.02 sec)

删除普通用户： 1.使用DROP USER user@host；删除； mysql> DROP USER ‘zhangsan’@‘localhost’; Query OK, 0 rows affected (0.00 sec) 2.使用DELETE语句删除用户： DELETE FROM mysql.user WHERE host=‘localhost’ and user=‘zhangsan’;

root用户修改自己的密码： 1.使用mysqladmin命令来修改密码: mysqladmin -u root -h localhost -p password “newpassword” Enter password: //这里输入root的旧密码

2.修改mysql库的user表： mysql> UPDATE mysql.user SET password=password(‘123.com’) WHERE user=‘root’ and host=‘localhost’; Query OK, 1 row affected (0.00 sec) Rows matched: 1 Changed: 1 Warnings: 0

mysql> flush privileges;（刷新权限） Query OK, 0 rows affected (0.00 sec)

3.使用SET语句来修改root用户的密码（修改当前登录用户的密码）： SET PASSWORD=PASSWORD（‘newpassword’）;

4.那当然你set语句还可以修改其他普通用户的密码： SET PASSWORD FOR user@host=password（‘newpassword’）; mysql> SET PASSWORD FOR ‘zhangsan’@‘localhost’=password(‘123.com’); Query OK, 0 rows affected (0.00 sec)

修改普通用户的密码我们还可以用grant授权的方式： mysql>grant usage on . to ‘zhangsan’@'localhost’identified by ‘321.com’; Query OK rows affected (0.00 sec)

mysql>flush privileges;

root密码忘记了怎么办？ 使用–skip-grant-tables选项启动mysql然后在设置密码： [root@localhost ~]# /etc/init.d/mysqld restart --skip-grant-tables Shutting down MySQL. SUCCESS! Starting MySQL… SUCCESS! [root@localhost ~]# mysql -uroot -p Enter password: //这里直接回车进入mysql mysql> UPDATE mysql.user SET password=password(‘1234.com’) WHERE user=‘root’ and host=‘localhost’; Query OK, 0 rows affected (0.00 sec) Rows matched: 1 Changed: 0 Warnings: 0

mysql> flush privileges; Query OK, 0 rows affected (0.00 sec)

[root@localhost ~]# systemctl restart mysqld

权限管理： 权限管理主要是对登录到mysql的用户进行权限验证，所有用户的权限都储存在mysql的权限表里，不合理的权限规划给mysql服务器带来安全隐患。mysql权限系统的主要功能是证实连接到一台给主机的用户，并赋予该用户在数据库中SELECT /INSERT/UPDATE和DELETE等权限，账户权限信息被存储在MySQL数据库 的user、db、host、tables_priv、columns_priv等权限表里。在MySQL启动时，服务器将这些库表中的权限信息读入内存中。

1.CREATE和DROP权限，可以创建新数据和表，或删除已有的数据库和表，如果将mysql数据库中的DROP权限授予某个用户，这个用户就可以删除它有访问权限的数据库和表。 2.SELECT、INSERT、UPDATE和DELETE权限允许在一个数据库现有的表里实施操作。 3.SELECT权限只有在他们真正从一个表中检索行时才被用到； 4.INDEX权限允许创建或删除索引，INDEX使用已有表，如果具有表的CREATE权限，可以在CREATE TABLE语句中包括索引定义。 ５.ALTER 权限，可以使用ALTER TABLE来更改表的结构和重新命名表。 ６.GRANET权限，允许授权给其他用户，可用于数据库、表和保存的程序。 7.file权限给与用户使用LOAD DATA INFILE和SELECT…INTO OUTFILE语句读或者写服务器上的文件，任何被授予file权限的用户都能读或写mysql服务器上的任何文件。（说明用户可以读任何数据目录下的文件，因为服务器可以访问这些文件）。file权限允许用户在MySQL服务器具有写权限目录下创建文件，但不能覆盖已有文件。

授权： 授权就是为某个用户授予授权，合理的授权可以保证数据库的安全，MySQL中可以使用grant语句为用户授权。 1.全局层级 全局权限适用于一个给定服务中的所有数据库。这些权限存储在MySQL表中，CRANT　ALL　　ON　＊.＊和REVOKE ALL ON *.*指收于和撤销全局权限。 2.数据库层级 数据库权限适用于一个给定数据库中的所有目标，这些权限存储在mysql.db和mysql.host表中。 GRANT ALL ON db_name 和REVOKE　ALL db_name; 只授予和撤销数据库权限。

3.表层级： 表权限适用于一个给定表中的所有列，这些权限存储在mysql.tables_priv表中。CRANT ALL ON table_name;和REVOKE ALL ON table_name;只针对表授予或撤销权限。

4.列层级： 列权限适用于一个给定表中的某单一的列，这些权限保存在mysql.column_priv表中。当使用REVOKE时，必须指定与被授权相同的列。

在mysq中，必须拥有GRANT权限的用户才可以执行grant语句，要使用grant或revoke，必须有grant option权限。

GRANT语法： CRANT priv_type1, priv_type2, priv_type3,…ON dbname.tablename,dbname.tablename…TO user@host;

REVOKE(收回权限)语法： REVOKE priv_type1, priv_type2, priv_type3,…ON dbname.tablename,dbname.tablename…TO user@host;

查看权限： SHOW GRANTS语句可以显示指定用户的权限信息； SHOW GRANTS FOR ‘user’@‘host’;

用SELECT语句查看user表中的各个权限字段以确定用户的权限信息 SELECT privileges_llist FRO mysql.user WHERE user

mysql的日志： mysql日志记录了mysql数据库日常操作和错误信息。mysql有不同类型的日志文件（各自存储了不同类型的日志），从日志当中可以查询到mysql数据库的运行情况、用户的操作、错误的信息等。

MySQL日志分为4大类： 1.错误日志：记录mysql服务的启动，运行或停止mysql服务时出现问题; 2.一般查询日志：记录建立连接客户端的执行的sql语句（耗费时间在<=long_query_tme设定的时间内）； 3.慢查询日志：记录所有执行的sql语句的时间超过long_query_time的所有查询； 4.二进制日志：记录所有更改数据的语句，可以用于数据复制和恢复。

默认情况下，所有日志创建于mysql数据目录中，通过刷新日志，可以强制mysql关闭和重新打开日志文件，flush logs刷新日志或执行mysqladmin flush-logs 如果正在使用mysql复制功能，在复制服务器上可以维护更多日志文件，这种日志我们称为接替日志。启动日志文件可能会降低mysql数据库的性能（官方表示，降低的性能不大于1%）， 二进制日志： 主要记录mysql数据的变化，二进制日志以一种有效的格式并且是事务安全的方式包含更新日志中可用的信息。二进制日志包含了所有更新了的数据。二级制日志还包含关于每个更新数据的语句的执行时间，它不包含没有修改任何数据的语句（select、show等）。 使用二进制日志的主要目的是最大可能的恢复数据。

启动二进制日志，默认情况下二进制日志关闭的（开启日志会降低服务器性能），编辑匹配文件my.cnf来开启二进制日志:

格式： [mysqld] log-bin=/date/logs/mysql-bin expire_logs_days=10 Max_binlog_size=100M

log-bin [=path/[filename]] //二进制日志[路径]指定日志文件的名字 Expire_logs_days=10 Max_binlog_size=100M 默认为1GB binlog_format=mixed

重启mysql，重启mysql也会产生二进制日志，flush logs 也会出现二进制日志。

二进制日志默认和mysql数据目录下（/var/mysql），我们也可以自定义。

查看二进制日志是否开启：SHOW VARIABLES LIKE’log_%’G

【查看二进制日志】 MySQL二进制日志存储了所有变更信息，mysql二进制日志经常使用。。当mysql创建二进制日志文件时，首先创建一个以‘filename’（默认bin-log）为名称，以‘index’为后缀的索引文件（这个文件是用来记录二进制日志的文件名的）；在创建一个以‘filename’为名称，以‘000001’为后缀日志文件。当mysql服务重启一次，以‘000001’为后缀的文件会增加一个，并且后缀加1递增，如果日志长度超过max_binlog_size的上限，也会创建一个新的日志。

show binary/master logs；可以查看当前的二进制日志文件个数及其文件名。 想要查看二进制内容，需要通过mysqlbinlog工具。

【删除二进制日志】 mysql的二进制文件可以匹配自动删除，也可以手动删除： 1.RESET MASTER；用来删除所有二进制日志文件 2.PPURGE MASTER/BINARY LOGS TO ‘二进制日志文件名’；用来删除单个日志文件。 mysql> SHOW BINARY LOGS; ±-----------------±---------- | Log_name | File_size | ±-----------------±---------- | mysql-bin.000001 | 27130 | | mysql-bin.000002 | 1031892 | | mysql-bin.000003 | 1857 | | mysql-bin.000004 | 478 | | mysql-bin.000005 | 1398 | | mysql-bin.000006 | 479 | | mysql-bin.000007 | 126 | | mysql-bin.000008 | 462 | | mysql-bin.000009 | 126 | | mysql-bin.000010 | 516 | ±-----------------±---------- 10 rows in set (0.00 sec)

mysql> PURGE MASTER LOGS TO ‘mysql-bin.000002’; Query OK, 0 rows affected (0.00 sec)

mysql> SHOW BINARY LOGS; ±-----------------±---------- | Log_name | File_size | ±-----------------±---------- | mysql-bin.000002 | 1031892 | | mysql-bin.000003 | 1857 | | mysql-bin.000004 | 478 | | mysql-bin.000005 | 1398 | | mysql-bin.000006 | 479 | | mysql-bin.000007 | 126 | | mysql-bin.000008 | 462 | | mysql-bin.000009 | 126 | | mysql-bin.000010 | 516 | ±-----------------±---------- 9 rows in set (0.00 sec)

3.PERGE BINARY/MASTER LOGS BEFORE ‘date（日期名字）’

mysql> PURGE MASTER LOGS BEFORE ‘20200227’; Query OK, 0 rows affected (0.01 sec)

mysql> SHOW BINARY LOGS; ±-----------------±---------- | Log_name | File_size | ±-----------------±---------- | mysql-bin.000010 | 516 | ±-----------------±---------- 1 row in set (0.00 sec)

【还原】 如果mysql服务器启用了二进制日志，使用二进制日志还原数据库，使用最后一次备份还原，或指定一个时间恢复数据。 MYSQLBINLOG [option] 日志文件名 mysql -u usre -p ‘password’

[option]里面的选项–start-date 开始时间 --stop-positon=结束的位置； [root@localhost bin]# ./mysqlbinlog --start-datetime=‘2020-02-26 23:23:56’ /usr/local/mysql/data/mysql-bin.000010 -uroot -p1234.com

【暂时停止二进制日志的功能】 如果mysql的配置文件已经启动了二进制日志，mysql会一直记录二进制日志，修改配置文件，可以停止二进制日志，但是需要从起mysql。mysql提供了暂时停止二进制日志的功能，通过SET SQL_LOG_BIN语句可以是mysql暂时停止二进制 mysql>SET SQL_LOG_BIN=0； 暂停二进制

错误日志： 错误日志文件包含了当mysql启动和停止时，以及服务器在运行过程中发生任何严重错误时的相关信息。

设置错误日志： 在默认的情况下错误日志是开启的，它默认被记录到数据目录下/var/mysql/data/。如果，没有在配置文件中指定文件名，文件默认为hostname.err(localhost.localdomain.err)。执行flushlogs;错误日志文件会重新加载。在my.cnf中添加 log-error=路径/文件名 来自定义错误日志文件。

【查看错误日志的存储路径】 通过错误日志可以见识系统的运行状态，便于即使发现故障、修复故障。mysql错误日志是以文本文件的形式存储的，可以使用文本编辑器直接查看；

删除错误日志： mysql的错误日志是以文本文件的形式存储的，可以直接删除。 在运行状态下删除错误日志文件后，则会自动创建。

【刷新所有日志】

一般查询日志： 它记录了所有用户的操作，宝库启动和关闭服务、执行查询和更新语句等。mysql默认没有开启 一般查询日志。如果需要可以修改my.cnf来开启。在my.cnf里添加log = /路径/文件名。一般查询 日志是以文本格式文件存储的，日知名.Pid。 可以直接删除。开启一般查询并且mysql服务在 运行状态下，删除了日志文件，它会自动创建新的。

慢查询日志： 慢查询日志是记录查询时长超过指定时间的日志。慢查询日志主要用来优化查询语句的。慢查询 日志默认是关闭的，我们可以修改配置文件来开启： [mysqld] log-slow-queries //开启慢查询 long_query_time = 1 //设置慢查询时间，默认10秒

查看慢查询日志 MySQL慢查询日志是以文本文件的形式存储的，可以直接使用文本编辑器查看。 删除慢查询日志可以直接删除。删除后不再重启服务器的情况，需要执行 mysqladmin -u root -p flush-logs 重新生成日志文件，或者在客户端登陆到服务器执行 flush logs语句重建日志文件。

MySQL主从复制概念MySQL主从复制是指数据可以从一个MySQL数据库服务器主节点复制到一个或多个从节点。MySQL默认采用异步复制方式，这样从节点不用一直访问主服务器来更新自己的数据，数据的更新可以在远程连接

MySQL主从复制主要用途读写分离 在开发工作中，有时候会遇见某个sql语句需要锁表，导致暂时不能使用读的服务，这样就会影响现有业务，使用主从复制，让主库负责写，从库负责读，这样即使主库出现了

MySQL主从形式：一主一从、一主多从、多主一从、双主复制、 一主一从和一主多从

杜兴宇 16:04:50 MySQL 主从复制概念MySQL 主从复制是指数据可以从一个MySQL数据库服务器主节点复制到一个或多个从节点。MySQL 默认采用异步复制方式，这样从节点不用一直访问主服务器来更新自己的数据，数据的更新可以在远程连接上进行，从节点可以复制主数据库中的所有数据库或者特定的数据库，或者特定的表。 MySQL 主从复制主要用途l 读写分离 在开发工作中，有时候会遇见某个sql 语句需要锁表，导致暂时不能使用读的服务，这样就会影响现有业务，使用主从复制，让主库负责写，从库负责读，这样，即使主库出现了锁表的情景，通过读从库也可以保证业务的正常运作。 l 数据实时备份，当系统中某个节点发生故障时，可以方便的故障切换

Mysql在3.25.15版本开启复制功能，mysql复制是将一个服务器（master）中的数据复制到其他服务器（slave）的过程。Mysql将存放DDL和DML的二进制日志发送到其他服务器上，然后从服务器（slave）将得到的二进制日志进行执行，从而保证主从服务器上的数据保持同步。 l 高可用HA 读写分离，分担服务器的压力； l 架构扩展 并发量增大，我们可以通过增加主从服务器的数量来扩展并发。 随着系统中业务访问量的增大，如果是单机部署数据库，就会导致I/O访问频率过高。有了主从复制，增加多个数据存储节点，将负载分布在多个从节点上，降低单机磁盘I/O访问的频率，提高单个机器的I/O性能。 MySQL 主从形式：一主一从、一主多从、多主一从、双主复制、级联复制 一主多从，提高系统的读性能

一主一从和一主多从是最常见的主从架构，实施起来简单并且有效，不仅可以实现HA，而且还能读写分离，进而提升集群的并发能力。

多主一从 （从5.7开始支持）

双主复制 双主复制，也就是互做主从复制，每个master既是master，又是另外一台服务器的slave。这样任何一方所做的变更，都会通过复制应用到另外一方的数据库中。 级联复制

级联复制模式下，部分slave的数据同步不连接主节点，而是连接从节点。因为如果主节点有太多的从节点，就会损耗一部分性能用于replication，那么我们可以让3~5个从节点连接主节点，其它从节点作为二级或者三级与从节点连接，这样不仅可以缓解主节点的压力，并且对数据一致性没有负面影响。

MySQL 主从复制原理MySQL主从复制涉及到三个线程，一个运行在主节点（log dump thread），其余两个(I/O thread, SQL thread)运行在从节点，如下图所示:

主节点 binary log dump 线程 当从节点连接主节点时，主节点会创建一个log dump 线程，用于发送bin-log的内容。在读取bin-log中的操作时，此线程会对主节点上的bin-log加锁，当读取完成，甚至在发送给从节点之前，锁会被释放。 l 从节点I/O线程 当从节点上执行start slave命令之后，从节点会创建一个I/O线程用来连接主节点，请求主库中更新的bin-log。I/O线程接收到主节点binlog dump 进程发来的更新之后，保存在本地relay-log中。 l 从节点SQL线程 SQL线程负责读取relay log中的内容，解析成具体的操作并执行，最终保证主从数据的一致性。

杜兴宇 16:05:24 对于每一个主从连接，都需要三个进程来完成。当主节点有多个从节点时，主节点会为每一个当前连接的从节点建一个binary log dump 进程，而每个从节点都有自己的I/O进程，SQL进程。从节点用两个线程将从主库拉取更新和执行分成独立的任务，这样在执行同步数据任务的时候，不会降低读操作的性能。比如，如果从节点没有运行，此时I/O进程可以很快从主节点获取更新，尽管SQL进程还没有执行。如果在SQL进程执行之前从节点服务停止，至少I/O进程已经从主节点拉取到了最新的变更并且保存在本地relay日志中，当服务再次起来之后，就可以完成数据的同步。 要实施复制，首先必须打开Master 端的binary log（bin-log）功能，否则无法实现。 因为整个复制过程实际上就是Slave 从Master 端获取该日志然后再在自己身上完全顺序的执行日志中所记录的各种操作。如下图所示：

复制的基本过程如下： 从节点上的I/O 进程连接主节点，并请求从指定日志文件的指定位置（或者从最开始的日志）之后的日志内容；主节点接收到来自从节点的I/O请求后，通过负责复制的I/O进程根据请求信息读取指定日志指定位置之后的日志信息，返回给从节点。返回信息中除了日志所包含的信息之外，还包括本次返回的信息的bin-log file 的以及bin-log position；从节点的I/O进程接收到内容后，将接收到的日志内容更新到本机的relay log中，并将读取到的binary log文件名和位置保存到master-info 文件中，以便在下一次读取的时候能够清楚的告诉Master“我需要从某个bin-log 的哪个位置开始往后的日志内容，请发给我”；Slave 的 SQL线程检测到relay-log 中新增加了内容后，会将relay-log的内容解析成在主节点上实际执行过的操作，并在本数据库中执行。 MySQL 主从复制模式MySQL 主从复制默认是异步的模式。MySQL增删改操作会全部记录在binary log中，当slave节点连接master时，会主动从master处获取最新的bin log文件。并把bin log中的sql relay。 l 异步模式（mysql async-mode） 异步模式如下图所示，这种模式下，主节点不会主动push bin log到从节点，这样有可能导致failover的情况下，也许从节点没有即时地将最新的bin log同步到本地。

l 半同步模式(mysql semi-sync) 这种模式下主节点只需要接收到其中一台从节点的返回信息，就会commit；否则需要等待直到超时时间然后切换成异步模式再提交；这样做的目的可以使主从数据库的数据延迟缩小，可以提高数据安全性，确保了事务提交后，binlog至少传输到了一个从节点上，不能保证所有从节点将此事务更新到db中。性能上会有一定的降低，响应时间会变长。如下图所示：

半同步模式不是mysql内置的，从mysql 5.5开始集成，需要master 和slave 安装插件开启半同步模式。 l 全同步模式 全同步模式是指主节点和从节点全部执行了commit并确认才会向客户端返回成功。

杜兴宇 16:05:43 binlog记录格式MySQL 主从复制有三种方式：基于SQL语句的复制（statement-based replication，SBR），基于行的复制（row-based replication，RBR)，混合模式复制（mixed-based replication,MBR)。对应的binlog文件的格式也有三种：STATEMENT,ROW,MIXED。 l Statement-base Replication (SBR)就是记录sql语句在bin log中，Mysql 5.1.4 及之前的版本都是使用的这种复制格式。优点是只需要记录会修改数据的sql语句到binlog中，减少了binlog日质量，节约I/O，提高性能。缺点是在某些情况下，会导致主从节点中数据不一致（比如sleep(),now()等）。 l Row-based Relication(RBR)是mysql master将SQL语句分解为基于Row更改的语句并记录在bin log中，也就是只记录哪条数据被修改了，修改成什么样。优点是不会出现某些特定情况下的存储过程、或者函数、或者trigger的调用或者触发无法被正确复制的问题。缺点是会产生大量的日志，尤其是修改table的时候会让日志暴增,同时增加bin log同步时间。也不能通过bin log解析获取执行过的sql语句，只能看到发生的data变更。 l Mixed-format Replication(MBR)，MySQL NDB cluster 7.3 和7.4 使用的MBR。是以上两种模式的混合，对于一般的复制使用STATEMENT模式保存到binlog，对于STATEMENT模式无法复制的操作则使用ROW模式来保存，MySQL会根据执行的SQL语句选择日志保存方式。 GTID复制模式@ 在传统的复制里面，当发生故障，需要主从切换，需要找到binlog和pos点，然后将主节点指向新的主节点，相对来说比较麻烦，也容易出错。在MySQL 5.6里面，不用再找binlog和pos点，我们只需要知道主节点的ip，端口，以及账号密码就行，因为复制是自动的，MySQL会通过内部机制GTID自动找点同步。 @ 多线程复制（基于库），在MySQL 5.6以前的版本，slave的复制是单线程的。一个事件一个事件的读取应用。而master是并发写入的，所以延时是避免不了的。唯一有效的方法是把多个库放在多台slave，这样又有点浪费服务器。在MySQL 5.6里面，我们可以把多个表放在多个库，这样就可以使用多线程复制。 基于GTID复制实现的工作原理主节点更新数据时，会在事务前产生GTID，一起记录到binlog日志中。从节点的I/O线程将变更的bin log，写入到本地的relay log中。SQL线程从relay log中获取GTID，然后对比本地binlog是否有记录（所以MySQL从节点必须要开启binary log）。如果有记录，说明该GTID的事务已经执行，从节点会忽略。如果没有记录，从节点就会从relay log中执行该GTID的事务，并记录到bin log。在解析过程中会判断是否有主键，如果没有就用二级索引，如果有就用全部扫描。 总结Mysql 主从复制是mysql 高可用，高性能的基础，有了这个基础，mysql 的部署会变得简单、灵活并且具有多样性，从而可以根据不同的业务场景做出灵活的调整。

MySQL的备份： 1.备份的重要性： 在生产环境中，为了防止硬件故障、软件故障、自然灾害、误操作等各种原因导致的数据库数据丢失后能恢复到事故之前的状态，我们需要对数据库进行备份和恢复操作。数据库的备份和恢复是非常重要的工作，数据的备份不是最终目的，数据的恢复才是； 2.备份时应该注意的事项： 1.最多能容忍多少数据丢失；决定了备份间隔时间、备份的方式； 2.恢复数据需要在多长时间之内完成；备份方式、备份的工具； 3.需要恢复哪些数据；决定了备份的内容； 4.定期测试备份的可用性并提高恢复操作的效率； 5.备份时的服务器负载； 6.锁定资源的时长；

备份的类型 a.按照备份的数据集的范围分类： 完全备份：整个数据集都进行备份； 部分备份：数据集的一部分，比如部分表；

b.按照数据的变化分类： 全量备份：将整个mysql的所有库表进行备份； 增量备份：仅备份自上一次完全备份或增量备份以来变量的那部分数据； 差异备份：仅备份自上一次完成全量备份以来变量的那部分数据；

c.按照操作对象分类： 物理备份：直接从磁盘复制数据文件进行备份； 逻辑备份：从数据库导出数据另存在一个或多个文本中，将数据转化为具体的sql语句；

d.按照数据服务备份时的运行状态分类： 热备：读写操作均可正常运行的状态下所做的备份； 温备：可读但不可写状态下进行的备份； 冷备：读写操作均不可进行，服务需要停止的状态下进行的备份。

3.备份策略： 备份策略一般都是 全量 差异 binlogs 或者是:全量 增量 binlogs。 需要注意的是，如果需要更完整的备份数据，还需要依靠binlongs（二进制日志）。binlogs是mysql最重要的日志之一，他记录了所有的DDL和DML，以事件的形式记录，这里强烈建议在生产环境中，将数据与二进制日志分开存放并对二进制日志也做备份。

4.mysql备份工具： （1）mysqldump：mysql服务自带的备份工具，mysqldump是一个逻辑备份工具，mysqldump的本质是将数据库转为可执行sql脚本，文件名以sql结尾。可以用来做完全备份和部分备份。 支持InnoDB存储引擎的热备份功能，MyISAM存储引擎的温备功能。

（2）系统自带的cp/tar工具：这是一种物理备份，它属于冷备份，需要注意的是不能仅仅备份数据，要同时备份事务日志，并且要求数据和日志在同一逻辑卷。

（3）xtrabackup：由Percona开发的很强大的开源工具，支持对InnoDB做热备，物理备份工具。

（4）mysql hotcopy：通过复制库表目录来实现mysql的备份；

mysqldump的语法： 备份：mysqldump -u root -p 库 表>路径/文件名.sql 1.对单个库进行备份： mysqldump -u user -p databasename> /路径/文件名.sql 2.对单个库中的表进行备份： mysqldump -u user -p databasename tablename1 tablename2 tablename3…> /路径/文件名.sql

3.对多个库进行备份： mysqldump -u user -p -B/–databases 库1 库2 库3…>/路径/文件名.sql

4.对所有库的所有内容进行备份： mysqldump -u user -p -A/–databases > /路径/文件名.sql

还有几个参数： -R 导出存储过程和自定义函数； -t 只备份数据而不备份表结构； -d 只备份表结构，不备份数据； –lock-table 在备份时进行锁表； –add-locks 在执行insert之前和之后进行锁表 –default-character-set 字符串 指定字符集 –single-transaction 备份期间不会锁表也不会组织任何的事务；

注意！【在命令行创建一个路径:mkdir /zhy】

备份恢复： 第一种方法： 1.在mysql里面：CREATE DATABASE databasename； 2.在命令行执行：mysql -u user -p databasename < /备份路径/文件名.sql 第二种方法： 1.CREATE DATABASE databasename； 2.USE databasename； 3.source /备份路径/文件名.sql

第二种：cp/tar 这是一种冷备份：他需要关闭服务情况下才行。 冷备份的优点： 1.非常快速的备份方法（直接拷贝文件）； 2.容易归档（简单拷贝即可） 3.容易恢复到某个时间点上（只需要将文件在拷贝回去）； 4.低度维护，告诉安全。

缺点： 1.单独使用时，只能提供到’某个时间点上’的恢复。 2.备份和还原的全过程必须停止服务； 3.若磁盘空间有限，只能拷贝到外部存储设备上，速度会慢。 4.不能按表或者用户来还原数据。

第三种：mysqlhotcopy：适用于MyISAM引擎，不适用于InnoDB。它是属于热备，而且速度快 ，适合数据量大的数据库备份；

原理：它使用LOCK TABLES，FLUSH TABLE和cｐ或者scp来快速备份数据库。 他是备份数据库或单个表的最快的途径。

mysqlhotcopy 他是perl语言一个工具，所以它需要per的支持： yum -y install per per-DBI perl-DBI-MySQL 备份语法： mysqlhotcopy -u user -p ‘password’ db_name tablename /备份路径/文件名

还原语法： cp -R /备份路径/文件名 /var/mysql/data chown -R /var/mysql/data 重启服务；

冷备份：在数据库关闭状态下进行备份操作。 热备份：早数据库处于运行时进行备份操作，该备份方法依赖数据库的日志文件。 温备份：数据库锁定表格（不可写入但可读）的状态下进行备份操作。

MySQL的优化： mysql性能优化就是通过合理安排资源，调整系统参数使mysql运行更快、 更节省资源，mysql优化，一方面是找出系统瓶颈，提高mysql数据库整体的性能，，另一方面需要合理的结构设计和参数调整，以提高用户操作响应的速度，同时还要尽可能节省系统资源，以便系统可以提供更大负荷的服务，mysql数据库优化是多方面的，原则是减少系统的瓶颈，减少资源的占用，提高系统反应速度，包含，性能优化、查询优化、数据库结构和mysql服务器优化。

mysql中可以使用SHOW STATUS语句查询一些mysql数据库的性能参数。 SHOW STATUS LIKE ‘value’; value指的是性能参数； 1.connection，连接mysql服务器的次数； 2.update,mysql服务器上线的时间； 3.slow_queries，慢查询的次数； 4.com_insert,插入操作的次数。 5.com_select，查询的次数； 6.com_update，更新的次数； 7.com_delete,删除操作的次数；

设置最大连接数： SET GLOBAL MAX_CONNECTIONS=5000; 最大可以设置16384，超过了没用

查看当前被使用的connection show variables like max_user_connections;

分析查询语句： 通过分析查询语句，可以了解查询语句执行的情况，找出查询语句的瓶颈，从而优化查询语句。 mysql中提供了EXPLAIN语句和DESCRIBE（可以缩写为DESC）语句，来分析查询语句。

EXPLAIN的语法： EXPLAIN [EXTENDED]SELECT options； EXTENDED 关键字，EXPAIN语句会产生附加信息；options是select语句的查询选项，包括from、where子句等等。 执行这个语句可以分析EXPLAIN 后面的查询语句的执行情况，并且能够分析出所查询的表的一些特征。 mysql>EXPLAIN SELECT * FROM db_info;

1.SIMPLE(simple)表示简单查询，其中不包括子查询和连接查询； 2.PRIMARY(primary)表示主查询，或者是最外层的查询语句； 3.UNION(union)表示连接查询的第2个或者后面的查询语句。 4.DEPENDENT UNION(dependent union)表示连接查询中的第二个或者后面的select语句，取决于外面的查询。 5.UNION RESULT 表示连接查询的结果； 6.SUBQUERY 表示子查询的第1个select语句； 7.DEPENDENT SUBQUERY 子查询的第1个select，取决于外面的查询； 8.DERIVED 表示导出表的select（from子句的查询）。

c.type:表示连接类型： 1.ALL：对于前面的表的任意的行组合，进行完整的表扫描。通常可以增加更多的索引来避免使用all连接； 2.index，它只扫描索引数，而不是全表数据，通常比ALL 类型要快，因为索引文件通常比数据文件小； 3.system，表示仅有一行的系统表。是const连接类型一个特例。 4.const:数据表最多只有一个匹配行，他将在查询开始时被读取，并在余下的查询优化中作为常量对待。const表查询速度很快，因为它只读一次。const与使用常数值比如primary key 或者union索引的所有部分的场合。 mysql>explain select * from db_info WHERE id=2;

5.eq_ref:对于某个来自前面的表的行组合，从该表中读取一行。当一个索引的所有部分都在查询中使用并且索引是unique或primary key时候即可使用这种类型，eq_ref 可以用于使用‘=’操作符比较带索引的列，比较值可以为常量或者一个在该表前面所读取的表的列的表达式。 EXPLAIN SELECT * FROM user，db_info WHERE user.com_id_info.com_id; 6.RE对于来自前面的表的任意行组合，将从该表中读取所有匹配行，这种类型用于所既不是unique也不是primary key 的情况，或者查询中使用了所引例的左子集，即索引中左边的部分组合ref可以用于使用=或者<=>操作符的带索引的列。

7.ref_or_null,该链接类型是如果是ref类型，但是添加了mysql可以专门搜索包含null值得行，在解决子查询中经常使用该链接类型得优化；

8.index_merge:该链接类型表示使用了索引合并优化方法，在这种情况下，key列包含了使用得索引得清单，key_len包含了 使用得索引得最长得关键元素；

9.index_subquery,：该链接类型类似于unique_subquery,可以替换in子查询，但是只适合下列形式得子查询中非唯一索引。

10.range：只检索给定范围得行，使用一个索引来选择行。key列显示使用得那个索引。key_len包含所使用得最长关键元素。当使用=，<>,<=,is null, <=>, between或者in操作符，用常量比较关键字列时，类型为range。

possible_keys:它列出的是mysql能使用哪个索引在该表中找到行。如果该列是null，则没有相关的索引，在这种情况下，可以通过检查 where子句看他是否引用某些列或者适合索引的列来提高查询性能，如果是这样，可以创建适合的索引来提高查询的性能。

ref:表示使用哪个列或者常熟或者索引一起来查询记录。

rows：显示mysql在表中进行查询必须检查的行数。

extra：显示mysql在处理查询时的详细信息。

DESCRIBE语句和EXPLAIN语句的用法以及效果一样。

索引对于查询速度的影响： mysql中提高性能的最有效的方式就是对数据表设计合理的索引，索引提供了高效的方法。 因此，索引对查询的速度有着至关重要的影响。使用所有可以快速的定位的某条记录，从而调高数据库的查询速度，提高数据库的性能，如果查询的时候没有使用索引，mysql将扫描全表的所有记录。在数据量大的情况下，这样的查询的速度会很慢。如果使用索引进行查询，查询语句可以依据索引快速定位到待查询的记录，从而减少查询的记录数，达到提高查询速度的目的。

使用索引的几种特殊情况： 1.使用like关键字的查询语句。如果匹配字符串的第一个字符为‘%’，索引不会起作用。只有‘%’不在第一个位置的时候，索引才会起到作用。 mysql> EXPLAIN SELECT * FROM db_info WHERE name LIKE ‘%jack’; ±—±------------±--------±-----±--------------±-----±--------±-----±-----±------------ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ±—±------------±--------±-----±--------------±-----±--------±-----±-----±------------ | 1 | SIMPLE | db_info | ALL | NULL | NULL | NULL | NULL | 3 | Using where | ±—±------------±--------±-----±--------------±-----±--------±-----±-----±------------ 1 row in set (0.00 sec)

mysql> EXPLAIN SELECT * FROM db_info WHERE name LIKE ‘jack%’; ±—±------------±--------±------±--------------±------±--------±-----±-----±------------ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | ±—±------------±--------±------±--------------±------±--------±-----±-----±------------ | 1 | SIMPLE | db_info | range | in_dx | in_dx | 62 | NULL | 1 | Using where | ±—±------------±--------±------±--------------±------±--------±-----±-----±------------ 1 row in set (0.00 sec)

2.使用多组合引的查询语句，一个组合索引最多可以包含16个字段，对于组合索引，只有查询条件中使用了最左边的索引字段，

3.使用or关键字的查询语句。查询条件中有or关键字，且or前后的两个条件中的字段都有索引时，查询中才使用索引，否则，查询将不适用索引。

优化数据库表结构： 分库和分表： 垂直分和水平分: 什么时候需要分库：当一个库里面的数量很多时，并且查询率很高，为了降低单个库的负荷，我们把一个库里面的表按照不同的业务类型放到多个库里面，这个叫做垂直分，水平分指库里面的表的数据量特别大，我们把同一个表里不常用的字段给他分离到其他库里面， 什么时候分表：当一个数据库里面的字段和数量特别庞大的时候，为了提高查询速度，我们根据业务把不同的字段分到多个表里，这个叫垂直分表。把一个表里的数据从一个位置分离到多个表里。

优化插入数据的速度： （1）禁止索引：alter table 表名 disable keys； （2）禁用唯一性检查：set unique_checks=0 (1开启，0禁用)； （3）禁用外键检查：set foreign_checks=0; （4）尽量自动提交：set autocommit=0； （5）尽量使用批量插入：insert into db_info（name,sex）value（‘lucy’,2）,（‘tom’,1）,（‘jack’,男）； （6）

第一章：初识redis redis（Remote dictionary server）是一个开源（BSD许可），是一种基于键值对（key-value）的NoSQL数据库，redis是一个内存存储结构的服务器，可以做告诉缓存和消息列队代理。 与很多键值对数据库不同的是，redis支持字符串(string)、哈希表(hash)、列表(list)、集合(set)、有序集合(zset)、位图(bitmaps)、Hyperloglogs、GEO等数据结构和算法组成，因此redis可以满足很多的应用场景，而且因为redis会将数据存放在内存中，所以它的读写速度非常惊人。读：110000/s 写：85000/s 不仅如此，redis还可以将内存中的数据利用快照和日志的形式保存到磁盘上，这样在发生断电或者机器故障的时候，内存中的数据不会‘丢失’。 redis还提供了键过期、流水线、哨兵、内置复制、lua脚本语言、LRU收回、事务、同时通过redis sentinel 提供高可用，通过redis cluster 提供自动分区。

redis的特性： 1.速度快： 正常情况下，redis执行命令的速度非常快，读:110000/s 写：80000/s 为什么快？ （1）把数据存放在内存中，是redis速度快的主要原因； （2）redis是用C语言编写的，一般来说C语言实现的程序‘距离’ 操作系统更近，执行速度更快； （3）redis使用了单线程架构，预防了多线可能产生的竞争问题。

2.基于键值对的数据结构服务器: 几乎所有的编程语言都提供了类似字典的功能，例如java里的map，Python里的dict，类似于这种组织数据的方式，与很多键值对数据库不同的是，redis中的值不仅可以是字符串，而且还可以是具体的数据结构，这样不仅能便于在许多应用场景的开发，同时也能够提高开发效率。 主要的数据结构有5种： 字符串(string)、哈希表(hash)、列表(list)、集合(set)、有序集合(zset)、 同时在字符串的基础上演变出了位图和Hyperloglog这两种神奇的数据结构，并且随着LBS(loclation based server基于位置服务)的不断发展，redis3.2版本中加入有关GEO（地理信息定位的功能，总之在这些数据结构的帮助下，开发者可以开发出各种’有意思’的应用）

3.丰富的功能： 除了多种数据结构，redis还提供了许多额外的功能： （1）提供了键过期功能，还可以来实现缓存； （2）提供了发布订阅功能，可以来实现消息系统; （3）支持Lua脚本功能，可以利用Lua创造出心得redis命令； （4）提供了简单得事务功能，能在一定程度上保证事务特性； （5）提供了流水线（Pipeline）功能，这样客户端能将一批命令一次性传到redis，减少了网络得开销；

4.简单稳定： redis的简单主要表现在三个方面，首先，redis的原码很少，早期版本只有2万，3.0版本后添加了集群特性，代码增至5万行左右，相对于很多NoSQL数据库来说代码量要少很多。其次redis，redis使用单线程模型，这样不仅使redis服务端处理模型变得简单，而且也是使用客户端开发变得简单，最后redis不需要依赖于操作系统中的类库（例如Memcache需要依赖libevent这样的系统类库），redis自己实现了事件处理的相关功能。

5.客户端语言多： redis提供了简单的TCP通信协议，很多编程语言可以很方便地介入redis，并且由于redis受到社区和大公司的广泛认可，所以支持redis的客户端语言也非常多，几乎涵盖了主流的编程语言， 例如：java、php、python、C、C 、Nodejs等；

6.持久化： 通常看，将数据放在内存中是不安全的，一旦发生断电或者机器故障，重要的数据可能丢失，因此redis提供了两种持久化方式：RDB和AOF，即可以用两种策略将内存数据保存到磁盘中，这样就保证了数据的持久性；

7.主从复制： redis提供了复制功能，实现了多个相同数据的redis副本，复制功能是分布式redis的基础。

8.高可用和分布式 redis从2.8版本正式提供高可用实现redis sentinel，它能够保证redis节点的故障发现和故障自动转移，redis从3.0版本正式提供了分布实现redis cluster，他是redis真正的分布式实现，提供了高可用、读写和容量的扩展性。

redis的使用场景： 1.缓存 缓存机制几乎在所有的大型网站都有使用，合理地使用缓存不仅可以加快数据的访问速度，而且能够有效的降低后端数据源的压力。redis提供了键值对过期时间设置，并且也提供了灵活控制最大内存和存溢出后的淘汰策略，可以这么说，一个合理的缓存设计能够称谓一个网站的稳定保驾护航。

2.排行榜系统： 排行榜系统几乎存在于所有的网站，例如按照热搜度的排行榜，按照发布时间的排行榜，按照各种复杂维度计算出的排行榜，redis提供了列表和有序合数架构，合理地使用这些数据结构，可以很方便的构建各种排行系统。

3.计算器的应用： 计算器在网站中的作用至关重要，例如视屏网站有浏览数，为了保证数据的实时性，每次播放和浏览要做加1的操作，如果并发量很大对于传统关系数据库的性能是一种挑战，redis天然支持技术功能而且计数的性能也非常好，可以i说是计算器系统的重要选择。

4.社交网络： 赞/踩、粉丝、共同好友/喜好、推送、下拉刷新等是社交网站的必备功能，由于社交网站访问量通常比较大，而且传统的关系型数据库不太适合保存这种类型的数据，redis提供的数据结构可以相对容易地实现这些功能。

5.消息列队系统： 消息列队系统可以说是一个大型网站的必备基础组件，因为其具有业务解耦、非实时业务肖峰等特征。redis提供了发布订阅功能和阻塞队列的功能，虽然和专业的消息队列比还不够强大，但是对于一般的消息队列功能基本可以满足。

redis的缺点： 实际上任何一门技术都一样，他都有自己的应用场景和边界，也就是说redis不是万能的，有很多问题也是redis不适合解决的。我们从数据的规模和冷热的角度来分析： 站在数据规模的角度看，数据可以分为大规模和小规模，我们redis将数据存储在内存中，虽然目前内存的价格已经相对便宜了，但是数据量太大，例如每天有几亿的用户行为数据，使用redis的话，基本是无底洞，经济成本会非常高。 站在数据冷热的角度看，数据分冷数据和热数据，热数据就是需要频繁操作的数据，反之为零冷数据。例如对于视频网站来说，视频基本信息在各个业务线都是经常操作的数据，而用户的观看记录不一定经常需要访问，所以视频信息就属于热数据，而用户观看记录属于冷数据。如果将冷数据也放在redis里，就会造成对于内存资源的浪费，但是对于热数据，就可以放在redis中加速读写，也可以减轻后端存储的负载，可以说是事半功倍。 所以redis的使用也要根据实际情况来决定。

第二章 redis得全局命令和数据结构

安装目录下src/里面的可执行命令： redis-server 启动redis服务的命令

redis-cli 启动redis客户端的

resis-server redis的哨兵服务

redis-check-aof redis-check-rdb 持久化文件的检查修复

redis-benchmark redis的基准测试工具

redis的日志级别：debug、varbose、notice、warning debug：记录很多信息，用于开发和测试 varbose：有用的信息，比debug记录的信息少 notice：普通的varbose，常用于生产环境 warning：只有非常重要的信息会记录到日志

redis的全局命令：

keys * 查看所有的键 dbsize 统计键的总和（个数） exists ‘key’ 判断这个key是否存在，返回1表示存在，返回0表示不存在 del key1 key2 … 删除键值对

键过期： 1.set key value [EX seconds] [PX milliseconds] 给新添加的键值对设置过期时间 2.expire key seconds 给已有的键值对设置过期时间 ttl key 查看键的过期时间，返回 数表示还有多少时间过期，返回-1表示未给这个key设置过期时间，返回-2表示这个key已经过期，并且被删除或者是这个键就不存在了。

redis的数据类型： 1.string（字符串）： 字符串类型是redis最基础的数据结构，首先键都是字符串类型，而且其他几种数据结构都是在字符串类型的基础上构建的，所以字符串类型能为其他四种数据结构的学习奠定基础。字符串实际上可以是字符串（简单的字符串、复杂的字符串），也可以是数字，甚至是二进制（图片、音频、视频），但是最大不能超过512M。 命令格式： set key value [ex seconds] [px mileseconds] [nx|xx]

[ex seconds] 为键设置过期时间，单位是秒

[px mileseconds] 单位是毫秒

nx 键必须不存在，才可以设置成功，用于添加；

xx 键必须存在，才可以设置成功，用于更新； 命令： setnx key value [xx] setnx这个命令在新添加键值对的时候非常有用，它能够避免我们把已有键的值误修改。返回0说明这个键已存在，并且我们的命令不会被执行；返回1，说明这个键不存在，键值对添加成功。

获取值的命令：get key

批量设置键值对： mset key1 value1 key2 value2… 例：mset a 1 b 2 c 3 d 4

计数： incr key incr只能对数字自增 值不是整数的时候，返回错误，值是整数，返回整数 1，如果键不存在，按0开始自增，返回结果是1 列： 127.0.0.1:6379> incr a (integer) 2 127.0.0.1:6379> get a “2” 127.0.0.1:6379> incr a (integer) 3 127.0.0.1:6379> get a “3” 127.0.0.1:6379> incr f (integer) 1 127.0.0.1:6379> incr f (integer) 2 127.0.0.1:6379> incr f (integer) 3 127.0.0.1:6379> get f “3”

decr key（自减） 例： 127.0.0.1:6379> decr f (integer) 2 127.0.0.1:6379> decr f (integer) 1 127.0.0.1:6379> decr f (integer) 0 127.0.0.1:6379> decr f (integer) -1 127.0.0.1:6379> decr f (integer) -2 127.0.0.1:6379> keys *

  1. “a”
  2. “e”
  3. “f”
  4. “b”
  5. “c”
  6. “d” 127.0.0.1:6379> decr g (integer) -1 127.0.0.1:6379> decr g (integer) -2 如果这个键不存在，那就从0开始递减，返回结果是-1

incrby key 数字 （自增指定数字） 例： 127.0.0.1:6379> incrby a 5 (integer) 6 127.0.0.1:6379> incrby a 5 (integer) 11 127.0.0.1:6379> incrby h 5 (integer) 5 127.0.0.1:6379> incrby h 5 (integer) 10 127.0.0.1:6379> 如果这个键不存在，从0开始增加

decrby key 数字 （自减指定个数字）

incrbyfloat key 小数 （带小数点的数） 例: 127.0.0.1:6379> incrbyfloat h 1.5 “11.5” 127.0.0.1:6379> incrbyfloat h 1.5 “13” 127.0.0.1:6379> incrbyfloat h 1.5 “14.5” 没有自减小数的命令

append key value 追加值：相当于在原在值后面再加上现在的值 例： 127.0.0.1:6379> set hello world OK 127.0.0.1:6379> append hello friends (integer) 12 127.0.0.1:6379> get hello “worldfriends”

strlen key 返回这个key他的值的字符串长度 例： 127.0.0.1:6379> strlen hello (integer) 12

设置key的新值，返回原值 getset key value 例： 127.0.0.1:6379> strlen hello (integer) 12

setrange key value的位置下标 value 把key的值的某一个位置上的字符改为value 例： 127.0.0.1:6379> get hello “world” 127.0.0.1:6379> setrange hello 0 x (integer) 5 127.0.0.1:6379> get hello “xorld”

用 value 参数覆写(overwrite)给定 key 所储存的字符串值，从偏移量 offset 开始。 不存在的 key 当作空白字符串处理。 SETRANGE 命令会确保字符串足够长以便将 value 设置在指定的偏移量上，如果给定 key 原来储存的字符串长度比偏移量小(比如字符串只有 5 个字符长，但你设置的 offset 是 10 )，那么原字符和偏移量之间的空白将用零字节(zerobytes, “x00” )来填充。 注意你能使用的最大偏移量是 2^29-1(536870911) ，因为 Redis 字符串的大小被限制在 512 兆(megabytes)以内。如果你需要使用比这更大的空间，你可以使用多个 key 。 当生成一个很长的字符串时，Redis 需要分配内存空间，该操作有时候可能会造成服务器阻塞(block)。在2010年的Macbook Pro上，设置偏移量为 536870911(512MB 内存分配)，耗费约 300 毫秒， 设置偏移量为 134217728(128MB 内存分配)，耗费约 80 毫秒，设置偏移量 33554432(32MB 内存分配)，耗费约 30 毫秒，设置偏移量为 8388608(8MB 内存分配)，耗费约 8 毫秒。 注意若首次内存分配成功之后，再对同一个 key 调用 SETRANGE 操作，无须再重新内存。

哈希： 几乎所有的编程语言都提供了哈希（hash）类型，他们的叫法可能是哈希、字典、关联数组等。 在redis中，哈希类型是指键值本身又是一种键值对结构，例如value=[field，vlue1 field2 value2…fieldN valueN] 哈希类型中的映射关系叫做field-value，这里的value是指field对应的值，不是键对应的值。

命令： 1.设置值 hset key field1 value1 fiels2 value2… 127.0.0.1:6379> hset redis a 1 b 2 c 3 (integer) 3 127.0.0.1:6379> hset redis d 4 (integer) 1

2.获取值： hget key filed 例： 127.0.0.1:6379> hget redis a “1” 127.0.0.1:6379> hget redis c “3” 127.0.0.1:6379> hget redis (error) ERR wrong number of arguments for ‘hget’ command 127.0.0.1:6379> hget redis b “2” key后面必须跟field 并且只能跟1个field

3.删除值： hdel key field1 field2… 127.0.0.1:6379> hdel redis a b c (integer) 3

4.hlen key 计算key里面field的个数 127.0.0.1:6379> hlen redis (integer) 1

5.批量设置和获取field value hmset 127.0.0.1:6379> hmset hello a 1 b 2 c 3 OK 127.0.0.1:6379> hmget hello a b c

  1. “1”
  2. “2”
  3. “3”

6.判断key里面是否存在field hexists key field 返回1代表有。返回0代表没有 127.0.0.1:6379> hexists hello a (integer) 1 127.0.0.1:6379> hexists hello b (integer) 1 127.0.0.1:6379> hexists hello d (integer) 0

7.获取所有的field hkeys key 例： 127.0.0.1:6379> hkeys hello

  1. “a”
  2. “b”
  3. “c”

8.获取所有的值 hvals key 例： 127.0.0.1:6379> hvals hello

  1. “1”
  2. “2”
  3. “3” 9.hgetall key 获取key里面每组field和value 127.0.0.1:6379> hgetall hello
  4. “a”
  5. “1”
  6. “b”
  7. “2”
  8. “c”
  9. “3”

10.hincrby key field 数字 使key里面field的值自增的指定的数字 127.0.0.1:6379> hincrby hello a 4 (integer) 5 127.0.0.1:6379> hget hello a “5”

11.计算value的字符串长度： hstrlen key field 127.0.0.1:6379> hstrlen hello a (integer) 1

list列表： 列表类型是用来存储多个有序的字符串，比如a，b，c，e五个元素从左到右组成了一个有序的列表，列表中的每个字符串称为元素，一个列表最多可以存储2^32-1个元素。在redis中，可以对列表两端进行插（push）和删除（pop），还可以获取指定范围的元素列表、获取指定索引下标的元素等，列表是一种比较灵活的数据结构。

列表类型有两个特点：第一、列获取表中的元素是有序的，这就意味着可以通过索引下标某个元素或者某个范围内的元素列表；第二、列表中的元素可以是重复的。

命令： 1.lpush 在列表的最左端添加元素，rpush 在列表的最右端添加元素， linsert 在已有列表某个元素的前面或后面添加新的元素。

lpush key value1 value2… rpush key value1 value2… linsert key before | after pivot value 例： 127.0.0.1:6379> lpush list a b c (integer) 3 127.0.0.1:6379> rpush list2 a b c (integer) 3 127.0.0.1:6379> lrange list 0 -1

  1. “c”
  2. “b”
  3. “a” 127.0.0.1:6379> lrange list2 0 -1
  4. “a”
  5. “b”
  6. “c” 127.0.0.1:6379> linsert list2 before b d (integer) 4 127.0.0.1:6379> linsert list2 after c e (integer) 5 127.0.0.1:6379> lrange list2 0 -1
  7. “a”
  8. “d”
  9. “b”
  10. “c”
  11. “e”

2.查看列表 （1）lrange key start end 查看指定范围的列表元素 127.0.0.1:6379> lrange list2 1 3

  1. “d”
  2. “b”
  3. “c” （2）lindex key index 查看指定索引下标的元素

（3）llen key 获取列表的长度

3.删除：

lpop 从左边删除1个元素 rpop 从右边删除1个元素

lrem key count（数字） value count>0 从左往右删，删除count范围内的这个value，如果范围内没有这个value，则不删； count<0 从右往左删，删除count范围内的这个value，如果范围内没有这个value，则不删； count=0 删除列表中所有和value相等的元素； 例： 127.0.0.1:6379> lrange list2 0 -1

  1. “b”
  2. “a”
  3. “b”
  4. “c”
  5. “d”
  6. “c”
  7. “b”
  8. “e”
  9. “d”
  10. “f”
  11. “a”
  12. “b”
  13. “c”
  14. “d”
  15. “a”
  16. “b”
  17. “c” 127.0.0.1:6379> lrem list2 1 a (integer) 1 127.0.0.1:6379> lrange list2 0 -1
  18. “b”
  19. “b”
  20. “c”
  21. “d”
  22. “c”
  23. “b”
  24. “e”
  25. “d”
  26. “f”
  27. “a”
  28. “b”
  29. “c”
  30. “d”
  31. “a”
  32. “b”
  33. “c” 127.0.0.1:6379> lrem list2 -1 a (integer) 1 127.0.0.1:6379> lrange list2 0 -1
  34. “b”
  35. “b”
  36. “c”
  37. “d”
  38. “c”
  39. “b”
  40. “e”
  41. “d”
  42. “f”
  43. “a”
  44. “b”
  45. “c”
  46. “d”
  47. “b”
  48. “c” 127.0.0.1:6379> lrem list2 0 b (integer) 5 127.0.0.1:6379> lrange list2 0 -1
  49. “c”
  50. “d”
  51. “c”
  52. “e”
  53. “d”
  54. “f”
  55. “a”
  56. “c”
  57. “d”
  58. “c”

4.lset key index newvalue 修改指定下标的元素为newvalue 例： 127.0.0.1:6379> lset list2 2 java OK 127.0.0.1:6379> lrange list2 0 -1

  1. “c”
  2. “d”
  3. “java”
  4. “e”
  5. “d”
  6. “f”
  7. “a”
  8. “c”
  9. “d”
  10. “c”

5.阻塞操作 blpop brpop 并发太大的时候，用阻塞的方法缓解服务器的压力。 blpop key1 key2… timeout 从第一个列表的最左边开始，弹出一个元素，返回列表名称和被删除的元素，如果所有列表中都没有元素了，那就会阻塞timeout秒，然后返回’nil’ 和阻塞的时间；

blpop key1 key2… timeout 从第一个列表的最右边开始，弹出一个元素，返回列表名称和被删除的元素，如果所有列表中都没有元素了，那就会阻塞timeout秒，然后返回’nil’ 和阻塞的时间；

127.0.0.1:6379> rpush list a b c d (integer) 4 127.0.0.1:6379> brpop list list2 3

  1. “list”
  2. “d” 127.0.0.1:6379> brpop list 3 (nil) (3.08s)

6.集合：（set）类型也是用来保存多个字符串元素，但和列表类型不同的是，集合中不允许有重复的元素，并且集合中的元素是无序的，不能通过索引下标获取元素，一个集合最多可以存储2^32-1个元素，redis处理支持集合内的增删改查，同时还支持多个集合取交集、并集、差集。 a=[0,2,3] b=[2,3,4,5,6,7,8]

命令： 1.往集合里添加元素：sadd key value1 value2… 注意：添加重复的元素只能添加进去一个；

127.0.0.1:6379> sadd set1 a b c d (integer) 4 127.0.0.1:6379> sadd set1 e e (integer) 1 127.0.0.1:6379> sadd set1 a b (integer) 0

2.删除元素：srem key value 127.0.0.1：6379>srem set1 a (integer) 1

3.显示集合中元素的个数：scard key 127.0.0.1:6379>scard set1 (intrger)4

4.判断元素是否在这个集合中： sismember key value 返回1，表示有这个元素，返回0表示没有。

5.随机从集合中返回指定个数的元素： srandmember key [count] 不写count就返回1

6.从集合中随机删除元素： spop key [count] 不写count默认是1

7.查看集合内所有元素： smember key

集合间的操作： 1.求多个集合的交集： sinter key1 key2… 例： 127.0.0.1:6379> sadd set 1 2 3 4 5 6 (integer) 6 127.0.0.1:6379> sadd set1 1 3 5 6 8 9 10 (integer) 7 127.0.0.1:6379> sinter set set1

  1. “1”
  2. “3”
  3. “5”
  4. “6”

2.求多个集合的并集：sunion key1 key2… 例: 127.0.0.1:6379> sunion set set1

  1. “1”
  2. “2”
  3. “3”
  4. “4”
  5. “5”
  6. “6”
  7. “8”
  8. “9”
  9. “10”

2.求多个集合的差集：sdiff key1 key2 例： 127.0.0.1:6379> sdiff set set1

  1. “2”
  2. “4”

将多个集合中的交集、并集、差集作为集合保存到redis当中： sinterstore destination key1 key2… sunionstore destination key1 key2… sdiffdtore

5.有序集合： 有序集合相对于哈希、列表、集合来说算是比较陌生的一种数据结构，但是他和我们这些熟悉的数据类型有很多联系，它保留了集合不能有重复值的特性，但不同的是，有序集合里边的元素是可以排序。他和列表使用下标的方式排序不同，它给每个元素设置了一个分数(score)作为排序依据，合理的利用有序集合，能帮助我们在实际开发中解决很多问题。

命令： 1.添加成员：zadd key 【NX|XX】[CH] [INCR] score member [score member…] NX:必须不存在才可以设置，用于添加； XX：必须存在才可以设置，用于更新； INCR：对分数做增加，zincrby 可以自定义增加的分数； CH：此次操作完成后，有序集合元素和分数变化的个数；

2.计算成员的个数：zcard key

3.求成员的分数： zscore key member

4.求成员的排名（根据分数）： （1）zrank key member 按照分数由低到高排 （2）zrevrank key member 按照分数由高到低排（返回位置信息是从0开始的）； 127.0.0.1:6379> zrank zset1 c (integer) 2 127.0.0.1:6379> zrank zset1 a (integer) 0

5.删除成员： zrem key member1 member2…

6.增加某个成员的分数： zincrby key increment member 若次成员不存在，则会添加此成员并且它的分数就是 increment 127.0.0.1:6379> zincrby zset1 3 a “4” 127.0.0.1:6379> zscore zset1 a “4” 127.0.0.1:6379> zincrby zset1 5 e “5” 127.0.0.1:6379> zscore zset1 e “5” 7.指定排名范围的成员： zrange key start end [withscores] 由低到高 zrevrange key start end [withscores] 由高到低 127.0.0.1:6379> zrange zset1 0 3 withscores

  1. “b”
  2. “2”
  3. “c”
  4. “3”
  5. “a”
  6. “4”
  7. “d”
  8. “4”

127.0.0.1:6379> zrevrange zset1 1 3 withscores

  1. “d”
  2. “4”
  3. “a”
  4. “4”
  5. “c”
  6. “3” 127.0.0.1:6379> zrevrange zset1 1 3
  7. “d”
  8. “a”
  9. “c”

8.指定分数范围内的成员排名： zrangebyscore key minscore maxscore [withscores] [limit offset count] 由低到高 zrevrangebyscore key minscore maxscore [withscores] [limit offset count] 由低到高 [limit offset count] 限制输出的起始位置和个数 127.0.0.1:6379> zrangebyscore zset1 1 10 withscores

  1. “b”
  2. “2”
  3. “c”
  4. “3”
  5. “a”
  6. “4”
  7. “d”
  8. “4”
  9. “e”
  10. “5” 127.0.0.1:6379> zrevrangebyscore zset1 3 1 withscores
  11. “c”
  12. “3”
  13. “b”
  14. “2”

minscore 和 maxscore 可以选择-inf（无限小） inf（无限大） 127.0.0.1:6379> zrangebyscore zset1 200 inf withscores

  1. “g”
  2. “201”
  3. “f”
  4. “290” 127.0.0.1:6379> zrevrangebyscore zset1 300 -inf withscores
  5. “f”
  6. “290”
  7. “g”
  8. “201”
  9. “e”
  10. “5”
  11. “d”
  12. “4”
  13. “a”
  14. “4”
  15. “c”
  16. “3”
  17. “b”
  18. “2” 8.求指定分数范围的成员个数：zcount key min max

9.删除指定排名内的升序元素 zrem range by rank ey start end 127.0.0.1:6379> zremrangebyrank zset1 0 2 (integer) 3

10.删除指定分数范围的成员： zremrangebyscore key min max 127.0.0.1:6379> zremrangebyscore zset1 1 5 (integer) 2

有序集合间的操作： 1.交集：zinterstore destination numkeys key1 key2…[weights weight] [aggregate sum|min|max] destination ：交集结果要保存的集合名称； numkeys：用来求交集的集合个数； weights：权重，后面的weight是权重的数字，可以是多个，根据key的数量定，默认是1； aggregate：交集取出元素的分数可以按照sum|main|max这三种方式进行计算取值； zinterstore user 2 user1 user2 weights 1 0.5

并集：zunionstore destination numkeys key1 key2…[weights weight] [aggregate sum|min|max]

第三章 键管理与redis的一些小功能 前面学过了type、del、object encoding 、exists、expire等 1.键的重命名：rename key newkey 127.0.0.1:6379> set python study OK 127.0.0.1:6379> rename python java OK

2.随即返回一个键：randomkey 127.0.0.1:6379> randomkey “user”

3.查看有多少个键：dbsize 127.0.0.1:6379> dbsize (integer) 16

4.键过期：expire 以秒为单位，expireat 后面跟时间戳（1970年1月1日0时0分0秒） 127.0.0.1:6379> expireat user 1469980800 (integer) 1

键过期后面可以不跟时间和时间戳，而是跟ttl值来给键设置键过期 ttl值: >0 -1 -2

0的整数表示：键过期的时间还剩多少秒； -1表示：键没有设置过期时间； -2表示：键不存在； 127.0.0.1:6379> EXPIRE user -2 (integer) 0 127.0.0.1:6379> get user (nil)

  1. 迁移键： （1）move ：同一个redis的不同的库之间迁移； redis的库的索引 ：0-15 move key dbindex（库的索引） 怎么让redis显示库的index： 127.0.0.1:6379> set dbnumber 0 OK 127.0.0.1:6379> select 1 OK 127.0.0.1:6379[1]> select 0 OK 127.0.0.1:6379> get dbnumber “0” select index 这个命令用来切换库； 127.0.0.1:6379> keys *
  2. “set1”
  3. “set3”
  4. “str”
  5. “user1_2”
  6. “java”
  7. “set2”
  8. “user1”
  9. “list2”
  10. “user2”
  11. “str1”
  12. “list”
  13. “zset1”
  14. “set”
  15. “str2”
  16. “set4”
  17. “dbnumber” 127.0.0.1:6379> move set 15 (integer) 1 127.0.0.1:6379> keys *
  18. “set1”
  19. “set3”
  20. “str”
  21. “user1_2”
  22. “java”
  23. “set2”
  24. “user1”
  25. “list2”
  26. “user2”
  27. “str1”
  28. “list”
  29. “zset1”
  30. “str2”
  31. “set4”
  32. “dbnumber” 127.0.0.1:6379> select 15 OK 127.0.0.1:6379[15]> keys *
  33. “set”

（2）【dump restore】： 命令： dump key 这个在源redis上 restore key ttl value [replace] 跟上replace表示如果目标redis上有同名key就覆盖掉 dump key 和restore key ttl value 用于多个redis之间进行数据迁移； 分2步：1.在源redis上，dump key 来使key序列化备份，格式rdb 2. 在目标redis上，restore key ttl value（这个value就是序列化后的值），ttl是过期时间， ttl=0的时候表示永远不过期； 127.0.0.1:6379[15]> set str1 abcdefg OK 127.0.0.1:6379[15]> dump str1 “x00aabcdefgbx00x7fx13xfcx0fx194yf” 127.0.0.1:6380[15]> restore str1 0 “x00aabcdefgbx00x7fx13xfcx0fx194yf” OK 127.0.0.1:6380[15]> keys *

  1. “str1” 127.0.0.1:6380[15]> get str1 “abcdefg”

（3）【migrate】 多个redis之间的key迁移， redis 3.0.6版本后才有的。 migrate相当于dump restore del migrate host port key|"" destination-db timeout [copy|replace] [keys key] host: 这里输入目标redis的IP port：输入目标redis的端口 key|"" ：指我们要迁移的键，如果要迁移的键多于1个时，我们这个位置写""，然后把这些键放在 后面的参数keys后面； destination-db ：目标redis的库的索引下标； timeout：超时时间，单位是毫秒； copy：表示移动key但不删除源redis上的这个key replace：跟上replace表示如果目标redis上有同名key就覆盖掉 keys : 当我们要迁移多个键的时候，把这些键的其他键写在keys后边 127.0.0.1:6379[15]> migrate 127.0.0.1 6380 str1 1 10000 OK 127.0.0.1:6380[15]> select 1 OK 127.0.0.1:6380[1]> keys *

  1. “str1” 127.0.0.1:6380[1]> get str1 “abcdefg”

127.0.0.1:6379> migrate 127.0.0.1 6380 “” 1 10000 keys list2 user1 OK 127.0.0.1:6380[1]> keys *

  1. “str1”
  2. “list2”
  3. “user1”

127.0.0.1:6380[1]> keys *

  1. “str1”
  2. “list2”
  3. “user1” 127.0.0.1:6380[1]> migrate 127.0.0.1 6379 list2 1 10000 copy OK 127.0.0.1:6380[1]> keys *
  4. “str1”
  5. “list2”
  6. “user1”

历键遍 1.keys pattern pattern：* 匹配任意字符；？ 匹配1个字符；[]匹配方括号当中任意字符，比如[1,3]代表1,3 [1-9]匹配1到9任意数字；x用来转义特殊字符； stx*1

有3种情况用到keys： 1.不对外提供服务的redis节点； 2.确认键值总数数量较少，可以用keys； 用scan可以防止阻塞： scan：渐进式的遍历，从2.8版本才有，相当于1个keys*=多个scan scan cursor [match pattern] [count number] cursor:游标，第一次遍历一般从0开始，每次scan遍历完了都会返回当前的游标的值，然后我们再从这个游标开始遍历，知道游标值为在为0，就遍历了所有的键； [match pattern]：可选项，作用是做模式的匹配； [count number]:作用是表名每次要变遍历的个数；

scan的缺点：不能保证完整的遍历所有键；

关于数据库的操作： 1.select dbindex 切换库 2.dbsize：查看库里面有多少个键 3.flushdb|fludhall：这个命令是清除当前库里的所有键，flushall这个是清楚所有库里的键

1.慢查询分析： 所谓慢查询日志就是系统在命令执行前后计算每条命令的执行时间，当超过预设的阈值，就将这条命令的相关信息（例如：发生时间，耗时，命令的详细信息）记录下来； redis的慢查询，在配置文件当中有两个配置参数： slowlog-log-slower-then:这个预设的阈值（单位：微妙，1秒=1000毫秒=1000000微秒） 阈值=0，记录所有命令；阈值<0,什么都不记录； slowlog-max-len：慢查询日志最多存储多少条； 若慢查询日志存满了，在有日志记录时，则删除最早一条，一次按此执行； 慢查询命令： （1）slowlog get [N] N是可选项，表示我们要获取慢查询日志的条数； 如果不为空，返回的内容应该包含以下内容： 1)慢查询命令的时间戳； 2） 3） 4） 5）

3.4 Redis Shell 【Redis-cli详解】 【-r】：代表将命令执行多次 [root@localhost redis-4.0.2]# redis-cli -r 3 ping PONG PONG PONG

【-i】：代表每隔几秒执行一次命令，但是-i 必须和-r 一起使用 [root@localhost redis-4.0.2]# redis-cli -r 5 -i 1 ping PONG PONG PONG PONG PONG

【-x】：从标准输入（stdin）读取数据作为redis-cli的最后参数 将world作为键hello的值 [root@localhost redis-4.0.2]# echo “world” | redis-cli -x set hello OK

【-c】：该选项是连接redis cluster节点时需要使用的，-c选项可以防止moved和ask异常 【-a】：不需要手动输入auth命令 【–scan】用于扫描指定模式的键 【–slave】把当前的客户端模拟当成redis节点的从节点，来获取redis节点的更新 【–pipe】用于执行流水线，将命令封装成redis通信协议定义的数据格式，批量发送给redis执行 【–rdb】用于拍快照（aof把快照保存在磁盘），备份，请求redis实例生成并发送RDB持久化文件，保存在本地 【–eval】执行指定的lua脚本（与魔兽世界脚本相同） 【–latency】查询延迟，包含以下3点 【–latency】 该选项可以测试客户端到目标redis的网络延迟，结果只有一条 【–latency-history】分时段的形式了解延迟信息 【–latency-dist】 使用统计图形式从控制台输出延迟统计信息 【–stat】选项可以实时获取redis的重要统计信息（info不是实时的） [root@localhost redis-4.0.2]# redis-cli --stat ------- data ------ --------------------- load -------------------- - child - keys mem clients blocked requests connections 11 829.17K 2 0 136 ( 0) 6 11 829.17K 2 0 137 ( 1) 6 11 829.17K 2 0 138 ( 1) 6 11 829.17K 2 0 139 ( 1) 6

杜兴宇 15:08:31 【redis-server详解】 【–test-memory 1024】是否有内存问题造成的内存崩溃，可以用来检测当前操作系统是否稳定的分配指定容量的内存给redis [root@localhost src]# redis-server --test-memory 1024

【redis-benchmark】可以为redis做基准性能测试，为开发和运维提供方便，做测试使 【-c】代表客户端的并发量（默认50） 【-n 】-n后面跟数量，代表客户端请求总量（默认为100 000） 【-q】仅仅显示requests per second（请求数量，秒，等信息）信息 【-r】在一个空的redis上执行redis-benhmark，并向redis里面插入更多随机键 【-p】每个请求pipe流水线的数据量（默认为1） 【-k】代表客户端是否使用keepalive，1为使用，0为没使用，默认是1

事务：为了保证多条命令组合的原子性，redis提供了简单的事务功能以及集成脚本来解决这个问题。 简单的说，事务表示一组动作，要么全部不执行，比如在社交网站上用户A关注用户B，那么需要在用户A的关注表中加入用户B，并且在用户B的粉丝中加入用户A，这两个行为要么全部执行，要么全部不执行，否则会出现数据不一致的情况。 redis事务：将一组需要一起执行的命令放到multi和exec这两个命令之间。multi命令代表事务开始exec代表事务结束；

第四章 redis的持久化 redis支持RDB和AOF两种持久化。持久化功能有效的避免了因为进程退出造成的数据丢失问题， 当下次重启时利用之前持久化的文件即可实现数据恢复。 理解持久化机制对于redis运维非常重要， 首先我们学习RDB、AFO的配置和运行流程，以及控制持久化的相关命令，如bgsave和bgrewriteaof；其次对于常见持久化问题进行分析和优化。

1.RDB RDB持久化是把当前进程数据生成快照保存到硬盘的过程，触发RDB持久化过程分为：手动触发和自动触发。

1.1 RDB触发机制： 手动触发分别对应save和bgsave命令： 手动：save和bgsave save：阻塞当前redis服务器，直到RDB过程完成（线上不建议使用，阻塞时间长）； bgsave：redis进程执行fork操作创建子进程，RDB持久化过程由子进程负责，完成后自动退出； （阻塞只发生在fork间段）

自动：save m n 1.表示m秒内的数据集存在n次修改时会自动触发bgsave； 2.如果从节点执行全量复制操作，主节点自动执行bgsave，形成RDB的文件发给其他节点； 3.执行debug reload命令重新加载redis时，也会自动触发save操作； 4.执行shutdown时，如果AOF持久化没有开启，则自动执行bgsave。

save命令：阻塞当前redis服务器，知道RDB过程完成，对于内存比较大的实例会造成长时间阻塞 线上环境不建议使用。运行save命令对应的redis的日志如下： *DB saved on disk bgsave命令：redis进程执行fork操作创建子进程，RDB持久化过程由子进程负责，完成后自动结束，阻塞只发生在fork阶段，一般时间很短。 运行bgsave命令对应的redis日志如下： *background saving started by pid 3151 *DB saved on disk *RDB：0MB of memory used by copy-on-write *background saving terminated with success

1.2 流程说明： 1）执行bgsave命令，redis父进程判断当前是否存在正在执行的子进程，如：RDB/AOF子进程， 如果存在bgsave命令直接 返回。 2）父进程执行fork操作创建进程，fork操作过程中父进程会阻塞，通过info stats命令查看latest_fork_usec选项，可以获取最近一个fork操作的耗时，单位为微秒。 3）父进程完成fork后，bgsave命令返回’‘background saving started’’ 信息并不在阻塞父进程可以继续响应其他命令； 4）子进程创建RDB文件，根据父进程内存生成临时快照文件，完成后对原有文件及进行原子替换。 执行lastsave命令可以获取最后一次生成RDB的时间，对应info统计的rdb_last_save_time选项。 5）进程发送信号给父进程表示完成，父进程更新系统信息，具体在info persistence 下的rdb_*相关选项。

1.3 RDB文件的处理 RDB文件的保存：3种方式 1.配置文件默认的保存路径 2.在redis命令行 config set dbfilename ； 3.修改配置文件redis.conf： dbfilename dir ./

压缩：LZF默认开启：我们可以通过修改配置文件里的rdbcompression yes/no 或者执行命令config set rdbcompression [yes/no] yes是开启压缩， no是禁用；

1.5校验：redis-check-dump 工具对rdb可以进行检测；/user/local/redis-4.06/src下

1.6 RDB的优缺点：： 优点： （1）RDB是一个紧凑压缩的二进制文件，代表redis在某个时间点上的数据快照，非常适用于备份 全量复制等场景，比如每6小时执行bgsave备份，并把RDB文件拷贝到远程机器或者文件系统中（如hdfs）， 用于灾难恢复。 （2）redis加载RDB恢复数据远远快于AOF的方式。 缺点： （1） RDB方式数据没办法做到实时持久化/秒级持久化。因为bgsave每次运行都要执行fork操作创建 子进程，属于重量级操作，频繁执行成本过高。 （2）RDB文件使用特定二进制格式保存、redis版本演进过程中有多个格式的RDB版本，存在老版本redis服务无法 兼容新版redis格式的问题。 针对RDB不适合实时持久化的问题，redis提供了AOF持久化方式来解决。

2.AOF 开启AOF功能需要设置配置：appendonly yes，默认不开启。AOF文件名appendfilenname 配置设置，默认文件名是appendonly.aof。保存路径和RDB持久化一直，通过dir配置指定。 AFO的过流程操作：命令写入（append）、文件同步（sync）、文件重写（rewrite）、重启加载（load）。

命令： 写入：append 将执行的redis命令写入aof缓存 aof缓冲：sync 将所有写入缓存的命令同步到磁盘 重写：rewrite 起到压缩日志文件的目的，整理磁盘空间； 重启：load 重启时执行AOF文件对数据进行加载；

3.自动持久化策略： redis提供了多种AOF缓冲同步策略，由参数appendfsync控制，不同值得含义； always、everysec、no always：命令写入aof_buf缓存区后面调用系统fsync操作同步到AOF文件，fsync完成后线程返回（一般线上不采用always）。

everysec： 命令写入aof_buf缓存区调用write操作，write完成后线程返回，fsync同步文件操作由 专门的线程每秒调用一次（一般建议选择这个策略，数据性能安全的平衡）；

no：命令写入aof_buf后调用write操作，不对AOF文件做fsync同步，同步硬盘操作由系统负责， 同步通常周期最长30秒（对线上不采用）

注意： fsync针对单个文件操作，做硬盘同步，fsync将阻塞直到写入硬盘完成后返回，保证数据持久化。 write：触发延迟写机制，write操作在写入系统缓存区之后直接返回，同步硬盘操作依赖于系统调用机制 ，比如：缓存区页写满或达到特定时间周期，同步文件之前，如果时间系统故障宕机，缓存区内数据将丢失。

4.重写机制（rewrite）： 随着命令不断写入AOF文件，文件会越来越大，为了解决这个问题，redis引入AOF重写机制压缩 文件体积，AOF文件重写是把redis进程内的数据转换为命令同步到新AOF文件的过程。 重写后的AOF文件为什么可以变小？ （1）进程内已经超时的数据不再写入文件。 （2）旧的AOF文件含由无效命令，如del key srem keys set a 111 set 222等。这些无效命令就不写入AOF文件里了。 （3）多条写命令可以合并，如：lpush list a、lpush list b、lpush list c可以转化为lpush list abc。 AOF重写降低了文件占用空间，除此之外，另一个目的是：更小的AOF文件可以更快的被redis加载。

AOF重写过程可以手动触发，也可以自动触发： 手动触发：直接条用bgrewiteao命令。 自动触发：根据auto-aof-rewrite-percentage 参数确定触发器时机，auto-aof-rewrite-min-size 表示运行AOF重写时文件最小体积，默认64MB auto-aof-rewrite-percentage：代表AOF文件空间（aof_current_size）和上一次重写后AOF文件空间的比值；

问题的定位余2优化： 1. 1.fork操作： 当redis做RDB或AOF 重写时，一个必不可少的操作就是fork子进程，对于大多数操作系统来说fork是个重量级操作。 虽然fork创建子进程不需要拷贝父进程的物理内存空间，但是需要父进程的空间存列表，例如对于10G的redis进程， 需要复制大约20MB的内存列表，因此fork操作耗时跟进程的内存量息息相关，如果使用虚拟化技术、特别是Xen虚拟机，fork操作更耗时。 fork耗时问题定位：对于高流量的redis实例OPS可达到5万以上，如果fork操作耗时在秒级别将拖慢redis几万条命令执行，对线上应用延迟影响非常明显， 正常情况下fork耗时应该是每GB消耗2毫秒左右 可以在info stats统计中查看latest_fork_user指定获取最近一次forl操作耗时，单位毫秒。

如何改善fork操作耗时： （1）优先使用物理机或者高效支持fork操作的虚拟技术，避免使用Xen。 （2）控制redis实力最大可用内存，fork耗时跟内存成正比，线上建议每个redis实例控制在10G以内。 （3）合理配置linux内存分配策略，避免物理内存不足导致fork失败。 （4）降低fork操作的频率，如湿度放宽AOF自动触发时机，避免不必要的全量复制。

2.子进程开销监控和优化： 子进程负责RDB或者AOF文件的重写，他的运行过程主要涉及到cpu、内存、硬盘三部分的消耗。 （1）cpu开销分析，子进程负责把进程内的数据 分批写入文件，这个过程属于cpu密集操作， 通常子进程对单核cpu利用率接近90%。 cpu消耗的优化：redis是cpu密集型服务，不要做绑定单核cpu操作， 子进程会和父进程产生单核资源竞争。不要和其他cpu密集服务部署在一起，造成cpu过度竞争。 如果多个redis实例，尽量保证同一时刻只有一个子进程执行重写工作。

2.内存消耗分析： 子进程通过fork产生，占用内存大小等同于父进程，理论上需要两倍内存来完成持久化操作， 但是linux系统有写时复制机制（copy-on-write）。父子进程会共享相同的内存页，当父进程处理写请求时会把要修改的内存页创建副本，而子进程fork操作过程中共享整个 父进程内存页快照。

3.硬盘的开销分析： 子进程主要职责就是把AOF或者RDB文件写入硬盘持久性，势必造成硬盘写入压力。 根据redis重写AOF/RDB的数量，结合系统工具如sar、iostat、iotop等， 可分析重重写期间负载情况。

硬盘开销优化： （1）不要和其他高硬盘的服务部署到一起。 （2）AOF重写时会消耗大量硬盘IO，可以开启配置no-appendfsync-no-rewrite，默认关闭，表示在AOF重写期间不做fsync操作/ （3）当开启AOF功能的redis用于高流量写入场景时，如果使用普通机械硬盘，写入吐量一般在100MB/s左右，这是redis实例的瓶颈 主要在AOF同步硬盘上。 （4）对于单机配置多个redis实例情况，可以配置不同实例分盘写入压力。

第五章 redis的复制 为了解决redis单节点的问题，为了保证数据的安全性，通常会把数据复制多个副本部署到其他服务器上，满足故障恢复和负载均衡的需求。 redis我们提供复制功能，它的复制方式主要有两种：1，主从复制 2.从从复制。

1.建立配置： 参与复制的redis实例划分主节点（master）和从节点（slave）。默认情况下，redis都是主节点，每一个从节点他只能有一个主节点， 而主节点可以同时具有多个从节点，复制的数据流是单向的。 配置复制的方式有以下三种： （1）在从节点的配置文件中加入：slaveof（master） （masterport）随着redis启动生效 （2）在redis-server启动命令后加上–slaveof （master） （masterport）生效； （3）在redis命令行直接执行命令：slaveof （master） （masterport）生效；

我们可以通过命令：info replication 可以在主节点或者从节点上查看主从信息

2.断开主从复制： slaveof命令不但可以建立复制，还可以在从节点上执行saveof no one断开与主节点复制关系。 断开复制主要流程： （1）断开主节点复制关系； （2）从节点晋升为主节点；

从节点断开复制后并不会抛弃原有数据，只是无法在获取 主节点上的数据变化， 通过slaveof命令还可以切主，切主就是把当前从节点的复制，切换到另一台主节点上的复制。命令： slaveof 新主节点的ip 新节点的port 即可完成切主。

3设置.主从复制的安全配置： 对于数据比较重要的节点，主节点会通过requirepass参数进行密码验证，这时所有的客户端访问 必须用auth命令实行校验，从节点与主节点的复制链接是通过一个特殊标识的客户端来完成的， 因此需要配置从节点的requirepass参数与主节点密码一致，这从节点才可以正确的

4.只读 默认情况下，从节点使用siave-read-only=yes配置为只读，由于复制只能从主节点到从节点，对于从节点 的任何修改主节点无法感知，修改从节点会造成主从数据不一致。因此建议线上不要修改从节点 的只读模式。

5.传输延迟： 主从节点一般部署在不同机器上，复制时的网络延迟就成为需要考虑的问题，redis为我们提供了repl-disable-tcp-nodelay参数用于控制是否 关闭TCP_NODELAY，默认 关闭，说明如下： （1）当关闭时，主节点产生 的命令数据无论大小都会及时地发送给从节点，这样主从节点之间延迟会变小，但增加了网络带宽的消耗，适用于主从之间的网络环境良好的场景，比如在同一个机房内的服务器。 （2）当开启时，主节点会合并较小的TCP数据包从而节省带宽，默认发送时间间隔取决于linux的内核，一般默认40毫秒，这种配置节省了带宽增大了主从之间的延迟，适合于主从网络环境复杂或带宽警长的场景，如机房部署。

6.主从拓扑： 1.主从，2.树状主从结构；

7.复制过程的原理： （1）slave向master发送sync命令 （2）master开启了进程来将dataset写入rdb文件，同时将子进程完成之前接收到的命令缓存起来。 （3）子进程写完，父进程得知，开始将RDB文件发送给slave。 （4）master发送完RDB文件，将缓存的命令也发给slave。 （5）master增量的把命令发送给slave。 值得注意的是，当slave跟master的链接断开时，slave可以自动的重新连接master，在redis2.8版本之前，每当slave进程挂掉重新连接master

7.心跳 朱从几点在建立复制后，他们之间维护者连接并彼此发送心跳命令，主从心跳判断机制： （1）主从点彼此都有心跳检测机制，各自模拟成对方的客户端通信，通过client list命令查看复制相关的哭护信息，主节点的连接状态为flags：M，从节点的连接状态为flags：S （2）主节点默认每隔10秒发送给从节点ping命令，判断从节点的存活和连接状态，可以通过repl-ping-slave-period 10 设置多少秒发送ping命令； （3）从节点在主线程中每个1秒发送replconf ack {offset}给主节点上报自身当前的复制偏移量。 replconf作用 （1）实时检测主节点网络状态； （2）上报自身复制偏移量，检测复制数据是否丢失，如果从节点数据丢失，在从主节点的积压复制缓存区里拉取丢失的数据； （3）时间保证从节点的数量和延迟性功能，通过min-slaves-to-write、min-salves-max-lag参数定义。主节点根据repconf命令判断从节点超时时间，体现在info replication统计中的lag信息中lag表示与从节点最后一次通信延迟的秒数，正常延迟应该在0-1之间，如果超过了repl-timeout配置值（默认60秒），则判定从节点下线并且断开复制客户端连接。即使主节点判定从节点下线后如果从节点重新恢复，心跳检测会继续进行。

8.异步复制 主节点不断负责数据的读写，还负责把命令同步给从节点。写命令的发送过程时异步的，也就是说主节点自身处理完命令后直接返回给客户端，并不等从节点复制完成。 主节点复制的流程： （1）主节点接受写命令‘ （2）命令执行完之后返回给客户端执行结果； （3）异步写命令发送给从节点； 由于主从复制是异步的，就会造成从节点的数据相对于主节点在延迟，具体延迟多少字节。我们可以在主节点上执行info replication命令相关指标：offset和master_repl_offset之间的差值就是延迟的字节；

在统计信息中可以看到从节点slave0信息，分别记录了从节点的ip、port、状态、offset表示当前从节点的复制偏移量，master_repl_offset表示当前主节点复制偏移量，两者的差值就是延迟量。 redis的复制速度取决于主从之间的网络环境，repo-disable-tcp-nodelay，命令处理速度等。正常情况下，延迟在1秒以内。

9.开发与运维中的问题 理解了复制原理之后，我们来看一些容易出现问题的应用场景： 1.读写分离的问题： master负责写，slave负责遇到的问题： （1）复制数据延迟；（2）读到过期的数据；（3）从节点故障 1.复制数据延迟： redis复制数据的延迟由于异步复制特性是无法避免的，延迟取决于网络带宽和命令阻塞 情况，比如刚在主节点写入数据后立刻在从节点上读取可能获取不到，需要业务场景允许短时间内的数据延迟对于无法容忍大量延迟的场景，可以编写外部监控程序监听主从节点的复制偏移量， 当延迟较大时，触发报警或者通知客户端避免读取过高的从节点。 监控程序：定期检查主从节点的偏移量，主从节点偏移量的差值叫做主从节点延迟的字节，当延迟过高时，监控程序触发报警，并通知客户端从节点延迟过高，客户端接收到具体的从节点读命令请求。

2.读到过期数据的问题： 当主节点存储大量设置超时间数据时，如缓存数据，redis内部需要维护过期数据删除策略， 删除策略主要有两种： 一种叫惰性删除，另一种叫做定时删除 惰性删除，主节点每次处理读取命令时，都会检查键是否超时，如果超时，则执行del删除键，del命令异步发送给从节点，从节点永远不会主动删除超时的数据; 定时删除，redis主节点在内部时任务会循环采样一定数量的键，当发现采样的键过期时执行del命令之后在同步给从节点。 如果此时数据大量超时，主节点采样速度跟不上过期且主节点没有读取过期键的操作，那么从节点将无法收到del命令。这时在从节点上可以读取到已经超时的数据，redis在3.2版本解决了这个问题，从节点读取数据之前会键的过期时间决定是否返回数据，可以升级到3.2版本来规避这个问题。

3.从节点故障问题： 对于从节点的故障问题，需要在哭护端维护，维护可用的节点列表，当从节点故障时，会立刻切换到其他的从节点或主节点上，由监控程序完成。

2.主从配置不一致： 主从配置不一致是一个容易忽视的问题，对于有些配置主从之间是可以不一致的，比如：主节点关闭AOF但是从节点开启。但对于内存相关的配置必须一致，比如：maxmemory， hash-max-ziplist-entries等参数。 当配置的maxmeory-policy策略进行内存溢出控制，此时从节点数据已经丢失，但主从复制流程依然正常进行，复制偏移量也正常，当增大了从节点的内存，要修复这类问题也只能手动进行全量复制。当压缩列表相关参数不一致时，虽然主从节点存储的数据一致，但是时间占用情况差异会比较大。

4.规避全量复制： 第一次建立连接的复制：由于是第一次建立复制，从节点不包含任何主节点数据，因此必须进行全量复制才能完成数据同步。对于这种情况全量制无法避免。当对数据量较大却流量较高的主节点添加从节点时，建议在低峰进行操作，或者尽量规避使用大量的redis节点。 节点运行id不匹配：当主从复制关系建立后，从节点会保存主节点的运行id，如果此时主节点因故障重启，那么他的运行id会变，从节点发现主节点的运行id不匹配时，会认为自己复制的是一个新主节点，从而进行全量复制，对于这种情况应该从架构上规避，比如提供故障转移功能，当主节点发生故障后，手动提升一个从节点为主节点或采用支持自动故障转移的哨兵或集群方案。 （3）复制积压缓存区不足：当主从节点连接中断后，从节点在次连接上主节点时会发送psync（runid）（offset），命令请求增量复制，但是请求的offset不在主节点的积压缓存区内，则无法提供给从字节点数据，因此增量复制会退化为全量复制，针对这种情况需要根据网络中断时长，调整复制积压缓存的大小。 入数据量，调整复制积压缓存区的大小。 网络中断一般有闪断、机房割接、网络分区等情况，这时网络中断的时长一般在分钟级，写命令可以查看的差值来计算出网络中断大约产生多少新写入的数据来修改缓存区大小。因而可以避免由于复制积压缓存区不足造成的全量复制。

5.单节点复制风暴： 单节点复制风暴一般发生在主节点挂载多个从节点的场景。当主节点重启恢复后，从节点会发起全量复制流程，这时主节点创建RDB快照，如果在快照创建完毕之前，有多个从节点都尝试与主节点进行全量同步，那么其他从节点将共享这份RDB快照。这点redis做了优化，有效避免了创建多个RDB快照，但是，，同时向多个从节点发送RDB快照，可能使主节点的网络带宽消耗严重，造成主节点的延迟变大，极端情况会发生主从节点连接断开，导致复制失败。 解决方案：首先可以也减少主节点挂载从节点的数量，或者采用树状复制结构，加入中间层从节点用来保护主节点。从节点采用树状复制非常有用，网络开销交给位于中间层的从节点，而不必消耗主节点的网络带宽，但是这种树状结构带来了运维的复杂性增加了手动和自动处理故障转移的难度。

单机复制风暴 由于redis的单线程架构，通常单带机器会部署多个redis实例，但一台机器同时部署多个主节点，如果这台机器出项故障或者网络长时间中断 ，当他重启恢复后，会有大量从节点针对这台机器的主节点进行全量复制，会造成当前机器网络带宽耗尽。 如何避免？方法如下： （1）应该尽量避免多台主节点部署在同一台服务器上，尽量分散部署。 （2）当主节点所在机器

redis的哨兵（sentinel）： redis的主从复制模式下，一旦主节点由于故障不能提供服务，需要人工将从节点晋升为主节点。 同时要通知应用方更新新节点地址，对于很多场景故障处理的方式是无法接受的。可服务，喜的是2.8版本开始提供了redis sentinel（哨兵）架构来解决这个问题。

1.redis主从复制问题： redis主从复制模式可以将主节点的数据改变同步给从节点，这样呢从节点就可以起到2个作用： 第一，作为主节点的一个备份，一旦主节点出了故障从节点 可以作为后备’‘顶上来’'提供服务，并且保证了数据的安全性；第二，从节点可以扩展主节点读的能力，一旦主节点不能支撑大并发量的读操作，从节点可以在一定程度上帮助主节点分担读的压力。 但是主从复制也带了以下问题： （1）一旦主节点出现故障，需要手动将一个从节点晋升为主节点，同时需要修改应用方的主节点地址，还需要 命令其他从节点去复制新的主节点，整个过程需要人为操作，同时耗时较长。 （2）主节点的写能力受到单机的限制。 （3）主节点的存储能力受到单机的限制。 哨兵主要解决第一个问题。

高可用 redis主从复制模式下，一旦主出现故障，需要人工故障转移，无论对redis的应用还是运维方都带来很大的不便，对于应用方来说无法及时感知到主节点的变化，必然会造成一定的写数据丢失和读数据的错误，甚至可能造成应用方服务不可用，对于redis的运维来说，整个故障转移的过程是需要人工来介入的，故障转移实时性和准确性都无法得到保障。 一个1主2从的redis主从复制模式下的主节点故障了，是如何进行故障转移的？ （1）主节点发生故障后，客户端连接到主节点失败，两个从节点与主节点的连接失败造成复制中断。 （2）如果主节点无法正常启动，需要选出一个从节点（slave1），对其执行slave no one命令使其成为新的主节点。 （3）原来的从节点（slave1）成为新的主节点后，更新应用方的主节点信息，从新启动应用方。 （4）客户端命令另外一个从节点（slave2）去复制新的主节点。 （5）待原来的主节点恢复后，让他去复制新的主节点。

哨兵部署安装： 在安装目录下有sentinel.conf这个哨兵的配置文件，我们修改这个配置文件部署sentinel： sentinel monitor mastername masterip masterport quorum（这里填数字） sentinel monitor mymaster 192.168.1.1 6379 这个选项指定了我们哨兵要监控的主节点，一个哨兵可以同时监控多个主节点（多个redis集群）； 我我们只需要增加以上配置就可以了。 2.sentinel down-after-milliseconds mastername 30000(默认值) 这一项配置是指定主节点多少毫秒没有回应就认为主节点下线了； 3.sentinel parallel-syncs mastername 1（默认值） 这一项是设置故障转移后，同时可以有几个从节点对主节点数据进行复制； 4.sentinel failover-timeout mastername 180000（默认） 这一项是故障转移的超过了设置故障转移失败，下一次故障转移的超时间会变成这个设置时间的两倍;

failover-time 这个超时间主要体现现在4个阶段： （1）如果redis sentinel对一个主节点故障转移失败，那么下次在对该主节点做故障转移的超时时间就会是failover-timeout的2倍； （2）如果sentinel节点选出的从节点执行slaveof no one一直失败（例如该从节点此时出现故障）当过程超过了failover-timeout，则故障转移失败； （3）从节点晋升为主节点之后，sentinel节点还会发送info命令来确认主节点故障转移成功，如果此过程执行时间超过了failover-timeout，则故障转移失败。 （4）从节点连接新的主节点超时，故障转移失败 ；这个failover-timeout不包含主从复制的过程；

4.sentinel auth-pass 如果我么要监控的主节点设置了密码，那么我们就通过这一项来填写主节点的名称和密码，以确保能够监控到主节点；

5.sentinel notification-script 在故障转移期间，当一些警告级的sentine时间发生（指重要事件，例如：-sdown：客观下线、-odown：主管下线）时，会触发对路径的脚本，并向脚本发送相应的事件参数。

6.sentinel client-reconfig-script 他的作用是在故障转移结束后，会触发对应路径的脚本，并向脚本发送故障结果的相关参数。

启动sentinel节点的方法： 第一种：redis-server redis-sentinel.conf 第二种：redis-server redis-sentinel.conf -sentinel 登录sentinel节点客户端命令：redis-cli -p 26379（默认）

sentinel节点不是数据点，所以它支持的命令不多，主要有以下常用： 1.sentinel master 展示所有被监视的主节点状态以及相关的信息； 2.sentinel slaves 展示所有指定的master的从节点的状态及相关的统计信息： 3.sentinel master 展示指定的主节点的详细信息； 4.sentinel sentinel 展示指定的主节点的哨兵的相关信息； 5.sentinel get-master-addr-by-name 返回指定master的ip地址和端口； 6.sentinel reset 当前sentinel节点对符合(通配符风格)的主节点的配置进行重置，包括清除主节点的相关状态（例如故障转移），从新发现从节点和sentinel节点。 对指定的master强制故障转移（没有和其他sentinel节点协商），当故障转移完成后，其他的sentinel节点按照故障的结果更新自身配置；

8.sentinel ckquorum 检测当前主节点的哨兵是否到达quorum的个数，ckquorum填的数和quorum对比； 9.将sentinel节点的配置信息强制写到磁盘上； 10.sentinel remove 取消当前sentinel节点对于指定主节点的控制； 11.sentinel monitor 指定监控的主节点name、ip、port、quorum 12.sentinel set 动态修改sentinel节点配置选项 13.sentinel is-master-down-by-addr sentinel节点之间用来交换对主节点是否下线的判断，根据参数不同，还可以作为sentinellingdaozhe选举的通信方式；

部署技巧 到现在有关Redis Sentinel的配置和部署方法相信你们已经基本掌握了,但在实际生产环境中都有哪些部署的技巧？本节将总结一下。

  1. Sentinel节点不应该部署在一台物理“机器”上。 这里特意强调物理机是因为一台物理机做成了若干虚拟机或者现今比较流行的容器,它们虽然有不同的IP地址,但实际上它们都是同一台物理视,同一台物理机意味着如果过台机器有什么硬件故障,所有的虚拟机都会受到影响,为了实现Sentinel节点集合真正的高可用,请勿将Sentinel节点部署在同一台物理机器上。 2)部署至少三个且奇数个的sentinel点。 3个以上是通过增加Sentinel节点的个数提高对于故障判定的准确性,因为领导者选举需要至少一半加1个节点,奇数个节点可以在满足该条件的基础上节省一个节点。 3)只有一套Sentinel,还是每个主节点配置一套Sentinel? Sentinel节点集合可以只监控一个主节点,也可以监控多个主节点那么在实际生产环境中更偏向于哪一种部署方式呢,下面分别分析两种方案的优缺点。 方案一:一套Sentinel,很明显这种方案在一定程度上降低了维护成本,因为只需要维护固定个数的Sentinel节点,集中对多个Redis数据节点进行管理就可以了。但是这同时也是它的缺点,如果这套Sentinel节点集合出现异常,可能会对多个Redis数据节点造成影响。还有如果监控的Redis数据节点较多,会造成Sentinel节点产生过多的网络连接,也会有一定的影响。 方案二:多套Sentinel,显然这种方案的优点和缺点和上面是相反的,每个Redis主节点都有自己的Sentinel节点集合,会造成资源浪费。但是优点也很明显,每套Redis Sentinel都是彼此隔离的。

6.4 实现原理 本节将介绍Redis Sentinel的基本实现原理,具体包含以下几个方面: Redis Sentinel的三个定时任务、主观下线和客观下线、Sentinel领导者选举、故障转移,相信通过本节的学习同学们能对Redis Sentinel的高可用特性有更加深人的理解和认识。

6.4.1 三个定时监控任务 一套合理的监控机制是Sentinel节点判定节点不可达的重要保证, Redis Sentinel通过三时监控任务完成对各个节点发现和监控: 1)每隔10秒,每个Sentinel节点会向主节点和从节点发送info命令获取最新的拓扑结构。

这个定时任务的作用具体可以表现在三个方面： （1）通过向主节点执行info命令,获取从节点的信息,这也是为什么Sentinel节点不需要显式配置监控从节点。 （2）当有新的从节点加入时都可以立刻感知出来。 （3）节点不可达或者故障转移后,可以通过into命令实时更新节点拓扑信息。

2)每隔2秒,每个Sentinel节点会向Redis数据节点的_sentinel :hello频道上发送该Sentinel节点对于主节点的判断以及当前Sentinel节点的信息,同时每个Sentinel节点也会订阅该频道,来了解其他Sentinel节点以及它们对主节点的判断,所以这个定时任务可以完成以下两个工作: （1）发现新的Sentinel节点:通过订阅主节点的_sentinel :hello了解其他的Sentinel节点信息,如果是新加人的Sentinel节点,将该Sentinel节点信息保存起来，并与该Sentinel节点创建连接。 （2）Sentinel节点之间交换主节点的状态,作为后面客观下线以及领导者选举的依据。 Sentinel节点publish的消息格式如下: <Sentinel 节点IP> <Sentinel 节点端口> <Sentinel 节点runId > <Sentinel 节点配置版本> <主节点名字> <主节点IP> <主节点端口> <主节点配置版本>

  1. 每隔1秒,每个Sentinel节点会向主节点、从节点、其余Sentinel节点发送一条ping命令做一次心跳检测,来确认这些节点当前是否可达。通过上面的定时任务, Sentinel节点对主节点、从节点、其余Sentinel节点都建立起连接,实现了对每个节点的监控，这个定时任务是节点失败判定的重要依据。

6.4.2 主观下线和客观下线 1、主观下线 刚才介绍的第三个定时任务,每个Sentinel节点会每隔1秒对主节点、从节点、其他Sentinel节点发送ping命令做心跳检测,当这些节点超过down-after-milliseconds没有进行有效回复, Sentinel节点就会对该节点做失败判定,这个行为叫做主观下线。从字面意思也可以很容易看出来主观下线是当前sentinel节点的一家之言，存在误判的可能。

2、客观下线 当Sentinel主观下线的节点是主节点时,该Sentinel节点会通过sentinel is-master-down-by-addr命令向其他Sentinel节点询问对主节点的判断,当超过个数， Sentinel节点认为主节点确实有问题,这时该Sentinel节点会做出客观下线的决定,这样客观下 线的含义是比较明显了,也就是大部分Sentinel节点都对主节点的下线做了同意的判定,那么 这个判定就是客观的。 Sentinel is-master-down-by-addr <current_epoch> Ip：主节点IP Port：主节点端口 Current_epoch：当前配置时间 Runid：不同类型决定了次API作用的不同 当runid等于“*”时,作用是Sentinel节点直接交换对主节点下线的判定。 当runid等于当前Sentinel节点的runid时,作用是当前Sentinel节点希望目标Sentinel节点同意自己成为领导者的请求。

6.4.3 领导者sentinel节点选举 假如Sentinel节点对于主节点已经做了客观下线,那么是不是就可以立即进行故障转移了?当然不是,实际上故障转移的工作只需要一个Sentinel节点来完成即可,所以Sentinel节点之间会做一个领导者选举的工作,选出一个Sentinel节点作为领导者进行故障转移的工作。Redis使用了Raft算法实现领导者选举,因为Raft算法相对比较抽象和复杂,以及篇幅所限,所以这里给出一个Redis Sentinel进行领导者选举的大致思路: 1)每个在线的Sentinel节点都有资格成为领导者,当它确认主节点主观下线时候,会向其他Sentinel节点发送sentinel is-master-down-by-addr命令,要求将自己设置为领导者。 2)收到命令的Sentinel节点,如果没有同意过其他Sentinel节点的sentinel is-master-down-by-addr命令,将同意该请求,否则拒绝。 3)如果该Sentinel节点发现自己的票数已经大于等于max (quorum, num (sentinels)/2 1),那么它将成为领导。 4)如果此过程没有选举出领导者,将进入下一次选举。

实际上Redis Sentinel实现会更简单一些,因为一旦有一个Sentinel节点获得了max (quorum,num (sentinels)/2 1)的票数,其他Sentinel节点再去确认已经没有意义了,因为每个sentinel节点只有一票。

6.4.4 故障转移 领导者选举出的Sentinel节点负责故障转移,具体步骤如下: 1)在从节点列表中选出一个节点作为新的主节点,选择方法如下: a)过滤: “不健康”(主观下线、断线)、5秒内没有回复过Sentinel节点ping响应、与主节点失联超过down-after-milliseconds*10秒。 b)选择slave-priority (从节点优先级)最高的从节点列表,如果存在则返回,不存在则继续。 c)选择复制偏移量最大的从节点(复制的最完整),如果存在则返回,不存在则继续。 d)选择runid最小的从节点。

  1. Sentinel领导者节点会对第一步选出来的从节点执行slaveof no one命令让其成为主节点。
  2. Sentinel领导者节点会向剩余的从节点发送命令,让它们成为新主节点的从节点,复制规则和parallel-syncs参数有关。 4)sentinel节点集合会将原来的主节点更新为从节点，并保持着对其关注，当其恢复后命令它去复制新的主节点。

6.5 节点运维 1.节点下线 在介绍如何进行节点下线之前，首先需要明白两个概念：临时下线和永久下线。 临时下线：暂时将节点关掉,之后还会重新启动,继续提供服务。 永久下线:将节点关掉后不再使用,需要做一些清理工作,如删除配置文件、持久化文件、日志文件。 所以你需要并清楚本次下线操作是临时下线还是永久下线。通常来看,无论是主节点、从节点还是Sentinel节点,下线原因无外乎以下几种: 1、节点所在的机器出现了不稳定或者即将过保被收回 2、节点所在的机器性能比较差或者内存比较小，无法支撑应用方的需求 3、节点自身出现服务不正常情况，需要快速处理

（1）主节点 如果需要对主节点进行下线,比较合理的做法是选出一个“合适"(例如性能更高的机器) 的从节点,使用sentinel failover功能将从节点晋升主节点, sentinel failover

（2）从节点和sentinel 如果需要对从节点或者Sentinel节点进行下线,只需要确定好是临时还是永久下线后执 行相应操作即可。如果使用了读写分离,下线从节点需要保证应用方可以感知从节点的下线变化,从而把读取请求路由到其他节点。 需要注意的是, Sentinel节点依然会对这些下线节点进行定期监控,这是由Redis sentinel的设计思路所决定。

2、节点上线 （1）添加从节点 添加从节点的场景大致有如下几种:

  1. 使用了读写分离,但现有的从节点无法支撑应用方的流量。
  2. 主节点没有可用的从节点,无法支持故障转移。
  3. 添加一个更强悍的从节点利用手动failover替换主节点。 添加方法：添加slaveof (masterIp) (masterPort)的配置,使用redis-server启动即可,它将被Sentinel节点自动发现。

（2）添加sentinel节点 添加Sentinel节点的场景可以分为以下几种: 1)当前Sentinel节点数量不够,无法达到Redis Sentinel健壮性要求或者无法达到票数。 2)原Sentinel节点所在机器需要下线。 添加方法:添加sentinel monitor主节点的配置,使用redis-sentinel启动即可,它将被其余Sentinel节点自动发现。

（3）添加主节点 因为Redis Sentinel中只能有一个主节点,所以不需要添加主节点,如果需要替换主节点,可以使用Sentinel failover手动故障转移。

3、节点配置 有关Redis数据节点和Sentinel节点配置修改以及优化的方法,前面的章节已经介绍过 了,这里给出Sentinel节点配置时要注意的地方: （1）Sentinel节点配置尽可能一致,这样在判断节点故障时会更加准确。 （2）Sentinel节点支持的命令非常有限,例如config命令是不支持的,而Sentinel节点也需要dir、loglevel之类的配置,所以尽量在一开始规划好,不过所幸Sentinel节点不存储数据,如果需要修改配置,重新启动即可。

云数据库Redis 数据库管理 数据结构 全文检索 数据库

0 人点赞