MySQL 为表字段添加索引

1.添加 PRIMARY KEY（主键索引）

  1. ALTER TABLE `table_name` ADD PRIMARY KEY ( `column` )

2.添加 UNIQUE(唯一索引)

  1. ALTER TABLE `table_name` ADD UNIQUE ( `column` )

3.添加 INDEX(普通索引)

  1. ALTER TABLE `table_name` ADD INDEX index_name ( `column` )

4.添加 FULLTEXT(全文索引)

  1. ALTER TABLE `table_name` ADD FULLTEXT ( `column`)

5.添加多列索引

  1. ALTER TABLE `table_name` ADD INDEX index_name ( `column1`, `column2`, `column3` )

为什么MySQL的索引要使用B+树而不是B树？

  1. 因为需要顺序和范围查找，所以不能用Hash，而是用树。
  2. InnoDB存储引擎的最小存储单元是页（page，默认16Kb），页可以用于存放数据也可以用于存放键值+指针，在B+树中叶子节点存放数据，非叶子节点存放键值+指针。因为B树不管叶子节点还是非叶子节点，都会保存数据（数据肯定比指针占空间多），这样导致在非叶子节点中能保存的指针数量变少（有些资料也称为扇出）（即M阶树不能设得很大）。指针少的情况下要保存大量数据，只能增加树的高度，导致IO操作变多，查询性能变低。
  3. 能采取索引+数据分开存的前提是，B+树 父节点存有右孩子的第一个元素的索引，这样所有的键值都会记录 在叶子节点，而B数只能分布于所有叶子和非叶子节点。

MySQL引擎B+数的高度一般为1~3。
参考：面试官：为什么MySQL的索引要使用B+树，而不是其它树？比如B树？
image.png
image.png
数据结构与算法

索引类型

主键索引(Primary Key)

二级索引(辅助索引)

二级索引的叶子节点存储的数据是主键，在根据辅助索引查找时，则需要先取出主键的值，再走一遍主索引。

唯一索引(Unique Key)

建立唯一索引的目的大部分时候都是为了该属性列的数据的唯一性，而不是为了查询效率。

普通索引(Index)

前缀索引(Prefix)

前缀索引是对文本的前几个字符创建索引，相比普通索引建立的数据更小。

全文索引(Full Text)

索大文本数据中的关键字的信息。

聚集索引与非聚集索引

聚集索引即索引结构和数据一起存放的索引。

  • 主键索引属于聚集索引，b+树的叶子节点存放了索引+数据。
  • 普通索引属于非聚集索引，b+树的叶子节点存放了索引+主键id。

    覆盖索引

    覆盖索引即需要查询的字段正好是索引的字段，那么直接根据该索引，就可以查到数据了， 而无需回表查询。 ```sql create table user ( id BIGINT(20) AUTO_INCREMENT PRIMARY KEY, name VARCHAR(40) NOT NULL, age TINYINT(3) NOT NULL );

CREATE INDEX user_name_index ON user(name);

不是覆盖索引，因为索引不包含age信息

SELECT name, age FROM user WHERE name LIKE ‘%zhang’;

是覆盖索引，因为索引不包含age信息

SELECT name FROM user WHERE name LIKE ‘%zhang’; ; ```

索引的注意事项

=> 创建索引的注意事项
=> 使用索引的一些建议

  • 在设计表的时候，不建议使用过长的字段作为主键，也不建议使用非单调的字段作为主键，这样会造成主索引频繁分裂。
  • 聚集索引即索引结构和数据一起存放的索引。主键索引属于聚集索引。聚集索引依赖于有序的数据 ：因为 B+树是多路平衡树，如果索引的数据不是有序的，那么就需要在插入时排序，如果数据是整型还好，否则类似于字符串或 UUID 这种又长又难比较的数据，插入或查找的速度肯定比较慢。

    索引失效

    一张图搞懂MySQL的索引失效
    image.png