DM数据库添加索引查看优化效果

转载

风华正茂的AI 2024-11-28 12:16:52

文章标签 DM数据库添加索引查看优化效果 sql 字段 SQL 数据 文章分类 数据仓库大数据

数据库性能优化——索引

一、引入

说到查询性能的优化，我们首先会想到对数据表通过建立索引。这也是最基础的优化方式，它能根据查询要求，迅速缩小查询范围，减少数据的访问次数，从而可以优化数据库性能，避免全表扫描。

二、简单的实例

先来看一个有无创建索引来进行查找的简单实例，比较下两者的查询效率，能具象地了解索引对查询效率的提高程度。

SQL语句：

-- 判断是否存在表，若存在则删除
USE Test2
IF EXISTS(SELECT * FROM INFORMATION_SCHEMA.TABLES
      WHERE TABLE_NAME = 'objects')
   DROP TABLE objects                 -- 删除表
 
-- 判断是否存在索引，若存在则删除
USE Test2
IF EXISTS(SELECT name FROM sys.indexes
      WHERE name = 'object_id_ind')
   DROP INDEX objects.object_id_ind     -- 删除索引
 
-- 创建表
CREATE TABLE objects
(
 owner  varchar(30),
 object_name  varchar(50),
 object_id int,
 createdtime  datetime,
 status varchar(10)
);
--数据从本地文件objects.csv （数据总行数: 9588）导入到表中。
 
--无索引，直接查询，并查看其查询计划
SELECT * FROM objects WHERE object_id=1477;
 
-- 创建索引，再进行查询，并查看其查询计划
SET NOCOUNT OFF
CREATE UNIQUE CLUSTERED INDEXobject_id_ind  ON objects (object_id);
SELECT * FROM objects WHERE object_id=1477;

操作：

1.在SQL ServerManagement Studio中，点击“查询”→“显示估计的查询计划”，会出现如下图所示的执行计划图：

DM数据库添加索引查看优化效果_字段

2.点击表扫描，即可查看该操作的所需开销。

3.同样，创建索引后，也可查看其执行计划。

4.在有无索引的情况下的执行计划，如下图所示：

DM数据库添加索引查看优化效果_sql_02

小结：

从这个实例可以看出，把查询语句中where子句中的字段设置为索引时，查询效率有所提高。但由于数据量的缘故，效果不是太明显。

当然，一个表中可以建多个索引，如一本字典可以建多个目录一样（按拼音、笔划、部首等等）。一个索引也可以由多个字段组成，称为组合索引，如按部首+笔划的组合目录。这里不再具体展示。

三、设计索引的重要性

数据库索引的原理非常简单，但在复杂的表中真正能正确使用索引的人很少。因为天下没有免费的午餐，使用索引是需要付出代价的。

在设计索引时，我们需要考虑两个重要的因素：

1) 对某个属性使用索引能极大地提高对该属性上的值的检索效率，使用到该属性时，还可加快表的连接。

2) 对表上某个属性的索引会使得对表的数据插入、删除和修改变得复杂和费时，即大大增加表记录的DML(INSERT,UPDATE,DELETE)开销。

创建并使用正确的索引，可以减少数据的访问次数，其性能可提升100、1000倍以上。但是，不合理的索引也可能会让性能下降100倍。

因此在一个表中创建什么样的索引显得尤为重要，同时还需要平衡各种业务需求。

四、索引建立的字段

知道了索引的重要性，那一般在哪些字段需要建立索引呢？这是一个非常复杂的话题，需要对业务及数据充分分析后再能得出结果。这边可以提供给大家普遍的经验分享。

索引建立的字段：

主键和外键字段通常要建索引。注：SQL Server会为主键自动添加了聚集索引。

如果其它字段需要建索引，应满足以下条件：

1、字段出现在查询条件中，并且查询条件可以使用索引；

2、语句执行频率高，一天会有几千次以上；

3、通过字段条件可筛选的记录集很小，数据筛选比例需要根据表数据量来评估。

以下是牛人的经验公式，可用于快速评估：

小表(记录数小于10000行的表)：筛选比例<10%；

大表：(筛选返回记录数)<(表总记录数*单条记录长度)/10000/16

单条记录长度≈字段平均内容长度之和+字段数*2

以下是一些字段是否需要建B-TREE索引的分类：

	字段类型	常见字段名
需要建索引的字段	主键	ID,PK
	外键	PRODUCT_ID,COMPANY_ID,MEMBER_ID,ORDER_ID,TRADE_ID,PAY_ID
	有对像或身份标识意义字段	HASH_CODE,USERNAME,IDCARD_NO,EMAIL,TEL_NO,IM_NO
索引慎用字段,需要进行数据分布及使用场景详细评估	日期	GMT_CREATE,GMT_MODIFIED
	年月	YEAR,MONTH
	状态标志	PRODUCT_STATUS,ORDER_STATUS,IS_DELETE,VIP_FLAG
	类型	ORDER_TYPE,IMAGE_TYPE,GENDER,CURRENCY_TYPE
	区域	COUNTRY,PROVINCE,CITY
	操作人员	CREATOR,AUDITOR
	数值	LEVEL,AMOUNT,SCORE
	长字符	ADDRESS,COMPANY_NAME,SUMMARY,SUBJECT
不适合建索引的字段	描述备注	DESCRIPTION,REMARK,MEMO,DETAIL
不适合建索引的字段	大字段	FILE_CONTENT,EMAIL_CONTENT

五、SQL使用索引的场景

除了需要知道在哪些字段建立索引，我们还需要知道使用索引和不能使用索引的运算。

SQL使用索引的条件：

　当字段上建有索引时，通常以下操作会使用索引：

INDEX_COLUMN = ?   
INDEX_COLUMN > ?   
INDEX_COLUMN >= ?
INDEX_COLUMN < ?    
INDEX_COLUMN <= ?  
INDEX_COLUMN between ? and ?
INDEX_COLUMN in (?,?,...,?) 
INDEX_COLUMN like ?||'%'（后导模糊查询）
T1. INDEX_COLUMN=T2. COLUMN1（两个表通过索引字段关联）

SQL不能使用索引的条件：

查询条件	不能使用索引原因
INDEX_COLUMN <> ? INDEX_COLUMN not in (?,?,...,?)	不等于操作不能使用索引，查找的记录太多
function(INDEX_COLUMN) = ? INDEX_COLUMN + 1 = ? INDEX_COLUMN \|\| 'a' = ?	经过普通运算或函数运算后的索引字段不能使用索引
INDEX_COLUMN like '%'\|\|? INDEX_COLUMN like '%'\|\|?\|\|'%'	含前导模糊查询的Like语法不能使用索引
INDEX_COLUMN is null	B-TREE索引里不保存字段为NULL值记录，因此IS NULL不能使用索引
NUMBER_INDEX_COLUMN='12345' CHAR_INDEX_COLUMN=12345	Oracle在做数值比较时需要将两边的数据转换成同一种数据类型，如果两边数据类型不同时会对字段值隐式转换，相当于加了一层函数处理，所以不能使用索引。
a.INDEX_COLUMN=a.COLUMN_1	给索引查询的值应是已知数据，不能是未知字段值。