MySQL数据库优化

目录

前言

一、数据库优化

1、数据库优化的必要性

2、数据库优化的几个方面

二、数据库优化方式

1、库表设计

1.1  标识实体关系

1.2  实体关系

1.3  E-R图，实体-联系图

2、库表添加合适的索引

2.1  索引分类

2.2  创建索引

3、分表

4、读写分离

5、存储过程与触发器

6、SQL优化与慢查询

6.1  数据库的增删改查

6.2  show status的相关常用命令

6.3  启动MqSql使用记录慢查询日志（2种）

6.4  explain

7、碎片整理

8、事务

总结

---

 

 

前言

对于一个以数据为中心的应用，数据库的好坏直接影响到程序的性能，因此数据库性能至关重要。

一、数据库优化

1、数据库优化的必要性

系统的数据都从数据库上来，数据库的吞吐量和速度一定程度决定系统的并发和响应速度；
系统运行与数据量成正比，数据读处理尤其是查询自然就慢；
Mysql数据库的数据最终在磁盘上持久化存储，读写不如Redis等这些内存数据库。

2、数据库优化的几个方面

数据库设计：数据表设计遵循三范式，使用合适的数据类型，使用合适的存储引擎；
适当创建索引；
数据库扩展：数据库的分表分库，读写分离等；
SQL语句优化等。

二、数据库优化方式

1、库表设计

良好的数据库设计，能够节省数据库空间，保持数据完整性，方便应用程序的开发；（相反：数据冗余，空间浪费，插入更新繁杂或者异常）

设计数据库

1.1  标识实体关系

以BBS论坛为例

实体：

用户（属性：昵称，密码，邮箱，生日，性别，登记，备注，积分，注册时间）

主贴（属性：标题，正文，发帖时间，状态，发帖人，回复数量，点击数）

回帖（属性：帖子编号，回帖人，回帖标题，回帖正文，回帖时间，点击数）

板块（属性：板块名称，版主，板块格言，点击数，发帖数）

1.2  实体关系

一对一，两个表的主键是公共字段

一对多，主键与非主键之间的关系

多对一，非主键与主键之间 的关系

多对多，非主键与非主键之间的关系

1.3  E-R图，实体-联系图

创建表时，将实体转化为表，将属性转化为列，唯一标识一行数据的列可为主键，无合适字段做主键就用自动增加列， 将关系转化为主外键展示实体之间的关系；

表结构规范化-三范式：

列的原子性。列不可分解，确保每列都不能再分解成更基本的数据单位；

记录的唯一标识。给记录增加一个主键，非主键字段依赖主键字段，即表的列中若有重复数据且与主键无关，则可拆分表；

字段不存在冗余。不存在传递依赖，即若表的除主键外各个列间有直接关联，即非主键字段一个字段可以推导出另一个字段，则可拆分表。

2、库表添加合适的索引

2.1  索引分类

主键索引，主键查询时默认使用；

组合索引，左边用，右边不用；

模糊查询，%或者_写在左边不会用索引，右边会用；

条件语句中如果有or，or的两侧均为索引才能使用，否则不会使用；

普通索引与组合索引区别：多个普通索引MySQL只用到认为似乎是最有效率的一个单列索引；组合索引为最左前缀，name-age-city建索引，相当于name，age，age-name，city-name；

主键索引与唯一索引区别：主键执行计划优于唯一，主键索引不能为空，仅一个主键索引列，主键索引更适合自生成不改变的列，主键可被其他列引为外键；

唯一索引，检索到一个直接返回；普通索引，检查是否是全部才返回；

2.2  创建索引

创建索引语法：

create index 索引名称 on 表明 （字段名）