MySQL优化⽅⾯ 问题
1、发布系统的存储采用MySQL数据库。每天增加5万多件，预计运行和维护将持续三年。如何优化它？
- a、 设计良好的数据库结构,允许部分数据冗余，尽可能避免join查询，并提高效率。
- b、 选择合适的表字段数据类型和存储引擎，适当的添加索引。
- c、 Mysql库主从读写分离。
- d、 找规律分表，减少单表中的数据量提高查询速度。
- E、 添加缓存机制，如memcached和APC等。
- f、 不经常改动的页面,生成静态页面。
- g、 书写高效的SQL。⽐ 例如，select*from table更改为select field,field2 from table
2、如何在实践中优化MySQL
- 最好按以下顺序进行优化：
- 1、SQL语句和索引优化
- 2、数据库表结构优化
- 3、系统配置优化
- 4、硬件优化
3、数据库优化方法
- 1、选择最合适用的字段属性，尽可能减小定义的字段宽度，尽量把字段设置为NOTNULL，例如，“省”和“性别”最好适用“ENUM”
- 2、使用连接(JOIN)来代替子查询
- 3、使用联合(union)来替换手动创建的临时表
- 4、事务处理
- 5、锁定表，优化事务处理
- 6、适用外键，优化锁定表
- 7、建立索引
- 8、优化查询语句
4、如何通俗地理解这三种范式？
- 第一范式：字段不可分；
- 第二种范式：有主键，非主键字段依赖主键；
- 第三种范式：非主键字段不能相互依赖。
- 规范化设计的优缺点：
- 优势：
- 可以尽可能减少数据冗余，以便更新速度更快，卷更大⼩
- 缺点：对于查询，需要输入多个表⾏ 联想，降低了写作效率，提高了阅读效率，使其更难进入⾏ 索引优化
- 非规范化：
- 优点：可以减少表之间的关联，更好地优化索引
- 缺点：数据冗余，数据异常，数据修改成本较高
5、优化SQL语句的方法有哪些？
- （1） 在“where”语句中：“where”表之间的连接必须写在其他“where”条件之前。那些可以过滤掉最大数量记录的条件必须写在“where”子句的末尾
- HAVING最后。
- Dome如下:
-
(低效,执行时间156.3 秒)
SELECT * FROM EMP E WHERE SAL > 50000 AND JOB = ‘MANAGER’ AND 25 < (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO);(高效,执行时间10.6 秒)
SELECT * FROM EMP E WHERE 25 < (SELECT COUNT(*) FROM EMP WHERE MGR=E.EMPNO) AND SAL > 50000 AND JOB = ‘MANAGER’; - （2）⽤EXISTS替换in，⽤ NOT EXISTS替换NOT IN。
-
如:
低效 Not in 法:排序和合并
select * from TA where TA.id not in(select id from TB)
高效 Exists 法:是一个外连接select * from TA where not Exists (select id from TB where TA.id = TB.id) - （3） 避免在索引列上使用计算
- （4） 避免在索引列上使用IS NULL和IS NOT NULL
- （5） 对查询进行优化，应尽量避免全表扫描。首先应考虑在where和order by中涉及的列上建立索引。
- （6） 应尽量避免在where子句中对字段进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描
- （7） 应尽量避免对where子句中的字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描
- 例如:
- select id from t where nu,/2=100 应改为 select id from t where num = 100 *2
