MySql

 mysql详解

mysql详解
数据库是什么

数据库是按照结构化组织存储和管理数据的仓库。

数据库的分类

关系型数据 和非关系型数据库

关系型数据库

数据有固定的存储结构 按照库–表--行–列的方式存储，

存储过程 解析sql语句—连接层—磁盘存取—结构化成表。

非关系数据库

以 键值对方式存储数据，每一个元组都可以有不一样的字段，这种就不会局限于固定的结构。尽可能的将数据放到内存当中；

mysql 是什么

mysql是一个关系型数据库管理系统，

SQL 是什么

是数据库 结构化语言 。

什么是表

表是数据库中的数据组成单位，

表的引擎

innoDB 支持数据库的高级处理包括 事物 外键等 默认是innodb

主键

特性：值 不可以为空 也不可以重复，通常用于公司员工的ID号

事务

关系型数据库 支持事务 innoDB 引擎支持

成批的SQL语句操作，要么完全执行，要么不执行，维护数据的完整性。

事务特性

A　C　I　D
原子性
执行事务要么成功 要么失败，只要有一条失败整个事务机会是失败。

一致性
事务执行前是一致的，执行后也是一致的不能破坏数据上的完整型，
比如A用户有1000RMB，B用户也有1000RMB，A用户给B用户转500，那么A用户和B用户的RMB的总额为多少？2000

持久性
事务执行成功后对数据库的修改是永久的。

隔离性
隔离可以防止多个事务并发时导致数据的不一致
 

事务并发不进行事务隔离

脏读

不可重复读

幻读

事务隔离级别

MySQL　默认　可重复读

串行化　避免脏读　不可重复读　幻读

可重复读　避免脏读　不可重复读

触发器

对一个数据库执行了什么操作，然后自动触发了预先写好的的SQL语句。

作用：起到对数据存储的约束。

存储过程

若干条SQL语句写到一起，类似函数。

MySQL的数据备份方式

MySQLdump工具备份　　MySQL官方的　自带的

xtrabackup工具备份　　　第三方工具

备份分为：冷备　温备和热备

备份的数据集合为　完全备份　增量备份和差异备份

冷备份(脱机备份) :是在关闭数据库的时候进行的
热备份(联机备份) : 数据库处于运行状态，依赖于数据库的日志文件
温备份:数据库锁定表格(不可写入但可读)的状态下进行备份操作
逻辑备份:对数据库逻辑组件(如: 表等数据库对象)的备份
完全备份:每次对数据库进行完整的备份
差异备份:备份自从上次完全备份之后被修改过的文件
增量备份:只有在上次完全备份或者增量备份后被修改的文件才会被备份

需要备份的对象： 

数据
配置文件
二进制日志

公司数据备份策略 

每星期一和周四晚上12点进行完全备份，

星期二、星期三、星期五、星期六、星期日每日12:00 进行差异备份；

每月10日在测试环境进行备份可用性恢复测试。

MySQL索引 

MySQL的读写 比例是10:1 左右 想要加速查询 就需要索引了

索引相当于字典的音序表，如果要查某个字，如果不使用音序表，则需要从几百页中逐页去查。

本质都是：通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。

索引分类

普通索引index :加速查找
唯一索引
    主键索引：primary key ：加速查找+约束（不为空且唯一）
    唯一索引：unique：加速查找+约束 （唯一）
联合索引
    -primary key(id,name):联合主键索引
    -unique(id,name):联合唯一索引
    -index(id,name):联合普通索引
全文索引fulltext :用于搜索很长一篇文章的时候，效果最好。

MYSQL优化 

1.添加 索引 
2.添加 主键和外键
3.使用 InnoDB 引擎
3.做集群
4.优化SQL语句

SQL性能优化

1. 尽量全值匹配
2. 最左前缀原则,在使用联合索引的时候要特别注意最左前缀原则，即查询从联合索引的最左前列开始并且不跳过索引中的列。
3.  不在索引列上做任何操作(计算、函数、(自动/手动)类型转换)，会导致索引失效而转向全表扫描
4. 存储引擎不能使用索引中范围条件右边的列
5. 尽量使用覆盖索引，减少select *语句
6. MySQL 在使用不等于( != 或者 <> )，not in，not exists 的时候无法使用索引会导致全表扫描
7. is null 和 is not null 一般情况下也无法使用索引
8. like 以通配符开头(’%itwxe…’) MySQL 索引失效会变成全表扫描操作
9. 类型不匹配 MySQL 自动转型导致索引失效
10. 少用 or 或 in ，用它查询时，MySQL 不一定使用索引

mysql集群 

3M集群介绍

Mysql Monitor agent

3M集群是支持双主故障切换和双主日常管理

虽然叫做双主复制，但是业务上同一时刻只允许对一个主进行写入，另一台备选主上提供部分读服务，以加速在主主切换时刻备选主的预热。

它实现了 mysql的负载均衡和高可用。

架构

两台 Master 公用一个VIP 两台 slaver 一台一个VIP 一台 Monitor

Moniter 是 监控所有MySQL的工作的，决定和处理所有节点角色活动。

