软件开发项目实践（一）
1.自己设计至少一张表，字段不少于10个，包含主键自增、自动生成时间戳等。业务根据自己的兴趣爱好，例如电影、美食、图书、旅游均可以。
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18mysql> 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
3show ENGINES
-- Engine字段下为InnoDB为支持事物操作的存储引擎
创建一个database，创建一张table，（此处全略过），接下来，使用该sql语句查看当前session的事物隔离级别：
1
2
3
4select @@tx_isolation;
-- 我的session1和session2执行结果为：
-- REPEATABLE-READ
我们可以先来验证一波关于可重复读，
1 | -- session1开启事物 |
继续测试可重复读，
1 | -- 清空user表 |
对可重读读的第二次测试出现了幻读，接下来验证read uncommit，
1 | -- 设置隔离级别 |
1 | -- session1 开启事物 |
未提交读避免了更新丢失，但是却导致了脏读，验证read committed，
1 | -- 设置隔离级别 |
1 | -- session1 开启事物 |
提交读避免了脏读，却又出现了不可重复读，最后来验证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
18delimiter $$
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
3select * from m_artcle
where owner_id = 4
order by create_time desc
获取某一用户被发表评论数最多的文章：
1
2
3
4
5select 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
8delimiter ;;
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 | -- 查看当前是否开启 |
如果想要每次重启数据库或者服务器（或者你自己的电脑）时都要保证这个事件是启动的，那么就需要去修改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
8DELIMITER ;;
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种情况。
以下内容均来自网络。🙂
- 条件字段选择性弱，查出的结果集较大，不走索引；
- where条件等号两边字段类型不同，不走索引；
- 优化器分析的统计信息陈旧也可能导致不走索引；
- 索引字段 is null 不走索引；
- 对于
count(*)当索引字段有not null约束时走索引，否则不走索引； - like 后面的字符当首位为通配符时不走索引；
- 使用不等于操作符如：<>、!= 等不走索引；
- 索引字段前加了函数或参加了运算不走索引；