mysql索引系统表_MySQL - 表索引

发布时间：2022-12-02 12:38:04 所属栏目：教程来源：

导读：　　1. 索引概述

　　索引是一种特殊的数据库结构，可以用来快速查询数据库表中的特定记录，是提高数据库性能的重要方式。MySQL中，所有的数据类型都可以被索引。

　　通过索引，查询数据时可以不必读完记录

　　1. 索引概述

　　索引是一种特殊的数据库结构，可以用来快速查询数据库表中的特定记录，是提高数据库性能的重要方式。MySQL中，所有的数据类型都可以被索引。

　　通过索引，查询数据时可以不必读完记录的所有信息，而只是查询索引列，否则数据库系统将读取每条记录的所有信息进行匹配。例如，索引相当于新华字典的音序表，如果要查“过”字，如果不适用音序，就需要从字典的第一页开始翻几百页；如果提取拼音出来，构成音序表，就只需要从10多页的音序表中直接查找，这样就可以大大节省时间。因此，使用索引可以在很大程度上提高数据库的查询速度，有效地提高了数据库系统的性能。

　　1.1 索引类型

　　索引类型都包括：普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等，下文中将详细介绍。

　　1.2索引优点

　　可以提高检索数据的速度。

　　1.3 索引缺点

　　创建和维护索引需要耗费时间，耗费时间的数量随着数据量的增加而增加；索引需要占用物理空间，每一个索引要占一定的物理空间；增加、删除和修改数据时，要动态地维护索引，造成数据的维护速度降低了。

　　1.4 使用建议

　　索引可以提高查询的速度，但是会影响插入记录的速度，因为向有索引的表中插入记录时，数据库系统会按照索引进行排序，这样就降低了插入记录的速度，插入大量记录时的速度影响更加明显。这种情况下，最好的办法是先删除表中的索引，然后插入数据，插入完成后再创建索引。

　　1.5创建和查看索引

　　创建索引是指在某个表的一列或多列上建立一个索引，以便提高对表的访问速度。创建索引有3种方式，分别是创建表的时候创建索引、在已经存在的表上创建索引和使用ALTER TABLE语句来创建索引。本节将根据具体的索引分类详细的讲解这3种创建方法。

　　1.6 索引储存

　　数据库底层索引实现主要有两种存储类型，B树(BTREE) 和哈希(HASH)索引，InnoDB 和MyISAM 使用BTREE 索引；

　　而MEMORY 存储引擎可以使用BTREE 和HASH 索引，默认用BTREE.在没有指定的情况下，数据库使用的引擎是InnoDB。

　　2.普通索引

　　所谓普通索引，就是在创建索引时，不附加任何限制条件(唯一、非空等限制)。该类型的索引可以创建在任何数据类型的字段上。

　　2.1 创建表时定义索引

　　1 CREATE TABLEtablename2 (3 propname1 type1,4 propname2 type2,5 ……6 propnamen type..n,7 INDEX| KEY

　　8 [indexname] (propnamen[(length)] [ASC | DESC])9 );

　　其中，参数INDEX和KEY是用来指定字段为索引的，两者选择其中之一就可以，作用是一样的；参数indexname是索引名字，可省略；参数propnamen是索引对应的字段的名称，该字段必须为前面定义好的字段；参数length是可选参数，其指索引的长度，必须是字符串类型才可以使用；参数ASC和DESC都是可选参数，ASC表示升序排列，DESC表示降序排列，如果不指定则为升序。

　　1 mysql> create databaseschool; #创建数据库school2 mysql> useschool;#选择数据库school3 mysql> create table class(id int, name varchar(128) UNIQUE, teacher varchar(64), INDEX index_no(id DESC)); #创建表class, 并建立为id 字段索引4 mysql> show create tableclass; #查看表结构5 mysql> insert into class values(1, '班级文字', 'A老师'); # 插入记录16 mysql> insert into class values(1, '班级文字', 'B老师);# 插入记录27 mysql> select * from class where id > 0; #根据id查询记录，结果将降序排列

　　2.2已存在的表上创建索引

　　方法一: 执行 create 语句

　　1 CREATE INDEXindexnameON tablename (propname [(length)] [ASC|DESC]);

　　参数 INDEX 是用来指定字段为索引，此处不能为 KEY；参数 indexname 是新创建的索引的名字；参数 tablename 是指需要创建索引的表的名称，该表必须是已经存在的，如果不存在，需要先创建；参数 propname 指定索引对应的字段的名称，该字段必须为前面定义好的字段；参数 length 是可选参数，表示索引的长度，必须是字符串类型才可以使用；参数 ASC 和 DESC 都是可选参数，ASC 表示升序排列，DESC 表示降序排列，默认升序。

