数据库连接操作
首先定义两个表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 条件的右边表的记录。
- 在解析阶段,左连接是内连接的下一阶段,内连接结束后,把存在于左输入而未存在于右输入的集,加回总的结果集,因此如果少了这一步效率应该要高些。
- 在编译的优化阶段,如果左连接的结果集和内连接一样时,左连接查询会转换成内连接查询,即编译优化器认为内连接要比左连接高效。
分组查询
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不与上述函数一起使用,那么查询结果就是字段取值的分组情况。字段中取值相同的记录为一组,但是只显示该组的第一条记录。
单独使用GROUP BY关键字进行分组
如果单独使用GROUP BY关键字,查询结果只显示一个分组的一条记录。
查询结果进行比较,GROUP BY关键字只显示每个分组的一条记录。这说明,GROUP BY关键字单独使用时,只能查询出每个分组的一条记录,这样做的意义不大。因此,一般在使用集合函数时才使用GROUP BY关键字。
GROUP BY关键字与GROUP_CONCAT()函数一起使用
GROUP BY关键字与GROUP_CONCAT()函数一起使用时,每个分组中指定的字段值会全部显示出来。
GROUP BY关键字与集合函数一起使用
GROUP BY关键字与集合函数一起使用时,可以通过集合函数计算分组中的总记录、最大值、最小值等。
提示:通常情况下,GROUP BY关键字与集合函数一起使用,先使用GROUP BY关键字将记录分组,然后每组都使用集合函数进行计算。在统计时经常需要使用GROUP BY关键字和集合函数。
GROUP BY关键字与HAVING一起使用
使用GROUP BY关键字时,如果加上“HAVING 条件表达式”,则可以限制输出的结果。只有符合条件表达式的结果才会显示
提示:“HAVING 条件表达式”与“WHERE 条件表达式”都是用于限制显示的。但是,两者起作用的地方不一样。
WHERE 条件表达式:作用于表或者视图,是表和视图的查询条件。
HAVING 条件表达式:作用于分组后的记录(having只能用于group by),用于选择符合条件的组。
按照多个字段进行分组
在MySQL中,还可以按照多个字段进行分组。例如,employee表按照d_id字段和sex字段进行分组。分组过程中,先按照d_id字段进行分组,遇到d_id字段的值相等的情况时,再把d_id值相等的记录按照sex字段进行分组。
GROUP BY关键字与WITH ROLLUP一起使用
使用WITH ROLLUP时,将会在所有记录的最后加上一条记录。这条记录是上面所有记录的总和。
数据库查询优化
1、使用索引
应尽量避免全表扫描,首先应考虑在where及order by,group by涉及的列上建立索引。
2、优化SQL语句
- 通过 explain(查询优化神器)用来查看 SQL 语句的执行效果,可以帮助选择更好的索引和优化查询语句,写出更好的优化语句。通常我们可以对比较复杂的尤其是涉及到多表的 SELECT 语句,把关键字 EXPLAIN 加到前面,查看执行计划。例如:explain select * from news;
- 任何地方都不要使用 select * from t,用具体的字段列表代替“*”,不要返回用不到的任何字段。
- 不在索引列做运算或者使用函数。
- 查询尽可能使用 limit 减少返回的行数,减少数据传输时间和带宽浪费。
3、优化数据库对象
-
使用procedure analyse()函数对表进行分析,该函数可以对表中列的数据提出优化建议。能小就用小。表数据类型第一个原则是:使用能正确的表示和存储数据的最短类型。这样可以减少对磁盘空间、内存、cpu缓存的使用。
使用方法:select * from 表名 procedure analyse(); -
对表进行拆分可以提高访问的效率
- 垂直拆分:把主键和一些列放在一个表中,然后把主键和另外的列放在另外一个表中。如果一个表中某些列常用,而另外一些不常用,则可以使用垂直拆分。
- 水平拆分:根据一列或者多列数据的值把数据行放在第二个独立的表中。
-
创建中间表来提高查询速度:中间表的结构和原表完全相同,转移要统计的数据到中间表,然后在中间表上进行统计,得出想要的结果。
4、硬件优化
- CPU优化,选择多核和主频高的CPU;
- 内存优化,使用更大的内存,将尽量多的内存分配给MYSQL做缓存;
- 磁盘IO优化,使用磁盘阵列,选择合适的磁盘调度算法,减少磁盘的寻道时间。
5、MYSQL自身优化
对Mysql自身的优化主要是对其配置文件my.cnf中的各项参数进行优化调整。如指定MySQL查询缓冲区的大小,指定MySQL允许的最大连接进程数等。
6、应用层面的优化
- 使用数据库连接池
- 使用查询缓存,它的作用是存储select查询的文本及其相应结果。如果随后收到一个相同的查询,服务器会从查询缓存中直接得到查询结果。查询缓存适用的对象是更新不频繁的表,到表中数据更改后,查询缓存中的相关条目就会被清空。
大访问量到数据库时,如何优化
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)复制的原理:
- master 将数据改变记录到二进制日志(binary log)中,也即是配置文件log-bin 指定的文件(这些记录叫做二进制日志事件,binary log events);PS:从图中可以看出,Slave服务器中有一个I/O线程(I/O Thread)在不停地监听Master的二进制日志(Binary Log)是否有更新:如果没有它会睡眠等待 Master 产生新的日志事件;如果有新的日志事件(Log Events),则会将其拷贝至 Slave 服务器中的中继日志(Relay Log)。
- slave 将 master 的二进制日志事件(binary log events)拷贝到它的中继日志(relay log);
- slave 重做中继日志中的事件,将 Master 上的改变反映到它自己的数据库中,所以两端的数据是完全一样的。
PS:从图中可以看出,Slave 服务器中有一个 SQL 线程(SQL Thread)从中继日志读取事件,并重做其中的事件,从而更新 Slave 的数据,使其与 Master 中的数据一致。只要该线程与 I/O 线程保持一致,中继日志通常会位于 OS 的缓存中,所以中继日志的开销很小。附简要原理图:
主从复制的几种方式:
- 同步复制
主服务器在将更新的数据写入它的二进制日志(Bin log)文件中后,必须等待验证所有的从服务器的更新数据是否已经复制到其中,之后才可以自由处理其它进入的事务处理请求。 - 异步复制
主服务器在将更新的数据写入它的二进制日志(Bin log)文件中后,无需等待验证更新数据是否已经复制到从服务器中,就可以自由处理其它进入的事务处理请求。 - 半同步复制
主服务器在将更新的数据写入它的二进制日志(Bin log)文件中后,只需等待验证其中一台从服务器的更新数据是否已经复制到其中,就可以自由处理其它进入的事务处理请求,其他的从服务器不用管。
3、数据库分表,分区,分库
- 分表见上面描述(垂直拆分、水平拆分)。
- 分区就是把一张表的数据分成多个区块,这些区块可以在一个磁盘上,也可以在不同的磁盘上,分区后,表面上还是一张表,但数据散列在多个位置,这样一来,多块硬盘同时处理不同的请求,从而提高磁盘 I/O 读写性能,实现比较简单。包括水平分区和垂直分区。
- 分库是根据业务不同把相关的表切分到不同的数据库中,比如 web、bbs、blog 等库。