如何优化MySQL数据库性能以提升查询效率和系统稳定性

索引

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

2、索引失效的情况&＃xff1a;

​ 1、索引列使用函数&＃xff0c;计算&＃xff08;加减乘除等&＃xff09;

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

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

​ 4、!&＃61; not <>

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

​ 6、字段类型不同

​ 7、比较两列值 where 索引列A&＃61;索引列B

​ 8、order by 单独使用

​ 等等

3、强制使用索引 force index(索引名) 例&＃xff1a;SELECT * FROM subscription force index(expires_time) order by expires_time desc(order by单独使用&＃xff0c;索引失效)

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

​ id&＃xff1a;表示sql的执行顺序&＃xff08;sql中会有子查询&＃xff09;&＃xff0c;id越大优先级越高&＃xff0c;同级别在上而下执行

​ select_type&＃xff1a;

​ SIMPLE&＃xff1a;查询中不包含子查询或者UNION

​ PRIMARY&＃xff1a;查询中包含子查询&＃xff0c;最外层查询则被标记为PRIMARY

​ SUBQUERY&＃xff1a;在SELECT或WHERE列表中包含了子查询&＃xff0c;该子查询被标记为SUBQUERY

​ DERIVED&＃xff1a;在FROM列表中包含的子查询被标记为&＃xff1a;DERIVED&＃xff08;衍生&＃xff09;

​ UNION&＃xff1a;第二个SELECT出现在UNION之后&＃xff0c;则被标记为UNION&＃xff1b;若UNION包含在 FROM 子句的子查询中&＃xff0c;外层SELECT将被标记为&＃xff1a;DERIVED

​ UNION RESULT&＃xff1a;从UNION表获取结果的SELECT

​ 等等

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

​ possible_keys&＃xff1a;可能使用的索引

​ key&＃xff1a;真正使用到的索引

​ key_len&＃xff1a;索引使用的字节数

​ ref&＃xff1a;连接匹配条件

​ rows&＃xff1a;查询记录大概需要查询的行数

​ filtered&＃xff1a;返回结果的行占需要读到的行(rows)的百分比

​ Extra&＃xff1a;额外信息

数据库配置文件优化

查看命令&＃xff1a;

show status 看系统的资源

show variables 看变量&＃xff0c;在my.cnf配置文件里定义的

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

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

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

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

query_cache_type &＃61; 1 //开启查询缓存

query_cache_limit &＃61; 50M //单个查询允许的最大的缓存内存&＃xff0c;超过限制则不会缓存

thread_cache_size &＃61; 20 //缓存空闲的线程

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

innodb_log_buffer_size &＃61; 32M //redo log 的写缓存

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

innodb_log_files_in_group &＃61; 3 //可以以循环方式将日志文件写到多个文件

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