为什么用?
- 通过创建唯一索引,保证数据库表的每一行的唯一性。
- 大大加快数据的检索速度。(创建索引最主要的原因)
- 帮助服务器避免排序和临时表
- 将随机IO编程顺序IO
- 可以加速表和表之间的连接。
优点很多,为什么不对表中的每一个列创建一个索引
- 当表中数据增加,删除和修改。索引也要动态维护。降低数据的维护速度。
- 索引占物理空间,除了数据表占据空间之外,每个索引也要占据物理空间。如果建立聚簇索引,需要的空间会很大。
- 创建索引和维护需要耗费时间,随着数据量增加而增加。
使用索引的注意事项:
- 在经常需要搜索的列加索引,加快搜索速度。
- 在经常使用where子句的列上创建索引,加快条件的判断速度
- 在经常需要排序的列上创建索引,因为索引已经排序,这样索引可以利用索引的排序,加快排序的查询时间;
- 对于中到大型表索引很有效。特大型维护开销会很大。
- 在经常连接的列上,主要是做外键,加快连接的速度
- 避免where子句对字段加函数,避免函数失效。
- 使用InnoDB时使用与业务无关的自增主键作为主键,即使用逻辑主键,而不用业务主键。
mysql索引主要使用的两种数据结构
哈希索引:
底层是哈希表。在绝大多数需求为单条记录查询的时候,可以选择哈希索引,查询性能最快,其余大部分场景,建议选择BTree索引。
B+Tree索引
MyISAM和InnoDB实现BTree索引方式的区别
MyISAM:
B+Tree叶节点的data域存放的事数据记录的地址,在检索的时候,首先按照B+Tree搜索算法搜索索引,如果指定的key存在,则取出data域的值,然后以data域的值为地址读取响应的数据记录。这被称为非聚簇索引。
InnoDB:
其数据文件本身就是索引文件。相比MyISAM,索引文件和数据文件是分离的,其表数据文件本身就是按B+Tree组织的一个索引结构,树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键,因此InnoDB表数据文件本身就是主索引。这被称为“聚簇索引(或聚集索引)
覆盖索引
如果一个索引包含(或者说覆盖)所有需要查询的字段的值,我们就称之为“覆盖索引”(不需要回表)。我们知道InnoDB存储引擎中,如果不是主键索引,叶子节点存储的是主键+列值。最终还是要“回表”,也就是要通过主键再查找一次。这样就会比较慢覆盖索引就是把要查询出的列和索引是对应的,不做回表操作!
为什么索引能提高查询速度
- mysql的基础存储结构是页。
- 各个数据页可组成一个双向链表
- 每个数据页中的记录又组成一个单项链表。
最左前缀原则
MySQL中的索引可以以一定顺序引用多列,这种索引叫作联合索引。如User表的name和city加联合索引就是(name,city),而最左前缀原则指的是,如果查询的时候查询条件精确匹配索引的左边连续一列或几列,则此列就可以被用到。
B树和B+树的区别
B树的所有节点既存放 键(key) 也存放 数据(data);而B+树只有叶子节点存放 key 和 data,其他内节点只存放key。
B树的叶子节点都是独立的;B+树的叶子节点有一条引用链指向与它相邻的叶子节点。
B树的检索的过程相当于对范围内的每个节点的关键字做二分查找,可能还没有到达叶子节点,检索就结束了。而B+树的检索效率就很稳定了,任何查找都是从根节点到叶子节点的过程,叶子节点的顺序检索很明显