• 表分析与优化

    (注意会造成锁表 生产环境慎用)

1
2
3
4
5
6
7
8
9
10
11
12
13
14
//查看索引信息  注意字段cardinality(“散列程度”),太小则可以认为索引基本失效
show index from tbl;
//分析 local 取消binlog写入
analyze [local] Table tbl;
//check table 检查表及索引是否同步更新或异常
check table tbl;

//Optimize Table 碎片整理,如果表经常更新或物理删除记录 可释放空间
optimize [local] table tbl

//修复表
repair table tbl

//同 mysqlcheck 命令
  • myisam 引擎 导入

    1
    2
    3
    4
    5
    6
    7
    load data infile "/data/mysql/e.sql" into table e fields terminated by ',';

    //大批量导入非空表 可设置,提高效率

    alter table tbl_name disable keys;
    load data infile '/path/file'
    alter table tbl_name enable keys;
  • 导出文件

    1
    select * from e into outfile "/data/mysql/e.sql" fields terminated by ',';
  • innodb 引擎导入

1
2
3
4
5
1. 按主键顺序排列导入
2. 关闭唯一性校验 set unique_checks=0 结束后 恢复
3. 使用自动方式提交
导入前 set autocommit=0
导入后 set autocommit=1
  • insert 优化

    1
    2
    3
    4
    5

    1. 尽量使用多个值表的insert 替代单个 (减少客户端与数据库的连接 关闭 提高效率)
    2. INSERT DELAYED 特定引擎 myisam(5.6 被抛弃 并且8不在支持) 写入队列 当前线程空闲时才会把数据刷到磁盘
    3. 将文件和数据分开存放 提高磁盘io
    4. 增强设置 bulk_insert_buffer_size myisam 有效
  • 排序优化

1
2
3
4
5
6
7
8
9
10

1. 有序索引顺序扫描直接返回有序数据 explain时 using Index 可以
2. 对返回数据进行排序 (不是通过索引直接返回排序结果的排序都叫FileSort)(sort_buffer_size 有关)
线程独占,同一时刻,存在多个sort_buffer 排序
尽量减少额外排序,通过索引直接返回有序数据 (where 条件和 order by 尽量使用相同索引且顺序一致) 目的减少fileSort
fileSort 优化

a. 两次扫描算法

b. 一次扫描算法(效率高 但是内存开销大) max_length_for_sort_data
  • Group by 优化
1
使用group by 并且避免排序结果消耗,可以指定 order by null (禁止排序)
  • 嵌套查询优化

    1
    2
    3

    1. 优点: 避免事务、死锁
    2. 可以考虑 join 替代(不会创建临时表)
  • or 条件优化

1
or之间条件都必须为索引
  • 分页查询优化
1
2
3
4
limit 1000,20

1. join 自连接 避免全扫
2. 转换为where + limit n 模式 (增加last_query_id 做条件)
  • SQL 提示 (非常熟悉)
1
2
3
4
5
6
7
8
9
10

1. use index(xx)
explain select * from pgc_user use index(index_phone);
2. ignore index(xx)

3. force index(xx)
explain select * from force index(index_id_phone_created) pgc_user;

4. SQL_BUFFER_RESULT 强制生成临时缓存结果集,可以达到释放锁的目的
select SQL_BUFFER_RESULT * from pgc_user;