mysql-SQL调优

本文主要为有一定的sql基础，但对sql是否有使用到索引等问题的调优方式还不大清楚的人，指出一个使用的方式。

本文并非纯原创，是结合前人的博文，自己总结写一篇博文。

 

mysql join集合用法:https://blog.csdn.net/lukabruce/article/details/80568796

 

索引、分库分表、sql的写法等，都会影响到sql性能。

概念：

1. 数据库事务ACID特性

数据库事务的4个特性：
原子性(Atomic): 事务中的多个操作，不可分割，要么都成功，要么都失败； All or Nothing.
一致性(Consistency): 事务操作之后, 数据库所处的状态和业务规则是一致的; 比如a,b账户相互转账之后，总金额不变；
隔离性(Isolation): 多个事务之间就像是串行执行一样，不相互影响;
持久性(Durability): 事务提交后被持久化到永久存储.

 

3. MySQL 中RC和RR隔离级别的区别

MySQL数据库中默认隔离级别为RR，但是实际情况是使用RC 和 RR隔离级别的都不少。好像淘宝、网易都是使用的 RC 隔离级别。那么在MySQL中 RC 和 RR有什么区别呢？我们该如何选择呢？为什么MySQL将RR作为默认的隔离级别呢？

4. RC 与 RR 在锁方面的区别

1> 显然 RR 支持 gap lock(next-key lock)，而RC则没有gap lock。因为MySQL的RR需要gap lock来解决幻读问题。而RC隔离级别则是允许存在不可重复读和幻读的。所以RC的并发一般要好于RR；

2> RC 隔离级别，通过 where 条件过滤之后，不符合条件的记录上的行锁，会释放掉(虽然这里破坏了“两阶段加锁原则”)；但是RR隔离级别，即使不符合where条件的记录，也不会释放行锁和gap lock；所以从锁方面来看，RC的并发应该要好于RR；另外 insert into t select ... from s where 语句在s表上的锁也是不一样的。

 

二、mysql行锁、表锁

对于myisam的表select 是会锁定表的 ，会导致其他操作挂起，处于等待状态。
对于innodb的表select 是不会锁表的。其实这里使用到了快照。快照这里不作讨论。

常用innodb，select ... for update，注意一定要where + id 或者唯一索引字段过滤，不然会导致表锁。

优化心得：

1.服务器优化，例如max_connection改大，connection_timeout修改

2.查看下在锁的事务

SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;

杀死进程id（就是上面命令的trx_mysql_thread_id列）

kill 线程ID

3.架构层面。

    1）使用缓存（例如redis）。

    2）再不行开集群，读写分离。

    3）即使集群分离读写，假设数据量上亿怎么也慢，那就分库分表（IO性能）

4.配置slow_query_log，用于分析执行时间超过设定时长的sql，但也会耗一定性能；可配合bin目录下mysqldumpslow工具分析统计慢查询语句。

sql优化：

1.分页查询倘若实在过于复杂，可以先查出分页数据， 然后再直接查指定的分页数据详情列。

2.case when语句，写在where前对性能影响不太大，但写在where后面会很耗时间

3.mysql大数据量使用limit分页，随着页码的增大，查询效率越低下。

    select * from product limit 10, 20   0.016秒
    select * from product limit 100, 20   0.016秒
    select * from product limit 10000, 20   0.094秒

    select * from product limit 400000, 20   3.229秒

   利用表的覆盖索引来加速分页查询
   我们都知道，利用了索引查询的语句中如果只包含了那个索引列（覆盖索引），那么这种情况会查询很快。

SELECT * FROM product WHERE ID > =(select id from product order by id limit 866613, 1) limit 20 或者

   SELECT * FROM product a JOIN (select id from product order by id limit 866613, 20) b ON a.ID = b.id

   查询时间为0.2秒！ 

select id from collect where vtype=1 limit 90000,10;   //加了 search(vtype,id) 这样的复合索引，才会快

4.mysql只会使用一个索引，假设有两个索引idx1，idx2，并在查询条件使用了where idx1 = ? and idx2 = ?，但实际上最终只会用到其中一个作为索引，这时候如果要更优的性能，建议使用联合索引（联合索引要符合最左匹配原则）；若使用where和order，建议order列要加入到联合索引。

