mysql数据库篇

1.sql优化

Sql优化首先需要找到需要优化的sql，也就是执行比较慢的sql语句，我们在项目中主要用mysql数据库较多，以mysql数据库为例，可以采用开启mysql慢日志，通过set global slow_query_log=1语句开启慢查询日志，通过show variables like '%slow_query_log%' 查看慢查询日志开启状态和存储位置，通过设置long_query_time的时间，来界定执行时间超过多久的sql为慢查询sql，设置log-queries-not-using-indexes可以把未使用索引的sql语句也输出到慢查询中。也可以使用第三方数据库连接池比如德鲁伊(druid)，设置logSlowSql属性为true，通过slowSqlMillis设置慢sql的界定时间。查找到执行较慢的sql语句之后，根据具体问题对sql语句进行分析，导致sql语句执行较慢的因素主要有

1. 过多的表联查

在拿到执行较慢的sql语句之后，分析其有无冗余的表联查，若存在，则去掉，使用嵌套查询解决多表直接联查问题，可以把多个表的查询结果分开执行，然后再把结果合并到一块。若无法解决表联查问题，可以把表的联查结果写入redis这样的noSQl数据库中，查询时直接走NoSql数据库获取表联查结果，这种适用于查询较多，写入请求较少的情况。

1. 数据量较大的表查询未使用索引，或使用了索引，但是索引未生效。

首先使用sql的执行计划查看当前sql语句有无使用索引，使用索引查询的执行类型是什么，使用EXPLAIN关键字对sql语句进行分析，返回结果中的TYPE字段表示索引的使用情况，ALL代表全表扫描，也就是未使用索引，const表示主键索引或唯一索引，查询效率最高，ref引用查找，查找非唯一索引匹配项，range索引范围查找。Key代表当前查询所使用的实际索引。根据sql执行计划分析索引的使用情况，对于有条件的表（数据量超过10万条以上，查询较频繁）添加索引，对于查询添加了索引，应注意一下几点，a.添加索引的字段必须为where或group by中使用的字段，否则sql语句不会执行索引，多个字段同时查询可以考虑组合索引。b.添加索引的字段不能进行运算操作，因为索引的使用是在sql编译器编译时选择的，若对索引字段进行运算，其值只有在sql语句执行时才能确定，所以会导致索引失效。c使用like模糊查询时前后都有%或前面有%时则索引失效，使用组合索引时第一个字段匹配则走索引，第一个字段不匹配则索引失效，各个字段都匹配索引效率最高。字段顺序尽量与索引顺序保持一致，E 加索引的字段不能为空值（单个字段为null会走索引但是多个字段都为null并且使用or连接时就会全表扫描）。F 一个表的索引尽量不要超过6个，否则会降低写入表的性能。

1. 一次查询返回的数据量较大

考虑需求的合理性，采用分页减少数据量的返回

1. 不同数据库之间Sql语句解析器解析方式不同

Mysql查询采用自上而下查询，过滤掉最大数据量的条件必须紧跟where子句之后，缩小查询的范围，例如查询北京市有多少男性用户，则要先查询地址为北京，再查询性别。Oralce采用自下而上的查询方式，过滤最大数据量的条件必须放在where子句的末尾。

2.mysql存储引擎

（1）：MyISAM存储引擎：不支持事务、也不支持外键，优势是访问速度快，对事务完整性没有要求或者以select，insert为主的应用基本上可以用这个引擎来创建表

支持3种不同的存储格式，分别是：静态表；动态表；压缩表

静态表：表中的字段都是非变长字段，这样每个记录都是固定长度的，优点存储非常迅速，容易缓存，出现故障容易恢复；缺点是占用的空间通常比动态表多（因为存储时会按照列的宽度定义补足空格）ps：在取数据的时候，默认会把字段后面的空格去掉，如果不注意会把数据本身带的空格也会忽略。

动态表：记录不是固定长度的，这样存储的优点是占用的空间相对较少；缺点：频繁的更新、删除数据容易产生碎片，需要定期执行OPTIMIZE TABLE或者myisamchk-r命令来改善性能

压缩表：因为每个记录是被单独压缩的，所以只有非常小的访问开支

