Java面试题之：sql优化方式

Java面试题之：sql优化方式

• 一、索引查询、避免全表扫描
• 二、查询数据尽量避免使用or
• 三、连续查询，能用between就用
• 四、where查询条件，对字段进行表达式操作
• 五、where查询条件，对字段进行函数操作
• 六、多张数据表查询数据，使用inner join
• 七、in()和exists()
• 八、使用like进行数据表查询时，能用%就不建议使用双%
• 九、最左优先
• 十、精确类型匹配
• 十一、表越小，查询越快
• 十二、数据量大时，合理使用分区表
• 十三、合理选择存储引擎
• 十四、慢查询日志

一、索引查询、避免全表扫描

查询数据库的数据尽量使用索引来查询，避免全表扫描。尽量只查询索引条件的字段，
例如：一张名为user用户数据表的id字段为索引，name字段为用户为非索引字段，当查询某条数据时，只查询id比查询name效率会高很多。因为叶子节点存储id的值，这样不用回表查询，实现了索引覆盖。
比如：查询select id from user 比select name from user效率高。

二、查询数据尽量避免使用or

从数据库中查询数据，sql语句尽量避免使用or来连接条件查询数据。因为使用or会导致执行sql的时候进行数据范围的索引扫描或者全表扫描，效率降低。
例如：select id from user where name=‘a’ or name=‘b’，可以改为以下的形式来提高查询的效率：select id from user where name=‘a’ union select id from user where name=‘b’；改了之后，两次查询都是走索引，效率相对较高。

三、连续查询，能用between就用

在连续数值的查询中，能使用between的情况下，尽量使用between，而不使用in。in和not in可能会导致全表查询。

四、where查询条件，对字段进行表达式操作

sql语句的where查询条件，对字段进行表达式的操作，会导致mysql引擎放弃使用索引而进行全表扫描。
例如：select * from user where age/2=10(age字段有索引)(整体不走索引)。

五、where查询条件，对字段进行函数操作

sql语句的where查询条件，对字段进行函数操作，也会导致mysql引擎放弃使用索引而进行全表扫描查询。
例如：select name from user where SUBSTR(name,2,3)=‘tom’;

六、多张数据表查询数据，使用inner join

多张数据表查询数据，使用inner join，left/right join来代替子查询。因为子查询需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。

七、in()和exists()

in()适合B表比A表数据小的情况，exists()适合B表比A表数据大的情况；
例如：select * from user a where exists (select name from user b where a.id=20)和select * from user a where id in(select id from user b where b.id=20)。

八、使用like进行数据表查询时，能用%就不建议使用双%

在使用like进行数据表的查询时，能用单%的情况下，不建议使用双%，双%查询会导致mysql引擎放弃使用索引而进行全表扫描查询，查询时尽量把%放后面，或者不适用%。

九、最左优先

在mysql建立联合索引时会遵循最左前缀匹配的原则，即最左优先，在检索数据时从联合索引的最左边开始匹配。左边匹配不到，整个sql不走索引。
例如：select * from user where u_one=‘1’ and u_two=‘2’;

十、精确类型匹配

从数据库中查询数据的时候，使用精确的类型匹配。
例如：select id from user where id=‘3’；如果id 建立的varchar类型的走索引，如果写成select id from user where id=3不走索引。

十一、表越小，查询越快

选取最使用的字段属性，一般来说，数据库中的表越小，执行的查询也就会越快。因此，在创建表的时候，为了获得更好的性能，我们可以将表中的字段的宽度设得尽可能小。另外一个提高效率的方法是在可能的情况下，应该尽量把字段设置为NOT NULL，这样在将来执行查询的时候，数据库不用去比较NULL值。

十二、数据量大时，合理使用分区表

数据量表较大时，合理使用分区表。
使用partition by 子句定义每个分区存放的数据。在执行查询的时候，优化器会根据分区定义过滤那些没有我们需要数据的分区，这样查询就无需扫描所有分区，只需要查找包含需要数据的分区就可以了。

十三、合理选择存储引擎

合理选择存储引擎。
Innodb:适合数据完整性，并发性控制，擅长更新，删除。
myisam:适合高速查询及插入。擅长插入和查询。

十四、慢查询日志

慢查询日志的使用，在调试的时候开启慢查询，定位的慢查询语句，再做优化策略。
关闭/开启语句 Slow_query_log=0|1，Long_query_time=N超过该时间临界点，就为慢查询。