如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，内存时间因为非常短（相比磁盘的IO）可以忽略不计，通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，结束查询，总计三次IO。

真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

2）Hash索引

3）full-text全文索引

4）R-Tree索引

（5）适合创建索引的场景。

1）主键自动建立唯一索引。

2）频繁作为查询条件的字段应该创建索引。为最经常查询、最经常排序的字段建立索引。

3）查询中与其他表关联的字段，外键关系建立索引。

4）where条件里面用不到的字段不创建索引。

5）单键索引和组合索引，在高并发情况下倾向创建组合索引。

6）查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度。

7）查询中统计或分组字段。

（6）不适合创建索引的场景。

1）表中记录数量太少，官方支持单表500~800万数据，数据量达到300万时就要注意。

2）经常增删改的表。

3）频繁更新的字段不适合建立索引。

索引提高了查询速度，在insert、update、delete时，mysql不仅需要保存数据，还要保存更新索引信息，降低了更新表的速度。

4）数据重复并且分布平均的表字段。如果某个数据列中包含许多重复的内容，建立索引没有太大的实际效果。例如：为sex性别字段建立索引。

索引的选择性：指建立索引的字段中不同值的记录数与表中总记录数量的比。

一个索引的选择性越接近1，索引的效率就越高。

7、explain。

（1）explain查看执行计划。

使用explain关键字可以模拟优化器执行sql查询语句，从而知道mysql是如何处理sql语句的，分析查询语句或者是表结构的性能瓶颈。

（2）explain的作用。

1）表的读取顺序。id。

2）数据读取操作的操作类型。select_type。

3）哪些索引可以使用。possible_keys。

4）哪些索引被实际使用。key。

5）表之间的引用。

6）每张表有多少行被优化器查询。rows。

（3）explain分析的各字段解释。

1）id。

select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序。

[1] id都相同，都是一样的。执行顺序由上至下。

[2] id不同，id都不一样。子查询的id序号会递增，id值越大优先级越高，越先被执行。

[3] id有相同的，也有不相同的。id相同的是一组，从上往下顺序执行。在所有组中，id值越大，优先级越高，越先执行。

2）select_type。

查询的类型，主要用于区分普通查询、联合查询、子查询等的复杂查询。

[1]SIMPLE。

简单的select查询，查询中不包含子查询或者UNION。

[2]PRIMARY。

查询中若包含任何复杂的子部分，最外层查询则被标记为PRIMARY，最后面被加载的那个。

[3]SUBQUERY。

在SELECT或WHERE列表中包含了子查询。

[4]DERIVED。

在from列表中包含的子查询被标记为DERIVED衍生，mysql会递归执行这些子查询，把结果放在临时表里。

table列显示的derived是衍生出的临时表，derived2，从id为2那行记录衍生出的临时表。

[5]UNION。

如果第2个select查询语句出现在UNION的后面，则被标记为UNION。

如果UNION包含在FROM子句的子查询中，外层SELECT将被标记为DERIVED。

[6]UNION RESULT。

从UNION表获取结果的SELECT。两个UNION合并的结果集。

3）table。

显示这一行的数据与哪一张表有关。

4）type。

访问类型排列。显示查询使用了何种类型。

type从最好的到最差的简化排序依次是：

system > const > eq_ref > ref > range > index > ALL

type从最好的到最差的详细排序依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

一般保证查询至少达到range级别，最好能达到ref即可。

[1] system。

表只有一行记录，相当于系统表，const类型的特例。

[2] const。

索引1次就找到了记录，只匹配1行数据。const用于比较primary key、unique索引。

例如：在where条件后面查询主键，mysql会将该查询转换成一个常量。

[3] eq_ref。

唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。

[4] ref。

非唯一性索引扫描，返回匹配某个单独值的所有行。可能会找到多个符合条件的行，属于查找和扫描的混合体。

[5] range。

只检索给定范围的行，使用一个索引来选择行。key列显示使用了那个索引。

一般是在where语句中使用了between、<、>、in等的查询。

范围扫描索引只需要开始于索引的某一点，结束于另一点，不用扫描全部索引。

[6] index。

