索引相关
类型隐式转换 大坑
**字段filed1是varchar类型,且加了索引,如果 where filed1 = 123; type 可能是all,因为123是数字类型,mysql内部会用函数做隐式转换,用了函数,索引就失效了。**
大数据深度分页,用主键
select field1,field2 from table limit 100000,10;
select field1,field2 from table where id > 100000 limit 10;
避免使用MySQL函数
MySQL内置了很多函数,使用函数可能导致索引失效,尽量让MySQL只做简单的增删改查。
避免类型的隐式转换
varchar等字符串类型的字段被加了索引,把这字段当做where条件,及时目标值是数字,也要加引号,否则类型的隐式转换,会引起索引失效的问题。
避免使用函数或表达式,尽量只让数据库做纯粹的增删改查。
用函数的前提是知道初始值,然后在操作数据,很多函数都是要传参的,所以mysql只能全表查,然后每次迭代将数据丢给函数处理。
表达式也是如此,例如where number + 1 = 10,都会让索引失效。
避免使用不等值做排除法
避免使用<>、!=、not in、is not null、这些都会使索引失效。
避免使用null值
避免字段使用null值,一是影响索引(mysql建索引建的是非null的值,大量的null值影响了正常的B+tree结构),而且造成聚合函数统计(如count(该字段))不准确的问题。
索引无关
减少大字段查询,避免使用*,不说磁盘io的损耗,连网络带宽都跟着损耗。
如果只select仅需字段,可能会触发覆盖索引机制,不用回表,提高性能。
select * from table;
select field1,field2 from table;
查询是否存在
select count(*) from table where...;
select field from table where ... limit 1;
冗余优化文章来源:https://www.toymoban.com/news/detail-837797.html
想要查询一篇文章的浏览量,不用count(浏览记录)。
新建一个在文章表中建立一个浏览量的字段,这使得查询的时间复杂度从O(n)变成O(1);
避免join,适用于大表关联小表。文章来源地址https://www.toymoban.com/news/detail-837797.html
如果想要join的两个表,一个很大,一个很小,应尽量避免join。
可将小表数据全部取出来组装成数组,放入编程语言的内存,用编程语言的内存匹配的方式去关联。
哪些场景下索引会失效
- 使用not in、is not null、<>、!=、这种排除法时会导致索引失效,覆盖索引除外。
- 最左匹配原则,左边的字段缺少时会出现,覆盖索引除外。
- 最左匹配原则,左边的字段有区间查询,导致右边的字段无法使用索引。
- like左边或两边加百分号。
- 类型的隐式转换,如varchar的字段,使用where varchar_field = 123,包括join表,用on连接的字段。
- where条件有函数,或表达式。
- where语句包含or,or中存在非索引列。
- 大数据量对二级索引字段排序,如果select * 或者其它字段,这个过程涉及回表,可能无法使用索引,因为数据量大,走索引的每条数据都需要回表,代价会很大。
- order by字段,如果排序与索引顺序不一致,则可能导致索引失效,如果order by的每个字段,都按照索引的顺序,或者反顺序,则仍旧会走索引。
那些查询适合创建索引?
- 需要唯一性约束兜底的字段。
- 经常被查询或者作为where条件的字段,=、>、<、<=、>=、in、between、like 右百分号。
- 经常group by或者order by的字段。
- delete或update被作为where条件的字段。
- distinct的字段。
- join on的连接字段需要加索引,但是需要类型一致,因为MySQL内部有用函数做隐式转换,用了函数就不适用索引。
- 区分度(不重复度)高的字段。
- 把搜索最频繁的列,放在联合索引的左侧,(受联合索引的最左原则影响)。
那些查询不适合创建索引?
- 数据量小,一个表,例如配置表,总类别表,可能最多几十条记录,创建不创建区别不大。
- 写多读少,数据的写操作对索引字段的开销比没有索引要大,而且读操作还少。
- 区分度低的字段,例如性别状态等,这会导致线性查找,能提升搜索效率,但是不明显,可加可不加。
- sql语句包含<>、!=、not in、is not null,无法使用索引,所以专门用作排除性查找的,不建议创建索引。
到了这里,关于MySQL查询优化方案汇总(索引相关)的文章就介绍完了。如果您还想了解更多内容,请在右上角搜索TOY模板网以前的文章或继续浏览下面的相关文章,希望大家以后多多支持TOY模板网!