1. 云栖社区>
  2. PHP教程>
  3. 正文

MySql数据库优化

作者:用户 来源:互联网 时间:2017-12-01 09:39:30

数据库mysql优化

MySql数据库优化 - 摘要: 本文讲的是MySql数据库优化, 建表优化 1.建表尽量符合数据库设计三范式 2.建表时字段可以适度冗余,适度反范式,以达到以空间换取时间效果。 3.根据需求,字段将要存的数据特点,来决定使用哪种数据类型。 4.如果能使用数字类型优先所有数字类型。 5.尽量给每个字段添加


建表优化

1.建表尽量符合数据库设计三范式


2.建表时字段可以适度冗余,适度反范式,以达到以空间换取时间效果。


3.根据需求,字段将要存的数据特点,来决定使用哪种数据类型。


4.如果能使用数字类型优先所有数字类型。


5.尽量给每个字段添加NOT NULL


6.根据实际情况来决定表引擎,一般选择都是Innodb.


7.根据表存放数据的特点,决定选择何种字符集,一般选择都是utf8,如果需要存放emoji表情,选择utf8mb4


8.当一个字段不常用,当一个字段类型比较大的时候,都可以考虑将这些字段分到另外一张表


范式:规则

三范式:就是三个规则


第一范式:确保每列保持原子性


第二范式:确保表中每列都和主键相关


第三范式:确保每列都和主键列直接相关,而不是间接相关


遵守三范式的目的:为了建立冗余较小,结构合理的数据库


MySql数据类型

数值类型


tinyint占用1个字节,表示范围(无符号):0-255有符号:-138~127


int占用4字节,表示范围(无符号):0-42亿有符号:-21亿~21亿


bigint 占用8个字节


float 精度有限


decimal精准(一般用来存放钱等对精准度高的数据)


字符串类型


char(n)char固定长度,n最大是255,n单位是字符


varchar(n)varchar是不定长,n最大可以超过255


时间类型


time 三个字节,存储时分秒


datetime占用八个字节


date占用三个字节,存储到天数。特别适合存储生日


timestamp 占用四个字节,存年-秒。如果一个字段类型是timestamp,在插入数据时,不给这个字段插入数据,mysql自动给这个字段分配时间


文本类型


text建议能不用就不用


数据类型扩展


1.int(9) 与int(10) 区别 int(n),n是不影响int占用字节大小,存储范围。


2.IP地址如何存放到int中可以使用php的ip2long()函数将IP地址转换成数字,long2ip() 将数字转换成IP地址。3.用户状态使用什么数据类型存储用户状态从占用字节大小,扩展性考虑,优先选择tinyint


表引擎

mysql常用引擎myisam,innodb.


myisam,innodb区别


a.速度


MySQL5.6之前,myisam查询速度比innodb快。


MySQL5.6之后,myisam速度相差无几。


b. 锁机制


innodb支持表锁,行锁。 myisam支持表锁。


c. 事务处理不同、


myisam不支持事务,innodb支持事务


d. 存储数据的方式


myisam生成三个文件,分别是.frm 存放表定义,.MYD存放表数据,.MYI存放表索引


innodb数据是存放ibdata1中,.frm存放表结构


mysql锁

1.表锁


当操作数据表,如果你的引擎是myisam,就会锁住整个表。


特点: 锁定粒度大(影响大),锁发生冲突概率大,并发度低,加锁速度快。


2.行锁


当操作数据表,如果你的引擎是innodb,就会锁住一行,或者多行,甚至整个表


特点: 锁定粒度小(影响大),锁发生冲突概率小,并发度高,加锁速度慢。


3.共享锁(读锁)


读锁发生时机: 进行读操作的时候,如果是myisam引擎,会给整个表加读锁。


如果是innodb引擎,会给一行或多行或多表加读锁。


读锁阻塞写,不阻塞读。


4.排他锁(写锁)


写锁发生时机: 进行写操作的时候, 会产生一个写锁。 具体影响范围锁住一行还是一个表,得看引擎。


写锁阻塞读,写


SQL语句优化

1.开启慢查询


2.根据慢查询找到需要执行慢的语句


3.分析语句,得到慢的原因


desc SQL语句/G;


4.根据慢的原因优化SQL语句


一般都是通过添加索引来优化。


添加索引

1.给经常作为where/order by/group by后的字段添加索引


2.减少使用子查询


3.多使用Limit, Limit可以减少传输的数据的大小


4.减少多表联查,设计表的时候,适度冗余字段就可以减少表联查。


5.尽量不要在数据库做运算,使用函数


6.不要使用*号,只查需要的字段。可以减少传输的数据的大小。


7.尽量根据索引来检索数据


8.不在核心业务中使用Like


9.将区分度字段写在where前面


索引

索引概念: 索引就像一本书的目录,可以加快检索数据的速度。


索引优点: 加快检索速度


索引缺点:


1、 占用磁盘空间


2、 导致写速度变慢,因为每次插入新的数据的同时需要更新一下索引索引是有成本,我们需要合理使用索引。


索引如何添加

a. 在建表的时候添加索引


例: create table user( id int auto_increment, name varchar(50) not null, pass varchar(255) not null, primary key(id), unique key(name), key (pass) );


b. 通过修改表结构添加索引


alter table user add 索引类型 索引名字(字段);


索引分类


普通索引、唯一索引、主键索引、全文索引(fulltext)


索引在什么情况下失效


1.like会导致索引失效


like "%name" //失效的 like "name%" //不失效


2. 字段使用函数来修饰


例:select * from u1 where md5(name) = 'j'


3、 联合索引中字段,将区分度较大的字段放大前面

以上是MySql数据库优化的全部内容,在云栖社区的博客、问答、公众号、人物、课程等栏目也有其他相关内容,欢迎继续使用右上角搜索按钮进行搜索数据库 , mysql 优化 ,以便于您获取更多的相关知识。