MySQL的优化

在应用的开发过程中,由于初期数据量小,开发人员写SQL语句更加重视功能上的实现,但是当应用系统正式上线后,随着数据量的急剧增长,很多SQL语句开始逐渐显露出性能问题,对生产的影响也越来越大,此时这些有问题的SQL语句就成为整个系统性能的瓶颈,因此我们必须要对它们进行优化.

MySQL的优化方式有很多,大致我们可以从以下几点来优化MySQL:

• 从设计上优化
• 从查询上优化
• 从索引上优化
• 从存储上优化

一.Explain分析执行计划

通过以上步骤查询到效率低的SQL语句中,可以通过EXPLAIN命令获取MySQL如何执行SELECT语句的信息,包括在SELECT语句执行过程中表如何连接和连接的顺序.

-- 查看查询user表中uid为1的全部信息这条语句的执行效率
explain select * from user where uid = 1;

字段含义

它可以帮助开发者理解MySQL查询优化器如何处理SQL查询。通过分析EXPLAIN的输出，可以识别查询的性能瓶颈并进行相应的优化。

二.show profile分析SQL

Mysql从5.0.37版本开始增加了对 show profiles 和 show profile 语句的支持。show profiles 能够在做SQL优化时帮助我们了解时间都耗费到哪里去了

通过 have_profiling 参数，能够看到当前MySQL是否支持profile：

select @@have_profiling; 
set profiling=1; -- 开启profiling 开关；
show variables like '%profiling%';
set profiling_history_size = 100; 

通过profile，我们能够更清楚地了解SQL执行的过程。首先，我们可以执行一系列的操作

show databases;
use mydb13_optimize;
show tables;
select * from user where uid < 2;
select count(*) from user;

-- 执行完上述命令之后，再执行show profiles 指令， 来查看SQL语句执行的耗时：
show profiles;

通过show profile for query query_id 语句可以查看到该SQL执行过程中每个线程的状态和消耗的时间：

show profile for query 8;

在获取到最消耗时间的线程状态后，MySQL支持进一步选择all、cpu、block io 、context switch、page faults等明细类型类查看MySQL在使用什么资源上耗费了过高的时间。例如，选择查看CPU的耗费时间 ：

show profile cpu for query 133; 

字段含义:

三.使用索引优化

索引是数据库优化最常用也是最重要的手段之一, 通过索引通常可以帮助用户解决大多数的MySQL的性能优化问题。

-- 数据准备时创建主键索引和唯一索引
create table `tb_user` (
  `id` int(11) not null auto_increment,
  `username` varchar(45) not null,
  `password` varchar(96) not null,
  `name` varchar(45) not null,
  `birthday` datetime default null,
  `sex` char(1) default null,
  `email` varchar(45) default null,
  `phone` varchar(45) default null,
  `qq` varchar(32) default null,
  `status` varchar(32) not null comment '用户状态',
  `create_time` datetime not null,
  `update_time` datetime default null,
  primary key (`id`),
  unique key `unique_user_username` (`username`)
);
-- 创建组合索引 
create index idx_seller_name_sta_addr on tb_seller(name,status,address);

索引的优点

• 大大加快数据的查询速度
• 使用分组和排序进行数据查询时，可以显著减少查询时分组和排序的时间
• 创建唯一索引，能够保证数据库表中每一行数据的唯一性
• 在实现数据的参考完整性方面，可以加速表和表之间的连接

索引的缺点

• 创建索引和维护索引需要消耗时间，并且随着数据量的增加，时间也会增加
• 索引需要占据磁盘空间
• 对数据表中的数据进行增加，修改，删除时，索引也要动态的维护，降低了维护的速度

四.SQL优化

• 主键顺序插入
• 优化insert语句
• 优化子查询

主键顺序插入

当使用load 命令导入数据的时候，适当的设置可以提高导入的效率。对于 InnoDB 类型的表，可以利用主键顺序插入可以提高导入的效率
因为InnoDB类型的表是按照主键的顺序保存的，所以将导入的数据按照主键的顺序排列，可以有效的提高导入数据的效率。如果InnoDB表没有主键，那么系统会自动默认创建一个内部列作为主键，所以如果可以给表创建一个主键，将可以利用这点，来提高导入数据的效率。

优化insert语句

当进行数据的insert操作的时候，可以考虑采用以下几种优化方案:

-- 如果需要同时对一张表插入很多行数据时，应该尽量使用多个值表的insert语句，这种方式将大大的缩减客户端与数据库之间的连接、关闭等消耗。使得效率比分开执行的单个insert语句快。

-- 原始方式为：
insert into tb_test values(1,'Tom');
insert into tb_test values(2,'Cat');
insert into tb_test values(3,'Jerry');
 
 
-- 优化后的方案为 ： 
insert into tb_test values(1,'Tom'),(2,'Cat')，(3,'Jerry');

优化子查询

使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询是可以被更高效的连接（JOIN）替代。

explain select * from user where uid in (select uid from user_role);-- 优化前
explain select * from user u,user_role ur where uid in (select uid from user_role);-- 优化后

type类型从快到慢:system > const > eq_ref > range > index > ALL
连接(Join)查询之所以更有效率一些 ，是因为MySQL不需要在内存中创建临时表来完成这个逻辑上需要两个步骤的查询工作