索引优化
索引失效（应该避免的）：
- 出现全值匹配也就是type为All
- 违背了最佳左前缀法则（如果索引了多列，要遵守左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中间列）---带头大哥不能死，中间兄弟不能短
- 不在索引列上做任何操作（计算、函数、自动或者手动类型转换），会导致索引失效而转向全表扫描
- 存储引擎不能使用索引中范围条件右边的列，也就是范围之后全失效
- 尽量使用覆盖索引（索引列和查询列一致），减少select *
- mysql在使用不等于（!=或者<>）的时候无法使用索引会导致全表扫描
- is null，is not null也无法使用索引
- like以通配符开头('%abc...')mysql索引会失效变成全表扫描，如果业务上一定要写like %abc%那么如何解决全表扫描？（用覆盖索引，也就是你建的索引列和查询列一致，或部分一致，不能查询索引之外的字段，不然也会全表扫描。）
- 字符串不加单引号索引失效
- 少用or，用它来连接时索引失效
分析：
1、观察线上慢sql情况
2、开启慢查询日志，设置阈值，比如超过5秒钟就是慢sql，并将他抓取出来。
3、explain+慢sql分析
4、showprofile
5、sql数据库服务器的参数调优
查询优化
永远小表驱动大表
为排序使用索引：orderby优化
MySOL支持二种方式的排序：FileSort和Index。Index是MySQL扫描索引本身内部完成排序，所以效率会比较高。FileSort方式效率较低，在ORDER BY查询中，尽量使用Index方式排序，避免使用FileSort方式排序。所以我们在使用order by的时候， 把order by查询结果由:using filesort优化到using index。在没有办法使用Using Index的时候FileSort可以接受。其他情况尽量使用Index方式排序。
- orderby子句，尽量使用index方式排序，避免使用filesort方式排序，order by 满足两种情况会使用Index方式排序：order by语句使用索引最左前列，使用where子句与order by子句条件组合满足索引最左前列。
- 尽可能在索引上完成排序操作，遵照索引建的最佳左前缀
- 如果不在索引列上，filesort有两种算法：双路排序和单路排序。Mysql4.1之前是使用双路排序，字面意思就是两次扫描磁盘，最终得到数据，读取行指针和orderby列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值从列表中读取对应的数据输出。取一批数据，就要对磁盘进行两次扫描，众所周知，I\O非常耗时；所以在mysql4之后，出现了第二种改进的算法，就是单路排序；单路：从磁盘读取查询需要的所有列，按照orderby列在buffer对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO但是它会使用更多的空间，因为它把每一行都保存在内存中了。
- 优化策略：增大sort_buffer_size参数的设置、增大max_length_for_sort_data参数的设置
- 提高 Order By的速度：
Order by时 select*是一个大忌只 Query需要的字段,这点非常重要。在这里的影响是:
1.1当 Query的字段大小总和小于max_length_for_sort_data而且排序字段不是TEXT|BLOB类型时,会用改进后的算法——单路排序,否则用老算法一一多路排序。
1.2两种算法的数据都有可能超出 sort buffer的容量,超出之后,会创建tmp文件进行合并排序,导致多次O,但是用 单路排序算法的风险会更大一些所以要提高sort_buffer_size。
尝试提高 sort_buffer_ size
不管用哪种算法,提髙这个参数都会提高效率,当然,要根据系统的能力去提高,因为这个参数是针对每个进程的
3.尝试提高max_length_for_sort_data
提高这个参数,会增加用改进算法的概率。但是如果设的太高,数据总容量超出 sort_buffer_size的概率就增大,明显症状是高的磁盘ⅣO活动和低的处理器使用率
group by优化
- group by实质是先排序后进行分组，遵照索引建的最佳做前缀
- 当无法使用索引列，增大sort_buffer_size参数的设置、增大max_length_for_sort_data参数的设置
- where高于having，能写在where限定的条件就不要去having限定了
慢查询日志
一、慢查询日志定义
MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MvSQL中响应时间超过阀值的语句，具体指运行时间超过long_querytime值的SQL，则会被记录到慢查询日志中。
具体指运行时间超过longquerytime值的SQL，则会被记录到慢查询日志中。long querytime的默认值为10，意思是运行10秒以上的语句。
由他来查看哪些SQL超出了我们的最大忍耐时间值，比如一条sql执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒的sal，结合之前explain进行全面分析。
二、慢查询日志的应用
1.说明：
默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。
当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件
2.查看是否开启及如何开启
默认SHOW VARIABLES LIKE '%slow_query_log%';
默认情况下slow-query-log的值为OFF，表示慢查询日志是禁用的，可以通过设置slow_query_log的值来开启
开启setglobal slow_query_log=1;
使用setglobal slow_query_log=1;开启了慢查询日志只对当前数据库生效，如果MySQL重启后则会失效。
如果要永久生效，就必须修改配置文件mycnf(其它系统变量也是如此)
修改mycnf文件，[mysqld]下增加或修改参数
slow_query_log 和slow_query_iog_file后，然后重启MySQL服务器。也即将如下两行配置进mycnf文件
slow_query_log =1
slow_query_log_file=/var/lib/mysql/atguigu-slow.log
关于慢查询的参数slowquerylog_file，它指定慢查询日志文件的存放路径，系统默认会给一个缺省的文件
host_name-slow.log
(如果没有指定参数slow_query_log_file的话)
3.那么开启了慢查询日志后，什么样的SQL才会记录到慢查询日志里面呢?
这个是由参数long_query_time控制，
默认情况下long_query_time的值为10秒命令:
SHOW VARIABLES LIKE 'long query time%;
可以使用命令修改，也可以在my.cnf参数里面修改。
假如运行时间正好等于long-query-time的情况，并不会被记录下来。
也就是说，在mysql源码里是判断大于long-query-time，而非大于等于。
4.Case+
查看当前多少秒算慢：
SHOW VARIABLES LIKE'long_query_time%;
设置慢的阙值时间：set global long_query_time=3;
为什么设置后看不出变化?
需要重新连接或新开一个会话才能看到修改值。
SHOW VARIABLES LIKE 'long_query_time%';
show global variables like 'long_query_time;
记录慢SQL并后续分析
查询当前系统中有多少条慢查询记录
5.配置版
【mysqld】下配置:
slow_query_log=1;
slow_querylogfile=/var/lib/mysql/atguigu-slow.log
long_query_time=3;
log_output=FILE
三、日志分析工具mysqldumpslow
在生产环境中，如果要手工分析日志查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具 mysqldumpslow。
1.查看mysqldumpslow的帮助信息
s:是表示按照何种方式排序:
c访问次数
l:锁定时间
r:返回记录
t:查询时间
al:平均锁定时间
ar:平均返回记录数
at:平均查询时间
t;即为返回前面多少条的数据;
g:后边搭配一个正则匹配模式，大小写不敏感的;
2.工作常用参考
得到返回记录集最多的10个SQL
mysqldumpslow-sr-t10/var/lib/mysql/atguigu-slow.log
得到访问次数最多的10个SQL
mysqldumpslow-sc-t10/var/lib/mysql/atguigu-slow.log
得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow-st-t10-g"leftjoin"Nar/lib/mysql/atguigu-slowlog
另外建议在使用这些命令时结合|和more使用，否则有可能出现
爆屏
mysqldumpslow-sr-t10/Nar/lib/mysql/atguigu-slow.log|more
用Show Profile进行sql分析
一、Show Profile的定义
前情回顾：我们要进行数据的调优和排查，第一步先让故障重现
一般都是DBA或者运维工程师从监控系统里面收到了报站，系统变慢了。因为重要的系统都会有另外一套辅助的系统监控，这种监控系统慢于一个模块平均时间优化以后应该更快执行完毕，那么现在还是时间很长，所以程序还是有问题。
- Show Profile定义
是mysql提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优的测量。
四层结构连接、服务、引擎、存储，打一条sql过来完整的生命周期走一圈。
假设有一种命令，能够把我走的全部路径走一遍，能够告诉你每一个执行步骤，分别使用了多长时间。
默认情况下,参数处于关闭状态,并保持最近15次的运行结果。//show profile后台记录每一条sql
show profile 命令用于跟踪执行过的sql语句的资源消耗信息，可以帮助查看sql语句的执行情况，可以在做性能分析或者问题诊断的时候作为参考
二、Show Profile分析步骤
1.是否支持，看看当前的mysql版本是否支持
Show variables like 'profiling';
默认是关闭，使用前需要开启
或者:Show variables like 'profiling%';
2.开启功能，默认是关闭，使用前需要开启
show variables like 'profiling'
set profiling=on;
查看是否开启（on代表已经开启，off关闭）：
SHOW VARIABLES LIKE 'profiling';//运行结果如下图
示例：
Mysql>select * from tbl_emp;//运行结果如下
8 rows in set (0.05 sec)
Mysql> select * from tbl_emp e inner join tbl_dept on e.deptip=d.id;//运行结果如下图所示：
Error 1054(42s22):unknown column ‘d.id’ in ‘on clause’
Mysql> select * from tbl_emp e inner join tbl_dept d on e.deptid=d.id;
重复点击按钮就会重复发出一条sql，后台就会记录到每一条sql的运行。
- 运行SQL
执行 show profiles 命令；
Mysql>select*from emp group by id%10 limit 150000; //除数取余分组
运行代码显示结果如下图：
select*from emp group byid%20 order by5//按照20分组查询
运行代码显示结果如下图：
- 查看结果，show profiles;//查询系统上所有执行过的sql
Query_id是从开始到现在发过9条sql
Duration是执行的时间
query是执行命令的内容
- 诊断SQL，show profile cpu,block io for query上一步前面的问题SQL数字号码;
命令：Show profile type for query Query_ID
示例：
Mysql>show profile cpu,block io for query 3//执行结果如下图
显示了完成查询生命周期，每一个步骤用了多少时间
参数备注:
ALL --显示所有的开销信息
BLOCKIO --显示块10相关开销
CONTEXTSWITCHES--上下文切换相关开销
CPU --显示CPU相关开销信息
IPC --显示发送和接收相关开销信息
MEMORY --显示内存相关开销信息
PAGEFAULTS --显示页面错误相关开销信息
SOURCE --显示和Sourcefunction,Source file,Source line相关的开销信息
SWAPS --显示交换次数相关开销的信息
三、日常开发需要注意的结论
1、converting HEAP to MyISAM查询结果太大，内存都不够用了往磁盘上搬了。
2、Creating tmp table 创建临时表
为什么创建临时表比较难？
第一步新建临时表，第二步拷贝数据到程序表，第三步数据推送给主人以后还需要删掉临时表
拷贝数据到临时表
用完再删除
3、Copying to tmp table on disk把内存中临时表复制到磁盘，危险!!!
4、locked
出现以上四个问题，一定是耗损内存，降低速度。，必须要进行优化。
全局查询日志
配置启用：
注意：只允许测试环境，不允许生产环境开启，永远不要在生产环境开启这个功能
配置
在mysqI的my.cnf中，设置如下：
#开启
General_log_file=/path/logfile
#输出格式
Log_output=FILE
编码启用：
命令
Set global general_log=1;
Query OK, 0 rows affected (0.00 sec)
Set global log_output=’TABLE’,
Query OK, 0 rows affected (0.00 sec)
此后，你所编写的sql语句，
将会记录到mysql库里的general_log表，可以用下面的命令
查看
Select*from mysql.general_log;
它会记录下你的sql
mysq1>setglobal general_1og= l ;
Query OK, 0 rowS affected (O.04sec)
mysq1>setgloba1 log_output ='TABLE＂; Query OK,0 rOWS affected (0.00sec)
mysq1>
系统的定量分析，开启以后所有的记录被记录过，偷偷地把sql记录下来了，什么时间段发生了什么sql帮助我们定位、收拢、汇集。
数据库锁理论概述
一、定义
MySqI锁机制，有利有弊，不加锁，会争抢引起浑沦；数据库需要进行数据的备份时，防止别人篡改数据，加上锁防止改动，一分为二的看待。
定义：
锁是计算机协调多个通程成线程并发访问某一资源的机制,（防止争抢）在数据库中,除传统的计算资源(如CPU、RAM、I/O等)的争用以外。数据也是一种供许多用户共享的。
如何保证数据并发访问的一政性,有效性是所有数想库必须鲜决的一个问题:冲突也量响数据库井发访问性能的一个重要国置。
从这个角度来说精确度对数据库而言显得尤其重要,也更加复杂。
举例说明，我们到淘宝上买一件商品，商品只有一件库存，这个时候如果还有另一个人买，那么如何解决时你买到还是另一个人买到的问题？
这里肯定要用到事务，先从库存表中取出物品数量，然后插入订单，付款后插入付款表信息，更新商品数量。
在这个过程中，使用锁可以对有限的资源进行保护，解决隔离和并发的矛盾。
二、锁的分类：
从数据操作维度：读锁、写锁
从数据操作力度：表锁、行锁
读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。
写锁（排它锁）：当前写操作没有完成前，它会阻断其他写锁和读锁。
开锁、加锁速度、死锁、粒度、并发性能，只能就具体应用特点来说哪种锁更合适。
MylSAM在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行增删改操作前，会自动给MySQL的表级锁有两种模式：
表共享读锁（Table Read Lock）
表独占写锁（Table Write Lock）
锁类型 |
可否兼容 |
读锁 |
写锁 |
读锁 |
是 |
是 |
否 |
写锁 |
是 |
否 |
否 |
结论：
结合上表，所以对MylSAM表进行操作，会有以下情况：
- 对MylSAM表的读操作（加读锁），不会阻塞其他进程对同一类表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其他进程的写操作。
- 对MylSAM表的写操作（写读锁），会阻塞其他进程对同一类表的读和写操作，只有当写锁释放后，才会执行其他进程的读写操作。
简而言之，就是读锁会阻塞写，但是不会阻塞读，而写锁则会把读和写都堵塞。
3.【看看哪些表被加锁了】
mysql>show open tables;
【如何分析表锁定】
可以通过检查
table_locks_waite和table_locks_immediate状态变量来分析系统上的表锁定:
SQL:show status like‘table%’；
4.【如何分析表锁定】
可以通过检查table locks walted和table locks immediate状态变量来分析系统上的表锁定:
SQL：show status like ‘table%’；
这里有两个状态变量记录MySQL内部表级锁定的情况,两个变量
说明如下:
Table_locks_immediate:
产生表级锁定的次数。表示可以立即获取顿的查询次数,每立即
获取锁值
Table_locks_waited:
出现表级锁定争用而发生等待的次数(不能立即获取锁的次数每等待一次恒值加1)，此值高则说明存在着较严重的表级锁争用情况。
此外，MyISAM的读写锁调度是写优先，这也是myisam不适合做写为主表的引擎，因为写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永久阻塞。
一、行锁简介
偏向与InnoDB存储引擎，开销大，加锁慢;会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。InnoDB与MyISAM的最大不同有两点：一是支持事务，二是采用了行级锁。
1.事务及其ACID属性
事务是由一组SQL语句组成的運辑处理单元,事务具有以下4个属性,通常称为事务的ACID属性。
原子性(Atomicity):事务是一个原子操作单元,其对数据的修改,要么全都执行,要么全都不执行。
一致性(Consistent):在事务开始和完成时,数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改,以保持数据的完整性,事务结束时,所有的内部数据结构(如B树素引或双向链表)也都必须是正确的。
隔离性(Isolation),数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的“独立”环境执行。这意味者事务处理过程中的中间状态对外部是不可见的,反之亦然。
持久性(Durable),事务完成之后,它对于数据的修改是永久性的,即使出现系统故障也能够保持。
2.并发事务处理带来的问题
1更新丢失
当两个或多个事务选择同一行。然后基于最初选定的值更新该行时。由于每个事务都不知道其他事务的存在,就会发生丢先更新问题一一最后的更新覆盖了由其他事务所做的更新。
例如,两个程序员修改同一java文件。每程序员独立地更改其副本,然后保存更改后的副本。
这样就覆盖了原始文档,最后保存其更改副本的编辑人员覆盖前一个程序员所做的更改。
如果在一个程序员完成并提交事务之前,另一个程序员不能访同同一文件,则可避免此问题。
2脏读：
一个事务正在对一条记录做修改,在这个事务完成并提交前,者条记量的数据就处于不一致状态；这时，另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些“脏”数据。并据此做进一步的处里,就会产生未提交的数据依赖关系。这种现象被形象地叫做“脏读”。
一句话:事务A读取到了事务B已修改但尚未提交的的数据,还在这个数据基础上做了操作。此时,如果B事务回滚，A读取的数据无效,不符合一致性要求。
3不可重复读：
一个事务在读取某些后某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变、或某些记录已经被删除了。这种现象就叫做“不可重复读”。
一句话：事务A读取到了事务B已经提交的修改数据，不符合隔离性。
4幻读
一个事务按相同的查询条件重新读取以前检索的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读”。
一句话：事务A读取到了事务B提交的新增数据，不符合隔离性。
脏读是事务B里面修改了数据，
幻读是事务B里面新增了数据。
二、事物隔离级别：
“脏读”、“不可重复读”和“幻读”，其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决。
读数据一致性及允许的并发副作用隔离级别 |
读数据一致性 |
脏读 |
不可重复读 |
幻读 |
未提交读（Read uncommitted） |
最低级别，只能保证不读取无理数损坏的数据 |
是 |
是 |
是 |
已提交读(Read committed) |
语句级 |
否 |
是 |
是 |
可重复读(Repeatable read) |
事务级 |
否 |
否 |
是 |
可序列化(Serializable) |
最高级别，事务级 |
否 |
否 |
否 |
数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使事务在一定程度上“串行化”进行，这显然与“并发”是矛盾的。
同时，不同的应用对读一致性和事务隔离程度的要求也是不同的，比如许多应用对“不可重复读”和“幻读”并不敏感，可能更关心数据并发访问的能力。
行锁总结
一、案例结论
Innodb存储引擎由于实现了行级锁定（存储引擎和锁的行为方式），虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些， 但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的。
当系统并发量较高的时候，因为innodb的高并发，使得Innodb的整体性当与MyISAM进行比较时，前者体现出了较为明显的优势。
但是，Innodb的行级锁定同样存在其脆弱的一面，当使用不当时，可能会让Innodb的整体性能表现不仅不能比MyISAM高甚至可能会更差，较为明显具有特征的，例如行锁变表锁造成走向了得不偿失的地步。
二、行锁定的分析
通过检查InnoDBrow_lock状态变量来分析系统上的行锁的争夺情况
1.对各个状态的说明
mysql>show status like 'innodb_row_lock%';
Innodb_row_lock_current_waits:当前正在等待锁定的数量;
Innodb_row_lock_time:从系统启动到现在锁定总时间长度;
Innodb_row_lock_time_avg:每次等待所花平均时间;
Innodb_row_lock_time_max:从系统启动到现在等待最长的一次所花的时间;
Innodb_ row_lock_waits:系统启动后到现在总共等待的次数;
对于这5个状态变量，比较重要的如下
Innodb_row_lock_time_avg (等待平均时长)，
Innodb_row_lock_waits (等待总次数)
Innodb_row_lock_time(等待总时长)
尤其是当等待次数很高，而且每次等待时长也不小的时候，需要分析系统中为什么会有如此多的等待，然后根据分析结果着手指定优化计划。
在mysq出错的情况下，有可能是客户端连接工具的问题。例如session-3重连后便恢复正常。
诸如mysq一类的客户端工具更改配置参数后，最好重启重连系统，此为最为保险的方法。
而当部分工具更改完毕后仍为原样或缓存、保存特定关系，前一个方法无法起到作用则可以使用新开窗口的方式进行连接。
2.实例
session-1
mysql>show status like 'innodb_row_lock%'; //鼠标右键操作
+-------------------------------------------+-----------+
| Variable_name | 0 |
+-------------------------------------------+-----------+
|Innodb_row_lock_current_waits | 72038 |
|Innodb_row_lock_time | 18009 |
|Innodb_row_lock_time_avg | 29740 |
|Innodb_ row_lock_waits | 4 |
+-------------------------------------------+-----------+
5 rows in set (0.00 sec)
前三列为时间类，当锁定时间越多，则时间花费越长。由上述实例结果可得，当前无锁，接下来分别为锁定总时间长度；等待所花平均时间；最长的一次等待时间；启动后到现在总共等待的时间。
其中，最为重要的部分为等待锁定的数量与启动后到现在总共等待的时间。
三、优化建议
（1）尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁。例如varchar类型必须添加单引号。
（2）合理设计索引，尽量缩小锁的范围尽可能较少检索条件，避免间隙锁。例如写范围查询时需要时刻注意间隙锁所带来的某些负面危害。
（3）尽量控制事务大小，减少锁定资源量和时间长度
（4）尽可能低级别事务隔离
