MySQL 索引与查询优化

2023-11-12 来源：花图问答

联合索引

在进行多列搜索时有一条经验法则: 首先使用选择性高的列进行搜索。

我们可以将选择性定义为 count(distinct ) / count(*), 也就是说满足条件的数据越少，则条件的选择性越高。

假设用户名name比性别gender选择性高, 那么查询应该写作WHERE name=‘finley‘ AND gender=‘M‘而不是WHERE gender=‘M‘ AND name=‘finley‘。

实际上两条语句是等效的, 当存在多个查询条件时 MySQL 优化器会根据索引和选择性决定最优的过滤顺序。

为每一个列单独建立索引，并不能有效支持多列查询

CREATE TABLE `user` ( `id` INT, `first_name` VARCHAR(16), `middle_name` VARCHAR(16), `last_name` VARCHAR(16), PRIMARY KEY (`id`), KEY `idx_first_name` (`first_name`), KEY `idx_middle_name` (`middle_name`));

查询语句:

select * from user where first_name = ‘a‘ AND middle_name = ‘bc‘;

查看查询计划:

id	select_type	table	type	possible_keys	key	key_len	ref	rows	filtered	Extra
1	SIMPLE	user	ALL	idx_first_name,idx_middle_name	NULL	NULL	NULL	4	100.00	Using where

根据最左匹配法则和优先使用高选择性列的经验法则，可以得出一条建议:

对于需要进行多列查询的表，应建立包含所有参与查询列的联合索引, 索引的顺序应按照列的选择性从强到弱排列

一些关于索引的建议

通常在使用索引检索到数据之后，需要访问磁盘上数据表文件读取所需要的列，这种操作称为"回表"。

若索引中包含查询的所有列，则不需要回表操作直接从索引文件中读取数据即可, 这种索引称为覆盖索引。

在查询时尽量减少"SELECT *"只查询需要的行, 条件允许时尽量建立覆盖索引。

《数据库索引设计与优化》一书中提出了判断最佳索引的"三星索引"概念:

1星: 可以在索引上(用 ref 或 eq_ref 方式)完成等值查询。需要取出等值谓词涉及的列作为索引开头的列以满足最左匹配原则。
2星: 可以使用索引进行排序
3星: 索引中包含要查询的所有列，不需要回表

MySQL 在索引包含 null 的列时需要额外的开销, 尽量避免允许索引列上存在 null。

除非有非常严格的一致性要求，否则应避免使用外键。

关于主键:

避免使用字符串类型作为主键

使用MD5、UUID等随机的主键可能导致更多的磁盘随机读写，但一般不会有太大的性能问题

auto_increment 使用锁机制实现，可能影响写入性能。

在查询较多且业务允许的情况下, 推荐使用自增主键。

不知道放哪儿好的两条建议:

BLOB 用于存储较大的二进制串，TEXT 用于存储较大的字符串；它们不能被索引；

ip地址是32位无符号整数，使用 INT UNSIGNED 存储ip地址而不是字符串。INET_ATON(), INET_NTOA()可以转换数字和字符串两种格式

查询一些关于查询的建议

尽量避免使用 != 或 not in

条件允许时避免使用 join 查询, 可以先分别查询然后在应用程序内存中关联

避免在where语句中进行 is null 判断, 这可能导致MySQL放弃使用索引而进行全表扫描

条件允许时使用 union all 而非 union, 避免 union 不必要的去重操作

必要时使用 union (all) 代替 or 条件

小表驱动大表

MySQL在执行多表查询时可以采用Nest Loop Join算法，即选择数据集较小的一张表(数据集)作为驱动表, 遍历驱动表中所有记录并连接另一张表中符合条件的记录。

在使用 JOIN 进行查询时 MySQL 会自动选择数据集较小的一张表作为驱动表。

LEFT JOIN 强制左表作为驱动表, RIGHT JOIN 则强制选择右表作为驱动表。

