mysql8.0

全角度解读企业主流数据库MySQL8.0-1

关系数据库的特点：数据结构化，存储在二维表中。
支持事务的原子性A,一致性C，隔离性I。持久性D.
支持使用sql语言对存储在其中的数据进行操作

关系型数据库的适用场景
数据之间存在着一定的关系，需要关联查询数据的场景
需要事务支持的业务场景
需要使用sql语言灵活操作数据的场景
非关系型数据库的特点
存储结构灵活，没有固定的结构
对事务的支持比较弱，但对数据的并发处理性能高。
大多数不使用sql语言操作数据
非关系型数据库的适用场景
数据结构不固定的场景
对事务的要求不高，但读写并发比较大的场景
对数据的处理操作比较简单的场景
关系型数据库选型原则
数据库使用的广泛性
数据库的可扩展性
数据库的安全性和稳定性
数据库所支持的系统
数据库的使用成本
mysql数据库的可扩展性
支持基于二进制日志的逻辑复制
存在多种第三方数据库的中间层，支持读写分离及分库分表。
mysql的安全性和稳定性
mysql主从复制集群可达到99%的可用性
配合主从复制高可用架构可以达到99.99%的可用性
支持对存储在mysql的数据进行分级安全控制。
支持的系统，liunx windows。
mysql 的使用成本
数据库结构设计
业务分析—>逻辑设计—>数据类型----->对象命名---->建立库表
宽表模式存在的问题
数据冗余：相同的数据在一个表中出现了多次
数据更新异常：修改一行中某列的值时，同时修改了多行数据
数据库插入异常：部分数据由于缺失主键信息而无法写入表中
数据的删除异常：删除某一数据时不得不删除另一数据

宽表模式的应用场景
配合列存储的数据报表应用
数据库的设计范式
第一范式：表中所有字段都是不可再分的
第二范式：表中必须存在业务主键，并且非主键依赖于全部业务主键
第三范式：表中的非主键列之间不能相互依赖
物理设计
mysql常见的存储引擎
MYISAM 不支持事务 5.6之前的默认引擎
CSV 不支持事务 以csv格式存储的非事务型存储引擎
Archive 不支持事务 只允许查询和新增数据而不允许修改的非事务型存储引擎
Memory 不支持事务 是一种易失性非事务型存储引擎
INNODB 支持事务 最常用的事务性存储引擎

INNODB存储引擎的特点
事务型存储引擎支持ACID
数据按照主键聚集存储（建议使用自增id）
支持行级锁及MVCC（mvcc：多版本的并发控制，进一步的避免读写操作的互相阻塞）
支持Btree和自适应的Hash的索引
支持全文和空间索引




如何选择合适的数据类型
优先选择符合存储数据需求的最小数据类型
谨慎使用ENUM.TEXT字符串类型
同财务相关的数值类型，必须使用decimal类型。
zerofill 不足两位时补0
如何为表和列选择适合的名字

1. 所有的数据库对象名称必须使用小写字母可选用下划线分割。
2. 所有数据库对象名称定义禁止使用mysql保留的关键字。
3. 数据库对象的命名要能做到见名知意，并且最好不要超过32个字符。
4. 临时库表必须以temp为前缀并且以日期为后缀。
5. 用于备份的库，表必须以bak为前缀并且以日期为后缀。
6. 所有存储相同数据的列名和列类型必须一致。
   初识SQL
   sql语言的作用对存储在rdms中的数据进行增删改查等操作，
   常用的sql语言的种类：
   DCL(DATA CONTROL LANGUAGE),
   创建数据库 create user、
   create user mc_test@‘192.168.1.@’ identified by ‘123456’
   with max_user_connections 1;
   创建用户mc_test 限制网段，设置密码为123456，最大连接数是1
   对用户进行授权 grant 收回权限 revoke
   
   
   
   

DDL(DATA DEFINITION LANGUAGE),


create database imc_db;


rename table tablename to baktablename;
truncate table tablename ;
创建唯一索引：create unique index uqx_classname ON tablename(col_name);
DML(data manipulation language).





group by …having 子句的作用：

1. 把结果集按某些列分成不同的组，并对分组后的数据进行聚合操作。 （所有出现在select语句中的聚合函数的列，都必须出现在group by
   子句后）.。

2. 可以通过可选的having子句对聚合后的数据进行过滤。

set session sql_mode =’ ONLY_FULL_GROUP_BY’;
对于GROUP BY聚合操作,如果在SELECT中的列,没有在GROUP BY中出现,那么这个SQL是不合法的,因为列不在GROUP BY从句中



SELECT col1,col2
FORM tablename
ORDER BY col1 DESC
LIMIT 0 10;
从表中查数据 按照col1排序，对结果进行分页，从0开始，每页返回10条。

select colname,
case when sex=1 then ‘男’，
when sex=0 then ‘女’
else ‘未知’
end as ‘性别’
from tablename





UNION ALL是将所有选择语句连接起来的关键字。
PARTITION BY 分割。


sql优化的一般步骤
发现问题，分析执行计划，优化索引，改写sql，数据库的垂直切分 ，数据库水平切分。



show variables like ‘long_query_time’;
显示sql执行时间。
set global long_query_time =0;
设置时间为0
show variables like ‘slow_query_log’;
查看状态
show variables like ‘slow_query_log_file’;
查看目录
set global slow_query_log=on;
启动慢查询。




索引的作用
告诉存储引擎如何快速的查找到所需要的数据。

btree索引的特点
btree索引适用于全值匹配的查询
btree索引适合处理范围查找
btree 索引从索引的最左侧列开始匹配查找列

应该在什么列上建立索引

查看sql 的执行计划 explain

创建索引
create index idx_regtime on tablename( colname);

脏读：一个事务读取了另一个事务的未提交的数据。
不可重复度：一个事务前后两次读取的数据不一致。
幻读：指一个事务两次查询的结果集记录数不一致。

INNODB默认的隔离级别是可重复读。

查看事务的隔离级别：show veriables like ‘%iso%’;
启动事务：begin;



kill 20；

什么是死锁？
并行执行的多个事务相互之间占有了对方所需要的资源，（mysql内部对死锁进行监控并进行处理，主动回滚两个事务中占用资源较少的事务。让另一个事务继续执行，）

如何发现死锁？
set global innodb_print_all_deadlocks=on;