mysql 优化策略_Mysql优化策略

以下是以Windows系统下实践为例:

一、造假数据，模拟场景

1.制造数据的存储过程：

DELIMITER //

CREATE PROCEDURE p1(num int)

BEGIN

DECLARE v INT;

SET v = 1;

WHILE v <= num DO

INSERT INTO user(username,password,gender,age,createtime) VALUES (concat("user",v),md5(v),round(rand()),round(rand()*100),now());

SET v = v + 1;

END WHILE;

END; //

2.调用存储过程

选择数据库test1，创建表user，建表语句如下：

DROP TABLE IF EXISTS `user`;

CREATE TABLE `user` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`username` varchar(20) NOT NULL,

`age` tinyint(3) NOT NULL,

`password` char(32) NOT NULL,

`gender` enum('0','1') DEFAULT NULL,

`createtime` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,

PRIMARY KEY (`id`)

) ENGINE=MyISAM DEFAULT CHARSET=utf8;

调用存储过程：call p1(1000000);

3.分析user表结构

二、慢查询日志的开启

1.命令行下查看mysql慢查询的配置信息：

show variables like '%slow%';

2.若slow_query_log为OFF，则需要我们开启慢查询日志

开启方法：修改my.ini文件，在文件最后加上以下内容：

slow_query_log=on    (开启慢查询日志)

long_query_time=0.2  (设置慢查询时间为0.2秒)

3.开启之后可通过命令查看慢查询配置

show variables like '%slow%';

show variables like '%long%';

三、分析sql语句，验证索引带来的查询速度的提升

1.执行一条慢查询sql语句

select * from user where username = ‘user999990’;

记录下该语句的执行时间，若超过了我们设置的慢查询时间，则会记录在日志里。

2.分析慢查询的sql语句

explain select * from user where username = ‘user999990’;

3.为username添加普通索引

alter table user add index(username);

查看：show index from user;

4.再次执行刚才的查询语句

select * from user where username = ‘user999990’;

记录本次查询所用的时间，验证是否加上索引加快了查询速度。

四、索引并非建的越多越好

索引的代价：

磁盘占用

对dml(update delete insert)语句的效率影响

验证加上索引之后的更新语句执行时间：

update user set username=’a’ where id=1000010;

记录执行时间

将索引删除再次执行该sql语句，记录执行时间，比较有无索引时更新语句哪个更快。

五．sql语句使用到索引的情况

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

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

3.更新非常频繁的字段不适合创建索引

4.不会出现在WHERE子句中字段不该创建索

5.对于使用like的查询，查询如果是’%aaa’不会使用到索引’aaa%’会使用到索引。

6.如果列类型是字符串，那一定要在条件中将数据使用引号引用起来。否则不使用索引。(添加时,字符串必须’’)

六、sql语句优化

1.避免使用select *

2.连接()代替子查询

3.如果想要在含有or的查询语句中利用索引，则or之间的每个条件列都必须用到索引，如果没有索引，则应该考虑增加索引

七、数据表的优化

MyISAM:如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性要求不是很高。其优势是访问的速度快。(尤其适合论坛的帖子表)

InnoDB：提供了具有提交、回滚和崩溃恢复能力的事务安全。但是对比MyISAM，写的处理效率差一些并且会占用更多的磁盘空间。

Memory [一些访问频繁，变化频繁，又没有必要入库的数据 ：比如用户在线状态]

选择合适的数据类型

分库分表

对标进行水平垂直划分:

数据库的读写分离