Full Index Scan，index与ALL区别为index类型只遍历索引树，通常比ALL快，索引文件通常比数据文件小。虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘中读取的。

[7] all。

Full Table Scan，将遍历全表以找到匹配的行。

5）possible_keys。

理论上可能被用到的索引，显示可能应用在这张表中的索引，一个或多个。

查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用。

6）key。

实际使用的索引。如果显示为NULL，则没有使用索引或者没有建立索引。

查询中若使用了覆盖索引，则该索引仅出现在key列表中。

覆盖索引：创建了复合索引，刚好查询时用的字段与复合索引的字段个数、顺序吻合。

7）key_len。

表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度，在不损失精确性的情况下，长度越短越好。

key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。

8）ref。

显示索引的那一列被使用了，如果可能的话，是一个常数，哪些列或常量被用于查找索引列上的值。

9）rows。

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。

10）Extra。

包含不适合在其他列中显示但十分重要的额外信息。

[1] Using filesort。

说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。mysql中无法利用索引完成的排序操作称为文件排序。

无法利用索引中的排序，mysql自己创建了一个排序，是一个不好的现象。

[2] Using temporary。

产生了一个临时表，是一个不好的现象。

使用临时表保存中间结果，mysql在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。

[3] USING index。

表示相应的select操作中使用了覆盖索引Covering Index，避免访问了表的数据行，查询效率高。

如果出现using index时，同时出现using where，表明索引被用来执行索引键值的查找。

如果只出现using index，没有出现using where，表明索引用来读取数据而非执行查找动作。

覆盖索引：select的数据列只用从索引中就能够取得，不必读取数据行，mysql可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件，查询列要被所建的索引覆盖。

索引时高效查找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此不必读取整个行，毕竟索引叶子节点存储了他们索引的数据，当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了或覆盖了满足查询结果的数据就叫做覆盖索引。

使用覆盖索引时，select列表中只取出需要的列，不要写select * from。

查询的列要被所建的索引覆盖，查询时的字段个数、顺序要与所建索引一致。

[4] using where。

表明使用了where过滤。

[5] using join buffer。

使用了join连接缓存。

[6] impossible where。

where子句的值总是false，不能用来获取任何元组。

[7] select tables optimized away。

在没有group by子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。

[8] distinct。

优化distinct操作，在找到第一匹配的元组后即停止查找同样值的动作。

[9] using index condition。

搜索条件中虽然出现了索引列，但是有部分条件无法使用索引，会根据能用索引的条件先搜索一遍再匹配无法使用索引的条件。

8、索引案例，数据库表优化。

（1）索引案例，单表优化。

（2）索引案例，两张表优化。

-- 结论：本例情况，左连接相反加，左连接时在右表创建索引。右连接相反加，右连接时在左表创建索引。

-- 由左表连接的特性决定的，left join条件用于确定如何从右表搜索行，左边一定都有，所以右表要建立索引。

（3）索引案例，三张表优化。

-- 结论：索引设置在需要经常查询的字段中。

-- 尽可能减少join语句中的nestedloop的循环总次数，永远用小结果集驱动大的结果集。

-- 优先优化nestedloop的内层循环。

-- 保证join语句中被驱动表上join条件字段已经被索引。

-- 配置文件设置合理的JoinBuffer连接缓存。

9、索引优化。

（1）全值匹配我最爱。

建的索引字段刚好与需要查询的字段一致，字段个数、名称、顺序完全相同。

（2）最佳左前缀法则。

如果索引了多个字段列，需要遵守最左前缀法则，查询从索引的最左前列开始并且不跳过索引中间的列。

带头大哥不能挂，中间兄弟不能断。

（3）索引列上少计算。

不在索引列上做任何操作，包括计算、函数、自动或手动类型转换，会导致索引失效，从而变成全表扫描。

（4）范围之后全失效。

存储引擎不能使用索引中范围条件右边的列。rang范围之后全失效。

（5）覆盖索引不写*。

尽量使用覆盖索引，只访问索引的查询，索引列和查询列一致，减少使用select * from查询。

（6）不等空值还有or，索引失效要少用。

1）在使用不等于“!=”、“<>”的时候，无法使用索引会导致全表扫描。

mysql8.0.12，type显示为range。mysql5.6.17，type显示为ALL。

