索引

2020-01-07

TRDB

0 0

索引

索引是一个单独的，存储在磁盘中上的数据库结构，它们包含着对数据表里的所有记录的引用指针。使用索引可以快速的找出在某列或多列中有特定值的行。

索引的优点

通过创建唯一索引，来保证数据库表中的每一行数据的唯一性。

可以加快数据的检索速度。
可以保证表数据的完整性与准确性

索引的缺点

索引需要占用物理空间。

对表中的数据进行改动时，索引也需要跟着动态维护，降低了数据的维护速度。

索引的常见类型

index：普通索引
unique：唯一索引
primary key：主键索引
foreign key：外键索引
fulltext: 全文索引
组合索引

准备数据

# 准备shell脚本
[root@localhost ~]# vim /test/test.sh

#!/bin/bash
echo "请输入字段servnumber的值："
read serber
echo "请输入创建sql语句的数量："
read number

# char=`head /dev/urandom | tr -dc 0-9 | head -c 11`
for (( i=0;i<$number;i++ ))
        do
        pass=`head /dev/urandom | tr -dc a-z | head -c 8`
        let serber=serber+1
        echo "insert into test(id,username,servnumber,password,createtime) values('$i','user${i}','${serber}','$pass',now());" >>sql.txt
    done

# 生成sql脚本
[root@localhost test]# sh test.sh 
请输入字段servnumber的值：
14223332345
请输入创建sql语句的数量：
1000
[root@localhost test]# ls
sql.txt  test.sh


# 将sql脚本从本机复制到容器中
docker cp 本地路径 容器长ID:容器路径
[root@localhost test]# docker cp /test/sql.txt  mysql:/sql.txt

# 创建test表
create table test (
    id int(7) zerofill auto_increment not null,
    username varchar(20),
    servnumber varchar(30),
    password varchar(20),
    createtime datetime,
    primary key (id)
)DEFAULT CHARSET=utf8;

# 插入数据
mysql> source /sql.txt
Query OK, 1 row affected (0.02 sec)
Query OK, 1 row affected (0.01 sec)

# 查看生成的数据
mysql> select * from test limit 10;
+---------+----------+-------------+----------+---------------------+
| id      | username | servnumber  | password | createtime          |
+---------+----------+-------------+----------+---------------------+
| 0000001 | user0    | 15223334346 | gsfzlosh | 2019-07-20 08:58:34 |
| 0000002 | user2    | 15223334348 | iooedzbw | 2019-07-20 08:58:34 |
| 0000003 | user3    | 15223334349 | stygrpgj | 2019-07-20 08:58:34 |
| 0000004 | user4    | 15223334350 | hodkesca | 2019-07-20 08:58:34 |
| 0000005 | user5    | 15223334351 | beyitktf | 2019-07-20 08:58:34 |
| 0000006 | user6    | 15223334352 | xgwqdden | 2019-07-20 08:58:34 |
| 0000007 | user7    | 15223334353 | pbhhvcus | 2019-07-20 08:58:34 |
| 0000008 | user8    | 15223334354 | sevxjxkn | 2019-07-20 08:58:34 |
| 0000009 | user9    | 15223334355 | hntiujvu | 2019-07-20 08:58:34 |
| 0000010 | user10   | 15223334356 | kcvqcvgi | 2019-07-20 08:58:34 |
+---------+----------+-------------+----------+---------------------+

普通索引

普通索引（index）顾名思义就是各类索引中最为普通的索引，主要任务就是提高查询速度。其特点是允许出现相同的索引内容，允许空（null）值。

当创建索引没有指定索引名时，索引吗默认为字段的名称。

# 创建表时添加普通索引
create table test (
    id int(7) zerofill auto_increment not null,
    username varchar(20),
    servnumber varchar(30),
    password varchar(20),
    createtime datetime,
    index (id)
)DEFAULT CHARSET=utf8;

