MySQL

innodb存储结构

执行流程：

              先连接mysql服务器，进行缓存的查询（SQL和对应的结果（result））,避免重复查询。但是因为数据特性，会影响性能，所以8.0就取消了缓存。后进行SQL的解析生成解析树，再进行SQL优化接着生成执行计划，最后把计划丢给存储引擎。

并发事务问题： 

• 脏读： 表示一个事务读取到了，另外一个事务进行数据操作但是还没有提交的数据=====数据已经发生了改变
• 不可重复读：一个事务的前后两次进行相同事务查询操作但是结果不一致，意思是一个事务的相同查询操作前后结果应该是一致的才行；
• 幻读：  一个事务a先进行查询事务发现没有该条数据，此时另外一个事务b进行了该条数据的插入操作然后提交了，事务a进行插入操作时就发生了冲突（插入失败），当事务a再次进行查询操作时还是依旧没有查询出来结果======》因为已经解决了不可重复读的问题的基础上

事务的隔离级别

越往下隔离级别越高，出现的问题越少；；Oracle数据库的默认级别是第二个读已提交 

MySql5.5后默认使用innoDB存储引擎 

索引

        * 概述：是帮助mysql高效获取数据的数据结构（有序）。这些数据结构以某种方式引用（指向）数据，可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。  

        * 常见的索引结构：

                        * B+Tree .

                        Hsah索引  ： 底层数据结构是用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询。

R-Tree(空间索引) ：空间索引是mYISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少

 Full-text(全文索引)： 是一种通过建立倒排索引，快速匹配文档的方式，

 二叉树缺点：顺序插入时，会形成一个链表，查询性能大大降低。大数据量情况下，层级较深，检索速度慢

红黑树：大数据量情况下，层级较深，检索速度慢

 B-Tree: 一个结点下面可以有多个子节点

B+Tree: 所有的数据都会出现在叶子结点，叶子结点形成一个单向链表

mysql索引数据库结构对经典的B+Tree进行了优化，在原B+Tree的基础上，增加了一个指向相邻叶子结点的链表指针，就形成了带有顺序指针的B+Tree.提高区间访问的性能。

         * 索引的分类

1.                 聚集索引：B+Tree的叶子结点存放的是一整行的数据：它必须有而且只能有一个    选取规则：主键索引》第一个唯一索引》InnoDB生成的rowid
2.                  二级索引：叶子结点关联的是对应的主键。可以有多个   
3.         从二级索引查到主键然后到聚集索引查到所有数据  回表查询

 

 应该是已知数据量求取innodb的高度。

 索引操作

        * 创建索引： create [unique   |    fulltext] index  index_name   on  table_name(index_col_name)

        * 查看索引： show index from table_name

        * 删除索引： drop index index_name  on table_name; 

 sql性能分析

       * 查看SQL执行频率：  show session/global  status like  'Com_____' ；一个_代表一个字符

       *  慢查询日志记录了所有执行时间超过指定参数（long_query_time，默认为10s）的所有SQL语句的日志，mysql的慢查询日志默认没有开启，需要在mysql的配置文件（/etc/my.cnf）中配置。

         * profile详情：那些虽然没有记录到慢查询日志中，但是业务简单，却接近long_query_time的操作；  select @@have_profiling    set  profiling = 1

        * explain执行计划

 

索引的使用

• 最左前缀法则：如果索引了多列（联合索引），要遵守最左前缀法则，最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。如果没有最左的列将不会使用索引，如果中间某一个列缺失，那么索引将部分失效（缺失列之后的索引都会失效）；；；列存在即可，不要求位置
• 范围查询右边的索引会失效，所以尽量使用<=或者>=;;;;;;
• 对索引关联的列进行运算操作、不加引号、和前置模糊查询“%xxx”都会使索引失效。
• 如果是or条件的话只要有一个列没有索引整个操作都不会使用索引。
• 根据数据分布情况，如果mysql评估使用全表比使用索引更快，则不使用索引。
• 
• 覆盖索引： 就是要查询的字段刚好是联合索引关联的字段，比回表查询的效率高
• 前缀索引： 当字段类型为字符串（varchar,text），有时索引会很大，查询时浪费大量的磁盘IO，此时可以只将字符串的一部分前缀建立索引，大大节约索引空间，从而提高索引效率。  这个前缀长度的选择就是用前缀检索的不重复的数据和全部数据的百分比表高比较合适的长度

sql优化

插入优化：

        1.insert   批量插入（500-1000条）

        2.多条插入语句时手动提交事务    如果是自动提交事务，没执行完一条语句都会自动发生开启事务和提交事务的动作，。

        3.主键顺序插入高于乱序插入

        4.达到百万级别的数据就不能使用insert插入了：使用load指令。

