汪微的博客
zane,做一个有思维的开发者

汪微的博客

mysql随笔

2018年04月26日93 browse

一:索引

概念

索引(Index)是帮助MySQL高效获取数据的数据结构。在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据,这样就可以在这些数据结构上实现高级查找算法,这种数据结构就是索引。

优势

类似大学图书馆建书目索引,提高数据检索效率,降低数据库的IO成本。通过索引对数据进行排序,降低数据排序的成本,降低了CPU的消耗。

劣势

实际上索引也是一张表,该表保存了主键与索引字段,并指向实体表的记录,所以索引列也是要占空间的。

虽然索引大大提高了查询速度,同时确会降低更新表的速度,如对表进行INSERT、UPDATE、DELETE。

因为更新表时,MySQL不仅要保存数据,还要保存一下索引文件每次更新添加了索引列的字段。

使用原则

不过度索引

主键自动建立唯一索引

索引条件(where)后面最频繁的条件比较适合建立索引

查询中与其他表关联的字段,外键关系建立索引

索引散列值过于集中的值不适合索引。例如:性别‘男’,‘女’,增加索引意义不大

频繁更新的字段不适合建立索引,因为每次更新不单单是更新了记录还会更新索引

类型

普通索引 (index):仅仅加快查询速度

唯一索引 (unique index):行上的值不能重复

主键索引 (primary key):不能重复。主键必唯一,但索引不一定是主键,一张表上,只能有一个主键,但是可以有一个或多个唯一索引

全文索引 (fulltext):在mysql的默认情况下,对中文作用不大

查看一张表上所有索引

show index from 表明

建立索引

alter table 表名 add index|unique|fulltext [索引名](列明)

主键索引
alter table 表名 add primary key (列明)

删除索引

alert table 表名 drop index 索引名;
删除主键
alert table 表明 drop promary key;

全文索引

match (全文索引名) against  ('keyword');

全文索引停止词,全文索引不针对非常频繁的词做索引,例如:this,is,you,my等等

全文索引在mysql的默认情况下,对中文意义不大。没有空格隔开,无法识别每个单词。


二:事务

储存引擎

myisam : 批量插入的速度快,不支持事务,表锁

innodb:批量插入的速度低,支持事物,行锁

bdb,memory,archive

建表语句
create table sometable (
    id int
) engine myisaml chartset utf8

事务特性 ACID特性

1. A(atomicity) 原子性。一个事务的执行被视为一个不可分割的最小单元。事务里面的操作,要么全部成功执行,要么全部失败回滚,不可以只执行其中的一部分。
2. C(consistency) 一致性。一个事务的执行不应该破坏数据库的完整性约束。如果上述例子中第2个操作执行后系统崩溃,保证A和B的金钱总计是不会变的。
3. I(isolation) 隔离性。通常来说,事务之间的行为不应该互相影响。然而实际情况中,事务相互影响的程度受到隔离级别的影响。
4. D(durability) 持久性。事务提交之后,需要将提交的事务持久化到磁盘。即使系统崩溃,提交的数据也不应该丢失。

需要使用事务的数据库选择innodb引擎。

开启事务

start transaction;
执行相关语句
sql ...
sql ...

提交/回滚

所有语句成功提交
commit; 
任何一项失败回滚
rollback;

注意:有些语句会造成事务的隐式提交,例如:start transaction;


三:触发器

触发器是与表有关的数据库对象,在满足定义条件时触发,并执行触发器中定义的语句集合。触发器的这种特性可以协助应用在数据库端确保数据的完整性。

一张表发生了某件事(插入、删除、更新操作),然后自动触发了预先编写好的若干条SQL语句的执行;

监听:增删改

触发:增删改

使用场景:

1. 商品订单量与库存

2. 消费多少金额触发某件事情,例如信用卡消费超过多少金额触发禁用操作

3. 新订单产生时,通知相关业务进行相应的业务

4. 增删改操作新增日志等

......

触发器创建语法4要数

监视地点(table)

监视事件(insert/update/delete)

触发时间(after/before)

触发事件(insert/update/delete)

创建单个语法

creact trigger trigger_name
tirgger_time trigger_event on 表名 
for each row trigger_stmt

trigger_name:触发器的名称
tirgger_time:触发时机,为before或者after
trigger_event:触发事件,为insert、delete或者update
trigger_stmt:触发器的程序体,可以是一条SQL语句或者是用begin和end包含的多条语句

创建有多个执行语句的触发器

create trigger 触发器名 触发时间 触发事件
on 表名 for each row
begin
    执行语句列表
end;

注意点:

一般情况下,mysql默认是以 ; 作为结束执行语句,与触发器中需要的分行起冲突
为解决此问题可用DELIMITER,如:DELIMITER $,可以将结束符号变成$
当触发器创建完成后,可以用DELIMITER ;来将结束符号变成;

对于insert而言,新增的行用new来表示,行中的每一列值,用 new.列名 来表示

对于delete而言,删除的行用old来表示,行中的每一列值,用 old.列名 来表示

对于update而言

修改前的数据用old表示,行中的每一列值,用 old.列名 来表示

修改后的数据用new来表示,行中的每一列值,用 new.列名 来表示

例如:

delimiter $

create trigger trigger_name
after insert on tb1 
for each row
begin
update g_table set num=num-new.much where id=new.id;
end$

delimiter ;


删除触发器

drop trigger trigger_name



博主 zane 发表于 2018-04-26 11:01:46,添加在了 mysql 标签下

打赏

您的支持将鼓励我继续努力与分享。

扫码打赏,建议金额1-10元

提醒:打赏金额将直接进此方账号,无法退款,请您谨慎操作。

评论

正在加载验证码......

提交

deddkddkldkdlkdlk 

2018-07-03 14:55:11