mysql索引和主键的区别_MySQL 索引和 SQL 调优手册

MySQL索引

MySQL支持多种存储引擎，各种存储引擎对索引的支持也不同。因此，MySQL数据库支持多种索引类型，比如BTree索引、哈希索引、全文索引等。为了避免混淆，本文将只关注BTree索引，因为这是你主要处理的索引使用MySQL时。

MySQL官方对索引的定义是：索引（Index）是一种帮助MySQL高效获取数据的数据结构。通过提取句子主干，就可以得到索引的本质：索引是一种数据结构。

MySQL索引原理

索引目的

索引的目的是提高查询效率，可以类比字典。如果我们要查找“mysql”这个词，肯定需要找到m字母，然后从下到下找到y字母，然后找到剩下的sql。如果没有索引，那么您可能需要查看所有单词才能找到您想要的内容。如果我想查找以 m 开头的单词怎么办？或者以 ze 开头的单词呢？你是不是觉得没有索引，这件事就根本无法完成？

当我们去图书馆借书时也是如此。如果你想借某本书，首先要找到对应的分类主题，然后找到对应的编号。这是生活中活生生的例子。通用索引可以加快查询速度。位置。

指标原理

所有指标的原理都是一样的。通过不断缩小想要获取的数据范围，可以筛选出最终想要的结果，同时，将随机事件变成顺序事件，即我们始终使用相同的搜索方法来锁定数据。