　　1 mysql> create databaseschool;#创建数据库school2 mysql> useschool;#选择数据库school3 mysql> create table class(id int, name varchar(128) UNIQUE, teacher varchar(64)); #创建表class, 并建立为id 字段索引4 mysql> create index index_id on class(id ASC);#追加升序索引5 mysql> show create tableclass;#查看表定义6 mysql> insert into class values(1, '一班', 'Martin'); #插入记录17 mysql> insert into class values(1, '二班', 'Rock'); #插入记录28 mysql> select * from class where id > 0; #根据id查询记录，结果将降序排列

　　方法二: 执行ALTER TABLE 语句

　　1 ALTER TABLE tablename ADD INDEX | KEYindexname2 (propname [(length)] [ASC|DESC]);

　　在上述语句中，参数tablename是需要创建索引的表；关键字IDNEX或KEY用来指定创建普通索引；参数indexname用来指定所创建的索引名；参数propname用来指定索引所关联的字段的名称；参数length用来指定索引的长度；参数ASC用来指定升序排序；参数DESC用来指定降序排序。

　　2.3 查看索引执行情况

　　EXPLAIN 查询语句

　　a2414a68aea67d89edaabb8119d8e269.png

　　输出结果：

　　key: 实际使用的索引。如果为NULL，则没有使用索引

　　possible_keys:显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用

　　key_len:表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。此值越短越好！

　　3.唯一索引

　　所谓唯一索引，就是在创建索引时，限制索引的字段值必须是唯一的。通过该类型的索引可以比普通索引更快速地查询某条记录。

　　创建表时定义索引

　　1 CREATE TABLE tablename2 (3 propname1 type1,4 ……5 propnamen type..n,6 UNIQUE INDEX | KEY [indexname] (propnamen [(length)] [ ASC | DESC ])7 );

　　注意：参数UNIQUEINDEX和UNIQUEKEY 是用来指定字段为索引的，两者选择其中之一即可；参数indexname是索引名字，可省略；参数propnamen是索引对应的字段的名称，该字段必须为前面定义好的字段且必须定义为UNIQUE 约束；参数length是可选参数，其指索引的长度，必须是字符串类型才可以使用；参数ASC和DESC都是可选参数，ASC表示升序排列，DESC表示降序排列，如果不指定，则为升序。

　　3.1 已存在的表上创建索引

　　方法一:

　　CREATE UNIQUE INDEX indexname ON tablename (propname [(length)] [ASC|DESC]);

　　方法二:

　　ALTER TABLE tablename ADD UNIQUE INDEX | KEY indexname (propname [(length)] [ASC|DESC]);

　　4.全文索引

　　全文索引主要对字符串类型建立基于分词的索引，主要是基于 CHAR、VARCHAR 和 TEXT 的字段上，以便能够更加快速地查询数据量较大的字符串类型的字段。

　　全文索引以词为基础的，MySQL 默认的分词是所有非字母和数字的特殊符号都是分词符。

　　MySQL 从 3.23.23 版本开始支持全文索引，MySQL5.6 以前只能在存储引擎为 MyISAM 的数据表上创建全文索引,5.6之后InnoDB开始支持全文索引(5.7之后支持中文全文索引) 。在默认情况下，全文索引的搜索执行方式为不区分大小写，如果全文索引所关联的字段为二进制数据类型，就以区分大小写的搜索方式执行。

　　4.1创建表时定义索引

　　1CREATE TABLE tablename2 (

　　3 propname1 type1,

　　4 propname2 type2,5 ……6 propnamen type..n,7 FULLTEXT INDEX | KEY

　　8 [indexname] (propnamen [(length)] )

　　9 );

　　具体如下:

　　1 mysql> create databaseschool; #创建数据库school2 mysql> useschool; #选择数据库school3 mysql>create table class(id int, name varchar(128) UNIQUE, strings varchar(64),comment varchar(1024),FULLTEXT INDEXindex_comm(comment)); #创建表class, 并建立为comment 字段为全文索引4 mysql> insert into class values(1,'1班','ABC','String!');# 插入记录15 mysql> insert into class values(2,'2班','BCD','真爱'); # 插入记录26 mysql> insert into class values(3,'3班','CDE','TureLove'); #插入记录37 mysql> select * from class where match(comment) AGAINST('TureLove');#利用全文检索索引快速查询记录

