11、MySql优化

1、学习目标：

MYSQL优化
1）表设计：存储引擎、字段类型、范式
2）功能：索引、缓存、分区
3）架构：主从复制、读写分离、负载均衡
4）合理的SQL：测试、经验

SQL注入攻击和防御

2、MySQL存储引擎的概念

3、存储引擎的区别

Show engines 查询存储引擎列表
1、 innodb引擎：mysql5.5之后默认引擎
提供事务、行级锁定、外键约束的存储引擎，事务安全性存储引擎、更加注重数据的完整性和安全性。
数据按主键顺序进行存储，插入时做排序工作，插入效率低，速度慢
行级锁 row-level-locking，并发能力强、多版本并发控制MVCC
擅长更新和删除
2、MyISAM引擎：mysql5.5之前默认引擎
擅长高速读和写，如微博、聊天记录等
插入速度快、空间占用量小。
支持全文索引，
数据进行压缩存储 : myisampack 表文件 压缩后的表为只读表
并发性：只支持表级锁定，
但支持并发插入：写操作中的插入数据时，不会阻塞读操作

4、字段类型的选择

1、满足需求
2、尽可能小（占用存储空间少）
Tinyint、smallint、mediumint、int、bigint
varchar()、char(255)
DateTime(8字节)，timestamp(4字节)
3、尽可能定长（占用存储空间固定）
char\varchar，double(float)\decimal
4、尽可能使用整数（计算）
5、多用位运算

5、逆范式

设计：满足第3范式，降低冗余度
优化：逆范式处理
打破范式，为了提高效率，但会增加冗余度、业务逻辑
如：班级表：班级ID、学生数量
注：建议在项目设计之初，就加入逆范式，不要在项目后期进行

6、索引

索引：利用关键字，就是记录的部分数据，建立记录位置的对应关系
类型（4种）：
A、主索引（主键索引primary） B、唯一索引(unique) C、普通索引（index） D、全文索引(fulltext index)
复合索引：多个字段；前缀索引；聚簇索引
索引排序：
索引覆盖：索引数据覆盖了查询的全部数据，就不需要查询表数据，只要索引数据
索引原则：
1、索引，没有使用到，则索引无效
2、列独立：字段独立在表达式的一侧
3、左原则：A、like 关键字一定要在左边，如’a%’、’b%c’等
以通配符开始，不能使用到索引，如:’%a%’,’_b%’等
B、复合索引：只针对左边字段，有效果
4、MYSQL智能选择

7、Explain执行计划

Explain select * from t_user \G

8、慢查询日志

定位执行较慢的查询语句方案。
Set global slow_query_log=1;
Set long_query_time=2 ;–2秒

9、查询缓存

开启查询缓存
显示：Show variables like ‘query_cache%’;
开启：set global query_cache_type=1;
set global query_cache_size=1024102432;

注意事项：
1、查询缓存存在，严格依赖select语句一致，大小写也要一样
2、如果查询出现动态数据，则不能使用缓存
3、一旦开启查询缓存，MYSQL会将所有可以被缓存的select语句全部缓存，
如果不用缓存，则可以使用 SQL_NO_CACHE

源数据改动，缓存清空，不是到时间清空

10、SQL语句优化

1、并发性的SQL
少用多表操作，如:子查询、join等，将复杂操作拆分。
2、如果查询很原子（很小），会增加查询缓存的利用率
3、大量数据的插入：
多条insert、load data into table
建议，先关闭约束及索引，完成后，再生成约束及索引
4、分页：limit offset,size;

11、SQL注入攻击和防御

如何理解SQL注入（攻击）？
所谓SQL注入，就是通过把SQL命令插入到输入框提交或输入域名或页面请求的查询字符串，最终达到欺骗服务器执行恶意的SQL命令。
具体来说，它是利用现有应用程序，将（恶意的）SQL命令注入到后台数据库引擎执行的能力，它可以通过在输入框中输入（恶意）SQL语句得到一个存在安全漏洞的网站上的数据库，而不是按照设计者意图去执行SQL语句。

SQL注入是怎么产生的？
1）开发人员无法保证所有的输入都已经过滤

2）攻击者利用发送给SQL服务器的输入参数构造可执行的SQL代码（可加入到get请求、post请求、http头信息、cookie中）

3）数据库未做相应的安全配置

如何进行SQL注入攻击？
以JAVA编程语言、mysql数据库为例，介绍一下SQL注入攻击的构造技巧、构造方法
1.数字注入
在查询的输入框中输入id=1，点击查询，相当于调用一个查询语句
String sql = “SELECT * FROM article WHERE id =” + id
正常情况下，应该返回一个id=1的文章信息。
那么，如果在输入框输入：id=-1 OR 1 =1，这就是一个SQL注入攻击了，可能会返回所有文章的相关信息。为什么会这样呢？
这是因为，id = -1永远是false，1=1永远是true，所有整个where语句永远是ture，所以where条件相当于没有加where条件，那么查询的结果相当于整张表的内容
SELECT * FROM article WHERE id =-1 OR 1=1
2.字符串注入
有这样一个用户登录场景：登录界面包括用户名和密码输入框，以及提交按钮。输入用户名和密码，提交。
假设正确的用户名和密码为user和pwd123，输入正确的用户名和密码、提交，相当于调用了以下的SQL语句：
SELECT * FROM user WHERE username = ‘user’ ADN password = ‘pwd123’
由于用户名和密码都是字符串，SQL注入方法即把参数携带的数据变成mysql中注释的字符串。mysql中有2种注释的方法：
1）’#’：’#'后所有的字符串都会被当成注释来处理
用户名输入：user’#（单引号闭合user左边的单引号），密码随意输入，如：111，然后点击提交。等价于SQL语句：
SELECT * FROM user WHERE username = ‘user’#'ADN password = ‘111’
'#'后面都被注释掉了，相当于： SELECT * FROM user WHERE username = ‘user’
2）’-- ’ （–后面有个空格）：’-- '后面的字符串都会被当成注释来处理
用户名输入：user’-- （注意–后面有个空格，单引号闭合user左边的单引号），密码随意输入，如：111，然后点击提交按钮。等价于SQL语句： SELECT * FROM user WHERE username = ‘user’-- 'AND password = ‘111’
'-- '后面都被注释掉了，相当于： SELECT * FROM user WHERE username = ‘user’
因此，以上两种情况可能输入一个错误的密码或者不输入密码就可登录用户名为’user’的账号，这是十分危险的事情。

如何预防SQL注入？
这是开发人员应该思考的问题，了解如何预防SQL注入，可以在发现注入攻击bug时，对bug产生原因进行定位。
1）严格检查输入变量的类型和格式
对于整数参数，加判断条件：不能为空、参数类型必须为数字
对于字符串参数，可以使用正则表达式进行过滤：如：必须为[0-9a-zA-Z]范围内的字符串
2）过滤和转义特殊字符
在username这个变量前进行转义，对’、"、\等特殊字符进行转义
3）利用mysql的预编译机制
把sql语句的模板（变量采用占位符进行占位）发送给mysql服务器，mysql服务器对sql语句的模板进行编译，编译之后根据语句的优化分析对相应的索引进行优化，在最终绑定参数时把相应的参数传送给mysql服务器，直接进行执行，节省了sql查询时间，以及mysql服务器的资源，达到一次编译、多次执行的目的，除此之外，还可以防止SQL注入。具体是怎样防止SQL注入的呢？实际上当将绑定的参数传到mysql服务器，mysql服务器对参数进行编译，即填充到相应的占位符的过程中，做了转义操作。