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;
