MySQL sql优化参考总结

SQL优化参考

1. 阿里「超过三张表禁止join」作为强制规定，实际是硬性杜绝了开发者和设计者为了省事而将复杂的关联关系写在SQL语句中的行为。迫使人使用程序代码代替SQL语句来处理问题。

分析:

减少join除了直观地降低了高并发状态下的资源消耗外，更大的好处是降低了业务之间的耦合，增加了扩展性。

这些规则都是互联网开发团队总结出来的，适用于高并发、轻写重读、分布式、业务逻辑简单的情况，甚至对数据的一致性要求都不高，允许脏读（毕竟web是个非实时、无状态的东西）。

而对于很多低并发、频繁复杂数据写入、CPU密集而非IO密集、主要业务逻辑通过数据库处理甚至包含大量存储过程、对一致性与完整性要求很高的系统。比如金融啦、财务啦、企业应用之类，复杂join也是不可避免的，不仅要写，还要写好，才能发挥数据库最大的功用。

参考链接：https://www.zhihu.com/question/56236190/answer/161571374

 

2.  阿里【推荐】 SQL 性能优化的目标：至少要达到 range 级别， 要求是 ref 级别， 如果可以是 consts 最好。 说明：

1） consts 单表中最多只有一个匹配行（主键或者唯一索引） ，在优化阶段即可读取到数据。

2） ref 指的是使用普通的索引（normal index） 。

3） range 对索引进行范围检索。

反例： explain 表的结果， type=index，索引物理文件全扫描，速度非常慢，这个 index 级 别比较 range 还低，与全表扫描是小巫见大巫。

分析:

MySQL中大于小于，IN，OR，BETWEEN性能比较

参考URL https://blog.youkuaiyun.com/u011781769/article/details/48471013

Using filesort表示在索引之外，需要额外进行外部的排序动作。导致该问题的原因一般和order by有者直接关系，一般可以通过合适的索引来减少或者避免。

什么时候会发生filesort 排序呢？

查询索引和order by 的字段不是同一个字段。

 

3. 关于mysql索引基数的概念

参考URL https://blog.youkuaiyun.com/tiansidehao/article/details/78931765

索引基数是数据列所包含的不同值的数量。列中包含很多不同的值，重复的值很少）的时候，它的工作效果最好。

如果值出现的几率几乎相等，那么无论搜索哪个值都可能得到一半的数据行。在这些情况下，最好根本不要使用索引，因为查询优化器发现某个值出现在表的数据行中的百分比很高的时候，它一般会忽略索引，进行全表扫描。惯用的百分比界线是”30%”

数据重复且分布平均的表字段

假如一个表有10万行记录，有一个字段A只有T和F两种值，且每个值的分布概率大约为50%，那么对这种表A字段建索引一般不会提高数据库的查询速度。

 

4. Mysql几种索引类型的区别及适用情况

参考URL https://www.cnblogs.com/yuan-shuai/p/3225417.html

Mysql目前主要有以下几种索引类型：FULLTEXT，HASH，BTREE，RTREE。

那么，这几种索引有什么功能和性能上的不同呢？

各种索引的使用情况

（1）对于BTREE这种Mysql默认的索引类型，具有普遍的适用性

（2）由于FULLTEXT对中文支持不是很好，在没有插件的情况下，最好不要使用。其实，一些小的博客应用，只需要在数据采集时，为其建立关键字列表，通过关键字索引，也是一个不错的方法，。

（3）对于一些搜索引擎级别的应用来说，FULLTEXT同样不是一个好的处理方法，Mysql的全文索引建立的文件还是比较大的，而且效率不是很高，即便是使用了中文分词插件，对中文分词支持也只是一般。真要碰到这种问题，Apache的Lucene或许是你的选择。

（4）正是因为hash表在处理较小数据量时具有无可比拟的素的优势，所以hash索引很适合做缓存（内存数据库）。如mysql数据库的内存版本Memsql，使用量很广泛的缓存工具Mencached，NoSql数据库redis等，都使用了hash索引这种形式。当然，不想学习这些东西的话Mysql的MEMORY引擎也是可以满足这种需求的。

（5）至于RTREE，至今还没有使用过，它具体怎么样，我就不知道了。有RTREE使用经历的同学，到时可以交流下！

 

5. MySQL对varchar型字段的索引，字段类型不同造成的隐式转换，导致索引失效

MySQL索引使用：字段为varchar类型时，条件要使用”包起来，才能正常走索引 表user 里的id是BigInt类型，order表里关联到user表的id误建成userId varchar类型了，在ibatis上查询时，直接传入id Long类型，SQL查询非常慢，即使建立了userId索引，于是改成String 类型的userId去查询，SQL立马变的很快了。

