mysql进阶学习 | DAY 14

存储引擎

体系结构

• 连接层

• 服务层

• 引擎层

• 存储层

存储引擎 表类型

查看引擎 查看建表语句

指定存储引擎 ENGINE

SHOW engins

InnoDB

默认存储引擎

• 遵循ACID模型 支持事务

• 行级锁 提高并发访问性能

• 支持外键 FOREIGN KEY约束 保证数据完整性和正确性

对应文件 xxx.ibd 表空间文件{结构 数据 索引}

应用：对事务完整性比较高 在并发条件下要求数据的一致性

MyISAM

早期默认存储引擎

• 不支持事务 不支持外键

• 支持表锁 不支持行锁

• 访问速度快

xxx.MYD（数据） xxx.MYI （索引） xxx.sdi(表结构)

应用：数据操作以读取和插入为主 很少更新或删除 对事务完整性并发性瑶琴斌不是很高

Memory

存储在内存 只能作为临时表或缓存使用

• 内存存放

• hash索引（默认）

应用：临时表或缓存 对表的大小有限制

索引

帮助mysql高效获取数据的数据结构

无索引 全表扫描

索引

• 优点 调高检索效率

• 缺点 降低增删改的效率

索引结构

在存储引擎层实现 不同引擎存储不同结构

• B+Tree索引 默认

• Hash索引

• R-Tree 空间索引

• FUll-text 全文索引

B树 多路平衡查找树

度数 一个节点子节点的个数 每个节点最多存储n个指针 n-1个值

B+树

• 所有元素都会出现在叶子节点

• 叶子节点形成一个单向链表

在mysql中 优化了B+树 增加一个指向相邻叶子节点的链表指针 形成了带有顺序指针的b+树 提高区间访问性能

Hash索引

• 不支持范围查找

• 无法利用索引

索引分类

• 主键索引 primary

• 唯一索引 uniquee

• 操常索引 快速定位数据

• 全文索引 fulltext

innodb存储引擎 根据索引存储形式分为

聚焦索引： 将数据存储与索引放到了一起 索引结构的叶子节点保存了行数据 必须有而且只有一个

二级索引 ：数据与索引分开存储 索引结构的叶子节点关联的是对应的主键

回表查询：先走二级索引找到主键值 然后走聚焦索引找到row

创建索引：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 执行频率 增删改查所占的频率

  show [session|globale] sattus like 'com_______';服务器状态信息

• 慢查询日志

  • 记录所有执行时间超过指定参数的sql语句的日志

  • 慢查询日志默认没有开启 需要在配置文件配置 /etc/my.cnf 添加 slow_query_log=1 long_query_time=2

  • 查询是否开启 show variables like 'slow_query_log';

  • 只会记录超过预定时间的操作才回记录

• profile 详情

  • show profiles 查看耗时都去了哪里

  • SELECT @@have_profiling参数查看是否支持profile操作

  • set profiling=1开启

  • 查看指定query_id 的sql语句各个阶段的耗时 show profile for query_id;

  • 查看cpu使用情况 show profile cpu for query_id

• explain执行计划

  • 在任意select语句之前添加关键字 explain

  • id 表示查询语句中执行select自居或者是操作表的顺序 id相同 按照从上往下执行 如果不同 数值越大先执行

  • select_type 表示select的类型 simple（简单查询）primary（主查询 外层查询） union（union中的第二个或者后面的查询语句）subquery（select/where 之后包含了子查询）

  • type 连接类型

  • pssible_key 显示可能应用在这张表上的索引

  • key 实际使用的索引

  • key_len 索引中使用的字节数

  • rows 预估值 执行查询的行数

  • filtered 行数占需要读取的行数的百分比

索引使用

• 最左前缀法则

出现了联合索引 从索引的最左列开始查询 并且不跳过索引中发的列

• 索引列运算

不要在索引列上进行运算 索引将失效

• 字符串索引添加引号

否则索引将失效

• 模糊查询

尾部模糊 不会失效 头部模糊 索引失效

• or连接条件

or前条件中有索引 后面没有索引 那么涉及的索引不会用到

• 覆盖索引

查询使用了索引 并且需要返回的列 在该索引中已经全都被找到

出现 using index condition 使用了索引 但是需要回表查询

出现 using where using index 使用了索引 不需要回表查询