如何高效地利用MySQL索引

1、要想高效利用索引，我们首先要考虑如何正确建立索引。

（1）在经常做搜索的列上，也就是WHERE子句里经常出现的列，考虑加上索引，加快搜索速度。

（2）唯一标识记录的列，应该加上唯一索引，强制该列的唯一性并且加快按该列查找记录的速度。

（3）在内连接使用的列上加上索引，最好是在内连接用到字段都加上，因为MySQL优化器会自动地选择连接顺序，然后观察索引的使用情况，将没用的索引删除即可。

（4）在需要排序的列上加上索引，因为索引本身是按顺序的组织的，它可以避免 filesort，要知道，Server层在进行排序时是在内存中进行的，非常消耗资源。

（5）可以考虑实现覆盖索引，即根据 SELECT 的所有字段上创建联合索引，这样存储引擎只用读取索引而不用去回表查询，极大地减少了对数据表的访问，大大地提高了性能。

（6）对于那些选择性很小的列，比如性别列，增加索引并不能明显加快查询速度，反而该索引会成为表的累赘。

（7）对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的要么数据量相当大，要么取值很少。

（8）当对写性能的要求远远大于读性能时，不应该创建索引。写性能和读性能是互相矛盾的。这是因为，维护一个 B+Tree 成本是非常大的，对索引的写会涉及到页的分裂等。

（9）复合索引的几个字段是否经常同时以AND方式出现在Where子句中？单字段查询是否极少甚至没有？如果是，则可以建立复合索引，否则考虑单字段索引。这还是说明，满足查询性能的前提下，索引越少越好。

（10）如果复合索引所包含的字段超过3个，那么仔细考虑其必要性，考虑减少复合的字段。

（11）在用于GROUP BY的列上加上索引，避免使用临时表。

（12）对于较长的字符列，如 char、varchar等，由于字符串的比较相对来说非常耗时，因此考虑使用前缀索引减少索引长度，或者创建自定义哈希索引，将字符串映射成整数，然后以该整数作为索引，同时以字符串的值作为过滤条件。

我们在创建索引时，可以根据下面原则进行简单判断：索引是否将相关记录集合到了一起，从未减少了磁盘I/O，加快搜索速度？索引中数据的排列顺序是否和查找的数据的排列顺序一致，从而避免了Server层的排序？索引中的列是否包含了查询中需要的全部列从而实现了覆盖索引？ 这几个条件层层递进，满足得越多越好。

2、索引正确地建立了，我们还需要正确地使用它们：

（1）使用了运算符 !=，以及关键字not in，not exist，>，<等，总之产生的结果集很大时（也在where条件进行大范围的选择时），往往导致引擎不使用索引而是走全盘扫描。因为如果使用索引会造成大量的随机I/O，得不偿失。

（2）如果对索引列进行运算，如 WHERE substr(name, 1, 3)=‘mark’，存储引擎并不能聪明地判断哪些索引满足等式，因此不能使用到索引。

（3）使用到了LIKE，并且通配符在最前面时，不能使用索引。

（4）对于联合索引 (a, b, c)，如果没用到最左列，那么一般情况下都使用不到索引。但是，比如统计 *** 作 count(*) where a >xxx，是可以使用到该联合索引的。毕竟统计这类 *** 作，它不是检索，并不需要索引完全有序。

（5）对于联合索引，如果某个列使用了范围查找，那么其右边的列都无法作为索引优化查询，但是由于 ICP（Index Condition Pushdown），这些列能作为过滤条件在存储引擎中对数据进行过滤。

（6）如果条件中有 OR，则必须每个OR用到的字段都有索引，否则不能使用任何索引。

（7）想在联合查询中使用索引来避免 filesort，则关联查询中的ORDER BY用到的字段必须全部是第一张表（驱动表）上的。

索引是在存储引擎中实现的，也就是说不同的存储引擎，会使用不同的索引。MyISAM和InnoDB存储引擎：只支持BTREE索引，也就是说默认使用BTREE，不能够更换，MySQL5.7中InnoDB可以支持HASH索引；MEMORY/HEAP存储引擎：支持HASH和BTREE索引。索引可划分为单列索引（其中包括普通索引、唯一索引、主键索引）、组合索引、全文索引、空间索引，其中单列索引是一个索引只包含单个列，但一个表中可以有多个单列索引。

MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值，纯粹为了查询数据更快一点。

索引列中的值必须是唯一的，但是允许为空值，

是一种特殊的唯一索引，不允许有空值。

在表中的多个字段组合上创建的索引，只有在查询条件中使用了这些字段的左边字段时，索引才会被使用，使用组合索引时遵循最左前缀集合。

由id、name和age3个字段构成的索引，索引行中就按id/name/age的顺序存放，索引可以索引下面字段组合(id，name，age)、(id，name)或者(id)。如果要查询的字段不构成索引最左面的前缀，那么就不会是用索引，比如，age或者（name，age）组合就不会使用索引查询