5.当索引字段特别长，例如var(255)的时候，假设这个字段的一般只需要前几位，就能区分绝大部分数据了，那么建议使用短索引。alter table observationrequest add index `index_short_deleteFlag_clinicInfoType`(`DeleteFlag`(1),ClinicInfoType(4));

6.建议使用Union [all] 替代 or，性能会上升。

7.

2.索引原理

mysql innodb现在主要用的就是B+ Tree结构的索引，B+Tree 是绝对平衡的树，它需要知道某条数据该往左往右走，才能很好的减少主存与硬盘间的io操作，加快查询速度。（注意区分：B树不等同于平衡二叉树，它是一种多路查找树，和B树不同的是它的数据都是存储于叶子结点的，且叶子结点之间由指针相连，只是平衡树的一种。）

mysql索引的存储结构一般使用B+树，其实它还有hash结构存储，hash结构查找数据的复杂度低o(1),而B+树一般只有o(log n)，那么为什么选择b+树？

分析：

1.为什么不用二叉树呢？ 
因为我们要考虑磁盘IO的影响，它相对于内存来说是很慢的。数据库索引是存储在磁盘上的，当数据量大时，就不能把整个索引全部加载到内存了，只能逐一加载每一个磁盘页（对应索引树的节点）。所以我们要减少IO次数

2.为什么不使用hash？

而且数据库中的索引一般是在磁盘上，数据量大的情况可能无法一次装入内存，B+树的设计可以允许数据分批加载，同时树的高度较低，提高查找效率。

这和业务场景有关。如果只选一个数据，那确实是Hash更快。但是数据库中经常会选择多条，这时候由于B+树索引有序，并且又有链表相连，它的查询效率比Hash就快很多了。

一般可以知道，not in  <> is null这些不走索引，不用死记其实很简单，就是因为它在使用b+树索引的时候，无法得知该从树的左边还是右边的路线找起，因而只能全表搜索，效率低，下面是更多的验证，其中联合索引的底层我还不了解。

就举个栗子，大家都知道索引列的数据长度太长也会影响索引的效率，但实际是为什么呢？

其实就是因为mysql使用的b+tree索引，所有的数据都是放在叶子节点上的，所以当索引列所占大小太大的时候，会导致每一个叶子结点存储不了太多的数据，导致出现更多的叶子，更多的叶子会导致树的层数增多，层数深了，查找时走io的次数也增多的，所以就效率低。

 

2.运行调优

IDEA工具推荐使用XRebel，可能快速定位哪些sql效率过低

试验

指令：

explain extended select * from tablename;
show warnings;

 

使用explain extended指令explain可以帮助我们分析select语句，从而可以针对性地去做优化。

show warnings则是可以查看到我们编写的sql是如何被优化的，可以很直观地查到自己的sql是否优良。

 

下面是一张测试表：

CREATE TABLE `t_stu` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(20) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;

 

输入explain extended xxx指令为结果一：

show warnings; 指令为结果二：

结果二很明了可以看到我们编写的sql被自动补上了全列名

解析

那么对于结果怎么分析呢?

对于结果一：（这个总结是引用自上面所提到的博文）

 

这里总结几个可能容易理解错误的技巧：

1. 通常来说把可为NULL的列改为NOT NULL不会对性能提升有多少帮助，只是如果计划在列上创建索引，就应该将该列设置为NOT NULL。

2. 对整数类型指定宽度，比如INT(11)，没有任何卵用。INT使用32位（4个字节）存储空间，那么它的表示范围已经确定，所以INT(1)和INT(20)对于存储和计算是相同的。

3. UNSIGNED表示不允许负值，大致可以使正数的上限提高一倍。比如TINYINT存储范围是-128 ~ 127，而UNSIGNED TINYINT存储的范围却是0 - 255。

4. 通常来讲，没有太大的必要使用DECIMAL数据类型。即使是在需要存储财务数据时，仍然可以使用BIGINT。比如需要精确到万分之一，那么可以将数据乘以一百万然后使用BIGINT存储。这样可以避免浮点数计算不准确和DECIMAL精确计算代价高的问题。

