mysql-锁引 mysql-锁引

1 锁引介绍官方介绍索引是帮助MySQL高效获取数据的数据结构。更通俗的说，数据库索引好比是一本书前面的目
录，能加快数据库的查询速度。
官方文档：
https://dev.mysql.com/doc/refman/8.0/en/index-condition-pushdown-optimization.html

优势
检索 -可以提高数据检索的效率，降低数据库的IO成本
排序- 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。
劣势
索引会占据磁盘空间
索引虽然会提高查询效率，但是会降低更新表的效率。比如每次对表进行增删改操作，
MySQL不仅要保存数据，还有保存或者更新对应的索引文件。

2 锁引分类

单列锁引（普通锁引，唯一锁引，全文锁引）
组合锁引
空间锁引

聚集锁引
非聚集锁引

MySQL的Innodb存储引擎的索引分为聚集索引和非聚集索引两大类，理解聚集索引和非聚集索引可通过对比汉语字典的索引。汉语字典提供了两类检索汉字的方式，第一类是拼音检索（前提是知道该汉字读音），比如拼音为cheng的汉字排在拼音chang的汉字后面，根据拼音找到对应汉字的页码（因为按拼音排序，二分查找很快就能定位），这就是我们通常所说的字典序；第二类是部首笔画检索，根据笔画找到对应汉字，查到汉字对应的页码。拼音检索就是聚集索引，因为存储的记录（数据库中是行数据、字典中是汉字的详情记录）是按照该索引排序的；笔画索引，虽然笔画相同的字在笔画索引中相邻，但是实际存储页码却不相邻。
create table student (
id INT UNSIGNED AUTO_INCREMENT,
name VARCHAR(255),
PRIMARY KEY(id),
KEY(name)
【mysql-锁引】) ENGINE=InnoDB DEFAULT CHARSET=utf8;
该表中主键id是该表的聚集索引、name为非聚集索引；表中的每行数据都是按照聚集索引id排序存储的；比如要查找name='Arla'和name='Arle'的两个同学，他们在name索引表中位置可能是相邻的，但是实际存储位置可能差的很远。name索引表节点按照name排序，检索的是每一行数据的主键。聚集索引表按照主键id排序，检索的是每一行数据的真实内容。
也就是说查询name='Arle'的记录时，首先通过name索引表查找到Arle的主键id（可能有多个主键id，因为有重名的同学），再根据主键id的聚集索引找到相应的行记录；
聚集索引一般是表中的主键索引，如果表中没有显示指定主键，则会选择表中的第一个不允许为NULL的唯一索引，如果还是没有的话，就采用Innodb存储引擎为每行数据内置的6字节ROWID作为聚集索引。
每张表只有一个聚集索引，因为聚集索引在精确查找和范围查找方面良好的性能表现（相比于普通索引和全表扫描），聚集索引就显得弥足珍贵，聚集索引选择还是要慎重的（一般不会让没有语义的自增id充当聚集索引）。
从宏观上分析下聚集索引和普通索引的性能差异，还是针对上述student表：
（1）select * from student where id >5000 and id <20000;
（2）select * from student where name > 'Alie' and name < 'John';
第一条SQL语句根据id进行范围查询，因为(5000, 20000)范围内的记录在磁盘上按顺序存储，顺序读取磁盘很快就能读到这批数据。
第二条SQL语句查询（'Alie', 'John'）范围内的记录，主键id分布可能是离散的1，100，20001，5000.....；增加了随机读取数据页几率；所以普通索引的范围查询效率被聚集索引甩开几条街都不止；非聚集索引的精确查询效率还是可以的，比聚集索引查询只增加了一次IO开销。
创建锁引单列锁引-普通锁引
CREATE INDEX index_name ON table(column(length)) ; ALTER TABLE table_name ADD INDEX index_name (column(length)) ;
单列锁引-唯一锁引
CREATE UNIQUE INDEX index_name ON table(column(length)) ; alter table table_name add unique index index_name(column);
单列锁引-全文锁引
CREATE FULLTEXT INDEX index_name ON table(column(length)) ; alter table table_name add fulltext index_name(column)
组合索引
ALTER TABLE article ADD INDEX index_titme_time (name1(50),name2(10)) ;
删除锁引 DROP INDEX index_name ON table
查看索引 SHOW INDEX FROM table_name
3 锁引原理锁引是在存储引擎中实现的，不同的存储引擎会使用不同的锁引，MyISAM和InnoDB存储引擎只支持B+TREE锁引
B树和B+树
数据结构示例网站：
https://www.cs.usfca.edu/~galles/visualization/Algorithms.html
B树是为了磁盘或其他存储设备而设计的一种多叉平衡查找树

