Mysql高级

Mysql的架构介绍

Mysql配置文件

Mysql主要配置文件：

  • 二进制日志log-bin：主从复制
  • 错误日志log-error：默认是关闭的,记录严重的警告和错误信息,每次启动和关闭的详细信息等.
  • 查询日志log：默认关闭,记录查询的sql语句，如果开启会减低mysql的整体性能，因为记录日志也是需要消耗系统资源的
  • 数据文件：
    • linux：默认路径：/var/lib/mysql
    • frm文件：存放表结构
    • myd文件：存放表数据
    • myi文件：存放表索引
  • 如何配置？Linux：/etc/my.cnf文件

Mysql逻辑架构介绍

总体概览：

和其它数据库相比，MySQL有点与众不同，它的架构可以在多种不同场景中应用并发挥良好作用。主要体现在存储引擎的架构上，插件式的存储引擎架构将查询处理和其它的系统任务以及数据的存储提取相分离。这种架构可以根据业务的需求和实际需要选择合适的存储引擎。

存储引擎是基于表的，而不是数据库。

  1. 连接层：最上层是一些客户端和连接服务，包含本地sock通信和大多数基于客户端/服务端工具实现的类似于tcplip的通信。主要完成一些类似于连接处理、授权认证、及相关的安全方案。在该层上引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样在该层上可以实现基于SSL的安全链接。服务器也会为安全接入的每个客户端验证它所具有的操作权限。
  2. 服务层：第二层架构主要完成大多少的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析和优化及部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程、函数等。在该层，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化如确定查询表的顺序，是否利用素引等，最后生成相应的执行操作。如果是select语句，服务器还会查询内部的缓存。如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的提升系统的性能。
  3. 引擎层：存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，这样我们可以根据自己的实际需要进行选取。后面介绍MyISAM和InnoDB
  4. 存储层：数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成与存储引擎的交互。

Untitled

  1. Connectors：指的是不同语言中与SQL的交互
  2. Management Serveices & Utilities：系统管理和控制工具
  3. Connection Pool: 连接池：管理缓冲用户连接，线程处理等需要缓存的需求。
    负责监听对 MySQL Server 的各种请求，接收连接请求，转发所有连接请求到线程管理模块。每一个连接上 MySQL Server 的客户端请求都会被分配（或创建）一个连接线程为其单独服务。而连接线程的主要工作就是负责 MySQL Server 与客户端的通信，
    接受客户端的命令请求，传递 Server 端的结果信息等。线程管理模块则负责管理维护这些连接线程。包括线程的创建，线程的 cache 等。
  4. SQL Interface: SQL接口。接受用户的SQL命令，并且返回用户需要查询的结果。比如select from就是调用SQL Interface
  5. Parser: 解析器。SQL命令传递到解析器的时候会被解析器验证和解析。解析器是由Lex和YACC实现的，是一个很长的脚本。在 MySQL中我们习惯将所有 Client 端发送给 Server 端的命令都称为 query ，在 MySQL Server 里面，连接线程接收到客户端的一个 Query 后，会直接将该 query 传递给专门负责将各种 Query 进行分类然后转发给各个对应的处理模块。主要功能：
    1. 将SQL语句进行语义和语法的分析，分解成数据结构，然后按照不同的操作类型进行分类，然后做出针对性的转发到后续步骤，以后SQL语句的传递和处理就是基于这个结构的。
    2. 如果在分解构成中遇到错误，那么就说明这个sql语句是不合理的
  6. Optimizer: 查询优化器。SQL语句在查询之前会使用查询优化器对查询进行优化。就是优化客户端请求的 query（sql语句） ，根据客户端请求的 query 语句，和数据库中的一些统计信息，在一系列算法的基础上进行分析，得出一个最优的策略，告诉后面的程序如何取得这个 query 语句的结果
    他使用的是“选取-投影-联接”策略进行查询。
  7. Cache和Buffer： 查询缓存。他的主要功能是将客户端提交 给MySQL 的 Select 类 query 请求的返回结果集 cache 到内存中，与该 query 的一个 hash 值 做一个对应。该 Query 所取数据的基表发生任何数据的变化之后， MySQL 会自动使该 query 的Cache 失效。在读写比例非常高的应用系统中， Query Cache 对性能的提高是非常显著的。当然它对内存的消耗也是非常大的。如果查询缓存有命中的查询结果，查询语句就可以直接去查询缓存中取数据。这个缓存机制是由一系列小缓存组成的。比如表缓存，记录缓存，key缓存，权限缓存等
  8. 存储引擎接口：存储引擎接口模块可以说是 MySQL 数据库中最有特色的一点了。目前各种数据库产品中，基本上只有 MySQL 可以实现其底层数据存储引擎的插件式管理。这个模块实际上只是 一个抽象类，但正是因为它成功地将各种数据处理高度抽象化，才成就了今天 MySQL 可插拔存储引擎的特色。