工作原理

mysql-mmm的监管端会提供多个虚拟IP（VIP），包括一个可写VIP，多个可读VIP；通过监管的管理，这些IP会绑定在可用mysql之上；当某一台mysql宕机时，监管会将VIP迁移至其他mysql
 

转发:#{}和${}的区别，以及为什么#{}可以防止sql注入_deftiii的博客-CSDN博客_#{}为什么能防止sql注入

一、当我向mybatis输入一条带有#{}的语句时：
select * from user where uid=#{id} and password=#{pwd};
这时数据库就会进行预编译，并进行一个缓存动作，缓存一条这样的语句：
select * from user where uid=? and password=?;
当我们调用这条语句，并实际向#{id}中的id传了一个值 “deftiii” or 1=1# 时，不需要在编译，数据库会直接找对应的表中有没有名字是 “deftiii” or 1=1# 的用户，而不再有编译sql语句的过程。

二、而如果把上述语句中的#{id}换成￥{id},那么就没有了预编译和缓存的过程，向￥{id}里面传值 deftiii or #1=1时，会编译执行
select * from user where uid=“deftiii” or 1=1 #and password=某个密码
，这样#后面的内容被注释掉了，这条语句就相当于
select * from user where uid= “deftiii” or 1=1
即当我们语句中使用${}时，可以无视密码就能查出所有用户信息，这就是sql注入。

原文链接：https://blog.csdn.net/weixin_41399873/article/details/104806374

mysql中ifnull()方法的用法_树叶要走风怎么挽留的博客-CSDN博客_mysql中的ifnull

一般我们在使用ifnull()方法的时候，都是类似下面的语句：

IFNULL(expr1,expr2)
如果expr1不是NULL，IFNULL()返回expr1，否则它返回expr2。IFNULL()返回一个数字或字符串值

select ifnull(name,‘no name’) from person;

将查询到的结果中的null转化为指定的字符串，但是其实在where查询部分也可以使用ifnull():

select * from person where ifnull(name,‘no name’)=‘no name’;

这种使用看起来有点笨，但是在一些应用中可以很好的减少代码量

原文链接：https://blog.csdn.net/weixin_44993313/article/details/105836397

 SQL- case when then else end 用法经验总结

对case when 的理解总结：

1、then和else后，只能写一条输出语句且输出结果就是新生成列的值;when 后的条件判断可以有多条，且可以多个字段联合判断；end 后的输出也可以有多条，但必须有一个是新生成列的字段名； [常规用法：select 后到from前，直接使用case when] (例1)

2、when 后多条件判断用and连接（例3）

3、case when 巧妙用法   [order by 后用case when] [count（）中用case when]   （例2）

4、group by 后用case when   （在参考里）

5、case when，如果有多个，如果一条记录满足第一个case when就不会再去执行后面的case when，所以要想保证某个case when一定执行，就把她写在最前面。

放些栗子：

例1：

case when 的三种用法：

（1）. case 要判断的字段 when  then 输出唯一字段的具体值 else 输出唯一字段的具体值  end 输出唯一字段的名字

select a.*,

case name

when '流浪' then '法师'

else '战士'

end as '类型'

FROM c_20170920 a

 

2. case when 字段，这个可以对字段进行取范围判断。

SELECT a.*,

CASE

WHEN a.age BETWEEN 0 and 20 THEN '青年'

WHEN a.age BETWEEN 20 and 40 THEN '中年'

ELSE '非人类'

END AS '描述'

FROM

c_20170920 a

3. case when 字段1,字段2，可以对多个字段进行判断，并输出唯一结果字段

SELECT a.*,

CASE

WHEN a.age BETWEEN 20 and 80 THEN '青年'

WHEN a.name ='流浪' THEN '帅气'

END AS '描述'

FROM

c_20170920 a

里需要注意的是，如果两个条件都针对一个字段的话，会显示第一个when的值。

比如下面：

SELECT a.*,

CASE

WHEN a.age BETWEEN 0 and 20 THEN '青年'

WHEN a.name ='流浪' THEN '帅气'

END AS '描述'

FROM

c_20170920 a

例2：【巧妙用法】

-- 统计年龄,姓名条件
select count(case age when 10 then age else null end) as age_num,count(case name when '张五' then name end) as name_num from test_list;

-- 统计年龄,姓名条件
select count(case when age between 10 and 30 then age else null end) as age_num,count(case when name='张五' then name end) as name_num from test_list;

-- 排序使用case when 排序

select * from test_list order by case when age>10 then sex end desc;
 

例3：【when 后多条件判断用and连接】

SELECT id,
CASE when reason is null THEN '未填写驳回理由'
ELSE reason
END reason,
time from
(
select
wf.fid id,
CASE
WHEN wf.Fstep = 1 AND wf.Fstatus = 'Reject' and wf.Foper_reason IS NULL THEN '未填写驳回理由'
WHEN wf.Fstep = 1 AND wf.Fstatus = 'Reject' and wf.Foper_reason IS NOT NULL THEN wf.Foper_reason
END reason,
wf.Fcreate_time time
from t_work_flow wf
) result
order by time desc;