通过explain sql可看下SQL是否走了索引，很快对比出来 原因：字段类型不同造成的隐式转换，导致索引失效

 

6. MySQL索引varchar长度问题（不能超过255） 阿里强制（长度不要超过 5000）

MySQL的varchar索引只支持不超过768个字节，可以指定前缀 测试： UTF8编码不能超过255 GBK是双字节，即：768/2=384

InnoDB存储引擎的表索引的前缀长度最长是767字节(bytes)

你如果需要建索引，就不能超过 767 bytes；utf8编码时 255*3=765bytes ,恰恰是能建索引情况下的最大值。

 

  **mysql 数据库varchar(100)可以存储多少个汉字，多少个数字**?
​
  参考URL https://blog.youkuaiyun.com/IAlexanderI/article/details/81356213
​
  >  5.0版本以上**，varchar(100)，指的是**100字符**，无论存放的是数字、字母还是UTF8汉字（每个汉字3字节），都可以存放100个。
​
  varchar(n) 表示n个字符，无论汉字和英文，`MySql`都能存入 `n` 个字符，仅实际字节长度有所区别。

 

 在字符集为UTF-8的情况下： 4.0以下 字节为单位存储，假设全部为常用汉字（UTF-8 3字节编码长度），则VARCHAR(255)， floor(255/3)=85个，共可存放约85个汉字； 4.1以上 VARCHAR以字符为单位存储，假设输入仍然为常用汉字，则VARCHAR(255)可以存放255个汉字。 另外，据我所知，MySQL对UTF-8的支持也仅仅限于1~3字节编码长度（Unicode：0x0000~0xFFFF），可以满足大部分需求，但是生僻字就不行了。

 

7. 数据库多表关联方式

  参考URL https://blog.youkuaiyun.com/iprettydeveloper/article/details/53943904 

•   小表驱动大表
•   在查询的优化中永远小表驱动大表
  ​
    参考URL https://www.aliyun.com/jiaocheng/1109845.html

  1). 如果都走全表的话,大表做驱动和小表做驱动都是M*N
  2). 如果走索引的话:
  a.索引对小表的作用不会太大,对于大表索引的作用就很大了,除非索引建的不好。。
  b.假设nexted-loop join中驱动表过滤后的行数为K,那么while(outer_row)一定会循环K次,这时驱动表上索引的功能是比聚簇索引占有更小的空间,一个节点上的数据量会更大些,减少随机I/O。
  c.如果被驱动表过滤后的行数为W,那么在while(outer_row)中两表连接条件上被驱动表还有机会利用索引来大大减少内循环的次数。
​所以过滤结果中的小表做驱动表。。

MySQL百万级、千万级数据多表关联SQL语句调优  参考URL  https://www.cnblogs.com/uttu/p/6384541.html  
EXPLAIN 结果中，第一行出现的表就是驱动表

 

inner join表时，连接的表字段都加了，不管你那个表写前面，mysql会自动把数据量少的那个表做驱动表。你可以通过EXPLAIN查看哪个表时驱动表。

LEFT JOIN 时，就是以你写在前面的那个表做为驱动表。同样可以通过EXPLAIN查看确认。

 

8. 查询总结

参考URL: https://blog.youkuaiyun.com/wendy432/article/details/52319908

参考URL: https://www.aliyun.com/jiaocheng/1209840.html

1） 查询速度，不简简单单和索引有关，和你存储的数据也有关系

你存储的某个字段内容全都一样，你where条件 = 字段内容，这种情况虽然有索引，但是还是比较慢；你的where条件 = 非字段内容，就很快，这和mysql的实现有关系。

总之，查询速度的快慢不仅仅是索引的问题，和该索引字段的内容查询条件也有关系。

可以总结出一个经验，你查出来的记录个数越少，你的查询速度越快。

left join后一次查出大数据量，请考虑是不是业务需求有问题，减小驱动表大小才是王道。

2）无where条件下，分情况测试

• * **无where条件情况下，无left join 情况下**，30w数据查询速度也不会很慢，100w时，本地测试3s，应该是一个比较线性的递增关系；但是如果你left join，驱动表的大小很影响查询速度，30w的数据响应已经比较大了。
• * **无where条件情况下**，查的某一个字段如果没有索引，则explain type都会是ALL。但是这个一般对性能影响不大。即使你给这些查询添加字段加了索引，explain type会变成index但是，性能提升不明显，index和ALL差不多，index是遍历完整个索引表。
• * **无where条件下，left join一个表**，explain type都会是ALL，即使你查询的字段有索引以及join on的字段也有索引

