黑马程序员武汉中心——javaee面试宝典 数据库

8、数据库

8.1 Sql之连接查询：

  外连接：

1）左连接（左外连接）以左表为基准进行查询,左表数据会全部显示出来,右表如果和左表匹配的数据则显示相应字段的数据,如果不匹配,则显示为NULL;

2）右连接（右外连接）以右表为基准进行查询,右表数据会全部显示出来,右表如果和左表匹配的数据则显示相应字段的数据,如果不匹配,则显示为NULL;

3）全连接就是先以左表进行左外连接，然后以右表进行右外连接。

  内连接：显示表之间有连接匹配的所有行。

8.2 Sql之聚合函数：

聚合函数是对一组值执行计算并返回单一的值的函数，它经常与SELECT语句的GROUP BY子句一同使用。

 1).AVG  返回指定组中的平均值，空值被忽略； COUNT  返回指定组中项目的数量。
    例：select  prd_no,avg(qty) from sales group by prd_no
 2). MAX  返回指定数据的最大值；MIN  返回指定数据的最小值；SUM  返回指定数据的和，只能用于数字列，空值被忽略。
    例：select  prd_no,max(qty) from sales group by prd_no

 3）使用group by 子句对数据进行分组；对group by 子句形成的组运行聚集函数计算每一组的值；最后用having 子句去掉不符合条件的组；having 子句中的每一个元素也必须出现在select列表中。有些数据库例外，如oracle.

     例：select  prd_no,max(qty) from sales group by prd_no having prd_no>10

8.3 Sql之SQL注入

举例：

select admin from user where username='admin' or  'a'='a' and passwd=''or 'a'='a'

防止SQL注入，使用预编译语句是预防SQL注入的最佳方式，如

select admin from user where username=？And password=?

使用预编译的SQL语句语义不会发生改变，在SQL语句中，变量用问号？表示。像上面例子中，username变量传递的'admin' or  'a'='a' 参数，也只会当作username字符串来解释查询，从根本上杜绝了SQL注入攻击的发生。

 

注意：使用mybaits时 mapper中#方式能够很大程度防止sql注入，$方式无法防止sql注入.

8.4 SQL Select语句完整的执行顺序:

from--->where--->group by--->having--->计算所有的表达式--->order by--->select输出

8.5什么是存储过程？它有什么优点？

 答：存储过程是一组予编译的SQL语句，

 它的优点有：

          允许模块化程序设计，就是说只需要创建一次过程，以后在程序中就可以调用该过程任意次。

          允许更快执行，如果某操作需要执行大量SQL语句或重复执行，存储过程比SQL语句执行的要快。

          减少网络流量，例如一个需要数百行的SQL代码的操作有一条执行语句完成，不需要在网络中发送数百行代码。

          更好的安全机制，对于没有权限执行存储过程的用户，也可授权他们执行存储过程。

   1）MySQL存储过程的创建

      (1). 格式

MySQL存储过程创建的格式：

CREATE PROCEDURE存储过程名(参数列表)

BEGIN

       SQL语句代码块

END

举例：
   CREATE PROCEDURE proc1(OUT s int)  

   BEGIN 

      SELECT COUNT(*) INTO s FROM user;  

   END 

     （了解）  (2).  参数： MySQL 存储过程参数有三种类型：in、out、inout。

如果仅仅想把数据传给 MySQL存储过程，那就使用“in”类型参数；如果仅仅从 MySQL存储过程返回值，那就使用“out”类型参数；如果需要把数据传给 MySQL存储过程，还要经过一些计算后再传回给我们，此时，要使用“inout”类型参数。

  (3). Mysql调用储存过程

   Set @n=1           //声明变量

   Call procName(@n)  //调用储存过程 

8.6：mysql性能优化举例

1） 当只要一行数据时使用 LIMIT 1 

当你查询表的有些时候，你已经知道结果只会有一条结果，在这种情况下，加上 LIMIT    1 可以增加性能。这样一样，MySQL数据库引擎会在找到一条数据后停止搜索，而不是继续往后查少下一条符合记录的数据。 

2）选择正确的存储引擎

在 MySQL 中有两个存储引擎 MyISAM 和 InnoDB，每个引擎都有利有弊。

MyISAM 适合于一些需要大量查询的应用，但其对于有大量写操作并不是很好。甚至你只是需要update一个字段，整个表都会被锁起来，而别的进程，就算是读进程都无法操作直到读操作完成。另外，MyISAM 对于 SELECT COUNT(*) 这类的计算是超快无比的。 
     InnoDB 的趋势会是一个非常复杂的存储引擎，对于一些小的应用，它会比 MyISAM 还慢。他是它支持“行锁” ，于是在写操作比较多的时候，会更优秀。并且，他还支持更多的高级应用，比如：事务。 

