MySQL面经【索引】

索引是什么

索引相当于一本书的目录，通过目录可以快速地找到对应的资源。 添加索引是给某一个字段，或者某些字段添加索引。

索引的种类

• 主键索引（PRIMARY） 数据列不允许重复，不允许为NULL，⼀个表只能有⼀个主键。
  • 唯⼀索引（UNIQUE） 数据列不允许重复，允许为NULL值，⼀个表允许多个列创建唯⼀索引。

    可以通过 ALTER TABLE table_name ADD UNIQUE (column);

    创建唯⼀索引 可以通过 ALTER TABLE table_name ADD UNIQUE (column1,column2);

    • 普通索引（INDEX）

      可以通过 ALTER TABLE table_name ADD INDEX index_name (column); 创建普通索引

      可以通过 ALTER TABLE table_name ADD INDEX index_name(column1,column2,column3); 创建组合索引

      全⽂索引（FULLTEXT）

      • 可以通过 ALTER TABLE table_name ADD FULLTEXT (column); 创建全⽂索引

        索引的特点

        1. 索引加快数据库的检索速度
        2. 索引降低了插⼊、删除、修改等维护任务的速度
        3. 唯⼀索引可以确保每⼀⾏数据的唯⼀性
        4. 通过使⽤索引，可以在查询的过程中使⽤优化隐藏器，提⾼系统的性能 索引需要占物理和数据空间

        索引并⾮是越多越好，创建索引也需要耗费资源，

        ⼀是额外占用了数据库的存储空间

        ⼆是在插⼊和删除时要花费较多的时间维护索引

        创建索引的三种方式

        1. 创建表时创建索引

        在执⾏CREATE TABLE时创建索引

        CREATE TABLE user_index2 (id INT auto_increment PRIMARY KEY,first_name VARCHAR (16),last_name VARCHAR (16),id_card VARCHAR (18),information text,KEY name (first_name, last_name),FULLTEXT KEY (information),UNIQUE KEY (id_card));

        1. 使⽤ALTER TABLE命令去增加索引

         ALTER TABLE table_name ADD INDEX index_name (column_list);

        ALTER TABLE⽤来创建普通索引、UNIQUE索引或PRIMARY KEY索引。

        其中table_name是要增加索引的表名，column_list指出对哪些列进⾏索引，多列时各列之间⽤逗号分隔。 索引名index_name可⾃⼰命名，缺省时，MySQL将根据第⼀个索引列赋⼀个名称。另外，ALTER TABLE允许在单个语句中更改多 个表，因此可以在同时创建多个索引。

        ALTER TABLE table_name ADD INDEX index_name (column_list);

        1. 使⽤CREATE INDEX命令创建

           CREATE INDEX index_name ON table_name (column_list);

        CREATE INDEX可对表增加普通索引或UNIQUE索引。（但是，不能创建PRIMARY KEY索引）

        删除索引

        根据索引名删除普通索引、唯⼀索引、全⽂索引：

        alter table 表名 drop KEY 索引名

        alter table user_index drop KEY name;alter table user_index drop KEY id_card;alter table user_index drop KEY information;

        删除主键索引： alter table 表名 drop primary key （因为主键只有⼀个）。

           alter table user_index drop primary key

        这⾥值得注意的是， 如果主键⾃增长，那么不能直接执⾏此操作（⾃增长依赖于主键索引）：

        alter table user_index-- 重新定义字段MODIFY id int,drop PRIMARY KEY

        但通常不会删除主键，因为设计主键⼀定与业务逻辑⽆关。