湛江手机建站模板,官网建设的意义,昆明做企业网站多少钱,网站设计中 查询怎么做1.1数据库架构#xff1a;如何设计一个关系型数据库?存储模块#xff08;文件系统#xff09;用块或者页作为存储单位 程序实例#xff1a;存储管理缓存机制 不宜过大#xff0c;要有淘汰机制SQL解析日志管理权限划分容灾机制索引管理锁管理1.2 索引为什么要使用索引如何设计一个关系型数据库?存储模块文件系统用块或者页作为存储单位 程序实例存储管理缓存机制 不宜过大要有淘汰机制SQL解析日志管理权限划分容灾机制索引管理锁管理1.2 索引为什么要使用索引全表扫描数据库存储用块或者页存储全表扫描时需要将整个表加载到缓存中当数据量很小的时候缓存可以一次加载所有数据量全表扫描就会比较快而当数据量很大需要缓存多次加载就会很慢这个时候就要用到索引。索引来源于字典将关键信息集中起来快速查找数据因为索引能够避免全表扫描查找数据提升检索效率什么样的信息能成为索引能把该记录限定在一定查找范围中的字段主键唯一键以及普通键 能够让数据具备一定区分度的字段索引的数据结构主流是BTree还有Hash结构 bitMap索引其中MySql不支持BitMap索引同时基于MyISAM 或 InnoDB的MySql不支持Hash二叉查找树有左右子树对于树中的节点值X 他的左子树的任意节点值小于X右字数任意节点值大于X时间复杂度O(logn)但在插入过程中可能会产生以下问题 导致IO次数变多时间复杂度变为O(n)比全表扫描都要慢得多B-Tree 通过合并上移下移来保证特征特征根节点至少包括两个孩子树中每个节点最多含有M个孩子m2除根节点和叶节点外其他每个节点至少有ceil(m/2)个孩子所有终端叶子节点都位于同一层即叶子节点高度一致假设每个非终端结点中包含N个关键字信息其中1)Kii1...n为关键字且关键字按顺序升序排序K(i-1)Ki, 2)关键字的个数N必须满足{cellm/2-1} n m-13非叶子结点的指针P[1],P[2] P[3]... P[M]其中P[1]指向关键字小于K[1]的子树P[m]指向关键字大于K[m-1]的子树其他P[i]指向关键字属于(K[i-1],k[i])的子树B-Tree B-Tree 的变体非叶子节点的子树指针与关键字个数相同非叶子节点得子树指针P[I]指向关键字【K[i],K[i1]】的子树非叶子节点仅用来做索引数据保存在叶子节点中即非叶子节点值和叶子节点值可能相同(保证树更矮)所有叶子节点均有一个链指针指向下一个叶子节点并按大小顺序链接可以方便横向跨子树进行统计比如n10 会直接统计10以后的叶子节点而不统计10以前的结论BTree更适合用于做存储索引1B树的磁盘读写代价更低B树的内部结构没有存储指向关键字具体信息的指针所容纳的关键字多一次IO可读取关键字数量更多。2B树的查询效率更加稳定由于非终端点并不是最终指向文件内容的结点而只是叶子结点中关键字的索引。所以任何关键字的查找必须走一条从根结点到叶子结点的路。所有关键字查询的路径长度相同导致每一个数据的查询效率相当。3B树更有利于数据库的扫描只需要遍历节点就可以完成对整个数据库数据的扫描查找Hash索引理论上高于BTree不稳定不支持线性查询存放通过HASH算法计算出来的Hash值和行指针优点根据Hash的运算可以一次查找到数据所在的链表缺点仅仅能满足“”“in”,不能使用范围查询 无法被用来避免数据的排序操作(HASH索引比较的是Hash算法计算后的值不能保证和原有顺序大小一致组合索引中Hash是用组合全部索引值的方式进行查找的无法通过组合索引中的部分索引键进行查询无法避免表扫描不同索引键可能存在相同索引值需要通过访问实际数据进行比较遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高有可能出现极端情况即很多不同数据计算出的索引值都一样变成线性存储结构BitMap位图索引存储时按照状态分开每个数据是否是这个值按位存储只适用于某个字段的值只有某种几个的情况锁力度较大不适合高并发联机系统适用于并发低但是统计多的。密集索引和稀疏索引的区别密集索引文件中的每个搜索码值都对应一个索引值叶子节点不仅仅保存键值 并且保存了位于同一行记录里的其他列的信息由于密集索引决定了一个表的物理排序一个表只能有一个物理排列顺序故只能创建一个密集索引稀疏索引文件只为索引码的某些值建立索引项叶子节点仅保存键位信息和该行指针地址或主键定位到叶子后通过地址或主键寻找存储引擎MyISAM 索引和数据是分开存储的 更适合大量的查询操作InnoDB 索引和数据是一起存储的 支持事务 支持行级锁 set update更快有且仅有一个密集索引如果一个主键被定义该主键作为密集索引如果没有主键被定义该表的第一个唯一非空索引作为密集索引若不满足以上条件innodb会在内部生成一个隐藏主键非主键索引存储箱关键为何其对应的主键值两次查找查找次级索引自身再查找主键索引或者物理索引索引如何定位并优化慢SQL该题目比较看经验1根据慢日志定位慢查询SQL数据定义语言不会进入慢查询慢日志记录执行比较慢的SQL 执行命令Show variables like‘ %quer%’从上到下分别是SQL执行时间即超过多少时间算执行较慢会记录超过这个时间的慢查询慢日志开关set global slow_query_log on慢日志路径set global long_query_time1(此句变量需要重连数据库后才生效)使用命令修改后再重启数据库服务后会回复默认可以在配置文件中修改2使用explain等工具分析sql 显示了mysql如何使用索引来处理select语句以及连接表。可以帮助选择更好的索引和写出更优化的查询语句typemysql找到数据行的方式 最优—最差(红字需要优化)extra中出现以下两项表示mysql没有使用索引3修改sql减少使用*减少使用模糊查询或者使SQL尽量走索引like‘%赵%’这种形式不会走索引但是like‘赵%会走索引’、强制索引等mysql的查询优化器会尽可能的使用索引并且使用最严格的索引消除尽可能多的数据行最终目标是提交SQL语句查询数据行而不是排除数据行sql优化器试图排除语句的目的在于排除数据行的素的越快找到与条件匹配的数据行就越快sql优化器会判断密集索引的叶子结点会带有数据效率可能会低故选择稀疏索引有时候不一定会最优。联合索引的最左匹配原则的成因最左匹配原则对于索引AB有where A1会走该索引where A1and B1也会走该索引where B1则不会走该索引mysql会一直向右匹配直到遇到范围查询,,between,like就停止匹配比如a3 and b2 and c5 and d6,如果建立abcd顺序的索引d是用不到索引的如果建立abdc顺序的索引abdc的索引都可以用到abd 的顺序可以任意调整和in可以乱序比如a1 and b2 and c3 建立abc索引可以任意顺序musql的查询优化器会帮你优化成索引可以识别的形式。mysql创建联合索引会对复合索引的第一个索引字段排序在此基础上再对第二个字段排序其中第一个字段是有序的第二个字段是无序的、所以用第一个字段是可以用到索引的单独用第二个字段是用不到索引的从col3,col2,col1做索引会做出下图所示的索引单使用col2无法走索引。索引是建立的越多越好吗1否定的物极必反数据量小的表不需要建立索引建立会增加额外的索引开销2数据变更需要维护索引更多的索引意味着更多的维护成本3更多的索引意味着需要更多的空间。
