189 8069 5689
本篇内容介绍了“高频MySQL的题目有哪些”的有关知识，在实际案例的操作过程中，不少人都会遇到这样的困境，接下来就让小编带领大家学习一下如何处理这些情况吧！希望大家仔细阅读，能够学有所成！
让客户满意是我们工作的目标，不断超越客户的期望值来自于我们对这个行业的热爱。我们立志把好的技术通过有效、简单的方式提供给客户，将通过不懈努力成为客户在信息化领域值得信任、有价值的长期合作伙伴，公司提供的服务项目有：国际域名空间、雅安服务器托管、营销软件、网站建设、山丹网站维护、网站推广。
客户端先通过连接器连接到 MySQL 服务器。
连接器权限验证通过之后，先查询是否有查询缓存，如果有缓存（之前执行过此语句）则直接返回缓存数据，如果没有缓存则进入分析器。
分析器会对查询语句进行语法分析和词法分析，判断 SQL 语法是否正确，如果查询语法错误会直接返回给客户端错误信息，如果语法正确则进入优化器。
优化器是对查询语句进行优化处理，例如一个表里面有多个索引，优化器会判别哪个索引性能更好。
优化器执行完就进入执行器，执行器就开始执行语句进行查询比对了，直到查询到满足条件的所有数据，然后进行返回。
此错误是执行到分析器阶段报出的，因为 MySQL 会在分析器阶段检查 SQL 语句的正确性。
MySQL 查询缓存功能是在连接器之后发生的，它的优点是效率高，如果已经有缓存则会直接返回结果。 查询缓存的缺点是失效太频繁导致缓存命中率比较低，任何更新表操作都会清空查询缓存，因此导致查询缓存非常容易失效。
MySQL 查询缓存默认是开启的，配置 querycachetype 参数为 DEMAND（按需使用）关闭查询缓存，MySQL 8.0 之后直接删除了查询缓存的功能。
MySQL 的常用引擎有 InnoDB、MyISAM、Memory 等，从 MySQL 5.5.5 版本开始 InnoDB 就成为了默认的存储引擎。
可以针对不同的表设置不同的引擎。在 create table 语句中使用 engine=引擎名（比如Memory）来设置此表的存储引擎。完整代码如下：
create table student( id int primary key auto_increment, username varchar(120), age int) ENGINE=Memory
InnoDB 和 MyISAM 最大的区别是 InnoDB 支持事务，而 MyISAM 不支持事务，它们主要区别如下：
InnoDB 支持崩溃后安全恢复，MyISAM 不支持崩溃后安全恢复；
InnoDB 支持行级锁，MyISAM 不支持行级锁，只支持到表锁；
InnoDB 支持外键，MyISAM 不支持外键；
MyISAM 性能比 InnoDB 高；
MyISAM 支持 FULLTEXT 类型的全文索引，InnoDB 不支持 FULLTEXT 类型的全文索引，但是 InnoDB 可以使用 sphinx 插件支持全文索引，并且效果更好；
InnoDB 主键查询性能高于 MyISAM。
1）插入缓冲(insert buffer)：对于非聚集索引的插入和更新，不是每一次直接插入索引页中，而是首先判断插入的非聚集索引页是否在缓冲池中，如果在，则直接插入，否则，先放入一个插入缓冲区中。好似欺骗数据库这个非聚集的索引已经插入到叶子节点了，然后再以一定的频率执行插入缓冲和非聚集索引页子节点的合并操作，这时通常能将多个插入合并到一个操作中，这就大大提高了对非聚集索引执行插入和修改操作的性能。
2）两次写(double write)：两次写给 InnoDB 带来的是可靠性，主要用来解决部分写失败(partial page write)。doublewrite 有两部分组成，一部分是内存中的 doublewrite buffer ，大小为 2M，另外一部分就是物理磁盘上的共享表空间中连续的 128 个页，即两个区，大小同样为 2M。当缓冲池的作业刷新时，并不直接写硬盘，而是通过 memcpy 函数将脏页先拷贝到内存中的 doublewrite buffer，之后通过 doublewrite buffer 再分两次写，每次写入 1M 到共享表空间的物理磁盘上，然后马上调用 fsync 函数，同步磁盘。如下图所示
3）自适应哈希索引(adaptive hash index)：由于 InnoDB 不支持 hash 索引，但在某些情况下 hash 索引的效率很高，于是出现了 adaptive hash index 功能， InnoDB 存储引擎会监控对表上索引的查找，如果观察到建立 hash 索引可以提高性能的时候，则自动建立 hash 索引。
如果这张表的引擎是 MyISAM，那么 ID=4，如果是 InnoDB 那么 ID=2（MySQL 8 之前的版本）。
以下情况会导致 MySQL 自增主键不能连续：
唯一主键冲突会导致自增主键不连续；
事务回滚也会导致自增主键不连续。
自增主键能不能被持久化，说的是 MySQL 重启之后 InnoDB 能不能恢复重启之前的自增列，InnoDB 在 8.0 之前是没有持久化能力的，但 MySQL 8.0 之后就把自增主键保存到 redo log（一种日志类型，下文会详细讲）中，当 MySQL 重启之后就会从 redo log 日志中恢复。
共享表空间：指的是数据库的所有的表数据，索引文件全部放在一个文件中，默认这个共享表空间的文件路径在 data 目录下。 独立表空间：每一个表都将会生成以独立的文件方式来进行存储。 共享表空间和独立表空间最大的区别是如果把表放再共享表空间，即使表删除了空间也不会删除，所以表依然很大，而独立表空间如果删除表就会清除空间。
独立表空间是由参数 innodbfileper_table 控制的，把它设置成 ON 就是独立表空间了，从 MySQL 5.6.6 版本之后，这个值就默认是 ON 了。
使用重建表的方式可以收缩表空间，重建表有以下三种方式：
alter table t engine=InnoDB
optmize table t
truncate table t
建立一个临时文件，扫描表 t 主键的所有数据页；
用数据页中表 t 的记录生成 B+ 树，存储到临时文件中；
生成临时文件的过程中，将所有对 t 的操作记录在一个日志文件（row log）中；
临时文件生成后，将日志文件中的操作应用到临时文件，得到一个逻辑数据上与表 t相同的数据文件；
用临时文件替换表 t 的数据文件。
表结构定义占有的存储空间比较小，在 MySQL 8 之前，表结构的定义信息存在以 .frm 为后缀的文件里，在 MySQL 8 之后，则允许把表结构的定义信息存在系统数据表之中。
覆盖索引是指，索引上的信息足够满足查询请求，不需要再回到主键上去取数据。
可以回表查询，如果把主键删掉了，那么 InnoDB 会自己生成一个长度为 6 字节的 rowid 作为主键。
可能是因为 update 语句执行完成后，InnoDB 只保证写完了 redo log、内存，可能还没来得及将数据写到磁盘。
内存表，指的是使用 Memory 引擎的表，建表语法是 create table … engine=memory。这种表的数据都保存在内存里，系统重启的时候会被清空，但是表结构还在。除了这两个特性看上去比较“奇怪”外，从其他的特征上看，它就是一个正常的表。
而临时表，可以使用各种引擎类型 。如果是使用 InnoDB 引擎或者 MyISAM 引擎的临时表，写数据的时候是写到磁盘上的。
脏读
修改丢失
不可重复读
幻读
脏读是一个事务在处理过程中读取了另外一个事务未提交的数据；幻读是指同一个事务内多次查询返回的结果集不一样（比如增加了或者减少了行记录）。
因为行锁只能锁定存在的行，针对新插入的操作没有限定，所以就有可能产生幻读。 幻读带来的问题如下：
对行锁语义的破坏；
破坏了数据一致性。
使用间隙锁的方式来避免出现幻读。间隙锁，是专门用于解决幻读这种问题的锁，它锁的了行与行之间的间隙，能够阻塞新插入的操作 间隙锁的引入也带来了一些新的问题，比如：降低并发度，可能导致死锁。
在 MySQL 的命令行中使用 show processlist; 查看所有连接，其中 Command 列显示为 Sleep 的表示空闲连接，如下图所示：
使用 distinct 去重，使用 count 统计总条数，具体实现脚本如下：
select count(distinct f) from t
lastinsertid() 用于查询最后一次自增表的编号，它的特点是查询时不需要不需要指定表名，使用 select last_insert_id() 即可查询，因为不需要指定表名所以它始终以最后一条自增编号为主，可以被其它表的自增编号覆盖。比如 A 表的最大编号是 10，lastinsertid() 查询出来的值为 10，这时 B 表插入了一条数据，它的最大编号为 3，这个时候使用 lastinsertid() 查询的值就是 3。
删除数据有两种方式：delete 和 truncate，它们的区别如下：
delete 可以添加 where 条件删除部分数据，truncate 不能添加 where 条件只能删除整张表；
delete 的删除信息会在 MySQL 的日志中记录，而 truncate 的删除信息不被记录在 MySQL 的日志中，因此 detele 的信息可以被找回而 truncate 的信息无法被找回；
truncate 因为不记录日志所以执行效率比 delete 快。
delete 和 truncate 的使用脚本如下：
delete from t where username='redis'; truncate table t;
MySQL 中支持两种模糊查询：regexp 和 like，like 是对任意多字符匹配或任意单字符进行模糊匹配，而 regexp 则支持正则表达式的匹配方式，提供比 like 更多的匹配方式。 regexp 和 like 的使用示例如下： select * from person where uname like '%SQL%';> select from person where uname regexp '.SQL*.';
MySQL 支持枚举，它的实现方式如下：
create table t( sex enum('boy','grid') default 'unknown');枚举的作用是预定义结果值，当插入数据不在枚举值范围内，则插入失败，提示错误 Data truncated for column 'xxx' at row n 。
count(column) 和 count() 最大区别是统计结果可能不一致，count(column) 统计不会统计列值为 null 的数据，而 count() 则会统计所有信息，所以最终的统计结果可能会不同。
A. count 的查询性能在各种存储引擎下的性能都是一样的。 B. count 在 MyISAM 比 InnoDB 的性能要低。 C. count 在 InnoDB 中是一行一行读取，然后累计计数的。 D. count 在 InnoDB 中存储了总条数，查询的时候直接取出。
答：C
因为 InnoDB 使用了事务实现，而事务的设计使用了多版本并发控制，即使是在同一时间进行查询，得到的结果也可能不相同，所以 InnoDB 不能把结果直接保存下来，因为这样是不准确的。
不能，因为 show table status 是通过采样统计估算出来的，官方文档说误差可能在 40% 左右，所以 show table status 中的表行数不能直接使用。
A. select count(*) from t where time>1000 and time<4500 B. show table status where name='t' C. select count(id) from t where time>1000 and time<4500 D. select count(name) from t where time>1000 and time<4500
答：B 题目解析：因为 show table status 的表行数是估算出来，而其他的查询因为添加了 where 条件，即使是 MyISAM 引擎也不能直接使用已经存储的总条数，所以 show table status 的查询性能最高。
MyISAM 效率最高，因为 MyISAM 内部维护了一个计数器，直接返回总条数，而 InnoDB 要逐行统计。
count(*) 在不同的 MySQL 引擎中的实现方式是不相同的，在没有 where 条件的情况下：
MyISAM 引擎会把表的总行数存储在磁盘上，因此在执行 count(*) 的时候会直接返回这个这个行数，执行效率很高；
InnoDB 引擎中 count(*) 就比较麻烦了，需要把数据一行一行的从引擎中读出来，然后累计基数。
但即使这样，在 InnoDB 中，MySQL 还是做了优化的，我们知道对于 count() 这样的操作，遍历任意索引树得到的结果，在逻辑上都是一样的，因此，MySQL 优化器会找到最小的那颗索引树来遍历，这样就能在保证逻辑正确的前提下，尽量少扫描数据量，从而优化了 count() 的执行效率。
count(字段) 对于 count(主键 id) 来说，InnoDB 引擎会遍历整张表，把每一行的 id 值都取出来，返回给 server 层。server 层拿到 id 后，判断是不可能为空的，就按行累加。 对于 count(1) 来说，InnoDB 引擎遍历整张表，但不取值。server 层对于返回的每一行，放一个数字“1”进去，判断是不可能为空的，按行累加。 对于 count(字段) 来说，如果这个“字段”是定义为 not null 的话，一行行地从记录里面读出这个字段，判断不能为 null，按行累加；如果这个“字段”定义允许为 null，那么执行的时候，判断到有可能是 null，还要把值取出来再判断一下，不是 null 才累加。 对于 count(*) 来说，并不会把全部字段取出来，而是专门做了优化，不取值，直接按行累加。 所以最后得出的结果是：count(字段) 内连（inner join）— 把匹配的关联数据显示出来； 左连接（left join）— 把左边的表全部显示出来，右边的表显示出符合条件的数据； 右连接（right join）— 把右边的表全部显示出来，左边的表显示出符合条件的数据； 视图是一种虚拟的表，具有和物理表相同的功能，可以对视图进行增、改、查操作。视图通常是一个表或者多个表的行或列的子集。 视图创建脚本如下： 获取数据更容易，相对于多表查询来说； 视图能够对机密数据提供安全保护； 视图的修改不会影响基本表，提供了独立的操作单元，比较轻量。 MySQL 中的“视图”概念有两个，它们分别是： MySQL 中的普通视图也是我们最常用的 view，创建语法是 create view …,它的查询和普通表一样； InnoDB 实现 MVCC（Multi-Version Concurrency Control）多版本并发控制时用到的一致性读视图，它没有物理结构，作用是事务执行期间定于可以看到的数据。 可以用 Flashback 工具通过闪回把数据恢复回来。 Flashback 恢复数据的原理是是修改 binlog 的内容，拿回原库重放，从而实现数据找回。 ###什么是索引？ 索引是一种能帮助 MySQL 提高查询效率的数据结构。 ###索引分别有哪些优点和缺点？ 索引的优点如下： 快速访问数据表中的特定信息，提高检索速度。 创建唯一性索引，保证数据表中每一行数据的唯一性。 加速表与表之间的连接。 使用分组和排序进行数据检索时，可以显著减少查询中分组和排序的时间。 索引的缺点： 虽然提高了的查询速度，但却降低了更新表的速度，比如 update、insert，因为更新数据时，MySQL 不仅要更新数据，还要更新索引文件； 建立索引会占用磁盘文件的索引文件。 使用索引注意事项： 使用短索引，短索引不仅可以提高查询速度，更能节省磁盘空间和 I/O 操作； 索引列排序，MySQL 查询只使用一个索引，因此如果 where 子句中已经使用了索引的话，那么 order by 中的列是不会使用索引的，因此数据库默认排序可以符合要求的情况下，不要进行排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引； like 语句操作，一般情况下不鼓励使用 like 操作，如果非使用不可， 注意 like "%aaa%"> 不要在列上进行运算； 不适用 NOT IN 和 <> 操作。 ###以下 SQL 有什么问题？该如何优化？ 该 SQL 会导致引擎放弃索引而全表扫描，尽量避免在索引列上计算。可改为： ###为什么 MySQL 官方建议使用自增主键作为表的主键？ 因为自增主键是连续的，在插入过程中尽量减少页分裂，即使要进行页分裂，也只会分裂很少一部分；并且自增主键也能减少数据的移动，每次插入都是插入到最后，所以自增主键作为表的主键，对于表的操作来说性能是最高的。 ###自增主键有哪些优缺点? 优点： 数据存储空间很小； 性能最好； 减少页分裂。 缺点： 数据量过大，可能会超出自增长取值范围； 无法满足分布式存储，分库分表的情况下无法合并表； 主键有自增规律，容易被破解； 综上所述：是否需要使用自增主键，需要根据自己的业务场景来设计。如果是单表单库，则优先考虑自增主键，如果是分布式存储，分库分表，则需要考虑数据合并的业务场景来做数据库设计方案。 ###索引有几种类型？分别如何创建？ MySQL 的索引有两种分类方式：逻辑分类和物理分类。 按照逻辑分类，索引可分为： 主键索引：一张表只能有一个主键索引，不允许重复、不允许为 NULL； 唯一索引：数据列不允许重复，允许为 NULL 值，一张表可有多个唯一索引，但是一个唯一索引只能包含一列，比如身份证号码、卡号等都可以作为唯一索引； 普通索引：一张表可以创建多个普通索引，一个普通索引可以包含多个字段，允许数据重复，允许 NULL 值插入； 全文索引：让搜索关键词更高效的一种索引。 按照物理分类，索引可分为： 聚集索引：一般是表中的主键索引，如果表中没有显示指定主键，则会选择表中的第一个不允许为 NULL 的唯一索引，如果还是没有的话，就采用 Innodb 存储引擎为每行数据内置的 6 字节 ROWID 作为聚集索引。每张表只有一个聚集索引，因为聚集索引的键值的逻辑顺序决定了表中相应行的物理顺序。聚集索引在精确查找和范围查找方面有良好的性能表现（相比于普通索引和全表扫描），聚集索引就显得弥足珍贵，聚集索引选择还是要慎重的（一般不会让没有语义的自增 id 充当聚集索引）； 非聚集索引：该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同（非主键的那一列），一个表中可以拥有多个非聚集索引。 各种索引的创建脚本如下： ###主索引和唯一索引有什么区别？ 主索引不能重复且不能为空，唯一索引不能重复，但可以为空； 一张表只能有一个主索引，但可以有多个唯一索引； 主索引的查询性能要高于唯一索引。 ###在 InnDB 中主键索引为什么比普通索引的查询性能高？ 因为普通索引的查询会多执行一次检索操作。比如主键查询 select * from t where id=10 只需要搜索 id 的这棵 B+ 树，而普通索引查询 select * from t where f=3 会先查询 f 索引树，得到 id 的值之后再去搜索 id 的 B+ 树，因为多执行了一次检索，所以执行效率就比主键索引要低。 ###什么叫回表查询？ 普通索引查询到主键索引后，回到主键索引树搜索的过程，我们称为回表查询。 参考SQL： 如果语句是 select * from T where ID=500，即主键查询方式，则只需要检索主键 ID 字段。 如果语句是 select * from T where k=5，即普通索引查询方式，则需要先搜索 k 索引树，得到 ID 的值为 500，再到 ID 索引树搜索一次，这个过程称为回表查询。 也就是说，基于非主键索引的查询需要多扫描一棵索引树。因此，我们在应用中应该尽量使用主键查询。 ###如何查询一张表的所有索引？ SHOW INDEX FROM T 查询表 T 所有索引。 ###MySQL 最多可以创建多少个索引列？ MySQL 中最多可以创建 16 个索引列。 ###以下 like 查询会使用索引的是哪一个选项？为什么？ A.like '%A%' B.like '%A' C.like 'A%' D.以上都不是 答：C 题目解析：like 查询要走索引，查询字符不能以通配符（%）开始。 ###如何让 like %abc 走索引查询？ 我们知道如果要让 like 查询要走索引，查询字符不能以通配符（%）开始，如果要让 like %abc 也走索引，可以使用 REVERSE() 函数来创建一个函数索引，查询脚本如下： ###MySQL 联合索引应该注意什么？ 联合索引又叫复合索引，MySQL 中的联合索引，遵循最左匹配原则，比如，联合索引为 key(a,b,c)，则能触发索引的搜索组合是 a|ab|abc 这三种查询。 ###联合索引的作用是什么？ 联合索引的作用如下： 用于多字段查询，比如，建了一个 key(a,b,c) 的联合索引，那么实际等于建了key(a)、key(a,b)、key(a,b,c)等三个索引，我们知道，每多一个索引，就会多一些写操作和占用磁盘空间的开销，尤其是对大数据量的表来说，这可以减少一部分不必要的开销； 覆盖索引，比如，对于联合索引 key(a,b,c) 来说，如果使用 SQL：select a,b,c from table where a=1 and b = 1 ，就可以直接通过遍历索引取得数据，而无需回表查询，这就减少了随机的 IO 操作，减少随机的 IO 操作，可以有效的提升数据库查询的性能，是非常重要的数据库优化手段之一； 索引列越多，通过索引筛选出的数据越少。 ###什么是最左匹配原则？它的生效原则有哪些？ 最左匹配原则也叫最左前缀原则，是 MySQL 中的一个重要原则，说的是索引以最左边的为起点任何连续的索引都能匹配上，当遇到范围查询（>、<、between、like）就会停止匹配。 生效原则来看以下示例，比如表中有一个联合索引字段 index(a,b,c)： where a=1 只使用了索引 a； where a=1 and b=2 只使用了索引 a,b； where a=1 and b=2 and c=3 使用a,b,c； where b=1 or where c=1 不使用索引； where a=1 and c=3 只使用了索引 a； where a=3 and b like 'xx%' and c=3 只使用了索引 a,b。 ###列值为 NULL 时，查询会使用到索引吗？ 在 MySQL 5.6 以上的 InnoDB 存储引擎会正常触发索引。但为了兼容低版本的 MySQL 和兼容其他数据库存储引擎，不建议使用 NULL 值来存储和查询数据，建议设置列为 NOT NULL，并设置一个默认值，比如 0 和空字符串等，如果是 datetime 类型，可以设置成 1970-01-01 00:00:00 这样的特殊值。 ###以下语句会走索引么？ 不会，因为在索引列上涉及到了运算。 ###能否给手机号的前 6 位创建索引？如何创建？ 可以，创建方式有两种： ###什么是前缀索引？ 前缀索引也叫局部索引，比如给身份证的前 10 位添加索引，类似这种给某列部分信息添加索引的方式叫做前缀索引。 ###为什么要用前缀索引？ 前缀索引能有效减小索引文件的大小，让每个索引页可以保存更多的索引值，从而提高了索引查询的速度。但前缀索引也有它的缺点，不能在 order by 或者 group by 中触发前缀索引，也不能把它们用于覆盖索引。 ###什么情况下适合使用前缀索引？ 当字符串本身可能比较长，而且前几个字符就开始不相同，适合使用前缀索引；相反情况下不适合使用前缀索引，比如，整个字段的长度为 20，索引选择性为 0.9，而我们对前 10 个字符建立前缀索引其选择性也只有 0.5，那么我们需要继续加大前缀字符的长度，但是这个时候前缀索引的优势已经不明显，就没有创建前缀索引的必要了。 ###什么是业？ 页是计算机管理存储器的逻辑块，硬件及操作系统往往将主存和磁盘存储区分割为连续的大小相等的块，每个存储块称为一页。主存和磁盘以页为单位交换数据。数据库系统的设计者巧妙利用了磁盘预读原理，将一个节点的大小设为等于一个页，这样每个节点只需要一次磁盘 IO 就可以完全载入。 ###索引的常见存储算法有哪些？ 哈希存储法：以 key、value 方式存储，把值存入数组中使用哈希值确认数据的位置，如果发生哈希冲突，使用链表存储数据； 有序数组存储法：按顺序存储，优点是可以使用二分法快速找到数据，缺点是更新效率，适合静态数据存储； 搜索树：以树的方式进行存储，查询性能好，更新速度快。 ###InnoDB 为什么要使用 B+ 树，而不是 B 树、Hash、红黑树或二叉树？ 因为 B 树、Hash、红黑树或二叉树存在以下问题： B 树：不管叶子节点还是非叶子节点，都会保存数据，这样导致在非叶子节点中能保存的指针数量变少（有些资料也称为扇出），指针少的情况下要保存大量数据，只能增加树的高度，导致IO操作变多，查询性能变低； Hash：虽然可以快速定位，但是没有顺序，IO 复杂度高； 二叉树：树的高度不均匀，不能自平衡，查找效率跟数据有关（树的高度），并且 IO 代价高； 红黑树：树的高度随着数据量增加而增加，IO 代价高。 ###为什么 InnoDB 要使用 B+ 谁来存储索引？ B+Tree 中的 B 是 Balance，是平衡的意思，它在经典 B Tree 的基础上进行了优化，增加了顺序访问指针，在B+Tree 的每个叶子节点增加一个指向相邻叶子节点的指针，就形成了带有顺序访问指针的 B+Tree，这样就提高了区间访问性能：如果要查询 key 为从 18 到 49 的所有数据记录，当找到 18 后，只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点，极大提到了区间查询效率（无需返回上层父节点重复遍历查找减少 IO 操作）。 索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上，这样的话，索引查找过程中就要产生磁盘 IO 消耗，相对于内存存取，IO 存取的消耗要高几个数量级，所以索引的结构组织要尽量减少查找过程中磁盘 IO 的存取次数，从而提升索引效率。 综合所述，InnDB 只有采取 B+ 树的数据结构存储索引，才能提供数据库整体的操作性能。 ###唯一索引和普通索引哪个性能更好？ 对于查询操作来说：普通索引和唯一索引的性能相近，都是从索引树中进行查询； 对于更新操作来说：唯一索引要比普通索引执行的慢，因为唯一索引需要先将数据读取到内存中，再在内存中进行数据的唯一效验，所以执行起来要比普通索引更慢。 ###优化器选择查询索引的影响因素有哪些？ 优化器的目的是使用最小的代价选择最优的执行方案，影响优化器选择索引的因素如下： 扫描行数，扫描的行数越少，执行代价就越少，执行效率就会越高； 是否使用了临时表； 是否排序。 ###MySQL 是如何判断索引扫描行数的多少？ MySQL 的扫描行数是通过索引统计列（cardinality）大致得到并且判断的，而索引统计列（cardinality）可以通过查询命令 show index 得到，索引扫描行数的多少就是通过这个值进行判断的。 ###MySQL 是如何得到索引基数的？它准确吗？ MySQL 的索引基数并不准确，因为 MySQL 的索引基数是通过采样统计得到的，比如 InnoDb 默认会有 N 个数据页，采样统计会统计这些页面上的不同值得到一个平均值，然后除以这个索引的页面数就得到了这个索引基数。 ###MySQL 如何指定查询的索引？ 在 MySQL 中可以使用 force index 强行选择一个索引，具体查询语句如下： ###在 MySQL 中指定了查询索引，为什么没有生效？ 我们知道在 MySQL 中使用 force index 可以指定查询的索引，但并不是一定会生效，原因是 MySQL 会根据优化器自己选择索引，如果 force index 指定的索引出现在候选索引上，这个时候 MySQL 不会在判断扫描的行数的多少直接使用指定的索引，如果没在候选索引中，即使 force index 指定了索引也是不会生效的。 ###以下 or 查询有什么问题吗？该如何优化？ 答：如果使用 or 查询会使 MySQL 放弃索引而全表扫描，可以改为： ###以下查询要如何优化？ 表中包含索引： 使用以下 SQL 进行查询： 答：此查询理论上是使用 dg 索引效率更高，通过 explain 可以对比查询扫描次数。由于使用了 order by begintime 则使查询放弃了 dg 索引，而使用 begintime 索引，从侧面印证 order by 关键字会影响查询使用索引，这时可以使查询强制使用索引，改为以下SQL： ###MySQL 会错选索引吗？ MySQL 会错选索引，比如 k 索引的速度更快，但是 MySQL 并没有使用而是采用了 v 索引，这种就叫错选索引，因为索引选择是 MySQL 的服务层的优化器来自动选择的，但它在复杂情况下也和人写程序一样出现缺陷。 ###如何解决 MySQL 错选索引的问题？ 删除错选的索引，只留下对的索引； 使用 force index 指定索引； 修改 SQL 查询语句引导 MySQL 使用我们期望的索引，比如把 order by b limit 1 改为 order by b,a limit 1 语义是相同的，但 MySQL 查询的时候会考虑使用 a 键上的索引。 ###如何优化身份证的索引？ 在中国因为前 6 位代表的是地区，所以很多人的前六位都是相同的，如果我们使用前缀索引为 6 位的话，性能提升也并不是很明显，但如果设置的位数过长，那么占用的磁盘空间也越大，数据页能放下的索引值就越少，搜索效率也越低。针对这种情况优化方案有以下两种： 使用身份证倒序存储，这样设置前六位的意义就很大了； 使用 hash 值，新创建一个字段用于存储身份证的 hash 值。 事务是一系列的数据库操作，是数据库应用的基本单位。MySQL 事务主要用于处理操作量大，复杂度高的数据。 在 MySQL 中只有 InnDB 引擎支持事务，它的四个特性如下： 原子性（Atomic）：要么全部执行，要么全部不执行； 一致性（Consistency）：事务的执行使得数据库从一种正确状态转化为另一种正确状态； 隔离性（Isolation）：在事务正确提交之前，不允许把该事务对数据的任何改变提供给其他事务； 持久性（Durability）：事务提交后，其结果永久保存在数据库中。 MySQL 中有四种事务隔离级别，它们分别是： read uncommited：未提交读，读到未提交数据； read committed：读已提交，也叫不可重复读，两次读取到的数据不一致； repetable read：可重复读； serializable：串行化，读写数据都会锁住整张表，数据操作不会出错，但并发性能极低，开发中很少用到。 MySQL 默认使用 REPEATABLE-READ 的事务隔离级别。 不可重复读的重点是修改：在同一事务中，同样的条件，第一次读的数据和第二次读的数据不一样。（因为中间有其他事务提交了修改）。 幻读的重点在于新增或者删除：在同一事务中，同样的条件,，第一次和第二次读出来的记录数不一样。（因为中间有其他事务提交了插入/删除）。 更新丢失（Lost Update）：当两个或多个事务选择同一行，然后基于最初选定的值更新该行时，由于每个事务都不知道其他事务的存在，就会发生丢失更新问题，最后的更新覆盖了由其他事务所做的更新。例如，两个编辑人员制作了同一文档的电子副本，每个编辑人员独立地更改其副本，然后保存更改后的副本，这样就覆盖了原始文档。 最后保存其更改副本的编辑人员覆盖另一个编辑人员所做的更改，如果在前一个编辑人员完成并提交事务之前，另一个编辑人员不能访问同一文件，则可避免此问题。 脏读（Dirty Reads）：一个事务正在对一条记录做修改，在这个事务完成并提交前， 这条记录的数据就处于不一致状态； 这时， 另一个事务也来读取同一条记录，如果不加控制，第二个事务读取了这些脏数据，并据此做进一步的处理，就会产生未提交的数据依赖关系，这种现象被形象地叫做脏读。 不可重复读（Non-Repeatable Reads）：一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现其读出的数据已经发生了改变、或某些记录已经被删除了！这种现象就叫做“不可重复读” 。 幻读（Phantom Reads）： 一个事务按相同的查询条件重新读取以前检索过的数据，却发现其他事务插入了满足其查询条件的新数据，这种现象就称为“幻读” 。 并发事务可能造成：脏读、不可重复读和幻读等问题 ，这些问题其实都是数据库读一致性问题，必须由数据库提供一定的事务隔离机制来解决，解决方案如下： 加锁：在读取数据前，对其加锁，阻止其他事务对数据进行修改。 提供数据多版本并发控制（MultiVersion Concurrency Control，简称 MVCC 或 MCC），也称为多版本数据库：不用加任何锁， 通过一定机制生成一个数据请求时间点的一致性数据快照（Snapshot)， 并用这个快照来提供一定级别 （语句级或事务级） 的一致性读取，从用户的角度来看，好象是数据库可以提供同一数据的多个版本。 MVCC 全称是多版本并发控制系统，InnoDB 和 Falcon 存储引擎通过多版本并发控制（MVCC，Multiversion Concurrency Control）机制解决幻读问题。 InnoDB 的 MVCC 是通过在每行记录后面保存两个隐藏的列来实现，这两个列一个保存了行的创建时间，一个保存行的过期时间（删除时间）。当然存储的并不是真实的时间而是系统版本号（system version number）。每开始一个新的事务，系统版本号都会自动新增，事务开始时刻的系统版本号会作为事务的版本号，用来查询到每行记录的版本号进行比较。 SELECT：InnoDB 会根据以下条件检查每一行记录：第一，InnoDB 只查找版本早于当前事务版本的数据行，这样可以确保事务读取的行要么是在开始事务之前已经存在要么是事务自身插入或者修改过的。第二，行的删除版本号要么未定义，要么大于当前事务版本号，这样可以确保事务读取到的行在事务开始之前未被删除。 INSERT：InnoDB 为新插入的每一行保存当前系统版本号作为行版本号。 DELETE：InnoDB 为删除的每一行保存当前系统版本号作为行删除标识。 UPDATE：InnoDB 为插入的一行新纪录保存当前系统版本号作为行版本号，同时保存当前系统版本号到原来的行作为删除标识保存这两个版本号，使大多数操作都不用加锁。它不足之处是每行记录都需要额外的存储空间，需要做更多的行检查工作和一些额外的维护工作。 事务的实现是基于数据库的存储引擎，不同的存储引擎对事务的支持程度不一样。MySQL 中支持事务的存储引擎有InnoDB 和 NDB。 InnoDB 是高版本 MySQL 的默认的存储引擎，因此就以 InnoDB 的事务实现为例，InnoDB 是通过多版本并发控制（MVCC，Multiversion Concurrency Control ）解决不可重复读问题，加上间隙锁（也就是并发控制）解决幻读问题。因此 InnoDB 的 RR 隔离级别其实实现了串行化级别的效果，而且保留了比较好的并发性能。事务的隔离性是通过锁实现，而事务的原子性、一致性和持久性则是通过事务日志实现。 MySQL 事务隔离级别 MySQL.cnf 文件里设置的（默认目录 /etc/my.cnf），在文件的文末添加配置： transaction-isolation = REPEATABLE-READ 可用的配置值：READ-UNCOMMITTED、READ-COMMITTED、REPEATABLE-READ、SERIALIZABLE。 InnoDB 默认的事务隔离是 repetable read（可重复读）；可以通过 set 作用域 transaction isolation level 事务隔离级别 来修改事务的隔离级别，比如： MySQL> set global transaction isolation level read committed; // 设置全局事务隔离级别为 read committed MySQL> set session transaction isolation level read committed; // 设置当前会话事务隔离级别为 read committed InnoDB 默认是自动提交事务的，每一次 SQL 操作（非 select 操作）都会自动提交一个事务，如果要手动开启事务需要设置 set autocommit=0 禁止自动提交事务，相当于开启手动提交事务。 autocommit=0 表示禁止自动事务提交，在添加操作之后没有进行手动提交，默认情况下其他连接客户端是查询不到此条新增数据的。 使用 begin 开启事务；rollback 回滚事务；commit 提交事务。具体使用示例如下： 锁是实现数据库并发控制的重要手段，可以保证数据库在多人同时操作时能够正常运行。MySQL 提供了全局锁、行级锁、表级锁。其中 InnoDB 支持表级锁和行级锁，MyISAM 只支持表级锁。 是指两个或两个以上的进程在执行过程中，因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁，这些永远在互相等待的过程称为死锁。 死锁是指两个或两个以上的进程在执行过程中，因争夺资源而造成的一种互相等待的现象,若无外力作用,它们都将无法推进下去。此时称系统处于死锁状态或系统产生了死锁，这些永远在互相等待的过程称为死锁。 将投资的钱拆封几份借给借款人，这时处理业务逻辑就要把若干个借款人一起锁住 select * from xxx where id in (xx,xx,xx) for update。 批量入库，存在则更新，不存在则插入。解决方法 insert into tab(xx,xx) on duplicate key update xx='xx'。 对待死锁常见的两种策略： 通过 innodblockwait_timeout 来设置超时时间，一直等待直到超时； 发起死锁检测，发现死锁之后，主动回滚死锁中的某一个事务，让其它事务继续执行。 使用命令 show engine innodb status 查看最近的一次死锁。 InnoDB Lock Monitor 打开锁监控，每 15s 输出一次日志。使用完毕后建议关闭，否则会影响数据库性能。 为了在单个 InnoDB 表上执行多个并发写入操作时避免死锁，可以在事务开始时通过为预期要修改的每个元祖（行）使用 SELECT … FOR UPDATE 语句来获取必要的锁，即使这些行的更改语句是在之后才执行的。 在事务中，如果要更新记录，应该直接申请足够级别的锁，即排他锁，而不应先申请共享锁、更新时再申请排他锁，因为这时候当用户再申请排他锁时，其他事务可能又已经获得了相同记录的共享锁，从而造成锁冲突，甚至死锁 如果事务需要修改或锁定多个表，则应在每个事务中以相同的顺序使用加锁语句。在应用中，如果不同的程序会并发存取多个表，应尽量约定以相同的顺序来访问表，这样可以大大降低产生死锁的机会 通过 SELECT … LOCK IN SHARE MODE 获取行的读锁后，如果当前事务再需要对该记录进行更新操作，则很有可能造成死锁。 改变事务隔离级别。 InnoDB 默认是使用设置死锁时间来让死锁超时的策略，默认 innodblockwait_timeout 设置的时长是 50s。 设置 innodbdeadlockdetect 设置为 on 可以主动检测死锁，在 Innodb 中这个值默认就是 on 开启的状态。 全局锁就是对整个数据库实例加锁，它的典型使用场景就是做全库逻辑备份。 这个命令可以使整个库处于只读状态。使用该命令之后，数据更新语句、数据定义语句、更新类事务的提交语句等操作都会被阻塞。 共享锁又称读锁 (read lock)，是读取操作创建的锁。其他用户可以并发读取数据，但任何事务都不能对数据进行修改（获取数据上的排他锁），直到已释放所有共享锁。当如果事务对读锁进行修改操作，很可能会造成死锁。 排他锁 exclusive lock（也叫 writer lock）又称写锁。 若某个事物对某一行加上了排他锁，只能这个事务对其进行读写，在此事务结束之前，其他事务不能对其进行加任何锁，其他进程可以读取,不能进行写操作，需等待其释放。 排它锁是悲观锁的一种实现，在上面悲观锁也介绍过。 若事务 1 对数据对象 A 加上 X 锁，事务 1 可以读 A 也可以修改 A，其他事务不能再对 A 加任何锁，直到事物 1 释放 A 上的锁。这保证了其他事务在事物 1 释放 A 上的锁之前不能再读取和修改 A。排它锁会阻塞所有的排它锁和共享锁。 如果在主库备份，在备份期间不能更新，业务停摆，所以更新业务会处于等待状态。 如果在从库备份，在备份期间不能执行主库同步的 binlog，导致主从延迟。 如果使用全局锁进行逻辑备份就会让整个库成为只读状态，幸好官方推出了一个逻辑备份工具 MySQLdump 来解决了这个问题，只需要在使用 MySQLdump 时，使用参数 -single-transaction 就会在导入数据之前启动一个事务来保证数据的一致性，并且这个过程是支持数据更新操作的。 使用命令 flush tables with read lock（简称 FTWRL）就可以实现设置数据库为全局只读锁。 除了使用 FTWRL 外，还可以使用命令 set global readonly=true 设置数据库为只读。 FTWRL 和 set global readonly=true 都是设置整个数据库为只读状态，但他们最大的区别就是，当执行 FTWRL 的客户端断开之后，整个数据库会取消只读，而 set global readonly=true 会一直让数据处于只读状态。 MySQL 里标记锁有两种：表级锁、元数据锁（meta data lock）简称 MDL。表锁的语法是 lock tables t read/write。 可以用 unlock tables 主动释放锁，也可以在客户端断开的时候自动释放。lock tables 语法除了会限制别的线程的读写外，也限定了本线程接下来的操作对象。 对于 InnoDB 这种支持行锁的引擎，一般不使用 lock tables 命令来控制并发，毕竟锁住整个表的影响面还是太大。 MDL：不需要显式使用，在访问一个表的时候会被自动加上。 MDL 的作用：保证读写的正确性。 在对一个表做增删改查操作的时候，加 MDL 读锁；当要对表做结构变更操作的时候，加 MDL 写锁。 读锁之间不互斥，读写锁之间，写锁之间是互斥的，用来保证变更表结构操作的安全性。 MDL 会直到事务提交才会释放，在做表结构变更的时候，一定要小心不要导致锁住线上查询和更新。 顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会 block 直到它拿到锁。正因为如此，悲观锁需要耗费较多的时间，另外与乐观锁相对应的，悲观锁是由数据库自己实现了的，要用的时候，我们直接调用数据库的相关语句就可以了。 说到这里，由悲观锁涉及到的另外两个锁概念就出来了，它们就是共享锁与排它锁。共享锁和排它锁是悲观锁的不同的实现，它俩都属于悲观锁的范畴。 乐观锁是用数据版本（Version）记录机制实现，这是乐观锁最常用的一种实现方式。何谓数据版本？即为数据增加一个版本标识，一般是通过为数据库表增加一个数字类型的 version 字段来实现。当读取数据时，将 version 字段的值一同读出，数据每更新一次，对此 version 值加 1。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的 version 值相等，则予以更新，否则认为是过期数据。 比如：1、数据库表三个字段，分别是id、value、version select id,value,version from t where id=#{id} 2、每次更新表中的value字段时，为了防止发生冲突，需要这样操作 因为没有加锁所以乐观锁的优点就是执行性能高。它的缺点就是有可能产生 ABA 的问题，ABA 问题指的是有一个变量 V 初次读取的时候是 A 值，并且在准备赋值的时候检查到它仍然是 A 值，会误以为没有被修改会正常的执行修改操作，实际上这段时间它的值可能被改了其他值，之后又改回为 A 值，这个问题被称为 ABA 问题。 Record Lock — 单个行记录上的锁； Gap Lock — 间隙锁，锁定一个范围，不包括记录本身； Next-Key Lock — 锁定一个范围，包括记录本身。 行级锁是 MySQL 中粒度最小的一种锁，他能大大减少数据库操作的冲突。 INNODB 的行级锁有共享锁（S LOCK）和排他锁（X LOCK）两种。共享锁允许事物读一行记录，不允许任何线程对该行记录进行修改。排他锁允许当前事物删除或更新一行记录，其他线程不能操作该记录。 共享锁：SELECT … LOCK IN SHARE MODE，MySQL 会对查询结果集中每行都添加共享锁，前提是当前线程没有对该结果集中的任何行使用排他锁，否则申请会阻塞。 排他锁：select * from t where id=1 for update，其中 id 字段必须有索引，MySQL 会对查询结果集中每行都添加排他锁，在事物操作中，任何对记录的更新与删除操作会自动加上排他锁。前提是当前没有线程对该结果集中的任何行使用排他锁或共享锁，否则申请会阻塞。 尽量使用较低的隔离级别。 精心设计索引， 并尽量使用索引访问数据， 使加锁更精确， 从而减少锁冲突的机会。 选择合理的事务大小，小事务发生锁冲突的几率也更小。 给记录集显示加锁时，最好一次性请求足够级别的锁。比如要修改数据的话，最好直接申请排他锁，而不是先申请共享锁，修改时再请求排他锁，这样容易产生死锁。 不同的程序访问一组表时，应尽量约定以相同的顺序访问各表，对一个表而言，尽可能以固定的顺序存取表中的行。这样可以大大减少死锁的机会。 尽量用相等条件访问数据，这样可以避免间隙锁对并发插入的影响。 不要申请超过实际需要的锁级别。 除非必须，查询时不要显示加锁。 MySQL 的 MVCC 可以实现事务中的查询不用加锁，优化事务性能；MVCC 只在 COMMITTED READ（读提交）和 REPEATABLE READ（可重复读）两种隔离级别下工作。 对于一些特定的事务，可以使用表锁来提高处理速度或减少死锁的可能。 “高频MySQL的题目有哪些”的内容就介绍到这里了，感谢大家的阅读。如果想了解更多行业相关的知识可以关注创新互联网站，小编将为大家输出更多高质量的实用文章！MySQL 中内连接、左连接、右连接有什么区别？
什么是视图？如何创建视图？
create view vname asselect column_namesfrom table_namewhere condition
视图有哪些优点？
MySQL 中“视图”的概念有几个？分别代表什么含义？
使用 delete 误删数据怎么找回？
Flashback 恢复数据的原理是什么？
MySQL索引篇
select * from t where f/2=100;
select * from t where f=100*2;
-- 创建主键索引alter table t add primary key add (`id`);-- 创建唯一索引alter table t add unique (`username`);-- 创建普通索引alter table t add index index_name (`username`);-- 创建全文索引alter table t add fulltext (`username`);
mysql> create table T(id int primary key, k int not null, name varchar(16),index (k))engine=InnoDB;
mysql> select * from T where ID=500;+-----+---+-------+| id | k | name |+-----+---+-------+| 500 | 5 | name5 |+-----+---+-------+
mysql> select * from T where k=5;
+-----+---+-------+
| id | k | name |
+-----+---+-------+
| 500 | 5 | name5 |
+-----+---+-------+
select * from t where reverse(f) like reverse('%abc');select * from t where year(date)>2018;
alter table t add index index_phone(phone(6));
create index index_phone on t(phone(6));
select * from t force index(index_t)
select * from t where num=10 or num=20;
select * from t where num=10 union select * from t where num=20;
KEY mid (mid)
KEY begintime (begintime)
KEY dg (day,group)
select f from t where day='2010-12-31' and group=18 and begintime<'2019-12-31 12:14:28' order by begintime limit 1;
select f from t use index(dg) where day='2010-12-31' and group=18 and begintime< '2019-12-31 12:14:28' order by begintime limit 1;
MySQL事务篇
事务是什么？
事务有哪些特性？
MySQL 中有几种事务隔离级别？分别是什么？
幻读和不可重复读的区别？
并发事务一般有哪些问题？
并发事务有什么什么问题？应该如何解决？
什么是 MVCC？
MVCC 是怎么工作的？
REPEATABLE READ（可重读）隔离级别下 MVCC 如何工作？
MySQL 事务实现原理是什么？
如何设置 MySQL 的事务隔离级别？
InnoDB 默认的事务隔离级别是什么？如何修改？
InnoDB 如何开启手动提交事务？
在 InnoDB 中设置了 autocommit=0，添加一条信息之后没有手动执行提交操作，请问这条信息可以被查到吗？
如何手动操作事务？
begin;
insert person(uname,age) values('laowang',18);
rollback;
commit;MySQL锁片
什么是锁？MySQL 中提供了几类锁？
什么是死锁？
常见的死锁案例有哪些？
如何处理死锁？
如何查看死锁？
如何避免死锁？
InnoDB 默认是如何对待死锁的？
如何开启死锁检测？
什么是全局锁？它的应用场景有哪些？
什么是共享锁？
什么是排它锁？
使用全局锁会导致什么问题？
如何处理逻辑备份时，整个数据库不能插入的情况？
如何设置数据库为全局只读锁？
除了 FTWRL 可以设置数据库只读外，还有什么别的方法？
FTWRL 和 set global readonly=true 有什么区别？
如何实现表锁？
悲观锁和乐观锁有什么区别？
update t
set value=2,version=version+1
where id=#{id} and version=#{version}乐观锁有什么优点和缺点？
InnoDB 存储引擎有几种锁算法？
InnoDB 如何实现行锁？
优化锁方面你有什么建议？
网站名称：高频MySQL的题目有哪些
URL标题：http://cdxtjz.cn/article/ggpphp.html