数据库总结
2023-03-25 22:02:15 0 举报
AI智能生成
数据库相关知识总结
作者其他创作
大纲/内容
数据库基础
关系型数据基础知识
范式
第一范式
每个字段都是原子,不可再分割
关系型数据库最基本要求
第二范式
满足第一范式的前提下,每一行的数据只能与其中一列相关,记录需要有唯一标记
第三范式
满足第二范式的前提下,每个字段都是直接与主键过关系,其他字段之间不能存在传递关系
数据存储
数据库引擎
innerDB
文件结构
FRM文件
表结构文件
IBD文件
索引数据+表数据
锁
支持行锁,表锁
行锁
记录锁
通过锁定索引记录来实现;锁定某一个索引记录
使用场景
只用通过索引条件查询数据的时候,才会使用到
作用
间隙锁
通过索引记录来实现
对一定返回内数据进行加锁,即使这数据不存在也是会加锁
Select * from emp where empid > 100 for update;会对empid大于100的都加上间隙锁,即使记录不存在也会添加
使用场景
作用
Next-key锁
相当于一个记录锁+间隙锁
使用场景
作用
避免幻读
插入意向锁
插入数据之前,由insert操作设置的一种间隙锁
表锁
意向共享锁
给数据行加行级锁之前需要取道该表的意向共享锁
意向排他锁
给数据行家排他锁之前需要取到该表的意向排他锁
日志
redolog(innerDB引擎独有)
redolog日志类容
记录后事物执行之后的状态,用来记录未写入 datafile 的已成功的事物更新的数据;
日志作用
在MySQL宕机时候,还有数据没有写入磁盘,可以通过重做日志重做,从而达到数据的持久化
日志生成
事物开始的时候就会有日志生成,随着事物的进行的过程中,日志逐渐写入redolog中
日志清理
当某个事物对应的数据写入 datafile中后,redolog的使命已经完成了,重做日志的空间就会被重用
undolog
undolog生成时间
数据库的每一次修改(insert,update,delete)操作,在落到磁盘之前都会先记录到undolog
undolog日志内容
记录信息修改之前和修改之后的数据
undolog日志回滚操作
根据undolog日志做数据库的逆向操作,原来的insert操作变成delete操作,delete变成insert,update变成逆向update
Myisam
文件结构
frm文件
存储的是表结构信息
MYD文件
存储的是数据行记录信息
MYI文件
存储索引对应的数据信息
myisam工作流程
根据索引
锁
支持表锁,不支持行锁
innerDB和Myisam区别
索引数据结构
myisam索引的B+树的叶子节点存储的是该条数据的地址值
innerDB索引的B+树的叶子节点存储的是该条数据的全部字段值(主键索引)
innerDB索引的非主键索引叶子节点存储的是id值(非主键索引)
锁区别
innerDB支持行锁,表锁;myisam支持表锁
事物
innerDB支持事物;myisam不支持事物(原因其实主要是myisam不支持行锁,间接导致不能支持事物)
是否支持全文索引
MySQL 5.6 以前的版本,只有 MyISAM 存储引擎支持全文索引
MySQL 5.6 及以后的版本,MyISAM 和 InnoDB 存储引擎均支持全文索引;
是否支持外键
innerDB支持外键;myisam不支持外键
主键是否必须
innerDB支持必须有主键;myisam可以没有主键
数据存储不同
innerDB数据存储frm、ibd文件;frm是表定义文件,ibd是数据文件
Myisam是frm、MYD、MYI;frm是表定义文件,myd是数据文件,myi是索引文件
innerDB数据索引数据和表数据是存储在一起;而myisam引擎索引数据和表数据是分开存储在两个文件
是否存储具体行数
innerDB不存储表的具体行数;myisam存储表的具体行数
是否支持表压缩
innerDB不支持表数据压缩;myisam支持表数据压缩,压缩后支持只读,修改操作需要加压才能操作;
memory
锁
支持表锁,不支持行锁
在内存中存在表数据,数据库重启之后,该引擎对应的数据就会消失
默认是使用hash索引
Merge
存储引擎允许将一组使用MyISAM存储引擎的并且表结构相同的数据表合并为一个表,方便了数据的查询
csv
其他存储引擎包括CSV(引用由逗号隔开的用作数据库表的文件)
ARCHIVE
为大量很少引用的历史、归档、或安全审计信息的存储和检索提供了完美的解决方案。
BLACKHOLE
用于临时禁止对数据库的应用程序输入
FEDERATED
能够将多个分离的MySQL服务器链接起来,从多个物理服务器创建一个逻辑数据库。十分适合于分布式环境或数据集市环境。
EXAMPLE
可为快速创建定制的插件式存储引擎提供帮助
BDB
可替代InnoDB的事务引擎,支持COMMIT、ROLLBACK和其他事务特性。
Cluster/NDB
MySQL的簇式数据库引擎,尤其适合于具有高性能查找要求的应用程序,这类查找需求还要求具有最高的正常工作时间和可用性
索引
索引分类
使用方式分类
单列索引
主键索引
根据主键排序做的索引,特殊的唯一索引
唯一索引
强制让表中某个字段值不能重复
普通单列索引
根据某一列字段建立的索引
组合索引
把建立索引的字段值全部兼容在一起组成的索引
聚簇非聚簇
聚簇索引(一级索引)
索引的顺序和数据存储的顺序是保持一致
主键索引就是数据聚簇索引
非聚簇索引(二级索引)
索引的存储顺序和数据的存储顺序不是一致
非主键索引全部都是属于聚簇索引
稀疏密集
稀疏索引
多条数据可以对应一条索引数据
非主键索引和有可能就是稀疏索引
密集索引
数据和索引是一一对应的
主键索引就是密集索引
索引数据结构
B+树
B+树的数据存储结构
数据库引擎不同B+树存储的数据不完全相同
innerDB的的B+树叶子节点存储的是全部的数据或者是主键id;
myisam的B+树叶子节点存储的时候该条数据地址值(不管是主键索引还是其他索引)
B+树和B树区别
B+树的非叶子节点存储的是节点之间的前后指向关系,并不存储实际的数据值;B树的只要是节点不管是叶子节点还是非叶子节点都会存储数据。
B+树的节点存储兄弟节点的地址值,可以直接指向兄弟节点;而B树是不可以的。
在相同数据层级,B+树能够存储更多的数据;而B树存储的数据少很多;
使用场景
myisam和innerDB索引都是使用B+树
hash索引
基于hash算法的散列索引
特点
查询单条数据查询时间的复杂度为01;
范围查询速度比较低
使用场景
memory引擎默认使用该中索引
全文索引
MySQL 5.6 以前的版本,只有 MyISAM 存储引擎支持全文索引;
MySQL 5.6 及以后的版本,MyISAM 和 InnoDB 存储引擎均支持全文索引;
使用
只有字段的数据类型为 char、varchar、text 及其系列才可以建全文索引。
索引开销
创建和维护索引都需要耗费时间和磁盘空间
修改语句操作的时候,需要对索引进行重新构建
索引相关概念
回表查询
查询的时候根据组合索引去查询,从索引中获取到的数据并未覆盖所需要 查询的全部字段,所以就需要那到索引中的id再回到表中去查询需要的字段,这就是回表
非索引下推
查询的时候,先根据组合索引中最左索引查询,拿到这些查询之后的数据的主键,再回表查询出整条数据,再从整条数据从按照其他条件做筛选;
索引下推
如果查询条件中包含了组合索引,那么就会优先使用组合索引查询,查询满足组合索引中最左索引的值,从这些筛选一遍的数据中再过滤出其他符合条件的数据,不用再次回表查询;
最左匹配原则
组合索引中,如果查询条件中并没有带上组合索引中最左边的索引,那么这个查询是不会先去查询组合索引
覆盖索引
查询的是主键索引字段,也就是查询的数据本身就是索引,直接从索引中拿数据,不需要再去根据主键回表查询其他数据
一次性非锁定读
就是普通的select
一次性锁定读
在select 语句后面中加 for update 或者是 lock in share mode
查询需走索引
mysql
mysql基础概念
锁
锁的分类
按照粒度划分
表锁
表锁是由MySQL本身来实现
表锁具体实现
意向共享锁
给数据行加行级锁之前需要取道该表的意向共享锁
意向排他锁
给数据行家排他锁之前需要取到该表的意向排他锁
概述
意向锁,锁是由innerDB自动添加,无需用户手动添加
行锁(innerDB实现)
记录锁
间隙锁
Next-key锁
插入意向锁
锁分类说明
乐观锁,悲观锁都是在并发事物的一种控制方式,是一种思想,不是实实在在的锁
行锁,表锁也并非实实在在的锁,只是锁的粒度来划分的锁
二阶段锁
概述
关系型数据库的一种加锁原则;把锁的操作划分为两个阶段,加锁阶段,解锁阶段
实际过程
加锁阶段,解锁阶段互不相交,加锁阶段,只加锁,不解锁;解锁阶段,只解锁,不加锁
ACID
基础概念
原子性
事物中发生多件事情,多件事情要么都成功,要么都失败
场景
A给B转钱,A成功的把钱给了B,B也成功的收到了A的钱
一致性
一种状态到另外一种状态必须保证数据逻辑上的一致
场景
A给B转1000块钱,A的账户扣了1000块,B的账户多了1000块
隔离性
当前事物不受其他事物影响
持久性
数据能够被持久化的存储在磁盘上
事物提交之后,该事物对应的修改数据都会被存储到磁盘上
ACID实现
原子性
因为需要在失败的时候做数据回滚操作,所以原子性是通过undolog来实现的
持久性
redolog来实现
隔离性
锁,mvcc来控制
一致性
通过原子性,持久性,隔离性来保证一致性
事物
事物隔离级别
脏读
同时开启AB两个事物,A事物可以看到B事物没有做事物提交的数据
A事物读取B事物更新的数据,B又做了回滚,A事物读到的是脏数据;
不可重复度
同时开启AB两个事物,A事物只能等到B事物提交了才能看到B事物的数据
A事物读取B事物更新的数据,B又做了回滚,A事物在B事物提交前后看到的数据是不一样的;
幻读
同时开启AB两个事物,A事物提交了,B事物还是看不到A事物更新的数据,只有B事物提交了才能看到A更新的数据;
A事物对某个数据做了修改,提交了事物,B事物也对某个事物做了修改,但是没有提交事物,这时候B事物在事物提交之前看到的是A事物修改的数值,只有等到B事物也提交了事物,才能看到B事物真正修改的值
序列化
同开启AB事物,A事物没有提交,B事物无法对A事物已经操作的过的任何其他数据进行操作
事物调度
并行
多个事物同时执行
串行
事物必须是一个一个的去执行,不能并发的去执行
并发事物控制
乐观控制
概念
每次访问数据的时候,都认为是查询操作,其他事物也能访问当前事物的数据;造成的数据不一致性,采取的方式是修改数据之后,对修改的数据进行回滚操作
特点
1.读操作过多的情况下,能够显著提高效率
2.采取时候回滚操作来来解决事物冲突问题
具体实现
通过在需要更新的表中设立相关的数据版本号来做操作
MySQL乐观锁使用场景
可在分布式场景下控制多个服务对相同数据的修改是线性执行的;
悲观控制
概念
每次访问数据的时候,都会对数据进行加锁操作
特点
1.采取的是提前预防冲突
2.严格的控制其他事物对当前事物的访问,避免大量的数据回滚
3.其他事物,在某些情况下还是可以访问当前数据
具体实现
通过数据库本身的锁机制来实现悲观控制
mysql悲观锁使用场景
单体服务的时候就实现对并发事物的悲观控制
概述
对并发(交叉)事物的控制的方式;数据本身对并发事物的控制都是悲观控制
日志
binlog日志(二进制日志)
binlog日志作用
主从复制;主从复制就是为了读写分离
主从复制过程
1.主机每次完成实事物提交,完成数据更新之前,都会把变更的数据记录在binlog日志中
2.从机开启一个IO线程去从主机的binlog日志中读取数据,记录到自己的中继日志中;
3.从机开启线程,解析中继日志的事件,并执行,来做数据主从复制
数据库基于时间还原数据
读写分离;读写分离的前提就是主从复制
缓存一致性;模拟成从机,拉取binlog日志异步更新到缓存中的数据;
数据多备份
binlog日志格式
5.0之前
statment模式
以mysql事件形式记录的是实际执行的sql语句
5.0之后
row模式
以MySQL事件形式记录字段前后变化的值
binlog日志配置
max_binlog_size
日志文件的大小,默认1G
expire_logs_days
日志保留事件,默认是0,永久保留
记录所有的更新操作
查询日志
记录建立的客户端连接和执行的语句。
只记录查询相关的日志
更新日志
记录更改数据的语句。不赞成使用该日志。
5.1版本之前的日志,5.1版本之后被binlog日志替代
错误日志
记录内容
记录mysql启动关闭,以及运行过程中发生的错误信息
设置
错误日志默认是关闭
慢查询日志
记录内容
记录的是查询时间超过10秒(默认值)的sql语句
设置
慢查询日志默认是关闭
中继日志
场景用在主从复制中从服务器读取到主服务器的二进制日志存储起来就是中继日志,日志格式和二进制日志格式相同,都可以使用相同的binlog解析程序解析
MVCC版本控制
MVCC作用
1、解决读写冲突;
2、保证在事物并发条件下的读操作的效率,写操作的准确性。
MVCC控制分类
快照读
概述
读取记录的可见版本(可能是历史版本,也可能是当前版本)不用加锁
使用场景
不加锁的select操作
当前读
当前读就是读取的最新数据,而且还要保证其他事物不能修改当前数据
场景
加锁的读操作
select * from table where ? for update;
select * from table where ? lock in share mode;
当前写
概述
加锁读取记录的最新版本,保证其他事物不会并发修改这条记录;
使用场景
特殊的读操作
插入/更新/删除
insert into table values (…);
update table set ? where ?;
delete from table where ?;
因为修改操作之前,都是先需要查询到目标记录;
当前读,快照读,Mvcc关系
子主题
MVCC实现原理
依靠的时候undo日志,read view ,三个隐式字段
MYSQL架构
server层
概述
所有跨存储引擎的功能都是在server层来实现的;包括存储过程,触发器,视图,函数,binlog日志
其他组件
存储过程
被用户定义的 sql语句的集合
触发器
某个时间所触发的操作;一种特殊的存储过程;
视图
基于基表的一种逻辑表或者虚拟表
五大组件
示意图
子主题
连接器
管理用户对数据库的连接
用户权限管理
优化器
根据语句对sql语句中能够做优化的SQL自动做优化
优化器分类
CBO
基于优化成本来做优化(主流的数据库都是这样)
RBO
基于规则,SQL语句经过分析器之后,会有多种执行方式,程序会判断选择一个效率最高的SQL
分析器
包括词法分析,语法分析
缓存模块
8.0之后就移除缓存模块
移除原因
1.缓存命中率低
2.内存空间宝贵
mysql缓存
mysql缓存查询到的数据永远是最新的数据,如果中件表有发生变化,则该缓存相关的数据就会被清空
缓存命中率比较低,最理想的情况下命中率最高13%
缓存工作
查询必须是完全相同的(逐字节相同)才能被认为是相同的
同样的字符串由于其他原因也可能会被认为是不一样的
缓存相关查询
指定从缓存中查询
SELECT SQL_CACHE id, name FROM customer;
前提是缓存是出于开启的状态
query_cache_type系统变量的值是ON或DEMAND,查询结果被缓存
指定不从缓存中查询
SELECT SQL_NO_CACHE id, name FROM customer;
缓存相关参数
have_query_cache
表示当前是否有使用缓存
query_cache_size
查询缓存大小
如果设置为0,则认为是禁用缓存
query_cache_limit
被缓存的查询结果最大值
默认值1MB
query_cache_min_res_unit
系统变量给查询缓存分配最小值
默认值是4KB
执行器
SQL语句真正的执行者
存储引擎
概述
主要负责数据的读取和存储,采用可替代式插件架构,支持各种数据库引擎。
概述
MySQL主要分为server层和存储引擎层
mysql主从复制
实现原理
mysql的主从复制基于主服务器在二进制日志中跟踪所有对数据库的更改(更新、删除等等)
并且主服务器开启了二进制日志
实现过程
复制过程中有三个线程在执行任务:一个线程在主服务器,负责将二进制日志的内容发送到从服务器(binlog dump线程);
主从复制开始;从服务器上会创建一个io线程,连接主线程并记录二进制日志中的数据到中继日志;中间需要识别binlog dump线程 以获取二进制日志
从服务器会开启一个sql线程(第三个线程),从中继日志中读取日志,并执行日志,以同步数据;
主从复制条件
1、主服务器开启了二进制日志
2、主从服务器的mysql版本之间是可以相互兼容
由于历史原因:
1、二进制日志格式不一样
2、字符集,函数,时区处理不一样
3、主服务器上设置了相应的从服务器
SQL语句
left join ,inner join ,right join full join
left join ,inner join ,right join 的区别
left join 做关联,以左表为主表,去右表当中匹配,如果左表中的数据未能匹配到右表中的数据,结果集中依旧会将左表的数据展示出来,未能匹配到的字段全部为空
right join 做关联,以右表为主表,去左表当中匹配,如果右表中的数据未能匹配到左表中的数据,结果集中依旧会将右表的数据展示出来,未能匹配到的字段全部为空
inner join 做关联,最明显就是左右两个表中只有关联上的数据才会展示出来,没有关联上的数据不会展示出来;
left join ,inner join ,right join full join 演示
原始数据
子主题
.inner join
子主题
.left join
子主题
.right join
子主题
full join
子主题
mysql模式
性能模式
系统模式
mysql客户端脚本和使用工具
myisampack
压缩MyISAM表以产生更小的只读表
mysql
交互式输入SQL语句或从文件以批处理模式执行它们的命令行工具
mysqlaccess
检查访问主机名、用户名和数据库组合的权限的脚本
mysqladmin
执行管理操作的客户程序,例如创建或删除数据库,重载授权表,将表刷新到硬盘上,以及重新打开日志文件
mysqlbinlog
从二进制日志读取语句的工具。在二进制日志文件中包含的执行过的语句的日志可用来帮助从崩溃中恢复
mysqlcheck
检查、修复、分析以及优化表的表维护客户程序
mysqldump
将MySQL数据库转储到一个文件(例如SQL语句或tab分隔符文本文件)的客户程序
mysqlhotcopy
当服务器在运行时,快速备份MyISAM或ISAM表的工具
mysql import
使用LOAD DATA INFILE将文本文件导入相关表的客户程序
mysqlshow
显示数据库、表、列以及索引相关信息的客户程序
perror
显示系统或MySQL错误代码含义的工具
replace
更改文件中或标准输入中的字符串的实用工具
MYSQL其他知识点
Mysql事件
ReadView
mysql实战问题
mysql添加表字段会不会锁表
5.6之前版本
1、对原始表加写锁
2、按照原始表和执行语句的定义,重新定义一个空的临时表
3、对临时表进行添加索引(如果有
4、再将原始表中的数据逐条Copy到临时表中
5、当原始表中的所有记录都被Copy临时表后,将原始表进行删除。再将临时表命名为原始表表名
5.6版本以之后版本
1、对原始表加写锁
2、按照原始表和执行语句的定义,重新定义一个空的临时表。并申请rowlog的空间
3、拷贝原表数据到临时表,此时的表数据修改操作(增删改)都会存放在rowlog中。此时该表客户端可以进行操作的
4、原始表数据全部拷贝完成后,会将rowlog中的改动全部同步到临时表,这个过程客户端是不能操作的
5、当原始表中的所有记录都被Copy临时表,并且Copy期间客户端的所有增删改操作都同步到临时表。再将临时表命名为原始表表名
myslq添加索引会不会锁表
肯定是会的
解决方式
前期设计的时候想好设计表的索引
mysql主从同步延迟解决方案
1、延迟原因
1、从机读取主机binlog日志的线程只有一个,如果主机有大量更新操作。从机是无法快速将这些binlog读取到从机
2、从机如果有执行慢的sql,也会导致无法快速处理读取到的binlog日志。
以上两点导致主从不一致的原因
2、解决方案
1、将从机的binlog日志取消,提高从机sql执行效率
2、将主从服务器放在同一个网络下,减少网络延迟
3、具体的硬件优化
4、调参
MySQL调优
执行计划详解
id(sql执行顺序)
id相同,从上到下顺序执行
id不同,id值越大执行优先级越高,越先被执行
select_type查询类型
SIMPLE
简单的单表select查询
PRIMARY
子主题 1
SUBQUERY/MATERIALIZED
SUBQUERY
表示select或者是where中含有子查询
MATERIALIZED
表示where后面的in条件有子查询
UNION
被关联查询的语句;也就是关联查询的union中的第二个语句或者是union后面的select语句
UNION RESULT
表示关联查询的结果
table(查询涉及到的表)
直接显示表明
<unionM,N>
由M,N两个表关联产生的结果
<subqueryN>
有N表产生的中间结果集维表
type (重点,查询性能指标)
system
查询的是系统表
const
单表查询直接命中主键查询
demo
CREATE TABLE `user` ( `id` int(11) NOT NULL, `NAME` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;insert into user values(1,'shenjian');insert into user values(2,'zhangsan');insert into user values(3,'lisi');
explain select * from user where id=1;
eq_ref
关联查询用到主键索引做为关联字段
demo
CREATE TABLE `user` ( `id` int(11) NOT NULL, `NAME` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;insert into user values(1,'shenjian');insert into user values(2,'zhangsan');insert into user values(3,'lisi');CREATE TABLE `user_ex` ( `id` int(11) NOT NULL, `age` int(11) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;insert into user_ex values(1,18);insert into user_ex values(2,20);insert into user_ex values(3,30);insert into user_ex values(4,40);insert into user_ex values(5,50);
EXPLAIN SELECT * FROM USER,user_ex WHERE user.id=user_ex.id;
ref
关联查询用到了非主键索引作为关联字段
demo
EXPLAIN SELECT * FROM USER,user_ex WHERE user.id=user_ex.id;
range
在索引上的范围查询
demo
CREATE TABLE `user` ( `id` int(11) NOT NULL, `name` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;insert into user values(1,'shenjian');insert into user values(2,'zhangsan');insert into user values(3,'lisi');insert into user values(4,'wangwu');insert into user values(5,'zhaoliu');
explain select * from user where id between 1 and 4;explain select * from user where id in(1,2,3);explain select * from user where id > 3;
index
查询是在所引述中扫描
demo
CREATE TABLE `user` ( `id` int(11) NOT NULL, `name` varchar(20) DEFAULT NULL, PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;insert into user values(1,'shenjian');insert into user values(2,'zhangsan');insert into user values(3,'lisi');insert into user values(4,'wangwu');insert into user values(5,'zhaoliu');
explain count (*) from user;
ALL
全表扫描
demo
CREATE TABLE `user` ( `id` int(11) DEFAULT NULL, `name` varchar(20) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;insert into user values(1,'shenjian');insert into user values(2,'zhangsan');insert into user values(3,'lisi');CREATE TABLE `user_ex` ( `id` int(11) DEFAULT NULL, `age` int(11) DEFAULT NULL) ENGINE=InnoDB DEFAULT CHARSET=utf8;insert into user_ex values(1,18);insert into user_ex values(2,20);insert into user_ex values(3,30);insert into user_ex values(4,40);insert into user_ex values(5,50);
explain select * from user,user_ex where user.id=user_ex.id;
查询的结果只的好坏顺序system > const > eq_ref > ref > range > index > ALL
CREATE TABLE `user` ( `id` int(11) DEFAULT NULL, `name` varchar(20) DEFAULT NULL, KEY `id` (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;insert into user values(1,'shenjian');insert into user values(2,'zhangsan');insert into user values(3,'lisi');CREATE TABLE `user_ex` ( `id` int(11) DEFAULT NULL, `age` int(11) DEFAULT NULL, KEY `id` (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf8;insert into user_ex values(1,18);insert into user_ex values(2,20);insert into user_ex values(3,30);insert into user_ex values(4,40);insert into user_ex values(5,50);
possible_key
查询过程中有可能用到的索引
key
实际使用的索引,如果为 NULL ,则没有使用索引
key_len
展示索引字段的实际长度
ref
显示该表的索引字段关联了哪个表的哪个字段
rows
估算查询所需要的记录需要读取的行数
filtered
返回的结果集行数占读取行数的百分比,越大越好
extra
展示其他重要信息
调优方向
1.sql调优
查询尽量覆盖到索引
1.在经常当做查询条件的字段上设置索引
未覆盖到索引情况
1.反向查询,!=,<> ,not in
2.前模糊查询
3.查询语句中在索引字段上做函数操作
4.关联字段虽然都是索引字段,如果数据类型不一致,走不了索引
5.where后面不带有效查询条件
6.建立联合索引,查询的时候不满足最左原则;
7.查询中对索引字段做运算操作
索引建立原则
索引长度限制
在字符串类型的字段上建立索引,需要指定索引的长度,一般10个就够了;
索引建立字段选取
1、必须要有主键索引
2、在经常做为查询条件,排序,分组,关联的字段上建立索引;
表的关联字段建立索引的时候确保两个表的数据类型是一样的
3、索引建立的字段应该是区分度非常高的字段;
索引数量
1、索引的数量不是越多越好,多了会增加索引构建维护成本;
2、不再使用或者使用非常少的索引需要删除索引
3、新建单列索引的时候,考虑和其他字段一起建立联合索引,减少索引个数
查询需要覆盖到索引
2.调整库,表结构
1、表的字段类型,字段长度合理
2、创建高性能的索引
3.系统配置优化
相关操作命令
查询服务器默认缓存区大小
mysqld --verbose --help命令生成所有mysqld选项和可配置变量的列表
SHOW VARIABLES;
查看当前运行的mysql服务器实际运行的值
SHOW STATUS;
运行服务器的统计和状态指标
系统变量和状态信息
mysqladmin variables
mysqladmin extended-status
优化核心参数
key_buffer_size
MYISAM引擎下面的索引缓存所占内存大小
table_cache
mysql同时打开表的数量
read_rnd_buffer_size
线程的缓冲区,注意不是线程的栈内存;
对GROUP BY或ORDER BY读取数据的时候会暂存读取的数据,
max_connections
最大连接数
默认151
可以适当设置大一些
thread_cache_size
线程池线程大小
查询相关
排序缓冲区
sort_buffer_size
默认为2MB
为排序或者分组的线程设置的缓冲区大小,可以提高排序分组的效率
关联查询缓冲区
join_buffer_size
默认为8Mb
为每个链接做联合查询操作的时候缓冲区的大小,提高关联查询效率
表的缓冲区
read_buffer_size
默认64k
每个线程扫描每一个表的锁分配的缓冲区大小
innerDB相关
innodb_buffer_pool_size
innerDB引擎下表和索引的最大缓存
可以缓存表数据还有索引数据
innodb_flush_log_at_trx_commit
innerDB日志刷盘策略
innodb_log_buffer_size
innerDB的失误日志缓冲区
4.硬件优化
数据分散到多个磁盘
提高搜索效率
将数据分布在多个磁盘上,提高单位时间内可以并行搜索磁盘次数
单个磁盘每秒大约1000次搜索
提高读写吞吐量
提高cpu频率
提高cpu缓存大小
概述:以上四个方向,效果最好的是SQL调优,以此往下效果越来越差,成本越来越高
sql优化实战
limit优化
demo
CREATE TABLE `test_news` (`id` int(11) NOT NULL AUTO_INCREMENT,`title` varchar(100) DEFAULT NULL COMMENT '文章标题',`content` longtext COMMENT '文章内容',`channel` int(11) DEFAULT NULL COMMENT '文章频道',`status` int(11) DEFAULT NULL COMMENT '状态,1正常,0关闭',`create_time` datetime DEFAULT NULL COMMENT '文章发布时间',PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=513 DEFAULT CHARSET=utf8;
原始查询方式
select* from test_news limit 10000,10;
查询每页是10条,查询低10000页的数据;这种查询完全就是没有用到索引;需要扫描到10000条数据,再开始查询
优化方式1
select* from test_news where id >( select id from test_news limit10000,1) limit 10;
解析
1.子查询select id from test_news where limit10000,1;只查询一个id,根据主键就能查询到,不用回表查询;
2.拿到查询出来的id,获取到后面10个id,再用这10个id直接可以直接回表拿到所有的数据;
优化方式2
select* from test_news a join ( select id from test_news limit10000,10) b on a.id = b.id ;
解析
相当于自关联的方式获取到相应的id;
再通过id回表获取所有的数据
分组取前几条
demo
CREATE TABLE `test_news` (`id` int(11) NOT NULL AUTO_INCREMENT,`title` varchar(100) DEFAULT NULL COMMENT '文章标题',`content` longtext COMMENT '文章内容',`channel` int(11) DEFAULT NULL COMMENT '文章频道',`status` int(11) DEFAULT NULL COMMENT '状态,1正常,0关闭',`create_time` datetime DEFAULT NULL COMMENT '文章发布时间',PRIMARY KEY (`id`)) ENGINE=InnoDB AUTO_INCREMENT=513 DEFAULT CHARSET=utf8;
SELECT a.id, a.title, a.channel,a.create_time FROMtest_news AS a LEFT JOIN test_news AS b ON a.channel = b.channelAND a. STATUS = b. STATUS AND a.id < b.id WHEREa. STATUS = 1 GROUP BY a.channel, a.id HAVINGcount(1) < 5 ORDER BY a.channel ASC, a.id DESC;
having
demo
sELECT CLASS,SUM(TOTAL_SCORES) FROM student_score GROUP BY CLASS HAVING SUM(TOTAL_SCORES)>200;
SQL自动优化
mysql缓存
mysql缓存查询到的数据永远是最新的数据,如果中件表有发生变化,则该缓存相关的数据就会被清空
缓存命中率比较低,最理想的情况下命中率最高13%
缓存工作
查询必须是完全相同的(逐字节相同)才能被认为是相同的
同样的字符串由于其他原因也可能会被认为是不一样的
缓存相关查询
指定从缓存中查询
SELECT SQL_CACHE id, name FROM customer;
前提是缓存是出于开启的状态
query_cache_type系统变量的值是ON或DEMAND,查询结果被缓存
指定不从缓存中查询
SELECT SQL_NO_CACHE id, name FROM customer;
缓存相关参数
have_query_cache
表示当前是否有使用缓存
query_cache_size
查询缓存大小
如果设置为0,则认为是禁用缓存
query_cache_limit
被缓存的查询结果最大值
默认值1MB
query_cache_min_res_unit
系统变量给查询缓存分配最小值
默认值是4KB
mysql异样工作场景
mysql链式复制
主从复制中,从机又是另外一台的主机;
从服务器本身也可以当做主服务器
MySQL客户端程序和实用工具
数据库中间件
oneProxy
canal
子主题
分库分表
sharding-jdbc
oneProxy
mycat
Cobar
TDDL
atlas
0 条评论
下一页