mysql常见知识点总结

待我称王封你为后i 2021-09-20 18:38 1407阅读 0赞

## **微信搜索：“二十同学” 公众号，欢迎关注一条不一样的成长之路** ##

# MySql的优化步骤 #

MySql优化的一般步骤：

**1.通过show status 命令了解各种sql的执行效率**

SHOW STATUS提供msyql服务器的状态信息

一般情况下，我们只需要了解以”Com”开头的指令

show session status like ‘Com%’：显示当前的连接的统计结果

show global status like ‘Com%’ ：显示自数据库上次启动至今的统计结果

注：默认是session级别的

其中Com\_XXX表示XXX语句所执行的次数。  
重点注意：Com\_select,Com\_insert,Com\_update,Com\_delete通过这几个参数，可以容易地了解到当前数据库的应用是以插入更新为主还是以查询操作为主，以及各类的SQL大致的执行比例是多少。

另外，还有几个参数需要注意下：

show status like ‘Connections’// 试图连接MySQL服务器的次数

show status like ‘Uptime’//服务器工作的时间（单位秒）

show status like ‘Slow\_queries’//慢查询的次数 (默认是10秒中就当做是慢查询，如下图所示)

![format_png][]

a) 如何查询mysql的慢查询时间

Show variables like 'long\_query\_time';

b) 修改mysql 慢查询时间

set long\_query\_time=2//如果查询时间超过2秒就算作是慢查询

**2. 定位执行效率较低的SQL语句（dql出现问题的概率较dml的大）**

问题是：如何在一个项目中，找到慢查询的select语句？

答案：mysql支持把慢查询语句记录到日志文件中。程序员需要修改php.ini的配置文件，默认情况下，慢查询记录是不开启的。

开启慢查询记录的步骤：

打开 my.ini ,找到 \[mysqld\] 在其下面添加

long\_query\_time = 2

log-slow-queries = D:/mysql/logs/slow.log \#设置把日志写在那里，可以为空，系统会给一个缺省的文件

例子：我们数据表中有1千万条的数据量

DQL语句：SELECT \* FROM order\_copy WHERE id=12345;

![format_png 1][]

查询耗时：19s>2s，所以mysql会将该条select语句记录到慢查询日志中

SELECT \* FROM order\_copy WHERE id=12345的执行时间：

**添加索引前：19s**

**　　添加索引后：0.08s**

**3.通过explain分析低效率的SQL语句的执行情况**

使用explain分析该dql语句：

EXPLAIN SELECT \* FROM order\_copy WHERE id=12345  
会产生如下信息：  
select\_type:表示查询的类型。  
table:输出结果集的表  
type:表示表的连接类型(system和const为佳)  
possible\_keys:表示查询时，可能使用的索引  
key:表示实际使用的索引  
key\_len:索引字段的长度  
rows:扫描的行数  
Extra:执行情况的描述和说明

**注意：要尽量避免让type的结果为all，extra的结果为：using filesort**

![format_png 2][]

**4.确定问题并采取相应的优化措施**

*  常用的优化措施是添加索引。添加索引，我们不用加内存，不用改程序，不用调sql，只要执行个正确的’create index’，查询速度就可能提高百倍千倍。但是天下没有免费的午餐，查询速度的提高是以插入、更新、删除的速度为代价的，这些写操作，增加了大量的I/O。

例如：给字段id添加索引：

ALTER TABLE order\_copy ADD PRIMARY KEY(id)

给1千万的数据添加primary key 需要耗时： 428秒（7分钟）

EXPLAIN SELECT \* FROM order\_copy WHERE id=12345

![format_png 3][]

正是因为给id添加了索引，才使得rows的结果为1

**但是索引并不是可以随便添加的，以下几种情况需牢记在心：**

*  较频繁的作为查询条件字段应该创建索引

select \* from order\_copy where id = $id

*  唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件

select \* from order\_copy where sex=’女’

*  更新非常频繁的字段不适合创建索引

select \* from order\_copy where order\_state=’未付款’

*  不会出现在WHERE子句中字段不该创建索引

**索引的类型：**

*  PRIMARY 索引      => 在主键上自动创建
 *  INDEX 索引          => 就是普通索引
 *  UNIQUE 索引        => 相当于INDEX + Unique
 *  FULLTEXT            => 只在MYISAM 存储引擎支持, 目的是全文索引，在内容系统中用的多， 在全英文网站用多(英文词独立). 中文数据不常用，意义不大 国内全文索引通常 使用 sphinx 来完成.

**索引的使用**

*  建立索引 create \[UNIQUE|FULLTEXT\]  index index\_name on tbl\_name (*col\_name* \[(*length*)\] \[ASC | DESC\] , …..);  
    alter table table\_name ADD INDEX \[*index\_name*\] (*index\_col\_name*,...)

添加主键(索引) ALTER TABLE 表名 ADD PRIMARY KEY(列名,..); 联合主键

*  删除索引 DROP INDEX *index\_name* ON *tbl\_name*;  
    alter table table\_name drop index index\_name;
 *  删除主键(索引)比较特别: alter table t\_b drop primary key;
 *  查询索引(均可) show index from table\_name;  
    show keys from table\_name;  
    desc table\_Name;

相关资料：

