MySQL的索引(面试常问)

为了帮我们更快地检索数据，索引就诞生了。

MySQL的索引

索引是MySQL高效获取数据的一种数据结构，是对表中一列或多列的值进行排序的数据结构，使得数据库系统可以更快地定位到所需的数据行。（类似于我们阅读文档里的目录）

索引的优缺点

优点

1.提高数据的检索速度，降低数据库IO成本:
使用索引的意义就是通过缩小表中需要查询的记录的数目从而加快搜索的速度。
2.降低数据排序的成本，降低CPU消耗:索引之所以查的快，
是因为先将数据排好序，若该字段正好需要排序，则正好降低了排序的成本

小记：一提三降：*提高数据的检索速度，降低数据库IO成本，降低数据排序的成本，降低CPU消耗

缺点：

1。占用存储空间:索引实际上也是一张表，记录了主键与索引字段，一股以索引文件的形式存储在磁盘上。
2.降低更新表的速度:表的数据发生了变化，对应的索引也需要—起变更，
从而减低的更新速度。否则索引指向的物理数据可能不对，这也是索引失效的原因之一

MySQL索引的类型和创建方式

MySQL索引的类型

1. 主键索引
   索引列中的值必须是唯一的，不允许有空值。
2. 普通索引
   MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值。
3. 唯一索引
   索引列中的值必须是唯一的，但是允许为空值。
4. 全文索引
   只能在文本类型CHAR,VARCHAR,TEXT类型字段上创建全文索引。字段长度比较大时，如果创建普通索引，在进行like模糊查询时效率比较低，这时可以创建全文索引。MyISAM和InnoDB中都可以使用全文索引。
5. 空间索引
   MySQL在5.7之后的版本支持了空间索引，而且支持OpenGIS几何数据模型。MySQL在空间索引这方面遵循OpenGIS几何数据模型规则。
6. 前缀索引
   在文本类型如CHAR,VARCHAR,TEXT类列上创建索引时，可以指定索引列的长度，但是数值类型不
   能指定。

创建方式

新建表中添加索引

主键索引

CREATE TABLE table_name (
    id INT PRIMARY KEY,
    ...
);

普通索引

CREATE TABLE table_name (
    ...
    INDEX index_name (column_name),
    ...
);

唯一索引

CREATE TABLE table_name (
    ...
    UNIQUE INDEX index_name (column_name),
    ...
);

全文索引

CREATE TABLE table_name (
    ...
    FULLTEXT INDEX index_name (text_column),
    ...
);

空间索引

CREATE TABLE table_name (
    ...
    SPATIAL INDEX index_name (geometry_column),
    ...
);

已建表中添加索引

主键索引

ALTER TABLE table_name ADD PRIMARY KEY (id);

普通索引

ALTER TABLE table_name ADD INDEX index_name (column_name);

唯一索引

ALTER TABLE table_name ADD UNIQUE INDEX index_name (column_name);

全文索引

ALTER TABLE table_name ADD FULLTEXT INDEX index_name (text_column);

空间索引

ALTER TABLE table_name ADD SPATIAL INDEX index_name (geometry_column);

修改表的方式添加索引

主键索引

ALTER TABLE table_name MODIFY COLUMN id INT PRIMARY KEY;

普通索引

ALTER TABLE table_name ADD INDEX index_name (column_name);

唯一索引

ALTER TABLE table_name ADD UNIQUE INDEX index_name (column_name);

全文索引

ALTER TABLE table_name ADD FULLTEXT INDEX index_name (text_column);

空间索引

ALTER TABLE table_name ADD SPATIAL INDEX index_name (geometry_column);

其他类型（按照索引列数量分类）

1. 单列索引
2. 组合索引
   组合索引的使用，需要遵循最左前缀匹配原则（最左匹配原则）。一般情况下在条件允许的情
   况下使用组合索引替代多个单列索引使用。

创建方式

新建表中添加索引

单列索引

CREATE TABLE table_name (
    ...
    INDEX index_name (column_name),
    ...
);

组合索引

CREATE TABLE table_name (
    ...
    INDEX index_name (column1, column2),
    ...
);

已建表中添加索引

单列索引

ALTER TABLE table_name ADD INDEX index_name (column_name);

组合索引

ALTER TABLE table_name ADD INDEX index_name (column1, column2);

修改表的方式添加索引

单列索引

ALTER TABLE table_name ADD INDEX index_name (column_name);

组合索引

ALTER TABLE table_name ADD INDEX index_name (column1, column2);

建了一个公众号(名字叫音耀)，后续会在上面更新一些有用资源和笔记，大家有兴趣的话可以加一下谢谢了。