Mysql相关知识点以及sql优化

InnoDb和MYISAM存储引擎得区别？

MyISAM innoDB
事务 不支持 支持
外键 不支持 支持
锁 表锁 行锁
缓存 缓存索引 缓存索引和数据
索引实现 非聚簇索引 聚簇索引

锁的区别

  2.     MyISAM支持表锁，InnoDB支持表锁和行锁，默认为行锁
  4.        表级锁：开销小，加锁快，不会出现死锁。锁定粒度大，发生锁冲突的概率最高，并发量最低
  6.        行级锁：开销大，加锁慢，会出现死锁。锁力度小，发生锁冲突的概率小，并发度最高

索引

  1. # 索引
  2.     索引存储在内存中，为服务器存储引擎为了快速找到记录的一种数据结构。索引的主要作用是加快数据查找速度，提高数据库的性能
  3. # 优点
  4.     (1)    创建唯一性索引，保证数据库表中每一行数据的唯一性
  5.     (2)    大大加快数据的检索速度，这也是创建索引的最主要的原因
  6.     (3)    加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
  7.     (4)    在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时        间。
  8. # 索引分类
  9.     1.普通索引
  10.     2.唯一索引：索引列的值必须唯一，可以有空值
  11.     3.主键索引：唯一标识数据表中的某一条记录，不允许有空值
  12.     4.联合索引：多个字段上建立的索引，能够加速复合查询条件的检索。
  13.     5.全文索引

索引的底层实现原理

  1. # 数据库的底层数据结构主要是基于Hash+B+Tree
  2.     从二叉树慢慢引进B+Tree

二叉树

  1. # 为什么不是二叉树
  2.     也可以是二叉树，然后我们为了加快数据的查找，维护二叉树，每个节点分别包含索引键值和一个指向对应数据记录的物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取相应的数据，从而快速的检索出符合条件的记录
  3.     但是随着数据越来越多，这个时候树的高度就会越来越高，我们想要减少磁盘IO次数查找的快就必须要压缩树的高度，让瘦高的树变成矮胖的树这个时候就要优化
  4.     这里再引出BTree

BTree

  1. # 为什么不使用BTree
  2.     BTree又叫多路平衡搜索树，
  3.     度节点的数据存储个数
  4.     叶节点具有相同的深度
  5.     叶节点中数据key从左到右递增排序
  6.     叶节点的指针为空
  7.     在叶节点中直接存储了数据data
  8.     SpringBoot_MybatisPlus_Travel
  9.     为了提高度的长度，还需要对数据结构进行优化，这个时候引出B+Tree

B+Tree

  1. # 为什么使用B+Tree
  2.     非叶子节点不存储data,只存储key,可以增大度
  3.     叶子节点不存储指针
  4.     顺序访问指针，提高区间访问能力
  6.     B+Tree的度一般会超过100，因此h非常小 (一般为3到5之间)，性能就会非常稳定
  7.     B+Tree叶子节点有顺序指针，更容易做范围查询

两种常见的存储引擎的索引实现

MyISAM索引实现

  1. # 索引特点：非聚簇索引
  2.     采用B+Tree 作为数据结构
  3.     MyISAM 索引文件和数据文件是分离的(非聚簇)  
  4.     叶子节点存储的是数据的磁盘地址
  5.     非主键索引和主键索引类似

InnoDB索引实现

  1. # 索引特点：聚簇索引
  2.     采用B+Tree 作为数据结构
  3.     数据文件本身就是索引文件  (聚簇索引)
  4.     表数据文件本身就是按照B+Tree组织的一个索引结构文件
  5.     聚集索引-叶节点包含了完整的数据记录
  6.     非主键索引 的叶子节点指向主键

索引的优劣势

优势

  1.     1.可以通过建立唯一索引或者主键索引,保证数据库表中每一行数据的唯一性.
  2.     2.建立索引可以大大提高检索的数据,以及减少表的检索行数
  3.     3.在表连接的连接条件 可以加速表与表直接的相连
  4.     employee dep_id   deparment 
  5.     4.在分组和排序字句进行数据检索,可以减少查询时间中 分组 和 排序时所消耗的时间(数据库的记录会重新排序)
  6.     5.建立索引,在查询中使用索引 可以提高性能

劣势

  1.     1.索引文件会占用物理空间,除了数据表需要占用物理空间之外,每一个索引还会占用一定的物理空间
  2.     2.在创建索引和维护索引 会耗费时间,随着数据量的增加而增加
  3.     3.当对表的数据进行INSERT,UPDATE,DELETE 的时候,索引也要动态的维护,这样就会降低数据的维护速度

适合建立索引

  1.     1.主键自动建立唯一索引:primary 
  2.              id 
  3.     2.频繁作为查询条件的字段应该创建索引 
  4.       where name =   
  5.     3.查询中与其它表关联的字段,外键关系建立索引
  6.        employ dep_id      dep id
  7.     4.查询中排序的字段,排序的字段若通过索引去访问将大大提升排序速度     
  8.         order by 字段
  9.     5.查询中统计或分组的字段
  10.         group by 字段

SQL性能优化

慢查询日志

  1. # 慢日志查询
  2.     慢日志查询是Mysql提供的一种日志记录，它用来记录在Mysql中响应时间超过阈值的语句，Mysql的日志是跟踪Mysql性能瓶颈的最快和最直接的方式了，系统性能出现瓶颈的时候，首先要打开慢查询日志，进行跟踪，尽快的分析和排查出执行效率较慢的SQL ,及时解决避免造成不好的影响
  3. # 作用
  4.     记录具体执行效率低的SQL语句的日志信息
  5. # 注意
  6.     默认情况下慢日志查询时关闭的
  7.     同时慢查询日志默认不记录管理语句和不使用索引进行查询的语句

慢查询日志分析

  1. # 主要功能
  2.     统计不同统计不同慢sql的
  3.     出现次数(Count), 
  4.     执行最长时间(Time), 
  5.     累计总耗费时间(Time), 
  6.     等待锁的时间(Lock), 
  7.     发送给客户端的行总数(Rows), 
  8.     扫描的行总数(Rows), 
  9.     用户以及sql语句本身(抽象了一下格式, 比如 limit 1, 20 用 limit N,N 表示).
  10. # 慢查询日志分析工具
  11.     mysqlsla，myprofi，pt-query-diges

Explain (执行计划)

  1. 使用explain关键字,可以模拟优化器执行的SQL语句
  2. 从而知道MYSQL是如何处理sql语句的
  3. 通过Explain可以分析查询语句或表结构的性能瓶颈
  5. # 具体作用：
  6.     查看表的读取顺序
  7.     数据读取操作的操作类型
  8.     查看哪些索引可以使用
  9.     查看哪些索引被实际使用
  10.     查看表之间的引用
  11.     查看每张表有多少行被优化器执行
  12. # 使用方法
  13.     使用Explain关键字 放到sql语句前
  14.     explain select cus_id from testemployee where cus_id > 10

避免索引失效

  1. -- 最左匹配原则 * 
  2. -- 范围条件右边的索引失效
  3. -- 不再索引列上做任何操作 * 
  4. -- 使用不等于(!=或者<>)索引失效
  5. -- is not null无法使用索引
  6. -- like以通配符开头(%qw)索引失效 * 
  7. -- 字符串不加引号索引失效
  8. -- 使用or连接索引失效
  9. -- 尽量使用覆盖索引