MySQL 的 STRAIGHT_JOIN 结果与 INNER JOIN 相同, 但强制使用左表作为驱动表, 可用来分析选择不同驱动表的效果。

在业务允许的情况下, 让 MySQL 自行决定驱动表。

在使用 IN 进行多表查询时一般会把 IN 内部的嵌套循环作为驱动表, 应尽量减少IN数据集的大小。实际上, MySQL 也会对 IN 和 EXISTS 查询进行优化, 选择最优的驱动表。

MySQL 索引与查询优化

标签：order 进制表驱动缩小 sign 部分一致性 ali 保持数据

小编还为您整理了以下内容，可能对您也有帮助：

mysql查询优化器应该怎么使用

在开始演示之前，我们先介绍下两个概念。

概念一，数据的可选择性基数，也就是常说的cardinality值。

查询优化器在生成各种执行计划之前，得先从统计信息中取得相关数据，这样才能估算每步操作所涉及到的记录数，而这个相关数据就是cardinality。简单来说，就是每个值在每个字段中的唯一值分布状态。

比如表t1有100行记录，其中一列为f1。f1中唯一值的个数可以是100个，也可以是1个，当然也可以是1到100之间的任何一个数字。这里唯一值越的多少，就是这个列的可选择基数。

那看到这里我们就明白了，为什么要在基数高的字段上建立索引，而基数低的的字段建立索引反而没有全表扫描来的快。当然这个只是一方面，至于更深入的探讨就不在我这篇探讨的范围了。

概念二，关于HINT的使用。

这里我来说下HINT是什么，在什么时候用。

HINT简单来说就是在某些特定的场景下人工协助MySQL优化器的工作，使她生成最优的执行计划。一般来说，优化器的执行计划都是最优化的，不过在某些特定场景下，执行计划可能不是最优化。

比如：表t1经过大量的频繁更新操作，（UPDATE,DELETE,INSERT），cardinality已经很不准确了，这时候刚好执行了一条SQL，那么有可能这条SQL的执行计划就不是最优的。为什么说有可能呢？

来看下具体演示

譬如，以下两条SQL，

A：

B：

如果f1的值刚好频繁更新的值为30，并且没有达到MySQL自动更新cardinality值的临界值或者说用户设置了手动更新又或者用户减少了sample page等等，那么对这两条语句来说，可能不准确的就是B了。

这里顺带说下，MySQL提供了自动更新和手动更新表cardinality值的方法，因篇幅有限，需要的可以查阅手册。

那回到正题上，MySQL 8.0 带来了几个HINT，我今天就举个index_merge的例子。

示例表结构：

表记录数：

这里我们两条经典的SQL：

SQL C：

SQL D：

表t1实际上在rank1,rank2,rank3三列上分别有一个二级索引。

那我们来看SQL C的查询计划。

显然，没有用到任何索引，扫描的行数为32034，cost为3243.65。

我们加上hint给相同的查询，再次看看查询计划。

这个时候用到了index_merge,union了三个列。扫描的行数为1103，cost为441.09，明显比之前的快了好几倍。

我们再看下SQL D的计划：

不加HINT，

加了HINT，

对比下以上两个，加了HINT的比不加HINT的cost小了100倍。

总结下，就是说表的cardinality值影响这张的查询计划，如果这个值没有正常更新的话，就需要手工加HINT了。相信MySQL未来的版本会带来更多的HINT。

mysql查询优化器应该怎么使用

在开始演示之前，我们先介绍下两个概念。

概念一，数据的可选择性基数，也就是常说的cardinality值。

概念二，关于HINT的使用。

这里我来说下HINT是什么，在什么时候用。

来看下具体演示

譬如，以下两条SQL，

A：

B：

这里顺带说下，MySQL提供了自动更新和手动更新表cardinality值的方法，因篇幅有限，需要的可以查阅手册。

