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 使用了索引 不需要回表查询