李桃峰的技术博客

# MySql慢查询日志

MySql慢查询日志
mySql联合索引最左匹配原则
mySql锁
mySql优化
回到主导航页

# 数据库架构

关系型数据库主要考点：架构、索引、锁、语法、理论范式
理论范式：

  • 一范式：列不可再分，既表的列具有原子性，不可再分解
  • 二范式：表中的记录是唯一的，通常设计一个主键来实现
  • 三范式：去除传递依赖，也就是表中不要有冗余数据，就是说表的信息如果能推导出来，就不应该单独的设计一个字段 来存放

# 假如由你设计一个关系型数据库，你应该怎么设计

设计与模块划分

  1. 存储模块（文件系统）
  2. 程序实例
    1. 存储管理模块
    2. 缓存机制模块
    3. SQL解析模块
    4. 日志管理
    5. 权限划分
    6. 容灾机制
    7. 索引管理
    8. 锁管理

# 存储模块

用于存储我们的数据，存储模块就类似我们的OS文件系统，将数据最终持久化存入磁盘中，如 存入机械硬盘或者SSK固态硬盘

# 程序实例

用逻辑结构映射出物理结构来，并且在程序中提供获取已经管理数据的方式，还需要必要的 问题追踪机制，细分程序实例如下：

# 存储管理模块

我们需要对数据的格式以及文件的分割进行统一的管理，既把物理数据 通过逻辑的形式给组织 和表示出来，于是就设计出了 存储管理 模块
处理数据不可能在磁盘上做，而肯定是让程序加载到程序所在的内存空间里去处理，而磁盘IO速率往往是程序执行速度的主要瓶颈，所以我们要尽量的减少IO，因为一次IO读取一条数据与读取多条数据花费的 时间几乎是等价的，所以我们不能逐行去查找返回，应该一次查询多行，以提升IO的效能，所以数据库每次查询是 以 块 或者 页 为单位读取的，块或者页中会存放多行数据，这样读取的时候可以将多个 块一起加载进内存当中

# 缓存机制

将取出的块或者页，加入到缓存中，有些数据并不一定是这次查询需要的，但有可能下次查询就需要，所以会将相邻的数据以前加载到内存中，缓存不易过大，且需要有淘汰机制，淘汰到一些不常用的数据

# SQL解析模块

将SQL编译解析，换成机器可识别的指令，这个时候为了能提升sql执行效率，可以将sql缓存起来，编译好的sql方便下次直接解析使用。另外执行的sql命令(增删改)要记录下来，方便做主从同步 或者灾难恢复

# 日志管理

执行的sql命令(增删改)要记录下来，方便做主从同步或者灾难恢复，就行blog的记录方式

# 权限划分

根据登录的用户，分配不同的操作权限

# 容灾机制

当我们的数据库挂了，该如何恢复，恢复到什么程度，这些都需要设计

# 索引管理

优化数据查询的索引模块

# 锁模块

使数据库支持并发操作的锁模块

# 索引模块

常见问题：

  1. 为什么要使用索引
  2. 什么样的信息能成为索引
  3. 索引的数据结构
  4. 密集索引和稀疏索引的区别

# 为什么要使用索引

使用索引是为了避免全表扫描，当数据量非常大的时候，使用全表扫描会导致查询非常的慢，所以引入了 索引的概念，索引的概念来源于字典，比如根据 偏旁部首 或者 拼音查找，能很快的查找到某个字。

# 数据表中什么样的信息能成为索引

能把该记录限定在一定的查找范围内的字段就是关键信息。主键、唯一键、普通键都可以成为索引

# 优化SQL语句

  • 不要把SELECT子句写成SELECT * 原因：1 select * 会将所有的字段拿出来，会有更多的IO操作 2 select * 数据库执行的时候 会先查询表结构，然后得到表结构后，把 * 语句改造成 字段名，数据库才能去执行sql语句，这样 不高效
  • 谨慎使用模糊查询，fileName like %A% ,% 在前面，索引是失效的，但是A% ,%分号在后面，索引是会命中的
  • 对ORDER BY 排序的字段设置索引，会大大的加快sql的执行速度 因为索引是一个二叉树的机制，给order by子句的字段加上索引后,排序就非常快了
  • 少用 IS NULL 和 IS NOT NULL 这两个表达式都会让mysql绕过索引，执行全表扫描，因为索引是一个二叉树，NULL值没有办法排序，所以null值是 不会记录到索引里面的，所以跟null值所做的判断都不会走索引，所以设计表的时候，就尽量设置成not null, 可以用 其他的值代替null，比如-1代表null
  • 尽量少用!=运算符 因为索引是二叉树，判断什么等于什么 或者 什么大于什么，这些表达式都可以利用二叉树定位到数据， 但是把条件写成了!=,就没有办法利用二叉树机制了，所以检索数据就变成了全表扫描。可以把 != 条件换一下，比如：!=20,可以换成 <20 and >20
  • 尽量少用 OR 运算符 逻辑 或 运算符 也会跳过索引，可以将 OR 的查询语句优化成如下：
select aname from t_tableName where age=20 or age=30 -- 优化为
1

