MySQL：高效的索引

embedded/2024/9/24 5:28:03/

数据库索引

  • 1. 索引介绍
    • 1.1 创建索引
    • 1.2 查看索引
  • 2. 索引应用
    • 2.1 前缀索引
    • 2.2 全文索引
    • 2.3 复合索引
    • 2.4 复合索引中的列顺序
    • 2.5 建立最佳索引
    • 2.6 使用索引排序
    • 2.7 覆盖索引
  • 3. 维护索引
  • 4. 建立性能数据库

索引对大型和高并发数据库非常有用，因为它可以显著提升查询的速度。

1. 索引介绍

原理：以寻找所在州（state）为 ‘CA’ 的顾客为例，如果没索引，MySQL就必须逐条扫描筛选所有记录。索引，就好比书籍最后的那些关键词索引一样，按字母排序，这样就能迅速找到需要的关键词，所以如果对state字段建立索引，也就是把state列单独拿出来分类排序并建立与原表顾客记录的对应关系，就可以通过该索引迅速找到在’CA’的顾客。
另一方面，索引会比原表小得多，通常能够储存在内存中，而从内存读取数据总是比从硬盘读取快多了，这也会提升查询速度。
如果数据量比较小，几百几千这种，没必要用索引，但如果是上百万的数据量，有无索引对查询时间的影响就很大了。
但建立索引也是有代价的，首先索引会占用内存，其次它会降低写入速度，因为每次修改数据时都会自动重建索引。所以不要对整个表建立索引，而只是针对关键的查询建立索引。
一般采用二叉树来描述索引。

1.1 创建索引

查看普通的搜索情况
在这里插入图片描述
代码：

EXPLAIN SELECT customer_id FROM customers WHERE state = 'CA'

type是ALL而rows是1010行，说明在没有索引的情况下，MySQL扫描了所有的记录。可用下面的语句确认customers表总共就是1010条记录。

  • 创建索引
    索引名习惯以idx或ix做前缀，后面的名字最好有意义，不要别取 idx_1、idx_2 这样没人知道是什么意思的名字。
CREATE INDEX idx_state ON customers(state);
EXPLAIN SELECT customer_id FROM customers WHERE state = 'CA';

EXPLAIN为解释性查询语句。
在这里插入图片描述
这次显示type是ref而rows只有112，扫面的行数显著减少，查询效率极大提升。

1.2 查看索引

代码：

SHOW INDEXES IN customers;

在这里插入图片描述
可以看到有三个索引，第一个是 MySQL 为主键 customer_id 创建的索引 PRIMARY，被称作clustered index 聚合索引，每当我们为表创建主键时，MySQL就会自动为其创建索引，这样就能快速通过主键（通常是id）找到记录。后两个是我们之前手动为state和points字段建立的索引 idx_state 和 idx_points，它们是 secondary index从属索引，MySQL在创建从属索引时会自动为其添加主键列，如每个idx_points索引的记录有两个值：客户的积分points和对应的客户编号customer_id，这样就可以通过客户积分快速找到对应的客户记录。

属性解释：

  1. Non_unique 是否是非唯一的，即是否是可重复的、可相同的，一般主键索引是0，其它是1；
  2. Column_name 表明索引建立在什么字段上；
  3. Collation 是索引内数据的排序方式，其中A是升序，B是降序；
  4. Cardinality（基数）表明索引中独特值/不同值的数量，如PRIMARY的基数就是1010，毕竟每条记录都都有独特的主键，而另两个索引的基数都要少一些，从之前 Non_unique 为 1 也可以看得出来 state 和 points 有重复值，这里的基数可以更明确看到 state 和 points 具体有多少种不同的值；Cardinality 这里只是近似值而非精确值，要先用以下语句重建顾客表的统计数据：
ANALYZE TABLE customers;
  1. Index_type 都是BTREE（二叉树），之前说过MySQL里大部分的索引都是以二叉树的形式储存的。

Note:

  1. 当我们建立表间连接时，MySQL会自动为外键添加索引，这样就能快速进行表连接（join tables）了;
  2. 还可以通过菜单方式查看某表中的索引，在左侧导航栏里 customers 表的子文件里就有一个 indexes 文件夹，点击里面的索引可以看到该索引的若干属性，其中 visible（可见性） 表示其是否可用（enabeled）.

