MySQL学习之索引

为什么需要索引

在平常的应用系统中，一般的插入和修改操作很少出现性能方面的问题，
平时我们常遇到的、也是最容易出现问题的还是查询操作，所以对查询语句的优化是非常重要的。
索引可以在不读取整个表的情况下，使数据库应用程序可以更快地查找数据。

索引是什么

索引在MySQL中也称为“键”，是存储引擎快速查找到记录的一种数据结构。

索引的本质

本质就是不断地缩小想要查询数据内容的范围来获取最终我们想要的结果，
同时将随机事件变成顺序事件

索引的目的

加快数据的查询速度

索引的副作用

索引会减慢数据插入和更新的速度
更新一个包含索引的表需要比更新一个没有索引的表花费更多的时间，这是由于索引本身也需要更新
因此，理想的做法是仅仅在常常被搜索的列 ( 以及表 ) 上面创建索引

索引的多与少

不管索引多还是少都会出现性能问题，所以需要找到一个平衡点。

预读

当经历一次IO时，OS不光把当前磁盘地址的数据读入内存，
还把相邻的磁盘地址的数据读入内存缓冲区内。
经历一次IO读入的数据称之为页，具体多少与OS有关

索引的两大类型

索引的两大类型hash与btree

hash类型的索引：查询单条快，范围查询慢
btree类型的索引：b+树，层数越多，数据量指数级增长（我们就用它，因为innodb默认支持它）

#不同的存储引擎支持的索引类型也不一样
InnoDB 支持事务，支持行级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；
MyISAM 不支持事务，支持表级别锁定，支持 B-tree、Full-text 等索引，不支持 Hash 索引；

索引的实现方式

b+树，由二叉查找树以及平衡二叉树演变而来
一般树的高度为2 ~ 4，也就是经历2 ~4次IO就能查找到我们想要的数据。
叶子结点存放真实的数据，非叶子结点存放指引搜索方向的数据项

b+树索引的分类

索引一般分为两大类： 聚集索引 和 非聚集索引(辅助索引)

聚集索引和辅助索引的区别在于：
1. 叶子结点是否存放的是一整行的信息
2. 一张表中聚集索引只能有一个，辅助索引可以有多个

    # 1. 聚集索引
        就是表记录的排列顺序和索引的排列顺序一模一样。
        聚集索引，不会有单独的空间存储索引数据，而是在存储数据的时候就已经根据索引排好序。

        通过表的主键来生成b+树，主键索引就属于 聚合索引

        好处：
            1. 对主键的排序查找和范围查找非常快，叶子结点存放的是数据一整行的信息。
            2. 范围查询，即如果想查找主键某一范围内的数据，通过叶子结点的上层中间节点就可以得到页的范围，之后直接读取数据即可。

    # 2. 辅助索引
        用另外的空间存储了记录的顺序，但是记录本身的物理顺序可以和索引不一样

        表中除了聚集索引以外的都是辅助索引（非聚集索引）
        例如：唯一索引 和 普通索引 都是非聚集索引

索引的种类

    主键索引
    唯一索引
    普通索引
        1. 普通索引允许重复的值，就是两个记录的索引字段的值可以重复
        2. 唯一索引不允许重复的值，两个记录的索引字段不允许重复，
           其实 主键索引 也是一种特殊的唯一索引
# 注意：
# 组合索引？
# 组合索引不属于它们，
# 如果三个索引中包含了两个及以上字段，其实就是组合索引，不如组合主键索引
# 遵循最左前缀规则。对where，order by，group by 都生效。

# 覆盖索引
# 针对的是辅助索引
select name from t1 where id = 20   命中了辅助索引但是未覆盖到索引，还需要从聚集索引中查找name
select id  name from t1  where id =  20  命中索引  一次就找到 覆盖到了索引

索引总结

#1. 一定是为搜索条件的字段创建索引，比如select * from s1 where id = 333;就需要为id加上索引

#2. 在表中已经有大量数据的情况下，建索引会很慢，且占用硬盘空间，建完后查询速度加快
比如create index idx on s1(id);会扫描表中所有的数据，然后以id为数据项，创建索引结构，存放于硬盘的表中。
建完以后，再查询就会很快了。

#3. 需要注意的是：innodb表的索引会存放于s1.ibd文件中，而myisam表的索引则会有单独的索引文件table1.MYI

MySAM索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。
而在innodb中，表数据文件本身就是按照B+Tree（BTree即Balance True）组织的一个索引结构，
这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此innodb表数据文件本身就是主索引。
因为inndob的数据文件要按照主键聚集，所以innodb要求表必须要有主键（Myisam可以没有），
如果没有显式定义，则mysql系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，
则mysql会自动为innodb表生成一个隐含字段作为主键，这字段的长度为6个字节，类型为长整型.