学习完本文章的好处：即使只是一个开发工程师，只是 MySQL 的用户，在了解了一个个系统模块的原理后，再来使用它，感觉是完全不一样的。当在代码里写下一行数据库命令的时候，我就能想到它在数据库端将怎么执行，它的性能是怎么样的，怎样写能让我的应用程序访问数据库的性能最高。进一步，哪些数据处理让数据库系统来做性能会更好，哪些数据处理在缓存里做性能会更好，我心里也会更清楚。在建表和建索引的时候，我也会更有意识地为将来的查询优化做综合考虑，比如确定是否使用递增主键、主键的列怎样选择。学习资料：《高性能mysql》《mysql必知必回》《redis入门指南》《redis实战》《mysql实战45讲》(强烈推荐) 慕课网教学视频
mysql是最流行的关系型数据库管理系统之一，在Java企业级开发中非常常用，因为 MySQL 是开源免费的，并且方便扩展，其默认端口号是3306。
linux下查看安装目录 ps -ef | grep mysql查看MySQL提供的所有存储引擎 show engines;1、日进制日志log-bin //主从复制 2、错误日志log-error //默认关闭，用于记录严重的警告和错误信息，每次启动和关闭的详情 3、查询日志log //默认关闭，记录查询的sql语句，开启会降低mysql性能 4、数据文件 //路径/var/lib/mysql frm文件：存放表结构 mym文件：存放表数据 myi文件：存放表索引 5、如何配置 // /etc/my.cnf文件
理论上达到第三范式是符合要求的，但是一般在生产环境下为了数据查询方便，数据会有一定的冗余，也就是说一般达到第二范式即可。 范式的种类：
第一范式：字段不可再分; 例如标准中心后台类目表中的tags字段就不满足该范式要求，给维护带来极大的困难；第二范式：满足第一范式的前提下，不能出现部分依赖。如果某个字段依赖复合主键的一部分字段，则称之为对主键的部分依赖 例如：两个字段才能确定一个主键，就不满足第二范式；第三范式：满足第二范式的前提下，不能出现传递依赖，某个字段依赖于主键，而有其他字段依赖于该字段。例如：C依赖于B，B依赖于主键，则C对主键存在传递依赖关系（实际开发中，达不到第三范式的要求也是可以的）1、mysql组件 连接器： 身份认证和权限相关(登录 MySQL 的时候)。 查询缓存: 执行查询语句的时候，会先查询缓存（MySQL 8.0 版本后移除，因为这个功能不太实用）。 分析器: 没有命中缓存的话，SQL 语句就会经过分析器，分析器说白了就是要先看你的 SQL 语句要干嘛，再检查你的 SQL 语句语法是否正确。 优化器： 按照 MySQL 认为最优的方案去执行。 执行器: 执行语句，然后从存储引擎返回数据。
2、简单来说 MySQL 主要分为 Server 层和存储引擎层： Server 层：主要包括连接器、查询缓存、分析器、优化器、执行器等，所有跨存储引擎的功能都在这一层实现，比如 存储过程、触发器、视图，函数 等(我司一般不使用)，还有一个通用的日志模块 binlog 日志模块。 存储引擎：主要负责数据的存储和读取，采用可以替换的插件式架构，支持 InnoDB、MyISAM、Memory 等多个存储引擎，其中 InnoDB 引擎有自有的日志模块 redolog 模块。现在最常用的存储引擎是 InnoDB，它从 MySQL 5.5.5 版本开始就被当做默认存储引擎了。
3、查询语句 我们的 sql 可以分为两种，一种是查询，一种是更新（增加，更新，删除）。我们先分析下查询语句，语句如下：
select * from tb_student A where A.age='18' and A.name=' 张三 ';结合上面的说明，我们分析下这个语句的执行流程：
先检查该语句是否有权限，如果没有权限，直接返回错误信息，如果有权限，在 MySQL8.0 版本以前，会先查询缓存，以这条 sql 语句为 key 在内存中查询是否有结果，如果有直接缓存，如果没有，执行下一步。
通过分析器进行词法分析，提取 sql 语句的关键元素，比如提取上面这个语句是查询 select，提取需要查询的表名为 parana_items，需要查询所有的列，查询条件是这个表的 id=‘1’。然后判断这个 sql 语句是否有语法错误，比如关键词是否正确等等，如果检查没问题就执行下一步。
接下来就是优化器进行确定执行方案，上面的 sql 语句，可以有两种执行方案：
a.先查询学生表中姓名为“张三”的学生，然后判断是否年龄是 18。 b.先找出学生中年龄 18 岁的学生，然后再查询姓名为“张三”的学生。 那么优化器根据自己的优化算法进行选择执行效率最好的一个方案（优化器认为，有时候不一定最好）。那么确认了执行计划后就准备开始执行了。
进行权限校验，如果没有权限就会返回错误信息，如果有权限就会调用数据库引擎接口，返回引擎的执行结果。
4、更新语句 以上就是一条查询 sql 的执行流程，那么接下来我们看看一条更新语句如何执行的呢？sql 语句如下：
update tb_student A set A.age='19' where A.name=' 张三 ';我们来给张三修改下年龄，在实际数据库肯定不会设置年龄这个字段的，不然要被技术负责人打的。其实条语句也基本上会沿着上一个查询的流程走，只不过执行更新的时候肯定要记录日志啦，这就会引入日志模块了，MySQL 自带的日志模块式 binlog（归档日志,可以理解为git中的master分支） ，所有的存储引擎都可以使用，我们常用的 InnoDB 引擎还自带了一个日志模块 redo log（重做日志，可以理解为git中的release分支），我们就以 InnoDB 模式下来探讨这个语句的执行流程。流程如下：
先查询到张三这一条数据，如果有缓存，也是会用到缓存。然后拿到查询的语句，把 age 改为 19，然后调用引擎 API 接口，写入这一行数据，InnoDB 引擎把数据保存在内存中，同时记录 redo log，此时 redo log 进入 prepare 状态，然后告诉执行器，执行完成了，随时可以提交。执行器收到通知后记录 binlog，然后调用引擎接口，提交 redo log 为提交状态。更新完成。5、为什么要用两个日志模块，用一个日志模块不行吗?
这是因为最开始 MySQL 并没与 InnoDB 引擎( InnoDB 引擎是其他公司以插件形式插入 MySQL 的) ，MySQL 自带的引擎是 MyISAM，但是我们知道 redo log 是 InnoDB 引擎特有的，其他存储引擎都没有，这就导致会没有 crash-safe 的能力(crash-safe 的能力即使数据库发生异常重启，之前提交的记录都不会丢失)，binlog 日志只能用来归档。
并不是说只用一个日志模块不可以，只是 InnoDB 引擎就是通过 redo log 来支持事务的。那么，又会有同学问，我用两个日志模块，但是不要这么复杂行不行，为什么 redo log 要引入 prepare 预提交状态？这里我们用反证法来说明下为什么要这么做？
先写 redo log 直接提交，然后写 binlog，假设写完 redo log 后，机器挂了，binlog 日志没有被写入，那么机器重启后，这台机器会通过 redo log 恢复数据，但是这个时候 binlog 并没有记录该数据，后续进行机器备份的时候，就会丢失这一条数据，同时主从同步也会丢失这一条数据。先写 binlog，然后写 redo log，假设写完了 binlog，机器异常重启了，由于没有 redo log，本机是无法恢复这一条记录的，但是 binlog 又有记录，那么和上面同样的道理，就会产生数据不一致的情况。如果采用 redo log 两阶段提交的方式就不一样了，写完 binglog 后，然后再提交 redo log 就会防止出现上述的问题，从而保证了数据的一致性。那么问题来了，有没有一个极端的情况呢？假设 redo log 处于预提交状态，binlog 也已经写完了，这个时候发生了异常重启会怎么样呢？ 这个就要依赖于 MySQL 的处理机制了，MySQL 的处理过程如下：
判断 redo log 是否完整，如果判断是完整的，就立即提交。如果 redo log 只是预提交但不是 commit 状态，这个时候就会去判断 binlog 是否完整，如果完整就提交 redo log, 不完整就回滚事务。这样就解决了数据一致性的问题。
6、总结
MySQL 主要分为 Server 层和引擎层，Server 层主要包括连接器、查询缓存、分析器、优化器、执行器，同时还有一个日志模块（binlog），这个日志模块所有执行引擎都可以共用,redolog 只有 InnoDB 有。引擎层是插件式的，目前主要包括，MyISAM,InnoDB,Memory 等。查询语句的执行流程如下：权限校验（如果命中缓存）—》查询缓存—》分析器—》优化器—》权限校验—》执行器—》引擎更新语句执行流程如下：分析器----》权限校验----》执行器—》引擎—redo log(prepare 状态)—》binlog—》redo log(commit状态)参考资料：林晓斌《MySQL 实战45讲》
1、mysql视图：是一种虚拟存在的表(对视图的修改不影响基本表)，通常是有一个表或者多个表的行或列的子集，具有和物理表相同的功能，可以对视图进行增，删，改，查等操作 作用:重用sql语句，简化复杂的sql操作，不必知道他的查询细节；保护数据，提高安全性 应用场景：1、多个地方用到同样的查询结果，该查询结果使用的sql语句较为复杂 如：查询邮箱中包含a字符的员工名、部门名和工种信息 /查询各部门的平均工资级别 /查询平均工资最低的部门信息 2、游标:是一个存储在MySQL服务器上的数据库查询，它不是一条 SELECT语句，而是被该语句检索出来的结果集。 在存储了游标之后，应用程序可以根据需要滚动或浏览其中的数据。游标主要用于交互式应用，其中用户需要滚动屏幕上的数据，并对数据进行浏览或做出更改. 使用游标的原因：使用简单的 SELECT语句，例如，没有办法得到第一行、下一行或前 10行，也不存在每次一行地处理所有行的简单方法（相对于成批地处理它们）。有时，需要在检索出来的行中前进或后退一行或多行。这就是使用游标的原因
我们公司目前没有用到视图和游标。
1、什么是存储过程？存储过程是事先经过编译并存储在数据库中的一段SQL语句的集合 业务逻辑可以封装存储过程中，这样不仅容易维护，而且执行效率也高。
2、好处：减少数据在数据库和应用服务器之间的传输，提高了数据处理的效率
3、MySQL存储过程
-- 创建MySQL存储过程 drop procedure if exists pr_add; （备注：如果存在pr_add的存储过程，则先删掉 计算两个数之和（备注：实现计算两个整数之和的功能） create procedure pr_add (a int,b int) begin declare c int; if a is null then set a = 0; end if; if b is null then set b = 0; end if; set c = a + b; select c as sum; //存储过程默认自动提交 -- 调用 MySQL 存储过程 call pr_add(10, 20);4、存储过程和存储函数的特点和区别
一般来说，存储过程实现的功能要复杂一点，而函数的实现的功能针对性比较强。对于存储过程来说可以返回参数，而函数只能返回值或者表对象;函数必须有返回值,而过程没有;函数可以嵌入到SQL语句中执行.而过程不行;函数可以单独执行.而过程必须通过execute执行.其实我们可以将比较复杂的查询写成函数.然后到存储过程中去调用这些函数5、存储过程与SQL的对比？
存储过程只在创建时进行编译，以后每次执行存储过程都不需再重新编译，而一般 SQL 语句每执行一次就编译一次，所以使用存储过程可提高数据库执行效率;存储过程调用时只需用提供存储过程名和必要的参数信息，从而可降低网络的流量。通过存储过程能够使没有权限的用户在控制之下间接地存取数据库，从而确保数据的安全当SQL语句有变动时，可以只修改数据库中的存储过程而不必修改代码，从而极大的提高了程序的可移植性。6、你觉得存储过程和SQL语句该使用哪个？
1、在一些高效率或者规范性要求比较高的项目，建议采用存储过程；2、对于一般项目建议采用参数化命令方式，是存储过程与SQL语句一种折中的方式；3、对于一些算法要求比较高，涉及多条数据逻辑，建议采用存储过程。触发器是一种与表操作有关的数据库对象，当触发器所在表上出现指定事件时，将调用该对象，即表的操作事件触发表上的触发器的执行。
CREATE TRIGGER trigger_name //触发器名称 trigger_time //触发时机 trigger_event ON tbl_name //触发事件 表名 FOR EACH ROW trigger_stmt //触发器程序体，可以是一句SQL语句，或者用 BEGIN 和 END 包含的多条语句 可以建立6种触发器 时机，即： BEFORE INSERT、BEFORE UPDATE、BEFORE DELETE、AFTER INSERT、AFTER UPDATE、AFTER DELETE create trigger tri_stuInsert after insert on student for each row begin declare c int; set c = (select stuCount from class where classID = new.classID); update class set stuCount = c + 1 where classID = new.classID; 作用？ 1、触发器可通过数据库中的相关表实现级联更改；通过级联引用完整性约束可以更有效地执行这些更改； 2、触发器可以强制比用 CHECK 约束定义的约束更为复杂的约束； 3、触发器还可以强制执行业务规则； 4、触发器也可以评估数据修改前后的表状态，并根据其差异采取对策。解决乱码的核心思想是统一编码。utf8编码
SQL注入：通过在Web表单中输入（恶意）SQL语句得到一个存在安全漏洞的网站上的数据库，而不是按照设计者意图去执行SQL语句
1、SQL注入攻击实例 比如，在一个登录界面，要求输入用户名和密码，可以这样输入实现免帐号登录：用户名: ‘or 1 = 1-- 密码：然后后面加两个-，这意味着注释，它将后面的语句注释，让他们不起作用。 又如：“ or “”=” 那么，拼接出的SQL字符串就变成了下面的条件，OR的存在导致输入什么名字都是符合条件的。 Select * from use_info where username = “input_usr_name” and password = “” or “” = “”举例：当执行的sql为 select * from user where username = “admin” or “a”=“a”时，sql语句恒成立，参数admin毫无意义。
防止sql注入的方式：
jdbc中，使用预编译语句：如，select * from user where username = ？，sql语句语义不会发生改变，sql语句中变量用？表示， 即使传递参数时为“admin or ‘a’= ‘a’”，也会把这整体做一个字符创去查询。Mybatis 框架中的mapper 方式中的 # （占位符）也能很大程度的防止sql注入（$（拼接符）无法防止sql注入）聚合函数是对一组值进行计算并返回单一的值的函数，它经常与select 语句中的 group by 子句一同使用。 a. avg()：返回的是指定组中的平均值，空值被忽略。 b. count()：返回的是指定组中的项目个数。 c. max()：返回指定数据中的最大值。 d. min()：返回指定数据中的最小值。 e. sum()：返回指定数据的和，只能用于数字列，空值忽略。 f. group by()：对数据进行分组，对执行完group by之后的组进行聚合函数的运算，计算每一组的值。 最后用having去掉不符合条件的组，having子句中的每一个元素必须出现在select列表中（只针对于mysql）
外连接： 左连接（左外连接）：以左表作为基准进行查询，左表数据会全部显示出来，右表如果和左表匹配的数据则显示相应字段的数据，如果不匹配则显示为null。 右连接（右外连接）：以右表作为基准进行查询，右表数据会全部显示出来，左表如果和右表匹配的数据则显示相应字段的数据，如果不匹配则显示为null。 全连接：先以左表进行左外连接，再以右表进行右外连接。 内连接： 显示表之间有连接匹配的所有行。
使用子查询，先查出所有包含XYZ的行，再使用Not Exists字段对数据进行过滤
spark相关知识点，暂时用不上
一个 SQL 执行的很慢，我们要分两种情况讨论：
1、大多数情况下很正常，偶尔很慢，则有如下原因 (1)数据库在刷新脏页，例如 redo log 写满了需要同步到磁盘。 (2)执行的时候，遇到锁，如表锁、行锁。
2、这条 SQL 语句一直执行的很慢，则有如下原因。 (1)没有用上索引：例如该字段没有索引；由于对字段进行运算、函数操作导致无法用索引。 (2)数据库选错了索引。
如何排查：
-- 判断是否真的在等待锁 show processlist -- 查询索引的基数和实际是否符合,可以重新来统计索引的基数 analyze table t;1、当只要一行数据时使用limit 1 查询时如果已知会得到一条数据，这种情况下加上 limit 1 会增加性能。因为 mysql 数据库引擎会在找到一条结果停止搜索，而不是继续查询下一条是否符合标准直到所有记录查询完毕。
2、选择正确的数据库引擎 Mysql中有两个引擎MyISAM和InnoDB，每个引擎有利有弊。 MyISAM ：适用于一些大量查询的应用，但对于有大量写功能的应用不是很好。甚至你只需要update一个字段整个表都会被锁起来。而别的进程就算是读操作也不行要等到当前update操作完成之后才能继续进行。另外，MyISAM对于select count(* )这类操作是超级快的。 InnoDB的趋势会是一个非常复杂的存储引擎，对于一些小的应用会比MyISAM慢，但是支持“行锁”，所以在写操作比较多的时候会比较优秀。并且，它支持很多的高级应用，例如：事务管理。
3、用not exists代替not in Not exists用到了连接能够发挥已经建立好的索引的作用，not in不能使用索引。Not in是最慢的方式要同每条记录比较，在数据量比较大的操作不建议使用这种方式。
4、对操作符的优化，尽量不采用不利于索引的操作符 如：in not in is null is not null <> 等 某个字段总要拿来搜索，为其建立索引： Mysql中可以利用alter table语句来为表中的字段添加索引，语法为：alter table表明add index(字段名)；
1、怎么发现有问题的sql语句？(通过MySQL慢查询日志对有效率问题的SQL进行监控)
MySQL的慢查询日志是MySQL提供的一种日志记录，它用来记录在MySQL中响应时间超过阀值的语句，具体指运行时间超过long_query_time 值的SQL，则会被记录到慢查询日志中.long_query_time的默认值为10，意思是运行10s以上的语句。慢查询日志的相关参数如下所示log_output:··（file|Table）通过MySQL的慢查询日志，我们可以查询出执行的次数多占用的时间长的SQL;可以通过pt_query_disgest(一种mysql慢日志分析工具)分析Rows examine(MySQL执行器需要检查的行数)项去找出IO大的SQL以及发现未命中索引的SQL，对于这些SQL，都是我们优化的对象2、通过explain查询和分析SQL的执行计划
使用 EXPLAIN 关键字可以知道MySQL是如何处理你的SQL语句的，以便分析查询语句或是表结构的性能瓶颈。通过explain命令可以得到表的读取顺序、数据读取操作的操作类型、哪些索引可以使用、哪些索引被实际使用、表之间的引用以及每张表有多少行被优化器查询等问题。3、where子句中可以对字段进行 null 值判断吗？ 最好不要给数据库留null,含有空值的列很难进行查询优化(你应该用0、一个特殊的值或者一个空串代替空值)
4、select * from admin left join log on admin.admin_id = log.admin_id where log.admin_id>10 如何优化?（政采云） 可以把join优化为子查询：select * from (select * from admin where admin_id>10) T1 left join log on T1.admin_id = log.admin_id。 使用JOIN 时候，应该用小的结果驱动大的结果（left join 左边表结果尽量小，如果有条件应该放到左边先处理，right join 同理反向）同时尽量把牵涉到多表联合的查询拆分多个query
5、limit的基数比较大时使用 between 政采云考到了哈哈，幸好我早有准备
例如：select * from admin order by admin_id limit 100000,10 用了id主键做索引更快 - 使用order by + between and优化为：select * from admin where admin_id between 100000 and 100010 order by admin_id。（不要使用*，想查询什么数据就全部查出来） - 也可使用子查询优化为：select id,title from collect where id >= (select id from collect order by id limit 90000,1) limit 10; 解释原因：limit 10000,10的意思是扫描满足条件的10010行，扔掉前面的10000行，返回最后20行优化方案： 1、子查询优化法 先找出第一条数据，然后大于等于这条数据的id就是要获取的数据 缺点：数据必须是连续的，可以说不能有where条件，where条件会筛选数据，导致数据失去连续性
2、尽量使用复合索引 对于有where条件，又想走索引用limit的，必须设计一个索引，将where放在第一位，limit用到的主键放在第2位，而且只能select主键。
3、使用id限定优化 这种方式假设数据表的id是连续递增的，则我们根据查询的页数和查询的记录数可以算出查询的id的范围，可以使用id between and来查询
select * from orders_history where type = 2 and id between 1000000 and 1000100 limit 100; 还有另外一种写法： select * from orders_history where id >= 1000001 limit 100;6、尽量避免在列上做运算，这样导致索引失效
-- 例如： select * from admin where year(admin_time) > 2014; -- 优化为： select * from admin where admin_time > '2014-01-01′总结：
1、当一个数据库表过于庞大，limit offset，length中offset值过大，则sql查询语句会非常缓慢，你需要增加order by，并且order by字段需要建立索引2、如果使用子查询去优化limit，子查询必须是连续的，从某种意义来讲，子查询不应该有where条件，where会过滤数据，使数据失去连续性3、如果你查询的记录比较大，并且数据传输量比较大，比如包含了text类型的field，则可以通过建立子查询 SELECT id,title,content FROM items WHERE id IN (SELECT id FROM items ORDER BY id limit 900000,10) -- 如果limit语句的offset较大，你可以通过传递pk键值来减少offset=0，这个主键最好是int型并且auto_increment SELECT * FROM users WHERE uid > 456891 ORDER BY uid LIMIT 0, 10;7、order by关键字优化 尽量使用index，否则会使用filesort，效率低
8、group by关键字优化 先排序后进行分组 按照索引建的最佳左前缀
9、开启慢查询日志 日志分析工具mysql dump slow
10、show profile 是mysql提供的分析当前会话中语句执行的资源消耗情况，可以用于sql调优的测量
1）数据库设计方面 1、对查询进行优化，应尽量避免全表扫描，首先应考虑在where及order by涉及的列上建立索引 2、应尽量避免在where子句中对字段进行null值判断，否则将导致引擎放弃使用索引而进行全表扫描 3、并不是所有索引对查询都有效，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时,查询可能不会去利用索引，如一表中有字段sex，male、female几乎各一半，那么即使在sex上建了索引也对查询效率起不了作用 4、索引并不是越多越好，索引固然可以提高相应的 select 的效率，但同时也降低了insert及update的效率 5、应尽可能的避免更新索引数据列，因为索引数据列的顺序就是表记录的物理存储顺序，一旦该列值改变将导致整个表记录的顺序的调整，会耗费相当大的资源 *不再需要的数据行，可以设置他的status为false 6、尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时会逐个比较字符串中每一个字符，而对于数字型而言只需要比较一次就够了 7、尽可能的使用 varchar/nvarchar 代替 char/nchar ，因为变长字段存储空间小 8、不再索引列上做任何操作(计算、函数、类型装换)，会导致全表扫表
2) SQL语句方面 1、应尽量避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描; 2、应尽量避免在 where 子句中使用 or 来连接条件，否则将导致引擎放弃使用索引而进行全表扫描; 3、in 和 not in 也要慎用，否则会导致全表扫描; 4、