explain的使用:[http://www.cnblogs.com/you-yang/archive/2012/01/18/2325841.html][http_www.cnblogs.com_you-yang_archive_2012_01_18_2325841.html]

# mysql优化策略 #

一个成熟的数据库架构并不是一开始设计就具备高可用、高伸缩等特性的，它是随着用户量的增加，基础架构才逐渐完善。暂且抛开前端应用不说，大致分为以下五个阶段：

1、数据库表设计

项目立项后，开发部根据产品部需求开发项目，开发工程师工作其中一部分就是对表结构设计。对于数据库来说，这点很重要，如果设计不当，会直接影响访问速度和用户体验。影响的因素很多，比如慢查询、低效的查询语句、没有适当建立索引、数据库堵塞（死锁）等。当然，有测试工程师的团队，会做压力测试，找bug。对于没有测试工程师的团队来说，大多数开发工程师初期不会太多考虑数据库设计是否合理，而是尽快完成功能实现和交付，等项目有一定访问量后，隐藏的问题就会暴露，这时再去修改就不是这么容易的事了。

2、数据库部署

该运维工程师出场了，项目初期访问量不会很大，所以单台部署足以应对在1500左右的QPS（每秒查询率）。考虑到高可用性，可采用MySQL主从复制+Keepalived做双击热备，常见集群软件有Keepalived、Heartbeat。

3、数据库性能优化

如果将MySQL部署到普通的X86服务器上，在不经过任何优化情况下，MySQL理论值正常可以处理2000左右QPS，经过优化后，有可能会提升到2500左右QPS，否则，访问量当达到1500左右并发连接时，数据库处理性能就会变慢，而且硬件资源还很富裕，这时就该考虑软件问题了。那么怎样让数据库最大化发挥性能呢？一方面可以单台运行多个MySQL实例让服务器性能发挥到最大化，另一方面是对数据库进行优化，往往操作系统和数据库默认配置都比较保守，会对数据库发挥有一定限制，可对这些配置进行适当的调整，尽可能的处理更多连接数。

具体优化有以下三个层面

3.1 数据库配置优化

MySQL常用有两种存储引擎，一个是MyISAM，不支持事务处理，读性能处理快，表级别锁。另一个是InnoDB，支持事务处理（ACID），设计目标是为处理大容量数据发挥最大化性能，行级别锁。

表锁：开销小，锁定粒度大，发生死锁概率高，相对并发也低。   
行锁：开销大，锁定粒度小，发生死锁概率低，相对并发也高。

为什么会出现表锁和行锁呢？主要是为了保证数据的完整性，举个例子，一个用户在操作一张表，其他用户也想操作这张表，那么就要等第一个用户操作完，其他用户才能操作，表锁和行锁就是这个作用。否则多个用户同时操作一张表，肯定会数据产生冲突或者异常。

根据以上看来，使用InnoDB存储引擎是最好的选择，也是MySQL5.5以后版本中默认存储引擎。每个存储引擎相关联参数比较多，以下列出主要影响数据库性能的参数。

公共参数默认值：

MyISAM参数默认值：

key\_buffer\_size = 16M   
索引缓存区大小，一般设置物理内存的30-40%

read\_buffer\_size = 128K   
读操作缓冲区大小，推荐设置16M或32M

InnoDB参数默认值：

innodb\_buffer\_pool\_size = 128M   
索引和数据缓冲区大小，一般设置物理内存的60%-70%

innodb\_buffer\_pool\_instances = 1   
缓冲池实例个数，推荐设置4个或8个

innodb\_flush\_log\_at\_trx\_commit = 1   
关键参数，0代表大约每秒写入到日志并同步到磁盘，数据库故障会丢失1秒左右事务数据。1为每执行一条SQL后写入到日志并同步到磁盘，I/O开销大，执行完SQL要等待日志读写，效率低。2代表只把日志写入到系统缓存区，再每秒同步到磁盘，效率很高，如果服务器故障，才会丢失事务数据。对数据安全性要求不是很高的推荐设置2，性能高，修改后效果明显。

innodb\_file\_per\_table = OFF   
默认是共享表空间，共享表空间idbdata文件不断增大，影响一定的I/O性能。推荐开启独立表空间模式，每个表的索引和数据都存在自己独立的表空间中，可以实现单表在不同数据库中移动。

innodb\_log\_buffer\_size = 8M   
日志缓冲区大小，由于日志最长每秒钟刷新一次，所以一般不用超过16M

3.2 系统内核优化

大多数MySQL都部署在Linux系统上，所以操作系统的一些参数也会影响到MySQL性能，以下对linux内核进行适当优化。

在linux系统中，如果进程打开的文件句柄数量超过系统默认值1024，就会提示“too many files open”信息，所以要调整打开文件句柄限制。

3.3 硬件配置

加大物理内存，提高文件系统性能。linux内核会从内存中分配出缓存区（系统缓存和数据缓存）来存放热数据，通过文件系统延迟写入机制，等满足条件时（如缓存区大小到达一定百分比或者执行sync命令）才会同步到磁盘。也就是说物理内存越大，分配缓存区越大，缓存数据越多。当然，服务器故障会丢失一定的缓存数据。

SSD硬盘代替SAS硬盘，将RAID级别调整为RAID1+0，相对于RAID1和RAID5有更好的读写性能（IOPS），毕竟数据库的压力主要来自磁盘I/O方面。

4、数据库架构扩展

随着业务量越来越大，单台数据库服务器性能已无法满足业务需求，该考虑加机器了，该做集群了~~~。主要思想是分解单台数据库负载，突破磁盘I/O性能，热数据存放缓存中，降低磁盘I/O访问频率。

4.1 主从复制与读写分离

因为生产环境中，数据库大多都是读操作，所以部署一主多从架构，主数据库负责写操作，并做双击热备，多台从数据库做负载均衡，负责读操作，主流的负载均衡器有LVS、HAProxy、Nginx。怎么来实现读写分离呢？大多数企业是在代码层面实现读写分离，效率比较高。另一个种方式通过代理程序实现读写分离，企业中应用较少，常见代理程序有MySQL Proxy、Amoeba。在这样数据库集群架构中，大大增加数据库高并发能力，解决单台性能瓶颈问题。如果从数据库一台从库能处理2000 QPS，那么5台就能处理1w QPS，数据库横向扩展性也很容易。

有时，面对大量写操作的应用时，单台写性能达不到业务需求。如果做双主，就会遇到数据库数据不一致现象，产生这个原因是在应用程序不同的用户会有可能操作两台数据库，同时的更新操作造成两台数据库数据库数据发生冲突或者不一致。在单库时MySQL利用存储引擎机制表锁和行锁来保证数据完整性，怎样在多台主库时解决这个问题呢？有一套基于perl语言开发的主从复制管理工具，叫MySQL-MMM（Master-Master replication managerfor Mysql，Mysql主主复制管理器），这个工具最大的优点是在同一时间只提供一台数据库写操作，有效保证数据一致性。

4.2 增加缓存

给数据库增加缓存系统，把热数据缓存到内存中，如果内存缓存中有要请求的数据就不再去数据库中返回结果，提高读性能。缓存实现有本地缓存和分布式缓存，本地缓存是将数据缓存到本地服务器内存中或者文件中，速度快。分布式可以缓存海量数据，扩展容易，主流的分布式缓存系统有memcached、Redis，memcached性能稳定，数据缓存在内存中，速度很快，QPS可达8w左右。如果想数据持久化那就用redis，性能不低于memcached。

工作过程：

4.3 分库

分库是根据业务不同把相关的表切分到不同的数据库中，比如web、bbs、blog等库。如果业务量很大，还可将切分后的库做主从架构，进一步避免单个库压力过大。

4.4 分表

数据量的日剧增加，数据库中某个表有几百万条数据，导致查询和插入耗时太长，怎么能解决单表压力呢？你就该考虑是否把这个表拆分成多个小表，来减轻单个表的压力，提高处理效率，此方式称为分表。

分表技术比较麻烦，要修改程序代码里的SQL语句，还要手动去创建其他表，也可以用merge存储引擎实现分表，相对简单许多。分表后，程序是对一个总表进行操作，这个总表不存放数据，只有一些分表的关系，以及更新数据的方式，总表会根据不同的查询，将压力分到不同的小表上，因此提高并发能力和磁盘I/O性能。

分表分为垂直拆分和水平拆分：

垂直拆分：把原来的一个很多字段的表拆分多个表，解决表的宽度问题。你可以把不常用的字段单独放到一个表中，也可以把大字段独立放一个表中，或者把关联密切的字段放一个表中。

水平拆分：把原来一个表拆分成多个表，每个表的结构都一样，解决单表数据量大的问题。

4.5 分区

分区就是把一张表的数据分成多个区块，这些区块可以在一个磁盘上，也可以在不同的磁盘上，分区后，表面上还是一张表，但数据散列在多个位置，这样一来，多块硬盘同时处理不同的请求，从而提高磁盘I/O读写性能，实现比较简单。

注：增加缓存、分库、分表和分区主要由程序猿来实现。

5、数据库维护

数据库维护是运维工程师或者DBA主要工作，包括性能监控、性能分析、性能调优、数据库备份和恢复等。

5.1 性能状态关键指标

QPS，Queries Per Second：每秒查询数，一台数据库每秒能够处理的查询次数

TPS，Transactions Per Second：每秒处理事务数

通过show status查看运行状态，会有300多条状态信息记录，其中有几个值帮可以我们计算出QPS和TPS，如下：

Uptime：服务器已经运行的实际，单位秒   
Questions：已经发送给数据库查询数   
Com\_select：查询次数，实际操作数据库的   
Com\_insert：插入次数   
Com\_delete：删除次数   
Com\_update：更新次数   
Com\_commit：事务次数   
Com\_rollback：回滚次数

那么，计算方法来了，基于Questions计算出QPS：

mysql> show global status like 'Questions';   
mysql> show global status like 'Uptime';

QPS = Questions / Uptime   
基于Com\_commit和Com\_rollback计算出TPS：

mysql> show global status like 'Com\_commit';   
mysql> show global status like 'Com\_rollback';   
mysql> show global status like 'Uptime';   
TPS = (Com\_commit + Com\_rollback) / Uptime

另一计算方式：基于Com\_select、Com\_insert、Com\_delete、Com\_update计算出QPS

mysql> show global status where Variable\_name in('com\_select','com\_insert','com\_delete','com\_update');

等待1秒再执行，获取间隔差值，第二次每个变量值减去第一次对应的变量值，就是QPSSTPS计算方法：

mysql> show global status where Variable\_name in('com\_insert','com\_delete','com\_update');

计算TPS，就不算查询操作了，计算出插入、删除、更新四个值即可。

经网友对这两个计算方式的测试得出，当数据库中myisam表比较多时，使用Questions计算比较准确。当数据库中innodb表比较多时，则以Com\_\*计算比较准确。

5.2 开启慢查询日志

MySQL开启慢查询日志，分析出哪条SQL语句比较慢，使用set设置变量，重启服务失效，可以在my.cnf添加参数永久生效。

分析慢查询日志，可以使用MySQL自带的mysqldumpslow工具，分析的日志较为简单。

mysqldumpslow -t 3 /var/log/mysql/mysql-slow.log \#查看最慢的前三个查询

也可以使用percona公司的pt-query-digest工具，日志分析功能全面，可分析slow log、binlog、general log。

分析慢查询日志：pt-query-digest /var/log/mysql/mysql-slow.log

分析binlog日志：mysqlbinlog mysql-bin.000001 >mysql-bin.000001.sql

pt-query-digest –type=binlog mysql-bin.000001.sql

分析普通日志：pt-query-digest –type=genlog localhost.log

5.3 数据库备份

备份数据库是最基本的工作，也是最重要的，否则后果很严重，你懂得！但由于数据库比较大，上百G，往往备份都很耗费时间，所以就该选择一个效率高的备份策略，对于数据量大的数据库，一般都采用增量备份。常用的备份工具有mysqldump、mysqlhotcopy、xtrabackup等，mysqldump比较适用于小的数据库，因为是逻辑备份，所以备份和恢复耗时都比较长。mysqlhotcopy和xtrabackup是物理备份，备份和恢复速度快，不影响数据库服务情况下进行热拷贝，建议使用xtrabackup，支持增量备份。

Xtrabackup备份工具使用博文：[http://lizhenliang.blog.51cto.com/7876557/1612800][http_lizhenliang.blog.51cto.com_7876557_1612800]

5.4 数据库修复

有时候MySQL服务器突然断电、异常关闭，会导致表损坏，无法读取表数据。这时就可以用到MySQL自带的两个工具进行修复，myisamchk和mysqlcheck。

myisamchk：只能修复myisam表，需要停止数据库

常用参数：

\-f –force 强制修复，覆盖老的临时文件，一般不使用   
\-r –recover 恢复模式   
\-q –quik 快速恢复   
\-a –analyze 分析表   
\-o –safe-recover 老的恢复模式，如果-r无法修复，可以使用此参数试试   
\-F –fast 只检查没有正常关闭的表

快速修复weibo数据库:   
cd /var/lib/mysql/weibo   
myisamchk -r -q \*.MYI

mysqlcheck：myisam和innodb表都可以用，不需要停止数据库，如修复单个表，可在数据库后面添加表名，以空格分割

常用参数：

\-a –all-databases 检查所有的库   
\-r –repair 修复表   
\-c –check 检查表，默认选项   
\-a –analyze 分析表   
\-o –optimize 优化表   
\-q –quik 最快检查或修复表   
\-F –fast 只检查没有正常关闭的表   
快速修复weibo数据库:   
mysqlcheck -r -q -uroot -p123 weibo

5.5 另外，查看CPU和I/O性能方法

查看CPU性能   
参数-P是显示CPU数，ALL为所有，也可以只显示第几颗   
查看I/O性能   
参数-m是以M单位显示，默认K   
%util：当达到100%时，说明I/O很忙。   
await：请求在队列中等待时间，直接影响read时间。   
I/O极限：IOPS（r/s+w/s）,一般在1200左右。（IOPS，每秒进行读写（I/O）操作次数）   
I/O带宽：在顺序读写模式下SAS硬盘理论值在300M/s左右，SSD硬盘理论值在600M/s左右。

以上是本人使用MySQL三年来总结的一些主要优化方案，能力有限，有些不太全面，但这些基本能够满足中小型企业数据库需求。由于关系型数据库初衷设计限制，一些BAT公司海量数据放到关系型数据库中，在海量数据查询和分析方面已经达不到更好的性能。因此NoSQL火起来了，非关系型数据库，大数据量，具有高性能，同时也弥补了关系型数据库某方面不足，渐渐大多数公司已经将部分业务数据库存放到NoSQL中，如MongoDB、Hbase等。数据存储方面采用分布式文件系统，如HDFS、GFS等。海量数据计算分析采用Hadoop、Spark、Storm等。这些都是与运维相关的前沿技术，也是在存储方面主要学习对象，小伙伴们共同加油吧！哪位博友有更好的优化方案，欢迎交流哦。

# MySQL性能优化总结 #

**一、MySQL****的主要适用场景**

1、Web网站系统

2、日志记录系统

3、数据仓库系统

4、嵌入式系统

**二、****MySQL****架构图：**

**![format_png 4][]**

**三、****MySQL****存储引擎概述**

**1****）****MyISAM****存储引擎**

MyISAM存储引擎的表在数据库中，每一个表都被存放为三个以表名命名的物理文件。首先肯定会有任何存储引擎都不可缺少的存放表结构定义信息的.frm文件，另外还有.MYD和.MYI文件，分别存放了表的数据（.MYD）和索引数据（.MYI）。每个表都有且仅有这样三个文件做为MyISAM存储类型的表的存储，也就是说不管这个表有多少个索引，都是存放在同一个.MYI文件中。

MyISAM支持以下三种类型的索引：

1、B-Tree索引

B-Tree索引，顾名思义，就是所有的索引节点都按照balancetree的数据结构来存储，所有的索引数据节点都在叶节点。

2、R-Tree索引

R-Tree索引的存储方式和b-tree索引有一些区别，主要设计用于为存储空间和多维数据的字段做索引，所以目前的MySQL版本来说，也仅支持geometry类型的字段作索引。

3、Full-text索引

Full-text索引就是我们长说的全文索引，他的存储结构也是b-tree。主要是为了解决在我们需要用like查询的低效问题。

**2****）****Innodb ****存储引擎**

1、支持事务安装

2、数据多版本读取

3、锁定机制的改进

4、实现外键

**3****）****NDBCluster****存储引擎**

NDB存储引擎也叫NDBCluster存储引擎，主要用于MySQLCluster分布式集群环境，Cluster是MySQL从5.0版本才开始提供的新功能。

**4****）****Merge****存储引擎**

MERGE存储引擎，在MySQL用户手册中也提到了，也被大家认识为MRG\_MyISAM引擎。Why？因为MERGE存储引擎可以简单的理解为其功能就是实现了对结构相同的MyISAM表，通过一些特殊的包装对外提供一个单一的访问入口，以达到减小应用的复杂度的目的。要创建MERGE表，不仅仅基表的结构要完全一致，包括字段的顺序，基表的索引也必须完全一致。

**5****）****Memory****存储引擎**

Memory存储引擎，通过名字就很容易让人知道，他是一个将数据存储在内存中的存储引擎。Memory存储引擎不会将任何数据存放到磁盘上，仅仅存放了一个表结构相关信息的.frm文件在磁盘上面。所以一旦MySQLCrash或者主机Crash之后，Memory的表就只剩下一个结构了。Memory表支持索引，并且同时支持Hash和B－Tree两种格式的索引。由于是存放在内存中，所以Memory都是按照定长的空间来存储数据的，而且不支持BLOB和TEXT类型的字段。Memory存储引擎实现页级锁定。

**6****）****BDB****存储引擎**

BDB存储引擎全称为BerkeleyDB存储引擎，和Innodb一样，也不是MySQL自己开发实现的一个存储引擎，而是由SleepycatSoftware所提供，当然，也是开源存储引擎，同样支持事务安全。

**7****）****FEDERATED****存储引擎**

FEDERATED存储引擎所实现的功能，和Oracle的DBLINK基本相似，主要用来提供对远程MySQL服务器上面的数据的访问接口。如果我们使用源码编译来安装MySQL，那么必须手工指定启用FEDERATED存储引擎才行，因为MySQL默认是不起用该存储引擎的。

**8****）****ARCHIVE****存储引擎**

ARCHIVE存储引擎主要用于通过较小的存储空间来存放过期的很少访问的历史数据。ARCHIVE表不支持索引，通过一个.frm的结构定义文件，一个.ARZ的数据压缩文件还有一个.ARM的meta信息文件。由于其所存放的数据的特殊性，ARCHIVE表不支持删除，修改操

作，仅支持插入和查询操作。锁定机制为行级锁定。

**9****）****BLACKHOLE****存储引擎**

BLACKHOLE存储引擎是一个非常有意思的存储引擎，功能恰如其名，就是一个“黑洞”。就像我们unix系统下面的“/dev/null”设备一样，不管我们写入任何信息，都是有去无回。

**10****）****CSV****存储引擎**

CSV存储引擎实际上操作的就是一个标准的CSV文件，他不支持索引。起主要用途就是大家有些时候可能会需要通过数据库中的数据导出成一份报表文件，而CSV文件是很多软件都支持的一种较为标准的格式，所以我们可以通过先在数据库中建立一张CVS表，然后将生成的报表信息插入到该表，即可得到一份CSV报表文件了。

**四、影响****MySQLServer****性能的相关因素**

**1****商业需求对性能的影响**

典型需求：一个论坛帖子总量的统计，要求：实时更新。

**2****系统架构及实现对性能的影响**

以下几类数据都是不适合在数据库中存放的：

二进制多媒体数据

流水队列数据

超大文本数据

通过Cache技术来提高系统性能：

系统各种配置及规则数据；

活跃用户的基本信息数据；

活跃用户的个性化定制信息数据；

准实时的统计信息数据；

其他一些访问频繁但变更较少的数据；

**3 Query****语句对系统性能的影响**

需求：取出某个group（假设id为1）下的用户编号（id），用户昵称（nick\_name），并按照加入组的时间（user\_group.gmt\_create）来进行倒序排列，取出前20个。

解决方案一：

SELECT id,nick_name FROM user,user_group WHERE user_group.group_id=1 and user_group.user_id=user.id ORDER BY user_group.gmt_create desc limit 100,20;

解决方案二：

SELECT user.id,user.nick_name FROM(
    SELECT user_id
    FROM user_group
    WHERE user_group.group_id=1
    ORDER BY gmt_create desc
    limit 100,20)t,user
    WHERE t.user_id=user.id;

通过比较两个解决方案的执行计划，我们可以看到第一中解决方案中需要和user表参与Join的记录数MySQL通过统计数据估算出来是31156，也就是通过user\_group表返回的所有满足group\_id=1的记录数（系统中的实际数据是20000）。而第二种解决方案的执行计划中，user表参与Join的数据就只有20条，两者相差很大，我们认为第二中解决方案应该明显优于第一种解决方案。

**4 Schema****设计对系统的性能影响**

尽量减少对数据库访问的请求。

尽量减少无用数据的查询请求。

**5****硬件环境对系统性能的影响**

1、典型OLTP应用系统

对于各种数据库系统环境中大家最常见的OLTP系统，其特点是并发量大，整体数据量比较多，但每次访问的数据比较少，且访问的数据比较离散，活跃数据占总体数据的比例不是太大。对于这类系统的数据库实际上是最难维护，最难以优化的，对主机整体性能要求也是最高的。因为不仅访问量很高，数据量也不小。

针对上面的这些特点和分析，我们可以对OLTP的得出一个大致的方向。

虽然系统总体数据量较大，但是系统活跃数据在数据总量中所占的比例不大，那么我们可以通过扩大内存容量来尽可能多的将活跃数据cache到内存中；

虽然IO访问非常频繁，但是每次访问的数据量较少且很离散，那么我们对磁盘存储的要求是IOPS表现要很好，吞吐量是次要因素；

并发量很高，CPU每秒所要处理的请求自然也就很多，所以CPU处理能力需要比较强劲；

虽然与客户端的每次交互的数据量并不是特别大，但是网络交互非常频繁，所以主机与客户端交互的网络设备对流量能力也要求不能太弱。

2、典型OLAP应用系统

用于数据分析的OLAP系统的主要特点就是数据量非常大，并发访问不多，但每次访问所需要检索的数据量都比较多，而且数据访问相对较为集中，没有太明显的活跃数据概念。

基于OLAP系统的各种特点和相应的分析，针对OLAP系统硬件优化的大致策略如下：

数据量非常大，所以磁盘存储系统的单位容量需要尽量大一些；

单次访问数据量较大，而且访问数据比较集中，那么对IO系统的性能要求是需要有尽可能大的每秒IO吞吐量，所以应该选用每秒吞吐量尽可能大的磁盘；

虽然IO性能要求也比较高，但是并发请求较少，所以CPU处理能力较难成为性能瓶颈，所以CPU处理能力没有太苛刻的要求；

虽然每次请求的访问量很大，但是执行过程中的数据大都不会返回给客户端，最终返回给客户端的数据量都较小，所以和客户端交互的网络设备要求并不是太高；

此外，由于OLAP系统由于其每次运算过程较长，可以很好的并行化，所以一般的OLAP系统都是由多台主机构成的一个集群，而集群中主机与主机之间的数据交互量一般来说都是非常大的，所以在集群中主机之间的网络设备要求很高。

3、除了以上两个典型应用之外，还有一类比较特殊的应用系统，他们的数据量不是特别大，但是访问请求及其频繁，而且大部分是读请求。可能每秒需要提供上万甚至几万次请求，每次请求都非常简单，可能大部分都只有一条或者几条比较小的记录返回，就比如基于数据库的DNS服务就是这样类型的服务。

虽然数据量小，但是访问极其频繁，所以可以通过较大的内存来cache住大部分的数据，这能够保证非常高的命中率，磁盘IO量比较小，所以磁盘也不需要特别高性能的；

并发请求非常频繁，比需要较强的CPU处理能力才能处理；

虽然应用与数据库交互量非常大，但是每次交互数据较少，总体流量虽然也会较大，但是一般来说普通的千兆网卡已经足够了。

**五、****MySQL ****锁定机制简介**

行级锁定（row-level）

表级锁定（table-level）

页级锁定（page-level）

在MySQL数据库中，使用表级锁定的主要是MyISAM，Memory，CSV等一些非事务性存储引擎，而使用行级锁定的主要是Innodb存储引擎和NDBCluster存储引擎，页级锁定主要是BerkeleyDB存储引擎的锁定方式。

**六、****MySQL Query****的优化**

Query语句的优化思路和原则主要提现在以下几个方面：

1. 优化更需要优化的Query；

2. 定位优化对象的性能瓶颈；

3. 明确的优化目标；

4. 从Explain入手；

5. 多使用profile

6. 永远用小结果集驱动大的结果集；

7. 尽可能在索引中完成排序；

8. 只取出自己需要的Columns；

9. 仅仅使用最有效的过滤条件；

10.尽可能避免复杂的Join和子查询；

**合理设计并利用索引**

1）B-Tree索引

一般来说，MySQL中的B-Tree索引的物理文件大多都是以BalanceTree的结构来存储的，也就是所有实际需要的数据都存放于Tree的LeafNode，而且到任何一个LeafNode的最短路径的长度都是完全相同的，所以我们大家都称之为B-Tree索引当然，可能各种数据库（或MySQL的各种存储引擎）在存放自己的B-Tree索引的时候会对存储结构稍作改造。如Innodb存储引擎的B-Tree索引实际使用的存储结构实际上是B+Tree，也就是在B-Tree数据结构的基础上做了很小的改造，在每一个LeafNode上面出了存放索引键的相关信息之外，还存储了指向与该LeafNode相邻的后一个LeafNode的指针信息，这主要是为了加快检索多个相邻LeafNode的效率考虑。

2）Hash索引

Hash索引在MySQL中使用的并不是很多，目前主要是Memory存储引擎使用，而且在Memory存储引擎中将Hash索引作为默认的索引类型。所谓Hash索引，实际上就是通过一定的Hash算法，将需要索引的键值进行Hash运算，然后将得到的Hash值存入一个Hash表中。然后每次需要检索的时候，都会将检索条件进行相同算法的Hash运算，然后再和Hash表中的Hash值进行比较并得出相应的信息。

Hash索引仅仅只能满足“=”,“IN”和“<=>”查询，不能使用范围查询；

Hash索引无法被利用来避免数据的排序操作；

Hash索引不能利用部分索引键查询；

Hash索引在任何时候都不能避免表扫面；

Hash索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高；

3）Full-text索引

Full-text索引也就是我们常说的全文索引，目前在MySQL中仅有MyISAM存储引擎支持，而且也并不是所有的数据类型都支持全文索引。目前来说，仅有CHAR，VARCHAR和TEXT这三种数据类型的列可以建Full-text索引。

索引能够极大的提高数据检索效率，也能够改善排序分组操作的性能，但是我们不能忽略的一个问题就是索引是完全独立于基础数据之外的一部分数据，更新数据会带来的IO量和调整索引所致的计算量的资源消耗。

是否需要创建索引，几点原则：较频繁的作为查询条件的字段应该创建索引；唯一性太差的字段不适合单独创建索引，即使频繁作为查询条件；更新非常频繁的字段不适合创建索引；

不会出现在WHERE子句中的字段不该创建索引；

**Join****语句的优化**

尽可能减少Join语句中的NestedLoop的循环总次数；“永远用小结果集驱动大的结果集”。

优先优化NestedLoop的内层循环；

保证Join语句中被驱动表上Join条件字段已经被索引；

当无法保证被驱动表的Join条件字段被索引且内存资源充足的前提下，不要太吝惜JoinBuffer的设置；

**ORDER BY****，****GROUP BY****和****DISTINCT****优化**

1）ORDER BY的实现与优化

优化Query语句中的ORDER BY的时候，尽可能利用已有的索引来避免实际的排序计算，可以很大幅度的提升ORDER BY操作的性能。

优化排序：

1.加大max\_length\_for\_sort\_data参数的设置；

2.去掉不必要的返回字段；

3.增大sort\_buffer\_size参数设置；

2）GROUP BY的实现与优化

由于GROUP BY实际上也同样需要进行排序操作，而且与ORDER BY相比，GROUP BY主要只是多了排序之后的分组操作。当然，如果在分组的时候还使用了其他的一些聚合函数，那么还需要一些聚合函数的计算。所以，在GROUP BY的实现过程中，与ORDER BY一样也可以利用到索引。

3）DISTINCT的实现与优化

DISTINCT实际上和GROUP BY的操作非常相似，只不过是在GROUP BY之后的每组中只取出一条记录而已。所以，DISTINCT的实现和GROUP BY的实现也基本差不多，没有太大的区别。同样可以通过松散索引扫描或者是紧凑索引扫描来实现，当然，在无法仅仅使用索引即能完成DISTINCT的时候，MySQL只能通过临时表来完成。但是，和GROUP BY有一点差别的是，DISTINCT并不需要进行排序。也就是说，在仅仅只是DISTINCT操作的Query如果无法仅仅利用索引完成操作的时候，MySQL会利用临时表来做一次数据的“缓存”，但是不会对临时表中的数据进行filesort操作。

**七、****MySQL****数据库****Schema****设计的性能优化**

**高效的模型设计**

适度冗余-让Query尽两减少Join

大字段垂直分拆-summary表优化

大表水平分拆-基于类型的分拆优化

统计表-准实时优化

**合适的数据类型**

![format_png 5][]

时间存储格式总类并不是太多，我们常用的主要就是DATETIME，DATE和TIMESTAMP这三种了。从存储空间来看TIMESTAMP最少，四个字节，而其他两种数据类型都是八个字节，多了一倍。而TIMESTAMP的缺点在于他只能存储从1970年之后的时间，而另外两种时间类型可以存放最早从1001年开始的时间。如果有需要存放早于1970年之前的时间的需求，我们必须放弃TIMESTAMP类型，但是只要我们不需要使用1970年之前的时间，最好尽量使用TIMESTAMP来减少存储空间的占用。

字符存储类型

![format_png 6][]

CHAR\[(M)\]类型属于静态长度类型，存放长度完全以字符数来计算，所以最终的存储长度是基于字符集的，如latin1则最大存储长度为255字节，但是如果使用gbk则最大存储长度为510字节。CHAR类型的存储特点是不管我们实际存放多长数据，在数据库中都会存放M个字符，不够的通过空格补上，M默认为1。虽然CHAR会通过空格补齐存放的空间，但是在访问数据的时候，MySQL会忽略最后的所有空格，所以如果我们的实际数据中如果在最后确实需要空格，则不能使用CHAR类型来存放。

VARCHAR\[(M)\]属于动态存储长度类型，仅存占用实际存储数据的长度。TINYTEXT，TEXT，MEDIUMTEXT和LONGTEXT这四种类型同属于一种存储方式，都是动态存储长度类型，不同的仅仅是最大长度的限制。

**事务优化**

1. 脏读：脏读就是指当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。

2. 不可重复读：是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。

3. 幻读：是指当事务不是独立执行时发生的一种现象，例如第一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，第二个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，以后就会发生操作第一个事务的用户发现表中还有没有修改的数据行，就好象发生了幻觉一样。

Innodb在事务隔离级别方面支持的信息如下：

1.READ UNCOMMITTED

常被成为Dirty Reads（脏读），可以说是事务上的最低隔离级别：在普通的非锁定模式下SELECT的执行使我们看到的数据可能并不是查询发起时间点的数据，因而在这个隔离度下是非Consistent Reads（一致性读）；

2.READ COMMITTED

这一隔离级别下，不会出现DirtyRead，但是可能出现Non-RepeatableReads(不可重复读)和PhantomReads（幻读）。

3. REPEATABLE READ

REPEATABLE READ隔离级别是InnoDB默认的事务隔离级。在REPEATABLE READ隔离级别下，不会出现DirtyReads，也不会出现Non-Repeatable Read，但是仍然存在PhantomReads的可能性。

4.SERIALIZABLE

SERIALIZABLE隔离级别是标准事务隔离级别中的最高级别。设置为SERIALIZABLE隔离级别之后，在事务中的任何时候所看到的数据都是事务启动时刻的状态，不论在这期间有没有其他事务已经修改了某些数据并提交。所以，SERIALIZABLE事务隔离级别下，PhantomReads也不会出现。

**八、可扩展性设计之数据切分**

**数据的垂直切分**

数据的垂直切分，也可以称之为纵向切分。将数据库想象成为由很多个一大块一大块的“数据块”（表）组成，我们垂直的将这些“数据块”切开，然后将他们分散到多台数据库主机上面。这样的切分方法就是一个垂直（纵向）的数据切分。

垂直切分的优点

◆数据库的拆分简单明了，拆分规则明确；

◆应用程序模块清晰明确，整合容易；

◆数据维护方便易行，容易定位；

垂直切分的缺点

◆部分表关联无法在数据库级别完成，需要在程序中完成；

◆对于访问极其频繁且数据量超大的表仍然存在性能平静，不一定能满足要求；

◆事务处理相对更为复杂；

◆切分达到一定程度之后，扩展性会遇到限制；

◆过读切分可能会带来系统过渡复杂而难以维护。

**数据的水平切分**

数据的垂直切分基本上可以简单的理解为按照表按照模块来切分数据，而水平切分就不再是按照表或者是功能模块来切分了。一般来说，简单的水平切分主要是将某个访问极其平凡的表再按照某个字段的某种规则来分散到多个表之中，每个表中包含一部分数据。

水平切分的优点

◆表关联基本能够在数据库端全部完成；

◆不会存在某些超大型数据量和高负载的表遇到瓶颈的问题；

◆应用程序端整体架构改动相对较少；

◆事务处理相对简单；

◆只要切分规则能够定义好，基本上较难遇到扩展性限制；

水平切分的缺点

◆切分规则相对更为复杂，很难抽象出一个能够满足整个数据库的切分规则；

◆后期数据的维护难度有所增加，人为手工定位数据更困难；

◆应用系统各模块耦合度较高，可能会对后面数据的迁移拆分造成一定的困难。

**数据切分与整合中可能存在的问题**

1.引入分布式事务的问题

完全可以将一个跨多个数据库的分布式事务分拆成多个仅处于单个数据库上面的小事务，并通过应用程序来总控各个小事务。当然，这样作的要求就是我们的俄应用程序必须要有足够的健壮性，当然也会给应用程序带来一些技术难度。

2.跨节点Join的问题

推荐通过应用程序来进行处理，先在驱动表所在的MySQLServer中取出相应的驱动结果集，然后根据驱动结果集再到被驱动表所在的MySQL Server中取出相应的数据。

3.跨节点合并排序分页问题

从多个数据源并行的取数据，然后应用程序汇总处理。

**九、可扩展性设计之****Cache****与****Search****的利用**

通过引入Cache（Redis、Memcached），减少数据库的访问，增加性能。

通过引入Search（Lucene、Solr、ElasticSearch），利用搜索引擎高效的全文索引和分词算法，以及高效的数据检索实现，来解决数据库和传统的Cache软件完全无法解决的全文模糊搜索、分类统计查询等功能。

![format_png 7][]

本文乃《MySQL性能调优与架构设计》读书笔记！

《MySQL性能优化的最佳20+条经验》 http://coolshell.cn/articles/1846.html

**不会使用索引，导致全表扫描情况**  
  
1.不要使用in操作符，这样数据库会进行全表扫描，  
推荐方案：在业务密集的SQL当中尽量不采用IN操作符  
  
2.not in 使用not in也不会走索引  
推荐方案：用not exists或者（外联结+判断为空）来代替  
  
3<> 操作符（不等于） 使用<>同样不会使用索引，因此对它的处理只会产生全表扫描  
推荐方案：用其它相同功能的操作运算代替，如  
a<>0 改为 a>0 or a<0  
  
a<>’’ 改为 a>’’  
  
4.IS NULL 或IS NOT NULL操作（判断字段是否为空）  
  
判断字段是否为空一般是不会应用索引的，因为B树索引是不索引空值的。  
推荐方案：用其它相同功能的操作运算代替，如  
a is not null 改为 a>0 或a>’’等。  
  
5.> 及 < 操作符（大于或小于操作符）  
  
大于或小于操作符一般情况下是不用调整的，因为它有索引就会采用索引查找，但有的情况下可以对它进行优化，如一个表有100万记录，一个数值型字段A，30万记录的A=0，30万记录的A=1，39万记录的A=2，1万记录的A=3。那么执行A>2与A>=3的效果就有很大的区别了，因为A>2时ORACLE会先找出为2的记录索引再进行比较，而A>=3时ORACLE则直接找到=3的记录索引。  
  
6.LIKE操作符  
  
LIKE操作符可以应用通配符查询，里面的通配符组合可能达到几乎是任意的查询，但是如果用得不好则会产生性能上的问题，如LIKE ‘%5400%’ 这种查询不会引用索引，而LIKE ‘5400%’则会引用范围索引。  
可以采用substr(column,1,4)=’5400’  
  
7.UNION操作符  
  
UNION在进行表链接后会筛选掉重复的记录，所以在表链接后会对所产生的结果集进行排序运算，删除重复的记录再返回结果。实际大部分应用中是不会产生重复的记录，最常见的是过程表与历史表UNION。如：  
select \* from gc\_dfys  
  
union  
  
select \* from ls\_jg\_dfys  
  
这个SQL在运行时先取出两个表的结果，再用排序空间进行排序删除重复的记录，最后返回结果集，如果表数据量大的话可能会导致用磁盘进行排序。  
  
推荐方案：采用UNION ALL操作符替代UNION，因为UNION ALL操作只是简单的将两个结果合并后就返回。  
  
8.SQL书写的影响（针对oracle而言）  
  
同一功能同一性能不同写法SQL的影响  
  
如一个SQL在A程序员写的为  
  
Select \* from zl\_yhjbqk  
  
B程序员写的为  
  
Select \* from dlyx.zl\_yhjbqk（带表所有者的前缀）  
  
C程序员写的为  
  
Select \* from DLYX.ZLYHJBQK（大写表名）  
  
D程序员写的为  
  
Select \* from DLYX.ZLYHJBQK（中间多了空格）  
  
以上四个SQL在ORACLE分析整理之后产生的结果及执行的时间是一样的，但是从ORACLE共享内存SGA的原理，可以得出ORACLE对每个 SQL都会对其进行一次分析，并且占用共享内存，如果将SQL的字符串及格式写得完全相同则ORACLE只会分析一次，共享内存也只会留下一次的分析结果，这不仅可以减少分析SQL的时间，而且可以减少共享内存重复的信息，ORACLE也可以准确统计SQL的执行频率。  
  
9.WHERE后面的条件顺序影响  
  
WHERE子句后面的条件顺序对大数据量表的查询会产生直接的影响，如  
  
Select \* from zl\_yhjbqk where dy\_dj = '1K以下' and xh\_bz=1  
  
Select \* from zl\_yhjbqk where xh\_bz=1 and dy\_dj = '1K以下'  
  
以上两个SQL中dy\_dj及xh\_bz两个字段都没进行索引，所以执行的时候都是全表扫描，第一条SQL的dy\_dj ='1KV以下'条件在记录集内比率为99%，而xh\_bz=1的比率只为0.5%，在进行第一条SQL的时候99%条记录都进行dy\_dj及xh\_bz的比较，而在进行第二条SQL的时候0.5%条记录都进行dy\_dj及xh\_bz的比较，以此可以得出第二条SQL的CPU占用率明显比第一条低。  
  
10.查询表顺序的影响  
  
在FROM后面的表中的列表顺序会对SQL执行性能影响，在没有索引及ORACLE没有对表进行统计分析的情况下ORACLE会按表出现的顺序进行链接，由此因为表的顺序不对会产生十分耗服务器资源的数据交叉。（注：如果对表进行了统计分析，ORACLE会自动先进小表的链接，再进行大表的链接）

**sql语句优化之一：尽量使用索引避免全表扫描**

1.对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 及 order by 涉及的列上建立索引。   
  
2.应尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描，如：   
select id from t where num is null   
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：   
select id from t where num=0   
  
3.应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。   
  
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.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   
  
6.下面的查询也将导致全表扫描：   
select id from t where name like '%abc%'   
若要提高效率，可以考虑全文检索。   
  
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 \#t from t where 1=0   
这类代码不会返回任何结果集，但是会消耗系统资源的，应改成这样：   
create table \#t(...)   
  
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个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。  
  
14.并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。  
18.尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。  
  
20.尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。   
  
21.避免频繁创建和删除临时表，以减少系统表资源的消耗。   
  
23.在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。  
  
25.尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。   
  
  
28.在所有的存储过程和触发器的开始处设置 SET NOCOUNT ON ，在结束时设置 SET NOCOUNT OFF 。无需在执行存储过程和触发器的每个语句后向客户端发送 DONE\_IN\_PROC 消息。  
  
30.尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理

# drop、truncate和delete的区别 #

（1）DELETE语句执行删除的过程是每次从表中删除一行，并且同时将该行的删除操作作为事务记录在日志中保存以便进行进行回滚操作。

TRUNCATE TABLE 则一次性地从表中删除所有的数据并不把单独的删除操作记录记入日志保存，删除行是不能恢复的。并且在删除的过程中不会激活与表有关的删除触发器。执行速度快。

（2）表和索引所占空间。

当表被TRUNCATE 后，这个表和索引所占用的空间会恢复到初始大小，

DELETE操作不会减少表或索引所占用的空间。

drop语句将表所占用的空间全释放掉。

（3）一般而言，drop > truncate > delete

（4）应用范围。

TRUNCATE 只能对TABLE；         DELETE可以是table和view

（5）TRUNCATE 和DELETE只删除数据， DROP则删除整个表（结构和数据）。

（6）truncate与不带where的delete ：只删除数据，而不删除表的结构（定义）drop语句将删除表的结构被依赖的约束（constrain),触发器（trigger)索引（index);依赖于该表的存储过程/函数将被保留，但其状态会变为：invalid。

