​
​

活动地址：CSDN21天学习挑战赛

索引

1.索引分类

1.主键索引 （primary key）

唯一标识，主键不可重复，只能有一个列作为主键

2.唯一索引 （unique key）

避免重复的列出现，唯一索引可以重复，多个列都可以标识位 唯一索引

3.常规索引（key/index）

默认的，index,key关键字来设置

4.全文索引（fullText）

在特定的数据库引擎下才有，mylsam
快速定位数据

5.基础语法

2.建表测试

CREATE TABLE `app_user` (
`id` bigint(20) unsigned NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT '' COMMENT '用户昵称',
`email` varchar(50) NOT NULL COMMENT '用户邮箱',
`phone` varchar(20) DEFAULT '' COMMENT '手机号',
`gender` tinyint(4) unsigned DEFAULT '0' COMMENT '性别（0:男；1：女）',
`password` varchar(100) NOT NULL COMMENT '密码',
`age` tinyint(4) DEFAULT '0' COMMENT '年龄',
`create_time` datetime DEFAULT CURRENT_TIMESTAMP,
`update_time` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='app用户表'
1
2
3
4
5
6
7
8
9
10
11
12

批量插入数据：100w

DROP FUNCTION IF EXISTS mock_data;
DELIMITER $$
CREATE FUNCTION mock_data()
RETURNS INT
BEGIN
DECLARE num INT DEFAULT 1000000;
DECLARE i INT DEFAULT 0;
WHILE i < num DO
  INSERT INTO app_user(`name`, `email`, `phone`, `gender`, `password`, `age`)
   VALUES(CONCAT('用户', i), '24736743@qq.com', CONCAT('18', FLOOR(RAND()*(999999999-100000000)+100000000)),FLOOR(RAND()*2),UUID(), FLOOR(RAND()*100));
  SET i = i + 1;
END WHILE;
RETURN i;
END;
SELECT mock_data();
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15

索引效率测试

无索引

SELECT * FROM app_user WHERE name = '用户9999'; -- 查看耗时
SELECT * FROM app_user WHERE name = '用户9999';
SELECT * FROM app_user WHERE name = '用户9999';

mysql> EXPLAIN SELECT * FROM app_user WHERE name = '用户9999'\G
*************************** 1. row ***************************
          id: 1
select_type: SIMPLE
       table: app_user
  partitions: NULL
        type: ALL
possible_keys: NULL
        key: NULL
    key_len: NULL
        ref: NULL
        rows: 992759
    filtered: 10.00
      Extra: Using where
1 row in set, 1 warning (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19

创建索引

CREATE INDEX idx_app_user_name ON app_user(name);
1

测试普通索引

mysql> EXPLAIN SELECT * FROM app_user WHERE name = '用户9999'\G
*************************** 1. row ***************************
          id: 1
select_type: SIMPLE
       table: app_user
  partitions: NULL
        type: ref
possible_keys: idx_app_user_name
        key: idx_app_user_name
    key_len: 203
        ref: const
        rows: 1
    filtered: 100.00
      Extra: NULL
1 row in set, 1 warning (0.00 sec)

mysql> SELECT * FROM app_user WHERE name = '用户9999';
1 row in set (0.00 sec)

mysql> SELECT * FROM app_user WHERE name = '用户9999';
1 row in set (0.00 sec)

mysql> SELECT * FROM app_user WHERE name = '用户9999';
1 row in set (0.00 sec)
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24

3.索引准则

索引不是越多越好

不要对经常变动的数据加索引

小数据量的表建议不要加索引

索引一般应加在查找条件的字段

约束

1.外键概念

外键：给关联字段创建关联就是添加外键，简单来说外键就是被约束条件

切记:作为外键一定要和关联主键的数据类型保持一致

–记录老师的数据表

CREATE TABLE Teachers(

id TINYINT PRIMARY KEY auto_increment,

name VARCHAR (20),

age INT,

gender boolean

);

–给老师添加数据

INSERT INTO Teachers (name,age,gender) VALUES (“Lily”,12,0),

(“Linta”,14,0),

(“Zahi”,22,0),

(“Paul”,20,1),

(“Jeans”,21,1);

–创建学生的表

CREATE TABLE Students(

id INT PRIMARY KEY auto_increment,

name VARCHAR(20),

Teacher_id TINYINT,

FOREIGN KEY (Teacher_id) REFERENCES Teachers(id)

)ENGINE=INNODB;

–给学生添加数据

INSERT INTO Students (name,Teacher_id) VALUES (“ViewIn1”,2),

(“ViewIn2”,4),

(“ViewIn3”,1),

(“ViewIn4”,3),

(“ViewIn5”,2),

(“ViewIn6”,3),

(“ViewIn7”,2),

(“ViewIn8”,4);

以上就是给Students的Teacher_id绑定外键为Teachers的id，这样就能将每一个学生与老师对应起来

2.关联约束

其中，Teacher是母表，Students是子表，无法单独删除母表中的字段，因为被子表约束了

要删除母表中的字段，必须将该字段的约束解除，即子表的外键不再对应该字段

因此可以选择修改作为外键的数据，来删除母表的某字段

比如要删除字段1，将Students中id=4和id=6(原本对应母表id=3)的Teacher_id值设置为4

—>这样子表中就没有与母表id=3的字段有关联了

update Students set Teacher_id=4 where id=4 or id=6;

—>之后再删除母表id=3的字段就不受影响了

delete from Teachers where id=3;

—>此时插入一个学生，绑定外键为3就会报错

INSERT INTO Students (name,Teacher_id) values (“ZaHuw”,3);

3.添加与删除外键

给现成的表加外键

例：给Students创建一个外键，取名为aaa，外键字段为Teacher_id，将该外键关联到表Teachers的id字段

ALTER TABLE Students ADD CONSTRAINT aaa

FOREIGN KEY(Teacher_id)

REFERENCES Teachers(id);

2)删除已有的外键

ALTER TABLE Students drop foreign key aaa;

4.集联删除

设置集联删除：这样就不会被限制删除了；

如果删除了外键的字段，那它对应的外键也会跟着被删除；

如果删除了母表的字段，它对应的子表字段也会跟着被删除。

CREATE TABLE Students3(

id INT PRIMARY KEY auto_increment,

name VARCHAR(20),

Teacher_id TINYINT,

FOREIGN KEY (Teacher_id) REFERENCES Teachers(id) on DELETE CASCADE

)ENGINE=INNODB;
​