分类目录归档:Database

Mysql必知必会

今天在v2ex看到有人问怎么建表,其实建表个人总结起来非常简单。

用面向对象方法建立表

class = table
object = row
property = field

如:(我习惯用单数,你可以用复数)

book:
id
title,
isbn

category:
id
name

book_category:
id
book_id
author_id

处理表关联关系

  • A1 对多 B:B 表加 a_id,比如 order_item 表加 order_id
  • A1 对 1B:附表加主表 id,比如 profile 表加 user_id
  • A 多对多 B:C 表关联 A,B,如 post_tag 表加 post_id 和 tag_id

如何建立索引(全文索引)

  • 通常常用的where条件增加索引,如select id,title from post where forum_id=3,这时候需要给forum_id加索引,ID是主键是不要加的,title也不要加
  • 数据库模糊查询(如搜索“Mysql 数据库”)需要建立全文索引(参考我的另外一篇文章:Mysql中文全文索引(含实例5分钟上手)
  • 排序可以加组合索引,如select id,title from post where forum_id=3 order by create_time desc,这时候可以加forum_id+create_time的组合索引,Mysql高版本组合索引可以设置排序
  • 组合索引单独查后部分不起作用,如上面的forum_id+create_time的组合索引,如果你只根据create_time查那么不起作用
  • 唯一字段可以加唯一索引

Mysql性能工具

  • explain可以分析sql性能,每个字段的含义都需要清楚
  • slow query log:Mysql的配置中开启慢速查询日志,慢速查询日志需要用慢查询分析工具来分析,不是用肉眼来看的
  • show full processlist可以查看当前数据库运行的查询
  • infomation_schema有一大堆有用东西

其他

  • null还是0:建议采用null表示没有,这样不会影响数据库的约束

有用的资源

Mysql 数据库空间优化

查看那张表占用空间大

USE information_schema;

SELECT
TABLE_SCHEMA,
TABLE_NAME,
DATA_LENGTH,
INDEX_LENGTH
FROM
TABLES
GROUP BY
TABLE_SCHEMA
ORDER BY
DATA_LENGTH DESC;

修改表采用压缩

USE mydb;

ALTER TABLE mytable ROW_FORMAT = DYNAMIC;

OPTIMIZE TABLE mytable;

如果没有 DYNAMIC,那么采用 COMPRESSED 也可以

修改表采用 tokudb (需要安装 tokudb 引擎)

ALTER TABLE `mytable`
ENGINE=tokudb;

修改字符集

ALTER TABLE `mytable`
DEFAULT CHARACTER SET=gbk

删除日志文件

第一步:登陆进入mysql,并使用 show binary logs; 查看日志文件。

mysql> show binary logs;

第二步:查看正在使用的日志文件:show master status;

mysql> show master status;
+------------------+-----------+--------------+------------------+-------------------+
| File             | Position  | Binlog_Do_DB | Binlog_Ignore_DB | Executed_Gtid_Set |
+------------------+-----------+--------------+------------------+-------------------+
| mysql-bin.000005 | 425994852 |              |                  |                   |
+------------------+-----------+--------------+------------------+-------------------+
1 row in set (0.00 sec)

当前正在使用的日志文件是mysql-bin.000005,那么删除日志文件的时候应该排除掉该文件。

mysql> purge binary logs to 'mysql-bin.000005';

删除除mysql-bin.000005以外的日志文件。

mysql 定时清理日志文件

如果每次等到发现空间不足的时候才去手动删除日志文件,这种方式是很不理想的。

那么,我们就需要设置mysql,让它能自动清理日志文件。

编辑mysql的配置文件,设置expire_logs_days(mysql定时删除日志文件)

[root@sam ~]# vim /etc/my.cnf

在my.cnf中,添加或修改expire_logs_days的值 (这里设置的自动删除时间为10天, 默认为0不自动删除)

expire_logs_days=10

修改后,重启mysql就会生效。

但是,在生产环境中,重启mysql数据库往往会付出很高的代价。

于是,可以在不重启mysql的情况下,修改expire_logs_days值

如下:

mysql> show variables like '%log%';
mysql> set global expire_logs_days = 10;

设置完后,可以通过 show variables like '%log%'; 看到expire_logs_days的值已被修改成10。

注意:通过这种方式设置expire_logs_days虽然不需要重启mysql即可生效,但是该方式在重启mysql之后,值会被恢复。

于是,建议通过mysql命令设置expire_logs_days的同时,也修改/etc/my.cnf下的expire_logs_days=10配置,这样在下次重启mysql的时候,expire_logs_days也一样是10;

参考

族谱的数据设计

需求分析

  • 族谱有一个特点就是写入少,所以可以建立一个缓存表。
  • 维护关系尽量简单
  • 查询上下几代尽量快速,避免递归

数据库结构

people 表

id 
name 姓名

relation 表

id
family_id 族号 一个用户可能存在多个族谱中,比如外嫁后进入其他族谱
people_id 用户编号
level 代系
relate_people_id 关联人,
type 关系:如丈夫,妻子,儿子,孙子,情人
order 第几个孩子,第几任妻子

索引

alter table relation create index family_level(family_id,level);

查询(高性能将上下三代查出来)

select * from relation where family_id=3 and level between current_level-3,current_level+3;

分析

这两个表记录基本不用改(除非录错了),任何时候都是增加记录即可,所以维护也非常简单。

界面展示上,查询出上下几代,然后名字作为主要展示标签,关系用连线展示,关系和人是多对多,完美解决关系混乱的问题。

level 代表是族谱里面的第几代。

如果发现了新的祖先,然么当然自己代数要加一。

比如李渊原来 level 是 1 代,然后认了老子做祖宗,那么他就不 1 代了,老子是 1 代。

族谱草图

file

参考

Mysql update select同一张表

根据下级状态更新上级状态

原来语句

SET display = 1
WHERE
    id IN (
        SELECT
            a.pid
        FROM
            area a
        WHERE
            a.display = 1
        AND pid != 0
    );

提示:[Err] 1093 - You can't specify target table 'area' for update in FROM clause

修改后

UPDATE area a1,(
        SELECT
            pid
        FROM
            area
        WHERE
            display = 1
        AND pid != 0
    ) a2
SET a1.display = 1
where a1.id=a2.pid;

Mysql中文全文索引(含实例5分钟上手)

数据库配置

[mysqld]
ngram_token_size=1 # 如果需要搜索一个字,需要改为1

innodb_ft_min_token_size

默认3,表示最小3个字符作为一个关键词,增大该值可减少全文索引的大小

innodb_ft_max_token_size

默认84,表示最大84个字符作为一个关键词,限制该值可减少全文索引的大小

ngram_token_size

默认2,表示2个字符作为内置分词解析器的一个关键词,如对“abcd”建立全文索引,关键词为'ab','bc','cd'

一般来说,查询正好等于ngram_token_size的词,速度会更快,但是查询比它更长的词或短语,则会变慢

如果需要搜索一个字,需要改为1

注意 这三个参数均不可动态修改,修改了这些参数,需重启MySQL服务,并重新建立全文索引

建立数据库

CREATE TABLE `article` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `title` varchar(200) DEFAULT NULL,
  `body` text,
  PRIMARY KEY (`id`),
  FULLTEXT KEY `ft_idx` (`title`,`body`) WITH PARSER ngram
) ENGINE=InnoDB AUTO_INCREMENT=5 DEFAULT CHARSET=utf8;

