Mysql-index(索引)

Mysql-index(索引)

MySQL中InnoDB引擎的索引模型

在InnoDB中,表都是根据主键顺序以索引的形式存放的.称为索引组织表.
InnoDB使用了 B+ 树索引模型,所以数据都是存储在 B+ 树中的.

每一个索引在InnoDB中对应一棵 B+ 树.

索引分为主键索引和非主键索引.

  • 主键索引
    主键索引的叶子节点存的是整行数据.在InnoDB中,主键索引也称为聚簇索引(clustered Index).

  • 非主键索引
    非主键索引的叶子节点内容是主键的值,在 InnoDB 里,非主键索引也被称为二级索引(secondary index).

    主键索引和非主键索引的主要区别是:基于非主键索引的查询需要多扫描一下索引树.
    例如,有一个以ID为主键,并有k为二级索引的表.

    • 使用主键索引时,只需要搜索ID的 B+ 树,就可以找到数据.
    • 使用非主键索引时,会先搜索k索引树,得s到ID值,然后再到ID索引树搜索一次.这个过程也称为回表.
    • 对一个索引字段进行检索,采用普通索引还是唯一索引在检索效率上基本上没有区别.因为根据数据页的结构,在读取数据时,会将整个页加载到磁中的.在内存中的判断,对于CPU来说所消耗的时间可以忽略不计.

索引的维护

B+ 树为了维护索引有序性,在插入新值的时候需要做必要的维护.

  • 页分裂
    在插入数据时,如果要插入的数据页已满,根据B+ 树的算法,需要申请一个新的数据页,然后挪动部分数据过去.
    在数据很少,一个页就能放下时,就只有一个根节点,主键和数据也都是保存在根节点.
    当一个页的数据写满后,InnoDB就会分页,分页顺序如下:

    1. 生成新的页2,将页1的内容复制到页2.
    2. 生成新的页3,放入新数据.
    3. 原有的页1依然作为根节点,但是变成了一个不放数据只放索引的页.并且有两个子节点页2,页3.
    4. 页3的数据写满时,会生成新的页4来存放数据.不移动原有页面的任何记录.
      页分裂会影响性能,还会影响数据页的利用率.一个页的数据分为两页,整体空间利用率降低50%.
  • 页合并
    合并可以认为是分裂的逆过程.

如何使用自增主键

关于MySQL自增主键的定义,属性可参照:
https://himawariokb.xsrv.jp/2018/09/03/mysql_auto_increment/

  • 自增主键的插入模式,每次插入一条新的记录,都是追加操作,不涉及挪动记录,不会触发叶子节点的分裂.
  • 用业务逻辑的字段做主键,往往不能保证有序插入,会增加写数据的成本.
  • 主键长度越小,普通索引的叶子节点就越小,普通索引占用的空间就越小.
  • 在InnoDB中,如果一个表没有主键,就默认找一个建了唯一索引的列.如果也没有,则生成一个隐形的字段为主键.
    从性能和存储空间方面考量,自增主键往往是更合理的选择.我们应该尽量使用主键查询.

MySQL中index语句

indexの確認:
mysql> SHOW INDEX FROM tbl_name;
indexの追加:
mysql> ALTER TABLE tbl_name ADD INDEX index_name(col_name);
# char,varchar列にindexを追加する場合:
mysql> ALTER TABLE tbl_bame ADD INDEX index_name(col_var_name(10));
# 複合indexの追加:
mysql> ALTER TABLE tbl_name ADD INDEX index_name(col1_name,col2_name...);
mysql> ALTER TABLE table_name ADD UNIQUE index_name (index_columns);
mysql> ALTER TABLE table_name ADD PRIMARY KEY (index_columns);
mysql> ALTER TABLE table_name ADD FULLTEXT index_name (index_columns);
mysql> ALTER TABLE table_name ADD SPATIAL index_name (index_columns);
indexの削除:
mysql> ALTER TABLE tbl_name DROP INDEX index_name;
mysql> DROP INDEX index_name ON table_name;

发表评论

邮箱地址不会被公开。 必填项已用*标注