SQLServer地址搜索性能优化例子分分快三全天计划

作者:分分快三全天计划网站

2.创建表和插入数据

 2.1 创建数据表TBAddress

分分快三全天计划网站 1分分快三全天计划网站 2

use test;
go
/* create table */
if object_id('TBAddress') is not null
   drop table TBAddress;
go
create table TBAddress
(
  ID int ,
  Parent int not null ,
  LevelNo smallint not null ,
  Name nvarchar(50) not null ,
  constraint PK_TBAddress primary key ( ID )
);
go
create nonclustered index ix_TBAddress_Parent on TBAddress(Parent,LevelNo) include(Name) with(fillfactor=80,pad_index=on);
create nonclustered index ix_TBAddress_Name on TBAddress(Name)include(LevelNo)with(fillfactor=80,pad_index=on);
go

create table

2.2 插入数据

use test
go
/*insert data*/
set nocount on
Begin Try
    Begin Tran
    Insert Into TBAddress ([ID],[Parent],[LevelNo],[Name])
        Select 1,0,0,N'中国' Union All 
        Select 2,1,1,N'直辖市' Union All 
        Select 3,1,1,N'辽宁省' Union All 
        Select 4,1,1,N'广东省' Union All 
        ... ...
        Select 44740,930,4,N'奥依塔克镇' Union All 
        Select 44741,932,4,N'巴音库鲁提乡' Union All 
        Select 44742,932,4,N'吉根乡' Union All 
        Select 44743,932,4,N'托云乡'
    Commit Tran
End Try
Begin Catch
    throw 50001,N'插入數據過程中發生錯誤.' ,1
Rollback Tran
End Catch
go

附件: insert Data

 Note: 数据有44700条,insert代码比较长,所以采用附件形式。

因为默认的存储引擎为InnoDB,而全文索引只支持MyISAM,所以这里创建表的时候要手动指定一下引擎。

3.测试,方法1

3.1 分析:

 分分快三全天计划网站 3

a. 先搜索出包字段Name中含有“广”、“大”的所有地址记录存入临时表#tmp。

  b. 再找出#tmp中各个地址到Level 1的全路径。

    c. 根据步骤2所得的结果,筛选出包含有“广”和“大”的地址路径。

      d. 根据步骤3筛选的结果,查询所有到Level n(n为没有子地址的层编号)的地址全路径。

3.2 存储过程代码:

分分快三全天计划网站 4分分快三全天计划网站 5

Use test
Go
if object_ID('[up_SearchAddressByNameV0]') is not null
    Drop Procedure [up_SearchAddressByNameV0]
Go
create proc up_SearchAddressByNameV0 
(
    @Name nvarchar(200)
)
As
set nocount on
declare @sql nvarchar(max)

declare @tmp Table (Name nvarchar(50))

set @Name=@Name ' '

while patindex('%  %',@Name)>0
begin
    set @Name=replace(@Name,'  ',' ')    
end

