欢迎您访问程序员文章站本站旨在为大家提供分享程序员计算机编程知识!
您现在的位置是: 首页  >  数据库

mysql数据库优化技术(1)

程序员文章站 2024-02-02 10:00:52
...

对 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左右,太大的话内存会受不了)、调整缓存大小;

gmysql服务器硬件的升级;

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


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

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

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

? 1NF:即表的列具有原子性,不可再分解,即列的信息,不能再分解。只要数据库是关系型数据库(mysqlOracledb2sql serverinformixsysbase),就自动满足1NF

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

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

mysql数据库优化技术(1)

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

mysql数据库优化技术(1)

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

? 常用语句:show statusshow 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有效,目前只针对英文有效(sphinxcoreseek)技术处理中文),对停用词不建索引

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语句的执行情况

mysql数据库优化技术(1)

mysql数据库优化技术(1)

mysql数据库优化技术(1)

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

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

mysql数据库优化技术(1)

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

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

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

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

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

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

mysql数据库优化技术(1)

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

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

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

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

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

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

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

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

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

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

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

简单连接查询: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要先对数据进行事务安全的校验,并进行一个适当的排序。

mysql数据库优化技术(1)

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

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