3）用Not Exists 代替Not In

      Not Exists允许用户使用相关子查询已排除一个表中能够与另一个表成功连接的所有记录。Not Exists用到了连接，能够发挥已经建好的索引的作用，而Not In不能使用索引。Not In是最慢的方式，要同每条记录比较，在数据量比较大的查询中不建议使用这种方式。

Select a.mobileid from Log_user a where not exists (select b.mobileid from magazineitem b where b.mobileid=a.mobileid);

4）对操作符的优化 尽量不采用不利用索引的操作符

    如：in ，not in ， is nul， is not null，<>等

某个字段你总要会经常用来做搜索，为其建立索引：

    Mysql中可以使用alter table语句来为表中的字段添加索引的基本语法是：
    ALTER TABLE <表名> ADD INDEX (<字段>);

例：mysql> alter table test add index(t_name);

 

6)mysql分库分表：

分库分表有垂直切分和水平切分两种。
垂直切分：即将表按照功能模块、关系密切程度划分出来，部署到不同的库上。例如，我们会建立定义数据库workDB、商品数据库payDB、用户数据库userDB、日志数据库logDB等，分别用于存储项目数据定义表、商品定义表、用户数据表、日志数据表等。
水平切分：当一个表中的数据量过大时，我们可以把该表的数据按照某种规则，例如userID散列，进行划分，然后存储到多个结构相同的表，和不同的库上。例如，我们的userDB中的用户数据表中，每一个表的数据量都很大，就可以把userDB切分为结构相同的多个userDB：part0DB、part1DB等，再将userDB上的用户数据表userTable，切分为很多userTable：userTable0、userTable1等，然后将这些表按照一定的规则存储到多个userDB上。
3.3 应该使用哪一种方式来实施数据库分库分表，这要看数据库中数据量的瓶颈所在，并综合项目的业务类型进行考虑。
如果数据库是因为表太多而造成海量数据，并且项目的各项业务逻辑划分清晰、低耦合，那么规则简单明了、容易实施的垂直切分必是首选。
而如果数据库中的表并不多，但单表的数据量很大、或数据热度很高，这种情况之下就应该选择水平切分，水平切分比垂直切分要复杂一些，它将原本逻辑上属于一体的数据进行了物理分割，除了在分割时要对分割的粒度做好评估，考虑数据平均和负载平均，后期也将对项目人员及应用程序产生额外的数据管理负担。
在现实项目中，往往是这两种情况兼而有之，这就需要做出权衡，甚至既需要垂直切分，又需要水平切分。我们的游戏项目便综合使用了垂直与水平切分，我们首先对数据库进行垂直切分，然后，再针对一部分表，通常是用户数据表，进行水平切分。

 

单库多表 ：

   随着用户数量的增加，user表的数据量会越来越大，当数据量达到一定程度的时候对user表的查询会渐渐的变慢，从而影响整个DB的性能。如果使用mysql, 还有一个更严重的问题是，当需要添加一列的时候，mysql会锁表，期间所有的读写操作只能等待。 
可以将user进行水平的切分，产生两个表结构完全一样的user_0000,user_0001等表，user_0000 + user_0001 + …的数据刚好是一份完整的数据。 

多库多表 ：

随着数据量增加也许单台DB的存储空间不够，随着查询量的增加单台数据库服务器已经没办法支撑。这个时候可以再对数据库进行水平区分。 

分库分表规则举例：

   通过分库分表规则查找到对应的表和库的过程。如分库分表的规则是user_id除以4的方式，当用户新注册了一个账号，账号id的123,我们可以通过id 除以4的方式确定此账号应该保存到User_0003表中。当用户123登录的时候，我们通过123 除以4后确定记录在User_0003中。

mysql读写分离：

  在实际的应用中，绝大部分情况都是读远大于写。Mysql提供了读写分离的机制，所有的写操作都必须对应到Master，读操作可以在Master和Slave机器上进行，Slave与Master的结构完全一样，一个Master可以有多个Slave,甚至Slave下还可以挂Slave,通过此方式可以有效的提高DB集群的每秒查询率. 

  所有的写操作都是先在Master上操作，然后同步更新到Slave上，所以从Master同步到Slave机器有一定的延迟，当系统很繁忙的时候，延迟问题会更加严重，Slave机器数量的增加也会使这个问题更加严重。 
此外，可以看出Master是集群的瓶颈，当写操作过多，会严重影响到Master的稳定性，如果Master挂掉，整个集群都将不能正常工作。 
所以，1. 当读压力很大的时候，可以考虑添加Slave机器的分式解决，但是当Slave机器达到一定的数量就得考虑分库了。 2. 当写压力很大的时候，就必须得进行分库操作。