MySQL 基本内容

MySQL的基础

Paste_Image.png Paste_Image.png Paste_Image.png Paste_Image.png Paste_Image.png Paste_Image.png

DDL 数据定义语言  表、索引、视图、
DML 数据操纵语言  操作表中数据

存储过程 没有返回值的函数 procedure 仅仅对数据进行加工
存储函数 有返回结果的代码片段 fuction 对数据加工后，并可以调用，有返回值（输入和输出）
事件调度器 完成周期性的内键任务

物理层 打开表空间后，可以看到内容
逻辑层 表、空间、视图
视图层 用户最终看到的数据

连接线程 维护用户连接
守护线程 数据<——>磁盘，维护cashed和buffer

[mysqld]
[mysqld_safe]
[mysqld_multi] 多实例共享参数
[server] 服务器端都有效
[mysql] 客户端
[mysqldump] 备份、导入、导出

源码 有些功能需要自己设计
rpm 不需要太多的功能  

SHOW ENGINES;    查看存储引擎

MySQL 数据类型

Paste_Image.png Paste_Image.png Paste_Image.png Paste_Image.png Paste_Image.png Paste_Image.png Paste_Image.png Paste_Image.png Paste_Image.png Paste_Image.png

char varchar text系列 不区分字符大小写
binary varbinary blob 区分字符大小写
unsigned 无符号，字段修饰符，紧跟数据类型之后，修饰类型本身，不是补充约束条件
ENUM 存储的是索引，不是字串（a字串-0索引），不能用来比较和查询
SET 储存最多64位的二进制数据，按位比较（a-100，b-011）

SQL mode

Paste_Image.png

修改全局，不会立即生效，只对新建的生效；
立即生效，修改会话级别；
% 任意长度的任意字符串
SHOW GLOBAL VARIABLES LIKE 'sql_%'
SHOW GLOBAL STATUS LIKE 'sql_%'

CREATE DATABASE mydb;
use mydb;
CREATE TABLE t1 (id INT UNSIGNED AUTO_INCREMENT PRIMARY KEY NOT NULL, name CHAR(5) NOT NULL)
INSERT INTO t1 不指明，对两个都插入数据
INSERT INTO t1 (name) VALUES ('tom'),('blackberry'); 违反5个长度的规则，保留前5个字符，进行插入
SHOW WARNINGS;  查看警告
SELECT * FROM t1;
SET sql_mode='TRADITIONAL';  修改当前会话，再次插入违反5个长度的规则，就error，不插入；
SET sql_mode='STRICT_ALL_TABLES'; 对已建立的会话无效，对新建的会话有效；
SHOW GLOBAL VARIABLES LIKE 'sql_mode'; 可以看到相应的值
使用SET修改，不论是全局/局部，mysql重启，都失效；
修改配置文件：可以全局、永久生效；
    或者 启动mysql时，直接传递给mysql，做命令行参数进行

多表查询
代价高
一张表——>两个表——>需要连接
连接 需要按照指定字段之间的关系进行连接——>过滤出所需数据

Paste_Image.png

笛卡尔乘积 最耗资源
可能无意义
等值连接 相同意义的行——>构建为新的行；不同意义的行——>不管它

Paste_Image.png Paste_Image.png Paste_Image.png Paste_Image.png Paste_Image.png

等值连接

Paste_Image.png

TeacherID=1有多个，那么TID会出现多次

Paste_Image.png Paste_Image.png Paste_Image.png Paste_Image.png

某个同学的老师的名字
将两张表的数据按指定字段进行连接

Paste_Image.png

查看索引 等值连接——代价较高

Paste_Image.png

同学 ~ 班级

Paste_Image.png Paste_Image.png Paste_Image.png

每位同学显示的班级、无班级的同学也要显示出来
等值连接 
    左外连接(左侧为基准)：左侧(每一项都出现、有相应的等值条目)~右侧无，左侧显示，右侧留空
    右外连接(右侧为基准)：右侧(每一项都出现、有相应的等值条目)~左侧无，右侧显示，左侧留空

Paste_Image.png Paste_Image.png Paste_Image.png

子查询
建立在另一个查询基础之上，查询中嵌套者查询
MySQL的子查询性能差，建议不要使用子查询，可以使用单表查询或者多表查询
FROM语句的子查询 相当于VIEW(虚表、保存)，但是不保存

Paste_Image.png

年龄大于平均年龄的同学
先 平均年龄，再 年龄大于平均年龄

Paste_Image.png Paste_Image.png

