大厂高频MySQL高级面试题之优化

一 、基于行锁的优化

 众所周知，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、如果排序字段，没用到索引，尽量少排序。