索引

常见的索引数据结构

  • key-value - 哈希表(数组)+ 链表:在数据量小的时候查询速度非常快,数据量大的时候需要遍历链表,对性能影响比较大。memcached就是使用这种数据结构。

  • 数组:查询速度是最快的,但是如果存在插入中间数据的情况,就需要挪动所有后面的数据,代价太大。即:查询快,插入慢,适合存储不会变更的数据。

  • 树(B+树):折中了查询和插入数据的速度,时间复杂度都是O(N),mysql的innoDB引擎就是使用了B+树的数据结构。

以User表举例,一下是建表语句

数据的查询过程

覆盖索引

最左前缀原则

索引下推

MySQL5.6之前的版本,当

普通索引和唯一索引

查询性能

没有太大区别。相对唯一索引,普通索引在查找到目标值得时候还会往下继续再查一次,判断是否与查询条件的值相等,不相等直接返回。但是这一次查询对整体性能影响微乎其微,可以忽略不计。

写入性能

唯一索引无法使用change buffer,因为唯一索引在的数据在插入之前需要判断数据是否满足唯一约束,就需要从数据页到内存后再比较,涉及到随机IO的访问,是比较耗时的操作。

而普通索引是直接将数据放到change buffer后就直接返回成功了。MySQL会定期的把change buffer数据merge到数据页,或者在需要查询数据的时候也会触发merge。

所以,在读多写少的场景下,change buffer反而起到副作用,没能减少IO次数,还增加了change buffer维护成本。

总结:在写多读少,并且程序已经能够保证数据唯一性的情况下可以使用普通索引来提高写入性能。

给字符串加索引

  1. 直接创建完整索引,这样可能⽐较占⽤空间;
  2. 创建前缀索引,节省空间,但会增加查询扫描次数(前缀长度要根据实际情况确保区分度比较高)
  3. 倒序存储,再创建前缀索引,⽤于绕过字符串本身
  4. 创建 hash 字段索引,查询性能稳定,有额外的存储和计算耗,跟第三种方式一样,都不支持范围扫描。

已经建了索引,sql执行却没有走索引

原因是优化器判断走索引并不是最佳方案(优化器误判了执行计划)