MySQL数据库（三）

1.关联查询

（1）交叉连接（CROSS JOIN）

SELECT * FROM A,B(,C)
或者
SELECT * FROM A CROSS JOIN B (CROSS JOIN C)
#没有任何关联条件，结果是笛卡尔积，结果集会很大，没有意义，很少使用
SELECT * FROM A,B WHERE A.id=B.id
或者
SELECT * FROM A INNER JOIN B ON A.id=B.id
#多表中同时符合某种条件的数据记录的集合，INNER JOIN可以缩写为JOIN

（2）内连接分为三类

等值连接：ON A.id=B.id

不等值连接：ON A.id > B.id

自连接：SELECT * FROM A T1 INNER JOIN A T2 ON T1.id=T2.pid

（3）外连接（LEFT JOIN/RIGHT JOIN）

左外连接：LEFT OUTER JOIN, 以左表为主，先查询出左表，按照ON后的关联条件匹配右表，没有匹配到的用NULL填充，可以简写成LEFT JOIN

右外连接：RIGHT OUTER JOIN, 以右表为主，先查询出右表，按照ON后的关联条件匹配左表，没有匹配到的用NULL填充，可以简写成RIGHT JOIN

（4）联合查询（UNION与UNION ALL）

SELECT * FROM A UNION SELECT * FROM B UNION …

就是把多个结果集集中在一起，UNION前的结果为基准，需要注意的是联合查询的列数要相等，相同的记录行会合并

如果使用UNION ALL，不会合并重复的记录行

例题

有2张表，1张R、1张S，R表有ABC三列，S表有CD两列，表中各有三条记录。

R表

A B C
a1 b1 c1
a2 b2 c2
a3 b3 c3

S表
C D
c1 d1
c2 d2
c4 d3

（1）交叉连接

笛卡尔乘积：

select * from r,s

结果：就是r的每一行乘以s的每一行

A B C C D
a1 b1 c1 c1 d1
a2 b2 c2 c1 d1
a3 b3 c3 c1 d1
a1 b1 c1 c2 d2
a2 b2 c2 c2 d2
a3 b3 c3 c2 d2
a1 b1 c1 c4 d3
a2 b2 c2 c4 d3
a3 b3 c3 c4 d3

(2)内连接

SELECT * FROM r INNER JOIN s ON r.id=s.id

结果：

A   B   C  C   D
a1 b1 c1 c1 d1
a2 b2 c2 c2 d2

（3）左连接

select r.,s. from r left join s on r.c=s.c

结果：实际上就是完全保存了左表r，并且新增了s的所有字段，添加r.c=s.c相同的数据

A B C C D
a1 b1 c1 c1 d1
a2 b2 c2 c2 d2
a3 b3 c3

（4)右连接

select r.,s. from r right join s on r.c=s.c

结果：保存了右表所有数据，并新增了左表所有字段，添加r.c=s.c相同的数据

A B C C D
a1 b1 c1 c1 d1
a2 b2 c2 c2 d2
         c4 d3

左右连接的作用？

比如：
person表

orders表

题目：查询出所有的人，以及他们的订单信息。

如果只采用内查询：

select * from person inner join order on person.pid=order.pid

结果：只会查询出两个人的数据，pid=2的人的信息没有被查询出来

所以我们采用左右连接的方式来查询，即使pid=2的人的订单信息不存在，我们也会将该条数据查询出来

SELECT
    persons.last_name,
    persons.first_name,
    orders.order_no
FROM
    persons
LEFT JOIN orders ON persons.pid = orders.pid

结果：

2.子查询有哪几种情况？

(1)子查询是单行单列的情况：结果集是一个值，父查询使用：=、 <、 > 等运算符

select max(salary) from employee 就是子查询，结果为单行单列

-- 查询工资最高的员工是谁？ 
select  * from employee where salary=(select max(salary) from employee);   

（2）子查询是多行单列的情况：结果集类似于一个数组，父查询使用：in 运算符

select * from table_name where doc_id IN ('1dba', 'c20a', '907b')

（3）子查询是多行多列的情况：结果集类似于一张虚拟表，不能用于where条件，用于select子句中做为子表

将 (select * from employee where join_date > ‘2011-1-1’) e 当成一个虚拟的表e

查询出每个部门2011年以后入职的所有员工的信息

-- 1) 查询出2011年以后入职的员工信息
-- 2) 查询所有的部门信息，与上面的虚拟表中的信息比对，找出所有部门ID相等的员工。
select * from dept d,  (select * from employee where join_date > '2011-1-1') e where e.dept_id =  d.id;    

