JAVA开发学习笔记（三）数据库部分

数据库遵循索引最左原则。自我理解，使用where语句时，能够排除最多无效数据的判断放where后的最左边一个。
例如：检索活动列表，where后面接的判断语句应该是先日期，然后再是活动权益名
缺省情况下建立的索引是非群集索引，但有时它并不是最佳的，合理的索引设计要建立在对各种查询的分析和预测上
where条件中的in，在逻辑上相当于or，所以如果条件语句是where id_no in(‘0’,‘1’)，那么实际上语法分析器会转化为id_no=‘0’ or id_no='1’来执行，所以会造成耗时较长
sql模糊查询：select * from 表 where 字段 like 条件
关于条件搜索，有四种匹配方式

	1、可以使用%表示任意0个或者多个字符，可以匹配任意类型和长度的字符，如果关键词是中文，那么需要使用%关键词%来进行搜索。使用方式是select * from a where name like '%王%'
	2、可以使用_表示任意单个字符。比如 select * from a where name like '王_'	这样只会检索出所有姓名是王开头的，并且名字是两个字的姓名
	3、可以使用[]表示方括号内字符中的任意一个，指定一个字符、字符串或者范围，要求匹配对象为其中一个。比如 select * from a where name like '[张王李]三'	这样只会找出叫张三、王三、李三的人
		还有种比如 select * from a where name like '王[1-3]'	那么检索出的信息人名是王1，王2，王3
	4、可以使用[^ ]来排除包含方括号内字符的信息。比如 select * from a where name like '[^张王李]三'	检索出的信息会排除所有叫张三、王三、李三的人的信息

一般来说：

1/有大量重复值，且经常有范围查询（between，>,<,>=,<=）和order by，group by发生的列，可考虑间离群集索引
2/经常同事存取多列，且每列都含有重复值，可以考虑间离组合索引
3/组合索引要尽量使关键查询形成索引覆盖，其前导列一定是使用最频繁的列
4/夺标操作在被实际执行钱，查询优化器会根据连接条件，列出几组可能的连接方案并从中找出系统开销最小的最佳方案。连接条件要充分考虑带有索引的表，行数多的表；内外表的选择可由公式：外层表的匹配行数*内层表中每一次查找的次数确定，乘积最小为最佳方案。
5/查看执行方案的方法--用set showplanon，打开showplan选项，就可以看到连接顺序、使用何种索引的信息；想要看到更加详细的信息，需要用sa角色执行dbcc（3604，310，302），3604是指将结果输出到客户端而不是日志文件，310是指index的使用详情，302是指查看优化器的优化plan。
6/任何对列的操作都会导致表的扫描，它包括数据库函数、计算表达式等，查询时要尽可能的吧操作移至等号的右边。例如select * from a where id >= 1*2*3
7/in、or子句会经常使用工作表，使得索引失效。如果不产生大量重复值，可以考虑把子句拆开；拆开的子句中应该包含索引
8/要善于使用存储过程，它会是sql变得更加灵活和高效
1/对于查询应该进行优化，尽量避免全表扫描。首先应当考虑在where以及order by涉及的列上面建立缩影
2/应该精良避免在where子句中对字段进行null判断，否则会导致引擎放弃使用缩影而进行全表扫描。比如判断某个值is null，那么可以设置这个值默认值是0，确保表中这个值的列没有null，然后检索这个值 = 0
3/尽量避免在where子句中使用 !=或者<>操作符，否则引擎将放弃使用索引，而进行全表扫描
4/尽量避免在where子句中使用 or 来连接条件，否则引擎将放弃使用索引，而进行全表扫描。如果使用or的话，可以换成union all。
	例如 select id from t where num = 10 or num = 20,应该改为select id from t where num = 10 union all select id from t where num = 20
5/in和not in 也要慎用，否则会导致全表扫描。比如select id from t where num in (1,2,3)，这种连续的数值，能用between，就别使用in。select id from t where num between 1and 3
6/例如select id from t where name like '%abc%' 也会导致全表扫描
7/应该尽量避免在where子句中对字段进行表达式操作，这样会导致引擎放弃使用索引，而进行全表扫描
	比如select id from t where num/2 = 100，应该改为select id from t where num = 100*2
