MySQL数据库进阶（索引，锁，SQL优化）

Mysql优化

（1）：发现问题
（2）：慢查询日志抓取
（3）：explain分析SQL
（4）：showProfile详解
（5）：mysql服务器参数调优

索引

什么是索引？

1. 索引是一种排好序的快速查找的数据结构。
2. 它帮助数据库高效的查询数据 在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式指向数据,这样就可以在这些数据结构上实现高效的查找算法。

哪些情况需要创建索引？

1. 主键自动创建唯一索引
2. 频繁作为查询条件的字段应该创建索引
3. 查询中与其他表关联的字段，外键关系建立索引
4. 频繁更新的字段不是花创建索引

为什么：因为每次更新了不单单是更新了记录（数据），还会更新索引（索引所维护的数据结构）

5. Where条件里用不到的字段不创建索引
6. 单键/组合索引的选择问题，Who?(在高并发下倾向创建组合索引)
7. 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
8. 查询中统计或者分组字段

哪些情况不需要创建索引？

1. 表记录太少（300W条记录以上的表需要建索引）
2. 经常增删改的表

为什么？提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。
因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件

9. 数据重复且分布平均的表字段，如（性别字段，状态0、1字段）

注意：如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果
因此应该只为最经常查询和最经常排序的数据列建立索引。

索引会失效的情况

失效原因：范围以后会导致索引失效
因为第一条sql中存在范围条件：where
comments >1 会导致后面views字段的索引失效，所以views会不好找，mysql会自动使用filesort排序导致sql性能下降
解决方法 因为comments >1类型是range（范围）那就跳过这个字段只给category_id和views字段建立索引

explain

explain（执行计划），使用explain关键字可以模拟优化器执行sql查询语句，从而知道MySQL是如何处理sql语句。explain主要用于分析查询语句或表结构的性能瓶颈。

explain的作用

通过explain+sql语句可以知道如下内容：

①表的读取顺序。（对应id）

②数据读取操作的操作类型。（对应select_type）

③哪些索引可以使用。（对应possible_keys）

④哪些索引被实际使用。（对应key）

⑤表直接的引用。（对应ref）

⑥每张表有多少行被优化器查询。（对应rows）

explain（id）

id:select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序，该字段通常与table字段搭配来分析。id相同，执行顺序从上到下。 id不同，如果是子查询，id的序号会递增，id值越大执行优先级越高。

explain（select_type）

查询的类型，主要用于区别普通查询、联合查询、子查询等复杂的查询。其值主要有六个：
#1.SIMPLE

简单的select查询，查询中不包含子查询或union查询。

#2.PRIMARY

查询中若包含任何复杂的子部分，最外层查询为PRIMARY，也就是最后加载的就是PRIMARY。

#3.SUBQUERY

在select或where列表中包含了子查询，就为被标记为SUBQUERY。

#4.DERIVED

在from列表中包含的子查询会被标记为DERIVED(衍生)，MySQL会递归执行这些子查询，将结果放在临时表中。

#5.UNION

若第二个select出现在union后，则被标记为UNION，若union包含在from子句的子查询中，外层select将被标记为DERIVED。

#6.UNION RESULT

从union表获取结果的select。

explain（table）

显示sql操作属于哪张表的。

explain（partitions）

官方定义为The matching partitions（匹配的分区），该字段应该是看table所在的分区吧。值为NULL表示表未被分区。

explain（type）

表示查询所使用的访问类型，type的值主要有八种，该值表示查询的sql语句好坏，从最好到最差依次为：system>const>eq_ref>ref>range>index>ALL。
#1.system
表只有一行记录（等于系统表），是const的特例类型，平时不会出现，可以忽略不计。

#2.const
表示通过一次索引就找到了结果，常出现于primary key或unique索引。因为只匹配一行数据，所以查询非常快。如将主键置于where条件中，MySQL就能将查询转换为一个常量。

#3.eq_ref
唯一索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见主键或唯一索引扫描。

#4.ref
非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，返回匹配某值（某条件）的多行值，属于查找和扫描的混合体。

#5.range
只检索给定范围的行，使用一个索引来检索行，可以在key列中查看使用的索引，一般出现在where语句的条件中，如使用between、>、<、in等查询。

