数据库优化之mysql

1. 优化流程图

mysql优化(主要增加数据库的select查询,让查询速度更快)

2. 优化mysql的方面

主要从以下四个方面去优化mysql

①存储层:如何选择一个数据库引擎,选择合适的字段列类型

②设计层:满足表的三范式,建立适当的索引,分表分区

③sql层:设计出更好的sql语句

④架构层:msyql读写分离

2. 数据库表的引擎

1. 引擎的简介

不同的数据表引擎有不同的存储数据的方式,其索引结构也是不一样的,从而提供不一样的功能.

如:innodb引擎提供事务的功能,myisam引擎就没有

2. 数据库表的引擎的相关的操作

①,查看数据库支持哪些数据表引擎

以后面试基本能够回答myisam和innodb引擎的区别即可

3. myisam引擎和innodb引擎的区别

myisam和innodb引擎的区别:

①innodb引擎支持事务,myisam不支持

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

③myisam主要用在高并发的一些网站,innodb主要用在逻辑性(支持事务)比较高的一些网站

修改一个表的引擎的命令

alter table 表名 engine=innodb

4.myisam引擎和innodb引擎生成数据文件的方式

①创建一个myisam的引擎表

打开mysql的安装目录的data目录查看创建的表结构文件:

②innodb引擎

查看mysql的安装目录的data目录,查看表的结构:

它的数据文件(表名.MYD)和数据表的索引文件(表名.MYI)去哪里了?

4. 数据库的备份

mysqldump命令备份数据库:

备份操作(mysqldump):

还原操作:

先建立一个数据库如(db2)

执行还原的命令(mysql)

mysql可视化工具备份和还原:

备份操作:

还原操作:

5. myiam引擎和innodb引擎插入数据的方式(问题)

①myisam引擎表插入数据

②innodb引擎插入同样的数据

总结:

①myisam引擎插入数据的时候没有按照主键的顺序进行插入,内部没有一个排序的操作

②使用innodb插入数据的时候,内部会按照主键进行一定的排序,排序的过程是需要消耗时间和内存的.

所以我们如果要做大批量数据的插入使用myisam的引擎

3. 选择合适的字段类型

1. 选择字段的基本原则

①使用最小的列类型来存储(如能使用tinyint就不要使用int),因为大的数据类型所占用的字节空间会大点,那么检索数据的速度会慢点

