目录

前言

一、数据库优化

1、数据库优化的必要性

2、数据库优化的几个方面

二、数据库优化方式

1、库表设计

1.1  标识实体关系

1.2  实体关系

1.3  E-R图，实体-联系图

2、库表添加合适的索引

2.1  索引分类

2.2  创建索引

3、分表

4、读写分离

5、存储过程与触发器

6、SQL优化与慢查询

6.1  数据库的增删改查

6.2  show status的相关常用命令

6.3  启动MqSql使用记录慢查询日志（2种）

6.4  explain

7、碎片整理

8、事务

总结

前言

对于一个以数据为中心的应用，数据库的好坏直接影响到程序的性能，因此数据库性能至关重要。

一、数据库优化

1、数据库优化的必要性

系统的数据都从数据库上来，数据库的吞吐量和速度一定程度决定系统的并发和响应速度；
系统运行与数据量成正比，数据读处理尤其是查询自然就慢；
Mysql数据库的数据最终在磁盘上持久化存储，读写不如Redis等这些内存数据库。

2、数据库优化的几个方面

数据库设计：数据表设计遵循三范式，使用合适的数据类型，使用合适的存储引擎；
适当创建索引；
数据库扩展：数据库的分表分库，读写分离等；
SQL语句优化等。

二、数据库优化方式

1、库表设计

良好的数据库设计，能够节省数据库空间，保持数据完整性，方便应用程序的开发；（相反：数据冗余，空间浪费，插入更新繁杂或者异常）

设计数据库

1.1  标识实体关系

以BBS论坛为例

实体：

用户（属性：昵称，密码，邮箱，生日，性别，登记，备注，积分，注册时间）

主贴（属性：标题，正文，发帖时间，状态，发帖人，回复数量，点击数）

回帖（属性：帖子编号，回帖人，回帖标题，回帖正文，回帖时间，点击数）

板块（属性：板块名称，版主，板块格言，点击数，发帖数）

1.2  实体关系

一对一，两个表的主键是公共字段

一对多，主键与非主键之间的关系

多对一，非主键与主键之间 的关系

多对多，非主键与非主键之间的关系

1.3  E-R图，实体-联系图

创建表时，将实体转化为表，将属性转化为列，唯一标识一行数据的列可为主键，无合适字段做主键就用自动增加列， 将关系转化为主外键展示实体之间的关系；

表结构规范化-三范式：

列的原子性。列不可分解，确保每列都不能再分解成更基本的数据单位；

记录的唯一标识。给记录增加一个主键，非主键字段依赖主键字段，即表的列中若有重复数据且与主键无关，则可拆分表；

字段不存在冗余。不存在传递依赖，即若表的除主键外各个列间有直接关联，即非主键字段一个字段可以推导出另一个字段，则可拆分表。

2、库表添加合适的索引

2.1  索引分类

主键索引，主键查询时默认使用；

组合索引，左边用，右边不用；

模糊查询，%或者_写在左边不会用索引，右边会用；

条件语句中如果有or，or的两侧均为索引才能使用，否则不会使用；

普通索引与组合索引区别：多个普通索引MySQL只用到认为似乎是最有效率的一个单列索引；组合索引为最左前缀，name-age-city建索引，相当于name，age，age-name，city-name；

主键索引与唯一索引区别：主键执行计划优于唯一，主键索引不能为空，仅一个主键索引列，主键索引更适合自生成不改变的列，主键可被其他列引为外键；

唯一索引，检索到一个直接返回；普通索引，检查是否是全部才返回；

2.2  创建索引

创建索引语法：

create index 索引名称 on 表明 （字段名）

创建全文索引语法：

CREATE fulltext INDEX 索引名称 ON 表明 （字段名）

例子：

SELECt * FROM articles   WHERe MATCH (title,body) AGAINST ('database'); 两个字段的索引：FULLTEXT (title,body)

SELECt * FROM articles WHERe MATCH (tags) AGAINST ('旅游' IN BOOLEAN MODE);IN BOOLEAN MODE是只有含有关键字就行,不用在乎位置,是不是起启位置.

仅存储引擎为MyISAM支持全文索引，InnoDB不支持不支持全文索引；