（2）InnoDB存储引擎

该存储引擎提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比MyISAM引擎，写的处理效率会差一些，并且会占用更多的磁盘空间以保留数据和索引。 
InnoDB存储引擎的特点：支持自动增长列，支持外键约束

(3)：MEMORY存储引擎

Memory存储引擎使用存在于内存中的内容来创建表。每个memory表只实际对应一个磁盘文件，格式是.frm。memory类型的表访问非常的快，因为它的数据是放在内存中的，并且默认使用HASH索引，但是一旦服务关闭，表中的数据就会丢失掉。 
MEMORY存储引擎的表可以选择使用BTREE索引或者HASH索引，两种不同类型的索引有其不同的使用范围

Hash索引优点： 
Hash 索引结构的特殊性，其检索效率非常高，索引的检索可以一次定位，不像B-Tree 索引需要从根节点到枝节点，最后才能访问到页节点这样多次的IO访问，所以 Hash 索引的查询效率要远高于 B-Tree 索引。 
Hash索引缺点：那么不精确查找呢，也很明显，因为hash算法是基于等值计算的，所以对于“like”等范围查找hash索引无效，不支持；

Memory类型的存储引擎主要用于哪些内容变化不频繁的代码表，或者作为统计操作的中间结果表，便于高效地对中间结果进行分析并得到最终的统计结果，。对存储引擎为memory的表进行更新操作要谨慎，因为数据并没有实际写入到磁盘中，所以一定要对下次重新启动服务后如何获得这些修改后的数据有所考虑。

（4）MERGE存储引擎

Merge存储引擎是一组MyISAM表的组合，这些MyISAM表必须结构完全相同，merge表本身并没有数据，对merge类型的表可以进行查询，更新，删除操作，这些操作实际上是对内部的MyISAM表进行的。

3.数据库视图

数据库中的视图是一个虚拟表，但它同真实表一样，包含一系列带有名称的行和列数据。行和列数据来自由定义视图查询所引用的表，并且在应用视图时动态生成。另外，视图还可以在已经存在的视图的基础上定义。

　　视图一经定义变存储在数据库中，与其相对应的数据并没有像表那样在数据库中再存储一份，通过视图看到的数据只是存储在基本表中的数据。对视图的操作与对标的操作一样，可以对其进行查询、修改和删除。当对通过视图看到的数据进行修改时，相应的基本表中的数据也会发生变化；同时，若是基本表的数据发生变化，则这种变化也会自动地反映在视图上。

 创建视图的基本格式如下：

   CREATE VIEW <视图名称> [(column_list)]

AS SELECT语句;

　在刚创建好的view_01视图进行查询：

select * from view_01 ;

4.数据库存储过程

存储过程（Stored Procedure）是一种在数据库中存储复杂程序，以便外部程序调用的一种数据库对象。

存储过程是为了完成特定功能的SQL语句集，经编译创建并保存在数据库中，用户可通过指定存储过程的名字并给定参数(需要时)来调用执行。

存储过程思想上很简单，就是数据库 SQL 语言层面的代码封装与重用。

优点

存储过程可封装，并隐藏复杂的商业逻辑。

存储过程可以回传值，并可以接受参数。

存储过程无法使用 SELECT 指令来运行，因为它是子程序，与查看表，数据表或用户定义函数不同。

存储过程可以用在数据检验，强制实行商业逻辑等。

缺点

存储过程，往往定制化于特定的数据库上，因为支持的编程语言不同。当切换到其他厂商的数据库系统时，需要重写原有的存储过程。

存储过程的性能调校与撰写，受限于各种数据库系统。

创建存储过程

CREATE PROCEDURE tree_procedure()

#存储开始

BEGIN

select * from t_tree;

#结束

END

调用存储过程

call tree_procedure();

5.数据库优化

当数据库数据量逐渐增多，sql优化不足以满足系统需求时，则要对数据库进行优化，平时使用mysql数据库较多，以mysql数据库为例分析，数据库优化主要分为

1. 数据库配置优化

