数据库优化

Mysql 的索引’

概述：索引就是编号，添加索引后，查询效率会提高，增删改效率会相对降低

分类：普通索引

唯一索引：我们以前用的主键约束，唯一约束就是俩个比较特殊的唯一索引

创建普通索引

create index 索引名 on 数据表名（字段名（长度））；

创建唯一索引：

create unique index 索引名 on 数据表名（字段名（长度））

删除索引 ：alter table 数据表名 drop index 索引名 //删除普通索引

alter table 数据表名 drop primary key; //删除主键索引

显示索引信息

show index from 数据表名

事务的概述:

事务指的是逻辑上的一组操作,组成该操作的各个逻辑单元,要么全部成功,要么全部失败

事务的特点:ACID

原子性 :事务的各个逻辑单元已是最小单位,不可继续分割

一致性:事务执行前后的数据应该保持一致

隔离性: 一个事务执行的过程中不受其他事务的干扰

持久性:事务执行完之后 结果应持久化到数据表中,进行永久存储

效率从高到低分别是:

read unconmmitted>read committed >repeatable read >serializable

隔离级别:

serializable >repeatable read > read committed > read uncommitted

事务控制语句:

begin 或者 start transaction   //开启事务

commit  //提交事务

rollback // 回滚事务

sql 操作事务的流程

1. 开启事务

begin 或者 start transaction

1. 执行相关操作

例如

先执行 50% -->保存一个节点(savepoint)

再接着执行 25% -->保存一个节点

最后在执行25%的时候,发现前25%执行错误, 如果采用rollback回滚事务会导致效率降低 ,可以通过恢复节点的方式进行处理将数据恢复到执行完50%节点上,然后执行剩下的俩个25%

3)提交事务,如果整条SQL语句执行都有问题,可以选择回滚事务

mysql 使用事务时可能遇到的问题

1)多个事务并发执行,会导致(脏读, 不可重复读,  虚读)

2)事务在运行过程中被强行停止

不考虑隔离性的问题和解决方案

脏读:  读未提交  一个事务读取到另一个事务还没来得及提交的数据

不 可重复读: 读已提交  一个事务读取到另一个事务已经提交的修改数据 导致俩次查询结果不一致

虚读 :一个事务读取到了另一个事务已经提交的添加的数据 导致俩次查询结果不一致

串行化读取:

一个事务A在操作某一张表的时候,另一个事务必须等待A操作完毕后草可以操作该表

四大隔离性:

read uncommitted :读未提交 可能发生脏读 不可重复读 ,虚读

read committed : 读已提交 能规避脏读, 可能发生不可重复读 虚读

repeatable read : 可重复读 能规避 脏读 不可重复读 可能发生虚读

serializable  能规避脏读, 不可重复读 虚读

mysql 的封锁

排它锁 //x锁 :可读可写 一个事务对表加了X锁 其它事务必须等到该事务操作完这张表后,才可以対这张表操作

共享锁 ://S 锁  只读 多个事务可以同时对么一张表加共享锁

活锁 // 有几率解开  某个事务在永远等待的状态,得不到封锁的机会 这种现象称为活锁

死锁 :肯定解不开  俩个或俩个以上的事务都处于等待状态每个事务都在等待对方事务接触封锁 这样任何事务都处于等待状态而无法继续执行的现象称为死锁

mysql的目录结构

/etc/my.cnf       //这是mysql的主配置文件  Linux 操作系统中后缀名是.cnf window 系统中是.ini

/var/lib/mysql    //mysql中数据库及数据存储的目录

/var/log/mysql   //数据库的日志文件存放目录   该路径也可能是/var/lib/mysql

netstat  -nltp  //查看端口

//ps -ef | grep mysql  //查看所有课mysql 相关的进程

数据库分类

关系型数据库 : mysql  Oracle  sqlserver

非关系型数据库 :redis hbase 等

数据库设计流程:

1. 需求分析: 明确客户需求 到底做什么?

2. 概念模型设计 : 该阶段是整个数据库设计的关键 它通过对用户需求进行综合,归纳 与抽象 主要是通过E-R图表示

优点 :简洁明了 描述了各个表之间的逻辑关系 独立于计算机与具体的RDBMS无关

3)逻辑模型设计 :与具体的数据库相关,反应业务部门的

4)数据库实施: 创建数据库 定义数据库结构 组织数据入库 调试数据库并进行数据库的试运行

5)数据库的运行和维护 :数据库正式运行之后对数据库运行过程中对其进行评价 调整 修改 调优等

数据库设计遵循的原则

范式 就是符合某一规范级别的关系模式的集合 共有7种范式

第一范式 :每个属性都是不可分割的

第二范式: 主属性(主键)和 非主属性 直接依赖

