时间：2021-07-01 10:21:17 帮助过：22人阅读
因为索引虽然加快了查询速度，但索引也是有代价的：索引文件本身要消耗存储空间，同时索引会加重插入、删除和修改记录时的负担，另外，MySQL在运行时也要消耗资源维护索引，因此索引并不是越多越好
在使用InnoDB存储引擎时，如果没有特别的需要，请永远使用一个与业务无关的自增字段作为主键。如果从数据库索引优化角度看，使用InnoDB引擎而不使用自增主键绝对是一个糟糕的主意。
InnoDB使用聚集索引，数据记录本身被存于主索引（一颗B+Tree）的叶子节点上。这就要求同一个叶子节点内（大小为一个内存页或磁盘页）的各条数据记录按主键顺序存放，因此每当有一条新的记录插入时，MySQL会根据其主键将其插入适当的节点和位置，如果页面达到装载因子（InnoDB默认为15/16），则开辟一个新的页（节点）。如果表使用自增主键，那么每次插入新的记录，记录就会顺序添加到当前索引节点的后续位置，当一页写满，就会自动开辟一个新的页。如下：
这样就会形成一个紧凑的索引结构，近似顺序填满。由于每次插入时也不需要移动已有数据，因此效率很高，也不会增加很多开销在维护索引上。
如果使用非自增主键（如果身份证号或学号等），由于每次插入主键的值近似于随机，因此每次新纪录都要被插到现有索引页得中间某个位置，如下：
此时MySQL不得不为了将新记录插到合适位置而移动数据，甚至目标页面可能已经被回写到磁盘上而从缓存中清掉，此时又要从磁盘上读回来，这增加了很多开销，同时频繁的移动、分页操作造成了大量的碎片，得到了不够紧凑的索引结构，后续不得不通过OPTIMIZE TABLE来重建表并优化填充页面。
因此，只要可以，请尽量在InnoDB上采用自增字段做主键。
最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式
尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录
索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);
尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可，当然要考虑原有数据和线上使用情况
配置优化指的MySQL 的 server端的配置，一般对于业务方而言，可以不用关注，毕竟会有专门的DBA来处理，但是对于原理的了解，我想，我们开发，是需要了解的
一般要进行SQL调优，那么就说有慢查询的SQL，系统或者server可以开启慢查询日志，尤其是线上系统，一般都会开启慢查询日志，如果有慢查询，可以通过日志来过滤。但是知道了有需要优化的SQL后，下面要做的就是如何进行调优
在日常工作中，我们有时会开慢查询去记录一些执行时间比较久的SQL语句，找出这些SQL语句并不意味着完事了，我们常常用到explain这个命令来查看一个这些SQL语句的执行计划，查看该SQL语句有没有使用上了索引，有没有做全表扫描，这都可以通过explain命令来查看。所以我们深入了解MySQL的基于开销的优化器，还可以获得很多可能被优化器考虑到的访问策略的细节，以及当运行SQL语句时哪种策略预计会被优化器采用。
使用explain 只需要在原有select 基础上加上explain关键字就可以了，如下：
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)
复制代码简要解释下explain各个字段的含义
EXPLAIN的特性
假如有如下表结构
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
复制代码通过执行计划explain分析如下查询语句
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)
复制代码通过上面两个执行计划可以发现当没有msg_id >= xxx这个查询条件的时候，检索的rows要少很多，并且两者查询的时候都用到了索引，而且用到的还只是主键索引。那说明索引应该是不合理的，没有发挥最大作用。
分析这个执行计划可以看到，当包含msg_id >= xxx 查询条件的时候，rows有34w多行，这种情况，说明检索太多，要么就是表里面确实有这么大，要么就是索引不合理没有用到索引，大都情况是没用合理用到索引。列中所用到的索引也是PRIMARY，那就可能是(msg_id,to_id)的其中一个，注意我们建立表的时候msg_id索引的顺序是在to_id前面的，因此MySQL查询一定会优先用msg_id索引，在使用了msg_id索引后，就已经检索出了34w行，并且由于msg_id的查询条件是大于等于，因此，再这个查询条件后，就不能再用到to_id的索引。
然后再看key_len长度为16，结合 key为PRIMARY，那么可以分析得知，只有一个主键索引被用到。
最后看看 type 值，是range，那么就说明这个查询要么是范围查询，要么就是多值匹配。
请注意，from_id != xxx 这样的语句，是无法用到索引的。 只有from_id = xxx就可以用到所以，因此from id 的索引其实可以不用，建立索引的时候就要考虑清楚
既然知道索引不合理，那么就要分析并调整索引。一般而言，我们既然要从单表里面查询，那么就需要能够知道大体，单表里面大致会有哪些数据，现在的量级大概是多少。
然后开始下一步的分析，既然msgid是被设置为了主键，那一定是全局唯一的，所有，有多少数据量就至少会有多少条msgid；那么检索msg_id基本就是检索整个表了。我们要做的优化就是要尽量减少索引，减少查询的行数；那么就需要思考，通过查询哪些字段才能够减少行数？比如，一个张表里面，所属某个用户的数据，会不会比查询msgid的行数要少？ 查询某个用户并且是属于某个圈子的，那会不会就更少了？ 等等。。。
然后根据实际情况分析，单表里面命中to_id 的行数应该是会小于命中msg_id的，因此要首先保证能够使用到to_id的索引，为此，可以设置主键的时候把msg_id和to_id的顺序交互一下；但是，由于已经是线上的表，已经有了大量数据，并且业务开始运行，这种情况下，修改主键会引发很多问题（当然修改索引是OK的），因此，不建议直接修改主键。那么，为了保证有效使用to_id的索引，就要新建一个联合索引；那么新建的联合索引的第一索引字段必然是to_id，针对此业务场景，最好能够再加上circle_id索引，这样可以快速索引；这样就得到了新的联合索引(to_id,circle_id)的索引，然后，因为要找msg_id，为此，在此基础上，再加上msg_id。最终得到的联合索引为(to_id,circle_id,msg_id)；这样的话，就能够快速检索这样的查询语句了：where to_id = xxx and circle_id = xxx and msgId >= xxx
当然，索引的建立，也不是说某个sql 语句需要啥索引，就建立某个联合索引，这样的话，索引太多的话，写的性能受影响（插入、删除、修改），然后存储空间也会相应增大；另外mysql在运行时也会消耗资源维护索引，所以，索引并不是越多越好，需要结合查询最频繁、最影响性能的sql来建立合适的索引。需要再说明的是，一个联合索引或者一组主键就是一个btree，多个索引就是多个btree
首先我们需要深入理解索引的原理和实现，当理解了原理后，才能够更有助于我们建立合适的索引。然后我们建立索引的时候，不要想当然，要先想清楚业务逻辑，再建立对应的表结构和索引。 需要再次强调如下几点:
链接：https://juejin.im/post/5a6873fbf265da3e393a97fa
MySQL索引和SQL调优
标签：做了 分析 code 你知道 commit mit off exp 关于