一、存储引擎
1.MySQL体系结构
- 连接层
最上层是一些客户端和链接服务,主要完成一些类似干连接处理,授权认证、及相关的安全方案。服务器也会为安全接入的每个客户端验证它所具有的操作权限。 - 服务层
第二层架构主要完成大多数的核心服务功能,如SQL接口,并完成缓存的查询,SQL的分析和优化,部分内置函数的执行。所有跨存储引擎的功能也在这一层实现,如 过程、函数等。 - 引擎层
存储引擎真正的负责了MVSOL中数据的存储和提取,服务器通过AP!和存储引擎进行通信。不同的存储引擎具有不同的功能,这样我们可以根据自己的需要,来选取合适的存储引擎。 - 存储层
主要是将数据存储在文件系统之上,并完成与存储引擎的交互。
注意:索引是位于存储引擎层的,所以不同的引擎对应着不同的索引结构
2.存储引擎简介
- 概念:存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的,而不是基于库的,所以存储引擎也可被称为表类型。
- 查看所有引擎的SQL语句
show engines ;
- 自主选定引擎
create table myISAM(
id int,
name varchar(50)
)engine =MyISAM;
- 查看表的引擎相关信息
show create table test;
InnoDB引擎为默认引擎
3.存储引擎特点
InnoDB引擎
- 1.介绍
InnoDB是一种兼顾高可靠性和高性能的通用存储引擎,在 MVSOL5.5之后,InnoDB是默认的 MySQL 存储引擎,
- 2.特点
DML操作遵循ACID模型,支持事务
行级锁,提高并发访问性能;
支持 外键 FOREIGN KEY约束,保证数据的完整性和正确性:
-
- 文件
xxx.ibd:xxx代表的是表名,innoDB引擎的每张表都会对应这样一个表空间文件,存储该表的表结构(frm、sdi)、数据和索引。参数:innodb file per table
- 4.逻辑存储结构
TableSpece:表空间
segment:段
Extent:区
Page:页
ROW:行 - 文件存储位置(以我的MySQL版本为参考)
C:\ProgramData\MySQL\MySQL Server 8.0\Data
MyISAM引擎
- 1.介绍
MyISAM是MySQL早期的默认存储引擎。
- 2.特点
不支持事务,不支持外键,支持表锁,不支持行锁,访问速度快
- 3.文件
xxx.sdi:存储表结构信息
xxx.MYD:存储数据
xxx.MYI:存储索引
Memory引擎
- 1.介绍
Memorv引擎的表数据存储在内存中的,由于受到硬件问题、或断电问题的影响,只能将这些表作为临时表或缓存使用
- 2.特点
内存存放
hash索引(默认)
- 3.文件
xxx.sdi:存储表结构信息
引擎特点区分
4.存储引擎选择
在选择存储引擎时,应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统,还可以根据实际情况选择多种存储引擎进行组合。
InnoDB:
是Mysql的默认存储引擎,支持事务、外键。如果应用对事务的完整性有比较高的要求,在并发条件下要求数据的一致》性,数据操作除了插入和查询之外,还包含很多的更新、删除操作,那么InnoDB存储引擎是比较合适的选择。
MyISAM:
如果应用是以读操作和插入操作为主,只有很少的更新和删除操作,并且对事务的完整性、并发性要求不是很高,那么选择这个存储引擎是非常合适的。
MEMORY:
将所有数据保存在内存中,访问速度快,通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制,太大的表>无法缓存在内存中,而且无法保障数据的安全性。
二、索引
1、索引概述
- 索引(index)是帮助MySQL高效获取数据的数据结构(有序)。在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法,这种数据结构就是索引。
没有索引的数据库查找 (全表扫描) 效率是很低,有索引则能更高速的查找数据。
- 优缺点
2、索引结构
我们平常所说的索引,如果没有特别指明,都是指B+树结构组织的索引
为了解决大数据量情况下,层级较深,检索速度慢-----------------》B+Tree
1.B-Tree(多路平衡查找树)
2.B+Tree
3.Hash索引
-
Hash索引特点
1.Hash索引只能用于对等比较(=,in),不支持范围查询(between,>,
2.无法利用索引完成排序操作
3.查询效率高,通常只需要一次检索就可以了,效率通常要高于B+tree索引 -
思考
为什么InnoDB存储引擎选择使用B+tree索引结构?
1.相对于二叉树,层级更少,搜索效率高
2.对于B-tree,无论是叶子节点还是非叶子节点,都会保存数据,这样导致一页中存储的键值减少,指针跟着减少,要同样保存大量数据,只能增加树的高度,导致性能降低;
3.相对Hash索引,B+tree支持范围匹配及排序操作;
3、索引分类
-
聚集索引选取规则:
如果存在主键,主键索引就是聚集索引。
如果不存在主键,将使用第一个唯一(UNIQUE)索引作为聚集索引。
如果表没有主键,或没有合适的唯一索引,则InnoDB会自动生成一个rowid作为隐藏的聚集索引。 -
如图所示,聚集索引与行数据对应,二级索引与主键对应。
-
回表查询:通过二级索引拿到对应数据的主键,再通过聚集索引由主键找到对应的行数据。
-
思考题 ————InnoDB主键索引的B+tree高度为多高呢?
4、索引语法
- 创建索引
CREATE [UNIQUE|FULLTEXT] INDEX index_name ON table_name (index_col_name,.. );
后面括号里面有多个参数的时候,表示联合索引,即:一个索引,多个字段使用。
- 查看索引
SHOW INDEX FROM table_name;
- 删除索引
DROP INDEX index_name ON table_name ;
5、索引—性能分析
通过查询数据库的主要功能(增、删、改、查),进行针对性优化。
5-1 访问频次查询
MySQL客户端连接成功后,通过show [session|global] status命令可以提供服务器状态信息。通过如下指令,可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次:
SHOW GLOBAL STATUS LIKE 'Com_______';
5-2 慢查询日志
作用:收集相关功能较慢的语句,方便进行针对性优化。
慢查询日志记录了所有执行时间超过指定参数(long_query_time,单位:秒,默认10秒)的所有SQL语句的日志。MySQL的慢查询日志默认没有开启,需要在MySQL的配置文件(C:\ProgramData\MySQL\MySQL Server 8.0\my.ini)中配置如下信息:
#开启MySOL慢日志查询开关 |
---|
slow_query_log=1 |
#设置慢日志的时间为2秒,SOL语句执行时间超过2秒,就会视为慢查询,记录慢查询日志long |
long_query_time=2 |
配置完毕之后,通过以下指令重新启动MySQL服务器进行测试,就可以根据慢日志文件中记录的信息,查询处需要优化的语句。
5-3 profile详情
show profiles 能够在做SOL优化时帮助我们了解时间都耗费到哪里去了。通过select @@have_profiling参数,能够看到当前MySQL是否支持profile操作:
-- 当前MySQL是否支持profile操作
select @@have_profiling;
-- 查看profiling是否开启。
select @@profiling;
-- 开启profiling
set @@profiling=1;
#查看每一条SOL的耗时基本情况
show profiles;
#查看指定query id的SQL语句各个阶段的耗时情况
show profile for query query_id;
#查看指定query id的SQL语句CPU的使用情况
show profile cpu for query query_id;
5-4 explain执行计划
EXPLAIN 或者 DESC命令获取 MySQL如何执行 SELECT语句的信息,包括在 SELECT 语句执行过程中表如何连接和连接的顺序。
语法:
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件
- EXPLAIN 执行计划各字段含义:
id
select查询的序列号,表示查询中执行select子句或者是操作表的顺序(id相同,执行顺序从上到下:id不同,值越大,越先执行)。
-- 查询选择MySQL课程的学生
select id from course where name='MySQL'; -->id=3
select studentId from student_course where courseId='3';
explain select * from student s where s.id in(select studentId from student_course where courseId=(select id from course where name='MySQL'));
select type
表示 SELECT的类型,常见的取值有 SIMPLE(简单表,即不使用表连接或者子查询)、PRIMARY(主查询,即外层的查询)UNION(UNION 中的第二个或者后面的查询语句)、SUBQUERY(SELECT/WHERE之后包含了子查询)等
type
表示连接类型,性能由好到差的连接类型为NULL、system、const、eq_ref、ref、range、index、all 。
语句优化的时候,尽量往前优化,当然NULL是优化不到的。
possible_key
显示可能应用在这张表上的索引,一个或多个
Key
实际使用的索引,如果为NULL,则没有使用索引。
Key_len
表示索引中使用的字节数,该值为索引字段最大可能长度,并非实际使用长度,在不损失精确性的前提下,长度越短越好。
rows
MVSOL认为必须要执行查询的行数,在innodb引擎的表中,是一个估计值,可能并不总是准确的
filtered
表示返回结果的行数占需读取行数的百分比,filtered 的值越大越好
6、索引的使用
6-1最左前缀法则
如果索引关联了多列(联合索引),要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列 (Column name) 开始,并且不跳过索引中的列。(where 之后 所有与索引关联的列都要存在)
如果跳跃某一列,索引将部分失效(后面的字段索引失效)。
举例
1.创建联合索引
create index key_num_name_age on employee(workaddres,name,age);
2.查询该表的索引
show index from employee;
- 查看执行效率--------正常情况------------从索引的最左列开始,并且不跳过索引中的列。
走索引搜索
explain select * from employee where workaddres='00314'&&name='lee'&&age=18;
4. 查看执行效率--------不正常情况------------不从索引的最左列开始。
走的是全表搜索,效率低下!
explain select * from employee where name='lee' and age=18;
6-2 范围查询
联合索引中,出现范围查询(>,<),范围查询右侧的列索引失效
因此建议使用(>=,<=)而不是(>,<)
6-3 索引列运算
不要在索引列上进行运算操作,索引将失效,
6-4 字符串不加引号
字符串类型字段使用时,不加引号,索引将失效
6-3 模糊查询
如果仅仅是尾部模糊匹配,索引不会失效。如果是头部模糊匹配,索引失效。
(但是我测试为什么’%'放前面,放后面,都没有使用索引啊)
6-4 or连接的条件
条件为or时,左右两边的操作数都应该有索引,并且不是联合索引。才能够走索引查询。
6-5 数据分布影响
如果MySQL评估使用索引比全表更慢,则不使用索引
6-6 SQL提示
SQL提示,是优化数据库的一个重要手段,简单来说,就是在SQL语句中加入一些人为的提示来达到优化操作的目的,
就比如说一个字段,它有两个索引,包括一个联合索引,这是系统会默认选择联合索引进行搜索,如果我需要改变选择的话,就需要用到SQL提示。
- use index() —use 只是建议性使用指定索引
explain select * from employee use index (index_num) where worknumber='00314';
- ignore index( )
explain select * from employee ignore index (index_num) where worknumber='00314';
-- 忽略index_num之后,走联合索引。
- force index () -----强制使用指定索引。
explain select * from employee force index (index_num) where worknumber='00314';
6-7 查询覆盖索引
-
尽量减少 * 的使用进行查询,而应做到查询覆盖索引,
因为:在使用索引查询时,当需要查询的字段没有索引时,那么需要通过其他字段的索引去查找他,就必须用到回表查询,回表查询势必就会减低查找效率。
而 * 查询的是一列的数据,一列数据中总有一些是没有索引的,就必须要回表,因此尽量减少select *的查询
-
在explain语句中字段extra含义有如下
6-8 前缀索引
当字段类型为字符串(varchar,text等)时,有时候需要索引很长的字符串,这会让索引变得很大,查询时,浪费大量的磁盘IO,影响查询效率。此时可以只将字符串的一部分前缀,建立索引,这样可以大大节约索引空间,从而提高索引效率。
- 前缀长度的选择
可以根据索引的选择性(不重复性)来决定,而选择性是指不重复的索引值(基数)和数据表的记录总数的比值,索引选择性越高则查询效率越高,唯一索引的选择性是1,这是最好的索引选择性,性能也是最好的。
简单点说就是用最少的字符串长度把所有该字段的数据都能够概括进来
步骤如下
- 选择性值查询如下
select count(distinct 字段名)/count(*) from tablename ;
- 确定前缀长度最小的最高选择性。
select count(distinct substring(字段名,1,选择性最高的前缀长度))/count(*) from employee ;
- 前缀索引使用
create index 字段名_选择性最高的前缀长度 on tablename(字段名(前缀长度));
7 索引设计原则
- 针对于数据量较大,且查询比较频繁的表建立索引。
- 针对于常作为查询条件(where)、排序(orderby)、分组(groupby)操作的字段建立索引。
- 尽量选择区分度高的列作为索引,尽量建立唯一索引,区分度越高,使用索引的效率越高。
- 如果是字符串类型的字段,字段的长度较长,可以针对于字段的特点,建立前缀索引。
- 尽量使用联合索引,减少单列索引,查询时,联合索引很多时候可以覆盖索引,节省存储空间,避免回表,提高查询效率。
- 要控制索引的数量,索引并不是多多益善,索引越多,维护索引结构的代价也就越大,会影响增删改的效率。
- 如果索引列不能存储NULL值,请在创建表时使用NOTNULL约束它。当优化器知道每列是否包含NULL值时,它可以更好地确定哪个索引最有效地用于查询。