性能优化
常见
-
Where子句中：
where表之间的连接必须写在其他Where条件之前，那些可以过滤掉最大数量记录的条件必须写在Where子句的末尾.HAVING最后。 -
用EXISTS替代IN、用NOT EXISTS替代NOT IN。
-
避免在索引列上使用计算。
-
避免在索引列上使用IS NULL和IS NOT NULL
-
对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
-
应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描。
-
应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。
为表中字段选择合适的数据类型
字段类型优先级: 整形>date,time>enum,char>varchar>blob,text
优先考虑数字类型，其次是日期或者二进制类型，最后是字符串类型，同级别得数据类型，应该优先选择占用空间小的数据类型。
CHAR和VARCHAR的区别
1.CHAR和VARCHAR类型在存储和检索方面有所不同。
2.CHAR列长度固定为创建表时声明的长度，长度值范围是1到255。
当CHAR值被存储时，它们被用空格填充到特定长度，检索CHAR值时需删除尾随空格。
字段类型和编码
MySQL返回字符串长度
CHARACTER_LENGTH(同CHAR_LENGTH)方法返回的是字符数，LENGTH函数返回的是字节数，一个汉字三个字节。
varvhar等字段建立索引长度计算语句
select count(distinct left(test,5))/count(*) from table；越趋近1越好
MySQL的utf8
MySQL的utf8最大是3个字节不支持emoji表情符号，必须只用utf8mb4。需要在MySQL配置文件中配置客户端字符集为utf8mb4。
JDBC的连接串不支持配置characterEncoding=utf8mb4，最好的办法是在连接池中指定初始化SQL，例如：hikari连接池，其他连接池类似spring . datasource . hikari . connection - init - sql =set names utf8mb4。否则需要每次执行SQL前都先执行set names utf8mb4。
MySQL排序规则(一般使用_bin和_genera_ci)
utf8_genera_ci不区分大小写，ci为case insensitive的缩写，即大小写不敏感；
utf8_general_cs区分大小写，cs为case sensitive的缩写，即大小写敏感，但是目前MySQL版本中已经不支持类似于***_genera_cs的排序规则，直接使用utf8_bin替代；
utf8_bin将字符串中的每一个字符用二进制数据存储，区分大小写。
那么，同样是区分大小写，utf8_general_cs和utf8_bin有什么区别？
cs为case sensitive的缩写，即大小写敏感；bin的意思是二进制，也就是二进制编码比较；
utf8_general_cs排序规则下，即便是区分了大小写，但是某些西欧的字符和拉丁字符是不区分的，比如ä=a，但是有时并不需要ä=a，所以才有utf8_bin；
utf8_bin的特点在于使用字符的二进制的编码进行运算，任何不同的二进制编码都是不同的，因此在utf8_bin排序规则下：ä<>a。
EXPLIAN
- desc或者explain加上你的SQL。
- extended explain加上你的SQL，然后通过show warnings可以查看实际执行的语句，这一点也是非常有用的，很多时候不同的写法经SQL分析后，实际执行的代码是一样的。
优化
-
设计良好的数据库结构，允许部分数据冗余，尽量避免join查询，提高效率。
-
选择合适的表字段数据类型和存储引擎，适当的添加索引。
-
mysql库主从读写分离。
-
找规律分表，减少单表中的数据量提高查询速度。
5.添加缓存机制。
比如memcached，apc等。
-
不经常改动的页面，生成静态页面。
-
书写高效率的SQL。
比如 SELECT * FROM TABEL 改为 SELECT field_1, field_2, field_3 FROM TABLE.
锁的优化策略
-
读写分离
-
分段加锁
-
减少锁持有的时间
-
多个线程尽量以相同的顺序去获取资源
不能将锁的粒度过于细化，不然可能会出现线程的加锁和释放次数过多，反而效率不如一次加一把大锁。
千万大表在线修改
MySQL在表数据量很大的时候，如果修改表结构会导致锁表，业务请求被阻塞。MySQL在5.6之后引入了在线更新，但是在某些情况下还是会锁表，所以一般都采用pt工具( Percona Toolkit)。
如对表添加索引：
pt-online-schema-change --user=‘root’ --host=‘localhost’ --ask-pass --alter “add index idx_user_id(room_id,create_time)”
D=fission_show_room_v2,t=room_favorite_info --execute
慢查询日志
有时候如果线上请求超时，应该去关注下慢查询日志，慢查询的分析很简单，先找到慢查询日志文件的位置，然后利用mysqldumpslow去分析。查询慢查询日志信息可以直接通过执行SQL命令查看相关变量，常用的SQL如下：
–查看慢查询配置
–slow_query_1og 慢查询日志是否开启
–slow_query_1og_file 的值是记录的慢查询日志到文件中
–long_query_time 指定了慢查询的阈值
–1og_queries_not_using_indexes 是否记录所有没有利用索引的查询
SHOW VARIABLES LIKE ‘%quer%’;
–查看慢查询是日志还是表的形式
SHOW VARIABLES LIKE ‘1og_output’;
–查看慢查询的数量
SHOW GLOBAL STATUS LIKE ‘slow_queries’;
mysqldumpslow的工具十分简单，主要用到的是参数如下：
- -t：限制输出的行数，我一般取前十条就够了；
- -s：根据什么来排序默认是平均查询时间at，我还经常用到c查询次数，因为查询次数很频繁但是时间不高也是有必要优化的，还有t查询时间，查看那个语句特别卡；
- -v：输出详细信息。
例子：mysqldumpslow -v -s t -t 10 mysql_slow.log.2018-11-20-0500
查看SQL进程和杀死进程
如果执行了一个SQL的操作，但是迟迟没有返回，可以通过查询进程列表看看它的实际执行状况，如果该SQL十分耗时，为了避免影响线上可以用kill命令杀死进程，通过查看进程列表也能直观的看下当前SQL的执行状态；如果当前数据库负载很高，在进程列表可能会出现，大量的进程夯住，执行时间很长。
命令如下：
1 | --查看进程列表 |
一些数据库性能的思考
在对公司慢查询日志做优化的时候，很多时候可能是忘了建索引，像这种问题很容易解决，加个索引就行了。但是有几种情况就不是简单加索引能解决了：
1、业务代码循环读数据库
考虑这样一个场景，获取用户粉丝列表信息，加入分页是十个，其实像这样的SQL是十分简单的，通过连表查询性能也很高。但是有时候，很多开发采用了取出一串ID，然后循环读每个ID的信息，这样如果ID很多对数据库的压力是很大的，而且性能也很低。
2、统计SQL
很多时候，业务上都会有排行榜这种，发现公司有很多地方直接采用数据库做计算，在对一些大表的做聚合运算的时候，经常超过五秒，这些SQL一般很长而且很难优化。像这种场景，如果业务允许（比如一致性要求不高或者是隔一段时间才统计的），可以专门在从库里面做统计。另外我建议还是采用Redis缓存来处理这种业务。
3、超大分页
在慢查询日志中发现了一些超大分页的慢查询如limit 40000,1000，因为MySQL的分页是在server层做的，可以采用延迟关联在减少回表。但是看了相关的业务代码正常的业务逻辑是不会出现这样的请求的，所以很有可能是有恶意用户在刷接口，最好在开发的时候也对接口加上校验拦截这些恶意请求。
- 如果有自增列，truncate语句会把自增列的基数重置为0，有些场景用自增列作为业务上的ID需要十分重视；
- 聚合函数会自动滤空，比如a列的类型是int且全部是null，则SUM(a)返回的是null而不是0；
- MySQL判断相等不能用“a=”，这个结果永远为UnKnown，where和having中，UnKnown永远被视为false，check约束中，UnKnown就会视为true来处理。所以要用“a is ”处理。
SQL注入与防止
SQL注入产生的原因：
程序开发过程中不注意规范书写sql语句和对特殊字符进行过滤，导致客户端可以通过全局变量POST和GET提交一些sql语句正常执行。
防止SQL注入的方式：
开启配置文件中的magic_quotes_gpc 和 magic_quotes_runtime设置
执行sql语句时使用addslashes进行sql语句转换
Sql语句书写尽量不要省略双引号和单引号。
过滤掉sql语句中的一些关键词：update、insert、delete、select、 * 。
提高数据库表和字段的命名技巧，对一些重要的字段根据程序的特点命名，取不易被猜到的。
