系列文章目录
第1章 MySQL系列文章之子查询
第2章 MySQL系列文章之表的操作和约束
第3章 MySQL系列文章之表的视图和存储过程
第4章 MySQL系列文章之逻辑架构
第5章 MySQL系列文章之存储引擎
第6章 MySQL系列文章之索引的数据结构
第7章 MySQL系列文章之索引的创建与设计原则
第8章 MySQL系列文章之索引的性能分析工具的使用
第9章 MySQL系列文章之索引优化与查询优化
第10章 MySQL系列文章之数据库其它调优策略
第11章 MySQL事务和事务日志
第12章 MySQL的锁
第13章 MySQL的多版本并发控制
第14章 MySQL日志和主从复制
第15章 基于Docker的MySQL备份
文章目录
一、索引的声明与使用
1.1、索引的分类
- 从功能逻辑上说,索引主要有 4 种,分别是普通索引、唯一索引、主键索引、全文索引。
- 按照物理实现方式,索引可以分为 2 种:聚簇索引和非聚簇索引。
- 按照作用字段个数进行划分,分成单列索引和联合索引。
1.2、创建索引
1.2.1、创建表的时候创建索引
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 student (
id INT(10) UNSIGNED AUTO_INCREMENT ,
student_no VARCHAR(200),
student_name VARCHAR(200),
PRIMARY KEY(id)
);
删除主键索引:
ALTER TABLE student
drop PRIMARY KEY ;
修改主键索引:必须先删除掉(drop)原索引,再新建(add)索引
查看表结构:
SHOW INDEX FROM 表名 \G
2)全文索引
CREATE TABLE test4(
id INT NOT NULL,
name CHAR(30) NOT NULL,
age INT NOT NULL,
info VARCHAR(255),
FULLTEXT INDEX futxt_idx_info(info)
) ENGINE=MyISAM;
全文索引用match+against方式查询:
SELECT * FROM papers WHERE MATCH(title,content) AGAINST (‘查询字符串’);
1.2.2、在已经存在的表上创建索引
- 方式一
ALTER TABLE table_name ADD [UNIQUE | FULLTEXT | SPATIAL] [INDEX | KEY]
[index_name] (col_name[length],...) [ASC | DESC]
- 方式二
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX index_name
ON table_name (col_name[length],...) [ASC | DESC]
1.2.3、删除索引
ALTER TABLE table_name DROP INDEX index_name;
或
DROP INDEX index_name ON table_name;
删除表中的列时,如果要删除的列为索引的组成部分,则该列也会从索引中删除。
如果组成索引的所有列都被删除,则整个索引将被删除。
二、MySQL8.0索引新特性
2.1、支持降序索引
举例:分别在MySQL 5.7版本和MySQL 8.0版本中创建数据表ts1
CREATE TABLE ts1(a int,b int,index idx_a_b(a,b desc));
通过sql语句查看表结构:
show create table ts1\G
MySQL 5.7索引仍然是升序,并没有降序。执行计划中扫描数为799,而且使用了Using filesort。Using filesort是MySQL中一种比较慢的外部排序,可以通过优化索引来避免。
而MySQL 8.0中该索引是降序。执行计划中扫描数为5,而且没有使用Using filesort。
降序索引只对查询中特定的排序顺序有效,如果使用不当,反而查询效率更低。
2.2、隐藏索引
在MySQL 5.7版本及之前,只能通过显式的方式删除索引。此时,如果发现删除索引后出现错误,又只能通过显式创建索引的方式将删除的索引创建回来。如果数据表中的数据量非常大,或者数据表本身比较大,这种操作就会消耗系统过多的资源,操作成本非常高。
从MySQL 8.x开始支持隐藏索引(invisible indexes) ,只需要将待删除的索引设置为隐藏索引,使
查询优化器不再使用这个索引(即使使用force index(强制使用索引),优化器也不会使用该索引),确认将索引设置为隐藏索引后系统不受任何响应,就可以彻底删除索引。这种通过先将索引设置为隐藏索引,再删除索引的方式就是软删除。
创建语句类似前面的创建语句,另外sql语句结尾加INVISIBLE即可。
比如:
ALTER TABLE tablename
ADD INDEX indexname (propname [(length)]) INVISIBLE;
切换索引可见状态:
ALTER TABLE tablename ALTER INDEX index_name INVISIBLE; #切换成隐藏索引
ALTER TABLE tablename ALTER INDEX index_name VISIBLE; #切换成非隐藏索引
当索引被隐藏时,它的内容仍然是和正常索引一样实时更新的。如果一个索引需要长期被隐藏,那么可以将其删除,因为索引的存在会影响插入、更新和删除的性能。
三、索引的设计原则
3.1、适合创建索引的情况
1)数值有唯一性的限制,或者区分度高(散列性高)的字段适合创建索引
2)sql语句中DISTINCT(字段);频繁作为 WHERE 查询条件的字段;经常 GROUP BY 和 ORDER BY 的字段,需要为这些字段创建索引
3)在多个字段都要创建索引的情况下,联合索引优于单值索引;使用最频繁的列放到联合索引的左侧(最左前缀)
4)使用字符串前缀创建索引
在 varchar 字段上建立索引时,必须指定索引长度,没必要对全字段建立索引,根据实际文本
区分度决定索引长度。
比如在地址字段上建立前缀索引,地址字段比较长,截取多少呢? 截取得多了,达不到节省索引存储空间的目的;截取得少了,重复内容太多,字段的散列度(选择性)会降低。怎么计算不同的长度的选择性呢?
看一下字段在全部数据中的选择度:
count(distinct left(列名, 索引长度))/count(*)
比如:
select count(distinct left(address,10)) / count(*) as sub10, -- 截取前10个字符的选择度
count(distinct left(address,15)) / count(*) as sub11, -- 截取前15个字符的选择度
count(distinct left(address,20)) / count(*) as sub12, -- 截取前20个字符的选择度
count(distinct left(address,25)) / count(*) as sub13 -- 截取前25个字符的选择度
from shop;
索引的长度与区分度是一对矛盾体,一般对字符串类型数据,长度为 20 的索引,区分度会高达90% 以上。
5)多表 JOIN 连接操作时,创建索引注意事项
- 连接表的数量尽量不要超过 3 张
- 对 WHERE 条件创建索引
- 对用于连接的字段创建索引,并且该字段在多张表中的类型必须一致
3.2、不适合创建索引的情况
1)有大量重复数据的列上不要建立索引,不建议用无序的值作为索引
2)在where中使用不到的字段,不要设置索引
3)数据量小的表最好不要使用索引,经常更新的表不要创建过多的索引
4)不要定义冗余或重复的索引,删除不再使用或者很少使用的索引
参考:MySQL高级特性篇-宋红康