项目开发之查询优化

需求背景

在刚开做项目的时候，由于数据量比较少，查询都是比较快的，当数据量大的时候，查询速度变得难以忍受。

怎么优化查询速度

对于优化查询速度，有一个简单且实用的方式就是对数据库表添加索引。而关于添加索引又有哪些问题呢？

1、对于一张表应该选择那些字段作为索引呢？

一般来说选择where、on、group by、order by后面的字段添加复合索引。

2、添加符合索引字段的顺序会影响查询速度吗？

会的。

如有一张表app_mycust，我们使用到的查询语句如下

select * from app_mycust where khbh= #{} and ghrgh= #{}

如果我们要对khbh和ghrgh建立索引是选择(khbh,ghrgh)好呢,还是(ghrgh,khbh)好呢。

select count(distinct ghrgh) / count(*) as ghrfhSelectivity,
count(distinct khbh) / count(*) as khbhSelectivity
from app_mycust;

查询结果如下：

发现khbh的选择性比ghrgh要高，所以我们会这样建立索引：

create index index_name on app_mycust(khbh,ghrgh);

当查询条件较多时，也并不是选择所有的字段作为索引，只需要选择其中的部分字段就行了，通常情况下少数的几个字段就能满足查询速度的要求了。

索引失效问题

当你对表建立了索引，并不代表你的sql语句一定就会走索引。

以下是我在项目中和网上搜索到的一些索引可能会失效的原因：

1、Where子句中进行”!= ”、”>”、”<”、”between”这样这样的操作

2、Where中的索引的开始列没有用到、或者中间列缺少,不满足最左匹配原则

3、使用了”like %xx”这样的查询

4、索引字段格式错误，若一个字段是字符类型，查询时使用数值型，就会使索引失效

5、对索引字段使用内部函数或表达式操作

6、根据索引列查出的数据较多，mysql放弃使用而使用全表扫描

7、使用or连接查询条件，没有对or连接的全部字段加索引

8、对order by后面的字段添加索引，但sql语句中的字段顺序与索引顺序不一致。

mysql查询优化的一些建议（搜集自互联网）

1、查询优化，避免走全表扫描，可以exlpain sql语句来查看sql语句的查询计划。

2、应尽量避免在 where 子句中使用or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描，如：select id from t where num=10 or num=20可以这样查询：select id from t where num=10 union all select id from t where num=20

3、between替换 in 或 not in

4、可考虑在使用”like %xx”的地方使用全文索引

5、需要查上面就选择什么，避免select * from xxx

6、尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高些。

7、避免”大查询”，当返回的数据量过大时，查询、网络传输、客户端解析显示都会变慢，考虑使用分页查询，而且一般用户一次性也看不了那么多数据。

索引存在的问题

索引虽然可以加快查询速度，但是需要耗费内存空间、索引会使得表的删除、添加变的更加慢。因为在执行这些操作时需要去维护索引的树结构。