【003】Linux MySQL 学习笔记-索引index

1 索引index

1. 索引：类似于书的目录，字典中的音序表
   功能：加快数据检索的速度，提高效率

2. 缺点：
   1）创建和维护索引都需要消耗时间，消耗的时间长短取决于表中数据量的多少
   2）会占用磁盘空间
   3）更新数据库中的数据时，索引也会更新

3. 什么时候都需要创建索引吗？
   不是。对于数据频繁更新的表不适合创建索引。

4. 索引的分类
   单列索引、多列索引、唯一性索引、主键索引、普通索引 …

2 创建索引

1. 在创建表时直接创建索引
   语法：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)

创建索引时不指定名字，那么默认会创建和字段名同名的索引。

2. 对于已经存在的表，添加索引

• 第一种方法：
  使用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

1. 课堂笔记