MySQL优化之“数据类型不一致”的问题

由于最近项目各方面功能都完成了,闲着没事,上司让我们去简单做一下MySQL语句优化,我这个人对自己要求很高,那我肯定是想要达到DBA级优化的效果对吧!

 

由于我们是做MySQL语句优化,需要用到两种MySQL日志:

1、MySQL慢查询日志

2、MySQL通用查询日志

 

注意:

1、以上两种日志很关键。你优化的时候,至少要打开MySQL慢查询日志这一种。

2、还需熟悉:MySQL Explain(执行计划)详解

 

在这两种日志文件,我发现了我有一个表的SQL语句有大问题:

`category`.`id` 主索引

`category`.`name` 唯一索引 varchar(20)

`category`.` alias ` 唯一索引 varchar(20)

 

最初的SQL语句:

mysql> EXPLAIN SELECT `id`,`alias` FROM `category` WHERE `alias` = 77 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: category
   partitions: NULL
         type: index
possible_keys: alias
          key: alias
      key_len: 62
          ref: NULL
         rows: 8
     filtered: 12.50
        Extra: Using where; Using index
1 row in set, 3 warnings (0.00 sec)

 

优化后的SQL语句:

mysql> EXPLAIN SELECT `id`,`alias` FROM `category` WHERE `alias` = '77' \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: category
   partitions: NULL
         type: const
possible_keys: alias
          key: alias
      key_len: 62
          ref: const
         rows: 1
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

 

如果你有详细了解 MySQL Explain ,我相信你已经看出:由于“mysql数据类型不一致”对SQL优化造成了多大的影响了。

 

于是我联想到了:Mysqli 的 bind_param() 函数的问题,结果去看项目代码,发现有部份的SQL语句,有很多字段类型都没有一 一对应起来。

通过查看以上两种日志文件,我发现只要你正确使用了 Mysqli 的 bind_param() 函数,它生成的原生SQL都是很正常的,替换时:字符串就是字符串,数字就是数字,如下:

2021-01-02T05:20:56.098424Z	   45 Prepare	SELECT `id`,`alias` FROM `category` WHERE `alias` = ?
2021-01-02T05:20:56.098444Z	   45 Execute	SELECT `id`,`alias` FROM `category` WHERE `alias` = 'aaa'

 

还有一点需要注意:

经过我大量的Mysql语句优化测试,“数据类型不一致”还会造成  Explain key_len 长度不一致,甚至是部份索引不到......等等问题。例如,遇到以下这种联合查询语句:

EXPLAIN SELECT * FROM `user_group` INNER JOIN `user_groupmap` on `user_groupmap`.`user_group_id` = `user_group`.`id` WHERE `user_id` = 71

你就得必须保证:所有字段的类型一样。

`user_group`.`id` 主索引 bigint(20)

`user_groupmap`.`user_group_id` 普通索引 bigint(20)

`user_groupmap`.`user_id ` 普通索引 bigint(20)

重大提醒:

千万不要这个表用 bigint(20),那个表又用 int(20),甚至其它表又用 tinyint(4)。其它类型同理。

 

总结:

MySQL优化之“数据类型不一致”这个问题解决其实很简单,只要你自己处理SQL语句的时候,写准确一点就可以了。我的建议是还是希望大家用:Mysqli 的 bind_param() 函数来替换,它可以完美的解决MySQL优化之“数据类型不一致”这个问题。

    A+
发布日期:2021年01月02日 16:37:35  所属分类:MySQL
最后更新时间:2021-01-03 06:32:20
评分: (3 票;平均数2.33 ;最高评分 5 ;用户总数3;总得分 7;百分比46.67)
付杰
前端工程化构建工具:Babel7+Webpack4
  • ¥ 15元
  • 市场价:15元
Sublime Text3使用视频教程:神级代码编辑工具
  • ¥ 15.0元
  • 市场价:15.0元
C++基础入门到进阶视频教程
  • ¥ 498.0元
  • 市场价:498.0元
微信小程序前后端全栈开发 (Node.js+Express)
  • ¥ 39.0元
  • 市场价:39.0元

发表评论

:?: :razz: :sad: :evil: :!: :smile: :oops: :grin: :eek: :shock: :???: :cool: :lol: :mad: :twisted: :roll: :wink: :idea: :arrow: :neutral: :cry: :mrgreen: