MySQL

最新推荐文章于 2024-08-13 22:28:08 发布

enjoyCode10

最新推荐文章于 2024-08-13 22:28:08 发布

阅读量219

点赞数

文章标签： mysql 索引数据库

本文链接：https://blog.csdn.net/enjoyYun/article/details/107433679

版权

1. 流程

1）mysql启动以后，初始化模块就从系统配置文件中读取系统参数和命令参数，初始化整个系统，同时存储引擎也会启动；

2）初始化结束后，连接管理模块会监听客户端的连接请求，并将连接请求转发给线程管理模块去请求一个连接线程；

3）线程模块接到请求后会调用用户模块进行授权检查，通过授权以后会检查是否又空闲线程，如果有取出并与客户端连接，如果没有则新建立建立一个线程与客户端连接；

4）mysql请求分为两种，一种是需要命令解析和分发才能执行，另一种可以直接执行；不管哪种，如果开启了日志，那么日志模块会记录日志；

5）如果是Query类型的请求，会将控制权交给Query解析器，Query解析器检查是否Select类型，如果是则启动查询缓存模块，如果缓存命中则将缓存数据返回给连接线程模块，连接线程将数据传递到客户端；如果没有缓存或者不是一个可以缓存的查询，此时解析器会进行相应的处理，通过查询分发器给相关的处理模块；

6）如果解析器结果是DML/DDL，则交给变更模块；如果是检查、修复的查询交给表维护模块，如果是一条没有被缓存的语句，则交给查询优化器模块。实际上表变更模块又分为若干小模块，例如：insert处理器、delete处理器、update处理器、create处理器，以及alter处理器这些小模块来负责不同的DML和DDL。总之，查询优化器、表变更模块、表维护模块、复制模块、状态模块都是根据命令解析器的结果不同而分发给不同的类型模块，最后和存储引擎进行交互。

7）当一条命令执行完毕后，控制权都会还给连接线程模块，在上面各个模块处理过程中都依赖于核心API模块，比如：内存管理、文件I/O，字符串处理等。

2. 分层

连接层：

　　最上层是一些客服端和连接服务，包括sock通信和大多数基于客服端/服务端工具实现的类似于tcp/ip的通信，主要完成一些类似于连接处理、授权认证及相关安全的方案，在该层上引入了线程池的概念，为通过认证安全接入的客服端提供线程，同样在该层上可以实现基于SSL的安全的连接，服务器也会为安全接入的每个客户端验证它所具有的操作权限。

服务层：

　　第二层架构主要完成大多数的核心服务功能，如SQL接口，并完成缓存的查询，SQL的分析以及优化部分内置函数的执行，所有跨存储引擎的功能也在这一层实现，如过程、函数等，服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化如确定查询的顺序是否利用索引，最后生成相应的执行操作。

引擎层：

　　存储引擎层，存储引擎真正的负责了MySQL中数据的存储和提取，服务器通过API与存储引擎进行通信，不同的存储引擎具有功能不同。

存储层：

　　数据存储层主要是将数据存储在运行与裸设备的文件系统之上，并完成与存储引擎的交互。

3. 存储引擎

mysql> show engines;
--------------
show engines
--------------

+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
9 rows in set (0.00 sec)



mysql> show variables like '%storage_engine%';
--------------
show variables like '%storage_engine%'
--------------

+----------------------------------+--------+
| Variable_name                    | Value  |
+----------------------------------+--------+
| default_storage_engine           | InnoDB |
| default_tmp_storage_engine       | InnoDB |
| disabled_storage_engines         |        |
| internal_tmp_disk_storage_engine | InnoDB |
+----------------------------------+--------+
4 rows in set, 1 warning (0.04 sec)

MySQL存储引擎MyISAM与InnoDB的区别比较

4. 性能分析

4.1 Explain+SQL语句（id, type, key, rows, extra）

id: select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序。表的读取顺序（小表驱动大表）

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

SIMPLE：简单的select查询，查询中不包含子查询或者UNION。
PRIMARY：查询中包含任何复杂的子部分，最外层查询则被标记为PRIMARY。
SUBQUERY：在select或where中包含的子查询
DERIVED：在FROM列表中包含的子查询被标记为DERIVED（衍生）。MySQL会递归执行这些子查询，把结果放在临时表里。
UNION：若第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在FROM子句的子查询中，外层SELECT将被标记为：DERIVED。
UNION RESULT：从UNION表中获取结果的SELECT。

