索引的作用
加快数据查询速度
什么是索引?
- 索引是帮助MySQL高效获取数据的数据结构
- 索引在存储引擎中实现,每种存储引擎的索引都不一定完全相同,每种存储引擎也不一定支持所有的所有类型
索引的分类
- 普通索引和唯一索引
- 普通索引是MySQL的基本索引类型
- 唯一索引对应列的值必须唯一,但允许空值。如果是组合索引,则列值的组合必须唯一
- 主键索引是一种特殊的唯一索引,不允许有空值
- 单列索引和组合索引
- 单列索引指只包含一个列的索引。一个表中可以有多个单列索引
- 组合索引指表的多个字段组合上创建的索引。遵循做前缀集合
- 全文索引
- FULLTEXT类型索引。可以在CHAR,VARCHAR或者TEXT类型的列上创建
- 仅MYISAM支持
- 空间索引
- 对空间数据类型的字段建立的索引
索引设计原则
- 索引并非越多越好
- 数据量不多不需要建立索引
- 列中的值变化不多也不需要建立索引
- 经常排序和分组的数据列要建立索引
- 唯一性约束对应使用唯一性索引
创建索引
在建表的同时,创建索引
创建普通索引
|
mysql> create table t_1 (id int, name varchar(10), index(name));
Query OK, 0 rows affected (0.32 sec)
mysql> show create table t_1\G;
*************************** 1. row ***************************
Table: t_1
Create Table: CREATE TABLE `t_1` (
`id` int(11) DEFAULT NULL,
`name` varchar(10) DEFAULT NULL,
KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
ERROR:
No query specified
创建唯一索引
UNIQUE INDEX
|
mysql> create table t_2 (
-> id int,
-> name varchar(10),
-> unique index idInx (id)
-> );
Query OK, 0 rows affected (0.06 sec)
mysql> show create table t_2\G;
*************************** 1. row ***************************
Table: t_2
Create Table: CREATE TABLE `t_2` (
`id` int(11) DEFAULT NULL,
`name` varchar(10) DEFAULT NULL,
UNIQUE KEY `idInx` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
ERROR:
No query specified
创建单列索引
|
mysql> create table t_3 (
-> id int,
-> name varchar(10),
-> index idinx (name(10))
-> );
Query OK, 0 rows affected (0.02 sec)
mysql> show create table t_3\G;
*************************** 1. row ***************************
Table: t_3
Create Table: CREATE TABLE `t_3` (
`id` int(11) DEFAULT NULL,
`name` varchar(10) DEFAULT NULL,
KEY `idinx` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
ERROR:
No query specified
创建组合索引
|
mysql> create table t_4 (
-> id int,
-> name varchar(10),
-> age int,
-> index MutilIdx (id,name,age)
-> );
Query OK, 0 rows affected (0.09 sec)
mysql> show create table t_4\G;
*************************** 1. row ***************************
Table: t_4
Create Table: CREATE TABLE `t_4` (
`id` int(11) DEFAULT NULL,
`name` varchar(10) DEFAULT NULL,
`age` int(11) DEFAULT NULL,
KEY `MutilIdx` (`id`,`name`,`age`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> insert into t_4 values(1,'AAA','10'),(2,'BBB','20'),(3,'CCC','30'),(4,'DDD','40'),(5,'EEE','50');
Query OK, 5 rows affected (0.00 sec)
Records: 5 Duplicates: 0 Warnings: 0
mysql> select * from t_4;
+------+------+------+
| id | name | age |
+------+------+------+
| 1 | AAA | 10 |
| 2 | BBB | 20 |
| 3 | CCC | 30 |
| 4 | DDD | 40 |
| 5 | EEE | 50 |
+------+------+------+
5 rows in set (0.00 sec)
mysql> explain select name,age from t_4 where id<3;
+----+-------------+-------+-------+---------------+----------+---------+------+------+--------------------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+---------------+----------+---------+------+------+--------------------------+
| 1 | SIMPLE | t_4 | index | MutilIdx | MutilIdx | 43 | NULL | 5 | Using where; Using index |
+----+-------------+-------+-------+---------------+----------+---------+------+------+--------------------------+
1 row in set (0.04 sec)
mysql> explain select name,age from t_4 where id<3\G;
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t_4
type: index
possible_keys: MutilIdx
key: MutilIdx
key_len: 43
ref: NULL
rows: 5
Extra: Using where; Using index
1 row in set (0.00 sec)
ERROR:
No query specified
创建全文索引
|
修改和删除索引
索引管理
使用ALTER语句
|
mysql> show create table t_5\G;
*************************** 1. row ***************************
Table: t_5
Create Table: CREATE TABLE `t_5` (
`id` int(11) DEFAULT NULL,
`name` varchar(10) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
ERROR:
No query specified
mysql> alter table t_5 add index nameIdx (name);
Query OK, 0 rows affected (0.08 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table t_5\G;
*************************** 1. row ***************************
Table: t_5
Create Table: CREATE TABLE `t_5` (
`id` int(11) DEFAULT NULL,
`name` varchar(10) DEFAULT NULL,
KEY `nameIdx` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
ERROR:
No query specified
增加
mysql> alter table t_5 add unique index nameIdx1 (name);
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table t_5\G;
*************************** 1. row ***************************
Table: t_5
Create Table: CREATE TABLE `t_5` (
`id` int(11) DEFAULT NULL,
`name` varchar(10) DEFAULT NULL,
UNIQUE KEY `nameIdx1` (`name`),
KEY `nameIdx` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
ERROR:
No query specified
删除
mysql> alter table t_5 drop index nameIdx;
Query OK, 0 rows affected (0.05 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table t_5\G;
*************************** 1. row ***************************
Table: t_5
Create Table: CREATE TABLE `t_5` (
`id` int(11) DEFAULT NULL,
`name` varchar(10) DEFAULT NULL,
UNIQUE KEY `nameIdx1` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
ERROR:
No query specified
使用CREATE语句
|
mysql> create index idIdx on t_5(id);
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table t_5\G;
*************************** 1. row ***************************
Table: t_5
Create Table: CREATE TABLE `t_5` (
`id` int(11) DEFAULT NULL,
`name` varchar(10) DEFAULT NULL,
UNIQUE KEY `nameIdx1` (`name`),
KEY `idIdx` (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
ERROR:
No query specified
使用DROP语句
|
mysql> drop index idIdx on t_5;
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table t_5\G;
*************************** 1. row ***************************
Table: t_5
Create Table: CREATE TABLE `t_5` (
`id` int(11) DEFAULT NULL,
`name` varchar(10) DEFAULT NULL,
UNIQUE KEY `nameIdx1` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
ERROR:
No query specified
索引性能测试
1、准备环境
- 在数据库中创建测试表
|
- 在系统提示符下执行如下语句创建100万行数据
|
2、简单测试索引性能
- 分别在有索引和没有索引的情况下执行查询。
- 分别在有索引和没有索引的情况下新增数据。
mysql> desc test1;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | YES | | NULL | |
| num | int(11) | YES | | NULL | |
| pass | varchar(50) | YES | | NULL | |
+-------+-------------+------+-----+---------+-------+
3 rows in set (0.05 sec)
1、使用limit查询指定的行数
|
2、刷新查询缓存
mysql> reset query cache; Query OK, 0 rows affected (0.00 sec) |
3、测试查询
|
4、创建test2并且添加索引
|
测试查询
|
5、不使用索引创建表、插入数据(比添加索引消耗的时间短)
|
总结
- 索引从本质上讲是一种数据结构
- 记住索引的设计原则
- FULLTEXT索引仅能在MYISAM上使用
- 数据量巨大的表要慎重操作索引
- 索引的管理可以使用多种不同的方法
转载于:https://blog.51cto.com/7424593/1774927