数据库优化

数据库之优化

全称 ：结构化查询语言

structured Query Language

ctrl+H 查看navicate 历史日志

sql语句

1.select max(字段名) from 表名 查询字段中最大的

select MD5('字符串') 给字符串进行md5加密

2.如果想要查询两个字段的个数 : count

select count(字段名=‘值’ or null) as '值',count(字段名=‘值’ or null) as '值' from 表名

select sex,count(sex) from user group by sex//男女生的个数

3.SQL语句去重函数：distinct

如：select distinct ‘字段名’ from 表名

4.可以使用int存储时间格式

unix_timestamp() 将年月日 时分秒日期函数 转换为int 时间戳

from_unixtime() 将时间戳转换为年月日

5.ip存储可以使用bigint 一般程序员会把它定义为varchar（15）

但是使用bigint（8）只需要8个字节，相差了7个字节；

inet_aton是存ip时用到的函数，inet_ntoa()是取ip时用到的函 数；

来存储IP地址效率很高，效率高很多。

如：insert into 表名 (time,ip) values (unix_timestamp(‘2017-08-29 17：02：03’),inet_aton(‘192.168.1.200’))；

查看：select from_unixtime('time') from 表名； 时间的时候

查看：select inet_ntoa('ip') from 表名； ip的时候

同时查看：

select from_unixtime(time),inet_ntoa('ip') from 表名；

sql循环添加

insert into goods_text(name,type,store) select name,type,store from goods_text

三门成绩大于90分的学生的所有成绩

select name,score from chengji where name in(select name from chengji where score>90 group by name having count(*)>=3);

//这是一条sql 查询年龄在15-20 20-25 25-30 30- 区间段的人数是多少

select case when (tel >=15 and tel <=20) then '15-20' when (tel >=21 and tel <=25) then '21-25' when (tel >=25 and tel <=30) then '25-30' else '30-' end '区间', count(*) c from students group by

case when (tel >=15 and tel <=20) then '15-20' when (tel >=21 and tel <=25) then '21-25' when (tel >=25 and tel <=30) then '25-30' else '30-' end

order by 1

MySQL中的INT类型

1、bigint BIGINT(M),M默认为20.

从 -2^63 (-9223372036854775808) 到 2^63-1 (9223372036854775807) 的整型数据（所有数字），无符号的范围是0到

18446744073709551615。一位为 8 个字节。

2、int

一个正常大小整数。有符号的范围是-2^31 (-2,147,483,648) 到 2^31 - 1 (2,147,483,647) 的整型数据（所有数字），无符号的范围是0到4294967295。一位大小为 4 个字节。

int 的 SQL-92 同义词为 integer。INT(M),M默认为11;

3、mediumint

一个中等大小整数，有符号的范围是-8388608到8388607，无符号的范围是0到16777215。 一位大小为3个字节。MEDIUMINT(M), M默认为9;

4、smallint

一个小整数。有符号的范围是-2^15 (-32,768) 到 2^15 - 1 (32,767) 的整型数据，无符号的范围是0到65535。一位大小为 2 个字节。MySQL提供的功能已经绰绰有余，而且由于MySQL是开放源码软件，因此可以大大降低总体拥有成本。SMALLINT(M), M默认为6;

5、tinyint tinyint(M), M默认为4;（保存时的默认值）

有符号的范围是-128 - 127，无符号的范围是 从 0 到 255 的整型数据。一位大小为 1 字节。

mysql 获取当前日期 now();

日期格式化 date_format();

连接字符串 concat();

四舍五入 round();

把文件里的sql导入数据库 source 路径 如 source F:/a.sql

导入失败如何调优 即数据库包大小限制 max_allowed_packet

char与varchar的区别

char的长度是固定的，而varchar的长度是可以变化的

比如，存储字符串 'abc'，对于char(10),表示你存储的字符将占10个字节(包括7个空字符)，而同样的VARCHAR2 (10)则只占用3个字节的长度，10只是最大值，当你存储的字符小于10时，按实际长度存储。

VARCHAR2比CHAR节省空间，在效率上比CHAR会稍微差一些，即要想获得效率，就必须牺牲一定的空间，这也就是我们在数据库设计上常说的‘以空间换效率’。

