mysql优化：覆盖索引（延迟关联）

我们都知道InnoDB采用的B+ tree来实现索引的，索引又分为主键索引(聚簇索引)和普通索引(二级索引)。

在富川等地区，都构建了全面的区域性战略布局，加强发展的系统性、市场前瞻性、产品创新能力，以专注、极致的服务理念，为客户提供网站设计制作、网站建设 网站设计制作按需开发网站,公司网站建设,企业网站建设,品牌网站设计,成都全网营销,成都外贸网站建设,富川网站建设费用合理。

那么我们就来看下 基于主键索引和普通索引的查询有什么区别？

举个栗子：

可以看出我们有一个普通索引k，那么两颗B+树的示意图如下：

[图片上传失败...(image-9b05f7-1597911217600)]

（注：图来自极客时间专栏）

当我们查询** select * from T where k=5 其实会先到k那个索引树上查询k = 5，然后找到对应的id为500，最后回表到主键索引的索引树找返回所需数据。

如果我们查询 select id from T where k=5 **则不需要回表就直接返回。

也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。

概念如上，这里我们还是用例子来说明：

/pre

[图片上传失败...(image-20977-1597911217600)]

（注：图来自极客时间专栏）

现在，我们一起来看看这条SQL查询语句的执行流程： select * from T where k between 3 and 5

在这个过程中， 回到主键索引树搜索的过程，我们称为回表。 可以看到，这个查询过程读了k索引树的3条记录（步骤1、3和5），回表了两次（步骤2和4）。

在这个例子中，由于查询结果所需要的数据只在主键索引上有，所以不得不回表。那么，有没有可能经过索引优化，避免回表过程呢？

如果执行的语句是select ID from T where k between 3 and 5，这时只需要查ID的值，而ID的值已经在k索引树上了，因此可以直接提供查询结果，不需要回表。也就是说，在这个查询里面，索引k已经“覆盖了”我们的查询需求，我们称为覆盖索引。

由于覆盖索引可以减少树的搜索次数，显著提升查询性能，所以使用覆盖索引是一个常用的性能优化手段。

需要注意的是，在引擎内部使用覆盖索引在索引k上其实读了三个记录，R3~R5（对应的索引k上的记录项），但是对于MySQL的Server层来说，它就是找引擎拿到了两条记录，因此MySQL认为扫描行数是2。

上面介绍了那么多 其实是在为延迟关联做铺垫，这里直接续上我们本次慢查询的sql：

我们都知道在做分页时会用到Limit关键字去筛选所需数据，limit接受1个或者2个参数，接受两个参数时第一个参数表示偏移量，即从哪一行开始取数据，第二个参数表示要取的行数。 如果只有一个参数，相当于偏移量为0。

当偏移量很大时，如limit 100000,10 取第100001-100010条记录，mysql会取出100010条记录然后将前100000条记录丢弃，这无疑是一种巨大的性能浪费。

当有这种写法时，我们可以采用延迟关联来进行优化，重点关注： SELECT id FROM qa_question WHERE expert_id = 69 AND STATUS = 30 ORDER BY over_time DESC LIMIT 0, 10 ， 这里其实利用了索引覆盖，where条件后的expert_id 是有添加索引的，这里查询id 可以避免回表，大大提升效率。

工作中会遇到各种各样的问题，对于一个研发来说最重要的是能够从这些问题中学到什么。好久没有写博客了，究其原因还是自己变得懒惰了。 (￣ェ￣;)

最后以《高性能Mysql》中的一段话结束：

MySQL性能优化之索引设计

上一篇给小伙伴们讲了关于SQL查询性能优化的相关技巧，一个好的查询SQL离不开合理的索引设计。这篇小二就来唠一唠怎么合理的设计一个索引来优化我们的查询速度，要是有不合理的地方...嗯..

当然啦，开个玩笑，欢迎小伙伴们指正！

通常情况下，字段类型的选择是需要根据业务来判断的，通常需要遵循以下几点。

下列各种类型表格内容来自菜鸟教程，权当备忘。

优化建议：

注意： INT(2)设置的为显示宽度，而不是整数的长度，需要配合 ZEROFILL 使用 。

例如 id 设置为 TINYINT(2) UNSIGNED ，表示无符号，可以存储的最大数值为255，其中 TINYINT(2) 没有配合 ZEROFILL 实际没有任何意义，例如插入数字200，长度虽然超过了两位，但是这个时候是可以插入成功的，查询结果同样为200；插入数字5时，同样查询结果为5。

