浅谈MySQL数据库的几点优化

一、SQL语句优化

(1)使用limit对查询结果的记录进行限定

(2)避免select *,将需要查找的字段列出来

(3)使用连接(join)来代替子查询

(4)拆分大的delete或insert语句

二、选择合适的数据类型

(1)使用可存下数据的最小的数据类型,整型 < date,time < char,varchar < blob

(2)使用简单的数据类型,整型比字符处理开销更小,因为字符串的比较更复杂。如,int类型存储时间类型,bigint类型转ip函数

(3)使用合理的字段属性长度,固定长度的表会更快。使用enum、char而不是varchar

(4)尽可能使用not null定义字段

(5)尽量少用text,非用不可最好分表

三、选择合适的索引列

(1)查询频繁的列,在where,group by,order by,on从句中出现的列

(2)where条件中<,<=,=,>,>=,between,in,以及like 字符串+通配符(%)出现的列

(3)长度小的列,索引字段越小越好,因为数据库的存储单位是页,一页中能存下的数据越多越好

(4)离散度大(不同的值多)的列,放在联合索引前面。查看离散度,通过统计不同的列值来实现,count越大,离散程度越高:

mysql> SELECT COUNT(DISTINCT column_name) FROM table_name;

四、使用命令分析

(1)SHOW查看状态

1.显示状态信息

mysql> SHOW [SESSION|GLOBAL] STATUS LIKE '%Status_name%';

session(默认):取出当前窗口的执行

global:从mysql启动到现在

(a)查看查询次数(插入次数com_insert、修改次数com_insert、删除次数com_delete)

mysql> SHOW STATUS LIKE 'com_select';

(b)查看连接数(登录次数)

mysql> SHOW STATUS LIKE 'connections';

(c)数据库运行时间

mysql> SHOW STATUS LIKE 'uptime';

(d)查看慢查询次数

mysql> SHOW STATUS LIKE 'slow_queries';

(e)查看索引使用的情况:

mysql> SHOW STATUS LIKE 'handler_read%';

handler_read_key:这个值越高越好,越高表示使用索引查询到的次数。

handler_read_rnd_next:这个值越高,说明查询低效。

2.显示系统变量

mysql> SHOW VARIABLES LIKE '%Variables_name%';

3.显示InnoDB存储引擎的状态

mysql> SHOW ENGINE INNODB STATUS;

(2)EXPLAIN分析查询

mysql> EXPLAIN SELECT column_name FROM table_name;

explain查询sql执行计划,各列含义:

table:表名;

type:连接的类型

-const:主键、索引;

-eq_reg:主键、索引的范围查找;

-ref:连接的查找(join)

-range:索引的范围查找;

-index:索引的扫描;

-all:全表扫描;

possible_keys:可能用到的索引;

key:实际使用的索引;

key_len:索引的长度,越短越好;

ref:索引的哪一列被使用了,常数较好;

rows:mysql认为必须检查的用来返回请求数据的行数;

extra:using filesort、using temporary(常出现在使用order by时)时需要优化。

-Using filesort 额外排序。看到这个的时候,查询就需要优化了

-Using temporary 使用了临时表。看到这个的时候,也需要优化

(3)PROFILING分析SQL语句

1.开启profile。查看当前SQL执行时间

mysql> SET PROFILING=ON;

mysql> SHOW profiles;

2.查看所有用户的当前连接。包括执行状态、是否锁表等

mysql> SHOW processlist;

(4)PROCEDURE ANALYSE()取得建议

通过分析select查询结果对现有的表的每一列给出优化的建议

mysql> SELECT column_name FROM table_name PROCEDURE ANALYSE();

(5)OPTIMIZE TABLE回收闲置的数据库空间

mysql> OPTIMIZE TABLE table_name;

对于MyISAM表,当表上的数据行被删除时,所占据的磁盘空间并没有立即被回收,使用命令后这些空间将被回收,并且对磁盘上的数据行进行重排(注意:是磁盘上,而非数据库)。

对于InnoDB表,OPTIMIZE TABLE被映射到ALTER TABLE上,这会重建表。重建操作能更新索引统计数据并释放成簇索引中的未使用的空间。

只需在批量删除数据行之后,或定期(每周一次或每月一次)进行一次数据表优化操作即可,只对那些特定的表运行。

(6)REPAIR TABLE修复被破坏的表

mysql> REPAIR TABLE table_name;

(7)CHECK TABLE检查表是否有错误

mysql> CHECK TABLE table_name;
点赞

发表评论

电子邮件地址不会被公开。 必填项已用*标注