MySQL索引查询优化技巧 基于InnoDB的存储引擎

MySQL特性

了解MySQL特性将帮助你更好地使用MySQL。 MySQL与其他公共数据库最大的区别在于存储引擎的概念。存储引擎 负责存储和读取数据。不同的存储机器有不同的特点。用户可以根据自己的业务特点选择合适的存储机器，甚至开发新机器。 MySQL的逻辑架构大致如下：

MySQL的默认存储引擎是InnoDB。该存储引擎的主要特点是：

• 支持事务处理
• 支持行级锁定
• 数据存储在表空间中，表空间由一系列数据文件组成
• 使用MVVC（多版本并发控制）机制，实现高并发
• 基于主键的集群索引的表
• 支持热备份

其他常用存储引擎特性概述： ‷ MyISAM：MySQL标准引擎旧版版本。它不支持事务和行级锁。开发者可以手动控制表锁。支持全文索引。事故发生后无法安全恢复。支持压缩表。压缩后的表数据无法修改，但会占用空间。少，可以提高查询性能

还有很多，就不一一列举了。

数据类型优化

数据类型选择原则：

• 选择占用空间小的数据类型
• 选择简单类型

选择不需要的类型都可以做 pes 占用空间小 还节省了硬件资源，如磁盘、内存和 CPU。尝试使用简单类型。如果可以使用int，就不要使用char，因为最后的排序涉及到字符集选择，比使用更复杂。可空列使用更多存储空间。如果在可为空的列上创建索引，MySQL 需要额外的字节来记录它。创建表时，默认可为空，这一点很容易被开发人员忽视。如果要存储的数据没有空值，最好手动将其更改为不可空。

整数类型

整数类型包括：

• tinyint
• smallintint♾ bigint

它们使用 8、16、24、32 和 64 位存储数字.bit，可以表示

可以更改范围内的数字，前面不加符号，即正确 可以表示 double 的数字范围，但不能表示负数。另外，为整数类型指定长度是没有意义的。一旦指定了数据类型，就准确指定了长度。

十进制类型

• float
• double
• 十进制

float 和 double

通常意义上的

和双，前者使用32位来存储数据，后者使用64位来存储数据，和整数一样，没有确定长度的意义。从空间来看。十进制使用每4个字节来表示9个数字，如decimal(18,9)表示数字的长度为18，十进制数。整数部分9位。加上小数点本身，总共占用了9个字符。部分。考虑到小数占用大量空间，且精度计算非常复杂，当数据量较大时，可以考虑使用bigint实数值再保存和读取。数据经历了多次缩放操作。 String类型varchar类型数据实际占用的空间等于字符串的长度加上1或2个用来记录字符串长度的字节（当line-format没有设置为fixed时），varchar很节省空间。当表中列数据的长度为字符串类型时，差异较大时使用varchar比较合适。

固定字符占用的实际空间。当表中字符串数据的长度几乎相同或很短时，图表类型适合。

对应varchar和char分别是varbiner和binary。后者存储二进制字符串。相比前者，后者区分大小写，不需要考虑编码方式，并且执行比较操作更快。

需要注意的是，varchar(5)和varchar(200)在存储“hello”字符串时使用相同的存储空间，并不意味着将varchar长度设置得太大不会影响性能。事实上，MySQL内部的一些计算，比如在内存中创建临时表时（有些查询会导致MySQL自动创建临时表），都会提供固定大小的空间来存储数据。

blob使用二进制字符串存储大文本，text使用字符存储大文本。 InnoDB将使用专用的外部存储区域来存储此类数据。仅指向它们的指针存储在数据行中。这种类型的数据不适合创建索引（你必须直接针对字符串前缀创建），但没有人会这样做。

如果字符串字段重复较多且内容有限，可以使用枚举。 MySQL 在处理枚举时维护一个“数字字符串”表。使用枚举可以节省大量的存储空间。

时间类型

• 年
• 日期
• 时间
• 日期时间
• 时间戳

日期90个精确存储时间最长90秒。 timestamp 存储的是从 1970 年 1 月 1 日午夜开始的秒数，可以表示 2038 年。 占用 4 个字节，其中一半被 datetime 占用。时间戳所代表的时间与时区有关。另外，时间戳列还有一个特点。当执行插入或更新语句时，MySQL会自动将时间戳类型的第一列数据更新为当前时间。许多表都设计有一个名为 UpdateTime 的列。该列使用时间戳是非常合适的。如果系统直到 2038 年才使用，它将自动更新。

主键类型选择

