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用户表'批量插入数据：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();索引效率测试
无索引
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)创建索引
CREATE INDEX idx_app_user_name ON app_user(name);测试普通索引
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)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;
