城乡建设部网站安全员证书查询,大连网络公司团队,5万左右的新能源电动汽车,制作一个交易平台网站1.索引的声明与使用
1.1索引的分类
MySQL的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等。
从 功能逻辑 上说#xff0c;索引主要有 4 种#xff0c;分别是普通索引、唯一索引、主键索引、全文索引。按照 物理实现方式#xff0c;索引可以分…1.索引的声明与使用
1.1索引的分类
MySQL的索引包括普通索引、唯一性索引、全文索引、单列索引、多列索引和空间索引等。
从 功能逻辑 上说索引主要有 4 种分别是普通索引、唯一索引、主键索引、全文索引。按照 物理实现方式索引可以分为 2 种: 聚族索引和非聚族索引。按照 作用字段个数 进行划分分成单列索引和联合索引。
1.普通索引
在创建普通索引时不附加任何限制条件只是用于提高查询效率。这类索引可以创建在 任何数据类型 中其值是否唯一和非空要由字段本身的完整性约束条件决定。建立索引以后可以通过索引进行查询。例如在表student 的字段 name 上建立一个普通索引查询记录时就可以根据该索引进行查询。
2.唯一性索引
使用UNIQUE参数 可以设置索引为唯一性索引在创建唯一性索引时限制该索引的值必须是唯一的但允许有空值。在一张数据表里 可以有多个 唯一索引。
3.主键索引
主键索引就是一种 特殊的唯一性索引在唯一索引的基础上增加了不为空的约束也就是 NOT NULLUNIQUE一张表里 最多只有一个 主键索引。
4.单列索引
在表中的单个字段上创建索引。单列索引只根据该字段进行索引。单列索引可以是普通索引也可以是唯一性索单列索引还可以以是全文索引只要保证该索引只对应个字段即可一个表可以有多个单列索引。
5.多列组合、联合索引
多列索引是在表的 多个字段组合 上创建一个索引。该索引指向创建时对应的多个字段可以通过这几个字段进行查询但是只有查询条件中使用了这些字段中的第一个字段时才会被使用。例如在表中的字段id、name和gender上建立一个多列索引idx_id_name_gender只有在查询条件中使用了字段id时该索引才会被使用。使用组合索引时遵循 最左前缀集合。
6.全文索引
全文索引(也称全文检索)是目前 搜索引擎 使用的一种关键技术。它能够利用[分词技术] 等多种算法智能分析出文本文字中关键词的频率和重要性然后按照一定的算法规则智能地筛选出我们想要的搜索结果。全文索引非常适合大型数据集对于小的数据集它的用处比较小。
使用参数 FULLTEXT 可以设置索引为全文索引。在定义索引的列上支持值的全文查找允许在这些索引列中插入重复值和空值。全文索引只能创建在 CHAR 、VARCHAR 或 TEXT 类型及其系列类型的字段上查询数据量较大的字符串类型的字段时使用全文索引可以提高查询速度。例如表 student 的字段 information 是TEXT 类型该字段包含了很多文字信息。在字段information上建立全文索引后可以提高查询字段information的速度。
全文索引典型的有两种类型: 自然语言的全文索引和 布尔全文索引。
MySQL数据库从3.23.23版开始支持全文索引MySQL5.6.4以前 只有Myisam支持5.6.4版本以后 innodb才支持但是官方版本不支持 中文分词需要第三方分词插件。在5.7.6版本MySQL内置了 ngram全文解析器用来支持亚洲语种的分词。测试或使用全文索引时要先看一下自己的 MySQL 版本、存储引擎和数据类型是否支持全文索引。
随着大数据时代的到来关系型数据库应对全文索引的需求已力不从心逐渐被 solr、ElasticSearch 等专门的搜索引擎所替代。
7.补充:空间索引
使用 参数SPATIAL 可以设置索引为 空间索引。空间索引只能建立在空间数据类型上这样可以提高系统获取空间数据的效率。MySOL中的空间数据类型包括 GEOMETRY、POINT、LINESTRING 和 POLYGON 等。目前只有MyISAM存储引擎支持空间检索而目索引的字段不能为空值。对于初学者来说这类索引很少会用到。
小结: 不同的存储引擎支持的索引类型也不一样
InnoDB支持 B-tree、Full-text 等索引不支持 Hash 索引MyISAM :支持 B-tree、Full-text 等索引不支持 Hash 索引Memory :支持 B-tree、Hash 等索引不支持 Full-text 索引NDB :支持 Hash 索引不支持 B-tree、 Full-text 等索引Archive :不支持 B-tree、 Hash、Full-text 等索引
1.2创建索引
MySQL支持多种方法在单个或多个列上创建索引: 在创建表的定义语句 CREATE TABLE 中指定索引列使用ALTER TABLE 语句在存在的表上创建索引或者使用CREATE INDEX语句在已存在的表上添加索引。
1.创建表的时候创建索引
使用CREATE TABLE创建表时除了可以定义列的数据类型外还可以定义主键约束、外键约束或者唯一性约束而不论创建哪种约束在定义约束的同时相当于在指定列上创建了一个索引。
显式创建表时创建索引的话基本语法格式如下
CREATE TABLE table_name [col_name data_type]
[UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY] [index_name] (col_name[length]) [ASC | DESC]UNIQUE、FULLTEXT 和SPATIAL 为可选参数分别表示唯一索引、全文索引和空间索引INDEX与KEY 为同义词两者的作用相同用来指定创建索引index_name 指定索引的名称为可选参数如果不指定那么MySQL默认col_name为索引名col_name为需要创建索的字段列该列必须从数据表中定义的多个列中选择length 为可选参数表示索引的长度只有字符串类型的字段才能指定索引长度ASC或 DESC 指定升序或者降序的索引值存储
1.创建普通索引
CREATE TABLE test1(id INT,name VARCHAR(25),age INT,sex CHAR,remark VARCHAR(255),INDEX(name)
);2.创建唯一性索引
创建唯一索引的目的也是保证数据的唯一性尤其是对比较庞大的数据表。它与前面的普通索引类似不同的是索引列的值必须唯一但允许有空值。
CREATE TABLE test2(id INT NOT NULL,name VARCHAR(25),UNIQUE INDEX un_idx_id(id)
);#查看表索引结构
SHOW INDEX FROM test2;其中各个主要参数的含义为
Table表示创建索引的表Non_unique表示索引非唯一1代表非唯一索引0代表唯一索引Key_name表示索引的名称Seq_in_index表示该字段在索引中的位置单列索引该值为1组合索引为每个字段在索引定义中的顺序Column_name表示定义索引的列字段Sub_part表示索引的长度Null表示该字段是否能为空值index_type表示索引类型
3.主键索引
设定为主键后数据库会自动建立索引innodb为聚簇索引。 随表一起建索引 CREATE TABLE test3(id INT(10) UNSIGNED,name VARCHAR(25),PRIMARY KEY(id)
);删除主键索引 ALTER TABLE test3 DROP PRIMARY KEY;修改主键索引: 必须先删除掉(drop)原索引。再新建(add)索引
4.创建单列索引
单列索引是在数据表中的某一个字段上创建的索引一个表中可以创建多个单列索引。
CREATE TABLE test4(id INT NOT NULL,name VARCHAR(255),INDEX single_idx_name(name(20))
);5.创建组合索引
组合索引是在多个字段上创建一个索引
CREATE TABLE test5(id INT NOT NULL,name VARCHAR(25) NOT NULL,age INT(11) NOT NULL,INDEX multi_idx(id,name,age)
);使用SHOW INDEX FROM test5;查看索引 由结果可以看到id、name和age字段上已经成功建立了一个名为multi_idx的组合索引。
组合索引可起几个索引的作用但是使用时并不是随便查询哪个字段都可以使用索引而是遵从“最左前缀”。例如索引可以搜索的字段组合为: (id,name, age)、 (id,name) 或者id而 (age) 或者 (name,age) 组合不能使用索引查询。
6.创建全文索引
FULLTEXT全文索引可以用于全文搜索并且只为CHAR、VARCHAR和TEXT列创建索引。索引总是对整个列进行不支持局部(前缀)索引。
CREATE TABLE test6(id INT NOT NULL,name VARCHAR(25),age INT(11),info VARCHAR(255),FULLTEXT INDEX futxt_idx_info(info)
);不同于like方式的的查询
SELECT * FROM test6 WHERE info LIKE %查询字符串%;全文索引用matchagainst方式查询
SELECT * FROM test6 WHERE MATCH(info) AGAINST(查询字符串);注意点
使用全文索引前搞清楚版本支持情况全文索引比 like %快N倍但是可能存在精度问题如果需要全文索引的是大量数据建议先添加数据再创建索引
7.创建空间索引
空间索引创建中要求空间类型的字段必须为 非空
CREATE TABLE test7(geo GEOMETRY NOT NULL,SPATIAL INDEX spa_idx_geo(geo)
);2.在已经存在的表上创建索引
在已经存在的表中创建索引可以使用ALTER TABLE语句或者CREATE INDEX语句
1.使用ALTER TABLE语句创建索引
ALTER TABLE语句创建索引的基本语法如下
ALTER TABLE table_name ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY]
[index_name](col_name[length]) [ASC | DESC]与创建表时创建索引的语法不同的是在这里使用了 ALTER TABLE 和 ADD 关键字ADD表示向表中添加索引。
ALTER TABLE test1 ADD INDEX idx_age(age);
ALTER TABLE test2 ADD PRIMARY KEY(id);2.使用CREATE INDEX创建索
CREATE INDEX语句可以在已经存在的表上添加索引基本语法结构为
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
ON table_name(col_name[length]) [ASC | DESC]CREATE UNIQUE INDEX uni_ind_name ON test3(name);1.3.删除索引
MySOL中删除索引使用ALTER TABLE 或者 DROP INDEX 语句两者可实现相同的功能DROPINDEX语句在内部被映射到一个ALTER TABLE语句中。
1.使用ALTER TABLE删除索引
ALTER TABLE删除索引的基本语法格式如下
ALTER TABLE table_name DROP INDEX index_name;ALTER TABLE test3 DROP INDEX uni_ind_name;提示添加AUTO_INCREMENT约束字段的唯一索引不能被删除
2.使用DROP INDEX语句删除索引
DROP INDEX删除索引的基本语法格式如下
DROP INDEX index_name ON table_name;DROP INDEX idx_age ON test1;提示删除表中的列时如果要删除的列为索引的组成部分则该列也会从索引中删除。如果组成索引的所有列都被删除则整个索引将被删除。
2.MySQL8.0索引新特性
2.1支持降序索引
降序索引以降序存储键值。虽然在语法上从MySOL 4版本开始就已经支持降序索引的语法了但实际上该DESC定义是被忽略的直到MySQL8.x版本才开始真正支持降序索引 (仅限于InnoDB存储引擎)
MySQL在8.0版本之前创建的仍然是升序索引使用时进行反向扫描这大大降低了数据库的效率。在某些场景下降序索引意义重大。例如如果一个查询需要对多个列进行排序且顺序要求不一致那么使用降序索引将会避免数据库使用额外的文件排序操作从而提高性能。
分别在MySQL 5.7版本和MySQL 8.0版本中创建数据表ts1结果如下
CREATE TABLE ts1(a int, b int, index inx_a_b(a,b desc));在MySQL 5.7版本中查看数据表ts1的结构结果如下 从结果可以看出索引仍然是默认的升序。
在MySQL 8.0版本中查看数据表ts1的结构结果如下: 从结果可以看出索引已经是降序了。
2.2隐藏索引
在MySQL 5.7版本及之前只能通过显式的方式删除索引。此时如果发现删除索引后出现错误又只能通过显式创建索引的方式将删除的索引创建回来。如果数据表中的数据量非常大或者数据表本身比较大这种操作就会消耗系统过多的资源操作成本非常高。
从MySQL 8.x开始支持 隐藏索引 (invisible indexes) 只需要将待删除的索引设置为隐藏索引使查询优化器不再使用这个索引(即使使用force index (强制使用索引)优化器也不会使用该索引)确认将索引设置为隐藏索引后系统不受任何响应就可以彻底删除索引。这种通过先将索引设置为隐藏索引再删除索引的方式就是软删除 。
同时如果你想验证某个索引删除之后的 查询性能影响就可以暂时先隐藏该索引。
注意主键不能被设置为隐藏索引。当表中没有显式主键时表中第一个唯一非空索引会成为隐式主键也不能设置为隐藏索引。
索默认是可见的在使用CREATE TABLECREATE INDEX或者ALTER TABLE等语句时可以通过 VISIBLF 或者INVISIBLE 关键词设置索引的可见性。
1.创建表时直接创建
在MySQL中创建隐藏索引通过SQL语句INVISIBLE来实现其语法形式如下
CREATE TABLE table_name(propname1 type1[CONSTRAINT1],...propnamen typen[CONSTRAINTn],INDEX [index_name](propname[length]) INVISIBLE
);上述语句比普通索引多了一个关键字INVISIBLE用来标记索引为不可见索引。
2.在已经创建的表上创建
可以为已经存在的表设置隐藏索引其语法形式如下
CREATE INDEX index_name
ON table_name(porpname(length)) INVISIBLE;3.通过ALTER TABLE语句创建
语法形式如下
ALTER TABLE table_name
ADD INDEX index_name (propname[length]) INVISIBLE;4.切换索引可见状态
已存在的索引可通过如下语句切换可见状态
ALTER TABLE table_name ALTER INDEX index_name INVISIBLE; #切换成隐藏索引
ALTER TABLE table_name ALTER INDEX index_name VISIBLE; #切换成非隐藏索引注意当索引被隐藏时它的内容仍然是和正常索引一样实时更新的。如果一个索引需要长期被隐藏那么可以将其删除因为索引的存在会影响插入、更新和删除的性能。
5.使隐藏索引对查询优化器可见
在MySQL 8.x版本中为索引提供了一种新的测试方式可以通过查询优化器的一个开关(use_invisible_indexes)来打开某个设置使隐藏索引对查询优化器可见。如果 use_invisible_indexes 设置为of(默认)优化器会忽略隐藏索引。如果设置为on即使隐藏索引不可见优化器在生成执行计划时仍会考虑使用隐藏索引
查看查询优化器的开关设置
select optimizer_switch;在输出的结果信息中找到如下属性配置,此属性配置值为off说明隐藏索引默认对查询优化器不可见
使隐藏索引对查询优化器可见
set session optimizer_switchuse_invisible_indexeson;3.索引的设计原则
为了使索引的使用效率更高在创建索引时必须考虑在哪些字段上创建索引和创建什么类型的索引。索引设计不合理或者缺少索引都会对数据库和应用程序的性能造成障碍。高效的索引对于获得良好的性能非常重要。设计索引时应该考虑相应准则。
3.1哪些情况适合创建索引
1.字段的数值有唯一性的限制
索引本身可以起到约束的作用比如唯一索引、主键索引都是可以起到唯一性约束的因此在我们的数据表中如果 某个字段是唯一性的就可以直接 创建唯一性索引或者 主键索引。这样可以更快速地通过该索引来确定某条记录。
业务上具有唯一特性的字段即使是组合字段也必须建成唯一索引。 (来源: Alibaba)
说明: 不要以为唯一索引影响了 insert 速度这个速度损耗可以忽略但提高查找速度是明显的。
2.频繁作为WHERE查询条件的字段
某个字段在SELECT语句的 WHERE 条件中经常被使用到那么就需要给这个字段创建索引了。尤其是在数据量大的情况下创建普通索引就可以大幅提升数据查询的效率。
3.经常 GROUP BY和ORDER BY的列
索引就是让数据按照某种顺序进行存储或检索因此当我们使用 GROUP BY 对数据进行分组查询或者使用ORDER BY 对数据进行排序的时候就需要 对分组或者排序的字段进行索引。如果待排序的列有多个那么可以在这些列上建立 组合索引。
4.UPDATE、DELETE的 WHERE条件列
对数据按照某个条件进行查询后再进行UPDATE 或 DELETE 的操作如果对 WHERE 字段创建了索引就能大幅提升效率。原理是因为我们需要先根据 WHERE 条件列检索出来这条记录然后再对它进行更新或删除。如果进行更新的时候更新的字段是非索引字段提升的效率会更明显这是因为非索引字段更新不需要对索引进行维护。
5.DISTINCT字段需要创建索引
有时候我们需要对某个字段进行去重使用 DISTINCT那么对这个字段创建索引也会提升查询效率。
同时显示出来的数据还是按照 递增的顺序进行展示的。这是因为索引会对数据按照某种顺序进行排序所以在去重的时候也会快很多。
6.多表JOIN 连接操作时创建索引注意事项
首先连接表的数量尽量不要超过 3 张因为每增加一张表就相当于增加了一次嵌套的循环数量级增长会非常快严重影响查询的效率
其次对 WHERE 条件创建索引因为 WHERE 才是对数据条件的过滤。如果在数据量非常大的情况下没有WHERE 条件过滤是非常可怕的
最后对用于连接的字段创建索引并且该字段在多张表中的 类型必须一致
7.使用列的类型小的创建索引
我们这里所说的 类型大小指的就是该类型表示的数据范围的大小。
我们在定义表结构的时候要显式的指定列的类型以整数类型为例有 TINYINT、MEDIUMINT、INT、BIGINT 等它们占用的存储空间依次递增能表示的整数范围当然也是依次递增。如果我们想要对某个整数列建立索引的话在表示的整数范围允许的情况下尽量让索引列使用较小的类型比如我们能使用 INT 就不要使用BIGINT能使用 MEDIUMINT 就不要使用 INT 。这是因为
数据类型越小在查询时进行的比较操作越快数据类型越小索引占用的存储空间就越少在一个数据页内就可以 放下更多的记录从而减少磁盘工/0 带来的性能损耗也就意味着可以把更多的数据页缓存在内存中从而加快读写效率
这个建议对于表的 主键来说更加适用因为不仅是聚簇索引中会存储主键值其他所有的二级索引的节点处都会存储一份记录的主键值如果主键使用更小的数据类型也就意味着节省更多的存储空间和更高效的I/O。
8.使用字符串前缀创建索引
假设我们的字符串很长那存储一个字符串就需要占用很大的存储空间。在我们需要为这个字符串列建立索引时那就意味着在对应的B树中有这么两个问题
B树索引中的记录需要把该列的完整字符串存储起来更费时。而且字符串越长在索引中占用的存储空间越大如果B树索引中索引列存储的字符串很长那在做字符串 比较时会占用更多的时间
我们可以通过截取字段的前面一部分内容建立索引这个就叫 前缀索引。这样在查找记录时虽然不能精确的定位到记录的位置但是能定位到相应前缀所在的位置然后根据前缀相同的记录的主键值回表查询完整的字符串值。既 节约空间 又 减少了字符串的比较时间 还大体能解决排序的问题。
9.区分度高(散列性高)的列适合作为索引
列的基数指的是某一列中不重复数据的个数比方说某个列包含值258258258虽然有9条记录但该列的基数却是3。也就是说在记录行数一定的情况下列的基数越大该列中的值越分散列的基数越小该列中的值越集中。这个列的基数指标非常重要直接影响我们是否能有效的利用索引。最好为列的基数大的列建立索引为基数太小列的建立索引效果可能不好。
可以使用公式 select count(distinct a)/count(*) from t1 计算区分度越接近1越好一般超过33%就算是比较高效的索引了。
拓展:联合索引把区分度高(散列性高)的列放在前面
10.使用最频繁的列放到联合索引的左侧
这样也可以较少的建立一些索引。同时由于最左前缀原则”可以增加联合索引的使用率。
11.在多个字段都要创建索引的情况下联合索引优于单值索引
3.2限制索引的数目
在实际工作中我们也需要注意平衡索引的数目不是越多越好。我们需要限制每张表上的索引数量建议单张表索引数量 不超过6个 。原因:
每个索都需要占用磁盘空间索引越多需要的磁盘空间就越大索引会影响 INSERT、DELETE、UPDATE等语句的性能因为表中的数据更改的同时索引也会进行调整和更新会造成负担优化器在选择如何优化查询时会根据统一信息对每一个可以用到的 索引来进行评估以生成出一个最好的执行计划如果同时有很多个索引都可以用于查询会增加MySQL优化器生成执行计划时间降低查询性能
3.3哪些情况不适合创建索引
1.在where中使用不到的字段不要设置索引
WHERE 条件(包括GROUP BY、ORDER BY) 里用不到的字段不需要创建索引索引的价值是快速定位如果起不到定位的字段通常是不需要创建索引的。
2.数据量小的表最好不要使用索引
如果表记录太少比如少于 1000 个那么是不需要创建索引的。表记录太少是否创建索引 对查询效率的影响并不大。甚至说查询花费的时间可能比遍历索引的时间还要短索引可能不会产生优化效果。
3.有大量重复数据的列上不要建立索引
在条件表达式中经常用到的不同值较多的列上建立索引但字段中如果有大量重复数据也不用创建索引。比在学生表的“性别”字段上只有“男”与“女”两个不同值因此无须建立索引。如果建立索引不但不会提高查询效率反而会严重降低数据更新速度。
结论:当数据重复度大比如 高于 10% 的时候也不需要对这个字段使用索引。
4.避免对经常更新的表创建过多的索引
第一层含义:频繁更新的字段不一定要创建索引。因为更新数据的时候也需要更新索引如果索引太多在更新索引的时候也会造成负担从而影响效率。
第二层含义:避免对经常更新的表创建过多的索引并且索引中的列尽可能少。此时虽然提高了查询速度同时却会降低更新表的速度。
5.不建议用无序的值作为索引
例如身份证、UUID(在索引比较时需要转为ASC并且插入时可能造成页分裂)、MD5、HASH、无序长字符串等
6.删除不再使用或者很少使用的索引
表中的数据被大量更新或者数据的使用方式被改变后原有的一些索引可能不再需要。数据库管理员应当定期找出这些索引将它们删除从而减少索引对更新操作的影响。
7.不要定义余或重复的索引 冗余索引 有时候有意或者无意的就对同一个列创建了多个索引比如: index(a,b,c)相当于index(a)、index(a,b).index(a,b,c)。 举例: 建表语句如下 我们知道通过 idx_name_birthday_phone_number 索引就可以对 name 列进行快速搜索再创建一个专门针对 name 列的索引就算是一个 冗余索引维护这个索只会增加维护的成本并不会对搜索有什么好处。 重复索引 另一种情况我们可能会对某个列 重复建立索引比方说这样: 我们看到col1 既是主键、又给它定义为一个唯一索还给它定义了一个普通索引可是主键本身就会生成聚簇索引所以定义的唯一索引和普通索引是重复的这种情况要避免。
3.4小结
索引是一把 双刃剑可提高查询效率但也会降低插入和更新的速度并占用磁盘空间。
选择索引的最终目的是为了使查询的速度变快上面给出的原则是最基本的准则但不能拘泥于上面的准则根据应用的实际情况进行分析和判断选择最合适的索引方式。