（7）delete语句为DML（data maintain Language),这个操作会被放到 rollback segment中,事务提交后才生效。如果有相应的 tigger,执行的时候将被触发。

（8）truncate、drop是DLL（data define language),操作立即生效，原数据不放到 rollback segment中，不能回滚

（9）在没有备份情况下，谨慎使用 drop 与 truncate。要删除部分数据行采用delete且注意结合where来约束影响范围。回滚段要足够大。要删除表用drop;若想保留表而将表中数据删除，如果于事务无关，用truncate即可实现。如果和事务有关，或老师想触发trigger,还是用delete。

（10） Truncate table 表名 速度快,而且效率高,因为:   
truncate table 在功能上与不带 WHERE 子句的 DELETE 语句相同：二者均删除表中的全部行。但 TRUNCATE TABLE 比 DELETE 速度快，且使用的系统和事务日志资源少。DELETE 语句每次删除一行，并在事务日志中为所删除的每行记录一项。TRUNCATE TABLE 通过释放存储表数据所用的数据页来删除数据，并且只在事务日志中记录页的释放。

（11） TRUNCATE TABLE 删除表中的所有行，但表结构及其列、约束、索引等保持不变。新行标识所用的计数值重置为该列的种子。如果想保留标识计数值，请改用 DELETE。如果要删除表定义及其数据，请使用 DROP TABLE 语句。