3.varchar与char的区别

这就好比arraylist与array的区别

char的特点

（1）char表示定长字符串，长度是固定的；

（2）如果插入数据的长度小于char的固定长度时，则用空格填充；

（3）因为长度固定，所以存取速度要比varchar快很多，甚至能快50%，但正因为其长度固定，所以会占据多余的空间，是空间换时间的做法；

（4）对于char来说，最多能存放的字符个数为255，和编码无关

varchar的特点

（1）varchar表示可变长字符串，长度是可变的；

（2）插入的数据是多长，就按照多长来存储；

（3）varchar在存取方面与char相反，它存取慢，因为长度不固定，但正因如此，不占据多余的空间，是时间换空间的做法；

（4）对于varchar来说，最多能存放的字符个数为65535

总之，结合性能角度（char更快）和节省磁盘空间角度（varchar更小），具体情况还需具体来设计数据库才是妥当的做法。

4. FLOAT和DOUBLE的区别是什么？

• FLOAT类型数据可以存储至多8位十进制数，并在内存中占4字节。
• DOUBLE类型数据可以存储至多18位十进制数，并在内存中占8字节。

5.drop、delete与truncate的区别

delete是一行一行删除的，删除速度慢。truncate和drop删除速度快。

6.如何定位及优化SQL语句的性能问题？创建的索引有没有被使用到?或者说怎么才可以知道这条语句运行很慢的原因？

对于低性能的SQL语句的定位，最重要也是最有效的方法就是使用执行计划，MySQL提供了explain命令来查看语句的执行计划。我们知道，不管是哪种数据库，或者是哪种数据库引擎，在对一条SQL语句进行执行的过程中都会做很多相关的优化，对于查询语句，最重要的优化方式就是使用索引。而执行计划，就是显示数据库引擎对于SQL语句的执行的详细情况，其中包含了是否使用索引，使用什么索引，使用的索引的相关信息等。

比如：

EXPLAIN select * from t_team 
right JOIN t_user 
ON 
t_user.id=t_team.id

结果：显示出该条sql语句的执行计划

各个字段：
（1）id
select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序 。id值越大优先级越高，越先被执行 。id相同：执行顺序由上至下 。
（2）select_type
查询的类型，主要是用于区分普通查询、联合查询、子查询等复杂的查询
（3）type
访问类型，sql查询优化中一个很重要的指标，结果值从好到坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

一般来说，好的sql查询至少达到range级别，最好能达到ref

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

7.SQL的生命周期？

（1）服务器与数据库建立一个连接

（2）数据库拿到请求sql

（3）解析并生成执行计划，执行

（4）读取数据到内存并进行逻辑处理

（5）通过步骤一的连接，发送结果到客户端

（6）关掉连接，释放资源

8.大表数据查询，怎么优化？

（1）优化shema、sql语句：首先查看，有没有致使索引失效,进行全表查询的地方，例如:<、>等,看看能不能去除,如果不能,就在进行这类操作前先进行其他条件的过滤。

（2）对于常用且不重复的字段建立索引

（2）第二加缓存，memcached, redis；

（3）主从复制，读写分离；

（4）垂直拆分，根据你模块的耦合度，将一个大的系统分为多个小的系统，也就是分布式系统；

（5）水平切分，针对数据量大的表，这一步最麻烦，最能考验技术水平，要选择一个合理的sharding key, 为了有好的查询效率，表结构也要改动，做一定的冗余，应用也要改，sql中尽量带sharding key，将数据定位到限定的表上去查，而不是扫描全部的表；

9.mysql如何分页？

LIMIT 子句可以被用于强制 SELECT 语句返回指定的记录数。LIMIT 接受一个或两个数字参数。参数必须是一个整数常量。如果给定两个参数，第一个参数指定第一个返回记录行的偏移量，第二个参数指定返回记录行的最大数目。初始记录行的偏移量是 0(而不是 1)

SELECT * FROM table LIMIT 5,10; // 检索记录行 6-15 
SELECT * FROM table LIMIT 95,-1; // 检索记录行 96-end. 
SELECT * FROM table LIMIT 5; //检索前 5 个记录行 

10.超大分页怎么处理？

数据库分页也是在数据库里写查询语句，不同的是查询的都是指定条数到指定条数的数据，不是一次性把数据全查出来。

超大的分页一般从两个方向上来解决.