8.x的mysql进行了优化。

2）is null、is not null无法使用索引。

is not null可以通过搭配覆盖索引使用上索引。

is null不可以使用覆盖索引。

3）少用or，使用or连接会导致索引失效。

（7）like百分写最右。

使用like以通配符“%abc...”开头，索引会失效，变成全表扫描。

“%abc...”索引会失效。

“%abc...%”索引会失效。

“abc...%”索引不会失效。

解决like “%abc...%”时索引不被使用的方法。

like查询的%写右边。如果要两边都要%，搭配覆盖索引使用上索引，使用覆盖索引解决like “%abc...%”时索引没有使用的情况。

（8）varchar单引号不可丢。

字符串不加单引号导致索引失效。

varchar类型要加单引号’字符串’。

（9）总结。

假设有一个复合索引index(a,b,c)

where语句。

索引是否被使用。

where a=3

Y，使用到a。

where a=3 and b=5

Y，使用到a,b。

where a=3 and b=5 and c=4

Y，使用到a,b,c。

where b=3 或者where b=3 and c=4 或者where c=4

N。？？？

where a=3 and c=5

使用到a，但是c不可以，断了中间b。

where a=3 and b>4 and c=5

使用到a和b，c不能用在范围后面，b断了。

where a=3 and b like ‘kk%’ and c=4

Y，使用到a,b,c。

where a=3 and b like ‘%kk’ and c=4

Y，只用到a。

where a=3 and b like ‘%kk%’ and c=4

Y，只用到a。

where a=3 and b like ‘k%kk%’ and c=4

Y，使用到a,b,c。

（10）索引优化记忆口诀。

全值匹配我最爱，最左前缀要遵守。

带头大哥不能挂，中间兄弟不能断。

索引列上少计算，范围之后全失效。

like百分写最右，覆盖索引不写*。

不等空值还有or，索引失效要少用。

varchar单引号不可丢，sql高级也不难。

10、小表驱动大表。

（1）优化流程。

1）开启慢查询，捕获sql。

2）explain + 慢sql分析。

3）show profile查询sql在mysql服务器里面的执行细节和生命周期情况。

4）调优mysql数据库服务器的参数。

（2）小表驱动大表。

小表驱动大表，即小的数据集驱动大的数据集，数据量小的表驱动数据量大的表。

1）当b表的数据量小于a表的数据量时，用in优于exists。

2）当a表的数据量小于b表的数据量时，用exists优于in。

a表与b表的id字段建立索引。

3）exists。

select ... from table where exists(subquery);

将主查询的数据，放到子查询中做条件验证，根据验证结果true或false来决定主查询的数据结果是否得以保留。

exists(subquery)只返回true或false，因此子查询中的select *也可以是select 1或select ‘X’或select 'Y'，实际执行时会忽略select清单。

exists子查询实际执行过程可能经过了优化。

11、使用order by排序时用上索引，order by排序关键字优化。

（1）order by子句尽量使用Using index方式排序，避免使用Using filesort方式排序。

MySQL支持两种方式的排序，FileSort、Index，Index效率高。

指mysql扫描索引本身完成排序，FileSort方式效率较低。

order by满足以下两种情况，才会使用Index方式排序。

order by语句使用索引最左前列。

使用where子句与order by子句条件列组合满足索引最左前列。

（2）filesort的两种算法：双路排序、单路排序。

尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀。

如果不在索引列上，filesort有两种算法：双路排序、单路排序。

双路排序：mysql4.1之前是使用双路排序，取一批数据，扫描两次磁盘，最终得到数据。

读取行指针和order by列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据输出。

从磁盘取排序字段，在buffer进行排序，再从磁盘取其他字段。

单路排序：从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。

并且把随机IO变成了顺序IO，但是它会使用更多的空间，把每一行都保存在内存中。

总体而言单路好于双路。

在sort_buffer中，方法B比方法A要多占用很多空间，因为方法B是把所有字段都取出，所以有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序，创建tmp文件，多路合并，排完再取。

sort_buffer容量大小，再排....导致多次I/O，大量I/O操作。

（3）优化策略。

增大sort_buffer_size参数的设置。

