关系型数据库优化（单机）

关系型数据库优化（单机）

一、关系型数据库瓶颈

1.高并发读写需求

   网站的用户并发性非常高，往往达到每秒上万次读写请求，对于传统关系型数据库来说，硬盘I/O是一个很大的瓶颈。例如：双十一购物
   双十一购物节，号称优惠比较多。所以很多人都在那天购物，特别是11.11凌晨的时候，成千上万或上亿，一起查询、浏览商品，下单购物。实质上，商品订单等信息是保存到数据库中的，那是不是很多人同时获取数据库连接，同时操作数据（读写），操作完成后关闭连接。要同时支持很多连接并且读写。但是现状我们一台数据库最大连接数是有限的。不能满足很多人同时读写。

解决方案： 集群和分布式

2.海量数据的高效率读写

   网站产生的数据量是巨大的，对于关系型数据库来说，在一张包含海量数据的表中查询，效率是非常低的.例如：商品表
  像天猫、京东等购物商城，某些数据是巨大的，比如说商品，用户等。存放到数据库中一张包含很多数据（上亿条记录）的表，我们查询、插入起来速度会很慢。也就是说海量数据表的的读写速率比较低。

解决方案： 分表、分库

3.高扩展性和可用性

   在基于web的结构（即浏览器/服务器）当中，数据库是最难进行横向扩展的，当一个应用系统的用户量和访问量与日俱增的时候，数据库却没有办法简单的通过添加更多的硬件和服务节点来扩展性能和负载能力。对于很多需要提供24小时不间断服务的网站来说，对数据库系统进行升级和扩展 是非常痛苦的事情，往往需要停机维护和数据迁移.
   动态添加服务器一台数据库服务器扩充到多台时，不下电情况是很难做到的。

单点故障： 一台数据库服务器挂了。业务就中断，期望去找还好的数据库继续提供服务。

二、关系型数据库的优化技术

1.找到执行效率低的Sql(定位慢查询)  —发现问题

原理：
我们使用数据库，实质上就是连接数据库，发送SQL、服务器执行SQL并返回结果、关闭连接。也就是所有的SQL语句MySQL服务器都能感知到，比如执行了那些SQL，都执行了多少时间等。我们做优化是不是就是找出执行时间长的Sql进行优化。问题是： 如何从一个大项目中，迅速的定位执行速度慢的语句. (定位慢查询)

• 所有sql都是mysql执行，当它发现客户端执行sql慢的时候，会把结果记录下来，方便用户查找定定位

  Q : 怎么找到执行较慢的Sql呢？
  
  A : 数据库以慢查询的方式启动（这样的话就可以把慢查询记录到日志文件中了）
  
  步骤一：关闭MySql服务器
  
  步骤二：在mysql的暗转目录bin目录下进入cmd窗口执行以下命令
  
  bin\mysqld.exe --safe-mode --slow-query-log [mysql5.5 可以在my.ini指定]
  
  步骤三：重启MySql服务器
  

查询数据库基本状态（mysql）：

#查看数据库运行时间 单位是秒 指MySql服务器启动了多少秒
show status like 'uptime'

#CRUD执行次数  一般来说 查询操作占八成
show status like '%Com_%'
show status like '%Com_select%' --执行select的次数
show status like '%Com_insert%' --执行insert的次数，批量插入算一次
show status like '%Com_update%' --执行更新操作的次数
show status like '%Com_delete%' --执行删除操作的次数
Show [session|global] status like '%Com_select%' --如果你不写[session|global] 默认是session会话(指取出当前窗口的执行)，如果你想看所有(从mysql启动到现在)，则应该使用global。

#INNODB执行行数
show status like '%Innodb_rows%'
show status like '%innodb_rows_read%' --查询返回的行数
show status like '%innodb_rows_inserted%' --插入成功的行数 
show status like '%innodb_rows_updated%' --更新成功的行数
show status like '%innodb_rows_deleted%' --删除成功的行数

#查询所有连接数  
show status like 'connections' 

#查询表的索引有哪些
show indexes  from emp 