type：显示查询使用了何种访问类型，一般来说，得保证查询至少达到range级别，最好能达到ref。从最好到最差依此是：

system>const>eq_ref>ref>range>index>All

system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现，这个也可以忽略不计。
const：表示通过索引一次就找到了，const常用于primary key或则unique索引。因为只匹配一行数据，所以很快。如将主键置于where列表中，MySQL就能将该查询转换为一个常量。
eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描。
ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而，它可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体。
range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引。一般就是在你的where语句中出现了between、<、>、in等的查询。这种范围扫描索引扫描比全表扫描要好，因为它只需要开始于索引的某一点，而结束于另一点，不会扫描全部索引。
index：Full Index Scan，index与All区别为index类型只遍历索引树。这通常比All快，因为索引文件通常比数据文件小。（也就是说虽然all和index都是读全表，但index是从索引中读取的，而all是从硬盘中读的）
all：Full Table Scan，将遍历全表以找到匹配的行。

possible_keys：显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出。但不一定被查询实际使用。
key：实际使用的索引。如果为NULL，则没有使用索引。查询中若使用了覆盖索引，则该索引仅出现在key列表中，不会出现在possible_keys列表中。（覆盖索引：查询的字段与建立的复合索引的个数及字段一一吻合）

key_len：表示索引中使用的字节数，可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好。key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算而得，不是通过表内检索出的。
ref：显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值。查询中与其它表关联的字段，外键关系建立索引。

rows：根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数。表有多少行被优化器查询

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

Using filesort：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作成为“文件排序”。
Using temporary：使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by。如果用group by，分组字段一定要与索引的顺序个数一直。
Using index：表示相应的select操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错！如果同时出现using where，表明索引被用来执行索引键值的查找；如果没有同时出现using where，表明索引用来读取数据而非执行查找动作。
Using where：表明使用了where过滤。
Using join buffer：使用了连接缓存。
impossible where：where子句的值总是false，不能用来获取任何元组。（查询语句中where的条件不可能被满足，恒为False）
select tables optimized away：在没有GROUPBY子句的情况下，基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化。
distinct：优化distinct操作，在找到第一匹配的元组后即停止找相同值的动作。

4.2 覆盖索引：

Case-单表

Case-两表

左连接在右表建立索引，右连接在左表建立索引

Case-三表

5. 索引失效

全值匹配我最爱
最佳左前缀法则：如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。（带头大哥不能死，中间兄弟不能断，不然索引只能用到中断前的部分索引）
不在索引列上作任何操作（计算、函数、（自动or手动）类型转换），会导致索引失效而转向全表扫描
存储引擎不能使用索引中范围条件右边的列：使用范围的索引使用排序，此索引前的索引有效用于查找，此索引后的索引无效。中间索引别搞范围，要搞等值
尽量使用覆盖索引（只访问索引的查询（索引列和查询列一致或查询列在索引列范围内），这样可以用到using index），减少select *

mysql在使用不等于（！=或者<>）的时候无法使用索引会导致全表扫描
is null，is not null也无法使用索引
like以通配符开头(‘%abc…’)mysql索引失效会变成全表扫描的操作，like%加右边

问题：解决like ‘%字符串%’时索引不被使用的方法？利用覆盖索引解决两边%的优化问题。

字符串不加单引号索引失效：该问题同问题3，是索引列上做了类型转换！VARCHAR类型绝对不能失去单引号
少用or，用它来连接时会索引失效

优化总结口诀：

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

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

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

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

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

VAR引号不可丢，SQL高级也不难！

面试题TODO

6. 查询截取分析

6.1 分析

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

6.2 总结

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

6.3 查询优化

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

EXISTS的用法

SELECT … FROM table WHERE EXISTS(subquery)
该语法可以理解为：将主查询的数据，放到子查询中做条件验证，根据验证结果（TRUE或FALSE）来决定主查询的数据结果是否得以保留。
提示1：EXISTS（subquery）只返回TRUE或FALSE，因此子查询中的SELECT *也可以是SELECT 1或SELECT ‘X’，官方说法是实际执行时会忽略SELECT清单，因此没有区别。
提示2：EXISTS子查询的实际执行过程可能经过了优化而不是我们理解上的逐条对比，如果担心效率问题，可进行实际检验以确定是否有效率问题。
提示3：EXISTS子查询往往也可以用条件表达式/其他子查询或者JOIN来替代，何种最优需要具体问题具体分析。
总结：in后跟小表，exists后跟大表

