您的当前位置:首页正文

mysql数据库优化技术(1)

2020-11-09 来源:爱够旅游网

对 mysql 优化是一个综合性的技术, 主要包括: a 、表的设计合理化(符合 3NF ); b 、添加适当的索引( index ) [ 四种 ] :普通索引、主键索引、唯一索引( unique )、全文索引; c 、分表技术(水平分割、垂直分割); d 、读写分离; e 、存储过程(

对mysql优化是一个综合性的技术,主要包括:

a、表的设计合理化(符合3NF);

b、添加适当的索引(index)[四种]:普通索引、主键索引、唯一索引(unique)、全文索引;

c、分表技术(水平分割、垂直分割);

d、读写分离;

e、存储过程(模块化编程,可以提高速度);

f、对mysql配置优化:配置最大并发数(my.ini文件:max_connections最大并发数,一般网站应设置到1000左右,太大的话内存会受不了)、调整缓存大小;

g、mysql服务器硬件的升级;

h、定时去清除不需要的数据,定时进行碎片整理(尤其是MyISAM存储引擎)。


? 数据库三层结构:PHP程序→dbms(数据库管理系统,我们平常说的数据库其实是这个)→数据库(就是文件)。

? PHP程序发送sql语句,dbms进行编译后,再执行,对从数据库中返回的数据进行缓存,所以第二次sql请求时速度会变快。但是使用sql语句去操作,编译会很耗时,我们可以事先把一些经常用的代码在数据库中进行编码形成二进制,再直接调用,这个过程就是存储过程。

? 符合3NF(范式)的表:表的范式,首先符合1NF,才能满足2NF,进一步满足3NF。

? 1NF:即表的列具有原子性,不可再分解,即列的信息,不能再分解。只要数据库是关系型数据库(mysql、Oracle、db2、sql server、informix、sysbase),就自动满足1NF。

? 2NF:表的记录是唯一的,满足2NF,通常我们通过设计一个主键来实现。(主键:一般来讲不含业务逻辑,一般是自增的。因为主键不含业务逻辑,数据较稳定)

? 3NF:即表中不要有冗余数据,就是说,表的信息如果能推导出来,就不应该单独的设计一个字段来存放。下图不符合3NF:


? 反3NF:(相册表的浏览次数是对应photo表的图片浏览次数之和,为了提升响应速度,在每次浏览图片增加图片浏览次数的同时,相册表也同时添加浏览次数。虽然相册的浏览次数可以通过photo表推导出来,但是如果图片太多,双表查询时速度就会慢,通过设计字段views就可以解决,所以必要的数据冗余也是允许的)


? SQL语句优化:如何从一个大型项目中快速定位执行速度慢的语句(定位慢查询)?

? 常用语句:show status:show status like ‘uptime’ 查看MySQL启用多长时间;show [session|global] status like ‘com_select’ show status like ‘com_update’ (默认参数是session会话,指取出当前窗口的执行,global取出从mysql启动到现在的执行次数) 查看对应语句执行了多少次(存储引擎的选择偏向于参考哪个操作执行的多);show status like ‘connections’ 查看试图连接mysql服务器的次数;show status like ‘slow_queries’(显示慢查询次数)、

? 慢查询(默认情况,mysql认为10秒是一个慢查询)优化:定位慢查询(构建一个大表->存储过程;修改mysql的慢查询:显示慢查询值show variables like ‘long_query_time’,修改值set long_query_time=1)

? 把慢查询的sql语句记录到我们的一个日志中(默认下mysql不会记录慢查询,需要在mysql启动时指定记录慢查询才行)。如果启用了慢查询记录日志,默认把这个文件放在my.ini文件记录的位置,如:datadir=d:/wamp/bin/mysql/mysql5.6.12/data(这个地址不要轻易去修改)

? 数据库中可以有多个数据对象:表、存储过程、视图、函数、触发器

? dual亚元表,即一个空表。select rand_string(6) from dual;


优化问题:

? 通过explain语句可以分析,mysql如何执行sql语句

? 建立适当索引:

? 1、添加索引:

? 主键索引的添加:当一张表,把某个列设为主键的时候,该列就是主键索引。创建表后再添加索引:alter table 表名 add primary key (列名)。建立索引是有开销的。不能为空,也不能重复。

? 为什么创建主键索引后速度会变快:没建立索引之前,dbms是按照给定的条件(如id=2)一个一个的顺序去查找。而建立索引后,可以利用二叉树算法(或哈希算法),建立索引文件。二叉树(BTREE)的效率log2N

? 普通索引的添加:普通索引的创建时,先建表,再创建普通索引。create table aaa....,create index 索引名 on 表(列)

? 全文索引的添加:全文索引主要针对文本的检索,全文索引只对MyISAM有效,目前只针对英文有效(sphinx(coreseek)技术处理中文),对停用词不建索引。

CREATE TABLE articles (

id INT UNSIGNED AUTO_INCREMENT NOT NULL PRIMARY KEY,

title VARCHAR(200),

body TEXT,

FULLTEXT (title,body)

)engine=myisam charset utf8;

? 使用全文索引:错误用法 select * from articles where body like ‘%mysql%’;(不会使用全文索引);正确用法:select * from articles where match(title,body) against (‘mysql’)

? 唯一索引的添加:当表的某列被指定为unique约束时,这列就是一个唯一索引(也采用了二叉树的算法)。唯一索引是不能重复的,但是可以为空(NULL可以有多个,’’空字符串只能有一个);创建表后再添加唯一索引:create unique index 索引名 on 表名(列名)

? 复合索引:索引作用在多列上。alter table 表名 add index 索引名 (列名1,列名2...)

? explain:了解sql语句的执行情况




? 2、查询索引:desc 表名(该方法的缺点是不能够显示索引的名字)、show index(es) from 表名(\G)、show keys from 表名(\G)

? D:\wamp\bin\mysql\mysql5.6.12\data\可以看到这个目录中,一个数据库有一个文件夹,使用InnoDB建立的表由三个文件构成:


使用MyASIN引擎建立的表一般只有.frm文件,而起数据反正上以及目录的。

.frm表示表的结构、.MYD表示表的数据、MYI表示表的索引.建立索引后.MYI文件会 变大。

? 3、删除索引:alter table 表名 drop index 索引名;如果删除的是主键索引,还可以:alter table 表名 drop primary key;

? 4、修改索引:一般是先删除,再重新创建。

? 使用索引的注意事项:磁盘占用;对dml(update delete insert)语句的效率影响,变慢:因为在增删改的时候,索引文件会更新(如删除一个记录,对应二叉树也应该删除对应记录),即使这样使用索引利大于弊,大部分网站查询多于增删改。

? 哪些列上适合添加索引:肯定在where中经常使用;该字段的内容不是唯一的几个值(sex);字段内容不是频繁变化的。


? 使用索引时的注意事项:

? 1、对于创建的多列索引,只要查询条件使用了最左边的列,索引一般会被使用。

alter table dept add index myind (dnam,loc)//dnam是左边的列,loc是右边的列。

? 2、对于使用like的查询,查询如果是’%aaa’(%aaa%、_aaa)不会使用到索引,’aaa%’会使用到索引(即,在like查询时,关键字的首个字符是确定的,不能使用%或_,如果前面有变化,则考虑全文索引)

? 3、如果条件中有or,所有使用到的字段都要建立索引(复合索引右边的列也要),建议尽量避免使用or

? 4、如果列类型是字符串,那一定要在条件中将值使用单引号引用起来,否则不使用索引。(值如果是字符串,不使用单引号,直接报错,如果是数字,不使用单引号不会报错,因为会自动转为字符串,但是无法使用索引)

? 5、mysql会估计全表扫描比使用索引还快,则不使用索引。

? 查看索引使用的情况:show status like ‘handler_read%’

? 把一张表的数据导入到另一张表中,建议先禁用索引,要不然在导入数据的同时也会建立索引(不是重点)

? group by分组查询时,默认分组后,还会自动排序(filesort),可能会降低速度。在group by后增加order by null 防止排序。

? 有些情况,可以使用连接代替子查询,因为使用join,MySQL不需要在内存中创建临时表。

简单连接查询:select * from dept,emp where dept.deptno=emp.deptno;

左外连接:select * from dept left join emp on dept.deptno=emp.deptno;

? 如何选择MySQL的存储引擎:

? myisam:如果表对事务要求不高,同时是以查询和添加为主的,我们考虑使用myisam。比如bbs中的发帖表、回复表。

? InnoDB:对事务要求高,保存的数据都是重要数据,建议使用InnoDB。比如订单表、账户表。

? Memory:比如我们数据变化频繁,不需要入库,同时又频繁的查询和修改,考虑使用。

myiasm数据查添加比InnoDB快,因为myisam直接在表尾插入,而InnoDB要先对数据进行事务安全的校验,并进行一个适当的排序。


在PHP开发中,通常不设置外键,通常是在程序中保证数据的一致。

? 如果数据库存储引擎是myisam,一定要定时进行碎片整理(要不然删除的数据永远不会删除):optimize table tablename

显示全文