那回到正题上，MySQL 8.0 带来了几个HINT，我今天就举个index_merge的例子。

示例表结构：

表记录数：

这里我们两条经典的SQL：

SQL C：

SQL D：

表t1实际上在rank1,rank2,rank3三列上分别有一个二级索引。

那我们来看SQL C的查询计划。

显然，没有用到任何索引，扫描的行数为32034，cost为3243.65。

我们加上hint给相同的查询，再次看看查询计划。

这个时候用到了index_merge,union了三个列。扫描的行数为1103，cost为441.09，明显比之前的快了好几倍。

我们再看下SQL D的计划：

不加HINT，

加了HINT，

对比下以上两个，加了HINT的比不加HINT的cost小了100倍。

总结下，就是说表的cardinality值影响这张的查询计划，如果这个值没有正常更新的话，就需要手工加HINT了。相信MySQL未来的版本会带来更多的HINT。

什么是索引及MySQL索引原理和慢查询优化

索引目的
索引的目的在于提高查询效率，可以类比字典，如果要查“mysql”这个单词，我们肯定需要定位到m字母，然后从下往下找到y字母，再找到剩下的sql。如果没有索引，那么你可能需要把所有单词看一遍才能找到你想要的，如果我想找到m开头的单词呢？或者ze开头的单词呢？是不是觉得如果没有索引，这个事情根本无法完成？
索引原理
除了词典，生活中随处可见索引的例子，如火车站的车次表、图书的目录等。它们的原理都是一样的，通过不断的缩小想要获得数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，也就是我们总是通过同一种查找方式来锁定数据。
数据库也是一样，但显然要复杂许多，因为不仅面临着等值查询，还有范围查询(>、<、between、in)、模糊查询(like)、并集查询(or)等等。数据库应该选择怎么样的方式来应对所有的问题呢？我们回想字典的例子，能不能把数据分成段，然后分段查询呢？最简单的如果1000条数据，1到100分成第一段，101到200分成第二段，201到300分成第三段……这样查第250条数据，只要找第三段就可以了，一下子去除了90%的无效数据。但如果是1千万的记录呢，分成几段比较好？稍有算法基础的同学会想到搜索树，其平均复杂度是lgN，具有不错的查询性能。但这里我们忽略了一个关键的问题，复杂度模型是基于每次相同的操作成本来考虑的，数据库实现比较复杂，数据保存在磁盘上，而为了提高性能，每次又可以把部分数据读入内存来计算，因为我们知道访问磁盘的成本大概是访问内存的十万倍左右，所以简单的搜索树难以满足复杂的应用场景。
索引的数据结构
前面讲了生活中索引的例子，索引的基本原理，数据库的复杂性，又讲了操作系统的相关知识，目的就是让大家了解，任何一种数据结构都不是凭空产生的，一定会有它的背景和使用场景，我们现在总结一下，我们需要这种数据结构能够做些什么，其实很简单，那就是：每次查找数据时把磁盘IO次数控制在一个很小的数量级，最好是常数数量级。那么我们就想到如果一个高度可控的多路搜索树是否能满足需求呢？就这样，b+树应运而生。

什么是索引及MySQL索引原理和慢查询优化

MySQL数据库优化(七):MySQL如何使用索引