set @sql ='select '''  replace(@Name,' ',''' union all select ''') ''''
insert into @tmp(Name) exec(@sql)

if object_id('tempdb..#tmp') is not null drop table #tmp
if object_id('tempdb..#') is not null drop table #

create table #tmp(ID int )


while @Name>''
begin
    insert into #tmp(ID)
    select a.ID  from TBAddress a where a.Name like '%' substring(@Name,1,patindex('% %',@Name)-1) '%' 

    set @Name=Stuff(@Name,1,patindex('% %',@Name),'')
end


;with cte_SearchParent as
(
    select a.ID,a.Parent,a.LevelNo,convert(nvarchar(500),a.Name) as AddressPath from TBAddress a where exists(select 1 from #tmp x where a.ID=x.ID) 
    union all
    select a.ID,b.Parent,b.LevelNo,convert(nvarchar(500),b.Name '/' a.AddressPath) as AddressPath
        from cte_SearchParent a
        inner join TBAddress b on b.ID=a.Parent
            --and b.LevelNo=a.LevelNo -1
            and b.LevelNo>=1
)
select a.ID,a.AddressPath 
    into #
    from cte_SearchParent  a 
    where a.LevelNo=1 and exists(select 1 from @tmp x where a.AddressPath like '%' x.Name '%' having count(1)=(select count(1) from @tmp))

;with cte_result as
(
    select a.ID,a.LevelNo,b.AddressPath
        from TBAddress a 
            inner join # b on b.ID=a.ID
    union all
    select b.ID,b.LevelNo,convert(nvarchar(500),a.AddressPath '/' b.Name) As AddressPath
        from cte_result a
            inner join TBAddress b on b.Parent=a.ID
                --and b.LevelNo=a.LevelNo 1

)
select distinct a.ID,a.AddressPath 
    from cte_result a 
    where not exists(select 1 from TBAddress x where x.Parent=a.ID)
    order by a.AddressPath 
Go

procedure:up_SearchAddressByNameV0

 3.3 执行查询:

exec up_SearchAddressByNameV0 '广 大'

分分快三全天计划网站 6

共返回195行记录。

3.4 客户端统计信息:

分分快三全天计划网站 7

平均的执行耗时:  244毫秒

ALTER TABLE table_name DROP EXISTS index_name;ALTER TABLE table_name DROP INDEX IF EXISTS index_name;

5.测试,方法3

在方法2中,我们在Name列上创建全文索引提高了查询性能,但我们不仅仅局限于一两个方法,下面我们介绍第3个方法。

第3个方法,通过修改表的结构和创建全文索引。在表TBAddress增加多一个字段FullPath存储各个地址到Level 1的全路径,再在FullPath列上创建全文索引,然后直接通过全文索引来搜索FullPath列中包含“广”和“大”的记录。

5.1 新增加字段FullPath,并更新列FullPath数据:

use test;
go
/*alter table */
if not exists ( select 1
                        from sys.columns a
                        where a.object_id = object_id('TBAddress')
                                and a.name = 'Fullpath' )
   begin
         alter table TBAddress add Fullpath nvarchar(200);
   end;
go
create nonclustered index IX_TBAddress_FullPath on dbo.TBAddress(Fullpath) with(fillfactor=80,pad_index=on);
go
/*update TBAddress */
with    cte_fullPath
          as ( select ID, Parent, LevelNo, convert(nvarchar(500), isnull(Name, '')) as FPath, Fullpath
                from dbo.TBAddress
                where LevelNo = 1
               union all
               select A.ID, A.Parent, A.LevelNo, convert(nvarchar(500), B.FPath   '/'   isnull(A.Name, '')) as FPath, A.Fullpath
                from TBAddress as A
                        inner join cte_fullPath as B on A.Parent = B.ID
             )
     update a
        set     a.Fullpath = isnull(b.FPath, a.Name)
        from dbo.TBAddress a
                left join cte_fullPath b on b.ID = a.ID;
go

5.2 在列FullPath添加全文索引:

alter fulltext index on dbo.TBAddress add(Fullpath language 2052)

5.3 存储过程代码:

分分快三全天计划网站 8分分快三全天计划网站 9

Use test
Go
if object_ID('[up_SearchAddressByNameV2]') is not null
    Drop Procedure [up_SearchAddressByNameV2]
Go
create proc up_SearchAddressByNameV2
(
    @name nvarchar(200)
)
As
declare @contains nvarchar(500)
set nocount on
set @contains='"' replace(@Name,' ','*" And "') '*"'

select id,FullPath As AddressPath from TBAddress a where contains(a.FullPath,@contains) and not exists(select 1 from TBAddress x where x.Parent=a.ID) order by AddressPath

Go

procedure:up_SearchAddressByNameV2

5.4 测试存储过程:

exec up_SearchAddressByNameV2 '广 大'

分分快三全天计划网站 10

共返回195行记录。

5.5 客户端统计信息:

分分快三全天计划网站 11

平均的执行耗时:  20.4毫秒

DROP INDEX index_name ON table_nameDROP INDEX IF EXISTS index_name ON table_name

7.小结

这里通过一个简单的表格,对方法1至方法4作比较。

 分分快三全天计划网站 12

从平均耗时方面分析,一眼就知道方法3比较符合开始的需求(耗时要控制在几十毫秒内)。

当然还有其他的方法,如通过程序实现,把数据一次性加载至内存中,再通过程序写的算法进行搜索,或通过其他工具如Lucene来实现。不管哪一种方法,我们都是选择最优的方法。实际的工作经验告诉我们,在实际应用中,多选择和测试不同的方法来,选择其中一个满足我们环境的,而且是最优的方法。

 

ALTER TABLE customer1 ADD UNIQUE INDEX `idx_customer_id` (`customer_id`);

4.测试,方法2

 方法2是参照方法1,并借助全文索引来优化方法1中的步骤1。也就是在name列上建立全文索引,在步骤1中,通过全文索引搜索出包字段Name中含有“广”、“大”的所有地址记录存入临时表#tmp,其他步骤保持不变。

 4.1 创建全文索引

use test
go
/*create fulltext index*/
if not exists(select 1 from sys.fulltext_catalogs a where a.name='ftCatalog')
begin
create fulltext catalog ftCatalog As default;
end
go
--select * From sys.fulltext_languages        
create fulltext index on TBAddress(Name language 2052 ) key index PK_TBAddress
go     
alter fulltext index on dbo.TBAddress add(Fullpath language 2052)
go

Note:  在Name列上创建全文索引使用的语言是简体中文(Simplified Chinese)

分分快三全天计划网站 13

4.2 存储过程代码:

分分快三全天计划网站 14分分快三全天计划网站 15

Use test
Go
if object_ID('[up_SearchAddressByNameV1]') is not null
    Drop Procedure [up_SearchAddressByNameV1]
Go
create proc up_SearchAddressByNameV1 
(
    @Name nvarchar(200)
)
As
set nocount on
declare @sql nvarchar(max),@contains nvarchar(500)

declare @tmp Table (Name nvarchar(50))

while patindex('%  %',@Name)>0
begin
    set @Name=replace(@Name,'  ',' ')    
end

set @sql ='select '''  replace(@Name,' ',''' union all select ''') ''''
set @contains='"' replace(@Name,' ','*" Or "') '*"'

insert into @tmp(Name) exec(@sql)

if object_id('tempdb..#') is not null drop table #

;with cte_SearchParent as
(
    select a.ID,a.Parent,a.LevelNo,convert(nvarchar(2000),a.Name) as AddressPath from TBAddress a where exists(select 1 from TBAddress x where contains(x.Name,@contains) And x.ID=a.ID) 
    union all
    select a.ID,b.Parent,b.LevelNo,convert(nvarchar(2000),b.Name '/' a.AddressPath) as AddressPath
        from cte_SearchParent a
        inner join TBAddress b on b.ID=a.Parent
            --and b.LevelNo=a.LevelNo -1
            and b.LevelNo>=1
)
select a.ID,a.AddressPath 
    into #
    from cte_SearchParent  a 
    where a.LevelNo=1 and exists(select 1 from @tmp x where a.AddressPath like '%' x.Name '%' having count(1)=(select count(1) from @tmp))

;with cte_result as
(
    select a.ID,a.LevelNo,b.AddressPath
        from TBAddress a 
            inner join # b on b.ID=a.ID
    union all
    select b.ID,b.LevelNo,convert(nvarchar(2000),a.AddressPath '/' b.Name) As AddressPath
        from cte_result a
            inner join TBAddress b on b.Parent=a.ID
                --and b.LevelNo=a.LevelNo 1

)
select distinct a.ID,a.AddressPath 
    from cte_result a 
    where not exists(select 1 from TBAddress x where x.Parent=a.ID)
    order by a.AddressPath  
Go

procedure:up_SearchAddressByNameV1

4.3测试存储过程:

exec up_SearchAddressByNameV1 '广 大'

分分快三全天计划网站 16

共返回195行记录。

 

4.4 客户端统计信息:

分分快三全天计划网站 17

平均的执行耗时:  166毫秒

注意一个细节,删除表中的列时,如果要删除的列为整个索引的组成部分,则该列也会从索引中删除;如果组成索引的所有列都被删除,则整个索引将被删除

1.需求

 1.1 基本需求: 根据输入的地址关键字,搜索出完整的地址路径,耗时要控制在几十毫秒内。

 1.2 数据库地址表结构和数据:

 表TBAddress

 分分快三全天计划网站 18

 表数据

 分分快三全天计划网站 19

 1.3 例子:

 e.g. 给出一个字符串如“广 大”,找出地址全路径中包含有“广” 和“大”的所有地址,結果如下:

分分快三全天计划网站 20

下面将通过4个方法来实现,再分析其中的性能优劣,然后选择一个比较优的方法。

 

意思是查询的时候,只需要检索前面50个字符。这里专门提一下,对字符串类型的字段进行索引,如果可以尽可能的指定一个前缀长度,例如,一个CHAR(255)的列,如果在前10个或者前30个字符内,多数值是唯一的,则不需要对整个列进行索引,短索引不仅可以提高查询速度而且可以节省磁盘空间、减少I/O操作。

6.测试,方法4

 直接使用Like对列FullPath进行查询。

 6.1存储过程代码:

分分快三全天计划网站 21分分快三全天计划网站 22

Use test
Go
if object_ID('[up_SearchAddressByNameV3]') is not null
    Drop Procedure [up_SearchAddressByNameV3]
Go
create proc up_SearchAddressByNameV3
(
    @name nvarchar(200)
)
As
set nocount on
declare @sql nvarchar(max)

declare @tmp Table (Name nvarchar(50))

set @Name=rtrim(rtrim(@Name))

while patindex('%  %',@Name)>0
begin
    set @Name=replace(@Name,'  ',' ')    
end

set @sql='select id,FullPath As AddressPath 
    from TBAddress a where not exists(select 1 from TBAddress x where x.Parent=a.ID)
    ' 
set @sql  ='And a.FullPath like ''%'  replace(@Name,' ','%'' And a.FullPath Like ''%') '%'''
exec (@sql) 
Go