ORDER BY关键字优化

ORDER BY子句，尽量使用Index方式排序，避免使用FileSort方式排序

MySQL支持两种方式的排序

FileSort和Index，Index效率高。FileSort方式效率较低。
Using Index，它指MySQL扫描索引本身完成排序。

ORDER BY满足两种情况，会使用Index方式排序：

ORDER BY语句使用索引最左前列
使用Where子句与ORDER BY子句条件列组合满足索引最左前列

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

如果不在索引列上，filesort有两种算法，mysql就要启动双路排序和单路排序：

双路排序：MySQL4.1之前是使用双路排序，字面意思就是两次扫描磁盘，最终得到数据。读取行指针和order by列，对他们进行排序，然后扫描已经排好序的列表，按照列表中的值重新从列表中读取对应的数据输出。从磁盘取排序字段，在buffer进行排序，再从磁盘读取其他字段。

单路排序：从磁盘读取查询需要的所有列，按照order by列在buffer对它们进行排序，然后扫描排序后的列表进行输出，它的效率更快一些，避免了第二次读取数据。并且把随机IO变成了顺序IO，但是它会使用更多的空间。

结论及引申出的问题：由于单路是后出的，总体而言好过双路；但是用单路有问题，本来想省一次I/O操作，反而导致了大量的I/O操作，反而得不偿失。（原因：数据的总大小超过sort_buffer的容量）

优化策略：

增大sort_buffer_size参数的设置
增大max_length_for_sort_data参数的设置

GROUP BY关键字优化

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

6.4 慢查询日志

是什么
- MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阈值的语句，具体指运行时间超过long_query_time值的SQL，则会被记录到慢查询日志中。
- long_query_time的默认值是10，意思是运行10秒以上的语句。
- 由它来查看哪些SQL超出了我们的最大忍耐时间值，比如一条sql执行超过5秒钟，我们就算慢SQL，希望能收集超过5秒的sql，结合之前的explain进行全面分析。
怎么玩
- 说明
  - 默认情况下，MySQL数据库没有开启慢查询日志，需要我们手动来设置这个参数。
  - 当然，如果不是调优需要的话，一般不建议启动该参数，因为开启慢查询日志会或多或少带来一定的性能影响。慢查询日志支持将日志记录写入文件。
- 查看是否开启及如何开启
  - 默认：SHOW VARIABLES LIKE ‘%slow_query_log%’;
  - 开启：set global slow_query_log=1;(重启后失效)
  - 那么开启了慢查询日志后，什么样的SQL才会记录到慢查询日志里面呢
Case
- 查看当前多少秒算慢：SHOW VARIABLES LIKE ‘long_query_time%’;
- 设置慢的阈值时间：set global long_query_time=3;
- 为什么设置后看不出变化（设置3之后，查询依然显示10）：
  - 需要重新连接或新开一个会话才能看到修改值。
  - SHOW VARIABLES LIEK ‘long_query_time%’;
  - show global variables like ‘long_query_time’;
- 记录慢SQL并后续分析
- 查询当前系统中有多少条慢查询记录：
  
  show global status like ‘%Slow_queries%’;
配置版

日志分析工具mysqldumpslow
- 在生产环境中，如果要手工分析日志，查找、分析SQL，显然是个体力活，MySQL提供了日志分析工具mysqldumpslow。
- 查看mysqldumpslow的帮助信息
  - mysqldumpslow --help
  - s：是表示按照何种方式排序
  - c：访问次数
  - I：锁定时间
  - r：返回记录
  - t：查询时间
  - al：平均锁定时间
  - ar：平均返回记录数
  - at：平均查询时间
  - t：即为返回前面多少条的数据
  - g：后边搭配一个正则匹配模式，大小写不敏感
- 工作常用参考

6.5 批量插入数据脚本

函数和存储过程

设置参数log_bin_trust_function_creators

创建函数和存储过程，保证每条数据都不同

create database bigData;
use bigData;

CREATE TABLE `dept` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `deptno` mediumint(8) unsigned NOT NULL DEFAULT '0',
  `dname` varchar(20) NOT NULL DEFAULT '',
  `loc` varchar(13) NOT NULL DEFAULT '',
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=GBK;

