59、数据库连接（LEFT）操作

  首先定义两个表t1和t2

  内连接（inner join）：只显示符合连接条件的记录
  select * from t1 inner join t2 on t1.id = t2.id;

  外连接分为左外连接、右外连接、全外连接三种
  1）、左外连接（LEFT JOIN或LEFT OUTER JOIN）：即以左表为基准，到右表找到匹配的数据，找不到匹配的用NULL补齐。 最后显示左表的全部记录以及右表符合条件的记录。
  select * from t1 left join t2 on t1.id = t2.id;

  2）、右外连接（RIGHT JOIN 或 RIGHT OUTER JOIN）：即以右表为基准，到左表找匹配的数据，找不到匹配的用 NULL 补齐。 显示右表的全部记录及左表符合连接条件的记录。
  select * from t1 right join t2 on t1.id = t2.id;

  3）、全外连接（FULL JOIN 或 FULL OUTER JOIN）：除了显示符合连接条件的记录外，在 2 个表中的其他记录也显示出来。

  inner join 和 left join的性能比较。
  从理论上来分析，确实是 inner join 的性能要好，因为是选出 2 个表都有的记录，而 left join 会出来左边表的所有记录、满足 on 条件的右边表的记录。
  1、在解析阶段，左连接是内连接的下一阶段，内连接结束后，把存在于左输入而未存在于右输入的集，加回总的结果集，因此如果少了这一步效率应该要高些。
  2、在编译的优化阶段，如果左连接的结果集和内连接一样时，左连接查询会转换成内连接查询，即编译优化器认为内连接要比左连接高效。

60、分组查询

60.1、MySQL对数据表进行分组查询（GROUP BY）

  GROUP BY关键字可以将查询结果按照某个字段或多个字段进行分组。字段中值相等的为一组。基本的语法格式如下：
  GROUP BY 属性名 [HAVING 条件表达式] [WITH ROLLUP]
  属性名：是指按照该字段的值进行分组。
  HAVING 条件表达式：用来限制分组后的显示，符合条件表达式的结果将被显示。
  WITH ROLLUP：将会在所有记录的最后加上一条记录。加上的这一条记录是上面所有记录的总和。
  GROUP BY关键字可以和GROUP_CONCAT()函数一起使用。GROUP_CONCAT()函数会把每个分组中指定的字段值都显示出来。
  同时，GROUP BY关键字通常与集合函数一起使用。集合函数包括COUNT()函数、SUM()函数、AVG()函数、MAX()函数和MIN()函数等。
  COUNT()函数：用于统计记录的条数。
  SUM()函数：用于计算字段的值的总和。
  AVG()函数：用于计算字段的值的平均值。
  MAX()函数：用于查询字段的最大值。
  MIN()函数：用于查询字段的最小值。
  如果GROUP BY不与上述函数一起使用，那么查询结果就是字段取值的分组情况。字段中取值相同的记录为一组，但是只显示该组的第一条记录。

60.2、单独使用GROUP BY关键字进行分组

  如果单独使用GROUP BY关键字，查询结果只显示一个分组的一条记录。

  查询结果进行比较，GROUP BY关键字只显示每个分组的一条记录。这说明，GROUP BY关键字单独使用时，只能查询出每个分组的一条记录，这样做的意义不大。因此，一般在使用集合函数时才使用GROUP BY关键字。

60.3、GROUP BY关键字与GROUP_CONCAT()函数一起使用

  GROUP BY关键字与GROUP_CONCAT()函数一起使用时，每个分组中指定的字段值会全部显示出来。

60.4、GROUP BY关键字与集合函数一起使用

  GROUP BY关键字与集合函数一起使用时，可以通过集合函数计算分组中的总记录、最大值、最小值等。

  提示：通常情况下，GROUP BY关键字与集合函数一起使用，先使用GROUP BY关键字将记录分组，然后每组都使用集合函数进行计算。在统计时经常需要使用GROUP BY关键字和集合函数。

