MySQL 之索引、视图、触发器

削个椰子皮给个梨

MySQL 之索引、视图、触发器

索引

  • 索引的引入

索引是由数据库表中一列或者多列组合而成,其作用是提高对表中数据的查询速度;类似于图书的目录,方便快速定位,寻找指定的内容。

  • 索引的优缺点

优点:提高查询数据的速度
缺点:创建和维护索引的时间增加了

  • 建立索引的建议
  • 一张表建议最多建立 5 个索引
  • 建立复合索引优于单值索引(复合索引占用空间小)
  • 建立索引的技巧
  • 如果是左连接则需要在右表关联字段上建立索引,因为左表是查的全部数据。如果是右连接则需要在左表关联字段上建立索引。
  • 尽可能减少 join 语句中的 NestedLoop 的循环总次数。(永远用小结果集驱动大的结果集)
  • 优先优化 NestedLoop 的内层循环。
  • 保证 join 语句中被驱动表上 join 条件字段已经被索引。
  • 当无法保证被驱动表的 join 条件字段被索引且内存资源充足的前提下,不要太吝啬 JoinBuffer 的设置。
  • 索引失效的常见原因
  • 查询全部列,不会使用到索引(select *)
  • 不遵守最佳左前缀法则(如果索引了多列,要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引的列
  • 在索引列上做任何操作(计算、函数、(自动或手动)类型转换),会导致索引失效而转向全表扫描
  • 存储引擎使用了索引中范围条件右边的列,会导致不会使用到索引
  • 尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致)),减少 select *
  • mysql 在使用不等于 (!= 或 <>)的时候无法使用索引会导致全表扫描
  • is null,is not null 也无法使用索引
  • like 以通配符开头 ('%abc' 或者 '%abc%')mysql 索引失效会变成全表扫描的操作,当百分号写在右边的时候索引不会失效。解决 '%abc%' 索引失效的方法是,在要模糊查询字段上建立索引,使用覆盖索引的方式查询,则索引则不会失效。
  • varchar 类型 (字符串)不加单引号索引失效(如果是 InnoDB 存储类型,会导致行锁变表锁)
  • 少用 or,用它来连接时索引会失效,即使其中的条件带有索引也不会使用到索引,如果要想使用 or,又想让索引生效,只能将 or 条件中的每一列都加上索引。如果出现 or 的语句中没有一个列加了索引,那么建议使用 union 拼接多个查询语句。
  • not in 和 not exist 不会走索引
  • 优化口诀
    全值匹配我最爱,最左前缀要遵守;
    带头大哥不能死,中间兄弟不能断;
    索引列上少计算,范围之后全失效;
    like百分写最右,覆盖索引不写*;
    不等空值还有or,索引失效要少用。
  • 创建索引的前提

索引的效率取决于索引列的值是否为散列,即该列的值如果越互不相同,那么索引效率越高。反过来,如果记录的列存在大量相同的值,例如 gender 列,大约一半的记录值是 M,另一半是 F,因此,对该列创建索引就没有意义。区分度的公式是 count(distinct col)/count(*),表示字段不重复的比例,比例越大我们扫描的记录数越少。

可以对一张表创建多个索引。索引的优点是提高了查询效率,缺点是在插入、更新和删除记录时,需要同时修改索引,因此,索引越多,插入、更新和删除记录的速度就越慢。

对于主键,关系数据库会自动对其创建主键索引。使用主键索引的效率是最高的,因为主键会保证绝对唯一。

  • 创建表的时候创建索引:

-- 创建普通索引
CREATE TABLE t_user1 (
    id INT,
    userName VARCHAR (20),
    PASSWORD VARCHAR (20),
    INDEX (userName)
);

-- 创建唯一性索引并为索引取别名
CREATE TABLE t_user2 (
    id INT,
    userName VARCHAR (20),
    PASSWORD VARCHAR (20),
    UNIQUE INDEX usrn (userName)
);

-- 创建多列索引
CREATE TABLE t_user3 (
    id INT,
    userName VARCHAR (20),
    PASSWORD VARCHAR (20),
    INDEX index_user_pwd (userName,PASSWORD)
);
  • 在已有表中创建索引

-- 在已有表中创建普通索引
CREATE INDEX index_userName ON t_user4(userName);

-- 在已有表中创建唯一性索引
CREATE UNIQUE INDEX index_userName ON t_user4(userName);

-- 在已有表中创建多列索引
CREATE INDEX index_userName_pwd ON t_user4(userName,PASSWORD);
// 或者采用下面的方式
ALTER TABLE students
ADD INDEX idx_name_score (name, score);

-- 使用 ALTER 删除索引
ALTER TABLE t_user5 ADD INDEX index_user(userName)
  • 查看索引

show index from table_name\G;
  • 删除索引