#查看服务器响应的最大连接数 可以作为my.ini中最大连接数配置的依据 通常mysql的最大连接数默认是100, 最大可以达到16384（理论上）
show status like 'Max_used_connections'  

定位慢查询：

#查看当前查询中慢查询有几个  慢查询，通常指花了2S以上的查询(默认10s)
show status like 'slow_queries'

#查询当前系统的慢查询时间
show variables like 'long_query_time' ; 

#修改数据库的慢查询时间
set global long_query_time=1 --直接修改global 的long_query_time 之后在当前的的窗口中是没有效果的，在新打开的窗口中才会有效果。如果想让本窗口也有效果 的话，不用加 global关键字

#把慢查询记录到日志中  mysql5.5 可以在my.ini指定
bin\mysqld.exe --safe-mode --slow-query-log 

• 注意事项：

1. 必须在mysql的安装目录执行，因为执行的使用依赖于my.ini
2. 慢查询日志会输出到data.dir
3. 以下文件不要乱删，乱修改，会导致mysql没法启动
   

• 步骤总结：

1. 关闭原有mysql服务
2. 以支持记录慢sql的方式来启动
3. 设置慢查询时间阀值
4. 构造慢sql
5. 到日志文件中找慢sql（data.dir）

• 什么时候开启慢查询记录日志

1. 开发者自验
2. 测试人员测试
3. 项目上线

2.分析执行较慢的Sql  —分析问题

通过慢启动找到了执行比较慢的Sql,接下来就应该分析这些执行慢的Sql的原因。

通过Sql语句前面加 explain 语句可以分析，mysql如何执行你的sql语句。

列	描述
id	查询序列号
select_type	查询类型
table	显示这一行的数据是关于哪张表
type	显示连接使用了何种类型。从最好到最差的连接类型为 const、eq_reg、ref、range、index、ALL。类型ALL效率最低
possible_keys	显示可能应用在这张表中的索引。如果为空，没有可用的索引。可用为相关的域从WHERE语句中选择一个合适的语句
key	实际使用的索引。如果为NULL，则没有使用索引。很少情况下，MySQL会选择优化不足的索引。这种情况下，可用在SELECT语句中使用USE INDEX 来强制使用一个索引或者用IGNORE INDEX 来强制MysQL忽略索引
key_len	使用的索引长度。在不损失精确性的情况下，长度越短越好
ref	显示索引的哪一列被使用了，如果可能的话是一个常数
rows	MySQL认为必须检查的用来返回请求数据的行数。该sql语句扫描了多少行可能得到记录
Extra	sql语句的额外信息

3.优化Sql —解决问题

三、单机优化

1.表结构设计

1）范式（规范）-常见有三种规范3NF：

• 表的范式，是首先符合1NF, 才能满足2NF , 进一步满足3NF。
• 1NF: 即表的列的具有原子性,不可再分解。即列的信息不能分解，只要数据库是关系型数据库(mysql/oracle/db2/sysbase/sql server)，就自动的满足1NF，关系型数据库中是不允许分割列的。
• 2NF:表中的记录是唯一的，通常我们设计一个主键来实现。
• 3NF:即表中不要有冗余数据, 也就是说表的信息如果能够被推导出来就不应该单独的设计一个字段来存放，通过外键来实现。
• 反3NF:没有冗余的数据库表未必是最好的数据库表，有时为了提高运行效率，就必须降低范式标准，适当保留冗余数据。降低范式就是增加字段，允许冗余。但采用反三范式会涉及到数据更新问题，解决方案有①用redis做作异步刷新②同步。同步的方式又有两种。一种是写两条sql,第二种是用触发器(达到某种条件就触发)

触发器：

#有个文章表  有个文章分类表  当文章表新增数据的时候 文章分类表应该更新总的数量
create table board1(id int primary key auto_increment,name varchar(50),articleCount int);
create table article1(id int primary key auto_increment,title varchar(50),bid int references board1(id));

#创建触发器
create trigger insertArticle_Trigger after insert on article1 for each row 
	begin
		update board1 set articleCount=articleCount+1 where id= NEW.bid;
	end;

2）存储引擎

分类：