B树的高度一般都是在2-4这个高度，树的高度直接影响IO读写的次数。
如果是三层树结构---支撑的数据可以达到20G，如果是四层树结构---支撑的数据可以达到几十T

B和B+的区别
B树和B+树的最大区别在于非叶子节点是否存储数据的问题。
B树是非叶子节点和叶子节点都会存储数据。
B+树只有叶子节点才会存储数据，而且存储的数据都是在一行上，而且这些数据都是有指针指向的，也就是有顺序的。

文章图片
B -Tree

文章图片
B+Tress 4 锁引使用场景需要创建锁引：

主键自动创建唯一锁引
频繁作为查询条件的字段应该创建索引 where
多表关联查询中，关联字段应该创建索引 on 两边都要创建索引
查询中排序的字段，应该创建索引 B + tree 有顺序
覆盖索引好处是？不需要回表组合索引
统计或者分组字段，应该创建索引
不需要创建锁引
表记录太少，锁引是要有存储的开销
频繁更新锁引要维护
查询字段使用频率不高

组合锁引

文章图片

在一颗索引树上由多个字段

优势：效率高，省空间，容易形成覆盖锁引
使用：遵循最左前缀原则
1, 前缀索引:like 常量% 使用索引,like %常量不使用索引
2, 最左前缀: 从左向右匹配直到遇到范围查询 > < between 索引失效

执行计划 explain出来的信息有10列，分别是
id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra
举例：

--用户表 create table tuser( id int primary key, loginname varchar(100), name varchar(100), age int, sex varchar(100), dep int, address varchar(100) ); --部门表 create table tdep( id int primary key, name varchar(100) ); --地址表 create table taddr( id int primary key, addr varchar(100) ); --创建普通索引 mysql> alter table tuser add index idx_dep(dep); --创建普通索引 mysql> alter table tdep add index idx_name(name); --创建唯一索引 mysql> alter table tuser add unique index idx_loginname(loginname); --创建组合索引 mysql> alter table tuser add index idx_name_age_sex(name,age,sex); --创建全文索引 mysql> alter table taddr add fulltext ft_addr(addr); INSERT into tuser VALUES(1, 'zhangshan', '张三', 10, '', 2, '辽宁'); INSERT into tuser VALUES(2, 'zhangshan2', '张三2', 12, '', 2, '辽宁1'); INSERT into tuser VALUES(3, 'zhangshan3', '张三3', 13, '', 3, '辽宁3'); INSERT into tuser VALUES(4, 'zhangshan4', '张三4', 14, '', 4, '辽宁4'); INSERT into tuser VALUES(5, 'zhangshan5', '张三5', 15, '', 5, '辽宁5');

id
1,每个 SELECT语句都会自动分配的一个唯一标识符.
2,表示查询中操作表的顺序，有三种情况：
id相同：执行顺序由上到下
id不同：如果是子查询，id号会自增，id越大，优先级越高。
id相同的不同的同时存在
3, id列为null的就表示这是一个结果集，不需要使用它来进行查询。
select_type*
查询类型，主要用于区别普通查询、联合查询(union、union all)、子查询等复杂查询。

simple
表示不需要union操作或者不包含子查询的简单select查询。有连接查询时，外层的查询为simple，且只有一个
explain select * from tuser;

文章图片
primary
一个需要union操作或者含有子查询的select，位于最外层的单位查询的select_type即为primary。且只有一个
subquery
除了from字句中包含的子查询外，其他地方出现的子查询都可能是subquery
explain select (select name from tuser) from tuser

文章图片
dependent subquery
与dependent union类似，表示这个subquery的查询要受到外部表查询的影响
explain select id,name,(select name from tdep a where a.id=b.dep) from tuser b;

文章图片

table

显示的查询表名，如果查询使用了别名，那么这里显示的是别名
如果不涉及对数据表的操作，那么这显示为null
如果显示为尖括号括起来的就表示这个是临时表，后边的N就是执行计划中的id，表示结果来自于这个查询产生。
如果是尖括号括起来的，与类似，也是一个临时表，表示这个结果来自于union查询的id为M,N的结果集。