而 TINYINT(2) 配合 ZEROFILL 后，当插入数字5时，实际存储的还是5，不过在查询是MySQL会在前面补上一个0，即查询出来的实际为 05 。

优化建议：

优化建议：

通常来说，考虑好表中每个字段应该使用什么类型和长度，建完表需要做的事情不是马上建立索引，而是先把相关主体业务开发完毕，然后把涉及该表的SQL都拿出来分析之后再建立索引。

尽量少建立单值索引( 唯一索引除外 )，应当设计一个或者两三个联合索引，让每一个联合索引都尽量去包含SQL语句中的 where、order by、group by 的字段，同时确保联合索引的字段顺序尽量满足SQL查询的最左前缀原则。

索引基数是指这个字段在表里总共有多少个不同的值，比如一张表总共100万行记录，其中有个性别字段，性别一共有三个值：男、女、保密，那么该字段的基数就是3。

如果对这种小基数字段建立索引的话，因为索引树中只有男、女、保密三个值，根本没法进行快速的二分查找，同时还需要回表查询，还不如全表扫描嘞。

一般建立索引，尽量使用那些基数比较大的字段，那么才能发挥出B+树快速二分查找的优势来。

在 where 和 order by 出现索引设计冲突时，是优先针对where去设计索引？还是优先针对order by设计索引？

通常情况下都是优先针对 where 来设计索引，因为通常情况下都是先 where 条件使用索引快速筛选出来符合条件的数据，然后对进行筛选出来的数据进行排序和分组，而 where 条件快速筛选出来的的数据往往不会很多。

对生产实际运行过程中，或者测试环境大数据量测试过程中发现的慢查询SQL进行特定的索引优化、代码优化等策略。

终于轮到实战了，小二最喜欢实战了。

写到这里不得不吐槽一下，这个金三银四的跳槽季节，年前提离职了，结果离职还没办完就封村整整两个礼拜了，呜呜呜...

上节小二就提到会有个很有意思的小案例，那么在疫情当下，门都出不去的日子，感觉这个例子更有意思了，咱们来讨论一下各种社交平台怎么做的用户信息搜索呢。

社交平台有一个小伙伴们都喜欢的功能，搜索好友信息，比如小二熟练的点开省份...城市..性别..年龄..身高...

咳咳咳...小二怎么可能干这种事情，小二的心里只有代码，嗯...没错，就是这样。

这个就可以说是对于用户信息的查询筛选了，通常这种表都是非常大数据量的，在不考虑分库分表的情况下，怎么通过索引配合SQL来优化呢？

通常我们在编写SQL是会写出类似如下的SQL来执行，有 where、order by、limit 等条件来查询。

那么接下来小二一个一个慢慢增加字段来分析分析，怎么根据业务场景来设计索引。

针对这种情况，很简单，设计一个联合索引 (provice, city, sex) 就完事了。

那么这时候有小伙伴就会说了，很简单啊，范围字段放最后咱还是知道的，联合索引改成 (provice, city, sex, age) 不就可以了。

嗯，是的，这么干没毛病，但是小伙伴们有没有想过有些人万一既喜欢帅哥又喜欢美女，别想歪了哈...，挺多小姐姐就既喜欢帅哥又喜欢美女的。

那么这个时候小姐姐就不搜索性别了，那么这个时候联合索引只能用到前两个字段了，那么不符合咱们的专业标准啊，咋办呢？这时候还是有办法的，咱们只需要动动小脑袋改改SQL就行了，在没有选择性别时判断一下，改成下面这样就可以了。

咋办嘞，同样往联合索引里面塞，例如 (provice, city, sex, hobby, xx, age) 。

针对这种多个范围查询的话，为了比较好的利用索引，在业务允许的情况下可以使用固定范围，然后数据库字段存储范围标识就可以了，这样就转化为了等值匹配，就可以很好地利用索引了。

例如最后登录时间字段不记录最后登录时间，而是记录设置字段 is_login_within_seven_days 在7天内有登录则为1，否则为0，最后索引设计成 (provice, city, sex, hobby, xx, is_login_within_seven_days, age) 。

那么根据场景最后设计出来的这个索引可能已经可以覆盖大部分的查询流量了，那么如果还有其他一部分热度比较高的查询怎么办呢，办法也很简单啊，再加一两个索引即可。

例如通常会查询这个城市比较受欢迎(评分：score)的小姐姐，这时候添加一个联合索引 (provice, city, sex, score) 那么就可以了。

可以看出，索引时必须结合场景来设计的，思路就是尽量用不超过3个复杂的联合索引来抗住大部分的80%以上的常用查询流量，然后再用一两个二级索引来抗下一些非常用查询流量。