• MySQL：myisam、innodb、memory

MyIsam和InNoDB的区别 :

1. 事务安全 MyIsam不支持事务,InnoDB支持事务
2. 外键 MyIsam不支持外键,InnoDB支持外键(通常不设置外键，通常是在程序中保证数据的一致)
3. 锁机制 MyIsam是表锁,InnoDB是行锁
4. 查询和添加速度 相对来说MyIsam的效率比InnoDB效率高
5. 支持全文索引 MyIsam支持全文检索，InnoDB不支持全文检索

特点	MYISAM	InnoDB	BDB	Memory	Archive
批量插入的速度	高	低	高	高	非常高
事务安全		支持	支持
全文索引	支持
锁机制	表锁	行锁	页锁	表锁	行锁
存储限制	没有	64TB	没有	有	没有
B树索引	支持	支持	支持	支持
哈希索引		支持		支持
集群索引		支持
数据缓存		支持		支持
索引缓存	支持	支持		支持
数据可压缩	支持				支持
空间使用	低	高	低	N/A	非常低
内存使用	低	高	低	中等	低
支持外键		支持

使用场景：

• MyISAM存储引擎
  如果表对事务要求不高，同时是以查询和添加为主的，我们考虑使用myisam存储引擎。比如 bbs 中的发帖表、回复表。

• INNODB存储引擎
  对事务要求高，保存的数据都是重要数据，我们建议使用INNODB。比如订单表、账号表。

• Memory 存储引擎
  比如我们数据变化频繁，不需要入库，同时又频繁的查询和修改，我们考虑使用memory, 速度极快。

#指定存储引擎  如果创建表时没有指定使用默认的 这个默认是在my.ini配置  每一张表都可以单独指定存储引擎 
#语法：create table 表名(字段列表) engine 存储引擎名称;
 create table test (id int,name varchar(50));
 create table test1 (id int,name varchar(50)) ENGINE myisam
 
#修改存储引擎
alter table test1 engine = innodb

#查看表是否创建索引  
show indexes from  emp

2.索引

1）什么是数据库索引

• 索引（Index）是帮助DBMS高效获取数据的数据结构。
• 组织数据方式，及访问数据的api的封装。-list，set，map,数组。
• 使用索引把全表查找变为索引查找，减少查询次数，增加查询效率。而索引查找效率的取决于索引算法。也就是索引（Index）是帮助DBMS高效获取数据的数据结构。
• 我理解的数据库索引就是帮助数据库高效获取查询数据的一种数据结构。

2）索引算法

Mysql常用存储引擎	Mysql运行的索引类型
MyISAM	BTREE
InnoDB	BTREE
MEMORY/HEAP	HASH、BTREE

• FullText全文索引算法，myisam,只能能在char vachar text
• hash就像Map,通过一个key直接就能找到value
• B-tree算法
  

3）关于一些索引的命令

# 单列索引   多列索引（复合索引 多个列共同组成）
show indexes  from dept
alter table dept add index my_indx (dname,loc); 

#对于创建的多列索引（复合索引），不是使用的第一部分就不会使用索引。
explain  select * from dept where  dname = 'OUhcNKkXfL'#使用了索引
explain  select * from dept where  loc ='mbvaQcPS'#不会
explain  select * from dept where  dname = 'OUhcNKkXfL' or loc ='mbvaQcPS'#不会 or会导致索引失效
explain  select * from dept where  dname = 'OUhcNKkXfL' and loc ='mbvaQcPS'#会
explain  select * from dept where  loc ='mbvaQcPS' and dname = 'OUhcNKkXfL' #会
explain  select * from dept where  dname like 'OUhcNKkXfL'#会
explain  select * from dept where  dname like '%OUhcNKkXfL%'#不会
explain  select * from dept where  dname like '%OUhcNKkXfL'#不会
explain  select * from dept where  dname like 'OUhcNKkXfL%'#会  like模糊查询前置匹配导致索引失效

4）mysql中索引的分类

