数据库

SQL之连接查询

外连接

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

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

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

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

SQL之聚合函数

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

1.AVG 返回指定组中的平均值，空值被忽略； COUNT 返回指定组中项目的数量。
例：

select prd_no,avg(qty) from sales group by prd_no

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

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 注入

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

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

它的优点有：

允许模块化程序设计，就是说只需要创建一次过程，以后在程序中就可以调用该过程任意次。
允许更快执行，如果某操作需要执行大量 SQL 语句或重复执行，存储过程比 SQL 语句执行的要快。
减少网络流量，例如一个需要数百行的 SQL 代码的操作有一条执行语句完成，不需要在网络中发送数百行代码。
更好的安全机制，对于没有权限执行存储过程的用户，也可授权他们执行存储过程。

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”类型参数。

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

    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.对操作符的优化

    4.对操作符的优化 尽量不采用不利用索引的操作符
    如：in ，not in ， is nul， is not null，<>等
    某个字段你总要会经常用来做搜索，为其建立索引：
    Mysql 中可以使用 alter table 语句来为表中的字段添加索引的基本语法是：
    ALTER TABLE <表名> ADD INDEX (<字段>);
    例：mysql> alter table test add index(t_name);

5.mysql 分库分表：

    分库分表有垂直切分和水平切分两种。

    垂直切分：即将表按照功能模块、关系密切程度划分出来，部署到不同的库上。例如，我们会建立定义数据库 workDB、商品数
    据库 payDB、用户数据库 userDB、日志数据库 logDB 等，分别用于存储项目数据定义表、商品定义表、用户数据表、日志数据表
    等。

    水平切分：当一个表中的数据量过大时，我们可以把该表的数据按照某种规则，例如 userID 散列，进行划分，然后存储到多个
    结构相同的表，和不同的库上。例如，我们的 userDB 中的用户数据表中，每一个表的数据量都很大，就可以把 userDB 切分为结
    构相同的多个 userDB：part0DB、part1DB 等，再将 userDB 上的用户数据表 userTable，切分为很多 userTable：userTable0、
    userTable1 等，然后将这些表按照一定的规则存储到多个 userDB 上。

    ===========================================================================================

    应该使用哪一种方式来实施数据库分库分表，这要看数据库中数据量的瓶颈所在，并综合项目的业务类型进行考虑。

    如果数据库是因为表太多而造成海量数据，并且项目的各项业务逻辑划分清晰、
    低耦合，那么规则简单明了、容易实施的垂直切分必是首选。

    而如果数据库中的表并不多，但单表的数据量很大、或数据热度很高，这种情况之下就应该选择水平切分，水平切分比垂直切分要复杂一些，
    它将原本逻辑上属于一体的数据进行了物理分割，除了在分割时要对分割的粒度做好评估，
    考虑数据平均和负载平均，后期也将对项目人员及应用程序产生额外的数据管理负担。


    在现实项目中，往往是这两种情况兼而有之，这就需要做出权衡，甚至既需要垂直切分，又需要水平切分。
    我们的游戏项目便综合使用了垂直与水平切分，我们首先对数据库进行垂直切分，
    然后，再针对一部分表，通常是

6.单库多表：

    随着用户数量的增加，user 表的数据量会越来越大，当数据量达到一定程度的时候对 user表的查询会渐渐的变慢，从而影响整个 DB 的性能。
    如果使用 mysql, 还有一个更严重的问题是，当需要添加一列的时候，mysql会锁表，期间所有的读写操作只能等待。

    可以将 user 进行水平的切分，产生两个表结构完全一样的 user_0000,user_0001 等表，user_0000 + user_0001 + …的数据刚好是一份完整的数据。

7.多库多表：

    随着数据量增加也许单台 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. 当写压力很大的时候，就必须得进行分库操作。

--内容整理自传智面试宝典

Java面试突击-数据库

数据库