mysql 大表优化 知乎_Mysql优化

MySQL 对于千万级的大表要怎么优化？-知乎

2k赞答案

优化sql和索引

增加缓存

就做主从复制或主主复制，读写分离

分区，并针对分区优化sql

垂直拆分，分布式系统

水平切分，针对数据量大的表

答题者推荐学习：

innodb为了避免二次查找可以使用索引覆盖技术

索引覆盖实现延迟关联

1.5k赞答案

数据库设计和表创建时就要考虑性能

sql的编写需要注意优化

分区

分表

分库

慢查询配置

查看慢查询配置：

show variables like 'slow_query%';

show variables like 'long_query_time';

配置慢查询：

可以通过全局变量设置和配置文件来设置。

set global slow_query_log='ON';

set global slow_query_log_file='/usr/local/mysql/data/slow.log';

set global long_query_time=1;

在配置文件中设置，修改my.cnf，在[mysqld]下方配置。

slow_query_log = ON

slow_query_log_file = /usr/local/mysql/data/slow.log

long_query_time = 1

配置完成后重启mysql

使用慢查询分析工具：mysqldumpslow命令

// 返回记录最多的10条sql

mysqldumpslow -s r -t 10 /path/to/slow.log

// 返回访问次数最多的10条sql

mysqldumpslow -s c -t 10 /path/to/slow.log

// 返回按照时间排序含有左连接的10条sql，可以结合 |more 使用

mysqldumpslow -s t -t 10 -g "lfet join" /path/to/slow.log | more

SQL性能分析

查看性能分析配置：show variables like 'profiling%';

开启性能分析：set profiling=1;

查看分析记录列表：show profiles;

根据query_id查看sql详细信息：

SHOW PROFILE [type [, type] ... ]

[FOR QUERY n]

[LIMIT row_count [OFFSET offset]]

type: {

ALL // 全部开销信息

| BLOCK IO // 硬盘IO开销

| CONTEXT SWITCHES // 上下文切换相关开销

| CPU // CUP开销

| IPC // 发送和接收开销

| MEMORY // 内存开销

| PAGE FAULTS // 页面错误开销

| SOURCE // Source_function ,Source_file,Source_line相关开销

| SWAPS // 交换分区开销

}

SQL怎么优化

INSERT优化

INSERT DELAYED先将sql放入内存队列中，mysql空闲时插入。

LOW_PRIORITY降低sql执行的优先级，也可以使用在UPDATE语句中。

SELECT优化

用join代替子查询。

查询包括GROUP BY,但我们想要避免排序带来的性能损耗，则可以指定ORDER BY NULL禁止排序。

使用索引排序

避免全表扫描

在where和order by涉及的列上创建索引或复合索引。

尽量避免在where中判断is null和is not null。

尽量避免在where中使用!=和<>操作符。

用or连接条件，如果条件中的字段没有索引，可以使用union all来代替。

使用in的条件语句，如果条件是连续的数值，可以使用between来代替。

很多情况可以使用exists来代替in。

使用like语句时，尽量使用左匹配name%，搜索数据量大的直接用搜索引擎。

在where条件中使用了变量，可以使用 tableName with(index(indexName))强制使用索引。

在where条件中使用表达式和函数，尽量将表达式和函数放在操作符右边num=100*2。

使用复合索引时，尽量遵循最左前缀法则。

在where条件中尽量避免类型转换。

表结构优化

一个表的索引控制在6个以内，索引在提高select效率的同时，会降低insert和update效率。

clustered(聚簇)索引列的数据，尽量不要修改。INNODB引擎的主键就是聚簇索引。

字符型字段char定长可以增加sql效率，varchar可以节省存储空间。

合理使用临时表

其他

合理使用游标。

处理大量数据时，拆分处理。

使用trace来查看一下强制使用辅助索引和全表扫描的开销。

使用explain来查看sql执行效果。

开启慢查询，分析慢查询日志。

使用性能分析工具。

索引覆盖和延迟关联

即只需扫描索引而无须回表，使用EXPLAIN查看SQL信息时Extra的值为Using index。

通过使用覆盖索引查询返回需要的主键,再根据主键关联原表获得需要的数据。

主从配置和读写分离

分区