mysql默认的阀值是50%，当某字段出现次数只有低于50%（停止词）的才会出现在结果集中；（意思是，全文索引用在海量数据中，不存在高于50%的情况）

fulltext不支持中文，用Sphinx是一个基于SQL的全文检索引擎，结合MySQL，PostgreSQL做全文搜索，他可以提供比数据库本身更专业的搜索功能，使得应用程序更容易实现专业化的全文检索；

3、分表

水平分表：字段不多但是记录行数超级多，达到千万级别，经常检索速度会很慢；按照合理的逻辑去拆分成一个个较小的表，比如按照月份或者类型等待，利于程序简单实现，同时必须考虑到避免union，否则不如不拆分；

垂直分表：记录不多但是字段较多或者较长，占用的空间也比较大，检索需要大量IO，降低性能；拆分时可将较大字段拆分出来，组成一对一的对应关系表；

4、读写分离

数据库服务器压力大时，可以利用主从数据库，对仅仅需要查询，且不特别关注失效性的功能，使用从数据库进行数据的查询；

5、存储过程与触发器

存储过程：可编程的函数，由sql语句和控制结构组成；

sql：需要先编译后执行；存储过程：跨平台和应用使用；速度快，减少网络流量，组件式编程，统一接口参数安全，灵活性差；

​
#语法
CREATE PROCEDURE  过程名([[IN|OUT|INOUT] 参数名 数据类型[,[IN|OUT|INOUT] 参数名 数据类型…]]) [特性 ...] 过程体

#小示例
CREATE PROCEDURE proc3(IN parameter int)
  BEGIN
    DECLARE var int;
    SET var=parameter+1;
    IF var=0 THEN
      INSERT INTO t VALUES (17);
    END IF ;
    IF parameter=0 THEN
      UPDATe t SET s1=s1+1;
    ELSE
      UPDATE t SET s1=s1+2;
    END IF ;
  END ;

​

存储过程内的普通变量

#语法：DECLARE 变量名1[,变量名2...] 数据类型 [默认值];
DECLARE x1 VARCHAr(5) DEFAULT 'outer';　　

变量赋值

#语法：SET 变量名 = 变量值 [,变量名= 变量值 ...]
SET x1=x1+1;

 存储过程中的用户变量

#用户变量一般以@开头
SET @y='Goodbye Cruel World';

参与select/update/where语句

SELECT data1,data2 INTO x1,@y FROM test.table1 LIMIT 1;
update test.table1 set data1=@y;
insert into test.table1 (data1,data2)values(x1,@y);

判断语句

#IF分支：
IF 条件1 THEN 语句;
ELSEIF 条件2 THEN 语句;
......
ELSE 语句;
END IF;
 
#CASE分支:
CASE [条件]
WHEN 条件1 THEN 语句1
WHEN 条件2 THEN 语句2 
......
ELSE 语句n
END CASE

循环语句

LOOP循环：
LOOP
语句群
END LOOP
 
WHILE语句：
WHILE 条件 DO
语句群
END WHILE
 
REPEAT UNTIL语句：
REPEAT
语句群
UNTIL 条件
END REPEAT

跳转或者终止符

ITERATE 语句:  ITERATE只可以出现在LOOP, REPEAT, 和WHILE语句内。ITERATE意思为：“再次循环”   会再次回到label开始位置；
BEGIN
    DECLARE v INT;
    SET v=0;
    LOOP_LABLE:LOOP
      IF v=3 THEN
        SET v=v+1;
        ITERATE LOOP_LABLE;
      END IF;
      INSERT INTO t VALUES(v);
      SET v=v+1;
      IF v>=5 THEN
        LEAVE LOOP_LABLE;
      END IF;
    END LOOP;
  END;
 
LEAVE语句：这个语句被用来退出任何被标注的流程控制构造。它和BEGIN ... END或循环一起被使用，像其他语言中的break。

开始结束符

[begin_label:] BEGIN
语句群
END [end_label]

6、SQL优化与慢查询

切入点：一个较大的项目，我们想了解当前mysql的运行状态、是否有耗时较长的sql执行等待

6.1  数据库的增删改查

一般情况下，增删改总计占数据库的10%，而90%是查询操作；

6.2  show status的相关常用命令

