Mysql 由单引号引发的性能问题

本文通过对比分析,揭示了在SQL查询中正确处理字符串类型的重要性。具体案例显示,仅通过在条件中为varchar类型字段添加单引号,就能使查询性能从2.67秒提升至170毫秒,大幅减少了数据扫描行数,从780675行降至20335行。文章强调,在涉及字符串类型的SQL查询中,必须使用单引号包裹参数,以确保索引的有效利用。

最近工作的时候遇到个问题就是要去优化一条Mysql的查询,想了许多方法都不尽人意,没能把查询速度提高到理想的速度,后来无意间把查询的条件加上单引号,性能就大大提高了,为什么加了单引号性能就会变得这么快呢?
优化前的sql

select  table_name,yet_totalSize,day1_totalSize,day1_rate,day7_rate,day7_totalSize from (select r.tbl_name as table_name,sum(if(r.d_s in (20190602) ,r.totalsize,0 ) ) as yet_totalSize,
                sum( if(r.d_s in (20190601) ,r.totalsize,0) ) as day1_totalSize,
                (sum( if(r.d_s in (20190602) ,r.totalsize,0) ) - sum(if(r.d_s in (20190601) ,r.totalsize,0 )))/sum(if(r.d_s in (20190601) ,r.totalsize,0 ) ) as day1_rate,
                (sum( if(r.d_s in (20190602) ,r.totalsize,0) ) - sum(if(r.d_s in (20190526) ,r.totalsize,0 )))/sum(if(r.d_s in (20190526) ,r.totalsize,0 ) ) as day7_rate,
                sum( if(r.d_s in (20190526) ,r.totalsize,0) ) as day7_totalSize
                from uts3.monitor_data_rawlog r where r.tbl_name  in ( "...") and   r.d_s in (20190602,20190601,20190526) group by  r.tbl_name) t order by day1_totalSize desc limit 0,10

优化后的sql

select  table_name,yet_totalSize,day1_totalSize,day1_rate,day7_rate,day7_totalSize from (select r.tbl_name as table_name,sum(if(r.d_s in ('20190602') ,r.totalsize,0 ) ) as yet_totalSize,
                sum( if(r.d_s in ('20190601') ,r.totalsize,0) ) as day1_totalSize,
                (sum( if(r.d_s in ('20190602') ,r.totalsize,0) ) - sum(if(r.d_s in ('20190601') ,r.totalsize,0 )))/sum(if(r.d_s in ('20190601') ,r.totalsize,0 ) ) as day1_rate,
                (sum( if(r.d_s in ('20190602') ,r.totalsize,0) ) - sum(if(r.d_s in ('20190526') ,r.totalsize,0 )))/sum(if(r.d_s in ('20190526') ,r.totalsize,0 ) ) as day7_rate,
                sum( if(r.d_s in ('20190526') ,r.totalsize,0) ) as day7_totalSize
                from uts3.monitor_data_rawlog r where r.tbl_name  in ( "...") and   r.d_s in ('20190602','20190601','20190526') group by  r.tbl_name) t order by day1_totalSize desc limit 0,10

注意只是d_s这个字段补上单引号而已。
先用EXPLAIN分析优化前的sql语句:
在这里插入图片描述
再分析优化后的sql语句:在这里插入图片描述
从2.67s降到170ms,可以看出优化前type=index,key=idx_monitor_data_rawlog_tn(table_name的索引),表示查询是读全表的,没有使用到d_s的索引,扫描了780675行数据。
再看优化后的sql,type=range,key=inx_ds_tn(d_s和table_name的聚合索引),表示查询是按索引范围扫描的,d_s的索引有被使用到,总共才扫描20335行数据。
所以可以看出,没加引号之前,d_s的索引是没有被用到的。
为什么呢?
因为d_s是varchar类型的,而没加单引号字段会被当作数字类型来使用,索引就没法正常使用了(索引)。这就是索引失效的一个条件之一:varchar类型在使用索引的时候要用单引号。
总结:如果列类型是字符串,那一定要在条件中将数据使用单引号包含起来,否则索引会失效。

