mysql数据库
2021-01-21 10:31:25 0 举报AI智能生成
mysql数据库知识
作者其他创作
大纲/内容
原理
数据页
数据库中的存储结构
页（Page）是数据库管理存储空间的基本单位，InnoDB默认16k
区（Extent）是比页大一级的存储结构，在InnoDB存储引擎中，一个区会分配64个连续的页
段（Segment）有一个或多个区组成，不同类型的数据库对象，段的形式不同。
表空间（Tablespace）是一个逻辑容器，表空间存储的对象是段，因此一个表空间
中可以有一个或多个段，但是一个段只能属于一个表空间
中可以有一个或多个段，但是一个段只能属于一个表空间
数据页的结构
页如果按类型分的话，常见的有数据页（保存B树节点），系统页、Undo页、事务页等
页的大小
InnoDB存储引擎中的默认是16k
SQL server的页大小为8k
Oracle中用术语“块”（Block）来代表“页”，
支持的大小为2KB、4KB、8KB、16KB、32KB和64KB
支持的大小为2KB、4KB、8KB、16KB、32KB和64KB
7个部分组成
文件头（File Header）：描述页的信息
页头（Page Header）：页的状态信息
最小和最大记录（Infimum+supermum）：这是两个虚拟的行记录
用户记录（User Records）：用户记录，存储行记录内容
空闲空间（Free Space）：页中没有使用的空间
页目录（Page Directory）：存储用户记录的相对位置
文件尾（File Trailer）：校验页是否完整
作用上可以分为3部分
文件通用部分：也就是文件头和文件尾，类似集装箱一样，将页的内容进行了封装
通过校验文件头和文件尾来确保页是否完整
通过校验文件头和文件尾来确保页是否完整
记录部分：页的主要作用是存储记录，占据主要空间
索引部分：这部分重点是页目录，起到了记录索引的作用
页内的记录查找方式：通过页目录（存储的是槽，槽相当于分组记录的索引）对记录
进行二分查找
进行二分查找
理解B+树的查询方式
页结构
在一棵B+树中，每个节点都是一个页，每次新建节点的时候，就会申请一个页空间
同一层上的节点之间，通过通过页的结构构成了一个双向链表（页文件头中的两个指针字段）
非叶子节点，包括了多个索引行，每个索引行里存储着索引键和指向下一层页面的页面指针
叶子节点，存储了关键字和行记录，在节点内部（也就是业结构的内部）记录之间是一个单项链表，
但是对记录进行查找可以通过页目录采用二分查找的方式来进行。
但是对记录进行查找可以通过页目录采用二分查找的方式来进行。
B+树中如何检索记录
首先从B+树的跟开始，逐层检索直到找到叶子节点
找到叶子节点对应的数据页，将数据也加载到内存中，通过页目录中的槽
采用二分查找的方式先找到一个粗略的记录分组
采用二分查找的方式先找到一个粗略的记录分组
在分组中通过链表遍历方式进行记录的查找
理解普通索引和唯一索引在检索上的效率为什么差别很小
磁盘IO
缓冲池
作用：DBMS会申请占用内存来作为数据缓冲池，最小化磁盘活动
如何读取数据：首先会判断该页是否在缓冲池中，如果存在就直接读取，
否则才会通过内存或磁盘将页面放到缓冲池中进行读取
否则才会通过内存或磁盘将页面放到缓冲池中进行读取
如何更新数据：记录进行修改时，首先会修改缓冲池中的页里面的记录信息，
然后缓冲池会采用一种checkpoint的机制以一定的频率刷新到磁盘上
然后缓冲池会采用一种checkpoint的机制以一定的频率刷新到磁盘上
查看缓冲池的大小：在InnoDB中对应的innodb_buffer_pool_size变量
查看缓冲池的个数：在InnoDB中对应innodb_buffer_pool_instances变量
缓存在数据库中的数据结构和作用
数据页加载的三种方式
内存读取：如果该数据存在在内从中，基本上执行时间1ms效率是很高的
随机读取：如果没有在内存中，就需要在磁盘上对该页进行查找，
整体时间预估在10ms
整体时间预估在10ms
顺序读取：批量读取的方式，如果磁盘吞吐量是40MB/S，页大小为16K，
那么一页的读取时间是0.4ms
那么一页的读取时间是0.4ms
统计SQL语句查询成本
如何确定查询计划：如果存在多种查询计划的话，MySQL会计算每一种查询计划所需要得成本，
从中选择成本最小的一个作为最终执行的查询计划
从中选择成本最小的一个作为最终执行的查询计划
last_query_cost
指令：SHOW STATUS LIKE ‘last_query_cost’
作用：统计刚才执行的SQl语句需要加载多少个页面
如果采用顺序读取的方式，计时加载的页面多，查询时间变化也不大
锁的划分
按照锁的粒度
行锁：按照行粒度对数据进行锁定，锁定力度小，因此发生所冲突概率低，可以实现并发度高，
但是对于锁的开销比较大，加锁比较慢
但是对于锁的开销比较大，加锁比较慢
页锁：页锁就是在页的粒度上进行锁定，锁定的资源比行锁多，开销介于行锁和表锁之间，
会出现死锁。锁粒度介于表锁和行锁之间，并发度一般
会出现死锁。锁粒度介于表锁和行锁之间，并发度一般
表锁：对数据表进行锁定，锁粒度很大，同时发生锁冲突的概率也会较高，也就是数据访问
的并发度低。不过好处在于对锁的使用开销小，加锁很快。
的并发度低。不过好处在于对锁的使用开销小，加锁很快。
MySQL中，InnoDB支持行锁、表锁；MyISAM支持表锁
Oracle支持行锁、表锁；SQLserver支持行锁、页锁、表锁
从数据库的管理角度
共享锁，也叫读锁或S锁，锁定的资源可以被其他用户读取，但不能修改
排他锁，也叫独占锁、写锁或者X锁。锁定的数据只允许进行操作的事务使用，而其他
事务无法对已锁定的数据进行查询或修改。
事务无法对已锁定的数据进行查询或修改。
意向锁，简单来说就是给更大一级别的空间示意里面已经上过锁
当有多个事务对同一数据获得读锁的时候，可能会出现死锁的情况
从程序员的角度
乐观锁，认为对同一数据的并发操作不会总发生，属于小概率事件，不采用数据库自身的锁机制，
而是通过程序员来实现：版本号机制、时间戳机制
而是通过程序员来实现：版本号机制、时间戳机制
悲观锁，对数据被其他事务的修改持保守态度，因此会通过数据库自身的锁机制来实现，
从而保证数据操作的排他性。
从而保证数据操作的排他性。
适用场景：乐观锁适合读多的场景；悲观锁适合写多的场景
如何防止死锁的发生
如果事务涉及多个表，操作比较复杂，那么我们可以尽量一次锁定所有资源，而不是逐步来获取
如果事务需要更新数据表中的大部分数据，而且数据表又比较大，这时可以采用锁升级的方式，
比如将行级锁升级为表级锁
比如将行级锁升级为表级锁
不同事务并发读取多张数据表，可以约定访问表的顺序，采用相同的顺序可以降低死锁发生的概率
MVCC
什么是MVCC
MVCC，通过数据行的多个版本管理来实现数据库的并发控制
使用MVCC的好处
读写互不阻塞
降低了死锁的概率
解决一致性读的问题
快照读：读的是快照数据（历史版本）
当前读：读取最新数据。加锁的SELECT，或者插入、删除、更新都会进行当前读
InnoDB中的MVCC
事务版本号：当每次开启一个事务，都会从数据库中获得一个事务ID，这个事务
ID是自增长的，所以通过ID大小可以判断出来事务的时间顺序
ID是自增长的，所以通过ID大小可以判断出来事务的时间顺序
记录隐藏的列
db_row_id,隐藏的行id
db_trx_id,操作这个数据的事务id
db_roll_ptr,回滚指针
Undo log，InnoDB将行记录快照保存在了Undo log里
Read View是如何工作的
作用：帮我们解决了行的可见性问题
Read View的结构
trx_id,系统当前正在活跃的事务ID集合
low_limit_id,活跃的事务中最大的事务ID
up_limit_id,活跃的事务中最小的事务ID
create_trx_id,创建这个Read View的事务ID
Read View的原则
trx_id < up_limit_id
trx_id > up_limit_id
up_limit_id < trx_id < low_limit_id
查询数据的步骤
1、首先获取事务自己的版本号，也就是事务ID
2、获取Read View
3、查询得到的数据，然后与Read View中的事务版本号进行比较
4、如果不符合Read View规则，就需要从Undo Log中获取历史快照
5、最后返回符合规则的数据
读已提交，一个事务中的每一次SELECT查询都会获取一次Read View
可重复度时，一个事务只在第一次SELECT时会获取Read View，后面所有的
SELECT都会复用这个这个Read View
SELECT都会复用这个这个Read View
InnoDB是如何解锁幻读的
InnoDB的三种行锁的方式
记录锁：针对单个行记录添加锁
间隙锁：锁住一个范围（索引之间的间隙），但不包括记录本身，可以防止幻读
Next-Key锁：锁住一个范围，同时锁定记录本身，相当于间隙锁+记录锁，
可以防止幻读
可以防止幻读
采用Next-Key锁+MVCC避免幻读
查询优化器
什么是查询优化器
SQL查询的执行步骤
分析器：包括语法分析、语义检查
优化器：逻辑优化器、物理优化器
执行器：将确定的执行计划进行执行
查询优化器的目标是找到执行SQL查询的最佳执行计划
执行计划就是查询树，它是有一系列物理符号组成，这些操作符按照一定的
运算关系组成查询的执行计划
运算关系组成查询的执行计划
优化的两个阶段
逻辑查询优化：通过改变SQL语句的内容来使得SQL查询更高效，
同时为物理查询优化提供更多的候选执行计划
同时为物理查询优化提供更多的候选执行计划
物理查询优化：关系代数的每一步都对应着物理计算，这种物理计算往往存在多种算法，
因此需要计算各种物理路径的代价，从中选择最小代价作为执行计划
因此需要计算各种物理路径的代价，从中选择最小代价作为执行计划
两种优化方式
基于规则的优化器：RBO，通过在优化器里面嵌入规则，来判断SQL查询符合那种规则
从而制定执行计划
从而制定执行计划
基于代价的优化器：CBO，会根据代价评估模型，计算每条可能的执行计划的代价，也就是
COST，从中选择代价最小的作为执行计划
COST，从中选择代价最小的作为执行计划
RBO像出租车老司机，从A到B的路径靠自己的经验来规划，而CBO更像手机导航
通过数据来驱动选择最佳的执行路径
通过数据来驱动选择最佳的执行路径
SQL是面向集合的语言，是解释性语言，不同的数据库优化器的实现原理不同
CBO是如何统计代价的
大部分关系型数据库都支持基于代价的优化器（CBO），CBO随着版本的迭代也
越来越成熟，但是CBO依然存在着缺陷
越来越成熟，但是CBO依然存在着缺陷
代价模型中可以匹配的参数
Server层
对应mysql.server_cost数据表
disk_temptable_create_cost，表示临时表文件（InnoDB或MyISAM）的创建代价，默认为0
disk_temptable_roe_cost，表示临时文件（InnoDB或MyISAM）的行代价，默认为0.5
key_compare_cost，键比较代价，键比较的次数越多，这项的代价就越大，
是个重要的指标，默认值0.05
是个重要的指标，默认值0.05
memory_temptable_create_cost，内存中临时表的创建代价，默认值1
momory_temptable_row_cost，内存中临时表的行代价，默认值0.1
row_evaluate_cost，统计符合条件的行代价，如果符合条件的行数越多，那么
这一项的代价就越大，因此这是个重要的指标，默认值0.1
这一项的代价就越大，因此这是个重要的指标，默认值0.1
Engine层
对应mysql.engine_cost
io_block_read_cost，从磁盘中读取一页数据的代价，默认是1
memory_block_read_cost，从内存中读取一页数据的代价，默认值是0.25
如何计算
总代价=IO代价+CPU代价
COST=PAGE FETCH+ W*（RSI CALLS）
PAGE FETCH是IO代价，也就是页面加载的代价，包括数据页和索引页的加载
W*（RSI CALLS）是CPU的代价，其中W是权重因子，表示了CPU到Io之间转化的
相关系数，同时RSI CALLS代表了CPU的代价估算
相关系数，同时RSI CALLS代表了CPU的代价估算
总代价=IO代价+CPU代价+内存代价+远程代价（MySQL5.7版本之后）
主从同步
主从同步的作用
读写分离
数据备份
高可用性
原理
基于binlog进行数据同步的
3个工作线程
二进制日志转储线程：当从库线程连接的时候可以将二进制日志发送给从库
从库IO线程：接到主库，向主库发送请求更新Binlog
从库SQL线程：读取从库中的中继日志，并且执行日志中的事件
Binlog：二进制日志，存储的是数据库的更新事件
主从同步的数据一致性问题
问题：主库更新后，从库还没有完成更新，这时的读从库可能会读到旧数据
方法1：异步复制
客户端提交COMMIT之后不需要等待从库返回任何结果，
而是直接将结果返回给客户端
而是直接将结果返回给客户端
优点：不会影响主库写的效率
缺点：可能会存在主库宕机，而binlog还没有同步到从库的情况，
也就是此事的主库和从库数据出现不一致情况
也就是此事的主库和从库数据出现不一致情况
方法2：半同步复制
MySQL5.5版之后开始支持半同步复制的方式
在客户端提交了COMMIT之后不直接将结果返回给客户端，
而是等待至少有一个从库接受到了binlog，并且写到中继
日志中，再进行返回给客户端
而是等待至少有一个从库接受到了binlog，并且写到中继
日志中，再进行返回给客户端
优点：提升了数据一致性
不足：仍然存在数据不一致的情况，增加了网络连接的延迟
方法3：组复制
简称MGR，是MySQl5.1.17版本中推出的一种新的数据复制技术
这种复制技术是基于Paxos协议的状态机复制
这种复制技术是基于Paxos协议的状态机复制
Paxos协议可以解决分布式系统中的数据不一致问题
优点：提供了数据强一致性，可以让MySQL应用到更多领域，比如金融
不足：对网络性能要求高，只支持InnoDB存储引擎
InnoDB数据恢复
表空间
InnoDB存储引擎的文件格式是.idb文件
共享表空间
多个数据表会共用一个表空间，同时表空间也会
自动分成多个文件存放到磁盘上，
自动分成多个文件存放到磁盘上，
好处：单个表空间的大小可以突破文件系统的限制，
最大可以达到64TB
最大可以达到64TB
不足：结构不清晰，不利于数据找回。将所有数据和索引存放到
一个文件中，也会使得功效表空间的文件大小很大
一个文件中，也会使得功效表空间的文件大小很大
独立表空间
每个数据表都有自己的物理文件。也就是table_name.idb文件
好处：每张表都相互独立，不会影响其他数据表，同时存储结构
清晰，利于数据迁移和恢复
清晰，利于数据迁移和恢复
不足：单表大小的限制在于文件系统大小的限制
.idb文件数据如何找回
InnoDB是有自动恢复机制，使用innodb_force_recovery
innodb_force_recovery
一共7种状态，数值从0到6
为0，表示不进行强制恢复，如果遇到错误，比如.idb文件中的数据页发生损坏，
则无法读取数据，会发生MySQl宕机的情况并且将错误日志记录下来
则无法读取数据，会发生MySQl宕机的情况并且将错误日志记录下来
为1，表示即使发生了损坏页也可以继续让服务运行，这样我们就可以
读取数据表，并且对当前损坏的数据进行分析和备份
读取数据表，并且对当前损坏的数据进行分析和备份
更大的参数值包括了较低的参数值的功能
一般来说不需要将参数设置为4或者以上，因为它可能对数据文件造成永久性损坏
>0时，相当于对InnoDB进行了写保护，只能进行SELECT读取操作
.idb文件损坏与恢复实例
1、使用innodb_force_recovery启动服务器
2、备份数据表
新的数据表使用MyISAM存储引擎
采用二分法判断数据页损坏的位置，将正确的数据进行备份
3、删除旧表，改表明
4、关闭innodb_force_recovery，并重启数据库
机制比人更重要
全量备份
增量备份
延迟备份
.idb文件数据恢复
基础
sql如何执行
查询语句
连接器
负责跟客户端建立连接、获取权限、维持和管理连接
show processlist：查看连接状态
查缓存
查看缓存是否存在，存在直接返回，不存在往下执行
将查询结果放到缓存中
建议：不建议使用查询缓存，因为查询缓存失效非常频繁，只要有一个表更新
这个表上的所有查询都会被清空。
这个表上的所有查询都会被清空。
配置项
query_cache_type
DEMAND:默认不使用查询缓存
注意：MySQL8.0查询缓存真快功能删掉了
分析器
词法分析
检查SQL语法是否正确
语法分析
检查SQL中的访问对象是否存在
权限检查
检查用户是否具有访问该数据的权限
共享池检查
共享池是一块内存池，最主要的作用是缓存SQL语句和该语句的执行计划。
优化器
进行硬解析：创建解析树，生成执行计划
执行器
当有了解析数和执行计划后，就可以在执行其中执行语句了
执行器的执行流程
流程图
更新语句
查询语句的流程走一遍
两个日志
redo log（WAL技术）
InnoDB特有的日志
InnoDB特有的日志
解决问题：如果每一次更新操作都需要写进磁盘，然后磁盘也要找到对应的那条记录，然后再更新，
整个过程IO成本、查找成本都很高。
整个过程IO成本、查找成本都很高。
先写日志，再写磁盘
当一条数据更新的时候，InnoDB会先把记录写到redo log里面，
并更新内存。在适当的时候将操作记录更新到磁盘里。
并更新内存。在适当的时候将操作记录更新到磁盘里。
固定大小，环形结构
write pos是记录当前的记录位置
checkpoint是当前要擦除的位置
有了redo log，InnoDB就可以保证即使数据库发生异常重启，
之前提交的记录都不会丢失，这个能力称为crash-safe
之前提交的记录都不会丢失，这个能力称为crash-safe
binlog
MySQL Server层实现的
MySQL Server层实现的
逻辑日志，记录着语句的原始逻辑
内部流程
1、执行器先找引擎取数据
2、执行器拿到引擎给的行数据，操作得到新的一行数据，
再调用引擎接口写入这行数据
再调用引擎接口写入这行数据
3、引擎将这行数据更新到内存中，同时将这个更新操作记录到redo log里
4、执行器声场这个操作的binlog，并把binlog写入磁盘
5、执行器调用引擎的提交事务接口，引擎把刚刚写入的redo log改成提交状态。
更新完成
更新完成
两段提交
redo log
binlog
sql在底层的执行顺序
1、FROM子句组装数据
2、WHERE子句进行条件筛选
3、GROUP BY分组
4、使用聚集函数进行计算
5、HAVING筛选分组
6、计算所有的表达式
7、SELECT的字段
8、ORDER BY排序
9、LIMIT筛选
ACID
原子性
一致性
隔离性
持久性
事务隔离级别
三种异常问题
脏读：读到了其他事务未提交的数据
不可重复读：两次读取结果不同。
幻读：事务A查询得到N条数据，事务B更改了M条数据，A再次查询发现有N+M条数据。
四种隔离级别
读未提交：会产生脏读、不可重复读、幻读等情况
读已提交：禁止产生脏读，会产生不可重复读、幻读等情况
可重复读：禁止脏读和不可重复读，会产生幻读
可串行化：讲事务串行化，全部禁止，牺牲了系统的并发性，但可以解决事务读取中所有可能出现的异常情况
游标
什么是游标
在SQL中游标是一种常用的数据库对象，可以指向存储在
数据表中的数据行指针，这里游标充当了指针的作用
数据表中的数据行指针，这里游标充当了指针的作用
游标是面向过程的编程方式，这与面向集合的编程方式不同
常用步骤
定义游标：DECLARE cursor_name CURSOR FOR select_statement
打开游标：OPEN cursor_name
从右表中取得数据：FETCH cursor_name INTO var_name...
关闭游标：CLOSE cursor_name
释放游标：DEALLOCATE PREPARE
说明：不同的REBMS的使用语法不同
游标性能
好处：灵活性强，可以解决复杂的数据处理问题，对数据进行逐行扫描处理
不足：使用游标的过程中会对数据进行加锁，当业务并发量大的时候，
会影响到业务的效率。同时游标实在内存中进行的处理，会消耗系统
资源，容易造成内存不足
会影响到业务的效率。同时游标实在内存中进行的处理，会消耗系统
资源，容易造成内存不足
建议：通常游标有替代方案的时候，可以采用替代方案，如果实在
绕不开有时候还是会用到游标
绕不开有时候还是会用到游标
使用场景：处理一些复杂的数据计算的时候：
比如对王者荣耀所有英雄的物攻按不同比例提升等级，
比如对王者荣耀所有英雄的物攻按不同比例提升等级，
进阶
设计范式
设计范式的种类
1NF(第一范式)、2NF(第二范式)、3NF(第三范式)、BCNF(巴斯范式)、4NF(第四范式)
和5NF(第五范式，又叫完美范式)
和5NF(第五范式，又叫完美范式)
越高的范式数据的冗余度越低
高阶的范式一定符合低阶范式的要求
键&属性的定义
超键：能唯一标识元组的属性集
候选键：如果超键不包括多余的属性，那么这个超键就是候选键
主键：用户可以从候选键中选一个作为主键
外键：如果数据表R1中的某属性集不是R1的主键，而是另一个数据表R2的主键，那
么这个属性集就是数据表R1的外键
么这个属性集就是数据表R1的外键
主属性：包含在任一候选键中的属性
非主属性：与主属性相对的，指的是不包含在任一候选键中的属性
三种设计范式
1NF(第一范式)：数据库表中的任何属性都是原子的，不可再分。
2NF(第二范式)：指的是数据表里的非主属性都要和这个数据表的候选键有完全依赖关系
3NF(第三范式)：首先需要满足2NF，然后对于任何非主属性都不传递依赖于候选键
3NF的不足
BCNF
3FN存在的问题：即使数据表符合3FN的要求，同样存在插入、更新、删除的异常情况
BCNF，也就是巴斯范式，它在3FN的基础上消除主属性对于候选键的部分依赖或者传递依赖
数据库与数据仓库的区别
数据库设计的目的在于捕获数据，而数据仓库设计的目的在于分析数据
数据库对数据的增删改实时性要求强，存储在线用户数据，而数据仓库存储的一般是历史数据
数据库设计尽量避免冗余，但有时候为了提供查询效率也允许一定的冗余度，
而数据仓库在设计上采用反范式更加常见
而数据仓库在设计上采用反范式更加常见
反范式设计
范式的目的是降低数据冗余度，而反范式会增加数据冗余度
如果多张表进行关联查询的时候，想要提升查询效率，可允许适当的数据冗余度
如果想要查询历史快照，可以考虑增加数据冗余度，采用反范式设计
数据仓库通常使用反范式设计
实际工作中，需要根据需求将范式和反范式结合使用
数据库调优
调优的目标
目标
让数据库运行的更快，也就是响应时间更快，同事吞吐量更大
微观反馈
用户反馈：业务的使用者，有些问题往往是用户第一时间发现的
日志分析：通过数据库日志，操作系统日志等找出异常情况，定位问题位置。
宏观反馈
服务器资源使用监控：CPU、内存、IO等使用情况。
数据库内部状况监控：活动会话（Active Session）监控了数据库是否
当前处于非常繁忙的状态，是否存在SQL堆积等，还可以对事务、锁等进行监控。
当前处于非常繁忙的状态，是否存在SQL堆积等，还可以对事务、锁等进行监控。
调优的方法
1、选择合适的DBMS
2、表设计优化
尽量采用第三范式原则
适当进行反范式优化
表字段的数据类型选择
3、逻辑查询优化
采用SQL语句等价交换的方式，对查询进行重写，数学基础是关系代数
SQL查询从写包括：子查询优化、等价谓词重写、条件优化、连接消除、嵌套连接消除等。
4、物理查询优化
物理查询优化是将逻辑查询执行计划变成物理操作符，为执行器提供准备
创建索引
如果数据重复度高，就不要创建索引（比如重复度超过10%）
索引的位置对索引使用的影响
联合索引对索引使用的影响
多个索引对索引使用的影响
确定访问路径
单标扫描：全表扫描、局部扫描
两张表连接：嵌套循环链接、HASH连接、合并连接
多张表连接：不同表的连接顺序导致不同的查询效率，及不同的索引空间
5、加缓存
Redis：支持持久化、更多的数据类型
Memcached：适合简单的key-value存储
6、库级优化
原则：通过切分的方式提高单一数据服务器的性能
主从复制：读写分离降低主数据库的负载
master完成写操作
slave完成读操作
使用mysql自带的分区表
垂直切分
垂直分库：将一个库中的多个数据表进行拆分，相关的数据表部署在一个库中
垂直分表：数据表中的列过多，采用垂直分表的方式，将数据表拆分成多张
水平切分
如果数据表中的数据达到了亿级以上，可以考虑进行水平切分，也就是大的数据表
分拆成不同的子表，每张表保持相同的数据结构
分拆成不同的子表，每张表保持相同的数据结构
如何思考数据库调优
选择比努力更重要：选择不同的DBMS，数据表的设计
SQL查询优化：逻辑查询优化、物理查询优化
通过外援来增强性能：Redis/Memcached，主从复制，分库分表
索引
索引概览
索引的作用
概念：索引就是帮助数据库管理系统高效获取数据的数据结构
索引不是万能的
数据表中的数据行较少的情况下，不需要创建索引（比如少于1000）
当数据重复度大，就不需要对这个字段使用索引（比如高于10%）
索引的种类
功能逻辑
普通索引：是基础索引，没有任何约束，主要用于提高查询效率
唯一索引：在普通索引的基础上增加了数据唯一性的约束，在一张数据表里可以有多个唯一索引
主键索引：在唯一索引的基础上增加了为空的约束，也就是NOT NULL+UNIQUE，
同时一张表里最多只有一个主键索引
同时一张表里最多只有一个主键索引
全文索引：Mysql自带的全文索引只能用在InnoDB和MyISAM，并且只能对应为进行全文索引
物理实现
聚集索引：按照主键的维度来排序存储数据，对数据的查询效率高
非聚集索引：在数据库系统会有单独的存储空间存放非聚集索引，这些索引是顺序存储的，但索引项只想的内容是随机存储的
字段个数
单一索引：索引列为一列时为单一索引
联合索引：多个列组合在一起创建的索引
索引的数据结构
索引的数据结构评价
数据库服务器有两种存储介质：硬盘和内存。内存是临时存储，硬盘是永久存储介质
索引只能放到硬盘中，因此磁盘IO次数决定了索引数据结构查询性能的好坏
二叉树
如果key大于根节点，则在右子树中进行查找
如果key小于根节点，则在左子树中进行查找
如果可以等于根节点，也就是找到了这个根节点，返回根节点即可
AVL树：平衡二叉树。每个节点的左子树和右子树 的高度差不能超过1
采用二叉树的数据结构，树的高低太大，磁盘IO操作多，影响查询效率
B树
B树：Banlance Tree，也就是平衡的多路搜索树
特点：它的高度要远小于平衡二叉树的高度，所以在文件系统和数据库系统中的
索引经常采用B树来实现。
索引经常采用B树来实现。
B树的特性
根节点的儿子数的范围是[2,M]
每个中间节点包含k-1个关键字和k个孩子
叶子节点包括k-1个关键字（叶子节点没有孩子）
所有叶子节点位于同一层
B+树
B+树是基于B树的改进
与B树的差异
有k个孩子的节点机油k个关键字
非叶子节点的关键字也会存在在子节点中，并且是在子节点中所有关键字的最大（会最小）
非叶子节点仅用于索引，不保存数据记录。跟记录有关的信息都放在叶子节点中
所有关键字都在叶子节点出现，叶子节点构成一个有序的链表，而且叶子节点本身
按照关键字的大小从小到大顺序链接
按照关键字的大小从小到大顺序链接
查询效率对比
查询效率更稳定
查询效率更高
范围查询更方便
Hash索引
理解Hash
Hash函数，又称为散列函数，通过它可以大幅提升我们检索数据的效率
Hash是现代密码学的核心，它被用于各种安全验证场景，比如密码存储，文件验证系统
MD5就是Hash函数的一种，Hash函数有多重映射方式
Mysql中的Hash索引
Hash索引流程：键值=>桶=>数据行
桶（bucket）指的是一个能存储一条或多条记录的存储单位
Hash冲突：多个键值对应同一个桶，会在桶中进行键值的查找
Hash值的字节数比较少，简单的4个字节就够了，多的16位或者32位
Mysql中采用的是“自适应Hash索引”的方式
B+树索引的区别
Hash索引不能进行范围查找
Hash索引不支持联合索引的最左侧原则，即联合索引的部分索引无法使用
Hash索引不支持ORDER BY排序
Hash索引不能进行模糊查询
如果字段的重复值较多，Hash索引效率可能比B+树索引效率低
索引的使用原则
什么情况下使用索引
字段的值有唯一性的限制，比如用户名
频繁作为WHEWE查询条件的字段，尤其数据量大的情况下效果更明显
需要经常GROUP BY和ORDER BY的列
UPDATE、DELETE、的WHERE条件列，一般也需要创建索引
DISTINCT字段需要创建索引
多表连接JOIN时
连接的表的数量不要超过3张
对WHEWE条件创建索引
对用于连接的字段创建索引，并且该字段在多张表中的类型必须一致
什么时候不需要创建索引
WHEWE条件（包括GROUP BY、ORDER BY）里用不到的字段，不需要创建索引
表记录太少，比如少于1000，是不需要创建索引的
字段中如果有大量重复数据，就不用创建索引
频繁更新的字段不一定要创建索引，因为有很高的索引维护成本
采用二叉树的数据结构，树的高度越大，磁盘IO操作越多，影响查询效率
什么情况下索引失效
如果索引进行了表达式计算，则会失效
如果对索引使用函数，则索引失效
在WHERE子句中，如果是OR条件，如果有一个条件列没有进行索引，那其他索引也会失效
当使用LIKE进行模糊查询的时候，后面不是“%”
索引尽量设置为NOT NULL约束（可以更好的利用索引）
使用联合索引的时候注意最左原则
理想索引
索引片
索引片就是SQL查询语句在执行中需要扫描的一个索引片段，
你可以把它理解为定义索引匹配列的数量
你可以把它理解为定义索引匹配列的数量
宽索引：索引片中的匹配数量多，大于2；窄索引：索引中匹配数量少，1~2
如何通过款索引避免回表：将SELECT中的字段添加到索引片中，避免回表
过滤因子的计算：这个满足这个谓词条件列的记录除以总记录数的比例
过滤因子的条件过滤能力越强，满足条件的记录数就越少，SQL查询需要扫描的索引
片也就越小，同理，如果没有选好过滤因子，就会造成索引片中的记录数过多
片也就越小，同理，如果没有选好过滤因子，就会造成索引片中的记录数过多
三星索引
Start1，在WHERE条件中，找到所有等值谓词中的条件列，作为索引片中的开始列
Start2，将GROUP BY和ORDER BY中的列加入到索引中
Start3，需要将SELECT字段中剩余的列加入到索引中
原理：Start1最小化索引片，Start2避免排序，Start3避免回表查询
理想索引设计很难存在
采用三星索引会让索引变宽，这样每个页能够存储的索引就会变少，从而增加了加载的数量。
虽然提升了单个SQL查询的效率，但是增加了索引维护的成本
设计合理的索引
一张表的索引树不宜过多
在索引片中，需要控制索引列的数量
单列索引和复合索引的长度需要控制
没有理想索引，只有适合的索引设计。需要在索引效率和维护成本中进行平衡
MySQL为什么会选错索引
优化器逻辑
工作
选择索引是优化器的工作
选择索引的目的是找一个最优的执行方案，
并用最小的代价去执行语句
并用最小的代价去执行语句
优化器还会结合扫描行数、是否使用临时表、是否排序等因素进行综合判断
如何判断扫描行数
根据统计信息估算记录数，这个统计信息就是索引的“区分度”。
一个索引上不同的值越多，这个索引的区分度就越好
一个索引上不同值的个数，称之为“基数”
统计基数的方法：采样统计
InnoDB默认混选择N个数据页，统计这些页面上的不同值，
得到一个平均值，然后乘以这个索引的页面数，就得到了
这个索引的基数。
得到一个平均值，然后乘以这个索引的页面数，就得到了
这个索引的基数。
而数据表是会持续更新的，索引统计信息也不会固定不变，
所以，当变更的数据行超过1/M的时候，会自动触发重新
做一次索引统计。
所以，当变更的数据行超过1/M的时候，会自动触发重新
做一次索引统计。
统计方式
innodb_stats_persistent
on：表示统计信息会持久化存储，默认的N是20，M是10
off：表示统计信息只存储在内存中。默认N是8，M是16
基数越大，索引的区分度越好
show index查看一个索引的基数
如show index from t；
如show index from t；
索引选择异常处理
采用force index强行选择一个索引
例：select * from t force index(a) where ...
例：select * from t force index(a) where ...
修改语句，引导MySQL使用我们期望的索引
例：把“order by b limit 1”改成“order by b,a limit 1”
例：把“order by b limit 1”改成“order by b,a limit 1”
创建一个更合适的索引，来提供给优化器做选择，或删掉无用的索引
如何给字符串字段加索引
前缀索引
区分度越高越好，重复的值越少
对覆盖索引的影响
使用前缀索引就用不上覆盖索引对查询性能的优化了
场景1：邮箱
定义字符串的一部分作为索引
例：alert table SUser add index index(email(6));
例：alert table SUser add index index(email(6));
不能使用覆盖索引
场景2：身份证号
倒序存储，在创建前缀索引，用于绕过字符串
本身的前缀区分度不够的问题，不支持范围扫描
本身的前缀区分度不够的问题，不支持范围扫描
创建hash自段索引，查询性能稳定，有额外的存储和计算
消耗，不支持范围扫描
消耗，不支持范围扫描
SQL性能分析
优化步骤
观察分析状态
S1，观察服务器的状态Status
S2、开启慢查询，定位执行的SQl
S3、EXPLAIN查看执行计划，或者使用show profile来查看每步骤的时间成本
优化方案
A1、加缓存，或者更改缓存失效策略的方式来解决
A2、如果SQL等待时间长，可以调优服务器参数，比如适当增加数据库缓冲池等
A3、如果SQL执行时间长，考虑是否索引设计问题，还是查询关联的数据表过多，
还是因为数据表字段设计问题等。
还是因为数据表字段设计问题等。
A4、如果已经达到了性能瓶颈，考虑增加服务器，采用读写分离的架构
或者考虑对数据库进行分库分表，比如垂直分库，垂直分表，水平分表等
或者考虑对数据库进行分库分表，比如垂直分库，垂直分表，水平分表等
慢查询定位
查询是否开启：show variables like "%low_query_log"
查询时间阈值：show variables like "%long_query_time%"
使用MySQLdumpslow工具分子
-s：采用order排序的方式
-t：返回前N条数据
-g：后面可以使正则表达式，对大小写不敏感
EXPLAIN查看执行计划
可以得到什么
id：数据表的读取顺序
select_type：SELECT子句的类型
type：数据表的访问类型
possible_keys：哪些索引可以使用
key：实际使用的索引
key_len：使用的索引长度
ref：上一个表的连接匹配条件
rows：有多少行被优化器查询
Extra：额外的信息
SQL执行的顺序是根据id从大到小顺序排序，当id相同时，从上到下进行执行
type字段
all：全数据表扫描
index：全索引扫描
range：对索引进行范围查找
index_merge：合并索引，使用多个单列索引搜索
ref：根据索引查找一个或多个值
eq_ref：搜索使用primary key或unique类型，常用语多表联查
const：常量，表最多有一个匹配行，因为只有一行，在这行的列值可以被优化器
认为是常数
认为是常数
system：系统，表只有一行（一般用于MyISAM或InnoDB表）
效率从低到高依次为：all<index<range<index_merge<ref<eq_ref<const/system
show profile查看执行时间
查看是否开启：show variables like 'profiling'
当前会话都有哪些Profiles：show profiles
查看某Query_ID开销：show profile for quert XX
明令将被弃用，可以从information_schema中的profiling数据表进行查看
0 条评论
下一页
