简述: 如何对mysql数据数据库进行有话进而提高程序代码的执行速度,执行效率一直是一个比较重要的问题。
一、优化概述.
存储层:存储引擎、字段类型选择、范式设计
设计层: 索引、缓存、分区分表
架构层:多个mysql服务器配置,读写分离(主从模式)
sql语句层:多个sql语句都可以达到目的的情况下,选择性能搞的,速度快的sql语句
二、存储引擎:
概念:存储数据的技术和功能的合并就称为"存储引擎"
查看mysql中所有的存储引擎: > show engines;
InnoDB:
数据库的每个数据表的数据设计三方面信息:表结构,数据,索引
技术特点:支持事务、行级锁定、外键
数据存储顺序:按照主键的顺序排列每个写入的数据,该特点决定了该类型表的写入操作比较慢
并发性高:该存储引擎的锁表级别是行锁,只锁定被操作的当前纪录
事务: 把许多(增、删、改)的sql语句捆绑在一起,要么全部执行,包么全部不执行
特点:原子性
一致性
持久性
隔离性
语法:commit 和 rollback 只能执行一个
start transaction;
许多sql语句
sql语句有问题
rollback; // 回滚
commit; // 提交
MyISAM:
该类型的存储引擎的数据表 表结构(.frm)、表数据(.MYD)、表索引(.MYI)都有各自的存储文件
特点: 独立的文件存储可以单独备份和还原
存储顺序:按照自然顺序排列每个写进的数据,该特点决定了该表类型的写入速度比较快
并发性:该类型的并发性较低 - 表锁定
压缩机制: 如果一个数据表的数据非常多,为了节省存储空间,需要对该表进行要锁处理
复制当前表的数据: insert into order1 select null, order_num from order2;
问题:
压缩的数据表是只读表,不能写信息
压缩数据表的特点:
不能频繁的写入操作,只是内容固定的数据表可以做压缩处理,例如: 存储全国地区信息的数据表
如果必须写入数据,就得先解压该数据表,写入数据,再压缩
InnoDB存储引擎:适合做修改,删除
MyISAM存储引擎:适合做查询,写入操作
Archive:
归档存储引擎,该引擎只有写入,查询操作,没有修改,删除操作
适用:存储『日志』性质的信息
Memory:
内存型存储引擎,比较适合存储临时信息,服务器断电,给存储引擎的数据立即丢失
三、字段类型的选择
字节占用: tinyint(1)、 smallint(2)、 mediumint(3)、 int(4)、 bigint(8)
① 尽量少的占用内存空间
年龄: tinyint 等
时间类型:
time():时分秒
datetime(): 年月日 时分秒
year(): 年份
date(): 年月日
timestamp(): 时间戳 1970-1-1 到现在的秒数
② 数据的整合最好固定长度
char(长度)
固定长度:运行速度快,255字符限制
长度不固定:内容表少要进行补位操作,该类型要保留1-2个字节保存当前的数据的长度 ,65535字节限制
③ 信息最好存储为整型
时间信息可以存储为整型(时间戳)
推荐使用set和enum类型,内部会通过整型信息参数进行具体计算,运行
ip地址也可以变为整型信息进行存储
mysql 内部 转换ip:
ip转整型:inet_aton(ip)
整型转ip:inet_ntoa(数字)
php: ip2long(ip) long2ip(数字)
四、索引index
索引可以明显提升查询sql语句的速度
索引: 索引本身是一个独立的存储单位,在该单位记录着数据表某个字段和字段对应的物理空间信息
① 索引类型
1. 主键索引: primary key
auto_increment 必须给主键索引设置
信息内容要求不能null,唯一
2.唯一索引:unique key
信息内容不能出现重复
3.普通索引:key
没有具体要求
4. 全文索引:fulltext index
5. 复合索引
② 执行计划 explain
针对查询语句设置执行计划,当前数据库只有查询语句支持执行计划, 查询sql语句真实执行之前所有的资源计划就是执行计划
具体操作:
explain 查询sql语句\G;
ex: explain select * from emp where empno=123\G
③ 索引适合场景
1. where查询条件:where 之后设置的查询条件字段都适合做做索引
2. 排序查询:排序字段适合做索引
3. 索引覆盖,查询的字段已经在索引里边存在,就直接获取即可,不用在到数据库中获取了,该查询速度非常快,效率高,该索引也成为黄金索引
4. 给数据表的外键设置索引,可以提高连表查询速度。
注: 索引本身要占用一定的系统资源
五、索引原则
① 字独立原则
select * from emp where empno=12343; // empno 条件字段独立
select * from emp where empno+=123123; // empno 条件字段不独立
② 左原则:查询条件信息左边出现,就给使用索引
③ 复合索引:
ename复合索引内部有两个字段(ename, job)
① ename(前者字段)作为查询条件可以使用复合索引
② job(后者字段)作为查询条件不能使用复合索引
④ OR原则: OR左右的条件必须都具备了索引才可以使用索引