您的位置:新葡亰496net > 网络数据库 > 新葡亰496net:MySQL创建全文索引学习笔记,索引详

新葡亰496net:MySQL创建全文索引学习笔记,索引详

发布时间:2019-09-08 03:36编辑:网络数据库浏览(88)

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

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

    MySQL-索引详解,mysql-索引

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

    概述

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

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

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

    优点

    • 加快数据的查询速度

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

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

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

    缺点

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

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

    分类

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

    • 唯一索引:索引列的值必须唯一,但允许有空值,主键索引是一种特殊的唯一索引,不允许有空值(比如自增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;
    

    新葡亰496net 1

    释义
    

    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_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 = '客户表';
    

    这样就代表在表的customer_id字段上创建了一个名为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; 将会看到两条记录(附图)

    新葡亰496net 2

    全文索引

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

    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;
    

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

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

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

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

    ALTER TABLE 语法

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

    ALTER TABLE table_name ADD [UNIQUE|FUUTEXT|SPATIAL][INDEX|KEY] [index_name] (col_name[length],...) [ASC|DESC]
    
    普通索引
    
    ALTER TABLE customer1 ADD INDEX idx_customer_id(`customer_id`);ALTER TABLE customer1 ADD INDEX idx_customer_id(customer_name(50));
    

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

    唯一索引
    
    ALTER TABLE customer1 ADD UNIQUE INDEX `idx_customer_id` (`customer_id`);
    
    组合索引
    
    ALTER TABLE customer1 ADD INDEX `idx_group_customer` (`customer_id`,`customer_name`);
    

    CREATE TABLE 语法

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

    CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name ON table_name(col_name[length],...)[ASC|DESC]
    

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

    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`);
    

    删除索引

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

    ALTER TABLE 语法

    ALTER TABLE的基本语法为:

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

    建议大家使用第二条

    DROP INDEX 语法

    DROP INDEX的基本语法为:

    DROP INDEX index_name ON table_nameDROP INDEX IF EXISTS index_name ON table_name
    

    建议大家使用第二条

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

    架构群:697579751

    索引 是用来快速检索出具有特定值的记录。如果没有索引,数据库就必须从第一条记录开始进行全表扫描,直到...

    全文索引是mysql数据库索引的一种,全文索引可以快速的实现全文的搜索,它的类型为fulltext了,下面我们一起来看看MySQL创建全文索引的创建与mysql配置全文索引的方法,希望本文章对大家有帮助。

    1.需求

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

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

     表TBAddress

     新葡亰496net 3

     表数据

     新葡亰496net 4

     1.3 例子:

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

    新葡亰496net 5

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

     

    概述

    使用索引是数据库性能优化的必备技能之一。在MySQL数据库中,有四种索引:聚集索引(主键索引)、普通索引、唯一索引以及我们这里将要介绍的全文索引(FULLTEXT INDEX)。

    2.创建表和插入数据

     2.1 创建数据表TBAddress

    新葡亰496net 6新葡亰496net 7

    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字节。大多数存储引擎有更高的额限制。

    全文索引(也称全文检索)是目前seo搜索引擎使用的一种关键技术。它能够利用“分词技术“等多种算法智能分析出文本文字中关键字词的频率及重要性,然后按照一定的算法规则智能地筛选出我们想要的搜索结果。在这里,我们就不追根究底其底层实现原理了,现在我们来看看在MySQL中如何创建并使用全文索引。

    3.测试,方法1

    3.1 分析:

     新葡亰496net 8

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

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

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

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

    3.2 存储过程代码:

    新葡亰496net 9新葡亰496net 10

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

    新葡亰496net 11

    共返回195行记录。

    3.4 客户端统计信息:

    新葡亰496net 12

    平均的执行耗时:  244毫秒

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

    全文索引只能在MyISAM数据表中创建 少于3个字符的单词不会被包含在全文索引里,可以通过修改my.cnf修改选项

    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)

    新葡亰496net 13

    4.2 存储过程代码:

    新葡亰496net 14新葡亰496net 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 '广 大'
    

    新葡亰496net 16

    共返回195行记录。

     

    4.4 客户端统计信息:

    新葡亰496net 17

    平均的执行耗时:  166毫秒

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

    ft_min_word_len=3

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

    新葡亰496net 18新葡亰496net 19

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

    新葡亰496net 20

    共返回195行记录。

    5.5 客户端统计信息:

    新葡亰496net 21

    平均的执行耗时:  20.4毫秒

    优点

    重新启动MySQL服务器,用

    6.测试,方法4

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

     6.1存储过程代码:

    新葡亰496net 22新葡亰496net 23

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

    新葡亰496net 24

     共返回195行记录。

    6.3 客户端统计信息

     新葡亰496net 25

    平均的执行耗时:  34毫秒

    • 加快数据的查询速度

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

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

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

    repair table tablename quick 为有关数据表重新生成全文索引
    select * from tablename

    7.小结

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

     新葡亰496net 26

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

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

     

    缺点

    where match(column1,column2) against('word1 word2 word3')>0.001
    match ... against 把column1,column2数据列中至少包含word1,word2,word3三个单词之一的数据记录查找到,在关键字match后的数据列必须跟创建全文索引的数据列相同,检索词不区分大小写和先后顺序,少于3个字符的单词通常被忽略。match... against ...表达式返回一个浮点数作为它本身的求值结果,这个数字反映了结果记录与被检索单词的匹配程度。如果没有匹配到任何记录,或者匹配到的结果记录太多反而被忽略,表达式将返回0,表达式>0.001的作用是排除match的返回值太小的结果记录。

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

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

     代码如下

    分类

    复制代码

    普通索引和唯一索引

    select *,match(column1,column2) against ('word1 word2 word3') as mtch

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

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

    from tablename

    单列索引和组合索引

    having mtch>0.01

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

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

    order by mtch desc

    全文索引

    limit 5

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

    找出最匹配的5条记录,在where字句中不能使用假名,所以用having

    设计原则

    创建全文索

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

    在MySQL中,创建全文索引相对比较简单。例如,我们有一个文章表(article),其中有主键ID(id)、文章标题(title)、文章内容(content)三个字段。现在我们希望能够在title和content两个列上创建全文索引,article表及全文索引的创建SQL语句如下:

    注意事项

       

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

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

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

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

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

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

     代码如下

    使用

    复制代码

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

    --创建article表
        CREATE TABLE article (
            id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,
            title VARCHAR(200),
            content TEXT,
            FULLTEXT (title, content) --在title和content列上创建全文索引
        );

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

    上面就是在创建表的同时建立全文索引的SQL示例。此外,如果我们想要给已经存在的表的指定字段创建全文索引,同样以article表为例,我们可以使用如下SQL语句进行创建:

    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指定升序或者降序的索引值存储

        --给现有的article表的title和content字段创建全文索引
        --索引名称为fulltext_article
        ALTER TABLE article
        ADD FULLTEXT INDEX fulltext_article (title, content)

    普通索引

    在MySQL中创建全文索引之后,现在就该了解如何使用了。众所周知,在数据库中进行模糊查询是使用LIKE关键字进行查询,例如:

    -- 这句作用是,如果 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;
    

    SELECT * FROM article WHERE content LIKE '%查询字符串%'

    新葡亰496net 27

    那么,我们使用全文索引也是这样用的吗?当然不是,我们必须使用特有的语法才能使用全文索引进行查询。例如,我们想要在article表的title和content列中全文检索指定的查询字符串,可以如下编写SQL语句:

    释义

     代码如下

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

    SELECT * FROM article WHERE MATCH(title, content) AGAINST('查询字符串')

    SHOW INDEX FROM 语法

    强烈注意:MySQL自带的全文索引只能用于数据库引擎为MyISAM的数据表,如果是其他数据引擎,则全文索引不会生效。此外,MySQL自带的全文索引只能对英文进行全文检索,目前无法对中文进行全文检索。如果需要对包含中文在内的文本数据进行全文检索,我们需要采用Sphinx(斯芬克斯)/Coreseek技术来处理中文。本站将会在后续文章中对Sphinx以及Coreseek进行介绍

    备注1:目前,使用MySQL自带的全文索引时,如果查询字符串的长度过短将无法得到期望的搜索结果。MySQL全文索引所能找到的词的默认最小长度为4个字符。另外,如果查询的字符串包含停止词,那么该停止词将会被忽略。

    • table: 表示创建索引的表

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

    • Key_name: 表示索引的名称

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

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

    • Sub_part: 表示索引的长度

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

    • Index_type: 表示索引类型

    备注2:如果可能,请尽量先创建表并插入所有数据后再创建全文索引,而不要在创建表时就直接创建全文索引,因为前者比后者的全文索引效率要高。

    当 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 = '客户表';
    

    这样就代表在表的customer_id字段上创建了一个名为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; 将会看到两条记录(附图)

    新葡亰496net 28

    全文索引

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

    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;
    

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

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

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

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

    ALTER TABLE 语法

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

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

    普通索引

    ALTER TABLE customer1 ADD INDEX idx_customer_id(`customer_id`);ALTER TABLE customer1 ADD INDEX idx_customer_id(customer_name(50));
    

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

    唯一索引

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

    组合索引

    ALTER TABLE customer1 ADD INDEX `idx_group_customer` (`customer_id`,`customer_name`);
    

    CREATE TABLE 语法

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

    CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name ON table_name(col_name[length],...)[ASC|DESC]
    

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

    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`);
    

    删除索引

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

    ALTER TABLE 语法

    ALTER TABLE的基本语法为:

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

    建议大家使用第二条

    DROP INDEX 语法

    DROP INDEX的基本语法为:

    DROP INDEX index_name ON table_nameDROP INDEX IF EXISTS index_name ON table_name
    

    建议大家使用第二条

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

    架构群:697579751

    本文由新葡亰496net发布于网络数据库,转载请注明出处:新葡亰496net:MySQL创建全文索引学习笔记,索引详

    关键词:

上一篇:关系型数据库三大范式,数据库范式

下一篇:没有了