#6.index
全索引扫描，index和ALL的区别：index只遍历索引树，通常比ALL快，因为索引文件通常比数据文件小。虽说index和ALL都是全表扫描，但是index是从索引中读取，ALL是从磁盘中读取。

#7.ALL
全表扫描。

注：一般来说，需保证查询至少达到range级别，最好能达到ref。

explain（possible_keys,key,key_len）

possible_keys：显示可能应用在表中的索引，可能一个或多个。查询涉及到的字段若存在索引，则该索引将被列出，但不一定被查询实际使用。

key：实际中使用的索引，如为NULL，则表示未使用索引。若查询中使用了覆盖索引，则该索引和查询的select字段重叠。

key_len：表示索引中所使用的字节数，可通过该列计算查询中使用的索引长度。在不损失精确性的情况下，长度越短越好。key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，并不是通过表内检索出的。

简单理解：possible_keys表示理论上可能用到的索引，key表示实际中使用的索引。

explain（rows）

根据表统计信息及索引选用情况大致估算出找到所需记录所要读取的行数。当然该值越小越好。

explain（filtered）

百分比值，表示存储引擎返回的数据经过滤后，剩下多少满足查询条件记录数量的比例。

explain（Extra）

显示十分重要的额外信息。其取值有以下几个：
#1.Using filesort
Using filesort表明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。mysql中无法利用索引完成的排序操作称为“文件排序”。

解决： 出现Using filesort就非常危险了，在数据量非常大的时候几乎“九死一生”。出现Using filesort尽快优化sql语句。

deptname字段未建索引的情况。

为deptname字段创建索引后。

#2.Using temporary
使用了临时表保存中间结果，常见于排序order by和分组查询group by。非常危险，“十死无生”，急需优化。

将tb_emp中name的索引先删除，出现如下图结果，非常烂，Using filesort和Using temporary，“十死无生”。
为name字段创建索引后。

#3.Using index

表明相应的select操作中使用了覆盖索引，避免访问表的额数据行，效率不错。

如果同时出现了Using where，表明索引被用来执行索引键值的查找。（where deptid=1）

如果没有同时出现Using where，表明索引用来读取数据而非执行查找动作。

删除tb_emp表中name和deptid字段的单独索引，创建复合索引。

从这里给出覆盖索引的定义：select的数据列只从索引中就能取得数据，不必读取数据行。通过上面的例子理解：创建了（name，deptid）的复合索引，查询的时候也使用复合索引或部分，这就形成了覆盖索引。简记：查询使用复合索引，并且查询的列就是索引列，不能多，个数需对应。

使用优先级Using index>Using filesort（九死一生）>Using temporary（十死无生）。也就说出现后面两项表明sql语句是非常烂的，急需优化！！！

explain总结

explain（执行计划）包含的信息十分的丰富，着重关注以下几个字段信息。

①id，select子句或表执行顺序，id相同，从上到下执行，id不同，id值越大，执行优先级越高。

②type，type主要取值及其表示sql的好坏程度（由好到差排序）：system>const>eq_ref>ref>range>index>ALL。保证range，最好到ref。

③key，实际被使用的索引列。

④ref，关联的库名，表名字段名，常量等值查询，显示为const，如果为连接查询，显示关联的库名，表名字段名。

⑤Extra，额外信息，使用优先级Using index>Using filesort（九死一生）>Using temporary（十死无生）。

着重关注上述五个字段信息，对日常生产过程中调优十分有用。

索引的建立和优化

索引两表优化案例

两表在没有索引的情况下都是ALL全表扫描

抛出疑问：给class.card加索引还是给book.card加索引性能会更好？
分别给左表跟右表建立索引查看效果
左表加索引：

从rows跟Tyoe可以看出两张表还是全表扫描

给右表book加索引

从rows跟type可以看出type是ref级别且查询行数是20+1=21行比左表加索引性能高

得出结论

左连接跟右连接类似因为RIGHT JOIN 条件用于确定如何从左表搜索行，右边一定都有，所以左边是我们的关键点，一定要创建索引。

总之不管左右连接，在他的另一边一定要创建索引（说白了就是相反建）

索引三表优化案例

