数据库索引

索引原理

磁盘预读性原理

• 一个block块 4096字节/9ms(linux系统)

树

• 根节点 root
• 分支节点 branch
• 叶子节点 leaf
• 平衡树 balance tree --B树
  • 1.数据存储在分支节点和叶子节点
  • 2.导致了树的高度增加，找到一个数据的时间不稳定
  • 3.在查找范围的时候不够便捷

把数据和索引存在一起的叫聚集索引

聚集索引/聚簇索引：叶子节点会存储整行数据 -- innodb的主键

• 数据直接存储在索引的叶子节点

辅助索引/非聚集索引：除了逐渐之外的普通索引都是辅助索引，一个辅助索引没办法查到整行数据，需要会聚集索引在查一次(回表)

• 数据不直接存储在索引的叶子节点

b+树 是为了更好的处理范围问题 在B树的基础上有所优化

• 1.数据部存储在分支节点上了，而是存储在叶子节点上(树的高度降低了)
• 2.在叶子节点与叶子节点之间添加的双向指针提高了在查找范围的效率

mysql中innodb存储引擎的所有的索引树都是b+树

索引的优缺点：

• 优点：加快查询的速度
• 缺点：降低写的效率，占用更多的磁盘空间

操作索引

• 辅助索引：index unique
• 聚集索引：primary key
• 建了主键就不用创建索引

创建索引：

• create index 索引名 on 表名(字段名);
• create index index_id on s1(id);

删除索引：

• drop index 索引名 on 表名;
• drop index index_id on s1;

正确使用索引

1.只有对创建了索引的字段进行条件筛选的时候效率才能提高

2.索引对应的列不能做条件运算，也不能使用函数

3.当某一列的区分度非常小(重复率高)不适合创建索引

4.当范围作为条件的时候，查询结果的范围越大越慢，越小越快

5.like关键字：如果使用%/_开头都无法命中索引

6.多个条件：如果只有一列创建了索引，条件用and相连，那么可以提高查询效率，用or相连，不会提高查询效率

and:
    select * from s1 where id = 1000000 and email = '123456789@qq.com';
or:
    select * from s1 where id = 1000000 or email = '123456789@qq.com';
	

7.联合索引(联合索引条件只能用and相连，要副总最左前缀原则，从使用了范围的条件开始之后的索引都失效)

explain 执行计划

explain select * from s1 where id = 000000 and email = '123456789@qq.com';

覆盖索引

• 在查询的过程中不需要回表 -- 覆盖索引 useing index

  select count(*) from s1 where id < 1000;
  

索引合并

explain select * from s1 where id = 1000000 or email = '123456789@qq.com';