分类 数据库 下的文章

多条件查询及 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 秒！

这是 Mysql 在查询数据进行排序时，需要用到的缓存不够导致的，默认才几百kb。可以通过以下方式增加：

查询当前大小 sort_buffer_size
select @@global.sort_buffer_size;

临时进行调整 sort_buffer_size
SET GLOBAL sort_buffer_size = 1024 1024 8;

永久性调整 (编辑 my.ini 或 my.cnf 配置文件)
[mysqld]
sort_buffer_size=8M

注一：sort_buffer_size 设定 1024 1024 8 是什么意思？
即为将 sort_buffer_size 设为 8MB

注二：my.ini 或 my.cnf 的 sort_buffer_size 不生效？
修改配置后，需要重启 MySQL 服务。

切换到信息库：

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';

这个存储过程，可以在当前数据库中按关键字查找所有存储过程，非常实用，DBA必备工具：

CREATE PROCEDURE [dbo].[SP] 
     @keywords nvarchar(100)= 'p_Comm_Area_GetList'
AS 
BEGIN 
    SET NOCOUNT ON; 
    SELECT ROUTINE_NAME, ROUTINE_DEFINITION 
    FROM INFORMATION_SCHEMA.ROUTINES 
    WHERE ROUTINE_DEFINITION LIKE '%' + @keywords+ '%' 
    AND ROUTINE_TYPE='PROCEDURE' 
END

用法：

exec sp 'keywords'
  1. 1
  2. 2