以上就是小二要给大家分享的索引设计，如果能动动你发财的小手给小二点个免费的赞就更好啦~

下篇小二就来讲讲MySQL事务和锁机制。

轻松优化MySQL-之索引优化2 附赠送优化口诀

索引是在存储引擎中实现的，也就是说不同的存储引擎，会使用不同的索引。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;/u

参考： u;/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因为实现事务隔离级别所带来的附加成本。

mysql---索引优化

索引就是为特定的mysql字段进行一些特定的算法排序，比如二叉树的算法和哈希算法，哈希算法是通过建立特征值，然后根据特征值来快速查找。

1.普通索引:(index)最基本的索引，没有任何限制  目的:加快数据的查询速度

2.唯一索引:(unique)  与"普通索引"类似，不同的就是：索引列的值必须唯一，但允许有空值。

3.主键索引(primary key) 它 是一种特殊的唯一索引，不允许有空值。

4.复合索引:index(a,b,c)  为了更多的提高mysql效率可建立组合索引，遵循”最左前缀“原则。

5.全文索引:fulltext  仅可用于 MyISAM 表，针对较大的数据，生成全文索引很耗时耗空间。

第一类是myisam存储引擎使用的叫做b-tree结构，

第二类是innodb存储引擎使用的叫做聚簇结构（也是一种 b-tree）。 如下图：

注意：

1.myisam不需要回行处理 

2.innodb不需要回行处理,直接可以获取数据,因为innodb的储存引擎是包含了数据和索引文件的,其主键索引包含了数据,(唯一索引及普通索是没有直接包含数据的)

1、索引列不能参与计算

有索引列参与计算的查询条件对索引不友好（甚至无法使用索引），如from_unixtime(create_time) = '2014-05-29'。

原因很简单，如何在节点中查找到对应key？如果线性扫描，则每次都需要重新计算，成本太高；如果二分查找，则需要针对from_unixtime方法确定大小关系。

因此，索引列不能参与计算。上述from_unixtime(create_time) = '2014-05-29'语句应该写成create_time = unix_timestamp('2014-05-29')。

2、最左前缀匹配

如有索引(a, b, c, d)，查询条件a = 1 and b = 2 and c 3 and d = 4，则会在每个节点依次命中a、b、c，无法命中d。也就是最左前缀匹配原则。

3、冗余和重复索引

冗余索引是指在相同的列上按照相同的顺序创建的相同类型的索引，应当尽量避免这种索引，发现后立即删除。比如有一个索引(A,B)，再创建索引(A)就是冗余索引。冗余索引经常发生在为表添加新索引时，比如有人新建了索引(A,B)，但这个索引不是扩展已有的索引(A)

4、避免多个范围条件

    select user.* from user where login_time '2017-04-01' and age between 18 and 30;

比如想查询某个时间段内登录过的用户：它有两个范围条件，login_time列和age列，MySQL可以使用login_time列的索引或者age列的索引，但无法同时使用它们 .

5、覆盖索引 (能扩展就不新建)

如果一个索引包含或者说覆盖所有需要查询的字段的值，那么就没有必要再回表查询，这就称为覆盖索引。覆盖索引是非常有用的工具，可以极大的提高性能，因为查询只需要扫描索引会带来许多好处：

1.索引条目远小于数据行大小，如果只读取索引，极大减少数据访问量2.索引是有按照列值顺序存储的，对于I/O密集型的范围查询要比随机从磁盘读取每一行数据的IO要少的多

6、选择区分度高的列作索引

如，用性别作索引，那么索引仅能将1000w行数据划分为两部分（如500w男，500w女），索引几乎无效。

区分度的公式是count(distinct ) / count(*)，表示字段不重复的比例，比例越大区分度越好。唯一键的区分度是1，而一些状态、性别字段可能在大数据面前的区分度趋近于0。

7、删除长期未使用的索引

场景一(覆盖索引 5)

索引应该建在选择性高的字段上（键值唯一的记录数/总记录条数），选择性越高索引的效果越好、价值越大，唯一索引的选择性最高；

组合索引中字段的顺序，选择性越高的字段排在最前面；

where条件中包含两个选择性高的字段时，可以考虑分别创建索引，引擎会同时使用两个索引（在OR条件下，应该说必须分开建索引）；

不要重复创建彼此有包含关系的索引，如index1(a,b,c) 、index2(a,b)、index3(a)；

组合索引的字段不要过多，如果超过4个字段，一般需要考虑拆分成多个单列索引或更为简单的组合索引；

