mysql基础优化-explain的使用-mysql死锁

MySQL的优化

主要包括三个方面，首先是SQL语句的优化，其次是表结构的优化（这里主要指索引的优化），最后是服务器配置的优化。

一．SQL语句的优化

• 在 where 及 order by 涉及的列上尽量使用索引。
• 尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
• 尽量避免在 where 子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描。
• 尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描。

　　例如：select a from tb where b=10 or b=20   可以修改为

　　select a from tb where b=10

　　union all

　　select a from tb where b=20

• 在使用Union操作符时，应该考虑是否可以使用Union ALL来代替，因为Union操作符在进行结果合并时，会对产生的结果进行排序运算，删除重复记录，对于没有该需求的应用应使用Union ALL，后者仅仅只是将结果合并返回，能大幅度提高性能。
• 尽量别使用like '%abc%',前面的%最好别使用，否则将导致全表扫描。
• 在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。
• 对于区分度不大的字段，不要建立索引，不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex，其中的男女几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用。
• 不要在 where 子句中的“=”左边进行函数、算术运算或其他表达式运算，否则系统将可能无法正确使用索引。

　　　　例如：select a from tb where b/2=100  应该为 select a from tb where b=100*2

• Select语句中尽量 避免使用“*”，因为在SQL语句在解析的过程中，会将“*”转换成所有列的列名，而这个工作是通过查询数据字典完成的，有一定的开销。需要哪些字段列出哪些即可。
• 尽量使用>=操作符代替>操作符。

例如：select a from tb where b > 3  该语句应该替换成

select a from tb where b >= 4

两个语句的执行结果是一样的，但是性能却不同，后者更加高效，因为前者在执行时，首先会去找等于3的记录，然后向前扫描，而后者直接定位到等于4的记录。

• 尽量使用EXIST和NOT EXIST代替 IN和NOT IN，因为后者很有可能导致全表扫描放弃使用索引。

例如：select num from tb where num in(select num from b)  应该改成

select num from a where exists(select 1 from b where b.num=a.num)

二．表结构的优化（这里主要指索引的优化）

• 对于Innodb表，虽然如果用户不指定主键，系统会自动生成一个主键列，但是自动产生的主键列有多个问题1). 性能不足，无法使用cache读取；2). 并发不足，系统所有无主键表，共用一个全局的Auto_Increment列。因此，InnoDB的所有表，在建表同时必须指定主键。
• 排序字段一般要建立索引。
• 分组统计字段一般要建立索引。
• 连接查询的连接字段应该建立索引。
• 大的文本字段或者BLOB字段，不要建立索引。
• 区分度不大的字段，不要建立索引。
• 一个字段只需建一种索引即可，无需建立了唯一索引，又建立INDEX索引。

三．MySQL服务器配置优化

• MySQL服务器有慢连接日志，可以将超过一定时间间隔和不使用索引的查询语句记录下来方便开发人员跟踪，可以通过设置slow_query_log=ON/OFF打开和关闭慢连接日志功能，slow_query_log_file设置慢连接日志的文件名，long_query_time设置超时时间，单位是ms,注意慢连接日志MySQL默认是关闭的。
• MySQL有查询缓存的功能，服务器会保存查询语句和相应的返回结果来减少相同的查询造成的服务器开销，可以通过设置query_cache_size设置查询缓存的大小，0表示关闭查询缓存，但是值得注意的是，一旦该表有更新，则所有的查询缓存都会失效，默认情况下，MySQL是关闭查询缓存的。

explain作用

EXPLAIN :模拟Mysql优化器是如何执行SQL查询语句的，从而知道Mysql是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。

1.id

select查询的序列号,相同的话执行顺序就是由上而下。如果是子查询，id序号会自增，id值越大优先级就越高，越先被执行。

2.select_type

select查询的类型，主要是区别普通查询和联合查询、子查询之类的复杂查询。

• SIMPLE:简单的select 查询，SQL中不包含子查询或者UNION。
• PRIMARY:查询中包含复杂的子查询部分，最外层查询被标记为PRIMARY
• SUBQUERY:在select 或者WHERE 列表中包含了子查询
• DERIVED:在FROM列表中包含的子查询会被标记为DERIVED(衍生表)，MYSQL会递归执行这些子查询，把结果集放到零时表中。
• UNION:如果第二个SELECT 出现在UNION之后，则被标记位UNION；如果UNION包含在FROM子句的子查询中，则外层SELECT 将被标记为DERIVED
• UNION RESULT:从UNION表获取结果的select

3.table

输出的行所引用的表。

4.type

联合查询所使用的类型。是较为重要的一个指标，结果值从好到坏依次是：system > const > eq_ref > ref > range > index > ALL

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

