oracle笔记

登陆方式:

sqlplus username/pwd as sysdba; 让用户以管理员身份登陆
sqlplus /@实例名 as sysdba; 直接以管理员身份登陆
sqlplus username/pwd@host_id:1521/实例名; 普通用户登陆

sqlplus /nolog;
conn username/pwd@host_id:1521/实例名;

查看表名称:

select table_name from user_tables;
select table_name from all_tables where owner=upper(‘scott’);

mysql分析语句执行时长

Query Profiler是MYSQL5.0.37之后添加的一种query诊断分析工具,通过它可以分析出一条SQL语句的性能瓶颈在什么地方。
使用的explain,以及slow query log都无法精确分析时,可用Query Profiler定位出一条SQL语句执行的各种资源消耗情况。

— 查看数据库版本
select version();

show variables like "%pro%";
— profiling 的值为 OFF 表示功能关闭,可通过 set profiling = 1; 来开启

set profiling = 1; — 执行后, profiling 就是开启状态

show profiles; — 这时再执行这条命令可以查看语句执行消耗的时间

show profile for query 1 ; — 这条语句可查看编号为1的语句各操作的详细耗时

— 可以查看出一条SQL语句执行的各种资源消耗情况,比如CPU,IO等
show profile cpu, block io, memory,swaps,context switches,source for query 6;

show profile all for query 6 查看第6条语句的所有的执行信息。

— 测试完毕后,关闭参数:
set profiling = 0

提升mysql语句执行速度 之 innodb缓存大小调整

背景:table A 有将近40万条数据,下面这个语句要执行:15  28  78 * 55 = 1801800 次,(where条件中的参数每次都会变化)

select avg(col_5), avg(col_10), avg(col_20), avg(col_30), avg(col_40), count(*)
FROM test_db.table_A
where di = 'M'
and (r_60 >= -2 and r_60 <= 10)
and (r_61 >= 4 and r_61 <= 12)
and (r_62 >= 24 and r_62 <= 40);

即使是在索引有生效的情况下,每次查询花费的时间在 1.2秒左右
1801800 * 1.2 / 3600 = 600 小时,约为25天

很明显,要执行完一个批次的分析就要花费将近1个月的时间,这个时间成本是无法接受的。虽然有按参数范围手工分了4个进程来跑,理论上也要执行将近7天才能把所有的参数全部跑完!

mysql数据库是搭建在win10上,任务管理器里查看mysqld.exe的进程,发现该进程只用了54.4M内存,而我的电脑总共有16G的内存,系统层面的还有将近8G的空闲内存,很自然的怀疑是否可以通过给mysql加大内存来提升查询速度。

事实证明,当下面这条语句执行完之后,马上就有了立杆见影的效果。
语句:SET GLOBAL innodb_buffer_pool_size = 3221225472;
变化:原来 innodb_buffer_pool_size 的值是:8388608 (8M), 调整后的值是: 3221225472 (3G)
效果:任务管理器中mysqld.exe进程占用的内存马上变成:350M左右,此时SQL查询速度得到了非常明显的提升,而且原来100M/s的读硬盘的操作消失了。

当把innodb_buffer_pool_size的值修改至6G后,同样的查询执行时间为: 0.36秒, 比起原来8M时的1.2秒有了很大的提升。这个进程mysqld.exe占用的内存将近1G。

文档主要参考了:如何在MySQL中分配innodb_buffer_pool_size

当前配置的innodb_buffer_pool_size是否合适,可以通过分析InnoDB缓冲池的性能来验证。
show status like ‘innodb_buffer_pool_read%’;
可以使用以下公式计算InnoDB缓冲池性能:
Performance = innodb_buffer_pool_reads / innodb_buffer_pool_read_requests * 100
innodb_buffer_pool_reads:表示InnoDB缓冲池无法满足的请求数。需要从磁盘中读取。
innodb_buffer_pool_read_requests:表示从内存中读取逻辑的请求数。

操作过程记录:
show variables like '%innodb_buffer_pool_size%';
show variables like 'innodb_buffer_pool%';
-- innodb_buffer_pool_instances 1
-- innodb_buffer_pool_chunk_size 8388608
-- innodb_buffer_pool_size 8388608 (缓冲池大小必须始终等于或者是innodb_buffer_pool_chunk_size * innodb_buffer_pool_instances的倍数)

-- 在线调整 innodb_buffer_pool_size, 无需重启mysql
SET GLOBAL innodb_buffer_pool_size = 3221225472‬; -- 这个参数调整为3G之后,US分析的速度明显变快 3221225472,4294967296‬
-- 调整进度跟踪
SHOW STATUS WHERE Variable_name='InnoDB_buffer_pool_resize_status';