-- 删除索引
-- DROP INDEX 索引名 ON 表名;
DROP INDEX index_user ON t_user5;
  • 索引检索原理

索引检索原理

索引的分析

  • 哪些情况下应该建立索引 ?
  • 主键自动建立唯一索引
  • 频繁作为查询条件的字段应该创建索引
  • 查询中与其它表关联的字段,外键关系建立索引
  • 频繁更新的字段不适合创建索引(因为每次更新不单单是更新了记录还会更新索引)
  • where 条件里用不到的字段不创建索引
  • 单键索引还是组合索引的选择问题?(在高并发下倾向创建组合索引)
  • 查询中排序的字段,排序字段若通过索引去访问将大大提高排序速度
  • 查询中统计或者分组字段
  • 哪些情况下不要建立索引?
  • 表记录太少
  • 经常增删改的表
  • 数据重复且分布平均的表字段,因此应该只为最经常查询和最经常排序的数据列建立索引。注意:如果某个数据列包含许多重复的内容,为它建立索引就没有太大的实际效果。

视图

  • 视图的引入
  • 试图是一种虚拟的表,是从数据库中一个或者多个表中导出来的表。
  • 数据库中只存放了视图的定义,而并没有存放视图中的数据,这些数据存放在原来的表中。
  • 使用视图查询数据时,数据库系统会从原来的表中取出对应的数据。
  • 视图的作用
  • 使操作简便化;eg:如果一张表中有 100 个字段,需求只需要 20 个字段,那么可以定义一个视图只取出 20 个字段。
  • 增加数据的安全性;eg:如果写代码的时候不想要别人知道某些字段,那么可以定义视图,只取出安全系数低的字段
  • 提高表的逻辑独立性;
  • 创建视图

CREATE [ALGORITHM = {UNDEFIEND | MERGE | TEMPTABLE}]
    VIEW 视图名 [(属性清单)]
    AS SELECT 语句
    [WITH [CASCADED | LOCAL] CHECK OPTION]
    
-- 创建单表视图
CREATE VIEW v1 AS SELECT userName,password FROM t_user4

-- 创建单表视图并给视图字段取别名
CREATE VIEW v1(u,p) AS SELECT userName,password FROM t_user4

-- 查询视图结果
SELECT * FROM v1

-- 在多表上创建视图
CREATE VIEW v2 AS SELECT bookName.bookTypeName FROM t_book,t_booktype WHERE t_book.bookTypeId = t_booktype.id    
    

触发器

  • 触发器的引入

触发器(trigger)是由事件来触发某个操作,这些事件包括 insert 语句、 update 语句和 delete 语句。当数据库系统执行这些事件时,就会激活触发器执行相应的操作。

  • 创建与使用触发器
  1. 创建只有一个执行语句的触发器

CREATE TRIGGER 触发器名 BEFORE | AFTER 触发事件 ON 表名 FOR EACH ROW 执行语句

-- 其中 new 和 old 为过渡变量, new 代表新的数据,old 代表旧的数据
eg:

CREATE TRIGGER trig_book AFTER INSERT
    ON t_book FOR EACH ROW
        UPDATE t_booktype SET bookNum=bookNum+1 WHERE new.bookTypeId=t_booktype.id

-- 执行以下语句之后将会触发触发器        
INSERT INTO t_book VALUES(NULL,'php学习',100,'ke',1)        
  1. 创建有多个执行语句的触发器

CREATE TRIGGER 触发器名 BEFORE | AFTER 触发事件
    ON 表名 FOR EACH ROW
    BEGIN
        执行语句列表
    END  

-- 其中,因为 mysql 遇到分号(;)之后会认为语句终止(分号前面的语句为执行语句),
因此需要使用 delimiter 来手动定义在 | 符号中间的语句才为执行语句   
eg:

delimiter |
CREATE TRIGGER trig_book2 AFTER DELETE
    ON t_book FOR EACH ROW
    BEGIN 
        UPDATE t_booktype SET bookNum=bookNum-1 WHERE old.bookTypeId=t_booktype.id;
        INSERT INTO t_log VALUES (NULL,NULL,'在book表里删除了一条数据');
        DELETE FROM t_test WHERE old.bookTypeId=t_test.id;
    END
|
delimiter;

-- 执行以下语句之后将会触发触发器
DELETE FROM t_book WHERE id=3;
    
  • 查看触发器
  1. 直接执行 sql 语句

SHOW TRIGGERS;
  1. 在系统数据库中 information_schema 库中查看 TRIGGERS 表
  • 删除触发器

DROP TRIGGER [触发器名];

eg:
DROP TRIGGER trig_book2;
阅读 296

生活不止眼前的苟且,你不努力,还有未来的苟且……

42 声望
4 粉丝
0 条评论
你知道吗?

生活不止眼前的苟且,你不努力,还有未来的苟且……

42 声望
4 粉丝
文章目录
宣传栏