逆袭清风

逆袭清风 查看完整档案

厦门编辑  |  填写毕业院校  |  填写所在公司/组织 www.emtalk.net 编辑
编辑
_ | |__ _ _ __ _ | '_ \| | | |/ _` | | |_) | |_| | (_| | |_.__/ \__,_|\__, | |___/ 个人简介什么都没有

个人动态

逆袭清风 发布了文章 · 2020-11-18

Centos7 Web服务搭建完整过程

一、 安装前准备

资源附件:
https://download.csdn.net/download/u011159821/13119645

下载后放置:/usr/local/src目录下

环境依赖:

groupadd www;
useradd -g www -s /sbin/nologin -M www;
yum -y install gcc gcc-c++ openssl openssl-devel libxml2 libxml2-devel bzip2 bzip2-devel libcurl libcurl-devel libjpeg libjpeg-devel libpng libpng-devel freetype freetype-devel gmp gmp-devel libmcrypt libmcrypt-devel readline readline-devel libxslt libxslt-devel sqlite-devel screen autoconf;
mkdir -p /opt/nginx/1.19;
mkdir -p /opt/php/7.4;
mkdir -p /data/mysql/data;
mkdir -p /opt/redis/6.0;
mkdir -p /opt/mongodb/4.4;

二、安装Nginx 1.19

cd /usr/local/src;
tar -zxvf nginx-1.19.4.tar.gz;
cd nginx-1.19.4;
./configure --prefix=/opt/nginx/1.19 --with-http_stub_status_module --with-http_ssl_module --with-http_gzip_static_module --with-pcre --with-http_realip_module --with-stream --with-http_v2_module;
make -j;
make install;
ln -s /opt/nginx/1.19/sbin/nginx /usr/local/bin/nginx;

编辑Nginx.conf,复制以下内容:

user  www www;
worker_processes auto;
error_log  logs/error.log crit;
#error_log  logs/error.log  notice;
#error_log  logs/error.log  info;
pid        logs/nginx.pid;
worker_rlimit_nofile 51200;

events {
    use epoll;
    worker_connections 51200;
    multi_accept on;
}