2. 索引应用

2.1 前缀索引

当索引的列是字符串时（包括 CHAR、VARCHAR、TEXT、BLOG），尤其是当字符串较长时，我们通常不会使用整个字符串而是只是用字符串的前面几个字符来建立索引，这被称作 Prefix Indexes 前缀索引，这样可以减少索引的大小使其更容易在内存中操作，毕竟在内存中操作数据比在硬盘中快很多.

CREATE INDEX idx_lastname ON customers (last_name(20));

这个字符数的设定对于 CHAR 和 VARCHAR 是可选的，但对于 TEXT 和 BLOG 是必须的。
如何选择最佳字符？太多了会使得索引太大难以在内存中运行，太少又达不到筛选的效果，达到目标是用尽可能少的前缀字符得到尽可能多的独特值个数。

  • 方法：利用 DISTINCT、LEFT 关键词和 COUNT 函数来测试不同数目的前缀字符得到的独特值个数。
SELECTCOUNT(DISTINCT LEFT(last_name,1)),COUNT(DISTINCT LEFT(last_name,5)),COUNT(DISTINCT LEFT(last_name,10))
FROM customers

在这里插入图片描述
前1个到前5个字符，效果提升是很显著的，但从前5个到前10个字符，所用的字符数增加了一倍但识别效应只增加了一点点，再加上5个字符已经能识别出966个独特值，与1010的记录总数相去不远了，所以可以认为用前5个字符来创建前缀索引是最优的。

2.2 全文索引

使用场景：假设我们创建了一个博客网站，里面有一些文章，并存放在上面这个sql_blog数据库里，如何让用户可以对博客文章进行搜索呢？

方法一：直接搜索

USE sql_blog;
SELECT *
FROM posts
WHERE title LIKE '%react redux%' OR body LIKE '%react redux%';

在没有索引的情况下，会对所有文本进行全面扫描，效率低下。如果用上节课讲的前缀索引也不行，因为前缀索引只包含标题或内容开头的若干字符，若搜索的内容不在开头，以依然需要全面扫描。这种搜索方式只会返回完全符合’%react redux%'的结果，但我们一般想搜索的是包括这两个单词的任意一个或两个，任意顺序，中间有任意间隔的所有相关结果，即google式的模糊搜索。

方法二：利用全文索引

CREATE FULLTEXT INDEX idx_title_body ON posts(title, body);
-- 创建全文索引，然后利用MATCH和AGAINST内置函数进行查询alter
SELECT *, MATCH(title, body) AGAINST('react redux')
-- 可以看到相关全文索引的相关性得分
FROM posts
WHERE MATCH(title, body) AGAINST('react redux');
-- MATCH函数中必须包含创建全文索引中所有列

在这里插入图片描述
全文检索模式：自然语言模式和布林模式
自然语言模式是默认模式，也是上面用到的模式。布林模式可以更明确地选择包含或排除一些词汇（google也有类似功能）。

  1. 尽量有 react，不要有 redux，必须有 form
WHERE MATCH(title, body) AGAINST('react 【-redux +form】' 【IN BOOLEAN MODE】);
  1. 布林模式也可以实现精确搜索，就是将需要精确搜索的内容再用双引号包起来
WHERE MATCH(title, body) AGAINST('【"handling a form"】' IN BOOLEAN MODE);

全文索引十分强大，如果你要建一个搜索引擎可以使用它，特别是要搜索的是长文本时，如文章、博客、说明和描述，否则，如果搜索比较短的字符串，比如名字或地址，就使用前置字符串。

2.3 复合索引

当查询多个组合条件时，如：

EXPLAIN SELECT customer_id
FROM customers
WHERE state = 'CA' AND points > 1000;

在这里插入图片描述

会发现MySQL在idx_state、idx_points两个候选索引最终选择了 idx_state，总共扫描了112行记录。虽然提升了速度，但是只完成了一半的工作量：它能帮助快速找到在 ‘CA’ 的顾客，但要寻找其中积分大于1000的人时，却不得不回到磁盘里进行原表扫描（因为idx_state索引里没有积分信息），如果加州有一百万人的话这就会变得很慢。
因此需要建立复合索引，如：

