mysql优化收藏

1、查询优化：

1.使用慢查询log获取sql语言（在日常工作中使用频率较高）

1.1.开启慢查询日志：在my.ini或my.cnf的mysqld一行中加入log-slow-queries=/data/mysqldata/data/slow-query.log和long_query_time=3，3表示3秒，同时刻意提出索引的查询：log-queries-not-using-index

1.2.使用命令mysqldumpslow [paramters] /data/mysqldata/data/slow-query.log,paramters为参数，有以下参数：

-s，是order的顺序

 al   平均锁定时间

ar   平均返回记录时间

 at   平均查询时间（默认）

c    计数

    锁定时间

r    返回记录

t    查询时间
 -t，是top n的意思，即为返回前面多少条的数据

 -g，后边可以写一个正则匹配模式，大小写不敏感的

2.使用explain对sql语句进行解析

2、使用索引

（索引独立于基础数据的空间存储的数据，更新数据会带来大量的i/o和调整索引的计算量，从而导致资源的耗损）

 2.1关于索引的使用有以下四点说明：

较频繁的作为查询天剑的字段应该创建索引；

唯一性较差的字段不适合单独创建索引；

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

不会出现在where字句的字段不该创建索引。

2.2使用索引

使用联合索引

使用“OR” 关键字查询。

3、存储优化

3.1常用的mysql存储引擎为mylsam和innodb

mylsam：非事务性，不安全，锁的粒度为表锁，适合大量的查询使用；

innodb：事务性，安全，锁的粒度为行锁，适合insert和update。

3.2mylsam的优化：

insert和update数据之前禁用索引，完成后再开启索引；

insert和update数据之前禁用唯一性检查；

使用批量insert代替单条insert。

3.3innodb的优化：

insert前禁用唯一性检查；

insert前禁用外键检查：SET foreign_key_check=0/1(关/开)

insert前禁用自动提交：SET autocommit=0/1(关/开)

4、数据库结构优化

4.1表拆分

4.1.1垂直拆分

将多个列存放在不同的表中，常用的字段放一个表，其余的放在其它表中

4.1.2水平拆分

使用某种规则，如id%4=0,1,2,3拆分成四个表，使用MyCat进行管理

4.2分区

mysql目前有四种分区：range分区，list与定义列表分区，hash分区和key键分区

5、读写分离

根据读与写的比例，划分多个用于读库和写库，读库数据要根据写库进行同步，确保数据的一致性。

6、硬件优化

内存优化

磁盘i/o优化：尽量使用ssd等快速硬盘

CPU优化