5. TIMESTAMP使用4个字节存储空间，DATETIME使用8个字节存储空间。因而，TIMESTAMP只能表示1970 - 2038年，比DATETIME表示的范围小得多，而且TIMESTAMP的值因时区不同而不同。

6. 大多数情况下没有使用枚举类型的必要，其中一个缺点是枚举的字符串列表是固定的，添加和删除字符串（枚举选项）必须使用ALTER TABLE（如果只只是在列表末尾追加元素，不需要重建表）。

7. schema的列不要太多。原因是存储引擎的API工作时需要在服务器层和存储引擎层之间通过行缓冲格式拷贝数据，然后在服务器层将缓冲内容解码成各个列，这个转换过程的代价是非常高的。如果列太多而实际使用的列又很少的话，有可能会导致CPU占用过高。

8. 大表ALTER TABLE非常耗时，MySQL执行大部分修改表结果操作的方法是用新的结构创建一个张空表，从旧表中查出所有的数据插入新表，然后再删除旧表。尤其当内存不足而表又很大，而且还有很大索引的情况下，耗时更久。当然有一些奇技淫巧可以解决这个问题，有兴趣可自行查阅。

索引建立依据 

 

回表：innodb查询条件是二级索引时，查到B+树的叶子节点没有整条的数据（innodb二级索引树叶子只存放该索引键值和主键键值），所以还得到主键索引的B+树再去查找一遍，这就是回表。

覆盖索引：同上，但特例是select 二级索引字段 from XX，因为查询列只有二级索引字段，索引在二级索引B+树查找的时候，叶子节点已经有想要的数据了，不再回表。

 mysql是否用索引，主要依据基于成本的优化器决定的。比较智能，所以有些死套的规则并不符合。

例如查询条件带 不等于 ， not in。挺多人说会导致索引失效，但不一定。

举个例，假设自增主键表，select  * from table where id != 1; 就会走索引，因为优化器发现这种情况，只要找到id=1的叶子节点，因为叶子节点是有序的，所以直接找出1之后的叶子节点数据即可，这就是走索引了，所以最好要灵活判断。

 

分库分表

 

为什么要分库

按一般的经验来说，一个单库最多支持并发量到2000，且最好保持在1000。如果有20000并发量的需求，这时就需要扩容了，可以将一个库的数据拆分到多个库中，访问的时候根据一定条件访问单库，缓解单库的性能压力。

为什么要分表

分表也是一样的，如果单表的数据量太大，就会影响SQL语句的执行性能。分表就是按照一定的策略将单表的数据拆分到多个表中，查询的时候也按照一定的策略去查询对应的表，这样就将一次查询的数据范围缩小了。比如按照用户id来分表，将一个用户的数据就放在一个表中，crud先通过用户id找到那个表在进行操作就可以了。这样就把每个表的数据量控制在一定范围内，提升SQL语句的执行性能。

 

综上所述，现在建议考量使用的就是sharding-jdbc和mycat。

mycat这种proxy方案的缺点在于需要部署，因此运维成本也就比较高。但是优点在于其对于各个项目是透明（解耦）的，如果要升级的话只需要在中间件处理就行了。

通常来说，这两个方案都是可以选用的。但是建议中小型公司选用sharding-jdbc比较好，因为client层方案轻便，维护成本低；建议中大型公司选用mycat比较好，因为proxy层方案可以应对多个系统和项目大量使用，虽然维护成本相对来说会较高，但是中大型公司还缺这点人力吗。

具体如何对数据库进行垂直拆分或水平拆分？

水平拆分的概念

水平拆分的意思，就是把一个表的数据拆分到多个库的多个表里面去。这里面的每个库的表结构都是一样的，只不过是表中存放的数据不一样，每个库表的数据汇总起来就是全部数据。水平拆分的意义在于将数据均匀地存放在各个库表里，依靠多个库来杠更高的并发，而且还能借助多个库的存储容量来进行扩容。

垂直拆分的概念

