[数据结构与算法]Mysql高级进阶篇（持续更新）

1. 存储引擎

1. 在创建表时，指定存储引擎

   create table 表名(
   	......
   )engine=innodb;
   

2. 查看当前数据库支持的存储引擎：

   show engines;

3. 存储引擎的特点

   • InnoDB
     • DML操作遵循ACID模型，支持事务
     • 行级锁，提高并发访问性能
     • 支持外键FOREIGN KEY约束，保证数据的完整性和正确性
   • MyISAM
     • 不支持事务，不支持外键
     • 支持表锁，不支持行锁
     • 访问速度快
   • Memory
     • 内存存放
     • hash索引
   • 区别
     

2. 索引

索引：一种高效查询的数据结构

• 优缺点：

2.1 索引的结构

• 二叉树、红黑树：

• B-Tree：

  数据结构动画演示网站

• B+Tree：所有的数据都会出现在叶子节点，并且叶子节点形成一个单向链表
  MySQL索引数据结构对B+Tree进行了优化，在其基础上，增加了一个指向相邻叶子节点的链表指针，形成了带有顺序指针的B+Tree，提高区间访问的性能
  

• Hash索引
  特点：

  1. Hash索引只能用于对等比较，不支持范围查询
  2. 无法利用索引完成排序操作
  3. 查询效率高，通常只需要一次检索就可以了，效率通常要高于B+Tree索引

• 为什么InnoDB存储引擎选择使用B+Tree索引结构？

  1. 相对于二叉树，层级更少，搜索效率高
  2. 相对于B-Tree，无论叶子节点还是非叶子节点，都会保存数据，导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低
  3. 相对于Hash索引，B+tree支持范围匹配和排序操作

2.2 索引的分类

• 索引分类：
  
  • 聚集索引选取规则：
    1. 主键索引为聚集索引
    2. 不存在主键，则将使用第一个唯一索引作为聚集索引
    3. 如果都没有，则会自动生成一个rowid作为隐藏的聚集索引
  • 聚集索引和二级索引
    
    如：select * from user where name = 'Arm';因为name字段的索引属于二级索引，则先查找二级索引构成的b+树，找到对应的叶子节点，因为该节点下挂载的是id，而查询的是全部数据，所以根据挂载的id进行回表查询聚集索引构建的B+树
• InnoDB 主键索引的B+Tree能存放多少行数据？

2.3 索引语法

• 创建索引

  CREATE [UNIQUE | FULLTEXT] INDEX 索引名 ON 表名(字段1,字段2…)

• 查看所有

  SHOW INDEX FROM 表名

• 删除索引

  DROP INDEX 索引名 ON 表名

2.4 SQL 性能分析

2.4.1 SQL 执行频率

MySQL客户端连接成功后，通过 show [session|global] status命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库的 增删改查的访问频率
> SHOW GLOBAL STATUS LIKE ‘Com_______’;

2.4.2 慢日志查询

慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志。
MySQL的慢查询日志默认没有开启，需要在配置文件 my.cnf中配置如下信息

查看慢查询日志的开启状态：show variables like ‘slow_query_log’;

2.4.3 profile 详情

show profiles 能够在做SQL优化时间帮助我们了解消费的时间，通过 have_profiling参数，能够查看当前MySQL是否支持profile操作

select @@have_profiling

默认profiling是关闭的，可以通过set语句在 session/global级别开启profiling

开启：set profiling = 1;
查看开启状态：select @@profiling;

2.4.4 执行计划

直接在select 语句前加上关键字 explain / desc

explain select 字段列表 from 表名 where 条件;

explain执行计划各字段含义：

2.5 索引使用

2.5.1 最左前缀法则

如果使用的是联合索引，要遵循最左前缀法则。查询从索引的最左列开始，并且不跳过索引中的列。如果跳跃某一列，索引将部分失效（后面的字段索引失效）

2.6 索引设计原则

3. 视图/存储过程/触发器

4. 锁

5. InnoDB引擎

6. MySQL 管理