开发中写sql若干tips

目前公司项目中负责统计部分这边的开发，经常需要写一些sql，对于一些常用tips，在此记录一下，mysql和oracle都有用过：

mysql：

1、想要随机从查询结果中获取一条记录 order by rand() 但是效率较低 会使用临时表和扫全表描 可以想办法优化 比如通过程序生成一个偏移量来实现

2、where中使用null会导致全表扫描

3、not in与not exist in与exist的使用的区别 in适合外表大而内表小的情况 而exists适合外表小而内表大的情况

4、索引失效的情况，导致全表扫描：不建议使用%作为前缀来进行模糊查询 or两边的字段如果都没有索引的话会导致不走索引 可用union all来代替 order by后字段没有索引

5、in的值不宜过多 select最好指明具体的字段

6、查询一条记录 可用limit 1 注意查询的范围 分段查询（利用程序来循环遍历，表的行数太多） 强制走某个索引 联合索引最左前缀 避免隐式类型转换

而对sql进行explain分析，需要理解一些常用的参数含义：

extra字段

• using filesort：说明MySQL会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成的排序操作称为“文件排序”，其实不一定是文件排序，内部使用的是快排；

• using temporary：使用了临时表保存中间结果，MySQL在对查询结果排序时使用临时表。常见于排序order by和分组查询group by；

• using index：表示相应的SELECT操作中使用了覆盖索引（Covering Index），避免访问了表的数据行，效率不错；

• impossible where：WHERE子句的值总是false，不能用来获取任何元组；

• select tables optimized away：在没有GROUP BY子句的情况下基于索引优化MIN/MAX操作或者对于MyISAM存储引擎优化COUNT(*)操作，不必等到执行阶段再进行计算，查询执行计划生成的阶段即完成优化；

• distinct：优化distinct操作，在找到第一匹配的元组后即停止找同样值的操作。

type字段

• system：表只有一行记录（等于系统表），这是const类型的特例，平时不会出现；

• const：如果通过索引依次就找到了，const用于比较主键索引或者unique索引。因为只能匹配一行数据，所以很快。如果将主键置于where列表中，MySQL就能将该查询转换为一个常量；

• eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配。常见于主键或唯一索引扫描；

• ref：非唯一性索引扫描，返回匹配某个单独值的所有行。本质上也是一种索引访问，它返回所有匹配某个单独值的行，然而它可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体；

• range：只检索给定范围的行，使用一个索引来选择行。key列显示使用了哪个索引，一般就是在你的where语句中出现between、<、>、in等的查询，这种范围扫描索引比全表扫描要好，因为只需要开始于缩印的某一点，而结束于另一点，不用扫描全部索引；

• index：Full Index Scan ，index与ALL的区别为index类型只遍历索引树，这通常比ALL快，因为索引文件通常比数据文件小，也就是说虽然ALL和index都是读全表，但index是从索引中读取的，而ALL是从硬盘读取的；

• all：Full Table Scan，遍历全表获得匹配的行。

对于上亿级的大表，我们常用的一些优化就是分表分库，在某些数据库访问压力较大的情况下，为了保证数据安全性与查询效率，会进行主从式架构的部署，同时在应用级别做二级缓存，对于主从式架构数据架构怎样保证数据的同步，这个有待研究，二级缓存的选择里面也是大有文章可以研究。日常应用的时候，要根据应用场合，合理创建索引，唯一索引，主键索引等，对于mysql的几种锁有所了解，如果出现了死锁，要会查找原因。

另外，就是我们查看mysql的慢日志，可以通过命令行直接查询慢日志是否开启：

有时候如果线上请求超时，应该去关注下慢查询日志，慢查询的分析很简单，先找到慢查询日志文件的位置，然后利用mysqldumpslow去分析。查询慢查询日志信息可以直接通过执行SQL命令查看相关变量，常用的SQL如下：

mysqldumpslow的工具十分简单，我主要用到的是参数如下：

• -t：限制输出的行数，我一般取前十条就够了；

• -s：根据什么来排序默认是平均查询时间at，我还经常用到c查询次数，因为查询次数很频繁但是时间不高也是有必要优化的，还有t查询时间，查看那个语句特别卡；

• -v：输出详细信息。

例子：mysqldumpslow -v -s t -t 10 mysql_slow.log.2018-11-20-0500

还有一个常用的命令就是查看进程和杀死进程：

--查看进程列表

SHOW PROCESSLIST;

--杀死某个进程

kill 183665

