数据库索引结构特点，聚集非聚集区别，及失效案例

索引特点

主流索引结构：b+树和hash
hash类型的索引：查询单条快，范围查询慢
btress类型的索引：b+树，层数越多，查询量指数级增长(mysql,innodb默认)

使用场景：

1. 索引的功能就是加速查找
2. mysql中的primary key，unique也都是索引，这些索引除了加速查找以外，还有约束的功能
3. 一定是为搜索条件的字段创建索引，比如select * from t1 where age>5;就需要为age加上索引
4. 在表中已经有大量数据的情况下，建索引会很慢，且占用硬盘空间，增删改都很慢，只有查询快，比如create index idx on user(id);会扫描表中所有的数据，然后以id为数据项，创建索引结构，存放于硬盘的表中，建完以后，再查询就快多了
5. (select * from user where id = 123 and name = “abc”)添加联合索引后(create index idx on user<id,name>)查询速度会变快

特点：

1. 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
2. 可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
3. 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
4. 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
5. 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。

• 索引表中的每个字段会降低写入性能
• 建议使用表中的唯一值为字段上索引
• 在关系数据库中充当外键的字段必须建立索引，因为他们有助于跨多表进行复杂查询
• 索引还会占用磁盘空间，所以选择的时候要小心

聚集索引

聚集索引（clusetered index）表中数据行的物理顺序和列值顺序相同，列值特指有主键的那一列。一个表中只能有一个聚集索引
主键一般会默认创建聚集索引

区别：
聚集索引在查询方面，速度往往更占优势，mysql主键就是聚集索引，一表中只能有一个索引
非聚集索引（普通索引，唯一索引，全文索引）自定规则，使用索引表

非聚集：

1. 普通索引index：加速查找
2. 唯一索引：
   主键索引：primary key：加速查找+约束，不为空且唯一
   唯一索引：unique：加速查找+约束，唯一
3. 联合索引
   -primary key(id,name)：联合主键索引
   -unique(id,name)：联合唯一索引
   -index(id,name)：联合普通索引
4. 全文索引fulltext：用于搜索很长一篇文章的时候，效果最好

总结：聚集比非聚集的查询效率高，聚集一表一个，非聚集一表多个

失效案例

条件中用or，即使其中有条件带索引，也不会使用索引查询，查询尽量不要用or，要用in或者exist

常见sql优化手段

1. 避免全表扫描
2. 避免索引失效
3. 避免排序，如果不能避免，尽量选择索引排序
4. 避免查询不必要的字段
5. 避免临时表的创建，删除

全表扫描往往发生下面几种情况：
SQL的on子句或者where子句涉及到的列上没有索引；
表数据量很小，走索引查询比全表扫描更麻烦，这对于少于10行行长度较短的表来说很常见

索引失效情况：
不在索引列上做任何操作（计算，函数，自动or手动类型转换）这样会导致索引失效而转向全表扫描
存储引擎不能使用索引中范围条件右边的列，这个是因为age中查询时范围查询了pos列导致索引就没有生效
尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致））减少select *

对于mysql：
mysql在使用不等于（!=或者<>）的时候无法使用索引会导致全表扫描
is null, is not null也无法使用索引
like通配符开头"%aaa"，索引失效会变成全表扫描