　　4.2已存在的表上创建索引

　　方法一: 执行create 语句

　　在MySQL中创建全文索引除了通过SQL语句FULLTEXT INDEX来实现外，还可以通过SQL语句CREATE FULLTEXT INDEX来实现，其语法形式如下：

　　CREATE FULLTEXT INDEX indexname ON tablename( propname1 [( length )] );

　　在上述语句中，关键字CREATE FULLTEXT INDEX表示用来创建全文索引。

　　如下例表已存在，可通过CREATE语句创建全文索引：

　　1 mysql> create databaseschool;#创建数据库school2 mysql> useschool;#选择数据库school3 mysql> create table class(id int, name varchar(128) UNIQUE, teacher varchar(64)); #创建表class, 并建立为id 字段索引4 mysql> create FULLTEXT index index_teacher onclass(teacher );#追加全文索引5 mysql> show create table class; #查看表定义

　　方法二: 执行ALTER TABLE 语句

　　除了上述两种方式来创建全文索引外，在MySQL中创建全文索引还可以通过SQL语句ALTER来实现，其语法形式如下:

　　ALTER TABLE tablename ADD FULLTEXT INDEX|KEY indexname(propname [(length)]);

　　根据全文索引字段进行全文检索数据：

　　SELECT * FROM 表名WHERE MATCH(`列名`) AGAINST('关键字');

　　1 mysql> create databaseschool; #创建数据库school2 mysql> useschool; #选择数据库school3 mysql> mysql> create table class(id int, name varchar(128) UNIQUE, strings varchar(64),info varchar(1024),FULLTEXT INDEXindex_des(info)); #创建表class, 并建立为info 字段为全文索引4 mysql> insert into class values(1,'1班','ABC','Sring'); # 插入记录15 mysql> insert into class values(2,'2班','BCD','真爱'); # 插入记录26 mysql> insert into class values(3,'3班','CDE','TureLove'); # 插入记录37 mysql> select * from class where match(teacher) AGAINST('TureLove');#根据id查询记录

　　MySQL8 中文分词支持

　　配置文件my.ini(Windows 10默认路径: C:\ProgramData\MySQL\MySQL Server 8.0) 中增加如下配置项，同时重启MySQL80 服务:

　　[mysqld]ngram_token_size=2

　　之后可以进行宽度为2的字符(注意是字符不是字节)的索引搜索

　　1 mysql> create databaseschool; #创建数据库school2 mysql> useschool; #选择数据库school3 mysql> mysql> create table class(id int, name varchar(128) UNIQUE, teacher varchar(64),comment varchar(1024),FULLTEXT INDEX index_des(comment) withparser ngram); #创建表class, 并建立为comment 字段为全文索引4 mysql> insert into class values(1,'1班','ABC','String'); # 插入记录15 mysql> insert into class values(2,'2班','BCD','真爱'); # 插入记录26 mysql> insert into class values(3,'3班','CDE','TureLove'); #插入记录37 mysql> select * from class where match(comment) AGAINST('真爱');#利用全文检索索引快速查询记录8 mysql> select * from class where match(comment) AGAINST('TureLove');#利用全文检索索引快速查询记录

　　5. 多列索引

　　多列索引，是指在创建索引时所关联的字段不是一个字段，而是多个字段，虽然可以通过所关联的字段进行查询，但是只有查询条件中使用了所关联字段中的第一个字段，多列索引才会被使用。

　　创建表时定义索引

　　1 CREATE TABLEtablename2 (3 propname1 type1,4 ……5 propnamen type..n,6 INDEX | KEY [indexname] (propname1 [(length)] [ASC | DESC],7 Propname2 [(length)] [ASC | DESC],8 ... ...9 Propnamen [(length)] [ASC | DESC])10 );

　　注意：和普通索引定义基本相同，不同之处就是增加了多个索引列。

　　如下:

　　1 mysql> create databaseschool;#创建数据库school2 mysql> useschool;#选择数据库school3 mysql> create table class(id int, name varchar(128) UNIQUE, teacher varchar(64), INDEXindex_mult_columns(id, teacher)); #创建表class, 并建立包含id,teacher字段的多列索引4 mysql> show create tableclass; #查看表定义5 mysql> insert into class values(1, '一班', 'ABC');# 插入记录16 mysql> insert into class values(1, '二班', 'BCD');# 插入记录27 mysql> select * from class where id > 0 ; #仅根据id查询记录会启用多列索引

