mysql 索引类型以及数据结构

索引

• mysql innodb不支持using hash，内部会有自动生成哈希索引

• 全文索引 fulltext

  • mysql5.6支持英文全文索引，参数：show variables like “%ft%”
  • mysql5.7.6 支持中文全文索引，参数：show variables like “%ngram%”

• 单列索引最大长度

  ​ myisam表，单列索引，最大长度不能超过 1000 bytes；

  ​ innodb表，单列索引，最大长度不能超过 767 bytes；

  ​ utf8 编码时 一个字符占三个字节 varchar 型能建立索引的最大长度分别为 myisam 1000/3 333 innodb 767/3 255

  ​ utf8mb4 编码时 一个字符占四个字节 varchar 型能建立索引的最大长度分别为 myisam 1000/4 250 innodb 767/4 191

• 不管以任何方式查询表， 最终都会利用主键通过聚集索引来定位到数据， 聚集索引（主键）是通往真实数据所在的唯一路径。除了多字段索引查询

• 表都是以树的形式形式存储

• b-树

  

• b+树

  

  所以innodb建议表都有主键自增，一般都是必须要有

• innodb一般使用b+树，比起b-的优点

  • 由于 B+Tree 所有的数据都在叶子结点，并且结点之间有指针连接，在找大于某个关键字或者小于某个关键字的数据的时候，B+Tree 只需要找到该关键字然后沿着链表遍历就可以了，而 B-Tree 还需要遍历该关键字结点的根结点去搜索。
  • 由于 B-Tree 的每个结点(这里的结点可以理解为一个数据页)都存储主键+实际数据，而 B+Tree 非叶子结点只存储关键字信息，而每个页的大小是有限的，所以同一页能存储的 B-Tree 的数据会比 B+Tree 存储的更少。这样同样总量的数据，B-Tree 的深度会更大，增大查询时的磁盘 I/O 次数，进而影响查询效率。

• 非聚集索引

  • 非聚集索引和聚集索引存储结构是一样的，只不过叶子节点的数据部分存的不再是具体的数据，而是数据的聚集索引的key。
  • 回表，通过非聚集索引查找的过程是先找到该索引 Key 对应的聚集索引的 Key，然后再拿聚集索引的 Key 到主键索引树上查找对应的数据，这个过程称为回表!

• 联合索引

  • 查询遵循最左匹配原则，例如：

    ALTER TABLE table_name ADD INDEX (col1,col2,col3)，那么只能使用col1和col1,col2和col1,col2,col3三种组合；当然，col1,col3组合也可以，但实际上只用到了col1的索引，col3并没有用到！

  • where条件遵循最左匹配的时候列是可以乱序的，mysql有自己的语句最优查询器，例如

    ELECT * FROM table_name WHERE col1=‘X’,col2=‘XX’,col3=‘XXX’;

    和 SELECT * FROM table_name WHERE col3=‘X’,col2=‘XX’,`col1=‘XXX’;

    效果是一样的

• 覆盖索引

  • 是一种特定于具体select语句的联合索引，直接通过索引获取查询结果，不需要回表

• InnoDB和MylSAM

  MylSAM

  

  • InnoDB 引擎数据在物理上是按主键顺序存放，而 MyISAM 引擎数据在物理上按插入的顺序存放。
  • 在使用非聚集索引查找数据的时候通过非聚集索引树就能直接找到数据的地址了，不需要回表，这比 InnoDB 的搜索效率会更高呢!