目次
多表查询
变乱
存储引擎
索引结构
索引分类
SQL性能优化
索引失效
视图
存储过程
触发器
MySQL锁
全局锁
表锁
行锁
多表查询
分类
- 内连接
只返回两个表符合条件的数据，干系联的数据（两个表的交集）
- 外连接
- 左外连接以左表为基础，返回左表的数据以及右表中匹配的数据，如果查询不到右边数据，将列数据填为NULL
左外连接，查询左表的数据以及交集数据
右外连接，查询右表的数据以及交集数据
- 自连接
将一个表看作两个表举行连接操纵，可以是内连接也可以是外连接
团结查询
将查询效果合并成起来，查询的语句必须表字段一致
子查询
将查询的效果作为条件再次查询，嵌套查询
变乱
- 原子性，变乱中的指令必须全部成功或者全部失败
- 隔离性，变乱具有隔离性，实行变乱不会被其他指令干扰
- 一致性，变乱完成后，数据必须包管一致
- 长期性，变乱一旦提交或者回滚，数据就会永久性发生变化
- 脏读，一个变乱读取到了另一个变乱还未提交更新的数据
- 不可重复读，一个变乱先后读取同一个数据，数据不一致（另一个变乱对数据举行了更新）
- 幻读，一个变乱先查询数据发现不存在，但是在插入时数据已经存在（另一个变乱先举行了插入）
- 读未提交（Read uncommitted），变乱会读取到其他变乱还未提交的数据，可能会造成
脏读（变乱A读取到变乱B还未提交更新的数据，或变乱B回滚数据，变乱A读取到是错误数据）
不可重复读
幻读
- 读提交（Read committed），变乱先后读取同一数据，数据可能不一致，可能造成
不可重复读（变乱A读取数据，一段时间后再次读取，但是变乱B已经对数据举行了更新，此时变乱A两次读取的数据不一样
幻读（变乱A想要实行插入数据，先去查询这个数据是否存在，查询到不存在，实行插入操纵。但是变乱B此时插入了这个数据，变乱A无法在插入）
- 可重复读（Repeatable Read）（默认）
MySQL默认隔离级别
变乱在先后读取数据，得到的数据是一致的，但是存在幻读题目
- 串行化（Serializable）
最高隔离机制，变乱不会受其他变乱影响，按次序实行变乱。变乱必须期待前一个变乱实行完毕
存储引擎
- MySQL体系结构
连接层，当客户端发送连接请求时，举行连接处理（建立连接）
服务层，存储最近查询的语句和效果，存入到数据缓存中。对SQL语句举行分析
引擎层，负责数据的存储和处理
存储层，磁盘IO操纵，将数据存储到磁盘中
- MySQL中的存储引擎
- InnoDB（Mysql默认引擎）
- 支持变乱，支持行锁，支持外键
- 利用B+树索引结构
- 存储逻辑分为：表空间>段>区（64个页）>页（索引中存放数据和指针 16K）>行（行数据）
- MyISAM
- 不支持变乱，不支持行锁，不支持外键，访问速度快，主要以读取数据和写入数据为主，很少对数据举行删除和修改
MyISAM的索引和数据分开存储在不同的文件中，数据按插入次序分列，次序读取时访问速度快
底层是B+树的索引结构，通过索引查找数据时更高效
不支持变乱，不消处理复杂的变乱管理
只支持表级锁，写入操纵时会锁定整个表
- Memory
- 内存存放是Hash索引，数据生存在内存中，访问速度快，常用于临时表和缓存
索引结构
索引结构主要是B+树索引和Hash索引
- 当树为二叉树时，次序插入会形成一个链表，查询服从低
- 红黑树时，层级较深，检索速度慢
- B树的插入、检索服从都高
以5阶为例，当一个页中存满4个元素时，插入元素会将这5个元素中的中间元素向上分裂。直到上面的页也存满，继承分裂
- B+树，基于B树，但叶子节点存放数据（单向链表），而非叶子节点只是起到一个索引数据的作用
B树的页中存放的是数据和指针，而B+树只在叶子节点存放数据，在相同数据量下，层级比B+树高，检索的服从不如B+树
- 而InnoDB采用的就是B+树，同时对B+树的叶子节点举行优化，由单向链表优化为双向链表
- Hash索引
将键值对通过哈希函数盘算出哈希值，映射到Hash表的槽位上
如果出现哈希冲突，利用链地址法，插入链表末了
- 当根据一个字段查找时，会先盘算这个字段的哈希值，根据哈希值找到哈希表中对应数据的位置
- 查找服从比B+树高，但是只能用于对等比较（=，in），不能范围查询（between，<，>）
索引分类
- 主键索引，针对字段中的主键
- 唯一索引，避免字段中的数据重复
- 常规索引，快速定位特点数据
- 全文索引，查找文本中的关键字
- 聚集索引，主要是对主键，叶子节点存放的是行数据
- 二级索引，将数据和索引分开存储，叶子节点关联的是行的主键
- 再查找时，利用回表查询，如果根据二级索引区查找，会先查找叶子节点，对应的主键值，在举行对聚集索引根据主键值举行查找行数据
SQL性能优化
- SQL实行频率
show global status like 'Com_______;'
可以查看数据库的增编削察访问频次
- 慢查询日志
在mysql的配置文件中开启慢查询日志，设置慢日志时间
当sql语句实行时间超过慢日志时间就会被记录
- profile详情
show profile可以查看每条SQL的耗时基本环境
- explain实行筹划（常用）
在sql语句前加explain关键字，可以查看sql语句可能用到的索引、实际用的索引等信息
索引失效
- 查询不依照最左前缀法则
创建的团结索引，在查询时必须包管最左边的字段被查询
在团结索引中，从哪个字段断开，索引就从哪开始失效
- 范围查询
在团结索引中，出现了范围查询，则范围右侧的列索引失效
- 索引列运行
在索引列上举行运算操纵，索引会失效
- 含糊查询
如果是尾部含糊查询，则不会失效（软件%）
如果是头部含糊查询，则索引失效（%工程）
- or连接的条件
or连接的两个条件，如果是索引列和非索引列，则索引全部都失效
- 数据分布影响
如果全表扫描的服从更好，则索引也会失效
视图
- 视图是一个虚拟存在的表，基于一个表或多个表的查询效果创建，可以将复杂的查询语句封装到一个视图中（将查询多张表的复杂语句），用户只需对视图简单查询就可以得到效果（无需每次编写复杂语句）
- 可以限制用户对敏感的数据举行访问，限制用户查看的权限
- 如果底层的表的结构发送变化（字段、数据范例），只要视图可以正确的获取数据，就可以不对视图修改
存储过程
- 存储过程是一组实行特点功能的SQL语句集，经过编译后存储在数据库中
- 存储过程在数据库服务器上实行，客户端只需发送实行存储过程的请求就行，不需要发送大量SQL语句
- 将复杂的业务逻辑写入存储过程中，业务逻辑发生变化，只需对存储过程修改，不需要修改多个地方的SQL语句
- 存储过程相称于一个方法
触发器
- 触发器是针对特点的数据库表
- 变乱触发时对表的增编削操纵
- 可以在触发器触发之前或者之后，实行一些操纵（一组SQL语句）
MySQL锁
全局锁
- 对整个数据库加锁，锁定数据库中所有的表，加锁后整个数据库都是只读状态
表锁
- 表共享锁（read lock）
加锁后，其他变乱不可以对表实行写入操纵，只能读取
- 表独占锁（write lock）
加锁后，其他变乱不可以多表实行写入和读取操纵，相称于阻塞其他变乱
行锁
- 对表中的一行数据加锁
- 当一个变乱对一行数据加共享锁时，其他变乱可以对这行数据加共享锁，不可以加排他锁。表示多个变乱可以对一行数据举行读取，不能修改
- 当一个变乱对一行数据加排他锁时，其他变乱可以对这行数据不可以加共享锁，也不可以加排他锁。表示一行数据只能由一个变乱修改，其他变乱只能等锁开释才能举行操纵
免责声明：如果侵犯了您的权益，请联系站长，我们会及时删除侵权内容，谢谢合作！更多信息从访问主页：qidao123.com:ToB企服之家，中国第一个企服评测及商务社交产业平台。 |