老师的年龄=同学的年龄

Paste_Image.png

求每一个班级的同学的平均年龄 大于25的班级信息

Paste_Image.png Paste_Image.png

使用FROM字句，大部分可以多加一个条件WHERE来使用
大多数子查询 可以拆为 多表查询

Paste_Image.png

使用查询语句写出非常复杂的用法，完成其功能 需要练习

联合查询 两个SELECT语句的查询结果合并成一个

Paste_Image.png Paste_Image.png Paste_Image.png Paste_Image.png Paste_Image.png

1  SELECT Name,Age FROM students WHERE Age>(SELECT avg(Age) FROM students);
参考网上
2  SELECT s.Name,scores.CourseID FROM students AS s LEFT JOIN scores ON scores.StuID = s.StuID WHERE scores.CourseID IN (1,2,4,7);
   select students.name from students,(select distinct stuid from scores where courseid in (1,2,4,7))as s where s.stuid=students.stuid;
3  SELECT Name,avg(Score) FROM (SELECT  * FROM students LIMIT 8) AS rj LEFT JOIN scores AS jr ON rj.StuID=jr.StuID GROUP BY Name ORDER BY avg(Score) DESC;
4  SELECT courses.Course,count(rj.StuID) FROM scores AS rj LEFT JOIN courses ON courses.CourseID=rj.CourseID GROUP BY rj.CourseID;

索引管理、视图、DML

Paste_Image.png

单进程多线程（线程池管理客户端的众多请求）
  用户连接连接线程
mysql提供给用户的视图3种
  物理视图 ...
mysql数据文件文件类型
  数据文件、索引文件
  物理视图
    重做日志、撤销日志、二进制日志（中继日志）、错误日志、查询日志、慢查询日志、
  逻辑视图（以上日志——SQL接口映射——关系型数据库，应该具备的组成部分）
    表（datablock）——映射——磁盘的block——映射——本地存储
      转换为二维映射关系，依赖于SQL接口和存储引擎
    逻辑视图在——SQL接口、storage engines
  用户视图
    终端用户访问的数据视图

Paste_Image.png Paste_Image.png

DDL&DML
索引管理

    按特定数据结构存储的数据（为算法的实施而设计——数据的查询）
聚集索引 非聚集索引  数据是否与索引存储在一起
主键索引 辅助索引
稠密索引 稀疏索引  是否索引了每一个数据
B-Tree/B+Tree(平衡树)索引
哈希索引（减值索引） 一对一查找 不能排序
R-TREE（空间）索引
左前缀索引  某一列数据过大，只取此列的一部分做索引，
  LIKE "%abc%"  只查找中间的abc
    最好左侧不要加%   LIKE "abc%" 
        索引优化  查询语句的优化(改写)  对关系型数据库很重要  提高数十倍
简单索引、组合索引
  名字和课程组合成一个索引
覆盖索引
  通过查找索引就可以找到数据，而不用去找原数据

解析
    （查询练习了 降龙18掌的同学，并不是查询所有，否则全部加载到内存后，耗内存资源）
    整个数据——>取一列（#稠密索引）——>分成4段（首字母排序等），标识其相关内容（#稀疏索引）——>目的：提高搜索效率
    索引 不包含数据本身  可能是一个文件，记录数据位置  特殊的数据结构  按照特定顺序存放 
    多级形式——顶级索引（稀疏）、最底层索引（稠密）
      eg: 目标在索引级别4——需要5次IO（假如没有索引，目标在第100号，就需要100次IO）
    插入数据到某一行，可能会导致整个数据的索引更改，提前预留空间，经常插入数据到数据库的某一行，索引的价值就不太大了，收益大于成本——就有价值
    元数据
    从原始文件中，加载出有限部分的数据块，并从中过滤出符合条件的行即可
    #主键索引（约束） 每一个数据不允许重复出现（n个同学练习降龙18掌，出现n次，搜索较慢，主见约束可以解决此问题）
      符合结果的可能只有一个
      一对一的查询
    #聚集索引  把数据抽出来按索引存放  索引可以指向数据位置——即必须是主键索引——1号、1号同学的数据存放在一起（索引和指向数据在一起）
      找到索引，即找到数据
      删除某行时，整个数据都要进行编排  麻烦
        除了主见索引的，其他都叫#辅助索引
        根据辅助索引查找——>聚集索引——>查找索引（目标）
    #非聚集索引——索引和指向数据不存放在一起
        辅助索引——>目标索引，没有谁是关键的索引