增大max_length_for_sort_data参数的设置。

提高order by的速度。

1）使用order by时，不写select *，写查询需要的具体字段。

当查询的字段大小总和小于max_length_for_sort_data，排序的字段不是text、blob类型时，会使用改进后的算法单路排序，否则使用多路排序。

两种算法的数据可能超出sort_buffer的容量大小，超出之后，会创建tmp文件进行合并排序，导致多次I/O。

2）提高sort_buffer_size大小。

这个参数配置大会提高效率，根据系统服务器情况配置。

3）提高max_length_for_sort_data的大小。

提高这个参数，会增加用改进算法的概念。但是如果设的太高，数据总容量超出sort_buffer_size的概率就增大，明显症状是高的磁盘I/O活动和低的处理器使用率。

（4）为排序使用索引。

mysql使用两种排序方式：Using filesort文件排序，Using index扫描有序索引排序。

mysql能为排序与查询使用相同的索引。

复合索引key a_b_c(a,b,c)

order by能使用索引最左前缀。

如果where使用索引的最左前缀定义为常量，则order by能使用索引。

不能使用索引进行排序。

12、group by关键字优化。

group by先排序后进行分组，遵照索引建的最佳左前缀。

当无法使用索引列，增大max_length_for_sort_data参数的设置 + 增大sort_buffer_size参数的设置。

where高于having，能写在where限定的条件就不要去having限定了。

其他优化与order by优化相同。

13、慢查询日志。

（1）mysql的慢查询日志。

mysql的慢查询日志是mysql提供的一种日志记录，用来记录在mysql中响应时间超过阀值的语句，具体指运行时间超过long_query_time值的sql，则会被记录到慢查询日志中。

long_query_time的默认值为10秒。

（2）查看是否开启慢查询。

show variables like '%slow_query_log%';

（3）开启慢查询日志。

永久开启慢查询，mysql重启后不会失效。

修改my.cnf文件，[mysqld]标签下增加以下参数内容。

不设置slow_query_log_file文件名，默认文件名为主机名称-slow.log。

（4）慢查询配置的时间。

设置之后，需要断开mysql连接，重新开一个窗口会话，才能用【show variables like 'long_query_time%';】看到修改的值。

（5）mysqldumpslow日志分析工具。

window下需要安装Perl。

Perl下载地址：Strawberry Perl for Windows

The Perl Programming Language - www.perl.org

日志分析工具mysqldumpslow。

查看mysqldumpslow帮助信息。

s：是表示按照何种方式排序。

c：访问次数。

l：锁定时间。

r：返回记录。

t：查询时间。

al：平均锁定时间。

ar：平均返回记录数。

at：平均查询时间。

t：即为返回前面多少条的数据。

g：后边搭配一个正则匹配模式，大小写不敏感。

-- 得到返回记录集最多的10个sql。

mysqldumpslow -s r -t 10 'D:\Program Files\MySQL8\db\data\mysql-slow.log'

-- 得到访问次数最多的10个sql。

mysqldumpslow -s c -t 10 'D:\Program Files\MySQL8\db\data\mysql-slow.log'

--得到按照时间排序的前10条里面包含有左连接的查询语句。

mysqldumpslow -s t -t 10 -g "left join" 'D:\Program Files\MySQL8\db\data\mysql-slow.log'

-- 结合| more使用，防止爆屏，防止大量日志刷屏。

mysqldumpslow -s r -t 10 'D:\Program Files\MySQL8\db\data\mysql-slow.log' | more

14、批量插入数据脚本。

15、用Show Profile进行sql分析。

（1）Show Profile介绍。

用来分析当前会话中语句执行的资源消耗情况，可以用于sql的调优的测量。

（2）sql分析。

（3）日常开发需要注意的结论。

converting HEAP to MyISAM。

查询结果太大，内存不够用，往磁盘上放数据了。

Creating tmp table。

创建临时表。拷贝数据到临时表，用完再删除。

Copying to tmp table on disk。

把内存中临时表复制到磁盘，很危险。

locked。

16、全局查询日志。

开启全局查询日志，不要在生产环境使用。

修改my.cnf配置文件，永久生效。

临时开启全局查询日志，mysql重启后失效。

