做网站开发要装什么软件,网站后台建设用到哪些编程语言,网店美工岗位要求,免费成品网站目录 视图视图的用途优点视图的缺点创建视图查看视图修改视图删除视图注意事项 索引索引的原理索引的数据结构二分查找法Hash结构Hash冲突#xff01;#xff01;#xff01; B树二叉查找树 存在问题改造二叉树——B树降低树的高度 B树特点案例继续优化的方向 改造B树——B树… 目录 视图视图的用途优点视图的缺点创建视图查看视图修改视图删除视图注意事项 索引索引的原理索引的数据结构二分查找法Hash结构Hash冲突 B树二叉查找树 存在问题改造二叉树——B树降低树的高度 B树特点案例继续优化的方向 改造B树——B树对比案例分析等值查询范围查询 索引的分类主键索引案例分析 辅助索引【普通索引】唯一索引主键索引与唯一索引的区别组合索引组合索引的数据结构组合索引的查询过程组合索引的最左匹配原则 覆盖索引执行计划分析覆盖索引未覆盖索引 SQL优化方向之一——避免回表案例分析 联合索引联合索引的创建原则联合索引的使用 索引的设计原则索引失效的情况搜集来的SQL优化经验(欢迎补充) 视图
视图是一张虚拟表表示一张表的部分数据或多张表的综合数据。 其结构和数据是建立在对表的查询基础上视图中不存放数据数据存放在视图所引用的原始表中 一个原始表根据不同用户的不同需求可以创建不同的视图
视图的用途
简化复杂查询通过将复杂的查询逻辑封装到视图中可以简化应用程序中的查询操作。数据安全性视图可以限制用户对底层表的访问权限从而提供更高的数据安全性和隐私保护。数据抽象和封装通过视图可以将多个表的数据抽象为一个虚拟表简化数据模型和应用程序开发。性能优化视图可以预先计算和存储结果集以提高查询性能并避免重复执行复杂查询。
优点 数据的抽象和简化视图是一个虚拟表它可以根据特定的查询语句从一个或多个表中选择、过滤和计算数据。通过使用视图可以将复杂的查询逻辑和多表连接操作封装为一个简化的视图查询提供了更简洁、更易于理解的数据模型。 数据安全性视图可以限制用户对底层表的访问权限。通过给用户授予对视图的访问权限可以隐藏底层表的结构和敏感数据只允许用户在特定条件下查看和操作数据。这为数据库提供了更高的安全性和数据保护。 逻辑数据分离和模块化通过视图可以将数据逻辑分离为不同的模块。这使得数据库的维护和管理更加灵活可以根据需要对各个模块进行独立的修改和优化而无需影响其他模块。 提高查询性能视图可以预先计算和存储查询结果从而提高查询性能。当使用视图进行查询时MySQL 可以利用预先计算的结果而不需要重新执行复杂的查询操作。这对于频繁执行相同查询的场景非常有用。 简化应用开发通过将复杂的查询逻辑封装为视图应用程序开发人员可以更快速、更轻松地构建应用程序。他们只需要简单地查询视图而无需关心视图背后的复杂查询逻辑和表结构。
视图的缺点 性能影响视图查询可能在执行时产生额外的性能开销。因为视图是根据查询语句动态生成的每次查询时都需要重新计算视图的结果。对于复杂的视图和大型数据集这可能导致查询较慢影响数据库性能。 更新限制默认情况下MySQL 不允许对包含特定条件的视图进行更新操作。这些条件包括使用聚合函数、DISTINCT、GROUP BY 和 HAVING 等的视图。因此如果你使用的视图有这些限制条件你将无法对其进行直接的插入、更新或删除操作。 数据一致性视图查询的结果是根据底层表的数据动态生成的而不是存储实际的数据副本。这意味着如果底层表的数据发生了变化但视图查询结果没有及时更新可能导致数据一致性的问题。 限制和复杂性视图的使用是受到一些限制的特别是在涉及复杂的查询和多表连接时。一些复杂的查询逻辑和操作可能无法在视图中实现这可能需要使用其他技术或重新设计查询。 管理复杂性随着数据库中视图的数量增加管理和维护视图变得更加困难。复杂的视图层次结构和依赖关系可能会导致维护和调试问题的增加
创建视图
CREATE VIEW view_name AS SELECT 语句;
#创建方便教师查看成绩的视图
DROP VIEW IF EXISTS v_result;
CREATE VIEW v_result
AS
SELECT s.studentno AS sno,studentname AS sname
,studentresult AS score
FROM student AS s
LEFT JOIN
result AS r
ON s.studentnor.studentno;查看视图
#使用视图
SELECT sno,sname,score FROM v_result;修改视图
ALTER VIEW view_name AS SELECT 语句
删除视图
DROP VIEW IF EXISTS v_result;注意事项
视图中可以使用多个表 create view view_student_score as select s.studentno,studentname,subjectname,studentresult,examdate from student s join result r on s.studentnor.studentno join subject su on r.subjectnosu.subjectno order by examdate desc;create view view_student_grade as select studentno,studentname,gradename from student s join grade g on s.gradeidg.gradeid;一个视图可以嵌套另一个视图
select t1.*,t2.gradename from view_student_score t1 join view_student_grade t2 on t1.studentnot2.studentno;对视图数据进行添加、更新和删除操作直接影响所引用表中的数据【不建议】当视图数据来自多个表时不允许添加和删除数据
使用视图修改数据会有许多限制一般在实际开发中视图仅用作查询
索引
举例汉语字典中的汉字按页存放一般都有汉语拼音目录索引、偏旁部首目录等。我们可以根据拼音或偏旁部首快速查找某个字词。MySQL官方对索引定义是存储引擎用于快速查找记录的一种数据结构。需要额外开辟空间和数据维护工作。索引底层数据结构存在很多种类型常见的索引结构有: B 树 B树 和 Hash、红黑树。在 MySQL 中无论是 Innodb 还是 MyIsam都使用了 B树作为索引结构。
索引的原理
简单来说以空间换时间。
一般来说索引本身也很大不可能全部存储在内存中因此索引往往是存储在磁盘上的文件中的可能存储在单独的索引文件中也可能和数据一起存储在数据文件中。数据库在未添加索引进行查询的时候默认是进行全文搜索也就是说有多少数据就进行多少次查询然后找到相应的数据就把它们放到结果集中直到全文扫描完毕。索引是物理数据页存储在数据文件中InnoDBibd文件利用数据页(page)存储。索引可以加快检索速度但是同时也会降低增删改操作速度索引维护需要代价。索引涉及的理论知识二分查找法、Hash和BTree。
索引的数据结构
二分查找法
二分查找法也叫作折半查找法它是在有序数组中查找指定数据的搜索算法。它的优点是等值查询、范围查询性能优秀缺点是更新数据、新增数据、删除数据维护成本高。
首先定位left和right两个指针计算(leftright)/2判断除2后索引位置值与目标值的大小比对索引位置值大于目标值就-1right移动如果小于目标值就1left移动 举个例子下面的有序数组有17 个值查找的目标值是7过程如下 第一次查找 第二次查找 第三次查找…第四次查找…
Hash结构
哈希表是键值对的集合通过键(key)即可快速取出对应的值(value)因此哈希表可以快速检索数据接近 O1。为何能够通过 key 快速取出 value 呢 原因在于 哈希算法。通过哈希算法我们可以快速找到 key 对应的 index找到了 index 也就找到了对应的 value。
Hash冲突
哈希算法有个 Hash 冲突 问题也就是说多个不同的 key 最后得到的 index 相同。通常情况下我们常用的解决办法是 链地址法。链地址法就是将哈希冲突数据存放在链表中。就比如 JDK1.8 之前 HashMap 就是通过链地址法来解决哈希冲突的。不过JDK1.8 以后HashMap为了减少链表过长的时候搜索时间过长引入了红黑树。 为了减少 Hash 冲突的发生一个好的哈希函数应该“均匀地”将数据分布在整个可能的哈希值集合中。
既然哈希表这么快为什么 MySQL 没有使用其作为索引的数据结构呢
主要是因为 Hash 索引不支持顺序和范围查询。假如我们要对表中的数据进行排序或者进行范围查询那 Hash 索引可就不行了。并且每次 IO 只能取一个
SELECT * FROM tb1 WHERE id 500;在这种范围查询中直接遍历比 500 小的叶子节点就够了。而 Hash 索引是根据 hash 算法来定位的如果把 1 - 499 的数据每个都进行一次 hash 计算来定位…
B树
说B树之前先说二叉查找树吧
二叉查找树
若它的左子树不为空则左子树上所有节点的值都小于根节点的值若它的右子树不为空则右子树上所有节点的值都大于根节点的值左右子树也分别为二叉查找树。
存在问题
时间复杂度和树高相关。 树有多高就需要检索多少次每个节点的读取都对应一次磁盘 IO 操作。树的高度就等于每次查询数据时磁盘 IO 操作的次数。磁盘每次寻道时间为10ms在表数据量大时查询性能就会很差。1百万的数据量log2n约等于20次磁盘IO时间20*100.2s 平衡二叉树不支持范围查询快速查找范围查询时需要从根节点多次遍历查询效率不高。当二叉查找树是平衡的时候也就是树的每个节点的左右子树深度相差不超过 1 的时候查询的时间复杂度为 O(log2(N))具有比较高的效率。然而当二叉查找树不平衡时例如在最坏情况下有序插入节点树会退化成线性链表也被称为斜树导致查询效率急剧下降时间复杂退化为 ON 因此二叉查找树不适合作为 MySQL 底层索引的数据结构
改造二叉树——B树
MySQL的数据是存储在磁盘文件中的查询处理数据时需要先把磁盘中的数据加载到内存中磁盘IO 操作非常耗时所以我们优化的重点就是尽量减少磁盘 IO 操作。访问二叉树的每个节点就会发生一次IO如果想要减少磁盘IO操作就需要尽量降低树的高度
降低树的高度
假如key为bigint8字节每个节点有两个指针每个指针为4个字节一个节点占用的空间16个字节84*216
因为在MySQL的InnoDB存储引擎一次IO会读取的一页默认一页16K的数据量而二叉树一次IO有效数据量只有16字节空间利用率极低。为了最大化利用一次IO空间一个简单的想法是在每个节点存储多个元素在每个节点尽可能多的存储数据。每个节点可以存储1000个索引16k/161000这样就将二叉树改造成了多叉树通过增加树的叉树将树从高瘦变为矮胖。构建1百万条数据树的高度只需要2层就可以1000*10001百万也就是说只需要2次磁盘IO就可以查询到数据。磁盘IO次数变少了查询数据的效率也就提高了
B树特点
B树的节点中存储着多个元素每个内节点有多个分叉。节点中的元素包含键值和数据节点中的键值从大到小排列。也就是说在所有的节点都储存数据。父节点当中的元素不会出现在子节点中。所有的叶子结点都位于同一层叶节点具有相同的深度叶节点之间没有指针连接。
案例
假如我们查询值等于10的数据。查询路径磁盘块1-磁盘块2-磁盘块5。
第一次磁盘IO将磁盘块1加载到内存中在内存中从头遍历比较1015走左路到磁盘寻址磁盘块2。第二次磁盘IO将磁盘块2加载到内存中在内存中从头遍历比较710到磁盘中寻址定位到磁盘块5。第三次磁盘IO将磁盘块5加载到内存中在内存中从头遍历比较1010找到10取出data如果data存储的行记录取出data查询结束。如果存储的是磁盘地址还需要根据磁盘地址到磁盘中取出数据查询终止。
相比二叉平衡查找树在整个查找过程中虽然数据的比较次数并没有明显减少但是磁盘IO次数会大大减少。同时由于我们的比较是在内存中进行的比较的耗时可以忽略不计。B树的高度一般2至3层就能满足大部分的应用场景所以使用B树构建索引可以很好的提升查询的效率。
继续优化的方向
B树不支持范围查询的快速查找你想想这么一个情况如果我们想要查找10和35之间的数据查找到15之后需要回到根节点重新遍历查找需要从根节点进行多次遍历查询效率有待提高。如果data存储的是行记录行的大小随着列数的增多所占空间会变大。这时一个页中可存储的数据量就会变少树相应就会变高磁盘IO次数就会变大。
改造B树——B树
在B树基础上MySQL在B树的基础上继续改造使用B树构建索引。B树和B树最主要的区别在于非叶子节点是否存储数据的问题
对比
B树非叶子节点和叶子节点都会存储数据。B树只有叶子节点才会存储数据非叶子节点至存储键值。叶子节点之间使用双向指针连接最底层的叶子节点形成了一个双向有序链表 B树的最底层叶子节点包含了所有的索引项。从图上可以看到B树在查找数据的时候由于数据都存放在最底层的叶子节点上所以每次查找都需要检索到叶子节点才能查询到数据。在需要查询数据的情况下每次的磁盘的IO跟树高有直接的关系但是从另一方面来说由于数据都被放到了叶子节点所以放索引的磁盘块锁存放的索引数量是会跟这增加的所以相对于B树来说B树的树高理论上情况下是比B树要矮的。存在索引覆盖查询的情况在索引中数据满足了当前查询语句所需要的全部数据此时只需要找到索引即可立刻返回不需要检索到最底层的叶子节点。
案例分析
等值查询
假如我们查询值等于9的数据。查询路径磁盘块1-磁盘块2-磁盘块6。
第一次磁盘IO将磁盘块1加载到内存中在内存中从头遍历比较915走左路到磁盘寻址磁盘块2。第二次磁盘IO将磁盘块2加载到内存中在内存中从头遍历比较7912到磁盘中寻址定位到磁盘块6。第三次磁盘IO将磁盘块6加载到内存中在内存中从头遍历比较在第三个索引中找到9取出data如果data存储的行记录取出data查询结束。如果存储的是磁盘地址还需要根据磁盘地址到磁盘中取出数据查询终止。这里需要区分的是在InnoDB中Data存储的为行数据而MyIsam中存储的是磁盘地址。
范围查询
假如我们想要查找9和26之间的数据。查找路径是磁盘块1-磁盘块2-磁盘块6-磁盘块7。
首先查找值等于9的数据将值等于9的数据缓存到结果集。这一步和前面等值查询流程一样发生了三次磁盘IO。查找到15之后底层的叶子节点是一个有序列表我们从磁盘块6键值9开始向后遍历筛选所有符合筛选条件的数据。第四次磁盘IO根据磁盘6后继指针到磁盘中寻址定位到磁盘块7将磁盘7加载到内存中在内存中从头遍历比较9252692626将data缓存到结果集。主键具备唯一性后面不会有26的数据不需再向后查找查询终止。将结果集返回给用户
B树可以保证等值和范围查询的快速查找MySQL的索引就采用了B树的数据结构
索引的分类
主键索引、唯一索引、普通索引、全文索引、组合索引、空间索引
主键索引
每个InnoDB表都有一个聚簇索引 聚簇索引使用B树构建叶子节点存储的数据是整行记录。一般情况下聚簇索引等同于主键索引当一个表没有创建主键索引时InnoDB会自动创建一个ROWID字段来构建聚簇索引。InnoDB创建索引的具体规则如下
在表上定义主键PRIMARY KEYInnoDB将主键索引用作聚簇索引。如果表没有定义主键InnoDB会选择第一个不为NULL的唯一索引列用作聚簇索引。如果以上两个都没有InnoDB 会使用一个6 字节长整型的隐式字段 ROWID字段构建聚簇索引。该ROWID字段会在插入新行时自动递增。
除聚簇索引之外的所有索引都称为辅助索引。在中InnoDB辅助索引中的叶子节点存储的数据是该行的主键值都。 在检索时InnoDB使用此主键值在聚簇索引中搜索行记录
案例分析 InnoDB的数据和索引存储在一个文件t_user_innodb.ibd中。InnoDB的数据组织方式是聚簇索引。主键索引的叶子节点会存储数据行辅助索引只会存储主键值。
等值查询select * from user_innodb where id 28;磁盘IO数量3次。 先在主键树中从根节点开始检索将根节点加载到内存比较2875走左路。1次磁盘IO将左子树节点加载到内存中比较162847向下检索。1次磁盘IO检索到叶节点将节点加载到内存中遍历比较162818282828。查找到值等于28的索引项直接可以获取整行数据。将改记录返回给客户端。1次磁盘IO
辅助索引【普通索引】
除聚簇索引之外的所有索引都称为辅助索引InnoDB的辅助索引只会存储主键值而非磁盘地址。以表user_innodb的age列为例age索引的索引结果如下
底层叶子节点的按照ageid的顺序排序先按照age列从小到大排序age列相同时按照id列从小到大排序。使用辅助索引需要检索两遍索引首先检索辅助索引获得主键然后使用主键到主索引中检索获得记录等值查询select * from t_user_innodb where age19;磁盘IO数辅助索引3次获取记录回表3次。根据在辅助索引树中获取的主键id到主键索引树检索数据的过程称为回表查询。
唯一索引
在创建索引时限制索引的字段值必须是唯一的。通过该类型的索引可以比普通索引更快速地查询某条记录当我们给某给字段定义了唯一约束时MySQL为了保证唯一性便会自动给这个字段添加唯一索引而之后再手动给这个字段添加唯一索引便是一些多余操作唯一索引可以是单列也可以是多列最大的所用就是确保写入数据库的数据是唯一值。
主键索引与唯一索引的区别
唯一性约束所在的列允许空值但是主键约束所在的列不允许空值。可以把唯一性约束放在一个或者多个列上这些列或列的组合必须有唯一的。但是唯一性约束所在的列并不是表的主键列。唯一性约束强制在指定的列上创建一个唯一性索引。在默认情况下创建唯一性的非聚簇索引但是也可以指定所创建的索引是聚簇索引。建立主键的目的是让外键来引用.一个表最多只有一个主键但可以有很多唯一键
组合索引 组合索引的数据结构 组合索引的查询过程
select * from abc_innodb where a 13 and b 16 and c 4;组合索引的最左匹配原则
最左前缀匹配原则和联合索引的索引存储结构和检索方式是有关系的。在组合索引树中最底层的叶子节点按照第一列a列从左到右递增排列但是b列和c列是无序的b列只有在a列值相等的情况下小范围内递增有序而c列只能在ab两列相等的情况下小范围内递增有序。就像上面的查询B树会先比较a列来确定下一步应该搜索的方向往左还是往右。如果a列相同再比较b列。但是如果查询条件没有a列B树就不知道第一步应该从哪个节点查起。可以说创建的idx_abc(a,b,c)索引相当于创建了(a)、a,ba,b,c三个索引。使用组合索引查询时mysql会一直向右匹配直至遇到范围查询(、、between、like)就停止匹配
覆盖索引
覆盖索引并不是说是索引结构覆盖索引是一种很常用的优化手段。因为在使用辅助索引的时候我们只可以拿到主键值相当于获取数据还需要再根据主键查询主键索引再获取到数据。
但是在上面abc_innodb表中的组合索引查询时如果我只需要abc字段的那是不是意味着我们查询到组合索引的叶子节点就可以直接返回了而不需要回表。这种情况就是覆盖索引。
执行计划分析
覆盖索引 explain select a,b,c from abc_innodb where a 13 and b14 and c4;未覆盖索引 explain select * from abc_innodb where a 13 and b14 and c4;SQL优化方向之一——避免回表
在InnoDB的存储引擎中使用辅助索引查询的时候因为辅助索引叶子节点保存的数据不是当前记录的数据而是当前记录的主键索引索引如果需要获取当前记录完整数据就必然需要根据主键值从主键索引继续查询。这个过程我们成位回表。想想回表必然是会消耗性能影响性能
案例分析
现有User表id(PK),name(key),sex,address,hobby…如果在一个场景下select id,name,sex from user where name zhangsan;这个语句在业务上频繁使用到而user表的其他字段使用频率远低于它在这种情况下如果我们在建立 name 字段的索引的时候不是使用单一索引而是使用联合索引namesex这样的话再执行这个查询语句是不是根据辅助索引查询到的结果就可以获取当前语句的完整数据。这样就可以有效地避免了回表再获取sex的数据。
这里就是一个典型的使用覆盖索引的优化策略减少回表的情况。
联合索引
在建立索引的时候尽量在多个单列索引上判断下是否可以使用联合索引。联合索引的使用不仅可以节省空间还可以更容易的使用到索引覆盖。试想一下索引的字段越多是不是更容易满足查询需要返回的数据呢。比如联合索引a_b_c是不是等于有了索引aa_ba_b_c三个索引这样是不是节省了空间当然节省的空间并不是三倍于aa_ba_b_c三个索引因为索引树的数据没变但是索引data字段的数据确实真实的节省了
联合索引的创建原则
在创建联合索引的时候因该把频繁使用的列、区分度高的列放在前面频繁使用代表索引利用率高区分度高代表筛选粒度大这些都是在索引创建的需要考虑到的优化场景也可以在常需要作为查询返回的字段上增加到联合索引中如果在联合索引上增加一个字段而使用到了覆盖索引那我建议这种情况下使用联合索引
联合索引的使用
考虑当前是否已经存在多个可以合并的单列索引如果有那么将当前多个单列索引创建为一个联合索引。当前索引存在频繁使用作为返回字段的列这个时候就可以考虑当前列是否可以加入到当前已经存在索引上使其查询语句可以使用到覆盖索引。
索引的设计原则
按照下列标准选择建立索引的列
频繁搜索的列经常用作查询选择的列经常排序、分组的列经常用作连接的列主键/外键
不要使用下面的列创建索引
仅包含几个不同值的列表中仅包含几行
索引失效的情况
模糊查询like使用“name%”索引可用“%name”索引失效组合索引包含从左到右的字段使用索引不包含左边的字段索引失效组合索引范围搜索范围搜索后的字段不使用索引条件字段数据类型不匹配导致索引失效联合查询时字符集不匹配导致索引失效不等于导致索引失效不等于的情况包括! 、、、not inor前后条件都包含索引则走索引or前后有一个不包含索引索引失效添加索引的字段上使用函数或者计算导致索引失效函数包括ABS,UPPER,DATE,DAY,YEAR等
搜集来的SQL优化经验(欢迎补充)
应尽量避免在 where 子句中使用!或操作符否则将引擎放弃使用索引而进行全表扫描。对查询进行优化应尽量避免全表扫描首先应考虑在 where 及 order by 涉及的列上建立索引。应尽量避免在 where 子句中对字段进行 null 值判断否则将导致引擎放弃使用索引而进行全表扫描尽量避免在 where 子句中使用 or 来连接条件否则将导致引擎放弃使用索引而进行全表扫描应尽量避免在 where 子句中对字段进行表达式操作这将导致引擎放弃使用索引而进行全表扫描应尽量避免在where子句中对字段进行函数操作这将导致引擎放弃使用索引而进行全表扫描不要在 查询条件的字段上进行函数、算术运算或其他表达式运算否则系统将可能无法正确使用索引在使用索引字段作为条件时如果该索引是复合索引那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引否则该索引将不会被使 用并且应尽可能的让字段顺序与索引顺序相一致很多时候用 exists 代替 in 是一个好的选择尽量避免大事务操作提高系统并发能力尽量避免向客户端返回大数据量若数据量过大应该考虑相应需求是否合理索引并不是越多越好索引固然可以提高相应的 select 的效率但同时也降低了 insert 及 update 的效率因为 insert 或 update 时有可能会重建索引所以怎样建索引需要慎重考虑视具体情况而定。一个表的索引数最好不要超过6个若太多则应考虑一些不常使用到的列上建的索引是否有 必要where条件中字段与参数的类型要保持一致否则会导致索引失效根据业务数据发生频率定期重新生成或重新组织索引进行碎片整理。WHERE子句中有多个条件表达式时包含索引列的表达式应置于其他条件表达式之前索引应该尽量小在字节数小的列上建立索引查询时减少使用*返回全部列不要返回不需要的列避免在ORDER BY子句中使用表达式。