（1）数据库层面,这也是我们主要集中关注的(虽然收效没那么大),类似于select * from table where age > 20 limit 1000000,10这种查询其实也是有可以优化的余地的. 这条语句需要load1000000数据然后基本上全部丢弃,只取10条当然比较慢.

这是为什么呢?因为limit 1000000,10的语法实际上是mysql查找到前1000010条数据,之后丢弃前面的1000000行,这个步骤其实是浪费掉的.

• 索引覆盖。我们可以修改为select * from table where id in (select id from table where age > 20 limit 1000000,10).这样虽然也load了一百万的数据,但是由于子查询只查询了索引id，mysql的查询完全命中索引的时候,称为覆盖索引,是非常快的,因为查询只需要在索引上进行查找,之后可以直接返回,而不用再回数据表拿数据.因此我们可以先查出索引的ID,然后根据Id拿数据.

• 连续id优化。同时如果ID连续的话,我们还可以select * from table where id > 1000000 limit 10,效率也是不错的,优化的可能性有许多种,但是核心思想都一样,就是减少load的数据.

（2）从需求的角度减少这种请求…主要是不做类似的需求(直接跳转到几百万页之后的具体某一页.只允许逐页查看或者按照给定的路线走,这样可预测,可缓存)以及防止ID泄漏且连续被人恶意攻击.

解决超大分页,其实主要是靠缓存,可预测性的提前查到内容,缓存至redis等k-V数据库中,直接返回即可.

11.业务系统里面的sql耗时？统计过慢查询吗？对慢查询都怎么优化过？

在业务系统中，除了使用主键进行的查询，其他的我都会在测试库上测试其耗时.

慢查询的优化首先要搞明白慢的原因是什么？

• 是查询条件没有命中索引
• 是load了不需要的数据列
• 还是数据量太大

所以优化也是针对这三个方向来的:
（1）分析语句的执行计划，然后获得其使用索引的情况，之后修改语句或者修改索引，使得语句可以尽可能的命中索引。

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

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

12.优化查询过程中的数据访问

（1）访问数据太多导致查询性能下降

（2）确定应用程序是否在检索大量超过需要的数据，可能是太多行或列

（3）确认MySQL服务器是否在分析大量不必要的数据行

（4）避免犯如下SQL语句错误

（5）查询不需要的数据。解决办法：使用limit解决

（6）多表关联返回全部列。解决办法：指定列名

（7）总是返回全部列。解决办法：避免使用SELECT *

（8）重复查询相同的数据。解决办法：可以缓存数据，下次直接读取缓存

（9）是否在扫描额外的记录。解决办法：

（10）使用explain进行分析，如果发现查询需要扫描大量的数据，但只返回少数的行，可以通过如下技巧去优化：

（11）使用索引覆盖扫描，把所有的列都放到索引中，这样存储引擎不需要回表获取对应行就可以返回结果。

（12）改变数据库和表的结构，修改数据表范式

（13）重写SQL语句，让优化器可以以更优的方式执行查询。

13.数据库设计上的优化？为什么要优化数据库？

• 系统的吞吐量瓶颈往往出现在数据库的访问速度上
• 随着应用程序的运行，数据库的中的数据会越来越多，处理时间会相应变慢
• 数据是存放在磁盘上的，读写速度无法和内存相比

14.数据库结构优化

一个好的数据库设计方案对于数据库的性能往往会起到事半功倍的效果。

需要考虑数据冗余、查询和更新的速度、字段的数据类型是否合理等多方面的内容。

（1）将字段很多的表分解成多个表

对于字段较多的表，如果有些字段的使用频率很低，可以将这些字段分离出来形成新表。也就是按字段进行分表

因为当一个表的数据量很大时，会由于使用频率低的字段的存在而变慢。

（2）增加中间表

对于需要经常联合查询的表，可以建立中间表以提高查询效率。
通过建立中间表，将需要通过联合查询的数据插入到中间表中，然后将原来的联合查询改为对中间表的查询。

（3）增加冗余字段
设计数据表时应尽量遵循范式理论的规约，尽可能的减少冗余字段，让数据库设计看起来精致、优雅。但是，合理的加入冗余字段可以提高查询速度。
表的规范化程度越高，表和表之间的关系越多，需要连接查询的情况也就越多，性能也就越差。

注意：
冗余字段的值在一个表中修改了，就要想办法在其他表中更新，否则就会导致数据不一致的问题。

