mysql-常规优化思路

八戒你干嘛

操作系统优化

sysbench 工具

1.测试CPU性能

2.测试IO读写性能

3.测试事务性能



数据库系统参数优化

1.使用 show processlist命令长时间查看服务器负载情况

2.开启服务器慢查询开关

3.减少临时表使用，可以EXPLAIN 语法查看 extra 是否为 using temporary

@如果group by 的列没有索引，会产生内部临时表

@如果order by 与 group by为不同列时，或多表联查时 order by,group by 包含的列不是第一张表的列，会产生外部临时表

@如果distinct 与 order by 一起使用可能会产生临时表

@如果用union合并查询会用到临时表

@如果表中数据过大，可能会把临时表转存在磁盘上处理



数据表设计

1.表结构拆分，核心字段与非核心字段或长文本字段最好拆出单一张表

2.字段选取规则：整形->data,time->enum,char->varchar->text->blob

3.字段长度设定,够用就行杜绝浪费

4.尽量避免用NULL()，不利于索引查询，要用特殊的字节来标注，占有磁盘空间较大。



索引设计，myisam与innodb默认使用B-tree索引

1.建议建立单个索引，避免建立组合索引

2.索引值，或主键值应尽量是连续增长的整型值，不是随机值

3.要建立适当的索引对象，过多的索引会影响插入和更新速度，同时占用内存

4.对于左前缀不易区分的列，要分析数据内容找出最容易区别的部分放在前面，（比如，要url倒过来存储，后缀都不一样）

5.索引碎片维护，长期的数据更改过程中, 索引文件和数据文件,都将产生空洞,形成碎片

4.注意索引的左前缀原则，范围截断原则(or 条件，只要其中一个没有都不会用索引)

建立组合索引index(a,b,c) 为例：

语句

索引是否发挥作用

Where a=3

是,只使用了a列

Where a=3 and b=5

是,使用了a,b列

Where a=3 and b=5 and c=4

是,使用了abc

Where b=3  or  where c=4

否

Where a=3 and c=4

a列能发挥索引,c不能

Where a=3 and b>10 and c=7

a能利用,b能利用, c不能利用                       

同上,where a=3 and b like ‘xxxx%’ and c=7   

a能用,b能用,c不能用


建立多个单列索引index(a),index(b),index(c)

语句

索引是否发挥作用

Where a=3

是,只使用了a列

Where a=3 and b=5

是,使用了a,b列

Where a=3 and b=5 and c=4

是,使用了abc

Where b=3  or  where c=4

bc使用

Where a=3 and c=4

ac都使用

Where a=3 and b>10 and c=7

a能利用,b能利用, c不能利用                        

同上,where a=3 and b like ‘xxxx%’ and c=7   

a能用,b用,c不能用




sql语句优化

1.尽量使用索引覆盖，加快查询速度（查询只需要在索引文件上进行,不需要回行到磁盘再找数据）

2.在大数据范围查找时，尽量在索引覆盖上面查找到需要的索引id,然后在通过id去查到对应的数据

3.尽量减少查询次数，能用业务逻辑处理的功能，都用算法处理。

4.利用explain来利用分析



hash索引为什么不能作为数据存储原因：

1.hash函数计算后的结果,是随机的,如果是在磁盘上放置数据,比主键为id为例, 那么随着id的增长, id对应的行,在磁盘上随机放置

2.无法对范围查询进行优化

3.无法利用前缀索引

4.排序无法优化

5.必须回行.就是说 通过索引拿到数据位置,必须回到表中取数据
---------------------