第一次来到

我有感觉自己注定会来到这里,出于自己经常来看这里的文章,这里的朋友们也很多,也学到了很多有趣的想法.今后我也会在这里分享一些我的经验! 请大家多多关照!

我给大家的礼物

有关SQL优化的相关探讨

这是我在Github的一个博主上学到的,觉得真的很不错,来这里给大家分享一下: 有时运行一句SQL语句,需要的时间较长到底是什么导致的? 我们可以分成两种情况来分析这种问题:

一.该语句平时运行都很快,只是有的时候很慢:

1.导致出现这种状况一定不是偶然的,肯定有什么关系.

当我们要往数据库插入一条数据、或者要更新一条数据的时候，对数据库进行了相关操作,我们知道数据库会在内存中把对应字段的数据更新了. 但是更新之后，这些更新的字段并不会马上同步持久化到磁盘中去，而是把这些更新的记录写入到 redo log 日记中去，等到空闲的时候，在通过 redo log 里的日记把最新的数据同步到磁盘中去。 但是redo log也不是无限存储,数据库一直处于工作状态,很忙,redo log很容易被写满.当然这个时候,redo log只能放下其他所有操作,做同步持久化磁盘的动作. 如果出现这种情况,那么我们运行SQL的速度肯定会变慢.

2.有时我们需要的表,正好别人在用,而且还给这个表上了锁,如果我们拿不到这个锁,那么,只有等使用锁的人将锁释放,才有可能进行我们的操作.

不仅表上锁,往往他仅仅是将我们需要的行上了锁,那么我们也是行不通的. 我们要想判断是否出现了这种情况,可通过show processlist命令来查看一下当前状态,看看是否有锁阻碍了我们的操作.

二.这条SQL语句一直都很慢,需要很长时间:

1.没有用上索引.哪怕你写了索引,也有可能并没有用上.

比如我们有下面的表,主键为id,c和d都是普通字段:

mysql> CREATE TABLE `t` ( `id` int(11) NOT NULL, `c` int(11) DEFAULT NULL, `d` int(11) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE=InnoDB;

我们并没有在c字段上建立索引,系统只能走全表扫描,这样无疑就是很慢的了. 但是哪怕你在c字段上建立了索引,然后执行下面这段代码:

select * from t where c - 1 = 1000;

这样也依然索引不会奏效,如果我们在字段的左边做了运算，那么在查询的时候，就不会用上索引,我们一定要注意. 正确应该像这样:

select * from t where c = 1000 + 1;

当然如果进行函数操作也是不会用上索引的,比如: select * from t where pow(c,2) = 1000;

2.选择索引错误,查找慢:

首先,先说明一下,主键索引和非主键索引是有区别的，主键索引存放的值是整行字段的数据，而非主键索引上存放的值不是整行字段的数据，而且存放主键字段的值。 也就是说,如果是非主键索引,会先查到对应主键再去查询对应主键的值. 有的时候就算你在 c 字段上有索引，系统也并不一定会走 c 这个字段上的索引，而是有可能会直接扫描扫描全表. 这到底是为什么呢? 是这样,在进行执行语句之前,系统会自己做一次检测,检测是走你的索引扫描行数比较少,还是我直接全盘扫描行数比较少.扫描行数越少，I/O操作的次数越少,那当然是越好. 如果走我们自己的非主键索引c的话，我们通过索引c找到主键，还得再通过主键索引来找我们整行的数据，也就是说，这期间要走两次索引. 全盘扫描无疑就是扫描一遍所有行,也就是n.万一满足条件的多,那么要有很多数据,基本接近n,那么就是不仅扫描n遍,每条数据还要走两次索引,这就得不偿失了. 系统完全可以根据检测选择扫描的方式,那么系统又是怎么检测的呢? 其实索引系统是对数据进行了抽样,以这段数据的基数来预测基数(一个索引上不同的值越多，即出现相同数值的索引越少，即索引的区分度越高。我们把这区分度称之为基数). 所以,既然是抽样的,那就一定存在误差,或者偶然性,如果恰好抽到一些比较近似的数据,就会觉得基数比较小,就默认判断走全盘扫描了,这就很可惜. 所以,其实是系统的这种选择索引错误,导致了你SQL语句执行的很慢. 既然会预测错索引的基数，这也意味着，当我们的查询语句有多个索引的时候，系统有可能也会选错索引哦，这也可能是 SQL 执行的很慢的一个原因。 但如果你对索引比较有信心,你完全可以进行强索引

select * from t force index(a) where c < 100 and c < 100000;

这样就能强制系统进行索引.

以上就是我所得到的一些新的收获,如果还有什么没有提及到的或者出现错误,欢迎讨论指正,让我们互相学习.