不要滥用索引。因为过多的索引不仅仅会增加物理存储的开销，对于插入、删除、更新操作也会增加处理上的开销，而且会增加优化器在选择索引时的计算代价。

因此太多的索引与不充分、不正确的索引对性能都是毫无益处的。一言以蔽之，索引的建立必须慎重，对每个索引的必要性都应该经过仔细分析，要有建立的依据。

mysql--索引优化

索引覆盖是指如果查询的列恰好是索引的一部分,那么查询只需要在索引文件上进行,不需要回行到磁盘再找数据。这种查询速度非常快,称为”索引覆盖”

    1查询频繁    2区分度高    3长度小    4尽量能覆盖常用查询字段

索引长度直接影响索引文件的大小,影响增删改的速度,并间接影响查询速度(占用内存多)。因此对于一些长短不同的字节，我们会针对列中的值,从左往右截取部分,来建索引。但是：

1:截的越短, 重复度越高,区分度越小, 索引效果越不好

2:截的越长, 重复度越低,区分度越高, 索引效果越好,但带来的影响也越大--增删改变慢,并间影响查询速度.

所以,我们要在  区分度 + 长度  两者上,取得一个平衡（ distinct 去重 ）

    select count (distinct left (word,6)) / count (*) from tablename;

对于一般的系统应用区别度能达到 0.1 ,索引的性能就可以接受.

    alter table tablename add index word(word(4));

给字符串类型的字段建立索引效率不高，但是必须要经常查这个字段怎么建索引？

比如说一个字段url，类型是字符串。那么可以建一个字段 crcurl 来存储url字段crc32后的值，并给 crcurl 建立索引。

   crc32：循环冗余校验。根据网上数据包或计算机文件等数据产生简短固定位数校验码的一种散列函数，主要用来检测或校验数据传输或者保存后可能出现的错误。生成的数字在传输或者存储之前计算出来并且附加到数据后面，然后接收方进行检验确定数据是否发生变化。一般来说，循环冗余校验的值都是32位的整数。

crc32 是整形，在MySQL中，给整形字段建立索引效率比较高，crc32虽然不能确保唯一性，但是无碍，相同的机率也是极小，关键是可以大大减少查询的范围，给crcurl这个字段建立索引，查询的时候带上crcurl字段就可以利用到索引。

        不允许翻过100页（百度搜索一般到70页左右） 

首先我们直接大数据分页limit 5000000,10  发现耗时4.41秒

接下来我们转换方式使用where条件查询，只耗时0.02秒

        2次的查询结果不一致，这是因为数据被物理删除过有空洞.，因此我们可以追加软删除功能

分析:优化思路是 不查,少查,查索引,少取.

我们现在必须要查,则只查索引,不查数据,得到id.

再用id去查具体条目.  这种技巧就是延迟索引.

    分析：limit是先查询再越过，也就是说我们先查询出所有数据再进行跳跃，上图我们越过500W页，还使用了inner join  内存并没有崩掉，这是因为我们子句tmp临时表中只查询了id（索引覆盖，不需要回行去磁盘找数据了）然后拿到这10个id 分别查询这10条数据 。

排序可能发生2种情况:

1:对于覆盖索引,直接在索引上查询时,就是有顺序的, using index

2:先取出数据,形成临时表做filesort(文件排序,但文件可能在磁盘上,也可能在内存中)

我们的争取目标：取出来的数据本身就是有序的! 利用索引来排序,那么什么时候发生索引排序呢？即查询索引和order by的字段是同一个字段

   goods表中 cat_id与shop_price组成联合索引：

select goods_id,cat_id,shop_price from goods where cat_id=4 order by shop_price;    可以直接利用索引来排序,

using where按照shop_price索引取出的结果,本身就是有序的

         select goods_id,cat_id,shop_price from goods order by click_count;

          using filesort用到了文件排序,即取出的结果再次排序

重复索引是指 在同1个列(如age), 或者顺序相同的几个列(age,school), 建立了多个索引,称为重复索引,重复索引没有任何帮助,只会增大索引文件,拖慢更新速度。

冗余索引是指2个索引所覆盖的列有重叠, 称为冗余索引。比如x,m,列，加索引 index x(x),  index xm(x,m) x,xm索引, 两者的x列重叠了,  这种情况,称为冗余索引. （mx, xm 不是重复的,因为列的顺序不一样）

当前标题：索引mysql优化怎么样 索引优化有哪些
文章链接：http://scpingwu.com/article/doooeho.html