一、查询优化
对于查询优化,尽量要避免全表扫描,考虑在where,order by涉及的列上建立索引
1、尽量避免在where子句中对字段进行null值判断:
select id from t where num is null
优化后:
select id from t where num=0
2、尽量避免在where子句中使用!=或<>
3、尽量避免在where子句中使用or来连接条件:
select id from t where num=0
优化后:
select id from t where num=10
union all
select id from t where num=20
4、in和not in 也要慎用,能用between就不要用in了:
select id from t where num in (1,2,3)
优化后:
select id from t where num between 1 and 3
5、模糊查询,字母打头
SELECT id FROM t WHERE NAME LIKE '李%';
6、避免索引字段在where后面运算
SELECT id FROM t WHERE num/2=100;
SELECT id FROM t WHERE num=200;
7、避免索引字段进行函数操作
SELECT id FROM t WHERE SUBSTRING(NAME,1,3)='abc';
复合索引,必须使用第一个字段
CREATE TABLE IF NOT EXISTS student
(
id INT,
NAME VARCHAR(20),
age INT,
tel VARCHAR(15)
);
创建索引
CREATE INDEX ix_id_name ON student(id,NAME);
SELECT * FROM student WHERE id = 10;
SELECT * FROM student WHERE id = 10 AND NAME = 'li';
二、MySQL性能优化之参数配置
1、目的:
通过根据服务器目前状况,修改Mysql的系统参数,达到合理利用服务器现有资源,最大合理的提高MySQL性能。