Paste_Image.png Paste_Image.png

索引管理的途径

索引管理的途径
  创建索引，创建表时设定，命令
  创建或删除索引，通过修改表的命令
    mysql
      HELP CREATE TABLE: create_definition
      HELP ALTER TABLE: ADD  增加  DROP 删除   
  删除索引
    DROP INDEX
     HELP CREATE INDEX: 
        不加索引 [选项] 就是指B-TREE索引
        使用CREATE创建索引，必须使用Index_name
        使用ALTER TABLE添加时，无需指定Index_name
        ON tbl_name(指明在哪个表) index_col_name(指明在哪些字段，单个字段——单节字段/简单索引，多个字段——组合索引/符合索引)
        指明长度length、降序ASC、升序DESC
      HELP DROP INDEX
索引没必要修改，否则整个索引都要重新建立        
创建索引，需要几分钟或几十分钟，千万不要对线上系统更新索引，代价太大，很多产品构建在现有的索引上，你一重建索引，数据库需要做原表扫描，需要时间，数据库会瞬间卡死，很多用户的请求就要等待此；
    退出
        bye
        quit
        ;
        quit

lftps
cd Files/
mget hellodb.sql
bye

mysql < hellodb.sql
mysql
SHOW DATABASES;
use hellodb;
SHOW TABLES;
SELECT * FROM classes;
SELECT * FROM courses;
SELECT * FROM students;

HELP SHOW INDEX
    查看索引的命令
SHOW INDEXES FROM students;
    创建表时，默认有主键，且建立在用户的id之上
EXPLAIN SELECT * FROM students WHERE(指明条件) StuID=3\G
    EXPLAIN 分析而不是真正的执行语句，分析"查询语句"在执行过程中，是否用到了索引，以及如何实现数据获取的；
    显示的是一个近似结果
    查询时，可能用到了主键
    PRIMARY——主键
    type: const——1对1查询
    type: all——全表扫描(耗费内存资源)
EXPLAIN SELECT * FROM students WHERE(指明条件) Age=53\G
    年龄是53的有很多，所以会耗费内存资源
如果经常根据年龄来查询，那么可以根据年龄来创建一个索引；
    创建索引的方法：HELP ALTER TABLE  或者 CREATE INDEX 
    ALTER TABLE tbl_name(指明表名) ADD INDEX [index_name][index_type](二者可省) index_col_name(指明添加索引的字段，用括号括起来即可)
    ALTER TABLE students ADD INDEX(age);
SHOW INDEXES FROM students;
    索引类型默认是BTREE
EXPLAIN SELECT * FROM students WHERE(指明条件) Age=53\G
    此时，在查询，就可以节省很多资源
    type: ref 不是const，可能已经参与了很多的搜索，需要有整体概念，才会理解；
记住一点：
    索引很重要——对查询来讲；

在添加一个索引，创建在名字之上
desc students; (描述students)
CREATE INDEX name(索引名) ON(指明表名) students (Name)(在这个表的哪个字段创建)
SHOW INDEXES FROM students;
SELECT * FROM students WHERE Name LIKE(条件) 'X%';
    搜索以X开头
EXPLAIN SELECT * FROM students WHERE Name LIKE 'X%';
EXPLAIN SELECT * FROM students WHERE Name LIKE 'X%'\G
    type: range 范围查询
EXPLAIN SELECT * FROM students WHERE Name LIKE '%X%'\G（多加一个%，导致索引无效，坑）
    type: all (全表扫描)耗费资源

视图 VIEW

虚表  
  存储SELECT语句，并针对此语句做SELECT查询或者修改
  针对的不是物理表，而是被SELECT虚化后的表
HELP CREATE VIEW
CREATE VIEW view_name [column_list](字段) AS select_statement
  把select语句的查询结果（select_statement）当做一个表，表的名字叫做view_name，事实上此表并不存在，
  MySQL的视图功能相当不完善，能不用就不用
  先SELECT查询，得到结果，再SELECT搜索这个SELECT的结果，效率低
    物化视图 把一个实实在在的结果存下来，然后对其做查询
CREATE VIEW test AS SELECT StuID,Name,Age FROM students;
  从students表里查询StuID,Name,Age的结果作为test视图
SHOW TABLES;
  虽然是虚表，但也可以查询到test
SHOW TABLE STATUS LIKE 'test'；
SHOW TABLE STATUS LIKE 'test'\G
  所有内容都是空的NULL，只有Comment:VIEW(表名)——虚表的特征
  SHOW TABLE STATUS LIKE 'students'\G 注意区别二者的不同