优化后：

select aname from t_tableName where age=20
UNION ALL
select aname from t_tableName where age=30;
1
2
3

以上两个sql是等价的

  • 尽量少用IN和NOT IN 运算符
select aname from t_tableName where age in (20,30)
1

优化后：

select aname from t_tableName where age=20
UNION ALL
select aname from t_tableName where age=30;
1
2
3
  • 避免条件语句中的数据类型转换 条件语句中的数据类型转换会损耗sql语句的执行速度，例如：字段age是int类型，而写sql的时候 where age='20',这样的话sql会先将字符串转换成int类型，才会执行
  • 在表达式左侧使用运算符和函数都会让索引失效 比如： 使用运算符
-- 查询年薪10万的员工记录
select ename from t_emp where salary*12>=100000
-- 正确的写法为：
select ename from t_emp where salary>=100000/12
1
2
3
4

# MySql数据库读多写少和读多写多

  • 多都写少的业务场景 普遍来说，绝大多数系统都是读多写少的

  • 写多读少的业务场景

比如滴滴打车，就是写多读行的业务场景，当行程开始之后，滴滴app就会将行车记录 数据实时写入到数据库，这样做是为了乘客的安全考虑，但是这些数据很少被查询，只有 在出现事故的时候，才会查询

# 写多读少的解决方案1 - 低价值数据

  • 如果是低价值的数据，可以采用NoSQL数据库来存储这些数据 什么是低价值的数据呢？
    比如：专车的线路坐标，虽然数据很多，但是每条记录的价值并不是很大，如果用mysql 这样的关系型数据库来存储，那么事务上的开销就让我们无法承受，因为在事务机制下 ，写入数据之前都要先写 undo日志,然后写redo日志，都没有问题了，等到事务提交的时候 在把redo日志里面的数据同步的数据文件里面，那么在读多写少的业务场景里，虽然说事务 机制下的写入速度并不快，但是写入的业务量不大，所有看不出什么问题，但是在写多读少 的业务场景里，每一秒中都要写入大量的数据，那么事务机制就会拖累写入的速度，因此传统的 关系型数据库就不太适合了，最好的解决办法就是使用NoSQL数据库

  • NoSQL抛弃了复杂的表结构和约束，有的NoSQL数据库也抛弃了事务机制，数据的写入速度很快

# 写多读少的解决方案2 - 高价值数据

  • 如果是高价值的数据，可以用MySql的TokuDB引擎来保存

因为NoSQL数据库抛弃了事务机制，所以不能存储高价值的数据。
MySQL的TokuDB可以带着事务高速写入

  • MySQL的TokuDB的写入速度是InnoDB的9-20倍，数据的压缩比大约是InnoDB的15倍 TokuDB适合写入多，查询少的业务场景，也就是存入冷数据占优势

# 写多读多的业务场景

这种场景一般不多见，比如qq和微信都有离线留言的功能，即便对方没有上线，我们发出去的 消息，会被临时存储在服务器上，等到对方上线之后，就能收到这些离校的消息了，这部分的 场景就是 读多写多的，常规的数据库时没有办法应对这样的场景的，所有这时候只能求助NoSql 数据库了，包括微信朋友圈，也是读多写多的场景，朋友圈的数据也是存储在noSql数据库中

# 数据库集群方案的缺点

  • 数据库集群的读写速度低于单节点数据库实例

比如：使用MyCat管理MySql集群，MyCat要生成全局主键，然后还有去判断当前商品是什么类型

# 数据库集群方案的优点

  • 数据库集群能支持更大规模的并发访问，并且存放更多的数据 比如：单节点的mysql难以支持500个并发连接，但是用了数据库集群，这个并发的数量就会翻倍，那么 单节点的数据库在高并发的情况下它的实际性能很差，一万的并发及不行了。
    InnoDB单表数据如果超过2千万，这个表的读写速度就会明显的下降，因此我们可以把数据切分存储 到不同的mysql数据节点上，这样每个节点单表数据量就减少了

# MySql参数优化

# 优化最大连接数

  • max_connections是mySql最大 并发连接数，默认值151

  • MySql允许的最大连接数上限是16384

  • 实际连接数是最大连接数的85%较为合适 如何知道实际连接数

    show variables like 'max_connections'
show status like 'max_user_connections'
    1
    2

# 密集索引和稀疏索引的区别

  • 密集索引文件中的每个搜索码值都对应一个索引值
  • 稀疏索引文件只为索引码的某些值建立索引项

密集索引和稀疏索引

InnoDB

  • 若一个主键被定义，该主键则作为密集索引
  • 若没有主键被定义，该表的第一个唯一非空索引则作为密集索引
  • 若不满足以上条件，innoDB内部会生成一个隐藏主键（密集索引）
  • 也就是说innoDB必须有一个主键作为密集索引而存在
  • 非主键索引存储相关键位和其对应的主键值，包含两次查找

# 本站导航

MySql慢查询日志
mySql联合索引最左匹配原则
mySql锁
mySql优化

# 支持我-微信扫一扫-加入微信公众号

Aseven公众号

# 赞赏作者

赞赏作者