Oracle：

oracle属于大型的关系型数据库，结合博主之前使用过的mysql和sqlserver的经验来看，oracle在数据操作方面稍微要麻烦一点，使用习惯也比较严肃一点，选用的时候一般是针对政府应用，一方面他们对于数据的安全性和保密性有比较高的要求，同时授权过的oracle也比较贵。

从sql的语法上看，有一些细微的差别，比如limit与rownum，sysdate与now()，时间类型转换上面，字符类型转换，字符串拼接，以及oracle提供的特有的一些函数，及专有的技术。实际应用过程中，常常用到了定时任务创建，存储过程过程编写，oracle用exp导出导入等：

declare

job number;

begin

sys.dbms_job.submit(job,'umstat.posynsjfx;',to_date('2019-01-11 23:00:00','yyyy-mm-dd hh24:mi:ss'),'trunc(sysdate) + 1 + 23/24');

end;

ORACLE数据库查找占用空间的大表

select TABLESPACE_NAME,SEGMENT_NAME,sum(BYTES/1024/1024)||'MB' BYTES_MB from DBA_EXTENTS

where SEGMENT_TYPE='TABLE' group by TABLESPACE_NAME,SEGMENT_NAME 

order by sum(BYTES/1024/1024) desc;

ORACLE数据库自动备份及异地备份方式

参考文献北京东城自动备份数据库脚本。

 fname=$(date +"%Y%m%d%H%M%S")
su oracle -lc "expdp system/oracle@dcwgrac directory=EXPDP_DIR dumpfile=expdp${fname}.dmp logfile=expdp${fname}.log schemas=DLINIT,DLGIS,DLHIST,DLSYS,DLMIS,DLTRANS,MMS,ULOG,SJZQ,UMSTAT"
scp /DBbak/expdp${fname}.dmp  root@172.25.72.xx:/egova/DB
scp /DBbak/expdp${fname}.log  root@172.25.72.xx:/egova/DB

首先解读下东城的自动备份脚本

 fname=$(date +"%Y%m%d%H%M%S")。定义fname为当前时间y=年m=月d-日h=时m=分s=秒

linux下执行如下图

su oracle -lc "expdp system/oracle@dcwgrac directory=EXPDP_DIR dumpfile=expdp${fname}.dmp logfile=expdp${fname}.log schemas=DLINIT,DLGIS,DLHIST,DLSYS,DLMIS,DLTRANS,MMS,ULOG,SJZQ,UMSTAT"

su oracle -lc 表示切换用户到oracle，执行完之后将用户切换会原有用户。后边的是导库语句不讲解，dumpfile=expdp${fname}.dmp 表示dmp文件命名expdp+时间格式 fname是我们上边定义的时间格式

注意：一下是异地备份方式，备份到异地要先做免密登陆到异地备份服务起，否则会弹出输入密码命令导致没法异地备份

scp /DBbak/expdp${fname}.dmp  root@172.25.72.xx:/egova/DB
scp /DBbak/expdp${fname}.log  root@172.25.72.xx:/egova/DB

免密登陆可以参考以下链接来配置：https://blog.csdn.net/furzoom/article/details/79139570

设置完免密登陆之后上边的异地备份才能执行

自动备份脚本写完了，我们要插入一个linux的定时任务来保证每天执行一次

首先执行 crontab 命令如果报 command not found，表明没有安装，若未安装执行命令 yum install crontab安装（有互联网）服务起如果没有连接互联网自己百度下载一个安装包安装

然后crontab -e配置定时任务

我写的每日凌晨1点执行opt/db/下的smtdb.sh

crontab详细命令或者时间计算格式可以参考此链接：https://www.cnblogs.com/longjshz/p/5779215.html

ps：中间的小插曲，我在写自动备份脚本的时候，命令直接执行会可以导出数据，但是将命令放在.sh文件里是执行就会报错，想了好久没有想出来，后来发现，安装dos2unix，并将原来的sh文件转换成UNIX格式之后执行成功

可能后面还是会以mysql为主，在某些应用环境下，需要将oracle表同步到mysql表中，公司的解决方案是：yugong项目是阿里团队使用纯java开发的数据库迁移工具，目的是把oracle数据迁移到mysql上，是去IOE的第一步。具体操作单独会写一篇文章进行介绍。

mysql的常用步骤：

1. 准备

1.1 客户端连接

mysql -u root -p 回车输入密码

1.2 重要命令

show variables 
show status 

2. 配置文件

