MySQL索引
Mysql索引分类
mysql 索引分为三类:B+树、hash索引、全文索引
InnDB索引与MyISAM索引实现有什么区别
相同点
- 他俩都是B+树为基础的
不同点
- 索引文件不同
- InnDB索引和数据是同文件
- MyISAM索引文件时索引文件、数据文件是数据文件
- 叶子节点存储数据不同
- InnDB聚簇索引子节点存放的是记录本身、非聚簇索引存放的是索引字段和主键
- MyISAM聚簇索引和非聚簇索引存放的是内存地址
一个表中如果没有创建索引,那么还会创建B+树么?
会创建!分为两种种情况
-
没有创建索引,但有主键。
- 这种情况会创建主键索引也就是聚簇索引(非主键索引是非聚簇索引)
-
没有创建索引,也没有主键。
- Mysql会创建一个row_id,只能mysql内部使用,外部无法使用其进行查询。
B+树怎么来的
- 首先是二叉树,左边节点,一定比上层节点小,右边节点一定比上层节点大。但会有一边枝繁叶茂,另一边层数低的情况。
- 平衡二叉树解决了这一问题,尽量的解决了两边不平衡的问题。但又发现树越高查询的判断的次数就越多。
- 结合数据库就出现了B树,他的非叶子节点也记录了真实数据,但每一页只有16k大小,这就导致如果真实数据过多,会导致每页的记录变少,树又会高起来
- B+树在此基础上,将真实数据全部存储到了叶子节点,非叶子节点不记录数据。这就是聚簇索引。非聚簇索引叶子节点记录的是主键。这就又导致了可能又回表操作。
B+树 叶子节点和非叶子节点
树最底层的就是 叶子节点
,而上层的就是 非叶字节点
页中大致有以下几个部分
- record_type: 表示记录类型 0 普通记录 2 最小记录 3 最大记录 1 非叶子节点记录
- next_record: 表示吓一条记录的相对位置。(单向链表)
- 叶子节点中会记录的真实数据。
- 非叶子节点中会记录子节点页中的最小值主键值和页数。
B+树能存储多少数据?
三层聚簇索引来举例
根节点 只存 主键 + 页数 + 其他信息 约等于 10字节,一页是16k 也就是说存储了1600条
那么也就是说第二层有 1600 页,每一页也同样是存储了16000条
也就是说叶子节点有 1600 * 1600 页
叶子节点要存储真实数据,就按照1k来计算,一页 16 条记录 那就是 16 * 1600 * 1600 条记录 大约四千万条。
索引失效的情况
- 计算、函数导致索引失效
- LIKE以%,_开头导致失效
- 不等于 !=、<> 索引失效 看数据量
- IS NOT NULL导致索引失效 看数据量
Mysql内部
Mysql基本模块
连接池 - 缓存 - 解析 - 优化 - 引擎
SQL执行流程
- 客户端连接 发送SQL。
- 查询缓存。
- 解析器解析,走语法树进行预处理。(这里会检查有没有语法错误)。
- 优化器,发布执行计划。
- 调用查询接口使用存储引擎。
MyISAM和InnoDB有什么功能上的区别
对比项 | MyISAM | InnDB |
---|---|---|
外键 | 不支持 | 支持 |
事务 | 不支持 | 支持 |
行表锁 | 表锁,及时操作一条记录也会锁住整个表,不适合高并发操作 | 行锁,操作时之锁某一行,不影响其他行,适合高并发操作 |
关注点 | 并发查询,节省资源,消耗少,简单业务 | 并发写、事务、多表关系、更大资源 |
事务
ACID是什么
符号 | 英文 | 中文 | 解释 |
---|---|---|---|
A | atomicity | 原子性 | 要么全成功要么全失败 |
C | consistency | 一致性 | 从一个合法状态、到另一个合法状态,如果被迫中断的操作将一些中间操作已经写入数据库这就是非法的,不一致的。 |
I | isolation | 隔离性 | 事务和事务之间不能互相干扰 |
D | durability | 持久性 | 一个事务一旦成功提交,它对数据库中的数据的改变就应该是永久的 |
原子性、隔离性、持久性都服务于一致性
并发事务问题
以下例子中 age 都是 18
脏读(Dirty read)
A 事务的任务是 修改 age=20 修改 name=张三,当A事务没有完整完成事务,只完成了 age=20的修改时。
B 事务读取 age获取到了20,这是A事务失败了数据回滚 age 返回到 18 那么B事务就拿到了脏数据,这就是 脏读
修改丢失(Lost of modify)
A 事务修改 age= age + 5
B 事务修改 age= age + 3
我们期望得到的是 age + 5 + 3 = 26
但如果隔离性不够B事务就可能覆盖A事务的操作
A 事务 读到数据 18 进行计算前 B事务也读到了数据18 也开始计算,这时 A数据计算结束 age = 23 但B事务的age还是18,计算结束
age= 21这就是 修改丢失,也可以叫做 脏写
不可重复读(Un repeatable read)
A 事务 一直读取age时
B 事务 修改了age=20,这时A事务读取的age变成了20,这就是不可重复读
。
A事务在一次事务中,多次读取age时age被其他事物修改,导致同一次事务中age值不同。
幻读(Phantom read)
类似于不可重复读,幻读是记录级别,返回的结果集不一致。
A 事务 第一次获取表行数时为5行,这时B事务新增了一行数据,A事务在同一次事务中再次查询行数变成了6行,这就是 幻读
事务隔离级别
隔离级别越高 性能越差,隔离级别越低 性能越高
隔离级别 | 脏读 | 不可重复读 | 幻读 |
---|---|---|---|
读未提交 | ❎ | ❎ | ❎ |
读已提交 | ✅ | ❎ | ❎ |
可重复读(默认) | ✅ | ✅ | ❎ |
串行化 | ✅ | ✅ | ✅ |
如何实现
串行化,基于读写锁实现,会对表进行加锁。
读已提交,可重复度基于MVCC多版并发本控制实现。因为加的是行锁,所以无法完全解决幻读。
什么是一致性 非锁定读 和 锁定读
锁定读使用到了读写锁,
- 每次读操作都要获取一个共享的读锁,而写操作需要获取一个写锁。
- 共享锁之间不会互斥,共享锁和写锁之间,以及写锁和写锁之间会互斥。
- 当产生锁竞争时,需要等待其中一个操作释放锁后,另一个操作才能获取到锁。
非锁定读 MVCC多版本并发控制
MVCC内部细节
实现依赖于 隐藏字段、Read View、undo log
隐藏字段
- DB_TRX_ID 用于表示最近一次本行记录做修改(insert、update)的事务的标识符,即最后一次修改本行记录的事务id。如果是删除操作也算是一次修改,会在Innodb中修改是否删除的标记,并非真正删除。
- DB_ROLL_PTR 回滚指针,指向该行的undo log。如果该行未被更新,则为空。
- DB_ROW_ID 如果没有主键并且没有其他唯一非空索引的时候会有这个,使用它来生成聚簇索引。
Read View
帮助我们在并发事务中,通过版本来判断哪个事务的数据是可见的。
undo log
除了用来回滚数据,还可以读取可见的版本数据。以此实现非锁定读。
ACID 如何保证
原子性如何保证
undo log,成功一起成功,否则一起回滚
隔离性如何保证
不同的隔离级别通过,通过锁和MVCC来保证一致性
数据持久性如何保证
redo log,提交成功必然redo log就写入了,如果写入失败那么数据就回滚了。
数据一致性如何保证
数据的原子性、隔离性、持久性一起保证数据的一致性。
是否使用过select for update
简单的select 查询不会上锁,其他的事务可能将它扣成 0 了 再-1就不对了。
查询库存 = 100
减库存 = -1
记录日志 = log
提交 commit
可以 加一个 for update 加一个排他锁
select * from table_name for update
死锁
如何出现,两个事务互相依赖对方未释放的资源时会出现,如下。
事务 A
表 t id = 100 更新 加行锁
表 t id = 200 更新 加行锁
事务 B
表 t id = 200 更新 加行锁
表 t id = 100 更新 加行锁
如何解决死锁
- 快速失败,设置innodb_lock_wait_timeout 行锁超时时间
- 拆分 sql,避免大事务
- 利用索引,优化索引,尽量吧有风险的事务SQL使用上覆盖索引,优化where条件尽量满足索引的最左原则。
- 并发需求不高可以使用for update上排他锁
日志
Mysql会产生几种日志
- undo log
- 回滚日志,用于事务回滚。
- redo log
- 重做日志,用于灾备
- bin log
- 二进制日志,用于主从复制数据同步
- error log
- 错误日志,用于记录Mysql运行的错误
- log-error=/var/log/mysqld.log 执行 mysql错误日志的位置
- slow query log
- 慢查询日志,记录超过设定时间的SQL语句
各日志执行顺序
将age=18 改为 20 日志记录顺序
- undo log 记录数据修改前的值 18,为了方便回滚,记录版本配合MVCC使用
- redo log 记录数据修改后的值 20,为了灾备。记录在磁盘
- bin log 记录执行的sql(不包括查询sql),灾备恢复、主从同步
- 如果涉及到系统变量,可能会有问题,如当前时间。
日志刷盘机制
bin log 刷盘三种机制,修改sync_binlog参数就可
- sync_bing=0,每次事务提交bin log不会马上写入磁盘,而是先写到page cache。速度快有丢失的风险
- sync_bing=1,每次提交 bin log 都会写入磁盘,不会丢数据,但速度略慢。
- sync_bing的值大于1时,当page cache内累计到指定值的时候才写入磁盘。
redo log 和 undo log刷盘机制
- 开启事务
- 查询数据库中需要更新的字段,加载到内存中,形成数据脏页。
- 记录undo log到内存缓冲区,(用于回滚和Mvcc)并关联redo log -> 可刷盘
- 记录 redo log 到内存缓冲区,(用于失败重放)准备提交事务 -> 可刷盘
- 修改内存中的脏页数据
- 提交事务触发 redo log 刷盘
- undo log 和 脏页刷盘
- 提交事务成功
bin log 录入格式
- binlog_format=STATEMENT(默认)
- 直接记录SQL优点是,免去记录数据本身,从而减少IO,提高新能。缺点是,有时候会导致master-slave中的数据不一致,比如一些函数:
sleep() last_insert_id()
- 直接记录SQL优点是,免去记录数据本身,从而减少IO,提高新能。缺点是,有时候会导致master-slave中的数据不一致,比如一些函数:
- binlog_format=ROW
- 批量操作数据时效率较低,会产生大量日志内容,但不会出现函数导致的数据不一致问题。
- binlog_format=MIXED
- 是以上两种混合使用,有函数用ROW没函数用STATEMENT。
集群同步为何使用bin log
为什么使用bin log
如果直接同步表结构的二进制数据那么会有未知同步到哪里的问题,如果发生中断。
bin log 有什么优点
- binlog 是 mysql 提供的日志所有引擎都能用
- 支持增量同步
- 还可以支持其他中间件
MySQL开发
Mysql可以直接存储文件么?
可以,使用BLOB(binary large object)来存储。
什么时候存文件,什么时候不存。
存
- 查询频率高,文件小的时候存。
不存
- 文件大、数量多、变更频繁。
碰到过什么问题
- 上传的文件大,SQL执行失败。可以调整max_allowed_packet
- 主从同步数据比较慢
- 应用线程阻塞
- 占用网络带宽
emoji乱码怎么办
使用 utf8mb4
如何存储 ip 地址
- 字符串存储
- 范围查询的话使用 like 有点慢。
- 无符号整型存储
- 支持范围查询 inet_aton()和inet_ntoa()。
长文本如何存储
- TEXT 类型,也是弹性的。
- 创建索引时候需要限制长度
长文本如何设计表结构
- 将长文本同时存储到搜索引擎
- 方便全文检索
- 分表存储
- 关键信息和长段文本分表存储。
- 聚簇索引,叶子结点会记录原数据。
- 分段存储
长文本如何创建索引
- 创建索引肯定为了去快速查询,建议存入搜索引擎
- 创建的时候需要指定长度
- 分段存储后创建索引
日期如何存储
- DATETIME 存储
- 写啥是啥,无时区
- 范围大
- TIMESTAMP 存储
- 可以跨时区,更具用户的session时区来
- 好像到38年
为什么不用字符串存储日期
- 无法完成范围查询
- 数据量大的话一定会按某些条件去限制查询数量的大小,一般按照时间,或者limit
为何不用 int
- timestamp存储空间小,支持时区转换,更易读。底层本质上就是int
char和varchar有什么区别
- char固定长度,性能高,空间也固定。
- 但可能会出现冗余,一般来说固定长度会使用char。
- varchar弹性,如果碰到扩容可能会导致物理空间不在一块。
- 节约空间,但需要计算下标读取内容,速度就比char慢一些。
财务计算
- 浮点计算可能会精度丢失,使用 decimal 存储解决。
- 注意多线程问题,避免数据不一致。
- 一定要使用事务,保证ACID特效。
- 数据只逻辑删除。
- 日志一定要记录。
decimal、float、double区别是什么
- decimal 精度最高,计算都用它。
- 仅展示,不计算可以使用float/double。如果还不需要排序等操作还可以使用字符串
预编译sql是什么
- 预编译SQL会被mysql缓存下来。查询加速
- 作用域是每个session对其他session无效
- 可以防止简单的sql注入
- 用户传入的数据会被当做参数,不会被拼接 如下
- select * from user where id = “1;delete from user where id = 1”
- select * from user where id = 1;delete from user where id = 1
- 用户传入的数据会被当做参数,不会被拼接 如下
子查询和join哪个效率高
join效率高,子查询效率低
为什么子查询效率低
子查询会创建临时表,会多一个创表和删表的操作,而join不需要,并且可以使用索引。
如何优化join查询
- 适当使用范式化的冗余字段,减少关联表。
- 更新时要记得同时更新
- 使用小表驱动大表。
- 小表 join 大表
- inner join 让系统帮忙选择驱动表
- 关联字段一定创建索引
- 调整 join buffer 大小
Mysql调优
- sql调优
- 表(结构)设计调优
- 索引调优
- 慢查询调优
- 操作系统调优
- 数据库参数调优
常用工具
- explain
- mysql dump slow 慢查询
- show profiles 一条sql执行的流程细节
- optimizer_trace 查询优化器的选择策略
如何监控线上环境中执行比较慢的sql
开启慢查询日志,收集sql(一般不开启,会带来一些性能上的问题)
查询到后使用 mysql dump slow (记得关闭慢查询日志)
EXPLAIN
type
- ALL 全表扫描
- range 范围查询
- ref 相对来说不错
key_len使用的索引的字节
rows和filtered
rows表示返回多少行 filtered 有用的是多少。
rows 100 filtered 1.00说明影响到 100行,只有百分之1有用
MySQL数据库cpu飙升的话你会如何分析
- 使用top观察mysqld的cpu利用率
- 使用pidstat定位线程
- 在PERFORMANCE_SCHEMA,THREADS记录了thread_os_id找到现成执行的sql
- 根据操作系统id可以到processlist表找到对应的会话
- 在会话中定位sql
千万数据如何优化
- 减少请求,尽量一条sql拿出
- 添加缓存,较少和数据库的直接交互。
- 使用合适字段,如varchar换char
- 使用索引
- 使用explain查看索引使用情况
- 尽量满足索引覆盖和索引下推的条件
- 查看key_len索引使用情况,判断索引创建是否合理
- 分段查询,避免拿出很多无效数据
- 关联字段,看看能不能设置冗余字段,简化分表查询逻辑。
- 冷热数据分库存储
- 通过日期判断冷热数据
- 读写分离,主备集群
- 拆分大服务为小服务,更具服务来分库
count(列名)和count(*)有什么区别
count(列名)不统计null行数
count(*)统计
limit翻页数量过多如何优化
分成两个sql,第一个正常查询,但只查询id字段,快速定位id,通过第二个sql来查询具体值。子查询和join都可以。