②定义一个表的字段类型的时候,建议设置为not null ,因为一个null会占用一个字节的空间,最好设置一个默认的值 如(varchar not null default ''')

③如果一个数值是正型,我们应该把它设置为unsigned,因为无符号型存储的数据量会大点.

2. mysql的字段类型回顾

char(定长字符串类型):占用0-255字符

varchar(变长字符串类型):占用0-65535,还需要1-2字节空间保存数据类型本身

text:基本无限制

date:日期:如2016-09-12

datetime:日期时间:2016-09-12 12:12:12

enum:枚举类型;一般用在单选enum('男','女')

set:集合的数据类型:一般用在多选 set('学代码','吃饭','睡觉');

3. 如何选择字段类型来存储数据

问题1:存储一个时间用什么类型比较好?

建议大家用int?因为使用int类型方便转化为日期或日期时间,也可以方便我们的时间的一个对比查询.

有时候也可以用datatime,这样把数据取出来就不需要在模板中进行转化

问题2:存储一个不确定的数据(如:文章的标题)

这时候使用varchar(20);

因为标题的长度不确定有多长,我们就使用varchar,如果使用char类型来存储,它会占用指定的空间大小,这样浪费空间,而且检索数据的速度也会慢些

如存储md5加密后的密码用char(32),

如手机号11为:char(11)

问题3:mysql中ip和数字的转化

可以使用int来存储我们的ip地址:

php中ip和数字的互相转化:

ip2long('ip address'): 把ip地址转化为数字

long2ip(): 把数字转化为ip地址

4. 建表的三范式

我们平时建表的时候基本都会满足三范式

1. 第一范式(1NF)

第一范式要保证字段的原子性,

如建立一个用户表

id name address

1 小白 广东广州市天河区

2 小黑 广东深圳市宝安区

以上用户表的address字段没有满足第一范式,因为address的值可以进行划分为省,市,详细地址

修改以上表结构为:

id name province city area

1 小白 广东 广州市 天河区

2 小黑 广东 深圳市 宝安区

2. 第二范式(2NF)

第二范式要保证每个字段和主键不可以有传递依赖

订单表:

order_id room_id username card phone

1 202 小白 234553X 13433333333

2 203 小白 234553X 13433333333

以上订单表有个问题,如果一个顾客有多个订单,那么久对应着多条订单记录,就会产生多条同一个用户的相关信息,那么多出来的用户的信息就有数据冗余

改造订单表如下,给用户信息单独来设置一个表

订单表

order_id room _id user_id

1 202 8

2 203 8

用户表

user_id user name card phone

8 小白 234553X 13433333333

3. 第三范式(3NF)

要保证每个字段和主键直接依赖,以上基于第二范式的完成就已经满足我们的第三范式了.

下一个订单要操作的基本表如下:

订单信息表

订单地址

用户信息表

商品信息表

商品优惠券表

商品属性表

商品图片表

商品分类表

5. mysql中的索引

1. 索引简介

生活上的索引:

书本的目录,公交牌,

使用索引的可以加快select查询的速度,但是对我们的写(insert,update,delete)操作效率会有所降低

2. mysql索引的分类

①主键索引 primary key 不能为空 not null 一个表只能有一个主键索引

②唯一索引 unique key 可以为空,一个表可以有多个唯一索引

③普通索引 key 可以为空,一个表可以有多个普通索引

④全文索引 fulltext index (varchar text)(sphinx)

复合索引:多个字段共同组成的索引

3. 索引基本操作

创建索引一般有两种方式

①创建表的时候指定索引(create)

②修改表结构的方式(alter)

①主键索引的增删改

a.主键索引的创建

create table tab_name(

id int not null auto_increment

primary key(id)

)

如果一个字段是自动增长的它必须是主键值,但是主键值不一定非要是自动增长

删除主键索引: alter table tab_name drop primary key

(注意:如果一个主键字段是自动增长的,必须先删除自动增长的属性,才可以继续删除主键)

添加一个主键索引: alter table tab_name add primary key(id)

②普通索引的增删改

create table table_name(

id int not null auto_increment,

name varchar(30) not null default '',

email varchar(30) not null default '';

primary key(id), //主键

unique key(email) , //唯一键

key key_name (name) //普通键

)

修改表的结构索引

唯一的索引的添加

alter table table_name add unique index uni_em (email)

alter table table_name add unique key uni_em (email)

普通索引的添加操作

alter table table_name add key index_name (name)

alter table table_name add index index_name (name)

4. 构造一张大型的数据表

可以进行蠕虫复制

insert into user (username) select username from user

insert into user (username,email) select username,email from user

例,使用主键和不使用主键查询一条记录的测试

①使用主键id=2000000的记录信息

②不使用主键索引进行查询

5.explain分析sql语句的索引使用情况

使用explain可以知道一条sql语句的使用索引的基本情况,只需要在查询语句前面加个explain或者是desc,从而可以更好的优化我们的sql语句

如: explain select * from users where id=200000;

desc select * from users where id=200000;

使用到了主键索引:

没有使用到索引查询,进行了全表扫描

6. 如何正确的使用索引

①进场出现where条件后面的字段,我们就可以把字段设置为索引

给字段no_index设置为普通索引

查看建表语句:

再次可发现使用到了索引:

②模糊查询索引的使用情况

对关键字查询一般有以下两种情况

like '%关键字%' ========> 不会时候到索引

like '关键字%' ========> 使用到索引查询

只要关键字左边有%,就不会使用到索引查询

③使用索引优化order by语句

④,优化分页limit语句

⑤复合索引的最左原则(面试经常问)

只要我们在where条件后查询的字段出现了复合索引的最左边的一个字段,那么其就会使用到索引查询.

先建立一个复合索引

建立一个复合索引,把username,和email字段作为复合索引

alter table users add index index_user_email(username,email);

a.

b.

如果想使用复合索引的后面字段,必须先使用最左边的一个字段

复合索引的索引长度计算:

215=(30+40)*3+2*2(变量类型本身2)+1(null)

utf-8下一个字符占用3个字节

gbk:下一个字符占用2个字节

六.慢日志查询

简介

当一条sql执行比较慢的时候(10秒),我们可以通过mysql的一个慢日志查询的功能可以记录

这条比较慢sql,我们可以定义一个我们认为比较慢(10秒)的sql语句,那么超过十秒钟的sql语句就会被记录下来,从而我们就可以优化我们这个有问题的sql语句

开启慢日志查询

①开启慢日志查询:

②修改mysql的配置文件,加以下两个选项

记得保存,重启mysql服务器.

③,进行测试:

打开慢日志文件如下:

注意:mysql查询的时候,有时候(默认)有缓存,我们可以通过以下配置禁用缓存

查询的时候也可以不查询缓存数据

select sql_no_cache from table_name...............