• 普通索引：允许重复的值出现，可以在任何字段上面添加。
• 唯一索引：除了不能有重复的记录外，其它和普通索引一样，可以在值是唯一的字段添加（用户名、手机号码、身份证、email，QQ），可以为null,并且可以有多个null。
• 主键索引：是随着设定主键而创建的，也就是把某个列设为主键的时候，数据库就会给该列创建索引。这就是主键索引。唯一且没有null值（也被称为非聚集索引）。
• 全文索引：用来对表中的文本域(char，varchar，text)进行索引， 全文索引针对MyISAM有用。

添加：

• 普通索引:允许重复的值出现
  一般来说，普通索引的创建，是先创建表，然后在创建普通索引

#语法：
create index 索引名 on 表 (列1,列名2,...);
alter table 表名add index 索引名(列1,列名2,..);

#举例：
create table aaa(id int unsigned,name varchar(32));
create index nameIndex on aaa(name);
alter table aaa add index index1(name);

• 唯一索引:除了不能有重复的记录外，其它和普通索引一样

#1.当表的某列被指定为unique约束时，这列就是一个唯一索引
create table bbb(id int primary key auto_increment , name varchar(32) unique); --name 列就是一个唯一索引

#2.在创建表后，再去创建唯一索引
#语法：
create unique index 索引名 on 表名 (列1,列2,..);
alter table 表名add unique index 索引名 (列1,列2,..);

#举例：
create table ccc(id int primary key auto_increment, name varchar(32)); --注意：unique字段可以为NULL,并可以有多NULL, 但是如果是具体内容，则不能重复

• 主键索引：是随着设定主键而创建的，也就是把某个列设为主键的时候，数据库就会给该列创建索引
  唯一且没有null值

#1、创建表时指定主键
create table ddd(id int unsigned primary key auto_increment ,name varchar(32) not null  defaul ‘’); --这时id 列就是主键索引

#2、如果你创建表时，没有指定主键，也可以在创建表后，再添加主键。 
#语法:
alter table 表名 add primary key (列名);

#举例: 
create table eee(id int , name varchar(32) not null default ‘’);
alter table eee add primary key (id);

• 全文索引:用来对表中的文本域(char，varchar，text)进行索引， 全文索引针对MyISAM有用

#1、创建表时定义：
CREATE TABLE articles (
       id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
       title VARCHAR(200),
       body TEXT,
       FULLTEXT (title,body)
     )engine=myisam charset utf8;

INSERT INTO articles (title,body) VALUES
     ('MySQL Tutorial','DBMS stands for DataBase ...'),
     ('How To Use MySQL Well','After you went through a ...'),
     ('Optimizing MySQL','In this tutorial we will show ...'),
     ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
     ('MySQL vs. YourSQL','In the following database comparison ...'),
     ('MySQL Security','When configured properly, MySQL ...');

#2.创建表完成后定义：
create fulltext index 索引名 on 表名(列1,列2);
alter table 表名add fulltext index 索引名 (列1,列2);

#举例: 
CREATE TABLE articles (
  id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
  title VARCHAR(200),
  body TEXT
)engine=myisam charset utf8;

Alter table article add fulltext index title_body_fulltext_index(title,body);

INSERT INTO articles (title,body) VALUES
 ('MySQL Tutorial','DBMS stands for DataBase ...'),
 ('How To Use MySQL Well','After you went through a ...'),
 ('Optimizing MySQL','In this tutorial we will show ...'),
 ('1001 MySQL Tricks','1. Never run mysqld as root. 2. ...'),
 ('MySQL vs. YourSQL','In the following database comparison ...'),
 ('MySQL Security','When configured properly, MySQL ...');

错误用法:

explain select * from articles where body like ‘%mysql%’; --不会使用到全文索引

正确用法：

explain select * from articles where match(title,body) against(‘database’); --会使用全文索引

查询：

show index(es) from 表名

删除：

alter table 表名 drop index 索引名; 
alter table 表名 drop primary key 删除主键。--主键定义为auto_increment时不能删除

修改：

先删除后添加=修改

5）创建索引的原则

• 索引的代价

1. 占用磁盘空间
2. 对dml操作有影响,因为要维护索引，变慢

• 创建索引的原则

