mysql优化(一)

mysql优化(一)

优化：

一、表设计优化。

1、适当的反范式。范式化的更新操作通常比反范式化要快，范式化设计的缺点是通常需要关联。反范式的优点：避免关联，避免了随机I/O，能使用更有效的索引策略。

2、合适的索引。

3、大字段用垂直拆分。

4、大表用水平拆分。

5、抛弃外键。

6、合适的数据类型。

二、sql语句优化。

1、查询字段中只返回需要的字段。特别是大字段内容。

2、只返回需要的数据记录。本来只需要一条数据，结果你返回10万条数据。

3、使用索引高效查询。不要让索引失效。

4、

三、配置优化。

1、

2、

3、

四、查看执行计划。

1、

2、

3、

五、定位慢查询和故障解决。

1、

2、

3、

一、SQL语句优化。

（1）使用limit对查询结果的记录进行限定。（2）避免select *，将需要查找的字段列出来，只返回需要的数据字段，减少不必要的数据。

优点：

1、减少数据在网络上传输开销。

2、减少服务器数据处理开销。

3、减少客户端内存占用。

4、字段变更时提前发现问题，减少程序BUG。

5、如果访问的所有字段刚好在一个索引里面，则可以使用纯索引访问提高性能。

缺点：增加编码工作量

由于会增加一些编码工作量，所以一般需求通过开发规范来要求程序员这么做，否则等项目上线后再整改工作量更大。

如果你的查询表中有大字段或内容较多的字段，如备注信息、文件内容等等，那在查询表时一定要注意这方面的问题，否则可能会带来严重的性能问题。如果表经常要查询并且请求大内容字段的概率很低，我们可以采用分表处理，将一个大表分拆成两个一对一的关系表，将不常用的大内容字段放在一张单独的表中，即表的垂直拆分。

（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;

五、定位慢查询

六、分区

七、配置优化

版权声明：本文内容由网络用户投稿，版权归原作者所有，本站不拥有其著作权，亦不承担相应法律责任。如果您发现本站中有涉嫌抄袭或描述失实的内容，请联系我们jiasou666@gmail.com 处理，核实后本网站将在24小时内删除侵权内容。