# 软件开发项目实践（一） > 原文：https://pjhubs.com/2018/03/25/%E8%BD%AF%E4%BB%B6%E5%BC%80%E5%8F%91%E9%A1%B9%E7%9B%AE%E5%AE%9E%E8%B7%B5%EF%BC%88%E4%B8%80%EF%BC%89/ > > 作者：PJHubs · 发布于 2018-03-25 · 标签：数据库 **1.自己设计至少一张表，字段不少于10个，包含主键自增、自动生成时间戳等。业务根据自己的兴趣爱好，例如电影、美食、图书、旅游均可以。** ```SQL 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就算了，那是一定支持的的） ```SQL show ENGINES -- Engine字段下为InnoDB为支持事物操作的存储引擎 ``` 创建一个database，创建一张table，（此处全略过），接下来，使用该sql语句查看当前session的事物隔离级别： ```SQL select @@tx_isolation; -- 我的session1和session2执行结果为： -- REPEATABLE-READ ``` 我们可以先来验证一波关于可重复读， ```SQL -- 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 ``` 继续测试可重复读， ```sql -- 清空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`， ```SQL -- 设置隔离级别 set session transaction isolation level read uncommitted; ``` ```SQL -- 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`， ```SQL -- 设置隔离级别 set session transaction isolation level read committed; ``` ```SQL -- 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`, ```SQL -- 改变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`的函数， ```SQL 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`文章表，如下所示： ```SQL +-------------+-----------+------+-----+-------------------+-----------------------------+ | 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`表，如下所示： ```SQL +-------------+-----------+------+-----+-------------------+-----------------------------+ | 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 | +-------------+-----------+------+-----+-------------------+-----------------------------+ ``` 建立普通索引，使得加快查询某一用户所发表的所有文章数据速度： ```SQL alter table m_artcle add index(owner_id) ``` 获取某一用户发布的所有文章，按时间降序排序 ```SQL select * from m_artcle where owner_id = 4 order by create_time desc ``` 获取某一用户被发表评论数最多的文章： ```SQL 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 ) ``` 当注销一个用户后，也要把其发布的文章和评论都一一删除，编写一个触发器： ```SQL 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语句命令.(来自网络) ```SQL -- 查看某个用户发布的文章数 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`表， ```SQL +--------------+-----------+------+-----+-------------------+-----------------------------+ | 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 | +--------------+-----------+------+-----+-------------------+-----------------------------+ ``` ```SQL -- 查看当前是否开启 show variables like 'event_scheduler'; -- 若为off，啧需要打开 set global event_scheduler = on; ``` 如果想要每次重启数据库或者服务器（或者你自己的电脑）时都要保证这个事件是启动的，那么就需要去修改mysql的配置文件，Mac下的mysql配置文件路径为 ```shell $ /usr/local/mysql/support-files/my-default.cnf ``` 使用文本编辑器打开后，在该文件末尾加上如下一行即可（我没试过🙂 ``` event_scheduler=ON ``` 创建一个每隔十秒中就自动统计`m_artcle`表中发布文章数的事件，如下所示， ```SQL 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. 索引字段前加了函数或参加了运算不走索引；