SELECT * FROM test
SELECT * FROM test WHERE Age=22; 可以显示出年龄为22的内容
EXPLAIN SELECT * FROM test WHERE Age=22;
EXPLAIN SELECT * FROM test WHERE Age=22\G
  table: students 查询test表，实际是students表
删除视图
HELP DROP VIEW
  DROP VIEW view_name
视图是否可以被修改——取决于基表的约束
视图中的数据，事实上存储于"基表"中，其修改操作针对基表实现；其修改操作受基表限制；
SELECT * FROM test WHERE Age=22;
SELECT * FROM students WHERE Age=22; 基表
此时，插入数据到视图表里，就是插入到其对应的基表里
  基表要求需要非空数据，就不能插入此数据
    如果不要求非空，就可以插入此数据，只不过内容为空而已

Paste_Image.png

DML

增删查改
INSERT,DELETE,UPDATE,SELECT

INSERT
  一次插入一行或者多行数据；
  多行数据需要插入——使用一次插入多行，只触发一次索引更新
HELP INSERT
INSERT tbl_name col_name VALUE (所有字段，指明值)
  col_name  字段名，对这些字段进行插入数据；不写就是针对所有字段，都要插入数据
  VALUE  针对所有字段，指明值 {VALUE|VALUES}
  [ ON DUMPLICATE KEY UPDATE] 插入值与现有值相同，更新现有值，不报错
简化格式
  INSERT tbl_name [(col1,...)] VALUE(S) (val1,...),(val21,...)
tbl_name (表名，区分大小写)
INSERT语句可以用replace语句替代，语法几乎相同；
INSERT语句，插入数据和现有数据冲突，会报错
replace语句，插入的数据，如果没有，就插入此行；有则会就替换此行；
格式1
DESC students;
Null : no——不允许为空；yes——允许为空；
INSERT [INTO] students (Name,Age,Gender) VALUES ('Jinjiao King',100,'M');
SELECT * FROM students;
  可以看到增加的内容
格式2
INSERT tbl_name SET col_name1=expr1(值),...
INSERT [INTO] students SET Name='Yinjiao King',Age=98,Gender='M';
SELECT * FROM students;
格式3
INSERT tbl_name [(col_name)] SELECT ...

DELETE:
HELP DELETE
DELETE FROM tbl_name WHERE 条件
    [WHERE where_condition]
    [QRDER BY...]
    [LIMIT row_count] 限制几行
  没有where条件，整个表会被清空，切记
  一定要有限制条件，否则整个表中的数据会被清空

UPDATE
HELP UPDATE
UPDATE table_reference SET col_name1=expr1,...
    [WHERE where_condition]
    [QRDER BY...]
    [LIMIT row_count] 
  一定要有限制条件，否则修改所有行的指定字段

SELECT DQL
  重要的组件Query Cache;
      某些场景下有用；
        非确定性查询语句select now(系统函数，显示当前时间)，每次查询时间都不同
查询缓存未命中——>解析器

查询执行路径中的组件：
  查询缓存、解析器、预处理器、查询优化器、查询执行引擎(过滤)、(IO)存储引擎
速度慢  缓存命中，效率快
MySQL服务器不止一台，都可以执行查询操作，提高缓存命中率的方法    (写操作)
  前端显示，有多台服务器可以供用户使用，
  1 多台前端服务器，可以基于相同的算法，此时，同一个语句必然发送到同一个MySQL服务器
    一致性hash算法，是基于查询语句(myaql)或者URL做了粘性的
    粘性在一定程度上损害了负载均衡的效果
  2 引入第三方的公共缓存来使用(mamercat)
    可以尝试关掉MySQL自己的缓存功能，我们从mysql查询到的结果，
      由应用程序自行存在mamercat当中，任何一个服务器在查询任何语句时，
      都发往mamercat服务器中来看缓存是否命中；这样可以避免MySQL的交互
    mamercat的实现，仅仅是基于内存实现做缓存的，在缓存响应方面，会更快；
    如果只有一个MySQL服务器的话，那么mamercash是没有必要的，因为MySQL自己的缓存性能会更好
    多个MySQL服务器，需要引入多个mamercash(一个扛不住的)，此时需要引入一致的hash算法；
