查找网站建设历史记录,基金管理公司司网站建设要求,哈尔滨哪里做网站好,中国建设银行网上银行官方网站文章目录MySQL体系结构MySQL存储结构#xff08;以InnoDB为例#xff09;MySQL执行流程#xff08;以InnoDB为例#xff09;1. 数据写入原理2. 数据查询原理MySQL存储引擎1. 为什么需要不同的存储引擎2. 如何为数据指定不同的存储引擎#xff0c;数据粒度又是多少3. MySQL…
文章目录MySQL体系结构MySQL存储结构以InnoDB为例MySQL执行流程以InnoDB为例1. 数据写入原理2. 数据查询原理MySQL存储引擎1. 为什么需要不同的存储引擎2. 如何为数据指定不同的存储引擎数据粒度又是多少3. MySQL支持哪些存储引擎4. MySQL支持的存储引擎各自有什么特性索引1. 索引的概念2. 为什么要使用索引a. 没有索引的情况下访问数据b. 使用平衡二叉树索引3. InnoDB索引底层为什么选择BTreea. 二叉树b. 二叉平衡树解决二叉树线性链表的问题c. B树解决了平衡二叉树的缺点d. B Tree4. 什么时候不要用索引什么时候要用索引a. 什么场景不要用索引b. 什么场景用索引5. 聚簇索引与非聚簇索引6. 辅助索引7. 全文索引1. 概念2. 实现3. 使用8. 索引失效8.1 最佳左前缀法则8.2 不要再索引列上做任何操作8.3 存储引擎不能使用索引中范围条件右边的列8.4 尽量使用覆盖索引8.5 like以通配符(%)开头的索引会失效变成全表扫描参考文章学习Mysql这一篇就够了参考文章1MySQL面试题目参考文章2MySQL面试题目参考文章MySQL数据库学习宝典参考文章Mysql存储引擎参考文章MySQL 索引底层选择BTree参考文章全文索引参考文章1MyISAM 和 InnoDB介绍参考文章2MyISAM 和 InnoDB区别参考文章索引失效参考文章探索MySQL是否走索引(一)——范围查询一定走索引吗工具提供一个很好用的国外数据结构模拟网站参考B站视频总体概括参考B站视频详细讲解MySQL体系结构 Client Connectors接入方程序员通过Client ConnectorsJDBC、ODBC与MySQL打交道 Connection Pool连接池管理缓冲用户连接、用户名、密码、权限校验等需要缓存的需求 SQL InterfaceSQL接口接受用户的SQL命令并且返回用户需要查询的结果 Parser解析器验证和解析SQL命令 Optimizer查询优化器在查询之前会对SQL语句进行优化 Cache和Buffer缓存区如果查询缓存命中查询语句就可以直接去查询缓存中取数据 pluggable storage Engines插件式存储引擎MySQL与文件系统打交道 File System文件系统数据、日志redoundo、索引、错误日志、查询记录、慢查询等 MySQL存储结构以InnoDB为例 1. InnoDB创建数据表 InnoDB创建数据表会将数据表分为表结构和表内容存储 表结构存储在.frm文件中表内容以前存储在系统表空间中现在默认存储在独立表空间.ibd 2. ibd文件 .ibd文件分为叶子节点段和非叶子节点段和回滚段 叶子节点段B树的叶子节点非叶子节点段B树的非叶子节点回滚段为存储一些特殊的数据而定义的段 如果没有设置索引那么所有的节点就不会区分成叶子节点段和非叶子节点段
3. 段 段不对应表空间的某一连续物理区域而是一个逻辑概念它是由表空间这个物理空间中的若干个零散页和一系列完整的区组成。 叶子节点段存放在区非叶子节点段存放在页 4. 区 为什么要有区 上面提到页与页之间通过双向链表进行连接若页与页间隔太远的话而随机I/O【定义读写操作时间连续但访问地址不连续随机分布在磁盘的地址空间中】是非常慢的。因此为了尽量让链表上相邻的页物理上也相邻引入了区。一个区在物理位置上就是连续的64个页。数据量大的时候可一次性分配多个区不必再按照页为单位进行分配了。消除很多的随机I/O。 碎片区 对于数据量太小的表不足以填满整个区的情况造成空间的浪费。因此针对这种情况InnoDB提出了碎片区的概念。碎片区中的页并不都是为了存储同一个段而存在的比如有的页用于段A有的用于段B有的页甚至哪个段也不属于。碎片区直属于表空间并不属于任何一个段。 综上为段分配的策略是 起初向表中插入数据的时候段是从某个碎片区以单个页面来分配存储空间的。 当某个段已经占用了32个碎片区页面之后就会申请以完整的区为单位来分配存储空间。 5. 数据页
1. 页头File Header
2. 页尾File Trailer 页头和页尾的校验和相对应由于我们的操作系统传输单元数据块通常是4KB一个页是4个数据块。如果碰到我断电的情况那么一些不走运的页可能只传输了一两个数据块并没有完整传输这种不完整就需要用页头和页尾的校验和通过一些验证算法进行验证。默认使用crc32.
3. 数据行 MySQL说数据行有4种格式DYNAMICREDUNDANTCOMPACTCOMPRESSED默认为DYNAMIC可以通过innodb_default_row_format进行查询和变更。 真实数据区 MySQL说第一个就是主键位置如果是复合主键那也会依序排在这里。如果没有主键就会优先用一个NOT NULL、UNIQUE的列作为主键如果这个都没有的话我的innoDB就会构建一个6B的DB_ROW_ID字段存储在这里。 MySQL说紧接着是6B的事务ID字段DB_TX_ID7B的回滚指针字段DB_ROLL_PTR MySQL说继续向右就记录除了主键和值为null的列之外的真实数据了因为值为null的列会用其他方法表示目的就是为了节省空间。 额外信息区 下一行下一个数据行真实数据的地址。 行类型0代表普通数据行1代表索引目录行2是最小行3是最大行。 位置用13bit的heap_no来标记该行在整个页的位置 组行数如果这个行是分组的最后一行则在这里标记该组的行数 每层最小不懂但是在后面视频有详细讲解 删除标记在删除数据行时并不会直接移除而是修改这个标记。同时将这行的next_record指向一个称为垃圾链表的地方这个链表会用于事务回滚。 NULL值列表用来记录值为NULL的列根据列的个数来开辟合适的长度。 可变字段长度列表记录了数据行里所有变成字段的实际长度。 4. 最大行和最小行 每当我体内创建一个新页都会自动分配两个行。一个行类型为2的最小行Infimum固定在0号heap_no位置。一个行类型为3的最大行Supremum固定在1号heap_no位置。这两个行并不存储真实的信息而是作为数据行链表的头和尾。 5. 页目录 当程序猿大哥想要查询数据时需要沿着链表顺序一个个比对查找这样显然是不行的。所以我进化出了一个页目录的器官它会将页内包括头行、尾行在内的所有行进行分组约定头行为单独为一组其他每个组最多有8条数据。同时把每个组最后一个行在页中的地址按照主键从小到大的顺序记录到页中这个区域叫做页目录页目录的每一个位置称为一个槽每个槽对应一个分组。 在页目录中根据二分法查找到组再在组里面遍历八个数据行即可找到。
MySQL执行流程以InnoDB为例
1. 数据写入原理 省流 执行器执行写入操作把数据存储在Buffer Pool把操作信息存储在redo log Buffer并且还把相关信息写入undo log和bin log在一定时间之后redo log Buffer将操作信息写入redo log再过一段时间之后Buffer Pool会把数据写入到磁盘文件中 MySQL深深地知道一切的逻辑处理和写入都只操作内存中的数据这个内存缓存区被称为Buffer Pool。InnoDB会把它需要写入的数据插入或更新到Buffer Pool中。当然为了能够让已经写入的数据支持回滚就需要在这之前将数据的旧值记录到另一个地方那就是Undo Log文件。将数据写到Buffer Pool内存之后InnoDB就会让它的小线程们在一些特定的时机从内存中把需要更新写入的数据读出来同时写入磁盘。一旦MySQL断电或者服务挂掉睡着那么处在内存区Buffer Pool中的数据就会随着内存失效而永久丢失。为了应对这种突发情况我的大脑InnoDB研发了一套redo Log体系。这套体系在数据进入Buffer Pool之后会将“更新写入信息”放入内存的另一个区域Redo Log Buffer然而光写入内存也无法解决刚才的问题为了保险起见还是要把它刷到磁盘中。我的InnoDB提供了多种Redo Log的刷盘机制。 # 将“更新写入信息”写入到操作系统Page cache和Redo Log Buffer并立刻刷新到磁盘中
innoDB_flush_log_at_trx_commit 1;# 将“更新写入信息”写入到Redo Log Buffer每隔一秒钟才进行系统内存放入和刷盘操作
innoDB_flush_log_at_trx_commit 0;# 将“更新写入信息”写入到操作系统Page cache和Redo Log Buffer每隔一秒钟才进行系统内存放入和刷盘操作
innoDB_flush_log_at_trx_commit 2;上述一系列操作都集成在MySQL内存我的程序猿大哥是不能直接操作的当我的程序猿大哥执行了错误的SQL语句就不能回复如初的。为了帮程序猿大哥解决这个问题我进化出了Bin Log文件。它可以给程序猿大哥提供变更历史查询、数据库备份和恢复、主从复制等功能我会在Redo日志写入的同时对Bin Log进行刷盘操作在Bin Log刷盘成功后我会告知Redo日志事务“已提交”这个信息Redo日志也会打入commit标志。这样一次数据写入的流程就完成啦。 提问既然为了防止断电redolog会立刻写入硬盘那为什么不干脆直接将buffer pool中的数据写入硬盘呢 一个事务可能包含多个语句数据不太可能都连续从bufferpool刷盘就需要大量磁盘io性能很低。而redolog日志本身不记录真实数据而是一些偏移量、页号之类的很小刷盘机制也都是顺序io所以效率高。
2. 数据查询原理 省流 查询缓存命中则返回结果不过5.8之后就把缓存撤销了解析器词法分析、语法分析优化器多次执行同一个SQL则提交一次模板随后只需要多次提交参数 查询缓存 查询缓存在5.7版本里是默认关闭的可以通过query cache type参数查看和设置。到了MySQL8.0整个缓存都删除了为什么官方都不建议使用这个看上去还不错的功能呢首先每次进来查询都要经过查询缓存加上每次查询条件不同导致命中率低那就非常消耗性能了。其次在没有命中缓存那就要创建新的缓存也会造成一定的消耗。最后为了保证数据一致会添加表级锁当表内数据更新后需要将这个表对应的缓存均设为失效又是一波不小的消耗。综上所述很多情况下“查询缓存”并不能为我们带来实质的效率提升。最终原因就是缓存命中率低。 # 查询缓存开关
SHOW VARIABLES LIKE query_cache_type;# 设置缓存开关
SET query_cache_type 0;#query_cache_type2时代表按需使用用SQL_CACHE对某表开启缓存
SELECT SQL_CACHE * FROM test;SQL解析器 词法分析通过sql/sql_lex.cc代码将SQL语句切分为各种Token词通过其中一个定义了各类关键字、操作符的数组symbols[]进行关键字、非关键字的标记。 语法分析使用bison这个语法分析器通过sql/sql_yacc.cc规则代码将语句解析为一个语法树。 预处理器 MySQL说预处理器将请求进行拆分先提交SQL模板语句然后再提交参数并进行执行。通过这种方式对于重复执行的语句来说可以提交并处理一次模板即可然后不断的提交参数就可以实现多次执行从而提高执行的效率。 MySQL存储引擎
1. 为什么需要不同的存储引擎
关系型数据库就是用来存储各种数据信息的。根据不同业务场景比如说有的表简单有的表复杂有的表根本不用来存储任何长期的数据有的表需要查询非常快。在我们实际的业务开发中可能需要用到各种各样的表不同的表也意味着存储不同类型的数据数据的处理上也就会存在着差异。针对 MySQL 来说它提供了很多类型的存储引擎来供我们选择我们可以根据业务需求来选择不同的存储引擎最大程度的发挥 MySQL 的强大之处。
2. 如何为数据指定不同的存储引擎数据粒度又是多少
一个库中的每一个表都可以指定选择存储引擎即数据粒度就是一个数据表
3. MySQL支持哪些存储引擎
我们可以使用命令show engines;来查看 MySQL 支持哪些存储引擎如下图所示。我们可以看到 MySQL 默认使用的存储引擎是 InnoDB。
4. MySQL支持的存储引擎各自有什么特性 1. CSV存储引擎 特点没有索引没有自增列必须为NOT NULL 缺点不适用于大表或者数据查询、排序等处理操作 2. Archive存储引擎 特点只支持insert 和 select 两种操作、只支持自增ID列建立索引、支持行级锁、不支持事务 缺点不适用于对数据的处理操作 优点数据占用磁盘少 3. Memory存储引擎 特点实际的应用场景中用到的很少但是它与优化器查询有很大的关系。对于精确查询非常高效、存储在内存、支持Hash索引、B- Tree索引 优点 数据都是存储在内存中IO效率比其他引擎高很多 缺点保证不了持久性不支持大数据存储类型 4. InnoDB存储引擎 特点支持事务、支持外键、支持行级锁、支持表级锁、必须有主键、5.8后支持全文索引、辅助索引和主键索引之间存在层级关系 5. MyISAM存储引擎 特点不支持事务、不支持外键、不支持行级锁、支持表级锁、主键不是必须的、支持全文索引、MyISAM辅助索引和主键索引则是平级关系 索引
1. 索引的概念
索引是一种利用某种规则的数据结构与实际数据的关系加快数据查找的功能索引数据节点中有着实际文件的位置因为索引是根据特定的规则和算法构建的,在查找的时候遵循索引的规则可以快速查找到对应数据的节点从而达到快速查找数据的效果说白了就是把每个数据都放在不同的盒子里再根据数据结构和算法快速找到指定的盒子。
2. 为什么要使用索引
a. 没有索引的情况下访问数据 b. 使用平衡二叉树索引 总结你的数据并不是乱放的而是根据一定的规则放置的。这样才能按一定的规则快速查找到无需遍历整个数据表。
3. InnoDB索引底层为什么选择BTree
接下来我们将从 二叉树、平衡二叉树、B-Tree、BTree 等方面来分析了解 MySQL 索引底层为什么会选择 BTree 。 a. 二叉树
场景一可以避免遍历整张数据表 场景二我们会发现这种链表样式的二叉树检索和全表扫描没有太大的区别 总结 我们发现二叉树最终的检索效率其实是取决于数据的分布情况。如果数据分布均匀还是能提高一定的查询效率的数据如果分布不均匀用二叉树来做索引的话还是存在着一定的不足性。 b. 二叉平衡树解决二叉树线性链表的问题
二叉平衡树检索数据的过程 1. 磁盘块分析一个节点就是一个磁盘块一个磁盘块保存有如下内容 关键字比如说我们创建 ID 为索引ID15那么15 就是一个关键字。数据区指向真正数据存储的磁盘位置(内存地址)通过数据区去加载数据。一般情况下为了节省空间数据区是不会保存数据的而是指向磁盘的一个位置然后去磁盘把内容加载过来达到快速检索的目的。子节点引用指向子节点的引用。 2. 检索数据的过程 首先将磁盘块1加载到内存中获取到根节点数据。让 12 和 15 这个关键字进行比对1215会基于 P1 (P1、P2是指向下一个磁盘块的地址)通过顺序 IO 快速的去加载磁盘块 2 中的内容12 和 10 继续比对12 10走P2 加载磁盘块 5然后命中 12。命中后获取到数据区地址再次加载磁盘中的数据。 平衡二叉树还存在哪些缺陷、问题 太深了比如说我们检索 12需要做 3 次 IO 操作获得数据。(3层高度只能存放 7 个数据)如果我们数据达到亿级别那么这棵二叉树会有多大我们无法想象。然而IO 操作是很费时的显然无法满足条件。太小了通过N多次 IO 操作后拿到的数据太少了没能很好的利用数据交换特性没有很好的利用磁盘IO的预读能力 c. B树解决了平衡二叉树的缺点
B-Tree相比平衡二叉树优势在哪 相比平衡二叉树(共3层能存储7个数据)B-Tree同样是三层这个存储的数据就很多了翻倍形式的增长。B-Tree 为多路平衡树如果我们定成1000路同样三层 IO 操作它能检索的数据就更多了。(形象理解平衡二叉树就是瘦高B-Tree就是矮胖) 在这种层度上多路平衡查找树完胜平衡二叉树。 相比平衡二叉树(共3层能存储7个数据)B-Tree同样是三层这个存储的数据就很多了翻倍形式的增长。B-Tree 为多路平衡树如果我们定成1000路同样三层 IO 操作它能检索的数据就更多了。(形象理解平衡二叉树就是瘦高B-Tree就是矮胖) 在这种层度上多路平衡查找树完胜平衡二叉树。还记得操作系统是一次性交互 4K 数据MySQL 一个节点 是 16K。如果我们以 ID 作为索引一个索引数据索引数据 4byte 其他冗余数据大约 4byte(数据区子类引用等数据)共 8byte (字节)的话16K 能存放 16*1024/82048 个索引那就能存放204812049路。这 2049 路能存放多少数据就多到数不清了吧。(Tips加索引时我们之前只知道数据类型、字段定义越精简越好为什么呢因为定义数据类型、字段时越越精简占用空间越小一个磁盘块能存放的数据就更多。)B-Tree如何检索数据 我们以检索 32 为例流程如下 首先将磁盘块1加载到内存中获取到根节点数据。然后让 32 和 17、35进行对比发现 17 32 35。此时会基于 P2 (P1、P2、P3是指向下一个磁盘块的地址)通过顺序 IO 快速的去加载磁盘块 3 中的内容(Tips检索数据XX17 走P1 17X35 走P2 X35 走P3) 32 29走P3加载磁盘块 10 中的内容然后命中 32。命中后获取到数据区地址再根据地址来加载磁盘中的数据。 B Tree缺点 B-Tree查询单个值效率并不低但是如果要搜索大于10小于15这样一个范围或者要遍历所有数据那么B树就力不从心了。B- Tree中要查找的数据离根节点近查找的快离根节点远查找的慢。这样不稳定的搜索时间并不利于我为程序猿大哥们做执行成本的估算。B- Tree虽然已经降低了树的高度但是仍然有下降空间 d. B Tree
B树如何检索数据呢 根据上图我们以检索 28 为例流程如下 在根节点我们就发现已经有 28 了。 B树则会直接停止向下检索。B树则会继续向下检索【因为 B树枝节点不存储数据数据都是保存在叶子节点】 然后他会继续向下去检索直到走到叶子节点然后命中 28。命中后它才会获取到数据区地址再根据地址来加载磁盘中的数据。 MySQL 为什么选择 BTree 呢 1.扫库、扫表能力更强B-Tree 扫库扫表会扫每一个枝节点最后再扫子节点基本都得扫一遍。BTree只扫子节点即可。2.磁盘读写能力更强BTree 枝节点不保存数据只保存关键字和子节点引用。从理论上来将能保存的关键字就更多能加载的关键字就更多。从理论上来讲磁盘的读写能力就更强3.查询效率更加稳定查询数据比如使用 B-Tree 第一次查询比较幸运第二层就出来了并返回用了0.2s。B树是平衡树此时如果又新插入一些数据导致刚刚查询的数据到达了底层那么此时第二次查询。时间就要大于0.2s了。即节点数据节点的深度是会变化的会随着数据的插入、删除而改变从而导致查询时间有所不稳定数据更多的话这个时间就会比第一次差距更大了BTree查询都会去叶子节点查找数据相对B-Tree来说就更加稳定 4. 什么时候不要用索引什么时候要用索引
a. 什么场景不要用索引 数据更新性能比查询性能要求要高的情况下不要使用索引因为数据的更新的同时索引也要进行维护和更新加了索引查询快但更新就会慢; 不要盲目的给表建太多索引因为索引本身的存储也要占用存储空间一旦更新操作频繁反而降低新性能; 不要给不经常使用的列建索引不怎么查询还建索引干嘛; 不要给高重复值的列建索引索引本身就是为了提高查询速度然而数据值高度重复数据区别性不高索引起不了效果比如说性别; 不要给img,tex.bit数据类型使用索引因为这种字段一般使用很少数据量太大;
b. 什么场景用索引 经常要用于查询的列 where id?; 经常要用于排序(order by)分组(group by)的列因为索引已经排好序了; 有值唯一性限制的列比如说主键、用户名
5. 聚簇索引与非聚簇索引
InnoDB使用聚簇索引索引节点包含数据 MyISAM使用非聚簇索引索引节点与数据分开 聚簇索引与非聚簇索引的合作查询 如果通过非聚簇索引查询首先会在这棵“非聚簇索引”树种快速找到叶子结点叶子结点中有聚簇索引的key拿着这个key再去聚簇索引中查询一遍就可以拿到真实数据了。以下是合作查询流程 6. 辅助索引 省流因为数据存放在聚簇索引中所以辅助索引找到key再根据key聚簇索引中找到数据 省流反正非聚簇索引找到key的数据地址也要到其他文件找数据倒不如辅助索引自己去找数据 7. 全文索引
1. 概念 通过数值比较、范围过滤等就可以完成绝大多数我们需要的查询但是如果希望通过关键字的匹配来进行查询过滤那么就需要基于相似度的查询而不是原来的精确数值比较。全文索引就是为这种场景设计的。 你可能会说用 like % 就可以实现模糊匹配了为什么还要全文索引like % 在文本比较少时是合适的但是对于大量的文本数据检索是不可想象的。全文索引在大量的数据面前能比 like % 快 N 倍速度不是一个数量级但是全文索引可能存在精度问题。 2. 实现
全文索引通常用倒排索引来实现倒排索引和BTree一样是一种索引结构它在辅助表中存储了单词与单词自身在一个或多个文档中所在位置之间的映射其拥有两种表现形式 inverted file idnex{单词单词文档所在ID}full inverted index{单词单词文档所在ID具体文档中的位置} 例如有如下表 A
用 inverted file idnex 方式存储内容如下所示
用 full inverted index 方式存储内容如下所示 相比之下full inverted index会占用更多空间但能更好的定位数据并扩充其他一些搜索特性。InnoDB全文检索采用full inverted index方式。
3. 使用
3.1 创建全文索引
# 1.创建表时创建全文索引
create table fulltext_test (id int(11) NOT NULL AUTO_INCREMENT,content text NOT NULL,tag varchar(255),PRIMARY KEY (id),FULLTEXT KEY content_tag_fulltext(content,tag) // 创建联合全文索引列
) ENGINEMyISAM DEFAULT CHARSETutf8;# 2.在已存在的表上创建全文索引
create fulltext index content_tag_fulltexton fulltext_test(content,tag);# 3.通过 SQL 语句 ALTER TABLE 创建全文索引
alter table fulltext_testadd fulltext index content_tag_fulltext(content,tag);3.2 删除全文索引
# 1. 直接使用 DROP INDEX 删除全文索引
drop index content_tag_fulltexton fulltext_test;# 2. 通过 SQL 语句 ALTER TABLE 删除全文索引
alter table fulltext_testdrop index content_tag_fulltext;3.3 使用全文索引 和常用的模糊匹配使用 like % 不同全文索引有自己的语法格式使用 match 和 against 关键字比如
select * from fulltext_test where match(content,tag) against(xxx xxx);8. 索引失效
准备 建立员工记录表staffsidnameageposadd_time并且给表中name、age、pos字段添加索引注意三个在字段的顺序
alter table staff
add index idx_staffs_nameAgePos(name,age,pos)8.1 最佳左前缀法则
1. 概念 指的是查询从索引的最左前列开始并且不跳过索引中的列
# 情况1name字段索引一直被使用
SELECT * FROM staffs WHERE NAME ‘July’;
SELECT * FROM staffs WHERE NAME ‘July’ and age 25;
SELECT * FROM staffs WHERE NAME ‘July’ and age 25 and pos dev;
SELECT * FROM staffs WHERE NAME ‘July’ and pos dev and age 25;# 情况2当不查找name而去查找后面age和pos字段时索引失效
SELECT * FROM staffs WHERE age 25 and pos dev;# 情况3查找了name字段和pos字段而缺少了中间的age字段字段pos的索引失效只用到了name字段索引
SELECT * FROM staffs WHERE NAME ‘July’ and pos dev;8.2 不要再索引列上做任何操作
不要在索引列上左任何操作计算函数自动or手动类型转换否则会导致索引失效转换为全表扫描。 下图可以看到没用left函数之前是一个正常的索引引用使用函数之后索引失效了。 典型问题字符串不加单引号会导致索引失效 name字段为varchar类型假设有一行name‘2000’这时如果用name2000条件查找也会有结果 用EXPLAIN分析 可以看出用name2000作为判断条件不加单引号导致索引失效了为什么
因为name2000也可以查出来是因为MySQL在底层做了一次类型转换把整形2000转换成了字符串’2000’所以索引失效。
8.3 存储引擎不能使用索引中范围条件右边的列
省流 并不是给一个列建立了索引对这个列进行范围查询的时候就会走索引他是有一个比例值的。比例值会随着版本、服务器、IO、数据量、数据重复情况而不同。MySQL根据比值来选择索引或全表扫描 提出问题 第一个查询全等值匹配没问题 第二个查询中间使用了范围条件结果导致manger的索引失效 但实际上where的范围条件判断后面是否走索引还需要做其他判断总的来说where的范围条件判断后面不一定走索引
范围查询一定走索引吗 现在就范围查询是否走索引这个问题讨论后面再总结范围查询后面的字段走不走索引。
1. MySQL5.5版本 准备数据 测试数据 分析数据 user_age 8 比值为35042/210463 0.1664走索引 user_age 8比值为(35042 2221)/210463 0.1770不走索引 以上可知比例值0.1664可以走索引比例值0.1770优化器认为全表扫描更快所以就不走索引。 2. MySQL5.6版本 准备数据 测试数据 分析数据 user_age 8 35042 / 208534 0.1680走索引 user_age 8(350422221)/208534 0.1786不走索引 由此可大致推断5.6版本下该比例值在 0.1680 ~ 0.1786之间。 3. MySQL5.7版本 准备数据 测试数据 分析数据 user_age 2087966 / 208303 0.422 走索引 user_age 20(87966 2093)/208303 0.4323不走索引 4. MySQL5.8版本 准备数据 测试数据 分析数据 user_age 1254798 / 208611 0.2626走索引 user_age 12(54798 2051)/208611 0.2725不走索引 范围查询后面的字段走不走索引 其实这个问题比较简单根据最佳左前缀法则在联合索引(name,age,pos)中如果age进行范围查询后都不走索引了pos还能走索引吗肯定不能啦。如果age还能走索引pos自然也能走索引。
8.4 尽量使用覆盖索引
只访问索引列的查询索引列和查询列一致避免select * 当只访问了索引列后Extra中出现了Using index表明是覆盖查询这是好的
8.5 like以通配符(%)开头的索引会失效变成全表扫描
通配符出现最前面说明所有都适配所有都要扫描 不以%开头则不失效 那我既想要以%作为开头来查询又想要这个查询是走索引的怎么办可以使用覆盖索引 准备数据tb1_user表中有id,name,age,email四个字段对name,age字段设置索引,id自动为主键索引。那么查询id,name,age字段都是覆盖索引都可以使索引生效 但是如果使用explain select * from tb1_user where name like %aa%就会索引失效因为此时不是覆盖索引不是覆盖索引使用%会失效
