备份
导入、导出数据库
导出: mysqldump -uroot db1 > db1.sql
(注db1为database名)
导入:mysql -uroot test < db1.sql
(注test为database名,将db1中所有的表及数据导入到test数据库)
导入、导出表
导出:mysqldump -uroot db1 tb1 tb2>tables.sql
(注db1为database名,tb1 tb2为要导出的表列表,中间用空格隔开)
导入:mysql -uroot test < tables.sql
(将db1数据库中的tb1和tb2表导入到test数据库)
https://zhidao.baidu.com/question/918251843038464259.html
基本操作
修改字段
修改字段属性
ALTER TABLE tb_name MODIFY 字段名称 字段类型 [完整性约束条件]
将email字段 VARCHAR(50)修改成VARCHAR(200)
注意,修改时如果不带完整性约束条件,原有的约束条件将丢失,如果想保留修改时就得带上完整性约束条件
ALTER TABLE user10 MODIFY email VARCHAR(200) NOT NULL DEFAULT 'a@a.com';
将card移到test后面
ALTER TABLE user10 MODIFY card CHAR(10) AFTER test;
将test放到第一个,保留原完整性约束条件
ALTER TABLE user10 MODIFY test CHAR(32) NOT NULL DEFAULT '123' FIRST;
https://www.cnblogs.com/huangxm/p/5736807.html
Lock wait timeout exceeded解决办法
Java执行一个SQL查询未提交,遇到1205错误。
java.lang.Exception:
Error updating database. Cause: java.sql.SQLException: Lock wait timeout exceeded; try restarting transaction
解决办法:
执行mysql命令:show full processlist;
查看事务表: select * from information_schema.innodb_trx;
然后找出查询语句的系统id:kill掉被锁住的线程id
(没有看到正在执行的很慢SQL记录线程,再去查看innodb的事务表INNODB_TRX,看下里面是否有正在锁定的事务线程,看看ID是否在show full processlist里面的sleep线程中,如果是,就证明这个sleep的线程事务一直没有commit或者rollback而是卡住了,我们需要手动kill掉)
创建用户赋予权限
修改表
select host,user from user where user='root';
如果上面查询到有信息,但host为localhost或其他值,就需要根据实际需求来更新表信息
update user set host='%' where user='root';
如果想任何的IP地址都可以使用root用户来远程登陆(注意如果上面的查询语句,查询出来有多条,就需要做一些处理,比如多加一个条件如where user='root' and host='localhost'
或先删除其他人记录只保存一条user='root’的记录)
修改权限
CREATE USER 'username'@'host' IDENTIFIED BY 'password';
grant all privileges on *.* to 'username'@'host' identified by 'password' with grant option; (host为 % 是通配)
flush privileges;
alter table table_name modify column col_name int auto_increment;
Error Code: 1175
MySql 执行 DELETE FROM Table 时,报
Error Code: 1175. You are using safe update mode and you tried to update a table without a WHERE that uses a KEY column To disable safe mode, toggle the option in Preferences -> SQL Editor and reconnect.
错误,这是因为 MySql 运行在 safe-updates模式下,该模式会导致非主键条件下无法执行update或者delete命令,执行命令如下命令
SET SQL_SAFE_UPDATES = 0;
Windows下username取到当前用户名问题解决
在Windows系统下,系统自带的 username 为当前win的用户,用spring mvc 配置数据源的时候不能用 username 属性,否则会出现MySQL连接的用户名取到当前用户
--推荐
jdbc.username=user
–禁用(Windows)
username=user
在Linux和Mac不会出现这个问题,应该是没有自带的 username 系统属性,兼顾团队其他成员,还是不要用的好。
Linux下查看mysql 线程执行内容
iotop #查看io占用高的线程,也可以用top命令查看
pstack tid # tid为占用比较高的线程(不是16进制)
获取秒数
unix_timestamp()
获取秒数
unix_timestamp()*1000
获取毫秒数
执行效率和时间
在sql语句前加上 explain
例如 explain SELECT IFNULL( sum( CASE WHEN b.NAME = 'BTC' AND a.direct = 3 THEN a.amount ELSE 0 END ), 0 ) btc_in, IFNULL( sum( CASE WHEN b.NAME = 'ETH' AND a.direct = 3 THEN a.amount ELSE 0 END ), 0 ) eth_in, IFNULL( sum( CASE WHEN b.NAME = 'USDT' AND a.direct = 3 THEN a.amount ELSE 0 END ), 0 ) usdt_in, IFNULL( sum( CASE WHEN b.NAME = 'BTC' AND a.direct = 4 THEN a.amount ELSE 0 END ), 0 ) btc_out, IFNULL( sum( CASE WHEN b.NAME = 'ETH' AND a.direct = 4 THEN a.amount ELSE 0 END ), 0 ) eth_out, IFNULL( sum( CASE WHEN b.NAME = 'USDT' AND a.direct = 4 THEN a.amount ELSE 0 END ), 0 ) usdt_out, IFNULL( sum( CASE WHEN b.NAME = 'BTC' AND a.direct = 1 THEN a.amount ELSE 0 END ), 0 ) btc_trans, IFNULL( sum( CASE WHEN b.NAME = 'ETH' AND a.direct = 1 THEN a.amount ELSE 0 END ), 0 ) eth_trans, IFNULL( sum( CASE WHEN b.NAME = 'USDT' AND a.direct = 1 THEN a.amount ELSE 0 END ), 0 ) usdt_trans FROM fiat_flow a LEFT JOIN coin b ON a.coin_id = b.id WHERE a.create_time >= 1542211200000 AND b.NAME IN ( 'BTC', 'ETH', 'USDT' )
各属性含义
id:select查询的序列号
select_type:select查询的类型,主要是区别普通查询和联合查询、子查询之类的复杂查询。
- SIMPLE, 表示此查询不包含 UNION 查询或子查询
- PRIMARY, 表示此查询是最外层的查询
- UNION, 表示此查询是 UNION 的第二或随后的查询
- DEPENDENT UNION, UNION 中的第二个或后面的查询语句, 取决于外面的查询
- UNION RESULT, UNION 的结果
- SUBQUERY, 子查询中的第一个 SELECT
- DEPENDENT SUBQUERY: 子查询中的第一个 SELECT, 取决于外面的查询. 即子查询依赖于外层查询的结果
table:输出的行所引用的表。
partitions:匹配的分区。
type:联合查询所使用的类型。
type显示的是访问类型,是较为重要的一个指标,结果值从好到坏依次是:
system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL
一般来说,得保证查询至少达到range级别,最好能达到ref。
- system,表中只有一条数据. 这个类型是特殊的 const 类型.
- const,针对主键或唯一索引的等值查询扫描, 最多只返回一行数据. const 查询速度非常快, 因为它仅仅读取一次即可.
- eq_ref,此类型通常出现在多表的 join 查询, 表示对于前表的每一个结果, 都只能匹配到后表的一行结果. 并且查询的比较操作通常是 =, 查询效率较高.
- ref,此类型通常出现在多表的 join 查询, 针对于非唯一或非主键索引, 或者是使用了 最左前缀 规则索引的查询.
- range,表示使用索引范围查询, 通过索引字段范围获取表中部分数据记录. 这个类型通常出现在 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN() 操作中.
- index,表示全索引扫描(full index scan), 和 ALL 类型类似, 只不过 ALL 类型是全表扫描, 而 index 类型则仅仅扫描所有的索引, 而不扫描数据.index 类型通常出现在: 所要查询的数据直接在索引树中就可以获取到, 而不需要扫描数据. 当是这种情况时, Extra 字段 会显示 Using index.
- ALL,表示全表扫描, 这个类型的查询是性能最差的查询之一. 通常来说, 我们的查询不应该出现 ALL 类型的查询, 因为这样的查询在数据量大的情况下, 对数据库的性能是巨大的灾难. 如一个查询是 ALL 类型查询, 那么一般来说可以对相应的字段添加索引来避免.
possible_keys:指出MySQL能使用哪个索引在该表中找到行。如果是空的,没有相关的索引。这时要提高性能,可通过检验 WHERE子句,看是否引用某些字段,或者检查字段不是适合索引。
key:显示MySQL实际决定使用的键。如果没有索引被选择,键是NULL。
key_len:显示MySQL决定使用的键长度。如果键是NULL,长度就是NULL。文档提示特别注意这个值可以得出一个多重主键里mysql实际使用了哪一部分。
ref:显示哪个字段或常数与key一起被使用。
rows:这个数表示mysql要遍历多少数据才能找到,在innodb上是不准确的。
filtered:表示此查询条件所过滤的数据的百分比。
Extra:如果是Only index,这意味着信息只用索引树中的信息检索出的,这比扫描整个表要快。
如果是where used,就是使用上了where限制。
如果是impossible where 表示用不着where,一般就是没查出来啥。
- Using filesort,表示 MySQL 需额外的排序操作, 不能通过索引顺序达到排序效果. 一般有 Using filesort, 都建议优化去掉, 因为这样的查询 CPU 资源消耗大.
- Using index,“覆盖索引扫描”, 表示查询在索引树中就可查找所需数据, 不用扫描表数据文件, 往往说明性能不错.
- Using temporary,查询有使用临时表, 一般出现于排序, 分组和多表 join 的情况, 查询效率不高, 建议优化.
- Using where,这意味着mysql服务器将在存储引擎检索行后再进行过滤。许多where条件里涉及索引中的列,当它如果并且读取索引时,就能背存储引擎检验,因此不是所有带有where子句的查询都会显示using where.
- Using index condition,Index Condition Pushdown (ICP)是MySQL 5.6版本中的新特性,是一种在存储引擎层使用索引过滤数据的一种优化方式.
- Using join buffer(Block Nested Loop),
- Using join buffer (Batched Key Access),
- Using MRR,