MySQL高级

MySQL面试题MySQL

全值匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
Like百分写最右，覆盖索引不写星；
不等空值还有or，索引失效要少用；
VAR引号不可丢，SQL高级也不难！

SQL Joins

SELECT <select_list> 
FROM TableA A
INNER JOIN TableB B
ON A.key = B.key


SELECT <select_list>
FROM TableA A
LEFT JOIN TableB B
ON A.key = B.key


SELECT <select_list>
FROM TableA A
RIGHT JOIN TableB B
ON A.key = B.key


SELECT <select_list>
FROM TableA A
LEFT JOIN TableB B
ON A.key = B.key
WHERE B.key IS NULL


SELECT <select_list>
FROM TableA A
RIGHT JOIN TableB B
ON A.key = B.key
WHERE A.key IS NULL


//oracle做法
SELECT <select_list>
FROM TableA A
FULL OUTER JOIN TableB B
ON A.key = B.key

//Mysql中没有FULL OUTER关键字，使用UNION
SELECT <select_list>
FROM TableA A
LEFT JOIN TableB B
ON A.key = B.key
WHERE B.key IS NULL
UNION 
SELECT <select_list>
FROM TableB 



SELECT <select_list>
FROM TableA A
FULL OUTER JOIN TableB B
ON A.key = B.key
WHERE A.key IS NULL
OR B.key IS NULL

索引

什么是索引？

MySQL官方对索引的定义是：索引（Index）是帮助MySQL高效获取数据的数据结构。

索引的本质：索引是数据结构。

你可以简单理解为“排好序的快速查找数据结构”。

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。

我们平常所说的索引，如果没有特别指明，都是指B树（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然，除了B+树这种类型的索引之外，还有哈希索引（hash index）等。

优势

类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本。

通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。

劣势

实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。

虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新带来的键值变化后的索引信息。

索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询。

索引分类

  1. 单值索引

    即一个索引只包含单个列，一个表可以有多个单列索引

  2. 唯一索引

    索引列的值必须唯一，但允许有空值

  3. 复合索引

    即一个索引包含多个列

  4. 基本语法

    创建

CREATE [UNIOUE] INDEX indexName ON mytable(columnname(length));

ALTER mytable ADD [UNIQUE] INDEX [indexName] ON (columnname(length));

**删除**

DROP INDEX [indexName] ON mytable;

**查看**

SHOW INDEX FROM table_name\G

有四种方式来添加数据表的索引：

ALTER TABLE tbl_name ADD PRIMARY KEY(column_list)
该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。

ALTER TABLE tbl_name ADD UNIQUE index_name(column_list)
这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。

ALTER TABLE tbl_name ADD INDEX index_name(column_list)
添加普通索引，索引值可能出现多次。

ALTER TABLE tbl_name ADD FULLTEXT index_name(column_list)
该语句指定了索引为FULLTEXT，用于全文索引。

索引结构

BTree索引 —— B树索引

Hash索引 —— 哈希索引

full-text全文索引 —— 全文索引

R-Tree索引

哪些情况需要创建索引？

主键自动创建唯一索引

频繁作为查询条件的字段应该创建索引

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

频繁更新的字段不适合创建索引（因为每次更新不单单是更新了记录还会更新索引）。

Where条件里用不到的字段不创建索引

单键/组合索引的选择问题，Who?（在高并发下倾向创建组合索引）

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

查询中统计或者分组字段

哪些情况不要创建索引？

  1. 表记录太少
  2. 经常增删改的表
  3. 数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引
  4. 注意：如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果

性能分析

EXPLAIN

是什么？

使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而指导MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈。

能干嘛？

  1. 表的读取顺序
  2. 数据读取操作的操作类型
  3. 哪些索引可以使用
  4. 哪些索引被实际使用
  5. 表之间的引用
  6. 每张表有多少行被优化器查询

各名词解释

id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra

id

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

三种情况：id相同，执行顺序由上至下

id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

id相同不同，同时存在

select_type

有哪些：

