澳门金沙vip 25

【澳门金沙vip】MySQL-索引详解

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

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

1.需求

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

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

 表TBAddress

 澳门金沙vip 1

 表数据

 澳门金沙vip 2

 1.3 例子:

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

澳门金沙vip 3

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

 

概述

2.创建表和插入数据

 2.1 创建数据表TBAddress

澳门金沙vip 4澳门金沙vip 5

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代码比较长,所以采用附件形式。

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

3.测试,方法1

3.1 分析:

 澳门金沙vip 6

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

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

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

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

3.2 存储过程代码:

澳门金沙vip 7澳门金沙vip 8

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 '广 大'

澳门金沙vip 9

共返回195行记录。

3.4 客户端统计信息:

澳门金沙vip 10

平均的执行耗时:  244毫秒

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

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)

澳门金沙vip 11

4.2 存储过程代码:

澳门金沙vip 12澳门金沙vip 13

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 '广 大'

澳门金沙vip 14

共返回195行记录。

 

4.4 客户端统计信息:

澳门金沙vip 15

平均的执行耗时:  166毫秒

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

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 存储过程代码:

澳门金沙vip 16澳门金沙vip 17

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 '广 大'

澳门金沙vip 18

共返回195行记录。

5.5 客户端统计信息:

澳门金沙vip 19

平均的执行耗时:  20.4毫秒

优点

6.测试,方法4

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

 6.1存储过程代码:

澳门金沙vip 20澳门金沙vip 21

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 '广 大'

澳门金沙vip 22

 共返回195行记录。

6.3 客户端统计信息

 澳门金沙vip 23

平均的执行耗时:  34毫秒

  • 加快数据的查询速度

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

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

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

7.小结

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

 澳门金沙vip 24

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

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

 

缺点

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

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

分类

普通索引和唯一索引

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

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

单列索引和组合索引

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

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

全文索引

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

设计原则

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

注意事项

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

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

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

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

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

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

使用

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

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

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

释义

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

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

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

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

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

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

普通索引

-- 这句作用是,如果 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='客户表';

测试

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

澳门金沙vip 25

释义

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

  • 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: 提供了与关联操作有关的信息

SHOW INDEX FROM 语法

  • table: 表示创建索引的表

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

  • Key_name: 表示索引的名称

  • Seq_澳门金沙vip,in_index:
    表示该字段在索引中的位置,单列索引改值该值为1,组合索引为每个字段在索引中定义的顺序

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

  • Sub_part: 表示索引的长度

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

  • Index_type: 表示索引类型

当 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 '客户姓名', UNIQUE INDEX `idx_customer_id` (`customer_id`) USING BTREE) ENGINE = INNODB DEFAULT CHARSET = utf8mb4 COMMENT = '客户表';