全文索引，只有在MyISAM引擎上才能使用，只能在CHAR,VARCHAR,TEXT类型字段上使用全文索引。全文索引就是在一堆文字中，通过其中的某个关键字等，就能找到该字段所属的记录行，比如有"你是个大牛，神人 ..." 通过大牛，可能就可以找到该条记录。这里说的是可能，因为全文索引的使用涉及了很多细节，我们只需要知道这个大概意思。

只有在MyISAM引擎上才能使用，空间索引是对空间数据类型的字段建立的索引，MySQL中的空间数据类型有四种，GEOMETRY、POINT、LINESTRING、POLYGON。

在创建空间索引时，使用SPATIAL关键字。

创建空间索引的列，必须将其声明为NOT NULL。。

SPATIAL INDEX spatIdx(g)

全值匹配我最爱，最左前缀要遵守；

带头大哥不能死，中间兄弟不能断；

索引列上少计算，范围之后全失效；

Like百分写最右，覆盖索引不写星；

不等空值还有or，索引失效要少用；

VAR引号不可丢，SQL高级也不难！

参考： <u>https://blog.csdn.net/zjy15203167987/article/details/81812370</u>

参考： <u>https://www.jianshu.com/p/d5b2f645d657</u>

如果索引包含满足查询的所有数据，就称为覆盖索引。覆盖索引是一种非常强大的工具，能大大提高查询性能。只需要读取索引而不用读取数据有以下一些优点：

(1) 索引项通常比记录要小，所以MySQL访问更少的数据；

(2) 索引都按值的大小顺序存储，相对于随机访问记录，需要更少的I/O；

(3) 大多数据引擎能更好的缓存索引。比如MyISAM只缓存索引。

(4) 覆盖索引对于InnoDB表尤其有用，因为InnoDB使用聚集索引组织数据，如果二级索引中包含查询所需的数据，就不再需要在聚集索引中查找了。

覆盖索引不能是任何索引，只有B-TREE索引存储相应的值。而且不同的存储引擎实现覆盖索引的方式都不同，并不是所有存储引擎都支持覆盖索引(Memory和Falcon就不支持)。

对于索引覆盖查询(index-covered query)，使用EXPLAIN时，可以在Extra一列中看到“Using index”。

产品中有一张图片表，数据量将近100万条，有一条相关的查询语句，由于执行频次较高，想针对此语句进行优化。表结构很简单，主要字段：

user_id 用户ID

picname 图片名称

smallimg 小图名称

一个用户会有多条图片记录，现在有一个根据user_id建立的索引：uid，查询语句也很简单。取得某用户的图片集合

执行查询语句（为了查看真实执行时间，强制不使用缓存）

执行了10次，平均耗时在40ms左右。使用explain进行分析

使用了user_id的索引，并且是const常数查找，表示性能已经很好了

因为这个语句太简单，sql本身没有什么优化空间，就考虑了索引。修改索引结构，建立一个(user_id,picname,smallimg)的联合索引：uid_pic。重新执行10次，平均耗时降到了30ms左右。使用explain进行分析

看到使用的索引变成了刚刚建立的联合索引，并且Extra部分显示使用了'Using Index'

'Using Index'的意思是“覆盖索引”，它是使上面sql性能提升的关键。一个包含查询所需字段的索引称为“覆盖索引”，MySQL只需要通过索引就可以返回查询所需要的数据，而不必在查到索引之后进行回表 *** 作，减少IO，提高了效率。

例如上面的sql，查询条件是user_id，可以使用联合索引，要查询的字段是picname smallimg，这两个字段也在联合索引中，这就实现了“覆盖索引”，可以根据这个联合索引一次性完成查询工作，所以提升了性能

InnoDB存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面带来的性能损耗可能比表级锁定要更高一些，但是在整体并发处理能力方面是要远远优于MyISAM的表级锁定的。当系统并发量较高的时候，InnoDB的整体性能和MyISAM相比就会有比较明显的优势了。但是当我们使用不当的时候，可能会让InnoDB的整体性能表现不仅不比MyISAM高，甚至可能会更差。

建议：

（1）尽可能让所有的数据检索都通过索引来完成，从而避免InnoDB因为无法通过索引键加锁而升级为表级锁定

（2）合理设计索引，让InnoDB在索引键上面加锁的时候尽可能准确，尽可能地缩小锁定范围，避免造成不必要的锁定而影响其他Query的执行

（3）尽可能减少基于范围的数据检索过滤条件，避免因为间隙锁带来的负面影响而锁定了不该锁定的记录

（4）尽量控制事务的大小，减少锁定的资源量和锁定时间长度

（5）在业务环境允许的情况下，尽量使用较低级别的事务隔离，以减少MySQL因为实现事务隔离级别所带来的附加成本。

---