MySQL学习

• MySQL高级

  • 索引：高效查询

    • 设计原则

      • 索引设计原则 1. 针对于数据量较大，且查询比较频繁的表建立索引。 2. 针对于常作为查询条件(where)、排序(orderby)、分组(groupby)操作的字段建立索引。 3. 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。 4. 如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。 5. 尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。 6. 要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。 7. 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个 索引最有效地用于查询。

    • 索引结构

      • B+Tree索引

      • Hash索引

      • R-Tree索引

      • Full-text索引

    • 索引分类

      • 主键索引

        • 默认自动创建，只能有一个 PRIMARY

      • 唯一索引

        • 避免同一个表中某数据列中值重复 UNIQUE

      • 常规索引

        • 快速定位特定数据

      • 全文索引

        • 查找文本中关键词 FULLTEXT

    • 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执行频率： MVSQL客户端连接成功后，通过show [sessionlglobal status命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库的INSERT、UPDATE、 DELETE、SELECT的访问频次: show global status like 'Com_______';

        • 慢查询日志： 慢查询日志记录了所有执行时间超过指定参数(long_query_time，单位:秒，默认10秒)的所有SQL语句的日志。 MySQL的慢查询日志默认没有开启，需要在MySQL的配置文件(/etc/my.cnf)中配置如下信息: show variables like 'slow_query_log';

        • profile详情： show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling参数，能够看到当前MySQL是否支持profile操作: select @@have_profiling;

        • explain执行计划

          • EXPLAIN 或者 DESC命令获取 MySQL如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序。 语法:

    • 索引使用

      • 最左前缀法则（针对联合索引）

        • 如果索引了多列(联合索引)，要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。

      • 索引失效

        • 索引列运算

          • 索引列上进行运算，索引会失效

        • 字符串不加引号

        • 模糊查询

          • 尾部模糊√ 首部模糊×

            • 避免出现like '%...' ，索引会失效

        • or连接条件

          • 用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

          • 需要or前后条件对应列都要有索引

        • 数据分布影响

      • SQL提示

        • use index

        • ignore index

        • force index

      • 覆盖索引--回表查询

        • 尽量使用覆盖索引(查询使用了索引，并且需要返回的列，在该索引中已经全部能够找到)，减少select *

      • 前缀索引

        • 当字段类型为字符串(varchar，text等),时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘10，影响查 询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率

        • create index idx_xxxx on table_name(column(n)）；

        • 计算n值

          • select count(distinct email) /count(*) from tb_user； select count(distinct substring(email,1,5))/ count(*) from tb_user ;

      • 单列&联合索引

  • 锁

    • 全局锁

      • 上锁：flush tables with read lock; 备份(windows命令)：mysqldump [-h主机ip] -uroot -p1234 database1 > database2.sql-- .sql文件存放路径 解锁：unlock tables；

    • 表级锁

      • 表锁

        • 表共享读锁（read lock）

        • 表独占写锁（write lock）

          • 语法：加锁 lock tables 表名... read/write 释放锁 unlock tables /客户端断开连接

      • 元数据锁（meta data lock， MDL）

        • 查看元数据锁： select object_type, object_schema, object_name, lock_type,lock_duration from performance_schema.metadata_locks

      • 意向锁

        • 意向共享锁（IS）

          • select ... lock in share mode

            • 与读锁兼容

        • 意向排他锁（IX）

          • insert/update/delete/select ... for update

            • 可以通过以下SOL，查看意向锁及行锁的加锁情况: select object_schema,object_name,index_name,lock_type,lock_mode,lock_data from performance_schema.data_locks;

    • 行级锁

      • 行锁(Record Lock):锁定单个行记录的锁，防止 其他事务对此行进行update 和delete。在RC、RR隔离级 别下都支持。

        • 共享锁（S）-- 共享锁互相兼容

          • 允许一个事务去读一行，阻止其他事务获得相同数据集的排它锁

        • 排他锁（X）

          • 允许获取排他锁的事务更新数据，阻止其他事务获得相同数据集的共享锁和排他锁

      • 间隙锁(GapLock):锁定索引记录间隙(不含该记 录)，确保索引记录间隙不变， 防止其他事务在这个间隙进行 insert，产生幻读。在RR隔离 级别下都支持

      • 临键锁(Next-Key Lock):行锁和间隙锁组合，同 时锁住数据，并锁住数 据前面的间隙Gap。在 RR隔离级别下支持

  • InnoDB引擎

    • 逻辑存储结构

      • 表空间（ibd文件）

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

      • 段

        • 段，分为数据段(Leafnode seament)、索引段(Non-leafnode segment)、回滚段(Rolbacksegment)，InnoDB是索引组织表，数据段就是B+树的叶子节点，索引段即为B+树的非叶子节点。段用来管理多个Extent(区)。

      • 区

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

      • 页

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

      • 行

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

        • Trx_id:每次对某条记录进行改动时，都会把对应的事务id赋值给trx_id隐藏列。 Rol poinier:每次对茶条引记录进行改动时，都会把旧的版本写入到und0日志中，然后这个隐藏列就相当干一个指针，可以通过它来找到该记是修改前的信息

    • 架构

      • 架构图

      • 内存架构

        • Buffer Pool 缓冲池

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

          • 缓冲池以Page页为单位，底层采用链表数据结构管理Page。根据状态，将Page分为三种类型: free page:空闲page，未被使用。 clean page:被使用page，数据没有被修改过: dirty page:脏页，被使用page，数据被修改过，也中数据与磁盘的数据产生了不一致。

        • Change Buffer 更改缓冲区 （针对于非唯一耳机索引页）

          • 在执行DML语句时，如果这些数据Page没有在Buffer Pool中，不会直接操作磁盘，而会将数据变更存在更改缓冲区Change Buffer 中，在未来数据被读取时，再将数据合并恢复到BufferPool中，再将合并后的数据刷新到磁盘中。

        • Adaptive Hash Index:自适应hash索引

          • 用于优化对Buffer Pool数据的查询。InnoDB存储引擎会监控对表上各索引页的查询，如果观察到hash索引可以提升速度，则建立hash索引，称之为自适应hash索引。 自适应哈希索引，无需人工干预，是系统根据情况自动完成。 参数:adaptive_hash_index

        • Log Buffer:日志缓冲区

          • 用来保存要写入到磁盘中的log日志数据(redolog、undolog)，默认大小为 16MB，日志缓冲区的日志会定期刷新到磁盘中。如果需要更新、插入或删除许多行的事务，增加日志缓冲区的大小可以节省磁盘 1/0。 参数: innodb_log_buffer_size:缓冲区大小 innodb_flush_log_at_trx_commit:日志刷新到磁盘时机

      • 磁盘结构

        • show variables like '%hash_index%';

        • System Tablespace:系统表空间

          • 更改缓冲区的存储区域。如果表是在系统表空间而不是每个表文件或通用表空间中创建的，它也可能包含表和索引数据。(在MySQL5.x版本中还包含InnoDB数 据字典、undolog等) 参数:innodb_data_file_path

        • File-Per-Table Tablespaces:表的独立表空间

          • 每个表的文件表空间包含单个InnoDB表的数据和索引，并存储在文件系统上的单个数据文件中， 参数:innodb_file_per _table

        • GeneralTablespaces:通用表空间

          • 需要通过CREATE TABLESPACE 语法创建通用表空间，在创建表时，可以指定该表空间。 CREATE TABLESPACE xxxx ADD DATAFILE 'file_name' ENGINE=engine_name； CREATE TABLE xxx ... TABLESPACE ts_name;

        • Undo Tablespaces:撤销表空间

          • MySQL实例在初始化时会自动创建两个默认的undo表空间(初始大小16M)，用于存储undolog日志。

        • TemporaryTablespaces：临时表空间

          • InnoDB 使用会话临时表空间和全局临时表空间。存储用户创建的临时表等数据

        • Doublewrite Buffer Files:双写缓冲区

          • innoDB引擎将数据页从Buffer Pool刷新到磁盘前，先将数据页写入双写缓冲区文件中，便于系统异常时恢复数据。

        • Redo Log:重做日志

          • 用来实现事务的持久性。该日志文件由两部分组成:重做日志缓冲(redo log buffer) 重做日志文件(redo log) 前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都会存到该日志中,用于在刷新脏页到磁盘时,发生错误时,进行数据恢复使用。

      • 后台线程： InnoDB存储引擎缓冲池刷新到磁盘

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

        • 2. IO Thread 在InnoDB存储引擎中大量使用了AI0来处理IO请求,这样可以极大地提高数据库的性能，而IOThread主要负责这些I0请求的回调。

        • 3. Purge Thread 主要用于回收事务已经提交了的undol0g，在事务提交之后，undolog可能不用了，就用它来回收。

        • 4. Page Cleaner Thread 协助 Master Thread 刷新脏页到磁盘的线程，它可以减轻 Master Thread 的工作压力，减少阻寨

    • 事务原理

      • 特性： 原子性(Atomicity):事务是不可分割的最小操作单元，要么全部成功，要么全部失败。 一致性(Consistency):事务完成时，必须使所有的数据都保持一致状态。 持久性(Durability):事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。--------redo log--undo log------- 隔离性(lsolation):数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。----锁--MVCC----

      • redo log：脏页刷新错误时保证持久性

        • 重做日志，记录的是事务提交时数据页的物理修改，是用来实现事务的持久性。 该日志文件由两部分组成:重做日志缓冲(redolog buffer)以及重做日志文件(redolog file),前者是在内存中，后者在磁盘中。当事务提交之后会把所有修改信息都存到该日志文件中,用于在刷新脏页到磁盘,发生错误时,进行数据恢复使用

      • undo log：提供回滚 和 MVCC(多版本并发控制)

        • 回滚日志，用于记录数据被修改前的信息。 undolog和redolog记录物理日志不一样，它是逻辑日志。可以认为当delete一条记录时，undolog中会记录一条对应的insert记录，反之亦然，当update一条记录时，它记录一条对应相反的update记录。当执行rolback时，就可以从undolog中的逻辑记录读取到相应的内容并进行回滚。 Undolog销毁:undolog在事务执行时产生，事务提交时，并不会立即删除undol0g，因为这些日志可能还用于MVCC。 Undo log存储:undolog采用段的方式进行管理和记录，存放在前面介绍的 rollback segment 回滚段中，内部包含1024个undolog segment.

      • MVCC

    • MVCC

      • 基本概念

        • MVCC

          • 全称 Multi-Version ConcurrencyControl，多版本并发控制。指维护一个数据的多个版本，使得读写操作没有冲突，快照读为MySQL实现 MVCC提供了一个非阻塞读功能。MVCC的具体实现，还需要依赖于数据库记录中的三个隐式字段、undolog日志、readView。

        • 当前读

          • 读取的是记录的最新版本，读取时还要保证其他并发事务不能修改当前记录，会对读取的记录进行加锁。对于我们日常的操作，如: select .. lock in share mode(共享锁)，select... for update、update、insert、delete(排他锁)都是一种当前读。

        • 快照读

          • 简单的select(不加锁)就是快照读，快照读，读取的是记录数据的可见版本》有可能是历史数据，不加锁，是非阻塞读。 Read Committed:每次select，都生成一个快照读。 Repeatable Read:开启事务后第一个select语句才是快照读的地方。 Serializable:快照读会退化为当前读。

      • 实现原理

        • 记录中的隐藏字段

          • 查看表空间字段结构：ibd2sdi xxx.ibd;

        • undo log

          • 在insert、update、delete的时候产生的便于数据回滚的日志。 当ipsert的时候，产生的undolog日志只在回滚时需要，在事务提交后，可被立即删除。 而update、delete的时候，产生的undolog日志不仅在回滚时需要，在快照读时也需要，不会立即被删除

          • undo log版本链

        • readview

          • ReadView(读视图)是 快照读 SQL执行时MVCC提取数据的依据，记录并维护系统当前活跃的事务(未提交的)id。 ReadView中包含了四个核心字段: m_ids min_trx_id max_trx_id creator_trx_id

          • trx_id:当前事务id 不同的隔离级别，今生成Readview的时机不同 READ COMMITTED : 在事务中每一次执行快照读时生成ReadView。 REPEATABLE READ: 仅在事务中第一次执行快照读时生成ReadView，后续复用该ReadView

      • MVCC+隐藏字段+undo log版本链+readview+锁 保证事务--隔离性-- redo log+undo log-------------------------------------一致性--