mysql-基础知识

三、性能监控

1.QPS

一台 MySQL 数据库，大致处理能力的极限是，每秒一万条左右的简单 SQL，这里的“简单 SQL”，指的是类似于主键查询这种不需要遍历很多条记录的 SQL。根据服务器的配置高低，可能低端的服务器只能达到每秒几千条，高端的服务器可以达到每秒钟几万条，所以这里给出的一万 TPS 是中位数的经验值。考虑到正常的系统不可能只有简单 SQL，所以实际的 TPS 还要打很多折扣。

我的经验数据，一般一台 MySQL 服务器，平均每秒钟执行的 SQL 数量在几百左右，就已经是非常繁忙了，即使看起来 CPU 利用率和磁盘繁忙程度没那么高，你也需要考虑给数据库“减负”了

2.表大小

你在编写一条查询语句的时候，可以依据你要查询数据表的数据总量，估算一下这条查询大致需要遍历多少行数据。

如果遍历行数在百万以内的，只要不是每秒钟都要执行几十上百次的频繁查询，可以认为是安全的。

遍历数据行数在几百万的，查询时间最少也要几秒钟，你就要仔细考虑有没有优化的办法。

遍历行数达到千万量级和以上的，我只能告诉你，这种查询就不应该出现在你的系统中。当然我们这里说的都是在线交易系统，离线分析类系统另
说。遍历行数在千万左右，是 MySQL 查询的一个坎儿。MySQL 中单个表数据量，也要尽量控制在一千万条以下，最多不要超过二三千万这个量级。原因也很好理解，对一个千万级别的表执行查询，加上几个 WHERE 条件过滤一下，符合条件的数据最多可能在几十万或者百万量级，这还可以接受。但如果再和其他的表做一个联合查询，遍历的数据量很可能就超过千万级别了。

所以，每个表的数据量最好小于千万级别。

三、核心技术

1.常见日期格式

date和datetime类型区别

区别1：

①date类型可用于需要一个日期值而不需要时间部分时；

②datetime类型:可用于需要同时包含日期和时间信息的值。

区别2：

①date：MySQL 以 ‘YYYY-MM-DD’ 格式检索与显示date值；

②datetime:MySQL 以 'YYYY-MM-DD HH:mm:ss’格式检索与显示 DATETIME 类型。

区别3：

①date类型：支持的范围是 ‘1000-01-01’ 到’9999-12-31’；

②datetime类型:支持的范围是’1000-01-0100:00:00’ 到 ‘9999-12-3123:59:59’。

2.mysql驱动版本对比

老版本驱动，一般指5.x版本：

driverClassName: com.mysql.jdbc.Driver

新版本驱动，一般指8.x版本：

driverClassName: com.mysql.cj.jdbc.Driver

总结：使用mysql数据库，代码中使用的数据库版本要和数据库服务版本保持一致，并且数据库驱动准确无误才可以正常创建连接

mysql中in 和exists 区别。

exists：
exists对外表用loop逐条查询，每次查询都会查看exists的条件语句，当 exists里的条件语句能够返回记录行时(无论记录行是的多少，只要能返回)，条件就为真，返回当前loop到的这条记录，反之如果exists里的条 件语句不能返回记录行，则当前loop到的这条记录被丢弃，exists的条件就像一个bool条件，当能返回结果集则为true，不能返回结果集则为 false

not exists：
not exists与exists相反，也就是当exists条件有结果集返回时，loop到的记录将被丢弃，否则将loop到的记录加入结果集

in
in是把外表和内表做hash连接，先查询内表，再把内表结果与外表匹配，对外表使用索引（外表效率高，可用大表），而内表多大都需要查询，不可避免，故外表大的使用in，可加快效率。

@see MySQL中exists和in的区别及使用场景 https://www.cnblogs.com/xiaoxiong-kankan/p/7928153.html

思考

1.一张自增表里面总共有 7 条数据，删除了最后 2 条数据，重启 MySQL 数据库，又插入了一条数据，此时 ID 是几？

6
一般情况下，我们创建的表的类型是InnoDB，如果新增一条记录（不重启mysql的情况下），这条记录的id是8；但是如果重启（上文中提到的）MySQL的话，这条记录的ID是5。因为InnoDB表只把自增主键的最大ID记录到内存中，所以重启数据库或者对表OPTIMIZE操作，都会使最大ID丢失。
但是，如果我们使用表的类型是MylSAM，那么这条记录的ID就是8。因为MylSAM表会把自增主键的最大ID记录到数据文件里面，重启MYSQL后，自增主键的最大ID也不会丢失。

@see Java面试题（四）：数据库 https://www.jianshu.com/p/4ff8add187a4

2. limit深度分页问题

普通的limit m,n，m表示偏移量，n表示返回条数，当m偏移量很大时，就需要扫描过多的表数据，例如limit 1000000,100，查询就需要扫描1000100条，然后舍弃掉不符合条件的前1000000条，效率自然也就低了。

我们大概有3种方式来解决limit分页慢的问题，请根据自己的数据量和业务需求进行选择：
1、id连续的情况下，直接用where id>500的方式来解决
2、id不连续的情况下，使用select id from test limit 5000000,1来获取limit起始值，但是实际测试发现效果不大
3、id不连续的情况下，新建一个order_no字段用来计算起始值，效率很高，但是需要解决order_no更新的问题
4、限制查询页数

参考：数据量大时limit分页慢的问题https://www.jianshu.com/p/b40852891fb0

3.慢查询优化思想？

在业务系统中，除了使用主键进行的查询，其他的都会在测试库上测试其耗时，慢查询的统计主要由运维在做，会定期将业务中的慢查询反馈给我们。

慢查询的优化首先要搞明白慢的原因是什么？是查询条件没有命中索引？是load了不需要的数据列？还是数据量太大？

所以优化也是针对这三个方向来的。

1.首先分析语句，看看是否load了额外的数据，可能是查询了多余的行并且抛弃掉了，可能是加载了许多结果中并不需要的列，对语句进行分析以及重写。

2.分析语句的执行计划，然后获得其使用索引的情况，之后修改语句或者修改索引，使得语句可以尽可能的命中索引。

3.如果对语句的优化已经无法进行，可以考虑表中的数据量是否太大，如果是的话可以进行横向或者纵向的分表。

参考文献

1.mysql中文官网 https://www.mysqlzh.com/
1.oracle中if/else功能的实现的3种写法 https://www.cnblogs.com/yangzhilong/archive/2013/04/03/2998282.html
2.mysql 概念和逻辑架构 https://www.cnblogs.com/andy6/p/5789254.html
3.MySql两种存储引擎的区别 https://www.cnblogs.com/wangdake-qq/p/7358322.html