索引用于快速找到特定一些值的记录。如果没有索引，MySQL就必须从第一行记录开始读取整个表来检索记录。表越大，资源消耗越大。如果在字段上有索引的话，MySQL就能很快决定该从数据文件的哪个位置开始搜索记录，而无须查找所有的数据。如果表中有1000条记录的话，那么这至少比顺序地读取数据快100倍。注意，如果需要存取几乎全部1000条记录的话，那么顺序读取就更快了，因为这样会使磁盘搜索最少。
　　大部分MySQL索引(PRIMARY KEY, UNIQUE,INDEX 和 FULLTEXT)都是以B树方式存储。只有空间类型的字段使用R树存储，MEMORY (HEAP)表支持哈希索引。
　　字符串默认都是自动压缩前缀和后缀中的空格。
　　通常，如下所述几种情况下可以使用索引。哈希索引(用于 MEMORY 表)的独特之处在后面会讨论到。
　　想要尽快找到匹配 WHERE 子句的记录。
　　根据条件排除记录。如果有多个索引可共选择的话，MySQL通常选择能找到最少记录的那个索引。
　　做表连接查询时从其他表中检索记录。
　　想要在指定的索引字段 key_col 上找到它的 MIN() 或 MAX() 值。优化程序会在检查索引的
　　key_col 字段前就先检查其他索引部分是否使用了 WHERE key_part_# = constant 子句。这样的话，
　　MySQL会为 MIN() 或 MAX() 表达式分别单独做一次索引查找，并且将它替换成常数。当所有的表达式都被替换成常数后，查询就立刻返回。如下：
　　SELECT MIN(key_part2),MAX(key_part2) FROM tbl_name WHERE key_part1=10;
　　对表作排序或分组，当在一个可用的最左前缀索引上做分组或排序时(如 ORDER
　　BY key_part1, key_part2)。如果所有的索引部分都按照 DESC 排序，索引就按倒序排序。
　　有些时候，查询可以优化使得无需计算数据就能直接取得结果。当查询使用表中的一个数字型字段，且这个字段是索引的最左部分，则可能从索引树中能很快就取得结果：
　　SELECTkey_part3FROMtbl_nameWHEREkey_part1=1
　　假设有如下 SELECT 语句：
　　如果在 col1 和 col2 上有一个多字段索引的话，就能直接取得对应的记录了。如果在 col1 和 col2 分别有独立的索引，那么优化程序会先找到*最多的那个索引，然后根据哪个索引能找到更少的记录就决定使用哪个索引。

MySQL数据库优化(七):MySQL如何使用索引

怎么进行mysql数据库优化?

有八个方面可以对mysql进行优化：
1、选取最适用的字段属性
MySQL可以很好的支持大数据量的存取，但是一般说来，数据库中的表越小，在它上面执行的查询也就会越快。因此，在创建表的时候，为了获得更好的性能，我们可以将表中字段的宽度设得尽可能小。
2. 使用连接（JOIN）来代替子查询(Sub-Queries)
MySQL从4.1开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。
3、使用联合(UNION)来代替手动创建的临时表
MySQL从4.0的版本开始支持union查询，它可以把需要使用临时表的两条或更多的select查询合并的一个查询中。在客户端的查询会话结束的时候，临时表会被自动删除，从而保证数据库整齐、高效。
4、事务
尽管我们可以使用子查询（Sub-Queries）、连接（JOIN）和联合（UNION）来创建各种各样的查询，但不是所有的数据库操作都可以只用一条或少数几条SQL语句就可以完成的。更多的时候是需要用到一系列的语句来完成某种工作。但是在这种情况下，当这个语句块中的某一条语句运行出错的时候，整个语句块的操作就会变得不确定起来。设想一下，要把某个数据同时插入两个相关联的表中，可能会出现这样的情况：第一个表中成功更新后，数据库突然出现意外状况，造成第二个表中的操作没有完成，这样，就会造成数据的不完整，甚至会破坏数据库中的数据。要避免这种情况，就应该使用事务，它的作用是：要么语句块中每条语句都操作成功，要么都失败
5、锁定表
尽管事务是维护数据库完整性的一个非常好的方法，但却因为它的独占性，有时会影响数据库的性能，尤其是在很大的应用系统中。由于在事务执行的过程中，数据库将会被锁定，因此其它的用户请求只能暂时等待直到该事务结束。其实，有些情况下我们可以通过锁定表的方法来获得更好的性能。
6、使用外键
锁定表的方法可以维护数据的完整性，但是它却不能保证数据的关联性。这个时候我们就可以使用外键。
7、使用索引
索引是提高数据库性能的常用方法，它可以令数据库服务器以比没有索引快得多的速度检索特定的行，尤其是在查询语句当中包含有MAX(),MIN()和ORDERBY这些命令的时候，性能提高更为明显。
8、优化的查询语句
绝大多数情况下，使用索引可以提高查询的速度，但如果SQL语句使用不恰当的话，索引将无法发挥它应有的作用。

