mysq数据库优化

本文通过8个方法优化Mysql数据库：

1. 创建索引、

2. 复合索引、

3. 索引不会包含有NULL值的列、

4. 使用短索引、

5. 排序的索引问题、

6. like语句操作、

7. 不要在列上进行运算、

8. 不使用NOT IN和<>操作

1、创建索引
对于查询占主要的应用来说，索引显得尤为重要。很多时候性能问题很简单的就是因为我们忘了添加索引而造成的，或者说没有添加更为有效的索引导致。如果不加索引的话，那么查找任何哪怕只是一条特定的数据都会进行一次全表扫描，如果一张表的数据量很大而符合条件的结果又很少，那么不加索引会引起致命的性能下降。但是也不是什么情况都非得建索引不可，比如性别可能就只有两个值，建索引不仅没什么优势，还会影响到更新速度，这被称为过度索引。
2、复合索引
比如有一条语句是这样的：select * from users where area='beijing' and age=22;
如 果我们是在area和age上分别创建单个索引的话，由于mysql查询每次只能使用一个索引，所以虽然这样已经相对不做索引时全表扫描提高了很多效率， 但是如果在area、age两列上创建复合索引的话将带来更高的效率。如果我们创建了(area, age, salary)的复合索引，那么其实相当于创建了(area,age,salary)、(area,age)、(area)三个索引，这被称为最佳左前缀 特性。因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边，依次递减。
3、索引不会包含有NULL值的列
只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。
4、使用短索引
对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的 列，如果在前10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
5、排序的索引问题wodesjiekoooqwe eoef fkdks op
mysql查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。
6、like语句操作
一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。
7、不要在列上进行运算
select * from users where YEAR(adddate)<2007;
将在每个行上进行运算，这将导致索引失效而进行全表扫描，因此我们可以改成
select * from users where adddate<‘2007-01-01';
8、不使用NOT IN和<>操作
NOT IN和<>操作都不会使用索引将进行全表扫描。NOT IN可以NOT EXISTS代替，id<>3则可使用id>3 or id<3来代替。

  数据库优化的目的

避免出现页面访问错误

 ·由于数据库连接timeout产生页面5xx的错误。5xx:服务器内部的错误  

         ·由于慢查询造成页面无法加载

         ·由于阻塞造成数据无法提交

        增加数据库的稳定性

         ·很多数据库问题都是由于低效的查询引起的

        优化用户体验

         ·流畅页面的访问速度

         ·良好的网站功能体验

  

 

一、SQL以及索引优化

在查询的时候，如果没有指定索引，那么必须要对每一行都进行扫描，然后才能找到每行中被查询的字段，所以，可以说，在没有索引的情况下，要扫描整个表才可以查询到需要的数据；

如何发现有问题的SQL？

  使用MySQL慢查日志对有效率问题的SQL进行监控

   ·1、show variables like‘slow_query_log’

   ·2、set global slow_query_log_file=’/home/mysql/sql_log/mysql-slow.log’

   ·3、set global log_queries_not_using_indexes=on;

   ·4、set global long_query_time=1;

  2 ： 慢查询日志的存储位置在哪里

  3 ： 是否需要把没有索引的SQL记录到慢查询之中

  4 ： 超过多少秒之后的查询，记录到慢查询之中

 

慢查询日志的存储格式

 

Time:查询执行的时间

User@Host ：执行SQL的主机信息

第三行: SQL的执行信息   query_time  查询所用时间   lock_time锁定的时间

Rows_sent: 所发送的行数  rows_examined：所扫描的行数

SET timestamp :SQL执行的时间

最后一行 SQL的具体内容

 

考虑到在真实环境下，一天所产生的慢查询日志会达几十个G， 我们不可能一条条的分析，所以要借助一些工具，生成报表 来帮我们进行分析

1. Mysqldumpslow  mysql官方的慢查询分析工具

 

查询出 慢查询日志中的前三条

 

2. pt-query-digest 工具Overall:总的SQL数量和不同的SQL数量  Time range：日志时间范围

 

第二部分 ：主要是展示出 哪些表 执行语句 所用到的时间是最多 调用次数 响应时间

 

第三部分：

 

 

如何通过慢查询日志发现有问题的SQL？

   1.查询次数多且每次查询占用时间长的SQL

   通常为pt-query-digest分析的前几个查询

   2.IO大的SQL

   注意pt-query-digest分析中的Rows examine项

   3.未命中索引的SQL

   注意pt-query-digest 分析中Rows examine 和Rows Send的对比

 

   如何分析SQL查询

   使用explain查询SQL的执行计划

 

explain返回各列的含义

table:显示这一行的数据是关于哪张表的

type:这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_reg、ref、range、index和ALL

possible_keys:显示可能应用在这张表中的索引。如果为空，没有可能的索引。

key:实际使用的索引。如果为null，则没有使用索引。

key_len:使用的索引的长度。在不损失精确性的情况下，长度越短越好

ref:显示索引的哪一列被使用了，如果可能的话，是一个常数

rows:Mysql认为必须检查的用来返回请求数据的行数。

 

 