三表连接查询

EXPLAIN 查看没有任何索引情况下的结果

尝试给Phone表Book表建立索引

结果：后两行的type都是ref且总rows优化很好，效果不错。因此索引最好设置在需要经常查询的字段中。

如何避免索引失效

先建张员工表，简历(name,age,position)这三个字段的联合索引

常见会让索引失效的原因

1、全值匹配 2、遵循左前缀法则

左前缀法则只针对Where列 不针对select列


全值匹配：

结果：全表扫描，没有遵循最左前缀原则，即：name字段值不确定，age是无序的，无法走索引

结果：从key_len可以看出这条SQL只是部分使用到了索引只使用到了name字段的索引，如果name跟pose两个索引都用到key_len应该是78，所以得出结论要根据复合索引的顺序（name,age.pos）不能跳过索引中的列

3、不要在索引列上做操作

第二句SQL用了LEFT函数对索引字段name做了计算导致索引失效了

4、不能使用索引中范围条件右边的列

第二条SQL name跟age用到了但是age是范围条件所以pos失效了
结果：范围条件右边的索引列、全失效如>=,<,between in 等等

5、尽量使用覆盖索引（索引列和查询列一致），减少select *

6、mysql在使用不等于（！=或<>）的时候会导致索引失效

不等于（！=或<>）本质上也是范围条件

7、is null，is not null 也会导致索引失效

8、like以通配符开头（%abc…’）会导致索引失效

like ‘%abc%’ 实际上也是一种范围查找

提出问题：如何解决 '%abc%'索引失效的问题？
解决方法：
1.建立索引


可以看出索引生效了，因为我们建立了name，age的复合索引SQL查询中又只查询这两列就不会全表扫描

总结： 如果必须要‘%abc%’又要索引不失效，我们就可以用覆盖索引（建的索引列能覆盖我们查的列）如建了name，age列索引，我们查name列这样就覆盖了。

id天生就是主键索引

9、字符串不加单引号索引失效

原因： varchar类型不加单引号Mysql会自动的进行一个类型转换类似：new String 所以会导致索引失效（参考第三条索引失效原因）

10、少用or，用它来连接时会索引失效

or本质上也是范围类似 in

mysql5.7可以用or不会导致索引失效

最后：Mysql版本不同索引失效可能也不同，实际开发中用Explain来看索引是否生效

在5.7中 or ，is null 索引都未失效

orderby 排序优化

order by的两种排序方式

MySQL支持两种方式的排序，FileSort和Index，Index效率高，它指MySQL扫描索引本身完成排序。FileSort方式效率低。

Order by 满足两种情况，会使用Index方式排序

1. order by 语句使用索引最左前列
2. 使用wherr子句与Order by 子句条件列组合满足索引最左前列
3. order by a desc，b desc， c desc 必须同升或同降否则会索引失效filesort排序

order by子句，尽量使用index方式排序，避免使用FileSort方式排序，尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀
如果不在索引列上，filesort有两种算法：mysql就要启动双路排序和单路排序

filesort的两种排序算法

1. 双路排序： mysql4.1之前是使用双路排序，字面意思就是两次扫描磁盘，最终得到数据，读取行指针和orderby列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取数据。
   从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段。
   mysql4.1之后出现了第二种改进的算法就是单路排序
2. **单路排序：**从磁盘读取查询需要的所有列，按照orderby列在buffer对他们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据，并且把随机IO变成了顺序IO，但是它会使用更多的空间，因为它把每一行都保存在内存中了。
   单路的问题
   如果读取的数据比buffer容量还要大一次放不下就会再进行
   
   单路的优化策略
   

提高Order by的速度

排序索引失效的一些案例

group by优化

其他的均和order by优化一致

慢查询日志

查看慢查询设置

SHOW VARIABLES LIKE '%slow_query_log%'

slow_query_log ：慢查询日志是否开启状态
slow_query_log_file：执行速度慢被记录的SQL写入该文件

默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。
当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询或多或少会带来一些性能上的影响。慢查询日志支持将日志记录写入文件。

开启慢查询日志

set global slow_query_log = 1;

使用set global slow_query_log = 1;开启了慢查询日志只对当前数据库生效，如果MySQL重启后则失效。

