数据库相关

1723855321

于 2020-05-24 17:14:23 发布

阅读量141

点赞数

原文链接：https://www.cnblogs.com/zhenbianshu/p/5002189.html

版权

1.数据库的连接

在这里插入图片描述
（1）内连接
语句：select * from a_table a inner join b_table on a.a_id = b.b_id;
说明：组合两个表中的记录，返回关联字段相符的记录，也就是返回两个表的交集（阴影）部分。

（2）左连接
语句：select * from a_table a left join b_table on a.a_id = b.b_id;
在这里插入图片描述

说明：
left join 是left outer join的简写，它的全称是左外连接，是外连接中的一种。
左(外)连接，左表(a_table)的记录将会全部表示出来，而右表(b_table)只会显示符合搜索条件的记录。右表记录不足的地方均为NULL。
（3）右连接
语句：select * from a_table a right outer join b_table b on a.a_id = b.b_id;
在这里插入图片描述

说明：
right join是right outer join的简写，它的全称是右外连接，是外连接中的一种。
与左(外)连接相反，右(外)连接，左表(a_table)只会显示符合搜索条件的记录，而右表(b_table)的记录将会全部表示出来。左表记录不足的地方均为NULL。
（4）全连接
MySQL目前不支持此种方式，可以用其他方式替代解决：左右连接合并去重

2.数据库优化

小技巧：1、SQL语句中的关键词最好用大写来书写，第一易于区分关键词和操作对象，第二，SQL语句在执行时，MySQL会将其转换为大写，手动写大写能增加查询效率(虽然很小)。
2、如果我们们经对数据库中的数据行进行增删，那么会出现数据ID过大的情况，用ALTER TABLE tablename AUTO_INCREMENT=N，使自增ID从N开始计数。
3、对int类型添加 ZEROFILL 属性可以对数据进行自动补0
4、导入大量数据时最好先删除索引再插入数据，再加入索引，不然，mysql会花费大量时间在更新索引上。
5、创建数据库书写sql语句时，我们可以在IDE里创建一个后缀为.sql的文件，IDE会识别sql语法，更易于书写。更重要的是，如果你的数据库丢失了，你还可以找到这个文件，在当前目录下使用/path/mysql -uusername -ppassword databasename < filename.sql来执行整个文件的sql语句（注意-u和-p后紧跟用户名密码，无空格）。
（1）数据库设计方面优化
1、数据库设计为了查询方便可以有一定的数据冗余。
2、选择数据类型优先级 int > date,time > enum,char>varchar > blob，选择数据类型时，可以考虑替换，如ip地址可以用ip2long()函数转换为unsign int型来进行存储。
3、对于char(n)类型，在数据完整的情况下尽量较小的的n值。
4、在建表时用partition命令对单个表分区可以大大提升查询效率，MySQL支持RANGE，LIST，HASH，KEY分区类型，其中以RANGE最为常用，分区方式为：
　　CREATE TABLE tablename{
　　}ENGINE innodb/myisam CHARSET utf8 //选择数据库引擎和编码
　　PARTITION BY RANGE/LIST(column),//按范围和预定义列表进行分区
　　PARTITION partname VALUES LESS THAN /IN(n),//命名分区并详细限定分区的范围
5、选择数据库引擎时要注意innodb 和 myisam的区别。
　　存储结构：MyISAM在磁盘上存储成三个文件。而InnoDB所有的表都保存在同一个数据文件中，一般为2GB
　　事务支持：MyISAM不提供事务支持。InnoDB提供事务支持事务。
　　表锁差异：MyISAM只支持表级锁。InnoDB支持事务和行级锁。
　　全文索引：MyISAM支持 FULLTEXT类型的全文索引(不适用中文，所以要用sphinx全文索引引擎)。InnoDB不支持。
　　表的具体行数：MyISAM保存有表的总行数，查询count(*)很快。InnoDB没有保存表的总行数,需要重新计算。
　　外键：MyISAM不支持。InnoDB支持
（2）索引方面优化
1、innodb是聚簇索引，存储索引时必须有主键，如果没有指定，引擎会自动生成一个隐藏的主键，生成一个主索引，索引内存放的是主键的物理地址，数据靠主键存放，每次使用索引时要先找到主索引，然后找到主索引下的数据。

优点通过主键查找特别快，缺点是次级索引会变慢，因为需要先通过次级索引（次级索引里是主索引的位置。）找到主索引，然后通过主索引找数据。并且如果主键无规律，插入新值时需要移动较多数据块，会影响效率，所以要尽量使用有规律递增的int型做主键。还有因为数据紧跟着主键放，所以如果数据中有数据量特别大的列(text/blob)，innodb查询时会跳过很多数据块，也会导致慢。

