1.自己设计至少一张表,字段不少于10个,包含主键自增、自动生成时间戳等。业务根据自己的兴趣爱好,例如电影、美食、图书、旅游均可以。

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
mysql> desc user;
+-------------+-------------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-------------+------+-----+-------------------+-----------------------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| phone | varchar(11) | NO | PRI | NULL | |
| passwd | varchar(16) | NO | | NULL | |
| nickname | varchar(20) | NO | | NULL | |
| gender | varchar(5) | NO | | NULL | |
| home | varchar(50) | NO | | NULL | |
| status | varchar(30) | NO | | NULL | |
| birth | varchar(50) | NO | | NULL | |
| body_height | int(11) | NO | | NULL | |
| body_weight | int(11) | NO | | NULL | |
| createTime | timestamp | NO | | CURRENT_TIMESTAMP | |
| updateTime | datetime | YES | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------------+-------------+------+-----+-------------------+-----------------------------+
12 rows in set (0.00 sec)

2. 验证事务的四个特性以及四个隔离级别。

实践所采取的数据库为MySQL,MySQL中的事物主要用于处理操作量大,复杂度高的数据。用我自己的话来说,我们每一个人都有属于自己的一个校园邮箱,毕业后这个邮箱会被收回并且注销,说白了,实际上就是当我们毕业后,教务会把我们的信息给删除了,并且还会告知网管中心把这个学生的邮箱账号也给删除,甚至还会告知其它部门同步的把这个学生对应的信息统统删除。现在都讲信息化,我们需要一套流程去规范它,这套流程就可以理解为事物,说得再实际一些,就是一坨SQL语句的集合。