具体如何应用，要看实际需要
  使用MySQL自己的缓存，但是缓存能力有限，究竟缓存哪些数据，不缓存哪些数据，需要考虑到MySQL自己内置的缓存功能
  如果进入第三方的公共缓存，把两种角色分开，事实上，就可以减轻MySQL的压力——>缓存功能有专置的缓存服务器来负责完成
  将来做MySQL的主从复制以后，MySQL节点肯定也是不知一个；(？)

MySQL多个主机(服务器)，考虑负载均衡
  提高缓存命中率，就要进行一致性hash算法或者其他算法的实现

前端——1_负载均衡器(专门MySQL的查询路由)_2——多台MySQL server
负载均衡器(专门MySQL的查询路由)
  此设备节点需要符合：
      把前端应用程序的读请求负载均衡到多台MySQL主机；
      把写请求仅仅发给主服务器；
      主节点能写，从节点只读；
   此时，前端不能在做一致性hash算法，只能在负载均衡器上做hash算法；
      要在此节点上做hash算法，需要对此服务器所实现的查询路由软件程序做改进，自行写代码实现相关功能；
        否则，只能使用mamercash，为多台MySQL服务器的查询结果做缓存
    如果mamercash有缓存，mysql自己的缓存也可以用（两级缓存）
    我们程序在对应的mamercash缓存数据时，都有其ttl（存活周期），ttl时间一到，缓存失效；
      但是对于mysql server而言，原来的缓存仍可能有用；如果我们仍然开启缓存，会导致缓存的命中率不高；因为同一个语句可能会被调度至任何一个查询服务器上去；
  

Paste_Image.png

后端主机的建构形式
mysql的读写分离器
负责将INSERT等的写操作发往主节点，读操作发往从节点

Paste_Image.png

SELECT语句的运行
select查询后——分组——过滤
条件
查询函数
查询到的结果——分组——显示其一部分

SELECT语句的完整用法

Paste_Image.png

mysql的SELECT语句
流程
FROM Clause——>WHERE Clause——>GROUP BY——>HAVING Clause——>ORDER BY——>SELECT——>LIMIT
FROM 从哪个表完成查询操作
SELECT 决定挑选字段
WHERE 选择  挑选符合条件的行
SELECT 投影

MYSQL的单表查询
  HELP SELECT (SELECT命令)
  修饰符
    DISTINCT 去除重复的内容  数据去重
      SELECT * FROM students;
      SELECT Gender FROM students; 性别
      SELECT DISTINCT Gender FROM students; M F 
    SQL_CASH  显示指定存储查询结果于缓存之中
    SQL_NO_CASH 显示查询结果不于缓存；
SHOW GLOBAL VARIABLES LIKE 'query%';
    query_cache_type的值为ON时，查询缓存功能表示打开了（不想用可以改为OFF）；SQL_CASH才会有用
    SELECT的结果符合缓存条件即会缓存，否则不予缓存；
    query_cache_type的值为DEMAND时,查询缓存功能按需进行；
     显示指定SQL_NO_CACHE，不予缓存；
    qiery_cash_size 缓存空间，如果为0，不予缓存；
    SELECT SQL_CACHE  表示缓存  不带SQL_CACHE的不缓存
    显示指定SQL_CACHE的SELECT语句才会缓存；但不一定会缓存，需要符合自己的常见缓存条件，才会缓存；其他均不予缓存；

MySQL的缓存命中率低，即使开启缓存，效率也会低，
查询命中的次数/查询的总次数(命中次数+未命中次数)=命中率；
命中率低不是没有用，如web缓存——稳当命中率+字节命中率

SHOW GLOBAL STATUS LIKE 'Qcache%';Qcache_hits 命中次数
SHOW GLOBAL STATUS LIKE 'Com_se%';Com_select 查询次数
缓存遇到缓存之前，缓存率低不代表事实就是这样，不理智

Paste_Image.png Paste_Image.png

SELECT语句的使用
字段显示可以使用别名  表中的内容未被修改，只是在显示时被修改

Paste_Image.png Paste_Image.png Paste_Image.png Paste_Image.png

WHERE
IN 多值等于 或的关系
LIKE 模糊比较 通配符、（模糊匹配）表达式（最好不用）
RLIKE 支持正则表达式 索引无法使用（最好不用）
逻辑操作符
  满足一个条件还是多个条件
  XOR 异或 二者不同为真，相同为假
MySQL内置的函数 比较操作 可能用到

Paste_Image.png Paste_Image.png Paste_Image.png Paste_Image.png Paste_Image.png Paste_Image.png Paste_Image.png Paste_Image.png

