浅谈数据库索引

  • 索引是为了加速对表中数据行的检索而创建的一种分散的存储结构。
  • 索引是针对表而建立的，它是由数据页面以外的索引页面组成的，每个索引页面中的行都会含有逻辑指针，以便加速检索物理数据。
  • 索引是关系数据库中对某一列或多个列的值进行预排序的数据结构。通过使用索引，可以让数据库系统不必扫描整个表，而是直接定位到符合条件的记录，这样就大大加快了查询速度。

简介

索引的效率取决于索引列的值是否散列，即该列的值如果越互不相同，那么索引效率越高。反过来，如果记录的列存在大量相同的值，对该列创建索引就没有意义。

作用

  1. 快速取数据；
  2. 保证数据记录的唯一性；
  3. 实现表与表之间的参照完整性；
  4. 在使用ORDER by、group by子句进行数据检索时，利用索引可以减少排序和分组的时间。

优缺点

优点：

1.大大加快数据的检索速度;
2.创建唯一性索引，保证数据库表中每一行数据的唯一性;
3.加速表和表之间的连接;
4.在使用分组和排序子句进行数据检索时，可以显著减少查询中分组和排序的时间。

缺点：

1.索引需要占物理空间。
2.当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，降低了数据的维护速度。

分类

  • 单值索引：一个索引只包含一列
  • 唯一索引：索引列的值必须唯一，但允许有空值
  • 复合索引：一个索引同时包括多个列

唯一索引

PS：

  • 主键索引：一种特殊的唯一索引，创建主键时自动创建。

复合索引

最左前缀特性：

复合索引的结构是B+树，B+树是按照从左到右的顺序建立搜索树的。

扩展

索引及其扫描类型

type：

  • ALL 全表扫描，没有优化。（最慢的方式）
  • index 索引全扫描
  • range 索引范围扫描，常用语<,<=,>=,between等操作
  • ref 使用非唯一索引扫描或唯一索引前缀扫描，返回单条记录，常出现在关联查询中
  • eq_ref 类似ref，区别于使用的是唯一索引，使用主键的关联查询
  • const 当查询是对主键或者唯一键进行精确查询，系统会把匹配行中的其他列作为常数处理。
  • null 不访问任何表或索引，直接返回结果
  • system 表只有一条记录

性能排行：

system > const > eq_ref > ref > range > index > ALL

possible_keys

显示可能应用在这张表中的索引

key

真正使用的索引方式

什么情况索引不生效？

  1. 有or必全有索引;
  2. 复合索引未用左列字段;
  3. like以%开头;
  4. 需要类型转换;
  5. where中索引列有运算;
  6. where中索引列使用了函数;
  7. 如果mysql觉得全表扫描更快时（数据少）;

扩展

数据库行锁、表锁机制

Q: 操作数据库时，什么时候进行行锁、表锁？

首先，我们思考一下，锁的作用————锁住数据，避免并发修改，导致脏数据产生。即保证并发修改时，不产生脏数据。

行锁：对数据表中特定一行或多行加锁。
表锁：对整张数据表加锁。

具体什么时候用行锁，表锁，实际上和索引有关。