Mysql

Great Wei

文章标签

首页

归档

标签

友人帐

2019-04-01

5 min read

mysql

Mysql

数据库基础

基础概念定义

SQL语言划分4个部分:

DDL(Data Definition Language) ，用来定义数据库对象，数据库表
DML(Data Manipulation Language), 数据库操作语言，操作数据库的相关数据，比如增删改
DCL(Data Control Language), 数据库控制语言，用来定义访问权限和安全等级 if grande等
DQL(Data Control Language), 数据查询语言, 用来查询数据select

SQL查询过程(Mysql8.0以下，如果设置了查询缓存，可能出现权限不足依然查询到数据情况)

Mysql架构组成

连接池组件(Connection Poll)
管理服务和工具组件(Enterprise Management Services & Utilities)
SQL接口组件(SQL Interface)
查询优化器(Parser)
优化组件(Optimizer)
缓存组件(Cache & Buffer)
插件式存储引擎(Pluggable Storage Engines)
物理文件(File System, Files & Logs)

存储引擎

InnoDB 存储引擎：Mysql 5.5版本后默认的存储引擎，优点是支持事务，行级锁(只作用在索引)，外键约束，支持崩溃后的安全恢复；
MyISAM 存储引擎：不支持事务和外键，支持全文索引（但只对英文有效），特点是查询速度快；
Memory 存储引擎：数据放在内存当中（类似memcache）以便得到更快的响应速度，但是崩掉的话数据会丢失；
NDB 存储引擎：主要用于Mysql Cluster分布式集群；
Archive 存储引擎：有很好的压缩机制，用于文件归档，写入时会进行压缩；

事务

事务是应用程序中一系列严密的操作，所有操作必须成功完成，否则在每个操作中所作的所有更改都会被撤消。也就是事务具有原子性，一个事务中的一系列的操作要么全部成功，要么一个都不做。
ACID：原子性，一致性，隔离性，持续性

四种隔离级别

可重复读(可以查看其他事务已提交的插入数据，会导致幻读)
可串行化(最高隔离级别当事务读取相同数据需要按照事务执行顺序执行)
读未提交(可以查看到其他事务未提交的数据内容，导致脏读)
读已提交(可以查看到其他事务提交的数据内容，导致不可重复读)

架构图

架构图：如有问题请指出

数据库迁移命令

mysqldump -uroot -ppassword -h172.xxx.xxx.xx  > back.sql 数据库导出
mysql -uroot -ppassword -h172.xxx.xxx.xx 连接数据库
source back.sql 执行sql文件

Mysql版本导致的sql查询异常

一个简单的需求，查询数据中如果 a = 0 则按照 x字段降序， 如果 b = 0 则按照 y字段降序。
问题sql:
select * from 
(select * from tableA where a = 0 order by a desc) A 
union 
select * from (select * from tableB where b = 0 order by b desc ) B

如果在5.6版本mysql可以得到预期结果,5.6版本以上order by会被优化器过滤,导致查询结果没有排序效果。
这居然还是官方的优化- -！,还有一点 orderby 和 union 同时使用的时候，order by 需要放入子查询语句中。

解决方法：
必须在子查询中order by 之后添加limit条件, 还有子查询使用order by  排序字段必须被select。

正确sql:

select * from 
 (select * from tableA where a = 0 order by a desc limit 1000) A 
 union 
 select * from (select * from tableB where b = 0 order by b desc limit 1000) B

sql 查询索引失效

在日常开发中，有许多问题是由于数据库索引没有建立好，与sql写法问题导致索引失效，当我们服务出现了接口压力时，分析原因，如果是由于数据库耗时原因的话，我们应该分析一下sql，获取日志的sql后，执行explain “需要执行的sql” 根据KEY字段判断索引是否有效。

多张分表联合查询出现bug

上线代码过程中，发现日志一直对一段正常的逻辑代码报错，排查后发现填充的字段数据有问题，于是去数据库查询后发现数据并没有问题，逻辑也是正常的，不断的排查过程中，发现是sql查询的结果有问题，copy sql执行后，发现查询出来的数据的确有问题，其中有一个字段的数据与其它字段数据进行了交换，之后排查发现两张分表的表字段顺序是不相同的，导致发生了这样的一个bug。