CREATE INDEX idx_state_points ON customers(state, points);
EXPLAIN SELECT customer_id
FROM customers
WHERE state = 'CA' AND points > 1000;

在这里插入图片描述
行数越少表示扫描的越少，执行的也就越快。

发现在 idx_state、idx_points、idx_state_points 三个候选索引中 MySQL 发现组合索引对我们要做的查询而言是最优的因而选择了它，最终扫描的行数由112降到了58，速度确实提高了。

多余多种组合查询，使用复合索引，然后删除不需要的单个索引以节约资源。

NOTE: 新手爱犯的错误是给表里每一列都建立一个单独的索引，再加上MySQL会给每个索引自动加上主键，这些过多的索引会占用大量储存空间并且拖慢更新速度 （因为每次数据更新都会重建索引）. 但实际中更多的是用到组合索引，所以不应该无脑地为每一列建立单独的索引而应该依据查询需求来建立合适的组合索引，一个组合索引最多可组合16列上，但一般4到6列的组合索引是比较合适的，但别把这个数字当作金科玉律，总是根据实际的查询需求和数据量来考虑。

  • 删除索引
DROP INDEX idx_state ON customers;

2.4 复合索引中的列顺序

确定组合索引的列顺序时有两个指导原则：

  1. 将最常使用的列放在前面：范围一般从大到小逐步去定位；
  2. 将基数（Cardinality）最大/独特性最高的列放在前面：因为基数越大/独特性越高，起到的筛选作用越明显，能够迅速缩小查询范围。但最终仍然要根据实际的查询需求来决定，因为实际查询的筛选条件不一定能完全利用相应列的全部独特性。但是还是要考虑定位时采用的筛选条件，绝对定位的等级（=）高于模糊定位（like）,此时不一定光考虑基数了。
  • 对比案例
CREATE INDEX idx_state_lastname ON customers(state, last_name);
CREATE INDEX idx_lastname_state ON customers(last_name, state);
EXPLAIN SELECT customer_id
FROM customers
USE INDEX (idx_state_lastname)
WHERE state = 'CA' AND last_name LIKE 'A%';

在这里插入图片描述

EXPLAIN SELECT customer_id
FROM customers
USE INDEX (idx_lastname_state)
WHERE state = 'CA' AND last_name LIKE 'A%';

在这里插入图片描述
会发现 idx_state_lastname 反而扫描的行数更少，效率更高，把查找的state换为’NY’也是一样。这是因为last_name的筛选条件是 ‘LIKE’ 而不是 ‘=’，约束性更小（less restrictive），更开放（more open），并没有充分利用姓氏列的高独特性，对于这种针对姓氏的模糊查找，先筛选州反而能更快缩小范围提高效率，所以idx_state_lastname 更有效。

总之，不仅要考虑各列的独特性高低，也要考虑常用的查询是否能充分利用各列的独特性，两者结合来决定组合索引里的排序，不确定就测试对比验证，所以，第二条原则也许应该改为将常用查询，实际利用到的独特性程度最高的列放在前面。

任何一个索引都只对一类查询有效而且对特定的查询内容最高效，我们要现实一些，要去最优化那些性能关键查询，而不是所有可能的查询（optimize performance critical queries, not all queries in the world）能加速所有查询的索引是不存在的，随着数据库以及查询需求的增长和扩展，我们可能需要建立不同列的不同顺序的组合索引。

2.5 建立最佳索引

案例1：根据查询条件来确定索引

EXPLAIN SELECT customer_id 
FROM customers
WHERE state = 'CA' OR points > 1000;

在这里插入图片描述
产生的效果仍然是将数据库表格全部检索了一遍，这里的复合索引没有起到效果。因此需要根据实际查询情况来建立相应的索引，这里将两个筛选条件分开来查询效果更好。更改后如下：

EXPLAIN SELECT customer_id 
FROM customers
WHERE state = 'CA' 
UNION
SELECT customer_id 
FROM customers
WHERE points > 1000;
-- 系统自己寻找合适的索引来查询