60.5、GROUP BY关键字与HAVING一起使用

  使用GROUP BY关键字时，如果加上“HAVING 条件表达式”，则可以限制输出的结果。只有符合条件表达式的结果才会显示。

  提示：“HAVING 条件表达式”与“WHERE 条件表达式”都是用于限制显示的。但是，两者起作用的地方不一样。
  WHERE 条件表达式：作用于表或者视图，是表和视图的查询条件。
  HAVING 条件表达式：作用于分组后的记录（having只能用于group by），用于选择符合条件的组。

60.6、按照多个字段进行分组

  在MySQL中，还可以按照多个字段进行分组。例如，employee表按照d_id字段和sex字段进行分组。分组过程中，先按照d_id字段进行分组，遇到d_id字段的值相等的情况时，再把d_id值相等的记录按照sex字段进行分组。

60.7、GROUP BY关键字与WITH ROLLUP一起使用

  使用WITH ROLLUP时，将会在所有记录的最后加上一条记录。这条记录是上面所有记录的总和。

61、数据库查询优化

  1、使用索引
  应尽量避免全表扫描，首先应考虑在where及order by，group by涉及的列上建立索引。

  2、优化SQL语句
  1）、通过 explain(查询优化神器)用来查看 SQL 语句的执行效果，可以帮助选择更好的索引和优化查询语句，写出更好的优化语句。通常我们可以对比较复杂的尤其是涉及到多表的 SELECT 语句，把关键字 EXPLAIN 加到前面，查看执行计划。例如：explain select * from news;
  2）、任何地方都不要使用 select * from t，用具体的字段列表代替“*”，不要返回用不到的任何字段。
  3）、不在索引列做运算或者使用函数。
  4）、查询尽可能使用 limit 减少返回的行数，减少数据传输时间和带宽浪费。

  3、优化数据库对象
  1）、使用procedure analyse()函数对表进行分析，该函数可以对表中列的数据提出优化建议。能小就用小。表数据类型第一个原则是：使用能正确的表示和存储数据的最短类型。这样可以减少对磁盘空间、内存、cpu缓存的使用。
  使用方法：select * from 表名 procedure analyse();
  2）、对表进行拆分可以提高访问的效率
  垂直拆分：把主键和一些列放在一个表中，然后把主键和另外的列放在另外一个表中。如果一个表中某些列常用，而另外一些不常用，则可以使用垂直拆分。
  水平拆分：根据一列或者多列数据的值把数据行放在第二个独立的表中。
  3）、创建中间表来提高查询速度：中间表的结构和原表完全相同，转移要统计的数据到中间表，然后在中间表上进行统计，得出想要的结果。

  4、硬件优化
  1）、CPU优化，选择多核和主频高的CPU；
  2）、内存优化，使用更大的内存，将尽量多的内存分配给MYSQL做缓存；
  3）、磁盘IO优化，使用磁盘阵列，选择合适的磁盘调度算法，减少磁盘的寻道时间。

  5、MYSQL自身优化
  对Mysql自身的优化主要是对其配置文件my.cnf中的各项参数进行优化调整。如指定MySQL查询缓冲区的大小，指定MySQL允许的最大连接进程数等。

  6、应用层面的优化
  1）、使用数据库连接池
  2）、使用查询缓存，它的作用是存储select查询的文本及其相应结果。如果随后收到一个相同的查询，服务器会从查询缓存中直接得到查询结果。查询缓存适用的对象是更新不频繁的表，到表中数据更改后，查询缓存中的相关条目就会被清空。

