一、数据库设计要合理
- 表的设计要符合3NF,有时需要适当的逆范式
- 1NF为属性的原子性约束,只要是关系型数据库均满足1NF。
- 2NF对记录的唯一性约束,要求记录有唯一的标识(主键)。
- 3NF在满足2NF的基础上,对字段冗余性的约束,任何字段不能由其他字段派生出来。
- 没有冗余的数据库未必是好的数据库,有时为了提高运行效率适当降低范式标准。(如统计一个相册总的点击量时,增加一个点击量字段而不是遍历相册所有相片进行统计)
二、SQL语句的优化
- 优化的一般步骤:
- 通过show status命令了解各种SQL语句的执行频率。
- 显示MySQL的当前状态,主要关心”com”开头的命令
- show status like “Com%” <=> show session status like “Com%”,当前终端会话的使用情况。
- show global status like “Com%”,显示数据库全局使用情况。
- 显示数据库连接次数, show status like ‘Connections’。
- 显示服务器工作的时间, show status like ‘Uptime'(时间为秒)。
- 慢查询的次数,show status like ‘Slow_queries'(默认是10秒),查看当前慢查询的情况show variables like ‘long_query_time’ 该时间可以更改。
- 显示MySQL的当前状态,主要关心”com”开头的命令
- 定位执行效率较低的SQL语句.(重点为Select)。
- MySQL支持把慢查询的语句记录在日志当中,供程序员分析,但默认该功能处于关闭状态需要先开启该功能,开启步骤:
- 启动MySQL的方式,进入到安装根目录运行mysqld.exe。在命令行窗口xx/mysqld.exe/ –slow-query-log
- 更改触发慢查询的时间阈值,set long_query_time=xx(单位为秒)
- MySQL支持把慢查询的语句记录在日志当中,供程序员分析,但默认该功能处于关闭状态需要先开启该功能,开启步骤:
- 通过explain分析低效率的 SQL语句的执行情况,可以得知该SQL语句是否使用索引,是从多少条记录中取出的,并可以看到排序的方式。
- 用法:explain 要分析的SQL语句
- type值表示对表的访问方式
- ALL:代表对完整的表进行扫描,效率不高。
- const: 表最多只有一个匹配行。
- system:表仅有一行(=系统表),是const联接类型的一个特例
- Extra表示查询细节信息
- No tables,Query语句中使用FROM DUAL 或不含任何FROM子句
- Using filesort,当Query语句中包含ORDER BY操作,而且无法利用索引完成排序
- Query Optimizer,通过收集统计信息不可能存在结果
- Using temporary,某些操作必须使用临时表,常见GROUP BY,ORDER BY
- Using where, 不用读取表中所有信息,仅通过索引就可以获取所需数据
- 确定问题并采取相应的优化措施。
- 最常用的方法,添加索引(普通索引、主键索引、唯一索引、全文索引(仅MyIsam引擎支持,中文分词sphinx)、 ),代价减缓插入删除的速度增加了IO的操作。添加索引所要遵循的一般规范:
- 较频繁的作为查询条件的字段应该添加索引。
- 唯一性太差的字段不应该添加索引。
- 更新频繁的字段不适合添加索引。
- 不会出现在WHERE字句的字段不适合添加索引。
- 使用索引时主要点:
- 对于创建的多列索引,只要查询条件使用了最左边的列,索引一般就会被使用,不使用最左边列不会使用索引。
- 对于使用LIKE的查询,查询如果是”%xx”不会使用到索引,”xx%”会使用到索引。
- 如果条件中有OR,即使其中有条件带索引也不会使用。
- 对于字符串类型的字段若未加引号则不会使用索引。
- 如果MYSQL估计使用全表扫描比索引快的情况下则不会使用索引。
- 查看索引的使用情况:
- show status like ‘Handler_read_key’,该值越高表示使用索引查询到的次数越高。
- ‘Handler_read_rnd_next’,该值越高表示查询效率越低
- 大批量插入数据时:
- 对于MyISAM:
- ALTER TABLE 表名 DISABLE KEYS;
- LOADING DATA;
- ALTER TABLE 表名 ENABLE KEYS;
- 对于Innodb:
- 将要导入的数据按照主键排序
- SET unique_checks=0, 关闭唯一性校验
- SET autocommit=0, 关闭自动提交
- MyISAM与Innodb的区别:
- MyIsam不支持外键,Innodb支持。
- MyIsam不支持事务。
- 对数据信息的存储处理不同,MyISAM对每一个表会创建三个数据文件,Innodb只会创建一个.frm的结构文件,所用数据文件均在data目录下的ibdata1文件中。
- MyIsam插入记录时默认是在当前表的最后插入新的数据,删除数据后默认不会回收整理空间有碎片产生,若经常做删除和修改记录的操作,要定时执行optimize table 表名 进行碎片整理。
- 结论:如果应用是以读操作和插入操作为主,只有很少的更新和删除操作操作,并且对事务的完整性、并发性要求不是很高则使用MyIsam,其优势为访问速度快。Innodb提供了提交、回滚和崩溃恢复能力的事物安全,但写的效率差点,会占用更多的磁盘空间。
- 对于MyISAM:
- 优化Group BY语句
- 默认情况,MYSQL对所有的group by col1,col2 进行排序。这与在查询中指定order by col1, col2类似。如果查询中包括group by但用户想要避免排序结果的消耗,则可以使用order by null 禁止排序。
- 有些情况下尽量使用连接语句来代替子查询,因为使用join,MYSQL不需要在内存中创建临时表。
- 如果想在含有OR的查询语句中利用索引,则OR之间的每个条件都必须用到索引,如果没有索引,则应该考虑增加索引。
- 选择合适的数据类型
- 在精度要求高的应用中,建议使用定点数(decimal)而非浮点数来存储数值,以保证结果的准确性。
- 日期类型要根据实际需要选择能够满足应用的最小存储类型(int、bigint), date函数的范围1970-2038(32位int的原因),常用方式date(‘Ymd’, time);
- 字段尽量加上非空约束。
- 对表进行水平划分,所有划分子表表结构相同。
- 对表进行垂直划分,将表中某些大字段分离出来建立新表使用外键进行关联。
- 数据的读写分离,对读请求进行负载均衡。
- 最常用的方法,添加索引(普通索引、主键索引、唯一索引、全文索引(仅MyIsam引擎支持,中文分词sphinx)、 ),代价减缓插入删除的速度增加了IO的操作。添加索引所要遵循的一般规范:
- 通过show status命令了解各种SQL语句的执行频率。
- SQL语句的类型:
- DDL(数据定义语言):create、alter、drop
- DML(数据操作语言):insert、delete、update
- DTL(数据事务语句):commit、rollback、save point
- DCL(数据控制语句):grant、revoke(回收权限)
- select
三、数据库的配置
- 将缓存设置大一些
- 对于 Innodb引擎:
- innodb_additional_mem_pool_size=64M
- innodb_buffer_pool_size=1G
- 对于MyIsam引擎:
- key_buffer_size=xx
- 对于 Innodb引擎:
四、适当硬件配置和操作系统