1. 较频繁的作为查询条件字段应该创建索引。在where条件经常使用 或者经常用来排序 order by后面的字段
2. 唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件。该字段的内容不是唯一的几个值，比如性别 男女
3. 更新非常频繁的字段不适合创建索引
4. 不会出现在WHERE子句中字段不该创建索引

6）根据索引列的多少分为复合索引和普通索引

• 普通索引（单列索引）：该索引只在一个列上面创建
• 复合索引（多列索引）：该索引只在多个列上面创建

1.对于创建的多列索引（复合索引），不是使用的第一部分就不会使用索引。

alter table dept add index my_indx (dname,loc); --dname 左边的列,loc就是 右边的列
explain select * from dept where dname='aaa'; --会使用到索引
explain select * from dept where loc='aaa'; --不会使用到索引

2.对于使用like的查询，查询如果是%aaa’不会使用到索引而‘aaa%’会使用到索引。

#在like查询时，‘关键字’的最前面不能使用 % 或者 _这样的字符.，如果一定要前面有变化的值，则考虑使用 全文索引->sphinx
explain select * from dept where dname like '%aaa'; --不能使用索引
explain select * from dept where dname like 'aaa%'; --使用索引

3.如果条件中有or，有条件没有使用索引,即使其中有条件带索引也不会使用。换言之，就是要求使用的所有字段,都必须单独使用时能使用索引。

explain select * from dept where dname = 'aaa'; --使用索引
explain select * from dept where loc = 'aaa'; --使用索引
explain select * from dept where dname=’xxx’ or loc=’xx’; --不能使用索引

4.如果列类型是字符串，那一定要在条件中将数据使用引号引用起来。否则不使用索引。

#如果列是字符串类型，无论是不是字符串数字就一定要用'' 把它包括起来.
expain select * from dept where dname=’111’;
expain select * from dept where dname=111; --数值自动转字符串
expain select * from dept where dname=qqq; --报错

5.如果mysql估计使用全表扫描要比使用索引快，则不使用索引。比如表里面只有一条记录。

7）索引失效

• like 模糊查询前置匹配
• or连接索引失效
• 不等于操作 ！=
• is null判断
• in或者not in

3.分表

1）水平分表

  前提：单表数据量较大时可以采用水平分表的策略，提交查询效率。
  如果一个表的记录数太多了，比如上千万条，而且需要经常检索，那么我们就有必要化整为零了。如果我拆成100个表，那么每个表只有10万条记录。
  当然这需要数据在逻辑上可以划分。一个好的划分依据，有利于程序的简单实现，也可以充分利用水平分表的优势。比如系统界面上只提供按月查询的功能，那么把表按月拆分成12个，每个查询只查询一个表就够了。如果非要按照地域来分，即使把表拆的再小，查询还是要联合所有表来查，还不如不拆了。
  所以一个好的拆分依据是最重要的。可以按照合适的策略拆分为多张小表。尽量在单张表中查询，减少扫描次数，增加查询效率。如果拆分不好，经常组合（union）查询，还不如不拆分

分表策略:
1.按时间分表
  这种分表方式有一定的局限性，当数据有较强的实效性，如微博发送记录、微信消息记录等，这种数据很少有用户会查询几个月前的数据，如就可以按月分表。
2.按区间范围分表
  一般在有严格的自增id需求上，如按照user_id水平分表

table_1  user_id从1~100w 
table_2  user_id从100W+1~200w 
table_3  user_id从200W+1~300w 

3.hash分表
  通过一个原始目标的ID或者名称通过一定的hash算法计算出数据存储表的表名，然后访问相应的表。
  生成id的方式：主键自增（java）、uuid、雪花算法（分布式主键）
  最简单hash算法： T_user + Id%100+1

分表后可能涉及跨表查询：

#union 和 union all 的区别：union会去掉两张表重复的数据,union all不会去重 效率更快
select *  from dept where deptno  between 101 and 105 
union all
select *  from dept where deptno  between 106 and 110