Linux位置 /etc/my.cnf
Windows一般安装目录下 my.ini

3. 检查配置

log-bin=mysql-bin
expire_logs_days=7
binlog_format=row
character-set-server=utf8
skip-host-cache
skip-name-resolve
max_connections=2000 #连接数
wait_timeout=28800
lower_case_table_names=1
innodb_buffer_pool_size=32G # 内存分配
transaction-isolation=READ-COMMITTED
slow_query_log=on
long_query_time=1
binlog_cache_size=2M
innodb_open_files=3000
max_allowed_packet=1G
open_files_limit=65535
performance_schema=OFF
tmp_table_size=256M
max_heap_table_size=256M
max_write_lock_count=102400
thread_cache_size=100
sort_buffer_size=4M
key_buffer_size=32M
read_buffer_size=4M
join_buffer_size=8M
innodb_flush_log_at_trx_commit=2
innodb_log_file_size=1G
innodb_buffer_pool_instances=8
query_cache_size=0
query_cache_type=0
query_cache_limit=2M
innodb_io_capacity=2000
innodb_io_capacity_max=4000

4. 日志分析

日志分割
慢日志

开启 slow_query_log=on
阈值 long_query_time=1 单位秒
位置 slow_query_log_file= 日志位置
查看日志位置 show variables like "%slow%";
热修改： set global long_query_time = 0.1;
mysqldumpslow 工具使用
使用方法 ./mysqldumpslow -h
日志分割 logrotate

全日志

set global general_log = on;
查看show variables like "%general_log_file%"

binlog 日志

用途：主从、实时监控数据变化、恢复数据
server-id=1     #id要唯一
log-bin=mysql-bin #开启binlog日志
expire_logs_days=7 # 只保留7天
binlog_format=row # 格式
查看
show binary logs;
show binlog events in 'mysql-bin.000001';
用mysqlbinlog工具查看
> mysqlbinlog --start-datetime='2018-04-01 00:00:00' --stop-datetime='2016-05-01 23:01:01' -d cgdb /var/lib/mysql/mysql-bin.000001

错误日志

查看show variables like "%log_error%"

5. 监控工具

doDBA

项目地址 https://github.com/dblucyne/dodba_tools

使用直接看帮助 ./doDBA -help
配置 doDBA.conf
缺点：只能用在Linux上
QPS:每秒查询次数 TPS:每秒提交事务次数

6. 操作系统相关

ulimit -a
学会修改操作系统打开文件数和打开进程数限制

7. 常用技巧

紧急杀掉某个进程

select * from information_schema.processlist  where state = "executing"；

然后执行 kill id；

杀掉死锁线程

SELECT trx_mysql_thread_id FROM information_schema.INNODB_TRX
是将查出的线程
kill trx_mysql_thread_id；

各个服务器连接数

SELECT SUBSTRING_INDEX(HOST,':',1) AS host , COUNT(*) as count_ FROM information_schema.processlist GROUP BY ip order by count_ desc;

查询数据量最大的表

select TABLE_ROWS,TABLE_NAME,TABLE_SCHEMA  from information_schema.TABLES where TABLE_SCHEMA = 'cgdb' order by TABLE_ROWS desc;

查看已连接数

SHOW STATUS LIKE 'Threads_connected';

查看innodb状态

show engine innodb status

查询锁信息

SELECT * FROM INFORMATION_SCHEMA.INNODB_LOCKS;

表分析

Analyze Table 
Optimize Table  
Optimize table 可能报错不可用，使用命令重启 service mysqld restart --skip-new

 

全库锁表问题

mysqldump导出时，会锁表（仅针对主库，从库请忽略）。

此时造成的现象是：

1. 可以连接，可以查询

2. show open tables where in_use>0; 查询结果数量有几百（等同于现有表数量），就说明全库的表被锁住了。

3. mis不能登录

4. select * from xxx for update  一直等待，提示无法获取锁

 

常见解决步骤：

  1. 执行 unlock tables;

  2. 如果unlock之后，查询 in_use>0 还有很多，请查询 正在运行且获取到锁的线程：

select * from information_schema.`PROCESSLIST` where command not in ('Sleep') and state not like '%lock'

  3. 排查可疑连接（注，请忽略从库的Binlog Dump）,其中最可疑的线程就是mysqldump，执行的语句类似 

 SELECT /*!40001 SQL_NO_CACHE */ * FROM `to_his_act_pre_human

  4. 查到之后 执行 kill xxx；杀掉对应ID