mysql 初探

最新推荐文章于 2022-09-28 10:34:25 发布

/^jamesChiang$/

最新推荐文章于 2022-09-28 10:34:25 发布

阅读量160

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/jameschiang1995/article/details/88691047

版权

mysql 专栏收录该内容

3 篇文章 0 订阅

订阅专栏

mysql 初探

mysql的逻辑架构大概分为三层：
在这里插入图片描述
第一层：服务层(为客户端服务)
为请求做连接处理，授权认证，安全等。

第二层：核心服务
比如查询解析，优化，缓存，内置函数。存储过程，触发器，视图等。对于第二层来说，所以跨存储引擎的功能都在这一层实现。

第三层：存储引擎
负责mysql中数据的存储和提取。服务器通过api与存储引擎通信，这些接口屏蔽了存储引擎之间的差异。也就是说，接口的存在，导致不同存储引擎的差异不会影响到上层查询过程。
架构分层之后，可以更好的理解一些问题，比如我们很关心的并发问题：mysql层面的并发控制，实际上是分为两种：
服务层的并发、存储引擎层的并发。

锁

锁的机制应用在存储引擎层，锁具有粒度性
根据粒度大小分为表级索、行级锁
表级索：并发能力差，系统开销小
行级锁：并发能力强，系统开销大
根据功能分为共享锁（读锁），排他锁（写锁）
读锁：比如（当前客户端）在user这张表中加了读锁，意味着(所有客户端)不能进行其他写的操作
写锁：只有当前客户端操作这个表（读写），，其他的客户端不能操作当前表

事务

原子性：一个事务要么都成功，要么都失败。
隔离性：一个进行的事务在提交的之前所做的修改对其他事务是不可见的。
持久性：成功持久写入数据库
一致性：从某种一致的状态切换到某种一致的状态，遵循客观事实

索引

索引：
A、
定义：是存储引擎快速找到记录的一种数据结构。
B、
可以使用show index from table_name 查看索引详情。
类型：
主键索引： Primary key 一个表只能有一个主键
唯一索引： UNIQUE 唯一索引列的值必须唯一，但允许有空值。如果是组合索引，则列值必须唯一
通过 ALTER TABLE table_name ADD UNIQUE index_name (index) 创建唯一索引
通过 ALTER TABLE table_name ADD UNIQUE index_name (column1,column2) 创建组合唯一索引
普通索引： INDEX 最基本的索引，他没有任何限制
通过ALTER TABLE table_name ADD index index_name (column) 创建普通索引
组合索引： INDEX 即一个索引包含多个列，多用于避免回表查询
通过 ALTER TABLE table_name ADD INDEX index_name (column1,column2) 创建组合索引
全文索引： FULLTEXT 也称全文索引，是目前搜索引擎使用的一种关键技术
通过 ALTER TABLE table_naem ADD FULLTEXT index_name(column) 创建全文索引
删除索引： DROP INDEX index_name ON table_name;
C、
索引优化实践：
a、默认返回表中30%内的数据会走索引，返回超过30%的数据就使用全表扫描
b、回表：当对一个列创建索引之后，索引会包含该列的键值及键值相对应的Rowid。通过索引中记录的rowid访问表中的数据就叫回表。回表次数太多会严重影响SQL性能
有些时候虽然数据库有索引，但是并不被优化器选择使用：
前导模糊查询不能命中索引，非前导模糊查询则可以使用索引，可优化为使用非前导模糊查询。
数据类型出现隐式转换的时候不会命中索引，特别是当列类型是字符串，一定要将字符常量值用引号引起来
复合索引的情况下，查询条件不包含索引列最左边部分（不满足最左原则），不会命中索引。
union\in\or 都能够命中索引建议使用in
查询的CPU 消耗 or>in>union
用or分割开的条件，如果or前的条件中列有索引，而后面的列中没有索引，那么涉及到的索引都不会被用到【后面的条件会全表扫描】
负向条件【!=、<>、not in、not exist、not like】查询不能使用索引，可以优化为in查询
范围条件可以命中索引【<、<=、>、>=、between】
注意：范围列可以用到索引(联合索引必须是最左前缀)，但是范围类后面的列无法用到索引，索引最多用于一个范围列，如果查询条件中有两个范围列则无法全用到索引。
如果是范围查询和等值查询同时存在，优先匹配等值查询列的索引。
数据库执行计算不会命中索引
计算逻辑应该放到业务层处理，节省数据库的Cpu的同时最大限度的命中索引
利用覆盖索引进行查询，避免回表
建立索引的列，不允许为NULL

myisam存储引擎和innodb存储引擎区别

myisam引擎和innodb引擎最大的区别主要有以下4点
缓存机制：
事务支持：
锁定实现：
数据存储方式差异：

mysql的隔离级别

分析mysql的隔离级别（默认是可重复读）：
1、未提交读
【另一个事务修改了数据，但尚未提交，而本事务中的SELECT会读到这些未被提交的数据（脏读）。】
2、提交读【本事务读取到的是最新的数据（其他事务提交后的）。问题是，在同一个事务里，前后两次相同的SELECT会读到不同的结果（不重复读）。】
3、可重复读（INNODB 默认该级别）
【在同一个事务里，SELECT的结果是事务开始时时间点的状态，因此，同样的SELECT操作读到的结果会是一致的。但是，会有幻读现象】
4、串行化读
【读操作会隐式获取共享锁，可以保证不同事务间的互斥。】

字段类型优化

数据类型的优化：
1、更小的通常更好
2、简单更好
3、尽量避免NULL
4、整型（tinyint smallint mediumint int bigint）分别使用（8，16,24,32,64）位存储空间 -2^(N-1) - 2^(N-1)-1 （N表示存储空间的位数）
5、浮点型（float 使用四个字节存储 double 使用八个字节存储近似计算）
（decimal 每四个四节存9个数字例如decimal(18,9) 小数点两边将各存储9个数字一共使用9个字节精确计算）。