首页 > 代码库 > mysql 优化点小节

mysql 优化点小节

1、数据库表设计的合理性

1)三范式

 

一范式:原子性,属性不可分;

 

二范式:无部分依赖,

例:(学号, 课程名称) → (姓名, 年龄, 成绩, 学分),存在部分依赖 (学号) → (姓名, 年龄)

拆分;(学号, 姓名, 年龄),(课程名称, 学分), (学号, 课程名称, 成绩),

 

三范式:无传递依赖,

例:(学号)→(姓名,年龄,性别,系别,系办地址、系办电话)

传递依赖:

(学号)→ (系别)→(系办地点,系办电话)

再拆分。。。

 

2)逆范式:

 

相片表(相片id,名称,点击次数,所属相册id,上传时间)

相册表(相册id,名称,时间)

若有相册点击次数的需求,并且频繁,则需要在相册表添加“点击次数”冗余字段。

 

添加冗余字段的规范:

一对多的情况

冗余的字段应该尽量在“一”的一方。

若在相片表放冗余字段“相册名称”,虽然反问相册名称方便了。但造成极大的空间浪费,并且极大的提高了修改成本。

 

3)反外键

有外键关系,但不加入外键约束。

外键的缺点:略

 

2、sql语句的优化

1)五类sql语句

ddl

dml

select

dtl事务控制语句 commit\rollback\savepoint

dcl数据控制语句 grant\revork

sql优化的核心是select,你知道为什么的。

 

2)show status命令

查看数据库当前状态,比较有用的几个状态包括:

a) show status like ‘Com%‘ <=> show session status like ‘Com%‘ //当前控制台的情况

b) show global status ‘Com%‘; //数据库从启动到现在的状态

c) show status like ‘Connections‘ 显示链接数据库的次数

d) show status like ‘Uptime‘  服务器工作时间(秒)

e) show status like ‘Slow_queries‘ 慢查询的次数(默认是10秒)

 

3)这里我们优化的重点是慢查询。

 

a)show variables like ‘long_query_time‘

默认为10秒,要求高一点,我们设为1秒。

set long_query_time = 1

搞个海量表玩一下,测试性能。

Show status like ‘slow_queres’

发现当前慢查询此时是0。

 

b)可以自定义函数 + 存储过程,创建一个海量表

自定义函数,产生一个随机字符串:

Delimiter $$

Drop function  if exists rand_string;

Create function rand_string(n INT)

Returns varchar(255)

Begin

Declare chars_str varchar(100) default

‘abcdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ‘;

Declare return_str varchar(255) default ‘‘;

Declare i int default 0;

While i < n do

Set return_str = concat(return_str,substring(chars_str,floor(1+rand()*52),1));

Set i = i + 1;

End while;

Return return_str;

End $$

Delimiter ; $$

 

存储过程:

Drop table if exists emp;

Create table emp(

Id int primary key,

name varchar(255),

descp varchar(255),

gene varchar(16)

);

Delimiter $$

Drop PROCEDURE if exists proc_insertemp;

CREATE PROCEDURE proc_insertemp(in start int(10), in max_num int(10) )

Begin

Declare i int default 0;

Set autocommit = 0;

Repeat

Set i = i + 1;

Insert into emp values ((start+i), rand_string(6), ‘salesman‘,‘man‘);

Until i = max_num

End repeat;

Commit;

End $$

Delimiter ; $$

 

使用存储过程

Call proc_insertemp(10000, 20000);

 

c)mysql支持把慢查询语句记录到日志中,供程序员分析。

默认情况是不启用的。

进入到mysql安装目录,启动--slow-query-log

 

d)索引

show indexes from tb

主键索引 alter table tb add primary key (keyname);

唯一索引 unique 即该列具有唯一性,同时又是索引。

普通索引 index

全文索引fullindex(仅mylsam支持)

复合索引(多列在一起,联合索引)从左到右顺序。

 

中文索引

sphinx + 中文分词coreseek

 

4)explain指令

explain select * from tb where id = 2000

select_type: simple

table: tb

type: all  //检索类型

possible_keys: primary //可能用到的索引

key: primary //实际用到的索引

key_len:

ref:

rows: 1 //从多少条记录中取出,因为有索引,所以是1

extra: using where //using temporary using filesort等

 

查询全表的逻辑在真实的项目里是没有道理的,必然存在分页的逻辑。分页必然有索引

 

5)索引添加场景

a) 较频繁的作为查询条件的字段应该创建索引

b)唯一性太差的字段不适合单独创建索引,即使频繁作为查询条件

select * from tb where sex = ‘n男‘;

c)更新非常频繁的字段,不适合添加索引。

 

6)不会用到索引的情况

a)like%放前边不会用到索引,放在中间和后面才能用到;

b)对于复合索引,只要查询条件使用了最左边的列,索引一般就会被使用。而如果只使用右边的列,则不会被使用。

c)如果mysql估计使用全表扫描比使用索引快,则不使用索引。

 

7)使用索引的注意事项

a)如何检测索引是否有效

show status like ‘Handler_read%‘

b)handler_read_key值越高,表示使用索引查询到的次数越多

c)handler_read_key值越高,说明查询效率低

 

8)常用技巧

对于大批量插入数据

a)       myisam先关闭keys,导入完毕再开启;

alter table table_name disable keys;

loading data;

alter table table_name enable keys;

b)       对于innodb 数据排序、关闭唯一性校验(不至于每插入一条校验一条)、关闭自动提交

Set unique_check = 0;

Set autocommit = 0;

 

group by 会默认排序,可以通过order by null禁用排序;

子查询会生成临时表,可以用join代替;

在精度要求高的应用中,建议使用定点数来存储数值decimal,而不要使用浮点数,以保证结果的准确性。如 10000000.32万,插入float(10,2) 型是10000000.31。

日期类型要根据实际需要选择能满足应用的最小存储的早期类型。用时间戳的话,很方便按范围搜索。比如查前三天的记录。但注意int型时间戳,只能表示到2038年。

图片的存储采用路径存储。甚至专门的图片服务器(图床)

 

9)MylSAM和Innodb的区别

前者不支持外键;

前者不支持事务和外键

前者有存储缓存,需要手动回收过期数据。MyISAM创建一张表,对应三个文件,如果Innodb则只有一个文件 *.frm

对于MyISAM数据库,需要定时清理。

optimize table 表名。

 

 

3、数据库参数配置

把缓存设置大一些:

innodb_additional_mem_pool_size = 64M

innodb_buff_pool_size = 1G

key_buff_size

 

4、硬件配置和操作系统

内存超过4G,用64位系统

 

5、分表读写分离

1)表的分割,水平分割(分库分表)、垂直分割(将表的粒度化小)

2)读写分离:缓解查询压力

a)判断请求的sql语句,判断dml语句,则由master处理,slave定时同步master数据。

b)判断若读的sql,则由lvs从slave读取即可。

 

mysql 优化点小节