（12） 对于由 FOREIGN KEY 约束引用的表，不能使用 TRUNCATE TABLE，而应使用不带 WHERE 子句的 DELETE 语句。由于 TRUNCATE TABLE 不记录在日志中，所以它不能激活触发器。

一、delete

1、delete是DML，执行delete操作时，每次从表中删除一行，并且同时将该行的的删除操作记录在redo和undo表空间中以便进行回滚（rollback）和重做操作，但要注意表空间要足够大，需要手动提交（commit）操作才能生效，可以通过rollback撤消操作。

2、delete可根据条件删除表中满足条件的数据，如果不指定where子句，那么删除表中所有记录。

3、delete语句不影响表所占用的extent，高水线(high watermark)保持原位置不变。

二、truncate

1、truncate是DDL，会隐式提交，所以，不能回滚，不会触发触发器。

2、truncate会删除表中所有记录，并且将重新设置高水线和所有的索引，缺省情况下将空间释放到minextents个extent，除非使用reuse storage，。不会记录日志，所以执行速度很快，但不能通过rollback撤消操作（如果一不小心把一个表truncate掉，也是可以恢复的，只是不能通过rollback来恢复）。

3、对于外键（foreignkey ）约束引用的表，不能使用 truncate table，而应使用不带 where 子句的 delete 语句。

