Skip to content

Latest commit

 

History

History
281 lines (192 loc) · 11 KB

索引.md

File metadata and controls

281 lines (192 loc) · 11 KB
Raw

MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。

打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。

拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。

索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。

创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。

实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。

上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。

建立索引会占用磁盘空间的索引文件。

普通索引

创建索引

create index indexName on table_name(column_name)

如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。

修改表结构(添加索引)

ALTER table tableName ADD INDEX indexName(columnName)

创建表的时候直接指定

create table mytable(
	id int not null,
	username varchar(16) not null,
	index [indexName] (username(length))
)

删除索引的语法

DROP INDEX [indexName] ON mytable;

唯一索引

创建索引：

create unique index indexName on mytable(username(length))

修改表的结构

alter table mytable add unique [indexName] (username(length))

创建表的时候直接指定

crate table mytable(
 id int not null,
 username varchar(16) not null,
 unique [indexName] (username(length))
);

使用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 ，用于全文索引。

以下实例为在表中添加索引。

mysql> ALTER TABLE testalter_tbl ADD INDEX (c);

你还可以在 ALTER 命令中使用 DROP 子句来删除索引。尝试以下实例删除索引:

mysql> ALTER TABLE testalter_tbl DROP INDEX c;

使用 ALTER 命令添加和删除主键

主键作用于列上（可以一个列或多个列联合主键），添加主键索引时，你需要确保该主键默认不为空（NOT NULL）。实例如下：

mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;
mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);

你也可以使用 ALTER 命令删除主键：

mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;

删除主键时只需指定PRIMARY KEY，但在删除索引时，你必须知道索引名。

显示索引信息

你可以使用 SHOW INDEX 命令来列出表中的相关的索引信息。可以通过添加 \G 来格式化输出信息。

尝试以下实例:

mysql> SHOW INDEX FROM table_name\G
........

alter table tal_name add primary key(column_list)
该语句添加一个主键，这意味着索引值必须是唯一的，且不能为null

alter table tbl_name add unique index_name(column_list)
这条语句创建索引的值必须是唯一的

alter table tal_name add index index_name(column_list)
添加普通索引，索引值可出现多次

alter table tal_name add fulltext index_name(column_list)
该语句指定了索引为fulltext，用于全文索引

在表中添加索引：

alter table test add index (c);

删除索引：

alter table test drop index (c);

使用alter 命令添加和删除主键：

alter table test modify i int not null
alter table test add primary key (i);
alter table test drop primary key;

show index 显示索引信息

命令来列出表中的相关的索引形象，可以通过添加 \G 来格式化输出信息。

show index from table_name\G

mysql 索引示例：

unsigned一般是无符号的意思，比如unsigned int就是无符号整数的意思，字面上的意思就是“无符号整数”，就是说该字段的数值都是非负的，这样可以扩大正数的范围，如有符号的TINYINT类型能表示-128至128，而无符号（unsigned）的TINYINT类型就可以表示0至256了。

# index_a表只有主键，无其他索引
mysql> create table index_a (
	-> id int unsigned not null auto_increment,
	-> title varchar(64) not null,
	-> name varchar(16) not null,
	-> stock int not nul default 0,
	-> create_time int not null default 0,
	-> primary key (id)
	-> );
Query OK, 0 rows affected (0.36 sec)

# index_b表有主键和单列索引
mysql> create table  index_b (
	->     id int unsigned not null auto_increment,
	->     title varchar(64) not null,
	->     name varchar(16) not null,
	->     sn varchar(12) not null default '',
	->     stock int not null default 0,
	->     create_time int not null default 0,
	->     primary key (id),
	->     unique key name(name) using btree,
	->     unique key sn(sn) using btree
	-> );
Query OK, 0 rows affected (0.58 sec)

# index_c表有主键和组合索引
mysql> create table  index_c (
	->     id int unsigned not null auto_increment,
	->     title varchar(64) not null,
	->     name varchar(16) not null,
	->     sn varchar(12) not null default '',
	->     stock int not null default 0,
	->     create_time int not null default 0,
	->     primary key (id),
	->     KEY name_sn_index(name, sn) using btree
	-> );
Query OK, 0 rows affected (0.48 sec)

通过指定name分别查询

a表name 无索引

b表name 有单列索引

c表name 有组合索引

mysql> show columns from index_a;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int unsigned | NO   | PRI | NULL    | auto_increment |
| title       | varchar(64)  | NO   |     | NULL    |                |
| name        | varchar(16)  | NO   |     | NULL    |                |
| stock       | int          | NO   |     | 0       |                |
| create_time | int          | NO   |     | 0       |                |
+-------------+--------------+------+-----+---------+----------------+
5 rows in set (0.01 sec)

mysql> show  columns from index_b;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int unsigned | NO   | PRI | NULL    | auto_increment |
| title       | varchar(64)  | NO   |     | NULL    |                |
| name        | varchar(16)  | NO   | UNI | NULL    |                |
| sn          | varchar(12)  | NO   | UNI |         |                |
| stock       | int          | NO   |     | 0       |                |
| create_time | int          | NO   |     | 0       |                |
+-------------+--------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)

mysql> show columns from index_c;
+-------------+--------------+------+-----+---------+----------------+
| Field       | Type         | Null | Key | Default | Extra          |
+-------------+--------------+------+-----+---------+----------------+
| id          | int unsigned | NO   | PRI | NULL    | auto_increment |
| title       | varchar(64)  | NO   |     | NULL    |                |
| name        | varchar(16)  | NO   | MUL | NULL    |                |
| sn          | varchar(12)  | NO   |     |         |                |
| stock       | int          | NO   |     | 0       |                |
| create_time | int          | NO   |     | 0       |                |
+-------------+--------------+------+-----+---------+----------------+
6 rows in set (0.01 sec)

name='name_876543';
title = 'title_456789';
name like '%www';
name like 'www%';
name='name_556677' or sn = 'sn_667788';
name in ('name_111111', 'name_333333');

索引： 主键索引，普通索引，组合索引，唯一索引，全文索引

unique|fulltext为可选参数，分别表示唯一索引、全文索引

index和key为同义词，两者作用相同，用来指定创建索引

虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行insert、update和delete。因为更新表时，不仅要保存数据，还要保存一下索引文件。

建立索引会占用磁盘空间。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会增长很快。

索引只是提高效率的一个因素，如果有大数据量的表，就需要花时间研究建立最优的索引，或优化查询语句。

普通索引

直接创建索引

create index index_name on table(column(length))

修改表结构的方式添加索引

alter table table_name add index index_name on (column(length))

drop index index_name on table

create unique index indexName on table(column(length))

修改表结构

alter table table_name add unique indexName ON (column(length))

优化一 强制走索引

force index(idx_name_age_position)


mysql> explain select * from employees force index(idx_name_age_position) where age > 666;
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table     | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | employees | NULL       | ALL  | NULL          | NULL | NULL    | NULL | 1000 |    33.33 | Using where |
+----+-------------+-----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

创建索引

create index indexName on table_name (column_name)

alter table tableName add index indexName(columnName)

index [indexName] (username(length))

drop index [indexName] on mytable;

create unique index indexName on mytable(username(length))
alter table mytable add unique [indexName] (username(length))

unique [indexName] (username(length))

alter table test modify i int not null;
alter table test add primary key(i);
alter table test drop primary key;

show index from table_name\G;