当前位置:Gxlcms > mysql > 数据库设计和查询的一些简单优化_MySQL

数据库设计和查询的一些简单优化_MySQL

时间:2021-07-01 10:21:17 帮助过:46人阅读

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

人气教程排行