MySQL学习笔记3-数据库索引

1.数据索引的定义

（1）全表搜索：
不适用大量数据，消耗大量数库系统时间，造成大量磁盘IO操作
（2）索引：
索引是数据库对象之一，用于加快数据的检索，类似于书籍的目录；在数据库中索引可以减少数据库程序查询结果时需要读取的数据量，类似于在书籍中我们利用索引可以不用翻阅整本书即可找到想要的信息。
索引在逻辑上和物理上都与相关表和数据无关，当创建或者删除一个索引时，不会影响基本的表
优点

• 针对表中指定列的值进行排序的数据结构，使用它可以加快表中数据查询，使用B+树结构作为MySQL的索引，提高数据查询的效率.
• 执行一条SQL语句时，默认的方式是根据搜索条件进行全表扫描，添加了索引，就先利用索引定位到特定值的行数，大大减少遍历匹配的次数
• 大大加快数据检索速度（主要索引），加速表和表之间的连接
  缺点：
• 创建索引和维护索引要耗费时间，会随着数据量的增大而增多
• 索引需要占用物理空间
• 对表中数据增、删、改时，索引也需要进行动态维护，会降低数据的维护速度
  （3）索引的分类
• 普通索引key：
• 唯一索引unique key/unique index：索引列（字段）的所有值只出现一次，唯一索引可以为空，可以有多个唯一索引
• 主键索引primary key： 一种约束，主键唯一，主键索引唯一。主键创建后一定包含唯一索引，唯一索引不一定是主键索引。主键索引不能为空
  一个表中，最多一个主键索引，可以有多个唯一索引
• 全文索引fulltext:在特定的数据库引擎下才有，快速定位数据
• 复合索引：在多个列，几个字段联合在一起组成一个索引，开销小

2.索引的数据结构-B树、B+树

2.1 B树：

（1）任何一个关键字出现且只出现在一个节点中
（2）搜素可能在非叶子结点结束
（3）搜索性能等价于关键字全集内做一次二分查找
（4）每个节点中都有可能存在数据

2.2 B+树

叶子节点不会直接存储行数据，而是存储索引的值
（1）关键字全部存放在叶子节点，非叶子节点做索引，叶子节点用链表连接（决定了B+树更适合存储外部数据），提高区间访问性能
（2）B+树扫库直接在叶子节点扫一遍，B+树支持范围查找，B树不支持
（3）B+树只要遍历叶子节点就可以实现整棵树的遍历，而且在数据库中基于范围查询很频繁
（4）B+树稳定必须到叶子节点
（5）增删文件：B+树叶子节点包含所有关键字且有序（提高增删效率）

3.为什么不用平衡二叉树作为索引

逻辑结构上是平衡二叉树，物理实现是数组，逻辑上相连，物理不一定。每次读取磁盘页的数据有许多是用不上的，不能利用局部性，预读许多无用数据，所以需要进行多次磁盘读取操作

4.为什么B-树可以作为索引

（1）充分利用磁盘预读功能创建的数据结构
（2）B树每个节点存储多个关键字（树不深），节点大小为磁盘页大小，读取磁盘页会读取一整个节点，磁盘I/O少，从内存中读取更快
（3）B树查询主要发生在内存中，平衡二叉树在磁盘中

磁盘预读功能

• 每次读取时，都会顺序向后读取一定长度的数据放入内存，依据局部性原理
• 预读的原因：磁盘读取比内存读取慢，为了提高效率进行预读，尽量减少磁盘I/O

局部性原理：

• 当一个数据被用到时，其附近的数据也通常会马上被使用
• 程序运行期间所需要的数据通常比较集中
• 磁盘顺序读取效率高（不用寻址），所以对于具有局部性的程序来说，预读可以提高I/O效率

5.MySQL操作索引

添加索引的原则

（1）仅对需要快速查询的数据库表相应列建立索引
（2）一般是对表的主键列创建索引
（3）索引不是越多越好
（4）不要对经常变动的数据加索引
（5）小数据量的表不需要加索引
（6）索引一般加在常用来查询的字段上

5.1查看索引

show index from student;

5.2 创建索引

（1）创建表时增加索引

（2）表创建完后增加索引

# 全文索引
alter table student add fulltext index name_idx(name)
# 常规索引：索引对应的字段，字段值可以重复
create index name_idx on student(name)
# 唯一索引：索引所在字段的值不可重复
create unique index name_idx on student(name);
# 唯一索引：索引所在字段的值不可重复
alter table student add unique index name_idx(name);
# 重复执行两次，会报索引值重复的错误
insert into student values('cc',2);

5.2 修改索引

alter index name_idx rename to names_idx

5.3 删除索引

drop index name_idx on 表名

5.4 查看索引

show index from表名