　　已存在的表上创建索引

　　方法一: 执行create 语句

　　1 CREATE INDEX indexname ONtablename2 (3 propname1 [(length)] [ASC | DESC],4 Propname2 [(length)] [ASC | DESC],5 ... ...6 Propnamen [(length)] [ASC | DESC]

　　7 );

　　在上述语句中，关键字CREATE INDEX表示用来创建多列索引。

　　如下例表已存在，可通过CREATE语句创建多列索引：

　　1 mysql> create databaseschool;#创建数据库school2 mysql> useschool;#选择数据库school3 mysql> create table class(id int, name varchar(128) UNIQUE, teacher varchar(64)); #创建表class, 并建立为id 字段索引4 mysql> create index index_id onclass(id, name );#追加多列索引5 mysql> show create table class;#查看表定义

　　方法二: 执行ALTER TABLE 语句

　　除了上述两种方式来创建全文索引外，在MySQL中创建全文索引还可以通过SQL语句ALTER来实现，其语法形式如下：

　　1 ALTER TABLE tablename ADD INDEX|KEYindexname2 (3 propname1 [(length)] [ASC | DESC],4 Propname2 [(length)] [ASC | DESC],5 ... ...6 Propnamen [(length)] [ASC | DESC]

　　7 );

　　6.隐藏索引

　　MySQL 8开始支持隐藏索引。隐藏索引提供了更人性化的数据库操作。

　　隐藏索引，顾名思义，让索引暂时不可见，不会被优化器使用。默认情况下索引是可见的。隐藏索引可以用来测试索引的性能。验证索引的必要性时不需要删除索引，可以先将索引隐藏，如果优化器性能无影响就可以真正地删除索引。

　　ALTER TABLE tablename ALTER INDEXindex_name INVISIBLE; #隐藏索引ALTER TABLE tablename ALTER INDEX index_name VISIBLE; #取消隐藏

　　7.删除索引

　　所谓删除索引，就是删除表中已经创建的索引。之所以要删除索引，是因为这些索引会降低表的更新速度，影响数据库的性能。

　　在MySQL中删除索引通过SQL语句DROP INDEX来实现，其语法形式如下：

　　DROP INDEX indexname ON tablename;

　　修改索引:先删除，后增加

　　8.索引的设计原则

　　为了使索引的使用效率更高，在创建索引时，必须考虑在哪些字段上创建索引和创建什么类型的索引。本节将介绍一些索引的设计原则。

　　8.1选择唯一性索引

　　唯一性索引的值是唯一的，可以更快速地通过该索引来确定某条记录。例如，学生表中学号是具有唯一性的字段，为该字段建立唯一性索引可以很快确定某个学生的信息，如果使用姓名的话，可能存在同名现象，从而降低查询速度。

　　8.2为经常需要排序、分组和联合操作的字段建立索引

　　经常需要使用ORDER BY、GROUP BY、DISTINCT和UNION等操作的字段，排序操作会浪费很多时间，如果为其建立索引，可以有效地避免排序操作。

　　8.3为经常作为查询条件的字段建立索引

　　如果某个字段经常用来做查询条件，那么该字段的查询速度会影响整个表的查询速度，为这样的字段建立索引可以提高整个表的查询速度。

　　8.4限制索引的数目

　　索引的数目不是越多越好。每个索引都需要占用磁盘空间，索引越多，需要的磁盘空间就越大，修改表时，对索引的重构和更新很麻烦。

　　8.5尽量使用数据量少的索引

　　如果索引的值很长，那么查询的速度会受到影响。例如，对一个CHAR(100)类型的字段进行全文检索需要的时间肯定要比对CHAR(10)类型的字段需要的时间多。

　　8.6尽量使用前缀来索引

　　如果索引的值很长，最好使用值的前缀来索引。例如，TEXT和BLOG类型的字段，进行全文检索会很浪费时间mysql索引表，如果只检索字段前面的若干字符，这样可以提高检索速度。

　　8.7删除不再使用或者很少使用的索引

　　表中的数据被大量更新，或者数据的使用方式被改变后，原有的一些索引可能不再需要。

（编辑：草根网）

【声明】本站内容均来自网络，其相关言论仅代表作者个人观点，不代表本站立场。若无意侵犯到您的权利，请及时与联系站长删除相关内容!

显卡3050为什么不如20	wifi突然需要登录认证
怎样把cad转pdf	适合电脑新手刷机教程