在这里插入图片描述
结果显示，两部分查询中，MySQ分别自动选用了对该查询最有效的索引idx_state_points和idx_points，扫描的行数分别为112和529，总共641行，相比于1010行有很大的提升。

案例2：索引时，将列单独列出来，列属性名不能参与计算会影响索引速度

EXPLAIN SELECT customer_id 
FROM customers
WHERE points + 10 > 2010;

在这里插入图片描述
因为column expression列表达式（列运算）不能最有效地使用索引，要重写运算表达式，独立/分离此列（isolate the column）。

EXPLAIN SELECT customer_id 
FROM customers
WHERE points > 2000;

在这里插入图片描述
直接从1010行降为4行，效率提升显著。所以想要MySQL有效利用索引，就总是在表达式中将列独立出来。

2.6 使用索引排序

  1. 特定索引只对特定查询和排序最有效，而且这些从索引的原理上都很好理解;
  2. 建立什么索引取决于查询和排序需求，而查询和排序也要尽量去迎合索引以尽可能提高效率。
  3. 理解字符串的索引建立和数值的索引建立（二叉树）。
  • 案例：针对上文中数据库，仅剩下idx_lastname, idx_state_points 两个索引。
EXPLAIN SELECT customer_id,state
FROM customers
ORDER BY state;
SHOW STATUS LIKE 'last_query_cost';  -- 上次查询的消耗值

在这里插入图片描述
在这里插入图片描述

EXPLAIN SELECT customer_id
FROM customers
ORDER BY first_name;
SHOW STATUS LIKE 'last_query_cost';

在这里插入图片描述
在这里插入图片描述
查看Extra信息，非索引列排序常常用的是filesort（外部文件排序）算法，从cost可以看到filesort消耗的资源几乎是用索引排序的10倍，这很好理解，因为索引就是对字段进行分类和排序，等于是已经提前排好序了。

所以，不到万不得已不要给非索引数据排序，有可能的话尽量设计好索引用于查询和排序。

  • idx_state_points，它等于是先对state分类排序，再在同一个state内对points进行分类排序，再加上customer_id映射到相应的原表记录。
    索引 idx_state_points 对于以下排序有效：
ORDER BY state
ORDER BY state, points
【ORDER BY points WHERE state = 'CA' 】

总的来说一个组合索引对于按它的组合列 “从头按顺序” 进行的WHERE筛选和ORDER BY排序最有效，对其他的无效或部分有效。

  • 对于ORDER BY子句还有一个问题是升降序，索引本身是升序的，但可以 Backward index scan倒序索引扫描，所以它对所有同向的（同升序或同降序）的ORDER BY子句都有效，但对于升降序混合方向的ORDER BY语句则不够有效，还是以idx_state_points为例，对以下 ORDER BY 子句有效：
ORDER BY state
ORDER BY state DESC
ORDER BY state, points
ORDER BY state DESC, points DESC

2.7 覆盖索引

设计索引时，先看 WHERE 子句，看看最常用的筛选字段是什么，把它们包含在索引中，这样就能迅速缩小查找范围，其次查看 ORDER BY 子句，看看能不能将这些列包含在索引中，最后，看看 SELECT 子句中的列，如果你连这些也包含了，就得到了覆盖索引，MySQL 就能只用索引就完成你的查询，实现最快的查询速度。

案例分析：

EXPLAIN SELECT customer_id
FROM customers
ORDER BY state;
SHOW STATUS LIKE 'last_query_cost';

在这里插入图片描述

EXPLAIN SELECT customer_id, state
FROM customers
ORDER BY state;
SHOW STATUS LIKE 'last_query_cost';

在这里插入图片描述

EXPLAIN SELECT *
FROM customers
ORDER BY state;
SHOW STATUS LIKE 'last_query_cost';# 3. 索引维护

在这里插入图片描述

Using index 而且 cost 均只有两百左右，而第3种是 Using filesort 而且 cost 超过一千，这从 idx_state_points 的原理上很好理解。

