MySql进阶篇---索引

什么是索引

        索引时类似于书的目录，可以通过目录(索引)快速的定位到数据的真实位置。是帮助Mysql高效获取数据的数据结构。

索引原理

        通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的时间变成顺序的时间，也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。

索引优劣势

        优势:快速的定位数据位置，减少IO次数

                 降低了排序成本

        劣势:维护索引需要占用存储空间

                 增删改操作时，还需要额外的维护索引树，需要消耗时间

索引分类

主键索引

        设定为主键后数据库会自动建立索引

-- 创建主键索引
ALTER TABLE 表名 add PRIMARY KEY 表名(列名);
-- 删除建主键索引：
ALTER TABLE 表名 drop PRIMARY KEY ;

单值索引

        即一个索引只包含单个列，一个表可以有多个单列索引      

-- 创建单值索引
CREATE INDEX 索引名 ON 表名(列名);
-- 删除索引：
DROP INDEX 索引名;

唯一索引

        索引列的值必须唯一，允许为null

-- 创建唯一索引
CREATE UNIQUE INDEX 索引名 ON 表名(列名);
-- 删除索引
DROP INDEX 索引名 ON 表名;

组合索引

        一个索引对应多个列，满足最左前缀原则。

        a,b,c三列。

        a,b建立组合索引，满足最左前缀原则，必须要出现最左边的列

        a and b  生效

        b and a  生效

        a and c  生效

        b and c  不生效

全文索引

        需要模糊查询时，一般索引无效，这时就可使用全文索引了

建立索引的原则

哪些情况需要创建索引

        主键自动建立索引

        频繁作为查询条件的字段应该创建索引(where 后面的语句)

        查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度

        分组中的字段

哪些情况不要创建索引

        表记录太少

        经常增删改的表:提高了查询速度，同时会降低更新表的速度如对表进行INSERT、UPDATE和DELETE，因为更新表时，MySql不仅要保存数据，还要保存一下索引文件

        Where条件里用不到的字段不创建索引

        数据重复且分布平均的表字段，因此应该职位最经常查询和最经常排序的数据列建立索引，某个数据列包含许多重复的内容，建立索引没有太大实际效果。

索引的数据结构

        索引是一个树形结构：B+树

        首先，二叉树，红黑树(自平衡)被排除，书的高度较高，会影响查询的效率。

        B-树  自平衡多鲁叔  在一个结点中放多个数据，横向扩展，降低了树的高度

        B+树  自平衡多路搜索树  排好序

                   非叶子节点只存储索引，数据都储存在叶子节点

                   会存在一些数据冗余。

                   叶子节点之间会有一个相互指向的指针(对于自增主键，范围查找非常适合)

聚簇索引和非聚簇索引

        聚簇索引：找到了索引，就找到了数据。

        非聚簇索引：索引和数据是分离的，找到了索引，还没有找到数据，需要根据主键，再次徽标查询，才能够查到数据

        innodb:使用主键作为查询条件，就是聚簇索引

        MyISAM:索引文件与数据文件时分开存放的，所以时非聚簇的