mysql高级（尚硅谷周阳）

mysql的架构分析

• mysql 高级（包括） （很多公司的mysql的优化有专门的DBA）
  • Mysql高级
  • sql优化攻城狮
  • mysql服务器的优化
  • 各种参数常量的设定
  • 查询语句优化
  • 主从复制
  • 软硬件升级
  • 容灾备份
  • sql编程
• 这里注意视频那个启动mysql老了应该用 service mysqld start
• mysql逻辑架构
  
• mysql支持多种存储引擎
• 与其他数据库相比， mysql有点与众不同， 他的架构可以在多种不同场景中应用并发挥良好的作用， 主要体现在存储引擎的架构下， 插件式的存储引擎架构将查询处理和其他的系统任务以及数据的存储提取和分离， 这种架构可以根据业务的需求和实际需要选择合适的存储引擎。
• 连接层
  

sql优化分析

---

性能下降sql慢

• 出现的两种可以划分的情况
  1、执行时间长
  2、 等待时间长
• 查询语句写的烂
• 索引失效
  • 索引分为单值索引以及复合索引
  • 单值索引
    create index idx_user_name on user(name)
• 关联查询太多join（设计缺陷或不得已的需求）
• 服务器调优及各个参数的设置（缓冲、线程数等）

join分析

• sql执行顺序
  • 手写的
• 机器
  
  • 机器sql执行总结
    
• join
  

索引

---

索引：

• 是帮助Mysql高效获取数据的数据结构。 可以得到索引的本质： 索引是数据结构。
• 提高查找效率， 可以类比字典。
• 排好序的快速查找数据结构， 会影响条件和orderby
  在数据之后， 数据库系统还维护着满特定查找算法的数据结构， 这些数据结构以某种方式引用数据， 这样就可以在这些数据结构上实现高级查找算法， 这种数据结构， 就是索引。
  
• 索引本身也很大， 不可能全部存储到内存中， 因此索引往往以索引文件的形式存储在磁盘上。
• 我们平常所说的索引， 如果没有特别指明， 都是指B树（多路搜索树）组织结构的索引， 其中聚集索引， 次要索引， 复合索引， 前缀索引， 唯一索引默认都是使用B+树索引， 统称索引， 当然， 除了B+树这种类型的索引之外， 还有hash索引。
• 对于MyISAM来说索引跟数据是分开放的， 有三个文件 结构数据和索引， 索引的数据部分为表数据的物理地址， 先查到索引如果要查数据， 还要再一次访存。
• 对于INNOdB引擎， 有且必须有一个聚集索引， 一般是主键， 数据和聚集索引放到一起， 如果没有主键， 找第个不为NULL的唯一列， 如果还没有Innodb会给加一个隐藏列作为聚集索引。而其他索引的数据部分都是聚集索引， 通过聚集索引找到目标的位置。
• 对于Innodb索引建议用自增的int数据作为主键， 原因：首先int比varchar()较为好比较， 其次int比varchar()省空间。 为什么要自增的呢： 原因： 对于B+树来说数据全在叶子节点上， 其次在叶子节点上从左到右是有序的双链表，而自增会顺序的添加到一页中， 这样会使b+树的分裂次数减少， 而如果使随机的， 由于B+必须保持叶子节点的有序所以， 就要查到B+树叶子节点的中间故就会导致B+树不必要的分裂， 则会导致性能较差， 当进行随机插入它的分裂是取页的中间的， 这样就会导致数据内部碎片， 而且分裂需要三个页的移动和加载。
  • 自增的主键的值是顺序的，所以Innodb把每一条记录都存储在一条记录的后面。当达到页面的最大填充因子时候(innodb默认的最大填充因子是页大小的15/16，会留出1/16的空间留作以后的修改)：
  • ①下一条记录就会写入新的页中，一旦数据按照这种顺序的方式加载，主键页就会近乎于顺序的记录填满，提升了页面的最大填充率，会有页的浪费
  • ②新插入的行一定会在原有的最大数据行下一行，mysql定位和寻址很快，不会为计算新行的位置而做出额外的消耗
  • ③减少了页分裂和碎片的产生 参考博客
• 优势： 提高数据索引的效率， 降低数据库IO成本，降低数据排序的成本， 降低了cpu的消耗。
• 缺点： 实际上索引也是一张表， 该表保存了主键与索引字段， 并指向实体表的记录， 所以索引列也是要占用空间的。虽然索引大大提高了查询速度， 同时却会降低更新表的速度， 如对表进行INSERT、update、delete。因为更新表时， 不仅要保存数据， 还要保存以下索引文件每次更新添加了索引列的字段， 都会调整因为更新带来的键值变化的索引信息。索引只是提高效率的一个因素， 如果mysql有大量的数据， 就要花时间建立最优秀的索引。
• 索引分类
  • 单值索引
    即一个索引只包含单个列， 一个表可以有多个单列索引。一张表的索引一般不要超过5个
  • 唯一索引
    索引列的值必须唯一， 但允许有空值
  • 复合索引
    一个索引包含多个列
    对于复合（联合）索引来说， 排序是先按第个排， 如果第一个相同的时候才会按第二个排， 接着， 这也是为什么索引要遵循最左原则。
  • 基本语法
    create 【UNIQUE 加唯一索引】Index indexName On mytable(名字)
    表级建立
    Alter table add [unique] index indexname on table(名字)
    查看 show index from table
    

索引数据结构及原理

• 原理
  
  

那些情况需要建立索引

1、主键自动建立聚簇索引
2、频繁作为查新条件的字段应该创建索引
3、查询中与其他表关联的字段， 外键关系建立索引。
4、频繁更新的字段不适合创