• system:表只有一条记录(等于系统表),这是const类型的特例，平时业务中不会出现。
• const:通过索引一次查到数据，该类型主要用于比较primary key 或者unique 索引，因为只匹配一行数据，所以很快;如果将主键置于WHERE语句后面，Mysql就能将该查询转换为一个常量。
• eq_ref:唯一索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或者唯一索引扫描。
• ref:非唯一索引扫描，返回匹配某个单独值得所有行，本质上是一种索引访问，它返回所有匹配某个单独值的行，就是说它可能会找到多条符合条件的数据，所以他是查找与扫描的混合体。
• range：只检索给定范围的行，使用一个索引来选着行。key列显示使用了哪个索引。一般在你的WHERE 语句中出现between 、< 、> 、in 等查询，这种给定范围扫描比全表扫描要好。因为他只需要开始于索引的某一点，而结束于另一点，不用扫描全部索引。
• index：FUll Index Scan 扫描遍历索引树(扫描全表的索引，从索引中获取数据)。
• ALL 全表扫描 从磁盘中获取数据 百万级别的数据ALL类型的数据尽量优化。

6.possible_keys

指出MySQL能使用哪个索引在该表中找到行。如果是空的，没有相关的索引。这时要提高性能，可通过检验WHERE子句，看是否引用某些字段，或者检查字段不是适合索引。

7.key

显示MySQL实际决定使用的键。如果没有索引被选择，键是NULL。

8.key_len

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

9.Ref

显示哪个字段或常数与key一起被使用。

10.Rows

这个数表示mysql要遍历多少数据才能找到，在innodb上是不准确的。而且是大致估算值。

11.Extra

扩展属性

• Using filesort(文件排序)：mysql无法按照表内既定的索引顺序进行读取。
• Using temporary:Mysql使用了临时表保存中间结果，常见于排序order by 和分组查询 group by。
• Using index 表示相应的select 操作使用了覆盖索引，避免访问了表的数据行，效率不错。如果同时出现Using where ，表明索引被用来执行索引键值的查找。如果没有同时出现using where 表明索引用来读取数据而非执行查找动作。
• Using where 查找
• Using join buffer ：表示当前sql使用了连接缓存。
• impossible where ：where 字句 总是false ，mysql 无法获取数据行。

死锁问题

数据库锁死会导致很多问题，比如程序无法执行，服务器卡顿等等。一般是多人操作的情况，例如频繁的扣减库存的时候。

　大多数情况下，服务器锁死的时候会有报错信息记录下来：

　　Deadlock found when trying to get lock

　但是这个信息对于绝大多数情况来说，只是了解一下这次数据库报错是因为锁导致的，而无法准确定位。

　在并发的情况下，当前来看比较容易出现的死锁情况是以下这种：

线程1修改货品A的库存，然后修改货品B的库存

线程2修改货品B的库存，然后修改货品A的库存

　在没有强制执行顺序的情况下，两个线程操作时均开启了事务，在执行完第一步之后，线程1修改了A的库存，准备修改B的库存，而线程2修改了B的库存，准备修改A的库存。但是此时线程1准备修改的B库存被线程2锁住，而线程2准备修改的A库存被线程1锁住。

在相互交叉的情况下，两个线程都无法继续往下走，因此就出现了死锁的情况。这也是目前主要需要避免的问题。

针对这种情况的调整很简单，只需要增加一个排序操作，要求都必须按顺序去修改，比如说线程2被排序为先修改A的库存然后修改B的库存，这就不会出现问题。

　但是有时候直接看业务代码有时候不能一下找到是哪导致死锁。

　这时候就需要在数据库中执行show engine innodb status来查看一些信息。

　这个操作会显示数据库innodb引擎最近的运行情况,

　其中会显示最后出现的两条锁死的sql代码，通过查询这些代码能够快速的定位问题所在位置，然后进行修改。

然后我们可以看一下死锁的进程：

show processlist;

找出产生死锁进程的ID，然后Kill掉。

Kill processid;

　　在MySQL中，行级锁并不是直接锁记录，而是锁索引。索引分为主键索引和非主键索引两种，如果一条sql语句操作了主键索引，MySQL就会锁定这条主键索引；如果一条语句操作了非主键索引，MySQL会先锁定该非主键索引，再锁定相关的主键索引。

　　如果两条更新语句，一条使用了非主键索引，更新时会先锁定非主键索引，在锁定主键索引，同时另一条语句使用了主键索引，但是更新的字段不是主键，那么还会锁定部分非主键索引，那么第一条锁定非主键索引，等待主键索引，第二天锁定主键索引，等待非主键索引，死锁也会产生。那么这种死锁比较难发现，直接看业务代码是想不到的，需要show engine innodb status来查看。然后在进行修改。