http {
    include       mime.types;
    default_type  application/octet-stream;

    #log_format  main  '$remote_addr - $remote_user [$time_local] "$request" '
    #                  '$status $body_bytes_sent "$http_referer" '
    #                  '"$http_user_agent" "$http_x_forwarded_for"';

    log_format  access  '$remote_addr - $remote_user [$time_local] "$request" '
         '$status $body_bytes_sent "$http_referer" "$request_body" '
         '"$http_user_agent" $http_x_forwarded_for';

    #access_log  logs/access.log  main;

    server_names_hash_bucket_size 128;
    client_header_buffer_size 32k;
    large_client_header_buffers 4 32k;
    client_max_body_size 50m;

    sendfile        on;
    tcp_nopush      on;
    tcp_nodelay     on;

    #keepalive_timeout  0;
    keepalive_timeout  65;

    fastcgi_connect_timeout 300;
    fastcgi_send_timeout 300;
    fastcgi_read_timeout 300;
    fastcgi_buffer_size 64k;
    fastcgi_buffers 4 64k;
    fastcgi_busy_buffers_size 128k;
    fastcgi_temp_file_write_size 256k;

    gzip on;
    gzip_min_length  1k;
    gzip_buffers     4 16k;
    gzip_http_version 1.1;
    gzip_comp_level 2;
    gzip_types     text/plain application/javascript application/x-javascript text/javascript text/css application/xml;
    gzip_vary on;
    gzip_proxied   expired no-cache no-store private auth;
    gzip_disable   "MSIE [1-6]\.";

    #server {
        #listen       80;
        #server_name  localhost;

        #charset koi8-r;

        #access_log  logs/host.access.log  main;

        #location / {
        #    root   html;
        #    index  index.html index.htm;
        #}

        #error_page  404              /404.html;

        # redirect server error pages to the static page /50x.html
        #
        #error_page   500 502 503 504  /50x.html;
        #location = /50x.html {
        #    root   html;
        #}

        # proxy the PHP scripts to Apache listening on 127.0.0.1:80
        #
        #location ~ \.php$ {
        #    proxy_pass   http://127.0.0.1;
        #}

        # pass the PHP scripts to FastCGI server listening on 127.0.0.1:9000
        #
        #location ~ \.php$ {
        #    root           html;
        #    fastcgi_pass   127.0.0.1:9000;
        #    fastcgi_index  index.php;
        #    fastcgi_param  SCRIPT_FILENAME  /scripts$fastcgi_script_name;
        #    include        fastcgi_params;
        #}

        # deny access to .htaccess files, if Apache's document root
        # concurs with nginx's one
        #
        #location ~ /\.ht {
        #    deny  all;
        #}
    #}


    # another virtual host using mix of IP-, name-, and port-based configuration
    #
    #server {
    #    listen       8000;
    #    listen       somename:8080;
    #    server_name  somename  alias  another.alias;

    #    location / {
    #        root   html;
    #        index  index.html index.htm;
    #    }
    #}


    # HTTPS server
    #
    #server {
    #    listen       443 ssl;
    #    server_name  localhost;

    #    ssl_certificate      cert.pem;
    #    ssl_certificate_key  cert.key;

    #    ssl_session_cache    shared:SSL:1m;
    #    ssl_session_timeout  5m;

    #    ssl_ciphers  HIGH:!aNULL:!MD5;
    #    ssl_prefer_server_ciphers  on;

    #    location / {
    #        root   html;
    #        index  index.html index.htm;
    #    }
    #}

    include vhost/*.conf;
    # 见vhost附件

}

服务命令

nginx # 启动服务
nginx -s reload # 重启服务
nginx -s stop # 停止服务

三、安装PHP 7.4

cd /usr/local/src;
tar -xvf php-7.4.12.tar.gz;
cd php-7.4.12;

配置PHP:

./configure \
--prefix=/opt/php/7.4 \
--with-config-file-path=/opt/php/7.4/etc \
--enable-fpm \
--with-fpm-user=nginx  \
--with-fpm-group=nginx \
--enable-inline-optimization \
--disable-debug \
--disable-rpath \
--enable-shared  \
--enable-soap \
--with-libxml-dir \
--with-xmlrpc \
--with-openssl \
--with-mcrypt \
--with-mhash \
--with-pcre-regex \
--with-sqlite3 \
--with-zlib \
--enable-bcmath \
--with-iconv \
--with-bz2 \
--enable-calendar \
--with-curl \
--with-cdb \
--enable-dom \
--enable-exif \
--enable-fileinfo \
--enable-filter \
--with-pcre-dir \
--enable-ftp \
--with-gd \
--with-openssl-dir \
--with-jpeg-dir \
--with-png-dir \
--with-zlib-dir  \
--with-freetype-dir \
--enable-gd-native-ttf \
--enable-gd-jis-conv \
--with-gettext \
--with-gmp \
--with-mhash \
--enable-json \
--enable-mbregex \
--enable-mbregex-backtrack \
--with-libmbfl \
--with-onig \
--enable-pdo \
--with-mysqli=mysqlnd \
--with-pdo-mysql=mysqlnd \
--with-zlib-dir \
--with-pdo-sqlite \
--with-readline \
--enable-session \
--enable-shmop \
--enable-simplexml \
--enable-sockets  \
--enable-sysvmsg \
--enable-sysvsem \
--enable-sysvshm \
--enable-wddx \
--with-libxml-dir \
--with-xsl \
--enable-zip \
--enable-mysqlnd-compression-support \
--with-pear \
--enable-opcache

编译安装:

make && make install;

常规设置:

# 修改fpm配置php-fpm.conf.default文件名称
mv /opt/php/7.4/etc/php-fpm.conf.default /opt/php/7.4/etc/php-fpm.conf;

# 复制php.ini配置文件
cp php.ini-production /opt/php/7.4/etc/php.ini;

# 复制php-fpm启动脚本到init.d
cp sapi/fpm/init.d.php-fpm /etc/init.d/php-fpm;

# 复制www.conf配置文件
cp /opt/php/7.4/etc/php-fpm.d/www.conf.default /opt/php/7.4/etc/php-fpm.d/www.conf;
# 修改www.conf配置文件
vim /opt/php/7.4/etc/php-fpm.d/www.conf;
// 23、24行,nginx修改为www

# 赋予执行权限
chmod +x /etc/init.d/php-fpm;

# 添加为启动项
chkconfig --add php-fpm;

# 设置开机启动
chkconfig php-fpm on;

# 建立软链接
ln -s /opt/php/7.4/bin/php /usr/local/bin/php;
ln -s /opt/php/7.4/bin/phpize /usr/local/bin/phpize;
ln -s /opt/php/7.4/bin/php-config /usr/local/bin/php-config;

php.ini基础修改

vim /opt/php/7.4/etc/php.ini

# 找到expose_php = On,修改为:
expose_php = Off

# 找到;extension_dir = "./",修改为:
extension_dir = "/opt/php/7.4/lib/php/extensions/no-debug-non-zts-20190902"

# 找到disable_functions = 修改为:
disable_functions = passthru,system,chroot,chgrp,chown,shell_exec,popen,pcntl_exec,ini_alter,ini_restore,dl,openlog,syslog,readlink,popepassthru,pcntl_fork,pcntl_waitpid,pcntl_wait,pcntl_wifexited,pcntl_wifstopped,pcntl_wifsignaled,pcntl_wifcontinued,pcntl_wexitstatus,pcntl_wtermsig,pcntl_wstopsig,pcntl_get_last_error,pcntl_strerror,pcntl_sigprocmask,pcntl_sigwaitinfo,pcntl_sigtimedwait,pcntl_exec,pcntl_getpriority,pcntl_setpriority,imap_open,apache_setenv

# 找到;date.timezone =修改为:
date.timezone = PRC

服务命令

service php-fpm start # 启动PHP
service php-fpm reload # 重启PHP
service php-fpm stop   # 停止PHP

四、安装MySQL 5.7

cd /usr/local/src;
yum -y install mysql57-community-release-el7-10.noarch.rpm;
yum -y install mysql-community-server;

出现以下,至此MySql就安装完成了;

修改/etc/my.cnf

[client]
#password    = your_password
port        = 4363
socket        = /tmp/mysql.sock
[mysqld]
# join_buffer_size = 128M
# sort_buffer_size = 2M
# read_rnd_buffer_size = 2M

#skip-grant-tables
#wait_timeout=500
#interactive_timeout=500
port        = 4363
socket        = /tmp/mysql.sock
datadir         = /data/mysql/data
default_storage_engine = InnoDB
performance_schema_max_table_instances = 400
table_definition_cache = 400
skip-external-locking
key_buffer_size = 256M
max_allowed_packet = 100G
table_open_cache = 1024
sort_buffer_size = 4M
net_buffer_length = 4K
read_buffer_size = 4M
read_rnd_buffer_size = 256K
myisam_sort_buffer_size = 64M
thread_cache_size = 128
query_cache_size = 128M
tmp_table_size = 128M
sql-mode=NO_ENGINE_SUBSTITUTION,STRICT_TRANS_TABLES
pid-file=/data/mysql/mysqld.pid

explicit_defaults_for_timestamp = true
#skip-name-resolve
max_connections = 500
max_connect_errors = 500
open_files_limit = 65535

log-bin=mysql-bin
binlog_format=mixed
server-id = 1
expire_logs_days = 10
slow_query_log=1
slow-query-log-file=/data/mysql/logs/mysql-slow.log
long_query_time=3
#log_queries_not_using_indexes=on
early-plugin-load = ""

character_set_server=utf8
init_connect='SET NAMES utf8'

innodb_data_home_dir = /data/mysql/data
innodb_data_file_path = ibdata1:10M:autoextend
innodb_log_group_home_dir = /data/mysql/data
innodb_buffer_pool_size = 1024M
innodb_log_file_size = 512M
innodb_log_buffer_size = 128M
innodb_flush_log_at_trx_commit = 1
innodb_lock_wait_timeout = 50
innodb_max_dirty_pages_pct = 90
innodb_read_io_threads = 4
innodb_write_io_threads = 4

# Disabling symbolic-links is recommended to prevent assorted security risks
symbolic-links=0

log-error=/data/mysql/logs/mysqld.error.log

[mysqld_safe]  
log-error=/data/mysql/logs/mysqld.error.log
pid-file=/data/mysql/mysqld.pid

[mysqldump]
quick
max_allowed_packet = 500M

[mysql]
no-auto-rehash

[myisamchk]
key_buffer_size = 256M
sort_buffer_size = 4M
read_buffer = 2M
write_buffer = 2M

[mysqlhotcopy]
interactive-timeout

初始化密码:

grep "password" /var/log/mysqld.log

命令行进入数据库,修改初始化密码

mysql -uroot -p
mysql>ALTER USER 'root'@'localhost' IDENTIFIED BY 'qoL5P*49KIlJ';

开启远程访问

mysql>use mysql;
mysql>select 'host' from user where user='root';
mysql>update user set host = '%' where user ='root';
mysql>flush privileges;
mysql>select 'host' from user where user='root';
mysql>exit;

服务命令

#首次服务启动前,确认/data/mysql目录权限为:mysql:mysql
# chown -R mysql:mysql /data/mysql;

systemctl start mysqld.service # 启动Mysql
systemctl restart mysqld #重启Mysql
systemctl stop mysqld #停止Mysql

为Firewalld添加开放端口
添加mysql端口

firewall-cmd --zone=public --add-port=4363/tcp --permanent

然后再重新载入

firewall-cmd --reload

注:若提示FirewallD is not running,则服务未开启:

# 开启Firewall防火墙服务
systemctl start firewalld.service

五、安装Redis 6.0

# 为了避免报错先执行以下命令:
yum -y install gcc tcl;
yum -y install centos-release-scl;
yum -y install devtoolset-9-gcc devtoolset-9-gcc-c++ devtoolset-9-binutils;
echo "source /opt/rh/devtoolset-9/enable" >> /etc/profile;

# 重连Shell后执行
cd /usr/local/src;
tar xzf redis-6.0.9.tar.gz;
mv ./redis-6.0.9 /opt/redis/6.0;
cd /opt/redis/6.0;
make;

# 修改redis.conf配置
# 找到port 6379,修改为:
port 6397

# 找到# requirepass foobared,修改为:
# 此次修改的是Redis密码
requirepass AmYQn2Hv6AytY4Ux

# 创建软链接
ln -s /opt/redis/6.0/src/redis-cli /usr/local/bin/redis-cli;

创建虚拟空间运行Redis服务

# 创建Screen环境
screen -R RedisServe;
[RedisServe]:src/redis-server /opt/redis/6.0/redis.conf;
# CTRL + A + D 退出Screen环境

# 进入已创建的Screen环境
screen -r RedisServe;

PHP加入Redis扩展

cd /usr/local/src/;
tar zxvf redis-5.3.2.tgz;
cd redis-5.3.2/;
phpize # 或 /opt/php/7.4/bin/phpize;
./configure --with-php-config=/opt/php/7.4/bin/php-config;
make;
make install;

# 修改php.ini
vim /opt/php/7.4/etc/php.ini;
extension=redis.so # 949行,;extension=xsl后面追加

# 重启PHP服务
service php-fpm reload;

六、安装MongoDB 4.4

cd /usr/local/src;
tar -zxvf mongodb-linux-x86_64-rhel70-4.4.1.tgz;
mv /usr/local/src/mongodb-linux-x86_64-rhel70-4.4.1/* /opt/mongodb/4.4/
export PATH=/opt/mongodb/4.4/bin:$PATH;

# 设置开机启动
systemctl enable mongod.service

启动前配置

mkdir -p /data/mongo/data/db;
cd /data/mongo;
mkdir logs;
touch mongodb.log;
mkdir etc;
touch mongodb.conf;

# 编辑mongodb.conf
# 更多配置项,可通过 mongod --help 查看
dbpath=/data/mongo/data/db             # 指定数据存储目录
logpath=/data/mongo/logs/mongodb.log   # 指定日志文件存储目录
logappend=true                         # 使用追加方式写日志
port=27017                             # 端口
fork=true                              # 以守护进程方式运行
auth=true                              # 启用验证
bind_ip=0.0.0.0                        # 允许任意外部地址访问

# 启动服务
mongod --config /data/mongo/etc/mongodb.conf;

# 进入mongo命令行下创建超级管理员
# root@dev-PC:~# mongo
db.createUser({user:"root",pwd:"K8gEBx05@F$9#jUe",roles:["root"]})

服务命令

# 启动mongodb
mongod --config /data/mongo/etc/mongodb.conf;

# 停止mongodb
ps -ef | grep mongodb;
kill -9 [对应MongoDB进程ID]

六、SSH端口修改

vi /etc/ssh/sshd_config
# 找到下面的Port指定修改

#Port 22 
Port 2063 #自己新设的端口

# 修改防火墙,增加对刚才的端口访问
# 重启使ssh配置生效
systemctl restart sshd.service 
# 若防火墙服务有开启,则需要将新端口加入端口名单中,并重启服务器
查看原文

赞 0 收藏 0 评论 0

逆袭清风 收藏了文章 · 2020-08-11

Laravel学习笔记之Schema Builder 和 Migration System(上)

说明:本文主要学习Schema Builder和Migration System的使用及相关原理。传统上在设计database时需要写大量的SQL语句,但Laravel提供了Schema Builder这个神器使得在设计database时使用面向对象方法来做,不需要写一行SQL,并且还提供了另一个神器Migration System,可以对database做版本控制,包括回滚上一次的迁移操作。本小系列主要分为上中下三篇。本篇主要学习使用Schema Builder来creating,dropping,updating tables;adding,removing,renaming columns;simple index,unique index,foreign keys,同时也会学习相关源码来进一步了解Schema Builder。

开发环境: Laravel5.3 + PHP7

表的操作-tables

在设计database时需要创建、删除和更新表,Schema Builder类提供了一些methods来面向对象的执行这些操作,而不需要写一行SQL。在写Laravel程序时,也经常使用类似命令php artisan make:migration create_accounts_table --create=accounts来做一个迁移类创建数据表,会在database/migrations文件夹下得到类似如下的代码类:

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateAccountsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('accounts', function (Blueprint $table) {
            $table->increments('id');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('accounts');
    }
}

(1)creating tables

在执行php artisan migrate命令时操作的是up()方法中语句。在创建的迁移类CreateAccountsTable中,Schema::create()就是创建表的语句,并且第一个参数就是表的名字,第二个参数是个闭包,是操作columns的语句,并且参数是个Blueprint对象。为什么有这么奇怪的写法呢?

看下Schema Facade中getFacadeAccessor的源码:

    /**
     * Get a schema builder instance for the default connection.
     *
     * @return \Illuminate\Database\Schema\Builder
     */
    protected static function getFacadeAccessor()
    {
        // 这里'db'服务是在DatabaseServiceProvider中定义的,是DatabaseManager对象,且laravel默认connection是mysql
        // 则返回的是MysqlBuilder,也就是\Illuminate\Database\Schema\Builder的子类
        return static::$app['db']->connection()->getSchemaBuilder();
    }

根据注释就知道Schema::create就是等同于MysqlBuilder::create(),看下源码:

    // \Illuminate\Database\Schema\Builder
    /**
     * Create a new table on the schema.
     *
     * @param  string    $table
     * @param  \Closure  $callback
     * @return \Illuminate\Database\Schema\Blueprint
     */
    public function create($table, Closure $callback)
    {
        /** @var \Illuminate\Database\Schema\Blueprint $blueprint */
        $blueprint = $this->createBlueprint($table);

        // 添加'create'命令
        $blueprint->create();

        // 执行闭包里的操作,也就是操作columns
        $callback($blueprint);

        $this->build($blueprint);
    }
    
    protected function createBlueprint($table, Closure $callback = null)
    {
        if (isset($this->resolver)) {
            return call_user_func($this->resolver, $table, $callback);
        }

        return new Blueprint($table, $callback);
    }
    
    // Illuminate\Database\Schema\Blueprint
    public function create()
    {
        return $this->addCommand('create');
    }
    
    protected function build(Blueprint $blueprint)
    {
        $blueprint->build($this->connection, $this->grammar);
    }

create()中的$callback($blueprint);语句执行了闭包操作,并且闭包参数还是个Blueprint对象。最关键的方法时Blueprint对象的build()方法,下文再聊具体细节。

当然,Schema Builder是可以在任意模块中使用的,如在路由中使用,执行https://localhost:8888/create_accounts就可以创建一个accounts表了:

Route::get('create_accounts', function () {
    \Illuminate\Support\Facades\Schema::create('accounts', function(\Illuminate\Database\Schema\Blueprint $table)
    {
        $table->increments('id');
        $table->string('name');
        $table->string('number');
        $table->tinyInteger('status');
        $table->enum('source', ['bank account', 'credit card', 'investment account']);
        $table->timestamps();
    });
});

(2)dropping tables

Schema Builder提供了两个方法来删除表:drop(string $table)dropIfExists(string $table),参数是表名,dropIfExists()表示只有在表存在才删除,所以dropIfExists()drop()更优雅。看下两个方法的源码:

    /**
     * Drop a table from the schema.
     *
     * @param  string  $table
     * @return \Illuminate\Database\Schema\Blueprint
     */
    public function drop($table)
    {
        $blueprint = $this->createBlueprint($table);

        $blueprint->drop();

        $this->build($blueprint);
    }

    /**
     * Drop a table from the schema if it exists.
     *
     * @param  string  $table
     * @return \Illuminate\Database\Schema\Blueprint
     */
    public function dropIfExists($table)
    {
        $blueprint = $this->createBlueprint($table);

        $blueprint->dropIfExists();

        $this->build($blueprint);
    }
    
    // Illuminate\Database\Schema\Blueprint
    public function drop()
    {
        return $this->addCommand('drop');
    }
    public function dropIfExists()
    {
        return $this->addCommand('dropIfExists');
    }

同样是使用了Blueprint对象来添加命令dropdropIfExists。在路由中删除accounts表:

Route::get('delete_accounts', function () {
    \Illuminate\Support\Facades\Schema::dropIfExists('accounts');
//    \Illuminate\Support\Facades\Schema::drop('accounts');
});

(3)updating tables

更新表的操作包括更新表名和更新表字段。

使用Schema::rename($from, $to)方法来更新表名:

Route::get('rename_bank_accounts', function () {
    \Illuminate\Support\Facades\Schema::rename('accounts', 'bank_accounts');
});

看下Schema的rename()源码,同样是使用Blueprint对象添加rename命令:

    public function rename($from, $to)
    {
        $blueprint = $this->createBlueprint($from);

        $blueprint->rename($to);

        $this->build($blueprint);
    }
    
    
    // Illuminate\Database\Schema\Blueprint
    public function rename($to)
    {
        return $this->addCommand('rename', compact('to'));
    }

使用Schema::table()方法来更新表字段值,如更新accountsnumber字段,,不过如果该表中有字段类型为enum就不支持修改:

Route::get('update_accounts', function () {
    \Illuminate\Support\Facades\Schema::table('accounts', function(\Illuminate\Database\Schema\Blueprint $table)
    {
        $table->string('number', 50)->change();
    });
});

同时,Schema还提供了几个有用的方法,如hasTable($table),hasColumn($table, $column),hasColumns($table, array $column),getColumnListing($table):

Route::get('get_column_listing', function () {
//    if (\Illuminate\Support\Facades\Schema::hasTable('accounts'))
//    if (\Illuminate\Support\Facades\Schema::hasColumn('accounts', 'name'))
    if (\Illuminate\Support\Facades\Schema::hasColumns('accounts', ['name']))
    {
        // ['id', 'name', 'number', 'status', 'source', 'created_at', 'updated_at']
        return \Illuminate\Support\Facades\Schema::getColumnListing('accounts');
    }
});

字段的操作-column

(1)adding columns

设计database时需要添加columns,上文说过这段逻辑是在Schema::create(),Schema::table()的闭包里执行的,利用Blueprint对象来依次添加每一个column字段属性和Mysql中数据类型对应,如:

\Illuminate\Support\Facades\Schema::create('accounts', function(\Illuminate\Database\Schema\Blueprint $table)
    {
        $table->string('name'); // => $this->addColumn('string', $column, compact('length'));
    });

常用的Schema字段类型函数表如下:

Schema Column TypeMySQL Column Type
bigIncrements('id')id UNSIGNED BIGINT
bigInteger('number')number BIGINT
binary('data')data BLOB
boolean('is_viewed')is_viewed BOOLEAN
char('title', 50)title CHAR(50)
date('created_at')created_at DATE
dateTime('updated_at')updated_at DATETIME
decimal('amount', 2, 2)amount DECIMAL(2,2)
double('length', 10, 10)length DOUBLE(10, 10)
enum('source', ['fund', 'equity'])source ENUM('fund', 'equity')
float('width', 5, 5)width FLOAT(5, 5)
json('options')options JSON
string('content')content VARCHAR(255)
text('description')description TEXT
......

``

(2)removing columns

Schema提供了dropColumn()方法来删除表中字段:

Route::get('drop_column', function () {
    \Illuminate\Support\Facades\Schema::table('accounts', function(\Illuminate\Database\Schema\Blueprint $table)
    {
        $table->dropColumn(['number', 'status']);
    });
});

(3)renaming columns

Schema提供了renameColumn()来修改column名称,不过如果该表中有字段类型为enum就不支持修改:

Route::get('rename_column', function () {
    \Illuminate\Support\Facades\Schema::table('accounts', function(\Illuminate\Database\Schema\Blueprint $table)
    {
        $table->renameColumn('name', 'title');
    });
});

索引操作-index

(1)simple index

Schema提供了index()方法来给column加索引,且索引名称约定为table-name_column-name_index-type:

Route::get('index_column', function () {
    \Illuminate\Support\Facades\Schema::table('accounts', function(\Illuminate\Database\Schema\Blueprint $table)
    {
        $table->index('name');
    });
});

同时,Schema提供了dropIndex('table-name_column-name_index')来删除simple index。

(2)unique index

Schema提供了unique()方法来给column加索引,且索引名称约定为table-name_column-name_index-type:

Route::get('unique_column', function () {
    \Illuminate\Support\Facades\Schema::table('accounts', function(\Illuminate\Database\Schema\Blueprint $table)
    {
        $table->unique(['title']);
    });
});

同时,Schema提供了dropUnique('table-name_column-name_unique')来删除unique index。

(3)foreign key

Schema提供了foreign()->reference()->on() fluent api来设计foreign key,且索引名称约定为table-name_column-name_index-type:

Route::get('foreign_key_column', function () {
    \Illuminate\Support\Facades\Schema::create('bills', function(\Illuminate\Database\Schema\Blueprint $table)
    {
        $table->increments('id');
        $table->unsignedInteger('account_id');
        $table->float('amount', 5, 5);

        $table->foreign('account_id')
            ->references('id')
            ->on('accounts')
            ->onUpdate('CASCADE')
            ->onDelete('CASCADE');
    });
});

同时,Schema提供了dropForeign('table-name_column-name_foreign')来删除foreign index。

总结:本篇主要学习下Laravel使用了Schema和Blueprint两个类来设计database,中篇将以Schema::create()为例仔细研究下源码是如何转换为SQL并执行SQL语句的,下篇聊下Migration System的使用及其原理。到时见。

RightCapital招聘Laravel DevOps

查看原文

逆袭清风 赞了文章 · 2020-08-11

Laravel学习笔记之Schema Builder 和 Migration System(上)

说明:本文主要学习Schema Builder和Migration System的使用及相关原理。传统上在设计database时需要写大量的SQL语句,但Laravel提供了Schema Builder这个神器使得在设计database时使用面向对象方法来做,不需要写一行SQL,并且还提供了另一个神器Migration System,可以对database做版本控制,包括回滚上一次的迁移操作。本小系列主要分为上中下三篇。本篇主要学习使用Schema Builder来creating,dropping,updating tables;adding,removing,renaming columns;simple index,unique index,foreign keys,同时也会学习相关源码来进一步了解Schema Builder。

开发环境: Laravel5.3 + PHP7

表的操作-tables

在设计database时需要创建、删除和更新表,Schema Builder类提供了一些methods来面向对象的执行这些操作,而不需要写一行SQL。在写Laravel程序时,也经常使用类似命令php artisan make:migration create_accounts_table --create=accounts来做一个迁移类创建数据表,会在database/migrations文件夹下得到类似如下的代码类:

use Illuminate\Support\Facades\Schema;
use Illuminate\Database\Schema\Blueprint;
use Illuminate\Database\Migrations\Migration;

class CreateAccountsTable extends Migration
{
    /**
     * Run the migrations.
     *
     * @return void
     */
    public function up()
    {
        Schema::create('accounts', function (Blueprint $table) {
            $table->increments('id');
            $table->timestamps();
        });
    }

    /**
     * Reverse the migrations.
     *
     * @return void
     */
    public function down()
    {
        Schema::dropIfExists('accounts');
    }
}

(1)creating tables

在执行php artisan migrate命令时操作的是up()方法中语句。在创建的迁移类CreateAccountsTable中,Schema::create()就是创建表的语句,并且第一个参数就是表的名字,第二个参数是个闭包,是操作columns的语句,并且参数是个Blueprint对象。为什么有这么奇怪的写法呢?

看下Schema Facade中getFacadeAccessor的源码:

    /**
     * Get a schema builder instance for the default connection.
     *
     * @return \Illuminate\Database\Schema\Builder
     */
    protected static function getFacadeAccessor()
    {
        // 这里'db'服务是在DatabaseServiceProvider中定义的,是DatabaseManager对象,且laravel默认connection是mysql
        // 则返回的是MysqlBuilder,也就是\Illuminate\Database\Schema\Builder的子类
        return static::$app['db']->connection()->getSchemaBuilder();
    }

根据注释就知道Schema::create就是等同于MysqlBuilder::create(),看下源码:

    // \Illuminate\Database\Schema\Builder
    /**
     * Create a new table on the schema.
     *
     * @param  string    $table
     * @param  \Closure  $callback
     * @return \Illuminate\Database\Schema\Blueprint
     */
    public function create($table, Closure $callback)
    {
        /** @var \Illuminate\Database\Schema\Blueprint $blueprint */
        $blueprint = $this->createBlueprint($table);

        // 添加'create'命令
        $blueprint->create();

        // 执行闭包里的操作,也就是操作columns
        $callback($blueprint);

        $this->build($blueprint);
    }
    
    protected function createBlueprint($table, Closure $callback = null)
    {
        if (isset($this->resolver)) {
            return call_user_func($this->resolver, $table, $callback);
        }

        return new Blueprint($table, $callback);
    }
    
    // Illuminate\Database\Schema\Blueprint
    public function create()
    {
        return $this->addCommand('create');
    }
    
    protected function build(Blueprint $blueprint)
    {
        $blueprint->build($this->connection, $this->grammar);
    }

create()中的$callback($blueprint);语句执行了闭包操作,并且闭包参数还是个Blueprint对象。最关键的方法时Blueprint对象的build()方法,下文再聊具体细节。

当然,Schema Builder是可以在任意模块中使用的,如在路由中使用,执行https://localhost:8888/create_accounts就可以创建一个accounts表了:

Route::get('create_accounts', function () {
    \Illuminate\Support\Facades\Schema::create('accounts', function(\Illuminate\Database\Schema\Blueprint $table)
    {
        $table->increments('id');
        $table->string('name');
        $table->string('number');
        $table->tinyInteger('status');
        $table->enum('source', ['bank account', 'credit card', 'investment account']);
        $table->timestamps();
    });
});

(2)dropping tables

Schema Builder提供了两个方法来删除表:drop(string $table)dropIfExists(string $table),参数是表名,dropIfExists()表示只有在表存在才删除,所以dropIfExists()drop()更优雅。看下两个方法的源码:

    /**
     * Drop a table from the schema.
     *
     * @param  string  $table
     * @return \Illuminate\Database\Schema\Blueprint
     */
    public function drop($table)
    {
        $blueprint = $this->createBlueprint($table);

        $blueprint->drop();

        $this->build($blueprint);
    }

    /**
     * Drop a table from the schema if it exists.
     *
     * @param  string  $table
     * @return \Illuminate\Database\Schema\Blueprint
     */
    public function dropIfExists($table)
    {
        $blueprint = $this->createBlueprint($table);

        $blueprint->dropIfExists();

        $this->build($blueprint);
    }
    
    // Illuminate\Database\Schema\Blueprint
    public function drop()
    {
        return $this->addCommand('drop');
    }
    public function dropIfExists()
    {
        return $this->addCommand('dropIfExists');
    }

同样是使用了Blueprint对象来添加命令dropdropIfExists。在路由中删除accounts表:

Route::get('delete_accounts', function () {
    \Illuminate\Support\Facades\Schema::dropIfExists('accounts');
//    \Illuminate\Support\Facades\Schema::drop('accounts');
});

(3)updating tables

更新表的操作包括更新表名和更新表字段。

使用Schema::rename($from, $to)方法来更新表名:

Route::get('rename_bank_accounts', function () {
    \Illuminate\Support\Facades\Schema::rename('accounts', 'bank_accounts');
});

看下Schema的rename()源码,同样是使用Blueprint对象添加rename命令:

    public function rename($from, $to)
    {
        $blueprint = $this->createBlueprint($from);

        $blueprint->rename($to);

        $this->build($blueprint);
    }
    
    
    // Illuminate\Database\Schema\Blueprint
    public function rename($to)
    {
        return $this->addCommand('rename', compact('to'));
    }

使用Schema::table()方法来更新表字段值,如更新accountsnumber字段,,不过如果该表中有字段类型为enum就不支持修改:

Route::get('update_accounts', function () {
    \Illuminate\Support\Facades\Schema::table('accounts', function(\Illuminate\Database\Schema\Blueprint $table)
    {
        $table->string('number', 50)->change();
    });
});

同时,Schema还提供了几个有用的方法,如hasTable($table),hasColumn($table, $column),hasColumns($table, array $column),getColumnListing($table):

Route::get('get_column_listing', function () {
//    if (\Illuminate\Support\Facades\Schema::hasTable('accounts'))
//    if (\Illuminate\Support\Facades\Schema::hasColumn('accounts', 'name'))
    if (\Illuminate\Support\Facades\Schema::hasColumns('accounts', ['name']))
    {
        // ['id', 'name', 'number', 'status', 'source', 'created_at', 'updated_at']
        return \Illuminate\Support\Facades\Schema::getColumnListing('accounts');
    }
});

字段的操作-column

(1)adding columns

设计database时需要添加columns,上文说过这段逻辑是在Schema::create(),Schema::table()的闭包里执行的,利用Blueprint对象来依次添加每一个column字段属性和Mysql中数据类型对应,如:

\Illuminate\Support\Facades\Schema::create('accounts', function(\Illuminate\Database\Schema\Blueprint $table)
    {
        $table->string('name'); // => $this->addColumn('string', $column, compact('length'));
    });

常用的Schema字段类型函数表如下:

Schema Column TypeMySQL Column Type
bigIncrements('id')id UNSIGNED BIGINT
bigInteger('number')number BIGINT
binary('data')data BLOB
boolean('is_viewed')is_viewed BOOLEAN
char('title', 50)title CHAR(50)
date('created_at')created_at DATE
dateTime('updated_at')updated_at DATETIME
decimal('amount', 2, 2)amount DECIMAL(2,2)
double('length', 10, 10)length DOUBLE(10, 10)
enum('source', ['fund', 'equity'])source ENUM('fund', 'equity')
float('width', 5, 5)width FLOAT(5, 5)
json('options')options JSON
string('content')content VARCHAR(255)
text('description')description TEXT
......

``

(2)removing columns

Schema提供了dropColumn()方法来删除表中字段:

Route::get('drop_column', function () {
    \Illuminate\Support\Facades\Schema::table('accounts', function(\Illuminate\Database\Schema\Blueprint $table)
    {
        $table->dropColumn(['number', 'status']);
    });
});

(3)renaming columns

Schema提供了renameColumn()来修改column名称,不过如果该表中有字段类型为enum就不支持修改:

Route::get('rename_column', function () {
    \Illuminate\Support\Facades\Schema::table('accounts', function(\Illuminate\Database\Schema\Blueprint $table)
    {
        $table->renameColumn('name', 'title');
    });
});

索引操作-index

(1)simple index

Schema提供了index()方法来给column加索引,且索引名称约定为table-name_column-name_index-type:

Route::get('index_column', function () {
    \Illuminate\Support\Facades\Schema::table('accounts', function(\Illuminate\Database\Schema\Blueprint $table)
    {
        $table->index('name');
    });
});

同时,Schema提供了dropIndex('table-name_column-name_index')来删除simple index。

(2)unique index

Schema提供了unique()方法来给column加索引,且索引名称约定为table-name_column-name_index-type:

Route::get('unique_column', function () {
    \Illuminate\Support\Facades\Schema::table('accounts', function(\Illuminate\Database\Schema\Blueprint $table)
    {
        $table->unique(['title']);
    });
});

同时,Schema提供了dropUnique('table-name_column-name_unique')来删除unique index。

(3)foreign key

Schema提供了foreign()->reference()->on() fluent api来设计foreign key,且索引名称约定为table-name_column-name_index-type:

Route::get('foreign_key_column', function () {
    \Illuminate\Support\Facades\Schema::create('bills', function(\Illuminate\Database\Schema\Blueprint $table)
    {
        $table->increments('id');
        $table->unsignedInteger('account_id');
        $table->float('amount', 5, 5);

        $table->foreign('account_id')
            ->references('id')
            ->on('accounts')
            ->onUpdate('CASCADE')
            ->onDelete('CASCADE');
    });
});

同时,Schema提供了dropForeign('table-name_column-name_foreign')来删除foreign index。

总结:本篇主要学习下Laravel使用了Schema和Blueprint两个类来设计database,中篇将以Schema::create()为例仔细研究下源码是如何转换为SQL并执行SQL语句的,下篇聊下Migration System的使用及其原理。到时见。

RightCapital招聘Laravel DevOps

查看原文

赞 3 收藏 11 评论 0

逆袭清风 赞了回答 · 2020-08-10

解决app后台统计用户新增、留存的设计方案

  1. 计算留存用户需要两个东西,每天新增的用户和每天活跃的用户

  2. 新增用户好办,你的用户表里面记录用户创建时间就可以了

  3. 活跃用户就需要记录每次用户访问的日志了。最好把用户这次干了啥也记下来,有助于以后的数据分析

  4. 一开始建议直接上友盟之类的服务在app端统计就可以了,后台不需要参与

关注 2 回答 1

逆袭清风 发布了文章 · 2020-07-30

PHP框架开发之Route路由简单实现

参数Laravel路由的使用方式,来效仿一个简单的路由实现方式

class Route
{
    /**
     * @var $_instance // 实例
     */
    private static $_instance = null;

    /**
     * @var $_currentPath // 当前URL中的地址
     */
    private $_currentPath = '';

    /**
     * @var $_prefix // URL地址中前缀
     */
    private $_prefix = '';

    private function __construct($currentPath)
    {
        $this->_currentPath = $currentPath;
    }

    // 通过静态方法来进行Route路由实例化
    static function getInstance($currentPath)
    {
        if( is_null(self::$_instance) ){
            self::$_instance = new self($currentPath);
        }
        return self::$_instance;
    }

    /**
     * 实现GET请求的路由访问
     *
     * @param string $path //访问路由规则(路径)
     * @param Closure $callback 匿名回调函数
     * 
     * @return Route
     */
    public function get($path,\Closure $callback)
    {
        if( !empty($this->_prefix) ){
            $path = '/'.$this->_prefix.$path;
        }
        if( $path == $this->_currentPath ){
            echo $callback();
            exit;
        }
        return $this;
    }

    /**
     * 实现路由前缀设定
     *
     * @param string $prefix //访问路由前缀(路径)
     * 
     * @return Route
     */
    public function prefix($prefix)
    {
        $this->_prefix = $prefix;
        return $this;
    }

    /**
     * 实现路由分组
     *
     * @param Closure $callback 路由组的具体实现
     * 
     * @return Route
     */
    public function group(\Closure $callback)
    {
        $callback($this);
        return $this;
    }

}

调用示例:


// 通过GET中s参数模拟路由规则的访问
$currentPath = empty($_GET['s']) ? '/index/index' : $_GET['s'];


$route = Route::getInstance($currentPath);

// 实现流程:
// 1.判断当前访问的URL地址是否被设定
// 2.如果被设定则执行get方法中的$callback匿名函数
$route->get('/aaaa',function(){
    return 'Route Http Get Called';
});
$route->get('/bbbb',function(){
    return 'Route Http Get Called 2';
});

// 实现流程:
// 1.设定URL地址前缀:test
// 2.将路由前缀和get的路径规则进行拼接
// 3.执行group方法中$callback匿名函数
// 4.判断当前访问的URL地址是否符合设定的路径规则
// 5.如果被设定则执行get方法中的$callback匿名函数
$route->prefix('test')->group(function($route){
    $route->get('/aaaa',function(){
        return 'Route Group Http Get Called';
    });
});

以上只是个实现思路,欢迎互相探讨交流!

查看原文

赞 0 收藏 0 评论 0

逆袭清风 赞了文章 · 2020-07-30

Mysql设计与开发规范

本规范旨在帮助或指导RD、QA、OP等技术人员做出适合线上业务的数据库设计。在数据库变更和处理流程、数据库表设计、SQL编写等方面予以规范,从而为公司业务系统稳定、健康地运行提供保障

设计规范

以下所有规范会按照【高危】、【强制】、【建议】三个级别进行标注,遵守优先级从高到低

对于不满足【高危】和【强制】两个级别的设计,DBA有权利强制打回要求修改

库名

1.【强制】库的名称必须控制在32个字符以内,相关模块的表名与表名之间尽量体现join的关系,如user表和user_login表

2.【强制】库的名称格式:业务系统名称_子系统名,同一模块使用的库名尽量使用统一前缀

3.【强制】一般分库名称命名格式是库通配名_编号,编号从0开始递增,比如wenda_001以时间进行分库的名称格式是“库通配名_时间”

4.【强制】创建数据库时必须显式指定字符集,并且字符集只能是utf8或者utf8mb4。创建数据库SQL举例:create database db1 default character set utf8;

表结构

1.【强制】表必须有主键,且设置id为自增主键

2.【强制】表禁止使用外键,如果要保证完整下,应由程序端实现,外键使表之间相互耦合,影响update、delete等性能,有可能造成死锁,高并发环境下容易导致数据库性能瓶颈

3.【强制】表和列的名称必须控制在32个字符以内,表名只能使用字母、数字和下划线,一律小写。如表名过长可以采用缩写等方式

4.【强制】创建表时必须显式指定字符集为utf8或utf8mb4

5.【强制】创建表时必须显式指定表存储引擎类型,如无特殊需求,一律为InnoDB。当需要使用除InnoDB/MyISAM/Memory以外的存储引擎时,必须通过DBA审核才能在生产环境中使用。因为Innodb表支持事务、行锁、宕机恢复、MVCC等关系型数据库重要特性,为业界使用最多的MySQL存储引擎。而这是其他大多数存储引擎不具备的,因此首推InnoDB

6.【强制】建表必须有comment,表级别和字段级别都要有comment

7.【建议】建表时关于主键:(1)强制要求主键为id,类型为int或bigint(为了以后延展性,这里要求新建表统一为bigint),且为auto_increment(2)标识表里每一行主体的字段不要设为主键,建议设为其他字段如user_id,order_id等,并建立unique key索引。因为如果设为主键且主键值为随机插入,则会导致innodb内部page分裂和大量随机I/O,性能下降

8.【建议】核心表(如用户表,金钱相关的表)必须有行数据的创建时间字段create_time和最后更新时间字段update_time,便于查问题

9.【建议】表中所有字段必须都是NOT NULL default 默认值 属性,业务可以根据需要定义DEFAULT值。因为使用NULL值会存在每一行都会占用额外存储空间、数据迁移容易出错、聚合函数计算结果偏差以及索引失效等问题

10.【建议】建议对表里的blob、text等大字段,垂直拆分到其他表里,仅在需要读这些对象的时候才去select

11.【建议】反范式设计:把经常需要join查询的字段,在其他表里冗余一份。如user_name属性在user_account,user_login_log等表里冗余一份,减少join查询

12.【强制】中间表用于保留中间结果集,名称必须以tmp_开头。备份表用于备份或抓取源表快照,名称必须以bak_开头。中间表和备份表定期清理

13.【强制】对于线上执行DDL变更,必须经过DBA审核,并由DBA在业务低峰期执行

列数据类型优化

1.【建议】表中的自增列(auto_increment属性),推荐使用bigint类型。因为无符号int存储范围为-2147483648~2147483647(大约21亿左右),溢出后会导致报错

2.【建议】业务中选择性很少的状态status、类型type等字段推荐使用tinytint或者smallint类型节省存储空

3.【建议】业务中IP地址字段推荐使用int类型,不推荐用char(15)。因为int只占4字节,可以用如下函数相互转换,而char(15)占用至少15字节。一旦表数据行数到了1亿,那么要多用1.1G存储空间。 SQL:select inet_aton('192.168.2.12'); select inet_ntoa(3232236044); PHP: ip2long(‘192.168.2.12’); long2ip(3530427185);

4.【建议】不推荐使用enum,set。 因为它们浪费空间,且枚举值写死了,变更不方便。推荐使用tinyint或smallint

5.【建议】不推荐使用blob,text等类型。它们都比较浪费硬盘和内存空间。在加载表数据时,会读取大字段到内存里从而浪费内存空间,影响系统性能。建议和PM、RD沟通,是否真的需要这么大字段

6.【建议】存储金钱的字段,建议用int,程序端乘以100和除以100进行存取。或者用decimal类型,而不要用double

7.【建议】文本数据尽量用varchar存储。因为varchar是变长存储,比char更省空间。MySQL server层规定一行所有文本最多存65535字节

8.【建议】时间类型尽量选取datetime。而timestamp虽然占用空间少,但是有时间范围为1970-01-01 00:00:01到2038-01-01 00:00:00的问题

索引设计

1.【强制】InnoDB表必须主键为id int/bigint auto_increment,且主键值禁止被更新

2.【建议】唯一键以“uk_”或“uq_”开头,普通索引以“idx_”开头,一律使用小写格式,以字段的名称或缩写作为后缀

3.【强制】InnoDB和MyISAM存储引擎表,索引类型必须为BTREE;MEMORY表可以根据需要选择HASH或者BTREE类型索引

4.【强制】单个索引中每个索引记录的长度不能超过64KB

5.【建议】单个表上的索引个数不能超过5个

6.【建议】在建立索引时,多考虑建立联合索引,并把区分度最高的字段放在最前面。如列userid的区分度可由select count(distinct userid)计算出来

7.【建议】在多表join的SQL里,保证被驱动表的连接列上有索引,这样join执行效率最高

8.【建议】建表或加索引时,保证表里互相不存在冗余索引。对于MySQL来说,如果表里已经存在key(a,b),则key(a)为冗余索引,需要删除

分库分表、分区表

1.【强制】分区表的分区字段(partition-key)必须有索引,或者是组合索引的首列

2.【强制】单个分区表中的分区(包括子分区)个数不能超过1024

3.【强制】上线前RD或者DBA必须指定分区表的创建、清理策略

4.【强制】访问分区表的SQL必须包含分区键

5.【建议】单个分区文件不超过2G,总大小不超过50G。建议总分区数不超过20个

6.【强制】对于分区表执行alter table操作,必须在业务低峰期执行

7.【强制】采用分库策略的,库的数量不能超过1024

8.【强制】采用分表策略的,表的数量不能超过4096

9.【建议】单个分表建议不超过500W行,这样才能保证数据查询性能更佳

10.【建议】水平分表尽量用取模方式,并预留出足够的buffer,以免日后需要重新拆分和迁移,日志、报表类数据建议采用日期进行分表

字符集

1.【强制】数据库本身库、表、列所有字符集必须保持一致,为utf8或utf8mb4

2.【强制】前端程序字符集或者环境变量中的字符集,与数据库、表的字符集必须一致,统一为utf8

SQL编写

DML语句

1.【强制】SELECT语句必须指定具体字段名称,禁止写成。因为select 会将不该读的数据也从MySQL里读出来,造成网卡压力。且表字段一旦更新,但程序端没有来得及更新的话,系统会报错

2.【强制】insert语句指定具体字段名称,不要写成insert into t1 values(…),道理同上

3.【建议】insert into…values(XX),(XX),(XX)…。这里XX的值不要超过500个。值过多虽然上线很很快,但会引起主从同步延迟

4.【建议】SELECT语句不要使用UNION,推荐使用UNION ALL,并且UNION子句个数限制在3个以内。因为union all不需要去重,节省数据库资源,提高性能

5.【建议】in值列表限制在500以内。例如select… where userid in(….500个以内…),这么做是为了减少底层扫描,减轻数据库压力从而加速查询

6.【建议】事务里批量更新数据需要控制数量,进行必要的sleep,做到少量多次

7.【强制】事务涉及的表必须全部是innodb表。否则一旦失败不会全部回滚,且易造成主从库同步中断

8.【强制】写入和事务发往主库,只读SQL发往从库,即程序端实现读写分离

9.【强制】DML语句必须有where条件,且使用索引查找

10.【强制】生产环境禁止使用hint,如sql_no_cache,force index,ignore key,straight join等。因为hint是用来强制SQL按照某个执行计划来执行,但随着数据量变化我们无法保证自己当初的预判是正确的,我们要尽量让MySQL优化器自己选择执行计划

11.【强制】where条件里等号左右字段类型必须一致,否则无法利用索引

12.【建议】SELECT|UPDATE|DELETE|REPLACE要有WHERE子句,且WHERE子句的条件必需使用索引查找

13.【强制】生产数据库中强烈不推荐大表上发生全表扫描,但对于100行以下的静态表可以全表扫描。查询数据量不要超过表行数的25%,否则不会利用索引

14.【强制】WHERE 子句中禁止只使用全模糊的LIKE条件进行查找,如果要使用like,请使用like ‘xxxx%’的方式,必须有其他等值或范围查询条件,否则无法利用索引

15.【建议】索引列不要使用函数或表达式,否则无法利用索引。如where length(name)='Admin'或where user_id+2=10023

16.【建议】减少使用or语句,可将or语句优化为union,然后在各个where条件上建立索引。如where a=1 or b=2优化为where a=1… union …where b=2, key(a),key(b)

17.【建议】分页查询,当limit起点较高时,可先用过滤条件进行过滤。如select a,b,c from t1 limit 10000,20;优化为: select a,b,c from t1 where id>10000 limit 20;

多表连接

1.【强制】禁止跨db的join语句。因为这样可以减少模块间耦合,为数据库拆分奠定坚实基础

2.【强制】禁止在业务的更新类SQL语句中使用join,比如update t1 join t2…

3.【建议】不建议使用子查询,建议将子查询SQL拆开结合程序多次查询,或使用join来代替子查询

4.【建议】线上环境,多表join不要超过3个表

5.【建议】多表连接查询推荐使用别名,且SELECT列表中要用别名引用字段,数据库.表格式,如select a from db1.table1 alias1 where …

6.【建议】在多表join中,尽量选取结果集较小的表作为驱动表,来join其他表

事务

1.【建议】事务中INSERT|UPDATE|DELETE|REPLACE语句操作的行数控制在1000以内,以及WHERE子句中IN列表的传参个数控制在500以内

2.【建议】批量操作数据时,需要控制事务处理间隔时间,进行必要的sleep,一般建议值1-2秒

3.【建议】对于有auto_increment属性字段的表的插入操作,并发需要控制在200以内

4.【强制】程序设计必须考虑“数据库事务隔离级别”带来的影响,包括脏读、不可重复读和幻读。线上建议事务隔离级别为repeatable-read

5.【建议】事务里包含SQL不超过5个(支付业务除外)。因为过长的事务会导致锁数据较久,MySQL内部缓存、连接消耗过多等雪崩问题

6.【建议】事务里更新语句尽量基于主键或unique key,如update … where id=XX; 否则会产生间隙锁,内部扩大锁定范围,导致系统性能下降,产生死锁

7.【建议】尽量把一些典型外部调用移出事务,如调用webservice,访问文件存储等,从而避免事务过长

8.【建议】对于MySQL主从延迟严格敏感的select语句,请开启事务强制访问主库

排序和分组

1.【建议】减少使用order by,和业务沟通能不排序就不排序,或将排序放到程序端去做。order by、group by、distinct这些语句较为耗费CPU,数据库的CPU资源是极其宝贵的

2.【建议】order by、group by、distinct这些SQL尽量利用索引直接检索出排序好的数据。如where a=1 order by可以利用key(a,b)

3.【建议】包含了order by、group by、distinct这些查询的语句,where条件过滤出来的结果集请保持在1000行以内,否则SQL会很慢

线上禁止使用的SQL语句

1.【高危】禁用update|delete t1 … where a=XX limit XX; 这种带limit的更新语句。如果是非row格式的binlog格式,会导致主从不一致,导致数据错乱。建议加上order by PK

2.【高危】禁止使用关联子查询,如update t1 set … where name in(select name from user where…);效率极其低下

3.【强制】禁用procedure、function、trigger、views、event、外键约束。因为他们消耗数据库资源,降低数据库实例可扩展性。推荐都在程序端实现

4.【建议】禁用insert into …on duplicate key update…、replace into等语句,在高并发环境下,极容易导致死锁

5.【强制】禁止联表更新语句,如update t1,t2 where t1.id=t2.id…

查看原文

赞 22 收藏 16 评论 3

逆袭清风 收藏了文章 · 2020-07-30

Mysql设计与开发规范

本规范旨在帮助或指导RD、QA、OP等技术人员做出适合线上业务的数据库设计。在数据库变更和处理流程、数据库表设计、SQL编写等方面予以规范,从而为公司业务系统稳定、健康地运行提供保障

设计规范

以下所有规范会按照【高危】、【强制】、【建议】三个级别进行标注,遵守优先级从高到低

对于不满足【高危】和【强制】两个级别的设计,DBA有权利强制打回要求修改

库名

1.【强制】库的名称必须控制在32个字符以内,相关模块的表名与表名之间尽量体现join的关系,如user表和user_login表

2.【强制】库的名称格式:业务系统名称_子系统名,同一模块使用的库名尽量使用统一前缀

3.【强制】一般分库名称命名格式是库通配名_编号,编号从0开始递增,比如wenda_001以时间进行分库的名称格式是“库通配名_时间”

4.【强制】创建数据库时必须显式指定字符集,并且字符集只能是utf8或者utf8mb4。创建数据库SQL举例:create database db1 default character set utf8;

表结构

1.【强制】表必须有主键,且设置id为自增主键

2.【强制】表禁止使用外键,如果要保证完整下,应由程序端实现,外键使表之间相互耦合,影响update、delete等性能,有可能造成死锁,高并发环境下容易导致数据库性能瓶颈

3.【强制】表和列的名称必须控制在32个字符以内,表名只能使用字母、数字和下划线,一律小写。如表名过长可以采用缩写等方式

4.【强制】创建表时必须显式指定字符集为utf8或utf8mb4

5.【强制】创建表时必须显式指定表存储引擎类型,如无特殊需求,一律为InnoDB。当需要使用除InnoDB/MyISAM/Memory以外的存储引擎时,必须通过DBA审核才能在生产环境中使用。因为Innodb表支持事务、行锁、宕机恢复、MVCC等关系型数据库重要特性,为业界使用最多的MySQL存储引擎。而这是其他大多数存储引擎不具备的,因此首推InnoDB

6.【强制】建表必须有comment,表级别和字段级别都要有comment

7.【建议】建表时关于主键:(1)强制要求主键为id,类型为int或bigint(为了以后延展性,这里要求新建表统一为bigint),且为auto_increment(2)标识表里每一行主体的字段不要设为主键,建议设为其他字段如user_id,order_id等,并建立unique key索引。因为如果设为主键且主键值为随机插入,则会导致innodb内部page分裂和大量随机I/O,性能下降

8.【建议】核心表(如用户表,金钱相关的表)必须有行数据的创建时间字段create_time和最后更新时间字段update_time,便于查问题

9.【建议】表中所有字段必须都是NOT NULL default 默认值 属性,业务可以根据需要定义DEFAULT值。因为使用NULL值会存在每一行都会占用额外存储空间、数据迁移容易出错、聚合函数计算结果偏差以及索引失效等问题

10.【建议】建议对表里的blob、text等大字段,垂直拆分到其他表里,仅在需要读这些对象的时候才去select

11.【建议】反范式设计:把经常需要join查询的字段,在其他表里冗余一份。如user_name属性在user_account,user_login_log等表里冗余一份,减少join查询

12.【强制】中间表用于保留中间结果集,名称必须以tmp_开头。备份表用于备份或抓取源表快照,名称必须以bak_开头。中间表和备份表定期清理

13.【强制】对于线上执行DDL变更,必须经过DBA审核,并由DBA在业务低峰期执行

列数据类型优化

1.【建议】表中的自增列(auto_increment属性),推荐使用bigint类型。因为无符号int存储范围为-2147483648~2147483647(大约21亿左右),溢出后会导致报错

2.【建议】业务中选择性很少的状态status、类型type等字段推荐使用tinytint或者smallint类型节省存储空

3.【建议】业务中IP地址字段推荐使用int类型,不推荐用char(15)。因为int只占4字节,可以用如下函数相互转换,而char(15)占用至少15字节。一旦表数据行数到了1亿,那么要多用1.1G存储空间。 SQL:select inet_aton('192.168.2.12'); select inet_ntoa(3232236044); PHP: ip2long(‘192.168.2.12’); long2ip(3530427185);

4.【建议】不推荐使用enum,set。 因为它们浪费空间,且枚举值写死了,变更不方便。推荐使用tinyint或smallint

5.【建议】不推荐使用blob,text等类型。它们都比较浪费硬盘和内存空间。在加载表数据时,会读取大字段到内存里从而浪费内存空间,影响系统性能。建议和PM、RD沟通,是否真的需要这么大字段

6.【建议】存储金钱的字段,建议用int,程序端乘以100和除以100进行存取。或者用decimal类型,而不要用double

7.【建议】文本数据尽量用varchar存储。因为varchar是变长存储,比char更省空间。MySQL server层规定一行所有文本最多存65535字节

8.【建议】时间类型尽量选取datetime。而timestamp虽然占用空间少,但是有时间范围为1970-01-01 00:00:01到2038-01-01 00:00:00的问题

索引设计

1.【强制】InnoDB表必须主键为id int/bigint auto_increment,且主键值禁止被更新

2.【建议】唯一键以“uk_”或“uq_”开头,普通索引以“idx_”开头,一律使用小写格式,以字段的名称或缩写作为后缀

3.【强制】InnoDB和MyISAM存储引擎表,索引类型必须为BTREE;MEMORY表可以根据需要选择HASH或者BTREE类型索引

4.【强制】单个索引中每个索引记录的长度不能超过64KB

5.【建议】单个表上的索引个数不能超过5个

6.【建议】在建立索引时,多考虑建立联合索引,并把区分度最高的字段放在最前面。如列userid的区分度可由select count(distinct userid)计算出来

7.【建议】在多表join的SQL里,保证被驱动表的连接列上有索引,这样join执行效率最高

8.【建议】建表或加索引时,保证表里互相不存在冗余索引。对于MySQL来说,如果表里已经存在key(a,b),则key(a)为冗余索引,需要删除

分库分表、分区表

1.【强制】分区表的分区字段(partition-key)必须有索引,或者是组合索引的首列

2.【强制】单个分区表中的分区(包括子分区)个数不能超过1024

3.【强制】上线前RD或者DBA必须指定分区表的创建、清理策略

4.【强制】访问分区表的SQL必须包含分区键

5.【建议】单个分区文件不超过2G,总大小不超过50G。建议总分区数不超过20个

6.【强制】对于分区表执行alter table操作,必须在业务低峰期执行

7.【强制】采用分库策略的,库的数量不能超过1024

8.【强制】采用分表策略的,表的数量不能超过4096

9.【建议】单个分表建议不超过500W行,这样才能保证数据查询性能更佳

10.【建议】水平分表尽量用取模方式,并预留出足够的buffer,以免日后需要重新拆分和迁移,日志、报表类数据建议采用日期进行分表

字符集

1.【强制】数据库本身库、表、列所有字符集必须保持一致,为utf8或utf8mb4

2.【强制】前端程序字符集或者环境变量中的字符集,与数据库、表的字符集必须一致,统一为utf8

SQL编写

DML语句

1.【强制】SELECT语句必须指定具体字段名称,禁止写成。因为select 会将不该读的数据也从MySQL里读出来,造成网卡压力。且表字段一旦更新,但程序端没有来得及更新的话,系统会报错

2.【强制】insert语句指定具体字段名称,不要写成insert into t1 values(…),道理同上

3.【建议】insert into…values(XX),(XX),(XX)…。这里XX的值不要超过500个。值过多虽然上线很很快,但会引起主从同步延迟

4.【建议】SELECT语句不要使用UNION,推荐使用UNION ALL,并且UNION子句个数限制在3个以内。因为union all不需要去重,节省数据库资源,提高性能

5.【建议】in值列表限制在500以内。例如select… where userid in(….500个以内…),这么做是为了减少底层扫描,减轻数据库压力从而加速查询

6.【建议】事务里批量更新数据需要控制数量,进行必要的sleep,做到少量多次

7.【强制】事务涉及的表必须全部是innodb表。否则一旦失败不会全部回滚,且易造成主从库同步中断

8.【强制】写入和事务发往主库,只读SQL发往从库,即程序端实现读写分离

9.【强制】DML语句必须有where条件,且使用索引查找

10.【强制】生产环境禁止使用hint,如sql_no_cache,force index,ignore key,straight join等。因为hint是用来强制SQL按照某个执行计划来执行,但随着数据量变化我们无法保证自己当初的预判是正确的,我们要尽量让MySQL优化器自己选择执行计划

11.【强制】where条件里等号左右字段类型必须一致,否则无法利用索引

12.【建议】SELECT|UPDATE|DELETE|REPLACE要有WHERE子句,且WHERE子句的条件必需使用索引查找

13.【强制】生产数据库中强烈不推荐大表上发生全表扫描,但对于100行以下的静态表可以全表扫描。查询数据量不要超过表行数的25%,否则不会利用索引

14.【强制】WHERE 子句中禁止只使用全模糊的LIKE条件进行查找,如果要使用like,请使用like ‘xxxx%’的方式,必须有其他等值或范围查询条件,否则无法利用索引

15.【建议】索引列不要使用函数或表达式,否则无法利用索引。如where length(name)='Admin'或where user_id+2=10023

16.【建议】减少使用or语句,可将or语句优化为union,然后在各个where条件上建立索引。如where a=1 or b=2优化为where a=1… union …where b=2, key(a),key(b)

17.【建议】分页查询,当limit起点较高时,可先用过滤条件进行过滤。如select a,b,c from t1 limit 10000,20;优化为: select a,b,c from t1 where id>10000 limit 20;

多表连接

1.【强制】禁止跨db的join语句。因为这样可以减少模块间耦合,为数据库拆分奠定坚实基础

2.【强制】禁止在业务的更新类SQL语句中使用join,比如update t1 join t2…

3.【建议】不建议使用子查询,建议将子查询SQL拆开结合程序多次查询,或使用join来代替子查询

4.【建议】线上环境,多表join不要超过3个表

5.【建议】多表连接查询推荐使用别名,且SELECT列表中要用别名引用字段,数据库.表格式,如select a from db1.table1 alias1 where …

6.【建议】在多表join中,尽量选取结果集较小的表作为驱动表,来join其他表

事务

1.【建议】事务中INSERT|UPDATE|DELETE|REPLACE语句操作的行数控制在1000以内,以及WHERE子句中IN列表的传参个数控制在500以内

2.【建议】批量操作数据时,需要控制事务处理间隔时间,进行必要的sleep,一般建议值1-2秒

3.【建议】对于有auto_increment属性字段的表的插入操作,并发需要控制在200以内

4.【强制】程序设计必须考虑“数据库事务隔离级别”带来的影响,包括脏读、不可重复读和幻读。线上建议事务隔离级别为repeatable-read

5.【建议】事务里包含SQL不超过5个(支付业务除外)。因为过长的事务会导致锁数据较久,MySQL内部缓存、连接消耗过多等雪崩问题

6.【建议】事务里更新语句尽量基于主键或unique key,如update … where id=XX; 否则会产生间隙锁,内部扩大锁定范围,导致系统性能下降,产生死锁

7.【建议】尽量把一些典型外部调用移出事务,如调用webservice,访问文件存储等,从而避免事务过长

8.【建议】对于MySQL主从延迟严格敏感的select语句,请开启事务强制访问主库

排序和分组

1.【建议】减少使用order by,和业务沟通能不排序就不排序,或将排序放到程序端去做。order by、group by、distinct这些语句较为耗费CPU,数据库的CPU资源是极其宝贵的

2.【建议】order by、group by、distinct这些SQL尽量利用索引直接检索出排序好的数据。如where a=1 order by可以利用key(a,b)

3.【建议】包含了order by、group by、distinct这些查询的语句,where条件过滤出来的结果集请保持在1000行以内,否则SQL会很慢

线上禁止使用的SQL语句

1.【高危】禁用update|delete t1 … where a=XX limit XX; 这种带limit的更新语句。如果是非row格式的binlog格式,会导致主从不一致,导致数据错乱。建议加上order by PK

2.【高危】禁止使用关联子查询,如update t1 set … where name in(select name from user where…);效率极其低下

3.【强制】禁用procedure、function、trigger、views、event、外键约束。因为他们消耗数据库资源,降低数据库实例可扩展性。推荐都在程序端实现

4.【建议】禁用insert into …on duplicate key update…、replace into等语句,在高并发环境下,极容易导致死锁

5.【强制】禁止联表更新语句,如update t1,t2 where t1.id=t2.id…

查看原文

逆袭清风 关注了标签 · 2020-07-30

高可用

通常来描述一个系统经过专门的设计,从而减少停工时间,而保持其服务的高度可用性。

关注 22

逆袭清风 关注了标签 · 2020-07-30

关注 138

逆袭清风 关注了标签 · 2020-07-30

关注 156

认证与成就

  • 获得 0 次点赞
  • 获得 5 枚徽章 获得 0 枚金徽章, 获得 1 枚银徽章, 获得 4 枚铜徽章

擅长技能
编辑

开源项目 & 著作
编辑

(゚∀゚ )
暂时没有

注册于 2016-09-30
个人主页被 178 人浏览