bitscn.com
搜集了网络上的一些资料，关于数据库设计和和查询方面的简单优化，整理如下：
设计方面
1、设计表和表之间的关联，能够降低数据的冗余，保证了数据的完整性。但是多表之间的关联查询，却会降低性能，查询速度较低，尤其是数据量非常大的时候。2、增加数据冗余，会加快系统的响应时间，提高查询速度，但是如果冗余数据更新不及时，就会造成数据的不一致。3、所以对表之间的关联需要合理设计，关联的数据量是否非常大，查询是否频繁，关联的数据是否经常改变都需要考虑，以做出合理的数据冗余，保证数据的一致性，提高查询速度。4、最好不要用自增字段作为主键与其他表进行关联，这样不利于数据的迁移和数据的恢复，也不利于数据库的分区。5、表字段的长度不要设计过长，最好根据实际的长度选择字段类型，设置合适的长度，这样可以提高查询效率，建立索引的时候也可以降低资源的消耗。6、能够使用数字类型就尽量使用数字类型，数据库引擎在处理和连接时会逐个比较字符串中的每一个字符，而对于数字类型，只需要比较一次。7、不可变字符类型char查询快，但是耗存储空间；可变字符类型varchar查询相对慢一些但节省存储空间。在设计时可以灵活选择，如用户名、密码长度变化不大的字段可以用char,而对于评论等长度变化大的字段可以用varchar。
查询方面
1、在保证功能的基础上，尽可能减少对数据库的访问次数；尽量减少对表的访问行数；尽量最小化结果集。
2、用到几列就选择几列，不过多使用通配符
--少使用select * from t_user;--用到几列选择几列select username,password from t_user;
3、用到几行结果集就返回几行结果集，降低网络负担
select username,password from t_user limit 2;
4、尽量避免使用!=和操作符，否则查询用不到索引而会进行全表扫描
--尽量避免使用 和 !=select * from t_user where username 'afei';select * from t_user where username != 'afei';
5、避免使用or来连接条件，否则查询用不到索引而会进行全表扫描
--尽量避免使用 orselect * from t_user where username = 'lihuai' or username = 'afei';--可以使用 union all 代替select * from t_user where username = 'lihuai' union allselect * from t_user where username = 'afei'
6、尽量避免使用in和not in，否则查询用不到索引而会进行全表扫描
--在in只有一个值时，还是可以用到索引mysql> explain select * from t_user where username in ('lihuai') /g;*************************** 1. row *************************** id: 1 select_type: simple table: t_user type: refpossible_keys: index_username key: index_username key_len: 18 ref: const rows: 1 extra: using where1 row in set (0.00 sec)
--在in中有两个以上的值时，无法再使用索引了select * from t_user where username in ('lihuai','afei');
--尽量避免使用not inselect * from t_user where username not in ('lihuai','afei');
7、在模糊查询中，避免前端模糊，否则无法使用索引而会进行全表扫描
#--避免使用前端模糊查询select * from t_user where username like '%lih%';select * from t_user where username like '%lih'#--后端端模糊查询可以用到索引select * from t_user where username like 'lih%'
mysql> explain select * from t_user where username like 'lih%' /g;*************************** 1. row *************************** id: 1 select_type: simple table: t_user type: rangepossible_keys: index_username key: index_username key_len: 18 ref: null rows: 2 extra: using where1 row in set (0.00 sec)
8、避免对查询字段进行表达式和函数操作，否则无法使用索引而会进行全表扫描
#--避免对字段进行表达式操作select * from t_user where age/2 = 20;--可以改成这样select * from t_user where age = 20*2;#--避免对字段进行函数操作select * from t_user where substr(username,1,3) = 'lih';#--可以改成这样select * from t_user where username like 'lih%';
9、很多时候可以使用exists 替代 in
--用inselect * from t_user t1 where t1.username in (select t2.username from t_temp t2 where t2.age=20);--用existsselect * from t_user t1 where exists (select 1 from t_temp t2 where t1.username=t2.username and t2.age=20);
两者的结果一样，但是exists 的效率好于in，exists 不会产生大量锁定的表扫描
bitscn.com