explain主要用于sql语句中的select查询，可以显示的查看该sql语句索引的命中情况

cmd命令连接mysql mysql -uroot -proot 后面不能 跟分号

cmd命令断开mysql exit 或者quit;

①创建数据库：create database '数据库名'

查看数据库：show databases

选择数据库：use '数据库名'

删除数据库：drop database '数据库名'

查看当前库所拥有的表：show tables;

表

②创建表 create table '表名'

create table '表名'(

id int (11) not null auto_increment primary key,(有逗号)

name varchar(30) not null(注意：没有逗号)

)engine=innodb/myisam default charset=utf8;

查看表结构 desc '表名' 或者 describe '表名'

or show columns from '表名'；

or show create table '表名'\G

查看表中某一个字段 desc '表名' '字段名'

修改表字段长度：alter table '表名' modify '表中字段名' '新长度'；

给表进行重命名：rename table '当前表名' to '新的表名'；

给表增加一个字段：

alter table '表名' add '字段名' char(32) not null;

alter table '表名' add '字段名' char(32) not null first;[把新列 放在最前面]

alter table '表名' add '字段名' char(32) not null after '字段 名'；把新列放在某列后面；

如何修改字段名：alter table tab_name change old_col new_col varchar(40); //必须为新字段指定数据类型等属性，否则不能修改

删除表中某字段

alter table 表名 drop column 字段名；

删除这个表

drop table 表名

表数据

③增加表数据

insert into 表名 （字段名） values （新值）

增加多条表数据

insert into 表名 （字段名）values （新值），（新值），（新值）

删除表数据

delete from 表名 where 条件

清空表数据 truncate '表名'

update 表名 set 字段名='新值' where 条件

查看表数据

select * from '表名'

④//对数据库数据进行备份

mysqldump -uroot -proot laravel(数据库名) >路径（如：F：\laravel.txt）;

//对数据库数据进行恢复

mysql -uroot -proot 数据库名 <路径（如：F：\laravel.txt）;

MYSQL数据库优化：几个方面

硬件 （成本最高）

系统配置

数据库表结构

SQL及索引（效率最高）很重要

A---

1.使用mysql慢查日志对有效率问题的sql进行监控

可以通过慢查日志查询有问题的sql

慢查询日志：记录所有执行时间超过long_query_time秒的所有查询或不使用索引的查询。

select @@version 查看（mysql）版本

show variables like version 查看（mysql）版本

show variables like 'slow_query_log' 查看是否开启慢查询日志

show variables like '%log%' 查询log变量设置

记录未使用索引的查询

set global log_queries_not_using_indexes=on;

开启慢查询日志 set global slow_query_log=on;

超过多少秒的查询记录到慢查询日志中。

set global long_query_time=1;

慢查询日志位置：show variables like 'slow%'

2.使用explain 及sql语句

extra列需要注意返回值：

Using filesort:看到这个的时候，查询需要优化了。

Using temporary 看到这个的时候，查询需要 优化了。

（这个通常发生在ordey by 上，而不是group by 上）

3.sql优化之 Count() 和Max();

时间戳越大，时间就越往后。

①select max('字段名') from 表名；

优化的时候可以给查询的字段加索引，就会速度很快。

大大优化了sql执行 的效率。

给字段加索引：

create index 索引名 on 表名（字段名）；创建普通索引

删除索引

drop index 索引名 on 表名；

create unique index 索引名 on 表名(字段名);创建唯一索引

alter table 表名 add primary key 索引名(字段名);创建主键索引

②.如果想要查询两个字段的个数 : count

select count(字段名=‘值’ or null) as '值',count(字段名=‘值’ or null) as '值' from 表名

select sex,count(sex) from user group by sex//男女生的个数

count(*) 与count('字段')区别

count(*)会查询出空值，count('字段') 不会查询出空值。

4.子查询的优化：

通常情况下，需要把子查询优化为join 查询，但是在优化的时候要注意关联建是否有一对多的关系，要注意重复数据。

可以使用distinct() 进行去重。

5.limit的优化：

使用有索引的列或主键进行order by 操作。

select 字段 from 表名 order by 主键 limit 50.

B--- 索引优化

如何选择合适的列建立索引？