MySQL使用注意点

比眉伴天荒 2022-11-20 08:28 233阅读 0赞

### MySQL使用注意点 ###

*  概述
 *  sql执行顺序
 *  LIMIT
 *  隐式转换

# 概述 #

mysql作为我们日常使用的RDBMS，但是在使用过程中还是有一些需要注意的事项。我们可以一起了解一下哈。

# sql执行顺序 #

了解sql执行顺序尤其是表的加载顺序，对于理解

FROM
    	< left_table > ON < join_condition > < join_type >
    	JOIN < right_table > 
    WHERE
    	< where_condition > 
    GROUP BY
    	< group_by_list > 
    HAVING
    	< having_condition > SELECT DISTINCT
    	< select_list > 
    ORDER BY
    	< order_by_condition > 
    	LIMIT < limit_number>

# LIMIT #

limit是常用的分页语句之一，但是在深度分页的时候我们会发现越后面的分页越慢。  
试验  
制造100W数据的表，开始分页查询  
表结构  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYXpoYWd1_size_16_color_FFFFFF_t_70_pic_center]  
表数据量  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYXpoYWd1_size_16_color_FFFFFF_t_70_pic_center 1]  
执行语句

SELECT * FROM  big_table LIMIT 10, 10;
    SELECT * FROM  big_table LIMIT 100, 10;
    SELECT * FROM  big_table LIMIT 1000, 10;
    SELECT * FROM  big_table LIMIT 10000, 10;
    SELECT * FROM  big_table LIMIT 100000, 10;
    SELECT * FROM  big_table LIMIT 1000000, 10;

![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYXpoYWd1_size_16_color_FFFFFF_t_70_pic_center 2]  
可以明显看出数据量越大的时候执行时间是越长的。究其原因在使用limit 1000000, 10的时候，比如mysql不知道是从那条记录开始的，因此需要获取前1000000 然后开始计数获取后面10条数据。  
其实深度翻页不仅仅对于mysql这类关系型数据库有这样的问题，对于Elasticsearch进行深度from size的时候也是有这个问题，并且比这个更严重，一般来说有这几种解决方案

1.  禁止深度翻页，一般对于超过百万千万的这种禁止深度翻页
2.  使用滚动及讲上一页末尾的记录作为下一页的初始条件，然后limit 10

# 隐式转换 #

指在sql中查询变量和字段定义类型不匹配，这种情况会导致索引的失效。  
exp字段为varchar(200),下面两条sql的返回结果完全一样

SELECT * FROM  big_table where exp = '999';
    SELECT * FROM  big_table where exp = 999;

但是我们添加explain之后就会发现一个索引失效了  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYXpoYWd1_size_16_color_FFFFFF_t_70_pic_center 3]  
![在这里插入图片描述][watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYXpoYWd1_size_16_color_FFFFFF_t_70_pic_center 4]  
如果希望了解explain可以参考一下，另一篇博文  
[MySQL语句定位性能问题-explain][MySQL_-explain]

[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYXpoYWd1_size_16_color_FFFFFF_t_70_pic_center]: /images/20221120/407ea9e6197143f698197aba664d175f.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYXpoYWd1_size_16_color_FFFFFF_t_70_pic_center 1]: /images/20221120/8a579551fe0549f386ec80862e59b676.png
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYXpoYWd1_size_16_color_FFFFFF_t_70_pic_center 2]: https://img-blog.csdnimg.cn/20201028222435965.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYXpoYWd1,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYXpoYWd1_size_16_color_FFFFFF_t_70_pic_center 3]: https://img-blog.csdnimg.cn/2020102823193279.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYXpoYWd1,size_16,color_FFFFFF,t_70#pic_center
[watermark_type_ZmFuZ3poZW5naGVpdGk_shadow_10_text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYXpoYWd1_size_16_color_FFFFFF_t_70_pic_center 4]: https://img-blog.csdnimg.cn/20201028231951339.png?x-oss-process=image/watermark,type_ZmFuZ3poZW5naGVpdGk,shadow_10,text_aHR0cHM6Ly9ibG9nLmNzZG4ubmV0L3poYXpoYWd1,size_16,color_FFFFFF,t_70#pic_center
[MySQL_-explain]: https://blog.csdn.net/zhazhagu/article/details/102058291