本文共 5367 字，大约阅读时间需要 17 分钟。
mysql优化
打开浏览器 --> 网络 --> 发送到服务器 --> 操作数据库 --> 结果响应
数据库的基本结构
1.客户端层 处理连接,授权认证2.mysql的核心服务层 查询解析,分析优化,缓存,内置函数(时间,数学...)3.存储引擎 存数据
##数据库的设计和数据类型的优化
数据库设计的陷阱:1.避免太多列 2.避免太多的关联,粗略的经验法则,单个查询最好在12表以内关联3.防止过度使用枚举4.范式和反范式(了解..)范式优点和缺点:更新快,没有或者很少有重复的数据通常都有关联多个表才能查询想要的数据反范式优点和缺点:查询减少关联存在冗余的数据数据类型的优化:1.更小的通常更好2.简单就好,例子:操作数字比操作字符代价更低 3.尽量避免使用null整型类型:18tinyint 8位smallint 16位mediumint 24位int 32位bigint 64位使用原则:根据需要实数类型:18.88decimal: 定点数据类型,存储精确的小数decimal(m,d) m<65, d<30float:单精度浮点型float(m,d) 只能保证6位有效数字是精确,使用4个字节double:双精度浮点类型,使用8个字节double(m,d)使用原则:精度要求不高: float,double,比decimal少占用空间精度准确: decimal,需要额外空间和开销char(10) 定长型varchar(10) 可变长度,通常情况下比char节省空间,varchar需要额外的字节去记录字符串的长度使用原则:不确定长度:使用varchar确定长度:char blob(二进制)和text(文本)存储大的数据使用建议:大量数据,存储文件的路径枚举类型:使用枚举类型代替字符串,mysql存储枚举的时候,内部把每个值都保存为整数例子:创建表CREATE TABLE enum_test(e ENUM('fish','apple','dog'));插入数据:INSERT INTO enum_test(e) VALUES('fish'),('dog'),('apple');查询数据:SELECT e+0 FROM enum_test;注意:使用整数当做枚举,容易混乱日期和时间类型:datetime :使用8个字节 1001-9999timestamp: 如果没有指定时间,会使用系统时间,使用4个字节1970-2038bit: 位,尽量避免使用 ##创建高性能的索引
索引的优点:1.减少服务器需要扫描的数据量2.避免服务器的排序和临时表3.将随机I/O变成顺序I/O索引添加到哪些列上面比较好?1.比较频繁作为查询条件字段应该创建索引select * from tablename where english...2.唯一性太差的字段不适合创建索引select * from student where sex=男/女3.更新比较频繁的字段不适合创建索引4.考虑在where以及order by涉及的列上创建索引例子:创建表:CREATE TABLE test( id INT NOT NULL, username VARCHAR(16) NOT NULL)插入100000数据:DELIMITER #CREATE PROCEDURE pro1()BEGIN DECLARE i INT DEFAULT 0; WHILE i<100000 DO INSERT INTO test VALUES(i, CONCAT('aa', i)); SET i=i+1; END WHILE;END #调用:CALL pro1();查询一条记录:select * from test where id=54321;通过explain查看执行信息EXPLAIN select * from test where id=54321;explain关键参数分析:type:连接访问类型 一般来说,至少range级别,最好能够达到ref级别rows:估计值,mysql认为获取一个查询结果需要扫描的行数添加索引: CREATE INDEX index_id ON test(id);测试:EXPLAIN select * from test where id=54321;普通索引:加速查询1.创建表时候创建索引create table test2( id int not null, username varchar(200) not null, index index_username (username(15)))2.使用create创建索引CREATE INDEX index_username ON test2(username(10));3.使用alter创建索引ALTER TABLE test2 ADD INDEX index_username(username(10));唯一索引:加速查询,唯一约束(可以含有null)1.创建表时候CREATE TABLE test3( id INT NOT NULL, username VARCHAR(20) NOT NULL, UNIQUE INDEX index_username (username(10)))2.使用createCREATE UNIQUE INDEX index_username ON test3(username(10));3.使用alterALTER TABLE test3 ADD UNIQUE INDEX index_username(username(10));复合(组合/多列)索引:1.创建表create table test4( name varchar(20) not null, age int not null, index index_name_age(name(10), age))2.使用createCREATE INDEX index_name_age ON test4(NAME(10), age);3.使用alterALTER TABLE test4 ADD INDEX index_name_age(NAME(10), age);主键索引:加速查询,唯一约束(不可以含有null)create table test5( id int not null PRIMARY key AUTO_INCREMENT, username varchar(20) not null)注意:创建索引的时候长度要合适索引缺点:1.降低表的更新速度2.创建索引占用磁盘空间. ##查询性能优化
查询优化分析步骤:1.应用查询是否超过需要数据select name, age, height form userinfo2.mysql服务器是否分析超出需要的数据SELECT * FROM test WHERE id=54321;正确使用索引:1.like语句的操作一般不使用%或_开头select * from tablename where name like "cn%"2.组合索引例如:索引 index index_name(a, b, c),只支持a或a,b或a,b,c 最左前缀3.尽量避免在where子句进行null判断例如: select * from t where num is null; select * from t where num = 0;4.尽量避免在where子句使用!=,or5.in和not in也要谨慎使用例子: select id from t where num in(1,2,3);修改:对于连续的值,使用between select id from t where num between 1 and 3;6.如果在where子句中使用参数,也会导致全表扫描mysql在 运行时候才会解析变量,mysql语句优化的时间在编译的时候7.尽量避免在where子句的=左面进行运算例如:select * from t where num/2 = 100;修改:select * from t where num = 100*2;例如:select * from t where substring(name, 1, 3) = 'abc'; select * from t where name like 'abc%';其他的优化点:1.尽量使用数字类型的字段2.在join表要使用相当类型的字段,并将其建立索引例如: select * from users left join company on users.state = company.state3.尽量避免使用 order by rand(); 4.尽量避免使用 select * 5.一定要为每张表设置主键,最好是int类型,最好设置auto_increment6.优化count查询select count(*)7.不要添加重复或者冗余的索引,删除长期不用索引例如:创建组合索引(A,B),不要再单独为(A)创建索引8.尽量避免大的事务操作,降低并发性能9.优化or条件创建表CREATE TABLE test6( id INT NOT NULL, NAME VARCHAR(20) NOT NULL, INDEX index_id(id), INDEX index_name(NAME(20)))插入数据INSERT INTO test6 VALUES(1, 'zhangsan');INSERT INTO test6 VALUES(2, 'lisi');使用orEXPLAIN SELECT * FROM test6 WHERE id=1 OR NAME='lisi';使用union allEXPLAIN SELECT * FROM test6 WHERE id=1 UNION ALL SELECT * FROM test6 WHERE NAME='lisi'10.优化分页查询(limit)创建表create table test7( id int not null, username varchar(20) not null, index index_id(id))插入数据DELIMITER #create PROCEDURE pro2()begin DECLARE i int default 0; while i<10000 do insert into test7 values(i, concat('aa', i)); set i=i+1; end while;end #call pro2();分页查询数据explain select * from test7 order by id limit 1000, 20;优化:EXPLAIN SELECT * FROM test7 INNER JOIN (SELECT id FROM test7 ORDER BY id LIMIT 1000, 20) AS o USING(id);EXPLAIN SELECT * FROM test7 t INNER JOIN (SELECT id FROM test7 ORDER BY id LIMIT 1000, 20) AS o ON t.id = o.id 不同存储引擎需要配置参数不一样,选择InnoDB注意:1.值不是越大越好2.不要随意修改,大部分情况下只有很少的参数真正重要两个重要参数:1.InnoDB缓冲池innodb_buffer_pool_size:一般情况可以设置为操作系统的内存70%-80%2.InnoDB事务日志减少事务的提交的开销,在日志记录事务日志是环形写入,写到尾部,就会跳到开头重新写入innodb_log_file_size:日志文件的大小,设置这个值缓冲池20%-100%innodb_log_buffer_size:日志文件的缓冲区大小,默认1M,推荐1M-8Minnodb_flush_log_at_trx_commit:控制日志缓冲的频繁程度
cpu : 多和快之间选择,一般选择快(其他条件相同)内存 : 大内存,位宽高,尽量不要出现SWAP(内存不足,用硬盘暂时存放内存中数据)硬盘 : 机械(高转速),固态(ssd, PCIe)操作系统: 尽量64位,可以利用更多的内存
##应用层的优化
1.不要请求不需要的数据2.应用处理本该由数据库处理的事情,或者反过来例子:获取所有记录,count(*), 使用java代码,select * from table , list.size()例子:在数据库重执行复杂的字符串操作3.创建了没有必要mysql连接,使用连接池即可4.尽量减少长连接使用5.不使用的连接及时断开6.缓存数据,用户昵称,id
转载地址：http://pzusi.baihongyu.com/