MySQL索引

作者QQ：67065435 QQ群：669756510
本站内容全部为作者原创，转载请注明出处！

什么是索引

索引是帮助MySQL高效获取数据的数据结构；

索引优缺点

索引优点：提高数据的检索效率，降低数据库的IO成本；
索引缺点：索引会占据磁盘的空间，会降低更新表的效率；

索引什么时候使用

使用索引场景：
1. 主键自动建立唯一索引；
2. 频繁作为查询条件的字段应该创建索引；
3. 多表连接查询中与其他表关联的字段应该创建索引；
4. 多字段查询下可创建组合索引；
5. 排序字段通过索引访问可大大提高排序速度；
6. 查询中统计或者分组字段增加索引可大大提高排序速度。
禁用索引场景：
1. 表记录很少；
2. 经常增删改的表；
3. where条件里用不到的字段；

索引分类

主键索引：列设为主键后会自动建立主键索引，索引列中的值必须唯一、不为空；

# 创建
ALTER TABLE 表名 ADD PRIMARY KEY(主键名);

# 删除
ALTER TABLE 表名 DROP PRIMARY KEY;

# 建表时声明
CREATE TABLE 表名 (
  主键名 INT AUTO_INCREMENT,
  字段名 VARCHAR(20) NOT NULL,
  PRIMARY KEY (主键名)
);

唯一索引：列创建唯一约束后会自动建立唯一索引，索引列中的值必须唯一、可为空；

# 创建
ALTER TABLE 表名 ADD UNIQUE INDEX 索引名(字段名);
CREATE UNIQUE INDEX 索引名 ON 表名(字段名);

# 删除
DROP INDEX 索引名 ON 表名;

# 建表时声明
CREATE TABLE 表名 (
  主键名 INT AUTO_INCREMENT,
  字段名 VARCHAR(20) NOT NULL,
  PRIMARY KEY (主键名),
  UNIQUE INDEX 索引名(字段名)
);

单值索引(单列索引、普通索引)：一个索引只包含单个列，一个表可以有多个单值索引；

# 创建
ALTER TABLE 表名 ADD INDEX 索引名(字段名);
CREATE INDEX 索引名 ON 表名(字段名);

# 删除
DROP INDEX 索引名 ON 表名;

# 建表时声明
CREATE TABLE 表名 (
  主键名 INT AUTO_INCREMENT,
  字段名 VARCHAR(20) NOT NULL,
  PRIMARY KEY (主键名),
  INDEX 索引名(字段名)
);

复合索引(组合索引)：一个索引包含多个列，一个表可以有多个复合索引；

# 复合索引也可以使用UNIQUE关键字添加唯一性约束
# 创建
ALTER TABLE 表名 ADD INDEX 索引名(字段名1,字段名2);
CREATE INDEX 索引名 ON 表名(字段名1,字段名2);

# 删除
DROP INDEX 索引名 ON 表名;

# 建表时声明
CREATE TABLE 表名 (
  主键名 INT AUTO_INCREMENT,
  字段名1 VARCHAR(20) NOT NULL,
  字段名2 VARCHAR(20) NOT NULL,
  PRIMARY KEY (主键名),
  INDEX 索引名(字段名1,字段名2)
);

全文索引：全文搜索专用，只有 MyISAM、InnoDB 存储引擎的 char、varchar、text类型列（MySQL-5.6开始支持）；

# 创建
ALTER TABLE 表名 ADD FULLTEXT INDEX 索引名(字段名);
CREATE FULLTEXT INDEX 索引名 ON 表名(字段名);

# 删除
DROP INDEX 索引名 ON 表名;

# 建表时声明
CREATE TABLE 表名 (
  主键名 INT AUTO_INCREMENT,
  字段名 VARCHAR(20) NOT NULL,
  PRIMARY KEY (主键名),
  FULLTEXT INDEX 索引名(字段名)
);

空间索引：用于空间数据类型的索引，如：地理数据类型GEOMETRY，这种索引类型主要用于空间数据查询，仅限MyISAM存储引擎；
前缀索引：对于文本类的长字段，可以创建前缀索引来提高索引效率，只索引字段的前面一部分字符；
覆盖索引：如果一个索引包含了查询所需的所有数据，那么这个索引被称为覆盖索引，可以直接通过索引来获取数据，无需回表查询；

results matching ""

No results matching ""