索引
索引介绍
- 索引是什么
- 索引(Index)是帮助MySQL高效获取数据的数据结构。索引的实质即是,排序加查找的数据结构。
- 数据本身之外,数据库还维护着一个满足特定查找算法的数据结构,这些数据结构以某种方式指向数据,这样就可以在这些数据结构的基础上实现高级查找算法。
- 索引的实现通常通过B+树来进行实现(聚集索引,次要索引,覆盖索引,复合索引,前缀索引,唯一索引),另外也有哈希索引。
- 为什么索引的实现是通过B+树,而非B树
- MySQL的默认存储引擎是InnoDB,最小存储单位是页,每页大小为16K,在B+树中非叶子节点的页,存储键值加指针,叶子节点可以存储具体真实的数据,MySQL可以通过在B+树上二分查找,来确定数据在那一页上,进而找到数据。由于B+树只在叶子节点存储数据,而使得非叶子节点能存储更多的键值(非叶子节点存储的键值和指针要比叶子节点数据占的内存小的多),进而使得能有更多的叶子来存储数据。这样一般我们的树的深度为3,就可以存储全部数据了。深度越小,io操作也进而变少,性能提高(树的第一级一般就在内存中,不用进行io操作;另外辅助索引也会建立B+树,会导致io操作的增加)。
- 而B树的非叶子节点也会存储数据,导致在相同深度情况下,整颗树相对与B+树能存储的数据减少,这使得不得不增加深度,增加io的操作,性能降低。
- 索引优劣势(空间换时间)
- 优势
- 提高数据检索的效率,降低数据库的io成本。
- 建立索引的同时,也会对数据进行排序,从而降低了CPU的消耗。
- 劣势
- 索引的建立也会占用一部分空间
- 对数据库进行写操作的时候,不仅要更新数据,也要更新因数据变化而带来的索引信息的变化。(所以频繁更新的表,不适合建立索引)
- 索引分类
- 单值索引:即一个索引只包含单个列,一个表可以有多个单列索引。
- 唯一索引:索引列的值必须唯一,但允许有空值。
- 复合索引:即一个索引包含多个列。
合理使用索引
- 适合建立索引
- 主键自动建立唯一索引
- 频繁作为查询条件的字段应该创建索引
- 查询中与其它表关联的字段,外键关系建立索引
- 频繁更新的字段不适合创建索引,因为每次更新不单单是更新了记录还会更新索引
- Where条件里用不到的字段不创建索引
- 单键/组合索引的选择问题,who?(在高并发下倾向创建组合索引)
- 查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度
- 查询中统计或者分组字段
- 不适合建立索引
- 表记录太少
- 经常增删改的表
- 数据重复且分布平均的表字段
- 索引失效的情况
- 最佳左前缀法则 - 如果索引了多列,要遵守最左前缀法则。(这和B+树这种数据结构有关,复合索引在B+树中,总是先排第一个列,再依次排剩下的列,即所有列的排序都基于前面列的排序,跳过前面,即左边的列,会致使排序失效,进而索引失效)
- 不在索引列上做任何操作(计算、函数、(自动or手动)类型转换),会导致索引失效而转向全表扫描。
- 存储引擎不能使用索引中范围条件右边的列(和最佳左前缀法则原理相同)。
- 尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致)),减少select *。(不用回表查询)
- Mysql在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描。
is null, is not null 也无法使用索引。 - like以通配符开头(’%abc…’),mysql索引失效会变成全表扫描的操作。
- 字符串不加单引号索引失效。(会使得MySQL进行转换,和在索引上进行操作,原理相同)
- 少用or,用它来连接时会索引失效。(也和最佳左前缀法则原理相同)
- 小表驱动大表。(一般小表建立索引用处不大,进行全扫描,而大表使用索引,减少io操作,可以大大增加效率,一般常见,多表连接查询,左连接,则右表建立索引,相反同理)。