INSERT INTO `test`.`article` (`id`, `title`, `body`) VALUES ('1', 'MySQL数据库权威指南', '非常不错的书籍,值得一看');
INSERT INTO `test`.`article` (`id`, `title`, `body`) VALUES ('2', 'Oracle数据库精选', '不妨看看');
INSERT INTO `test`.`article` (`id`, `title`, `body`) VALUES ('3', 'SQL Servr 数据库进阶', '不容错过');
INSERT INTO `test`.`article` (`id`, `title`, `body`) VALUES ('4', 'postgreq 数据库进阶', '知道了吗');

这里注意全文索引使用了WITH PARSER ngram

开始搜索

SELECT * FROM article WHERE MATCH(title,body) AGAINST ('精选 值得')
SELECT * FROM article WHERE MATCH(title,body) AGAINST ('精选 值得' IN NATURAL LANGUAGE MODE))
SELECT * FROM article WHERE MATCH(title,body) AGAINST ('精选 值得' IN BOOLEAN MODE))

1、自然语言模式(NATURAL LANGUAGE MODE)

自然语言模式是MySQL 默认的全文检索模式。自然语言模式不能使用操作符,不能指定关键词必须出现或者必须不能出现等复杂查询。

2、BOOLEAN模式(BOOLEAN MODE)

BOOLEAN模式可以使用操作符,可以支持指定关键词必须出现或者必须不能出现或者关键词的权重高还是低等复杂查询。

参考

Mysql不锁表增加字段和索引方法

看了网上很多回答,基本都是过时和错误的。

5.6 以后增加了ONLINE DDL,

实现不锁表增加字段和索引非常简单。

解决办法

ALTER TABLE `member` ADD `user_from` smallint(1) NOT NULL, ALGORITHM=INPLACE, LOCK=NONE

ALGORITHM表示算法:default默认(根据具体操作类型自动选择),inplace(不影响DML),copy创建临时表(锁表),INSTANT只修改元数据(8.0新增,在修改名字等极少数情况可用)

