二十五岁时我们都一样愚蠢、多愁善感,喜欢故弄玄虚,可如果不那样的话,五十岁时也就不会如此明智。
标题:MySQL 索引
MySQL 索引可以大大提高 MySQL 的检索速度
打个比方,如果合理的设计且使用索引的 MySQL 是一辆高速公路的话,那么没有设计和使用索引的 MySQL 就是一个人力山村泥泞路
索引分单列索引和组合索引
- 单列索引,即一个索引只包含单个列,一个表可以有多个单列索引,但这不是组合索引
- 组合索引,即一个索引包含多个列
创建索引时,需要确保该索引是应用在
SQL
查询语句的条件(一般作为 WHERE 子句的条件)实际上,索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录
过多的使用索引将会造成滥用
因此索引也会有它的缺点:虽然索引大大提高了查询速度,同时却会降低更新表的速度,如对表进行
INSERT
、UPDATE
和DELETE
因为更新表时,MySQL 不仅要保存数据,还要保存一下索引文件建立索引会占用磁盘空间的索引文件
显示索引信息
SHOW INDEX FROM tablename;
命令可以列出某个表中的相关的索引信息比如下面的 SQL 命名用于列出
tbl_language
表的索引信息SHOW INDEX FROM tbl_language;运行结果如下
MariaDB [twle]> SHOW INDEX FROM tbl_language; +--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | tbl_language | 0 | PRIMARY | 1 | id | A | 0 | NULL | NULL | | BTREE | | | +--------------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 1 row in set (0.00 sec)不太直观,我们加上
\G
格式化一下MariaDB [twle]> SHOW INDEX FROM tbl_language\G; *********************** 1. row *********************** Table: tbl_language Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: id Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: 1 row in set (0.00 sec) ERROR: No query specified普通索引
普通索引是最基本的索引,它没有任何限制
普通索引有以下几种创建方式
创建索引
CREATE INDEX indexName ON mytable(username(length));如果是 CHAR,VARCHAR 类型,length 可以小于字段实际长度
如果是 BLOB 和 TEXT 类型,必须指定 length
修改表结构(添加索引)
ALTER table tableName ADD INDEX indexName(columnName)创建表的时候直接指定
CREATE TABLE IF NOT EXISTS `tbl_language`( `id` INT UNSIGNED AUTO_INCREMENT, `name` VARCHAR(64) NOT NULL, `url` VARCHAR(128) NOT NULL, `founded_at` DATE, INDEX name (`name`(64)), PRIMARY KEY ( `id` ) )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;删除索引的语法
DROP INDEX [indexName] ON tablename;唯一索引
唯一索引 与普通索引类似,不同的就是:唯一索引的索引列的值必须唯一,但允许有空值
如果是组合索引,则列值的组合必须唯一
唯一索引有有以下几种创建方式
创建索引
CREATE UNIQUE INDEX indexName ON mytable(username(length))修改表结构
ALTER table mytable ADD UNIQUE [indexName] (username(length))创建表的时候直接指定
CREATE TABLE IF NOT EXISTS `tbl_language`( `id` INT UNSIGNED AUTO_INCREMENT, `name` VARCHAR(64) NOT NULL, `url` VARCHAR(128) NOT NULL, `founded_at` DATE, UNIQUE name (`name`(64)), PRIMARY KEY ( `id` ) )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;使用 ALTER 命令添加索引
有四种方式来添加数据表的索引
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list);
添加一个主键,这意味着索引值必须是唯一的,且不能为NULL。
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list);
创建索引的值必须是唯一的 ( 除了 NULL 外,NULL 可能会出现多次)
ALTER TABLE tbl_name ADD INDEX index_name (column_list);
添加普通索引,索引值可出现多次
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list);
指定了索引为 FULLTEXT ,用于全文索引
准备测试数据
运行下面的 SQL 语句在 twle 数据库中准备测试数据
DROP DATABASE IF EXISTS `twle`; CREATE DATABASE twle default character set utf8mb4 collate utf8mb4_unicode_ci; USE twle; DROP TABLE IF EXISTS `tbl_language`; CREATE TABLE IF NOT EXISTS `tbl_language`( `id` INT UNSIGNED AUTO_INCREMENT, `name` VARCHAR(64) NOT NULL, `url` VARCHAR(128) NOT NULL, `founded_at` DATE, PRIMARY KEY ( `id` ) )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;可以使用
ALTER TABLE tbl_language ADD INDEX (name);
将name
字段添加为索引我们先用
SHOW INDEX FROM tbl_language\G;
查看下tbl_language
表的索引信息MariaDB [twle]> SHOW INDEX FROM tbl_language\G; *********************** 1. row *********************** Table: tbl_language Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: id Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: 1 rows in set (0.00 sec)然后运行
ALTER TABLE tbl_language ADD INDEX (name);
将name
字段添加为索引MariaDB [twle]> ALTER TABLE tbl_language ADD INDEX (name); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0最后使用
SHOW INDEX FROM tbl_language\G;
查看下tbl_language
表的索引信息,发现添加了一条name
的索引MariaDB [twle]> SHOW INDEX FROM tbl_language\G; *********************** 1. row *********************** Table: tbl_language Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: id Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: *********************** 2. row *********************** Table: tbl_language Non_unique: 1 Key_name: name Seq_in_index: 1 Column_name: name Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: 2 rows in set (0.00 sec)使用 ALTER 命令删除索引
命名
ALTER TABLE tablename DROP INDEX index_name
可以删除 tablename 表中的 index_name 索引例如下面的语句用于删除
tbl_language
表中的name
索引ALTER TABLE tbl_language DROP INDEX name;我们先使用
SHOW INDEX FROM tbl_language\G;
查看下tbl_language
表的索引信息,发现有一条name
的索引MariaDB [twle]> SHOW INDEX FROM tbl_language\G; *********************** 1. row *********************** Table: tbl_language Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: id Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: *********************** 2. row *********************** Table: tbl_language Non_unique: 1 Key_name: name Seq_in_index: 1 Column_name: name Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: 2 rows in set (0.00 sec)然后运行
ALTER TABLE tbl_language DROP INDEX name;
将索引name
删除MariaDB [twle]> ALTER TABLE tbl_language DROP INDEX name; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0最后使用
SHOW INDEX FROM tbl_language\G;
查看下tbl_language
表的索引信息,发现name
已经删除了MariaDB [twle]> SHOW INDEX FROM tbl_language\G; *********************** 1. row *********************** Table: tbl_language Non_unique: 0 Key_name: PRIMARY Seq_in_index: 1 Column_name: id Collation: A Cardinality: 0 Sub_part: NULL Packed: NULL Null: Index_type: BTREE Comment: Index_comment: 1 rows in set (0.00 sec)使用 ALTER 命令添加或删除主键
我们先运行下面的 SQL 语句准备测试用的表
DROP DATABASE IF EXISTS `twle`; CREATE DATABASE twle default character set utf8mb4 collate utf8mb4_unicode_ci; USE twle; DROP TABLE IF EXISTS `tbl_language`; CREATE TABLE IF NOT EXISTS `tbl_language`( `id` INT UNSIGNED NOT NULL, `name` VARCHAR(64) NOT NULL, `url` VARCHAR(128) NOT NULL, `founded_at` DATE )ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;使用 ALTER 命令添加主键
ALTER TABLE tablename ADD PRIMARY KEY (fieldname)
命令用于给表 tablename 添加主键主键只能作用于一个列上,添加主键索引时,你需要确保该主键默认不为空 (NOT NULL)
例如下面的 SQL 语句会把
name
添加为主键ALTER TABLE tbl_language ADD PRIMARY KEY (name);我们先用
desc tbl_language;
查看下tbl_language
表的字段信息MariaDB [twle]> desc tbl_language; +------------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+------------------+------+-----+---------+-------+ | id | int(10) unsigned | NO | | NULL | | | name | varchar(64) | NO | | NULL | | | url | varchar(128) | NO | | NULL | | | founded_at | date | YES | | NULL | | +------------+------------------+------+-----+---------+-------+ 4 rows in set (0.01 sec)然后运行
ALTER TABLE tbl_language ADD PRIMARY KEY (name);
添加name
为主键MariaDB [twle]> ALTER TABLE tbl_language ADD PRIMARY KEY (name); Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0最后使用
desc tbl_language;
查看tbl_language
表的字段信息,发现name
已经是主键了MariaDB [twle]> desc tbl_language; +------------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+------------------+------+-----+---------+-------+ | id | int(10) unsigned | NO | | NULL | | | name | varchar(64) | NO | PRI | NULL | | | url | varchar(128) | NO | | NULL | | | founded_at | date | YES | | NULL | | +------------+------------------+------+-----+---------+-------+ 4 rows in set (0.02 sec)使用 ALTER 命令删除主键
ALTER TABLE tablename DROP PRIMARY KEY
可以用来删除表tablename
的主键删除主键时只需指定PRIMARY KEY,但在删除索引时,必须知道索引名
例如下面的 SQL 语句用于删除表
tbl_language
的主键ALTER TABLE tbl_language DROP PRIMARY KEY;我们先用
desc tbl_language;
查看下tbl_language
表的主键MariaDB [twle]> desc tbl_language; +------------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+------------------+------+-----+---------+-------+ | id | int(10) unsigned | NO | | NULL | | | name | varchar(64) | NO | PRI | NULL | | | url | varchar(128) | NO | | NULL | | | founded_at | date | YES | | NULL | | +------------+------------------+------+-----+---------+-------+ 4 rows in set (0.02 sec)可以看到
name
是主键然后执行
ALTER TABLE tbl_language DROP PRIMARY KEY;
删除主键MariaDB [twle]> ALTER TABLE tbl_language DROP PRIMARY KEY; Query OK, 0 rows affected (0.02 sec) Records: 0 Duplicates: 0 Warnings: 0最后使用
desc tbl_language;
查看tbl_language
表的字段信息,发现name
不是主键了MariaDB [twle]> desc tbl_language; +------------+------------------+------+-----+---------+-------+ | Field | Type | Null | Key | Default | Extra | +------------+------------------+------+-----+---------+-------+ | id | int(10) unsigned | NO | | NULL | | | name | varchar(64) | NO | | NULL | | | url | varchar(128) | NO | | NULL | | | founded_at | date | YES | | NULL | | +------------+------------------+------+-----+---------+-------+ 4 rows in set (0.01 sec)