Mysql索引查询

索引分类

• 主键索引主键索引：一张表只能有一个主键索引，不允许重复、不允许为 NULL；

 ALTER TABLE table_name ADD PRIMARY KEY(column_list);

• 唯一索引：数据列不允许重复，允许为NULL 值，一张表可有多个唯一索引，索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。

CREATE UNIQUE INDEX index_name ON `table_name`(`column`(length));
# 或者
ALTER TABLE table_name ADD UNIQUE (column_list); 

• 普通索引：一张表可以创建多个普通索引，一个普通索引可以包含多个字段，允许数据重复，允许 NULL 值插入；

CREATE INDEX index_name ON `table_name`(`column`(length));
# 或者
ALTER TABLE table_name ADD INDEX index_name(`column`(length));

索引名称 index_name 是可以省略的，省略后，索引的名称和索引列名相同。

按列数划分

• 单例索引：一个索引只包含一个列，一个表可以有多个单例索引。
• 组合索引：一个组合索引包含两个或两个以上的列。查询的时候遵循 mysql 组合索引的 “最左前缀”原则，即使用 where 时条件要按照建立索引的时候字段的排列方式放置索引才会生效。

创建索引

-- 创建普通索引
CREATE INDEX index_name ON table_name(col_name);

-- 创建唯一索引
CREATE UNIQUE INDEX index_name ON table_name(col_name);

-- 创建普通组合索引
CREATE INDEX index_name ON table_name(col_name_1, col_name_2, ...);

-- 创建唯一组合索引
CREATE UNIQUE INDEX index_name ON table_name(col_name_1, col_name_2, ...);

-- 修改表结构创建索引
ALTER TABLE table_name ADD INDEX index_name(col_name);

-- 创建表时直接指定索引
CREATE TABLE table_name (
    ID INT NOT NULL,
    col_name VARCHAR (16) NOT NULL,
    INDEX index_name (col_name)
);

删除索引

-- 直接删除索引
DROP INDEX index_name ON table_name;

-- 修改表结构删除索引
ALTER TABLE table_name DROP INDEX index_name;

使用索引查询

问： FORCE INDEX 与 USE INDEX 的区别？
答： FORCE INDEX ：这个语句指示MySQL强制查询使用特定的索引。它会忽略优化器的选择,无论索引的选择性如何，都会使用指定的索引。这意味着即使使用了不太适合的索引，MySQL也会强制使用它。这可能会导致性能下降，因为不适合的索引可能会导致查询变慢。
USE INDEX ：这个语句也允许你指定要使用的索引，但它与FORCE INDEX不同的是，它只是暗示MySQL在可能的情况下使用指定的索引。如果MySQL认为其他索引更适合查询，它仍然可以选择其他索引。这样可以保留一定的灵活性，让MySQL根据实际情况选择最佳的索引。

• USE INDEX

SELECT * 
FROM table_name USE INDEX (index_name_1, index_name_2, ..)
WHERE id = 1

• FORCE INDEX

SELECT * 
FROM table_name FORCE INDEX (index_name_1, index_name_2, ..)
WHERE id = 1

注意事项

1. 添加索引的字段，最好保证有序，如果该字段是无序插入的，那么即使有索引，数据库也可能需要进行额外的操作来定位数据，这可能导致索引不被有效利用，或者导致查询性能下降。