#查看数据库的一些状态
show status；
#显示执行了多少条/次的增删改查
show stauts like 'com_select';
show stauts like 'com_insert';
show stauts like 'com_delete';
show stauts like 'com_update';
#[session|global] 默认是session会话级-只取出当前窗口的执行;global-从mysql启动到现在
show global stauts like 'com_select';
 
#查询当前MySQL本次启动后的运行统计时间(单位：秒)-另外，存储引擎为MyISAM,且运行时间过长，则注意碎片整理
show status like 'uptime';
#查看试图连接到MySQL(不管是否连接成功)的连接数
show status like 'connections';
#查看线程缓存内的线程的数量。
show status like 'threads_cached';
 
#慢查询
#查看查询时间超过long_query_time秒的查询的个数-即慢查询
show status like 'slow_queries';
#可以显示当前慢查询时间(单位：秒)（默认10秒）
show variables like 'long_query_time';
#可以修改慢查询时间(单位：秒)
set long_query_time=1;

6.3  启动MqSql使用记录慢查询日志（2种）

#第一种：中括号[]内的部分是可选的，file_name表示日志文件路径
#在5.5及以上版本的MySQL中，使用如下命令启动：
mysqld --safe-mode --show-query-log[=1] [--show-query-log-file=file_name]
#在5.0、5.1等低版本的MySQL中，使用如下命令启动：
mysqld --log-slow-queries[=file_name]
 
#第二种：启动命令配置到my.ini中的[mysqld]节点
[mysqld]
#设置慢查询界定时间为1秒
long_query_time=1
#5.0、5.1等版本配置如下选项
log-slow-queries="mysql_slow_query.log"
#5.5及以上版本配置如下选项
slow-query-log=On
slow_query_log_file="mysql_slow_query.log"

6.4  explain

explain命令，可以用显示mysql如何使用索引来处理select语句以及连接表；　

 

上述图片中的字段再次描述：

id：查询序号，即执行顺序号，不重要；

select_type：simple 它表示简单的select,没有union和子查询；primary 最外面的select,在有子查询的语句中，最外面的select查询就是primary；union union语句的第二个或者说是后面那一个；

table：显示这一行的数据是关于哪张表的;

possible_keys：可能会使用的索引；

key：实际使用的索引；优化where语句，选择合适的字段或者表字段；

key_len：索引长度，越短越好；

ref：使用某个库表字段 去 匹配表中数据

rows：查询的行数，越小越好；

extra：关于mysql如何解析查询的额外信息

　　关注当内容显示using temporary，即需要优化sql，因为用到了缓存；--尽力用小表驱动大表

7、碎片整理

存储引擎为MyISAM

数据insert会使用其占用空间增加，但delete数据不会是其占用的空间减少，原因：删除数据时，mysql并不会回收被已删除数据的占据的存储空间以及索引位；而是等待新的数据来弥补这个空缺；

语法命令（定期optimize）：

#删除数据后的优化 - 碎片整理
optimize table 表名

8、事务

尽管我们可以使用子查询（Sub-Queries）、连接（JOIN）和联合（UNIOn）来创建各种各样的查询，但不是所有的数据库操作都可以只用一条或少数几条SQL语句就可以完成的。更多的时候是需要用到一系列的语句来完成某种工作。但是在这种情况下，当这个语句块中的某一条语句运行出错的时候，整个语句块的操作就会变得不确定起来。设想一下，要把某个数据同时插入两个相关联的表中，可能会出现这样的情况：第一个表中成功更新后，数据库突然出现意外状况，造成第二个表中的操作没有完成，这样，就会造成数据的不完整，甚至会破坏数据库中的数据。要避免这种情况，就应该使用事务，它的作用是：要么语句块中每条语句都操作成功，要么都失败。换句话说，就是可以保持数据库中数据的一致性和完整性。事物以BEGIN关键字开始，COMMIT关键字结束。在这之间的一条SQL操作失败，那么，ROLLBACK命令就可以把数据库恢复到BEGIN开始之前的状态。

实例：
BEGIN; INSERT INTO salesinfo SET CustomerID=14; UPDATE inventory SET Quantity=11 WHERe item='book'; COMMIT;

总结

数据库优化，是一种综合性的技术，不是通过某一种方式让数据库效率提高很多，而是通过各个方面的优化，来是数据库效率明显的稳步的提高。