procedure:up_SearchAddressByNameV3

6.2 测试存储过程:

exec up_SearchAddressByNameV3 '广 大'

分分快三全天计划网站 23

 共返回195行记录。

6.3 客户端统计信息

 分分快三全天计划网站 24

平均的执行耗时:  34毫秒

这样就代表在表的customer_id字段上创建了一个名为idx_customer_id的唯一索引

这是一个很久以前的例子,现在在整理资料时无意发现,就拿出来再改写分享。

普通索引

组合索引是在多个字段上创建一个索引,比如:

DROP TABLEIF EXISTS customer1;CREATE TABLE `customer1` ( `customer_id` BIGINT (20) NOT NULL COMMENT '客户ID', `customer_name` VARCHAR (255) DEFAULT NULL COMMENT '客户姓名', FULLTEXT INDEX `idx_fulltext_customer_name` (`customer_name`)) ENGINE = MyISAM DEFAULT CHARSET = utf8mb4 COMMENT = '客户表';SHOW INDEX FROM customer1;
ALTER TABLE customer1 ADD INDEX idx_customer_id(`customer_id`);ALTER TABLE customer1 ADD INDEX idx_customer_id(customer_name(50));

注意事项

ALTER TABLE创建索引的基本语法为:

优点

ALTER TABLE的基本语法为:

