一、索引的基本原理
索引用来快速地寻找那些具有特定值的记录。如果没有索引，一般来说执行查询时遍历整张表。
索引的原理：就是把无序的数据变成有序的查询
- 把创建了索引的列的内容进行排序
- 对排序结果生成倒排表
- 在倒排表内容上拼上数据地址链
在查询的时候，先拿到倒排表内容，再取出数据地址链，从而拿到具体数据
二、SQL导致索引失效的情况
不要再列上使用函数和进行运算
- 尽量避免使用！=或not in或<> 等否定操作符
- 尽量避免使用or来连接，如果一个有索引一个无索引会放弃索引全表扫描
- 多个单列索引不是最佳选择，mysql只使用一个单列索引
- 复合索引的最左前缀匹配原则，查询条件使用了复合索引的第一个字段，复合索引才会被使用
- 索引不会包含null的列
- 查询条件左右类型不匹配会发生隐式转换，导致索引失效
- like语句左模糊匹配会导致索引失效
三、SQL语句优化
- 为搜索字段和where及order by字段增加索引
- 避免使用select * ，列出需要查询的字段
- 选择正确的存储引擎
- 避免在where语句上进行null值判断，null会导致索引失效而全表扫描
- 避免在where语句上使用否定操作符！= 或 not in 或<>
- 避免在where使用or连接，or的字段不全是索引的话会导致放弃索引全表扫描
- in和not in慎用，会导致全表扫描，使用between或者exists代替in
- 尽量使用全文检索，不要模糊匹配，左模糊的话会导致索引失效
- 避免where使用函数，会放弃索引
- 避免大事务提交操作
-
四、索引覆盖
如果一个索引包含(覆盖)所有需要查询的字段值，我们就称之为“覆盖索引”。
下面的情况适合使用覆盖索引，查询时只扫描覆盖索引，而不需回表查询。 索引条目通常远小于数据行大小，只读取索引，会减少访问量。
- 索引按照列值顺序存储，对于范围查询效率更高。
- InnoDB二级索引如果能够覆盖索引，可以避免对主键索引的二次查询。
-
五、聚簇索引和非聚簇索引（二级索引）
一、聚簇索引
聚簇索引不是一种单独的索引类型，而是一种数据存储方式。InnoDB聚簇索引的数据行实际上存放在索引的叶子页(leaf page)中。“聚簇”代表着数据行和相邻的键值紧凑地存储在一起，InnoDB通过主键聚集数据，如果不存在主键，InnoDB会选择唯一的非空索引，如果也没有唯一非空索引，InnoDB隐式定义一个主键来作为聚簇索引。一张表中只能存在一个聚簇索引。
1、聚簇索引优点
可以把相关数据保存在一起。
- 数据访问速度更快。索引和数据保存在同一棵B+Tree中，因此从聚簇索引获取数据更快些。
- 使用覆盖索引扫描的查询可以直接使用叶节点中的主键值。
-
2、聚簇索引缺点
插入速度严重依赖插入顺序。
- 更新聚簇索引代价高。
- 聚簇索引可能引起页分裂(page split)，页分裂会导致表占用更多的磁盘空间。
- 聚簇索引可能导致全表扫描变慢，尤其是行稀疏，或者页分裂导致数据存储不连续的时候。
- 二级索引(非聚簇索引)可能比想象的要更大，因为在二级索引的叶子节点包含了引用行的主键列。
- 二级索引访问需要两次索引查找，而不是一次。二级索引叶子节点保存的不是指向行的物理位置的指针，而是行的主键值。
3、InnoDB聚簇索引的存储
InnoDB中聚簇索引“就是”表。聚簇索引的每一个叶子节点都包含了主键值、事务ID、用于事务和MVCC的回滚指针以及所有剩余的列。InnoDB二级索引的叶子节点中存储的不是行指针，而是主键值。这样的设计减少了行移动或者页分裂时二级索引的维护成本，但是在二级索引访问时却需要两次索引查找。
InnoDB聚簇索引尽可能地按主键顺序插入数据，并且尽可能地使用单调增加的聚簇键的值来插入新行。二、非聚簇索引
将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行，myisam通过key_buffer把索引先缓存到内存中，当需要访问数据时（通过索引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据，这也就是为什么索引不在key buffer命中时，速度慢的原因
innodb中，在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找，查询辅助索引得到主键值，再用主键值查询聚簇索引得到数据，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引，辅助索引叶子节点存储的不再是行的物理位置，而是主键值六、查看索引使用情况，扫描行数
1、oracle
若有收获，就点个赞吧
0 人点赞