# 添加普通索引（已经创建好的表）  普通索引允许索引字段中存在相同的值。
mysql> alter table test add index index_createtime (createtime);
Query OK, 0 rows affected (0.08 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 直接创建普通索引 
mysql> create index index_createtime on test (createtime);

# 查看表中索引
mysql> show index from test\G
mysql> show create table test\G

# 删除索引
mysql> drop index index_createtime on test;
Query OK, 0 rows affected (0.06 sec)
Records: 0  Duplicates: 0  Warnings: 0

测试查询时间（100W 条数据）
随着表结构的复杂度增加和数据量的增加，索引查询带来的性能优势会越来越明显，这里测试的表结构相对简单，数据量也只有 100W 条。

# 不使用索引字段作为查询条件：查询耗时0.28秒
mysql> select * from test where password='hiavzobu';
1 row in set (0.28 sec)

# 添加索引，再次查询：查询耗时0.00秒
mysql> alter table test add index index_password (password);
mysql> select * from test where password='hiavzobu';
1 row in set (0.00 sec)

唯一索引

（unique）顾名思义就是不可以出现相同的索引内容，但是可以为空（null）值。

# 创建表时添加唯一索引
create table test (

    id int(7) zerofill auto_increment not null,
    username varchar(20),
    servnumber varchar(30),
    password varchar(20),
    createtime datetime,
    index (id)
)DEFAULT CHARSET=utf8;

# 添加唯一索引（已经创建好的表）
mysql>  alter table test add unique unique_username (username);
Query OK, 0 rows affected (0.13 sec)
Records: 0  Duplicates: 0  Warnings: 0

# 查看表中索引
mysql> show index from test\G
mysql> show create table test\G

# 删除索引
mysql> drop index unique_username on test;
Query OK, 0 rows affected (0.05 sec)
Records: 0  Duplicates: 0  Warnings: 0

主键索引

基于主键添加索引就是主键索引，它是一种特殊的唯一索引，不允许有空值，而唯一索引（unique 是允许为空值的）。指定为“PRIMARY KEY”。

主键：主键是表的某一列，这一列的值可以用来标志表中的每一行数据的。

################### 创建主键
# 创建主键方式一： 创建表的时候创建

# 创建主键方式二：直接为表添加主键索引
mysql> alter table test add primary key (id);
Query OK, 0 rows affected (0.47 sec)
Records: 0  Duplicates: 0  Warnings: 0

################### 删除主键
# 注意：在有自增的情况下，必须先删除自增，才可以删除主键
mysql> desc test;
+------------+--------------------------+------+-----+---------+----------------+
| Field      | Type                     | Null | Key | Default | Extra          |
+------------+--------------------------+------+-----+---------+----------------+
| id         | int(7) unsigned zerofill | NO   | PRI | NULL    | auto_increment |
| username   | varchar(20)              | YES  |     | NULL    |                |
| servnumber | varchar(30)              | YES  |     | NULL    |                |
| password   | varchar(20)              | YES  | MUL | NULL    |                |
| createtime | datetime                 | YES  |     | NULL    |                |
+------------+--------------------------+------+-----+---------+----------------+
5 rows in set (0.00 sec)

mysql> alter table test change id id int(7) unsigned zerofill not null;
Query OK, 999 rows affected (0.28 sec)
Records: 999  Duplicates: 0  Warnings: 0

mysql> desc test;
+------------+--------------------------+------+-----+---------+-------+
| Field      | Type                     | Null | Key | Default | Extra |
+------------+--------------------------+------+-----+---------+-------+
| id         | int(7) unsigned zerofill | NO   | PRI | NULL    |       |
| username   | varchar(20)              | YES  |     | NULL    |       |
| servnumber | varchar(30)              | YES  |     | NULL    |       |
| password   | varchar(20)              | YES  | MUL | NULL    |       |
| createtime | datetime                 | YES  |     | NULL    |       |
+------------+--------------------------+------+-----+---------+-------+
5 rows in set (0.00 sec)

# 删除主键
mysql> alter table test drop primary key;

测试查询时间（100W 条数据）

# 没有主键索引情况下查询，耗时：0.41秒
mysql> alter table test drop primary key;
mysql> select * from test where id='0999999';
1 rows in set (0.41 sec)

# 添加主键索引再次查询，耗时：0.00秒
mysql> alter table test add primary key (id);
mysql> select * from test where id='0999999';
1 rows in set (0.00 sec)

全文索引

全文索引是将存储在数据库中的文章或者句子等任意内容信息查找出来的索引，单位是词。全文索引也是目前搜索引擎使用的一种关键技术。指定为 fulltex。

mysql5.6 版本字后，innodb 引擎也支持全文检索功能。
出现频率很高的词，将会使全文索引失效。全文索引不针对非常频繁的词做索引。比如 is，no，not，you，me，yes 这些，称之为停止词。
对英文检索时忽略大小写。
一般情况下创建全文索引的字段数据类型为 char、varchar、text 。其它字段类型不可以。
使用通配符*时，只能放在词的后边，不能放前边。
出现频率很高的词，将会使全文索引失效 in boolean mode：意思是指定全文检索模式为布尔全文检索（简单可以理解为是检索方式）；select * from 表名 where match (字段名) against ('检索内容' in boolean mode);

外键约束

外键就是作用于两个表数据之间的链接的一列或多列，用来保证表与表之间的数据的完整性和准确性。例如：员工表和部门表，外键可以保证每一个员工都能有自己所属的部门，保证了不会出一个员工没有部门的情况出现。

两张表，主键跟外键的字段类型一定要相同。
要使用外键约束表的引擎一定得是 InnoDB 引擎，MyISAM 是不起作用的。
在干掉外键索引之前必须先把外键约束删除，才能删除索引。

################### 添加外键约束
#  方式一：创建表时候声明外键约束
CREATE TABLE `employee` (
  `empno` int(11) NOT NULL COMMENT '雇员编号',
  `ename` varchar(50) DEFAULT NULL COMMENT '雇员姓名',
  `job` varchar(30) DEFAULT NULL,
  `mgr` int(11) DEFAULT NULL COMMENT '雇员上级编号',
  `hiredate` date DEFAULT NULL COMMENT '雇佣日期',
  `sal` decimal(7,2) DEFAULT NULL COMMENT '薪资',
  `deptnu` int(11) DEFAULT NULL COMMENT '部门编号',
  PRIMARY KEY (`empno`),
 foreign key (deptnu) references dept(deptnu)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

# 方式二：基于已创建的表添加外键
mysql> alter table employee add foreign key (deptnu) references dept(deptnu)；



# 查看表
mysql> show create table employee\G

*************************** 1. row ***************************
       Table: employee
Create Table: CREATE TABLE `employee` (
  `empno` int(11) NOT NULL,
  `ename` varchar(50) DEFAULT NULL,
  `job` varchar(30) DEFAULT NULL,
  `mgr` int(11) DEFAULT NULL,
  `hiredate` date DEFAULT NULL,
  `sal` decimal(7,2) DEFAULT NULL,
  `deptnu` int(11) DEFAULT NULL,
  PRIMARY KEY (`empno`),
  KEY `deptnu` (`deptnu`),
  CONSTRAINT `employee_ibfk_1` FOREIGN KEY (`deptnu`) REFERENCES `dept` (`deptnu`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8
1 row in set (0.00 sec)

# 删除外键约束
mysql> alter table employee drop foreign key employee_ibfk_1;

# 再次查看表
mysql> show create table employee\G
*************************** 1. row ***************************
       Table: employee
Create Table: CREATE TABLE `employee` (
  `empno` int(11) NOT NULL,
  `ename` varchar(50) DEFAULT NULL,
  `job` varchar(30) DEFAULT NULL,
  `mgr` int(11) DEFAULT NULL,
  `hiredate` date DEFAULT NULL,
  `sal` decimal(7,2) DEFAULT NULL,
  `deptnu` int(11) DEFAULT NULL,
  PRIMARY KEY (`empno`),
  KEY `deptnu` (`deptnu`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

#注意：在干掉外键索引之前必须先把外键约束删除，才能删除索引
mysql> alter table employee drop index deptnu;
ERROR 1553 (HY000): Cannot drop index 'deptnu': needed in a foreign key constraint
mysql> alter table employee drop foreign key employee_ibfk_1;
mysql> alter table employee drop index deptnu;

联合索引

联合索引又称组合索引或者复合索引，是建立在俩列或者多列以上的索引。

为什么要使用联合索引，而不使用多个单列索引？：联合索引的效率远远高于单列索引。
联合索引的最左原则：组合索引需要有最左边的第一个字段，才能让整个联合索引生效。

索引的使用原则

索引并非越多越好，过多的索引会增加数据的维护速度还有磁盘空间的浪费。
当表的数据量很大的时候，可以考虑建立索引。
表中经常查数据的字段，可以考虑建立索引。
想要保证表中数据的唯一性，可以考虑建立唯一索引。
想要保证俩张表中的数据的完整性跟准确性，可以考虑建立外键约束。
经常对多列数据进行查询时，可以考虑建立联合索引。

作者：Soulboy