选择合理的数据库存储引擎，mysql常用的存储引擎主要有两种，一个是MyISAM，不支持事务处理，读性能处理快，表级别锁。另一个是InnoDB，支持事务处理（ACID），设计目标是为处理大容量数据发挥最大化性能，行级别锁。考虑到数据库事务问题，我们选择使用的是InnoDB，以InnoDB为例设置数据库相关参数，innodb_buffer_pool_size设置索引和数据缓冲区大小，一般设置为物理内存的60%，innodb_flush_log_at_trx_commit关键参数，0代表大约每秒写入到日志并同步到磁盘，数据库故障会丢失1秒左右事务数据。1为每执行一条SQL后写入到日志并同步到磁盘，I/O开销大，执行完SQL要等待日志读写，效率低。2代表只把日志写入到系统缓存区，再每秒同步到磁盘，效率很高，如果服务器故障，才会丢失事务数据。对数据安全性要求不是很高的推荐设置2，性能高，修改后效果明显。innodb_file_per_table = OFF  默认是共享表空间，共享表空间idbdata文件不断增大，影响一定的I/O性能。推荐开启独立表空间模式，每个表的索引和数据都存在自己独立的表空间中，可以实现单表在不同数据库中移动

1. 系统内核优化

我们的mysql部署在64位的linux系统中，系统为centos7，对linux内核进行适当的优化也能提高数据库性能。net.ipv4.tcp_fin_timeout设置连接超时时间，避免过长的错误等待，net.ipv4.tcp_tw_reuse = 1 允许TIME_WAIT socket重新用于新的TCP连接，减少开销。设置net.ipv4.tcp_tw_recycle=1，开启TIME_WAIT socket快速回收。设置net.ipv4.tcp_max_tw_buckets增大TIME_WAIT socket数量， 设置net.ipv4.tcp_max_syn_backlog，进入SYN队列最大长度，加大队列长度可容纳更多的等待连接，

1. 硬件配置

增大物理内存，通过文件系统延迟写入机制使数据暂时先存入内存中增加写入效率，使用固态硬盘代替传统机械硬盘。

1. 数据库架构扩展

随着业务量的增大，单节点数据库无法满足业务需求，考虑做数据库集群

1. 增加缓存

使用缓存适当降低数据库I/O访问频率，把操作较多的数据存入缓存库中。减少对数据库的访问。

  数据库的分表分库策略：

        分表:

横切割:数据量过大时，把表按照数据规则进行分割

       常见分表策略，

1：按照时间区间分割，将不同年或月的数据存入不同的数据库中，视单位时间内数据的产生量而定。

2:使用hash取模分表，根据表中的一个关键字段值例如用户id进行hash运算后取模，得到固定余数，根据得到的数值存入不同的数据库中，

纵切割:表中字段过多，但是常用的字段比较固定，此时就可以根据字段把表分成两个一对一的表，经常被查询的字段分为一张表，不经常查询的字段放在另外一张表

例如user表和user_info表，用户表要使用用户名密码登录，而很少有用户经常去查看自己的个人信息，此时就可以把表分为一个user表和一个user_info表，两张表之间使用一对一关联，当用户想看详细信息时，再去查询user_info表，类似于hibernate的懒加载机制。

分库：

水平分割:大体上和分表的横切割是一致的，就是数据量较大，按照数据规则分割

例如按照账户位数（与qq类似）分割，6位数的用户查A库，7位数的用户查B库

垂直分割:按照项目的业务逻辑进行分割例如日志相关的功能存日志库，库存相关的功能存库存库，订单相关的功能存订单库。

6.SQL语句的执行顺序：

查询中用到的关键词主要包含六个，并且他们的顺序依次为 

select--from--where--group by--having--order by 

其中select和from是必须的，其他关键词是可选的，这六个关键词的执行顺序 

与sql语句的书写顺序并不是一样的，而是按照下面的顺序来执行 

from--where--group by--having--select--order by, 

from:需要从哪个数据表检索数据 

where:过滤表中数据的条件 

group by:如何将上面过滤出的数据分组 

having:对上面已经分组的数据进行过滤的条件  

select:查看结果集中的哪个列，或列的计算结果 

order by :按照什么样的顺序来查看返回的数据