怎么进行mysql数据库优化?

mysql调优的几种方式

MySQL调优是许多网站和公司的重要任务之一。MySQL是一个非常流行的关系型数据库管理系统，因此对其进行优化可以提高网站的性能和响应时间。下面介绍一些MySQL调优的常用方法：
　　1. 使用索引：索引是MySQL中一种优化查询速度的技术。在处理大量数据时，索引可以显著提高查询速度。要使用索引，需要在数据库表中添加索引，以便快速查找数据。
　　2. 优化查询：查询是数据库中最常用的操作之一，因此需要对查询进行优化，以提高查询速度。可以通过避免使用通配符、优化查询语句和减少JOIN操作等方式来优化查询。
　　3. 调整缓存：MySQL有多个缓存机制，包括查询缓存、表缓存和连接池等。调整这些缓存可以提高MySQL的性能。例如，增加查询缓存的大小可以提高查询速度。
　　4. 调整服务器参数：可以通过调整MySQL服务器参数来优化性能。例如，可以增加缓冲区大小、调整线程池大小、调整日志输出等。
　　5. 升级硬件：如果MySQL在大量并发请求下表现不佳，可以考虑升级硬件。例如，可以增加内存、增加CPU核心数、使用SSD等。
　　以上是MySQL调优的一些常用方法，但需要注意的是，调优并非一劳永逸的事情。随着网站的发展和数据量的增加，需要不断地对MySQL进行调优和优化，以保证网站的高性能和稳定性。

mysql调优的几种方式

mysql的分区表和索引对查询性能优化有何区别

谈点我的看法
分区表：可以想象为磁盘的多个分区一样，可以减少全盘扫描的可能。直接定位到某个分区表上
类似要在电脑上找文件，直接到c盘，防止在D E F盘扫描一样，从而减少io压力，提升性能。在查询上分区表表现还不是十分突出，但是删除，作数据迁移的时候就很明显了。

索引：索引在查询上可以快速定位符合要求的纪录，查询通过索引，也可以防止全表扫描，类似直接定位excel里面的行号一样。但是索引维护对insert update影响必须要考虑到

简单点理解2者区别，从数据库角度来看，分区表更侧向于片状范围划定，索引更趋向于线性定位

mysql的分区表和索引对查询性能优化有何区别

mysql数据库的优化方法？

我们都知道，服务器数据库的开发一般都是通过java或者是PHP语言来编程实现的，而为了提高我们数据库的运行速度和效率，数据库优化也成为了我们每日的工作重点，今天，昌平IT培训就一起来了解一下mysql服务器数据库的优化方法。

为什么要了解索引

真实案例

案例一：大学有段时间学习爬虫，爬取了知乎300w用户答题数据，存储到mysql数据中。那时不了解索引，一条简单的“根据用户名搜索全部回答的sql“需要执行半分钟左右，完全满足不了正常的使用。

案例二：近线上应用的数据库频频出现多条慢sql风险提示，而工作以来，对数据库优化方面所知甚少。例如一个用户数据页面需要执行很多次数据库查询，性能很慢，通过增加超时时间勉强可以访问，但是性能上需要优化。

索引的优点

合适的索引，可以大大减小mysql服务器扫描的数据量，避免内存排序和临时表，提高应用程序的查询性能。

索引的类型

mysql数据中有多种索引类型，primarykey，unique，normal，但底层存储的数据结构都是BTREE;有些存储引擎还提供hash索引，全文索引。

BTREE是常见的优化要面对的索引结构，都是基于BTREE的讨论。

