[原创]关于数据库优化

数据库优化
1:硬件
　　如果费用可行，独立数据库{服务器}
　　cpu/ram/hd
2:软件
　　a:操作系统
　　window{蓝屏;不稳定;漏洞}/linux!/{unix}
　　Ubuntu/CentOS 6 7/
　　http://mirrors.sohu.com/
　　http://mirrors.163.com/
　　http://mirrors.aliyun.com/
　　b:mysql系统选择{版本/种类}
　　oracle 官方##
　　mariadb https://downloads.mariadb.org
　　mysql 5.5 5.6{*} 5.7
c:安装优化
　　c.1:yum
　　c.2:rpm
　　c.3:二进安装包--->
　　c.4:源代码编码(**)
　　效率最高 最稳定安装方式
d:基本安全操作
　　1:root密码加上

SET PASSWORD FOR 'root'@'localhost' = PASSWORD('admin');
select user,password,host from mysql.user;
use mysql;
UPDATE user SET Password = PASSWORD('admin') WHERE user = 'root';
FLUSH PRIVILEGES;

3:删除空用户

delete from mysql.user where user='';
FLUSH PRIVILEGES;

4:将test 库删除

drop database test;

=============================================
e:表设计优化
　　1:int(11) vs int(1)
　　占用4个字节 21亿~-21亿

　　2:char(25) vs varchar(25)
　　char的长度是不可变的
　　varchar的长度是可变的
　　例如:char(10) varchar(10)
　　如果数据库中存得时候是'casd' 那么对于
　　char来说 最后面就是6个空格 保证10位
　　而对于varchar来说 自动会将长度变为4

　　3:尽量使用占用空间少的类型
　　4:货币
　　5:ip
　　char(15) 15个字节
　　UNSIGNED int 4个字节
　　select inet_ntoa(3402603468);
　　select inet_aton('202.207.155.204'); 3402603468
　　6:日期
　　7:少用text varchar
　　8:最大数varchar(20000)
　　text 使用自动创建临时表
　　varchar(65535) 字母数字
　　varchar(23000) 包含汉字
　　9:不要将图片保存在数据库中

　　10:不在数据库做运算：cpu计算务必移至业务层
　　11:控制单表数据量：单表记录控制在500w
　　12:控制列数量：char字段数控制在20以内
　　13:拒绝3B：拒绝大sql，大事物，大批量
　　14:避免使用NULL字段
　　复合索引无效
　　普通索引多占空间
　　很难查询优化

name varchar(25) not null default '',
age int not null int 0,

　　15:优先使用enum或set
　　sex enum('F','M');//枚举
　　16:字符转化为数字
　　字数类型{效率高、查询快、占用内存小}
　　17:字符字段必须建前缀索引
　　18:innodb主键推荐使用自增列
　　19:不用select *
　　20:limit高效分页

　　21:单库300-400
　　22:单表20-50字段

性能分析工具
f:sql语句优化
g:分析判断慢sql语句优化

　　17:字符字段必须建前缀索引
　　索引:合理添加索引
　　a:查询快[更新慢]
　　b:索引添加列数不要超总列数20%
　　c:不要性别添加索引{唯一性太差}

　　19:字符字段要加前缀索引

pinyin varchar(100) not null default '' comment '小区拼音';
alter table t_info add index on(pinyin(8));

幸福家园 xinfujiayuan
宏达家园 hongdajiayuan
小狗家园 xiaogoujiayuan

　　20:不在索引列进行数学运算或函数返算
原因:
　　1:无法使用索引
　　2:导致全表扫描

error select * from user where id+1 =2;
ok select * from user where id = 2-1;
bad:select * from user
where to_days(current_date) -
to_days(date_col) <= 10;
good:
select * from user
where date_col >=
DATE_SUB('2011-10-22',Interval 10 DAY);

　　21:主键不应更新修改/禁止字符串做主键

　　22:线上OLTP系统{不要用外键容易死锁}
数据库
OLAP {在线分析系统} 1%
处理非常慢：最终结果
OLTP {在线事务系统} 99% oa news shop
处理非常快:实时结果
　　22:mysql 不需要大SQL
　　23:保持短小事务{一个事务SQL少}
　　24:不用select * 需要哪些字段
则查询哪些字段

