Mysql笔记

mysql索引机制

为什么使用索引

• 减少存储引擎扫描的数据量;
• 把随机io转化为顺序io;
• 在分组和排序的时候, 避免使用临时表;

索引的结构

使用b+tree实现

• 二叉树: 存在的问题, 会形成链表的数据结构, 而且树的层级太深的时候(io次数也会很多), 查询效率也不高;
• 平衡二叉树: 主要的优点是, 整个树的高度差不会超过1, 可以避免形成链表结构, 并且保证层级不会在某一侧太深, 但是因为每个节点保存的数据太少, 还是会出现层级太深的问题;
• B-Tree: 多路平衡查找树, 顾名思义, 就是一个节点下存在多个分支, 可以大大减少树的高度, 并且每个节点上行;
• B+tree: 大体结构和b-tree类似, 但是b+tree非叶子节点不保存数据, 只保存关键字, 数据全部放在叶子节点中, 叶子节点的数据区是由顺序的, 并且叶子节点之间有链表连接;

mysql为什么使用b+tree?

1. 拥有b-tree的优势;
2. 由于每个节点不保存数据只保存关键字, 因此, 他的扫表能力和磁盘的读取能力更强;
3. 数据保存在叶子节点, 并且顺序存储, 所以b+tree的排序能力更好;

mysql中的b+tree实现具体形式

Myisam

数据和索引分别存储, 索引文件中的叶子节点, 保存的是数据的引用地址;

innerDB

1. 只有一个文件, 索引和数据放在同一个文件中;
2. 以主键为索引来组织存储数据的;
3. 并且主键索引使用的是聚集索引(数据存储的顺序和索引的逻辑顺序);
4. 其他索引的叶子节点实际存储的是主键的值, 查找时在会主键存储查找一次;

索引的其他知识

1. 列的离散性, 离散性越高, 选择性越好, 离散性太低的可近似为全表扫描了;
2. 最左匹配原则: 查询的时候是从左向右一个一个比对, 并且不能跳过;
3. 最小空间原则: 空间最小原则;
4. 覆盖索引: sql查询的列可以通过索引的关键字直接返回, 则称之为覆盖索引;
5. like 'xxx%'不一定用到索引;
6. age > 22 and name = 'xxx', 如果是联合索引的话, 只有前面一条能用到;

sql执行顺序

(8) SELECT (9)DISTINCT<select_list> 
(1) FROM <left_table> 
(3) <join_type> JOIN <right_table> 
(2) ON <join_condition> (4) WHERE <where_condition> 
(5) GROUP BY <group_by_list> (6) WITH {CUBE|ROLLUP} 
(7) HAVING <having_condition> 
(10) ORDER BY <order_by_list> 
(11) LIMIT <limit_number>

mysql整体架构

1. 客户端driver, 如java , php等;
2. 接入方进入后, 交给connect pool管理(包括建立连接和安全认证);
3. sql interface, 用来接收sql;
4. parse, 根据sql规范来解析sql;
5. optimize: sql优化器, 会将我们的sql进行优化;
6. cache: 缓存, 会缓存sql语句和查询结果;
7. 存储引擎: 插拔式的存储引擎;

存储引擎分类
    a). csv: 使用于数据的导入导出, 表格直接转化成csv文件;
    b). archive: 只允许insert和select, id只能自增, 不支持事务, 行级锁, 
    但是数据占用空间小, 适用于日志采集;
    c). memory: 数据存储在内存中, io效率高, 安全性低, 
    d). myisam: 不支持事务, 表级锁, 自动存储count, 索引的存储结构, 主要适用于大量读, 少量写的场景;
    e). innerDB: 支持事务, 行级锁, 主键索引, 对数据安全性要求较高的场景;

mysql的连接的状态

• sleep: 等待连接
• query: 正在查询
• locked: 线程等待表锁释放
• sorting result: 正在对结果进行排序
• sending data: 向请求端返回数据

查询命令 show full processlist

缓存的作用

