MYSQL-索引

索引

概念

索引(index) 是帮助MySQL高效获取数据的数据结构(有序)

比如查找select * from user where age = 45

数据库会扫描整个表来找到合适的数据。

而基于索引，会更快，比如二叉树：
image-20250124163251965

缺点：1.占空间。2.对于更新表维护索引时，效率会有所影响

索引结构

二叉树缺点:顺序插入时，会形成一个链表，查询性能大大降低。大数据量情况下，层级较深，检索速度慢。
红黑树:大数据量情况下，层级较深，检索速度慢。

B-Tree（多路平衡查找树）

有n个key会有n+1个指针

B+Tree：

相对于B-Tree的区别：

①所有数据都会出现在叶子结点。

②叶子结点形成一个单向链表（在mysql上增加一个指向相邻叶子节点的链表指针）

image-20250124164851931

Hash结构

哈希索引就是采用一定的hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。

如果两个(或多个)键值，映射到一个相同的槽位上，他们就产生了hash冲突(也称为hash碰撞)，可以通过链表来解决。

image-20250124180744042

为什么inodb采用B+树作为存储的数据结构

相较于二叉树，层级更少，搜索效率更高

相较于B-Tree，除了最后叶子结点存储数据，其他存储的都是索引指针，树的高度低，效率比较高，双向链表

相对于Hash索引，B+tree支持范围匹配以及排序操作，而Hash索引只能用于对等比较(=，in)，不支持范围查询(between，>，……），因为他是计算哈希值来精确定位的，没法范围排序操作。

聚集索引与二级索引

聚集索引（必须有且唯一）：将数据存储与索引放到了一块，索引结构的叶子结点保存了行数据，默认是主键索引，若没主键索引，用唯一索引代替。

二级索引（可存在多个）：数据与索引分开存储，索引结构的叶子节点关联的是对应的主键（如id）

例子：select * from user where name = 'Arm';

先根据二级索引拿到主键值，再根据主键值走聚集索引拿到这一行的数据

image-20250124184104787 image-20250124184227058

索引语法

创建索引：CREATE [UNIQUE|FULLTEXT] INDEX index_name ON table_name (index_col_name,...);

查看索引：SHOW INDEX FROM table_name;

删除索引：DROP INDEX index_ name ON table_ name;

SQL性能分析

查看执行频次

主要优化查询语句select

首先确定SQL执行频率，show [session|global] status like '';来看各语句执行的频次，可以看出来此数据库是以查询为主还是增删等为主。

慢查询日志

慢查询日志记录了所有执行时间超过指定参数(long_query_time，单位:秒，默认10秒)的所有SQL语句的日志。

针对这些语句来针对性做优化。

profile详情

show profiles; 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling参数，能够看到当前MySQL是否支持

show profiles;主要是查询所有语句耗时。

explain执行计划

EXPLAIN 或者 DESC命令获取 MySQL如何执行 SELECT语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序
语法:

直接在select语句之前加上关键字 explain/desc

EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件

EXPLAIN执行计划各字段含义:

Id：select查询的序列号，表示查询中执行select子句或者是操作表的顺序(id相同，执行顺序从上到下; id不同，值越大，越先执行)。

多表查询：explain select s.,c. from student s, course c, student_ course sc where s.id = sc.studentid and c.id = sc.courseid;

嵌套查询：explain select * from student s where s.id in (select studentid from student_course sc where sc.courseid=(select id from course c where c.name = 'MySQL'));

多表查询中ID都一样，而嵌套查询中根据括号排顺序

  • type

    • 表示连接类型，性能由好到差的连接类型为NULL、system、 const、 eq_ref、 ref、range、 index、all。

  • possible_key

    • 显示可能应用在这张表上的索引，一个或多个。

  • key

    • 实际使用的索引，如果为NULL，则没有使用索引。

  • key_len

    • 表示索引中使用的字节数，该值为索引字段最大可能长度, 并非实际使用长度,在不损失精确性的前提下，长度越短越好。

使用规则

最左前缀法则

  • 如果索引了多列(联合索引)， 要遵守最左前缀法则。

  • 最左前缀法则指的是查询从索引的最左列开始，并且不跳过索引中的列。

    如CREATE INDEX idx_category_price_name ON products (category, price, name)

    SELECT * FROM products WHERE category = 'Electronics' AND price = 100

    只要category存在就成功，不用在乎category, price, name的顺序，MySQL会自动调整。

  • 如果跳跃某一列， 索引将部分失效(前面的字段索引失效)。

    SELECT * FROM products WHERE category = 'Electronics' AND name ='中餐'

    (category, price, name)跳过了，price，则name索引失效

范围查询

联合索引中，出现范围查询(>,<),范围查询右侧的列索引失效

索引失效情况一

①在索引列上进行函数运算。

②字符串不加单引号。

③尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效（百分号）。因为索引对比的时候是在第一位开始比较的。

索引失效情况二

or连接条件
用or分割开的条件，如果or前的条件 中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

索引使用
数据分布影响：如果MySQL评估使用索引比全表更慢，则不使用索引。

SQL提示

SQL语句中加入一些人为的提示来达到优化操作的目的。

use index:
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 **; 避免回表查询的情况出现，因为*可能包含索引没有的列，就需要回表查询。

image-20250125165315087

在id是主键的情况下，对username和password建立联合索引，此时是二级索引，叶子结点下面挂的就是id。

前缀索引

当字段类型为字符串(varchar, text等)时，有时候需要索引很长的字符串，这会让索引变得很大，查询时,浪费大量的磁盘IO，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率。

前缀长度
可以根据索引的选择性来决定，而选择性是指不重复的索引值(基数)和数据表的记录总数的比值，索引选择性越高则查询效率越高，唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。

单列&联合索引

在业务场景中，如果存在多个查询条件,考虑针对于查询字段建立索引时，建议建立联合索引，而非单列索引。

多条件联合查询时（可以做到覆盖索引），MySQL优化器会评估哪个字段的索引效率更高，会选择该索引完成本次查询。

posted @ 2025-01-24 21:57  hongfeiliu  阅读(14)  评论(0)    收藏  举报