mysql varchar 非空判断_工资从1万到3万，你还差mysql数据库优化之系列三

查询性能的优化

优化查询分析的步骤:

1.应用查询是否检索超过需要的数据

2.mysql服务器是否在分析超过需要的数据

正确使用索引:

1.like语句操作

一般不使用%或_开头例如: select * from tableName where name like '%cn';只能使用like 'aaa%';

2.组合索引

例如索引index index_name (a, b, c)  只支持a 或 a, b 或 a, b, c者三种组合,不支持b,c, 最左前缀

3.避免在where子句中对null进行判断

例如:select id from t where num is null修改:可以num上设置默认值为0select id from t where num=0;

4.尽量避免使用where子句使用!=,<>,or

5.in和not in也要谨慎使用

例如:select id from t where num in(1,2,3);修改:对于连续的数值,可以使用betweenselect id from t where num between 1 and 3;

6.如果在where子句中使用参数,也会导致全表扫描

mysql在运行时候才会解析变量,sql语句的优化在编译的时候就进行了,但是编译的时候这个变量还是未知的例如: select id from t where num=@num;修改:强制使用索引select id from t with(index(索引名)) where num=@num;

7.尽量避免在where子句的=左面进行运算

例如:select id from t where num/2=100;修改:select id from t where num = 100*2;例如:select id from t where substring(name, 1, 3)='abc';修改:select id from t where name like 'abc%';

其他的优化点:

1.尽量使用数字类型的字段

2.把ip存储为unsigned int

使用int类型4个字节,比char/varchar更节省空间,查询时候也会占用优势

3.在join表要使用相当类型的列,并将其建立索引

例如:select * from users left join company on users.state=company.state两个state都要建立索引,并且使用相当类型,使用相同的字符集

4.尽量避免使用order by rand()

例子: explain select * from test order by rand();

5.避免使用select *

6.一定要为每张表设置id为主键,最好是int类型,并且最好设置auto_increment

7.优化count()查询

统计某列值的数量,要求列值为非空,也就是不会统计null统计行数如果确定count()没有空值,实际是在统计行数统计行数时候直接使用count(*)

8.不要添加冗余或者是重复索引,删除长期不用的索引

例如:创建了联合索引(A,B), 不要再单独为(A)创建索引

9.尽量避免使用游标,因为通常情况下游标的效率比较差

10.尽量避免大事务操作,会降低并发性能

11.优化or条件

例如:create table test6(id int not null,name varchar(20) not null,index index_id (id),index index_name(name))插入数据:insert into test6 values(1, 'zhangsan');insert into test6 values(2, 'lisi');使用or查询:explain select * from test6 where id=1 or name='lisi';使用union all优化explain select * from test6 where id=1 union all select * from test6 where name='lisi';

12.优化分页查询(limit)

例子:创建表:create table test7(id int not null,username varchar(16) not null,index index_id(id))插入100000条数据的函数:DELIMITER #CREATE PROCEDURE pro2()BEGIN DECLARE i INT DEFAULT 0; WHILE i<10000 DO  INSERT INTO test7 VALUES(i, CONCAT('aa', i));    SET i=i+1; END WHILE;END #调用函数CALL pro2();分页查询数据:explain select * from test7 order by id limit 1000, 20;优化:explain select * from test7 inner join (select id from test7 order by id limit 1000, 20) as o using(id);上面的优化可以改写为:explain select * from test7 t inner join (select id from test7 order by id limit 1000, 20) as o on t.id=o.id;