MySQL的索引在存储引擎层实现的,性能跟存储引擎有关。
MySQL的索引类型
B-tree索引采用的是B+tree的结构。
B+tree是一个平衡查找树,每个叶子到根节点的路径是相同的。特点:能够加快数据的查询速度。更适合范围查找。
使用场景:全值匹配查找;匹配最左前缀查询;匹配列前缀查询;精确匹配左前列,并范围匹配另外一列;只访问索引的查询。
使用限制:如果不是按照索引最左列开始查找,则无法使用索引;使用索引时不能跳过索引中的列;Not in 和<>不能使用索引;
如果查询中有某个列的范围查询,则其右边所有列都无法使用索引。
Hash索引
是基于Hash表实现的,特点:(1)只有查询条件精确匹配Hash索引中的所有列时,才能使用到Hash索引。(2)对Hash索引的所有列,存储引擎会为每一行计算一个Hash码,Hash索引中存储的就是Hash码。
限制:(1)必须进行二次查找。(2)无法用于排序,(3)不支持部分索引查找也不支持范围查找。(4)Hash码的计算可能存在Hash冲突。
使用索引的原因:
(1)减少了存储引擎扫描的数据量。(2)帮我们排序,避免使用临时表。(3)把随机I/O变为顺序I/O。
索引的确定
(1)索引会增加写操作的成本。(2)太多的所有会增加查询优化器的选择时间。
索引优化策略:
(1)索引列上不能使用表达式或函数。(2)对字符串前缀建立索引。索引的选择性是不重复的索引值和表的记录数的比值。
(3)联合索引。索引列的顺序:经常被使用到的列优先、选择性高德列优先,宽度小的列优先。
(4)覆盖索引。优点:优化缓存,减少磁盘IO的操作。减少随机IO,避免对Innodb主键索引的二次查询。避免MyISAM表进行系统调用。不能使用该索引的情况:存储引擎不支持覆盖索引,查询中使用了太多的列。使用了双%的like查询。