MySQL存储引擎
MySQL体系结构
- 连接层
- 服务层
- 引擎层
- 存储层
存储引擎简介
存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的,而不是基于库的,所以存储引擎也可被称为表类型。
- 创建表时,指定存储引擎
CREATE TABLE 表名( 字段1 字段1类型 [COMMENT 字段1注解], ... 字段n 字段n类型 [COMMENT 字段n注解] )ENGINE = INNODB [COMMENT 表注释];
- 查看当前数据库支持的存储引擎
SHOW ENGINES;
存储引擎特点
- InnoDB
- 介绍:InnoDB是一种兼顾高可靠性和高性能的通用存储引擎,在 MySQL 5.5. 之后,InnoDB是默认的MySQL存储引擎。
- 特点:
- DML操作遵循ACID模型,支持事务
- 行级锁。提高并发访问性能
- 支持外键FOREIGN KEY 约束,保证数据的完整性和正确性;
- 文件:
- xxx.ibd : xxx代表的是表名,innoDB引擎的每张表都会对应这样一个表空间文件,存储该表的表结构(frm、sdi)、数据和索引。
- 参数:innodb_file_per_table
- 逻辑结构
- TableSpace:表空间
- Segment:段
- Extent:区(1M)
- Page:页(16K)
- Row;行
- MyISAM
- 介绍
- MyISAM是MySQL早期的默认存储引擎
- 特点
- 不支持事务,不支持外键
- 支持表锁,不支持行锁
- 访问速度快
- 文件:
- xxx.sdi : 存储表结构信息
- xxx.MYD : 存储数据
- xxx.MYI : 存储索引
- 介绍
- Memory
- 介绍
- Memory引擎的表数据是存储在内存中的,由于受到硬件问题、或断电问题的影响,只能将这些表作为临时表或缓存使用
- 特点
- 内存存放
- hash索引(默认)
- 文件
- xxx.sdi : 存储表结构信息
特点 InnoDB MyISAM Memory 存储限制 64TB 有 有 事务安全 支持 - - 锁机制 行锁 表锁 表锁 B+tree索引 支持 支持 支持 Hash索引 - - 支持 全文索引 支持(5.6版本之后) 支持 - 空间使用 高 低 N/A 内存使用 高 低 中等 批量插入速度 低 高 高 支持外键 支持 - -
- xxx.sdi : 存储表结构信息
- 介绍
存储引擎选择
- InnoDB:是Mysql的默认存储引擎,支持事务,外键。如果应用对事物的完整性有比较高的要求,在并发条件下要求数据的一致性,数据存储操作除了插入和查询之外,还包含很多的更新、删除操作,那么InnoDB存储引擎是比较合适的选择
- MyISAM:如果应用是以读操作和插入操作为主,只有很少的更新和删除操作,并且对事物的完整性、并发性要求不是很高,那么选择这个存储引擎是非常合适的
- MEMORY:将所有数据保存在内存中,访问速度快,通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制,太大的表无法缓存在内存中,而且无法保障数据的安全性。
MySQL索引
索引概述
索引是帮助MySQL高效获取数据的数据结构(有序)。在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法,这种数据结构就是索引。
- 优缺点
优势 劣势 提高数据检索的效率,降低数据库的IO成本 索引列也是要占用空间的 通过索引列对数据进行排序,降低数据排序的成本,降低CPU的消耗 索引大大提高查询效率,同时却也降低更新表的速度看,如对表进行INSERT、UPDATE、DELETE时,效率降低。
索引结构
MySQL的索引是在存储引擎层实现的,不同的存储引擎有不同的结构,主要包含一下几种:
索引结构 | 描述 |
B+Tree索引 | 最常见的索引类型,大部分引擎都支持B+树索引 |
Hash索引 | 底层数据结构是用哈希表实现的,只有精确匹配索引列的查询才有效,不支持范围查询 |
R-tree(空间索引) | 空间索引是MyISAM引擎的一个特殊索引类型,主要用于地理空间数据类,通常使用较少 |
Full-text(全文索引) | 是一种通过建立倒排索引,快速匹配文档的方式。类似于Lucene,Solr,ES |
索引 | innoDB | MyISAM | Memory |
B+tree索引 | 支持 | 支持 | 支持 |
Hash索引 | 不支持 | 不支持 | 支持 |
R-tree索引 | 不支持 | 支持 | 不支持 |
Full-text | 5.6版本之后支持 | 支持 | 不支持 |
我们平常所说的索引,如果没有特别指明,都是B+树结构组织的索引。
- 二叉树缺点:顺序插入时,会形成一个链表,查询性能大大降低。大数据量情况下,层级较深,检索速度慢。
- 红黑树:大数据量情况下,层级较深,检索速度慢。
- B+Tree(MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上,增加一个指向相邻叶子节点的链表指针,就形成了带有顺序指针的B+Tree,提高区间访问的性能。
- Hash(哈希索引就是采用一定的hash算法,将键值换算成新的hash值,映射到对应的槽位上,然后存储在hash表中。如果两个(或多个)键值,映射到一个相同的槽位上,他们就长生了hash冲突(也称为hash碰撞),可以通过链表来解决。
- Hash索引特点
- Hash索引只能用于等比较(=,in),不支持范围查询(between,>,<,...)
- 无法利用索引完成排序操作
- 查询效率高,通常只需要一次检索就可以了,效率通常要高于B+tree索引
- 存储引擎支持
- 在MySQL中,支持hash索引的是Memory引擎,而InnoDB中具有自适应hash功能,hash索引是存储引擎根据B+Tree索引在指定条件下自动构建的
- Hash索引特点
为什么InnoDB存储引擎选择使用B+Tree索引结构?
- 相对于二叉树,层级更少,搜索效率高;
- 对应B+tree,无论是叶子节点还是非叶子节点,都会保存数据,这样导致一页中存储的键值减少,指针跟着减少,要同样保存大量数据,只能增加树的高度,导致性能降低;
- 相对于Hash索引,B+Tree支持范围匹配及排序操作;
索引分类
分类 | 含义 | 特点 | 关键字 |
主键索引 | 针对于表中主键创建的索引 | 默认自动创建,只能有一个 | PRIMARY |
唯一索引 | 避免同一个表中某数据列中的值重复 | 可以有多个 | UNIQUE |
常规索引 | 快速定位特定数据 | 可以有多个 | |
全文索引 | 全文索引查找的是文本中的关键字,而不是比较索引中的值 | 可以有多个 | FULLTEXT |
在InnoDB存储引擎中,根据索引的存储形式,又可以分为以下两种:
分类 | 含义 | 特点 |
聚集索引 | 将数据存储与索引放到一块,索引结构的叶子节点保存行数据 | 必须有,而且只有一个 |
二级索引 | 将数据与索引分开存储,索引结构的叶子节点关联的是对应的主键 | 可以存在多个 |
聚集索引选取规则:
- 如果存在主键,主键索引就是聚集索引。
- 如果不存在主键,将使用第一个唯一(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执行频率(MySQL客户端连接成功后,通过 show [seesion|global] status 命令可以提供服务器状态信息。通过如下指令,可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次:
SHOW GLOBAL STATUS LIKE 'COM_______'; --七个下划线
-
慢查询日志
-
慢查询日志记录了所有执行事件超过指定参数(long_query_time,单位:秒,默认10秒)的所有SQL语句日志。MySQL的慢查询日志默认没有开启。需要在MySQL的配置文件(/etc/my.cnf)中配置如下信息:
--开启MySQL慢日志查询开关 slow_query_log=1 --设置慢日志的时间为2秒,SQL语句执行时间超过2秒,SQL语句执行时间超过2秒,就会视为慢查询,记录慢查询日志 long_query_time=2
-
配置完毕之后,通过以下指令重新启动MySQL服务器进行测试,查看慢日志文件中记录的信息 /var/lib/mysql/localhost-slow.log。
-
-
profile详情
-
show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling参数,能够看到当前MySQL是否支持profile操作:
SELECT @@have_profiling;
默认profiling是关闭的,可以通过set语句在session/global级别开启profiling:
SET profilinhg=1;
执行一系列的业务SQL的操作,然后通过如下指令查看指令的执行耗时:
#查看每一条SQL的耗时基本情况 show profiles; #查看指定query_id的SQL语句各个阶段的耗时情况 show profile for query query_id; #查看指定query_id的SQL语句CPU的使用情况 show profile cpu for query query_id;
-
-
explain执行计划
-
EXPLAIN或者DESC 命令获取MySQL 如何执行 SELECT 语句的信息,包括在SELECT 语句执行过程中表如何连接和连接的顺序。
-
语法
#直接在select语句之前加上关键字 explain / desc EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件;
-
EXPLAIN执行计划各字段含义:
-
id
-
select查询的序列号,表示查询中执行select子句或者是操作表的顺序(id相同,值越大,越先执行)。
-
-
select_type
-
表示SELECT 的类型,常见的取值有SIMPLE (简单表,即不使用表连接或者子查询)、PRIMARY(主查询,即外层的查询)、UNION(UNION 中的第二个或者后面的查询语句)、SUBQUERY(SELECT / WHERE 之后包含了子查询)等
-
-
type
-
表示连接类型,性能由好到差的连接类型为NULL、system、const、eq_ref、range、index、all。
-
-
possible_key
-
显示可能应用在这张表上的索引,一个或多个。
-
-
Key
-
实际使用的索引,如果为NULL则没有使用索引。
-
-
Key_len
-
表示索引中使用的字节数,该值为索引字段最大可能长度,并非实际使用长度,在不损失精确性的前提下,长度越短越好。
-
-
rows
-
MySQL认为必须要执行查询的行数,在innoDB引擎的表中,是一个估计值,可能并不总是准确
-
-
filtered
-
表示返回结果的行数占需读取行数的百分比,filtered 的值越大越好。
-
-
-
索引使用
- 验证索引效率
- 在未建立索引之前,执行如下SQL语句,查看SQL的耗时
SELECT * FROM tb_sku WHERE sn='109388383838';
- 针对字段创建索引
create index idx_sku_sn on tb_sku(sn);
- 然后再次执行相同的SQL语句,再次查看SQL的耗时
SELECT * FROM tb_sku WHERE sn='19299339939';
- 在未建立索引之前,执行如下SQL语句,查看SQL的耗时
- 最左前缀法则
- 如果索引了多列(联合索引),要遵守最左前缀法则指的是查询从索引的最左列开始,并且不跳过索引中的列。如果跳跃某一列,索引将部分失效(后面的字段索引失效)。
- 范围查询
- 联合索引中,出现范围(>,<),范围查询右侧的列索引失效。
- 索引列运算
- 不要再索引列上进行运算操作,索引将失效。
- 字符串不加引号
- 字符串类型字段使用时,不加引号,索引将失效。
- 模糊匹配
- 如果仅仅是尾部模糊匹配,索引不会失效。如果是头部模糊匹配,索引失效。
explain select * from tb_user where profession like '软件%'; explain select * from tb_user where profession like '%工程'; explain select * from tb_user where profession like '%工%';
- 如果仅仅是尾部模糊匹配,索引不会失效。如果是头部模糊匹配,索引失效。
-
or 连接的条件
-
用or分割开的条件,如果or前的条件中的列由索引,而后面的列没有索引,那么涉及的索引都不会被用到。
explain select * from tb_user where id=10 or age=23; explain select * from tb_user where ph='1234412124566' or age=23;
-
-
数据分布影响
-
如果MySQL评估使用索引比全表更慢,则不使用索引。
select * from tb_user where phone>='12233556'; select * from tb_user where phone>='12993933';
-
-
SQL提示
-
SQL提示,是优化数据库的一个重要手段,简单来说,就是在SQL语句中加入一些人为的提示来达到优化的目的。
-
use index:(建议MySQL使用这个索引)
explain select * from tb_user use index(idx_user_pro) where profession='软件工程';
-
ignore index:(忽略这个索引)
explain select * from tb_user ignore index(idx_user_pro) where profession='软件工程';
-
forcr index:(强制使用这个索引)
explain select * from tb_user force index(idx_user_pro) where profession='软件工程';
-
-
覆盖索引
-
尽量使用覆盖索引(查询使用了索引,并且需要返回的列,在该索引中已经全部能够找到),减少 select *。
-
-
单列索引与联合索引
-
单列索引:即一个索引只包含单个列。
-
联合索引:即一个索引包含了多个列。
-
在业务场景中,如果存在多个查询条件,考虑针对于查询字段建立索引时,建议建立联合索引,而非单列索引。
-
多条件联合查询时,MySQL优化器会评估哪个字段的索引效率更高,会选择该索引完成本次查询。
-
索引设计原则
-
针对于数据量较大,且查询比较频繁的表建立索引。
-
针对于常作为查询条件(where)、排序(order by)、分组(group by)操作的字段建立索引
-
尽量选择区分度高的列作为索引,精良建立唯一索引,区分度越高,使用索引的效率越高
-
如果是字符串类型的字段,字段的长度较长,可以针对于字段的特点,建立前缀索引
-
尽量使用联合索引,减少单列索引,查询时,联合索引很多时候可以覆盖索引,节省存储空间,避免回表,提高查询效率
-
要控制索引的数量,索引并不是多多益善,索引越多。维护索引结构的代价也就越大,会影响增删改的效率
-
如果索引列不能存储NULL值,请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时它可以更好地缺点哪个索引最有效地用于查询。
索引优化
插入数据
- insert优化
- 批量插入
insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry');
- 手动事务提交
start transaction; insert into tb_test values(1,'Tom'),(2,'Cat'),(3,'Jerry'); insert into tb_test values(4,'Tom'),(5,'Cat'),(6,'Jerry'); insert into tb_test values(7,'Tom'),(8,'Cat'),(9,'Jerry'); commit;
- 主键顺序插入
主键乱序插入: 8 1 9 23 33 3 42 主键顺序插入:1 2 3 4 5 6 7 8 9 14 22
- 大批量插入数据:
- 如果一次性需要插入大批量数据,使用insert语句插入性能较低,此时可以使用MySQL数据库提供的load指令进行插入
#客户端连接服务端时,加上参数 --local-infile mysql --local-infile -u root -p #设置全局参数local_infile为1,开启从本地加载文件导入数据的开关 set global local_infile=1; #执行load指令将准备好的数据,加载到表结构中 load data local infile '/root/sql1.log' into table 'tb_user' fields terminated by ',' lines terminated by '\n';
- 如果一次性需要插入大批量数据,使用insert语句插入性能较低,此时可以使用MySQL数据库提供的load指令进行插入
- 批量插入
主键优化
- 数据组织方式
- 在InnoDB存储方式中,表数据都是根据主键顺序组织存放的,这种存储方式的表称为索引组织表(index organized table IOT)。
- 页分裂
- 页可以为空,也可以填充一半,也可以填充100%。每个页包含了2-N行数据(如果一行数据足够大,会行溢出),根据主键排列。
- 页合并
- 当删除一行记录时,实际上记录并没有被物理删除,只是记录被标记为删除并且它的空间变得允许被其他记录声明使用。
- 当页中删除的记录达到 MERGE_THRESHOLD(默认为页的50%),InnoDB会开始寻找最靠近的页(前或后)看看是否可以将两个页合并以优化空间使用。
- MERGE_THRESHOLD:合并页的阈值,可以自己设置,在创建表或者创建索引时指定。
- 主键设计原则
- 满足业务需求的情况下,尽量降低主键长度。
- 插入数据时,尽量选择顺序插入,选择使用AUTO_INCREMENT自增主键。
- 尽量不要使用UUID做主键或者是其他自然主键,如身份证号。
- 业务操作时,避免对主键的修改。
order by优化
- Usin filesort:通过表的索引或全表扫描,读取满足条件的数据行,然后在排序缓冲区sort buffer中完成排序操作,所有不是通过索引直接返回排序结果的排序都叫做 FileSort 排序。
- Using index :通过有序索引顺序扫描直接返回有序数据,这种情况即位 Using index ,不需要额外排序,操作效率高。
#在没有创建索引时,根据age,phone进行排序 explain select id,age,phone from tb_user order by age,phone; #创建索引 create idnex idx_user_age_phone_aa on tb_user(age,phone); #创建索引后,根据age,phone进行升序排序 explain select id,age,phone from tb_user order by age,phone; #创建索引后,根据age,phone进行降序排序 explain select id,age,phone from tb_user order by age desc,phone desc; #根据age,phone进行降序一个升序,一个降序 explain select id,age,phone from tb_user order by age asc,phone desc; #创建索引 create index idx_user_age_phone_ad on tb_user(age asc,phone desc); #根据age,phone进行降序一个升序,一个降序 explain select id,age,phone from tb_user order by age asc,phone desc;
- 根据排序字段建立合适的索引,多字段排序时,也遵循最左前缀法则。
- 尽量使用覆盖索引
- 多字段排序,一个升序一个降序,此时需要注意联合索引在创建时的规则(ASC/DESC)。
- 如果不可避免的出现filesort,大数据排序时,可以适当增大排序缓冲区大小 sort_buffer_size (默认256k).
group by 优化
#删除目前的联合索引 idx_user_pro_age_sta
drop index idx_user_pro_age_sta on tb_user;
#执行分组操作,根据profession字段分组
explain select profession,count(*) from tb_user group by profession;
#创建索引
create index idx_user_pro_age_sta on tb_user(profession,age,status);
#执行分组操作,根据profession字段分组
explain select profession,count(*) from tb_user group by profession;
#执行分组操作,根据profession字段分组
explain select profession,count(*) from tb_user group by profession,age;
- 在分组操作时,可以通过索引来提高效率。
- 分组操作时,索引的使用也是满足最左前缀法则的。
limit优化
- 一个常见又非常头疼的问题是 limit 2000000,10 ,此时需要MySQL排序前2000010 记录,仅仅返回2000000 - 2000010 的记录,其他记录丢弃,查询排序的代价非常大
- 优化思路:一般分页查询时,通过创建 覆盖索引 能够较好地提高性能,可以通过覆盖索引加子查询形式进行优化
explain select * from tb_sku t,(select id from tb_sku order by id limit 2000000,10) a where t.id=a.id;
count优化
- MyISAM 引擎把一个表的总行数存在了磁盘上,因此执行count(*) 的时候会直接返回这个数,效率很高
- InnoDB 引擎就麻烦了,它执行 count(*)的时候,需要把数据一行一行地从引擎里面读出来,然后累积计数
- 优化思路:自己计数
- count的几种用法
- count()是一个聚合函数,对于返回的结果集,一行一行地判断,如果count函数的参数不是NULL,累计值就加1,否则不加,最后返回累计值。
- 用法:count(*)、count(主键)、count(字段)、count(1)。
- count(主键)
- InnoDB 引擎会遍历整张表,把每一行的 主键id 值都取出来,返回给服务层。服务层拿到主键后,直接按行进行累加(主键不可能为null)。
- count(字段)
- 没有not null约束:InnoDB 引擎会遍历整张表把每一行的字段值都取出来,返回给服务层,服务层判断是否为null,不为null,计数累加。有not null约束:InnoDB 引擎会遍历整张表把每一行的字段值都取出来,返回给服务层,直接按行进行累加
- count(1)
- InnoDB 引擎遍历整张表,但不取值。服务层对于返回的每一行,放一个数字"1"进去,直接按行进行累加。
- count(*)
- InnoDB引擎并不会把全部字段取出来,而是专门做了优化,不取值,服务层直接按行进行累加
- 按照效率排序的话,count(字段) <count(主键 id) <count(1)~=count(*) ,所以尽量使用 count(*)。
update优化
InnoDB的行锁是针对索引加的锁,不是针对记录加的锁,并且改索引不能失效,否则会从行锁升级为表锁