2）垂直分表

  有些表记录数并不多，可能也就2、3万条，但是字段却很长，表占用空间很大，检索表时需要执行大量I/O，严重降低了性能。这个时候需要把大的字段拆分到另一个表，并且该表与原表是一对一的关系（外键）。
  如果一张表某个字段，信息量大，但是我们很少查询，则可以考虑把这些字段，单独的放入到一张表中。如果非要查询，就是用跨表查询(join)

4.分区（了解）

1）什么是分区

• 不同在于分表是将大表分解为若干个独立的实体表，而分区是将数据分段划分在多个位置存放，可以是同一块磁盘也可以在不同的机器。分区后，表面上还是一张表，但数据散列到多个位置了。app读写的时候操作的还是大表名字，db自动去组织分区的数据。

#查看数据库是否支持
SHOW VARIABLES LIKE '%partition%';

2）分区作用

• 与单个磁盘或文件系统分区相比，可以存储更多的数据
• 很容易就能删除不用或者过时的数据
• 一些查询可以得到极大的优化 可以并发查询
• 涉及到 SUM()/COUNT() 等聚合函数时，可以并发进行
• IO吞吐量更大（多台服务器）

3）分区方式

• Range（范围）： 基于一个给定的连续空间，把数据分配到不同分区。1-10 11-20
• List（预定义列表）： 类似Range分区，区别在List分区是基于枚举出的值列表分区，而Range分区是根据给定的连续区间范围分区。3, 5, 6, 9, 17
• Hash（哈希）：允许通过对表的一个或多个列的Hash Key进行计算，最后通过这个Hash码不同数值对应的数据区域进行分区。例如：可以建立一个对表主键进行分区的表。这个根据给定的分区个数，把数据分配到不同的分区。
• Key（键值）： 上面Hash模式的一种延伸，这里的Hash Key是MySQL系统产生的。
• Composite（复合模式）： 以上模式的组合使用

RANGE分区：

• 基于属于一个给定连续区间的列值，把多行分配给分区。这些区间要连续且不能相互重叠，使用VALUES LESS THAN操作符来进行定义。

CREATE TABLE employees (
    id INT NOT NULL,
    name VARCHAR(30),
    hired DATE NOT NULL DEFAULT '2015-12-01',
    job  VARCHAR(30) NOT NULL,
    dept_id INT NOT NULL
) engine myisam 
partition BY RANGE (dept_id) (
    partition p0 VALUES LESS THAN (6),1-5
    partition p1 VALUES LESS THAN (11),6-10
    partition p2 VALUES LESS THAN (16),11-15
    partition p3 VALUES LESS THAN (21)
);

• RANGE分区在如下场合特别有用：

1. 当需要删除一个分区上的“旧的”数据时,只删除分区即可。如果你使用上面最近的那个例子给出的分区方案，你只需简单地使用 “ALTER TABLE employees DROP PARTITION p0；”来删除所有在1991年前就已经停止工作的雇员相对应的所有行。对于有大量行的表，这比运行一个如“DELETE FROM employees WHERE YEAR (separated) <= 1990；”这样的一个DELETE查询要有效得多。
2. 想要使用一个包含有日期或时间值，或包含有从一些其他级数开始增长的值的列。
3. 经常运行直接依赖于用于分割表的列的查询。例如，当执行一个如“SELECT COUNT(*) FROM employees WHERE YEAR(separated) = 2000 GROUP BY dept_id；”这样的查询时，MySQL可以很迅速地确定只有分区p2需要扫描，这是因为余下的分区不可能包含有符合该WHERE子句的任何记录。

LIST分区：

• 类似于按RANGE分区，区别在于LIST分区是基于列值匹配一个离散值集合中的某个值来进行选择。
• LIST分区通过使用“PARTITION BY LIST(expr)”来实现，其中“expr” 是某列值或一个基于某个列值、并返回一个整数值的表达式，然后通过“VALUES IN (value_list)”的方式来定义每个分区，其中“value_list”是一个通过逗号分隔的整数列表。

假设：
在成都开了有20个咖啡店，分布在5个有经销权的地区，如下表所示：
====================
地区      商店ID 号
------------------------------------
青羊区      3, 5, 6, 17
金牛区      1, 10, 11, 19
成华区      4, 12, 14, 18
锦江区      2, 9, 13, 16
高薪区      7, 8, 15, 20