主键优化

 1.如果主键乱序插入可能会发生分裂，就是一个页没有被占满，因为索引也是要顺序的不能随便往空白页添加

2.页合并：就是对索引删除后，会对该数据做上标记，如果被标记数超过了一个页的50%（默认），就会查看该页的前后页，是否能进行合并

主键设计时，应该尽量不要太长（不仅影响检索，还要浪费空间）；插入数据时要选择主键顺序插入；尽量不要选择自然主键（UUID或者身份证等）；在进行业务操作时避免对主键的修改。

order by优化

执行计划分为 using filesort 和 using index  显然后者效率更高；

using index 表示直接通过有序索引顺序扫描直接返回有序数据；

想要出现using index 就必须要用覆盖索引否则都是using filesort；

当要使用using filesort ，对于大数据量的排序可以适当的增加sort_buffer_zise的大小，提高效率

group by 优化

 还是对对应字段创建索引还是要覆盖索引，还是满足最左前缀法则===即需要存在创建索引时最左边的列，但是无关位置

limit 优化

limit对大数据量进行分表查询时，要对所需数据之前的所有数据进行排序，但是实际需要却是只有一点点，其他记录丢弃，查询排序的代价非常大。

一般分页查询时，通过创建覆盖索引加子查询的形式进行优化。 如下：

select s.* from table1 s,（select id from table order by id limit 20000000,10) b where s.id = a.id

update优化

只有根据加有索引的字段进行更新的操作，事务加的是行锁此时操作其他行没有问题；但如果操作的字段没有索引，事务加的就是表锁，此时操作其他行会失败。=======》就是where条件后的字段。

 InnoDB的行锁是针对索引加的锁，不是针对记录加的锁，并且该索引不能失效，否则会从行锁升级为表锁。

 视图/存储过程/触发器

视图

        视图（View）是一种虚拟存在的表。视图中的数据并不在数据库中实际存在，行和列数据来自定义视图的查询中使用的表，并且是在使用视图时动态生成的。

        视图的检查选项；当使用with check option子句创建视图时，mysql会通过视图检查正在更改的每个行，例如插入，更新，删除，以使其符合视图的定义。

cascaded：不仅会查看当前视图的条件还会查看当前视图依赖的所有视图的条件。

local:            不仅会查看当前视图的条件还会查看当前视图依赖的所有视图的条件,前提是依赖的视图也有视图检查选项。

视图的作用

 * 简单：视图不仅可以简化用户对数据的理解，也可以简化他们的操作。那些被经常使用的查询可以被定义为视图，从而使得用户不比以后的操作每次指定全部条件。

* 安全： 数据库可以授权，但不能授权到数据库特定行和特定的列上。通过视图用户只能查询和修改他们所能见到的数据。

* 数据独立： 视图可以帮助用户屏蔽真实表结构变化带来的影响。

存储过程

: 存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合，调用存储过程可以简化应用开发人员的很多工作，减少数据在数据库和应用服务器之间的传输，对于提高数据库处理的效率是有好处的。

存储过程思想上很简单，就是数据库SQL语言层面的代码封装与重用。

创建：create procedure 存储过程名称（参数）

               begin 。。。。。。SQL语句； end；

调用： call  存储过程名称（）

查看：show create procedure 存储过程名称；  删除：drop

触发器

create trigger trigger_name

before/after  insert/update/delete

on table_name for each row ----- 行级触发  ？语句触发  一条语句值触发一次 mysql不支持

begin 

end;

锁

全局锁

典型运用于数据备份；但是也可以不用全局锁，而是加上参数 --single-transaction ，在底层

mysql的innoDB引擎是使用的是快照读来实现的。

加锁语句：flush tables with read lock ;

释放锁语句： unlock tables;

表级锁

表锁

lock tables 表名  read/write;;;;;;; unlock tables

读锁不会阻塞其他客户端的读，但是会阻塞写。

写锁其他客户端的写和读都会阻塞

元数据锁 MDL

加锁过程是系统自动控制的；

在表上有活动事务的时候，不可以对元数据进行写入操作。为了避免DML与DDL冲突，保证写的正确性。

意向锁

如果对一个数据表进行操作，会对该行数据加上一个行锁，此时就给这个数据库表加上一个意向锁；便于其他客户端进行加表锁时的判断。 

意向共享锁：与表锁共享锁（read）兼容，与表锁排它锁（write）互斥

意向排它锁：都互斥。意向锁之间不会互斥。

行锁

 

 InnoDB逻辑存储结构

表空间：（ibd文件），一个mysql实例可以对应多个表空间，用于存储记录、索引等数据。

段：分为数据段、索引段、回滚段，InnoDB是索引组织表，数据段就是B+Tree的叶子结点，索引段就是非叶子结点。段用来管理多个区（Extent）

