本文主要是介绍MySQL,对大家解决编程问题具有一定的参考价值,需要的程序猿们随着小编来一起学习吧!
MySQL架构介绍
- 客户端:支持接口(标准的API、JDBC、ODBC、.NET、PHP、Phython等)
- 服务端:
- MySQL软件
- Server层
- 连接池:验证与授权、线程、内存与缓存管理
- 管理服务与工具:备份与恢复、安全、复制、集群、分区管理、事务管理、数据库模板管理、工作台、合并工具
- 核心业务处理层:
- SQL接口:数据管理语言和数据定义语言、存储过程、视图、触发器等
- 解析器:查询\事务 对象优先级
- 优化控制器:访问路径统计
- 缓存和缓冲池:全局和局部引擎的缓存和缓冲池
- 存储层
- 插件式存储引擎:MyISM、InnoDB、Cluster。。。。。
- 磁盘文件
- 文件系统:NTFS、UTS、EXT2/3、NFS、NAS。。。
- 文件和日志:REDO、UNDO、DATA。。。。
连接池
- show fulll processlist 查询所有连接
- show processlist 只能查询前100个连接
- 通过max_connections控制修改最大连接数
SQL接口:CRUD分流执行SQL语句(执行器)
Parser:解析器
Optimizer:查询优化器
Cache和Buffer:查询缓存(MySQL8.0去掉:缓存命中太差,同一条SQL语句才能命中)
SQL执行过程
- 连接器:客户端通过连接器与服务端简历连接(mysql -uroot -p123456),验证用户,建立连接
- 查询缓存
- 解析器和预处理
- 优化器
- 解析语法树的执行一般有多种计划
- 最终只能选择其中一种执行计划去执行
- 基于成本计算
- 根据索引的执行情况选择
- 一般一个SQL语句执行,只会选择一个索引去使用
- 如果通过join连接的多表,可以再查询计划中选择基表
- 自己写的SQL语句,不一定按照你写的条件执行
- 执行器
- 按照执行计划调用存储引擎接口,通过存储引擎去操作磁盘中的数据。
MySQL的存储引擎
存储引擎针对表制定,存储引擎也是一种性能优化
- InnoDB
- MySQL5.以后默认
- 特点:行锁、事务
- 适合于读写不分离,并发高的场景
- MyISAM
- 特点:不支持行锁、不支持事务
- 适合于读多、写少
- 写操作时,进行表级锁定
- CSV :csv文件
- Memory:内存的存储引擎,类似Redis
MySQL磁盘文件
查看磁盘文件位置:show variables like ‘datadir%’;可以通过datadir指定磁盘文件位置。
- 数据文件
- InnoDB
- frm表结构文件
- idb数据和索引文件(用户表空间)
- MyISAM
- ibdata系统表空间文件
- 日志文件
- binlog:二进制日志文件(数据恢复、主从复制)
- errlog:错误日志文件 mycentos.err
- slow_query_log:慢查询日志文件(默认关闭,性能分析)
- general_query_log:通用查询日志文件;
- redo_log:重做日志文件(保证事务ACID特性)ib_logfile0、ib_logfile1
- undo_log:回滚日志文件(保证事务ACID特性)ibdata1
- relay_log:中继日志文件
- 磁盘如何存储数据
-
- 随机写(数据文件:性能低)
- 旋转时间
- 寻找磁道
- 寻找扇区
- 判断扇区是否占用
- 占用继续上面
- 顺序写(日志文件:性能高)
-
MySQL数据在磁盘中存取
- MySQL在磁盘中存数据
- 磁盘和MySQL交互
- 磁盘和操作系统(内核空间)交互
- 磁盘中最小的存储单位是扇面(扇区),默认最小512字节,小于512字节也占用512字节
- 磁盘和操作系统(内核空间)交互最小单位是4K,该大小由操作系统限制
- 操作系统和MySQL应用(用户空间)交互
- 操作系统和MySQL应用交互的最小单位是16K,该大小可以通过参数(innodb_page_size)控制,索引结构中的节点是一个物理页的大小
- 思考:100条数据存储在磁盘上如何存储
- 100条数据对应100个磁盘地址,需要通过IO读取100次才能遍历所有数据
- 如何快速查找
- MySQL索引结构
- MyISAM索引结构
- 主键索引为非聚簇索引::叶子节点存储索引和数据地址
- 辅助索引为非聚簇索引:叶子节点存储索引和数据地址
InnoDB索引结构
- 主键索引为聚簇索引:叶子节点存储索引和数据
- 一个表只会再主键索引树存储数据
- 辅助索引为非聚簇索引:叶子节点存储索引和数据主键Id
- 一个表中会存在多个索引,非主键索引是不存储数据的(因为数据只存一份,再主键索引树),只存主键值
- 通过次要索引查找数据,需要先通过次要索引树查找主键,再通过主键所索引树查找数据。
- MySQL在磁盘中读取数据
索引的磁层原理与使用原则
- 索引常用的数据结构
- 索引的使用
- 索引分类
- 主键索引
- 唯一索引
- 普通索引
- 全文索引
- 只能用在char、varchar、text文本类型的字段上。字段长度较大时,如果用普通索引效率低,可以用全文索引
- 前缀索引
- 在char、varchar、text文本类型的字段,可以指定索引列长度(前十个字符组成索引),但是数据类型不指定
- 组合索引
- 最左前缀原则
- 建议使用组合索引代替单列索引,主键索引除外
- MySQL索引实现
- MyISAM索引(B+树)
- InnoDB索引(一个索引一个索引树)
- 主键索引(聚簇索引)
辅助索引(非聚簇索引)
- 叶子节点存储的时主键索引的key值
- 回表,回到主键索引树查询数据
- 组合索引(辅助索引)
- 针对多列创建索引,索引中的列是有顺序的
- 组合索引存储
-
组合索引查找
- 最左匹配原则
- 组合索引(a,b,c)相当于创建三个索引(a;a,b;a,b,c),在where条件里,必须有a,才能使用索引,a可以在条件最右侧,优化器会自动优化到最左侧
- 书写SQL的顺序不一定是执行顺序,优化器会优化
- 使用组合索引时,MySQL会一直向右查询,直到遇到(<,>,between,like)范围查询
- 何时创建组合索引
- 频繁出现在where条件中的的非主键列,建议创建组合索引
- 频繁出现在order by和group by中的列,建议按照顺序去创建索引(顺序不一致用不到索引,优化器不能优化,因为order by是明确需求)
- 常出现在select语句中列也建议创建组合索引(所以不建议使用*)
- 覆盖索引
- 防止了回表(一次回标,多次IO),辅助索引中查找到的一条记录,会进行一次回表
- 主要是针对select中的列进行优化,如果查找的select列,就不想去主键索引中查找对应的列,查询数据了
-
- 索引下推ICP
- 索引下推是为了减少回表次数
- 索引下推设置参数:Index_Condition_PushDown
- 展示MySQL优化器中优化参数开关 show variables like ‘optimizer_switch’
- 关闭索引下推(ICP)set optimizer_switch='index_Condition_PushDown=off';
-
-
ICP未开启
- 存储引擎根据索引a=13,b>=15会匹配三条索引记录 三条索引记录会涉及三次回标
- 存储引擎将三条记录返回给Server层
- Server层进行c=5过滤 两条记录
- Server层根据d=‘pdf’过滤 一条记录
-
ICP开启
- 存储引擎根据索引a=13,b>=15会匹配三条记录
- 存储引擎根据索引下推的条件c=5在辅助索引树中进行过滤 两条记录涉及两次回表
- 存储引擎层会将两条记录返回Server层
- Server层进行d='pdf'过滤 一条记录
- explain工具查看执行计划
- 执行explain查询计划:explain select * from table_name;
- id:标识符(多张表时标识哪个先执行)
- select_type:查询类型(子查询,关联查询)
- 展示单位查询的查询类型
- simple 简单查询不需要unton和子查询(列为查询语句)
- primary 主查询(区分主表、关联表或者子表)
- subquery 子查询
- derived from字句中出现子查询,就是派生表
- table:查询表名称
- partitions:匹配的分区,表被分区时才会用到
- type:join类型
- 单位查询的连接类型或访问类型
- System 效率最高 说明表要么只有一条记录,要么是空表
- const :使用到唯一或者主键索引,单表,where条件是一个常量值
- eq_ref:等值连接(join...on中的on)的列用到唯一或者主键列,多表
- ref:等值连接(join...on中的on)的列用到非唯一索引列,多表;或者使用最左前缀
- fulltext
- req_or_null
- unique_subquery
- index_subquery
- rang:组合索引,第二个索引列用的范围(> < ...)
- index_merge
- index :全部索引扫描(select a from table;a是索引)一般是用到覆盖索引
- ALL 效率最低(全表扫描,没用索引)select a,d from table;a是索引,d不是索引
-
除all之外都用到索引;除index_merge,其他只可以用一个索引;优化时最少要用到rang级别
- possible_keys:此次查询中可能选用的索引
- key:此次查询中确切使用到的索引
- ref:哪个字段或常数与key一起使用
- rows:显示查询一共扫描了多少行
- filtered:表示此次查询条件所过滤的数据的百分比
- extra:额外的信息
- using idex 使用到覆盖索引
- 没有using where 索引用来查找数据;
- 如果同时出现using where表明索引用来查找键值
- using where 表示对存储引擎层返回结果在Server层过滤,过滤字段无索引
- using index condition 用到索引下推
- using filesort 说名排序没有用到index 在Server层排序
- 索引失效
- 全值匹配
- 最佳左前缀原则(多列索引遵循此原则)
- 带头索引死(不使用索引,全表扫)
- 中间索引断(带头索引生效,其他索引失效)
- 不要在索引上计算会失效
- 索引条件右边的列失效 (不要在索引中间使用 范围比骄)
- 范围条件右边的列索引失效
- 尽量使用覆盖索引 selelct 索引1,索引2 from
- 索引字段上不要使用 != 会索引失效
- 索引字段使用like不以通配符开头
- 索引字段字符串要加单引号
- 索引字段不要使用or
-
总结:全值匹配我最爱,最左前缀要遵守。带头大哥不能死,中间兄弟不能断;索引列上少计算,范围之后全失效;Like百分写最右,覆盖索引不写星,不等空值还有or,索引失效要少用。
- 索引创建原则
- 索引需要创建的情况
- 频繁出现在where、order排序、group by
- select频繁出现的列,创建组合索引,覆盖索引
- 多表join关联,on字段两边的字段要创建索引
- 索引创建的建议
- 表记录少的不需要
- 一个表索引记录不要过多
- 频繁更新字段不建议创建索引
- 区分度低字段(男、女区分数据度不大)
- InnoDB主键索引建议自增,避免使用长字段
- 不建议使用无序值作为索引
- 尽量创建组合索引
- 字符太长,使用前缀索引
- 索引优缺点
- 优点
- 提高检索效率,降低磁盘IO
- 通过索引进行数据排序,降低排序 成本
- 缺点
MySQL锁和事物
- 锁划分
- 按照锁粒度:、
- 全局锁
- 表级锁:开销小,加锁快;不会出现死锁;锁粒度大,出现冲突的概率高,并发度最小。
- 行级锁:开销大,加锁慢;会出现死锁;锁粒度小,出现冲突概率低,并发度也高
- 按照锁的功能:排他写锁、共享读锁
- 按照锁的实现方式:悲观锁(物理锁,真正锁)、乐观锁(版本等逻辑控制)
这篇关于MySQL的文章就介绍到这儿,希望我们推荐的文章对大家有所帮助,也希望大家多多支持为之网!