Mysql数据库如何调优

MYSQL数据库调优

索引

1、对于常用的查询字段加索引，但如果常用字段只有几个常量值就不需要加索引，或者使用索引会失效的情况；

2、索引失效的情况：

​ 1、索引列使用函数，计算（加减乘除等）

​ 2、like使用左模糊查询 ’%qwew’

​ 3、组合索引不使用第一列

​ 4、!= not <>

​ 5、使用OR 条件查询 前后没有同时使用索引

​ 6、字段类型不同

​ 7、比较两列值 where 索引列A=索引列B

​ 8、order by 单独使用

​ 等等

3、强制使用索引 force index(索引名) 例：SELECT * FROM subscription force index(expires_time) order by expires_time desc(order by单独使用，索引失效)

4、查看索引的的生效情况，在查询语句前加 EXPLAIN(仅对 select 生效)

​ id：表示sql的执行顺序（sql中会有子查询），id越大优先级越高，同级别在上而下执行

​ select_type：

​ SIMPLE：查询中不包含子查询或者UNION

​ PRIMARY：查询中包含子查询，最外层查询则被标记为PRIMARY

​ SUBQUERY：在SELECT或WHERE列表中包含了子查询，该子查询被标记为SUBQUERY

​ DERIVED：在FROM列表中包含的子查询被标记为：DERIVED（衍生）

​ UNION：第二个SELECT出现在UNION之后，则被标记为UNION；若UNION包含在 FROM 子句的子查询中，外层SELECT将被标记为：DERIVED

​ UNION RESULT：从UNION表获取结果的SELECT

​ 等等

​ type：访问类型 ALL(全表查询) index(走索引) range(范围查询) 等等

​ possible_keys：可能使用的索引

​ key：真正使用到的索引

​ key_len：索引使用的字节数

​ ref：连接匹配条件

​ rows：查询记录大概需要查询的行数

​ filtered：返回结果的行占需要读到的行(rows)的百分比

​ Extra：额外信息

数据库配置文件优化

查看命令：

show status 看系统的资源

show variables 看变量，在my.cnf配置文件里定义的

show warnings 查看最近一个sql语句产生的错误警告，看其他的需要看.err日志

show processlist显示系统中正在运行的所有进程。

my.cnf配置(针对innodb引擎)

query_cache_size = 128M //查询增加缓存，由于服务中的很多查询可能在一段时间内结果都是一样的，增加缓存后能大大降低查询时间，如果结果有更新，则会去查询数据库

query_cache_type = 1 //开启查询缓存

query_cache_limit = 50M //单个查询允许的最大的缓存内存，超过限制则不会缓存

thread_cache_size = 20 //缓存空闲的线程

innodb_buffer_pool_size = 4G //指定大小的内存来缓冲数据和索引,根据MySQL手册,建议设置为服务器内存的50%；MYISAM引擎使用 key_buffer_size

innodb_log_buffer_size = 32M //redo log 的写缓存

innodb_log_file_size = 128M //日志文件大小 更大的设置可以提高性能，但也会增加恢复故障数据库所需的时间

innodb_log_files_in_group = 3 //可以以循环方式将日志文件写到多个文件

其他大多数配置使用默认参数即可