**「reason:」**
* Primary keys are usually added. Tables without primary keys have no soul
* It is recommended to add the creation time and update time. Detailed audit and tracking records are useful.
Ali's development manual also mentions this point, as shown in the figure
8,finish writing sth. SQL Statements, checking where,order by,group by For the following columns, whether the columns associated with multiple tables have been indexed, the combined index is given priority. ( SQL Performance optimization)
**「Counterexample:」**
select * from user
where address = 'Shenzhen' order by age;
 **「Positive example:」**
Add index
alter table user add index idx_address_age (address,age)
 9,Before modifying or deleting important data, backup first, backup first( SQL Regret medicine) If you want to modify or delete data, execute SQL Be sure to back up the data to be modified before operation. In case of misoperation, you can eat**「regret」**~ 10,where For the following fields, pay attention to the implicit conversion of their data types( SQL Performance optimization) **「Counterexample:」**
//userid is a varchar string type
select * from user where userid =123;
 **「Positive example:」**
select * from user where userid ='123';
**「reason:」**
Because when no single quotation mark is added, it is the comparison between string and number, and their types do not match, MySQL Will do implicit type conversion, convert them to floating-point numbers, and then compare them, resulting in index invalidation
11,Try to define all columns as NOT NULL(SQL Standard (elegant)
* **「NOT NULL Columns save more space」**，NULL Column needs an extra byte to determine whether it is NULL Flag bit of.
* **「NULL Columns need to pay attention to null pointers」**，NULL When calculating and comparing columns, you need to pay attention to null pointers.
12,Modify or delete SQL，Write first WHERE Check it and add it after confirmation delete or update(SQL Regret medicine)
Especially when operating production data, it is difficult to modify or delete SQL，Add one first where Check and confirm OK Then execute update perhaps delete operation
13,Reduce unnecessary field returns, such as using select <Specific field> replace select \* (SQL Performance optimization)
**「Counterexample:」**
select * from employee;
**「Positive example:」**
select id，name from employee;
reason:
* Save resources and reduce network overhead.
* Overlay indexes may be used to reduce back to table and improve query efficiency.
14,All tables must use Innodb Storage engine( SQL Standard (elegant)
Innodb **「Support transactions and row level locks for better recoverability」**，High and better performance, so there are no special requirements (i.e Innodb In case of functions that cannot be met (such as column storage, storage space data, etc.), all tables must be used Innodb Storage engine
15,The character set of database and table shall be used uniformly as far as possible UTF8(SQL Standard (elegant)
Try to use it uniformly UTF8 code
* It can avoid the problem of garbled code
* It can avoid the index failure caused by the comparison and conversion of different character sets
**「If you need to store expressions, select utf8mb4 To store, pay attention to its relationship with utf-8 Coding differences.」**
begin;
update account set balance =1000000
where # name = 'little boy picking up snails';
commit;
16,Try to use varchar replace char. (SQL Performance optimization) **「Counterexample:」**
deptName char(100) DEFAULT NULL COMMENT 'department name'
**「Positive example:」**
deptName 'varchar(100) DEFAULT' NULL 'COMMENT' department name '
reason:
* Because the storage space of variable length fields is small, the storage space can be saved.
17,If you modify the meaning of the field or append the status represented by the field, you need to update the field comments in time. ( SQL Standard (elegant)
This point is in Ali's development manual, Mysql The statute of. If the meaning of your field, especially when it represents the enumeration status, is modified or the status is added, the comments of the field need to be updated immediately for better maintenance later.
18,SQL Modify data from the command line begin + commit Business habits(SQL regret)
**「Counterexample:」**
update account set balance =1000000
where # name = 'little boy picking up snails';
**「Positive example:」**
begin;
update account set balance =1000000
where # name = 'little boy picking up snails';
commit;
19,The index name should be standardized. The primary key index name is pk\_ Field name; The unique index name is uk \_Field name; The normal index name is idx \_Field name. ( SQL Standard (elegant) explain: pk\_Namely primary key；uk\_Namely unique key；idx\_Namely index Short for. 20,WHERE The column is not subjected to function conversion and expression evaluation in the clause hypothesis loginTime Indexed **「Counterexample:」**
select userId,loginTime
from loginuser
where Date_ADD(loginTime,Interval 7 DAY) >=now();
**「Positive example:」**
explain select userId,loginTime
from loginuser
where loginTime >= Date_ADD(NOW(),INTERVAL - 7 DAY);
**「reason:」** Use on index columns mysql The index is invalid because of the built-in function of  21,If modified/There is too much data to update. Consider batch updating. **「Counterexample:」**
delete from account limit 100000;
**「Positive example:」**
How to quickly update your technology accumulation?
- In the existing projects, deep excavation technology, such as using netty, can make the relevant underlying code and key points appear.
- If you don't know the current direction, it depends on what your leaders or skilled people in the company are learning.
- After knowing the direction of effort, I don't know how to learn, so I look for relevant materials everywhere and practice.
- If you don't know whether you have achieved anything after study, you can test it through the interview.
Personally, I think the interview is also like a new journey. Failure and victory are common things. Therefore, I advise you not to lose heart and morale because of the failure of the interview. Don't be complacent because you passed the interview. What's waiting for you will be a better future. Keep going!
The answers to the above interview topics have been compiled into interview documents. There are detailed answers in the documents, as well as some other interview questions of large factories. Friends in need Click here to get it for free
You can look at the underlying code and key points.
- If you don't know the current direction, it depends on what your leaders or skilled people in the company are learning.
- After knowing the direction of effort, I don't know how to learn, so I look for relevant materials everywhere and practice.
- If you don't know whether you have achieved anything after study, you can test it through the interview.
Personally, I think the interview is also like a new journey. Failure and victory are common things. Therefore, I advise you not to lose heart and morale because of the failure of the interview. Don't be complacent because you passed the interview. What's waiting for you will be a better future. Keep going!
The answers to the above interview topics have been compiled into interview documents. There are detailed answers in the documents, as well as some other interview questions of large factories. Friends in need Click here to get it for free
[external chain picture transferring... (img-1IzEV1A0-1628296895654)]
[external chain picture transferring... (img-wluJdm1n-1628296895656)]