4、truncatetable不能用于参与了索引视图的表。

三、drop

1、drop是DDL，会隐式提交，所以，不能回滚，不会触发触发器。

2、drop语句删除表结构及所有数据，并将表所占用的空间全部释放。

3、drop语句将删除表的结构所依赖的约束，触发器，索引，依赖于该表的存储过程/函数将保留,但是变为invalid状态。

总结：

1、在速度上，一般来说，drop> truncate > delete。

2、在使用drop和truncate时一定要注意，虽然可以恢复，但为了减少麻烦，还是要慎重。

3、如果想删除部分数据用delete，注意带上where子句，回滚段要足够大；

如果想删除表，当然用drop；

如果想保留表而将所有数据删除，如果和事务无关，用truncate即可；

如果和事务有关，或者想触发trigger，还是用delete；

如果是整理表内部的碎片，可以用truncate跟上reuse stroage，再重新导入/插入数据。

MySQL的一条慢SQL查询导致整个网站宕机的解决方法

我们通过观看应用程序服务器的监控看到一些信息：我们的 goods\_service 这个服务异常：异常情况如下：

1. cpu 长期占用100% ＋   
2. jstatck pid 无法dump 内存堆栈信息，必须强制dump －F  
3. dump 出来的内存信息发现，这个进程里边所有线程 均处于 BLOCKED 状态  
4. 通过jstat －gcutil 看到 FGC 相当频繁，10s左右就FGC一次  
5. 内存占用超过了分配的内存

