mysql数据库优化

1.表设计
    遵循三范式,但必要的时候做数据冗余,举例说明:在权限模型中可能会用到5张表 用户表、角色表、权限表,还有用户角色关联表和角色权限关联表。如果此时要通过用户查询权限则必须关联查询或者使用多条sql查询,此时可以在用户表增加一个字段来存储用户的权限(例如将权限值使用逗号隔开),这样可以如果查询某用户的权限可以直接在用户表中查询,查询后再用程序来处理。

    设计表时要选用合适的存储引擎,对于经常查询的表,并且不需要事务等应用,优先选用MyISAM(例如新闻表),如果需要事务处理对稳定性要求高的表优先选用InnoDB存储引擎(例如订单表,订单商品表),目前InnoDB已经成为新版MySQL的默认存储引擎,在高并发写入时的稳定性相比MyISAM更高,在读取速度上速度也比较快,所以推荐使用此引擎。 不需要永久保存的数据可以选用Memory存储引擎(例如session,购物车)

    对于不同的字段选用合适的字段类型,例如IP地址应该存储为整型,见将IP以整型的方式存储到数据库中。固定长度的字符串要使用char,例如md5加密的密码。

2.分表技术
    横向分表(各个表的字段类型与数量是完全一致的),例如用户表,可以按用户首字母横向切分;交易记录可以按照年份或者月份切分,每张表的字段一致,至于按什么规则来来区分可以看具体的业务。
    纵向分表(各个表的字段不一致,但条数是一致的),例如某商城网站用户表使用MyISAM引擎,可以应对用户登录时的查询操作,但是用户之间转账需要事务来保证安全,这样就可以将用户余额字段分离出来组成一张InnoDB引擎的新表,和用户表关联,既满足高效查询,又满足稳定性。
    另外还可以进行分区,或者分库,都是类似的思路。
3.索引优化
    常见的索引有 主键索引、唯一索引、普通索引、全文索引(仅MyISAM存储引擎支持,并且不支持中文,如果需要支持中文需要安装插件)。通常需要在条件字段、排序字段、分组字段以及关联字段上建立对应索引。但重复率很高的字段不宜建立索引,比如状态字段(是否被删除,是否上架等),emum类型(性别等),另外建立索引会对增删改操作的速度有影响,因此频繁更新的字段不适合建立索引,例如文章的点击量。
4.SQL优化
在开发时可以使用explain 测试sql语句
在测试结果会有如下参数,简要解释一下
select_type    
查询的方式 SIMPLE表示select类型,没有连接或者子查询,PRIMARY表示主查询(注意:不是主键,例如子查询时的外层查询,UNION查询时的第一个select),DEPENDENT SUBQUERY表示子查询语句,UNION表示UNION查询时除了PRIMARY(第一条语句)之外的语句
table        表名
type        扫描类型(重要)如果是All表示全表扫描,效率低;如果是const表示最多有一行与结果匹配,效率高;system表示表中仅有一条数据,肯定高效;eq_ref表示所以用到主键或者唯一索引;ref表示用到普通索引;range表示查询一个区间(范围的数据);index表示都是通过索引查询性能一般
possible_keys    可能使用到的索引(重要)
key        实际用到的索引(重要)
key_len        索引长度
ref
rows        MySQL认为它执行查询时必须检查的行数(重要)越小越好        
Extra        额外信息 Using filesort表示查询中使用了order by 并且无法利用索引排序,如果确实不需要排序可以在SQL语句末尾增加order by null.Using temporary某些操作使用了临时表,不要.Using where 使用索引.

    编写代码以及SQL语句需要注意的如下几项:

    1)如果查询条件中有or,则所有使用到的字段必须都有索引才有效;

    2)like查询条件中如果以%开头则索引失效;

    3)联合(多列)索引 只有使用左边的列索引才能生效;

    4)where后有多个查询条件应优先使用能够过滤数据量大的条件(先后顺序很重要);

    5)不要使用子查询(效率很低,除非数据量非常少),尽可能少用关联查询,宁可份多次查询,有条件可以封装成存储过程。

    6)不要在循环中使用SQL语句,如果是查询尽可能一次查询然后通过程序来计算。如果是insert可使用扩展插入,即类似insert into tablename values (11,22),(33,33),(45,42)。
    检测MySQL的索引使用情况可以使用以下命令
show global status where Variable_name like ‘Handler_read%‘; 
解释一下主要参数
Handler_read_first 全索引扫描的次数,使用到了索引,但是全部索引;
Handler_read_key 越大越好,使用索引的情况;
Handler_read_rnd_next 表示没有命中,值越大索引利用率越低;

对于已经上线的项目可以开启MySQL的慢查询来定位低效率的SQL,见使用MySQL的慢查询日志找到低效的SQL语句并通过explain分析进行优化
使用如下命令
show global status like ‘Com%‘;
show global status like ‘InnoDB_rows%‘;
show variables like  ‘long_query_time‘;
show variables like  ‘%slow%‘;

查看MySQL各种SQL的执行频度 以及慢查询状态

使用optimize table 表名;命令来优化表,执行时会占用大量资源,所以建议在用户访问量少的时刻执行。
5.配置优化

修改mysql的配置文件,通常在windows下为my.ini,在Linux下为my.cnf

query_cache_size = 64M

该值可以通过使用SHOW STATUS LIKE ‘Qcache%‘;命令来查看MySQL状态来进行相应的更改,如果Qcache_lowmem_prunes的值非常大,则表明经常出现缓冲不够的情况需要增加cache值;如果Qcache_hits的值非常大,则表明查询缓冲使用非常频繁,是理想状态,如果该值较小反而会影响效率,那么可以考虑不用查询缓冲。
对于MyISAM设置
key_buffer_size = 32M
MyISAM表会使用操作系统的缓存来缓存数据,设置的值取决于系统内存、索引大小、数据量以及负载。
对于InnoDB设置
innodb_buffer_pool_size = 2.4G
可以设置内存的70%左右,当然要考虑内存的整体占用情况。

6.硬件架构设计优化

更换硬件 硬盘更换为SSD 可提高几倍的效率,
延时写入(更新),对于实时性较小的数据可以先将数据写入到文件,每隔一段时间批量写入(更新)到数据库中
搭建MySQL主从同步架构,使用读写分离技术
使用sphinx 全文检索服务器, 减轻MySQL服务器的压力

郑重声明:本站内容如果来自互联网及其他传播媒体,其版权均属原媒体及文章作者所有。转载目的在于传递更多信息及用于网络分享,并不代表本站赞同其观点和对其真实性负责,也不构成任何其他建议。