编写的sql语句记录到mysql库中的general_log表。使用如下命令查看。

select * from mysql.general_log;

17、数据库锁。

锁是计算机协调多个进程或线程并发访问某一资源的机制。

（1）锁的分类。

从对数据操作的类型区分：读锁、写锁。

读锁（共享锁）：对于同一份数据，多个读操作可以同时进行，不会相互影响。

写锁（排它锁）：当前写操作没有完成之前，阻断其他写锁和读锁。

从对数据操作的粒度区分：表锁、行锁。

特点：偏向MyISAM存储引擎，开销小，加锁快，无死锁，锁定粒度大，发送锁冲突的概率最高，并发度最低。

手动增加表锁。

lock table 表名字1 read(write), 表名字2 read(write)，其它。

查看表上加过的锁，In_use字段显示1为已加锁，0位没有加锁。

show open tables;

18、数据库表锁。

（1）表加read锁。

session1

session2

获得mylock表的read锁。

lock table mylock read;

连接上mysql终端。

当前session1可以查询mylock表的记录。

select * from mylock;

session2可以查询mylock表的记录。

select * from mylock;

当前session1不能查询其他没有锁定的表。

select * from book;

session2可以查询、更新未锁定的表。

select * from book;

update book set card=1 where bookid=1;

当前session1不能插入、更新锁定的表。

update mylock set name='a2' where id=1;

insert into mylock(name) values('e');

session2插入、更新锁定的表时，会出现阻塞现象，会一直等待获得锁。

update mylock set name='a3' where id=1;

当前会话session1释放锁。

unlock tables;

session1释放锁后，session2获得锁，阻塞消失，完成更新或插入操作。

update mylock set name='a3' where id=1;

（2）表加write锁。

session1

session2

获得mylock表的write写锁定。

lock table mylock write;

待session1开启写锁后，session2再连接终端。

当前session1对锁定的mylock表，可以执行查询、更新、插入操作。

select * from mylock;

update mylock set name='a4' where id=1;

insert into mylock(name) values('f');

session2对已经被session1锁定的表作查询操作，查询被阻塞，需要等待session1释放锁。

select * from mylock where id=1;

session1释放锁。

unlock tables;

session1释放锁，session2获得锁，查询返回数据。

select * from mylock where id=1;

（3）表读锁和写锁对比。

MyISAM在执行select查询语句之前，会自动给涉及的所有表加锁，在执行增删改操作前，会自动给涉及的表加写锁。

mysql的表级锁模式：

表共享读锁Table Read Lock。

表独占写锁Table Write Lock。

其他session读锁

其他session写锁

1）对MyISAM表的读操作，加读锁，不会堵塞其他进程对同一表的读请求，会堵塞其他线程对同一表的写请求。

只有当读锁释放后，才会执行其它进程的写操作。

2）对MyISAM表的写操作，加写锁，会阻塞其它进程对同一表的读和写操作。

只有当写释放后，才会执行其它进程的读写操作。

总结：读锁会堵塞写，不会堵塞读。写锁会把读和写都堵塞。

（4）分析表锁定。

Table_locks_immediate

产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁值加1.

Table_locks_waited

出现表级锁定争用而发生等待的次数，不能立即获取锁的次数，每等待一次锁值加1，此值高则说明存在着较严重的表级锁争用情况。

MyISAM的读写锁是写优先，不适合作为写为主表的引擎。因为写锁后，其他线程不能做任何操作，大量的更新会使查询很难得到锁，从而造成永远阻塞。

19、数据库行锁。

（1）行锁特点。

偏向InnoDB存储引擎，开销大，加锁慢，会出现死锁，锁定粒度最小，发生锁冲突的概率最低，并发度也最高。

InnodbDB与MyISAM最大不同有两点：支持事务TRANSACTION。采用了行级锁。

（2）行锁支持事务。

事务Transaction及其ACID属性。

事务是由一组SQL语句组成的逻辑处理单元，事务具有以下4个属性，通常称为事务的ACID属性。

原子性Atomicity：事务是一个原子操作单元，对数据的修改，要么全都执行成功，要么全都不执行。

一致性Consistent：在事务开始和完成时，数据都必须保持一致状态。保持数据的完整性。