事物有四大特性和四大隔离级别。四大特性分别为:

  • 原子性: 事物中的所有操作,要么全部执行完,要么全部不执行。就好比,有可能这个学生留级了要回来重新读书,所以教务不能够因为毕业时间到就把所有学生信息都删除,而是要等待一个迂回时间进行统一。
  • 一致性: 在事物执行的整个生命周期中,数据库的完整性没有被破坏,包括实体完整性、参照完整性、用户自定义完整性等。
  • 隔离性: 包括事物的四个隔离级别。这个性质比较重要,后边细说。
  • 持久性: 事物执行完毕后,对数据的修改就是永久的,宕机也是OJBK的。🙂

    四大隔离级别为:

  • 未提交读: 该隔离级别下避免数据丢失,只要你更新了数据就一定能够保证更新成功,但却有可能(基本上100%)出现脏读,也就是可能读取到其他会话中未提交事务修改的数据。说的再透彻些,就是指当一个事务正在执行访问数据的SQL,并且对数据进行了修改,而这种修改还没有commit到数据库中,此时另外一个事务也执行了相同的访问数据SQL,那么就会读取到前面事物中修改的数据。
  • 提交读: 在该隔离级别下,避免了脏读,只能读取到已经提交的数据。当事务还没有结束时,另外一个事务也访问该同一table下的数据,此时并不能读到第一个事物修改的数据,但正因为如此,又有可能出现了不可重复读问题。
  • 可重复读: 在同一个事务内的查询都是事务开始时刻一致的。若在A时刻创建了事物一,随后又在B时刻创建了事物二,那么位于AB两个时刻下创建的事物只在之前时刻的数据是一致的,此后位于时刻A创建的事物一与位于时刻B创建的事物二再无任何关系(因为会被锁住该事物下的所有行),但是却会出现幻读。
  • 串行读: 该隔离级别下会发现比如完全串行化的读,每次读都需要获得表级共享锁,读写相互都会阻塞,有事物没有被提交,其它事物只能等着。

    说的可能有些些的绕口,用我自己的话来说,就好比要去抄别人的作业。未提交读 的意思是:我随时可以看同桌试卷上的答案,不管他此时此刻到底写没写题,读没读题,我想看的时候,就去看好了。提交读 的意思是:我只能看到同桌写完并且停笔的题。没写过的题或者写了没停笔的题,我是看不了的。可重复读 的意思:我开始写题之前,必须要小心,因为一旦选定某个时机开始写题后,就再也不能看到同桌写的题目了,假设我8点半开始写题,那我就只能看到8点半此时同桌完成的题目,随后的任何时间里我都看不到了,除非等待下一场考试。串行读 的意思:这个隔离级别下我和同桌简直就是难兄难弟,他写完一道题后,我抄一道题,我抄完一道题后,他开始写一道题(如果你的同桌愿意这么干的话),也可以认为你的同桌对你不好,你要抄他的题之前,必须要得到他的允许。(用了自己的大白话说了一遍,比较尴尬。肯定有错误的地方,不过也可以先这么构建起来。

    接下来先看看如何实现为提交读,也就是如果不管session1和session2有何关系,只要session1中进行了修改,那么session2中就可以读到。

    不过在进行实验之前,我们首先得先确定自己所使用的数据库是否支持事物操作(不过如果你是mysql就算了,那是一定支持的的)

    1
    2
    3
    show ENGINES

    -- Engine字段下为InnoDB为支持事物操作的存储引擎

    创建一个database,创建一张table,(此处全略过),接下来,使用该sql语句查看当前session的事物隔离级别:

    1
    2
    3
    4
    select @@tx_isolation;

    -- 我的session1和session2执行结果为:
    -- REPEATABLE-READ

    我们可以先来验证一波关于可重复读,

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    -- session1开启事物
    begin

    select * from user;
    -- select结果为空数据

    -- session2 开启事物
    begin

    insert into user(phone, passwd, nickname, gender, home, status, birth, body_height, body_weight) value('18811758987', 'qweqwe123qweqwe', 'kkl', 'man', 'china', 'travel', '1997/12/05', '165', '67');

    commit
    -- session2 commit

    -- session1
    select * from user;
    -- select结果为空数据,此时session1并未查到session2提交数据,解决了脏读
    commit
    -- session1 commit

    继续测试可重复读,

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    -- 清空user表
    truncate table user;

    --session1 开启事物
    begin

    select * from user;
    -- select结果为空数据

    -- session2 开启事物
    begin

    insert into user(phone, passwd, nickname, gender, home, status, birth, body_height, body_weight) value('18811758987', 'qweqwe123qweqwe', 'kkl', 'man', 'china', 'travel', '1997/12/05', '165', '67');
    -- session2 commit
    commit

    -- session1
    select * from user;
    -- session1 select返回空数据

    -- 此时执行update passwd
    update user set passwd='woaiwoziji321'

    -- 😱恐怖的事情发生了!明明上一次select出来的空集合,但是却update成功了!!!

    -- 此时再select一次
    select * from user;
    -- 返回了session2中insert的数据

    对可重读读的第二次测试出现了幻读,接下来验证read uncommit

    1
    2
    -- 设置隔离级别
    set session transaction isolation level read uncommitted;
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    -- session1 开启事物
    begin

    -- session2 开启事物
    begin

    select * from user;
    -- 返回空集合

    -- session1 insert
    insert into user(phone, passwd, nickname, gender, home, status, birth, body_height, body_weight) value('18811758987', 'qweqwe123qweqwe', 'kkl', 'man', 'china', 'travel', '1997/12/05', '165', '67');

    -- session2 select
    select * from user;
    -- 😱返回了session1中insert的数据

    -- session1 commit
    commit

    -- session2 commit
    commit

    未提交读避免了更新丢失,但是却导致了脏读,验证read committed

    1
    2
    -- 设置隔离级别
    set session transaction isolation level read committed;
    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    -- session1 开启事物
    begin

    -- session1 select
    select * from user;
    -- 返回空集合

    -- session2 开启事物
    begin
    -- session2 select
    select * from user;
    -- 返回空集合

    -- session2 insert
    insert into user(phone, passwd, nickname, gender, home, status, birth, body_height, body_weight) value('18811758987', 'qweqwe123qweqwe', 'kkl', 'man', 'china', 'travel', '1997/12/05', '165', '67');

    -- session1 select
    select * from user;
    -- 返回了空集合

    -- session2 commit
    commit

    -- session1 select
    select * from user;
    -- 😱返回了session2中insert的数据

    -- session1 commit
    commit

    提交读避免了脏读,却又出现了不可重复读,最后来验证Serializable,

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    -- 改变session1的隔离级别为 serializable
    set session transaction isolation level serializable;
    -- session1 开启事物
    begin

    -- session2 开启事物
    begin

    -- session1 select
    select * from user;

    -- session2 insert
    insert into user(phone, passwd, nickname, gender, home, status, birth, body_height, body_weight) value('18811758987', 'qweqwe123qweqwe', 'kkl', 'man', 'china', 'travel', '1997/12/05', '165', '67');

    -- 此时会进行等待,如果你用terminal,那就是啥都不出现,如果你用DB manager会出现loading

    -- session1 select
    select * from user;
    -- 返回空集合

    -- session commit
    commit
    -- commit结束后,session2的loading结束,插入数据完成

    -- session1 select
    select * from user;
    -- 返回空集合

    -- session2 commit
    commit

3. 利用insert into使该表的数据量达到百万级别。
实验文档给了个提示,使用insert into来进行,那么我的猜想,老师应该不会让我们按20次键盘来达到所谓的百万级别又或者其它数据,因此我继续猜想,老师应该是想让我们用mysql的自定义函数来做,因此,写了一个addRowToMillion的函数,

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
delimiter $$
create function addRowToMillion(x int) returns int
begin
declare i int default 1;
declare sum int default 0;
pjwhile:while i < x do
insert into user(
phone, passwd, nickname, gender, home, status, birth, body_height, body_weight
) select
phone, passwd, nickname, gender, home, status, birth, body_height, body_weight
from user;
set i = i + 1;
set sum = i;
end while;
return sum;
end
$$
delimiter ;

然后直接使用select addRowToMillion(times),即可达到把table中数据拉到自己想要的级别,当然前提得是你的table中务必保证有至少一条数据。

4. 自己定义一个业务实现,业务不做硬性规定,但是技术点要包括:分组,排序,avg、sum、max、min中至少两个,索引,触发器,存储过程,事件。
一个用户可以发布多篇文章和多个评论,一篇文章下可以有多个用户的评论。

新增两张表,分别为m_artcle文章表,如下所示:

1
2
3
4
5
6
7
8
9
+-------------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-----------+------+-----+-------------------+-----------------------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| owner_id | int(11) | NO | PRI | NULL | |
| content | text | NO | | NULL | |
| create_time | timestamp | NO | | CURRENT_TIMESTAMP | |
| update_time | datetime | YES | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------------+-----------+------+-----+-------------------+-----------------------------+

m_comment表,如下所示:

1
2
3
4
5
6
7
8
9
10
+-------------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+-------------+-----------+------+-----+-------------------+-----------------------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| owner_id | int(11) | NO | PRI | NULL | |
| artcle_id | int(11) | NO | PRI | NULL | |
| content | text | NO | | NULL | |
| create_time | timestamp | NO | | CURRENT_TIMESTAMP | |
| update_time | datetime | YES | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+-------------+-----------+------+-----+-------------------+-----------------------------+

建立普通索引,使得加快查询某一用户所发表的所有文章数据速度:

1
alter table m_artcle add index(owner_id)

获取某一用户发布的所有文章,按时间降序排序

1
2
3
select * from m_artcle
where owner_id = 4
order by create_time desc

获取某一用户被发表评论数最多的文章:

1
2
3
4
5
select id, content from m_artcle
where id = (select artcle_id from m_comment
group by artcle_id
order by count(artcle_id) desc limit 1
)

当注销一个用户后,也要把其发布的文章和评论都一一删除,编写一个触发器:

1
2
3
4
5
6
7
8
delimiter ;;
create trigger `tri_delete_user` after delete on `user` for each row
begin
delete from m_artcle where owner_id = old.id;
delete from m_comment where owner_id = old.id;
end
;;
DELIMITER ;

查看一个用户发布文章数,和一个文章下的评论数,使用存储过程进行:
PS : 存储过程是存储在数据库服务器中的一组sql语句,通过在查询中调用一个指定的名称来执行这些sql语句命令.(来自网络)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- 查看某个用户发布的文章数
delimiter ;;
create procedure `select_artcle_count`(ownerID int)
begin
select count(id) from m_artcle where owner_id = ownerID;
end;;
delimiter ;

-- 执行
call select_artcle_count(3)

-- 查看某个文章下的评论数
delimiter ;;
create procedure `select_comment_count`(artcleID int)
begin
select count(id) from m_comment where artcle_id = artcleID;
end;;
delimiter ;

-- 执行
call select_comment_count(3)

每一小时执行统计一遍当前发布的文章数(测试,10秒即可),在正式运行之前得先判断当前数据库是否开启事件功能,当然如果你是mysql 5.1之前的版本就别看了,不支持的,不过我猜现在应该也没人会去装5.1之前的版本吧。

需要新增一张用户计数的c_artcle表,

1
2
3
4
5
6
7
8
+--------------+-----------+------+-----+-------------------+-----------------------------+
| Field | Type | Null | Key | Default | Extra |
+--------------+-----------+------+-----+-------------------+-----------------------------+
| id | int(11) | NO | PRI | NULL | auto_increment |
| artcle_count | int(11) | NO | | NULL | |
| create_time | timestamp | NO | | CURRENT_TIMESTAMP | |
| update_time | datetime | YES | | CURRENT_TIMESTAMP | on update CURRENT_TIMESTAMP |
+--------------+-----------+------+-----+-------------------+-----------------------------+

1
2
3
4
-- 查看当前是否开启
show variables like 'event_scheduler';
-- 若为off,啧需要打开
set global event_scheduler = on;

如果想要每次重启数据库或者服务器(或者你自己的电脑)时都要保证这个事件是启动的,那么就需要去修改mysql的配置文件,Mac下的mysql配置文件路径为

1
$ /usr/local/mysql/support-files/my-default.cnf

使用文本编辑器打开后,在该文件末尾加上如下一行即可(我没试过🙂

1
event_scheduler=ON

创建一个每隔十秒中就自动统计m_artcle表中发布文章数的事件,如下所示,

1
2
3
4
5
6
7
8
DELIMITER ;;
create event `event_artcleCount_second` on schedule every 10 second starts now() on completion preserve enable do
BEGIN
insert into c_artcle(artcle_count)
select count(*) from _artcle where unix_timestamp(now()) - unix_timestamp(create_time) < 10;
END
;;
DELIMITER ;

5. 自己查询资料,验证在何种情况下无法走到索引,例如like,找到至少3种情况。
以下内容均来自网络。🙂

  1. 条件字段选择性弱,查出的结果集较大,不走索引;
  2. where条件等号两边字段类型不同,不走索引;
  3. 优化器分析的统计信息陈旧也可能导致不走索引;
  4. 索引字段 is null 不走索引;
  5. 对于count(*) 当索引字段有not null约束时走索引,否则不走索引;
  6. like 后面的字符当首位为通配符时不走索引;
  7. 使用不等于操作符如:<>、!= 等不走索引;
  8. 索引字段前加了函数或参加了运算不走索引;