15.大表怎么优化？某个表有近千万数据，CRUD比较慢，如何优化？分库分表了是怎么做的？分表分库了有什么问题？有用到中间件么？他们的原理知道么？

当MySQL单表记录数过大时，数据库的CRUD性能会明显下降，一些常见的优化措施如下：

（1）限定数据的范围：务必禁止不带任何限制数据范围条件的查询语句。比如：根据用户需求，我们当用户在查询订单历史的时候，我们可以控制在一个月的范围内。

（2）读/写分离：经典的数据库拆分方案，主库负责写，从库负责读；

（3）缓存：使用MySQL的缓存，另外对重量级、更新少的数据可以考虑使用应用级别的缓存；

（4）还有就是通过分库分表的方式进行优化，主要有垂直分表和水平分表

垂直分表
**简单来说垂直拆分是指数据表列的拆分，把一张列比较多的表拆分为多张表。**根据数据库里面数据表的相关性进行拆分。 例如，用户表中既有用户的登录信息又有用户的基本信息，可以将用户表拆分成两个单独的表，甚至放到单独的库做分库。

把主键和一些列放在一个表，然后把主键和另外的列放在另一个表中。可以使得行数据变小，但是主键会冗余。

水平分表

**保持数据表结构不变，通过某种策略存储数据分片。这样每一片数据分散到不同的表或者库中，达到了分布式的目的。**水平拆分可以支撑非常大的数据量。

水平拆分是指数据表行的拆分，表的行数超过200万行时，就会变慢，这时可以把一张的表的数据拆成多张表来存放，这样就可以避免单一表数据量过大对性能造成影响。但由于表的数据还是在同一台机器上，其实对于提升MySQL并发能力没有什么意义，所以 水平拆分最好分库，将其存放在不同的机器上 。

水平拆分能够 支持非常大的数据量存储，应用端改造也少，但 分片事务难以解决 ，跨界点Join性能较差，逻辑复杂。

补充一下数据库分片的两种常见方案：

（1）客户端代理：分片逻辑在应用端，封装在jar包中，通过修改或者封装JDBC层来实现。当当网的 Sharding-JDBC 、阿里的TDDL是两种比较常用的实现。

（2）中间件代理：在应用和数据中间加了一个代理层。分片逻辑统一维护在中间件服务中。我们现在谈的 Mycat 、360的Atlas、网易的DDB等等都是这种架构的实现。

16.分库分表后面临的问题？

(1) 事务支持 分库分表后，就成了分布式事务了。如果依赖数据库本身的分布式事务管理功能去执行事务，将付出高昂的性能代价；如果由应用程序去协助控制，形成程序逻辑上的事务，又会造成编程方面的负担。

（2）跨库join
只要是进行切分，跨节点Join的问题是不可避免的。但是良好的设计和切分却可以减少此类情况的发生。解决这一问题的普遍做法是分两次查询实现。在第一次查询的结果集中找出关联数据的id,根据这些id发起第二次请求得到关联数据。

（3）跨节点的count,order by,group by以及聚合函数问题 这些是一类问题，因为它们都需要基于全部数据集合进行计算。多数的代理都不会自动处理合并工作。解决方案：与解决跨节点join问题的类似，分别在各个节点上得到结果后在应用程序端进行合并。和join不同的是每个结点的查询可以并行执行，因此很多时候它的速度要比单一大表快很多。但如果结果集很大，对应用程序内存的消耗是一个问题。

（4）ID问题，一旦数据库被切分到多个物理结点上，我们将不能再依赖数据库自身的主键生成机制。一方面，某个分区数据库自生成的ID无法保证在全局上是唯一的；另一方面，应用程序在插入数据之前需要先获得ID,以便进行SQL路由. 一些常见的主键生成策略

对于分库分表后id主键的处理方法：

https://blog.csdn.net/zty1317313805/article/details/89675703

因为要是分成多个表之后，每个表都是从 1 开始累加，这样是不对的，我们需要一个全局唯一的 id 来支持。

生成全局 id 有下面这几种方式：

• UUID：不适合作为主键，因为太长了，并且无序不可读，查询效率低。比较适合用于生成唯一的名字的标示比如文件的名字。
• 数据库自增 id : 两台数据库分别设置不同步长，生成不重复ID的策略来实现高可用。这种方式生成的 id 有序，但是需要独立部署数据库实例，成本高，还会有性能瓶颈。
• 利用 redis 生成 id : 性能比较好，灵活方便，不依赖于数据库。但是，引入了新的组件造成系统更加复杂，可用性降低，编码更加复杂，增加了系统成本。
• Twitter的snowflake算法（又名“雪花算法”）
  Twitter的snowflake算法解决了分布式系统生成全局ID的需求，生成64位的Long型数字，组成部分：