隔离性Isolation：通过数据库系统的隔离机制，保证事务在不受外部并发操作影响的独立环境执行。事务处理过程的中间状态对外部是不可见的。

持久性Durable：事务完成之后，对于数据的修改时持久性的。

（3）并发事务处理带来的问题。

1）更新丢失lost update。

当多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发送丢失更新问题，最后的更新覆盖了由其他事务所做的更新。

例如：两个程序员修改同一个java文件，每个程序员独立更新其副本，然后保存更改后的副本，这样就覆盖了原始文档。最后保存其更改副本的编辑人员覆盖前面一个程序员所做的更改。

如果再一个程序员完成并且提交事务之前，另一个程序员不能访问同一个文件，则可避免此问题。

2）脏读dirty reads。

一个事务正在对一条记录做修改，在这个事务完成并且提交之前，这条记录的数据就处于不一致的状态。此时，另一个事务也来读取同一条记录，

如果不加控制，第二个事务读取了这些脏数据，并且根据此数据做进一步的处理，就会产生未提交的数据依赖关系。

这种现象就是脏读。

事务A读取到了事务B已经修改但尚未提交的数据，还在这个数据基础上做了操作。此时，如果B事务回滚，A读取的数据无效，不符合一致性要求。

3）不可重复读non-repeatable reads。

一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，发现读取的数据已经发生了改变，或者某些记录已经被删除了，这种现象就叫做不可以重复度。

事务A读取到了事务B已经提交的修改数据，不符合隔离性。

4）幻读phantom reads。

一个事务按相同的查询条件重新读取以前检索过的数据，发现其他事务插入了满足其查询条件的新数据，这种现象就是幻读。

事务A读取到了事务B提交的新增加的数据，不符合隔离性。

幻读和脏读有点类似，脏读是事务B里面修改了数据，幻读是事务B里面新增了数据。

（4）事务的隔离级别。

脏读、不可重复读、幻读，都是数据库读一致性问题，由数据库提供一定的事务隔离机制来解决。

数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使事务在一定程度上串行化进行，显然与并发是矛盾的。

同时，不同的应用对读一致性和事务隔离程度的要求也是不同的，比如许多应用对不可重复读和幻读并不敏感，可能更关心数据并发访问的能力。

查看当前的数据库事务隔离级别。

（5）行锁演示。

session1

session2

操作同一条记录。

-- 1、关闭自动提交。当前session禁用自动提交事物，每个SQL语句或者语句块所在的事务都需要显示"commit"才能提交事务。

set autocommit=0;

-- 1、关闭自动提交。当前session禁用自动提交事物，每个SQL语句或者语句块所在的事务都需要显示"commit"才能提交事务。

set autocommit=0;

session1修改更新记录，暂时不提交记录，没有手动commit。

update test_innodb_lock set b='4001' where a=4;

session2修改被阻塞，只能等待。

update test_innodb_lock set b='4002' where a=4;

session1手动commit提交更新。

commit;

session2解除阻塞，更新记录完成。

update test_innodb_lock set b='4001' where a=4;

操作不同的记录互不影响。

session1更新id为4的记录。

update test_innodb_lock set b='4005' where a=4;

commit;

select * from test_innodb_lock;

session2更新id为9的记录。

update test_innodb_lock set b='9001' where a=9;

commit;

select * from test_innodb_lock;

（6）索引失效行锁变表锁。

session1

session2

update test_innodb_lock set b='4000' where a=4;

commit;

select * from test_innodb_lock;

update test_innodb_lock set b='9000' where a=9;

commit;

select * from test_innodb_lock;

查看表建立的索引。

show index from test_innodb_lock;

查看表建立的索引。

show index from test_innodb_lock;

正常更新。

update test_innodb_lock set a=41 where b='4000';

正常更新。

update test_innodb_lock set b='9001' where a=9;

commit;

已建立的索引失效，导致从行锁变成表锁。

-- 7、b字段建立了索引，b字段没有用单引号括起来'4000'，索引失效，从行锁变成了表锁。

update test_innodb_lock set a=41 where b=4000;

update test_innodb_lock set b='9002' where a=9;

commit;

select * from test_innodb_lock;