Mysql存储引擎：

Mysql存储引擎主要分为：MyISAM和InnoDB

  • 查看当前所使用的引擎命令：show engines;
  • MyISAM和InnoDB的区别

Untitled

索引优化分析

性能下降SQL慢、执行时间长、等待时间长的原因：

  1. 查询语句写的烂
  2. 索引失效(单值、复值)
  3. 关联查询太多join(设计缺陷或不得已的需求)
  4. 服务器调优及各个参数设置(缓冲\线程数等)

常见通用的join查询：

SQL执行顺序：

  • 手写

    Select distinct 
	<select_list>
FROM 
	<left_table> <join_type>
JOIN
	<right_table> ON <join_condition>
WHERE
	<where_condition>
GROUP BY
	<group_condition>
ORDER BY
	<order_by_condition>
LIMIT <limit_number>

  • 机读

    FROM <left_table>
ON <join_condition>
<join_type> JOIN <right_table>
WHERE <where_condition>
GROUP BY	<group_condition>
HAVING <having_condition>
SELECT
DISTINCT <select_list>
ORDER BY <order_by_condition>
LIMIT <limit_number>

  • 总结：

Untitled

七大JOIN类型：

Untitled

  • 注意：Mysql不支持FULL OUTER JOIN语法，但是可以通过union来达到全查询的要求。例如：

    SELECT
