简介
无论是开发人员,还是数据库运维人员,一定要遵守规范,养成好的写SQL习惯,会让你工作更加顺风顺水,让你不再给人挖坑。
![75eba96b2e035124f94f2001d54d9a34.png](https://i-blog.csdnimg.cn/blog_migrate/28350956a6e55d985811a43fc89ff2b7.jpeg)
举例表结构
以下所有的SQL涉及的表结构如下所示
mysql> show create table t_test_1G;*************************** 1. row *************************** Table: t_test_1Create Table: CREATE TABLE `t_test_1` ( `id` int(11) NOT NULL, `name` char(10) NOT NULL DEFAULT '', `name2` char(15) NOT NULL DEFAULT '', `status` int(11) NOT NULL DEFAULT '0', PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.00 sec)
习惯一:INSERT语句标明对应的字段名称
在代码里,写insert语句,一定要写要插入数据的字段名称。
不好的习惯,不写字段名称
insert into t_test_1 values(1,'name1','name2',1);
好的习惯,明确写明要插入数据的字段名称
insert into t_test_1(id,name,name2,status) values(1,'name1','name2',1);
可是为什么不写字段名称,是不好的习惯呢,在这里举一个实际的案例:假如明天晚上20点应用要做变更上线,这次上线之前,需要在t_test_1表里添加字段('pay_type'),正常上线流程,肯定是DBA先做表结构变更,然后再做应用上线。好了,下面来做一次变更操作
![78245f84f77a4e3ea5a6cdba533b833e.png](https://i-blog.csdnimg.cn/blog_migrate/e115c8f9c4dee82f3a2aa416adb403b3.jpeg)
1.表(t_test_1)结构变更
mysql> alter table t_test_1 add pay_type smallint(1) not null default '0';Query OK, 0 rows affected (0.37 sec)Records: 0 Duplicates: 0 Warnings: 0mysql> show create table t_test_1G;*************************** 1. row *************************** Table: t_test_1Create Table: CREATE TABLE `t_test_1` ( `id` int(11) NOT NULL, `name` char(10) NOT NULL DEFAULT '', `name2` char(15) NOT NULL DEFAULT '', `status` int(11) NOT NULL DEFAULT '0', `pay_type` smallint(1) NOT NULL DEFAULT '0', PRIMARY KEY (`id`)) ENGINE=InnoDB DEFAULT CHARSET=utf81 row in set (0.01 sec)
2.表结构添加好字段之后,应用还没有上线前,会发生什么呢?
mysql> insert into t_test_1 values(2,'name2','name2',2);ERROR 1136 (21S01): Column count doesn't match value count at row 1
看到了吧,由于t_test_1表添加了一个新字段,这个时候执行insert语句,就会报错了,这个时候,估计会一大堆地告警了,排查之后,只能删除字段,回滚上线了,而且删除字段,会再次导致业务堵塞,一个线上故障就这么发生了,做为一个开发人员,你还敢这么写insert语句吗?
避免隐式转换
写SQL语句时,一定要注意隐式转换,这不仅仅会导致查询性能问题,而且会导致查询结果不正确。导致查询性能问题,估计大部分朋友有所了解,可是导致查询结果不正确,估计有一部分朋友不太了解。下面就来举例说明一下。
1.测试SQL语句
select * from t_test_1 where name2=1001
来看看测试SQL语句的执行计划
mysql> explain select * from t_test_1 where name2=1001;+----+-------------+----------+------------+------+--------------------+------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+----------+------------+------+--------------------+------+---------+------+------+----------+-------------+| 1 | SIMPLE | t_test_1 | NULL | ALL | idx_t_test_1_name2 | NULL | NULL | NULL | 20 | 10.00 | Using where |+----+-------------+----------+------------+------+--------------------+------+---------+------+------+----------+-------------+1 row in set, 3 warnings (0.02 sec)mysql> mysql> show warnings;+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Level | Code | Message |+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+| Warning | 1739 | Cannot use ref access on index 'idx_t_test_1_name2' due to type or collation conversion on field 'name2' || Warning | 1739 | Cannot use range access on index 'idx_t_test_1_name2' due to type or collation conversion on field 'name2' || Note | 1003 | /* select#1 */ select `testdb`.`t_test_1`.`id` AS `id`,`testdb`.`t_test_1`.`name` AS `name`,`testdb`.`t_test_1`.`name2` AS `name2`,`testdb`.`t_test_1`.`status` AS `status` from `testdb`.`t_test_1` where (`testdb`.`t_test_1`.`name2` = 1001) |+---------+------+-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------------+3 rows in set (0.00 sec)
看到了吧,Cannot use range access on index 'idx_t_test_1_name2' due to type or collation conversion on field 'name2' ,因为隐士转换,导致无法使用索引。这个SQL不仅仅只有这个问题,还有一个更严重的问题,查询结果不正确。
mysql> select * from t_test_1 where name2=1001;+----+--------+-------+--------+| id | name | name2 | status |+----+--------+-------+--------+| 1 | name1 | 1001 | 0 || 10 | name10 | 1001 | 0 || 11 | name11 | 1001 | 2 || 12 | name12 | 1001C | 3 |+----+--------+-------+--------+4 rows in set, 1 warning (0.01 sec)
在这里多查询了一条记录(id=12),所以好的习惯是如果字段定义成字符串,则查询条件一定要带单引号
mysql> select * from t_test_1 where name2='1001';+----+--------+-------+--------+| id | name | name2 | status |+----+--------+-------+--------+| 1 | name1 | 1001 | 0 || 10 | name10 | 1001 | 0 || 11 | name11 | 1001 | 2 |+----+--------+-------+--------+3 rows in set (0.00 sec)
每个表必须要有主键
数据库上每张表,必须要设置主键,这个非常重要,如果不设置主键,会导致表中数据记录重复,导致MySQL数据库的主从复制延迟。
![50e22426dae043b653f35d4aa54da197.png](https://i-blog.csdnimg.cn/blog_migrate/0d5533cedc178be5d77a16b92106b03c.jpeg)
禁止用select *
禁止用select * ,这个很多开发人员知晓,避免用select * 可以节省资源、减少网络开销。可是能节省多少呢,没有一个概念,这里来举个极端的例子。
假设有一张带有一个blob字段的表,这个blob字段存储了大约500M的数据,如果前端用分页展示,每页显示20条记录,但是不需要显示这个blob字段信息,但是开发人员用select * ,导致向前端返回20条带有blob字段信息,这20条记录总大小:20*500M=10G,看到了,返回了10G的数据,这个数据非常有可能直接将后端应用程序就给弄内存溢出。
关注
1.如果您喜欢这篇文章,请点赞+转发。
2.如果您特别喜欢,请加关注。