MySQL数据库索引_腾讯视频
1.背景介绍
概念
索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。
MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。
不建立索引的话，MySQL执行查询会从数据表的第一行开始查直到查询想要的数据结果。
2.知识剖析
索引的分类--单列来区分
1).普通索引--这是最基本的索引，它没有任何限制
2).唯一索引--索引列的值必须唯一，但允许有空值;如果是组合索引，则列值的组合必须唯一
3).主键索引--列值唯一，非空，一个数据表只能有一个主键
4).组合索引：将数据表中多个列建到一个索引中，MySQL组合索引遵循“最左前缀”原则，
就是说你的查询条件语句里必选包含组合索引第列的值才能使用
改组合索引，例如在某个数据表中为user,name,age三个列建立组合索引，
查询语句里必须有where user*才会调用这个索引。
索引的分类（存储结构）（InnoDB）
B-tree
mysql数据库中主键、唯一、普通、全文都是采用b-tree数据结构
在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，
这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，
因此InnoDB表数据文件本身就是主索引，InooDB必须要求主键，没有显示声明，系统会默认添加
除了主键索，其它都是辅助索引，辅助索引也会包含主键列，因此主键列最好不要设置太大的列值
因为是聚簇索引方式，聚簇索引的顺序就是数据的物理存储顺序，叶节点就是数据节点。主键最好是单调的。
hash索引
简单地说，哈希索引就是采用一定的哈希算法，把键值换算成新的哈希值，检索时不需要类似B树那样从根节点到叶子节点逐级查找，只需一次哈希算法即可立刻定位到相应的位置，速度非常快
1.可以通过建立唯一索引或者主键索引,保证数据库表中每一行数据的唯一性.
2.建立索引可以大大提高检索的数据,以及减少表的检索行数
3.在表连接的连接条件 可以加速表与表直接的相连
4.在分组和排序字句进行数据检索,可以减少查询时间中 分组 和 排序时所消耗的时间(数据库的记录会重新排序)
5.建立索引,在查询中使用索引 可以提高性能
3.常见问题
1.索引可以提高查询效率，数据表每个列都建索引,这样可行吗？
1).在创建索引和维护索引 会耗费时间,随着数据量的增加而增加
2).索引文件会占用物理空间,除了数据表需要占用物理空间之外,每一个索引还会占用一定的物理空间
3).当对表的数据进行INSERT,UPDATE,DELETE的时候,索引也要动态的维护,这样就会降低数据的维护速度,(建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快)。
4.解决方案
1.在经常需要搜索的列上,可以加快索引的速度
2.主键列上可以确保列的唯一
3.在经常需要排序(order by),分组(group by)和的distinct列上加索引 可以加快排序查询的时间
4.在一些where之后的< <= > >= BETWEEN IN以及某个情况下的like建立字段的索引(B-TREE)
5.选择越小的数据类型越好,因为通常越小的数据类型通常在磁盘,内存,cpu,缓存中 占用的空间很少,处理起来更快
5.什么情况不要建索引？
1.查询中很少使用到的列 不应该创建索引,如果建立了索引然而还会降低mysql的性能和增大了空间需求.
2.很少数据的列也不应该建立索引,比如 一个性别字段0或者1,在查询中,结果集的数据占了表中数据行的比例比较大,mysql需要扫描的行数很多,增加索引,并不能提高效率
3.当表的修改(UPDATE,INSERT,DELETE)操作远远大于检索(SELECT)操作时不应该创建索引,这两个操作是互斥的关系
网友评论