<think>好的,用户想了解SQL中单引号的使用规则和适用情况。首先,我需要回顾一下相关的知识,确保自己理解正确。根据引用的资料,特别是引用[2]中提到,单引号用于字符串、日期和字符类型,而双引号用于对象名和保留字。比如,字段名如果是中文或者包含空格,可能需要用双引号括起来,而具体的字符串值应该用单引号。 接下来,用户的问题可能涉及单引号的基本用法,比如在INSERT或SELECT语句中的字符串值必须用单引号包裹。比如,INSERT INTO table (name) VALUES ('John'),这里的'John'就需要单引号。这一点在引用[3]和[4]中的例子也有体现,比如strsql=“Insert into mytable(username) values(‘” & & “')”以及拼接的SQL语句中的单引号使用。 然后,用户可能想知道为什么有时候需要转义单引号,比如在字符串中包含单引号的情况。这时候应该用两个单引号来转义,例如'O''Neil'。引用[5]中的代码示例展示了如何通过替换单引号为两个单引号来处理这种情况,防止SQL注入和语法错误。 另外,用户可能对性能影响感兴趣,比如引用[1]提到主键为字符串类型时,单引号会导致性能下降。这是因为数据库可能无法正确使用索引,导致全表扫描。所以,正确使用单引号仅关乎语法正确,还影响查询效率。 还需要区分同的数据库系统,比如MySQL和Oracle在处理引号时的差异。例如,MySQL允许使用双引号表示字符串,但建议统一使用单引号以避免混淆,而Oracle则严格区分单双引号的用途。 最后,总结单引号的使用规则:包裹字符串和日期、处理转义、注意性能影响以及同数据库的差异。同时,应该提醒用户避免动态拼接SQL,使用参数化查询来防止注入问题,这也是引用[5]中提到的处理方式。 检查是否有遗漏点,比如是否提到LIKE子句中的通配符处理,或者存储过程中的字符串处理。确保覆盖主要的使用场景,并引用正确的参考资料。现在可以组织答案,结构清晰,分点说明,并附上相关问题。</think>### SQL中单引号的使用规则及适用情况 在SQL中,单引号主要用于标识**字符串字面量**、**日期/时间类型数据**以及**字符类型字段的值**。以下是具体规则和适用场景: --- #### 一、基础使用场景 1. **字符串值包裹** SQL语句中所有字符串类型的值必须用单引号包裹。例如: ```sql SELECT * FROM users WHERE name = '张三'; -- 正确 SELECT * FROM users WHERE name = 张三; -- 错误(缺少单引号) ``` 若字符串本身包含单引号,需通过**双写单引号**转义: ```sql INSERT INTO books (title) VALUES ('O''Neil'); -- 插入值:O'Neil ``` 引用[5]通过代码示例展示了如何动态处理单引号转义[^5]。 2. **日期/时间类型** 日期或时间值通常需要用单引号包裹,即使某些数据库支持隐式转换: ```sql SELECT * FROM orders WHERE order_date = '2023-10-01'; ``` 3. **字符类型字段的条件匹配** 例如`CHAR`或`VARCHAR`类型的主键查询时,必须使用单引号: ```sql SELECT * FROM products WHERE product_id = 'A100'; -- 正确 SELECT * FROM products WHERE product_id = A100; -- 错误(字符串主键需单引号) ``` 引用[1]指出,若主键为字符串类型,单引号可能导致索引失效,性能下降百倍[^1]。 --- #### 二、特殊场景与注意事项 1. **动态SQL拼接中的单引号处理** 在代码中拼接SQL时,需特别注意单引号的转义。例如: ```python # 用户输入 name = "John's Book" sql = f"INSERT INTO items (name) VALUES ('{name.replace("'", "''")}')" ``` 引用[3]和[4]展示了错误拼接可能导致语法错误或SQL注入风险[^3][^4]。 2. **与双引号的区分** - **双引号**用于包裹数据库对象名(如表名、字段名),尤其是包含特殊字符或保留字时: ```sql SELECT "用户ID" FROM "2023订单"; -- Oracle/PostgreSQL中常见用法 ``` - **单引号**与双引号可混用,否则会引发语法错误[^2]。 3. **数据库差异性** - **MySQL**:默认模式下允许双引号表示字符串,但建议统一使用单引号。 - **Oracle**:严格区分单双引号,字符串必须用单引号。 --- #### 三、性能影响与最佳实践 1. **索引与单引号的关系** 若字段为字符串类型但查询未加单引号,数据库可能无法使用索引(如数字字符串主键): ```sql SELECT * FROM users WHERE id = '1001'; -- 正确(假设id为VARCHAR) SELECT * FROM users WHERE id = 1001; -- 可能触发全表扫描 ``` 2. **参数化查询替代拼接** 为避免单引号转义问题,优先使用参数化查询(Prepared Statements): ```python cursor.execute("INSERT INTO logs (message) VALUES (%s)", (user_input,)) ``` ---
评论
添加红包

请填写红包祝福语或标题

红包个数最小为10个

红包金额最低5元

当前余额3.43前往充值 >
需支付:10.00
成就一亿技术人!
领取后你会自动成为博主和红包主的粉丝 规则
hope_wisdom
发出的红包
实付
使用余额支付
点击重新获取
扫码支付
钱包余额 0

抵扣说明:

1.余额是钱包充值的虚拟货币,按照1:1的比例进行支付金额的抵扣。
2.余额无法直接购买下载,可以购买VIP、付费专栏及课程。

余额充值