Mysql 进阶（一）

MySQL 体系结构

• 连接层

  最上层是一些客户端和链接服务，主要完成一些类似于连接处理，授权认证，及相关安全方案。服务器也会为安全接入的每个客户端验证它所具有的操作权限

• 服务层

  第二层架构主要完成大多数的核心服务功能，如 SQL 接口，并完成缓存的查询，SQL 的分析和优化，部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程，函数等

• 引擎层

  存储引擎真正地负责了 Mysql 中数据的存储和提取，服务器通过 API 和存储引擎进行通信，不同的存储引擎具有不同的功能，需要根据自己的需要选择合适的存户引擎

• 存储层

  主要是将数据存储在文件系统之上，并完成与存储引擎的交互

存储引擎

存储引擎是存储数据，建立索引，更新/查询数据等技术的实现方式。存储引擎是基于表的，所以存储引擎也被称为表类型, Mysql 默认存储引擎为 innoDB

• 查看当前数据库支持的存储引擎

  SHOW ENGINES;

• 创建表时指定存储引擎

  CREATE TABLE my_engine(
      id INT PRIMARY KEY 
  ) ENGINE = 存储引擎;

存储引擎特点

innoDB

• innoDB 时一种兼顾高可靠性和高性能的通用存储引擎，在 Mysql 5.5 之后，innoDB 是 Mysql 默认存储引擎

• 特点

  • DML 操作遵循 ACID 模型，支持事务
  • 行级锁，提高并发访问性能
  • 支持外键约束，保证数据的完整性和可靠性

• 文件

  xxx.ibd: xxx 代表表名，innoDB 引擎的每张表都会对应一个表空间文件，储存该表的结构（ frm, sdi ），数据和索引

• 逻辑结构

  表空间 段 区 页 行

MyISAM

• MyISAM 是 Mysql 早期的默认存储结构

• 特点

  • 不支持事务，不支持外键
  • 支持表锁，不支持行锁
  • 访问速度快

• 文件

  • xxx.sdi: 存储表结构信息
  • xxx.MYD: 存储数据
  • xxx.MYI: 存储索引

Memory

• Memory 引擎的表数据存储在内存中，只能作为临时表或缓存使用

• 特点

  • 内存存放
  • hash 索引（默认）

• 文件

  xxx.sdi: 储存表结构信息

索引

索引是帮助 Mysql 高效获取数据的数据结构（有序）

优缺点

优势	劣势
提高数据检索的效率，降低数据库的 IO 成本	索引列需要占据空间
通过索引对数据结构进行排序，降低数据排序的成本，降低 CPU 的消耗	在对表进行插入，更新，删除时需要同时维护索引列，降低效率

索引结构

索引结构	描述	InnoDB	MyISAM	Memory
B+ Tree 索引	最常见的索引，大部分引擎支持 B+树索引	支持	支持	支持
Hash 索引	使用哈希表实现，只对精确匹配索引列的查询有效，不支持范围查询	不支持	不支持	支持
R-Tree（空间索引）	MyISAM 引擎的一个特殊索引类型，主要用于地理空间数据类型，使用较少	不支持	支持	不支持
Full-text（全文索引）	通过建立倒排索引，快速匹配文档	5.6 之后版本支持	支持	不支持

InnoDB 为什么选择使用 B+Tree

• 相对于二叉树/红黑树，层级更少，搜索效率更高
• 对于 B-tree, 无论是叶子节点还是非叶子节点都储存数据，由于一个节点储存为一页，一页中储存的键值减少，指针减少，书的高度更高，性能下降
• 相对于 Hash 索引，支持范围查找和排序操作

索引分类

分类	含义	特点	关键字
主键索引	针对于表中的主键创建的索引	默认自动创建，只能有一个	PRIMARY
唯一索引	避免同一个表中某数据列中的值重复	可以有多个	UNIQUE
常规索引	快速定位特定顺序	可以有多个
全文索引	全文索引查找的是文本中的关键字，而不是比较索引中的值	可以有多个	FULLTEXT

根据储存形式分类

分类	含义	特点
聚集索引(Clustered Index)	将数据存储与索引放到一起，索引结构的叶子节点保存了行数据	必须有且只有一个
二级索引(Secondary Index)	将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键	可以存在多个

聚集索引选取规则

• 如果存在主键，主键索引就是聚集索引
• 如果没有主键，将使用第一个唯一（UNIQUE）索引作为聚集索引
• 如果没有主键也没有唯一索引，则 InnoDB 自动生成一个 rowid 作为隐藏的聚集索引

回表查询：先通过二级索引找到主键值，再通过聚集索引查询数据

索引语法

• 创建索引

  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；

• 查看数据库 INSERT, UPDATE, DELETE, SELECT 访问频次

  SHOW GLOBAL STATUS LIKE 'Com_______';

慢查询日志

慢查询日志记录了所有执行时间超过指定参数（long_query_time, 单位：秒，默认10秒）的所有日志

MySQL 的慢查询日志默认没有开启，需要在 MySQL 的配置文件（/etc/my.cnf）中配置

#开启 MySQL 慢查询日志
slow_query_log = 1
#设置慢查询日志时间为2秒，SQL yu

• 查看慢查询是否开启

  SHOW VARIABLES LIKE 'slow_query_log';