show status like 'innodb_buffer_pool_read%'; -- '112702592' / '3003766565' = 0.0375204229626945 = 3.75%
-- InnoDB buffer pool 命中率 = innodb_buffer_pool_read_requests / (innodb_buffer_pool_read_requests + innodb_buffer_pool_reads ) * 100
-- = '3003766565'/('3003766565' + '112702592') = 3003766565/3116469157‬ = 96.38%, InnoDB buffer pool 命中率小于 99%, 可以考虑增加innodb_buffer_pool_size

Continue reading 提升mysql语句执行速度 之 innodb缓存大小调整

高质量SQL注意点

  1. 如果只需要取一条记录,要加下 limit 1, 这样可以避免无谓的性能消耗;
  2. 建立了索引,SQL并不一定会用到,一定要先用 explain 先分析下,让索引产生作用;
  3. where 条件中,关键字的顺序要与索引中关键字的顺序一致,才有利于调出索引;
  4. 应尽量避免在where子句中使用or来连接条件,因为这样会导致索引失效,造成全表扫描
    -- 使用union all 
    select * from user where userid=1 
    union all 
    select * from user where age = 18;

    参考资料

mysql 千万级数据提高查询速度

mysql 8.0, 默认engine: InnoDB,数据放在固态硬盘上

表存放有千万级别的数据,虽然有针对 where查询条件做了联合索引,但查询速度还是有点卡。
使用explain select 发现查询未使用到已建立的索引,导致select进行了全表千万级别数据的扫描。进一步检查发现是表格的索引全部 invisible 状态,导致优化器将索引忽略。
使用这个语句将索引更新为 visible 状态:
ALTER TABLE tbl_name ALTER INDEX idx_name VISIBLE;
这篇文档详细介绍了 explain 的优化思路

另外也查到对 select 操作比较多,没有事务要求的表,其实engine用 MyISAM 比较合适。

MyISAM和InnoDB适合场景的对比:
MyISAM类型不支持事务处理等高级处理,而InnoDB类型支持。MyISAM类型的表强调的是性能,其执行数度比InnoDB类型更快,但是不提供事务支持,而InnoDB提供事务支持以及外部键等高级数据库功能。

MyISAM适合:

(1)做很多count 的计算;
(2)插入不频繁,查询非常频繁,如果执行大量的SELECT,MyISAM是更好的选择;
(3)没有事务。

InnoDB适合:

(1)可靠性要求比较高,或者要求事务;
(2)表更新和查询都相当的频繁,并且表锁定的机会比较大的情况指定数据引擎的创建;
(3)如果你的数据执行大量的INSERT或UPDATE,出于性能方面的考虑,应该使用InnoDB表;
(4)DELETE FROM table时,InnoDB不会重新建立表,而是一行一行的 删除;
(5)LOAD TABLE FROM MASTER操作对InnoDB是不起作用的,解决方法是首先把InnoDB表改成MyISAM表,导入数据后再改成InnoDB表,但是对于使用的额外的InnoDB特性(例如外键)的表不适用。
参考网址

修改语句:
ALTER TABLE tbl_name ENGINE = MyISAM ;

关于提升代码运算效率的几点心得

  1. 硬件方面:
    CPU的主频,睿频要高(保证CPU散热),
    高频内存组双通道,主板开启XMP给内存超频
    显卡(可以的话用显卡做数值运算)

  2. 代码方面:
    使用连接池来连接数据库,降低在连接建立方面的开销(包含但不限于:进程数,端口数,网络通信握手),同时也能降低数据库连接压力
    尽量减少硬盘IO读写,能在内存里进行的操作一定不要用硬盘实现
    离散数据可考虑先缓存,达到一定阀值后再统一写入
    算法也很重要,好的算法可以减少很多运算时间

python 使用连接池 连接 mysql

问题
当我们在Python中连接Mysql时,每次增、删、改、查如果都申请一个数据库连接的话,当应用程序对mysql数据库请求量大时候,运行Python的机器就会大量报time  wait(我碰到的是40000+) 。这是因为每次连接mysql数据库请求时,都是独立的去请求访问(看到开发者的代码后得出结论),相当浪费资源,访问数量达到一定量时 ,运行程序的机器就报警了。

解决办法:
访问数据库应该使用连接池,来达到复用数据库连接的目的

python数据库连接池使用方法:
   a、安装 DBUtils(这里是DBUtils-1.3.tar.gz包)

   b、tar -zxvf DBUtils-1.3.tar.gz

   c、cd DBUtils-1.3

   d、 python setup.py install (安装)

