1、sql语句性能达不到你的要求，执行效率让你忍无可忍，一般会时下面几种情况。
网速不给力，不稳定。
服务器内存不够，或者SQL 被分配的内存不够。
sql语句设计不合理
没有相应的索引，索引不合理
没有有效的索引视图
表数据过大没有有效的分区设计
数据库设计太2，存在大量的数据冗余
索引列上缺少相应的统计信息，或者统计信息过期
2、那么我们如何给找出来导致性能慢的的原因呢？
首先你要知道是否跟sql语句有关，确保不是机器开不开机，服务器硬件配置太差；
开启慢查询日志，并使用Pt-query-digest工具分析sql慢的相关语句，也就是执行时间过长，占用系统资源，cpu过多的；
用explain去分析执行时间长的sql语句，看是否需要进行优化操作；
3、sql优化的方法
1、对查询进行优化
应尽量避免全表扫描;
尽量避免select * 的存在，使用具体的列代替*，避免多余的列
使用where限定具体要查询的数据，避免多余的行
使用top，distinct关键字减少多余重复的行
应考虑在 where 及 order by 涉及的列上建立索引;
2、在where条件后面避免如下会造成全表查询的情况。
A、避免在 where 子句中使用!=或<>操作符，无法使用索引就全表查询；
B、避免在 where 子句中对字段进行 null 值判断；
优化的实例：select id from t where num is null
可以在num上设置默认值0，确保表中num列没有null值，然后这样查询：
select id from t where num=0
C、避免在 where 子句中使用 or 来连接条件
select id from t where num=10 or num=20 可以把or用union all来进行替换优化。
可以这样查询：
select id from t where num=10
union all
select id from t where num=20
D、避免在where语句后进行前置模糊查询，可以使用后置模糊查询使用到索引。
select id from t where name like '%abc%'；
E、in 和 not in 也要慎用，否则会导致全表扫描，可使用between and进行优化；如：
select id from t where num in(1,2,3)对于连续的数值，能用 between 就不要用 in 了：
select id from t where num between 1 and 3
F、如果在 where 子句中使用参数，也会导致全表扫描。因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时；它必须在编译时进行选择。然而，如果在编译时建立访问计划，变量的值还是未知的，因而无法作为索引选择的输入项。如下面语句将进行全表扫描：
select id from t where num=@num可以改为强制查询使用索引： where后面使用参数也会导致全表查询，优化可以是使用with(index(索引名)) 进行强制索引使用。
select id from t with(index(索引名)) where num=@num
G、避免在 where 子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描
select id from t where num/2=100
优化为在字段后面的值上进行操作
select id from t where num=100*2
H、应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描
select id from t where datediff(day,createdate,'2005-11-30')=0--'2005-11-30'生成的id
优化为：select id from t where createdate>='2005-11-30' and createdate<'2005-12-1'；
I、不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引
3、对于索引使用的一些认识，注意使用索引时的查询顺序以及何时选择使用索引：
A、在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。
B、并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引；如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。
C、索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了 insert 及 update 的效率，因为 insert 或 update 时有可能会重建索引，所以怎样建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。
D、避免跟新聚簇索引(Clustered Index)的值，因为聚簇索引数据列的数据就是表记录的物理地址顺序，一旦该列值改变将导致整个表记录的顺序的调整。
4、尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了。
5、尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。
6.任何地方都不要使用 select * from t ，用具体的字段列表代替“*”，不要返回用不到的任何字段。
7.尽量使用表变量来代替临时表。如果表变量包含大量数据，请注意索引非常有限（只有主键索引）。
8.避免频繁创建和删除临时表，以减少系统表资源的消耗。
9.在新建临时表时，如果一次性插入数据量很大，那么可以使用 select into 代替 create table，避免造成大量 log ，以提高速度；如果数据量不大，为了缓和系统表的资源，应先create table，然后insert。
10.如果使用到了临时表，在存储过程的最后务必将所有的临时表显式删除，先 truncate table ，然后 drop table ，这样可以避免系统表的较长时间锁定。
11.尽量避免使用游标，因为游标的效率较差，如果游标操作的数据超过1万行，那么就应该考虑改写。
12.使用基于游标的方法或临时表方法之前，应先寻找基于集的解决方案来解决问题，基于集的方法通常更有效。
13.与临时表一样，游标并不是不可使用。对小型数据集使用 FAST_FORWARD 游标通常要优于其他逐行处理方法，尤其是在必须引用几个表才能获得所需的数据时。在结果集中包括“合计”的例程通常要比使用游标执行的速度快。如果开发时间允许，基于游标的方法和基于集的方法都可以尝试一下，看哪一种方法的效果更好。
14.尽量避免向客户端返回大数据量，若数据量过大，应该考虑相应需求是否合理。
15.尽量避免大事务操作，提高系统并发能力。
16、对于多张大数据量（这里几百条就算大了）的表JOIN，要先分页再JOIN，否则逻辑读会很高，性能很差。
17、在可能的情况下尽量限制尽量结果集行数如：SELECT TOP 300 COL1,COL2,COL3 FROM T1,因为某些情况下用户是不需要那么多的数据的。
什么是大事物？
1.定义：运行时间比较长，操作的数据比较多的事务
2.大事务风险：
a)锁定太多的数据，造成大量的阻塞和锁超时，回滚所需要的时间比较长。
b)执行时间长，容易造成主从延迟
3.如何处理大事务
a)避免一次处理太多大数据
b)移出不必要在事务中的select操作
1.原子性：一个事务必须被视为一个不可分割的最小工作单元整个事务的所有操作要么全部提交成功，要么全部失败，对于一个事务来说不可能只执行其中一部分。例如：银行卡转账（整个事务中的所有操作要么全部提交成功要么全部失败）。
2.事务的一致性：一致性是指事务将数据库从一种一致性状态转换到另外一种一致性状态，在事务开始之前和事务结束后数据库中数据的完整性没有被破坏。例如：银行卡转账（一张减去100元，一张增加100元数据没有变化）。
3.事务的隔离性：一个事务对数据库中数据的修改，在未提交完成前对于其他事务不可见的。
a.读未提交（read-uncommitted）：就是一个事务可以读取另一个未提交事务的数据脏读（脏读）
b.读已提交（read-committed）：就是一个事务要等另一个事务提交后才能读取数据（不可重复读）
c.可重复读（repeatable-read）：就是在开始读取数据（事务开启）时，不再允许修改操作
d.可串行化（serializable）：是最高的事务隔离级别，在该级别下，事务串行化顺序执行，可以避免脏读、不可重复读与幻读。但是这种事务隔离级别效率低下，比较耗数据库性能，一般不使用。
注意：
并发性由高到低1>2>3>4
隔离性由高到低4>3>2>1
show variables like '%iso%'(查看当前隔离性级别)
set session tx_isolation （设置隔离级别）
4.事务的持久性
一旦事务提交，则其所做的修改就会永久的保存到数据库中，此时即使系统崩溃，已经提交的修改数据也不会丢失。
2.大表对查询的影响
a)慢查询、b)区分度底、c)大量磁盘IO、d)建立索引需要很长的时间、e)修改表结构需要长时间锁表、f)影响正常的数据操作
3.如何处理大表问题
a)分库分表把一张表分成多个小表
难点：分表主键的选择、分表后夸分区数据的查询和统计
b)大表的历史数据归档（前端增加历史查询）
难点：时间点选择，如何进行归档操作
2.TPS:每秒处理事务的速度（一个事务三个过程）。
a)用户请求服务器、b)服务器内部处理、c)服务器返回给用户。
3.QPS:是一台指定服务器每秒能够相应的查询次数。
4.并发量：同一时间处理的请求的数量。
6.并发量大，连接数大说明cpu空闲少繁忙。
7.磁盘IO读写过高会对服务器性能能造成影响。
8.不要在主库上数据库备份（磁盘读压力增大）。
二、通过创建索引减少数据访问
为什么要创建索引？
在没有建索引的情况下，数据库查找某一条数据，就必须进行全表扫描了，对所有数据进行一次遍历，查找出符合条件的记录。在数据量比较小的情况下，也许看不出明显的差别，但是当数据量大的情况下，这种情况就是极为糟糕的了。
创建并使用正确的索引
索引会大大增加表记录的DML（数据库管理语言）(INSERT,UPDATE,DELETE)开销，正确的索引可以让性能提升100，1000倍以上，不合理的索引也可能会让性能下降100倍，因此在一个表中创建什么样的索引需要平衡各种业务需求。
索引常见问题：
索引有哪些种类？
常见的索引有B-TREE索引、hash、全文索引，位图索引一般用于数据仓库应用，全文索引由于使用较少，这里不深入介绍。B-TREE索引包括很多扩展类型，如组合索引、反向索引、函数索引等等，以下是B-TREE索引的简单介绍：
B-TREE索引也称为平衡树索引(Balance Tree)，它是一种按字段排好序的树形目录结构，主要用于提升查询性能和唯一约束支持。B-TREE索引的内容包括根节点、分支节点、叶子节点。叶子节点内容：索引字段内容+表记录ROWID
根节点，分支节点内容：当一个数据块中不能放下所有索引字段数据时，就会形成树形的根节点或分支节点，根节点与分支节点保存了索引树（字段）的顺序及各层级间的引用关系。
一个普通的BTREE索引结构示意图如下所示：
如果我们把一个表的内容认为是一本字典，那索引就相当于字典的目录，如下图所示：
图中是一个字典按部首+笔划数的目录，相当于给字典建了一个按部首+笔划的组合索引。
一个表中可以建多个索引，就如一本字典可以建多个目录一样（按拼音、笔划、部首等等）。
一个索引也可以由多个字段组成，称为组合索引，如上图就是一个按部首+笔划的组合目录。
SQL什么条件会使用索引？
当字段上建有索引时，通常以下情况会使用索引：
INDEX_COLUMN = ?
INDEX_COLUMN > ?
INDEX_COLUMN >= ?
INDEX_COLUMN < ?
INDEX_COLUMN <= ?
INDEX_COLUMN between ? and ?
INDEX_COLUMN in (?,?,...,?)
INDEX_COLUMN like ?||'%'（后导模糊查询）
T1. INDEX_COLUMN=T2. COLUMN1（两个表通过索引字段关联）
SQL什么条件不会使用索引？
|
查询条件 |
不能使用索引原因 |
|
INDEX_COLUMN <> ? INDEX_COLUMN not in (?,?,...,?) |
不等于操作不能使用索引 |
|
function(INDEX_COLUMN) = ? INDEX_COLUMN + 1 = ? INDEX_COLUMN || 'a' = ? |
经过普通运算或函数运算后的索引字段不能使用索引 |
|
INDEX_COLUMN like '%'||? INDEX_COLUMN like '%'||?||'%' |
含前导模糊查询的Like语法不能使用索引 |
|
INDEX_COLUMN is null |
B-TREE索引里不保存字段为NULL值记录，因此IS NULL不能使用索引 |
|
NUMBER_INDEX_COLUMN='12345' CHAR_INDEX_COLUMN=12345 |
Oracle在做数值比较时需要将两边的数据转换成同一种数据类型，如果两边数据类型不同时会对字段值隐式转换，相当于加了一层函数处理，所以不能使用索引。 |
|
a.INDEX_COLUMN=a.COLUMN_1 |
给索引查询的值应是已知数据，不能是未知字段值。 |
|
注： 经过函数运算字段的字段要使用可以使用函数索引，这种需求建议与DBA沟通。 有时候我们会使用多个字段的组合索引，如果查询条件中第一个字段不能使用索引，那整个查询也不能使用索引 如：我们company表建了一个id+name的组合索引，以下SQL是不能使用索引的 Select * from company where name=? Oracle9i后引入了一种index skip scan的索引方式来解决类似的问题，但是通过index skip scan提高性能的条件比较特殊，使用不好反而性能会更差。
|
|
我们一般在什么字段上建索引？
这是一个非常复杂的话题，需要对业务及数据充分分析后再能得出结果。主键及外键通常都要有索引，其它需要建索引的字段应满足以下条件：
1、字段出现在查询条件中，并且查询条件可以使用索引；
2、语句执行频率高，一天会有几千次以上；
3、通过字段条件可筛选的记录集很小，那数据筛选比例是多少才适合？
这个没有固定值，需要根据表数据量来评估，以下是经验公式，可用于快速评估：
小表(记录数小于10000行的表)：筛选比例<10%；
大表：(筛选返回记录数)<(表总记录数*单条记录长度)/10000/16
单条记录长度≈字段平均内容长度之和+字段数*2
以下是一些字段是否需要建B-TREE索引的经验分类：
建立B-TREE索引的是唯一的主键、外键、类似对象身份唯一的值。
|
|
字段类型 |
常见字段名 |
|
需要建索引的字段 |
主键 |
ID,PK |
|
外键 |
PRODUCT_ID,COMPANY_ID,MEMBER_ID,ORDER_ID,TRADE_ID,PAY_ID |
|
|
有对像或身份标识意义字段 |
HASH_CODE,USERNAME,IDCARD_NO,EMAIL,TEL_NO,IM_NO |
|
|
索引慎用字段,需要进行数据分布及使用场景详细评估 |
日期 |
GMT_CREATE,GMT_MODIFIED |
|
年月 |
YEAR,MONTH |
|
|
状态标志 |
PRODUCT_STATUS,ORDER_STATUS,IS_DELETE,VIP_FLAG |
|
|
类型 |
ORDER_TYPE,IMAGE_TYPE,GENDER,CURRENCY_TYPE |
|
|
区域 |
COUNTRY,PROVINCE,CITY |
|
|
操作人员 |
CREATOR,AUDITOR |
|
|
数值 |
LEVEL,AMOUNT,SCORE |
|
|
长字符 |
ADDRESS,COMPANY_NAME,SUMMARY,SUBJECT |
|
|
不适合建索引的字段 |
描述备注 |
DESCRIPTION,REMARK,MEMO,DETAIL |
|
大字段 |
FILE_CONTENT,EMAIL_CONTENT |
如何知道SQL是否使用了正确的索引？
简单SQL可以根据索引使用语法规则判断，复杂的SQL不好办，判断SQL的响应时间是一种策略，但是这会受到数据量、主机负载及缓存等因素的影响，有时数据全在缓存里，可能全表访问的时间比索引访问时间还少。要准确知道索引是否正确使用，需要到数据库中查看SQL真实的执行计划，这个话题比较复杂，详见SQL执行计划专题介绍。
索引对DML(INSERT,UPDATE,DELETE)附加的开销有多少？
这个没有固定的比例，与每个表记录的大小及索引字段大小密切相关，以下是一个普通表测试数据，仅供参考：
索引对于Insert性能降低56%
索引对于Update性能降低47%
索引对于Delete性能降低29%
因此对于写IO压力比较大的系统，表的索引需要仔细评估必要性，另外索引也会占用一定的存储空间。
1.2、只通过索引访问数据
有些时候，我们只是访问表中的几个字段，并且字段内容较少，我们可以为这几个字段单独建立一个组合索引，这样就可以直接只通过访问索引就能得到数据，一般索引占用的磁盘空间比表小很多，所以这种方式可以大大减少磁盘IO开销。
select id,name from company where type='2'; 这里sql语句经常使用的话可以在type /id 、name字段上加组合索引。这样可以减少io开销。为什么组合索引上加上type是因为where后面的字段在索引中，这个组合索引才会有效。
如果这个SQL经常使用，我们可以在type,id,name上创建组合索引
create index my_comb_index on company(type,id,name);
有了这个组合索引后，SQL就可以直接通过my_comb_index索引返回数据，不需要访问company表。
还是拿字典举例：有一个需求，需要查询一本汉语字典中所有汉字的个数，如果我们的字典没有目录索引，那我们只能从字典内容里一个一个字计数，最后返回结果。如果我们有一个拼音目录，那就可以只访问拼音目录的汉字进行计数。如果一本字典有1000页，拼音目录有20页，那我们的数据访问成本相当于全表访问的50分之一。
切记，性能优化是无止境的，当性能可以满足需求时即可，不要过度优化。在实际数据库中我们不可能把每个SQL请求的字段都建在索引里，所以这种只通过索引访问数据的方法一般只用于核心应用，也就是那种对核心表访问量最高且查询字段数据量很少的查询。
1.3 查看索引的方法
show index from table（表名）
三、慢查询分析工具：pt-query-digest使用介绍
https://blog.csdn.net/sofia1217/article/details/49309297
pt-query-digest [OPTIONS] [FILES] [DSN]
–filter 对输入的慢查询按指定的字符串进行匹配过滤后再进行分析
–limit限制输出结果百分比或数量，默认值是20,即将最慢的20条语句输出，如果是50%则按总响应时间占比从大到小排序，输出到总和达到50%位置截止。
–host mysql服务器地址
–user mysql用户名
–password mysql用户密码
–history 将分析结果保存到表中，分析结果比较详细，下次再使用–history时，如果存在相同的语句，且查询所在的时间区间和历史表中的不同，则会记录到数据表中，可以通过查询同一CHECKSUM来比较某类型查询的历史变化。
–review 将分析结果保存到表中，这个分析只是对查询条件进行参数化，一个类型的查询一条记录，比较简单。当下次使用–review时，如果存在相同的语句分析，就不会记录到数据表中。
–output 分析结果输出类型，值可以是report(标准分析报告)、slowlog(Mysql slow log)、json、json-anon，一般使用report，以便于阅读。
–since 从什么时间开始分析，值为字符串，可以是指定的某个”yyyy-mm-dd [hh:mm:ss]”格式的时间点，也可以是简单的一个时间值：s(秒)、h(小时)、m(分钟)、d(天)，如12h就表示从12小时前开始统计。
–until 截止时间，配合—since可以分析一段时间内的慢查询。
B、用法示例
(1)直接分析慢查询文件:
pt-query-digest slow.log > slow_report.log
(2)分析最近12小时内的查询：
pt-query-digest --since=12h slow.log > slow_report2.log
(3)分析指定时间范围内的查询：
pt-query-digest slow.log --since '2014-04-17 09:30:00' --until '2014-04-17 10:00:00'> > slow_report3.log
(4)分析指含有select语句的慢查询
pt-query-digest--filter '$event->{fingerprint} =~ m/^select/i' slow.log> slow_report4.log
(5) 针对某个用户的慢查询
pt-query-digest--filter '($event->{user} || "") =~ m/^root/i' slow.log> slow_report5.log
(6) 查询所有所有的全表扫描或full join的慢查询
pt-query-digest--filter '(($event->{Full_scan} || "") eq "yes") ||(($event->{Full_join} || "") eq "yes")' slow.log> slow_report6.log
(7)把查询保存到query_review表
pt-query-digest --user=root –password=abc123 --review h=localhost,D=test,t=query_review--create-review-table slow.log
(8)把查询保存到query_history表
pt-query-digest --user=root –password=abc123 --review h=localhost,D=test,t=query_ history--create-review-table slow.log_20140401
pt-query-digest --user=root –password=abc123--review h=localhost,D=test,t=query_history--create-review-table slow.log_20140402
(9)通过tcpdump抓取mysql的tcp协议数据，然后再分析
tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt
pt-query-digest --type tcpdump mysql.tcp.txt> slow_report9.log
(10)分析binlog
mysqlbinlog mysql-bin.000093 > mysql-bin000093.sql
pt-query-digest --type=binlog mysql-bin000093.sql > slow_report10.log
(11)分析general log
pt-query-digest --type=genlog localhost.log > slow_report11.log
四、使用explain优化sql执行计划
SHOW INDEX FROM tbl_name [FROM db_name]
SQL执行计划
在mysql命令行中，执行explain命令可以看到sql执行计划，如下图所示：
ID：包含一组数字，表示查询中执行select子句或操作表的顺序
id相同，可以认为是一组，执行顺序由上至下
如果是子查询，id的序号会递增
id值越大优先级越高，越先被执行
select_type：表示查询中每个select子句的类型（简单 、复杂）
SIMPLE：查询中不包含子查询或者UNION
PRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY
SUBQUERY：在SELECT或WHERE列表中包含了子查询，该子查询被标记为SUBQUERY
DERIVED：在FROM列表中包含的子查询被标记为DERIVED（衍生）。若UNION包含在 FROM子句的子查询中，外层SELECT将被标记为DERIVED
UNION：若第二个SELECT出现在UNION之后，则被标记为UNION
UNION RESULT：从UNION表获取结果的SELECT被标记为UNION RESULTtable
输出的行所引用的表
type：表示MySQL在表中找到所需行的方式，又称“访问类型”
ALL：Full Table Scan， MySQL将遍历全表以找到匹配的行
index：Full Index Scan，index与ALL区别为index类型只遍历索引树
range：索引范围扫描，对索引的扫描开始于某一点，返回匹配值域的行，常见于between、<、>等的查询
ref：非唯一性索引扫描（普通索引），返回匹配某个单独值的所有行。常见于使用非唯一索引即唯一索引的非唯一前缀进行的查找
eq_ref：唯一性索引扫描（唯一索引），对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描
const、system：当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。如将主键置于where列表中，MySQL就能将该查询转换为一个常量。system是const类型的特例，当查询的表只有一行的情况下， 使用system
NULL：MySQL在优化过程中分解语句，执行时甚至不用访问表或索引possible_keys：指出MySQL能使用哪个索引在表中找到行，查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询使用。
key：显示MySQL在查询中实际使用的索引，若没有使用索引，显示为NULL。查询中若使用了覆盖索引，则该索引仅出现在key列表中。
key_len
表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。
row：表示MySQL根据表统计信息及索引选用情况，估算的找到所需的记录所需要读取的行数。
Extra
包含不适合在其他列中显示但十分重要的额外信息
Using index：该值表示相应的select操作中使用了覆盖索引（Covering Index）【注：MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件 包含所有满足查询需要的数据的索引称为 覆盖索引】
Using where：表示MySQL服务器在存储引擎受到记录后进行“后过滤”（Post-filter）,如果查询未能使用索引，Using where的作用只是提醒我们MySQL将用where子句来过滤结果集
Using temporary：表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询
Using filesort： MySQL中无法利用索引完成的排序操作称为“文件排序”
五、减少交互的次数
4.1 batch DML