查询的时候先查缓存, 命中, 返回, 没命中, 查询磁盘, 然后保存进缓存中;

#查看缓存参数
show variables like 'query_cache%'

query_cache_limit    1048576 #一次查询最多缓存多少, 超过不缓存
query_cache_min_res_unit    4096
query_cache_size    0 #总的缓存的大小
query_cache_type    ON #是否开启缓存, 0 不开启, 1 开启
query_cache_wlock_invalidate    OFF

设置值, set global query_cache_type = ..;
数据被增删改的时候会删除缓存;

查询优化处理

规则

1. 等价查询转化: a < b and b = 5转化为a< 5 and b = 5;
2. 将可转化的外链接转化为内连接;
3. 优化count, max, min等函数;
4. 覆盖索扫描;
5. 子查询优化;
6. 自动终止查询, limit=1时, 再查到数据后不会继续扫描;
7. in的优化, 会对in中数据进行二分查找, 所以用or的时候尽量转化为in
...

连接查询

1.内连接（自然连接）：只有两张表相匹配的行才能出现在结果集;
2.外连接包括：
    2.1 左外连接：左边为主表，左边的表显示全部；右边为副表，右边无符号数据时显示null，不符合的不显示；
    2.2 右外连接：右边为主表，右边的表显示全部；左边为副表，左边无符号数据时显示null，不符合的不显示；

执行计划id

id相同时, 由上向下;
不同时, 值越大的先被执行;

select type

• simple: 简单的select查询, 不包含子查询和union查询;
• primary: 查询中包含子查询, 最外层的查询时primary查询;
• subquery/materialize: subquery表示在select或where列表中包含了子查询, meterialize表示where后面的in中的查询;
• union: 若第二个select出现在union之后, 为union查询;
• union result: 从union表获取结果的select

执行计划type

system: 表中只有一行记录, const的特例, 基本不会出现;
const: 只查到一条数据的查询, 常用在主键索引和唯一索引上;
eq_ref: 查询时, 每个查询条件只有一条记录和他匹配;
ref: 每个查询可能会有多条和他匹配, 常见于主键或者唯一索引;
range: 范围查询
index: 索引的全表扫描;
all: 不是用索引的全表扫描;

extra

• useing filesort: 进行了重新的排序, 如果命中索引, 则不会;
• useing where: 进行了回标查询;
• useing temporary: 使用到了临时表(例如group by时);
• useing index: 用到了覆盖索引(提高性能);

定位慢sql

show variables like 'slow_query_log';
set global ...='xxx';
show variables like 'slow_query%'; #可以设置慢查询日志保存路径
set global log_queries_not_using_indexes = on;#没有命中索引的也记录
set global long_query_time=0.1; #设置慢查询时间

mysql事务

acid: 原子性, 隔离性, 一致性, 持久性;

隔离级别

• uncommited; 读到其他事务未提交数据;
• commited: 连续两次读取同一数据不相同, 解决脏读;
• repeatable-read: 解决连续两次同一数据读取值不同问题;
• serializable: 解决幻读, 数据插入和删除的问题;

innerDB在rr级别解决了幻读的问题;

锁的使用

• 行锁和表锁的区别
  锁定粒度, 加锁效率, 冲突概率, 并发性能方面有区别;
• innerDB支持行锁和表锁, 但是实现的前提是加索引;
• 锁类型

共享锁: 多个事务可共用, 其他事务只能读不能写;
    使用方式: select * from xxx LOCK IN SHARE MODE;
排他锁: 只能被一个事务持有的锁;
    使用方式: delete/update/insert默认加排他锁, select * from 
    xx FOR UPDATE;
    
    innerDB的行锁锁是通过给索引项加锁实现的, 只有通过索引进行条
    件检索, 才能使用行锁, 否则使用表锁;
innerDB默认使用临建锁(next-key)实现行锁算法, 另外还有间隙锁, 记录锁, 范围查找并找到值时, 临建锁, 未找到值, 间隙锁, 而精确查找并命中主键/唯一索引的时候, 是记录锁(详情待补充);

