一)联合索引:
1)定义:是给一张表上面的多个列增加索引，也就是说给表上面的多个列增加索引，供快速查询使用，当两个列的组合是唯一值时，联合索引是个不错的选择
联合索引和单个索引对比来讲，联合索引的所有索引项都会出现在索引上，存储引擎会先根据第一个索引项排序，如果第一个索引项相同的话才会去看第二个，所有我们在查询的时候，如果头索引不带的话，联合索引就会失效，因为在根节点他就不知道怎么往下走
一般来说在实际开发中，很少使用到单个字段来做索引，因为总的来说尽可能还是让索引的数量少，避免占用磁盘空间太多影响性能；
二)索引失效:
1)最左前缀法则:如果说使用了联合索引，那么要遵循最左前缀法则，最左前缀法则指的是从查询索引的最左列开始，并且不跳过索引中的列，如果说跳过某一列，那么直接会导致后面字段索引失效；
1.1)注意:最左前缀法则指的是最左边的列，是在进行查询的时候，联合索引的最左边字段必须存在和我们前后所写的SQL顺序没有关系
1.2)就是当你比如说创建了一张表，这张表的所有字段都有索引，那么这个时候最左匹配不遵循，索引也不会失效，因为索引树里面已经包含了有关于查询的全部字段，此时就不需要进行回表查询了
1.3)况且如果说跳过了某一列，后面的索引字段全部失效
desc select * from child where username="A" and classID=1; +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-----------------------+ | 1 | SIMPLE | child | NULL | ref | AllIndex | AllIndex | 153 | const | 1 | 50.00 | Using index condition | +----+-------------+-------+------------+------+---------------+----------+---------+-------+------+----------+-----------------------+select * from child;//查询表里面所有字段 +----+----------+----------+---------+--------+ | id | username | password | classID | salary | +----+----------+----------+---------+--------+ | 1 | A | 12345 | 1 | 1234 | | 2 | B | 8989 | 2 | 89 | +----+----------+----------+---------+--------+ 2 rows in set (0.00 sec) mysql> create index AllIndex on child(username,password,classID);//针对表中的三列建立索引 Query OK, 0 rows affected (0.03 sec) Records: 0 Duplicates: 0 Warnings: 0 mysql> select * from child where username="A" and password="12345" and classID=1;//符合最左前缀法则,索引生效 +----+----------+----------+---------+--------+ | id | username | password | classID | salary | +----+----------+----------+---------+--------+ | 1 | A | 12345 | 1 | 1234 | +----+----------+----------+---------+--------+ mysql> desc select * from child where username="A" and password="12345" and classID=1; //显示信息 +----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------+ | 1 | SIMPLE | child | NULL | ref | AllIndex | AllIndex | 311 | const,const,const | 1 | 100.00 | NULL | +----+-------------+-------+------------+------+---------------+----------+---------+-------------------+------+----------+-------+ mysql> select * from child where password="12345" and classID=1; //不满足最左前缀法则,索引失效 +----+----------+----------+---------+--------+ | id | username | password | classID | salary | +----+----------+----------+---------+--------+ | 1 | A | 12345 | 1 | 1234 | +----+----------+----------+---------+--------+ mysql> desc select * from child where password="12345" and classID=1; +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+ | 1 | SIMPLE | child | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 50.00 | Using where | +----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+-------------+
1)在联合索引中，出现了范围查询，范围查询右侧的列索引失效，所以尽量少使用>,<而是使用>=和<=
2)在索引列上面进行列运算操作，那么索引列会失效，也包括查询的列使用了函数
3)字符串类型的字段不加上引号，那么索引字段失效，或者说查询的列进行了隐式类型转换
4)如果是like尾部模糊查询，那么索引不会失效，但是如果头部模糊查询，那么索引会失效
5)or连接条件:如果说or前面的列有索引，但是后面的列没有索引，那么涉及到的索引都不会生效，只有当or两侧都有索引之后，索引才会生效
MYSQL评估如果说索引比全表扫描的更慢，那么直接会走全表扫描，索引也会失效
前缀索引:当需要针对字符串类型来进行创建索引的时候，索引的字符串就会变得很长很长，或者是大文本数据，查询时候会浪费大量的磁盘IO，影响查询效率，所以应该只将字符串的一部分前缀作为索引，这样可以大大节省索引空间
create index passwordIndex on child(password(3)); show index from child; +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | child | 0 | PRIMARY | 1 | id | A | 2 | NULL | NULL | | BTREE | | | | child | 1 | AllIndex | 1 | username | A | 2 | NULL | NULL | YES | BTREE | | | | child | 1 | AllIndex | 2 | password | A | 2 | NULL | NULL | YES | BTREE | | | | child | 1 | AllIndex | 3 | classID | A | 2 | NULL | NULL | YES | BTREE | | | | child | 1 | passwordIndex | 1 | password | A | 2 | 3 | NULL | YES | BTREE | | | +-------+------------+---------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------
select count(*) from child;//先进行查询一共有多行 +----------+ | count(*) | +----------+ | 2 | +----------+ 1 row in set (0.01 sec) mysql> select count(distinct password) from child;//查询不重复密码有多少行 +--------------------------+ | count(distinct password) | +--------------------------+ | 2 | +--------------------------+ select count(distinct substring(password,1,3))/count(*) from child;//查询截取后的字段除以总数据长度的值是不是1,如果截取的字段出现了重复,那么值会小于1 +--------------------------------------------------+ | count(distinct substring(password,1,3))/count(*) | +--------------------------------------------------+ | 1.0000 | +--------------------------------------------------+1)create index 索引名字 on 表名(哪一个列名(整数前缀，截取充当前缀索引的范围))
2)咱们可以根据索引的选择性来进行确定，而选择性是根据不重复的索引值/数据库表中的记录总数的比值，索引选择性越高那么查询效率就越高，性能也是最好的
最后一列中的Sub_Part就是描述在原来的字符串里面截取了几个字段
上面就创建了一个前缀索引:对前五个字符构建前缀索引并没有针对这个字段建立完整的索引
1)当进行查询select * from "17799999"的话，会进行截取前5个字符，根据字符先后顺序，进行比较，也就是根据26个字母的先后顺序，查到主键id，再根据主键id在聚簇索引上面查询到完整的数据载荷了，再从数据载荷里面查询到email的值，和刚才的from后面的值进行对比，相同的话返回完整的记录
2)然后那个前缀索引的叶子结点的指针继续向后走，重复刚才的过程
单列索引和联合索引的对比:
create table User(userID int,username varchar(40),password(40)，classID int);
1)在User表中针对username和password分别创建索引，也就是说现在有两个索引
先在进行操作select username,password from User where username=XXX and password=XXX
2)但是现在这样的查询效率是比较低的，但是最终MYSQL只会选择一个索引，所以还是要进行回表查询的
3)但是当针对这两个字段创建一个联合索引在继续执行上面的SQL语句，就会效率很高的
所以说在进行多条件联合查询的时候，就例如上面的例子，针对两个列分别建立了索引，又创建了联合索引，MYSQL就会进行自动评估哪一个字段的索引效率会更高，从而会进行选择合适的，效率高的索引来进行查询
覆盖索引:是select的数据列只用从索引中就能够取得，不必读取数据行，换句话说查询列要被所建的索引覆盖
三)存储引擎:
连接池:提供了多个用于客户端和服务器端交互的线程
SQLInterface:接收SQL指令，返回查询结果的
Parser:解析器，语法解析SQL，语义解析SQL，生成一个语法树，便于后面进行查询的优化
Optimizer:对SQL进行优化
Cachees/Buffer:查询缓存以key(SQL语句)，value(查询出来的结果)的方式来缓存查询结果的
插件式的存储引擎:和底层的文件系统进行交互
MYSQL的存储引擎:
2.1)在创建表的时候指定存储引擎:
create table 表名() engine=存储引擎的名字
2.2)查看数据库所支持的存储引擎:show engines
四)SQL优化:
一)慢查询日志:
通过这个来进行查询那些select的操作频次比较高，要针对那些SQL执行效率比较低，MYSQL的慢查询日志记录了所有执行时间查过了指定参数的所有SQL语句的日志，MYSQL的慢查询日志没有进行开启，需要在MYSQL的配置文件里面(/ect/my.cnf)配置如下信息:
1)启动慢查询日志执行开关:show_query_log=1;
2)设置慢查询的时间是2s，如果说SQL语句执行时间超过2s，就会被视为是慢查询操作，会进行记录慢查询日志:long_query_time=2;
3)show variables like "%slow_query_log%";查询当前数据库是否支持慢查询日志
慢查询日志在linux中的路径是:/var/lib/mysql/localhost-show.log
查询里面的信息cat localhost-show.log
二)观察SQL语句的执行时间:
profile详情:
看看当前数据库是否支持profiles:select @@having_profiling
开启profile操作:set profiling =1
mysql> select * from dish; +--------+--------------+-----------+ | dishID | dishName | dishMoney | +--------+--------------+-----------+ | 2 | 红烧茄子 | 90 | | 3 | 红烧里脊 | 100 | | 4 | 红烧牛肉 | 700 | | 5 | 牛肉干 | 70 | +--------+--------------+-----------+ 4 rows in set (0.00 sec) mysql> show profiles; +----------+------------+--------------------+ | Query_ID | Duration | Query | +----------+------------+--------------------+ | 1 | 0.00010350 | show profies | | 2 | 0.00072225 | show tables | | 3 | 0.00054600 | select * from dish | +----------+------------+--------------------+ 通过上面的命令就可以看到MYSQL语句的耗时时间show profiles:查看每一条SQL的基本查询情况
show profile for query query_id;查看指定id的SQL的查询情况，看看时间长短，看看哪一部分耗时时间
show profile cpu for query query_id;
show profile for query 3; +----------------------+----------+ | Status | Duration | +----------------------+----------+ | starting | 0.000073 | | checking permissions | 0.000004 | | Opening tables | 0.000102 | | init | 0.000023 | | System lock | 0.000030 | | optimizing | 0.000002 | | statistics | 0.000008 | | preparing | 0.000006 | | executing | 0.000001 | | Sending data | 0.000187 | | end | 0.000002 | | query end | 0.000013 | | closing tables | 0.000005 | | freeing items | 0.000076 | | cleaning up | 0.000018 | +----------------------+----------+
覆盖索引定义:MySQL 执行计划 explain 结果里的 key 有使用索引，如果 select 后面查询的字段都可以从这个索引的树中获取，这种情况一般可以说是用到了覆盖索引，extra 里一般都有 using index；覆盖索引一般针对的是辅助索引，整个査询结果只通过辅助索引就能拿到结果，不需要通过辅助索引树找到主键，再通过主键去主键索引树里获取其它字段值。
1.覆盖索引是一种数据查询方式，不是索引类型
2.在索引数据结构中，通过索引值可以直接找到要查询字段的值，而不需要通过主键值回表查询，那么就叫覆盖索引
3.查询的字段被使用到的索引树全部覆盖到4)假设你定义一个联合索引
create index id_name_age on user(name,age);
5)查询名称为 liudehua 的年龄：select name, age from user where name = "生命在于运动"
6)上述语句中，查找的字段 name 和 age 都包含在联合索引 id_name_age 的索引树中，这样的查询就是覆盖索引查询
五)explain执行计划:
可以通过explain或者是desc命令来进行获取到MYSQL是如何执行select语句的信息，包括select语句执行过程中如何连接和怎么连接的顺序
expalin执行计划各个字段的含义:
4.1)id:表示MYSQL查询的序列号，表示查询中执行select子句或者是表的操作顺序，id相同，执行顺序从上到下，id不同，值越大，越被先执行
现在针对这几个字段来进行演示一下
创建一张学生表: 1)drop table if exists student; 2)create table student(id int primary key auto_increment, name varchar(40)); drop table if exists course; 3)create table course(courseid int primary key auto_increment, coursename varchar(50)); drop table if exists student_course; 4)create table student_course( studentid int, courseid int, foreign key (studentid) references student(id), foreign key (courseid) references course(courseid)); 5)insert into student values(1,"李佳伟"),(2,"张中军"),(3,"张志超"); 6)insert into course values(1,"Java"),(2,"MYSQL"),(3,"C++"); 现在我们来进行查询一下每一个人选了那些课程: mysql> select student.*,course.coursename from student_course,course,student where student.id=student_course.studentid and student_course.courseid=course.courseid; +----+-----------+------------+ | id | name | coursename | +----+-----------+------------+ | 1 | 李佳伟 | Java | | 1 | 李佳伟 | MYSQL | | 2 | 张中军 | MYSQL | | 3 | 张志超 | Java | | 3 | 张志超 | MYSQL | | 3 | 张志超 | C++ | +----+-----------+------------+刚才在进行使用外键的时候，注意:
1)本表中的字段名也要加上括号
2)注意是references
desc select student.*,course.coursename from student_course,course,student where student.id=student_course.studentid and student_course.courseid=course.courseid; +----+-------------+----------------+------------+------+--------------------+----------+---------+---------------------------+------+----------+----------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+----------------+------------+------+--------------------+----------+---------+---------------------------+------+----------+----------------------------------------------------+ | 1 | SIMPLE | course | NULL | ALL | PRIMARY | NULL | NULL | NULL | 3 | 100.00 | NULL | | 1 | SIMPLE | student_course | NULL | ref | studentid,courseid | courseid | 5 | orderdish.course.courseid | 1 | 100.00 | NULL | | 1 | SIMPLE | student | NULL | ALL | PRIMARY | NULL | NULL | NULL | 3 | 33.33 | Using where; Using join buffer (Block Nested Loop) | +----+-------------+----------------+------------+------+--------------------+----------+---------+------------------查询一下选择了MYSQL课程的学生，子查询
select courseid from course where coursename="MYSQL"; select studentid from student_course where courseid=2; select name from student where id in(1,2,3);select name from student s1 where s1.id in (select studentid from student_course s2 where s2.courseid=(select courseid from course s3 where s3.coursename="MYSQL"));----------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra | +----+-------------+-------+------------+------+--------------------+------+---------+------+------+----------+--------------------------------------------------------------------+ | 1 | PRIMARY | s1 | NULL | ALL | PRIMARY | NULL | NULL | NULL | 3 | 100.00 | NULL | | 1 | PRIMARY | s2 | NULL | ALL | studentid,courseid | NULL | NULL | NULL | 6 | 16.67 | Using where; FirstMatch(s1); Using join buffer (Block Nested Loop) | | 3 | SUBQUERY | s3 | NULL | ALL | NULL | NULL | NULL | NULL | 3 | 33.33 | Using where | +----+-------------+-------+------------+------+--------------------+------+---------+------+------+----------+----------------------------------------------4.2)select type:表示select的类型
常见的取值有simple:简单表，表示不进行使用表连接或者子查询
primary:表示主查询，即外层的查询
union:表示union后面第二个或者后面的查询语句
subquery:表示select where后面包括了子查询等等
4.3)type:表示连接类型，性能由好到差的连接类型有
null(什么时候都不操作表)
system(系统表)，
const:把一个主键放在where后面进行作为条件查询
eq_ref(在join中使用主键或者唯一索引)，
ref(使用非唯一性索引进行查询)，
range(索引的范围查询)，
index(代表索引覆盖，遍历所有的索引查找)
all(全表扫描，(通常没有建索引的列))；
4.4)possible_key:显示可能应用在这张表上面的索引，一个或者多个
4.5)key_len:表示索引中所使用的字节数，该值可能为索引字段最大可能长度，而不是实际使用长度，在不损失精度的情况下，长度越短越好
4.6)rows:MYSQL认为必须要进行执行查询的行数，在innodb引擎中是一个估计值，但是结果并不总是准确的
4.7)filedter:返回结果的行数占需要进行读取行数的百分比，这个值越大性能越高
4.8)Extra:表示额外数据查询，执行情况的描述和说明
4.9)key:表示实际使用到的索引，如果为空，那么表示从来没有使用过索引，反之，那么使用到了索引
5.0)table:表示输出结果的表
六)MYSQL中的锁:
一)全局锁
1)就是对整个数据库进行加锁，加锁之后整个数据库就处于只读状态，后续的DML写语句，DDL语句，以及对更新事务的提交操作都会被阻塞，典型地使用场景就是做整个数据库的逻辑备份，对所有的表进行锁定，从而获取到一致性视图，保证数据的完整性，是锁粒度最大的锁，只能进行查询操作
2)就是将数据库中的数据，放到一个文件里面做数据备份
加全局锁:flush tables with read lock;
进行数据备份:mysqldump -uroot -p12503487 数据库名> D:/mysql.sql
释放锁:unlock tables
二)表级锁:
每一次操作直接进行锁住整张表，锁的粒度大，锁冲突的概率比较高，并发性是最低的，应用在MYISM，INNDB引擎当中，对于表级锁，主要分成三类:
一)表锁:对于表锁，主要分为两类:
加锁操作:
lock tables 表名+read;表示加读锁
lock tables 表名+write;表示加写锁
解锁操作:
unlock tables;
1)表共享读锁:
所有操作只能读，不能写，所有客户端都可以读，都不可以写
1.1)首先客户端1对这张表加了一个读锁，就会把这张表锁住了，那么客户端1肯定是可以读取这张表的数据的，那么客户端1只能读取这张表里面的数据，是不能够写数据和操作事务
1.2)此时假设有一个客户端2想读取这张表的数据是可以的，写数据和操作事务是不可以的
对于当前加读锁的客户端，进行更新操作直接会出现报错信息，对于其他客户端，想要操作这张表，即使输入SQL语句，也会阻塞等待，只有等到对应的加锁的客户端把所释放之后，之前阻塞的SQL语句才可以执行成功
2)表共享写锁:
表共享写锁:metadata
当前加写锁的客户端既能进行读，也可以进行写操作，但是别的客户端既不可以读，也是不可以写的，就会阻塞等待，一直阻塞到表锁释放为止
3)元数据锁:
元数据锁，无需进行显式使用，在访问一张表的时候会自动加上，也就是说某一张表存在未提交的事务，是不能修改这张表的表结构，如果输入了对应的SQL语句，就会出现阻塞状态，为了防止DDL和DML语句冲突，保证数据读写的正确性
对一张表进行增删改查的时候，加上MDL读锁(共享锁)，兼容
当我们对表结构进行变更的时候，会加上MDL写锁(排它锁)
1)客户端1开启了一个事务，现在进行查询select * from student，咱们的客户端2想要进行修改这个表结构:alert user add column Java int，这个客户端就会出现阻塞状态
2)一直阻塞到事务提交
元数据锁可以认为就是认为是一张表的结构；
4)意向锁:是给表中的行加锁的时候(顺便加上的)
1)意向锁，那么客户端1对表加上了行锁之后，客户端二如何给表进行加锁呢？
2)对于一个客户端1，开启一个事务，进行数据的更新操作DML语句，在进行更新的时候，会给涉及到的行进行加锁，客户端2想要对这张表进行加锁的时候，会检查当前表中是否有对应的行锁，如果说没有，就进行添加表锁，
3)此时就会从第一行数据，检查到最后一行数据，效率很低
1)客户端1在进行执行DML语句操作的时候，会对涉及到的行进行加锁，同时也会对该表进行加锁(意向锁)
2)而其他的客户端，在对这张表加上表锁的时候，会先根据给表曾经加上的意向锁来进行判定是否可以成功的加上表锁，而不用再判断行锁情况了
3)而意向锁的种类以及是否最终表锁最终是否会添加成功，完全取决于加行锁的时候的行为
一旦事务提交了，那么最终我们的意向共享锁，意向排它锁都会进行释放
意向共享锁(IS):由select ......lock in share mode 添加，和表级共享锁(read)兼容，和表级排它锁互斥(write)；
1)select * from score where id=1 lock in share mode; 表示给这一行数据加上一个行锁的共享锁,还会给这一张score表加上一个意向共享锁,咱们现在的侧重点是给这张表加上意向共享锁 2)其他会话进行给整张表进行加锁的时候:lock table score read;//表示给表加锁成功,因为意向共享锁和(给表加读锁)是兼容的 3)如果说其他会话加上了lock table score write;//表示加锁失败,因为意向共享锁是(和给表加写锁)是互斥的,一直处于阻塞状态意向排它锁:由insert update delete，select.....for update进行添加，和表级共享锁(read)和表级排它锁互斥(write)，但是意向锁之间不会互斥；
当我们顺便给一行进行加锁的时候: update student set username="A"; 1)此时就会给表锁自动加上一个意向排它锁,那么此时别的客户端 进行给整张表加读锁和写锁都会失败 2)因为此时InnoDB引擎会自动进行判断当前表是否有意向锁,有的话,是意向排它锁还是意向共享锁,再决定和当前给表加读锁和写锁是否互斥
三)行级锁:
1)行级锁是每一次操作都会锁住对应的行数据，锁的粒度最小，发生锁冲突的概率最低并发最高，应用在InnoDB引擎当中
2)InnoDB引擎是基于索引来进行组织的，行锁是针对索引上面的索引项来进行加锁实现的，而不是针对记录进行加锁
锁住单个行记录的锁，防止其他事务进行update和delete，在read commited 和reapitable read都支持:
在InnoDB引擎里面我们进行提供了两种类型的行锁:
1)共享锁:S共享锁和共享锁之间是兼容的，共享锁和排它锁之间是互斥的，事务A可以获取到这一行数据的共享锁，事务B可以获取到这个数据行的共享锁
2)排它锁S:某一个事务获取到了某一行数据的排它锁，就不能在获取到这一行数据的共享锁和排它锁了，排它锁和排它锁都是互斥的
常用的对数据进行增删改查的加行锁操作:
1)insert update delete 会自动加上排它锁
2)select 不会加上任何锁
3)select+对应的SQL语句+lock+in+share+mode;----会自动加上共享锁
4)select+for+update会自动加上排它锁
假设现在客户端1进行给行数据加上共享锁: 1)select * from stu where userID=1 lock in share model; 现在客户端2进行操作: 1)update set username="Java" where userID=3;//这条语句将会被正常执行,因为之前共享锁所得是id=1的叶子节点的数据,但是id=3的叶子节点的数据并没有被锁住 2)update set username="C++" where userID=1;//这个就会失败,之前在第一个客户端已经给id=1的行数据加上了共享锁,而此时还想加排它锁,那么此时就会出现不兼容的情况,就会出现堵塞等待此时我们在进行举一个例子: 1)客户端1进行修改操作: update student set username="" where id=1; 2)当我们的第二个客户端也进行操作更新的时候,就会失败: update userInfo set username="admin" where userID=6;此时执行着一条语句的时候就会尝试给这一行加上排它锁，但是之前已经加过排它锁了,排它锁和排它锁之间互斥,所以会一直进行阻塞等待 3)我们的第二个客户端再去执行:select * from userInfo; 4)select * from userInfo where userID=6 lock in share mode;此时我们想查询这一行数据,会自动加上共享锁,共享锁和排它锁之间会互斥注意:
1)对唯一索引进行检索的时候，对已经存在的记录进行等值匹配的时候会被自动优化成行锁
2)InnoDB的行锁是针对索引来进行加的锁，不通过索引来进行检索数据，那么InnoDB引擎会对表中所有的记录进行加锁，此时就会升级成表锁，当我们针对索引字段进行更新操作，就可以避免行锁升级成表锁的情况
3)此时我们开启一个事务update student set username="Java" where username="Java"，此时我们并没有针对name字段进行建立索引，此时我们但是根据name字段来进行查询，就会出现直接给整张表加锁
4)此时进行对整张表的任意一个字段，都会失败
二:间隙锁(Gap)
间隙锁是直接锁住索引间隙记录(不会包含该记录)，确保索引记录间隙不变，防止其他事务在这个间隙进行插入操作，产生幻读，在可重复读的事务隔离级别下支持
间隙锁Gap，左右都是开区间，间隙锁+行锁合称next-key lock,每个 next-key lock 是前开后闭区间，间隙锁和next-key lock的引入帮我们解决幻读问题。
三:临键锁:行锁和间隙锁进行组合，不仅锁住对应的行，还锁住了前面的间隙(RR下支持)
默认情况下:InnoDB使用repeatable read事务隔离级别来进行运行，InnoDb使用临建锁来进行搜索和索引扫描:
1)当进行索引上面的等值查询的时候，给不存在的记录进行加锁的时候，优化成间隙锁，会优化成两端区间的间隙加锁
1)select * from stu; +----+------+----------+ | id | name | password | +----+------+----------+ | 1 | A | 1234 | | 4 | B | 8899 | +----+------+----------+ 2 rows in set (0.00 sec) 2)mysql> create index nameIndex on stu(name);//我们给他建立唯一索引 3)pdate stu set name="Java" where id=2;现在我们进行数据的更新操作 此时客户端2进行开启事务,进行尝试在1和4之间插入数据: insert into stu values(3,"N","777888"); 此时就会插入失败2)当我们进行加锁的时候，我们是针对索引来进行加锁的，而索引是一个B+树，而B+树的叶子结点是一个有序的双向链表，
1)假设是唯一索引，那么对18这个记录进行操作的时候，是不会插入一条记录是18的数据的
2)但是此时是非唯一索引，有可能在18之后进行加锁，也有可能对18到29之间继续加锁，那么会向后遍历一个值不满足需求的时候，临建锁会退化成间隙锁，会对18-29和16-18这段空隙进行加锁
3)接下来进行演示一下:
客户端1: //1.先创建一张表create table stu(id int,username varchar(50),password varchar(50),age varchar(40)); //2.进行插入语句: select * from stu; +------+----------+----------+------+ | id | username | password | age | +------+----------+----------+------+ | 1 | A | 126 | 1 | | 2 | B | 89 | 3 | | 3 | C | 90 | 10 | +------+----------+----------+------+ //3.进行查询:针对age字段创建普通索引:create index age on stu(age); //4.开启事务: start transcation; //5.进行更新操作: update stu set password="90" where age=3; 客户端2: 开启事务进行插入操作: insert into stu values(2,"D","ooo",2);失败 客户端3: 开启事务进行插入操作: insert into stu values(2,"D","ooo",8);失败 //因为此时年龄字段,已经对1-3和3-10这段区间进行加上了间隙锁3)索引上面的范围查询，会进行访问到不满足条件的第一个值为止:
start transaction; Query OK, 0 rows affected (0.00 sec) mysql> select * from stu where age>4 lock in share mode; +------+----------+----------+------+ | id | username | password | age | +------+----------+----------+------+ | 3 | C | 90 | 10 | | 2 | D | ooo | 12 | +------+----------+----------+------+ 此时就在年龄4以后加了间隙锁,以后的其他客户端不允许在
扫一扫
10万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