垂直拆分的意思，就是把一个有很多字段的表给拆分成多个表或者多个库上面去，每个库表的结构都不一样，每个库表都包含部分字段。一般来说，会将较少的访问频率很高的字段放到一个表里面去，然后将较多的访问频率很低的字段放到另外一个表里面去。因为数据库是有缓存的，你访问频率高的行字段越少，就可以在缓存里面缓存更多的行，性能也就越好。这个一般在表层面做的较多一些。

水平拆分和垂直拆分的场景

所谓表层面的拆分，就是分表。具体就是将一个表拆分为N个表，让每个表的数据量控制在一定的范围内，保证SQL的性能。否则，单表的数据量越大，SQL的性能也就越差，一般是200万行左右，不要太多。如果你的SQL越复杂，就尽量让单表的行数越少。

无论是分库还是分表，主流的数据库中间件都是可以支持的。这些中间件可以在你分库分表之后，根据指定的某个字段值自动路由到对应的库和对应的表上面。这时就只要考虑项目如何分库分表就行了。一般来说，垂直拆分，可以在表层面做，即对一些字段特别多的表做一下拆分；水平拆分的话，可能是因为并发承载不了或容量承载不了，也就可以按某个字段去分布到不同的库表里面去。

分库分表的两个方案

这里说一下两种分库分表的方案和它们的优缺点。

1.按照range来分。比如说按照时间范围来分库分表，每个库表中存放的都是连续时间范围的数据。但是这种方式一般很少用，因为很容易会产生热点问题，大量的流量都打在最新的数据上了。这种方案的优点在于扩容的时候非常简单，比如只要预备好每个月都准备一个库就可以了，到了下一个新的月份自动将数据写入新的库。缺点则是，如果大部分请求都是访问最新的数据，那么在这里，分库分表的设计目的就只是简单的扩容，而不是为了应对高并发了。

2.按照hash分发。按照某个字段的hash值均匀分散，这个较为常用。优点在于可以平均分配每个库表的数据量和请求压力；缺点在于扩容比较麻烦，因为会存在一个数据迁移的过程，即之前的数据需要重新计算hash值并重新分配到不同的库表中。

 

实战篇

exists 和  in，一定是前者性能更好？  not in 和 not exists又如何选择？

1、in会把外表和内表作hash 连接，in内的子查询的结果集会被存储到临时表中，不论是内存临时表还是磁盘临时表都不会存在索引，所以查询性能会受到一定的影响，特别是对于返回结果集比较大的子查询。

2、exists是对外表作loop循环，每次loop循环再对内表进行查询。
因此根据外表跟内表数据大小的差异，分为以下几种情况：
a. 外表数据大，内表数据小的exists与in： 这种情况应当使用in，虽然会存储在临时表，但是内表数据小，因此查询次数不会那么多，而exists外表loop循环会占用较长时间，所以不适合内表数据小的情况；
b. 外表数据小，内表数据大的exists与in： 这种情况应当使用exists，内表数据量太大导致临时表太大，占用过多IO与CPU使性能下降，而exists的loop循环在这种情况下会会占用较少资源，效率高；
c. 外表内表数据大小相当的exists与in： 这种情况in与exists性能也相当。
d.表中有索引的情况：exists与in均不会使索引失效，使用状况参照上边三种情况。但是not exists与not in的情况中，not exists仍旧是索引生效，但是not in会使索引失效导致全表扫描，因此在not exists与not in情况中，均推荐使用not exists。
 

mysql强制索引和禁止某个索引
1、mysql强制使用索引:force index(索引名或者主键PRI)

例如:

select * from table force index(PRI) limit 2;(强制使用主键)

select * from table force index(ziduan1_index) limit 2;(强制使用索引”ziduan1_index”)

select * from table force index(PRI,ziduan1_index) limit 2;(强制使用索引”PRI和ziduan1_index”)

 

2、mysql禁止某个索引：ignore index(索引名或者主键PRI)

例如:

select * from table ignore index(PRI) limit 2;(禁止使用主键)

select * from table ignore index(ziduan1_index) limit 2;(禁止使用索引”ziduan1_index”)

select * from table ignore index(PRI,ziduan1_index) limit 2;(禁止使用索引”PRI,ziduan1_index”)