Mysql索引

Mysql索引

​ 索引就是帮助Mysql高效获取数据的排好序的数据结构

​ 如果没有索引，Mysql会全盘扫描进行查找数据，直到比对成功为止，每一次查询都要对磁盘进行io操作，所以效率肯定不高的

索引数据类型

索引数据类型Hash

​ 因为hash值是无序的值，因为无需就不能进行一个范围查找，因为数据有很多大于小于的查询，所以使用hash的话就不能进行范围的查找还有排序操作，因为hash值是无序的。还有一个问题就是我的值不相同但是我的hash值不相同，这样的查找速度是比较慢了，他就得需要一个一个的去比对。

索引数据类型二叉树

​ 当树越高的时候查找速度就越慢，它的缺点插入耗费时间，会出现回旋查找的问题。

索引数据类型B-Trees

​ B-Trees的最大特点就是一个树可以存放2个值，但是也会出现回旋查找问题。

索引数据类型为什么选择B+Tree

​ B+Tree的叶子节点其实就是一个链表，他把你所有的数进行了一个链表的排序，它和BTree有一个共同特点，就是一个节点可以存储2个值，B+Tree的高度很低，也解决了回旋查找的问题，B+tree的范围查找速度是特别高的，这也就是为什么要在排序的时候选择索引排序，我们按照索引排序就不会产生这个文件的排序，所以这也就是为什么采用B+树的原因

下面的链表叫做叶子节点，上面的叫非叶子节点，叶子节点又存key又存value，value就是数据的地址

Mysql如何通过索引定位到真实的数据

对比项	MyISAM	InnoDB
主外键	不支持	支持
事务	不支持	支持
行表锁	表锁	行锁
缓存	只存储缓存，不存储真实数据	不仅存储缓存索引，还要存储缓存和真实数据，对内存要求比较高，而且内存大小能决定效率
表空间	小	大
关注点	性能	事务

MyISAM(非聚集索引)

​ 非聚集索引就是将数据与索引分开存储，表存储顺序与

通过物理存储的地址去数据文件里面查找数据，它不管通过主键索引还是普通索引，都是通过找到物理地址然后去数据文件里查找数据的

InnoDB(聚集索引)

聚集索引不像非聚集索引一样存储物理内存地址，它存放的是真实数据，如果通过普通索引查询那么就先找到这条数据的主键，然后再去通过主键查找。

为什么不要用select *from？为什么要求使用覆盖索引

​ 因为他会造成不必要的磁盘I/O，增加IO的开销

​ 还会加大网络消耗，尤其是text类型的字段

​ 无法使用覆盖索引

​ 可能会拖慢join的连接查询

索引的优化

索引的单表情况的优化

​ 比如查询id为1 & comments 大于1的情况下，views最多的article_id

select id,author_id from article where id = 1 and comments > 1 order by views desc limit 1

使用explain 分析下此sql

​ 我们发现type为all（全盘扫描），然后Extra的结果是Using where；Using filesort

所以我们要给where后面的建立索引,order by也要，

​ 要注意范围后的索引失效

两表优化

​ 左连接把索索引加右边，右连接加左面，最好左右都加上

三表优化

​ 永远用小结果集驱动大的结果集，优先优化最NestedLoop最内层的循环

索引的注意

​ 想要做一个好的sql优化，where后面常用的查询字段最好设置上索引，一定要先注意最佳左前缀法则，意思就是把区别度最高的放左侧，不能在索引列上做任何操作（如计算、函数、（自动or手动）类型转换）都会导致索引失效变成全盘扫描，如果某一个索引使用了范围，那么他后面的索引全部失效，尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select *，使用is null,is not null也会导致索引失效，使用> < != <>操作也会导致索引失效,使用%like 开头的也会导致索引失效，少用or，因为使用or连接会导致索引失效，varchar必须要带单引号