聚集索引与非聚集索引
1. 索引类型:
MySQL
索引类型有:唯一索引,主键(聚集)索引,非聚集索引,全文索引。
SQL Sever
索引类型有:唯一索引,主键索引,聚集索引,非聚集索引。
2. 聚集索引-clustered
也叫聚簇索引。
定义:数据行的物理顺序与列值(一般是主键的那一列)的逻辑顺序相同,一个表中只能拥有一个聚集索引。
MySQL
1 | alter table table_name add primary key(colum_name) |
SQL Server
1 | create clustered index clustered_index on table_name(colum_name) |
最好还是在创建表的时候添加聚集索引,由于聚集索引的物理顺序上的特殊性,因此如果再在上面创建索引的时候会根据索引列的排序移动全部数据行上面的顺序,会非常地耗费时间以及性能。
3. 非聚集索引-unclustered
定义:该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同,一个表中可以拥有多个非聚集索引。
分成普通索引,唯一索引,全文索引。
4. 非聚集索引的二次查询问题
非聚集索引叶节点仍然是索引节点,只是有一个指针指向对应的数据块,此如果使用非聚集索引查询,而查询列中包含了其他该索引没有覆盖的列,那么他还要进行第二次的查询,查询节点上对应的数据行的数据。
使用以下语句进行查询,不需要进行二次查询,直接就可以从非聚集索引的节点里面就可以获取到查询列的数据。
1 | select id, name from t1 where name = 'a' |
但是使用以下语句进行查询,就需要二次的查询去获取原数据行的 age:
1 | select name, age from t1 where name = 'a' |
选择适合的索引,因此如果在数据量不大的情况下,SQL Server很有可能不会使用非聚集索引进行查询,而是使用聚集索引进行查询,即便需要扫描整个聚集索引,效率也比使用非聚集索引效率要高。
在MySQL里面就算表里数据量少且查询了非键列,也不会使用聚集索引去全索引扫描,但如果强制使用聚集索引去查询,性能反而比非聚集索引查询要差,这就是两种SQL的不同之处。
非聚集索引其实叶子节点除了会存储索引覆盖列的数据,也会存放聚集索引所覆盖的列数据。
如何解决非聚集索引的二次查询问题
复合索引(覆盖索引),建立两列以上的索引,即可查询复合索引里的列的数据而不需要进行回表二次查询
使用复合索引需要满足最左侧索引的原则,也就是查询的时候如果where条件里面没有最左边的一到多列,索引就不会起作用。