目录
索引的概念
什么是索引
数据库使用索引维护着一个满足特定查找算法的数据结构,这些数据结构以某种方式指向数据。索引的本质是一种排好序的快速查找的数据结构
索引往往以文件的形式存储在硬盘上,平常我们所说的索引都是指B+树索引(多路搜索树,并不一定是二叉树) ,当然除了B+树索引外还有很多其他索引结构。
索引的两个功能,查找和排序
索引的优点
- 提高了数据检索效率,降低数据库的IO成本
- 通过索引列队数据进行排序,降低数据排序成本,降低了CPU的消耗
索引的缺点
- 索引也是一张表,因此索引也是会占用空间的
- 索引降低了更新表的速度,因为更新表时除了保存数据外还要保存索引字段,以及跳转索引指向的数据信息
- 如果表中数据量巨大,需要花时间建立优秀的索引或优化查询语句。
索引的分类
- 主键索引(PRIMARY KEY)
唯一的标识,主键不可重复,只能有一个列作为主键,不可以有null值
为什么主键索引推荐Int类型(String类型也能比较,但是需要转ASCII码,执行效率没Int块)
- 唯一索引(UNIQUE KEY)
避免重复的列出现,唯一索引可以重复,多个列都可以标识为唯一索引,可以有null值
- 常规索引(KEY/INDEX)
自定义为index类型的索引,可以有多个
- 全文索引(FullText)
用于在长篇幅文章中快速定位数据,优化模糊查询的
- 覆盖索引
SQL只需要通过索引就可以返回查询所需要的数据,而不必通过二级索引查到主键之后再去查询数据
- 组合索引
多列组合成一个索引
- 聚簇索引
B+树中数据和索引存在一块叫聚簇索引,主键索引采用聚簇索引。如果索引在叶子节点上,想查数据需要根据索引再遍历另一颗树叫做非聚集索引,也叫非主键索引
(为什么非聚簇索引叶子节点不存数据),首先即使存也只是存部分数据项而不是一整条记录,需要整条记录还需要回表。其次聚簇索引表里维护这一个数据结构,非聚簇索引如果又维护一遍,当作修改时会修改两次,降低效率
索引的基本语法
1.创建
如果是CHAR或VARCHAR类型,length可以小于字段实际长度。如果是BLOB或TEXT类型必须指定length
2.删除
DROP INDEX [indexname] ON mytable;
3.查看
SHOW INDEX FROM table_name
索引的结构
MySQL使用索引结构有四个。B+Tree索引、Hash索引、full-text全文索引、R-Tree索引。开发人员主要了解Bree索引
B+Tree索引
B+Tree即(Balance+ Tree)是InnoDB默认的数据库索引结构,理论上使用B+Tree在亿条数据与100条数据中定位记录的花销相同
https://blog.csdn.net/Delicious_Life/article/details/107330253
Hash索引
1.键值唯一,哈希索引具有明显优势。
2.Hash索引无法完成范围检索,无法利用索引完成排序,模糊查询等
3.不支持多列联合索引
4.存在哈希碰撞,数据量大后索引效率极低
哪些情况需要建立索引
1.主键自动建立唯一索引
2.频繁作为查询的条件字段应该创建索引
3.查询中与其他表关联的字段,外键关系建立索引
4.在高并发的情况下倾向于创建组合索引
5.查询中的排序字段应该建立索引
6.查询中统计或分组字段
哪些情况不需要建立索引
1.表记录太少
2.经常增删改的表
3.where条件里用不到的字段不要创建索引
4.数据重复且分布平均的表字段不要建立索引因为意义不大。
举例
假设一个表有10万行记录,有一个字段A只有T和F两种值,且每个值的分布概率大约为50%,那么对这种表A字段建立索引一般不会提高数据库的查询速度
索引的选择性指索引列中不同值的数目与表中记录数的比。如果一个表中有2000条记录,表索引列有1980个不同的值,那么这个索引的选择性就是1980/2000=0.99.一个索引的选择性越接近1,这个索引的效率就越高