id select_type
1 SIMPLE
2 PRIMARY（主键查询）
3 SUBQUERY（子查询）
4 DERIVED（衍生）
5 UNION（联合查询）
6 UNION RESULT（UNION表获取结果的SELECT）

查询的类型，主要是用于区别普通查询、联合查询、子查询的复杂查询

table

显示这一行的数据是关于哪张表的

type

ALL index range ref eq_ref const,system NULL

从最好到最差依次是：

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

实际开发中大多数index，多range，最好ref

possible_keys（理论上可能用到的索引）

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

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

key（实际上真正使用到的索引）

实际使用的索引。如果为NULL，则没有使用索引

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

key_len

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

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

ref

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

rows

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

Extra

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

  1. Using filesort（九死一生）

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

  2. Using temporary（十死无生）

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

  3. USING index（好结果）

    表示相应的select操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错！

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

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

    覆盖索引（Covering Index），一说为索引覆盖

    理解：

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

  4. Using where

    表明使用了where过滤

  5. using join buffer

    使用了连接缓存

  6. impossible where

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

  7. select tables optimized away

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

  8. distinct

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

索引优化

口诀（对应本章首）

  1. 全值匹配我最爱
  2. 最佳左前缀法则
  3. 不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描
  4. 存储引擎不能使用索引中范围条件右边的列
  5. 尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少select *
  6. mysql在使用不等于（!= 或者 < >）的时候无法使用索引会导致全表扫描
  7. is null，is not null 也无法使用索引
  8. like以通配符开头（'%abc...'）mysql索引失效会变成全表扫描的操作
  9. 字符串不加单引号索引失效
  10. 少用or，用它来连接时会索引失效

索引失效

  • 定值、范围还是排序，一般order by 是给个范围
  • group by 基本上都需要进行排序，会有临时表产生

一般性建议

  • 对于单键索引，尽量选择针对当前query过滤性更好的索引
  • 在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好
  • 在选择组合索引的时候，尽量选择可以能够包含当前query中的where子句中更多字段的索引
  • 尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的

查询优化

--------分析--------

  1. 观察，至少跑1天，看看生产的慢SQL情况
  2. 开启慢查询日志，设置阈值，比如超过5秒钟的就是慢SQL，并将它抓取出来
  3. explain + 慢SQL分析
  4. show profile
  5. 运维经理 or DBA，进行SQL数据库服务器的参数调优

--------总结--------

  1. 慢查询的开启并捕获
  2. explain + 慢SQL分析
  3. show profile查询SQL在MySQL服务器里面的执行细节和生命周期情况
  4. SQL数据库服务器的参数调优

查询优化

永远小表驱动大表

举例：尽量使用第一种情况
for(int i = 5...){
    for(int j = 1000...){
        
    }
}
==============================
for(int i = 1000...){
  for(int j = 5...){
        
    }        
}

**优化原则：小表驱动大表，即小的数据集驱动大的数据集**
######################原理（RBO）#######################
select * from A where id in (select id from B)
等价于：
for select id from B
for select * from A where A.id = B.id
**当B表的数据集必须小于A表的数据集时，用in优于exists**

select * from A where exists (select 1 from B where B.id = A.id)
等价于
for select * from A
for select * from B where B.id = A.id
**当A表的数据集小于B表的数据集时，用exists优于in**
**注意：A表与B表的ID字段应建立索引**
  • EXISTS
SELECT ... FROM table WHERE EXISTS(subquery)

该语法可以理解为：**将主查询的数据，放到子查询中做条件验证，根据验证结果（TRUE或FALSE）来决定主查询的数据结果是否得以保留。**

  • 提示

    ① EXISTS(subquery)只返回TRUE或FALSE，因此子查询中的SELECT*也可以是SELECT 1或其他，官方说法是实际执行时会忽略SELECT清单，因此没有区别

    ② EXISTS子查询的实际执行过程可能经历 了优化而不是我们理解上的逐条对比，如果担忧效率问题，可进行实验检验以确定是否有效率问题

    ③ EXISTS子查询往往也可以用条件表达式、其他子查询或者JOIN来替代，何种最优需要具体问题具体分析

