开发中数据库查询的优化与索引的建立

最新推荐文章于 2022-07-23 18:35:11 发布

tomxiaodong

最新推荐文章于 2022-07-23 18:35:11 发布

阅读量709

点赞数

分类专栏： J2EE@VT 文章标签：数据库优化 postgresql 引擎 string null

本文链接：https://blog.csdn.net/tomxiaodong/article/details/5972115

版权

J2EE@VT 专栏收录该内容

5 篇文章 0 订阅

订阅专栏

数据库查询优化上，能做的工作有调优，删除同义词，优化网络状态，建立索引，优化查询语句，采用高效率语句编写程序，循环最小次数化等，数据库端主要是在索引的选择与建立上，据说 95% 的数据库性能问题都可以用索引技术得到解决；而程序端主要是 sql 语句的优化 ……

会使数据库索引失效和其他全表查询的查询语句

① 对 where 子句中对字段进行 null 值判断，这将导致引擎放弃使用索引，进行全表扫描

若有必要设置默认值，如“ 0 ”，改成 0 值判断

② 在 where 子句中使用！ = 或 <> 符号，这会导致引擎放弃使用索引，进行全表扫描。

③ 在 where 子句中使用 or ，这回使引擎放弃使用索引而进行全表扫描。

如不要使用 select * from NETIDS_EVENTLOG a where a.eventname=? or a.eventname=?

可以用 UNION ， select * from NETIDS_EVENTLOG a where a.eventname=? UNION ALL select * from NETIDS_EVENTLOG a where a.eventname=?

④ 在 where 子句中使用 in 或 not in 都会导致全表扫描，这里注意，连续的一个集合可以改用 between 子句

⑤ 模糊查询 like “%abc%” 也会导致全表扫描，当然，有时候模糊查询不可避免，大数据量的数据库要谨慎提供模糊查询功能。

⑥ 在 where 子句中使用参数会导致全表扫描 select * from NETIDS_EVENTLOG a where a.eventtime=@eventtime . 可以改用强制使用索引 select * from NETIDS_EVENTLOG a with(index(index_name))

⑦ 在 where 子句中使用表达式或在 where 子句中进行对字段进行函数操作，如： select * from NETIDS_EVENTLOG a where a.evnettime>=123456789*1000 或 select * from NETIDS_EVENTLOG a where substring(a.recID,2,6)=’AE12D’

⑧ 在 where 子句中使用复合索引必须使用索引的第一个字段才能保证系统使用该索引，尽可能的在 where 子句中让字段顺序与复合索引中建立索引的顺序一致。

⑨ 用 exists 子句代替 in 子句

⑩ 某些列有大量数据重复时不宜建立索引，如 sex 字段， NETIDS_EVENTLOG 中事件级别 eventLevel 字段， sex 是 male 和 female 几乎各一半， eventLevel 是高中低和非攻击事件各占 1/4 ，这样即使建了索引，查询效率也不会有提高。

11 ．需要频繁更新的列最好不要建立成聚集索引。

12 ．可以使用为数字类型的尽量不要使用 string 类型。如 NETIDS_EVENTLOG 中 IP 地址，发生时间，事件级别等都存为数字类型。使用 string 时引擎需要逐个比较字符串的每一个字符，同时尽量使用 varchar 和 nvarchar 代替 char 和 nchar ，减少存储空间和查询事件。

13 ．不要使用 select * ，不要返回用不到的字段。

14 ．避免使用游标

15 ．避免返回大量数据。

16 ．对需要经常查询和 order by 的字段一定要建立索引，例如在 NETIDS_EVENTLOG 的 eventtime 建立索引，而且根据需求，一般要建立降序的索引。另外，不要将两个查询 UNION 起来再排序，这会导致索引无效。

索引的建立原则

数据库没有对表的索引数量进行限制，但是作为 DBA ，一定要谨慎的建立和维护索引，虽然索引为查询提高了效率，但是增加了插入、删除和修改的开销。

索引的类型不同的数据库会有不同的原理和表现形式，一般来说，索引的类型从实现机制上讲包括（ POSTgreSQL ）： B-tree ， R-tree ， Hash 和 GiST ，一般来说，我们最常用到的是 B-tree 索引， B-tree 索引能处理 > < = >= <= 等， between 子句和 in 子句查询的字段上建立索引都能起到提高查询效率的作用。