type*
依次从好到差：
system，const，eq_ref，ref，fulltext，ref_or_null，unique_subquery， index_subquery，range，index_merge，index，ALL
除了all之外，其他的type都可以使用到索引，除了index_merge之外，其他的type只可以用到一个索引
优化器会选用最优索引一个
最少要索引使用到range级别。

system
表中只有一行数据或者是空表。
const*
使用唯一索引或者主键，返回记录一定是1行记录的等值where条件时，通常type是const。其他数据库也叫做唯一索引扫描

explain select * from tuser where id=1;

文章图片

explain select * from tuser where loginname = 'zhangshan';

文章图片

eq_ref
关键字:连接字段主键或者唯一性索引。
此类型通常出现在多表的 join 查询, 表示对于前表的每一个结果, 都只能匹配到后表的一行结果. 并且查询的比较操作通常是 '=', 查询效率较高.

explain select a.id from tuser a left join tdep b on a.dep=b.id;

文章图片

ref*
针对非唯一性索引，使用等值（=）查询非主键。或者是使用了最左前缀规则索引的查询。
非唯一索引
explain select * from tuser where dep=1;

文章图片

等值非主键连接
explain select a.id from tuser a left join tdep b on a.name=b.name;

文章图片
最左前缀
explain select * from tuser where name = '张三';

文章图片

ref :const
key_len 403
使用name锁引
explain select * from tuser where name = '张三' and age=10;

文章图片

ref :const,const
key_len 408
使用name, age锁引
explain select * from tuser where name = '张三' and age=10 and sex='11';

文章图片
ref :const,const,const
key_len 811
使用name, age, sex锁引
explain select * from tuser where age=10 and sex='1';

文章图片

全表扫描
explain select * from tuser where name = '张三' and sex='1';

文章图片

只用到了name锁引
结论：最佳左前缀法则：带头大哥不能死、中间兄弟不能断

fulltext
全文索引检索，要注意，全文索引的优先级很高，若全文索引和普通索引同时存在时，mysql不管代价，优先选择使用全文索引
explain select * from taddr where match(addr) against('bei');

文章图片
ref_or_null
与ref方法类似，只是增加了null值的比较。实际用的不多。
unique_subquery
用于where中的in形式子查询，子查询返回不重复值唯一值
index_subquery
用于in形式子查询使用到了辅助索引或者in常数列表，子查询可能返回重复值，可以使用索引将子查询去重。
range*
索引范围扫描，常见于使用>,<,is null,between ,in ,like等运算符的查询中。
explain select * from tuser where id>1;

文章图片

explain select * from tuser where name like 'a%';

文章图片

index_merge
表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引，官方排序这个在ref_or_null之后，但是实际上由于要读取所个索引，性能可能大部分时间都不如range
index*
关键字：条件是出现在索引树中的节点的。可能没有完全匹配索引。
索引全表扫描，把索引从头到尾扫一遍，常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组的查询。
explain select loginname from tuser;
单锁引

文章图片

explain select age from tuser;

文章图片

组合索引
explain select name,age,sex from tuser;

文章图片

覆盖锁引

all*
这个就是全表扫描数据文件，然后再在server层进行过滤返回符合要求的记录。
explain select * from tuser;

possible_keys
此次查询中可能选用的索引，一个或多个
key
查询真正使用到的索引，select_type为index_merge时，这里可能出现两个以上的索引，其他的select_type这里只会出现一个。
key_len

用于处理查询的索引长度，如果是单列索引，那就整个索引长度算进去，如果是多列索引，那么查
询不一定都能使用到所有的列，具体使用到了多少个列的索引，这里就会计算进去，没有使用到的列，这里不会计算进去。
留意下这个列的值，算一下你的多列索引总长度就知道有没有使用到所有的列了。

ref

如果是使用的常数等值查询，这里会显示const
如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段
如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func

rows
这里是执行计划中估算的扫描行数，不是精确值（InnoDB不是精确的值，MyISAM是精确的值，主要原
因是InnoDB里面使用了MVCC并发机制）
extra*
这个列包含不适合在其他列中显示单十分重要的额外的信息，这个列可以显示的信息非常多，有几十种，常用的有

