[2025.1.26 MySQL学习] 索引

索引

索引概述

  • 索引(index)是帮助MySQL高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引
  • 优缺点：

image





索引结构

  • MySQL的索引是在存储引擎层实现的，不同的存储引擎有不同结构，主要包含以下几种：

image

image

1、B+tree

image

2、Hash

image

  • 只能用于对等比较（=， in），不支持范围查询（between，>，<，...）
  • 无法利用索引完成排序操作
  • 查询效率高，通常只需要一次检索就可以，效率通常要高于B+tree索引

3、思考

  • Q：为什么InnoDB采用B+tree索引结构
  • A：
    • 相对于二叉树，层级更少，搜索效率高
    • 对于B-tree而言，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少，指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低

image





索引的分类

image

image

  • 聚集索引选取规则：
    • 如果存在主键（一个字段），主键索引就是聚集索引
    • 如果不存在主键，将使用第一个唯一（UNIQUE）索引作为聚集索引
    • 如果表没有主键，或没有合适的唯一索引，则InnoDB会自动生成一个rowid作为隐藏的聚集索引
    • 回表查询：在二级索引后，根据索引值再进行聚集索引

思考

image

  • n为一个页中的键值数
  • 计算得出的是一页最多可以存储的字节数




索引语法

1、创建索引

  • create index idx_user_name on emp(name);，索引名为idx_user_name，常规索引，可重复
  • create unique index idx_user_phone on emp(phone);，唯一索引
  • create index idx_user_pro_age_sta on emp(profession,age,status);，联合索引，要注意顺序

2、查看索引

  • show index from emp;，查看索引

3、删除索引

  • drop index idx_user_name on emp;




SQL性能分析（SQL优化需要）

  • MySQL客户端连接成功后，通过show [session|global] status命令可以提供服务器状态信息。通过如下指令，可以查看当前数据库INSERT、UPDATE、DELETE、SELECT的访问频次
  • 慢查询日志：查询频次后，需要定位执行效率低的SQL语句，慢查询日志记录了所有执行时间超过指定参数的所有SQL语句的日志，默认未开启，需要在MySQL的配置文件（/etc/my.cnf）中配置如下信息：
#开启MySQL慢查询日志开关
slow_query_log=1
#设置慢日志的时间为2s，超过该时间则视为慢查询
long_query_time=2
  • profile详情：show profile能够在SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profile参数，能够看到当前MySQL是否支持profile操作：select @@have_profiling;，默认关闭，使用set profiling = 1;进行打开
  • explain执行计划：在任意sql语句前加上explain：explain select * from emp where id = 1;，其中显示每一列的意义：
    • id：表示查询中执行select子句或者是操作表的顺序（id相同，执行顺序从上到下；id不同，值越大，越先执行）
    • select_type：表示SELECT的类型，常见的取值有：
      • SIMPLE：简单表，不使用表连接或子查询
      • PRIMARY：主查询，即外层查询
      • UNION：UNION中第二个或者后面的查询语句
      • SUBQUERY：SELECT/WHERE之后包含子查询
    • type：表示连接类型，性能由好到差为：NULL、system、const、eq_ref、ref、range、index、all（全表扫描）
    • possible_key：可能用到的索引
    • key：实际用到的索引
    • Key_len：索引中使用的字节数
    • rows：MySQL认为必须要执行查询的行数
    • filtered：表示返回结果的行数占需读取行数的百分比，越大越好




**

索引使用

  • 查询从索引最左列开始（最左索引必须存在，不限位置 - where），并且不跳过索引中的列（跳过，后面的索引会失效）
  • 联合索引中，出现范围查询（>,<），范围查询右侧的列索引失效（范围查询会失去有序性，无法精确定位，如：在 age > 25 的范围内，status 的值可能是无序的。例如：age = 30 的 status = 0。age = 35 的 status = 1，MySQL 无法通过索引快速定位 status = 1 的行，只能逐行检查）
  • 不要在索引列上进行运算操作，索引将失效
  • 字符串类型字段使用时，不加引号，索引将失效
  • 模糊查询中，如果仅仅是尾部模糊匹配，索引不会失效；如果是头部模糊匹配，索引失效
  • 用or分割开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么**涉及的索引都不会被用到
  • 如果MySQL评估使用索引比全表更慢，则不使用索引
  • 尽量使用覆盖索引（查询使用了索引，同时需要返回的列在该索引中已经能全部找到），减少select *
    • using index condition：查找使用了索引，但是需要回表查询数据
    • using where；using index：不需要回表
  • 前缀索引：当字段类型为字符串时，索引过长，此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，提高索引效率

SQL提示

  • 是优化数据库的一个重要手段，简单来说就是在SQL语句中加入一些人为的提示来达到优化操作的目的
  • 表名后 + use index()、ignore index()、force index()，建议使用、忽略、强制使用

单列索引和联合索引

  • 单列索引：即一个索引只包含单个列
  • 联合索引：即一个索引包含多个列
    • 联合索引查询要符合最左前缀法则，联合索引的第一列可以说是从左到右单调递增的，但其他列并没有这个特性，它们只能在第一列值相等的情况下这个小范围内递增。
    • 因此，由于联合索引是上述那样的索引构建方式及存储结构，所以联合索引只能从多列索引的第一列开始查找。所以如果联合索引为(b,c,d)，你的查找条件不包含b列如（c,d）、(c）、(d)是无法应用缓存的，以及跨列也是无法完全用到索引如(b,d)，只会用到b列索引。
  • 在业务场景中，如果存在多个查询条件，考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引




索引设计原则

  • 针对数据量较大，且查询较频繁的表建立索引
  • 针对于常作为查询条件（where）、排序（ordered by）、分组（group by）操作的字段建立索引
  • 尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用效率越高
  • 如果是字符串类型字段，字段长度较长，可根据特点，建立前缀索引
  • 控制索引的数量，并不是越多越好，索引越多，维护索引结构的代价也就越大，会影响增删改的效率
  • 如果索引不能存储NULL值，在创建表时使用NOT NULL约束，当优化器知道每列是否包含NULL值时，它可以更好地确定哪个索引最有效地用于查询
posted @ 2025-01-26 22:03  Luna-Evelyn  阅读(49)  评论(0)    收藏  举报