一 、基于行锁的优化
众所周知,Innodb的锁级别是细致到行锁的。所以,这么细粒度的锁是更加有利于数据库系统的并发的。
1.1、如何使用行锁
select * from ip_table where i_id = 6 for update;
二、避免行锁变表锁
2.1、索引失效:
对于varchar类型的数据,使用int来查询,将导致索引失效,如以下语句。
select * from userinfo where phone = 12698789784;
2.2、间隙锁:
尽量不出现不连续id,如果数据库中,存在不连续的id,那么进行范查找时,MySQL会自主的把范围内所有id锁住,即使id值不存在。所以,尽量在表更新时,不要去真实的去删除某一行,而是通过某个字段来表示数据是否已删除。
update userinfo set age = 18 where id >= 2 and id <=6;
注意:以上语句的问题在于,即使数据库中不存在id=3的行,我们在这条语句commit前,也休想往数据库中插入id=3的数据行。
二、具体实践上的优化
2.1、表设计的优化
1、为每一张表设置一个唯一id。
2、避免大事务操作,提高系统并发能力。
3、可以使用**数字类型**时,不使用**字符串**。
2.2、查询语句的优化
1、千万不要使用 order by rand()
。
2、不要使用select *
,坚持"要什么查什么"原则。
4、尽可能的使用not null
,如果一定想表示null可以用 0 作为默认值代替。
5、拆分大的Delete
一句Insert
语句。
6、在Join表的时候使用相当类型的列,并将其索引。
7、为搜索字段 (where
经常使用的字段) 建立索引。
8、不使用is null
判断,会导致放弃索引,全表搜索。
9、当只要一行数据时使用limit 1
。
10、explain
分析查询。
11、避免使用 in
查询,可以使用 between
代替。
12、缓存。
13、避免模糊的前缀查询,如以下语句:
select id from t where name like '%abc%';
14、避免使用 or
查询,可以使用 union all
来代替。
15、避免在where
后面,接上不定判断,比如使用操作表达式,使用函数,都将导致放弃索引。
16、应尽量避免在 where 子句中使用!=或<>操作符,否则将引擎放弃使用索引而进行全表扫描。
17、where
子句中,用 exists 代替 in 是一个好的选择。
20、如果排序字段,没用到索引,尽量少排序。