尽可能使用整数。整数占用空间较小，还可以设置自动增长。特别是避免使用GUID、MD5等哈希值字符串作为主键。这样的字符串是高度随机的。由于InnoDB的主键默认是聚集索引列，因此数据存储是分布式的。此外，InnoDB二级索引列默认包含主键列。如果主键太长，辅助索引会占用大量空间。

特殊数据类型

最好使用无符号32位整数来存储IP。 MySQL 提供了函数 inet_aton() 和 inet_ntoa() 在 IP 地址的数字表示形式和字符串表示形式之间进行转换。 “data-src =”https://user-gold-cdn.xitu.io/2020/7/6/173236f438fa0d62?imageView2/0/w/1280/h/960/format/webp/ignore-error/1“ height = "20" data-width = "720" data-height = "422" />

也就是说，索引列的顺序非常重要，如果两个数据行的Name列相同，使用Age列比较大小 如果Age列相同，则使用Number列比较大小。按第一列排序，然后是第二列，最后是第三列。右列的索引不能使用时，也不能在alternative列中查询，否则下一个索引将无法使用。例如，以下 SQL 就是一个正例：

• SELECT * from person where Name ='Abel' and Age = 2 AND Number = 12312
• ♷ from person ='Abel'
• 从人中选择 * WHERE Name 就像 'Abel%'
• 从人中选择 * WHERE = 'Andy'年龄在 11 岁到 20 岁之间
• SELECT * from person ORDER BY NAME
• SELECT * from person ORDER BY NAME, Age
• SELECT * from person GROUP BY Name示例如下：SQLName♽ SELECT * from person where Age = 2
• 从 NAME 类似于“%B”的人中选择 *
• 从年龄 = 2 的人中选择 * 从 NAME = 'ABC' 且数字 = 3 的人中选择 *
• 从年龄 = 2 的人中选择 *
• 从年龄 = 2 的人中选择 * NAME 就像 'B%' 且年龄 = 22

利用 Hash 值创建索引的技巧

如果表中有一列存储了一个长字符串，假设名称是一个 URL，则在该列中索引制作的比较大。有一种方法可以缓解这种情况：根据 URL 字符串的数字哈希值创建索引。新建一个字段，比如叫URL_CRC，专门用来放URL的哈希值，然后为这个字段创建索引。询问的时候这样写：

select * from t where URL_CRC = 387695885 and URL = 'www.baidu.com'

如果数据量比较大，为了避免hash冲突，可以配置hash函数，或者使用MD5函数的部分返回值是哈希值：

SELECT CONV(RIGHT(MD5('www.baidu.com'),16), 16, 10)

前缀索引

如果字符串列中存储的数据很长，创建的索引也很大，可以使用前缀索引，即：只对字符串的第一部分某些字符建立索引，可以缩短索引的大小，但显然当执行 order by 和 group by 时，这种索引不起作用。

创建前缀索引时选择前缀长度很重要。选择尽可能短的前缀而不扭曲原始数据的分布。例如，如果大多数字符串都以“abc”开头，那么如果将前缀索引的长度限制为4，则索引值将包含太多重复的“abcX”。

多列索引

上面提到的“人物”上创建的索引是多列索引。多列索引通常比多个单列索引更好。

• 对多个索引进行AND查询时，应该创建多列索引而不是单列索引
• 可以尝试这样写：

select * from t where f1 = 'v1 ' and f2 'v2' union all select * from t where f2 = 'v2' and f1 'v1'

多列索引的顺序很重要。一般来说，在不考虑排序和分组查询的情况下，首先放置选择性（选择性是指某个表索引列中不同数据的数量/总行数。选择性高意味着重复数据少）和较大的列。但也有例外。如果您可以确认某些查询正在频繁执行，则应优先考虑这些查询的选择性。例如，如果上面的People表中Name的选择性大于Age，那么查询语句就应该这样写：

select * from people who name = 'xxx' andage = xx

列Name放在左边索引上，但是如果某个特定的SQL执行评价级别最高，比如

select * from person who name = 'xxx' andage = 20

有几条带有age的记录时= 20 在数据库中，将age放在索引列的左侧会更高效。把age放在索引的左边，对于其他age不等于20的查询来说可能不公平。如果你不确定age=20是最频繁的查询条件，你应该全面考虑一下，把名字在左侧。 。

聚簇索引

聚簇索引是一种数据存储结构。 InnoDB直接将数据行存储在主键索引的叶子节点中，而不是像二级索引那样只存储索引列值和所有索引列。显示行中的主键值。由于这一特性，一张表只能有一个聚集索引。如果表没有定义主键或具有唯一索引的列，InnoDB将生成一个隐藏列并将该列设置为聚集索引列。

覆盖索引

