MYSQL索引

MYSQL索引

• 聚集索引（主键索引）：是一种存储方式。InnoDB 的聚簇索引实际上在同一个结构中保存了 B-Tree 索引和数据行，非叶子结点储存索引，叶子结点储存完整的数据记录
• 非聚集索引：（MYISAM）索引文件和数据文件分离
• 辅助索引（二级索引）：非主键索引，叶子节点=主键值+书签
• 覆盖索引：就是select的数据列只用从索引中就能够取得，不必从数据表中读取，换句话说查询列要被所使用的索引覆盖，也即，索引包含了查询正在查找的所有数据
  • 当发起一个被索引覆盖的查询(也叫作索引覆盖查询)时，在EXPLAIN的Extra列可以看到“Using index”的信息,从执行结果上看，这个SQL语句只通过索引，就取到了所需要的数据，这个过程就叫做索引覆盖
• 全文索引：先建立索引，再对索引进行搜索的过程就叫全文检索

优势：

• 提高数据检索的效率，降低磁盘IO成本
• 数据排序，降低CPU消耗

劣势：

• 索引本质也是一张表，保存着索引字段和指向实际记录的指针，所以也要占用数据库空间，一般而言，索引表占用的空间是数据表的1.5倍
• 索引虽然能提高查询速度，但是会降低表的更新速度，因为更新数据时，也要更新索引

基本语法：

--创建
CREATE [UNIQUE] INDEX indexName ON mytable(columnname(length));
ALTER mytable ADD [UNIQUE] INDEX [indexName] ON (columnname(lenght));
--删除
DROP INDEX [indexName] ON mytable;
--查看
SHOW INDEX FROM table_name\G

3.1 B+Tree索引

存储结构

存储器范围比较大，但是数据具体怎么存储，有自己的最小存储单元。

1、数据持久化存储磁盘里，磁盘的最小单元是扇区，一个扇区的大小是 512个字节

2、文件系统的最小单元是块，一个块的大小是 4K

3、InnoDB存储引擎，有自己的最小单元，称之为**页**，一个页的大小是16K

B+ 树是如何检索记录？

• 首先找到根页，你怎么知道一张表的根页在哪呢？
• 其实每张表的根页位置在表空间文件中是固定的，即page number=3的页
• 找到根页后通过二分查找法，定位到id=5的数据应该在指针P5指向的页中
• 然后再去page number=5的页中查找，同样通过二分查询法即可找到id=5的记录

特别说明：

• 查询数据库时，不论读一行，还是读多行，都是将这些行所在的整页数据加载，然后在内存中匹配过滤出最终结果。

• 表的检索速度跟树的深度有直接关系，毕竟一次页加载就是一次IO，而磁盘IO又是比较费时间。对于一张千万级条数B+树高度为3的表与几十万级B+树高度也为3的表，其实查询效率相差不大。

  千万级的数据存储只需要约3层B+树，查询数据时，每加载一页（page）代表一次IO。所以说，根据主键id索引查询约3次IO便可以找到目标结果。
  

3.2 hash索引