MVCC多版本并发控制

主要为了解决写操作对读操作的阻塞;
原理:

1. 数据库中的每张表都会设置一列事务id(存储数据创建时的id)和一列
删除事务id(保存数据删除时的id), 这个事务id是全局唯一的;
2. 数据创建时, 会获取当前的事务id, 保存在事务id的列中, 删除事务
id列为空;
3. 数据删除时, 获取当前事务id, 保存在删除事务id列中;
4. 数据更新时, 获取当前事务id, 将原数据copy一份, 放入表中, 事务
id为当前事务id, 删除id置为空, 原来的数据则将删除id设置为当前事务
id;
5. 查询的时候, 会获取事务id比当前事务id小(保证获取的数据在本事务
开始之前就存在), 同时删除事务id为空或者比当前事务id大的数据(保证
获取的数据是在当前事务之前没删除的);

上述过程如果是先查询后更新, 是没有问题的;
但是在先更新(但未提交)后查询的时候是会出错的;

解决办法undo.log

使用undo.log, 在每个事务开始的时候, mysql会备份一次老的数据, 存
在undo.log中, 这样查询的操作, 如果修改已经提交, 就查表, 如果未
提交, 就可以直接去undo.log中查询;

undo.log的作用:
1. 用于事务的rollback回滚;
2. 作为数据的旧版本, 为其他并发事务提供快照读;

快照读

读取历史版本, innerdb的普通select语句都是快照读, 读取的数据有原
本数据和undo数据组成

当前度

读取最新版本, 保证当前的数据不能被其他事务修改, update/ delete/ insert/ select ... lock in share mode/ select ... for update, 这些都是当前读;

Redo.log

前提是, innerDB不是每次的事务都会持久化到磁盘, 而是先保存在
缓存中, 在一定时间的时候一起提交, 将事务更改的最新的数据放入
redo.log, 主要是为了实现事务的持久性, 防止数据库发生故障的
时候, 有未进行磁盘化的数据, 保存在redo.log中保证在mysql重启
后, 可以根据redo.log恢复已经提交的数据;

redo.log的其他细节
1. redo.log日志文件组中文件的个数默认innerdb_log_files_in_group=2;
2. 每个日志最大存储量innordb_log_file_size=48M;
3. redo.log的缓存池大小innodb_log_buffer_size=16M;

Buffer持久化策略: innordb_flush_log_at_trx_commit
0: 每秒提交一次;
1. 每次事务提交, 提交一次;
2. 每次事务提交, 将redo buffer刷一次到os cache, 然后后台每秒提交一次;
(提交说的是持久化磁盘)

mysql配置优化

全局参数和会话参数

set global ...是全局参数设置
set session ...是会话参数设置
注意:
    全局参数设置对已经存在的会话无效, 需要关闭会话重新连接;
    会话参数随着会话结束会销毁;
    全局参数如果服务器重启, 也会销毁, 最好配置在配置文件中;

查找my.cnf命令

mysql --help
里面注意的配置:
#最大连接配置数, 该这个参数的时候要注意两个地方
#1. 系统句柄数, ulimit -a
#2. mysql句柄数配置 /usr/lib/systemd/system/mysqld.service
max_connections

mysql内存参数配置
    #每个connection内存参数配置
    #排序缓冲区大小
    sort_buffer_size 建议2M以内;
    #关联缓冲区大小
    join_buffer_size 1M以内;
    上述配置占用内存计算: 4000*(0.256+0.256)
    
    #innerdb缓冲池大小, 大的缓冲区可以减少网络io(数据缓存, 索引缓存等)
    #计算公式 (总物理内存-系统运行内存-connection所用)*90%
    innerdb_buffer_pool_size
    
其他参数配置
    #服务器关闭非活跃连接之前要等待多少秒
    wait_timeout
    #限制innordb能打开表的数量
    innordb_open_files
    #等待锁的超时时间
    innordb_lock_wait_timeout

数据库表设计也会影响性能

三范式以及字段设置为not null