62、大访问量到数据库时，如何优化

  1、使用优化查询方法（数据库查询优化）

  2、主从复制，读写分离，负载均衡
  目前，大部分的主流关系型数据库都提供了主从复制的功能，通过配置两台（或多台）数据库的主从关系，可以将一台数据库服务器的数据更新同步到另一台服务器上。网站可以利用数据库的这一功能，实现数据库的读写分离，从而改善数据库的负载压力。一个系统的读操作远远多于写操作，因此写操作发向 master，读操作发向 slaves 进行操作（简单的轮循算法来决定使用哪个 slave）。
  利用数据库的读写分离，Web 服务器在写数据的时候，访问主数据库（Master），主数据库通过主从复制机制将数据更新同步到从数据库（Slave），这样当 Web 服务器读数据的时候，就可以通过从数据库获得数据。 这一方案使得在大量读操作的 Web 应用可以轻松地读取数据，而主数据库也只会承受少量的写入操作，还可以实现数据热备份，可谓是一举两得的方案。

  主从复制的原理：
  影响 MySQL-A 数据库的操作，在数据库执行后，都会写入本地的日志系统 A 中。假设，实时的将变化了的日志系统中的数据库事件操作，通过网络发给 MYSQL-B。MYSQL-B 收到后，写入本地日志系统 B，然后一条条的将数据库事件在数据库中完成。那么，MYSQL-A 的变化，MYSQL-B 也会变化，这样就是所谓的 MYSQL 的复制。
  在上面的模型中，MYSQL-A 就是主服务器，即 master，MYSQL-B 就是从服务器，即slave。
  日志系统 A，其实它是 MYSQL 的日志类型中的二进制日志，也就是专门用来保存修改数据库表的所有动作，即 bin log。【注意 MYSQL 会在执行语句之后，释放锁之前，写入二进制日志，确保事务安全】；
  日志系统 B，并不是二进制日志，由于它是从 MYSQL-A 的二进制日志复制过来的，并不是自己的数据库变化产生的，有点接力的感觉，称为中继日志，即 relay log。
  可以发现，通过上面的机制，可以保证 MYSQL-A 和 MYSQL-B 的数据库数据一致，但是时间上肯定有延迟，即 MYSQL-B 的数据是滞后的。

  简化版：
  mysql 主(称 master)从(称 slave)复制的原理：
  (1)、master 将数据改变记录到二进制日志(binary log)中，也即是配置文件log-bin 指定的文件(这些记录叫做二进制日志事件，binary log events)；
  PS：从图中可以看出，Slave服务器中有一个I/O线程(I/O Thread)在不停地监听Master的二进制日志(Binary Log)是否有更新：如果没有它会睡眠等待 Master 产生新的日志事件；
  如果有新的日志事件(Log Events)，则会将其拷贝至 Slave 服务器中的中继日志(Relay Log)。
  (2)、slave 将 master 的二进制日志事件（binary log events）拷贝到它的中继日志(relay log)；
  (3)、slave 重做中继日志中的事件，将 Master 上的改变反映到它自己的数据库中，所以两端的数据是完全一样的。
  PS：从图中可以看出，Slave 服务器中有一个 SQL 线程(SQL Thread)从中继日志读取事件，并重做其中的事件，从而更新 Slave 的数据，使其与 Master 中的数据一致。只要该线程与 I/O 线程保持一致，中继日志通常会位于 OS 的缓存中，所以中继日志的开销很小。附简要原理图：

  主从复制的几种方式：
  （1）、同步复制
  主服务器在将更新的数据写入它的二进制日志（Bin log）文件中后，必须等待验证所有的从服务器的更新数据是否已经复制到其中，之后才可以自由处理其它进入的事务处理请求。
  （2）、异步复制
  主服务器在将更新的数据写入它的二进制日志（Bin log）文件中后，无需等待验证更新数据是否已经复制到从服务器中，就可以自由处理其它进入的事务处理请求。
  （3）、半同步复制
  主服务器在将更新的数据写入它的二进制日志（Bin log）文件中后，只需等待验证其中一台从服务器的更新数据是否已经复制到其中，就可以自由处理其它进入的事务处理请求，其他的从服务器不用管。

  3、数据库分表，分区，分库
  分表见上面描述（垂直拆分、水平拆分）。
  分区就是把一张表的数据分成多个区块，这些区块可以在一个磁盘上，也可以在不同的磁盘上，分区后，表面上还是一张表，但数据散列在多个位置，这样一来，多块硬盘同时处理不同的请求，从而提高磁盘 I/O 读写性能，实现比较简单。包括水平分区和垂直分区。
  分库是根据业务不同把相关的表切分到不同的数据库中，比如 web、bbs、blog 等库。