ORDER BY 关键字优化

为排序使用索引

  • MySQL两种排序方式：文件排序或扫描有序索引排序
  • MySQL能为排序与查询使用相同的索引
建索引：KEY a_b_c(a,b,c)
order by 能使用索引最左前缀
—— ORDER BY a
—— ORDER BY a,b
—— ORDER BY a,b,c
—— ORDER BY a DESC,b DESC,c DESC

如果WHERE使用索引的最左前缀定义为常量，则order by能使用索引
—— WHERE a = const ORDER BY b,c
—— WHERE a = const AND b = const ORDER BY c
—— WHERE a = const ORDER BY b,c
—— WHERE a = const AND b > const ORDER BY b,c

不能使用索引进行排序的情况
—— ORDER BY a ASC,b DESC,c DESC  /*排序不一致*/
—— WHERE g = const ORDER BY b,c  /*丢失a索引*/
—— WHERE a = const ORDER BY c    /*丢失b索引*/
—— WHERE a = const ORDER BY a,d  /*d不是索引的一部分*/
—— WHERE a in(...) ORDER BY b,c  /*对于排序来说，多个相等条件也是范围查询*/

GROUP BY 关键字优化

  • group by 实质是先排序后进行分组，遵照索引建的最佳左前缀
  • 当无法使用索引列，增大max_length_for_sort_data参数的设置 + 增大sort_buffer_size参数的设置
  • where高于having，能写在where限定的条件就不要去having限定了

慢查询日志

  • MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阙值的语句，具体值运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中
  • 具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。long_query_time的默认值为10，意思是运行10秒以上的语句
  • 由他来查看哪些SQL超出了我们的最大忍耐时间值，比如一条sql执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒的sql，结合之前explain进行全面分析

Show Profile

Show Profile是mysql提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL调优的测量。

分析步骤

  1. 是否支持，看看当前的mysql版本是否支持

    Show variables like 'profiling';

  2. 开启功能，默认是关闭，使用前需要开启

    set profiling = on;

  3. 运行SQL

    • select * from emp group by id%10 limit 150000;
    • select * from emp group by id%20 order by 5

  4. 查看结果，show profiles

  5. 诊断SQL，show profile cpu，block io for query上一步前面的问题SQL数字号码

type:
ALL          --显示所有的开销信息
BLOCK IO      --显示块IO相关开销
CONTEXT SWITCHES   --上下文切换相关开销
CPU          --显示CPU相关开销信息
IPC          --显示发送和接收相关开销信息
MEMORY        --显示内存相关开销信息
PAGE FAULTS       --显示页面错误相关开销信息
SOURCE         --显示和Source_function，   Source_file，Source_line相关的开销信息
SWAPS         --显示交换次数相关开销的信息
  1. 日常开发需要注意的结论

  • converting HEAP to MyISAM查询结果太大，内存都不够用了往磁盘上搬了

  • Creating tmp table 创建临时表

    ① 拷贝数据到临时表

    ② 用完再删除

  • Copying to tmp table on disk 把内存中临时表复制到磁盘，危险！！！

  • locked

全局查询日志

配置启用

在mysql的my.cnf中，设置如下：
#开启
general_log=1
#记录日志文件的路径
general_log_file=/path/logfile
#输出格式
log_output=FILE

编码启用

命令
set global general_log=1;
set global log_output='TABLE';
此后，你所编写的sql语句，将会记录到mysql库里的general_log表，可以用下面的命令查看
select * from mysql.general_log;

永远不要在生产环境开启这个功能

MySQL锁机制

概述

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

在数据库中，除传统的计算资源（如CPU、RAM、I/O等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。

锁的分类

  • 从对数据操作的类型（读 / 写）分

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

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

  • 从对数据操作的粒度分

    表锁

    行锁

三锁

表锁（偏读）

特点

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

读写锁案例分析

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

MySQL的表级锁有两种模式

表共享读锁（Table Read Lock）

表独占写锁（Table Write Lock）

