关于索引的学习（编程不良人）

mysql底层用过哪些数据结构

hash、B、B+、二叉树

Hash是等值查询，查找的数据很多都不是等值查询，所有的数据都在内存中浪费内存

二叉树都会因为深度过深而造成IO次数过多，影响读取效率。

索引的分类

主键索引：唯一的primary key，建表时自动创建

唯一索引：索引中所有索引只能出现一次

普通索引：基本的索引类型

全文索引：FULLTEXT 可以在varchar、char、text

组合索引：多个列组成一个索引，专门用于组合搜索

数据库中最常见的慢查询优化方式

• 索引没起作用，检查查询字段，按照最左匹配原则，是否索引失效。

• 将字段很多的表分解成多个表

  对于字段比较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。
  

• 增加中间表

对于需要经常联合查询的表，可以建立中间表以提高查询效率。通过建立中间表，把需要经常联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询，以此来提高查询效率。

• 优化limit查询分页

对于下面的查询：

​ select id,title from collect limit 90000,10;

方法一：虑筛选字段（title）上加索引

   title字段加索引  （此效率如何未加验证） 

方法二：先查询出主键id值

select id,title from collect where id>=(select id from collect order by id limit 90000,1) limit 10;

原理：先查询出90000条数据对应的主键id的值，然后直接通过该id的值直接查询该id后面的数据。

方法三：进行关联查询，尽量减少查询的页面

为什么加索引能优化慢查询

索引可以将无序内容转换为有序的一个集合（相对），就如同新华字典，如果没有目录，那么查询一个汉字就需要很长时间了。

数据页和数据页之间，组成一个双向链表；
每个数据页中的记录，是一个单向链表；

但是添加索引是会影响插入删除效率的。

B+树是一颗平衡树，如果我们对这棵树增删改的话，那肯定会破坏它的原有结构。要维持平衡树，就必须做额外的工作。正因为这些额外的工作开销，导致索引会降低增删改的速度

为什么MYSQL使用B+树

B树的缺点 B+树就衍生出来了

每个节点都有key，同时包含data，单个磁盘块上存储尽可能少的数据增加IO的次数，影响查询效率。

Innodb是通过B+对主键创建索引，叶子节点存储记录，没有主键，会选择唯一键，没有唯一键，会生成一个6维度row_id作为主键，如果索引不是主键，叶子节点存储的还是主键，然后就通过主键找到对应的记录

根据age bir建立的索引 bir、age能否使用索引 ：不能

最左前缀原则：在使用符合索引的时候才可以使用当前索引，mysql为了更好的查询，会动态的调整查询字段顺序以便利用索引。

每一片大概16kb 只存储主键和指针，插入数据时首先会对数据进行排序，然后插入数据。

聚簇索引和非聚簇索引

聚簇索引：数据和索引存储到一起，索引结构的叶子节点保存了行数据。

非聚簇索引：将数据与索引分开存储，索引结构与叶子节点指向了数据的对应位置。

如果查找的不是主键，会根据索引树找到主键索引，然后进行查找。

聚簇索引优点：

1、可以把相关数据保存在一起，例如可以根据用户id来聚集数据，这样只需要从磁盘读取少数的数据页就能获取某个用户的全部邮件，如果没有使用聚簇索引，那么每一封邮件都可能导致一次磁盘的IO。

2、访问数据更快，聚簇索引将索引和数据保存在一个B+树上，因为比非聚簇索引获取数据更快，非聚簇索引还得回表到聚簇索引上根据主键查询需要的数据行。

3、使用索引覆盖扫描的查询可以直接使用页节点中的主键值。

缺点：

1、插入速度严重依赖于插入顺序，按照主键的顺序插入是加载数据页到InnoDB表中速度最快的方式。但是如果不按照主键顺序加载数据，那么在加载完成后最好使用 optimize table 命令重新组织一下表。

2、更新聚簇索引列的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置。插入的时候会面临页分裂的问题。页分裂会导致表占用更多的磁盘空间。

3、二级索引可能比想象的大，因为二级索引的叶子结点保存了引用行的主键

4、二级索引访问需要两次索引查找，要回表，对于innodb，自适应hash索引能够减少这样的重复工作。

什么情况下索引失效？

• 查询语句中有 or 时，如果or前后都是索引，将会被使用，如果有一个不是索引，将不会被使用。
• 复合索引未用左列字段（左前缀原则）
• 使用like以%匹配到索引字段。
• 查询语句中where中有运算、有函数是无法利用索引的。