以下是几种常见的优化索引的方法:
覆盖索引优化
假设我们只需要查询商品名称和价格这两个数据,这时候我们可以对这两个字段建立联合索引,即「商品名
称、价格」作为一个联合索引
针对 select product_id,product_name, price from table where product name ="iphone" ;
的语句,这时候就利用覆盖索引优化了, 因为索引中已经包含这两个字段数据了,同时,会与 id 绑定,所以查询将不会再次检索主键索引,从而避免回表,减少了大量的 I/0 操作。
主键索引优化
主键索引最好是自增的:
如果我们使用自增主键,那么每次插入的新数据就会按顺序添加到当前索引节点的位置,不需要移动已有的数据,
当页面写满,就会自动开辟一个新页面。因为每次插入一条新记录,都是追加操作,不需要重新移动
数据,因此这种插入数据的方法效率非常高。
如果我们使用非自增主键,由于每次插入主键的索引值都是随机的,因此每次插入新的数据时,就可能会插入到现有数据页中间的某个位置,这将不得不移动其它数据来满足新数据的插入,甚至需要从一个页面复制数据到另外一个页面,我们通常将这种情况称为页分裂。
页分裂还有可能会造成大量的内存碎片,导致索引结构不紧凑,从而影响查询效率。
页分裂详细可以看这篇:
什么是页分裂?insert 操作对 B+ 树结构的改变是什么样的?
防止索引失效
当我们使用左或者左右模糊匹配的时候,也就是 like%xx 或者 like%xx% 这两种方式都会造成索引失效;
当我们在查询条件中对索引列做了计算、函数、类型转换操作,这些情况下都会造成索引失效;
联合索引要能正确遵循最左匹配原则,也就是按照最左优先的方式进行索引的匹配,否则就会导致索引失效。
在 WHERE 子句中,如果在 OR 前的条件列是索引列,而在 OR 后的条件列不是索引列,那么索引会失效。
前缀索引优化:
使用前缀索引可以减小索引字段大小,可以增加一个索引页中存储的索引值,有效提高索引的查询速度。
在一些大字符串的字段作为索引时,使用前缀索引可以帮助我们减小索引项的大小。