临沧网站制作,做酒店网站,制作网站如何赚钱,wordpress最大附件✨博客主页#xff1a;小小恶斯法克的博客 #x1f388;该系列文章专栏#xff1a;重拾MySQL-进阶篇 #x1f4dc; 感谢大家的关注#xff01; ❤️ 可以关注黑马IT#xff0c;进行学习 目录
#x1f680;SQL提示
#x1f680;覆盖索引
#x1f680;前缀索引
小小恶斯法克的博客 该系列文章专栏重拾MySQL-进阶篇 感谢大家的关注 ❤️ 可以关注黑马IT进行学习 目录
SQL提示
覆盖索引
前缀索引
前缀长度
单列索引与联合索引
索引设计原则 SQL提示
目前tb_user表的数据情况如下 : 索引情况如下 : 把上述的 idx_user_age, idx_email 这两个之前测试使用过的索引直接删除。
drop index idx_user_age on tb_user;
drop index idx_email on tb_user;执行SQL : explain select * from tb_user where profession 电气工程 ; 很明显查询走了联合索引
执行SQL创建profession的单列索引 create index idx_user_pro on tb_user(profession); 创建完单列索引之后我们再执行语句explain select * from tb_user where profession 电气工程 ;看看走哪个索引 测试结果我们可以看到possible_keys中 idx_user_pro_age_sta,idx_user_pro 这两个 索引都可能用到最终MySQL选择了idx_user_pro_age_sta索引。这是MySQL自动选择的结果。 那么我们能不能在查询的时候自己来指定使用哪个索引呢 当然可以指定使用特定的索引进行查询。在大多数数据库管理系统中您可以在查询中明确指定要使用的索引。这通常通过在查询语句中添加关键字来实现具体取决于您使用的数据库系统。例如在许多 SQL 数据库中您可以使用类似于 USE INDEX 或 FORCE INDEX 的语法来指定要使用的索引。 如果您正在使用 NoSQL 数据库或其他类型的数据存储通常也会有类似的机制来允许您指定使用的索引。 请注意虽然可以手动指定索引但应该谨慎使用。数据库系统通常会自动选择最佳的索引手动指定索引可能会导致性能问题除非您对数据模式和查询性能有深入了解。 借助SQL提示完成手动指定索引
SQL提示是优化数据库的一个重要手段简单来说就是在SQL语句中加入一些人为的提示来达到优 化操作的目的。
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 电气工
程;覆盖索引
尽量使用覆盖索引减少select *。 那么什么是覆盖索引呢覆盖索引是指 查询使用了索引并且需要返回的列在该索引中已经全部能够找到 。 覆盖索引是一种特殊类型的索引它包含了在查询中涉及的所有字段从而使得数据库系统无需访问实际的数据行就能够满足查询需求。这种索引覆盖了查询所需的所有列因此称为覆盖索引。 当数据库执行查询时如果能够仅通过索引就能够获取到需要的数据那么数据库引擎就不必再去实际的数据表中查找相应的行这样可以大大提高查询性能。覆盖索引通常用于查询中只需要返回索引列的情况而不需要返回整个数据行的场景。 使用覆盖索引有以下几个优点 减少IO操作由于不需要访问实际的数据行因此可以减少IO操作提高查询性能。减少内存消耗覆盖索引可以减少数据库系统需要维护的内存空间因为不需要缓存完整的数据行。降低磁盘占用由于不需要存储完整的数据行覆盖索引可以节省磁盘空间。 要创建覆盖索引您需要确保索引包含了查询中涉及的所有字段。在设计数据库时考虑到查询的需求并合理地创建覆盖索引可以显著改善查询性能。 接下来我们来看一组SQL的执行计划看看执行计划的差别然后再来具体做一个解析
explain select id, profession from tb_user where profession 化工 and age
38 and status 5 ;explain select id,profession,age, status from tb_user where profession 化工
and age 38 and status 5 ;explain select id,profession,age, status, name from tb_user where profession 化工 and age 38 and status 5 ;explain select * from tb_user where profession 化工 and age 38 and status 5 ; 从上述的执行计划我们可以看到这四条SQL语句的执行计划前面所有的指标都是一样的看不出来差异。但是此时我们主要关注的是后面的Extra前面两条SQL的结果为 Using where; Using Index ; 而后面两条SQL的结果为 : null 或 Using index condition Extra 含义 Using where; Using Index 查找使用了索引但是需要的数据都在索引列中能找到所以不需 要回表查询数据 Using index condition 查找使用了索引但是需要回表查询数据 因为在tb_user表中有一个联合索引 idx_user_pro_age_sta该索引关联了三个字段 profession、age、status而这个索引也是一个二级索引所以叶子节点下面挂的是这一行的主键id。所以当我们查询返回的数据在id、profession、age、status 之中则直接走二级索引直接返回数据了。如果超出这个范围就需要拿到主键id再去扫描聚集索引再获取额外的数据了这个过程就是回表。而我们如果一直使用select * 查询返回所有字段值很容易就会造成回表查询除非是根据主键查询此时只会扫描聚集索引。 为了更清楚的理解什么是覆盖索引什么是回表查询看下面的这组SQL的执行过程。以下内容和图片均来自黑马
表结构及索引示意图 :
id是主键是一个聚集索引。 name字段建立了普通索引是一个二级索引辅助索引
执行SQL : select * from tb_user where id 2; 根据id查询直接走聚集索引查询一次索引扫描直接返回数据性能高。
执行SQL selet id,name from tb_user where name Arm; 虽然是根据name字段查询查询二级索引但是由于查询返回在字段为idname在name的二级索引中这两个值都是可以直接获取到的因为覆盖索引所以不需要回表查询性能高。 执行SQL selet id,name,gender from tb_user where name Arm; 由于在name的二级索引中不包含gender所以需要两次索引扫描也就是需要回表查询性能相对较差一点
思考 一张表 , 有四个字段 (id, username, password, status), 由于数据量大 , 需要对以下SQL语句进行优化 , 该如何进行才是最优方案 : select id,username,password from tb_user where username itcast; 答 : 针对于 username, password建立联合索引 , sql为 : create index idx_user_name_pass on tb_user(username,password); 这样可以避免上述的SQL语句在查询的过程中出现回表查询。 答针对这个查询语句最优的优化方案是创建一个覆盖索引以便在查询时能够仅通过索引就获取所需的数据而无需访问实际的数据行。在这种情况下您可以创建一个包含 (username, id, password) 字段的索引。 以下是针对该表的创建覆盖索引的SQL语句 CREATE INDEX idx_username_covering ON tb_user (username, id, password);通过创建这样的覆盖索引数据库系统在执行上述查询时只需要访问索引而无需再去实际的数据行中查找相应的列从而提高了查询性能。 前缀索引
当字段类型为字符串 varchar text longtext等时有时候需要索引很长的字符串这会让 索引变得很大查询时浪费大量的磁盘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这是最好的索引选择性性能也是最好的。
前缀索引的查询流程 单列索引与联合索引 单列索引即一个索引只包含单个列。
联合索引即一个索引包含了多个列。
我们先来看看 tb_user 表中目前的索引情况 : 在查询出来的索引中既有单列索引又有联合索引
我们来执行一条SQL语句看看其执行计划
explain select id,phone,name from tb_user where phone 15377777775 and name f; 通过上述执行计划我们可以看出来在and连接的两个字段 phone、 name上都是有单列索引的但是 最终mysql只会选择一个索引也就是说只能走一个字段的索引此时是会回表查询的。
紧接着我们再来创建一个phone和name字段的联合索引来查询一下执行计划
create unique index idx_user_phone_name on tb_user (phone,name); 此时查询时就走了联合索引而在联合索引中包含 phone、 name的信息在叶子节点下挂的是对应的主键id所以查询是无需回表查询的。 在业务场景中如果存在多个查询条件考虑针对于查询字段建立索引时建议建立联合索引 而非单列索引。 如果查询使用的是联合索引具体的结构示意图如下 索引设计原则 针对于数据量较大且查询比较频繁的表建立索引。 针对于常作为查询条件where、排序 order by、分组group by操作的字段建立索 引。尽量选择区分度高的列作为索引尽量建立唯一索引区分度越高使用索引的效率越高。 如果是字符串类型的字段字段的长度较长可以针对于字段的特点建立前缀索引。 尽量使用联合索引减少单列索引查询时联合索引很多时候可以覆盖索引节省存储空间 避免回表提高查询效率。 要控制索引的数量索引并不是多多益善索引越多维护索引结构的代价也就越大会影响增 删改的效率。 如果索引列不能存储NULL值请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含 NULL值时它可以更好地确定哪个索引最有效地用于查询。 希望对大家有帮助
