分类 MySQL 下的文章

多条件查询及 left join 的利用,几乎在所有项目都会用到。但只有数据量大、并发大时才会有人去注意他的性能。

先来看一个最有代表性的分页存储过程:

ALTER PROCEDURE [dbo].[P_Article_List]  
@page int=1,
@pagesize int=10,
@key VARCHAR(450)=''
AS
BEGIN
    SELECT COUNT(1)OVER(PARTITION BY '''') AS Total,b.NickName,b.Avator,a.*
    FROM dbo.t_article a with (NOLOCK)
    LEFT join t_user b with (NOLOCK) ON b.UserID=a.UserID
    WHERE a.IsValid=1
    AND (@key='' or a.Title like '%'+@key+'%')
    ORDER BY a.order DESC desc
    OFFSET @pagesize*(@page-1) ROWS FETCH NEXT @pagesize ROWS ONLY
END

这是一个很标准的存储过程,这样写代码也很漂亮,但这样的性能很差。通过查看执行计划,你会发现一个很奇怪的问题:

明明是 left join,为什么执行计划里是 inner join 呢,要知道后者的性能是低很多的。

这个问题是因为 where 条件导致的,所以我们要优化它,就必须用新的方式:

先把左边表的数据按条件查询出来放入临时表,再将临时表与右表 left join,不带where

当然,还有一个很重要的因素,分页,我们可以把分页也在临时表里先做了,比如一页10条,这样最后 left join时,左表只有10条数据,这个性能,就会很高

最后贴出优化后的存储过程:

ALTER PROCEDURE [dbo].[P_Article_List]  
@page int=1,
@pagesize int=10,
@key VARCHAR(450)=''
AS
BEGIN
    SELECT COUNT(1)OVER(PARTITION BY '''') AS Total,b.NickName,b.Avator,a.* FROM
    (SELECT * FROM dbo.t_article with (NOLOCK)
        WHERE IsValid=1
        AND (@key='' or Title like '%'+@key+'%')
        ORDER BY order DESC desc
        OFFSET @pagesize*(@page-1) ROWS FETCH NEXT @pagesize ROWS ONLY
    ) a
    LEFT join t_user b with (NOLOCK) ON b.UserID=a.UserID
END

搞定,最后测试,我们数据库的这个存储过程,查询时间从平均 2.3 秒优化到了 0.15 秒!

切换到信息库:

use information_schema;

查询所有数据库的总大小

select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from TABLES;

统计一下所有库数据量

SELECT SUM(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024/1024 AS total_mb FROM information_schema.TABLES;

统计每个库大小

SELECT table_schema,SUM(AVG_ROW_LENGTH*TABLE_ROWS+INDEX_LENGTH)/1024/1024 AS total_mb FROM information_schema.TABLES group by table_schema;
select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from TABLES where table_schema='h0118';

查看所有数据库各容量大小:

select table_schema as '数据库',sum(table_rows) as '记录数',sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)' from information_schema.tables group by table_schema order by sum(data_length) desc, sum(index_length) desc; 

查看所有数据库各表容量大小

select table_schema as '数据库',table_name as '表名',table_rows as '记录数',truncate(data_length/1024/1024, 2) as '数据容量(MB)',truncate(index_length/1024/1024, 2) as '索引容量(MB)' from information_schema.tables order by data_length desc, index_length desc;

查看指定数据库容量大小

select table_schema as '数据库',sum(table_rows) as '记录数',sum(truncate(data_length/1024/1024, 2)) as '数据容量(MB)',sum(truncate(index_length/1024/1024, 2)) as '索引容量(MB)' from information_schema.tables where table_schema='h0118';

查看指定数据库各表容量大小

select table_schema as '数据库',table_name as '表名',table_rows as '记录数',truncate(data_length/1024/1024, 2) as '数据容量(MB)',truncate(index_length/1024/1024, 2) as '索引容量(MB)' from information_schema.tables where table_schema='h0118'
order by data_length desc, index_length desc;

一、下载地址:http://dev.mysql.com/downloads/mysql/
1.进入官网下载,显示的应该是最新版本,选择第二个(mysql5.7.20-winx64.zip)
2.下载完成后,直接解压到自定义目录,解压目录就是安装目录

二、配置环境变量
1.新增环境变量,例:
变量名:MYSQL_HOME
变量值:D:\mysql\mysql5.7.20-winx64

2.修改环境变量PATH
在PATH后面加入%MYSQL_HOME%\bin,注:加入新的变量值需要用;隔开

三、添加my.ini配置文件
1.下载的压缩文件中没有my.ini配置文件和data文件夹,需要手动在根目录(D:\mysql\mysql5.7.20-winx64\)下新建文本my.ini,如果 data 不能自动生成就放 bin 目录下,my.ini,内容如下:

[mysqld]
bind-address = 0.0.0.0
port = 8020
explicit_defaults_for_timestamp = true
character-set-client-handshake = FALSE
character-set-server = UTF8MB4

tmpdir="d:/tools/mysql-8.0.22-winx64/tmp"
basedir="d:/tools/mysql-8.0.22-winx64"
tmpdir="d:/tools/mysql-8.0.22-winx64/data"
max_connections=200
default-authentication-plugin=mysql_native_password
#skip_grant_tables

[mysql]
default-character-set = UTF8MB4

[mysql.server]
default-character-set = UTF8MB4

[client]
default-character-set = UTF8MB4

[WinMySQLadmin]  
Server = "d:/tools/mysql-8.0.22-winx64/bin/mysqld.exe"

四、以管理员身份运行命令行cmd,进入bin目录(一定要进入bin目录)

mysqld --initialize --user=mysql --console (生成临时密码,要记住)
mysqld --install
net start mysql
mysql -P8020 -uroot -p    (输入刚才的临时密码)
SET PASSWORD FOR 'root'@'localhost' = 'password';
use mysql;
update user set host='%' where user='root';
flush privileges;
grant system_user on *.* to 'root';  //root还缺一些权限,不能操作其它用户,需要给予所有权限

五、成功
其他:移除服务命令,在bin目录下(mysqld --remove)

备份 MySql 数据库:

mysqldump -uroot -pPassword mydb | gzip > /home/backup/mydb_$(date +%Y%m%d_%H%M%S).sql.gz
mysqldump -P8010 -uroot -pPassword mydb > D:\data\bak\mydb.sql

备份SP:

mysqldump -uroot -pPassword -n -t -d -R mydb > /home/backup/mydb_sp.sql
mysqldump -P8010 -uroot -pPassword -n -t -d -R mydb > D:\data\bak\mydb_sp.sql

删除10天前的备份:

find /home/backup -mtime +10 -name "*.*" -exec rm -rf {} \;

windows版本下:

D:\tools\mysql-8.0.17-winx64\bin>mysqldump -uroot -pPassword crms > D:\crms_db.sql
D:\tools\mysql-8.0.17-winx64\bin>mysqldump -uroot -pPassword -n -t -d -R crms > D:\crms_sp.sql

创建数据库:

create database allchat;

还原数据库:

source D:\sql.sql

增加用户、修改密码登录、远程访问及权限

CREATE USER 'operator'@'%' IDENTIFIED BY 'Password';
ALTER USER 'operator'@'%' IDENTIFIED WITH mysql_native_password BY 'Password';
grant all privileges on ball.* to 'operator'@'%';
#UPDATE mysql.user set Select_priv='Y',Insert_priv='Y',Update_priv='Y',Delete_priv='Y',Create_priv='Y',Drop_priv='Y',Execute_priv='Y',Alter_priv='Y',create_routine_priv='Y',alter_routine_priv='Y',Grant_priv='Y',Super_priv='Y' where user='operator';
flush privileges;

删除用户:

drop user 'allchat_db'@'localhost';

修改密码:

ALTER USER 'user'@'%' IDENTIFIED WITH mysql_native_password BY'Password';

Ubuntu 服务重启:

/etc/init.d/mysql start|stop|restart|reload|force-reload|status

日志读取:

mysqlbinlog --no-defaults  /var/lib/mysql/ib_logfile0 > /home/test.txt

移除黑名单(IP被阻止):

flush hosts;

mysql 查看版本号:

mysql --version
mysql> status; 

远程连接:

mysql -h192.168.10.28 -P9906 -uroot -p

Ubuntu Mysql 配置文件位置:

cd /etc/mysql/mysql.conf.d
vi mysqld.cnf

迁移安装位置:
修改系统虚拟变量 - 卸载服务[mysqld --remove] - 重启 - 目录复制到新位置 - 修改my.ini - 安装服务[mysqld --install] - 启动

忘记重置密码:

mysqld –shared-memory –skip-grant-tables
FLUSH PRIVILEGES;
ALTER USER 'root'@'localhost' IDENTIFIED WITH mysql_native_password BY 'Password';

下载mysql源安装包

rpm -Uvh https://dev.mysql.com/get/mysql80-community-release-el7-3.noarch.rpm

检查是否安装成功

yum repolist enabled | grep "mysql.*-community.*"

安装

yum install mysql-community-server

启动

systemctl start mysqld
systemctl restart mysqld

查看启动状态

systemctl status mysqld

设置开机启动

systemctl enable mysqld
systemctl daemon-reload

修改登录密码
mysql安装完并启动后,在/var/log/mysqld.log文件中给root生成了一个默认密码。

本地MySQL客户端登录

mysql -uroot -p
ALTER USER 'root'@'localhost' IDENTIFIED BY 'mypassword';

use mysql;
select host, user from user;

允许远程连接

update user set host='%' where user ='root';
flush privileges;

ALTER USER 'root'@'%' IDENTIFIED WITH mysql_native_password BY 'mypassword';

修改侦听:

vi /etc/my.cnf

后面加上:

bind-address=0.0.0.0
port=8020
max_connections=200

修改后如果不能启动:
1.  防火墙问题

firewall-cmd --zone=public --add-port=8020/tcp --permanent
systemctl restart firewalld
  1. SELinux问题
    方法一: 不需要重启Linux:

 [root@dytl05 ~]# setenforce 0

 方法二:需要重启Linux:

vi /etc/selinux/config
SELINUX=disabled

默认编码方式

SHOW VARIABLES WHERE Variable_name LIKE 'character_set_%' OR Variable_name LIKE 'collation%';

部分参数配置查询命令

#查询mysql最大连接数设置
show global variables like 'max_conn%';
SELECT @@MAX_CONNECTIONS AS 'Max Connections';

# 查看最大链接数
show global status like 'Max_used_connections'; 

# 查看慢查询日志是否开启以及日志位置
show variables like 'slow_query%';

# 查看慢查询日志超时记录时间
show variables like 'long_query_time'; 

# 查看链接创建以及现在正在链接数
show status like 'Threads%';

# 查看数据库当前链接
show processlist;

# 查看数据库配置
show variables like '%quer%';


dbbackup.bat

@echo off
set bak_file=D:\data\backup\mydb_%date:~2,2%%date:~5,2%%date:~8,2%%time:~0,2%%time:~3,2%

mysqldump -h127.0.0.1 -P8010 -uroot -pPassword mydb > %bak_file%_db.sql
mysqldump -h127.0.0.1 -P8010 -uroot -pPassword -n -t -d -R allchat > %bak_file%_sp.sql

::zip
makecab /d compressiontype=mszip %bak_file%_db.sql %bak_file%_db.zip
makecab /d compressiontype=mszip %bak_file%_sp.sql %bak_file%_sp.zip

del /f /s /q %bak_file%_db.sql
del /f /s /q %bak_file%_sp.sql

deloldbak

deloldbak.bat

@echo off
title 清理IIS日志文件

:: 日志文件目录
set log_dir="D:\data\backup"

:: 保留日志天数
set bak_dat=15

:: 删除日志文件
forfiles /p %log_dir% /S /M *.zip /D -%bak_dat% /C "cmd /c echo 正在删除@relpath 文件… & echo. & del @file"

:: pause