1.避免使用select * from 查询,减少查询字段。示例:
优化前:
select * from test;
优化后:
select name,sex from test;
2.减少 in 和not in 的使用,改为exists和not exists。示例:
优化前:
SELECT * FROM table1
WHERE column1 IN (SELECT column2 FROM table2);
优化后:
SELECT * FROM table1
WHERE EXISTS (SELECT 1 FROM table2 WHERE table1.column1 = table2.column2);
3.使用索引查询,示例:
优化前:
SELECT * FROM table1 WHERE column1 = 'value1' AND column2 = 'value2';
优化后:
SELECT column1, column2 FROM table1 WHERE column1 = 'value1' AND column2 = 'value2';
4.使用like查询时,能用%就不建议使用双%
优化前:
select name from test where name like '%12%'
优化后:
select name from test where name like '12%'