西安网站建设-中国互联,网站备案和不备案有什么区别,万网,外星人做的网站文章目录 一、存储引擎1.1 MySQL体系结构1.2 存储引擎介绍1.3 存储引擎特点1.4 存储引擎选择 二、索引2.1 基本介绍2.2 索引结构2.3 索引分类2.4 索引语法2.5 SQL性能分析2.6 索引使用2.6.1 最左前缀法则2.6.2 范围查询2.6.3 索引失效情况2.6.4 SQL提示2.6.5 覆盖索引2.6.6 前缀… 文章目录 一、存储引擎1.1 MySQL体系结构1.2 存储引擎介绍1.3 存储引擎特点1.4 存储引擎选择 二、索引2.1 基本介绍2.2 索引结构2.3 索引分类2.4 索引语法2.5 SQL性能分析2.6 索引使用2.6.1 最左前缀法则2.6.2 范围查询2.6.3 索引失效情况2.6.4 SQL提示2.6.5 覆盖索引2.6.6 前缀索引2.6.7 单列索引与联合索引 2.7 索引设计原则 一、存储引擎
1.1 MySQL体系结构 1). 连接层 最上层是一些客户端和链接服务包含本地sock 通信和大多数基于客户端/服务端工具实现的类似于TCP/IP的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。 2). 服务层 第二层架构主要完成大多数的核心服务功能如SQL接口并完成缓存的查询SQL的分析和优化部分内置函数的执行。所有跨存储引擎的功能也在这一层实现如过程、函数等。在该层服务器会解析查询并创建相应的内部解析树并对其完成相应的优化如确定表的查询的顺序是否利用索引等最后生成相应的执行操作。如果是select语句服务器还会查询内部的缓存如果缓存空间足够大这样在解决大量读操作的环境中能够很好的提升系统的性能。 3). 引擎层 存储引擎层存储引擎真正的负责了MySQL中数据的存储和提取服务器通过API和存储引擎进行通信。不同的存储引擎具有不同的功能这样我们可以根据自己的需要来选取合适的存储引擎。数据库中的索引是在存储引擎层实现的。 4). 存储层 数据存储层主要是将数据(如:redolog、undolog、数据、索引、二进制日志、错误日志、查询日志、慢查询日志等)存储在文件系统之上并完成与存储引擎的交互。和其他数据库相比MySQL有点与众不同它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎上插件式的存储引擎架构将查询处理和其他的系统任务以及数据的存储提取分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。 1.2 存储引擎介绍
对于存储引擎它是mysql数据库的核心我们也需要在合适的场景选择合适的存储引擎。接下来就来介绍一下存储引擎 存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的而不是基于库的所以存储引擎也可被称为表类型。我们可以在创建表的时候来指定选择的存储引擎如果没有指定将自动选择默认的存储引擎。 建表时指定存储引擎
CREATE TABLE 表名(
字段1 字段1类型 [ COMMENT 字段1注释 ] ,
......
字段n 字段n类型 [COMMENT 字段n注释 ]
) ENGINE INNODB [ COMMENT 表注释 ] ;查询当前数据库支持的存储引擎
show engines; 1.3 存储引擎特点
重点介绍三种存储引擎InnoDB、MyISAM、Memory的特点。
先来重点看看InnoDB引擎。
1). 介绍 InnoDB是一种兼顾高可靠性和高性能的通用存储引擎在 MySQL 5.5 之后InnoDB是默认的MySQL 存储引擎。 2). 特点
DML操作遵循ACID模型支持事务 行级锁提高并发访问性能 支持外键FOREIGN KEY约束保证数据的完整性和正确性 3). 文件 xxx.ibdxxx代表的是表名innoDB引擎的每张表都会对应这样一个表空间文件存储该表的表结构、数据和索引。
show variables like innodb_file_per_table;#查看是否开启如果该参数开启代表对于InnoDB引擎的表每一张表都对应一个ibd文件。 我们直接打开MySQL的数据存放目录 C:\ProgramData\MySQL\MySQL Server 8.0\Data 这个目录下有很多文件夹不同的文件夹代表不同的数据库随便打开一个数据库文件夹。 这些文件是基于二进制存储的不能直接基于记事本打开我们可以使用mysql提供的一 个指令ibd2sdi通过该指令就可以从ibd文件中提取sdi信息而sdi数据字典信息中就包含该表的表结构。 4). 逻辑存储结构
表空间 : InnoDB存储引擎逻辑结构的最高层ibd文件其实就是表空间文件在表空间中可以包含多个Segment段。段 : 表空间是由各个段组成的常见的段有数据段、索引段、回滚段等。InnoDB中对于段的管理都是引擎自身完成不需要人为对其控制一个段中包含多个区。区 : 区是表空间的单元结构每个区的大小为1M。 默认情况下 InnoDB存储引擎页大小为16K 即一个区中一共有64个连续的页。页 : 页是组成区的最小单元页也是InnoDB 存储引擎磁盘管理的最小单元每个页的大小默认为16KB。为了保证页的连续性InnoDB 存储引擎每次从磁盘申请 4-5 个区。行 : InnoDB 存储引擎是面向行的也就是说数据是按行进行存放的在每一行中除了定义表时所指定的字段以外还包含两个隐藏字段。 再来简单看看MyISAM引擎。
1). 介绍 MyISAM是MySQL早期的默认存储引擎。 2). 特点
不支持事务不支持外键 支持表锁不支持行锁 访问速度快 3). 文件 xxx.sdi存储表结构信息 xxx.MYD: 存储数据 xxx.MYI: 存储索引 最后简单来看看Memory引擎。
1). 介绍 Memory引擎的表数据时存储在内存中的由于受到硬件问题、或断电问题的影响只能将这些表作为临时表或缓存使用。
2). 特点 内存存放 hash索引默认
3).文件 xxx.sdi存储表结构信息 三者的区别与联系
1.4 存储引擎选择
在选择存储引擎时应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统还可以根据实际情况选择多种存储引擎进行组合。
InnoDB: 是Mysql的默认存储引擎支持事务、外键。如果应用对事务的完整性有比较高的要求在并发条件下要求数据的一致性数据操作除了插入和查询之外还包含很多的更新、删除操作那么InnoDB存储引擎是比较合适的选择。MyISAM如果应用是以读操作和插入操作为主只有很少的更新和删除操作并且对事务的完整性、并发性要求不是很高那么选择这个存储引擎是非常合适的。MEMORY将所有数据保存在内存中访问速度快通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制太大的表无法缓存在内存中而且无法保障数据的安全性。 二、索引
2.1 基本介绍
索引index是帮助MySQL高效获取数据的数据结构(有序)。在数据之外数据库系统还维护着满足特定查找算法的数据结构这些数据结构以某种方式引用指向数据 这样就可以在这些数据结构上实现高级查找算法这种数据结构就是索引。 表如上图所示假如我们要执行的SQL语句为:select * from user where age 45;在无索引情况下就需要从第一行开始扫描一直扫描到最后一行我们称之为全表扫描性能很低。
但如果我们针对于这张表建立了索引假设索引结构就是二叉树那么也就意味着会对age这个字段建立一个二叉树的索引结构。 此时我们再进行查询时只需要扫描三次就可以找到数据了极大的提高的查询的效率。
优缺点 2.2 索引结构
2.2.1概述 MySQL的索引是在存储引擎层实现的不同的存储引擎有不同的索引结构主要包含以下几种 上述是MySQL中所支持的所有的索引结构接下来我们再来看看不同的存储引擎对于索引结构的支持情况。 2.2.2二叉树 如图所示如果选择二叉树作为索引结构会存在以下缺点
顺序插入时会形成一个链表查询性能大大降低。 大数据量情况下层级较深检索速度慢。
由于红黑树也是一颗二叉树所以也会存在一个缺点大数据量情况下层级较深检索速度慢。 2.2.3B-Tree
所以在MySQL的索引结构中并没有选择二叉树或者红黑树而选择的是BTree那么什么是BTree呢在详解BTree之前先来介绍一个B-Tree。
B树是一种多路平衡查找树相对于二叉树B树每个节点可以有多个分支即多叉。
插入一组数据 100 65 169 368 900 556 780 35 215 1200 234 888 158 90 1000 88 120 268 250 。 特点
5阶的B树每一个节点最多存储4个key对应5个指针。 一旦节点存储的key数量到达5就会裂变中间元素向上分裂。 在B树中非叶子节点和叶子节点都会存放数据。 2.2.4BTree 绿色框框起来的部分是索引部分仅仅起到索引数据的作用不存储数据。 红色框框起来的部分是数据存储部分在其叶子节点中要存储具体的数据。
仍然插入上面的数据。 BTree 与 B-Tree相比主要有以下三点区别
所有的数据都会出现在叶子节点。 叶子节点形成一个单向链表。 非叶子节点仅仅起到索引数据作用具体的数据都是在叶子节点存放的。 上述我们所看到的结构是标准的BTree的数据结构接下来我们再来看看MySQL中优化之后的BTree。MySQL索引数据结构对经典的BTree进行了优化。在原BTree的基础上增加一个指向相邻叶子节点的链表指针就形成了带有顺序指针的BTree提高区间访问的性能利于排序。 2.2.5Hash 哈希索引就是采用一定的hash算法将键值换算成新的hash值映射到对应的槽位上然后存储在hash表中。如果两个(或多个)键值映射到一个相同的槽位上他们就产生了hash冲突也称为hash碰撞可以通过链表来解决。 特点
Hash索引只能用于对等比较(in)不支持范围查询between … 无法利用索引完成排序操作 查询效率高通常(不存在hash冲突的情况)只需要一次检索就可以了效率通常要高于Btree索引
存储引擎支持
在MySQL中支持hash索引的是Memory存储引擎。 而InnoDB中具有自适应hash功能hash索引是InnoDB存储引擎根据BTree索引在指定条件下自动构建的。 2.3 索引分类
在MySQL数据库将索引的具体类型主要分为以下几类主键索引、唯一索引、常规索引、全文索引。 聚集索引二级索引:
在InnoDB存储引擎中根据索引的存储形式又可以分为以下两种 聚集索引选取规则:
如果存在主键主键索引就是聚集索引。 如果不存在主键将使用第一个唯一UNIQUE索引作为聚集索引。 如果表没有主键或没有合适的唯一索引则InnoDB会自动生成一个rowid作为隐藏的聚集索引。 聚集索引和二级索引的具体结构如下 由上图可知
聚集索引的叶子节点下挂的是这一行的数据 。 二级索引的叶子节点下挂的是该字段值对应的主键值。 接下来我们来分析一下当我们执行如下的SQL语句时具体的查找过程是什么样子的。 具体过程如下:
由于是根据name字段进行查询所以先根据nameArm’到name字段的二级索引中进行匹配查找。但是在二级索引中只能查找到 Arm 对应的主键值 10。 由于查询返回的数据是*所以此时还需要根据主键值10到聚集索引中查找10对应的记录最终找到10对应的行row。 最终拿到这一行的数据直接返回即可这种方式叫做回表查询。 2.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 ;案例演示
# name字段为姓名字段该字段的值可能会重复为该字段创建索引
mysql create index idx_user_name on tb_user(name);
# phone手机号字段的值是非空且唯一的为该字段创建唯一索引
mysql create unique index idx_user_phone on tb_user(phone);
# 为profession、age、status创建联合索引
mysql create index idx_user_pro_age_sta on tb_user(profession,age,status);
# 为email建立合适的索引来提升查询效率
mysql create index idx_email on tb_user(email);
# 查看tb_user表的所有的索引数据
mysql show index from tb_user;2.5 SQL性能分析
2.5.1 SQL执行频率
通过如下指令可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次
-- session 是查看当前会话 ;
-- global 是查询全局数据 ;
SHOW GLOBAL STATUS LIKE Com_______;2.5.2 慢查询日志
慢查询日志记录了所有执行时间超过指定参数long_query_time单位秒默认10秒的所有SQL语句的日志。 MySQL的慢查询日志默认没有开启我们可以查看一下系统变量 slow_query_log。
# 查看慢查询日志记录是否开启
mysql show variables like slow_query_log;如果要开启慢查询日志需要在MySQL的配置文件/etc/my.cnf中配置如下信息
# 开启MySQL慢日志查询开关
slow_query_log1
# 设置慢日志的时间为2秒SQL语句执行时间超过2秒就会视为慢查询记录慢查询日志
long_query_time2开启后我们可以通过下面的语句来对慢查询进行监督
[rootlocalhost mysql]# cat /var/lib/mysql/localhost-slow.log
/usr/sbin/mysqld, Version: 8.0.26 (MySQL Community Server - GPL). started with:
Tcp port: 3306 Unix socket: /var/lib/mysql/mysql.sock
Time Id Command Argument
[rootlocalhost mysql]# tail -f localhost-slow.log 2.5.3 profile详情
# 查看当前MySQL是否支持profile操作
SELECT have_profiling ;
# 查看当前profiling的值
SELECT profiling ;
# 通过set语句在session/global级别开启profiling
SET profiling 1;-- 查看每一条SQL的耗时基本情况
show profiles;
-- 查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;
-- 查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;2.5.4 explain
EXPLAIN 或者 DESC命令获取 MySQL 如何执行 SELECT 语句的信息包括在 SELECT 语句执行过程中表如何连接和连接的顺序。 Explain 执行计划中各个字段的含义
字段含义idselect查询的序列号表示查询中执行select子句或者是操作表的顺序(id相同执行顺序从上到下id不同值越大越先执行)。select_type表示 SELECT 的类型常见的取值有 SIMPLE简单表即不使用表连接或者子查询、PRIMARY主查询即外层的查询、UNIONUNION 中的第二个或者后面的查询语句、SUBQUERYSELECT/WHERE之后包含了子查询等type表示连接类型性能由好到差的连接类型为NULL、system、const、eq_ref、ref、range、 index、allpossible_key显示可能应用在这张表上的索引一个或多个key实际使用的索引如果为NULL则没有使用索引key_len表示索引中使用的字节数 该值为索引字段最大可能长度并非实际使用长度在不损失精确性的前提下 长度越短越好rowsMySQL认为必须要执行查询的行数在innodb引擎的表中是一个估计值可能并不总是准确的filtered表示返回结果的行数占需读取行数的百分比 filtered 的值越大越好 2.6 索引使用
2.6.1 最左前缀法则
如果索引了多列联合索引要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始并且不跳过索引中的列。如果跳跃某一列索引将会部分失效(后面的字段索引失效)。
以 tb_user 表为例我们先来查看一下之前 tb_user 表所创建的索引。 对于最左前缀法则指的是查询时最左边的列也就是profession必须存在否则索引全部失效。而且中间不能跳过某一列否则该列后面的字段索引将失效。 以上的这三组测试中我们发现只要联合索引最左边的字段 profession存在索引就会生效只不过索引的长度不同。 而且由以上三组测试我们也可以推测出profession字段索引长度为47、age字段索引长度为2、status字段索引长度为5。 通过上面的这两组测试我们也可以看到索引并未生效原因是因为不满足最左前缀法则联合索引最左边的列profession不存在。 上述的SQL查询时存在profession字段最左边的列是存在的索引满足最左前缀法则的基本条件。但是查询时跳过了age这个列所以后面的列索引是不会使用的也就是索引部分生效所以索引的长度就是47。 当查询字段顺序发生改变时我们可以看到是完全满足最左前缀法则的索引长度54联合索引是生效的。即最左前缀法则中指的最左边的列是指在查询时联合索引的最左边的字段(即是第一个字段)必须存在与我们编写SQL时条件编写的先后顺序无关。 2.6.2 范围查询 联合索引中出现范围查询(,)范围查询右侧的列索引失效。 当范围查询使用 或 时走联合索引了但是索引的长度为49就说明范围查询右边的status字段是没有走索引的。 当范围查询使用 或 时走联合索引了但是索引的长度为54就说明所有的字段都是走索引的。所以在业务允许的情况下尽可能的使用类似于 或 这类的范围查询而避免使用 或 。 2.6.3 索引失效情况
失效方式一索引列运算
不要在索引列上进行运算操作 索引将失效。 失效方式二字符串不加引号
字符串类型字段使用时不加引号索引将失效。 失效方式三模糊查询
如果仅仅是尾部模糊匹配索引不会失效。如果是头部模糊匹配索引失效。 经过上述的测试我们发现在like模糊查询中在关键字后面加%索引可以生效。而如果在关键字前面加了%索引将会失效。 失效方式四or连接条件
用or分割开的条件 如果or前的条件中的列有索引而后面的列中没有索引那么涉及的索引都不会被用到。 由于age没有索引所以即使id有索引索引也会失效所以需要针对于age也要建立索引。 失效方式五数据分布影响
如果MySQL评估使用索引比全表更慢则不使用索引。 经过测试我们发现相同的SQL语句只是传入的字段值不同最终的执行计划也完全不一样这是为什么呢就是因为MySQL在查询时会评估使用索引的效率与走全表扫描的效率如果走全表扫描更快则放弃索引走全表扫描。 因为索引是用来索引少量数据的如果通过索引查询返回大批量的数据则还不如走全表扫描来的快此时索引就会失效。 2.6.4 SQL提示
SQL提示是优化数据库的一个重要手段简单来L语句中加入一些人为的提示来达到优 化操作的目的。
use index 建议MySQL使用哪一个索引完成此次查询仅仅是建议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 软件工程;force index 强制使用索引
explain select * from tb_user force index(idx_user_pro) where profession 软件工程;2.6.5 覆盖索引 尽量使用覆盖索引减少select *。 那么什么是覆盖索引呢 覆盖索引是指 查询使用了索引并且需要返回的列在该索引中已经全部能够找到。 从上述的执行计划我们可以看到这些SQL语句的执行计划前面所有的指标都是一样的看不出来差异。但是此时我们主要关注的是后面的Extra第一条SQL的结果为 Using where; UsingIndex ; 而后面两条SQL的结果为: Using index condition 。 2.6.6 前缀索引
当字段类型为字符串varchartextlongtext等时有时候需要索引很长的字符串这会让索引变得很大查询时浪费大量的磁盘IO 影响查询效率。此时可以只将字符串的一部分前缀建立索引这样可以大大节约索引空间从而提高索引效率。
语法
create index idx_xxxx on table_name(column(n)) ;示例
# 为tb_user表的email字段建立长度为5的前缀索引。
create index idx_email_5 on tb_user(email(5)); 前缀长度
可以根据索引的选择性来决定而选择性是指不重复的索引值基数和数据表的记录总数的比值索引选择性越高则查询效率越高 唯一索引的选择性是1这是最好的索引选择性性能也是最好的。
select count(distinct email) / count(*) from tb_user ;
select count(distinct substring(email,1,5)) / count(*) from tb_user ;前缀索引的查询流程 2.6.7 单列索引与联合索引
单列索引即一个索引只包含单个列。 联合索引即一个索引包含了多个列。 在业务场景中如果存在多个查询条件考虑针对于查询字段建立索引时建议建立联合索引而非单列索引避免回表查询。 如果查询使用的是联合索引具体的结构示意图如下 2.7 索引设计原则
针对于数据量较大且查询比较频繁的表建立索引。针对于常作为查询条件where、排序order by、分组group by操作的字段建立索引。尽量选择区分度高的列作为索引尽量建立唯一索引区分度越高使用索引的效率越高。如果是字符串类型的字段字段的长度较长可以针对于字段的特点建立前缀索引。尽量使用联合索引减少单列索引查询时联合索引很多时候可以覆盖索引节省存储空间避免回表提高查询效率。要控制索引的数量索引并不是多多益善索引越多维护索引结构的代价也就越大会影响增删改的效率。如果索引列不能存储NULL值请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时它可以更好地确定哪个索引最有效地用于查询。