Count()和Max()方法的优化方法

在Max()要查询的数据上 添加索引

在Count()查询中， 查询*  和 查询某一列 是不一样的   *的话 查询的是所有行的信息，   查询某一列的话 如果该类的数据有null的 ， 则count不进行计数

子查询的优化

通常情况下，需要把子查询优化为join查询，但是在优化时要注意关联建是否有一对多的关系，要注意重复数据。  可以使用distinct关键字来去重

group by优化查询

最好选用同一张表的列，来进行group by的操作

explain SELECT actor.first_name,actor.last_name,COUNT(*) FROM sakila.film_actor  INNER JOIN sakila.actor USING(actor_id) GROUP BY film_actor.actor_id;

 

优化后的查询

explain SELECT actor.first_name,actor.last_name,c.cnt FROM sakila.actor INNER JOIN (SELECT actor_id,COUNT(*) AS cnt FROM sakila.film_actor GROUP BY actor_id) AS c USING(actor_id);

 

优化limit查询

limit常用于分页处理，时常会伴随order by从句的使用，因此大多时候会使用Filesorts这样会造成大量的IO问题。

SELECT film_id,description FROM sakila.film ORDER BY title LIMIT 50,5

   优化步骤 1. 使用有索引的列或者主键进行Order By 操作

SELECT film_id,description FROM sakila.film ORDER BY film_id LIMIT 50,5

   优化步骤 2.记录上次返回的主键，在下次查询时使用主键过滤

SELECT film_id,description FROM sakila.film WHERE film_id>55 AND film_id<=60 ORDER BY film_id LIMIT 50,5

* 避免了数据量大时扫描过多的记录   但是要求 主键必须是顺序增加的  如果Where条件中 有空缺的   则查询不出5条记录

 

 

如何选择合适的列建立索引？

1.在where从句，group by从句，order by从句，on从句中出现的列

2.索引字段越小越好

3.离散度大的列放到联合索引的前面

     SELECT * FROM payment WHERE staff_id = 2 AND customer_id=584;

是index(staff_id,customer_id)好?还是index(customer_id,staff_id)好？

由于customer_id的离散度更大，所以应该使用index(customer_id,staff_id);

索引的维护及优化----重复及冗余索引

     重复索引是指相同的列以 相同的顺序建立的同类型的索引，如下表primary key 和 ID列上的索引就是重复索引

Create table test(

Id int not null primary key

Name varchar(10) not null,

Title varchar(50) not null,

Unique(id)

)engine=innodb;

     冗余索引是指多个索引的前缀列相同，或是在联合表中包含了主键的索引，下面的列子中key（name,id）就是一个冗余索引。

Create table test(

Id int not null primary key

Name varchar(10) not null,

Title varchar(50) not null,

Key(name,id)

)engine = innodb;

 

 

查找重复以及冗余索引

使用pt-duplicate-key-checker工具检查重复或者冗余的索引

 

删除不用的索引 目前Mysql中还没有记录索引的使用情况，但是在PerconMYsql和MariaDB中可以通过INDEX_STATISTICS表来查看哪些索引未使用，但在Mysql中目前只能通过慢查日志配合pt-index-usage工具来进行索引使用情况的分析

 

 

 

二、数据库结构的优化

1.选择合适的数据类型

 

 

 

 

 

2.表的范式化和反范式化

表的范式化

 

 

 

 

3.反范式化

 

 

 

 

 

数据库的分表.

 

 

 

前台数据的查询 使用   拆分的表    后台的操作使用  汇总的表

 

 

 

 

三、系统的优化

一.系统配置的优化

1.  网络方面配置， 增加tcp支持的队列数，减少断开连接时，资源回收

2. 修改打开文件数的限制

二．MySQL配置文件的优化

Innodb_buffer_pool_size 用于配置innode的缓冲池如果数据库中只有innodb表，则推荐配置量为总内存的75%

Innode_flush_log_at_trx_commit：对innodb的IO影响很大，默认值是1， 取值有0,1,2

一般情况下是2，如果数据安全性比较高，则使用1

Innodb_file_per_table  控制innodb每一个表使用独立的表空间，默认是off，也就是所有的表共享一块表空间。

 

 

 

 

 

MySQL配置文件的优化

Innodb_buffer_pool_size 用于配置innode的缓冲池如果数据库中只有innodb表，则推荐配置量为总内存的75%

Innode_flush_log_at_trx_commit：对innodb的IO影响很大，默认值是1， 取值有0,1,2

一般情况下是2，如果数据安全性比较高，则使用1

Innodb_file_per_table  控制innodb每一个表使用独立的表空间，默认是off，也就是所有的表共享一块表空间。

 

 

 

 

 

 

 

 

 

 

第三方配置工具

 

 

 

四、服务器硬件的优化

 

磁盘IO 的优化

 

RAID0:一个条带中某一个磁盘进行了损害，那我们所有的数据都将丢失。对数据的安全性不好。

RAID1：IO不如RAID0  安全性却高   可以替换.

除了 RAID外   还有一些 新兴的 存储设备

 

SAN和NAS  不是  SNA和NAT