*
FROM
beauty
LEFT JOIN boys ON beauty.boyfriend_id = [boys.id](http://boys.id/)
UNION
SELECT
*
FROM
beauty
RIGHT JOIN boys ON beauty.boyfriend_id = [boys.id](http://boys.id/);

索引

索引是什么？MySQL官方对索引的定义为：索引(Index)是帮助MySQL高校获取数据的数据结构。简单来所索引是数据结构。

索引的特点：

  • 可以将索引简单理解为“排好序的快速查找数据结构”。
  • 结论：数据本身之外,数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法,这种数据结构就是索引。
  • 一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以文件形式存储在硬盘上
  • 我们平时所说的索引，如果没有特别指明，都是指B树(多路搜索树，并不一定是二叉树)结构组织的索引。其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。当然,除了B+树这种类型的索引之外，还有哈希索引(hash index)等。

索引的优劣势：

  • 优势：

    通过索引列对数据进行排序，降低数据排序成本，降低了CPU的消耗，提高了数据检索效率，降低了数据库的IO成本。

  • 劣势：

    • 实际上索引也是一张表，该表保存了主键和索引字段，并指向实体表的记录,所以索引列也是要占用空间的。
    • 虽然索引大大提高了查询速度，同时却会降低更新表的速度,如果对表INSERT,UPDATE和DELETE。因为更新表时，MySQL不仅要不存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。
    • 索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立优秀的索引，或优化查询语句。

索引的分类：

建议一张表索引不要超过5个，优先考虑复合索引

  • 单值索引：即一个索引只包含单个列，一个表可以有多个单列索引。
  • 唯一索引：索引列的值必须唯一，但允许有空值。
  • 复合索引：即一个索引包含多个列。

索引的语法：

  • 创建：

    CREATE [UNION] INDEX index_name ON table_name(column_name(lenght));

  • 删除：

    DROP INDEX [index_name] ON table_name;

  • 查看：

    SHOW INDEX FROM table_name\G

  • 使用ALTER命令：

Untitled

Mysql索引结构：

  • BTree索引：

Untitled

创建索引条件：

那些情况下需要创建索引：

索引组好建在经常需要查询的字段中。

  1. 主键自动建立唯一索引
  2. 频繁作为查询的条件的字段应该创建索引
  3. 查询中与其他表关联的字段，外键关系建立索引
  4. 频繁更新的字段不适合创建索引，因为每次更新不单单是更新了记录还会更新索引，加重IO负担。
  5. Where条件里用不到的字段不创建索引
  6. 单间/组合索引的选择问题（在高并发下倾向创建组合索引）
  7. 查询中排序的字段，排序字段若通过索引去访问将大大提高排序的速度
  8. 查询中统计或者分组字段

那些情况下不需要创建索引：

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

例如：

假如一个表有10万行记录，有一个字段A只有T和IF两种值，且每个宿的芬布概率天约为50%，那么对这种表A字段建索引一般不会提高数据库的查询速度。
索引的选择性是指索引列中不同值的数目与表中记录数的比。如果一个表中有2000条记录，表索引列有1980个不同的值，那么这个索引的选择性就是1980/2000=0.99。一个索引的选择性越接近于1，这个索引的效率就越高。

性能分析：

MySQL常见性能瓶颈：

  • CPU:CPU在饱和的时候一般发生在数据装入在内存或从磁盘上读取数据时候。
  • IO:磁盘I/O瓶颈发生在装入数据远大于内存容量时。
  • 服务器硬件的性能瓶颈：top,free,iostat和vmstat来查看系统的性能状态

Explain（查看执行计划）：

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

作用：

  • 表的读取顺序。
  • 数据读取的操作类型。
  • 那些索引可以使用。
  • 哪些索引被实际使用。
  • 表之间的引用。
  • 每张表有多少行被优化器查询。

Explain各个字段解释：

一般来说，得保证查询type达到range级别，最好达到ref.

覆盖索引：覆盖索引(Covering Index),一说为索引覆盖。**理解方式一：**就是select的数据列只用从索引中就能够取得，不必读取数据行，MySQL可以利用索引返回select列表中的字段，而不必根据索引再次读取数据文件,换句话说查询列要被所建的索引覆盖。
**理解方式二：**索引是高效找到行的一个方法，但是一般数据库也能使用索引找到一个列的数据，因此它不必读取整个行。毕竟索引叶子节点存储了它们索引的数据;当能通过读取索引就可以得到想要的数据，那就不需要读取行了。一个索引包含了(或覆盖了)满足查询结果的数据就叫做覆盖索引。

注意：如果要使用覆盖索引，一定要注意select列表中只取出需要的列，不可select*，因为如果将所有字段一起做索引会导致索引文件过大，查询性能下降。

  1. id：select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序，分为三种情况：
    1. id相同，执行顺序由上至下
    2. id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行。
    3. id相同不同，同时存在，id如果相同可以认定为是一组，从上往下顺序执行；在所有组中，id越大，优先级越高，越先执行。
  2. select_type：查询的类型，主要用于区别普通查询、联合查询、子查询等的复杂查询。查询类型有：
    1. SIMPLE：简单的select查询，查询中不包含子查询或者UNION
    2. PRIMARY：查询中若包含任何复杂的子部分，最外层查询则被标记为
    3. SUBQUERY：在SELECT或者WHERE列表中包含了子查询。
    4. DERIVED(衍生)：在FROM列表中包含的子查询被标记为DERIVED（衍生）MySQL会递归执行这些子查询，把结果放在临时表里。
    5. UNION：若第二个SELECT出现在UNION之后，则被标记为UNION;若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED。
    6. UNION RESULT：从UNION表获取结果的SELECT。
  3. table：显示这一行的数据是关于哪张表的。
  4. type：显示查询使用了何种类型从最好到最差依次是：system>const>eq_ref>ref>range>index>ALL
    1. system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，这个也可以忽略不计。
    2. const：表示通过索引一次就找到了，const用于比较primary key或者unique索引。因为只匹配一行数据，所以很快。如将主键至于where列表中，MySQL就能将该查询转换为一个常量。
    3. eq_ref：唯一性索引，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描。
    4. ref：非唯一索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以他应该属于查找和扫描的混合体。
    5. range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。
    6. index：Full Index Scan,index与ALL区别为index类型只遍历索引树。这通常比ALL快，因为索引文件通常比数据文件小。（也就是说虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）。
    7. all：FullTable Scan,将遍历全表以找到匹配的行。
  5. possible_keys：显示可能应用在这张表中的索引,一个或多个，但不一定被查询实际使用。
  6. key：实际使用的索引。如果为null则没有使用索引
  7. key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好。key_len显示的值为索引最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。
  8. ref：显示索引那一列被使用了，如果可能的话，是一个常数。那些列或常量被用于查找索引列上的值。
  9. rows：根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。
  10. extra：包含不适合在其他列中显示但十分重要的额外信息。额外信息类型有：
    1. Using filesort（重点）：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成排序操作称为“文件排序”。
    2. Using temporary（重点）：使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by 和分组查询 group by。
    3. USING index（重点）：表示相应的select操作中使用了覆盖索引（Coveing Index）,避免访问了表的数据行，效率不错！如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。
    4. Using where：表明使用了where过滤。
    5. Using join buffer：where子句的值总是false，不能用来获取任何元组。
    6. impossible where：where子句的值总是false，不能用来获取任何元组
    7. select tables optimized away：在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
    8. distinct：优化distinct，在找到第一匹配的元组后即停止找同样值的工作。

索引优化

索引优化注意项：

like KK%相当于=常量 %KK和%KK% 相当于范围。

定值、范围还是排序，一般order by是给个范围。

group by 基本上都需要进行排序，会有临时表产生。

  1. 创建的索引全值匹配，效率高
  2. 最佳左前缀法则：如果索引了多例，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
  3. 不在索引列上做任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描。
  4. 存储引擎不能使用索引中范围条件右边的列。
  5. 尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致）），减少使用select * 。
  6. mysql在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描
  7. is null,is not null 也会导致索引失效。
  8. like以通配符开头（'$abc...'）mysql索引失效会变成全表扫描操作，解决方案：
    1. 可以使用主键索引
    2. 使用覆盖索引，查询字段必须是建立覆盖索引字段
    3. 当覆盖索引指向的字段是varchar(380)及380以上的字段时，覆盖索引会失效！
  9. 字符串类型（varchar）不加单引号导致索引失效。
  10. 少用or,用它连接时会索引失效

优化总结口诀：

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

带头大哥不能死，中间兄弟不能断;

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

LIKE百分写最右，覆盖索引不写星;

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

VAR引号不可丢，SQL高级也不难!
  • 索引优化建议：

对于单键索引，尽量选择针对当前query过滤性更好的索引。

在选择组合索引的时候，当前Query中过滤性最好的字段在索引字段顺序中，位置越靠前越好。

在选择组合索引的时候，尽量选择可以能包含当前query中的where子句中更多字段的索引。

尽可能通过分析统计信息和调整query的写法来达到选择合适索引的目的。

查询截取分析

查询优化

永远小表驱动大表,类似嵌套循环Nested Loop

In和Exists：

  • Exists语法说明：

    #Exists语法：
SELECT * FROM table WHERE EXISTS (subquery)
#该语法可以理解为，将主查询的数据，放到子查询中做条件验证，根据验证结果(TRUE或FALSE)来决定主查询的数据结果是否得以保留。

#in语法：

  • Exists注意项

    • EXSTS (subquery)只返回TRUE或FALSE，因此子查询中的SELECT"也可以是SELECT1或selct"X，隆方说法是实际执行时会忽略SELECT洁单，因此没有区别
    • EXISTS子查询的实际执行过程可能经过了忧化而不是我们理解上的逐条对比，如果担忧效率问题，可进行实际检验以确定是否有效率问题。
    • EXISTS子查询往往也可以用条件表达式、其他子查询或者JOIN来昔代，何种最优需要具体问题具体分析

  • IN案例：

    select * from A where id in (select id from B)

等价于:

for select id from B 
for select * from where A.id = B.id;

  • 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;

结论：

  • 当B表的数据集小于A表的数据集时，用in优于exists。
  • 当A表的数据集小于B表的数据集时，用exists优于in。

Order by关键字优化：

ORDER BY子句，尽量使用Index方式排序，避免使用FileSort方式排序，**尽可能在索引列上完成排序操作，遵照索引建的最佳左前缀。**如果不在索引列上，filesort有两种算法：mysql就要启动双路排序和单路排序

Order by时select * 是大忌.

  • 双路排序和单路排序的介绍：MySQL4.1之前是使用双路排序，字面意思是两次扫描磁盘，最终得到数据。读取行指针和order by列，对他们进行排序，然后扫描已经排序好的列表，按照列表中的值重新从列表中读取对应的数据传输。取一批数据，要对磁盘进行两次扫描，众所周知，I\O是很耗时的，所以在mysql4.1之后，出现了第二张改进的算法，就是单路排序。单路排序从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据，并且把随机IO变成顺序IO，但是它会使用更多的空间，因为它把每一行都保存在内存中了。由于单路是后出来的，总体而言好过双路，但是用单路有问题。如下：
  • 单路排序面临的问题：
    • 在sort_buffer中，方法B（单路排序）比方法A（双路排序）要多占用很多空间，因为方法B是把所有字段都取出,所以有可能取出的数据的总大小超出了sort_buffer的容量，导致每次只能取sort_buffer容量大小的数据，进行排序（创建tmp文件，多路合并)，排完再取。
    • sort_buffer容量太小，再排……从而多次I/O。本来想省一次IO操作，反而导致了大量的IO操作，反而得不偿失。
  • 单路排序优化策略：
    • 增大sort_buffer_size参数的设置
    • 增大max_length_for_sort_data参数的设置
  • 如何提高Order By的速度：
    • Order by时select * 是一个大忌只Query需要的字段，这点非常重要。在这里的影响是:
      • 当Query的字段大小总和小于max_length_for_sort_data而且排序字段不是TEXT或BLOB类型时，会用改进后的算法——单路排序，否则用老算法——多路排序。
      • 两种算法的数据都有可能超出sort_buffer的容量，超出之后，会创建tmp文件进行合并排序，导致多次I/O，但是用单路排序算法的风险会更大一些,所以要提高sort_buffer_size.
    • 尝试提高sort_buffer_size不管用哪种算法，提高这个参数都会提高效率，当然，要根据系统的能力去提高，因为这个参数是针对每个进程的
      • 尝试提高max_length_for_sort_data提高这个参数，会增加用改进算法的概率。但是如果设的太高，数据总容量超出sort_buffer_size的概率就增大，明显症状是高的磁盘I/O活动和低的处理器使用率.

Group by关键字优化：

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

慢查询日志

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

慢查询日志简介：

默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。默认情况下，mysql数据库没有开启慢查询日志，需要我们手动来设置这个参数。当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。

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

慢查询日志如何使用？

  • 查看是否开启慢查询日志：

    SHOW VARIABLES LIKE '%slow_query_log%';

    Untitled

  • 开启慢查询日志：

    set global slow_query_log = 1;

  • 查看当前多少秒算慢

    SHOW VARIABLES LIKE 'long_query_time%';

  • 设置慢的阙值时间:

    SET GLOBAL long_query_time = 时间;

  • 查看当前有多少条慢记录条数：

    show global status like 'slow_queries';

日志分析工具mysqldumpshow：

mysqldumpshow是一个程序，不在mysql客户端中使用，是一个命令。

  • mysqldumpshow参数：
    • s:是表示按何种方式排序
    • c:访问次数
    • l:锁定时间
    • r:返回记录
    • t:查询时间
    • al:平均锁定时间
    • ar:平均返回记录数
    • at:平均查询时间
    • t:即为返回前面多少条的数据
    • g:后边搭配一个正则匹配模式，大小写不敏感的
  • 使用案例：
    • 得到返回记录集最多的10个SQL：mysqldumpslow -s r-t 10 lvar/lib/mysql/***-slow.log
    • 得到访问次数最多的10个SQL：mysqldumpslow -s c -t 10 /var/lib/mysql/***-slow.log
    • 得到按照时间排序的前10条里面含有左连接的查询语句：mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/***-slow.log
    • 另外建议在使用这些命令时结合│和more 使用，否则有可能出现爆屏情况mysqldumpslow -s r-t 10 /lar/lib/mysql/***-slow.log | more

批量插入数据

批量插入数据案例：

创建函数，假如报错:This function has none of-DETERMINISTIC......。就需要设置参数log_trust_function_createors

  • 设置log_trust_function_createors :

    #由于开启过慢查询日志，因为我们开启了bin-log,我们就必须为我们的function指定一个参数。
show variables like 'log_bin_trust_function_creators';

set global log_bin_trust_function_creators=1;

  • 创建批量插入函数：

    #创建生成随机字符串函数
delimiter $$
create function rand_string(n int) returns varchar(255)
begin
	declare chars_str varchar(100) default 'abcdefghijklmnopqrstuvwxyzabcdefghijklmnopqrstuvwxyz';
	declare return_str varchar(255) default '';
	declare i int default 0;
	while i < n do
	set return_str = concat(return_str,substring(chars_str,floor(1+rand()*52),1));
	set i = i + 1;
	end while;
	return return_str;
end $$

#创建生成随机数函数（用于创建部门编号，或随机id）
delimiter $$
create function rand_num() returns int(5)
begin 
	declare i int default 0;
	set i = floor(100+rand()*10);
	return i;
end $$

  • 创建存储过程：

    delimiter $$
create procedure insert_demo (in start_num int (10),in max_num int(10))
begin
	declare i int default 0;
	set autocommit = 0;
	repeat 
	set i = i + 1;
	insert into admin(id,username,`password`) values((start_num+i),rand_string(6),'ikart123');
	until i = max_num
	end repeat;
	commit;
end $$

Show profiles

是mysql提供可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优测量。默认情况下，参数处于关闭状态，并保存最近15次的运行结果。官网：http://dev.mysql.com/doc/refman/5.5/en/show-profile.html

如何使用❓

  • 查看时候否开启：

    	show variables like 'profiling%'

  • 开启profiling：

    set [global] profiling = on;

  • 诊断Sql：

    show profile cpu,block io for query Query_ID;

  • 可选参数

Untitled

当诊断结果出现如下结果，表明sql需要优化：

  1. converting HEAP to MyISAM : 查询结果太大，内存都不够用了往磁盘上搬了。
  2. Creating tmp table 创建临时表。即拷贝数据到临时表，用完再删除，十分消耗内存，导致sql变慢。
  3. Copying to tmp table on disk 把内存中临时表复制到磁盘，危险！！！
  4. locked：表被锁了而导致sql慢

全局查询日志

如何使用❓

永远不要在生产环境开启此功能！

  • 配置启用：

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

  • 编码启用：

    #开启日志
set [global] general_log = 1;
#这个无需设置，默认即是
set global log_output='TABLE';
#查看日志表
select * from mysql.general_log;

Mysql锁机制

锁是计算机协调多个进程或线程并发访问某一资源的机制。在数据库中，除传统的计算资源（如CPU、RAM、VO等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂。开销、加锁速度、死锁、粒度、并发性能只能就具体应用的特点来说哪种锁更合适。

锁的分类：

  1. 从数据操作的类型（读、写）分：
    1. 读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。
    2. 写锁（排它锁）：当前写操作没有完成前，它会阻断其他写锁和读锁。
  2. 从对数据操作的颗粒度：
    1. 表锁
    2. 行锁

表锁（偏读）：

  • 添加、查看、释放表锁：

    #手动增加表锁
lock table 表名 read(wirte),表名2 read(wirte), ...;
#查看表上加过的锁
show open tables;
#释放表锁
unlock tables;

Untitled

  • 总结：

    1. 当表某一session用户锁定，其他session用户就不能对该表操作（增删查改都不可以）。
    2. 那个session上的锁也只能由它来解锁，其他session无权解锁。
    3. 对MyISAM表的读操作（加读锁)，不会阻塞其他进程对同一表的读请求，但会阻塞对同一表的写请求。只有当读锁释放后，才会执行其它进程的写操作。
    4. 对MyISAM表的写操作（加写锁)，会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其它进程的读写操。

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

如何分析表锁定❓

  • 可以通过检查table_locks_waited和table_locks_immediate状态变量来分析系统上的表锁定。SQL：

    show status like 'table%';
    • table_locks_immediate:产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁值加1;
    • table_locks_waited:出现表级锁定争用而发生等待的次数(不能立即获取锁的次数，每等待一次锁值加1)，T此值高则说明存在着较严重的表级锁争用情况;

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

行锁（偏写）：

偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高。InnoDB与MyISAM的最大不同有两点：一是支持事务（TRANSACTION）;二是采用了行级锁。

varchar 类型数据不加 ' ' 导致系统自动转换类型, 行锁变表锁。大忌！

面试题：如何锁定一行？

  • 事务介绍：前面有介绍；

  • 行锁演示：

    1. session-1和session-2关闭自动提交：set commit = 0;

    2. 对数据进行更新，此时没有commit

      Untitled

    3. commit后：session-1 commit 后行锁消失 session-2 update完成。

      Untitled

    4. 此时session-1和session-2执行SQL：select * from innodb_lock_test 得到两个不同的答案：session-2的数据更新，session-1数据并未更新。

      Untitled

    5. session-2 commit后session-1 select * from innodb_lock_test; session-1数据已经更新

      Untitled

  • 面试题：如何锁定一行❓

    begin;
select * from table_name where id = column_number for update;
#在没有commit之前，其余操作锁定行的session都会阻塞等待。
#commit提交，行锁解除。
commit;

    Untitled

行锁-间隙锁：

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

  • 间隙锁的危害：因为Query执行过程中通过过范围查找的话，他会锁定整个范围内所有的索引键值，即使这个键值并不存在。间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，即使某些不存在的键值也会被无辜的锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害。

行锁分析：

  • 通过show status like 'innodb_row_lock%';查看行锁争夺情况，对各个状态量的说明如下:
    • Innodb_row_lock_current_waits：当前正在等待锁定的数量
    • Innodb_row_lock_time(重要)：从系统启动到现在锁定总时间长度(等待总时长）;
    • Innodb_row_lock_time_avg(重要)：每次等待所花平均时间（等待平均时长）;
    • Innodb_row_lock_time_max：从系统启动到现在等待最常的一次所花的时间;
    • Innodb_row_lock_waits(重要)：系统启动后到现在总共等待的次数(等待总次数);
  • 对于这5个状态变量，比较重要的主要是lnnodb_row_lock_time_avg，Innodb_row_lock_waits，lnnodb_row_lock_time这三项。尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手指定优化计划。

行锁优化建议：

Innodb存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些，但是在整体并发处理能力方面要远远优于MylISAM的表级锁定的。当系统并发量较高的时候，Innodb的整体性能和MyISAM相比就会有比较明显的优势了。但是，Innodb的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让Innodb的整体性能表现不仅不能比MyISAM高甚至可能会更差。

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

页锁（了解即可）：

开销和加锁时间界于表锁和行锁之间：会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。

主从复制

主从复制基本原理：

  • 步骤：
    1. master将改变记录到二进制日志（binary log）。这些记录过程叫做二进制日志时间，binary log events.
    2. slave会从master读取binlog来进行数据同步.
    3. slave将master的binary log ebents拷贝到它的中继日志（relay log）.
    4. slave重做中继日志中的时间，将改变应用到自己的数据库中。MySQL复制是异步的且串行化的
  • 原理图：

Untitled

  • 复制的基本原则：
    1. 每个slave只有一个master.
    2. 每个slave只能有唯一的服务器ID。
    3. 每个master可以有多个salve。
  • 复制最大的问题：延时。

一主一从常见配置：

尽量确保Mysql版本一致。

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

注意端口的开放。

%:是通配符

  • 主机配置，修改/etc/my.cnf文件
    • 【必须】主服务器唯一ID ：server-id =1
    • 【必须】启用二进制日志：log-bin=自己本地的路径/mysqlbin
    • 【可选】启动错误日志：log-err=自己本地的路径/mysqlerr
    • 【可选】根目录：basedir="自己本地路径"
    • 【可选】临时目录：tmpdir="自己的本地路劲"
    • 【可选】数据目录：datadir="自己本地路径/Data/"
    • 【可选】read-only=0：主机，读写都可以
    • 【可选】设置不要复制的数据库：binlog-lgnore-db=mysql
    • 【可选】设置需要复制的数据：binlog-do-db=需要复制的主数据库名字
  • 从机配置，修改/etc/my.cnf文件：
    • 【必须】从服务器唯一ID
    • 【可选】启用二进制文件
  • 主机创建账户为salve授权：

    • 创建账号

      #第一步（从机数据库ip为%的时候表示从机可以是任意ip）
GRANT REPLICATION SLAVE  ON*.* TO 'zhangsan'@'从机器数据库IP‘ IDENTIFIED BY '123456';

#第二步
flush privileges;

#第三步查看master状态
show master status；

    • 记录下File和Position的值（如果一次集群没成功需要再次查看master，因为file和position的值可能发生了变化）

  • 从机连接到主机：

    • 第一步：

      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。Slave_IO_Running:Yes，Slave_SQL_Running:Yes两个参数都是YES，则说明主从配置成功！
  • 停止主从复制：stop slave;

Mysql其他

Mysql注意项：

Varchar类型数据加单引号，不加头打飞?。

在使用DBeaver或Navicat等数据库客户端软件时，每执行一句sql代表一个会话，执行完sql会话结束，特别要注意因为随着会话消失而消失的设置。

Mysql中的\g和\G的区别：

\g：在MySQL的sql语句后加上\g，效果等同于加上定界符，一般默认的定界符是分号;

\G：在MySQL的sql语句后加上\G，表示将查询结果进行按列打印，可以使每个字段打印到单独的行。即将查到的结构旋转90度变成纵向；

Mysql管理：

  • mysql的配置文件路径在/etc/my.cnf
  • my.cnf中保存着mysql的各种参数配置，数据文件存储路径和错误日志保存路径等。

Untitled

参考资料

  • 官方文档：

MySQL 8.0 Reference Manual

  • 阿里云RDS Mysql 参考文档：

RDS MySQL 数据库

Q.E.D.

在等花开，等春天来.