GROUP

Paste_Image.png

求男女同学的平均年龄

Paste_Image.png

分组并聚合

Paste_Image.png

平均年龄大于20的
定义别名更为直观，尤其做函数运算；

Paste_Image.png

每个班有多少人
根据班级分组——计算班里人数（计算行数）——count统计
count * 执行性能最差，最好选择主键所在的字段做统计

Paste_Image.png Paste_Image.png

ORDER BY

Paste_Image.png Paste_Image.png Paste_Image.png Paste_Image.png

LIMIT 对显示的结果做限制
offset 偏移量

Paste_Image.png Paste_Image.png Paste_Image.png Paste_Image.png

写锁 不能写，不能读
读锁 可以读

Paste_Image.png Paste_Image.png

mysql < hellodb.sql
1  SELECT Gender(MAN) FROM students GROUP BY Age>25;(错误)
    SELECT Name,Age FROM students WHERE Age>25 AND Gender='M';
2  SELECT avg(Age),ClassID FROM students GROUP BY ClassID;
    SELECT avg(Age),ClassID FROM students WHERE ClassID IS NOT NULL GROUP BY ClassID ;
3  SELECT avg(Age) as AAge,ClassID FROM students GROUP BY ClassID HAVING AAge>30;
    SELECT avg(Age),ClassID FROM students WHERE ClassID IS NOT NULL GROUP BY ClassID HAVING avg(Age) > 30;
4  SELECT * FROM students WHERE Name LKIE 'L%';
5  SELECT * FROM students WHERE TeacherID IS NOT NULL;默认为非空？
6  SELECT * FROM students ORDER BY Age DESC LIMIT 10; 
7  SELECT * FROM students GROUP BY 20<=Age<=25;
    SELECT * FROM students WHERE Age RLKIE 'Age>=20 && Age<=25';
    
   SELECT * FROM students WHERE Age >=20 AND Age <=25; 
   SELECT * FROM students WHERE Age BETWEEN 20 AND 25; 
   SELECT * FROM students WHERE Age IN (20,21,22,23,24,25);

查询缓存

Paste_Image.png

缓存——键值（缓存键）——hash表——再次查询，每个语句做hash计算——查找表里是否有与其对应的条目
查询缓存 
    MySQL不会解析、正则和参数化我们的查询语句
    直接使用SQL语句和客户端发来的语言信息
    存放在内存中，对整个内存空间的分配、回收等，也会额外的产生系统资源消耗
    调整合适的值=调整CPU和消耗内存浪费 资源消耗和收益的平衡折中（消耗~收益）

提高缓存使用率

quire_cash_size 一旦调整，整个缓存空间必须重新进行分配，即整个缓存都会失效
  尽量不要调整，不要频繁改变

Paste_Image.png Paste_Image.png

Com_select
  不是记录了所有的查询语句
  MySQL执行的查询语句
  free block 内存空间被分割成block以后，仍处于空闲阶段的块
  缓存的内存空间不一定连续

quire_inserts 可缓存查询语句的结果，被放入缓存的次数
quire_lowmem_prunes 缓存空间（quire_size）太小导致清理缓存（利用lru算法清理）
qcashe_queries_in_cache 在当前的缓存空间里被缓存下来的查询的个数
  16M——1000个查询
qcashe_total_blocks 整个查询缓存共有多少个内存块（单元）
  是内存区段
  不是分割后能够缓存内存查询结果的内存块

查询缓存 
  不是所有情况下都有效
  物理服务器CPU、物理核心数很多
  查询缓存可能成为瓶颈和单点  如果是，就要把查询缓存关闭
  只要有用，就要开启，节约时间多
  使用mamercash缓存，MySQL自己的缓存就不太重要，可以根据情况关闭

索引和explain

索引 ~ 键
数据库越大，索引越重要

Paste_Image.png

SELECT * FROM students WHERE Age+20>50
避免左侧(Age+20)参与布尔运算
mysql中char——长度固定(以字符长度为索引)与varchar——不固定
varchar、text——左前缀(左侧多少个字符)

多列索引
服务器对多个索引做相交操作 AND..(条件)AND...组合条件索引
联合操作 ALL
耗费大量的CPU和内存资源在算法的缓存排序上 单键索引

inodb 聚集索引
MyISAM表 不支持聚集索引
聚集索引  2次查询 非聚集索引——>聚集索引（索引+数据）  性能好
覆盖索引  查询、索引  查询索引，直接返回结果，不访问元数据  降低IO次数

