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,然后在业务层进行数据的拼装。
-
2.6 在使用 in 进行查询时,in中的值不能太多,最好不要超过500个,因为查询太多可能导致系统超时,减少 in 中查询的数据可以提高查询的速度。
-
2.7在进行多表联查时,一定要确保联查的键有索引,这样会加快联查的速率。我们一般不需要设置联查表的顺序,先查大表和先查小表各有各的优势,数据库系统也会自动选择最优的查询方案。
小表优先:在某些情况下,先联查小表可以减少中间结果集的大小,从而提高查询效率。这是因为小表的结果集会更快地过滤掉不必要的数据。
大表优先:在其他情况下,先联查大表可能更有效,尤其是当大表的过滤条件能够显著减少结果集时。
-
2.8 在使用InnoDB引擎时,尽量不要使用count(*)
因为InnoDB引擎并没有存储总的行数,所以每次执行count函数时,内部都是重新逐行计算,十分浪费时间。这个问题在MyISAM引擎中不存在,因为MyISAM内部存储的有总的行数。
2.9 like通配符尽量不要用在开头
将通配符 % 用在开头会减慢查询效率,不推荐,不建议
这是放在开头的情况:
这是不放在开头的情况:
2.10 运算时等号左边只设置一个值
例如,将 id + 1 = 100 替换成 id = 100-1 。因为这样会造成索引失效
3.关于索引失效的几种原因
关于索引失效的原因很多,网上也有很多的解释,这里我就总结几个常见的索引失效原因吧。
3.1 like通配符可能导致索引失效
like通配符放在开头会导致索引失效
并不是用了like通配符,索引一定会失效,而是like查询是以%开头,才会导致索引失效。
3.2 查询条件中有 or 可能造成索引失效
如果 or 的两测都有索引,那不会索引失效,如何有一个没有索引,那么系统不会使用索引进行查找,而是全局查找。
3.3 运算时等号左边设置多个值
例如, id + 1 = 100 造成索引失效
3.4 索引字段上使用(!= 或者 < >),索引可能失效
因为此类查询范围依旧较大,系统择优会选择普通查询,而不会使用索引
3.5 查询条件的字段中的字段编码格式不一样
编码格式不一样会导致索引失效,即便两个字段都有索引,但也不会生效。
3.6 注意查询时的类型与字段类型相同
例如,id = 10 ,假如id的类型为char,则这条查询将不会使用索引,应该id = ’10‘.
3.7 使用联合索引时,需要按照最左匹配原则
也就是需要按照创建索引时传入的顺序查询,如果不满足传入时的顺序,将导致索引失效