第三范式 : 主属性(主键) 和非主属性 间接依赖

优点

1)范式主要说明的就是:设计表的时候,扩展拆分(分离)程度

1. 范式越高,会让扩展的程度变得更好

缺点:编写sql 语句时会变得更加的繁琐

为什么要进行数据库的优化

1)避免网站页面出现访问错误

由于数据库连接超时产生页面错误,由于慢查询造成页面无法加载

2)增加数据库的稳定性

很对数据库问题都是由于低效的查询引起的

3)优化用户体验

    流畅页面的访问速度

     良好的功能体验

数据库优化的方案

优化"成本"从低到高分别为 :

Sql 语句及索引 --> 数据表结构--> 系统配置-->硬件

1)sql 及索引优化

  根据需求写出良好的sql 并创建有效的索引 ,实现一种需求可有多种写法 这时候我们就需要选择一种效率高的写法 这个时候就需要Sql 优化

2)数据库表结构优化 

根据数据库的范式,设计表结构 ,表结构设计的好坏直接关系到写SQL语句

3)系统配置的优化

     大多数运行在Linux机器上 ,如tcp 连接数的限制, 打开文件数的限制,安全性的限制,因此我们要对这些配置进行相应的优化

 4)硬件配置优化

选择合适数据库服务的cpu 更快的io   更高的内存 

如何发现有问题的sql

mysql 提供了慢查询日志查看功能,可以帮助查看某一条sql语句执行的一些状态

查看mysql 是否开启慢查询日志

show variables like '%slow %'  //默认是关闭状态

启用mysql 的慢查询日志功能

set global log queries not using_indexes=on // 将不使用索引的慢查询日志进行记录

set global slow_query_log=on // 开启慢查询日志

show variables like '%slow%'// 查看慢查询日志存储的位置

set global long_query_time=1 //大于1秒钟的数据记录到慢日志中 如果设置为默认0 则会有大量的信息存贮在磁盘中,磁盘很容易满掉

监听日志文件,看是否写入 如果自己不小心删除了 log 日志文件 重启下mysql 服务 然后重新登录即可 service mysql restart

如何通过慢查询日志发现有问题的sql

a)查询次数多且每次查询占用时间长的sql

b) Io大的sql :扫描的行数越多,IO 越大

c) 未命中索引的sql

explain 关键字: 

sql 的执行计划 侧面反映出了sql的执行效率  具体的执行方式是只需要在执行的sql前面加上explain 关键字即可

用法 : explain select *from film ; // 查看指定 sql的执行效率 (横向排列)

explain  select * from film \g  // 查看指定的sql 的执行效率 (纵向排列)

名词解释:

table :显示这一行的数据是关于哪张表的

type : 这是重要的列,显示连接使用了何种类型 连接类型从最好到最差分别为

const >eq_reg >ref >range >index 和all  

possible_keys 显示可能应用在这张表中的索引

key:实际使用的索引,如果为null则没使用索引

key_len 使用索引的长度 ,在不损失精确性的情况下,长度越短越好

ref:显示索引的那一列被使用了

rows: mysql 认为必须检查的用来返回请求数据的行数

总结: 我们重点关注的是type(尽可能不要查询整张表) key (尽可能使用索引).rows_(行数不能太大,尽可能降低扫描次数)

聚合函数的优化方案 : 通过加索引的方式实现优化  然后执行查询语句 索引是顺序操作的 不需要扫描表,执行效率就会比较恒定

子查询的优化

子查询 是我们在开发过程中经常使用的一种方式,在通常情况下 需要把子查询优化为join查询 但在优化时 需要注意是否有一对多的关系 要注意重复数据  注: 在编写代码的时候,如果使用子查询可能会比多表连接查询更加的简单 所以在开发阶段可以使用子查询 只要能满足需要就可以但在正式上线商业化使用的时候,尽可能将子查询更改为join查询会更好一点 join查询的效率要比子查询的效率更高  建议:可以先用子查询实现功能,然后后期再优化为连接查询即可

group by 分组优化 :先分组

limit 查询的优化

select film_id,description from where film_id>=150 and film_id<155  oder by film_id limit 150 ,5; 扫描行数不变, 执行计划很 固定,效率也是很固定的 

注意 :主键要顺序排序并连续的 如果主键中缺了某一列 或者某 几列 会出现数据不足5行的数据 如果不连续,建立一个附加的index_id 列 保证这一列数据要自增的,并添加索引即可

总结: 尽可能避免文件排序 和row的扫描(越少越好)因为这俩种操作都会增加IO的操作 从而降低sql语句的效率.

 

 

 

 

 

 

 

 

 

 

转载于:https://my.oschina.net/u/4140673/blog/3057518