一.导入导出数据
1.命令行连接mysql并导入
mysql -h 192.168.0.1 -u user1 -p enter
password1
MySQL> show databases;
MySQL> use database1;
MySQL> show tables;
MySQL> source c:/test.sql #文件导入数据库database1中对应的表格
说明1: -u, 用户名
说明2: -h,指明IP或域名
说明3: -P(大写),指明端口
说明4: -p(小写),即密码
数据导出
mysqldump
参数名 | 缩写 | 含义 |
---|---|---|
--host | -h | 服务器IP地址 |
--port | -P | 服务器端口号 |
--user | -u | MySQL 用户名 |
--pasword | -p | MySQL 密码 |
--databases | 指定要备份的数据库 | |
--all-databases | 备份mysql服务器上的所有数据库 | |
--compact | 压缩模式,产生更少的输出 | |
--comments | 添加注释信息 | |
--complete-insert | 输出完成的插入语句 | |
--lock-tables | 备份前,锁定所有数据库表 | |
--no-create-db/--no-create-info | 禁止生成创建数据库语句 | |
--force | 当出现错误时仍然继续备份操作 | |
--default-character-set | 指定默认字符集 | |
--add-locks | 备份数据库表时锁定数据库表 |
-- tables table1 table2
例:
1.导出db1、db2两个数据库的所有数据
mysqldump -uroot -proot --databases db1 db2 >/tmp/user.sql
2.导出db1中的a1、a2表
注意:
导出指定表只能针对一个数据库进行导出,且导出的内容中和导出数据库也不一样,导出指定表的导出文本中没有创建数据库的判断语句,只有删除表-创建表-导入数据。
mysqldump -uroot -proot --databases db1 --tables a1 a2 >/tmp/db1.sql
3.条件导出,导出db1表a1中id=1的数据
如果多个表的条件相同可以一次性导出多个表。
• 字段是整形
mysqldump -uroot -proot --databases db1 --tables a1 --where=‘id=1’ >/tmp/a1.sql
• 字段是字符串,并且导出的sql中不包含drop table,create table
mysqldump -uroot -proot --no-create-info --databases db1 --tables a1 --where=“id=‘a’” >/tmp/a1.sql
二.慢查询
2.1抓取慢查询
对慢SQL语句优化一般可以按下面几步思路:
1.开启慢查询日志,设置超过几秒为慢SQL语句,抓取慢SQL语句;
2.通过explain查看执行计划,对慢SQL语句分析;
3.创建索引并调整语句,再查看执行计划,对比调优结果。
参数 slow_query_log :表示是否开启慢查询日志。语句“set global slow_query_log=on”临时开启慢查询日志,如果想关闭慢查询日志只需要执行“set global slow_query_log=off ”即可。
- 参数slow_query_log_file:当使用文件存储慢查询日志时(log_output设置为“FILE”或者“FILE,TABLE”时),指定慢查询日志存储于哪个日志文件中,默认的慢查询日志文件名为“主机名-slow.log”,慢查询日志的位置为datadir参数所对应的目录位置。另外,在MySQL 5.7.2之后,如果
- 设置了慢日志是写到文件里,就需要设置log_timestamps(默认是UTC时间,比我们晚8小时,需要设置为系统时间log_timestamps=SYSTEM)来控制写入到慢日志文件里面的时区(该参数同时影响general日志和error日志)。
- 参数long_query_time :表示“多长时间的查询”被认定为“慢查询”,默认值为10秒,表示超过10秒的查询被认定为慢查询。语句“set long_query_time=5”表示现在起所有执行时间超过1秒的SQL都将被记录到慢查询文件中。
- 参数log_queries_not_using_indexes :表示如果运行的SQL语句没有使用到索引,是否也被当作慢查询语句记录到慢查询日志中,OFF表示不记录,ON表示记录。
- 参数log_throttle_queries_not_using_indexes :当log_queries_not_using_indexes设置为ON时,没有使用索引的查询语句也会被当作慢查询语句记录到慢查询日志中。使用log_throttle_queries_not_using_indexes可以限制这种语句每分钟记录到慢查询日志中的次数,因为在生产环境中有可能有很多没有使用索引的语句,此类语句频繁地被记录到慢查询日志中,可能会导致慢查询日志快速不断地增长,管理员可以通过此参数进行控制。
慢查询日志中给出了账号、主机、运行时间、锁定时间、返回行等信息,然后根据这些信息来分析此SQL语句哪里出了问题。当开始使用慢查询功能后,可能随着慢查询日志越来越大,通过vi或cat命令不能很直观地查看慢查询日志,这时就可以使用MySQL内置的mysqldumpslow命令来进行分析。
2.2利用explain分析查询语句
使用explain,只需要在查询中的select关键字之前增加explain这个词即可,MySQL会在查询上设置一个标记,当执行查询时返回关于在执行计划中每一步的信息,而不是执行它
1)id:反映的是表的读取顺序或查询中执行select子句的顺序。
① id相同,执行顺序是由上至下的。
② id不同,如果是子查询,id序号会递增,id值越大优先级越高,越先被执行。
③ id如果相同,可以认为是一组,从上往下顺序执行;在所有组中,id值越大,优先级越高,越先执行。
(2)select_type:表示select的类型,主要用于区别普通查询、联合查询、子查询等复杂查询。
① simple:简单的select查询,查询中不包含子查询或union。
② primary:查询中若包含任何复杂的子部分,最外层查询标记为primary。
③ subquery:select或where列表中的子查询。
④ derived(衍生):在from列表中包含的子查询,MySQL会递归执行这些子查询,把结果放在临时表里。
⑤ union:若第二个select出现在union后,则被标记为union;若union包含在from子句的子查询中,外层select将被标记为derived。
⑥ union result:union后的结果集。
(3)table:显示这一步所访问数据库中表名称(显示这一行的数据是关于哪张表的),有时不是真实的表名字,可能是第几步执行的结果的简称。
(4)type:对表的访问方式,表示MySQL在表中找到所需行的方式,又称“访问类型”。常见的访问类型有ALL、index、range、 ref、eq_ref、const、system、NULL(从左到右,性能从差到好)。
① ALL:Full Table Scan,MySQL将遍历全表以找到匹配的行。
② index::Full Index Scan,index与ALL的区别为index类型只遍历索引树。
③ range:索引范围扫描,返回一批只检索给定范围的行,使用一个索引来选择行,一般就是在where语句中出现between、< 、>、in等的查询。这种范围扫描索引比全表扫描要好,因为它只需要开始于索引的某一点,而结束于另一点,不用扫描全部索引。
④ ref:非唯一性索引扫描,返回匹配某个单独值的所有行,本质上也是一种索引访问,它返回所有匹配某个单独值的行,然而它可能会找到多个符合条件的行,所以应该属于查找和扫描的混合体。
⑤ eq_ref:类似ref,区别在于使用的索引是唯一索引,对于每个索引键,表中只有一条记录与之匹配,常见于主键或唯一索引扫描。简单来说,就是多表连接中使用primary key或者unique key作为关联条件。
⑥ const、system:当MySQL对查询某部分进行优化并转换为一个常量时,使用这些类型访问。如果查询条件用到常量,那么通过索引一次就能找到,常在使用primary key或unique的索引中出现。system是const类型的特例,当查询的表只有一行的情况下使用。
⑦ NULL:MySQL在优化过程中分解语句,执行时甚至不用访问表或索引,例如从一个索引列里选取最小值可以通过单独索引查找完成。
(5)possible_keys:指出MySQL能使用哪个索引在该表中找到行,查询涉及的字段上若存在索引,则该索引将被列出,但不一定会被查询使用。
(6)key:显示MySQL实际决定使用的索引,如果没有选择索引,则显示是NULL。要想强制MySQL使用或忽视possible_keys列中的索引,在查询中使用FORCE INDEX或者IGNORE INDEX。查询中若使用了覆盖索引(select后要查询的字段刚好和创建的索引字段完全相同),则该索引仅出现在key列表中。
(7)key_len:显示索引中使用的字节数。
(8)ref:表示上述表的连接匹配条件,即哪些列或常量被用于查找索引列上的值。
(9)rows:显示MySQL根据表统计信息以及索引选用的情况,估算找到所需的记录要读取的行数。
(10)Extra:该列包含MySQL解决查询的说明和描述,包含不适合在其他列中显示但是对执行计划非常重要的额外信息。
① Using where:不用读取表中所有信息,仅通过索引就可以获取所需数据,发生在对表的全部请求列都是同一个索引部分的时候,表示MySQL服务器将在存储引擎检索行后再进行过滤。
② Using temporary:表示MySQL需要使用临时表来存储结果集,MySQL在对查询结果排序时使用临时表,常见于排序(order by)和分组查询(group by)。
③ Using filesort:当Query中包含order by操作而且无法利用索引完成的排序操作称为“文件排序”,创建索引时会对数据先进行排序,出现using filesort一般是因为order by后的条件导致索引失效,最好进行优化。
④ Using join buffer:表明使用了连接缓存,比如说在查询的时候,多表join的次数非常多,就将配置文件中缓冲区的join buffer调大一些。如果出现了这个值,应该注意,根据查询的具体情况可能需要添加索引来改进。
⑤ Using index:只使用索引树中的信息,而不需要进一步搜索读取实际的行来检索表中的列信息。相应的select操作中使用了覆盖索引,避免访问了表的数据行,效率好。覆盖索引:select后的数据列只从索引就能取得,不必读取数据行,且与所建索引的个数(查询列小于等于索引个数)、顺序一致。如果要用覆盖索引,就要注意select的列只取需要用到的列,不用select *,同时如果将所有字段一起做索引会导致索引文件过大,性能会下降。
⑥ Using Index Condition:表示进行了ICP优化。
总结一下针对explain命令生成执行计划:首先关注查询类型type列,如果出现all关键字,代表全表扫描,没有用到任何index;再看key列,如果key列是NULL,代表没有使用索引;然后看rows列,该列数值越大意味着需要扫描的行数越多,相应耗时越长;最后看Extra列,要避免出现Using filesort或Using temporary这样的字眼,这是很影响性能的。
三.数据库表被锁解决
show processlist
显示进程中state有 wait for ... lock信息 则表示当前进程在等待锁
执行
SELECT * FROM INFORMATION_SCHEMA.INNODB_TRX;
- 1
得到多条数据:
trx_mysql_thread_id
执行命令kill对应线程
kill
参考: