MySQL优化

MySQL优化

数据库优化包括的方面：

• 表的设计合理化（3范式）
• SQL语句的优化
• 给表要添加合适的索引（四种：普通索引、主键索引、唯一索引unique、全文索引），如何使用索引
• 分表技术（水平分割、垂直分割）
• 定时清除垃圾数据，定时进行碎片整理（尤其存储引擎是myisam的）
• UML序列图和流程图
• 多用存储过程[执行sql：编译->执行->缓存，存储过程（已编译过）：执行->缓存。模块化编程，但是移植性不好]和触发器
• 对mysql的配置[1、配置最大并发数max_connections ,默认值为100，一般网站调整到1000左右。2、调整缓存大小。my.ini文件中设置]进行一些优化
• 读写分离
• MySQL服务器硬件升级

---

数据库设计

• 良好的数据库
  节省存储空间
  保证数据完整性
• 糟糕的数据库
  数据冗余，存储空间浪费
  数据产生不完整

设计数据库步骤

• 充分了解数据
• 标识实体：具体存在的对象、名词
• 标识属性
• 标识关系

一个栗子：

1、需求

2、实体

• 用户
• 发帖
• 回帖
• 板块

3、属性

4、关系

• 一对一
• 一对多
• 多对一
• 多对多

如何将E_R图（实体关系图）转成表：

数据规范化

通过三范式对表进行规范化。
满足顺序：第一范式——第二范式（所有非键字段依赖主键字段）——第三范式（非主键字段消除传递依赖）

1. 确保每列的原子性

   第一范式用来规范所有字段，所有字段都不可再分。
   注意：比如地址这个字段，如果不分类汇总、不排序，仅仅起一个字符串的作用，这时候我们不拆分（反三范式）。

2. 非键字段必须依赖于键字段（一个表只能描述一个事情）

3. 消除传递依赖

   在非主键字段中，如果一个字段可以推导出另一个字段，这就叫做传递依赖。

数据库分类

• 关系型数据库：MySQL、Oracle、SWL Server、db2、informix、sysbase
• 非关系型数据库（面向对象）
• NoSql数据库：MongoDB(面向文档)

定位慢查询

【增删改】（一共占有10%）查（90%）

MySQL数据库一些关于状态的查询

show status指令：

指令	说明
show status	查询mysql数据库的一些运行状态
show status like ‘com_insert’	显示了执行了多少次插入
show status like ‘com_update’	显示了执行了多少次更新
show status like ‘com_delete’	显示了执行了多少次删除
show status like ‘com_select’	显示了执行了多少次查询语句
show status like ‘uptime’	显示了Mysql数据库起用了多长时间，如果时间长了，数据库表的存储引擎有的是myisam的，这时候要注意碎片整理

show[session | global ] status like ……如果不写，默认是会话级的（session），如果mysql一直启动到现在，用global。

显示慢查询

指令	说明
show variables like ‘long_query_time’	显示慢查询的时间，默认是10s是一个慢查询
set long_query_time = 0.5	设置long_query_time为0.5
show status like ‘slow_queries’	显示慢查询条数

定位慢查询（开启慢查询的日志）

开启慢查询日志后，日志文件的位置在my.ini文件中查找 ‘datadir’ ,默认情况下，mysql是不会记录慢查询。

开启慢查询步骤：

1. 关闭当前的MySQL服务 net stop mysql（myswl.exe：客户端， mysqld.exe :服务器端）
2. 安全模式启动就会写日志，考虑MySQL版本相应的命令不同，进入mysql/bin目录下， mysqld –safe–mode –slow–query–log [–safe–mode：安全模式，–slow–query–log：开启慢查询日志]
   注：安全模式下的关闭mysql服务，需要通过mysqladmin -uroot -pXXX shut down。
3. 设置慢查询时间
4. 执行查询语句
5. 分析慢查询日志

explain分析sql语句

索引（建立适当的索引 ——create index）

索引分类：

1. 主键索引
2. 唯一索引
3. 全文索引
4. 普通索引

操作索引

1. 添加

1.1 主键索引添加——把表的某个列设置为主键，则该列就是主键索引

     create table test（id int unsigned primary key auto_increment， name varchar（32） not null default ‘’）；

如果没有指定主键索引，可以创建表后，再次添加指令：

     alter table 表名 add primary key（列名）

1.2 普通索引——一般情况下，先创建表，再创建普通索引

cretate index 索引名 on 表 （列）

1.3 全文索引——FULLTEXT,应用于对文字、文本的检索，比如文章。全文索只针对MyISAM有用

    create table articles（
    id int unsigned auto_increment not null primary key,
    title varchar(200),
    body text,
    FULLTEXT(title, body)
    ）engin=myisam charset utf8

用法：

select * from articles where match(title, body) against('database')

2. 查询

     desc 表名 【缺点：无法显示索引名】
     show index（es） from 表名 
     show keys from 表名
【拓展】
.frm 表的结构
.MYD 表的数据本身
.MYI 表的索引

3. 删除

4. 修改

【为什么创建索引后，速度会变快？】
 索引是以二叉树（BTREE）的算法索引文件