索引概述
索引(index)是帮助MySOL高效获取数据的数据结构(有序)。在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式引用(指向)数据, 这样就可以在这些数据结构上实现高级查找算法,这种数据结构就是索引。
在查询数据时,如果一张表没有索引,则会一条一条查询,直到全部记录查询完成即止,这样查询的效率极低。有索引则会显著提高效率。
优缺点:
优点:
- 提高数据检索的效率,降低数据库的IO成本
- 通过索引列对数据进行排序,降低数据排序的成本,降低CPU的消耗。
缺点:
- 索引列也是要占用空间的。
- 索引大大提高了查询效率,同时却也降低更新表的速度,如对表进行INSERT、UPDATE、DELETE时,效率降低。
索引的数据结构
MySQL的索引是在存储引擎层实现的,不同的存储引擎有不同的结构,主要包含以下几种:
B+tree
最常见的索引类型,大部分引擎都支持 B+tree 索引。我们平常所说的索引,如果没有特别指明,都是指B+tree 结构组织的索引。
先看二叉树:
二叉树缺点:顺序插入时,会形成一个链表,查询性能大大降低。大数据量情况下,层级较深,检索速度慢。这时可以采用红黑树解决链表结构问题:
红黑树的缺点:大数据量情况下,层级较深,检索速度慢。
为解决大数据量层级较深这个问题,可以采用B-tree(多路平衡查找树)
以一颗最大度数(max-degree)为5(5阶)的b-tree为例(每个节点最多存储4个key,5个指针)。(树的度数指的是一个节点的子节点个数。)
B-tree可视化:Data Structure Visualization
大于4个key时,中间元素向上分裂(2n阶则为第个元素向上分裂):
B+tree是B-tree的变种,以一颗最大度数(max-degree)为4(4阶)的b+tree为例:
所有的数据都会存放在叶子节点,根节点只做索引作用。
中间元素向上裂变时,中间元素仍会保留在叶子节点中,叶子节点间会形成单向链表。
MySOL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上,增加一个指向相邻叶子节点的链表指针,就形成了带有顺序指针的B+Tree,提高区间访问的性能。
Hash
底层数据结构是用哈希表实现的,只有精确匹配索引列的查询才有效,不支持范围查询
hash索引就是采用一定的hash算法,将键值换算成新的hash值,映射到对应的槽位上,然后存储在hash表中。
如果两个(或多个)键值,映射到一个相同的槽位上,他们就产生了hash冲突(也称为hash碰撞),可以通过链表来解决。
hash索引的特点:
- Hash索引只能用于对等比较(=,in),不支持范围查询 (between,>,<,...
- 无法利用索引完成排序操作
- 查询效率高,通常(不存在hash碰撞时)只需要一次检索就可以了,效率通常要高于B+tree索引
在MysQL中,支持hash索引的是Memry引擎,而lnnoDB中具有自适应hash功能,hash索引是存储引擎根据B+Tree索引在指定条件下自动构建的。
R-tree(空间索引)
空间索引是MyISAM引擎的一个特殊索引类型,主要用于地理空间数据类型,通常使用较少
Full-text(全文索引)
是一种通过建立倒排索引快速匹配文档的方式。类似于Lucene, Solr, ES
思考
为什么InnoDB存储引擎选择使用B+tree索引结构?
- 相对于二叉树,层级更少,搜索效率高;
- 对于B-tree,无论是叶子节点还是非叶子节点,都会保存数据,这样导致一页中存储的键值减少,指针跟着减少,要同样保存大量数据,只能增加树的高度,导致性能降低;
- 相对Hash索引,B+tree支持范围匹配及排序操作。
索引的分类
分类 | 含义 | 特点 | 关键字 |
---|---|---|---|
主键索引 | 针对与表中主键创建的索引 | 默认自动创建,只能有一个 | PRIMARY |
唯一索引 | 避免同一个表中某数据类的值重复 | 可以有多个 | UNIQUE |
常规索引 | 快速定位特定数据 | 可以有多个 | |
全文索引 | 全文索引查找的是文本中的关键词,而不是比较索引中的值 | 可以有多个 | FULLTEXT |
在InnoDB存储引擎中,根据索引的存储形式,又可以分为以下两种:
- 聚集索引(Clustered Index):将数据存储与索引放到了一块,索引结构的叶子节点保存了行数据。必须有,而且只有一个。
选取规则:
- 如果存在主键,主键索引就是聚集索引。
- 如果不存在主键,将使用第一个唯一(UNIQUE)索引作为聚集索引。
- 如果表没有主键,或没有合适的唯一索引,则innoDB会自动生成一个rowid作为隐藏的聚集索引。
- 二级索引(Secondary Index):将数据与索引分开存储,索引结构的叶子节点关联的是对应的主键。可以存在多个。
id为主键,则id为聚集索引。聚集索引下面挂的是行数据,二级索引下面挂的是name对应的id。
当执行下面这行代码时,先通过二级索引找到Arm对应的id=10,再根据聚集索引找到id=10的row(行数据)。这个过程成为回表查询。
select * from user where name = 'Arm';
思考
- 以下SQL语句,那个执行效率高?为什么?(其中,id为主键,name字段创建有索引)
select * from user where id = 10; select * from user where name = 'Arm';
答:代码1只需要根据聚集索引查询即可;代码2需要有回表查询的过程。
- InnoDB主键索引的B+tree有多高?
假设:一行数据大小为1k,一页中可以存储16行这样的数据。InnoDB的指针占用6个字节的空间,主键即使为bigint,占用字节数为8。
如果树的高度为2,当前节点存储的key的数量为n,则n+1为指针数量:
,解得
,则这个B+tree能存储的数据量大概为18736
::
索引的语法
-- 创建索引
CREATE [UNIQUE | FULLTEXT] INDEX index_name ON table_name (index_col_name, ...);
-- 查看索引
SHOW INDEX FROM tb_name;
-- 删除索引
DROP INDEX idx_tbname_colname ON tb_name;
举个栗子
准备数据:复制world中的country表于test数据库中,对于这张表进行操作,这样不会改变原有数据。
use test;
-- 复制world中的country表
CREATE TABLE `country` (
`Code` char(3) NOT NULL DEFAULT '',
`Name` char(52) NOT NULL DEFAULT '',
`Continent` enum('Asia','Europe','North America','Africa','Oceania','Antarctica','South America') NOT NULL DEFAULT 'Asia',
`Region` char(26) NOT NULL DEFAULT '',
`SurfaceArea` decimal(10,2) NOT NULL DEFAULT '0.00',
`IndepYear` smallint DEFAULT NULL,
`Population` int NOT NULL DEFAULT '0',
`LifeExpectancy` decimal(3,1) DEFAULT NULL,
`GNP` decimal(10,2) DEFAULT NULL,
`GNPOld` decimal(10,2) DEFAULT NULL,
`LocalName` char(45) NOT NULL DEFAULT '',
`GovernmentForm` char(45) NOT NULL DEFAULT '',
`HeadOfState` char(60) DEFAULT NULL,
`Capital` int DEFAULT NULL,
`Code2` char(2) NOT NULL DEFAULT '',
PRIMARY KEY (`Code`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_0900_ai_ci;
insert into country select * from world.country; -- 复制
需求:
- name字段为国家名称字段,该字段的值可能会重复,为该字段创建索引。
- code2是非空,且唯一的,为该字段创建唯一索引。
- 为continent, region, surfacearea创建联合索引。
- 为GNP建立合适的索引来提升查询效率。
-- 查询当前表有的索引
show index from country; -- 只有1个主键索引,数据结构为B+tree(结果显示为BTREE)
-- 创建索引
-- 1
create index idx_country_name on country(name);
-- 2
create unique index idx_country_code2 on country(code2);
-- 3
create index idx_country_ctt_reg_sfa on country(continent, region, surfacearea); -- 字段顺序是有讲究的
-- 4
create index idx_country_gnp on country(gnp);
-- 删除索引
drop index idx_country_gnp on country;
SQL性能分析
SQL执行频率
-- 查看服务器状态信息
SHOW [SESSION | GLOBAL] STATUS;
-- 查看当前数据库的INSERT, UPDATE, DELETE, SELECT访问频次
SHOW GLOBAL STATUS LIKE 'Com_______'; -- 7个下划线
如果上述结果中,select占绝大多次,则需要根据这一块,对SQL进行优化。其他也一样。
慢查询日志
慢查询日志记录了所有执行时间超过指定参数(long_query_time,单位: 秒,默认10秒)的所有SQL语句的日志。可以定位哪些sql语句用时较长,从而进行针对性的优化。
MySQL的慢查询日志默认没有开启,可以通过以下方式设置:
-- 查询慢查询日志是否开启及日志文件
show variables like 'slow_query%';
-- 开启mysql的慢查询日志
set global slow_query_log = 1;
-- 设置慢查询日志的时间为0.01秒(便于教学),SQL语句执行时间超过2秒,就会视为慢查询,记录慢查询日志
set global long_query_time = 0.01;
以管理员身份打开慢查询日志xxx-slow.log。在C:\ProgramData\MySQL\MySQL Server 8.0\Data目录下,可以找到慢查询记录(但cmd只能在关闭mysql慢查询以后才能打开这个文件,还是linux实时更好一点)。
profile详情
show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling参数,能够看到当前MySQL是否支持。默认profiling是关闭的,可以通过set语句在session/global级别开启profiling:
-- mysql是否支持
select @@have_profiling;
-- 查看profiling是否打开
select @@profiling;
-- 打开profiling
set profiling = 1;
执行一系列的业务SOL的操作,然后通过如下指令查看指令的执行耗时:
-- 查看每一条SQL的耗时基本情况
show profiles;
-- 查看指定query_id的SQL语句各个阶段的耗时情况
show profile for query query_id;
-- 查看指定query_id的SQL语句CPU的使用情况
show profile cpu for query query_id;
explain执行计划
EXPLAIN 或者 DESC命令获取 MySQL 如何执行 SELECT 语句的信息,包括在 SELECT 语句执行过程中表如何连接和连接的顺序。
-- 直接在select语句之前加上关键字 explain / desc
EXPLNN SELECT 字段列表 FROM 表名 WHERE 条件
- id:select查询的序列号,表示查询中执行selet子句或者是操作表的顺序(id相同,执行顺序从上到下; id不同,值越大,越先执行)。
-- 多表查询 use world; explain select a.name as city_name, b.name as country_name from city a, country b where a.countrycode = b.code;
-- 查询country为Afghanistan的ciry name(子查询) explain select name from city where countrycode = (select code from country where name = 'Afghanistan');
- select_type:表示 SELECT 的类型,常见的取值有 SIMPLE(简单表,即不使用表连接或者子查询)、PRIMARY(主查询,即外层的查询)、UNION (UNION 中的第二个或者后面的查询语句)、SUBQUERY (SELECT/WHERE之后包含了子查询)等
- type:表示连接类型,性能由好到差的连接类型为NULL(当查询的时候不访问任何表时)、System(访问系统表)、const(用主键/唯一索引访问时)、eq_ref、ref(使用非唯一索引访问时)、range、index、all。
- possible key:显示可能应用在这张表上的索引,一个或多个
- Key:实际使用的索引,如果为NULL,则没有使用索引。
- Key_len:表示索引中使用的字节数,该值为索引字段最大可能长度,并非实际使用长度,在不损失精确性的前提下,长度越短越好
- rows:MySQL认为必须要执行查询的行数,在innodb引擎的表中,是一个估计值,可能并不总是准确的。
- filtered:表示返回结果的行数占需读取行数的百分比,filtered 的值越大越好,100%是最好的。
索引的使用
验证索引效率
-- 在未建立索引之前,执行如下SQL语句,查看SQL的耗时。
SELECT * FROM tb_sku WHERE sn = 100000003145001; -- sku大概1000万条数据,大概20秒
-- 针对字段创建索引
create index idx_sku_sn on tb_sku(sn);
-- 然后再次执行相同的SQL语句,再次查看SQL的耗时
SELECT * FROM tb_sku WHERS sn = '100000003145001'; -- 大概0.01秒
说明创建索引对提高数据查询速度非常有效。如果这里直接查询100000003145001数值,也能查到,但是不会再使用idx_sku_sn索引(索引失效),查询速度不会提升。
使用原则
最左前缀法则
如果索引了多列(联合索引),要遵守最左前缀法则。最左前缀法则指的是查询从索引的最左列开始,并且不跳过索引中的列。
如果跳跃某一列,索引将部分失效(后面的字段索引失效)。
use test;
explain select * from country
where continent = 'Africa'
and region = 'Central Africa'
and surfacearea = 1246700.00;
-- 索引生效
explain select * from country
where continent = 'Africa'; -- 最左边存在,没有跳过
-- 索引部分生效
explain select * from country
where continent = 'Africa'
and surfacearea = 1246700.00; -- 最左边存在,但只用了continent索引,后面索引失效
-- 索引失效
explain select * from country
where surfacearea = 1246700.00; -- 最左边不存在
范围查询
联合索引中,出现范围查询(>,<),范围查询右侧的列索引失效
索引列运算
不要在索引列上进行运算操作,索引将失效
explain select code2, name from country where code2 = 'AQ'; -- 用到了索引
explain select code2, name from country where substring(code2, 1, 1) = 'A'; -- 没用到索引
字符串不加引号
字符串类型字段使用时,不加引号,索引将失效
模糊查询
如果仅仅是尾部模糊匹配,索引不会失效。如果是头部模糊匹配,索引失效
OR连接的条件
用or分割开的条件,如果or前的条件中的列有索引,而后面的列中没有索引,那么涉及的索引都不会被用到。解决:给没有索引的列创建索引。
数据分布原则
如果MySQL评估使用索引比全表更慢,则不使用索引。
SQL提示
SQL提示,是优化数据库的一个重要手段,简单来说,就是在SOL语句中加入一些人为的提示来达到优化操作的目的。
提示:use index; ignore index; force index;
explain select * from country use index(idx_country_code2) where code2 like 'A%'; -- 这只是给mysql的建议,建不建议在mysql自身
explain select * from country ignore index(idx_country_code2) where code2 like 'A%';
explain select * from country force index(idx_country_code2) where code2 like 'A%'; -- 强制mysql
覆盖索引
尽量使用覆盖索引(查询使用了索引,并且需要返回的列,在该索引中已经全部能够找到,也就是不出现回表查询),减少select * (实际中因尽量避免使用select *,因为很容易造成回表查询,这时性能就会较低)。
explain select code, continent from country where continent = 'Europe' and region = 'Southern Europe' and surfacearea = '28748.00';
explain select code, continent, region, surfacearea from country where continent = 'Europe' and region = 'Southern Europe' and surfacearea = '28748.00';
explain select code, name, continent, region, surfacearea from country where continent = 'Europe' and region = 'Southern Europe' and surfacearea = '28748.00';
explain select * from country where continent = 'Europe' and region = 'Southern Europe' and surfacearea = '28748.00';
前两条SQL语句中select包含了主键索引primary的字段code,在使用联合索引idx_country_ctt_reg_sfa 查询时,不需要回表查询就可以直接返回结果,这时候就是覆盖索引。mysql显示using where; using index。
但后两条SQL语句中,查询了除主键索引和联合索引以外的列name,这时候就需要回表查询,相应的性能也较弱。mysql显示using index condition。
- using index condition : 查找使用了索引,但是需要回表查询数据
- using where; using index : 查找使用了索引,但是需要的数据都在索引列中能找到,所以不需要回表查询数据
思考:一张表,有四个字段(id,username,password,status)由于数据量大,需要对以下SQL语句进行优化,该如何进行才是最优方案::
select id, username, password from tb_user where usename = 'hxx';
解决:优化就是建立索引,最优的就是建立username, password的联合索引。
前缀索引
当字段类型为字符串(varchar,text等)时,有时候需要索引很长的字符串,这会让索引变得很大,查询时,浪费大量的磁盘0,影响查询效率。此时可以只将字符串的一部分前缀,建立索引,这样可以大大节约索引空间,从而提高索引效率。 (主要用于大文本字符串)
create index idx_xxx on tb_name(col(n)); -- 提取列的前n个字符创建索引
前缀长度n:可以根据索引的选择性来决定,而选择性是指不重复的索引值(基数)和数据表的记录总数的比值,索引选择性越高则查询效率越高,唯一索引的选择性是1,这是最好的索引选择性,性能也是最好的。
select 'sub_20' 前缀长度, count(distinct substring(name, 1, 20)) / count(*) 选择性 from country
union
select 'sub_10', count(distinct substring(name, 1, 10)) / count(*) from country
union
select 'sub_05', count(distinct substring(name, 1, 5)) / count(*) from country
union
select 'sub_03', count(distinct substring(name, 1, 3)) / count(*) from country;
-- 创建索引
create index idx_country_name_5 on country(name(5));
-- 比较
explain select * from country use index(idx_country_name_5) where name = 'Canada';
explain select * from country where name = 'canada';
单列索引&联合索引
在业务场景中,如果存在多个查询条件,考虑针对于查询字段建立索引时,建议建立联合索引,而非单列索引。多条件联合查询时,MySOL优化器会评估哪个字段的索引效率更高,会选择该索引完成本次查询。
-- 联合索引:容易避免回表查询(注意考虑字段顺序)
create unique index idx_country_name_region on country(name, region);
索引的设计原则
- 针对于数据量较大(100w+),且查询比较频繁的表建立索引。
- 针对于常作为查询条件 (where)、排序 (order by)、分组 (group by) 操作的字段建立索引。
- 尽量选择区分度高的列作为索引,尽量建立唯一索引,区分度越高,使用索引的效率越高。
- 如果是字符串类型的字段,字段的长度较长,可以针对于字段的特点,建立前缀索引。
- 尽量使用联合索引,减少单列索引,查询时,联合索引很多时候可以覆盖索引,节省存储空间,避免回表,提高查询效率。
- 要控制索引的数量,索引并不是多多益善,索引越多,维护索引结构的代价也就越大,会影响增删改的效率。
- 如果索列不能存储NULL值,请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时,它可以更好地确定哪个索引最有效地用于查询
不总结=白学
THE END