美文网首页
MySQL优化

MySQL优化

作者: 忘忧谷主 | 来源:发表于2018-03-07 11:01 被阅读0次

    一、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> SELECTCOUNT(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 STATUSLIKE'handler_read%';

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

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

    2.显示系统变量

    mysql>SHOW VARIABLESLIKE'%Variables_name%';

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

    mysql>SHOW ENGINE INNODB STATUS;

    (2)EXPLAIN分析查询

    mysql>EXPLAINSELECTcolumn_nameFROMtable_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>SELECTcolumn_nameFROMtable_namePROCEDUREANALYSE();

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

    mysql>OPTIMIZETABLEtable_name;

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

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

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

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

    mysql>REPAIRTABLEtable_name;

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

    mysql>CHECKTABLEtable_name;

    五、定位慢查询

    六、分区

    七、配置优化

    相关文章

      网友评论

          本文标题:MySQL优化

          本文链接:https://www.haomeiwen.com/subject/brqjfftx.html