缺点

  • 单列索引: 即一个索引只包含单个列,一个表可以有多个单列索引

  • 组合索引: 指在表的多个字段组合上创建的索引,只有在查询条件中使用了这些字段的左边字段时,索引才会被使用

CREATE TABLE table_name[col_name data_type][UNIQUE|FULLTEXT|SPATIAL][INDEX|KEY][index_name](col_name[length])[ASC|DESC]

组合索引

  • 普通索引: 数据库中的基本索引类型,允许在定义索引的列中插入重复值和空值

  • 唯一索引:索引列的值必须唯一,但允许有空值,主键索引是一种特殊的唯一索引,不允许有空值(比如自增ID)

在已经存在的表上创建索引,可以使用ALTER TABLE语句或者CREATE INDEX语句,所以,分别讲解一下如何使用ALTER TABLE和CREATE INDEX语句在已知的表字段上创建索引。

索引依托于存储引擎的实现,因此,每种存储引擎的索引都不一定完全相同,并且每种存储引擎也不一定支持所有索引类型。所有存储引擎支持每个表至少16个索引,总索引长度至少为256字节。大多数存储引擎有更高的额限制。

  • table: 表示创建索引的表

  • Non_unique: 表示索引不是一个唯一索引,1表示非唯一索引,0表示唯一索引

  • Key_name: 表示索引的名称

  • Seq_in_index: 表示该字段在索引中的位置,单列索引改值该值为1,组合索引为每个字段在索引中定义的顺序

  • Column_name: 表示定义索引的列字段

  • Sub_part: 表示索引的长度

  • Null: 表示该字段是否能为空值

  • Index_type: 表示索引类型