从属索引除了包含相关列还会自动包含主键列（通常是某种id列）来和原表中的记录建立对应关系，所以 组合索引idx_state_points中包含三列：state、points以及customer_id，所以如果SELECT子句里选择的列是这三列中的一列或几列的话，整个查询就可以在只使用索引不碰原表的情况下完成，这叫作覆盖索引（covering index），即索引满足了查询的所有需求，这是最快的。

3. 维护索引

索引管理也应该是个根据业务查询需求需要不断去权衡成本效益，抓大放小，迭代优化的过程。
维护三准则：

  1. 不要建立重复索引，建立索引前一定要先用SHOW查看已有索引。现有版本已经不容许建立相同名字索引和相同对象索引。
  2. 不要建立冗余索引，除非有不同的用处。如已有idx_state_points，那 idx_state就是冗余的了，因为所有idx_state能满足的筛选和排序需求idx_state_points都能满足。但idx_points和idx_points_state不是冗余的，因为它们可以满足不同的筛选和排序需求。
  3. 不要建立无用索引。是那些常用查询、排序用不到的索引没必要建立。

4. 建立性能数据库

  1. 较小的表性能更好。不要存储不需要的数据。解决今天的问题，而不是明天可能永远不会发生的问题。
  2. 使用尽可能小的数据类型。如果你需要存储人们的年龄，一个TINYINT就足够了，无需使用INT。对于一个小的表来说，增加几个字节没什么大不了的，但在包含数百万条记录的表中却具有显著的影响。
  3. 每个表都必须有一个主键。
  4. 主键应短。如果您只需要存储一百条记录，最好选择 TINYINT 而不是 INT。
  5. 首选数字类型而不是字符串作为主键。这使得通过主键查找记录更快。
  6. 避免 BLOB。它们会增加数据库的大小，并会对性能产生负面影响。如果可以，请将文件存储在磁盘上。
  7. 如果表的列太多，请考虑使用一对一关系将其拆分为两个相关表。这称为垂直分区（vertical partitioning）。例如，您可能有一个包含地址列的客户表。如果这些地址不经常被读取，请将表拆分为两个表（users 和user_addresses）。
  8. 相反，如果由于数据过于碎片化而总是需要在查询中多次使用表联接，则可能需要考虑对数据反归一化。反归一化与归一化相反。它涉及把一个表中的列合并到另一个表（以减少联接数）（如之前 airports 里的 city 和state）。
  9. 请考虑为昂贵的查询创建摘要/缓存表。例如，如果获取论坛列表和每个论坛中的帖子数量的查询非常昂贵，请创建一个名为 forums_summary 的表，其中包含论坛列表及其中的帖子数量。您可以使用事件定期刷新此表中的数据。您还可以使用触发器在每次有新帖子时更新计数。
  10. 全表扫描是查询速度慢的一个主要原因。使用 EXPLAIN 语句并查找类型为 “ALL” 的查询。这些是全表扫描。使用索引优化这些查询。
  11. 在设计索引时，请先查看WHERE子句中的列。这些是第一批候选人，因为它们有助于缩小搜索范围。接下来，查看 ORDER BY 子句中使用的列。如果它们存在于索引中，MySQL 可以扫描索引以返回有序的数据，而无需执行排序操作（filesort）。最后，考虑将 SELECT 子句中的列添加到索引中。这为您提供了覆盖索引，能覆盖你查询的完整需求。MySQL 不再需要从原表中检索任何内容。
  12. 选择组合索引，而不是多个单列索引。
  13. 索引中的列顺序很重要。将最常用的列和基数较高的列放在第一位，但始终考虑您的查询。
  14. 删除重复、冗余和未使用的索引。重复索引是同一组具有相同顺序的列上的索引。冗余索引是不必要的索引，可以替换为现有索引。例如，如果在列（A、 B）上有索引，并在列 （A）上创建另一个索引，则后者是冗余的，因为前一个索引可以满足相同的需求。
  15. 在分析现有索引之前，不要创建新索引。
  16. 在查询中隔离你的列，以便 MySQL 可以使用你的索引。
  17. 避免 SELECT *。大多数时候，选择所有列会忽略索引并返回您可能不需要的不必要的列。这会给数据库服务器带来额外负载。
  18. 只返回你需要的行。使用 LIMIT 子句限制返回的行数。
  19. 避免使用前导通配符的LIKE 表达式（eg.“%name”） 。
  20. 如果您有一个使用 OR 运算符的速度较慢的查询，请考虑将查询分解为两个使用单独索引的查询，并使用UNION 运算符组合它们。

