<> Interviews are often asked SQL optimization
<>
As a back-end developer , It is essential to deal with the database in daily work , Whether in the project , Or change jobs to other companies in the future , about MySQL We must master the basic skills of , First share some common SQL Optimize small chestnuts for your reference , What's wrong? I hope you can leave a message below to discuss it together .
<>1. Don't put SELECT Write clause as SELECT *
SELECT * FROM t_emp;
<>2. yes ORDER BY Set index for sorted fields
<>3. Use less IS NULL
SELECT ename FROM t_emp WHERE comm IS NULL; # Do not use index
SELECT ename FROM t_emp WHERE comm =-1;
<>4. Use as little as possible != operator
SELECT ename FROM t_emp WHERE deptno!=20; # Do not use index
SELECT ename FROM t_emp WHERE deptno<20 AND deptno>20;
<>5. Use as little as possible OR operator
SELECT ename FROM t_emp WHERE deptno=20 OR deptno=30; # Do not use index
SELECT ename FROM t_emp WHERE deptno=20
UNION ALL
SELECT ename FROM t_emp WHERE deptno=30;
<>6. Use as little as possible IN and NOT IN operator
SELECT ename FROM t_emp WHERE deptno IN (20,30); # Do not use index
SELECT ename FROM t_emp WHERE deptno=20
UNION ALL
SELECT ename FROM t_emp WHERE deptno=30;
<>7. Avoid data type conversions in conditional statements
SELECT ename FROM t_emp WHERE deptno=‘20’;
<>8. Using operators and functions on the left side of an expression invalidates the index
SELECT ename FROM t_emp WHERE salary*12>=100000; # Do not use index
SELECT ename FROM t_emp WHERE salary>=100000/12;
SELECT ename FROM t_emp WHERE year(hiredate)>=2000; # Do not use index
SELECT ename FROM t_emp
WHERE hiredate>=‘2000-01-01 00:00:00’;
<> about SQL There are many more optimizations , I will call you in more detail later SQL Optimization details , and MySQL Knowledge of tuning .
Technology