SQL Optimization

SQL Optimization  （SQL 优化）

1) *   &&  field

SELECT * from sys_user
SELECT USER_ID, USER_NAME, EMAIL FROM SYS_USER;

栗子：

48.664s    142877rows

6.194s  142877rows

2）UNION      &&     UNION ALL

SELECT USER_ID, USER_NAME, EMAIL FROM SYS_USER
UNION
SELECT USER_ID, USER_NAME, EMAIL FROM SYS_USER  ORDER BY USER_ID;

SELECT USER_ID, USER_NAME, EMAIL FROM SYS_USER
UNION ALL
SELECT USER_ID, USER_NAME, EMAIL FROM SYS_USER ORDER BY USER_ID;

3） IN （1, ......., 1000,  .....  2000）j假设条件过多，拆分，UNION ALL

SELECT * FROM sys_user WHERE USER_ID IN (1, 2, 3, 4, 2000);

SELECT * FROM sys_user WHERE USER_ID IN (1, 2, 3, 4, 1000)
UNION ALL
SELECT * FROM sys_user WHERE USER_ID IN (1001, 2000);

4）batch  insert  （limited  500 rows）

INSERT INTO SYS_USER (USER_ID, USER_NAME) VALUES (1, ZWF), (2, 005129), (500, 13805029595);

5）limit

SELECT * FROM SYS_USER LIMIT 1;

6）分页

MYSQL分页：

SELECT column1, column2, ...FROM table_name ORDER BY column_name LIMIT offset, count;

SELECT * FROM sys_user ORDER BY user_id LIMIT 0, 10;
SELECT * FROM sys_user ORDER BY user_id LIMIT 10, 10;

ORACLE分页：

SELECT * FROM (
  SELECT a.*, ROWNUM rnum FROM (
    SELECT * FROM your_table ORDER BY some_column
  ) a
  WHERE ROWNUM <= :end_row
)
WHERE rnum > :start_row;

7）EXPLAIN

SELECT id FROM logic_case_detail;

EXPLAIN SELECT id FROM logic_case_detail;

8）

9）

10）

11）

12）

13）

14）

15）

16）