1. msyql 常用查询
目录
- 1. msyql 常用查询
- 2. 常用命令
- 2.1. mysql帮助命令的使用
- 2.2. mysql比较常用的视图（注意一般需要大写）
- 2.3. Mysql中查询系统时间的方法
- 2.4. Mysql select INTO OUTFILE 配置
- 2.5. mysql隔离级别
- 2.6. 查看某个用户执行过的sql语句
- 2.7. 会话和连接相关
- 2.8. 查看参数文件位置：
- 2.9. msyql innodb缓存池的命中率
- 2.10. mysql拼接批量alert表语句
- 2.11. mysql 替换字段中的换行符
- 2.12. 查询mysql自增主键使用情况
- 2.13. mysql新增列，并将该列放到指定列之后
- 2.14. mysql临时表解决create table x select 字段1，字段2 from tab
- 2.15. mysql 手动锁表操作：
- 2.16. 添加函数索引
- 线上无锁添加索引：加索引的语句不加锁
- 查询视图的定义者
- 5.7修改函数定义者
- mysql 8.0统一修改函数定义者
- 查看mysql中库下面的所有索引和字段信息
1.1. 查询重复记录
select * from TD_PC_TRADE where TRADE_ID in (select TRADE_ID from TD_PC_TRADE group by TRADE_ID having count(TRADE_ID) > 1);
1.2. 查询库中所有表的大小，按照大小排序
SELECT TABLE_SCHEMA ,
TABLE_NAME ,
round((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024 / 1024, 2) AS TOTAL_GB ,
round(DATA_LENGTH / 1024 / 1024 / 1024, 2) AS DATA_GB ,
round(INDEX_LENGTH / 1024 / 1024 / 1024, 2) AS INDEX_GB ,
round(DATA_FREE / 1024 / 1024 / 1024, 2) free_GB ,
TABLE_ROWS
FROM information_schema.tables t
WHERE TABLE_TYPE NOT IN ( 'SYSTEM VIEW' ,
'VIEW' )
AND tABLE_SCHEMA NOT IN ('information_schema',
'mysql',
'performance_schema',
'sys')
ORDER BY TOTAL_GB DESC;
1.3. 查询库中指定表的大小
SELECT TABLE_SCHEMA ,
TABLE_NAME ,
round((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024 / 1024, 2) AS TOTAL_GB ,
round(DATA_LENGTH / 1024 / 1024 / 1024, 2) AS DATA_GB ,
round(INDEX_LENGTH / 1024 / 1024 / 1024, 2) AS INDEX_GB ,
round(DATA_FREE / 1024 / 1024 / 1024, 2) free_GB ,
TABLE_ROWS
FROM information_schema.tables t
WHERE TABLE_TYPE NOT IN ( 'SYSTEM VIEW' ,
'VIEW' )
AND tABLE_SCHEMA NOT IN ('information_schema',
'mysql',
'performance_schema',
'sys')
AND TABLE_NAME in('tb_tags',
'20171025artist',
'huawei_tags');
1.4. 查看所有数据库的大小
SELECT
TABLE_SCHEMA AS "库名",
TRUNCATE(SUM(`DATA_LENGTH`) / 1024 / 1024/1024, 2) AS "表所占空间（GB）",
TRUNCATE(SUM(`INDEX_LENGTH`) / 1024 / 1024/1024, 2) AS "索引所占空间（GB）",
TRUNCATE((SUM(`DATA_LENGTH`) + SUM(`INDEX_LENGTH`)) / 1024 / 1024/1024,2) AS "空间累计（GB）"
FROM
information_schema.`TABLES`
where TABLE_SCHEMA not in('information_schema',
'mysql',
'performance_schema',
'sys')
GROUP BY `TABLE_SCHEMA`;
1.5. 查看指定数据库容量大小，包含记录数
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='mysql';
1.6. 查看前30个大表信息
SELECT TABLE_SCHEMA ,
TABLE_NAME ,
round((DATA_LENGTH + INDEX_LENGTH) / 1024 / 1024 / 1024, 2) AS TOTAL_GB ,
round(DATA_LENGTH / 1024 / 1024 / 1024, 2) AS DATA_GB ,
round(INDEX_LENGTH / 1024 / 1024 / 1024, 2) AS INDEX_GB ,
round(DATA_FREE / 1024 / 1024 / 1024, 2) free_GB ,
TABLE_ROWS
FROM information_schema.tables t
WHERE TABLE_TYPE NOT IN ( 'SYSTEM VIEW' ,
'VIEW' )
AND tABLE_SCHEMA NOT IN ('information_schema',
'mysql',
'performance_schema',
'sys')
ORDER BY TOTAL_GB DESC LIMIT 30;
1.7. 查看分区表信息
SELECT TABLE_SCHEMA,
TABLE_NAME,
PARTITION_NAME,
PARTITION_METHOD,
SUBPARTITION_NAME,
SUBPARTITION_METHOD,
PARTITION_EXPRESSION,
PARTITION_DESCRIPTION ,
CREATE_TIME,
CHECK_TIME
FROM INFORMATION_SCHEMA.PARTITIONS
WHERE table_schema NOT IN ('mysql',
'information_schema',
'sys',
'performance_schema')
AND PARTITION_NAME is not NULL ;
1.8. 查看表的数据估算值和表的估算大小
show table status like 'test' \G;
1.9. 查看主键信息
SELECT TABLE_SCHEMA,
TABLE_NAME,
GROUP_CONCAT(COLUMN_NAME,' ',COLUMN_TYPE) AS col ,
COLUMN_KEY
FROM information_schema.COLUMNS
WHERE COLUMN_KEY = 'PRI'
AND table_schema NOT IN ('mysql',
'information_schema',
'sys',
'performance_schema')
GROUP BY TABLE_SCHEMA,
TABLE_NAME,
COLUMN_KEY;
1.10. 查看没有主键的表
SELECT DISTINCT t.table_schema,
t.table_name
FROM information_schema.tables AS t
LEFT JOIN information_schema.columns AS c ON t.table_schema = c.table_schema
AND t.table_name = c.table_name
AND c.column_key = 'PRI'
WHERE t.table_schema NOT IN ('information_schema',
'mysql',
'performance_schema')
AND c.table_name IS NULL
AND t.table_type != 'VIEW';
1.11. 查看没有索引的表
SELECT T1.TABLE_SCHEMA,
T1.TABLE_NAME
FROM INFORMATION_SCHEMA.COLUMNS T1
JOIN INFORMATION_SCHEMA.TABLES T2 ON T1.TABLE_SCHEMA=T2.TABLE_SCHEMA
AND T1.TABLE_NAME=T2.TABLE_NAME
WHERE T1.TABLE_SCHEMA NOT IN ('SYS' ,
'MYSQL',
'INFORMATION_SCHEMA' ,
'PERFORMANCE_SCHEMA')
AND T2. TABLE_TYPE='BASE TABLE'
GROUP BY T1.TABLE_SCHEMA,
T1.TABLE_NAME HAVING MAX(COLUMN_KEY)='';
1.12. 查询前n条记录
SELECT * FROM tb_name LIMIT 0,$N;
1.13. 查询后n条记录
SELECT * FROM tb_stu ORDER BY id ASC LIMIT $n
1.14. 查询从指定位置开始的n条记录
SELECT * FROM tb_stu ORDER BY id ASC LIMIT a,N
a ：表示从第a条数据开始
N :表示取n条记录
1.15. 查询数据库中的冗余索引和重复索引
1.15.1. 冗余索引清理规则
- 第一个索引是第二个索引的左前缀索引, 删除第一个索引
- 有主键, 用不到唯一索引, 删除唯一索引
- 有主键, 用不到普通索引, 删除普通索引
- 有唯一索引, 用不到普通索引, 删除普通索引
- 有联合主键索引, 用不到普通组合索引, 删除普通组合索引
- 索引重复, 删除其中一个索引
- 普通索引默认以主键索引做后缀, 重建普通索引
1.15.2. 查询冗余索引
use information_schema;
SELECT
a.TABLE_SCHEMA AS '数据名',
a.TABLE_NAME AS '表名',
a.INDEX_NAME AS '索引1',
b.INDEX_NAME AS '索引2',
a.COLUMN_NAME AS '重复列名'
FROM
STATISTICS a
JOIN STATISTICS b ON a.TABLE_SCHEMA = b.TABLE_SCHEMA
AND a.TABLE_NAME = b.TABLE_NAME
AND a.SEQ_IN_INDEX = b.SEQ_IN_INDEX
AND a.COLUMN_NAME = b.COLUMN_NAME
WHERE
a.SEQ_IN_INDEX = 1
AND a.INDEX_NAME <> b.INDEX_NAME;
SELECT a.TABLE_SCHEMA, a.TABLE_NAME, a.COLUMN_NAME,
a.INDEX_NAME AS 'index1', b.INDEX_NAME AS 'index2'
FROM information_schema.STATISTICS a
JOIN information_schema.STATISTICS b
ON a.TABLE_SCHEMA = b.TABLE_SCHEMA
AND a.TABLE_NAME = b.TABLE_NAME
AND a.SEQ_IN_INDEX = b.SEQ_IN_INDEX
AND a.COLUMN_NAME = b.COLUMN_NAME
WHERE a.SEQ_IN_INDEX = 1 AND a.INDEX_NAME <> b.INDEX_NAME;
1.16. mysql查询外键关系
SELECT C.TABLE_SCHEMA ,
C.REFERENCED_TABLE_NAME father_table,
GROUP_CONCAT(c.REFERENCED_COLUMN_NAME
ORDER BY c.ORDINAL_POSITION separator ',') AS father_table_col,
C.TABLE_NAME child_table,
GROUP_CONCAT(c.COLUMN_NAME
ORDER BY c.ORDINAL_POSITION separator ',') AS child_table_col,
C.CONSTRAINT_NAME ,
R.UPDATE_RULE ,
R.DELETE_RULE
FROM INFORMATION_SCHEMA.KEY_COLUMN_USAGE C,
INFORMATION_SCHEMA.REFERENTIAL_CONSTRAINTS R
WHERE C.CONSTRAINT_SCHEMA=R.CONSTRAINT_SCHEMA
AND R.TABLE_NAME = C.TABLE_NAME
AND R.CONSTRAINT_NAME = C.CONSTRAINT_NAME
AND R.REFERENCED_TABLE_NAME = C.REFERENCED_TABLE_NAME
AND C.REFERENCED_TABLE_NAME IS NOT NULL
GROUP BY C.TABLE_SCHEMA,
C.REFERENCED_TABLE_NAME ,
C.TABLE_NAME,
C.CONSTRAINT_NAME ,
R.UPDATE_RULE ,
R.DELETE_RULE;
1.17. 查询会话信息
select* from information_schema.processlist where COMMAND<>'Sleep' \G;
1.18. 查询当前连接的进程使用的内存信息
SELECT
t.PROCESSLIST_ID,SUM(m.CURRENT_NUMBER_OF_BYTES_USED) AS TOTAL
FROM
performance_schema.memory_summary_by_thread_by_event_name m
INNER JOIN
performance_schema.threads t
ON
m.THREAD_ID = t.THREAD_ID;
2. 常用命令
2.1. mysql帮助命令的使用
在mysql的使用过程中常常会遇到下面的问题：
- 某个操作语法忘记了，如何快速查找？
- 如何快速知道当前版本上某个字段类型的取值范围？
- 当前版本都支持哪些函数？希望有例子说明
- 当前版本是否支持某个功能
一、想知道能够提供哪些帮助
使用
mysql>? contents
2、查看支持的数据类型
mysql>? data types
3、查看int这个数据类型的特点，取值范围等
mysql>? int
4、查看show 命令的作用
mysql>? show
2.2. mysql比较常用的视图（注意一般需要大写）
schemata:该视图提供了当前mysql实例中的所有的数据库的信息，show databases的结果就是从该表中取得数据
tables：该表提供了关于数据库中的表的信息（包括视图），详细表述了某个表属于哪个schema、表的类型、表引擎、创建时间等信息
show tables from schemaname 的结果就是从该表中取出的数据
columns:该表提供了表中的列的信息，详细表述了某张表的所有列以及每个列的信息，
show clumns from schemaname.tablename的结果就是来自该表
statistics:该表提供了关于表的索引的信息，show index schemaname.tablename 取自该表
2.3. Mysql中查询系统时间的方法
第一种方法：select current_date；
mysql> select current_date as Systemtime;
+------------+
| Systemtime |
+------------+
| 2009-07-29 |
+------------+
第二种方法：select now()
mysql> select now() as Systemtime;
+---------------------+
| Systemtime |
+---------------------+
| 2009-07-29 19:06:07 |
+---------------------+
第三种方法：select sysdate()
mysql> select sysdate() as Systemtime;
+---------------------+
| Systemtime |
+---------------------+
| 2009-07-29 19:06:45 |
2.4. Mysql select INTO OUTFILE 配置
Mysql可使用 into outfile 参数把表中数据导出到文件，
例如可用以下命令把 test 表的数据导出到 test.txt
SELECT * FROM newdb.test INTO OUTFILE 'test.txt';
以上命令在mysql5.6下运行没有问题，但在mysql5.7下运行则出现了以下错误：'ERROR 1290 (HY000): The MySQL server is running with the --secure-file-priv
option so it cannot execute this statement'
2.4.1. secure_file_priv
查看官方文档，secure_file_priv参数用于限制LOAD DATA, SELECT …OUTFILE,
LOAD_FILE()传到哪个指定目录。secure_file_priv 为 'NULL' 时，表示限制mysqld不允许导入或导出。
- secure_file_priv 为 '/tmp' 时，表示限制mysqld只能在/tmp目录中执行导入导出;
- secure_file_priv '没有值'时，表示不限制mysqld在任意目录的导入导出。
show global variables like '%secure_file_priv%';
2.4.2. secure_file_priv配置
因为 secure_file_priv 参数是只读参数，'不能使用set global命令修改'。
mysql> # set global secure_file_priv='';
'ERROR 1238 (HY000): Variable 'secure_file_priv' is a read only variable'
打开my.cnf ，加入以下语句后重启mysql。# vim /etc/my.cnf
[mysqld]
secure-file-priv = ""
2.4.3. 授予普通用户select outfile 权限
GRANT FILE ON *.* TO `circleadmin`@`localhost`;
注意： file 权限是全局， 不可以单独指定给某个数据库授权，只能是*.*
2.5. mysql隔离级别
数据库事务的隔离级别
1）级别1：read uncommitted :
读取尚未提交的数据，哪个问题都不能解决
2）级别2：read committed：
读取已经提交的数据（可以读取到其他事务提交的update更新和insert新增），可以解决脏读，oracle默认的
3）级别4：repeatable read：
重读读取（只能读取其他事务已经提交的insert新增数据），可以解决脏读和不可重复读，mysql默认的
4）级别8：serializable：
串行化：可以解决 脏读 不可重复读 和 虚读---相当于锁表
2.5.1. 查看当前会话隔离级别
SELECT @@tx_isolation;
2.5.2. 设置当前会话隔离级别
set session transaction isolation level repeatable read;
2.5.3. 查看系统当前隔离级别
select @@global.tx_isolation;
2.5.4. 设置系统当前隔离级别
set global transaction isolation level repeatable read;
set global transaction isolation level repeatable read;
//设置read uncommitted级别：
set global transaction isolation level read uncommitted;
//设置read committed级别：
set global transaction isolation level read committed;
//设置repeatable read级别：
set global transaction isolation level repeatable read;
//设置serializable级别：
set global transaction isolation level serializable;
2.6. 查看某个用户执行过的sql语句
select * from v$sqlarea t where t.PARSING_SCHEMA_NAME in ('用户名') order by t.LAST_ACTIVE_TIME desc
用户名需要大写
select SQL_ID,PARSING_SCHEMA_NAME,module,LAST_ACTIVE_TIME,SQL_FULLTEXT from v$sqlarea order by LAST_ACTIVE_TIME desc;
oracle 查看已经执行过的sql 这些是存在共享池中的
select * from v$sqlarea t order by t.LAST_ACTIVE_TIME desc;
2.7. 会话和连接相关
2.7.1. 查看当前的连接数量和并发数
show status like 'Threads%';
+-------------------+-------+
| Variable_name | Value |
+-------------------+-------+
| Threads_cached | 0 |
| Threads_connected | 1 | 连接数
| Threads_created | 1 |
| Threads_running | 1 | 当前运行的连接数（并发数）
+-------------------+-------+
show global status like '%threads_running%';
Threads_connected 跟show processlist结果相同，表示当前连接数。准确的来说，Threads_running是代表当前并发数
这是是查询数据库当前设置的最大连接数
show variables like '%max_connections%';
可以在/etc/my.cnf里面设置数据库的最大连接数
[mysqld]
max_connections = 1000
常用的表
SELECT * FROM sys.metrics; 查看当前变量和值
2.7.2. 查找TOP sql -性能视图方式
2.7.2.1. 查找TOP sql
以下的方式仅展示数据库没有重启的这段时间的所有sql
在不方便修改客户的系统配置的前提下，我们可以直接查看如下的视图，抓出系统只能怪执行过的时间最长的sql，有些时候可能不愿意启用慢查询等配置，那么我们就以使用如下的方法分析sql
在视图sys.statement_analysis中找出总计执行时间最长的SQL语句：
select * from sys.statement_analysis limit 1\G;
视图sys.statement_analysis已经是按照总的执行时间降序排序按了，所以我们直接查询即可
还可以在视图sys.statements_with_runtimes_in_95th_percentline 中可以查询到运行时间最长的5%的语句
select * from sys.statements_with_runtimes_in_95th_percentile limit 3;
2.7.2.2. 查询平均时间最长的sql
下面的sql语句列出平均执行时间最长的语句，这类sql语句通常优化空间最大
select * from performance_schema.events_statements_summary_by_digest order by avg_timer_wait desc limIt 1 \G;
查询执行次数最多的,这种对整体信息影响较大
select * from performance_schema.events_statements_summary_by_digest order by count_star desc limit 3 \G;
检查函数最多的sql，消耗读最多
sum_row_examined
返回行数最多，消耗最多的网络带宽
sum_rows_sent
2.7.3. 查找TOP sql -存储过程方式
存储过程
diagnostics()
ps_trace_statement_digest()
statement_performance_analyzer()
ps_trace_thread()
2.7.3.1. diagnostics()
diagnostics() 存储过会生成一个关于当前msyql整体性能的诊断报告
eg：
tee diagnostics.log
call sys.diagnostics(null,null,'current');
notee;
2.7.3.2. ps_trace_statement_digest()
2.7.3.3. statement_performance_analyzer()
2.7.3.4. ps_trace_thread()
2.8. 查看参数文件位置：
MySQL8.0中将很多数据库配置信息都写入了variables_info表中，查找起来非常方便。
仅8.0版本有改表
select * from performance_schema.variables_info where VARIABLE_NAME='max_connections' \G
select VARIABLE_PATH,VARIABLE_SOURCE,count(*) from performance_schema.variables_info where length(VARIABLE_PATH)!=0 group by VARIABLE_PATH,VARIABLE_SOURCE;
ps -ef|grep mysql
2.9. msyql innodb缓存池的命中率
show status like '%innodb_buffer_pool_read%';
- Innodb_buffer_pool_read_requests 逻辑读 表示向innodb缓存池进行逻辑读额次数 逻辑读请求数，但是因为一次请求一个块，所以等同于逻辑读的page数。
- Innodb_buffer_pool_reads物理读 表示从物理磁盘读取数据的次数 ：等同于物理读的page数
msyql innodb缓存池的命中率=(Innodb_buffer_pool_read_requests-Innodb_buffer_pool_reads)/Innodb_buffer_pool_read_requests*100%
对于设置innodb缓存大小的思考
1.太小的缓存池可能会导致数据页备频繁的从磁盘读取到内存，导致性能下降
2.如果设置过大，可能会造成内存被交换到swap分区，引起内存急剧下降
3.这两种情况比较起来，把innodb缓存池设置小一些对性能的负面影响不是特别大，实际生产中
mysql进程崩溃的一个常见原因是操作系统的内存耗尽（导致系统OMM），然后系统会将mysql服务kill掉
innodb_buffer_pool_size 如果是专用的mysql服务器，建议设置系统内存的70-80%左右
innodb_buffer_pool_instances表示要将缓存分成多少份，值能在innodb_buffer_pool_size>1G时生效
2.10. mysql拼接批量alert表语句
select CONCAT("alter table ",table_name," modify column ",column_name," datetime;")
from information_schema.columns
where table_schema='pcard' and data_type='datetime'
limit 10;
select CONCAT("alter table ",table_name," modify column ",column_name," int;")
from information_schema.columns
where table_schema='pcard' and data_type='decimal'
limit 10;
2.11. mysql 替换字段中的换行符
select REPLACE(age,CHAR(10),'') from testa;
CHAR(10) 表示的就是换行符
2.12. 查询mysql自增主键使用情况
SELECT table_schema,
TABLE_NAME,
COLUMN_NAME,
AUTO_INCREMENT ,
round((AUTO_INCREMENT/(POW(2, CASE data_type WHEN 'tinyint' THEN 7 WHEN 'smallint' THEN 15 WHEN 'mediumint' THEN 23 WHEN 'int' THEN 31 WHEN 'bigint' THEN 63 END+(column_type LIKE '% unsigned'))))*100,0) as USED_PERCENT,
POW(2, CASE data_type WHEN 'tinyint' THEN 7 WHEN 'smallint' THEN 15 WHEN 'mediumint' THEN 23 WHEN 'int' THEN 31 WHEN 'bigint' THEN 63 END+(column_type LIKE '% unsigned')) AS MAX_VALUES,
COLUMN_TYPE
FROM information_schema.tables t
JOIN information_schema.columns c USING (table_schema,
TABLE_NAME)
WHERE c.extra = 'auto_increment'
AND t.TABLE_SCHEMA NOT IN ('information_schema',
'mysql',
'sys',
'performance_schema')
AND t.auto_increment IS NOT NULL
and t.TABLE_NAME in('saas_company_song','saas_company_song_new');
SELECT
table_schema,
table_name,
column_name,
AUTO_INCREMENT,
POW(2, CASE data_type
WHEN 'tinyint' THEN 7
WHEN 'smallint' THEN 15
WHEN 'mediumint' THEN 23
WHEN 'int' THEN 31
WHEN 'bigint' THEN 63
END+(column_type LIKE '% unsigned'))-1 AS max_int
FROM information_schema.tables t
JOIN information_schema.columns c USING (table_schema,table_name)
WHERE
c.extra = 'auto_increment'
AND
t.TABLE_SCHEMA NOT IN ('information_schema','mysql', 'sys','test','performance_schema')
AND
t.auto_increment IS NOT NULL ;
修改数据库和表的字符集：
修改数据库的字符集需要重启才能生效
alter database kettle character set utf8mb4;
ALTER TABLE r_cluster DEFAULT CHARACTER SET utf8mb4 COLLATE utf8mb4_general_ci;
查看表和列的注释：
查看表的注释：
select TABLE_SCHEMA,table_name,TABLE_COMMENT from information_schema.TABLES ;
查看列的注释：
SELECT
a.table_name 表名,
a.table_comment 表说明,
b.COLUMN_NAME 字段名,
b.column_comment 字段说明,
b.column_type 字段类型,
b.column_key 约束
FROM
information_schema.TABLES a
LEFT JOIN information_schema.COLUMNS b
ON a.table_name = b.TABLE_NAME
WHERE a.table_schema = '数据库名'
ORDER BY a.table_name
2.13. mysql新增列，并将该列放到指定列之后
ALTER TABLE `goldkinn_center_test_salary`.`year_result_info_new`
ADD COLUMN `next_use_year` int(4) NULL AFTER `description`;
2.14. mysql临时表解决create table x select 字段1，字段2 from tab
创建临时表：
CREATE TEMPORARY TABLE select id from test;
查看临时表表结构
show create table Sale;
CREATE TEMPORARY TABLE `sale` (
`id` int(11) DEFAULT NULL
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;
通过临时表创建表结构
create table xxxx like Sale;
insert 数据
insert into xxx select id from test;
2.15. mysql 手动锁表操作：
begin;
LOCK TABLES in_prodcost_config WRITE,in_prodcost_price WRITE;
alter table in_prodcost_config add column fine_org_id bigint(32) DEFAULT NULL comment '精细库存组织ID' after `bom_id`;
alter table in_prodcost_price add column fine_org_id bigint(32) DEFAULT NULL comment '精细库存组织ID' after `organization_id`;
UNLOCK TABLES;
commit;
oracle:
SET TRANSACTION;
LOCK TABLE er_bxzb in SHARE MODE;
LOCK TABLE er_jkzb in SHARE MODE;
alter table er_bxzb modify zy varchar2(1500);
alter table er_jkzb modify zy varchar2(1500);
commit;
2.16. 添加函数索引
alter table amb_data_result_accounts add index fun_idx_fm_date((DATE_FORMAT(fm_date,_utf8mb3'%Y-%m-%d %H:%i:%s')));
alter table amb_data_docs add index fun_idx_fm_date((DATE_FORMAT(fm_date,_utf8mb3'%Y-%m-%d %H:%i:%s')));
线上无锁添加索引：加索引的语句不加锁
ALTER TABLE tbl_name ADD PRIMARY KEY (column), ALGORITHM=INPLACE, LOCK=NONE;
说明
更优秀的解决方案，在当前表加索引，步骤：
ALGORITHM=INPLACE
1.创建索引(二级索引)数据字典
2.加共享表锁，禁止DML，允许查询
3.读取聚簇索引，构造新的索引项，排序并插
入新索引
4.等待打开当前表的所有只读事务提交
5.创建索引结束
ALGORITHM=COPY
通过临时表创建索引，需要多一倍存储，还有更多的IO，步骤：
1.新建带索引（主键索引）的临时表
2.锁原表，禁止DML，允许查询
3.将原表数据拷贝到临时表
4.禁止读写,进行rename，升级字典锁
5.完成创建索引操作
LOCK=DEFAULT：默认方式，MySQL自行判断使用哪种LOCK模式，尽量不锁表
LOCK=NONE：无锁：允许Online DDL期间进行并发读写操作。如果Online DDL操常用查询
作不支持对表的继续写入，则DDL操作失败，对表修改无效
LOCK=SHARED：共享锁：Online DDL操作期间堵塞写入，不影响读取
LOCK=EXCLUSIVE：排它锁：Online DDL操作期间不允许对锁表进行任何操作
查询视图的定义者
select * from information_schema.VIEWS where DEFINER='root@%';
select distinct DEFINER from information_schema.VIEWS;
批量修改语句
select concat("alter DEFINER='root'@'172.28.1.%' SQL SECURITY DEFINER VIEW ",TABLE_SCHEMA,".",TABLE_NAME," as ",VIEW_DEFINITION,";") from information_schema.VIEWS where DEFINER = "root@%";
5.7修改函数定义者
mysql.proc
mysql 8.0统一修改函数定义者
SELECT * FROM information_schema `ROUTINES`
select DEFINER from mysql.proc where DEFINER='gkdb-bpm-prod-web@%'
select dinstinct DEFINER from mysql.proc;
查看mysql中库下面的所有索引和字段信息
拼接重建索引语句
SELECT a.TABLE_SCHEMA,
a.TABLE_NAME,
a.index_name,
GROUP_CONCAT(column_name ORDER BY seq_in_index) AS `Columns`
FROM information_schema.statistics a
where a.TABLE_SCHEMA='goldkinn_mes_pre'
GROUP BY a.TABLE_SCHEMA,a.TABLE_NAME,a.index_name
拼接重建索引语句
SELECT
CASE b.index_name
WHEN 'PRIMARY' THEN
CONCAT('alter table ',b.TABLE_SCHEMA,'.',b.TABLE_NAME,' drop ',b.index_name,' key ;')
ELSE
CONCAT('alter table ',b.TABLE_SCHEMA,'.',b.TABLE_NAME,' drop INDEX ',b.index_name,';')
END as delete_idx,
CASE b.index_name
WHEN 'PRIMARY' THEN
CONCAT('alter table ',b.TABLE_SCHEMA,'.',b.TABLE_NAME,' add ',b.index_name,'key (',b.Columns,');')
ELSE
CONCAT('alter table ',b.TABLE_SCHEMA,'.',b.TABLE_NAME,' add INDEX ',b.index_name,'(',b.Columns,');')
END as add_idx
FROM
(
SELECT
a.TABLE_SCHEMA,
a.TABLE_NAME,
a.index_name,
GROUP_CONCAT( column_name ORDER BY seq_in_index ) AS `Columns`
FROM
information_schema.statistics a
WHERE
a.TABLE_SCHEMA = 'goldkinn_mes_pre'
GROUP BY
a.TABLE_SCHEMA,
a.TABLE_NAME,
a.index_name
) AS b