Paste_Image.png Paste_Image.png

explain
单表查询 1行
两表连接查询 2行
uninue查询 3行
maridb——explain extend、explain partitions(分区)

Paste_Image.png Paste_Image.png Paste_Image.png Paste_Image.png Paste_Image.png Paste_Image.png

all 效率低
index 有序，但产生大量的随机IO
null 执行阶段不用访问索引，

const 固定值=常量

Paste_Image.png Paste_Image.png

事务

Paste_Image.png Paste_Image.png Paste_Image.png Paste_Image.png

事务隔离级别

Paste_Image.png Paste_Image.png

READ COMMITTED 并发性会提高，但是数据复制——不一致

级别	线程1	线程2
READ UNCOMMITTED	更改数据	读到新数据
READ UNCOMMITTED	回滚	读到原数据
READ COMMITTED	读到原数据	更改数据
READ COMMITTED	读到新数据	更改数据+提交
REPEATABLE READ	更改数据	读到原数据
REPEATABLE READ	更改数据+提交	读到原数据（幻读）
REPEATABLE READ	更改数据+提交	提交——读到新数据
SERIALIZABILE	访问阻塞	更改数据
SERIALIZABILE	访问阻塞	更改数据+提交

2个线程执行相同的操作

Paste_Image.png

事务日志

提高事务效率
 使用事务日志存储引擎修改表中数据，只需修改内存拷贝，并记录事务日志中(追加)，避免修改数据文件
事务日志2个或3个，不要太大（崩溃后恢复，不会耗费太长时间）（1、2、3）——事务日志组
  先用日志1，1存满了，就及时将其存储于数据文件里，再用2，...，3满了，再用1

逻辑卷——对事务日志和事务文件——快照和备份——事务日志和磁盘必须放在一起
其他——事务日志和磁盘可以不放在一起

InnoDB有很多变量
崩溃后，回复时间——取决于事务日志的数量和文件大小
事务日志——存储引擎
InnoDB的参数——调整事务日志的位置和大小

编译MySQL

服务器特性
  服务器、存储引擎各占一半
独特的存储引擎——自己手动编译MySQL

yum info cmake
yum install cmake
yum grouplist
yum install -y "Development Tools" "Server Platform Development"


下载源码
www.mariadb.org
lftps; cd Sources/sources/mariadb/; ls
mget mariadb-5.5.44-...; bye; ls
groupadd -r(系统) -g(gid) 306 mysql
useradd -r -g 306 -u 306 mysql; id mysql; ls
tar xf mariadb-5.5.44-...; cd mariadb-5.5.44; ls 源代码文件
此处步骤：如下图
make 比较耗时; fdisk /dev/sda——...+20G/8e/;如下图 （创建逻辑卷）
make install

Paste_Image.png

创建逻辑卷

Paste_Image.png Paste_Image.png Paste_Image.png Paste_Image.png

make install完成后执行的操作

Paste_Image.png Paste_Image.png Paste_Image.png Paste_Image.png Paste_Image.png

回顾：执行某些操作
第一次访问，应该运行；
给所有root用户设定密码，删除匿名用户的操作；

Paste_Image.png Paste_Image.png Paste_Image.png Paste_Image.png

heap——memary

Paste_Image.png Paste_Image.png

centos7 编译安装

rpm -qa|grep mariadb  查询是否安装
yum remove mariadb  如果安装，则删除
    localectl set-locale LANG=en_US.UTF-8  把x-shell的汉语转换为英语
yum -y groupinstall "Development Tools"
yum install ncurses-devel openssl-devel libevent-devel jemalloc-devel cmake
下载mariadb的源码包
    tar xf mariadb-5.5.46-...; cd mariadb-5.5.46/; 
cmake ...
make && make install
vim /usr/lib/systemd/system/mariadb.service
    粘贴内容（如下图）
systemctl daemon-reload

Paste_Image.png Paste_Image.png Paste_Image.png Paste_Image.png Paste_Image.png Paste_Image.png Paste_Image.png Paste_Image.png

备份工具

备份策略——面试的话题
数据要备份多份~本地、自己、云端
MySQLdump~MySQL自带~几十到几百M~逻辑/物理备份都可以
几十到几百G~物理备份~文件系统级别、复制文件方式、实现备份
xtrebackup~并行、多数聚集同时备份、远程复制~

Paste_Image.png

