MySQL杂谈

重拾mysql，记点新知识

1、关于外键约束，项目中一直少用外键约束，是怕删除的时候删除不掉，原来是可以设置的，关于外键约束有四种更新行为

　　NO ACTION：当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许更新、删除。（与RESTRICT一致）

　　RESTRICT：当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则不允许更新、删除。（与NO ACTION一致）

　　CASCADE （级联）：当在父表中删除/更新对应记录时，首先检查该记录是否有对应外键，如果有则也删除/更新外键在子表中的记录。

　　SET NULL：当在父表中删除对应记录时，首先检查该记录是否有对应外键，如果有则设置子表中该外键值为null（需要要求该外键值允许为null）

　　SET DEFAULT：父表有变更时，子表将外键列设置成一个默认的值（innoDB不支持）

ALTER TABLE表名ADD CONSTRAINT外键名称FOREIGN KEY (外键字段) REFERENCES 主表名(主表字段名) ON UPDATE CASCADE ON DELETE CASCADE; 

SQL语句如下

 

 SQLyog中可以在外部键这里设置

 

2、关于mysql事务

  方法1：

　　首先查看当前事务的提交方式， 使用SELECT @@autocommit; 默认为1，也就是自动提交，不需要我们手动commit和rollback

　　使用 SET @@autocommit = 0, 修改为手动提交；

　　执行经典的转账操作（张三给李四转1000块钱）

--1 查询张三账户余额
select * from account where name = "zs"

--2 将张三余额-1000
update account set money = money - 1000 where name = "zs"

--3 将李四余额+1000
update account set money = money +1000 where name = "ls"

　　选中三条语句执行后，account并不会有变化，需要我们再执行 commit；指令提交事务，然后account才发生变化

　　如果执行三条语句时报错了，需要执行 rollback；进行事务回滚，account不会发生任何变化。

　方法2：

　　　不需要执行 SET @@autocommit = 0， 还是使用默认的自动提交， 在需要使用事务的指令前添加 start transaction；

-- 开启事务
start transaction

--1 查询张三账户余额
select * from account where name = "zs"

--2 将张三余额-1000
update account set money = money - 1000 where name = "zs"

--3 将李四余额+1000
update account set money = money +1000 where name = "ls"

　　选中四条指令一起执行，之后和第一种方法一样需要commit或者rollback

 

3、问：InnoDB主键索引高度为2的B+树可以储存多少数据？

　　首先需要知道每页可以储存固定大小16kb的数据，InnoDB指针固定占用6个字节的空间，一页可以储存n个主键和n+1个指针。

　　而主键占用空间根据他的数据结构决定，如果是int占用4个字节，如果是bigint占用8个字节。

　　这里假设主键是bigint，表中每行数据大小为1k，则一页中可以储存16行数据。

　　n * 8 + ( n + 1) * 6 = 1024 * 16,  得出 n 约为 1170， 也就是1页可以储存1170个key， 1171个指针

　　1171 * 16 = 18376， 得到2层的B+树可以储存18376行的数据。

 

　　如果是三层的话，再乘以个1171，

　　18376 * 1171 = 21939865， 可以储存两千多万的数据，所以说InnoDB引擎的检索效率很高，再往上的话，四五千万，甚至上亿的数据，一般就要考虑分库分表了。

 

4、查看SQL执行频率

　　输入指令 SHOW GLOBAL STATUS LIKE 'Com_______'       注： 下划线代表占位符，共7个

　可以看到当前表各种语句执行的次数，Select语句占比多少，可以作为SQL调优的依据。配合慢查询日志

 

 

5、利用explain进行性能分析

　　任意一条查询语句，在前面加上explain

explain SELECT u.user_name, n.nation FROM fd_user AS u, nation AS n WHERE u.fk_nation_id = n.id

 各字段含义如下：

id：select查询的序列号，表示查询中执行select子句或者是操作表的顺序（id相同，执行顺序从上到下，id不同，值越大，越先执行）

select_type：表示SELECT的类型，常见的取值有SIMPLE (简单表,即不使用表连接或者子查询)、PRIMARY (主查询，即外层的查询）、

UNION （UNION中的第二个或者后面的查询语句）、SUBQUERY（SELECT/WHERE之后包含了子查询）等。

type：表示连接类型，性能由好到差的连接类型为NULL，system，const，eq_ref，ref，range，index，all。

　　　　const：使用主键或者唯一索引访问

　　　　ref：使用非唯一索引

　　　　all：全表扫描，性能最差

　　　　index：用了索引，但是也会对索引进行扫描，遍历整个索引树。

possible_keys：可能用到的索引

key：实际用到的索引，如果为null，则没用到索引

key_len：表示索引中使用的字节数，该值为索引字段最大可能长度，并非实际使用长度，在不损失精确性的情况下，长度越短越好。

rows：MYSQL认为必须要执行查询的行数，在InnoDB中，是一个预估值，可能不总是准确的。

filtered：表示返回结果的行数占需读取行数的百分比，filtered的值越大越好。