不建议长期开启慢查询日志，会影响性能，一般在测试环境下开启

设置慢查询阙值

查看当前多少秒算慢：

SHOW VARIABLES LIKE 'long_query_time%';

设置慢的阙值时间：

set global long_query_time=3

刚设置重新查看不会发生改变，需要重新连接或新开一个会话才能看到修改值
SHOW global VARIABLES LIKE ‘long_query_time’;

日志分析工具 mysqldumpslow

帮助指令

mysqldumpslow --help

show Profile SQL执行详解

show profile 记录了我们SQL从连接到结束的生命周期
默认情况下，参数处于关闭状态，并保存最近15次的运行结果

开启showProfile

1. 查看当前版本是否支持

Show variables like 'profiling';

或者

Show variables like 'profiling%';

2. 开启功能

set profiling = on;

开启功能后会记录我们的查询
输入：

show profiles 可以看到被记录的查询

show profile cpu,block io for query 上一步的Query_ID ;
除了看cpu和block io还有以下这些参数：

status状态列出现以下四个状态比较危险

数据库锁的理论概述

锁的分类

锁分类：

1. 从对数据操作的类型（读\写）分为读锁（共享锁）和写锁（排它锁）
   读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。
   写锁（排它锁）：当前写操作没有完成前，他会阻断其他写锁和读锁。
2. 从对数据操作的粒度分为表锁和行锁
   表锁：偏向读锁
   行锁：偏向写锁

手动增加表锁

表锁偏向MyISAM引擎，对表进行增删改操作时自动上写锁导致其他连接不能对该表进行任何操作

lock table 表名字 read（write），表名字2 read（write），其他;

查看哪些表上加过的锁（In_use）字段如果是0就是没锁

show open tables

给mylock表上读锁，给book上写锁

lock table mylock read,book write;

释放锁

unlock tables;

加读锁，读阻塞例子

session_1 和session_2是两个连接

session_1给mylock表加了读锁
session_1可以对mylock可以读但不可以写（增删改）也不能对其他表（不管其他表有没有锁）进行读写操作因为mylock表的锁还未释放
session_2可以对mylock读操作，但如果对mylock进行写操作会一直等待session_1对mylock释放锁后才执行写操作，session_2可以对其他表进行读写（增删改查）。

加写锁，写阻塞例子

session-1 对mylock表加写锁

lock table mylock write;

分析：

可以看到session-1对mylock加了写锁 session-1可以对mylock表读写但不能对其他表进行读写操作，因为mylock表的锁还没释放
session-2也不能对mylock表进行读写

案例总结

总而言之：读锁会阻塞写，但是不会阻塞读，而写锁则会把读和写都堵塞。

手动增加行锁

行锁的前提是：使用索引，并且要有事务
在对一行数据进行upadate时innodb表会自动对该行上行锁

行锁的基本演示

建表

先给两列字段加索引

行锁互不影响：

索引失效导致行锁变表锁：

什么是间隙锁：

范围是a>1 and a<5 也就是2、3、4 但我们并没有2这条记录，讲道理另一个连接是可以插入a=2的记录
但是因为间隙锁的原因江2、3、4这三条都上了锁导致session_2的插入操作阻塞了

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁;对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁(Next-Key锁）。

间隙锁的危害

因为Query执行过程中通过过范围查找的话，他会锁定整个范围内所有的索引键值，即使这个键值并不存在。
间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害

间隙锁的好处

间隙锁完美的解决了幻读。

如何锁定一行？

这样加的是排它锁

行锁总结

Innodb存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些，但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的。当系统并发量较高的时候，nodb的整体性能和MylSAM相比就会有比较明显的优势了。
但是，Innodb的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让Innodb的整体性能表现不仅不能比MyISAM高，甚至可能会更差。（例如：索引失效行锁变表锁）

优化建议

1. 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁。
2. 合理设计索引，尽量缩小锁的范围尽可能较少检索条件，避免间隙锁
3. 合理设计索引，尽量缩小锁的范围尽可能较少检索条件，避免间隙锁
4. 尽量控制事务大小，减少锁定资源量和时间长度
5. 尽量控制事务大小，减少锁定资源量和时间长度