【Mysql】三大组合拳 （索引、锁、事务） 之 重拳--“索引”
站长
· 阅读数 1
1、什么是索引
索引是一种帮助数据库系统提高查询效率的数据结构。它通过按照特定的规则对数据进行排序和存储，从而加快数据的检索速度。
索引的优点
- 大大加快数据查询速度
索引的缺点
- 维护索引需要耗费数据库资源
- 索引需要占用磁盘空间
- 数据的增删改操作需要维护索引，影响操作速度
2、索引分类
- 主键索引：设定为主键后数据库会自动建立索引，InnoDB为聚簇索引
- 单值索引：一个索引只包含单个列，一个表可以有多个单列索引
- 唯一索引：索引列的值必须唯一，但允许有空值
- 复合索引：一个索引包含多个列
- 全文索引：全文索引类型为FULLTEXT，支持值的全文查找，允许在这些索引列中插入重复值和空值，但只有MyISAM存储引擎支持全文索引。
（注：MyISAM存储引擎支持全文索引，而InnoDB存储引擎也在MySQL5.6版本之后开始支持全文索引）
3、索引的基本操作
主键索引
在创建表时，可以通过指定一个列作为主键来自动创建主键索引。例如：
CREATE TABLE t_user (
id VARCHAR(20) PRIMARY KEY,
name VARCHAR(20)
);
这样就可以自动创建一个名为 "PRIMARY" 的主键索引。要查看表中的索引，可以使用如下命令：
SHOW INDEX FROM t_user;
单列索引
在创建表时，可以通过在列定义之后使用 KEY 关键字来创建单列索引。如果要在建表后创建单列索引，可以使用 CREATE INDEX 语句。例如：
-- 建表时创建单列索引
CREATE TABLE t_user (
id VARCHAR(20) PRIMARY KEY,
name VARCHAR(20),
KEY (name)
);
-- 建表后创建单列索引
CREATE INDEX nameindex ON t_user(name);
要删除索引，可以使用 DROP INDEX 语句：
-- 删除索引
DROP INDEX nameindex ON t_user;
这样可以有效地创建和删除单列索引。
唯一索引
在建表时创建唯一索引，可以在列定义后使用 UNIQUE 关键字来创建唯一索引。如果是在建表后创建唯一索引，可以使用 CREATE UNIQUE INDEX 语句。例如：
-- 建表时创建唯一索引
CREATE TABLE t_user (
id VARCHAR(20) PRIMARY KEY,
name VARCHAR(20),
UNIQUE (name)
);
-- 建表后创建唯一索引
CREATE UNIQUE INDEX nameindex ON t_user(name);
这样可以有效地创建唯一索引。
复合索引
在建表时创建复合索引，可以在列定义后使用 KEY 关键字来创建复合索引。如果是在建表后创建复合索引，可以使用 CREATE INDEX 语句。例如：
-- 建表时创建复合索引
CREATE TABLE t_user (
id VARCHAR(20) PRIMARY KEY,
name VARCHAR(20),
age INT,
KEY name_age_index (name, age)
);
-- 建表后创建复合索引
CREATE INDEX name_age_index ON t_user(name, age);
这样可以有效地创建复合索引。
4、索引什么情况下会失效？
在以下情况下，可能无法充分利用索引：
-
当查询语句中使用LIKE关键字进行查询时，如果匹配字符串的第一个字符为“%”，索引通常不会被使用。如果“%”不是在第一个位置，索引会被使用。
-
当查询语句中使用多列索引时，只有在查询条件中使用了这些字段中的第一个字段，索引才会被使用。
-
当查询语句中使用OR关键字时，如果OR前后的两个条件的列都是索引，则查询中将使用索引。但如果OR前后有一个条件的列不是索引，查询中将不使用索引。
以上情况都会影响索引的使用效果，因此在设计数据库时，需要考虑这些因素以便进行优化。