1. 接下来41位是毫秒级时间，41位的长度可以表示69年的时间
2. 5位datacenterId，5位workerId。10位的长度最多支持部署1024个节点
3. 最后12位是毫秒内的计数，12位的计数顺序号支持每个节点每毫秒产生4096个ID序列
   
   所以总的来说就是：毫秒时间戳+节点id+序列id（每毫秒的序列id）
   由于毫秒数在高位，生成的ID整体上按时间趋势递增；

（5）跨分片的排序分页
一般来讲，分页时需要按照指定字段进行排序。当排序字段就是分片字段的时候，我们通过分片规则可以比较容易定位到指定的分片。而当排序字段不是分片字段的时候，情况就会变得比较复杂了。为了最终结果的准确性，我们需要在不同的分片节点中将数据进行排序并返回，并将不同分片返回的结果集进行汇总和再次排序，最后再返回给用户。

分库分表的分页类似于归并排序算法，从每个库中取出最小的，或者最大的N条记录，然后进行最大最小比较即可。

17.MySQL的复制原理以及流程

主从复制：将主数据库中的DDL和DML操作通过二进制日志（BINLOG）传输到从数据库上，然后将这些日志重新执行（重做）；从而使得从数据库的数据与主数据库保持一致。

主从复制的作用

（1）主数据库出现问题，可以切换到从数据库。

（2）可以进行数据库层面的读写分离。

（3）可以在从数据库上进行日常备份。

MySQL主从复制解决的问题

（1）数据分布：随意开始或停止复制，并在不同地理位置分布数据备份

（2）负载均衡：降低单个服务器的压力

（3）高可用和故障切换：帮助应用程序避免单点失败

（4）升级测试：可以用更高版本的MySQL作为从库

MySQL主从复制工作原理

（1）在主库上把数据更改记录到二进制日志

（2）从库将主库的日志复制到自己的中继日志

（3）从库读取中继日志的事件，将其存放到从库数据中

基本原理流程，3个线程以及之间的关联

主：binlog线程——记录下所有改变了数据库数据的语句，放进master上的binlog中；

从：io线程——在使用start slave 之后，负责从master上拉取 binlog 内容，放进自己的relay log中；

从：sql执行线程——执行relay log中的语句；

18.读写分离有哪些解决方案？

读写分离是依赖于主从复制，而主从复制又是为读写分离服务的。因为主从复制要求slave(从数据库)不能写只能读。

方案一：使用mysql-proxy代理

优点：直接实现读写分离和负载均衡，不用修改代码，master和slave用一样的帐号。

缺点：降低性能， 不支持事务

方案二：使用AbstractRoutingDataSource+aop+annotation在dao层决定数据源。

如果采用了mybatis， 可以将读写分离放在ORM层，比如mybatis可以通过mybatis plugin拦截sql语句，所有的insert/update/delete都访问master库，所有的select 都访问salve库，这样对于dao层都是透明。plugin实现时可以通过注解或者分析语句是读写方法来选定主从库。不过这样依然有一个问题， 也就是不支持事务， 所以我们还需要重写一下DataSourceTransactionManager， 将read-only的事务扔进读库， 其余的有读有写的扔进写库。

方案三

使用AbstractRoutingDataSource+aop+annotation在service层决定数据源，可以支持事务.

缺点：类内部方法通过this.xx()方式相互调用时，aop不会进行拦截，需进行特殊处理。

19.数据表损坏的修复方式有哪些？

使用 myisamchk 来修复，具体步骤：

1）修复前将mysql服务停止。

2）打开命令行方式，然后进入到mysql的/bin目录。

3）执行myisamchk –recover 数据库所在路径/*.MYI

使用repair table 或者 OPTIMIZE table命令来修复，REPAIR TABLE table_name 修复表 OPTIMIZE TABLE table_name 优化表 REPAIR TABLE 用于修复被破坏的表。OPTIMIZE TABLE 用于回收闲置的数据库空间，当表上的数据行被删除时，所占据的磁盘空间并没有立即被回收，使用了OPTIMIZE TABLE命令后这些空间将被回收，并且对磁盘上的数据行进行重排（注意：是磁盘上，而非数据库）