本课程不会从0开始，只列出一些可能会在面试环节出现的知识点
1.事务相关
1.1 并发事务问题
| 问题 | 描述 |
|---|---|
| 脏读 | 一个事务读到另一个事务还没提交的数据 |
| 不可重复读 | 一个事务多次读取同一条数据，但是读取的结果不一样（被别的事务修改了），称为不可重复读 |
| 幻读 | 一个事务根据条件查询一条数据的时候，没有对应的行。但是插入的时候又发现该行数据已存在，好像出现了幻影，这就是幻读。 |
1.2 事务隔离级别，针对并发事务问题的解决方案
事务的隔离级别，以及每个级别能解决的问题
| 隔离级别 | 脏读 | 不可重复读 | 幻读 |
|---|---|---|---|
| Read Uncommitted（读未提交） | × | × | × |
| Read Committed（读已提交） | √ | × | × |
| Repeatable Read（默认，可重复读） | √ | √ | × |
| Serializable（串行话） | √ | √ | √ |
- Read Uncommitted（读未提交）
允许事务读取别的事务未提交的数据，并发能力最强，但是安全性最差。脏读、幻读、不可重复读问题都可能会出现。 - Read Committed（读已提交）
不允许读取别的事务未提交的数据，该级别不会出现脏读的问题，但是可能会出现幻读、不可重复读的问题 - Repeatable Read（MySQL默认，可重复读）
MySQL默认的隔离级别，该级别不会出现脏读和不可重复读的问题，查询的某一条数据始终是一致的，哪怕是别的事务修改了该数据。但是该级别无法解决幻读的问题。 - Serializable（串行话）
隔离级别最高，解决了所有的并发事务问题，但是性能是最差的。
事务的隔离级别越高，数据越安全，性能越低
2.存储引擎
2.1 InnoDB
介绍
InnoDB是一种兼顾高可靠性和高性能的通用存储引擎，在MySQL 5.5版本之后InnoDB就是默认的存储引擎。
特点
- 支持事务
- 行级锁，提供并发性能
- 支持外键约束，保证数据的完整性和正确性
2.2 MyISAM
介绍
MyISAM是早期MySQL默认的存储引擎
特点
- 不支持事务，不支持外键约束
- 支持表锁，不支持行锁
- 访问速度快
2.3 Memory
介绍
Memory引擎的表数据是存储在内存中的，由于受到硬件问题、或断电问题的影响，只能将这些表作为临时表或缓存使用。
特点
- 内存存放，访问速度快
- 支持hash索引（默认）
2.4 存储引擎的选择
在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合。
- InnoDB
Mysql的默认存储引擎，支持事务、外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询之外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。 - MyISAM
如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。 - MEMORY
将所有数据保存在内存中，访问速度快，通常用于临时表及缓存。MEMORY的缺陷就是对表的大小有限制，太大的表无法缓存在内存中，而且无法保障数据的安全性。一般用于当作缓存使用，但是现在大多数缓存都用Redis，所以该引擎基本上用不到。
3. 索引
3.1 概述
索引是一种帮助MySQL高效获取数据的数据结构（有序）。在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。
3.2 使用索引的优缺点
优点
- 提高数据检索效率，降低数据库IO成本
- 通过索引列对数据进行排序，降低数据排序成本，降低CPU消耗
缺点
- 索引列也是要占用空间的
- 索引提高了查询的效率，但是却降低了增删改的效率
3.3 索引结构
索引是在MySQL的存储引擎层中实现的，所以不同的存储引擎支持的索引结构也不同。目前MySQL主要有以下几种索引。
- B+Tree索引
最常见的索引类型，大部分索引都支持B+Tree索引。 - Hash索引
底层数据结构使用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询 - R-Tree（空间索引）
MylSAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少。 - full-text（全文索引）
是一种通过建立倒排索引，快速匹配文档的的方式。类似与ElasticSearch。
| 索引 | InnoDB引擎 | MyISAM引擎 | Memory引擎 |
|---|---|---|---|
| B+Tree索引 | 支持 | 支持 | 支持 |
| Hash索引 | 不支持 | 不支持 | 支持 |
| R-Tree索引 | 不支持 | 支持 | 不支持 |
| Full-Text索引 | 5.6版本后支持 | 支持 | 不支持 |
我们平常所说的索引，如果没有特别说明，一般都是指的B+Tree索引。
3.3.1 B-Tree（多路平衡查找树）
以一颗最大度数(max-degree)为5的B-Tree为例(每个节点最多存储4个key，5个指针)，度数指的是一个节点下的子节点个数。指针超过5的话中间的元素向上分裂。
数据结构动态演示地址：https://www.cs.usfca.edu/~galles/visualization/Algorithms.html
3.3.2 B+Tree
以一颗最大度数为4的b+tree为例。
B+Tree特点（与BTree的区别）：
- 所有的元素都会出现在叶子节点
- 叶子节点会形成单向链表，非叶子节点只起到索引数据的作用
MySQL中的B+Tree
MySQL索引数据结构对经典的B+Tree进行了优化。在原B+Tree的基础上，增加一个指向相邻叶子节点的链表指针，就形成了带有顺序指针的B+Tree，提高区间访问的性能。
3.3.3 灵魂拷问
为什么InnoDB存储引擎选择使用B+Tree索引结构？
- 二叉树最大的缺点就是，顺序插入的换会形成单向链表，层级比较深。红黑树本质上也是二叉树。
- 对于B-Tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少（一页最多只能存16k），指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低；而B+Trees数据只存放在叶子节点，这样搜索数据只查找叶子节点就行，搜索效率更加稳定，而且由于非叶子节点不存放数据，所以同样的数据量B+Tree的树层级没有B-Tree层级那么深。
- hash索引不支持范围查询及排序操作，而B+Tree支持。
3.4 索引分类
| 分类 | 含义 | 特点 | 关键字 |
|---|---|---|---|
| 主键索引 | 针对表中主键创建的索引 | 默认自动创建，只能有一个 | PRIMARY |
| 唯一索引 | 用于避免同一张表中行级数据出现重复项 | 可以有多个 | UNIQUE |
| 常规索引 | 快速定位特定数据 | 可以有多个 | |
| 全文索引 | 全文索引查找的是文本中的关键字，而不是比较索引中的值 | 可以有多个 | FULLTEXT |
在InnoDB存储引擎中，根据索引的存储形式，又可以分为以下两种:
| 分类 | 含义 | 特点 |
|---|---|---|
| 聚集索引 | 数据与索引存储在一起，索引结构的叶子节点保存了行数据 | 有且只有一个，一般是主键Id |
| 非聚集索引（或二级索引） | 数据与索引分开存储，索引结构的叶子节点关联的是对应的主键 | 可以存在多个 |
聚集索引选取规则
- 如果存在主键，主键就是聚集索引
- 如果不存在主键，将使用第一个唯一索引作为聚集索引
- 如果不存在主键，或没有合适的唯一索引，InnoDB则会自动生成一个rowid作为隐藏的聚集索引。
3.5 SQL性能分析
3.5.1 执行频率
MySQL客户端连接成功后，通过show [session|global] status 命令可以提供服务器状态信息(从上次启动至今)。通过如下指令，可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次:
show global status like 'Com_______';
3.5.2 慢查询日志
慢查询日志记录了所有执行时间超过指定参数（long_query_time，单位：秒，默认10秒）的所有SQL语句的日志。
##查看是否开启慢查询日志
show variables like 'slow_query_log'
MySQL的慢查询日志默认没有开启，需要在MySQL的配置文件(/etc/my.cnf）中配置如下信息:
##开启慢查询日志
slow_query_log=1
##设置慢查询日志时间为2秒,默认10秒，单位：秒
long_query_time=2
慢查询日志存放在/var/lib/mysql文件夹下，名称为xx-slow.log
3.5.3 profile详情
show profiles能够在做SQL优化时帮助我们了解时间都耗费到哪里去了。通过have_profiling参数，能够看到当前MySQL是否支持：
select @@have_profiling;
默认profiling是关闭的，可以通过set语句在session/global级别开启profiling：
##查询profiling是否开启，1开启 0未开启
SELECT @@profiling;
#开启profiling，可以在set后加上session或global指定级别
set profiling=1;
可以执行慢查询日志记录的sql，通过show profiles命令来查看执行时间和query_id，再通过query_id具体分析时间都花费在哪里了：
##假如这是一条慢查询sql
SELECT * FROM pms_sku_info WHERE sku_name LIKE '%Apple iPhone 13 (A2634) 星光色 128%';
##查看执行时间和Query_Id
show profiles;
##根据query_id具体分析时间花费在哪里了
show profile for query query_id;
##根据query_id查看sql的cpu使用情况
show profile cpu for query query_id;
注意：
Sending data 状态标识MySQL线程开始访问数据行并把结果返回给客户端。在Sending data下，MySQL线程往往需要做大量的磁盘读取操作，所以往往该状态是查询中耗时最长的状态。
查询的结果中出现以下状态，就需要注意了，尽量去优化SQL：
- converting HEAP to MyISAM：查询结果太大，内存不够用了，需要借用磁盘空间。
- Creating tmp table：创建临时表
- Copying to tmp table on disk：把内存中临时表文件复制到磁盘，危急！！
- locked：出现了锁
3.5.4 explain执行计划
explain或者desc命令获取MySQL如何执行SELECT语句的信息，包括在SELECT语句执行过程中表如何连接和连接的顺序。
语法
explain 可以加在任意的select前面。例如：
EXPLAIN SELECT * FROM pms_sku_info WHERE sku_name LIKE ‘%Apple iPhone 13 (A2634) 星光色 128%’
各个字段含义：
1. id
select查询的序列号，表示查询中执行select子句或者是操作表的顺序(id相同，执行顺序从上到下；id不同，值越大，越先执行)。
2. select_type
表示select的类型，常见的值有以下几个：
| select_type | 含义 |
|---|---|
| SIMPLE | 简单的select查询，查询中不包含子查询或UNION |
| PRIMARY | 查询中若包含任何复杂的子查询，最外层查询标记为该标识 |
| SUBQUERY | 在select或where列表中包含了子查询 |
| DERIVED | 在from列表中包含的子查询，被标记为 DERIVED（衍生），MySQL会递归执行这些子查询，把结果放到临时表中 |
| UNION | 若第二个select出现在union之后，则被标记为UNION；若UNION包含在FROM子句的子查询中，外层select将被标记为：DERIVED |
| UNION RESULT | 从UNION表获取结果的SELECT |
上表中，select_type从上到下，效率越来越低。
3. type[重点]
表示连接类型，是较为重要的一个指标，可取值为：
| type | 含义 |
|---|---|
| NULL | MySQL不访问任何表，索引，直接返回结果 |
| system | 表只有一行记录（等于系统表），这是const类型的特例，一般不会出现 |
| const | 表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据所以很快。如将主键索引置于where语句中，MySQL就能将该查询转换为一个常量。 |
| eq_ref | 类似于ref，区别在于使用的是唯一索引，使用主键的多表关联查询，关联查询出的记录只有一条。常见于主键或唯一索引扫描 |
| ref | 非唯一索引扫描，返回匹配某个单独值得所有行。本质上也是一种索引访问，返回所有匹配某个单独值得所有行（多个） |
| range | 只检索给定返回得行，使用一个索引来选择行。where之后出现between,<,>,in等范围搜索 |
| index | index与all得区别为index类型只是遍历了索引树，通常比all快，all是遍历数据文件 |
| all | 将遍历全表以找到匹配得行 |
结果从好到坏以此是：
NULL、system 、const 、eq_ref 、ref 、range 、index 、ALL
一般来说，优化语句得时候能达到ref或者range级别就可以了。
4. possible_key[重点]
显示可能应用到这张表的索引，一个或多个
5. key[重点]
实际用到的索引，如果为NULL，则没有使用索引。
6.key_len[重点]
表示索引使用的字节数，该值为索引字段最大可能长度。在不损失精确性的情况下，长度越短越好。
7.rows
扫描的行数，是一个预估值，不一定总是准确的
8.filtered[重点]
表示返回行数占读取行数的百分比，越大越好
9.extra[重点]
前面几个字段中没出现得，都会出现在extra中。
| extra | 含义 |
|---|---|
| Using filesort | 说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。mysql中无法利用索引完成的排序操作成为“文件排序”。出现这个就尽可能的优化。 |
| Using temporary | 使用了临时表保存了中间结果，mysql在对查询结果排序时使用临时表。常见于order by和group by。出现这个就赶紧优化。 |
| Using index | 表示相应的select操作中使用了覆盖索引，避免访问了表的数据行，效率很好； |
3.6 索引的使用原则
3.6.1 最左前缀法则
如果是多列索引，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不跳过中间的列。如果跳过某一列，索引将部分失效（后面的字段索引失效）。如果所有的列都用到了，顺序乱了也没问题的，mysql会自动优化查询再执行。
假如有个多列索引（对table表中的a，b，c三个字段建立索引）idx_table_a_b_c，那么索引的生效情况如下表所示：
| 查询条件及顺序 | 索引是否生效 | 原因 |
|---|---|---|
| where a = ? and b = ? and c = ? | 索引生效 | 符合最左前缀法则 |
| where a = ? and b = ? | 索引生效 | 符合最左前缀法则 |
| where a = ? | 索引生效 | 符合最左前缀法则 |
| where c = ? and b = ? and a = ? | 索引生效 | 最左前缀法则要求所有的列都存在，则与位置无关，mysql自动优化 |
| where a = ? and c = ? | a走索引，c不走索引 | 最左前列开始，但是跳过了b列 |
| where b = ? and c = ? | 索引不生效 | 不符合最左前缀法则 |
| where b = ? | 索引不生效 | 不符合最左前缀法则 |
| where c = ? | 索引不生效 | 不符合最左前缀法则 |
3.6.2 范围查询
联合索引（多列索引）中，出现范围查询（>,<）,范围查询右侧的列索引会失效。如果业务允许，尽量使用大于等于（>=）或小于等于（<=）,这样就会走索引。
上图可以看出最后两天语句key_len相同，说明最后一个查询的address字段没有走索引，因为他出现在了范围查询（status>‘1’）的后面。
3.6.3 其他索引失效问题
3.6.3.1 索引运算
- 不要再索引列上进行运算操作，否则索引将失效。
#phone建立了索引，在索引列上运算，索引会失效
explain select * from tb_user where substring(phone,10,2)= '15;
3.6.3.2 字符串不加引号
- 字符串不加单引号，索引将失效。
#这里status是字符串类型，没有加引号，索引会失效。
explain select * from tb_user where profession= '软件工程' and age = 31 and status = O;
#phone是字符串类型，没加引号，索引会失效。
explain select *from tb_user where phone =17799990015;
3.6.3.3 模糊查询
- 如果仅仅是尾部模糊匹配，索引不会失效。如果是头部模糊匹配，索引失效。
#索引不失效
explain select * from tb_user where profession like '软件%';
#索引失效
explain select * from tb_user where profession like '%工程';
#索引失效
explain select * from tb_user where profession like '%工%';
3.6.3.4 or 连接的查询条件
- or连接的查询条件，只有or两侧的列都有索引的情况下，索引才会起作用。
#phone有索引，age没有索引，那么phone的索引不会起作用。如果对age字段建立索引的话，那么phone和age的索引都会起作用
explain select *from tb_user where phone = '17799990017' or age = 23;
3.6.3.5 数据分布影响
- 如果MySQL评估使用索引比全盘扫描慢，那么就不会使用索引。
当根据查询条件查询出来得数据占表中总数居的一大部分或者全部的时候，MySQL会评估为不使用所以查的更快，就会放弃使用索引。
#address有索引，如果表中address全是北京或者大多数都是北京，那么索引就会失效
explain select *from tb_user where address = '北京';
is null 和 is not null 注意事项
需要注意的一点，is null 和 is not null 也受数据分布影响。假如 where address is null，如果address大多或者全部都是null，那么即使address有索引也会失效。反之，is not null 也是一样的。
3.6.4 SQL提示
SQL提示，是优化数据库的一个重要手段，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。
-
use index
建议数据库使用哪个索引（非强制性的，数据库也有可能不听建议）explain select * from tb_user use index(idx_user_pro) where profession= '软件工程'; -
ignore index
告诉数据库忽略哪个索引explain select * from tb_user ignore index(idx_user_pro) where profession= '软件工程'; -
force index
告诉数据库强制使用哪个索引explain select * from tb_user force index(idx_user_pro) where profession= '软件工程';
3.6.5 覆盖索引
尽量使用覆盖索引，覆盖索引就是只查询建立索引的字段，减少使用select * 。
explain返回的extra部分数据的解释
-
using index condition
查找使用了索引，但是需要回表查询数据。比如select a,b,c from table where a = ？，其中a和b有索引，c没有索引，那么c就需要回表查询。回表查询：回表指的是先从非聚集索引查询数据（非聚集索引只存储主键），根据查询到的主键再去聚集索引中加载这一行的数据。忘记聚集索引和非聚集索引存储结构的可以回到目录3.4 索引分类复习。
-
using where; using index
查找使用了索引，并且需要的数据列在索引中都能找到，不需要和回表查询。比如select a,b,c from table where a = ？其中a,b,c有联合索引。
所以using where; using index的查询效率要高于using index condition。
分析一下下面的图，会更有助于理解
上图分析
- 第一条查询语句
第一条语句是根据Id查询的，所以会直接查询聚集索引。由于聚集索引存储的是这一行的所有数据，所以返回的是select * 也没问题。 - 第二条查询语句
第二条语句是根据name查询的，而name建立了所以。所以会去辅助索引（非聚集索引）查找name字段，而name字段在叶子节点上存在数据，其节点关联的数据是主键，所以select的后面的id和name都可以在非聚集索引上查询到，扫描一次索引就可以了。 - 第三条查询语句
在第三条语句中，select后面的gender字段没有建立索引。所以数据库会先根据where后面的name条件去非聚集索引查询，可以查询到id和name，但是却查不到gender，所以数据库会通过id再去聚集索引查询数据，返回该id对应的一行数据。数据库扫描了两次索引，也就是回表查询。
3.6.6 前缀索引
当字段类型为字符串(varchar, text等)时，有时候需要索引很长的字符串，这会让索引变得很大，查询时，浪费大量的磁盘O，影响查询效率。此时可以只将字符串的一部分前缀，建立索引，这样可以大大节约索引空间，从而提高索引效率
CREATE INDEX idx_skuinfo_skuname_10 ON pms_sku_info(sku_name(10))
前缀长度计算
可以根据索引的选择性来决定，而选择性是指不重复的索引值〈基数）和数据表的记录总数的比值，索引选择性越高则查询效率越高，
唯一索引的选择性是1，这是最好的索引选择性，性能也是最好的。
select count(distinct email) / count(*) from tb_user ;
##截取的长度，与总数的比例越接近1越好
select count(distinct substring(email,1,5)) / count(*) from tb_user ;
3.6.7 单列索引和联合索引
尽量使用复合索引，少使用单列索引。
举例：
select a,b,c from table where a = ？
- 如果a，b，c都只创建了单列索引，那么mysql只会选择最优的一个索引使用，而不会把三个字段的索引都用上。
- 如果a，b，c创建了联合索引，那么他们三个字段都会用到索引去查询。
3.6.8 索引设计原则
1.针对于数据量较大，且查询比较频繁的表建立索引。
2.针对于常作为查询条件（where)、排序（order by)、分组(group by)操作的字段建立索引。
3．尽量选择区分度高的列作为索引，尽量建立唯一索引，区分度越高，使用索引的效率越高。
4．如果是字符串类型的字段，字段的长度较长，可以针对于字段的特点，建立前缀索引。
5、尽量使用联合索引，减少单列索引，查询时，联合索引很多时候可以覆盖索引，节省存储空间，避免回表，提高查询效率。
6．要控制索引的数量，索引并不是多多益善，索引越多，维护索引结构的代价也就越大，会影响增删改的效率。
7． 如果索引列不能存储NULL值，请在创建表时使用NOT NULL约束它。当优化器知道每列是否包含NULL值时，它可以更好地确定哪个
索引最有效地用于查询。
4. MySQL优化
4.1 insert 优化
-
批量插入
尽量将多条插入语句进行批量插入，但是一条语句不建议超过1000条insert into tb_test values(1,'Tom').(2,'Cat').(3,'Jerry'); -
手动提交事务
多条插入语句的话，关闭自动提交事务，开启手动提交。避免频繁的开启关闭事务。数据少的话就无所谓了。start transaction; insert into tb_test values(1,'Tom'),(2,'Cat')(3,'Jerry'); insert into tb_test values(4,'Tom'),(5,'Cat'),(6,'Jerry'); insert into tb_test values(7,'Tom'),(8,'Cat').(9,'Jerry'); commit; -
主键顺序插入
主键乱序插入:8 1 9 21882 41589 5 7 3 主键顺序插入:1 2 3 4 57 8 915218889 -
大批量插入数据，使用load指令
如果一次性需要插入大批量数据，使用insert语句插入性能较低，此时可以使用MySQL数据库提供的load指令进行插入。操作如下:#客户端连接服务端时，加上参数--local-infile mysql --local-infile -u root -p #设置全局参数local_infile为1，开启从本地加载文件导入数据的开关 set global local_infile = 1; #执行load指令将准备好的数据，加载到表结构中，下面语句表示每个字段用','分隔，每一行之间用'\n'分隔 load data local infile '/root/sql1.log into table 'tb_user fields terminated by ',' lines terminated by '\n';
4.2 主键优化
在InnoDB存储引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表(index organized table IOT)。
主键设计原则
- 满足业务需求的情况下，尽量降低主键的长度。
因为非聚集索引叶子节点下关联的主键，如果主键太长并且非聚集索引有很多的话，会浪费大量的磁盘空间，搜索也会占用大量磁盘IO。
- 插入数据时，尽量选择顺序插入，选择使用AUTO_INCREMENT自增主键。
- 尽量不要使用UUID做主键或者是其他自然主键，如身份证号。
- 业务操作时，避免对主键的修改。
4.3 order by 优化
- Using filesort :通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫 FileSort排序。
- Using index:通过有序索引顺序扫描直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率高。
优化原则
-
根据排序字段建立合适的索引，多字段排序时，也遵循最左前缀法则。
-
尽量使用覆盖索引。
-
多字段排序,一个升序一个降序，此时需要注意联合索引在创建时的规则(ASC/DESC)。
如果联合索引创建时也是一个升序一个降序，那么会是索引排序。否则，就是文件排序。 -
如果不可避免的出现filesort，大数据量排序时，可以适当增大排序缓冲区大小sort_buffer_size(默认256k)。
4.4 group by 优化
- group by也遵循最左前缀法则
- 如果之分组不排序的话可以使用order by null，来降低排序的消耗
4.5 limit 优化
对于limit来说，大数据量的情况下，查询的数据越靠后耗时就越久。一千万的数据，查询最后一页的时候耗时可能会是数十秒。
一个常见又非常头疼的问题就是limit 2000000,10，此时需要MySQL排序前2000010记录，仅仅返回2000000 - 2000010的记录，其他记录丢弃，查询排序的代价非常大。
优化思路
一般分页查询时，通过创建覆盖索引能够比较好地提高性能，可以通过覆盖索引加子查询形式进行优化。
explain select * from tb_sku t,(select id from tb_sku order by id limit 2000000,10) a where t.id = a.id;
4.6 count 优化
4.7 update 优化
InnoDB的行锁是针对索引加的锁，不是针对记录加的锁,并且该索引不能失效，否则会从行锁升级为表锁。因此在修改数据的时候要根据索引字段去修改，而且索引不能失效。
#id是主键有索引，因此该语句只会锁住一行
update student set no='2000100100' where id = 1;
#如果name没有索引，那么行锁会升级为表锁，有可能导致死锁。
update student set no = '2000100105' where name='韦一笑';
4.8 子查询优化
子查询尽量使用连接替代。
1d8ba30e114a138c92ac5df138cdb6.png?x-oss-process=image/watermark,type_ZHJvaWRzYW5zZmFsbGJhY2s,shadow_50,text_Q1NETiBAamF2YeS_rueCvOS5i-i3rw==,size_20,color_FFFFFF,t_70,g_se,x_16#pic_center)
5. 常见面试题总结
5.1. 索引结构
1.1 为什么InnoDB存储引擎选择使用B+Tree索引结构？而不是二叉树、B-Tree以及hash等。
- 二叉树最大的缺点就是，顺序插入的换会形成单向链表，层级比较深。红黑树本质上也是二叉树。
- 对于B-Tree，无论是叶子节点还是非叶子节点，都会保存数据，这样导致一页中存储的键值减少（一页最多只能存16k），指针跟着减少，要同样保存大量数据，只能增加树的高度，导致性能降低；而B+Trees数据只存放在叶子节点，这样搜索数据只查找叶子节点就行，搜索效率更加稳定，而且由于非叶子节点不存放数据，所以同样的数据量B+Tree的树层级没有B-Tree层级那么深。
- hash索引不支持范围查询及排序操作，而B+Tree支持。
5.2. 索引相关
2.1 一张 tb_user 表，有四个字段(id, username, password, status),其中id是主键。由于数据量大，需要对以下SQL语句进行优化,该如何进行才是最优方案:
select id,username,password from tb_user where username = 'itcast';
答案：根据username和password建立联合索引，并且username在前面。
分析：建立联合索引后，where后面的查询条件username就会走非聚集索引。而且username和password数据都会存储在叶子节点上，叶子节点关联的是主键Id。所以扫描一次非聚集索引就可以把id，username和password都能查出来。
扫一扫
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
