mysql常见操作

发表于 | 分类于 | |  阅读次数
字数统计: 542 | 阅读时长 ≈ 2

日常运维

一些通用操作

1
2
3
4
5
6
7
8
9
10
11
12
13
-- 查看mysql版本
select version();

-- 查看数据文件保存目录
show variables like "%datadir%";

-- mysql修改数据库时间
set global time_zone = '+8:00';
set time_zone = '+8:00';
flush privileges;

-- 查看存储引擎
show table status from ytx_combo where name='combo';

mysql中的常用工具

1
2
3
4
5
6
7
8
# 查找my.cnf的文件地址
mysql --help|grep my.cnf

# 查看mysql的my.conf文件所在位置
mysql -p123456 --help |grep my.cnf

# 登录mysql
mysql -h 127.0.0.1 -P3306 -u root -p

mybatis

1
2
# mybatis批量修改
&allowMultiQueries=true

查询建表语句

1
2
3
4
5
6
7
show create table film
show create table store;
alter table tt add (age INT); --添加一列
alter table tt modify age VARCHAR(30); --修改列数据类型
alter table tt drop age; --删除列
alter table tt change column  tel  age int ; --修改表列名称
alter table test rename test1; --修改表名

创建索引

SQL优化

  1. 查看各种SQL执行频率 

    1
    show status;

    两种方式定位执行效率比较低的SQL语句:

    A. 慢查询日志：

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    --查询默认慢查询
    show variables like ”%long%”
    --设置慢查询时间
    set global long_query_time=2
    --启动满查询
    set global slow_query_log=’ON’; 
    --查看满查询相关，是否开启、日志存储位置慢查询日志分析工具
    show variables like "%slow%"; 
    --slow记录最多的10个语句
    mysqldumpslow -s r -t 10  /slowquery.log
    --按照时间排序前10中含有"left join"的
    mysqldumpslow -s t -t 10 -g "left join"  /slowquery.log

    B. 当前mysql在进行的线程，包括线程的状态、是否锁表

    1
    show processlist

  2. 通过explain分析效率低SQL的执行计划

1
SHOW STATUS LIKE "%Handler_read%"; //查看索引使用情况

  1. 两个简单实用的优化方法：

    A. 定期分析表和检查表

    • 分析表
    1
    analyze table store;
    • 检查表

    B. 定期优化表

    1
    optimize table store;

  2. 常用的SQL优化

    优化数据库对象：

    1. SELECT * from combo_item PROCEDURE ANALYSE() //优化表的数据类型，最适合大表优化
    2. 使用中间表提高统计查询速度
    3. 逆规范化
    4. 通过拆分提高表的访问效率

    锁问题
    MyISAM 

    1
    2
    3
    show status like ‘table_locks_%’; 

    # Table_locks_waited 的值比较高的情况下，说明存在着较严重的表级锁争用情况

    获取Innodb 行锁争用情况 

    1
    show status like “innodb_row_lock_%%”;