LOCK表示是否锁表:default默认,none,shared共享锁,exclusive

什么情况下会inplace,什么情况下会copy?

什么情况下会inplace,什么情况下会copy,具体参考:Online DDL Operations

5.6以前版本解决办法

参考

精品数据库(省市区县带拼音行政区划、大学、IP地址、农历、等)

行政区划-数据库

全国行政区划,省市区镇四级,包含名称、完整名称、经纬度、区号、邮编、行政区划代码、拼音。

使用说明

area.xlsx Excel文件数据
area.sql MySQL脚本
area.psc Navicat for MySQL备份文件
area.nb3 Navicat Premium备份文件

数据来源

高德API
民政部官网
中国邮政官网

postgresql 常用命令

创建用户

CREATE USER davide WITH PASSWORD 'jw8s0F4';
CREATE ROLE admin WITH CREATEDB CREATEROLE;

修改用户密码

ALTER ROLE davide WITH PASSWORD 'hu8jmn3';
ALTER ROLE davide WITH PASSWORD NULL;

创建数据库

CREATE DATABASE sales OWNER salesapp

解决nginx+php/java/go/python+mysql下time_wait连接数过多问题

查看连接数

netstat -n |  wc -l # 总连接数
netstat -n | grep -i time_wait | wc -l # time_wait 连接数
netstat -anp # 查看占用端口过多的程序

tcp复用解决方案

网上大部分解决方案是修改sysctl.conf回收重用ipv4连接,但是可能带来其他问题

net.ipv4.tcp_syncookies = 1
net.ipv4.tcp_timestamps=1
net.ipv4.tcp_tw_reuse=1
net.ipv4.tcp_tw_recycle=1
net.ipv4.tcp_fin_timeout=30
net.ipv4.tcp_keepalive_time = 600

nginx fastcgi(php) 解决方案

修改nginx.conf

upstream fastcgi_backend {
    server 127.0.0.1:9000;
    keepalive 60;
}

location ~ \.php$ {
        fastcgi_pass  fastcgi_backend;
        fastcgi_keep_conn on;
        fastcgi_index  index.php;
        fastcgi_param  SCRIPT_FILENAME $document_root$fastcgi_script_name;
        include        fastcgi_params;
}

主要是 fastcgi_keep_conn onupstreamkeepalive

nginx + proxy (python/go/java) 解决方案

upstream wxpic {
    keepalive 60;
    server 127.0.0.1:xxx;
}

server {
    keepalive_requests 10000; # 默认100
    location / {
        proxy_http_version 1.1;
        proxy_set_header Connection "";
    }
}

主要是 proxy_http_version, proxy_set_header Connection ""以及keepalive_requests

mysql/数据库连接解决方案

修改yii数据库配置采用长链接

'db' => [ 
    'class' => 'yii\db\Connection',
    'dsn' => '*', 'username' => '*', 
    'password' => '*', 
    'charset' => 'utf8mb4', 
    'attributes' => [ 
       PDO::ATTR_PERSISTENT => true 
    ]
],

CentOS7通过yum安装PostgreSQL10

发现最新的postgresql版本

https://www.postgresql.org/download/linux/redhat/

下载

yum install https://download.postgresql.org/pub/repos/yum/10/redhat/rhel-7-x86_64/pgdg-centos10-10-2.noarch.rpm

安装

yum install postgresql10-server

设置数据目录

创建数据目录并修改权限

mkdir -p /data/postgresql/
chown postgres:postgres /data/postgresql/

复制开机启动文件

sudo cp /usr/lib/systemd/system/postgresql-10.service /etc/systemd/system/

修改开机启动文件

vim /etc/systemd/system/postgresql-10.service

找到

Environment=PGDATA=/var/lib/pgsql/10/data/

修改为

Environment=PGDATA=/data/postgresql/

初始化数据库

/usr/pgsql-10/bin/postgresql-10-setup initdb

开机自启动

systemctl enable postgresql-10
systemctl start postgresql-10
systemctl status postgresql-10

修改本地帐户权限

vim /data/postgresql/pg_hba.conf

host    all             all             127.0.0.1/32            ident

修改为

host    all             all             127.0.0.1/32            md5

重新加载配置

systemctl restart postgresql-10

新增用户和数据库

su - postgres
createuser dbuser
createdb -e -O dbuser dbname

设定密码

su - postgres
psql
\password dbuser (输入两次密码)

新用户登录数据库

psql -U dbuser -d dbname -h 127.0.0.1 (输入之前的密码)

允许非本机ip登录

  • 编辑data/postgresql.conf修改listen_addresses = 'localhost'listen_addresses = 'localhost'
  • 编辑data/pg_hba.conf

    host all all 192.168.1.0/24 md5