mysql数据库底层原理及查询优化提升速度问题总结-CSDN博客

本文链接：https://blog.csdn.net/gttswra/article/details/140919265

1. 两种搜索引擎比较

首先我们得知道，索引的本质是创建B+树，这里的B+树是一种特殊的树。

在InnoDB引擎下，数据库会先创建一个主键B+树，这个B+树的key是主键的值，value是该主键下对应的全部数据，这个B+树是在创建表的时候就会生成的，所以这就是为什么在InnoDB创建表时必须要指定主键；主键会自动创建索引，之后查询该主键索引时就会直接得到数据本身（这里在之后会提到，这是常见情况中唯一InnoDB引擎查询速率快于MyISAM引擎的情景，这里就先略过。）在之后创建的索引，创建专门属于该索引的B+树，他的key是索引值，value是对应的主键（大部分情况下是主键id），索引检索到主键id后在根据主键所在的主键B+树查询到对应的数据。

我们对InnoDB引擎做个总结：如果索引是主键，那么查询后直接得到数据本身；如果索引不是主键，那么会先根据索引查询到对应的主键，在通过主键的B+树查询对应的数据。

在MyISAM引擎下，每个索引都对应的B+数，这个树key是索引，value是对应数据的物理地址，引擎会通过该物理地址查询到对应的数据，这里可以看到，MyISAM引擎无论如何都需要查询两次，但是第二次根据地址查询时的速度会快于第一次，所以MyISAM引擎查询速度绝大部分情况下快于InnoDB。

2.理解了索引的原理，接下来我们谈谈如何提升查询速度。

2.1 提升查询速度最明显的方式，就是创建索引

这是没有创建索引的效果

这是有索引的效果

注意，使用索引时可以使用explain语句查看索引是否生效，有很多情况下会导致索引失效，导致索引失效的原因有很多，这里就不一一列举了，大家得先知道是索引未生效之后，那么专门针对解决索引问题便比较简单了。

2.2 查询时不要使用select * 语句，这回严重拖慢查询速度，一般想要什么数据查询什么数据

2.3 limit深分页问题，在使用limit时，偏移量太大，会导致查询速度变慢，解决办法，将偏移量转换为子查询

2.4当表单数据量太大时（一般为百万级以上的数据量），基于底层B+树的结构，其可能需要查询到树的第三层或者更多，这会导致查询时的速度变慢，解决该办法可以进行分库分表，不让B+树查询到第三层即可。

2.5 join和子查询不能太多。一般来说，不建议使用子查询，可以把子查询改成join来优化。而数据库有个规范约定就是：尽量不要有超过3个以上的表连接。为什么要这么建议呢? 我们来聊聊，join哪些方面可能导致慢查询吧。

MySQL中，join的执行算法，分别是：Index Nested-Loop Join和Block Nested-Loop Join。

Index Nested-Loop Join：这个join算法，跟我们写程序时的嵌套查询类似，并且可以用上被驱动表的索引。
Block Nested-Loop
Join：这种join算法，被驱动表上没有可用的索引,它会先把驱动表的数据读入线程内存join_buffer中，再扫描被驱动表，把被驱动表的每一行取出来，跟join_buffer中的数据做对比，满足join条件的，作为结果集的一部分返回。
join过多的问题：

一方面，过多的表连接，会大大增加SQL复杂度。另外一方面，如果可以使用被驱动表的索引那还好，并且使用小表来做驱动表，查询效率更佳。如果被驱动表没有可用的索引，join是在join_buffer内存做的，如果匹配的数据量比较小或者join_buffer设置的比较大，速度也不会太慢。但是，如果join的数据量比较大时，mysql会采用在硬盘上创建临时表的方式进行多张表的关联匹配，这种显然效率就极低，本来磁盘的 IO 就不快，还要关联。

一般情况下，如果业务需要的话，关联2~3个表是可以接受的，但是关联的字段需要加索引哈。如果需要关联更多的表，建议从代码层面进行拆分，在业务层先查询一张表的数据，然后以关联字段作为条件查询关联表形成map，然后在业务层进行数据的拼装。