创建表时创建索引的基本语法如下:

-- 查看当前表的索引情况SHOW INDEX FROM customer1;-- 使用 EXPLAIN 分析 SQL语句 是否使用了索引EXPLAIN SELECT * FROM customer1 WHERE customer_id = 1;

单列索引和组合索引

  • select_type: 指定所使用的SELECT查询类型,这里值为SIMPLE,表示简单的SELECT,不使用UNION或者子查询。其他取值有PRIMARY、UNION、SUBQUERY、等

  • table: 指定数据库读取的数据表的名字,它们按照被读取的先后顺序排列

  • type: 指定了本数据表与其他数据表之间的关联关系,其它取值有system、const、eq_ref、ref、range、index和All

  • possible_keys: MySQL在搜索数据记录时可选用的各个索引

  • key: MySQL使用的实际索引

  • key_len: 给出了索引按字节计算的长度,key_len数值越小,表示越快

  • ref: 提供了关联关系中另外一个数据表里的数据列的名字

  • rows: 指MySQL执行查询时预计从当前数据表中读出的数据行数

  • Extra: 提供了与关联操作有关的信息

DROP TABLEIF EXISTS customer1;CREATE TABLE `customer1` ( `customer_id` BIGINT (20) NOT NULL COMMENT '客户ID', `customer_name` VARCHAR (30) DEFAULT NULL COMMENT '客户姓名', UNIQUE INDEX `idx_customer_id` (`customer_id`) USING BTREE) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 COMMENT = '客户表';
  • 加快数据的查询速度

  • 唯一索引,可以保证数据库表中每一行数据的唯一性

  • 在实现数据的参考完整性方面,可以加速表和表之间的连接

  • 在使用分组和排序子句进行数据查询时,也可以显著减少查询中分组和排序的时间.

释义

DROP INDEX 语法

概述

-- 这句作用是,如果 customer1 存在就删除DROP TABLE IF EXISTS customer1;CREATE TABLE `customer1` ( `customer_id` bigint(20) NOT NULL COMMENT '客户ID', `customer_name` varchar(30) DEFAULT NULL COMMENT '客户姓名', INDEX `idx_customer_id` (`customer_id`) USING BTREE) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='客户表';

当 possible_keys 与 key 都为 idx_customer_id,说明查询时使用了索引

普通索引

DROP TABLEIF EXISTS customer1;CREATE TABLE `customer1` ( `customer_id` BIGINT (20) NOT NULL COMMENT '客户ID', `customer_name` VARCHAR (30) DEFAULT NULL COMMENT '客户姓名', INDEX `idx_group_customer` (`customer_id`,`customer_name`) USING BTREE) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 COMMENT = '客户表';SHOW INDEX FROM customer1;

使用

这就为customer_id、customer_name两个字段成功创建了一个名为idx_group_customer的组合索引,通过SHOW INDEX FROM customer1; 将会看到两条记录(附图)

分分快三全天计划网站 25

全文索引

最后一项工作就是删除索引了,可以使用ALTER TABLE和DROP INDEX删除索引。

  1. UNIQUE、FULLTEXT和SPATIAL为可选参数,分别表示唯一索引、全文索引和空间索引

  2. INDEX和KEY为同义词,二者作用相同,用来指定创建索引

  3. col_name为需要创建索引的字段列,该列必须从数据表中该定义的多个列中选择

  4. index_name为指定索引的名称,为可选参数,如果不指定则MySQL默认col_name为索引值

  5. length为可选参数,表示索引的长度,只有字符串类型的字段才能指定索引长度

  6. ASC或DESC指定升序或者降序的索引值存储

  • 全文索引: 类型为FULLTEXT,在定义索引的列上支持值的全文查找,允许在这些索引列中插入重复值和空值。全文索引可以在CHAR、VARCHAR或者TEXT类型的列上创建,MySQL中只有MyISAM存储引擎支持全文索引

全文索引可以对全文进行搜索,只有MyISAM存储引擎支持全文索引,并且只为CHAR、VARCHAR和TEXT列,索引总是对整个列进行,不支持局部索引,比如:

