Mysql索引 小知识点

索引缺点

【1】维护索引要耗费数据库资源
【2】索引会占用磁盘空间
【3】做增删改操作的时候，因为要维护索引，所以会影响效率

索引分类

【1】主键索引
主键设定好之后会自动创建主键索引，innodb中为聚簇索引，主键索引列的值不能为null
【2】普通索引
除了主键索引，我们自己另外在别的列上加的索引就是普通索引，一张表可以有很多个普通索引
【3】唯一索引
索引列的值不能重复，并且最多能有一个null
【4】复合索引（必须遵从最左前缀原则）
多个列共同定义的索引：
比如我的查询条件既有年龄又有收入水平，就可以把这两列数据一起建个索引
【5】全文索引
用的少！因为要对词做分词操作，所以只能加在字符类型列上比如char，varchar，text类型的列

【最左前缀】
这里单独说一下复合索引查询的时候必须要遵从的最左前缀原则：
比如有一个复合索引name age bir，必须要满足从左往右的顺序，才能走索引，比如name age bir或者name age，像age bir就不行了，但是如果是name bir age呢？答案是可以的，因为innodb引擎会自动调整查询的字段以便更好的利用索引，所以这里存储引擎会自动帮我们调整成name age bir

索引底层原理

假如有一张表：
CREATE TABLE t_emp(id INT PRIMARY KEY, name VARCHAR(20), age INT);
乱序插入添加5个数据
INSERT INTO t_emp VALUES(4,‘d’,21);
INSERT INTO t_emp VALUES(2,‘b’,21);
INSERT INTO t_emp VALUES(5,‘e’,23);
INSERT INTO t_emp VALUES(1,‘a’,22);
INSERT INTO t_emp VALUES(3,‘c’,25);
这时候查询表发现，竟然是排好序的
SELECT * from t_emp;

创建表的时候虽然没有手动添加索引，但是主键索引是自动生成的，所以说，主键索引其实自动帮我们做了排序
mysql底层用B+树实现索引，叶子节点存主键，数据和指针，其他非叶子节点只存主键和指针，因为innodb每次从磁盘读取数据的大小是不变的16KB，所以这样存的话每次读取的个数就会更多；所有叶子节点的数据按主键从小到大排好序，每个叶子节点之间用链表连接

聚簇索引和非聚簇索引

聚簇索引
数据和索引在一起，叶子节点存数据（一张表里面一定会有一个聚簇索引，默认主键，没有主键的话存储引擎会自动选一个不重复且非空的索引，如果也没有，会隐式的定义一个主键来作为聚簇索引）
非聚簇索引
数据和索引不在一起，叶子节点只存主键和指针，查到了主键再根据主键去聚簇索引找数据

【PS】为什么非聚簇索引要存主键值，不直接存数据的地址呢？这是因为如果存数据地址的话，在做增删改操作的时候，会导致数据的地址发生变化，这样就要修改索引，但是主键是不会变的，所以要存主键而不存地址

所以说，查询的时候其实分两种情况。
【1】根据主键查询，直接走聚簇索引
【2】根据其他列的普通索引，唯一索引或者是复合索引查，先走非聚簇索引查到主键，再根据主键去聚簇索引查数据

【聚簇索引的优势】
1.由于聚簇索引数据存在叶子节点中，同一页读取的数据有很多条，当读取某一页的时候会把这一整页都加载到Buffer中，所以后面再查这一页里面的数据的时候会直接从Buffer中读取，不用再从树顶往下找了
2.如果数据发生改变的话，我们只用维护聚簇索引，不用维护非聚簇索引，因为主键是不变的

【聚簇索引要注意什么】
1.用主键作为聚簇索引的时候主键不要用uuid，因为uuid不好排序，用int会更好因为int本来就是自增的，插入数据的时候不会对前面的数据造成任何影响

什么情况会导致索引失效

【1】模糊查询，%开头的时候，会导致全表扫描，没办法用索引

【2】复合索引没有遵循最左前缀

【3】查询条件用or，or的左右都必须有索引，如果有一边没有索引，查询就不会走索引

【4】出现了类型转换，比如varchar没有加单引号，变成了int

【5】索引列做了计算或者用了函数

【6】还有一种是表的数据量很小，mysql觉得用全表扫描更快