mysql索引详解

何时使用复合索引，要根据where条件建索引，注意不要过多使用索引，过多使用会对更新操作效率有很大影响。
如果表已经建立了(col1，col2)，就没有必要再单独建立(col1);如果现在有(col1)索引，如果查询需要 col1和col2条件，可以建立(col1,col2)复合索引，对于查询有一定提高。

1.5. 全文索引

比like 关键字更快
创建全文索引的方法如下:

CREATE FULLTEXT INDEX <索引的名字> ON tablen ame (字段名);
ALTER TABLE tablename ADD FULLTEXT [索引的名字] (字段名);
CREATE TABLE tablename ( […], FULLTEXT KEY [索引的名字] (字段名) ;

和常用的like 模糊查询不同，全文索引有自己的语法格式，使用 match 和 again st 关键字，比如

select * from user
 where match(name) against('aaa');

全文索引使用注意事项:

全文索引必须在字符串、文本字段上建立。
全文索引字段值必须在最小字符和最大字符之间的才会有效。(innodb:3-84;my i sam:4- 84)
全文索引字段值要进行切词处理，按syntax字符进行切割，例如b+aaa，切分成b和aaa
全文索引匹配查询，默认使用的是等值匹配，例如a匹配a，不会匹配ab,ac。如果想匹配可以在布尔模式下搜索a*

select * from user
 where match(name) against('a*' in boolean mode);

2.索引的原理

MySQL官方对索引定义:是存储引擎用于快速查找记录的一种数据结构。需要额外开辟空间和数据维护工作。

索引是物理数据页存储，在数据文件中(Inn oDB，ibd 文件)，利用数据页(page)存储。
索引可以加快检索速度，但是同时也会降低增删改操作速度，索引维护需要代价。
索引涉及的理论知识:二分查找法、Hash和B+Tree

2.1. 二分 查找

二分查找法也叫作折半查找法，它是在有序数组中查找指定数据的搜索算法。它的优点是等值查询、范围查询性能优秀，缺点是更新数据、新增数据、删除数据维护成本高。

2.2.Hash 结构

Hash 底层实现是由Hash表来实现的，是根据键值 <key,value> 存储数据的结构。非常适合根据key 查找 value值，也就是单个key 查询，或者说等值查询。

Inn oDB自适应哈希索引:在使用Has h索引访问时，一次性查找就能定位数据，等值查询效率要优于 B+Tree。

自适应哈希索引的建立使得Inn oDB存储引擎能自动根据索引页访问的频率和模式自动地为某些热点页建立哈希索引来加速访问。另外Inn oDB自适应哈希索引的功能，用户只能选择开启或关闭功能，无法进行人工干涉。

show engine innodb status G;
show variables like '%innodb_adaptive%';

2.3 B+Tree结构

MySQL数据库索引采用的是B+Tree结构，在B-Tree结构上做了优化改造

B-Tree结构：

索引值和data数据分布在整棵树结构中
每个节点可以存放多个索引值及对应的data数据
树节点中的多个索引值从左到右升序排列

B树的搜索:从根节点开始，对节点内的索引值序列采用二分法查找，如果命中就结束查找。没有命中会进入子节点重复查找过程，直到所对应的的节点指针为空，或已经是叶子节点了才结束。
B+Tree结构

非叶子节点不存储data数据，只存储索引值，这样便于存储更多的索引值
叶子节点包含了所有的索引值和data数据
叶子节点用指针连接，提高区间的访问性能

相比B树，B+树进行范围查找时，只需要查找定位两个节点的索引值，然后利用叶子节点的指针进行遍历即可。而B树需要遍历范围内所有的节点和数据，显然B+Tree效率高。

2.4. 聚簇索引和辅助索引

聚簇索引和非聚簇索引:B+Tree的叶子节点存放主键索引值和行记录就属于聚簇索引;如果索引值和行记录分开存放就属于非聚簇索引。
主键索引和辅助索引:B+Tree的叶子节点存放的是主键字段值就属于主键索引;如果存放的是非主键值就属于辅助索引(二级索引)。

在Inn oDB引擎中，主键索引采用的就是聚簇索引结构存储。

聚簇索引(聚集索引)

聚簇索引是一种数据存储方式，Inn oDB的聚簇索引就是按照主键顺序构建 B+Tree结构。B+Tree 的叶子节点就是行记录，行记录和主键值紧凑地存储在一起。这也意味着 Inn oDB 的主键索引就是数据表本身，它按主键顺序存放了整张表的数据，占用的空间就是整个表数据量的大小。通常说的主键索引就是聚集索引。
Inn oDB的表要求必须要有聚簇索引:

如果表定义了主键，则主键索引就是聚簇索引
如果表没有定义主键，则第一个非空unique列作为聚簇索引
否则Inn oDB会从建一个隐藏的row–id作为聚簇索引
辅助索引

InnoDB辅助索引，也叫作二级索引，是根据索引列构建 B+Tree结构。但在 B+Tree 的叶子节点中只存了索引列和主键的信息。二级索引占用的空间会比聚簇索引小很多，通常创建辅助索引就是为了提升查询效率。一个表InnoDB只能创建一个聚簇索引，但可以创建多个辅助索引。

非聚簇索引

与InnoDB表存储不同，MyISAM数据表的索引文件和数据文件是分开的，被称为非聚簇索引结构。

3. 索引分析与优化*

3.1.EXPLAIN

MySQL 提供了一个 EXPLAIN 命令，它可以对 SELECT 语句进行分析，并输出 SELECT 执行的详细信息，供开发人员有针对性的优化。例如:

EXPLAIN SELECT * from user WHERE id < 3;

EXPLAIN 命令的输出内容大致如下:

ty pe

表示存储引擎查询数据时采用的方式。比较重要的一个属性，通过它可以判断出查询是全表扫描还是基于索引的部分扫描。常用属性值如下，从上至下效率依次增强。

  - ALL:表示全表扫描，性能最差。
  -  index:表示基于索引的全表扫描，先扫描索引再扫描全表数据。 
  - range:表示使用索引范围查询。使用>、>=、<、<=、in等等。 
  - ref:表示使用非唯一索引进行单值查询。 
  - eq_ref:一般情况下出现在多表join查询，表示前面表的每一个记录，都只能匹配后面表的一 行结果。
  - const:表示使用主键或唯一索引做等值查询，常量查询。
  - NULL:表示不用访问表，速度最快。

rows

MySQL查询优化器会根据统计信息，估算SQL要查询到结果需要扫描多少行记录。原则上rows是
越少效率越高，可以直观的了解到SQL效率高低。

key_len

表示查询使用了索引的字节数量。可以判断是否全部使用了组合索引。
key_len的计算规则如下:

字符串类型

字符串长度跟字符集有关:latin1=1、gbk=2、utf8=3、utf8mb4=4

char(n):n*字符集长度
 varchar(n):n * 字符集长度 + 2字节

数值类型

TINYINT:1个字节
SMALLINT:2个字节
MEDIUMINT:3个字节
INT、FLOAT:4个字节
BIGINT、DOUBLE:8个字节

时间类型

DATE:3个字节
TIMESTAMP:4个字节
DATETIME:8个字节

字段属性

NULL属性占用1个字节，如果一个字段设置了NOT NULL，则没有此项。

Extra

Extra表示很多额外的信息，各种操作会在Extra提示相关信息，常见几种如下:

Using where

表示查询需要通过索引回表查询数据。
Using index

表示查询需要通过索引，索引就可以满足所需数据。
Using filesort 表示查询出来的结果需要额外排序，数据量小在内存，大的话在磁盘，因此有Using filesort 建议优化。
Using temprora ry

查询使用到了临时表，一般出现于去重、分组等操作。

3.2. 回表查询

在之前介绍过，InnoDB索引有聚簇索引和辅助索引。聚簇索引的叶子节点存储行记录，InnoDB必须要有，且只有一个。辅助索引的叶子节点存储的是主键值和索引字段值，通过辅助索引无法直接定位行记录，通常情况下，需要扫码两遍索引树。先通过辅助索引定位主键值，然后再通过聚簇索引定位行记录，这就叫做回表查询，它的性能比扫一遍索引树低。
总结:通过索引查询主键值，然后再去聚簇索引查询记录信息

3.3. 覆盖索引

只需要在一棵索引树上就能获取SQL所需的所有列数据，无需回表，速度更快，这就叫做索引覆盖。
实现索引覆盖最常见的方法就是:将被查询的字段，建立到组合索引。

3.4. 最左前缀原则

复合索引使用时遵循最左前缀原则，最左前缀顾名思义，就是最左优先，即查询中使用到最左边的列，那么查询就会使用到索引，如果从索引的第二列开始查找，索引将失效。

3.5. LIKE查询

面试题:MySQL在使用li ke模糊查询时，索引能不能起作用?
回答:MySQL在使用Like模糊查询时，索引是可以被使用的，只有把%字符写在后面才会使用到索引。

select * from user where name li ke ‘%o%’; //不起作用
sel ect * from user where name li ke ‘o%’; //起作用
sel ect * from user where name li ke ‘%o’; //不起作用

3.6. NULL查询

面试题:如果MySQL表的某一列含有NULL值，那么包含该列的索引是否有效?
对MySQL来说，NULL是一个特殊的值，从概念上讲，NULL意味着“一个未知值”，它的处理方式与其他值有些不同。比如:不能使用=，<，>这样的运算符，对NULL做算术运算的结果都是NULL，count时不会包括NULL行等，NULL比空字符串需要更多的存储空间等。

3.7. 排序和索引

MySQL查询支持filesort和index两种方式的排序，filesort是先把结果查出，然后在缓存或磁盘进行排序
 操作，效率较低。使用index是指利用索引自动实现排序，不需另做排序操作，效率会比较高。
filesort有两种排序算法:双路排序和单路排序。
双路排序:需要两次磁盘扫描读取，最终得到用户数据。第一次将排序字段读取出来，然后排序;第二
次去读取其他字段数据。
单路排序:从磁盘查询所需的所有列数据，然后在内存排序将结果返回。如果查询数据超出缓存 sort_buffer，会导致多次磁盘读取操作，并创建临时表，最后产生了多次IO，反而会增加负担。解决方案:少使用sel ect *;增加sort_buffer_size容量和max_length_for_sort_data容量。
如果我们Explain分析SQL，结果中Extra属性显示Using filesort，表示使用了filesort排序方式，需要优化。如果Extra属性显示Using index时，表示覆盖索引，也表示所有操作在索引上完成，也可以使用 index排序方式，建议大家尽可能采用覆盖索引。

以下几种情况，会使用index方式的排序。
- ORDER BY 子句索引列组合满足索引最左前列

explain select id from user order by id; //对应(id)、(id,name)索引有效

WHERE子句+ORDER BY子句索引列组合满足索引最左前列

explain select id from user where age=18 order by name; //对应 (age,name)索引

以下几种情况，会使用filesort方式的排序。
- 对索引列同时使用了ASC和DESC

explain select id from user order by age asc,name desc; //对应 (age,name)索引

WHERE子句和ORDER BY子句满足最左前缀，但where子句使用了范围查询(例如>、<、in 等)

explain select id from user where age>10 order by name; //对应 (age,name)索引

ORDER BY或者WHERE+ORDER BY索引列没有满足索引最左前列

explain select id from user order by name; //对应(age,name)索引

使用了不同的索引，MySQL每次只采用一个索引，ORDER BY涉及了两个索引

explain select id from user order by name,age; //对应(name)、(age)两个索 引

WHERE子句与ORDER BY子句，使用了不同的索引

explain select id from user where name='tom' order by age; //对应 (name)、(age)索引

WHERE子句或者ORDER BY子句中索引列使用了表达式，包括函数表达式

explain select id from user order by abs(age); //对应(age)索引

4.查询优化*

4.1 慢查询定位

开启慢查询日志

查看 MySQL 数据库是否开启了慢查询日志和慢查询日志文件的存储位置的命令如下:

SHOW VARIABLES LIKE 'slow_query_log%'

通过如下命令开启慢查询日志:

SET global slow_query_log = ON;
SET global slow_query_log_file = 'OAK-slow.log';
SET global log_queries_not_using_indexes = ON;
SET long_query_time = 10;

long_query_time:指定慢查询的阀值，单位秒。如果SQL执行时间超过阀值，就属于慢查询

记录到日志文件中。

log_queries_not_using_index es:表示会记录没有使用索引的查询SQL。前提是slow_query_log 的值为ON，否则不会奏效。

查看慢查询日志

文本方式查看

直接使用文本编辑器打开 slow.log日志即可。

  - time:日志记录的时间 
  - User@Host:执行的用户及主机 
  - Query_time:执行的时间 
  - Lock_time:锁表时间 
  - Rows_sent:发送给请求方的记录数，结果数量 
  - Rows_examined:语句扫描的记录条数
  - SET timestamp:语句执行的时间点
  - select....:执行的具体的SQL语句

使用mysqldump slow查看

MySQL 提供了一个慢查询日志分析工具 mysqldump slow，可以通过该工具分析慢查询日志内容。
在 MySQL bin 目录下执行下面命令可以查看该使用格式。

perl mysqldumpslow.pl --help

运行如下命令查看慢查询日志信息:

perl mysqldumpslow.pl -t 5 -s at C:ProgramDataMySQLDataOAK-slow.log

除了使用mysqldumpslow工具，也可以使用第三方分析工具，比如pt-query–dig est、 mysqlsla等。

4.2 慢查询优化

索引和慢查询
- 如何判断是否为慢查询?

MySQL判断一条语句是否为慢查询语句，主要依据SQL语句的执行时间，它把当前语句的执行时间跟 long_query_time 参数做比较，如果语句的执行时间 > long_query_time，就会把这条执行语句记录到慢查询日志里面。long_query_time 参数的默认值是 10s，该参数值可以根据自己的业务需要进行调整。

如何判断是否应用了索引?

SQL语句是否使用了索引，可根据SQL语句执行过程中有没有用到表的索引，可通过 explain
命令分析查看，检查结果中的 key 值，是否为NULL。

应用了索引是否一定快?

下面我们来看看下面语句的 explain 的结果，你觉得这条语句有用上索引吗?比如

select * from user where id>0;

	虽然使用了索引，但是还是从主键索引的最左边的叶节点开始向右扫描整个索引树，进行了
	全表扫描，此时索引就失去了意义。

而像 select * from user where id = 2; 这样的语句，才是我们平时说的使用了索引。它表示的意思是，我们使用了索引的快速搜索功能，并且有效地减少了扫描行数。
查询是否使用索引，只是表示一个SQL语句的执行过程;而是否为慢查询，是由它执行的时间决定的，也就是说是否使用了索引和是否是慢查询两者之间没有必然的联系。
我们在使用索引时，不要只关注是否起作用，应该关心索引是否减少了查询扫描的数据行数，如果
扫描行数减少了，效率才会得到提升。对于一个大表，不止要创建索引，还要考虑索引过滤性，过
滤性好，执行速度才会快。

4.3 提高索引过滤性

假如有一个5000万记录的用户表，通过sex=’男’索引过滤后，还需要定位3000万，SQL执行速度也不会很快。其实这个问题涉及到索引的过滤性，比如1万条记录利用索引过滤后定位10条、100 条、1000条，那他们过滤性是不同的。索引过滤性与索引字段、表的数据量、表设计结构都有关系。

下面我们看一个案例:

表:student
字段:id,name,sex,age
造数据:insert into student (name,sex,age) select name,sex,age from student;
SQL案例:select * from student where age=18 and name like '张%';(全表扫 描)

优化1

alter table student add index(name); //追加name索引

优化2

alter table student add index(age,name); //追加age,name索引

优化3

//为user表添加first_name虚拟列，以及联合索引(first_name,age)
alter table student add first_name varchar(2) generated always as
(left(name, 1)), add index(first_name, age);
explain select * from student where first_name='张' and age=18;

慢查询原因总结
- 全表扫描:explain分析type属性all
- 全索引扫描:explain分析type属性index
- 索引过滤性不好:靠索引字段选型、数据量和状态、表设计
- 频繁的回表查询开销:尽量少用select *，使用覆盖索引

4.4 分页查询优化

一般性分页

般的分页查询使用简单的 limit 子句就可以实现。limit 格式如下:

SELECT * FROM 表名 LIMIT [offset,] rows

第一个参数指定第一个返回记录行的偏移量，注意从0开始;
第二个参数指定返回记录行的最大数目;
如果只给定一个参数，它表示返回最大的记录行数目;

思考1:如果偏移量固定，返回记录量对执行时间有什么影响?

select * from user limit 10000,1;
select * from user limit 10000,10;
select * from user limit 10000,100;
select * from user limit 10000,1000;
select * from user limit 10000,10000;

结果:在查询记录时，返回记录量低于100条，查询时间基本没有变化，差距不大。随着查询记录量越大，所花费的时间也会越来越多。
思考2:如果查询偏移量变化，返回记录数固定对执行时间有什么影响?

select * from user limit 1,100;
select * from user limit 10,100;
select * from user limit 100,100;
select * from user limit 1000,100;
select * from user limit 10000,100

结果:在查询记录时，如果查询记录量相同，偏移量超过100后就开始随着偏移量增大，查询时间急剧的增加。(这种分页查询机制，每次都会从数据库第一条记录开始扫描，越往后查询越慢，而且查询的数据越多，也会拖慢总查询速度。)

分页优化方案

第一步:利用覆盖索引优化

select * from user limit 10000,100;
select id from user limit 10000,100;

第二步:利用子查询优化

select * from user limit 10000,100;
select * from user where id>= (select id from user limit 10000,1) limit 100;

原因:使用了id做主键比较(id>=)，并且子查询使用了覆盖索引进行优化

原文地址:https://blog.csdn.net/qq_36649893/article/details/134740028

本文来自互联网用户投稿，该文观点仅代表作者本人，不代表本站立场。本站仅提供信息存储空间服务，不拥有所有权，不承担相关法律责任。

如若转载，请注明出处：http://www.7code.cn/show_20610.html

如若内容造成侵权/违法违规/事实不符，请联系代码007邮箱：suwngjj01@126.com进行投诉反馈，一经查实，立即删除！

声明：本站所有文章，如无特殊说明或标注，均为本站原创发布。任何个人或组织，在未征得本站同意时，禁止复制、盗用、采集、发布本站内容到任何网站、书籍等各类媒体平台。如若本站内容侵犯了原著者的合法权益，可联系我们进行处理。

查询索引键值

1. 索引的类型

1.1. 普通索引

1.2. 唯一索引

1.3.主键索引

1.4. 复合索引

1.5. 全文索引

2.索引的原理

2.1. 二分 查找

2.2.Hash 结构

2.3 B+Tree结构

2.4. 聚簇索引和辅助索引

3. 索引分析与优化*

3.1.EXPLAIN

select _type

ty pe

p ossible_keys

key

rows

key_len

Extra

3.2. 回表查询

3.3. 覆盖索引

3.4. 最左前缀原则

3.5. LIKE查询

3.6. NULL查询

3.7. 排序和索引

4.查询优化*

4.1 慢查询定位

4.2 慢查询优化

4.3 提高索引过滤性

4.4 分页查询优化

发表回复取消回复

1. 索引的类型

1.1. 普通索引

1.2. 唯一索引

1.3.主键索引

1.4. 复合索引

1.5. 全文索引

2.索引的原理

2.1. 二分查找

2.2.Hash结构

2.3 B+Tree结构

2.4. 聚簇索引和辅助索引

3. 索引分析与优化*

3.1.EXPLAIN

select_type

type

possible_keys

rows

key_len

Extra

3.2. 回表查询

3.3. 覆盖索引

3.4. 最左前缀原则

3.5. LIKE查询

3.6. NULL查询

3.7. 排序和索引

4.查询优化*

4.1 慢查询定位

4.2 慢查询优化

4.3 提高索引过滤性

4.4 分页查询优化

相关文章

发表回复 取消回复

2.2.Hash 结构

select _type

ty pe

p ossible_keys

发表回复取消回复