CREATE TABLE employees (
    id INT NOT NULL,
    name VARCHAR(30),
    hired DATE NOT NULL DEFAULT '2015-12-10',
    job_code INT,
    store_id INT
)
PARTITION BY LIST(store_id)(
    PARTITION pQY VALUES IN (3, 5, 6, 17),
    PARTITION pJN VALUES IN (1, 10, 11, 19),
    PARTITION pCH VALUES IN (4, 12, 14, 18),
    PARTITION pJJ VALUES IN (2, 9, 13, 16),
    PARTITION pGX VALUES IN ( 7, 8, 15, 20)
);

这使得在表中增加或删除指定地区的雇员记录变得容易起来。例如，假定成华区的所有咖啡店都卖给了其他公司。
那么与在成华区咖啡店工作雇员相关的所有记录（行）可以使用查询“ALTER TABLE employees DROP PARTITION pCH；”来进行删除，
它与具有同样作用的DELETE （删除）查询“DELETE query DELETE FROM employees WHERE store_id IN (4,12,13,14,18)；”比起来，要有效得多。

HASH分区 ：

• 基于用户定义的表达式的返回值来进行选择的分区，该表达式使用将要插入到表中的这些行的列值进行计算。这个函数可以包含MySQL 中有效的、产生非负整数值的任何表达式。
• 主要用来分散热点读，确保数据在预先确定个数的分区中尽可能平均分布。

CREATE TABLE employees (
    id INT NOT NULL,
    name VARCHAR(30),
    hired DATE NOT NULL DEFAULT '2015-12-10',
    job_code INT,
    store_id INT
)
PARTITION BY HASH(store_id)
PARTITIONS 4

MySQL还支持"线性哈希"功能，它与常规哈希的区别在于，
	常规哈希使用的是求哈希函数值的模数。缺点在于分区增加时的重新计算。
	而线性哈希功能使用的一个线性的2的幂（powers-of-two）运算法则。

区别:
线性哈希分区和常规哈希分区在语法上的唯一区别在于，在“PARTITION BY” 子句中添加“LINEAR”关键字。
PARTITION BY LINEAR HASH(YEAR(hired))

线性哈希分区的优点在于增加、删除、合并和拆分分区将变得更加快捷，有利于处理含有极其大量数据的表。它的缺点在于，与使用
常规HASH分区得到的数据分布相比，各个分区间数据的分布不大可能均衡。

KSY分区：

• 类似于按HASH分区，区别在于KEY分区只支持计算一列或多列，且MySQL 服务器提供其自身的哈希函数。(表达式已经确定)必须有一列或多列包含整数值。

CREATE TABLE employees (
    id INT NOT NULL,
    name VARCHAR(30),
    hired DATE NOT NULL DEFAULT '2015-12-01',
    job  VARCHAR(30) NOT NULL,
   dept_id INT NOT NULL
)
partition BY KEY (job) 
PARTITIONS 3;

与key不同在于key的值可以为空，但是要注意：
1） 可以不指定值，默认以主键为准
2） 如果没主键，以唯一键为准
3） 如果没主键，以唯一键为准，唯一键必须非空
4） 无主无唯一，就必须手动指定

在KEY分区中使用关键字LINEAR和在HASH分区中使用具有同样的作用，分区的编号是通过2的幂（powers-of-two）算法得到，而不是通过模数算法。
分区空值处理：
1、range方式，默认放入最小值分区
2、list方式，必须指定null值匹配
3、hash方式，默认当成0

四、SQL优化

1. 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。
2. 应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
3. 应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：

select id from t where num is null

可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：

select id from t where num=0

4. 应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：

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. 下面的查询也将导致全表扫描：

select id from t where name like '%abc%'

若要提高效率，可以考虑全文检索。

6. in 和 not in 也要慎用，否则会导致全表扫描，如：

select id from t where num in(1,2,3)

对于连续的数值，能用 between 就不要用 in 了。

select id from t where num between 1 and 3