简单来说，有些查询只需要查询索引列，因此不需要根据索引B树节点记录的主键ID进行二次查询。

重复索引和冗余索引

如果在列上重复创建索引，不会带来好处，只会带来坏处，应该避免。例如，不需要为主键创建唯一索引和普通索引，因为InnoDB的主键默认是聚集索引。

冗余索引与重复索引不同。例如，一个索引（A，B）和另一个索引（A）。这称为冗余索引。前者可以替代后者，但后者不能替代前者。 。然而，(A,B)和(B)以及(A,B)和(B,A)都不是冗余索引，任何人都无法在可以的情况下替换另一个。

如果表中已经存在索引（A），现在要创建索引（A，B），则只需扩展现有索引即可，无需创建新索引。需要注意的是，如果索引(A)已经存在，则不必创建索引(A，ID)，其中ID指主键，因为索引A默认已经包含主键，这也是多余的。首要的关键。

然而，有时，冗余索引也是可取的。假设有一个索引(A)，将其添加到(A,B)之后，由于B列是一个很长的类型，所以查询A本身时就没有以前那么快了。此时，可以考虑创建一个新的索引（A，B）。

未使用的索引

未使用的索引徒劳增加插入、更新、删除的效率，应及时删除

索引使用总结 ♽ -star

索引会询问相关备注 如果合并后，您将获得一颗星

首先这个原则是指条件中查询的顺序适合索引，即索引是从左到右使用的。如前面提到的。

索引不是万能的。当数据量很大时，维护索引也是性能密集型的。您应该考虑分区和表存储。

查询优化

查询慢的原因

是否向数据库请求额外行

例如应用只需要10张数据，却向数据库请求数据以及所有数据。它在UI中之前的大部分数据都被丢弃了。

是否从数据库请求额外的列

例如，应用程序应该只显示5列，但通过select * from检查所有列

同一查询♿♿是否执行多次。应用程序是否可以考虑一次查询，然后将其缓存，以便第一次查询获得的记录可供后续使用。

MySQL 是否扫描其他记录？

通过查看执行计划，可以大致了解需要扫描多少条记录。如果这个数字超出预期，可以尝试添加索引、优化SQL（本节的重点）、或者改变表结构（比如专门针对某些语句查询添加单独的汇总表）来解决问题。

如何重构查询

• 将复杂查询分解为多个简单查询
• 将大型查询分解为较小的查询。每个查询功能相同，只解决一小部分
• 分解相关查询。可以将相关大查询改为分别查询几张表，然后在应用代码中处理

杂项

优化 count()

Count 有两个函数。一是计算指定的列或表达式。第二个是统计行的数量。如果参数传入列名或表达式，count 将统计结果不为 NULL 的所有行。如果参数为 *，则 count 将计算所有行。下面是传递表达式的示例：

SELECT count(name like 'B%') from person

• 您可以使用近似值优化来替换 count()，例如执行计划中的行号。
• 索引覆盖率扫描
• 添加汇总表
• 增加内存缓存系统中记录的数据记录数量

相关查询的优化

• 与MySQL表相关的查询。例如，有两个表A链接到B，直到c列，MySQL将遍历A表，然后根据传递的c列的值在B表中查找数据。总结一下，通常情况下，如果没有，只需要在表B
• 的c列上添加索引即可，确保order和group中的列只有一张表，这样就可以轮换了。索引角色

优化子查询

对于MySQL 5.5及以下版本，尝试使用连接而不是子查询。

优化group by、distinct

如果可能，尝试将这两个操作应用到主键上。

优化约束，例如，如果你有SQL

SELECT * from sa_stockinfo ORDER BY StockAcc LIMIT 400, 5
复制代码

MySQL优化器会找到第405行中的所有列数据并丢弃400。如果可以使用索引查询覆盖，则不需要询问很多列。先改成：

SELECT * FROM sa_stockinfo i JOIN (SELECT StockInfoID FROM sa_stockinfo ORDER BY StockAcc LIMIT 400,5)t ON i.StockInfoID = t.StockInfoID
复制代码

StockAcc中有一个索引。该查询将使用索引范围快速找到符合条件的主键，然后并发执行查询。当数据量较大时，效果可见。

优化union

如有必要，请务必使用关键字union all，这样MySQL在将数据放入临时表时不会进行唯一性验证

判断某条记录是否存在，通常的做法是

select count(*) from t where condition
复制代码

最好这样写：

SELECT IFNULL((SELECT 1 from tableName where condition LIMIT 1),0)

作者：PHP高级架构师
链接：https://juejin.im/post/6847902218700619789 版权归作者所有。商业转载请联系作者获取授权。非商业转载请注明出处。