8/应该避免在where子句中队字段进行函数操作，这样会导致引擎放弃使用索引，而进行全表扫描
	例如select id from t where substring(name,1,3)='abc'(搜索以abc为开头的name的id)。应该改为select id from t where name like '%abc%'
9/不要在where子句的"="左边进行函数、算术运算或者其他表达式运算，否则系统将无法正确使用索引
10/在使用索引字段使用条件时，如果是复合索引，那么必须使用到该索引的第一个字段作为条件时才能保证系统正确使用该索引，否则该索引不会被使用。并且需要注意，要尽可能的让字段顺序与索引顺序相一致
11/不要写一些没有任何意义的查询，比如要生成一个空表结构 select col1,col2 into #t from t where 1=0。这种代码不会返回任何结果集，但是会消耗系统资源。所以应该修改为create ttable #t(...)
12/很多时候使用exists来代替in是一个比较好的选择。例如select num from a where num in (select num from b)替换成select num from a where exists(select 1 from b where num=a.num)
13/并不是所有索引对于查询都有效，sql是根据表中的数据来进行查询优化的，当索引又大量数据重复时，SQL查询可能不回去利用索引。比如表中又字段sex，male，female几乎各一半，那么即使在sex上建立了索引也对效率提升起不了什么作用
14/索引并不是越多越好，索引的确是可以提高相关表的select的效率，但是同时也降低了insert和update的效率。以为insert和update的时候有可能会重新建立索引，所以如何建索引需要仔细考虑
15/尽量使用数字型字段，如果只含有数值信息的字段尽量不要设计为字符型，这样会降低查询和连接的性能，并且增加存储开销。因为引擎在处理查询和连接时会逐个比较字符串中的每一个字符，但是对于数字型的话只需要比较一次就可以了
16/尽可能的使用varchar来代替char，因为首先边长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率会更高
17/任何时候都不要使用select * from t，要使用具体字段来代替*，不要返回用不到的任何字段
18/避免频繁的创建和删除临时表，以减少系统表资源的消耗
19/临时表不是不可使用，适当的使用可以是某些例程更加有效。比如需要重复引用大型表或者常用表中的某个数据集时，但是，对于一次性时间，最好使用导出表
20/在新建临时表的时候，如果一次性插入数据量过大，那么可以使用select into 代替 create table ，避免造成大量log以提升速度。如果数据量不大，为了缓和系统表的资源，应该先create table，然后在insert
21/如果使用到了临时表，在存储过程的最后一定要把所有的临时表全部显式删除。先truncate table，然后drop table，这样可以避免系统表的较长时间锁定
22/尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1w行，那么就应该考虑改写
23/使用基于游标的方法或者临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效
24/与临时表一样，游标并不是不可使用，对于小型数据集使用FAST_FORWARD游标通常要由于其他逐行处理办法，尤其是在必须引用几个表才能获取到想要的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快，如果开发时间允许，基于游标的方法和基于集的方法都可以尝试一下，看看哪种方法效果更好
25/尽量避免大失误操作，提高系统并发能力
26/尽量避免想客户端返回大数据量，如果数据量过大，应该考虑相关需求是否合理。或者使用分表返回

**

防止Sql注入

**

1/什么是sql注入：sql注入实际上是利用程序编写期间产生的疏漏，利用sql语句，实现读写修改数据库的目标 
2/sql注入的大致思路：
	1、寻找到sql注入的位置
	2、判断服务器类型和后台数据库类型
	3、针对不同的服务器和数据库特点进行sql注入攻击
3/sql注入攻击实例：
	比如在登陆界面，要求输入用户名和密码，在用户名处输入 ' or 1=1 --。由于后台认证程序中的sql语句是这样：
	String loginSql = "select * from user_table where username ='" + username + "' and password = '" + password +"'";
	当上述的用户名输入之后，sql语句变成了：select * from user_table where username ='' or 1=1 -- and password = '',而这条sql语句中的--表示把后面的语句给注释掉，确保不起作用。由于1=1恒成立，所以这条sql语句始终能正确执行，从而骗过系统，获取合法身份。
	如果是输入语句更改为 select * from user_table where username='';DROP DATABASE (DB NAME) --'and password=''，则直接删除表中所有用户信息，造成更大危害。
