Linux MySQL 学习笔记-索引index
1 索引index
-
索引:类似于书的目录,字典中的音序表
功能:加快数据检索的速度,提高效率 -
缺点:
1)创建和维护索引都需要消耗时间,消耗的时间长短取决于表中数据量的多少
2)会占用磁盘空间
3)更新数据库中的数据时,索引也会更新 -
什么时候都需要创建索引吗?
不是。对于数据频繁更新的表不适合创建索引。 -
索引的分类
单列索引、多列索引、唯一性索引、主键索引、普通索引 …
2 创建索引
- 在创建表时直接创建索引
语法:create table 表名 (字段名 类型,…,index索引名);
MariaDB [test]> create table index1 (
-> id int,
-> name char(10),
-> index (id));
Query OK, 0 rows affected (0.04 sec)
MariaDB [test]> show create table index1\G;
************** 1. row ***************
Table: index1
Create Table: CREATE TABLE `index1` (
`id` int(11) DEFAULT NULL,
`name` char(10) DEFAULT NULL,
KEY `id` (`id`) #索引相关的行 KEY 索引名 (索引字段)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
创建索引时不指定名字,那么默认会创建和字段名同名的索引。
- 对于已经存在的表,添加索引
- 第一种方法:
使用create index 命令创建索引
语法:create index 索引名 on 表名 (字段名);
MariaDB [test]> create index ind_name1 on index1(name);
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
MariaDB [test]> show create table index1\G;
**************** 1. row ***************
Table: index1
Create Table: CREATE TABLE `index1` (
`id` int(11) DEFAULT NULL,
`name` char(10) DEFAULT NULL,
KEY `id` (`id`),
KEY `ind_name1` (`name`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1
1 row in set (0.00 sec)
- 第二种方法:
使用alter table 命令添加索引
语法:alter table 表名 add index [索引名](索引字段);
MariaDB [test]> create table index2 ( id int, user char(10));
Query OK, 0 rows affected (0.05 sec)
MariaDB [test]> alter table index2 add index(id);
Query OK, 0 rows affected (0.07 sec)
Records: 0 Duplicates: 0 Warnings: 0
唯一性索引:unique index [索引名](索引字段)
MariaDB [test]> alter table index2 add unique index user_name(user);
Query OK, 0 rows affected (0.06 sec)
Records: 0 Duplicates: 0 Warnings: 0
2 查看索引
MariaDB [test]> show create table index1\G;
MariaDB [test]> show index from index1\G;
3 删除索引
语法:drop index 索引名 on 表名;
MariaDB [test]> drop index user_name on index2;
语法:alter table 表名 drop index 索引名;
MariaDB [test]> alter table index1 drop index id;
4 测试
4.1 创建表
MariaDB [test]> create table insert_test (
-> id int,
-> name char(20),
-> street char(20),
-> city char(20),
-> price float(10,2)) engine=myisam;
Query OK, 0 rows affected (0.04 sec)
4.2 写脚本生成插入语句
# vim insert.sh
#!/bin/bash
for ((i=1;i<=1000000;i++))
do
echo "insert into insert_test values($i,'name$i','street$i','city$i',$i.00); " >> /tmp/insert_test.sql
echo $i
done
4.3 执行脚本生成sql文件
# bash insert.sh
4.4 向数据库中插入数据
MariaDB [test]> source /tmp/insert_test.sql
MariaDB [test]> select count(*) from insert_test;
+----------+
| count(*) |
+----------+
| 42802 |
+----------+
1 row in set (0.01 sec)
4.5 开启可以查看每个select语句执行时间的功能
MariaDB [test]> show variables like '%prof%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| have_profiling | YES |
| profiling | OFF |
| profiling_history_size | 15 |
+------------------------+-------+
3 rows in set (0.00 sec)
MariaDB [test]> set profiling=1; #打开性能分析功能
Query OK, 0 rows affected (0.00 sec)
MariaDB [test]> show variables like '%prof%';
+------------------------+-------+
| Variable_name | Value |
+------------------------+-------+
| have_profiling | YES |
| profiling | ON |
| profiling_history_size | 15 |
+------------------------+-------+
3 rows in set (0.00 sec)
4.6 创建一张带索引的表
MariaDB [test]> create table insert_test (
-> id int,
-> name char(20),
-> street char(20),
-> city char(20),
-> price float(10,2) index (id)) engine=myisam;
Query OK, 0 rows affected (0.04 sec)
4.7 复制表内容
MariaDB [test]> insert into insert_test2 select * from insert_test;
4.8 对两个表分别执行sql语句
对没有添加索引的表执行sql语句
> select * from insert_test;
> select id from insert_test;
> select * from insert_test where id=6000;
> select id,name from insert_test where id=6000;
> select id,name from insert_test where id=750000;
> show profiles;
对添加索引的表执行sql语句
> select * from insert_test2;
> select id from insert_test2;
> select * from insert_test2 where id=6000;
> select id,name from insert_test2 where id=6000;
> select id,name from insert_test2 where id=750000;
> show profiles;
Reference
- 课堂笔记