那么最终的原因就是因为上边的慢查询 查询了大量数据（最多有700w行数据），导致goods\_service 内存暴涨，出现服务无法响应，进一步的恶化就是挂占

OK，知道了为什么会挂占，那么我们是如何解决这个问题的呢？  
既然我们知道是由于查询了 ss\_si\_id＝0 导致的，那么我们屏蔽掉这个SQL不就好了么。屏蔽的办法可以有多种：  
1. 我们程序逻辑判断一下这类型的 查询 如果 有查询 ss\_si\_id＝0 的一律封杀掉  
2. 我们改改SQL配置文件，修改SQL语句

我们发现DB服务器上存在大量的 这个慢查询，而且DB服务器负载已经从 0.xx 飙升到了 50+ 了，随之而来的连接数也飙升的厉害， 如果再不及时处理，估计DB服务器也挂掉了

那么我们最终采取以下处理办法：  
1.运维配合研发修改SQL语句 我们在这个WHERE 条件中添加了一个条件： AND ss\_si\_id <> 0 ,在MySQL之行计划层屏蔽掉此SQL;  
2.DBA 开启kill 掉这个查询语句，避免DB服务器出现down机的情况，当然这个就用到了我们的 pt-kill 工具，不得不说这个工具相当好用

总结（经验与教训）：  
1.类似这种查询 default 值的 SQL ，我们应该从源头上杜绝这类查询  
2.限制查询结果集大小，避免因查询结果集太大导致服务死掉

[format_png]: /images/20210920/aee45a0b317e4f1ebacc039a3cc01fbb.png
[format_png 1]: /images/20210920/df9a4db6cd7b4eefa39cfd62231d0eb6.png
[format_png 2]: /images/20210920/b063028aaec1475d8d4a71672435c516.png
[format_png 3]: /images/20210920/bfaed64ce586429098078e17ff31b56c.png
[http_www.cnblogs.com_you-yang_archive_2012_01_18_2325841.html]: http://www.cnblogs.com/you-yang/archive/2012/01/18/2325841.html
[http_lizhenliang.blog.51cto.com_7876557_1612800]: http://lizhenliang.blog.51cto.com/7876557/1612800
[format_png 4]: /images/20210920/2f99ee7367aa451f8a277b6187a9a6fb.png
[format_png 5]: /images/20210920/2172dd3eab964c3cb94bfaab886badb7.png
[format_png 6]: /images/20210920/124a2010a0044d69acdc804a964adb0b.png
[format_png 7]: /images/20210920/a8e50eddebb4448bbdcfe9424975579e.png