MySQL之索引

MySQL索引有哪些

索引是为了加速对表中数据行的检索而创建的一种分散存储的数据结构，由存储引擎实现。mysql中索引有：b+tree索引、hash索引、全文索引、rtree空间索引，最普遍使用的是b+tree索引，hash索引在memory引擎中默认使用，innodb引擎在对频繁查找的行上为了优化会自动为表建立hash索引。使用innodb和myisam引擎可以在字符串文本数据上建立全文索引。

B+树索引

B+树索引优势

数据库的数据是存储在磁盘上，查找数据就是一次磁盘io操作。如果不使用索引，会对全表的数据进行扫描查找，此时复杂度O(N)。而采用平衡二叉树复杂度为O(logN)，为什么最后选择B+树呢？
1.mysql读取数据是按页来读，默认是一页对应磁盘4个扇区，一个扇区4K大小，为了最大程度利用mysql一次读取数据的大小，而选择将树节点中的内容增多。（B树）
2.B+关键字对应的数据保存在叶子节点，而且叶子节点是顺序排列的，相邻节点具有顺序引用的关系，提高了排序能力和范围查找能力。使磁盘IO变为连续IO，读写能力更强。（B+树）
3.同时B+树节点内不存放数据，比B树节点的key更多，扫表能力更强。

innodb和myisam引擎上的体现

myisam引擎中会给表建立两个文件MYI文件、MYD文件，MYI文件存放索引，叶子节点存储的是对应行数据的磁盘地址。MYD文件存放的是磁盘地址与行数据的信息。从myi文件中查找对应的磁盘地址，然后从myd文件中读取数据。
innodb引擎中会建立一个IBD文件，根据主键建立聚集索引，即叶子节点上就存放了行数据信息。其他索引为辅助键索引，叶子节点上存放的是行数据的主键。也就是说如果通过非主键字段查找数据，会查找两次b+树。原因：
1.innodb建立初衷认为主键查询是最常用的。
2.数据更新后，行数据的磁盘地址会有改变 ，myisam需要维护相关多个索引，而innodb只要主键不变辅助索引就不需要维护。

怎么设置索引

1，经常用的列优先 【最左匹配原则】
2，选择性（离散度）高的列优先【离散度高原则】 ，选择离散型高的列建立索引，索引列的离散性越高，树的选择性就越好。
3，宽度小的列优先【最少空间原则】，占用空间少，节点可以存储更多的key。

补充

覆盖索引

如果查询列可通过索引节点中的关键字直接返回，则该索引称之为 覆盖索引。
覆盖索引可减少数据库IO，将随机IO变为顺序IO，可提高查询性能

索引小技巧

索引列的数据长度能少则少。
索引一定不是越多越好，越全越好，一定是建合适的。
匹配列前缀可用到索引 like 9999%，like %9999%、like %9999用不到索引； Where 条件中 not in 和 <>操作无法使用索引； 匹配范围值，order by 也可用到索引；
多用指定列查询，只返回自己想到的数据列，少用select *；
联合索引中如果不是按照索引最左列开始查找，无法使用索引；
联合索引中精确匹配最左前列并范围匹配另外一列可以用到索引；
联合索引中如果查询中有某个列的范围查询，则其右边的所有列都无法使用索引