　　25:同一个字段改写 or 为 in

bad: select id,name,age
from user
where id = 1 or id = 3;
good:select id,name,age
from user
where id in(1,3);

　　26:不同字段改写 or 为 union(合并)

bad:
select id,name,age
from user
where id = 1 or age = 15;
good:
select id,name,age from user where id = 1
union
select id,name,age from user where age = 15; 

　　27:避免负向查询和%前缀模糊查询
*避免负向

NOT != <> !< !> NOT EXITS NOT IN
NOT LIKE ...
BAD: SELECT * from post where name like '%北%'
GOOD:SELECT * from post where name like '北%'

:innodb主键推荐使用自增列
:limit高效分页

　　28:尽量少用不用 count(*)

　　29:分页

select * from user
where id >= 23423 limit 11;

select * from user
where id >=
(select id from user limit
10000,1) limit 10;

select * from user
inner join
(select id from user
limit 10000,10)
using(id)

　　30:用 union all 而非union
#若无需对结果进行去重
　　31:如果高并发环境减少两个表以上的
join
　　32:group by 去除排序
#无需排序 order by null
#特定排序 group by desc/asc

select id,name,sal
from post
group by name order by null
limit 1;

　　33:同数据类型的列值比较

bad select * from user
where id = '1';
good select * from user
where id = 1;

　　34:load data 加载大量数量
性能分析工具

mysql> show processlist \G;

#查询当前 mysql所有正在执行sql
#当前mysql服务器状态

mysql> show status;

#当前连接mysql统计结果

mysql>show session status like 'Com%';

#显示自数据库上次启动至今统计结果

mysql>show global status like 'Com%';

Com_select
Com_insert
Com_update
Com_delete

通过这个几个参数知现在数据库常用操作
#试图连接数据库次数

show status like 'Connections';

#服务器工作时间(秒)

show status like 'Uptime';

#慢查询次数

show status like 'Slow_queries';

#获取慢查询时间
#启动慢查询禁止
#什么时候:如果发现系统慢
#show processlist 执行很长时间sql

show variables like 'long_query_time';

#可以修改慢查询时间

a:my.ini/my.cnf
long_query_time=2;
log_show_queries= d:/123.log
b:set long_query_time=2;

35:杀死正在执行慢sql{临时}
show processlist;
查看sql状态与id
kill id;

查看执行计划
1:一条sql 提交 mysql
2:mysql优化器:分析并给出一个执行计划
3:分析执行计划可以查看sql执行内容

explain select .... \G;

示例:
select id,sn,name from test.t_student;

explain select id,sn,name from
test.t_student \G;

id: 1
select_type: SIMPLE
table: t_student
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 4
Extra:
#id sql执行顺序标识 1

explain select id,sn,name from
test.t_student
where sn = (select sn from test.t_student
where id = 1) \G;

explain
select id,sn from test.t_student
where id = 1
union
select id,sn from test.t_student
where id = 2 \G;

select_type:
SIMPLE: 简单查询(没有union或子查询)
PRIMARY: 最外层SQL
UNION: 第二个或后面select
SUBQUERY:子查询
UNION RESULT:连接结果

table:
查询数据关于哪张表

type:***
显示使用哪种类别，有无使用索引
最好最差
system/const/eq_reg/ref/range/indexhe/ALL
:system:最好仅有一行满足条件

explain
select * from
(select * from test.t_student where id = 1) a \G;
:const
:eq_ref

explain
select * from test.t_student where id <= 3
and id >= 1 \G;

ALL:全表扫描

possible_keys:当前查询可以使用哪个索引
primary 唯一

key: 显示mysql实际决定使用索引
key_len: 显示mysql决定使用索引长度

explain
select * from test.t_student where sn = '001' \G;

rows
显示mysql执行查询时必须检查行数;

Extra:
不好
Using filesort 使用文件排序
Using temploary 创建临时表

作者:little飞 出处:http://www.cnblogs.com/little-fly/ 欢迎转载 也请保留这段声明 谢谢!