因此，无where条件下，包含left join 的驱动表比较大的情况下，优化空间有限。

思路：加where条件，把驱动表explain type锁定到range以上。其原理其实就是减小驱动表的大小，驱动表很大的情况下，如果没有where条件限制，其实就是前面的情况，left join后驱动表全表扫描连接（join），优化提升空间有限。

经过测试，where条件比较大时，比如你给时间戳加了索引，卡时间戳范围，你卡的时间范围比较大（比如完全包住，其实rows有个百分比，超过一定比例），你的exaplin type显示还是ALL不会是range。其实就是相当于还是走了全表扫描，索引失效。

另外where条件应遵守一个原则：排除越多的条件放在第一个。

之前有个错误的认识，认为 where再on后执行，left join on之后你再where，是对连接结果集合的过滤，on在前，你就会遍历所有的主表，所以必须再驱动表上使用临时表使用where条件过滤后再于你的副表连接left join。

其实不是的，mysql有自己的优化机制（猜测：mysql会判断你的左表有没有过滤字段，它会根据过滤字段过滤的百分比，百分比过低就走全表扫描，否则其实应该是过滤完主表再与副表连接），直接两个连接on后，你加where过滤主表的字段，使用EXPLAIN查看，它的rows就会是你满足条件的记录。 因此就相当于较少了驱动表的大小。你where条件将主表的记录数缩小越小，速度就越快。利用where最好可以把主表的EXPLAIN type控制在range级别。

 

3） SQL_NO_CACHE的用法

MYSQL优化（一）：MySQL 查询过程、查询缓存及 SQL_CACHE与SQL_NO_CACHE的用法

参考URL: https://blog.youkuaiyun.com/u014749862/article/details/80242078

参考URL: http://www.dewen.net.cn/q/5149/Mysql

参考URL: https://www.cnblogs.com/wangjuns8/p/7840730.html

Mysql中SQL_NO_CACHE的真正意思，它是The query result is not cached.(“禁止SQL结果集被缓存”)，而不是“禁止从缓存中读结果集”，由此可以看出，你的第一次查询没有带SQL_NO_CACHE,所以结果集就被query cache起来了，那么此后的所有这个查询，不管是带不带上SQL_NO_CACHE都会从cache里取，所以才会看起来无效，如果要重新测试，就在查询前先执行一下"FLUSH QUERY CACHE"，清空一下query cache就行了。然后再带上SQL_NO_CACHE选项，就没问题了

关于SQL_CACHE与SQL_NO_CACHE

MySql中可以在SQL中指定SQL_CACHE和SQL_NO_CACHE来控制某个查询语句是否需要进行缓存

关于 query_cache_type变量

mysql是根据query_cache_type这个变量来决定要不要把查询结果放到查询缓存中。
这个变量有三个取值：0,1,2，分别代表了off、on、demand。

mysql默认为开启 on

例如在my.ini中增加一行 ：query_cache_type=2

当query_cache_type=0，query cache 是关闭的。

当query_cache_type=1，那么查询总是先到查询缓存中查找，即使使用了sql_no_cache仍然查询缓存，因为sql_no_cache只是不缓存查询结果，而不是不使用查询结果。

当query_cache_type=2，demand。

则只有加入SQL_CACHE的查询才会走缓存

注：修改变量配置，需要重启mysql服务

4）EXPLAIN 中的rows含义

参考URL: https://www.cnblogs.com/LBSer/p/3333881.html

为什么explain的结果和真实的结果运行不一致，并且产生这么大的误差？

explain使用Rows来告知我们数据库即将要阅读的行数，但是实际将要阅读的行数和explain所记载的将要阅读的行数可能会有差异，这是因为explain并没有真的去执行sql语句从而得出行数，而是进行了某种预估。

5） 关联查询时用on筛选和用where筛选的区别

数据库左右连接on后的限制条件问题 参考URL: https://www.cnblogs.com/Jacck/p/8213828.html

参考URL: https://blog.youkuaiyun.com/wb_snail/article/details/79235219

对left join 左连接的理解很重要，on后添加除了连接条件外的其他限制条件(and xx>1等)

左表的行数总是左边总记录数，不管是限制的是左表还是右表，只把满足条件的进行连接。如下两种情况：

结果分析：在t1中的列进行限制后，只把满足条件的t1行进行左连接，t1中的数据行数并没有减少。

结果分析：在t2中的列进行限制后，只把t2满足条件的列进行左连接，t1中的数据行数并没有减少。

如果在on后加上限制条件，会在连接时考虑限制条件，如果不符合条件，则本行数据不进行连接动作。作为主表所有数据必出现在结果集中，而副表不进行连接的行数据一定不出现在结果集中。