测试

看到这么创建,就在info字段上成功建立了一个名为idx_fulltext_customer_name的FULLTEXT全文索引,全文索引非常适合大型数据库,而对于小的数据集,它的用处可能比较小

CREATE INDEX idx_customer_id ON customer1(`customer_id`);CREATE UNIQUE INDEX idx_customer_id ON customer1(`customer_id`);CREATE INDEX idx_group_customer ON customer1(`customer_id`,`customer_name`);

CREATE TABLE 语法

释义

  • 占用磁盘空间,除了数据表占数据空间之外,每一个索引还要占一定的物理空间,如果有大量的索引,索引文件可能比数据文件更快达到最大文件尺寸(合理运用,问题不大)

  • 损耗性能(添加、修改、删除) 索引需要动态地维护

设计原则

建议大家使用第二条

看到和ALTER INDEX语句的语法基本一样,下面把 customer1 表删除了再创建,所有字段都没有索引,用CREATE INDEX语句创建一次索引:

唯一索引

CREATE INDEX语句可以在已经存在的表上添加索引,MySQL中CREATE INDEX被映射到一个ALTER TABLE语句上,基本语法结构为:

唯一索引

EXPLAIN 语法下章会详细讲解,本章重心是索引

MySQL中索引的存储类型有两种:BTREE和HASH,具体和表的存储引擎相关;

ALTER TABLE 语法

ALTER TABLE 语法

ALTER TABLE table_name ADD [UNIQUE|FUUTEXT|SPATIAL][INDEX|KEY] [index_name] (col_name[length],...) [ASC|DESC]

索引是用来快速检索出具有特定值的记录。如果没有索引,数据库就必须从第一条记录开始进行全表扫描,直到找出相关的行。数据越多,检索的代价就越高,检索时如果表的列存在索引,那么MySQL就能快速到达指定位置去搜索数据文件,而不必查看所有数据。

删除索引

分分快三全天计划网站 26

DROP INDEX的基本语法为:

全文索引

MyISAM和InnoDB存储引擎只支持BTREE索引,MEMORY/HEAP存储引擎可以支持HASH和BTREE索引。

普通索引和唯一索引

建议大家使用第二条

SHOW INDEX FROM 语法

  1. 索引并非越多越好,一个表中如有大量的索引,不仅占用磁盘空间,而且会影响INSERT、DELETE、UPDATE等语句的性能,因为当表中的数据更改的同时,索引也会进行调整和更新

  2. 避免对经常更新的表设计过多的索引,并且索引中的列尽可能要少,而对经常用于查询的字段应该创建索引,但要避免添加不必要的字段

  3. 数据量小的表最好不要使用索引,由于数据较少,查询花费的时间可能比遍历索引时间还要短,索引可能不会产生优化效果

  4. 在条件表达式中经常用到的不同值较多的列上建立索引,在不同值较少的列上不要建立索引,比如性别字段只有男和女,就没必要建立索引。如果建立索引不但不会提高查询效率,反而会严重降低更新速度

  5. 当唯一性是某种数据本身的特征时,指定唯一索引。使用唯一索引需能确保定义的列的数据完整性,以提高查询速度

  6. 在频繁排序或分组(即group by或order by操作)的列上建立索引,如果待排序的列有多个,可以在这些列上建立组合索引

索引设计不合理或者缺少索引都会对数据库和应用程序的性能造成障碍,高效的索引对于获得良好的性能非常重要。

在已经存在的表上创建索引

组合索引

架构群:697579751

分类

CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name ON table_name(col_name[length],...)[ASC|DESC]
ALTER TABLE customer1 ADD INDEX `idx_group_customer` (`customer_id`,`customer_name`);

单列索引是在数据表中的某一个字段上创建的索引,一个表中可以创建多个单列索引,前面两个例子中创建的索引都是单列索引,比如:

使用 CREATE TABLE 创建表的时候,除了可以定义列的数据类型,还可以定义主键约束、外键约束或者唯一性约束,而不论创建哪种约束,在定义约束的同时相当于在指定列上创建了一个索引。

本文由分分快三计划发布,转载请注明来源

关键词: 分分快三计划 mysql 技术 JAVA JAVA高级