Meituan Interview: What pitfalls have you encountered using MySQL?

Interviewer: You still have a good grasp of the lock.

Rookie me: (smiling slightly to express my response)

Interviewer: After using MySQL for so many years, what are the pitfalls that you will never forget.

Rookie me: Balabala starts to talk (I have already prepared this kind of interview question before the interview, so sprinkle it with water)

The following is based on my actual combat experience We have compiled some standardized usages of database development and summarized them with 6 "avoid".

1. Avoid doing calculations in the database

There is a saying called "Don't let your toes slide Thinking about things, that's the responsibility of Naoguazi", when used in database development, it means to avoid letting the database do things that it is not good at. MySQL is not good at mathematical operations and logical judgment, so try not to do operations in the database. Complex operations can be moved to the program-side CPU.

2. Avoid performing operations on index columns

Once, a colleague asked me to look at a SQL statement. It is said that querying at the front desk is very fast, but when I take out the SQL and execute it in the database, no result is produced even after 10 minutes of running. After looking at the SQL, I finally located a subquery in a view. The SQL text of this subquery is as follows:

## 以下SQL来源于网络
SELECT  acinv_07.id_item ,
        SUM(acinv_07.dec_endqty) dec_endqty
FROM    acinv_07
WHERE   acinv_07.fiscal_year * 100 + acinv_07.fiscal_period 
        = ( SELECT DISTINCT
                   ctlm1101.fiscal_year * 100 + ctlm1101.fiscal_period
                   FROM ctlm1101 WHERE flag_curr = 'Y'
                   AND id_oprcode = 'acinv'
                   AND acinv_07.id_wh = ctlm1101.id_table)
GROUP BY acinv_07.id_item

The columns fiscal_year and column fiscal_period on the acinv_07 table are indexed. However, if operations are performed on the index columns, the index will not be available for those that could have been indexed. So, I rewrote it into the following SQL:

## 以下SQL来源于网络
SELECT    id_item ,
                    SUM(dec_qty) dec_qty
          FROM      dpurreq_03
          GROUP BY  id_item
        ) a ,
        ( SELECT    a.id_item ,
                    SUM(a.dec_endqty) dec_endqty
          FROM      acinv_07 a ,
                    ( SELECT DISTINCT
                                ctlm1101.fiscal_year ,
                                ctlm1101.fiscal_period ,
                                id_table
                      FROM      ctlm1101
                      WHERE     flag_curr = 'Y'
                                AND id_oprcode = 'acinv'
                    ) b
          WHERE     a.fiscal_year = b.fiscal_year
                    AND a.fiscal_period = b.fiscal_period
                    AND a.id_wh = b.id_table
          GROUP BY  a.id_item

and then executed it. The result will be available in about 4 seconds. In general, when writing SQL, do not perform calculations on index columns unless absolutely necessary.

3. Avoid count(*)

When doing paging queries, some people always use Select count() obtains the total number of records. In fact, this is not an efficient approach, because the query has been made once before when obtaining the data, and select count() is equivalent to querying with the same statement. Two times, the overhead on the database will naturally be large. We should use the API that comes with the database or system variables to complete this work.

4. Avoid using NULL fields

Everyone should try their best to add NULL fields when designing database table fields. NOT NULL DEFAULT'. Using NULL fields will have many bad effects, such as: it is difficult to optimize queries, adding indexes to NULL columns requires additional space, and composite indexes containing NULL are invalid... Look at the following case:

数据初始化:
create table table1 (
    `id` INT (11) NOT NULL,
    `name` varchar(20) NOT NULL
)


create table table2 (
    `id` INT (11) NOT NULL,
    `name`  varchar(20)
)

insert into table1 values (4,"tianweichang"),(2,"zhangsan"),(3,"lisi")
insert into table2 values (1,"tianweichang"),(2, null)

(1) NOT IN subquery returns always an empty result when there is a NULL value, and the query is error-prone

select name from table1 where name not in (select name from table2 where id!=1)

(2) Column values ​​are allowed to be empty, the index does not store null values, and these records will not be included in the result set.

select * from table2 where name != 'tianweichang'

select * from table2 where name != 'zhaoyun1'

(3) When using concat to splice, each field must first be judged to be non-null, otherwise as long as any field is empty, the splicing result will be null

select concat("1", null) from dual;

(4) 当计算count时候,name为null 的不会计入统计

select count(name) from table2;

5、避免select

• 使用select *可能会返回不使用的列的数据。它在MySQL数据库服务器和应用程序之间产生不必要的I/O磁盘和网络流量。
• 如果明确指定列，则结果集更可预测并且更易于管理。想象一下，当您使用select *并且有人通过添加更多列来更改表格数据时，将会得到一个与预期不同的结果集。
• 使用select *可能会将敏感信息暴露给未经授权的用户。

6、避免在数据库里存图片

图片确实是可以存储到数据库里的，例如通过二进制流将图片存到数据库中。

但是，强烈不建议把图片存储到数据库中！！！！首先对数据库的读/写的速度永远都赶不上文件系统处理的速度，其次数据库备份变的巨大，越来越耗时间，最后对文件的访问需要穿越你的应用层和数据库层。

The above is the detailed content of Meituan Interview: What pitfalls have you encountered using MySQL?. For more information, please follow other related articles on the PHP Chinese website!