连接示例:
https://yq.aliyun.com/articles/619548?utm_content=m_1000008987

参考链接:https://www.jianshu.com/p/c5a8cccecf32

MySQL挑战:建立10万连接

连接池操作示例:
https://zhuanlan.zhihu.com/p/61050785

安装 DBUtils:
pip install DBUtils -i https://pypi.tuna.tsinghua.edu.cn/simple

代码示例:

import pymysql
from DBUtils.PooledDB import PooledDB

'''
PooledDB() 参数含义

creator:使用链接数据库的模块
maxconnections:连接池允许的最大连接数,0和None表示没有限制
mincached:初始化时,连接池至少创建的空闲的连接,0表示不创建
maxcached:连接池空闲的最多连接数,0和None表示没有限制
maxshared:连接池中最多共享的连接数量,0和None表示全部共享,ps:其实并没有什么用,因为pymsql和MySQLDB等模块中的threadsafety都为1,所有值无论设置多少,_maxcahed永远为0,所以永远是所有链接共享
blocking:链接池中如果没有可用共享连接后,是否阻塞等待,True表示等待,False表示不等待然后报错
setsession:开始会话前执行的命令列表
ping:ping Mysql 服务端,检查服务是否可用
'''
global_pool = PooledDB(
    creator=pymysql,
    maxconnections=500,
    mincached=0,
    maxcached=20,
    maxshared=0,
    blocking=True,
    setsession=[],
    ping=5,
    host=global_db_addr,
    port=3306,
    user='xxxx',
    password='xxxxxx',
    database='xxxxx',
    charset='utf8mb4')

# 以后每次需要数据库连接用 connection() 函数获取连接即可
conn = global_pool.connection()

# 使用 cursor() 方法创建一个游标对象 cursor
cursor = conn.cursor()

#sql = 'INSERT INTO ' + tbl_name + ' (`time_key`,`code`,`direct`) VALUES (%s, %s, %s);'
#params = (dic_data["time_key"], dic_data["code"], dic_data["direct"])

tbl_name = 'stock_cur_kline_sh'
stock_code = 'SH.600161'
time_key = '2020-02-28 00:00:00'

sql = "SELECT Close FROM " + tbl_name + " where code = '" + stock_code + "' and time_key = '" + time_key + "';"

try:
    # 执行SQL语句
    cursor.execute(sql)
    # 获取所有记录列表
    results = cursor.fetchall()
    for row in results:
        close = row[0]
        # 打印结果
        print("close=%f" %(close))
except Exception as e:
    print("插入数据库异常, msg=%s" % (e))
    logging.error(traceback.format_exc())
finally:
    print("插入数据库成功")
    cursor.close()
    conn.close()

pymysql.connect() 参数含义

host=None,          # 要连接的主机地址
user=None,          # 用于登录的数据库用户
password='',        # 密码
database=None,      # 要连接的数据库
port=0,             # 端口,一般为 3306
unix_socket=None,   # 选择是否要用unix_socket而不是TCP/IP
charset='',         # 字符编码
sql_mode=None,      # Default SQL_MODE to use.
read_default_file=None, # 从默认配置文件(my.ini或my.cnf)中读取参数
conv=None,          # 转换字典
use_unicode=None,   # 是否使用 unicode 编码
client_flag=0,      # Custom flags to send to MySQL. Find potential values in constants.CLIENT.
cursorclass=,       # 选择 Cursor 类型
init_command=None,  # 连接建立时运行的初始语句 
connect_timeout=10, # 连接超时时间,(default: 10, min: 1, max: 31536000)
ssl=None,           # A dict of arguments similar to mysql_ssl_set()'s parameters.For now the capath and cipher arguments are not supported. 
read_default_group=None, # Group to read from in the configuration file.
compress=None,      # 不支持
named_pipe=None,    # 不支持
no_delay=None,      # 
autocommit=False,   # 是否自动提交事务
db=None,            # 同 database,为了兼容 MySQLdb
passwd=None,        # 同 password,为了兼容 MySQLdb
local_infile=False, # 是否允许载入本地文件
max_allowed_packet=16777216, # 限制 `LOCAL DATA INFILE` 大小
defer_connect=False, # Don't explicitly connect on contruction - wait for connect call.
auth_plugin_map={}, #
read_timeout=None,  # 
write_timeout=None, 
bind_address=None   # 当客户有多个网络接口,指定一个连接到主机

其它常用函数的用法讲解:
https://blog.csdn.net/memoryd/article/details/74995651