commit;

select * from test_innodb_lock;

（7）间隙锁危害。

session1

session2

更新某个键值范围内的记录，锁住了这个范围内的记录，包括存在和不存在的记录。

update test_innodb_lock set b='0629' where a>1 and a<6;

产生阻塞，暂时不能插入，只能等待。

insert into test_innodb_lock values(2, '2000');

commit;

接触阻塞，执行插入记录完成。

insert into test_innodb_lock values(2, '2000');

当用范围条件而不是相等条件查询数据，并请求共享或排他锁时，InnoDB会给符合范围条件的已有数据记录的索引项加锁。

对于键值在条件范围内，但是不存在的记录（符合查询范围条件，但是记录不存在，ID序号不连续。），叫做间隙GAP。

InnoDB也会对这个间隙加锁，这种锁机制就是所谓的间隙锁。

查询执行过程中通过范围查找，将锁定整个范围内所有的索引键值，即使这个键值不存在。

间隙锁锁定一个范围键值之后，即使某些不存在的键值也会被锁定，造成在锁定的时候无法插入锁定键值范围内的任何数据。

在某些场景下可能会对性能造成很大的危害。

（8）使用for update锁定某一行记录。

session1

session2

select * from test_innodb_lock;

begin;

select * from test_innodb_lock where a=8 for update;

update test_innodb_lock set b='8001' where a=8;

commit;

select * from test_innodb_lock;

（9）行锁总结。

1）行锁总结。

InnoDB存储存储引擎由于实现了行级锁定，在锁定机制的实现方面的性能损耗可能比表级锁高。

在并发量较高时，InnoDB的整体性能比MyISAM高，处理并发的能力强。

如果InnoDB使用不当，可能造成行锁变成表锁。

2）行锁分析。

Innodb_row_lock_current_waits

当前正在等待锁定的数量。

Innodb_row_lock_time

等待总时长。从系统启动到现在锁定总时间长度。

Innodb_row_lock_time_avg

等待平均时长。每次等待所花平均时间。

Innodb_row_lock_time_max

从系统启动到现在等待最长的一次所花的时间。

Innodb_row_lock_waits

等待总次数。系统启动后到现在总共等待的次数。

当Innodb_row_lock_waits等待次数很高，每次等待时长很大时，需要分析系统情况，根据分析结果进行优化。

3）行锁优化。

行锁分析。

尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁。

合理设计索引，尽量缩小锁的范围。

尽可能较少检索条件，避免间隙锁。

尽量控制事务大小，减少锁定资源量和时间长度。

尽可能低级别事务隔离。

20、页锁。

开销和加锁时间介于表锁和行锁之间，会出现死锁。

锁定粒度介于表锁和行锁之间，处理高并发能力一般。

21、mysql主从复制。

（1）复制的基本原理。

slave会从master读取binlog二进制日志来进行数据同步。

（2）mysql复制过程的三个步骤。

1）master将改变记录到二进制日志binary log，这个过程叫做二进制日志事件binary log events。

2）slave将master的binary log events拷贝到他的中继日志relay log。

3）slave重做中继日志中的事件，将改变应用到自己的数据库中。

mysql复制是异步串行化的。复制可能有延迟。

（3）复制的基本原则。

每个slave只有一个master。

每个slave只能有一个唯一的服务器ID。

每个master可以有多个salve。

（4）配置mysql一主一从复制。

master主机服务器：window系统，mysql版本：mysql8.0.12。

slave从机服务器：centos7，Linux系统，mysql版本：8.0.27。

在以上环境试验成功。

1）mysql版本需要一致，以后台服务的方式运行。

2）在同一个网段，相互可以ping的通。

3）以小写的方式，在[mysqld]节点下配置。

4）修改master主机上的my.ini文件。

注意：以下配置信息需要配置在[mysqld]节点下。

5）修改slave从机上的my.cnf文件。

注意：以下配置信息需要配置在[mysqld]节点下。

6）主机服务器、从机服务器关闭防火墙。

关闭主机服务器的防火墙，关闭从机服务器的防火墙，一定要关闭，就算IP地址可以ping同，也要关闭，否则会出现连接不上的现象。

