MySQL and PostgreSQL

This is my personal notes for mySQL and PostgreSQL.

Introduction

本文介绍MySQL和PostgreSQL

MySQL: The world’s most popular open source database

PostgreSQL: The world’s most advanced open source database

MySQL

• 基本操作:

  mysql -u root -p; # 输入密码进入mysql
  show databases;
  ues xxx(name of db);
  show tables;
  create database DBname;
  CREATE table table_name (column_name column_type, column_name column_type…);

本地数据导入:

Load Data Local Infile “d:/emp.txt” Into Table Employee Fields Terminated BY “symbol(! or : or …)” Lines Terminated By “\n” (ID, Name, Salary,Manager, Department);

如果出现”The used command is not allowed with this MySQL version”, 需要重新登录，在登录时指定参数:

mysql –local-infile -u root -p**

或者:

去掉 Local, 提前Create Table，将文件放在 secure path下即可导入

SHOW VARIABLES LIKE “secure_file_priv”;

• MySQL索引:

索引是一种特殊的文件(InnoDB数据表上的索引是表空间的一个组成部分)，它们包含着对数据表里所有记录的引用指针。更通俗的说，数据库索引好比是一本书前面的目录，能加快数据库的查询速度。

举例:

SELECT * FROM article WHERE id=2000000

如果没有索引, 会从头遍历直到找到id=2000000，而索引可以帮助直接定位，类似HashMap

创建索引的方式:

1 直接创建索引

Create Index index_name on table(column(length))

2 修改表的方式创建索引:

Alter table table_name add index index_name (columns 可多个)

3 创建表的同时创建索引:

 CREATE TABLE `table` (
      `id` int(11) NOT NULL AUTO_INCREMENT ,
      `title` char(255) CHARACTER SET utf8 COLLATE utf8_general_ci NOT NULL ,
      `content` text CHARACTER SET utf8 COLLATE utf8_general_ci NULL ,
      `time` int(10) NULL DEFAULT NULL ,
       PRIMARY KEY (`id`),
       INDEX index_name (title(length))

4 删除索引:

Drop index indexName on tablename;

Alter Table TableName drop index c;

5 查看索引:

show index from TableName;

• 创建本地MySQL数据库并可视化操作 (以Mac为例)

下载并配置MySQL
1 下载MySQL, 下载地址
2 dmg文件下载安装完毕后，在系统偏好设置中会出现MySQL的图标，点击可控制开启关闭的状态
3 配置环境变量, vim ~/.bash_profile，在path=”xxxx”中加入”:/usr/local/mysql/bin”
4 在terminal中运行 mysql -uroot -p, 输入密码, 进入本地MySQL

Sequel Pro连接本地数据库
1 参数: Host: 127.0.0.1 UserName: root PassWord: 之前设置的密码 Port: 默认3306
2 可以手动添加DB,Table和rows. 如果遇到一打开DataBase sequel pro就崩溃的情况，使用最新的test-build版本

PostgreSQL

事实上，PostgreSQL的性能比MySQL要好，但PG由于关系模型支持的很好，用起来有许多的限制，学习成本也比较高，导致其没有MySQL流行。

OLTP：事务处理是PostgreSQL的本行
OLAP：citus分布式插件，ANSI SQL兼容，窗口函数，CTE，CUBE等高级分析功能，任意语言写UDF
Streaming：PipelineDB扩展，Notify-Listen，物化视图，规则系统，灵活的存储过程与函数编写
TimeSeries：timescaledb时序数据库插件，分区表，BRIN索引
GeoSpatial：PostGIS扩展（杀手锏），内建的几何类型支持，GiST索引。
搜索索引：全文搜索索引足以应对简单场景；丰富的索引类型，支持函数索引，条件索引
NoSQL：JSON，JSONB，XML，HStore原生支持，至NoSQL数据库的外部数据包装器
数据仓库：能平滑迁移至同属Pg生态的GreenPlum，DeepGreen，HAWK等，使用FDW进行ETL
图数据：递归查询
缓存：物化视图

优势:

• 1、 PostgreSQL 的稳定性极强， Innodb 等引擎在崩溃、断电之类的灾难场景下抗打击能力有了长足进步，然而很多 MySQL 用户都遇到过Server级的数据库丢失的场景——mysql系统库是MyISAM的，相比之下，PG数据库这方面要好一些。
• 2、任何系统都有它的性能极限，在高并发读写，负载逼近极限下，PG的性能指标仍可以维持双曲线甚至对数曲线，到顶峰之后不再下降，而 MySQL 明显出现一个波峰后下滑（5.5版本之后，在企业级版本中有个插件可以改善很多，不过需要付费）
• 3、PG 多年来在 GIS 领域处于优势地位，因为它有丰富的几何类型，实际上不止几何类型，PG有大量字典、数组、bitmap 等数据类型，相比之下mysql就差很多，instagram就是因为PG的空间数据库扩展PostGIS远远强于MYSQL的my spatial而采用PGSQL的。
• 4、PG 的“无锁定”特性非常突出，甚至包括 vacuum 这样的整理数据空间的操作，这个和PGSQL的MVCC实现有关系。
• 5、PG 的可以使用函数和条件索引，这使得PG数据库的调优非常灵活，mysql就没有这个功能，条件索引在web应用中很重要。
• 6、PG有极其强悍的 SQL 编程能力（9.x 图灵完备，支持递归！），有非常丰富的统计函数和统计语法支持，比如分析函数（ORACLE的叫法，PG里叫window函数），还可以用多种语言来写存储过程，对于R的支持也很好。这一点上MYSQL就差的很远，很多分析功能都不支持，腾讯内部数据存储主要是MYSQL，但是数据分析主要是Hadoop+PGSQL。
• 7、PG 的有多种集群架构可以选择，plproxy 可以支持语句级的镜像或分片，slony 可以进行字段级的同步设置，standby 可以构建WAL文件级或流式的读写分离集群，同步频率和集群策略调整方便，操作非常简单。
• 8、一般关系型数据库的字符串有限定长度8k左右，无限长 TEXT 类型的功能受限，只能作为外部大数据访问。而 PG 的 TEXT 类型可以直接访问，SQL语法内置正则表达式，可以索引，还可以全文检索，或使用xml xpath。用PG的话，文档数据库都可以省了。
• 9、对于WEB应用来说，复制的特性很重要，mysql到现在也是异步复制，pgsql可以做到同步，异步，半同步复制。还有mysql的同步是基于binlog复制，类似oracle golden gate,是基于stream的复制，做到同步很困难，这种方式更加适合异地复制，pgsql的复制基于wal，可以做到同步复制。同时，pgsql还提供stream复制。
• 10、pgsql对于numa架构的支持比mysql强一些，比MYSQL对于读的性能更好一些，pgsql提交可以完全异步，而mysql的内存表不够实用（因为表锁的原因）

劣势:

• 1、MySQL有一些实用的运维支持，如 slow-query.log ，这个pg肯定可以定制出来，但是如果可以配置使用就更好了。
• 2、mysql的innodb引擎，可以充分优化利用系统所有内存，超大内存下PG对内存使用的不那么充分。
• 3、MySQL的复制可以用多级从库，但是在9.2之前，PGSQL不能用从库带从库。
• 4、从测试结果上看，mysql 5.5的性能提升很大，单机性能强于pgsql，5.6应该会强更多.
• 5、对于web应用来说，mysql 5.6 的内置MC API功能很好用，PGSQL差一些。