mysql索引
mysql索引
初始索引
索引在mysql中也叫做键
是存储引擎用于快速找到记录的一种数据结构
优点:
- 索引对于良好的性能非常关键
- 尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要
- 索引优化应该是对查询性能优化最有效的手段了，索引能够轻易将查询性能提高好几个数量级
- 索引相对于字段的音序表，如果要查某个字，如果不使用音序表，则需要从几百页中逐页去找
缺点:
- 当在已经创建索引的表上插入数据时，索引会重新构建，降低插入数据的效率
索引的分类
- 普通索引
- 唯一索引
- 全文索引
- 单列索引
- 多列索引
- 空间索引
用的比较多的还是单列索引
普通索引允许字段重复
唯一索引不允许字段重复
创建表时创建索引
语法
create table 表名(
字段名1 数据类型[完整性约束条件...],
字段名2 数据类型[完整性约束条件...],
[unique | fulltext | spatial] index|key [索引名] (字段名[(长度)] [asc|desc])
)
创建索引使用的关键字可以是index,也可以是key
创建普通索引
mysql> create table table1(
-> id int,
-> name varchar(50),
-> comment varchar(100),
-> index(name)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> show create table table1\G
****** 1. row ******
Table: table1
Create Table: CREATE TABLE `table1` (
`id` int(11) DEFAULT NULL,
`name` varchar(50) DEFAULT NULL,
`comment` varchar(100) DEFAULT NULL,
KEY `name` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
创建唯一索引
mysql> create table table2(
-> id int,
-> name varchar(50),
-> comment varchar(100),
-> unique index(name)
-> );
Query OK, 0 rows affected (0.01 sec)
创建全文索引
create table table3(
dept_id int,
dept_name varchar(30),
comment varchar(50),
log text,
fulltext index(log)
)
创建多列索引
此处应该创建索引名,不然会以其中一列的列名来命名
mysql> create table table4(
-> id int,
-> name varchar(50),
-> comment varchar(100),
-> index name_comment_index (name,comment)
-> );
Query OK, 0 rows affected (0.01 sec)
在已存在的表上创建索引
当发现查询比较慢时,创建索引
create table
语法
create [unique | fulltext | spatial] index 索引名 on 表名 (字段名[(长度)] [asc|desc]);
创建普通索引
mysql> create table table5(
-> id int,
-> name varchar(50));
Query OK, 0 rows affected (0.01 sec)
mysql> create index index_name on table5(name);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
创建唯一索引
mysql> create table table6(
-> id int,
-> name varchar(50));
Query OK, 0 rows affected (0.00 sec)
mysql> create unique index index_name on table6(name);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
创建全文索引
mysql> create table table7(
-> id int,
-> name varchar(50)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> create fulltext index index_name on table7(name);
Query OK, 0 rows affected, 1 warning (0.04 sec)
Records: 0 Duplicates: 0 Warnings: 1
创建多列索引
mysql> create table table8(
-> id int,
-> name varchar(50)
-> );
Query OK, 0 rows affected (0.01 sec)
mysql> create index index_id_name on table8(id,name);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
alter table
语法
alter table 表名 add [unique | fulltext | spatial ] index 索引名 (字段名[(长度)] [asc|desc])
创建普通索引
mysql> create table table9(
-> id int,
-> name varchar(50)
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> alter table table9 add index index_name(name);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
创建唯一索引
mysql> create table table10(
-> id int,
-> name varchar(50)
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> alter table table10 add unique index index_name(name);
Query OK, 0 rows affected (0.01 sec)
Records: 0 Duplicates: 0 Warnings: 0
创建全文索引
mysql> create table table11(
-> id int,
-> name varchar(50)
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> alter table table11 add fulltext index index_name(name);
Query OK, 0 rows affected, 1 warning (0.33 sec)
Records: 0 Duplicates: 0 Warnings: 1
创建多列索引
mysql> create table table12(
-> id int,
-> name varchar(50)
-> );
Query OK, 0 rows affected (0.00 sec)
mysql> alter table table12 add index index_id_name(id,name);
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
管理索引
查看索引
show create table 表名\G
mysql> show create table table12\G
*************************** 1. row ***************************
Table: table12
Create Table: CREATE TABLE `table12` (
`id` int(11) DEFAULT NULL,
`name` varchar(50) DEFAULT NULL,
KEY `index_id_name` (`id`,`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
删除索引
drop index 索引名 on 表名;
mysql> drop index index_id_name on table12;
Query OK, 0 rows affected (0.00 sec)
Records: 0 Duplicates: 0 Warnings: 0
mysql> show create table table12\G
*************************** 1. row ***************************
Table: table12
Create Table: CREATE TABLE `table12` (
`id` int(11) DEFAULT NULL,
`name` varchar(50) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
测试索引效率
delimiter或者\d用于更改命令行结束符号
explain 告诉我们查询优化器如何查询
使用索引时,一定要在where后添加该索引的条件
创建表
mysql> create table t1( id int, name varchar(50));
Query OK, 0 rows affected (0.01 sec)
创建存储过程,实现批量插入
mysql> delimiter $$
mysql> create procedure autoinsert_t1()
BEGIN
declare i int default 1;
while(i<=200000)do
insert into test.t1 values(i,'ccc');
set i=i+1;
end while;
END$$
Query OK, 0 rows affected (0.00 sec)
调用存储过程
mysql> \d ;
mysql> call autoinsert_t1;
测试查询速度
mysql> select * from t1 where id=123456\G
*************************** 1. row ***************************
id: 123456
name: ccc
1 row in set (0.06 sec)
mysql> explain select * from t1 where id=123456\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 200226
filtered: 10.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
创建索引
mysql> create index index_id on t1(id);
Query OK, 0 rows affected (0.19 sec)
Records: 0 Duplicates: 0 Warnings: 0
创建索引后查询
mysql> select * from t1 where id=123456\G
*************************** 1. row ***************************
id: 123456
name: ccc
1 row in set (0.00 sec)
mysql> explain select * from t1 where id=123456\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: t1
partitions: NULL
type: ref
possible_keys: index_id
key: index_id
key_len: 5
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
