Interviews are often asked SQL optimization


<> 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
©2020-2024 ioDraw All rights reserved