7.存储引擎
MySQL体系结构
存储引擎简介
存储引擎是存储数据、建立索引、更新、查询数据等技术的实现方式。存储引擎是基于表的,而不是基于库的,所以也可被称为表类型。
默认存储引擎:InnoDB
1.建表时指定存储引擎
CREATE TABLE 表名{
字段1 字段1类型[COMMENT 字段1注释],
字段2 字段2类型[COMMENT 字段2注释],
字段3 字段3类型[COMMENT 字段3注释],
......
字段n 字段n类型[COMMENT 字段n注释],
}ENGINE=INNODB [COMMENT 表注释];
2.查看当前数据库支持的引擎
SHOW ENGINE;
存储引擎特点
InnoDB介绍
特点
1.DML操作遵循ACID模型,支持事务。
2.行级锁,提高并行访问性能
3.支持外键约束,保证数据完整性
文件
xxx.idb:xxx代表表名,innoDB的每张表都会对应一个表空间文件,存储该表的表结构(frm,sdi)、数据和索引。
MyISAM
特点
1.不支持事务
2.支持表锁,不支持行锁
3.访问速度快
文件
xxx.sdi 存储表结构信息
xxx.MYD 存储数据
xxx.MYI 存储索引
Memory
特点
1.内存存放
2.hash索引
文件
xxx.sdi
存储引擎选择
8.索引
索引概述
帮助MySQL高效获取数据的数据结构。这些数据结构以某种方式引用(指向)数据,可以在这些数据结构上实现高级查找算法,这种数据结构就是索引。
索引结构
数据库更倾向于使用B+树而不是其他数据结构
-
磁盘读写效率:数据库通常需要处理大量数据,这些数据无法全部加载到内存中,而需要存储在磁盘上。B+树相较于其他树形结构,具有更低的树高度,从而减少了磁盘I/O操作的次数。因为B+树的非叶子节点不存储数据,只存储索引,这使得每个磁盘块能够容纳更多的索引,进一步减少了磁盘读写的次数。
-
查询效率稳定:B+树的所有关键字查询路径长度相同,这意味着每个数据的查询效率相当。相较于其他树形结构,B+树提供了更稳定的查询性能。
-
范围查询效率高:B+树的数据都存储在叶子节点中,且叶子节点之间通过指针相连,形成了一个有序的链表结构。这使得范围查询变得非常高效,只需要找到范围的最小元素,然后沿着叶子节点链表遍历即可。这在数据库中是非常常见的操作。
-
适合数据插入和删除:B+树的平衡性较好,当插入或删除数据时,只需对局部节点进行调整即可,无需对整个树进行重构。这保证了数据库在频繁进行数据更新时仍能保持较高的性能。
索引分类
按索引的存储形式
聚集索引选取规则
1.如存在主键,主键索引就是聚集索引。
2.如果不存在主键,将使用第一个唯一索引作为聚集索引。
3.如果表没有主键,或没有合适的唯一索引,则InnoDB会自动生成一个rowid作为隐藏的聚集索引。
回表查询:先在二级索引中找到对应主键值,再到聚集索引中拿到对应的行数据,因为数据存在聚集索引中
第一句效率更高,因为第二句需要回表查询
select * from user where id=10;
select * from user where name= 'arm';
索引语法
创建索引
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 GLOBAL STATUS LIKE 'Com______';
慢查询日志
慢查询日志记录了索引执行时间超过指定参数的所有SQL语句的日志。
# 开启MySQL慢日志查询开关
slow_query_log=1
#设置慢日志的时间为两秒
long_query_time=2
profile详情
show profiles能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling参数,能够看到当前MySQL是否支持profile操作。
SELECT @@hava_profiling
#打开开关
SET profiling=1;
profile详情
#查看数据
show profiles;
#查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;
#查看指定语句的CPU使用情况
show profile cpu for query query_id;
explain
explain或desc命令获取MySQL如何执行SELECT语句的信息,包括SELECT语句执行过程中表如何连接和链接的顺序。
#直接在语句前加上desc或explain
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件;
重点:type、possible_key、key、key_len
索引使用
验证索引效率
未建立索引时查看效率
最左前缀法则
范围查询
范围查询用>=右侧索引不会失效
索引列运算
不要再索引上进行运算操作,索引将失效
explain select * from tb_user where substring(phone,10,2)= '15';
字符串不加引号
字符串类型不加引号时,索引失效
select *from tb_user where phone = 17778392917;
模糊查询
尾部模糊查询,索引不会失效,前部模糊查询,索引失效
select * from tb_user where profession like '软件%'; #尾部模糊
or连接的条件
用or分开,如果or前面列有索引,后面列没有索引,那么涉及的索引都不会被用到。
explain select * from tb_user where id=10 or age=23; #age没有索引,两边都不会用索引
数据分布影响
如果MySQL评估用索引比全表查询漫,就不用索引
SQL提示
在SQL语句中加入一些人为的提示来达到优化的目的。
覆盖索引
尽量使用覆盖索引(查询使用了索引,需要返回的列,在该索引中已经全部能够找到),减少select *
超出覆盖查询要回表查询
前缀索引
当字段类型为字符串时,有时需要索引很长的字符串,这会让索引变得很大,浪费磁盘IO,影响查询效率。此时可以只将字符串的一部分前缀,建立索引,大大节约索引空间,提高索引效率。
create index idx_xxx on table_name(column(n))
前缀长度
可以根据索引的选择性来决定,而选择性是指不重复的索引值(基数)和数据表的记录总数的比值,索引选择性越高则查询效率越高,唯一索引的选择性是1,这是最好的索引选择性,性能也是最好的。
select count(distinct substring(email,1,10))/count(*) from tb_user;
单列索引和联合索引
索引设计原则
9.SQL优化
插入数据
主键优化
order by优化
extra中的Using filesort和Using index
创建索引时默认是升序排序
全升或全降都会用索引 (Using index)
一升一降,如果和索引顺序不一样,会使用filesort,一样不用
group by 优化
limit优化
MySQL中不支持在in中使用limit
select * from tb_sku where id in(select id from tb_sku order by id limit 900000,10);
可以将limit的结果看成一张表,通过多表联查实现
select * from tb_sku s,(select * from tb_sku where id in(select id from tb_sku order by id limit 900000,10)) a where s.id=a.id;
count优化
update优化
10.视图/存储过程/触发器
视图
检查选项
CASCADED
如果为视图指定了CASCADED
选项,那么在操作该视图时会检查该视图的条件,并递归检查该视图依赖的所有视图的条件。
如果依赖的视图没有添加CASCADED
选项,则会自动给它们添加,以确保所有视图的检查条件都生效。
LOCAL
如果为视图指定了LOCAL
选项,那么会检查该视图的条件,并尝试递归检查该视图依赖的所有视图的条件。
如果依赖的视图没有添加LOCAL
选项,则不会检查它们的视图条件,即不会给它们强制添加视图检查选项。
更新及作用
11.存储过程
基本语法
在命令行,识别到分号会认为结束。可以使用delimiter指定结束符号
系统变量
用户定义变量
局部变量
if判断
参数
case
while循环
repeat
loop
cursor
handler
存储函数
能够使用存储函数的地方也能使用存储过程
12.触发器
insert型
update型
delete型