[慕课电商项目] 02. 对用户模块 select 语句的优化

一、对用户模块用到的 select 语句的优化

1. 给 email 列添加唯一索引

  在用户表中，email 是不允许重复的，而且经常需要查询从前台传来的邮箱在表中是否存在。在原项目中，没有给 email添加索引，所以我们给 email 添加上唯一索引，可以确保表中的 email 字段没有重复值，更可以加快查询。

   这种操作可以用在相似的字段上。

ALTER TABLE 表名 ADD UNIQUE INDEX 索引别名(列名)
ALTER TABLE mmall_user ADD UNIQUE INDEX email_unique(email)

2. 对 select 语句的优化

(1) 原项目中 select 需要改进的地方在哪里 ？

  原项目中，在查询用户名或者邮箱是否存在时，是这样查询的：

  <select id="checkUsername" resultType="int" parameterType="string" >
    select count(1) from mmall_user
    where username = #{username}
  </select>

   这个看起来已经比着 select count(*) 优化了，但是我觉得还不够。sql 语句中 count 函数会在全表中进行检索，即使在 limit 1 存在的情况下，同样会在全文中检索。

limit 的作用：

   limit 子句可以被用于强制 SELECT 语句返回指定的记录数。

做个实验：

   用户表中前 4 行记录的手机号是重复的，我们用这个做实验。

    id  username          PASSWORD                          email                  phone       
------  ----------------  --------------------------------  ---------------------  ----------- 
     1  admin             427338237BD929443EC5D48E24FD2B1A  admin@happymmall.com   13800138000 
    13  geely             08E9A6EA287E70E7E3F7C982BF7923AC  geely@happymmall.com   13800138000  
    17  rosen             095AC193FE2212EEC7A93E8FEFF11902  rosen1@happymmall.com  13800138000  
    21  soonerbetter      DE6D76FE7C40D5A1A8F04213F2BEFBEE  test06@happymmall.com  13800138000  
    22  b95fc04aadd74     12345jaslkdfjiwe                  t4sl1q3h@sohu.com      13506194910
    90  1539530           123kdfjiqewrwe                    ff0vrke@qq.com         15706052224 

执行代码：

1：SELECT COUNT(1) FROM mmall_user WHERE phone = '13800138000'

2：SELECT COUNT(1) FROM mmall_user WHERE phone = '13800138000' LIMIT 1

3：SELECT COUNT(1) FROM mmall_user WHERE phone = '13800138000' LIMIT 0;

结果：

  count(1)      count(1)       count(1)
----------    ----------     ----------
        4           4

   可以看到，即使加了 limit 1，但是因为 count() 的存在，还是在全表中检索了一遍。在这里 limit 1 是不起作用的，但是 limit 0 却起了作用。我猜想，既然 limit 1 的意思是，限制数据库只输出一行记录，那么 count 函数的结果总共只有一条，所以数据库就在count 函数中忽略了 limit 1 的限制（这是我猜的，并没有确凿证据）。

(2) 如何改进 ？

   在用户表中，有自增的 id 作为主键，我们在查找某值在某列中是否存在时 select id 即可，这样没有了count函数的打扰 limit 1 就起了作用，在查找到符合条件的第一条记录后就不再继续检索了。

代码：

  <select id="checkUsername" resultType="java.lang.Integer">
    SELECT id FROM mmall_user WHERE username = #{name} limit 1;
  </select>

   如果查到了，则返回具体的 id 值；如果没查到，则返回 null 。在 service 层通过判断返回值是否是 null ，就得到用户名是否存在了。