http://www.ppmy.cn/embedded/56730.html

相关文章

【云原生监控】Prometheus 普罗米修斯从搭建到使用详解

【云原生监控】Prometheus 普罗米修斯从搭建到使用详解

目录 一、前言 二、服务监控概述 2.1 什么是微服务监控 2.2 微服务监控指标 2.3 微服务监控工具 三、Prometheus概述 3.1 Prometheus是什么 3.2 Prometheus 特点 3.3 Prometheus 架构图 3.3.1 Prometheus核心组件 3.3.2 Prometheus 工作流程 3.4 Prometheus 应用场景…
阅读更多...
Redis的两种持久化方案

Redis的两种持久化方案

Redis 提供了多种持久化机制来保证数据在发生意外情况下（如断电或服务器崩溃）不丢失。以下是几种主要的 Redis 持久化方案及其特点： 1. RDB (Redis Database Backup) RDB 是 Redis 创建的数据库快照，它可以将数据集快照以二进制…
阅读更多...
2 极/2 零 （2P2Z） 补偿器

2 极/2 零 （2P2Z） 补偿器

极/2 零 （2P2Z） 补偿器是模拟 II 型控制器的数字实现。它是一种滤波器，通过考虑两个极点和一个零点，将特定的增益和相位升压引入系统。您必须战略性地选择每个极点和零点的频率位置，这将有助于实现所需的系统性能。在该…
阅读更多...
c_各个unsigned int 和 int的取值范围

c_各个unsigned int 和 int的取值范围

bool, uint8_t, uint16_t, uint32_t, uint64_t, int8_t, int16_t, int32_t, int64_t 取值范围分别是什么？ 定义形式： typedef unsigned char uint8_t; typedef unsigned short uint16_t; typedef unsigned int uint32_t; typedef unsigned long uint64_…
阅读更多...
Qt中使用MySQL数据库详解，好用的模块类封装

Qt中使用MySQL数据库详解，好用的模块类封装

本文将详细介绍如何在Qt应用程序中集成MySQL数据库，并封装实现好用的mysql数据库操作类。包括环境准备、连接数据库、执行查询及异常处理等关键步骤，同时包含mysql驱动的编译。分享给有需要的小伙伴，喜欢的可以点击收藏。 目录 环境准备 项…
阅读更多...
入门机器视觉的正确打开方式——徒手撸一个python+opencv实现的机器视觉简易调试工具（下）

入门机器视觉的正确打开方式——徒手撸一个python+opencv实现的机器视觉简易调试工具（下）

目录 1.引言2.框架思路3.图像处理流程化的实现3.1如何解析图像流程数据结构3.2 使用networkx网络图库3.3 python实现 4.结论5.python源码PS.扩展阅读ps1.六自由度机器人相关文章资源ps2.四轴机器相关文章资源ps3.移动小车相关文章资源 1.引言 在当今AI时代，关于视觉…
阅读更多...
33. 搜索旋转排序数组（中等）

33. 搜索旋转排序数组（中等）

33. 搜索旋转排序数组 1. 题目描述2.详细题解3.代码实现3.1 Python3.2 Java 1. 题目描述 题目中转：33. 搜索旋转排序数组 2.详细题解 给定数组是严格有序的，但在某个位置的下标处进行了旋转，因此改变了数组有序的特性，但数组局…
阅读更多...
【Python机器学习】算法链与管道——用预处理进行参数选择的注意项

【Python机器学习】算法链与管道——用预处理进行参数选择的注意项

对于许多机器学习算法，提供的特定数据表示非常重要。比如，首先对数据进行缩放，然后手动合并特征，再利用无监督机器学习来学习特征。因此，大多数机器学习应用不仅需要应用多个算法，而且还需要将许多不同的处…
阅读更多...
最新文章

Copyright @ 2022~2023