no tables used
不带from字句的查询或者From dual查询
using filesort
排序时无法使用到索引时，就会出现这个。常见于order by和group by语句中
说明MySQL会使用一个外部的索引排序，而不是按照索引顺序进行读取。
MySQL中无法利用索引完成的排序操作称为“文件排序”(尽量不要出现)
explain select * from tuser order by address;

文章图片
using index*
查询时不需要回表查询，直接通过索引就可以获取查询的数据。
1 表示相应的SELECT查询中使用到了覆盖索引（Covering Index），避免访问表的数据行，效率不错！
2 如果同时出现Using Where ，说明索引被用来执行查找索引键值
3 如果没有同时出现Using Where ，表明索引用来读取数据而非执行查找动作
using temporary
1 表示使用了临时表存储中间结果。
2 MySQL在对查询结果order by和group by时使用临时表
3 临时表可以是内存临时表和磁盘临时表，执行计划中看不出来，需要查看status变量，used_tmp_table，used_tmp_disk_table才能看出来。
4 在select部分使用了distinct关键字（索引字段）
explain select distinct a.id from tuser a,tdep b where a.dep=b.id;

文章图片
using where*
表示存储引擎返回的记录并不是所有的都满足查询条件，需要在server层进行过滤。
查询条件无索引：
explain select * from tuser where address='beijing';

文章图片

索引失效：
explain select * from tuser where age=1;

文章图片
截屏2021-04-08 16.46.19.png 备注：
查询条件中分为限制条件和检查条件，5.6之前，存储引擎只能根据限制条件扫描数据并返回，然后server层根据检查条件进行过滤再返回真正符合查询的数据。5.6.x之后支持ICP特性，可以把检查条件也下推到存储引擎层，不符合检查条件和限制条件的数据，直接不读取，这样就大大减少了存储引擎扫描的记录数量。extra列显示using index condition
explain select * from tuser where name='张三' and sex='10'

文章图片

根据官方解释：
EXPLAIN output shows Using index condition in the Extra column when Index Condition Pushdown is used. It does not show Using indexbecause that does not apply when full table rows must be read.
锁引失效分析*

1 全值匹配我最爱
explain select * from tuser where name='zhaoyun' and age=1 and sex='1';

文章图片
2 最佳左前缀法则
组合索引：带头索引不能死，中间索引不能断
带头索引死：
explain select * from tuser where age=23;

文章图片

中间索引断（带头索引生效，其他索引失效）：
explain select * from tuser where name='aa' and sex='1';

文章图片

a :explain select * from tuser where name='aa' and sex='1' and age=23;

文章图片
b: explain select * from tuser where name='aa' and sex=1 and age=23;

文章图片

比较a,b
sex类型不一致，导致b只用了name 和age锁引

3 不要在索引上做计算
不要进行这些操作：计算、函数、自动/手动类型转换，不然会导致索引失效而转向全表扫描
explain select * from tuser where left(loginname,1)='zy';

文章图片
4.范围条件右边的列失效
explain select * from tuser where name='张三' and age>20 and sex='1';

文章图片
5 尽量使用覆盖索引
尽量使用覆盖索引（只查询索引的列），也就是索引列和查询列一致，减少select *

explain select name,age,sex from tuser ;

文章图片
explain select * from tuser ;

文章图片
explain select address from tuser ;

文章图片
explain select loginname from tuser ;

文章图片

6 .索引字段上不要使用不等
索引字段上使用（！= 或者 < >）判断时，会使用范围索引，

explain select * from tuser where loginname='zhangshan';

文章图片
explain select * from tuser where loginname!='zhangshan';

文章图片
截屏2021-04-08 17.45.05.png explain select * from tuser where dep!=7;

文章图片
explain select * from tuser where dep=7;

文章图片

7.主键索引字段上不可以判断null
主键字段上不可以使用 null 索引字段上使用 is null 判断时，可使用索引
explain select * from tuser where id is not null;

文章图片
8 .索引字段使用like不以通配符开头索引字段使用like以通配符开头（‘%字符串’）时，会导致索引失效而转向全表扫描
explain select * from tuser where name like '%a';

文章图片

问题：解决like ‘%字符串%’时，索引失效问题的方法？使用覆盖索引可以解决
explain select name ,age,sex from tuser where name like '%a%';

文章图片

9.索引字段字符串要加单引号
explain select * from tuser where name=123;

文章图片
10.索引字段不要使用or
索引字段使用 or 时，会导致索引失效而转向全表扫描
explain select * from tuser where name='张三' or age=23;

文章图片