4/常用解决办法
	1.PreparedStatement：采用预编译语句集，内置了处理sql注入的能力，只要使用它的setXXX方法传值即可
		优势：代码的可读性和可维护性
		PreparedStatement尽最大可能新提高了性能
		最重要的是极大的提高了安全性
		原理：sql注入只对sql语句的准备(编译)过程有着破坏作用，而PreparedStatement已经准备好了，执行阶段只把输入串作为数据处理，而不在对sql语句进行解析、准备，因此避免了sql注入问题
	2.使用正则表达式过滤传入的参数：
		需要引入 import java.util.regex.*包，
		正则表达式：private String CHECKSQL = "^(.+)\\sand\\s(.+)|(.+)\\sor(.+)\\s$";
		判断是否匹配：
		Pattern.marches(CHECKSQL , targerStr);
		具体正则表达式为：
		检测SQL meta-characters的正则表达式：/(\%27)|(\-\-)|(\%23)|(#)/ix
		修正检测SQL meta-characters的正则表达式：/\w*((\%27)|(\' ))((\%6F)|o|(\%4F))((\%72)|r|(\%52))/ix
		典型的SQL注入攻击的正则表达式：/\w*((\%27)|(\' ))((\%6F)|o|(\%4F))((\%72)|r|(\%52))/ix
		检测SQL注入，UNION查询关键字的正则表达式：/((\%27)|(\' ))union/ix/(\%27)|(\' )
		检测MS SQL Server SQL注入攻击的正则表达式：/exec(\s|\+)+(s|x)p\w+/ix
		等
	3.字符串过滤：比较通用的一个方法（||之间的参数可以根据自己程序的需要增减）

public static boolean sql_inj(String str){
				String inj_str = "'|and|exec|insert|select|delete|update|count|*|%|chr|mid|master|truncate|char|declare|;|or|-|+|,";
				String inj_stra[] = split(inj_str,"|");
				for(int i = 0 ; i &lt ; inj_stra.length;i++){
					if(str.indexOf(inj_stra[i]>=0)){
						return true;
					}
				}
				return false;
			}

4.jsp中调用该函数检查是否包含非法字符
5.jsp页面判断代码
		使用js语句在客户端进行不安全字符屏蔽，
		功能介绍：检查是否含有"'","\\","/"
		参数说明：要检查的字符串
		返回值：0是，1不是

function check(a){
				return 1;
				fibdn = new Array("'","\\","/");
				i = fibdn.length;
				j = a.length;
				for(ii = 0 ; ii < i ; ii++){
					for(jj = 0 ; jj < j ; jj++){
						temp1 = a.charAt(jj);
						temp2 = fibdn[ii];
						if(tem' ; p1 == temp2){
							return 0;
						}
					}
				}
				return 1;
			}

凡设计到执行sql中有变量的，用JDBC（或者其他的数据持久层）提供的比如PreparedStatement就可以，切记不要用拼接字符串的方式。
有操作风险的API函数：

-Statement.exectue*
-PreparedStatement.exectue*
-CallableStatement.exectue*
-JdbcTemplate.qurey*
-JdbcTemplate.insert
-JdbcTemplate.update
-JdbcTemplate.delete
-JdbcTemplate.execute
-Session.createQuery
-Session.createSQLQuery
-Session.createFilter*

防治思想：
	1.输入校验：做好规范的校验工作，比如搜索框不能输入sql语句等
	2.权限控制：在创建一个SQL数据库的用户账户时，要遵循最低权限法则。用户应该只拥有使用账户的必要的最低的权限。如果系统显示需要用户可以读取和修改自己的数据，那么就应该限制其权限，使得他们之恶能读写自己的数据，而不是别人的
	3.重复校验：在服务器端重复客户端所进行的所有过滤（黑名单验证和白名单验证）
	4.动态参数：应该使用preparedStatement语句绑定变量来执行sql字符串，没有绑定的变量很容易收到攻击
	使用严格的白名单型来检查可以用于sql命令的所有用户输入数据，而不是避开元字符集，完全禁止元字符集才是最安全的。原因是后期对于已经被输入数据库的数据进行使用可能将之前使用过的元字符丢弃。所以应该对于request中期望的安全的字符集精选更为精细的定义
	在使用mybatis的时候，将尽可能使用#（param）占位符的方式去避免sql注入

参考：https://blog.csdn.net/jie_liang/article/details/77340905
https://blog.csdn.net/Darkjazz11/article/details/86535904
https://www.cnblogs.com/baizhanshi/p/6002898.html