数据库也是如此，但显然要复杂得多，因为它不仅面临等值查询，还面临范围查询(>,,3和d=4。如果按照(a ，b，c，d），d是必须的吗？对于索引来说，如果创建了（a，b，d，c）的索引，则a，b，d的顺序可以任意调整。

2.=和in可以乱序，如a=1、b=2、c=3。(a,b,c)索引可以按任意顺序建立。 MySQL的查询优化器会帮助你优化成索引可以识别的形式。

3、尽量选择差异化程度高的列作为指标。区分的公式是count(col)/count(*)，代表不重复字段的比例。比例越大，我们扫描的记录就越少，唯一键的区分也就越少。是1，有些身份、性别字段在大数据面前可能会有0的区分。那么有人可能会问，这个比例有没有经验价值呢？不同的使用场景使得这个值很难确定。一般我们要求需要join的字段在0.1以上，即平均每条扫描10条记录。

4、索引列不能参与计算。保持色谱柱“清洁”。例如，如果() = '2014-05-29'，则不能使用索引。原因很简单。 b+树中存储的所有字段值都是数据表中的字段值，但是检索时需要对所有元素应用函数来进行比较，这显然成本太高。所以该语句应该写成 = ('2014-05-29');

5. 尽可能扩展索引，不要创建新索引。比如表中已经有a的索引，现在想为(a,b)添加索引，那么只需要修改原来的索引即可。当然，你必须考虑原始数据和在线使用情况。

MySQL优化配置优化

配置优化是指MySQL的配置。一般来说，业务方不需要关注。毕竟会有专门的DBA来处理。但是，我认为我们需要了解我们发展的原则。

基本配置

尺寸

这是安装后应设置的第一个选项。缓冲池是缓存数据和索引的地方：值越高越好，确保您使用内存而不是硬盘进行大多数读取操作。典型值为5-6GB（8GB内存）、20-25GB（32GB内存）、100-120GB（128GB内存）。

这是重做日志的大小。重做日志记录用于确保写入操作快速可靠，并且可以从崩溃中恢复。在 MySQL 5.1 之前，调优很困难，因为一方面您希望将其变大以提高性能，另一方面您希望将其变小以更快地从崩溃中恢复。

幸运的是，从 MySQL 5.5 开始，崩溃恢复性能有了很大的提升，让你可以同时拥有较高的写入性能和崩溃恢复性能。直到MySQL 5.5，重做日志的总大小被限制为4GB（默认情况下可以有2个日志文件）。 MySQL 5.6 对此进行了改进。如果你知道你的应用程序需要频繁写入数据，并且你使用的是MySQL 5.6，那么你可以一开始就设置为4G。

如果您经常看到“太多”错误，那是因为 的值太低。这是很常见的，因为应用程序没有正确关闭数据库连接，并且您需要比默认的 151 个连接更高的值。

当该值设置得较高（例如 1000 或更高）时，一个主要缺点是服务器在运行 1000 或更多活动事务时变得无响应。在应用程序中使用连接池或在 MySQL 中使用进程池可以帮助解决这个问题。

配置

e

此设置告诉您是否需要将所有表的数据和索引存储在共享表空间中 (e = OFF)，或者将每个表的数据放在单独的 .ibd 文件中 (e = ON)。每个表一个文件允许您在删除表时回收磁盘空间。

这对于某些高级功能（例如数据压缩）也是必要的。但它并没有带来任何性能提升。您不希望每个表一个文件的主要场景是如果您有大量表（例如 10k+）。在 MySQL 5.6 中，该属性的默认值为 ON，因此大多数情况下您不需要执行任何操作。在以前的版本中，您必须在加载数据之前将此属性设置为 ON，因为它只影响新创建的表。

默认值为1，表示完全支持ACID特性。当您主要关注数据安全性时（例如在主节点上），此值最合适。但对于磁盘（读写）速度较慢的系统来说，会带来巨大的开销，因为每次都需要额外刷新重做日志的更改。

将其值设置为2会导致可靠性降低（），因为已提交的事务每秒只会刷新一次到重做日志中，但对于某些场景是可以接受的，例如主节点的备份节点。这个值是可以接受的。 。值 0 速度更快，但在系统崩溃时可能会导致部分数据丢失：仅适用于备份节点。

此配置决定如何将数据和日志写入硬盘。一般来说，如果您有一个硬件RAID控制器，并且其独立缓存采用回写机制并具有电池断电保护，则应配置为；否则，在大多数情况下应将其设置为 (默认值) 。是帮助您决定此选项的一个很好的工具。

泽

此配置确定为尚未执行的事务分配的缓存。默认值 (1MB) 通常就足够了，但如果您的事务包含大型二进制对象或大型文本字段，则此缓存将很快填满并触发额外的 I/O 操作。查看状态变量，如果不为0，则增加ze。

其他设置

即使并发性不多，查询缓存也是一个众所周知的瓶颈。最好的选择是从一开始就禁用它，设置 = 0（现在是 MySQL 5.6 中的默认值），并利用其他方法来加速查询：优化索引、添加副本以分散负载或启用额外的缓存（例如或redis）。

如果您为应用程序启用了查询缓存并且没有发现任何问题，那么查询缓存可能对您有用。如果您想禁用它，则需要小心这一点。

如果您希望数据库服务器充当主节点的备份节点，则需要启用二进制日志记录。如果您这样做，请不要忘记将其设置为唯一值。即使只有一台服务器，如果您想要进行时间点数据恢复，此功能（打开二进制日志记录）也很有用：从最近的备份（完整备份）恢复并应用二进制日志中的更改（增量备份） 。

创建后，二进制日志将永久保存。因此，如果您不想耗尽磁盘空间，可以使用 PURGE LOGS 来清除旧文件，或者将其设置为指定多少天后自动清除日志。二进制日志记录并非没有开销，因此如果在非主节点的副本节点上不需要该选项，建议关闭此选项。

当客户端连接数据库服务器时，服务器会进行主机名解析，当DNS速度慢时，建立连接也会很慢。因此，建议在启动服务器而不进行 DNS 查找时关闭该选项。唯一的限制是稍后在 GRANT 语句中只能使用 IP 地址，因此将此设置添加到现有系统时必须小心。

SQL调优

一般需要进行SQL调优，因此如果存在慢查询SQL，系统可能会启用慢查询日志。特别是在线系统，一般都会启用慢查询日志。如果存在慢查询，可以通过日志进行过滤。但是知道有SQL需要优化之后，接下来要做的就是如何优化。

慢查询优化的基本步骤

先运行一下看看是不是真的很慢。注意设置。

where条件单表查询，锁定最小返回记录表。这句话的意思就是将查询语句的WHERE应用到返回记录数最少的表上，然后分别查询单表的每个字段，看看哪个字段的分化程度最高。

检查执行计划是否与1期望一致（从锁定记录较少的表开始查询）

order by limit形式的SQL语句允许先查询已排序的表

了解业务使用场景

添加索引时，参考建索引的几个原则

观察结果不符合预期从0继续分析

常用调优方法

执行计划

在日常工作中，我们有时会运行慢查询来记录一些执行时间较长的SQL语句。找到这些 SQL 语句并不意味着我们就完成了。我们经常使用这个命令来查看这些SQL语句的执行计划。检查SQL语句是否使用索引，是否进行全表扫描。这可以通过命令来检查。

因此我们深入研究MySQL基于成本的优化器，我们还可以详细了解优化器可能考虑的许多访问策略，以及优化器在运行SQL语句时预计会采用哪些策略。

使用时只需在原有基础上添加关键字即可，如下：

mysql> explain select * from servers;
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
| id | select_type | table  | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | servers | ALL  | NULL          | NULL | NULL    | NULL |    1 | NULL  |
+----+-------------+---------+------+---------------+------+---------+------+------+-------+
1 row in set (0.03 sec)

简单解释一下各个字段的含义

特征

实战练习表结构和查询语句

假如有如下表结构

circlemessage_idx_0 | CREATE TABLE `circlemessage_idx_0` (
  `circle_id` bigint(20) unsigned NOT NULL COMMENT '群组id',
  `from_id` bigint(20) unsigned NOT NULL COMMENT '发送用户id',
  `to_id` bigint(20) unsigned NOT NULL COMMENT '指定接收用户id',
  `msg_id` bigint(20) unsigned NOT NULL COMMENT '消息ID',
  `type` tinyint(3) unsigned NOT NULL DEFAULT '0' COMMENT '消息类型',
  PRIMARY KEY (`msg_id`,`to_id`),
  KEY `idx_from_circle` (`from_id`,`circle_id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_bin

通过执行计划分析以下查询语句

mysql> explain select msg_id from circlemessage_idx_0 where  to_id = 113487 and circle_id=10019063  and msg_id>=6273803462253938690  and from_id != 113487 order by msg_id asc limit 30;
+----+-------------+---------------------+-------+-------------------------+---------+---------+------+--------+-------------+
| id | select_type | table               | type  | possible_keys           | key     | key_len | ref  | rows   | Extra       |
+----+-------------+---------------------+-------+-------------------------+---------+---------+------+--------+-------------+
|  1 | SIMPLE      | circlemessage_idx_0 | range | PRIMARY,idx_from_circle | PRIMARY | 16      | NULL | 349780 | Using where |
+----+-------------+---------------------+-------+-------------------------+---------+---------+------+--------+-------------+
1 row in set (0.00 sec)

mysql> explain select msg_id from circlemessage_idx_0 where  to_id = 113487 and circle_id=10019063   and from_id != 113487 order by msg_id asc limit 30;
+----+-------------+---------------------+-------+-----------------+---------+---------+------+------+-------------+
| id | select_type | table               | type  | possible_keys   | key     | key_len | ref  | rows | Extra       |
+----+-------------+---------------------+-------+-----------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | circlemessage_idx_0 | index | idx_from_circle | PRIMARY | 16      | NULL |   30 | Using where |
+----+-------------+---------------------+-------+-----------------+---------+---------+------+------+-------------+
1 row in set (0.00 sec)

问题分析

通过上面两个执行计划我们可以发现，当没有查询条件>=xxx时，检索到的行数少了很多，而且两个查询都使用了索引，而且只使用了主键索引。这意味着该指标应该是不合理的，没有发挥其最佳作用。

分析这个执行计划可以看到，当包含查询条件>=xxx时，行数超过34万行。在这种情况下，就意味着检索次数过多。要么是表真的那么大，要么是索引不合理，没有使用。索引，大多数情况下没有合理利用索引。列中使用的索引也是如此，它可能是 (,to_id) 之一。注意，我们创建表的时候，索引的顺序是在to_id前面的，所以MySQL查询肯定会先使用索引。使用索引后，已经检索到了34万行，由于查询条件大于等于，所以在这个查询条件之后就不能再使用to_id的索引了。

然后看长度16，结合key，可以分析出只使用了一个主键索引。

最后，查看类型值。是范围，也就是说这个查询要么是范围查询，要么是多值匹配。

请注意，!= xxx 等语句不能使用索引。只能使用=xxx，所以id中的索引其实不需要使用。建立索引的时候一定要考虑清楚。

如何优化

既然知道了指标不合理，那么就需要对指标进行分析和调整。一般来说，由于我们要从单表进行查询，所以我们需要能够大致知道单表中有哪些数据以及它当前的量级是多少。

然后开始下一步的分析。由于msgid被设置为主键，因此它必须是全局唯一的。因此， 的数量至少与数据量一样多；那么检索基本上就是检索整个表。我们需要做的优化是尽可能减少索引，减少查询的行数；那么我们需要思考可以查询哪些字段来减少行数呢？例如，在一个表中，属于某个用户的数据会小于msgid查询的行数吗？如果查询一个用户，他属于某个圈子，是不是就更少了？ ETC。

那么根据实际情况分析，单表中命中to_id的行数应该小于命中数。因此，首先要保证to_id的索引可以使用。为此，可以在设置主键时与to_id的顺序进行交互；但是，由于表已经上线，已经有大量数据，业务正在运行。这种情况下修改主键会带来很多问题（当然修改索引也可以）。因此，不建议直接修改主键。

那么，为了保证to_id索引的有效使用，需要创建新的联合索引；那么新联合索引的第一个索引字段必须是to_id。对于这种业务场景，最好添加索引，这样可以快速建立索引；这样就得到了新的联合索引（to_id,）的索引，然后，因为我们需要找到它，为此，我们在此基础上添加它。最终的联合索引为(to_id,,)；这样的话，就可以快速检索到这样的查询语句： where to_id = xxx and = xxx and msgId >= xxx

当然，创建索引并不意味着某个SQL语句需要某个索引，所以创建联合索引即可。这种情况下，如果索引过多，会影响写入性能（插入、删除、修改），存储空间也会受到影响。相应增加；另外，mysql在运行过程中也会消耗资源来维护索引。因此，索引越多越好。需要根据查询最频繁、对性能影响最大的SQL建立合适的索引。需要注意的是，联合索引或者一组主键是一个btree，多个索引是多个btree。

总结

首先，我们需要深入了解索引的原理和实现。只有明白了其中的原理，才能对我们建立合适的索引更有帮助。那么我们在建立索引的时候，不要想当然。我们首先要思考业务逻辑，然后构建相应的表结构和索引。需要再次强调以下几点：

参考