MySQL索引

MySQL索引

作用：大幅度提高SELECT查询速度

缺点：索引占据一定的磁盘空间，影响insert、update、delete的速度。

最适合索引的列是出现在WHERE子句中的列

1、普通索引：最基本的索引，不具备唯一性

================创建表时创建索引==================
create table 表名 (
	列定义，
	index 索引名称 (字段)	#若不写索引名，则以字段名命名。
	index 索引名称 (字段)	
)
mysql> create table test(
    -> id int,
    -> name varchar(20),
    -> number int,
    -> index (number)
    -> );
Query OK, 0 rows affected (0.11 sec)

mysql> show create table test\G
*************************** 1. row ***************************
       Table: test
Create Table: CREATE TABLE `test` (
  `id` int(11) DEFAULT NULL,
  `name` varchar(20) DEFAULT NULL,
  `number` int(11) DEFAULT NULL,
  KEY `number` (`number`)	#自动添加了 key与index一样。
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

================在已有的表上创建索引====================
create index 索引名 on 表名(字段名)

mysql> create index test on test(name);
Query OK, 0 rows affected (0.59 sec)
Records: 0  Duplicates: 0  Warnings: 0

mysql> desc test;
+--------+-------------+------+-----+---------+-------+
| Field  | Type        | Null | Key | Default | Extra |
+--------+-------------+------+-----+---------+-------+
| id     | int(11)     | YES  |     | NULL    |       |
| name   | varchar(20) | YES  | MUL | NULL    |       |
| number | int(11)     | YES  | MUL | NULL    |       |
+--------+-------------+------+-----+---------+-------+
3 rows in set (0.00 sec

2、唯一性索引：字段值只能出现一次

create table 表名 (
	列定义，
	unique index 索引名称 (字段)	#若不写索引名，则以字段名命名。
	unique index 索引名称 (字段)	
)
mysql> create table demo( id int auto_increment primary key, name varchar(20), number int, unique index (number) );
Query OK, 0 rows affected (0.02 sec)

mysql> desc demo
    -> ;
+--------+-------------+------+-----+---------+----------------+
| Field  | Type        | Null | Key | Default | Extra          |
+--------+-------------+------+-----+---------+----------------+
| id     | int(11)     | NO   | PRI | NULL    | auto_increment |
| name   | varchar(20) | YES  |     | NULL    |                |
| number | int(11)     | YES  | UNI | NULL    |                |
+--------+-------------+------+-----+---------+----------------+
3 rows in set (0.00 sec)

3、主索引(主键)

primary key 约束

主键特点：此字段的记录唯一，主键字段很少被修改。一般主键都约束为：auto_increment或not null和unique。不能为空，不能重复。主键k可用于一个字段，也可以多个字段。

create table 表名(
	列定义 primary key
	...
)
或
create table 表名(
	列定义 
	...
	primary key（字段）
)

mysql> create table demo1(id int auto_increment primary key, name varchar(20), number int);

mysql> show create table demo1\G
*************************** 1. row ***************************
       Table: demo1
Create Table: CREATE TABLE `demo1` (
  `id` int(11) NOT NULL AUTO_INCREMENT,	#自动添加了not null
  `name` varchar(20) DEFAULT NULL,
  `number` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec

主键索引和唯一索引区别：主键索引不能有空值，唯一索引可以有空值。