锁类型 可否兼容 读锁 写锁
读锁 是 是 否
写锁 是 否 否 
结论：
结合上表，所有对MyISAM表进行操作，会有以下情况：
1、对MyISAM表的读操作（加读锁），不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其他进程的写操作。
2、对MyISAM表的写操作（加写锁），会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其他进程的读写操作。

简而言之，就是读锁会阻塞写，但是不会阻塞读。而写锁则会把读和写都堵塞。

表锁分析

【看看哪些表被加锁了】
show open tables;

【如何分析表锁定】
可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定
show status like 'table%';

Table_locks_immediate：产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁值加1。
Table_locks_waited：出现表级锁定争用而发生等待的次数（不能立即获取锁的次数，每等待一次锁值加1），此值高则说明存在着较严重的表级锁争用情况。

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

行锁

特点

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

InnoDB与MyISAM的最大不同有两点：

一是支持事务（TRANSACTION）

二是采用了行级锁

案例分析

无索引行锁升级为表锁

间隙锁危害

当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP）”，InnoDB也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key锁）。

危害

因为Query执行过程中通过范围查找的话，他会锁定整个范围内所有的索引键值，即使这个简直并不存在。

间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害。

select xxx... for update 锁定某一行后，其他的操作会被阻塞，直到锁定行的会话提交commit

案例总结

InnoDB存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会更高一些，但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的。当系统并发量较高的时候，InnoDB的整体性能和MyISAM相比就会有比较明显的优势了。

但是，InnoDB的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让InnoDB的整体性能表现不仅不能比MyISAM高，甚至可能会更差。

优化建议

  • 尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
  • 合理设计索引，尽量缩小锁的范围
  • 尽可能较少检索条件，避免间隙锁
  • 尽量控制事务大小，减少锁定资源量和时间长度
  • 尽可能低级别事务隔离

页锁

开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。（了解即可）

主从复制

复制的基本原理

slave会从master读取binlog来进行数据同步

三步骤 + 原理图

MySQL复制过程分成三步：
1、master将改变记录到二进制日志（binary log）。这些记录过程叫做二进制日志事件，binary log events;
2、slave将master的binary log events拷贝到它的中继日志（relay log）。
3、slave重做中继日志中的事件，将改变应用到自己的数据库中。MySQL复制是异步的且串行化的。

复制的基本原则

  • 每个slave只有一个master
  • 每个slave只能有一个唯一的服务器ID
  • 每个master可以有多个slave

复制的最大问题

延时

一主一从常见配置

mysql版本一致且后台以服务运行

主从都配置在[mysqld]结点下，都是小写

主机修改my.ini配置文件

  1. [必须] 主服务器唯一ID
  2. [必须] 启用二进制日志
  3. [可选] 启用错误日志
  4. [可选] 根目录
  5. [可选] 临时目录
  6. [可选] 数据目录
  7. read-only=0
  8. [可选] 设置不要复制的数据库
  9. [可选] 设置需要复制的数据库

从机修改my.cnf配置文件

  1. [必须] 从服务器唯一ID
  2. [可选] 启用二进制日志

因修改过配置文件，请主机 + 从机都重启后台mysql服务

主机从机都关闭防火墙

在Windows主机上建立账户并授权slave

GRANT REPLICATION SLAVE ON *.* TO 'zhangsan'@'192.168.xx.xxx' IDENTIFIED BY '123456';

flush privileges;

#查询master的状态
show master status;
记录下File和Position的值

在Linux从机上配置需要复制的主机

CHANGE MASTER TO MASTER_HOST='主机IP',
MASTER_USER='zhangsan',
MASTER_PASSWORD='123456',
MASTER_LOG_FILE='File名字',
MASTER_LOG_POS=Position数字;

#启动从服务器复制功能
start slave;

show slave status\G
#下面两个参数都是Yes，则说明主从配置成功！
Slave_IO_Running：Yes
Slave_SQL_Running：Yes

如何停止从服务复制功能

stop slave;
posted @ 2022-01-21 16:07  ArosyCat  阅读(27)  评论(0)  编辑  收藏  举报