MySQL 创建和使用索引

目录

 

初识索引  

索引分类&原则

   检测索引是否启用     explain

主键索引（PRIMARY KEY）

唯一索引（UNIQUE）

常规索引（INDEX）

创建索引 （ create | alter）

触发器（Trigger）

---

初识索引  

定义：建立在表之上，由单列或多列组成，旨在提高查询效率

作用：

• 提高查询速度
• 确保数据的唯一性
• 加速表与表之间的连接，实现表与表之间的参照完整性
• 使用分组和排序子句进行数据检索时，缩短分组和排序时间
• 全文检索字段进行搜索优化

索引分类&原则

分类：

• 主键索引（PRIMARY KEY）
• 唯一索引（UNIQUE）
• 常规索引（INDEX）
• 单列索引
• 多列索引
• 全文索引（FULLTEXT）          mysql 5.6 InnoDB开始支持全文索引
• 聚集索引、非聚集索引、空间索引

原则：

1. 对于查询频率高的，可以设置索引
2. 对于排序、分组、联合查询频率高
3. 索引数目不易太多（每创建一个索引，都会占用物理空间；insert  update  delete 效率会降低）
4. 若实际多列需要设置索引，采用多列索引 （如：index  index_name(StudentNo,StudentName,Sex...）,只有在第一列使用时才启用索引（如：select  *  from  student  where  StudentNo=111）
5. 尽量在字节占用少的列添加索引
6. 若在字符类型上添加索引，尽量添加在长度小的列上

   检测索引是否启用     explain

主键索引（PRIMARY KEY）

作用：某一个属性组能唯一标识一条记录

如：学生表（学号，姓名，性别，班级等），学号就是唯一标识，可作为主键

特点：

• 最常见的索引类型
• 确保数据记录的唯一性
• 确保特定数据记录在数据库中的位置

示例：

CREATE '表名'(
  'GradeID' INT(11) AUTO_INCREMENT PRIMARY KEY,
 #或 PRIMARY KEY('GradeID')
)

唯一索引（UNIQUE）

作用：避免同一表中某数据列中的值重复出现

与主键索引的区别：主键索引只能有一个，唯一索引可以有多个

示例：

CREATE table 'Grade'(
  'GradeID' INT(11) AUTO_INCREMENT PRIMARY KEY,
  'GradeName' VARCHAR(32) NOT NULL UNIQUE
)

常规索引（INDEX）

作用：快速定位特定数据

注意：

• index 和 key 关键字都可以设置常规索引
• 不易添加太多常规索引，影响数据的插入、删除和修改操作

最佳实践：往存在索引的表中插入10W条数据

                  先删除表中所有的索引，再插入数据

创建索引 （ create | alter）

• 创建表时添加索引：

CREATE TABLE 'result'(
 //省略
INDEX/KEY 'ind'('studentNo','subjectNo')
)

 例子：

create table tbl_tts
(id int ,
index index_tts_id(id)
)

 

• 创建表后追加索引：

ALTER TABLE 'result' ADD INDEX 'ind'('studentNo','subjectNo');

例子：

#创建表
CREATE table t_index_test
(id int auto_increment PRIMARY key ,
name VARCHAR(20))

#给name添加索引
ALTER table t_index_test ADD index index_index(name);
ALTER table t_index_test ADD index index_test(name);
#给id添加索引
CREATE index index_test_id
on t_index_test(id);

CREATE index index_id
on t_index_test(id);

CREATE index index_test_iid
on t_index_test(id);

检验创建的索引：

注意：在表中不存在 where 条件中的值时，查询结果是没有使用索引

触发器（Trigger）

定义：触发器（Trigger）是与表有关的数据库对象，是一种特殊的存储过程，在满足定义条件时触发，并执行触发器中定义的语句集合。

在ＭySQL中，触发器可以在你执行INSERT、UPDATE或DELETE的时候，执行一些特定的操作。在创建触发器时，可以指定是在执行SQL语句之前或是之后执行这些操作。通过触发器，你可以实现一些业务逻辑或一些数据限制，在简化应用程序逻辑，优化系统性能时非常有用。

创建触发器（Trigger）的基本语法如下：

CREATE TRIGGER <触发器名称>
{ BEFORE | AFTER }              #触发时间
{ INSERT | UPDATE | DELETE }    #触发事件
ON <表名称>                      # 触发器所属表
FOR EACH ROW          #触发器的执行间隔：FOR EACH ROW子句通知触发器，每行执行一次动作
BEGIN
 <触发的SQL语句>    #事件触发时所要执行的SQL语句
END;

删除触发器语法：

DROP TRIGGER [IF EXISTS] [schema_name.]trigger_name;

例1：

（1）先创建表 tbl_trig

create table tbl_trig
(
action VARCHAR(20),
action_time datetime
)

（2）创建触发器

CREATE trigger trig01 
BEFORE 
INSERT on student
for EACH ROW  
BEGIN
INSERT INTO tbl_trig(action,action_time) VALUES('insert',NOW());
END

（3）查看触发器

show CREATE TRIGGER trig01

（4） 触发事件：向 student 表中插入数据时触发

insert into student(StudentNo,StudentName) values (1031,'四叶子')

（5）查看插入数据

SELECT * from student;

（6）查看触发后的 tbl_trig 表

SELECT * from tbl_trig

（7）删除触发器

DROP TRIGGER if EXISTS trig01

show CREATE TRIGGER trig01

删除结果：

例2：

CREATE TRIGGER trig02 AFTER
UPDATE on student FOR EACH ROW
UPDATE tbl_trig SET action='update';

show CREATE TRIGGER trig02

UPDATE student set studentname='小石头' where studentNo =1031 

SELECT * FROM student

SELECT * from tbl_trig