CREATE TABLE `emp` (
  `id` int(10) unsigned NOT NULL AUTO_INCREMENT,
  `empno` mediumint(8) unsigned NOT NULL DEFAULT '0',/*编号*/
  `ename` varchar(20) NOT NULL DEFAULT '',/*名字*/
  `job` varchar(9) NOT NULL DEFAULT '',/*工作*/
  `mgr` mediumint(8) unsigned NOT NULL DEFAULT '0',/*上级编号*/
  `hiredate` date NOT NULL,/*入职时间*/
  `sal` decimal(7,2) NOT NULL,/*薪水*/
  `comm` decimal(7,2) NOT NULL,/*红利*/
  `deptno` mediumint(8) unsigned NOT NULL DEFAULT '0',/*部门编号*/
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=GBK;


DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
	DECLARE char_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(char_str,FLOOR(1+RAND()*52),1));
	SET i = i+1;
	END WHILE;
	RETURN return_str;
END $$


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_emp(IN START INT(10),IN max_num INT(10))
BEGIN
	DECLARE i INT DEFAULT 0;
	SET autocommit = 0;#把自动提交关闭
	REPEAT
	SET i = i + 1;
	INSERT INTO emp (empno,ename,job,mgr,hiredate,sal,comm,deptno) VALUES((START+i),rand_string(6),'SALESMAN',0001,CURDATE(),2000,400,rand_num());
	UNTIL i = max_num
	END REPEAT;
	COMMIT;
END $$


DELIMITER $$
CREATE PROCEDURE insert_dept(IN START INT(10),IN max_num INT(10))
BEGIN
	DECLARE i INT DEFAULT 0;
	SET autocommit = 0;
	REPEAT
	SET i = i + 1;
	INSERT INTO dept (deptno,dname,loc) VALUES((START+i),rand_string(10), rand_string(8));
	UNTIL i = max_num
	END REPEAT;
	COMMIT;
END $$

调用存储过程

dept：
DELIMITER ;
CALL insert_dept(100, 10);


emp：
DELIMITER ;
CALL insert_emp(100001, 500000);

6.6 Show Profile

是什么：是mysql提供的可以用来分析当前会话中语句执行的资源消耗情况。可以用于SQL的调优的测量
默认情况下，参数处于关闭状态，并保存最近15次的运行结果
分析步骤
1. 是否支持，看看当前的mysql版本是否支持：show variables like ‘profiling’;
2. 开启功能，默认是关闭，使用前需要开启：set profiling = on;

运行SQL
- select * from emp group by id%10 limit 150000;
- select * from emp group by id%20 order by 5;
查看结果，show profiles;
诊断SQL，show profile cpu, block io for query [上一步前面的问题SQL数字号码];

参数备注

日常开发需要注意的结论
- converting HEAP to MyISAM：查询结果太大，内存都不够用了往磁盘上搬了
- Creating tmp table：创建临时表
  - 拷贝数据到临时表
  - 用完再删除
- Copying to tmp table on disk：把内存中临时表复制到磁盘，危险！！
- locked

6.7 全局查询日志

配置启用
编码启用
永远不要在生产环境开启这个功能！

7. 数据库锁

行锁分析（如何分析行锁定）：

通过检查InnoDB_row_lock状态变量来分析系统上的行锁的争夺情况：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：系统启动后到现在总共等待的次数。（等待总次数）

尤其是当等待次数很高，而且每次等待时长也不小的时候，我们就需要分析系统中为什么会有如此多的等待，然后根据分析结果着手制定优化计划。

优化建议

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

页锁

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

8. 题

8.1 为什么MySQL的索引要使用B+树而不是Hash或其它树形结构，如B树？

对于B+树，相比于Hash好处：

1.利用Hash需要把数据全部加载到内存中，如果数据量大，是一件很消耗内存的事，而采用B+树，是基于按照节点分段加载，由此减少内存消耗。

2.和业务场景有关，对于唯一查找（查找一个值），Hash确实更快，但数据库中经常查询多条数据，这时候由于B+数据的有序性，与叶子节点又有链表相连，他的查询效率会比Hash快的多。

（1、hash表只能匹配是否相等，不能实现范围查找；2、当需要按照索引进行order by时，hash值没办法支持排序；3、组合索引可以支持部分索引查询，如(a,b,c)的组合索引，查询中只用到了阿和b也可以查询的，如果使用hash表，组合索引会将几个字段合并hash，没办法支持部分索引；4、当数据量很大时，hash冲突的概率也会非常大；）

对于B+树，相比于B树的好处：

主要区别：B+树的叶子会包含所有的节点数据，并产生链表结构。

B树：