标签归档:mysql

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;

参考

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以前版本解决办法

参考

解决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 
    ]
],

使用Vagrant+CentOS 7搭建PHP7开发环境(含centos7.box直接下载地址)

Vagrant是一款基于命令行的虚拟机管理软件,可以用来快速部署统一的开发环境。

下载Vagrant

https://www.vagrantup.com/downloads.html

下载CentOS 7 Box

官方box下载地址

https://app.vagrantup.com/boxes/search

第三方box下载地址

http://www.vagrantbox.es/

使用原生下载

https://app.vagrantup.com/centos/boxes/7

vagrant init centos/7
vagrant up

vagrant 配置

  config.vm.network "public_network", ip: "192.168.31.245"
  config.vm.synced_folder "d:/data", "/data"
  config.vm.synced_folder "d:/phpcode", "/phpcode"

通过下载工具下载centos 7 box

官方box文件下载地址:http://cloud.centos.org/centos/7/vagrant/x86_64/images/

百度网盘box文件下载地址

  • CentOS 7: https://pan.baidu.com/s/1kVlAz59

添加并运行box

vagrant box add centos7 CentOS-7.box
vagrant init centos7
vagrant up

基础系统安装

基本系统安装

vagrant ssh
sudo passwd vagrant
sudo yum groupinstall "Development tools" -y
sudo yum install vim gcc kernel-devel kenel-devel-`uname -r`

禁止selinux

sudo setenforce 0 

sudo vi /etc/selinux/config

SELINUX=disabled

停止防火墙

sudo systemctl disable firewalld
sudo systemctl stop firewalld

更新系统

sudo yum install https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
sudo yum install http://rpms.remirepo.net/enterprise/remi-release-7.rpm
sudo yum install yum-utils
sudo yum-config-manager --enable remi-php72
sudo yum update
sudo yum install php-gd php-pdo php-opcache php-fpm php-pecl-redis php-pecl-mysql php-pecl-mysql php-mbstring php-intl php-cli php-xml
sudo yum install nginx -y
sudo yum install mariadb mariadb-server -y

修改nginx配置

mkdir /data/log/nginx /data/run/nginx -p
sudo service nginx stop 
sudo vim /etc/nginx/nginx.conf

nginx.conf配置修改如下:

user vagrant;
error_log /data/log/nginx/error.log;
pid /data/run/nginx/nginx.pid;

access_log  /data/log/nginx/access.log  main;

include /data/phpcode/projectname/vagrant/nginx/app.conf;

测试nginx配置

sudo nginx -t

修改nginx service配置:

sudo vim /usr/lib/systemd/system/nginx.service

nginx.service修改内容如下:

[Service]
PIDFile=/data/run/nginx/nginx.pid

重新加载service

sudo systemctl daemon-reload
sudo systemctl start nginx

修改PHP配置

mkdir  /data/run/php-fpm/session /data/run/php-fpm/wsdlcache /data/run/php-fpm/opcache /data/log/php-fpm/ -p
sudo service php-fpm stop
sudo vim /etc/php-fpm.d/www.conf

配置内容

user = vagrant
group = vagrant
php_value[session.save_path]    = /data/run/php-fpm/session
php_value[soap.wsdl_cache_dir]  = /data/run/php-fpm/wsdlcache
php_value[opcache.file_cache]  = /data/run/php-fpm/opcache
php_admin_value[error_log] = /data/log/php-fpm/www-error.log
slowlog = /data/log/php-fpm/www-slow.log
request_slowlog_timeout = 1

重启

sudo service php-fpm stop

配置Mysql

mkdir /data/mysql /data/run/mariadb /data/log/mariadb -p
sudo service mariadb stop
sudo vim /etc/my.cnf

mysqld配置

[mysqld]
datadir=/data/mysql
socket=/usr/lib/mysql/mysql.sock

[mysqld_safe]
log-error=/data/log/mariadb/mariadb.log
pid-file=/data/run/mariadb/mariadb.pid

mysql client 配置

[client]

初始化数据库

sudo /usr/libexec/mariadb-prepare-db-dir mariadb.service

修改systemd配置

sudo vim /usr/lib/systemd/system/mariadb.service

配置内容

User=vagrant
Group=vagrant

重载systemd

sudo systemctl daemon-reload
sudo systemctl start mariadb

修改mysql账号密码

'/usr/bin/mysqladmin' -u root password 'new-password'
'/usr/bin/mysqladmin' -u root -h localhost.localdomain password 'new-password'

# Alternatively you can run:
'/usr/bin/mysql_secure_installation'

配置composer

下载安装文件

php -r "copy('https://install.phpcomposer.com/installer', 'composer-setup.php');"

安装

php composer-setup.php

删除安装文件

php -r "unlink('composer-setup.php');"

设置全局路径(windows请按参考文档设置)

sudo mv composer.phar /usr/bin/composer

配置使用国内镜像

composer config -g repo.packagist composer https://packagist.phpcomposer.com