7）在master主机上建立账户，并且授权给slave从机。

在主机服务器mysql上操作。

mysql8.x语法：

CREATE USER '用户名'@'slave从机数据库服务器IP地址' IDENTIFIED WITH mysql_native_password BY '用户密码';

GRANT REPLICATION SLAVE ON *.* TO '用户名'@'slave从机数据库服务器IP地址' WITH GRANT OPTION;

flush privileges;

创建用户名xinxin，设置密码为123456，充许192.168.3.101访问。

#查看master主机的状态。

show master status;

查看master主机的状态显示解读：

File：mysqlbin.00000x。

二进制日志文件。

Position。

从日志文件的那个位置开始复制数据。

Binlog_Do_DB。

需要复制的数据库，为空代表所有数据库，包括新建时产生的。

Binlog_Ignore_DB。

忽略的数据库，不复制的数据库。

8）在salve从机上配置需要复制的主机。

在从机服务器mysql上操作。

CHANGE MASTER TO MASTER_HOST='master主机服务器IP地址',

MASTER_USER='主机数据库用户名',

MASTER_PASSWORD='主机数据库用户密码',

MASTER_LOG_FILE='mysqlbin.具体数字',MASTER_LOG_POS=具体Position位置值;

9）启动slave从机服务器的复制功能。

在从机服务器mysql上操作。

显示如图所示才可以数据同步，以下为正常显示。

字段Slave_IO_State显示为Waiting for source to send event，等待master主机发送事件。

字段Slave_IO_Running显示为Yes。

字段Slave_SQL_Running显示为Yes。

注意：如果出现不正常显示，数据同步不了，需要查看这Last_IO_Error、Last_SQL_Error两个字段，错误信息会显示在这两个字段里面，根据这两个字段就可以排查原因了。

显示不正常，并且这两个字段显示为空，需要等待一段时间，slave从会重试60次去连接同步数据，重试完之后，还是不可以才会有错误显示。

不需要主从不同时，在从机上停止复制功能。

10）测试主从复制。

在主机服务器上操作。

（5）配置mysql主从复制常见问题。

如果出现不正常，数据同步不了，需要查看这Last_IO_Error、Last_SQL_Error两个字段，错误信息会显示在这两个字段里面，根据这两个字段就可以排查原因了。

#查看slave从机的状态。

show slave status;

1）错误1：

Last_IO_Error: Fatal error: The slave I/O thread stops because master and slave have equal MySQL server ids; these ids must be different for replication to work (or the –replicate-same-server-id option must be used on slave but this does not always make sense; please check the manual before using it).

没有配置server_id，或者server_id配置错误。

server_id配置在[mysqld]节点下，master主机和slave从机都需要配置，配置成不一样的唯一标识数字。

-- 查看mysql服务ID，server_id命令。

show variables like 'server_id';

在my.ini或my.cnf配置文件中配置。

[mysqld]

# master主服务器唯一ID。

server_id = 1

[mysqld]

# salve从服务器唯一ID。

server_id=2

2）错误2：

error connecting to master '[email protected]:3306' - retry-time: 60 retries: 2 message: Authentication plugin 'caching_sha2_password' reported error: Authentication requires secure connection.

master主机创建用户时使用的caching_sha2_password加密方式，slave从机使用mysql_native_password加密方式连接不上，出现权限认证不通过。

删掉master主机的用户，重新创建用户，创建用户指定使用mysql_native_password加密方式，WITH mysql_native_password。

use mysql;

select * from user;

delete from user where host='192.168.3.101' and user='xinxin';

CREATE USER 'xinxin'@'192.168.3.101' IDENTIFIED WITH mysql_native_password BY '123456';

GRANT REPLICATION SLAVE ON *.* TO 'xinxin'@'192.168.3.101' WITH GRANT OPTION;

flush privileges;

3）错误3：

error connecting to master '[email protected]:3306' - retry-time: 60 retries: 1 message: Can't connect to MySQL server on '192.168.3.3:3306' (110)

没有关闭防火墙，连接不上mysql，关闭master主机、slave从机防火墙。

（6）my.ini和my.cnf配置文件。

window上的my.ini配置文件内容。

Linux上的my.cnf配置文件内容。