区：表空间的单元结构，每个区的大小为1M。默认情况下，InnoDB存储引擎页大小为16k，即一个区中一共有64个连续的页

页：是InnoDB存储引擎磁盘管理的最小单位，每个页的大小默认为16KB，为了保证页的连续性，InnoDB存储引擎每次从磁盘申请4-5个区；

行，InnoDB存储引擎数据是按行进行存放的。

InnoDB内存架构

Buffer Pool ：缓冲池是主内存中的一个区域，里面可以缓存磁盘上经常操作的真实数据，在执行增删改查操作时，先操作缓冲池中的数据（若缓冲池没有数据，则从磁盘加载并缓存），然后再以一定频率刷新到磁盘，从而减少磁盘IO，加快处理速度；

缓冲池以page页为单位，底层采用链表数据结构管理page；page分为了三种

free page：空闲page，未被使用。clean page：被使用page，数据没有被修改过。 dirty page ：脏页，被使用page，数据被修改过，页中数据与磁盘的数据不一致。

磁盘架构

后台线程

Master Thread  : 核心后台线程，负责调度其他线程，还负责将缓冲池中的数据异步刷新到磁盘中，保持数据的一致性，还包括脏页的刷新、合并插入缓存、undo页的回收

事务的原理

redo log 保证事务的持久性：

重做日志，记录的是事务提交时数据页的物理修改。该日志分为重做日志缓存（redo log buffer）

以及重做日志文件（redo log file）。事务提交之后会把所有修改信息都存到该日志文件中，用于在刷新脏页到磁盘，发生错误时，进行数据恢复。  

之所以选择从日志缓存中刷新到磁盘而不是从缓冲池，是因为日志文件是追加的，是顺序磁盘IO。缓存池中的操作很多而且都是随机操作的页数据，是随机磁盘IO，所以性能不高。

 undo log 解决 事务的原子性

它是逻辑日志，就是与现操作的语句相反delete ===insert..。 提供回滚和MVCC(多版本并发控制)

 insert操作产生的undo log 日志只在回滚时被需要，事务提交后可以被立即删除。

update 和delete 操作产生的undo log 日志不仅在回滚的时候需要，在快照读的时候也需要，提交事务后不会立即删除。

         相同或不同的事务对同一条记录进行修改会导致该记录的undo log 形成一条记录版本链表，链表的头部是最新的记录，尾部是最早的记录。

 MVCC 多版本并发控制

概念：

        当前读：读取的是记录的最新版本

        快照读：简单的select（不加锁）就是快照读

读提交：每次select都生成一个快照读；

可重复读： 开启事务的第一个select语句才是快照读的地方。

串行化： 快照读会退化为当前读。

MVCC:  维护一个数据的多个版本，使得读写操作没有冲突，快照读为mysql实现MVCC提供了一个非阻塞读功能。MVCC的具体实现，还需要依赖于数据库记录中的三个隐式字段、undolog日志、readview。 

事务的隔离性由MVCC+锁保证；

事务的一致性由 原子性（undo log）和持久性（redo log）保证。

mysql管理工具

 日志

        错误日志：mysqld.log  该日志默认开启，默认存放目录 /var/log/; 可以通过 show variables lke '%log_error%' 查看日志的位置。

        二进制日志：记录了所有的DDL(数据定义语言) 和DML(数据操纵语言)，但不包括数据查询（select、show）；灾难时的数据恢复、、、mysql的主从复制

    删除二进制日志：      

           查询日志：记录所有语句，记录对象太过庞大所以默认关闭

           慢查询日志：慢查询日志记录了所有执行时间超过参数long_query_time 设置 并且扫描记录数不小于min_examined_row_limit的所有的SQL语句，默认关闭。long_query_time默认为10秒。

             

 主从复制

 主库配置：

server-id、、、、read-only=0、、、还可以配置同步忽略的数据库和要同步的数据库

从库配置：

server-id（和主库不冲突的值）、、、、、read-only=1、、、、

还要在从库配置要连接的主库，包括ip、用户名、密码、二进制文件名、开始同步的位置等信息。

 分库分表

        热点数据太多，数据库缓存不足，产生大量IO,请求数据太多，带宽不够，网络IO瓶颈

        CPU瓶颈，请求数太多，SQL太多会耗费大量的CPU资源。

拆分策略

垂直拆分

垂直分库：以表为依据，根据业务将不同表拆分到不同库中；每个库的结构都不一样、所有库的并集是全量数据

垂直分表：以字段为依据，根据字段属性将不同字段拆分到不同表中；每个表的结构都不一样、所有表的并集是全量数据

水平拆分

水平分库：将一个数据库拆分到多个库中；每个库的结构都一样，只是数据不一样

水平分表：将一个表的数据拆分到多个表中。每个表结构都一样，只是数据不一样。