2、myisam的索引各个索引都相同统一指向磁盘上各个行的地址，都是轻量级的指针数据。缺点是各个索引的建立不是通过主键，查询没有聚簇索引查找主键快。但其因为存储的是地址，所以在插入新值时比较方面移动改变。
3、进行多条件查询时，对多条件分别建立索引时，执行sql查询时，MySQL只会选择一个最贴近的索引来使用，所以如果需要多条件查询，要建立联合索引，即使会造成数据冗余。
联合索引的BTREE建立方法：对第一个条件建立索引，在第一个索引的BTREE区域对第二个条件建立索引，以此类推，所以，在使用索引时，不用第一个条件用第二个条件也不会用到联合索引。使用索引时要条件要有顺序，有序列的使用。
4、索引长度对查询也有很大影响，我们应该尽量建立短的索引长度，我们可以使用查询列
SELECT COUNT(DISTINCT LEFT(column)) / COUNT(*) FROM tablename 来测试对column列建立索引时选取不同的长度，索引的覆盖率有多大，我们选择一下接近饱和的n个长度来建立索引
ALTER TABLE tablename ADD INDEX (column(n)); 来对某一列的前n个字符建立索引。若前n个字符相同，我们甚至可以对字符串进行反转存储，然后建立索引。
5、对于经常修改导致的索引碎片的维护方式：ALTER TABLE tablename ENGINE oldengine;即再次应用一下表存储引擎，使其自动维护；也可以用 OPTIMIZE tablename 命令来进行维护。
（3）查询方面优化
数据库操作尽量少查询，有查询时尽量不在数据库层面上进行数据操作，而是返回到PHP脚本中操作数据，减轻数据库压力。
一旦发现有数据库性能问题，要及时解决，一般用慢查询日志记录查询很"慢"的语句，用EXPLAIN分析查询和索引使用情况，用PROFILE分析语句执行时的具体资源消耗。
慢查询日志：
1、在my.ini或my.cnf的[mysqld]下添加
slow_query_log_file=/path //设置日志存储路径
long_query_time=n //设置如果语句执行时间达到n秒，就会被记录下来
2、然后在MySQL里设置SET slow_query_log='ON’来开启慢查询。
3、记录下日志后，我们用/bin/目录下的mysqldumpslow filename来查看日志，其常用参数如下:
　　-g pattern 使用正则表达式
　　-t n返回前n条数据
　　-s c/t/l/r 以记录次数/时间/查询时间/返回记录数来排序
（4）大型存储方面优化
数据库主从复制和读写分离
1、master将改变记录到二进制日志中，slave将master的二进制拷贝到它的中继日志中，重新将数据返回到它自己的数据中，达到复制主服务器数据的目的。

主从复制可以用作：数据库负载均衡、数据库备份、读写分离等功能。
2、配置主服务器master
　　修改my.ini/my.conf
　　[mysqld]
　　log-bin=mysql-bin //启用二进制日志
　　server-id=102 //服务器唯一ID
3、配置从服务器slave
　　log-bin=mysql-bin //启用二进制日志
　　server-id=226 //服务器唯一ID
4、在主服务器上授权从服务器
　　GRANT REPLICATION SLAVE ON . to ‘slavename’@‘IP’ identified by ‘root’
5、在从服务器上使用
　　change master to
　　　　master_host=“masterip”,
　　　　master_user=“masteruser”,
　　　　master_password=“masterpasswd”;
6、然后使用start slave命令开始进行主从复制。
不要忘记在每次修改配置后重启服务器，然后可以在主从服务器上用show master/slave status查看主/从状态。
实现数据库的读写分离要依赖MySQL的中间件，如mysql_proxy，atlas等。通过配置这些中间件来对主从服务器进行读写分离，使从服务器承担被读取的责任，从而减轻主服务器的负担。
数据库的sharding
在数据库中数据表中的数据量非常庞大的时候，无论是索引还是缓存等压力都很大，对数据库进行sharding，使之分别以多个数据库服务器或多个表存储，以减轻查询压力。
方式有垂直切分、水平切分和联合切分。
垂直切分：在数据表非常多的时候，把数据库中关系紧密(如同一模块，经常连接查询)的表切分出来分别放到不同的主从server上。
水平切分：在表不多，而表里的数据量非常大的时候，为了加快查询，可以用哈希等算法，将一个数据表分为几个，分别放到不同的服务器上，加快查询。水平切分和数据表分区的区别在于其存储介质上的不同。
联合切分：更多的情况是数据表和表中的数据量都非常大，则要进行联合切分，即同时进行垂直和水平分表，将数据库切分为一个分布式的矩阵来存储。
这些数据库的优化方式，每一种拿出来都可以写作一篇文章，可谓是博大精深，了解并记忆了这些方式，可以在有需要的时候进行有目的的选择优化，达到数据库效率的高效。