B-TREE

查询数据简单暴力的方式是遍历所有记录;如果数据不重复，就可以通过组织成一颗排序二叉树，通过二分查找算法来查询，大大提高查询性能。而BTREE是一种更强大的排序树，支持多个分支，高度更低，数据的插入、删除、更新更快。

现代数据库的索引文件和文件系统的文件块都被组织成BTREE。

btree的每个节点都包含有key，data和只想子节点指针。

btree有度的概念d>=1。假设btree的度为d，则每个内部节点可以有n=[d+1，2d+1)个key，n+1个子节点指针。树的大高度为h=Logb[(N+1)/2]。

索引和文件系统中，B-TREE的节点常设计成接近一个内存页大小(也是磁盘扇区大小)，且树的度非常大。这样磁盘I/O的次数，就等于树的高度h。假设b=100，一百万个节点的树，h将只有3层。即，只有3次磁盘I/O就可以查找完毕，性能非常高。

索引查询

建立索引后，合适的查询语句才能大发挥索引的优势。

另外，由于查询优化器可以解析客户端的sql语句，会调整sql的查询语句的条件顺序去匹配合适的索引。

MySQL 查询优化（六）： MySQL 的查询优化排序优化机制

对结果进行排序操作的代价可能很高，因此可以通过避免排序或让参与排序的数据行更少来优化查询性能。

当 MySQL 不能使用索引产生有序结果时，它必须对数据行进行排序。这有可能是在内存中进行也可能是在磁盘进行，但 MySQL 始终将这个过程称之为 filesort，即便实际上并没有使用一个文件。

如果用于排序的值可以一次性放入排序缓存中，MySQL 可以在内存中使用快排算法进行排序。如果 MySQL 不能在内存中进行排序，则会在磁盘中按块逐块排序。它对每个块使用快排算法，然后在将这些排序好的块合并到结果中。

有两个文件排序（filesort）算法：

很难说哪种算法更有效，对每个算法来说都会有最优和最坏案例。MySQL 在数据表全部列加上用于排序的列的大小不超过 max_length_for_sort_data 时会使用单次遍历算法。可以通过修改这个参数影响排序算法的选择。

需要注意的是，MySQL 的 filesort使用的临时存储空间可能会超出你的预期，这是因为它对每个排序元素都分配了固定大小的存储空间。这些存储空间要足够大以便容下存储最大的元素，而且 VARCHAR这类字段使用的是对应的最大长度。而且，如果使用的是 UTF-8字符集，MuSQL 会对每个字符分配3个字节。结果是，我们会发现那些没怎么优化的查询会导致磁盘上的临时存储空间是数据表自身存储空间的好几倍。

而在对联合查询进行排序时，MySQL 可能会在查询执行过程中执行两次文件排序。如果 ORDER BY 子句只是引用联合查询的第一张表，MySQL 可以先对这个表进行文件排序，然后再处理联合查询。如果是这种情况，在 EXPLAIN 时会在 Extra 字段显示“Using filesort”。而对于其他的排序情况——例如排序不是针对第一张表，或者是 ORDER BY 使用的列对应了不止一个数据表，MySQL 必须使用临时表缓存查询结果，然而在联合查询完成后，再对临时表进行文件排序。在这种情况下，EXPLAIN 会在 Extra 字段显示“Using temorary; Using filesort”。如果包含 LIMIT 约束的话，会发生在文件排序后，因此临时表和文件排序的存储空间可能非常大。

MySQL 5.6在只需要对数据行的子集（例如 LIMIT）进行排序时，引入了一个重大改进。相对于对整个结果集进行排序再返回部分数据，MySQL 有时候会在排序的时候直接丢弃掉不需要的数据行来提高效率。不管怎么样，排序也需要小心使用，很可能会导致存储占用的飙升最终导致系统负荷过大。

显示全文

全部频道

MySQL 索引与查询优化