Sql 之连接查询
外连接:
1) 左连接(左外连接)以左表为基准进行查询,左表数据会全部显示出来,右表 如果和左表匹配 的数据则显示相应字段的数据,如果不匹配,则显示为 NULL;
2) 右连接(右外连接)以右表为基准进行查询,右表数据会全部显示出来,右表 如果和左表匹配的数据则显示相应字段的数据,如果不匹配,则显示为 NULL;
3) 全连接就是先以左表进行左外连接,然后以右表进行右外连接。
内连接:
显示表之间有连接匹配的所有行。
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
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 Select 语句完整的执行顺序:
from--->where--->group by--->having--->计算所有的表达式--->order by-->select 输出
什么是存储过程?它有什么优点?
答:存储过程是一组予编译的 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) //调用储存过程
Mysql 性能优化举例
1) 当只要一行数据时使用 LIMIT
当你查询表的有些时候,你已经知道结果只会有一条结果,在这种情况下, 加上 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);
5)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. 当写压力很大的时候,就必须 得进行分库操作。