备份是手段，恢复数据才是目的
mysqldump~备份程序文件~不能备份二进制文件
完全备份+二进制日志文件备份
  二进制日志文件名、事件位置
  change master  指明从主服务器的哪个二进制文件读取事件位置
  二进制文件~线上修改，失效~不能手动删除~如何删除，后面阐述

磁盘设备
  RAID设备
  RAID1-0(有冗余能力)~4块磁盘~存放数据文件
  RAID1~2块磁盘~存放二进制日志文件
  InnoDB的事务日志~服务器启动后，崩溃恢复~存放于非数据磁盘或者数据磁盘

mysqldump + 二进制日志文件
  一周~~做一次完全备份
  每半天~~做一次复制二进制文件（滚动一次）~~增量备份
  完全备份
  二进制日志文件按顺序导出所有内容，恢复到数据库~~
  导出第1次的所有内容，恢复到数据库~~
  导出第2次的所有内容，恢复到数据库~~
  一直到所有增量备份完成~~
  取出最后一个增量备份以后新生成的二进制日志文件中的事件~~
  再次replay到数据库

物理备份

commit——不支持热备和温备
逻辑卷、xtra...——几乎热备
逻辑卷——硬件的基础之上，加上软件，硬件损坏或者误删除，——很难恢复
B-TREE——试验阶段

主服务器出故障，备份文件需要还原至备用服务器，如果二进制日志开放状态，还原时，是否关闭；
备份文件还原至备用服务器的过程：是一个写操作，产生二进制事件，一般没有必要，建议关闭；
关闭方式——更改会话级别即可——如下图

Paste_Image.png Paste_Image.png Paste_Image.png

必须在同一个会话——即同一个窗口（复制，再开一个窗口，不可以）
source /root  不行，当前运行mysql的用户是mysql，
  服务器端无权限读管理员家目录的文件，所以最好放在/tmp目录下；

Paste_Image.png Paste_Image.png Paste_Image.png

备份方式：逻辑卷 + 快照
lvm2 + cp/tar

基于lvm2，创造条件

Paste_Image.png Paste_Image.png

可以再mkdir 一个文件夹，用于存放二进制文件，这里不再演示
/data/mysql——存放逻辑卷
fdisk /dev/sda  创建逻辑卷(7/20G)
partx -a /dev/sda
mke2fs -t ext4 /dev/myvg/mydata 

Paste_Image.png Paste_Image.png Paste_Image.png

还原时，尽量关闭二进制文件

Paste_Image.png

由于此服务器在线上工作，需要对其做物理模式的备份，如何操作

Paste_Image.png

快照之后的修改，
  先将原卷数据复制到快照卷上，再对原卷进行修改，
  快照卷上的数据——原卷未修改——通过原卷访问
  快照卷上的数据——原卷已经修改——通过快照卷上复制的修改之前的文件副本来访问

Paste_Image.png Paste_Image.png Paste_Image.png

为逻辑卷创建快照——目的：备份——大小
  多大合适——用于备份的时间范围内~~所改变的数据量能够存储的下~~未必和原卷一样大
  备份只需3个小时~~3h的数据量，在改变最多的时候，也不会超过1个G~~即创建1个G即可

Paste_Image.png Paste_Image.png Paste_Image.png

开始备份
  挂载快照卷

Paste_Image.png

出错，重新做

Paste_Image.png Paste_Image.png Paste_Image.png Paste_Image.png Paste_Image.png

锁定以后，马上滚动，再改数据
这里没有操作，后续利用二进制日志文件恢复至时间点，就不行了
都是你妈的瞎BB——mage教育
备份数据——从快照备份
备份二进制日志——从原卷备份

Paste_Image.png

以上为主机1
主机2

Paste_Image.png

主机1

Paste_Image.png Paste_Image.png

主机2

Paste_Image.png Paste_Image.png Paste_Image.png Paste_Image.png

备份数据——从快照备份
备份二进制日志——从原卷备份

二进制日志——可以单独放在一个卷上，这个卷无需快照

Paste_Image.png

还原回来的数据要确保数据的属主和属组是否正确 cp -a
执行恢复时，通过重放二进制日志——进行时间点还原，需要把当前的二进制日志关闭

Paste_Image.png

问题
0:00-2:00备份
10:00 误操作
2h之后才意识到出错
解决
备用服务器——备份还原
二进制日志文件——导出至文本文件————从文本找到误操作后，删除——replay即可

xtrabackup
percona,www.percona.com
percona-server/oracle-mysql/mariadb
InnoDB(MYSQL)——>XtraDB
    Xtrabackup