7. 如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：

select id from t where num=@num

可以改为强制查询使用索引。

select id from t with(index(索引名)) where num=@num

8. 应尽量避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where num/2=100

应改为:

select id from t where num=100*2

9. 应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：

select id from t where substring(name,1,3)='abc' --name以abc开头的id

select id from t where datediff(day,createdate,'2005-11-30')=0 --'2005-11-30'生成的id

应改为:

select id from t where name like 'abc%'

select id from t where createdate>='2005-11-30' and createdate<'2005-12-1'

10. 不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。
11. 在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。
12. 不要写一些没有意义的查询，如需要生成一个空表结构：

select col1,col2 into t1 from t where 1=0

这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：

create table t1(...)

13. 很多时候用 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)

14. 并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。
15. 索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。
16. 应尽可能的避免更新 clustered 索引数据列，因为 clustered 索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源。若应用系统需要频繁更新 clustered 索引数据列，那么需要考虑是否应将该索引建为 clustered 索引。
17. 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
18. 尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
19. 任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。
20. 尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。
21. 避免频繁创建和删除临时表，以减少系统表资源的消耗。
22. 临时表并不是不可使用，适当地使用它们可以使某些例程更有效，例如，当需要重复引用大型表或常用表中的某个数据集时。但是，对于一次性事件，最好使用导出表。
23. 在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。
24. 如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。
25. 尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。
26. 使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。
27. 与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。
28. 在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE_IN_PROC 消息。
29. 尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。
30. 尽量避免大事务操作，提高系统并发能力。

五、DDL、DML、DQL优化

• ddl ：define 创建 修改 删除Drop
• dml ：manage 增加 insert 修改update 删除
• dql ：query select 查询

1.DDL优化

1 、通过禁用索引来提供导入数据性能 。 这个操作主要针对有数据库的表，追加数据

• 变多次索引维护为一次索引维护

#去除键
alter table test3 DISABLE keys;
#批量插入数据
insert into test3 select * from test;
#恢复键
alter table test3 ENABLE keys;

2、 关闭唯一校验

• 变多次唯一校验为一次唯一校验

#关闭
set unique_checks=0 
#批量插入数据
insert into test3 select * from test;
#开启
set unique_checks=1 

3、修改事务提交方式(导入)

• 变多次事务提交为一次事务提交

#关闭
set autocommit=0 
#批量插入
insert into test3 select * from test;
#开启
set autocommit=1 

2.DML优化

• 变多次事务提交为一次事务提交

insert into test values(1,2);
insert into test values(1,3);
insert into test values(1,4);
//合并多条为一条
insert into test values(1,2),(1,3),(1,4)

3.DQL优化

1）order by优化

• 多用索引排序
• 普通结果排序（非索引排序）Filesort
• 索引本身就是排序的，所以多使用索引

2）group by优化

• 在group by是时使用order by null,取消默认排序

#查询某个时间的付款总和
explain
select DATE_FORMAT(payment_date,'%Y-%m'),sum(amount) from payment GROUP BY DATE_FORMAT(payment_date,'%Y-%m') ;

explain
select DATE_FORMAT(payment_date,'%Y-%m'),sum(amount) from payment GROUP BY DATE_FORMAT(payment_date,'%Y-%m') order by null;

3）subQuery嵌套优化

#在客户列表找到不在“支付列表”的客户 , 查询没买过东西的客户
explain
select * from customer where customer_id not in (select DISTINCT customer_id from payment); --子查询 这种是基于func外链

explain
select * from customer c left join payment p on(c.customer_id=p.customer_id) where p.customer_id is null --这种是基于“索引”外链

4）or优化

• or两边都是用索引字段做判断，性能好
• or两边，有一边不用，性能差
• 如果employee表的name和email这两列是一个复合索引，但是如果是 :name=‘A’ OR email=‘B’ 这种方式，不会用到索引

5）limit优化 在分页时使用一个有索引的字段排序

select film_id,description from film order by title limit 50,5;

select a.film_id,a.description from filqm a inner join (select film_id from film order by title limit 50,5)b on a.film_id=b.film_id