MySQL优化

1.什么是优化

合理安排资源、调整系统参数是Mysql运行更快、更节省资源。

优化是多方面的，包括、查询、、表设计、服务器等。

原则：减少系统瓶颈，减少资源占用、增加系统的反应速度。

2.查询优化

慢查询日志

可以再mysql数据库的配置文件中 【mysqld】下加配置

log-slow-queries= 记录日志存放的位置

long_query_time=1 表示 查询语句超过1秒的被记录 这个时间是根据数据库数据的数量

来设定的

EXplain分析查询

在MySQL中可以使用EXPLAIN查看sql执行计划 用法：EXPLAIN select * FROM

PRODUTS

1.id

只是个序号

2.select_type

表示select语句的类型

例如：

1.simple

表示简单查询，其中不包含连接查询和子查询

2.primary

表示主查询，或者是最外面的查询语句

3.union

表示连接查询的第二个或后面的查询语句

3.table

表示查询的表

4.type

表示表的连接类型

以下的连接类型的顺序是从最佳类型到最差类型：

（1）SYSTEM

CONST的特例，当表上只有一条元组匹配

（2）CONST

WHERE条件筛选后表上至多有一条元组匹配时，比如WHERE ID = 2 （ID是主键，值为2的要么有一条要么没有）

（3）EQ_REF

参与连接运算的表是内表（在代码实现的算法中，两表连接时作为循环中的内循环遍历的对象，这样的表称为内表）。

基于索引（连接字段上存在唯一索引或者主键索引，且操作符必须是“=”谓词，索引值不能为NULL）做扫描，使得对外表的一条元组，内表只有唯一一条元组与之对应。

（4）REF

可以用于单表扫描或者连接。参与连接运算的表，是内表。

基于索引（连接字段上的索引是非唯一索引，操作符必须是“=”谓词，连接字段值不可为NULL）做扫描，使得对外表的一条元组，内表可有若干条元组与之对应。

（5）REF_OR_NULL

类似REF，只是搜索条件包括：连接字段的值可以为NULL的情况，比如 where col = 2 or col is null

（6）RANGE

范围扫描，基于索引做范围扫描，为诸如BETWEEN，IN，>=，LIKE类操作提供支持

（7）INDEX_SCAN

索引做扫描，是基于索引在索引的叶子节点上找满足条件的数据（不需要访问数据文件）

（8）ALL

全表扫描或者范围扫描：不使用索引，顺序扫描，直接读取表上的数据（访问数据文件）

（9）UNIQUE_SUBQUERY

在子查询中，基于唯一索引进行扫描，类似于EQ_REF

（10）INDEX_SUBQUERY

在子查询中，基于除唯一索引之外的索引进行扫描

（11）INDEX_MERGE

多重范围扫描。两表连接的每个表的连接字段上均有索引存在且索引有序，结果合并在一起。适用于作集合的并、交操作。

（12）FT

FULL TEXT，全文检索

5.possible_keys

指出Mysql能使用那个索引在该表中找到行。

如果该列为NULL 说明没有使用索引，可以对改了创建索引提高性能

6.key

显示MySQL实际解决使用的键(索引)，如果没有选择索引，键是NULL

7.key_len

显示MySQL决定使用的键的长度.如果键为NULL 那么长度也为NULL

8.ref

显示使用哪个列或常数与key一起从表中选择行

9.rows

显示MySQL 认为他执行查询时必须检查的行数

10.Extra

该列包含MySQL解决查询的详细信息

Distinct：MySql 发现第一个匹配行后，停止为当前的行组合搜索更多的行。

Not exists:mysql能够对查询进行 left join 优化，发现一个匹配left join 标准的行后，不再为 前面的行组合在该表内查询更多的行。

range checked for each record （index map:# ）:mysql 没有发现更好的可以使用的索引，但发现如果来自前面的表的列值已知，可能部分索引可以使用。

Using filesort： 表示 MySQL 会对结果使用一个外部索引排序,而不是从表里按索引次序读到相关内容。可能在内存或者磁盘上进行排序。MySQL 中无法利用索引完成的排序操作称为“文件排序”

Using temporary：表示 MySQL 在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。

3.索引的使用

1.B-Tree索引

一般来说，mysql中的B-Tree索引的物理文件大多都是以二叉树的结构来储存的，也就是所有实际数据都存放于树的叶子节点。而且任何一个叶子节点的最短路径的长度都是完全相同的。

2.R-Tree 索引

RTREE在mysql中很少使用。支持该类型的存储引擎只有MyISAM.BDb.InnoDb.NDb.Archive几种。相对于BTREE.RTEE的优势在于范围查找。

3.Hash索引

目前主要用于Memory存储引擎中

Hash 索引仅仅能满足"=","IN"和"<=>"查询，不能使用范围查询。

由于 Hash 索引比较的是进行 Hash 运算之后的 Hash 值，所以它只能用于等值的过滤，不能用于基于范围的过 滤，因为经过相应的 Hash 算法处理之后的 Hash 值的大小关系，并不能保证和Hash运算前完全一样。

Hash 索引无法被用来避免数据的排序操作。

由于 Hash 索引中存放的是经过 Hash 计算之后的 Hash 值，而且Hash值的大小关系并不一定和 Hash 运算前的键值完全一样，所以数据库无法利用索引的数据来避免任何排序运算；

Hash 索引不能利用部分索引键查询。

对于组合索引，Hash 索引在计算 Hash 值的时候是组合索引键合并后再一起计算 Hash 值，而不是单独计算 Hash 值，所以通过组合索引的前面一个或几个索引键进行查询的时候，Hash 索引也无法被利用。

Hash 索引在任何时候都不能避免表扫描。

前面已经知道，Hash 索引是将索引键通过 Hash 运算之后，将 Hash运算结果的 Hash 值和所对应的行指针信息存放于一个 Hash 表中，由于不同索引键存在相同 Hash 值，所以即使取满足某个 Hash 键值的数据的记录条数，也无法从 Hash 索引中直接完成查询，还是要通过访问表中的实际数据进行相应的比较，并得到相应的结果。

Hash 索引遇到大量Hash值相等的情况后性能并不一定就会比B-Tree索引高。

对于选择性比较低的索引键，如果创建 Hash 索引，那么将会存在大量记录指针信息存于同一个 Hash 值相关联。这样要定位某一条记录时就会非常麻烦，会浪费多次表数据的访问，而造成整体性能低下。

FULLTEXT索引

即为全文索引，目前只有MyISAM引擎支持。其可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，不过目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引 。

全文索引并不是和MyISAM一起诞生的，它的出现是为了解决WHERE name LIKE “%word%"这类针对文本的模糊查询效率较低的问题。在没有全文索引之前，这样一个查询语句是要进行遍历数据表操作的，可见，在数据量较大时是极其的耗时的，如果没有异步IO处理，进程将被挟持，很浪费时间，

4.创建索引

是否要创建索引，几点原则：

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

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

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

不会再出现where子句中的字段不该创建索引

索引能够极大的提高数据检索效率，也能够改善排序分组操作的性能，但是我们不能忽略的一个问题就是索引是完全独立于基础数据之外的一部分数据，更新数据带来的IO量和调整索引的所致的计算量的资源消耗。

5.使用索引

1.使用联合索引

Mysql可以为多个字段创建索引，一个索引可以包括16个字段。对于联合索引，只有查询条件中使用了这些字段第一个字段时，索引才会生效。

2.使用 OR 关键字的查询

查询语句的查询条件中只有 OR 关键字 且 OR 前后的两个条件中的列都是索引时，索引才会生效 否则索引不生效。

6.存储优化

存储数据时，影响存储素的主要是索引、唯一性校验、一次存储的数据条数等。

存储数据的优化，不同的存储引擎优化手段不一样 在Mysql中常用的存储引擎有 MyISAM和innoDB， 两者的区别：

1）InnoDB支持事务，MyISAM不支持，这一点是非常之重要。事务是一种高级的处理方式，如在一些列增删改中只要哪个出错还可以回滚还原，而MyISAM就不可以了。

2）MyISAM适合查询以及插入为主的应用，InnoDB适合频繁修改以及涉及到安全性较高的应用

3）InnoDB支持外键，MyISAM不支持

4）从MySQL5.5.5以后，InnoDB是默认引擎

5）InnoDB不支持FULLTEXT类型的索引

6）InnoDB中不保存表的行数，如select count(*) from table时，InnoDB需要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count(*)语句包含where条件时MyISAM也需要扫描整个表

7）对于自增长的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中可以和其他字段一起建立联合索引

8）清空整个表时，InnoDB是一行一行的删除，效率非常慢。MyISAM则会重建表

9）InnoDB支持行锁（某些情况下还是锁整表，如 update table set a=1 where user like '%lee%'

7.存储引擎介绍

1.MyISAM存储引擎

MyISAM存储引擎是一种非事务性的引擎，提供高速存储和检索，以及全文搜索能力，适合数据仓库等查询频繁的应用。

每一个表都存放为三个以表名命名的物理文件。有存放表结构定义信息的.frm文件，还有存放了表的数据。MYD文件和存放索引数据的.MYI文件。

2.innodb存储引擎

innodb 存储引擎是事务安全的，因此如果需要一个事务安全的存储引擎，建议使用它，如果你的数据执行大量的的insert和update，出于性能方面的考虑应该是用innoDB表

提供行锁(locking on row level)，提供与  Oracle  类型一致的不加锁读取(non-locking read in SELECTs)，另外，InnoDB表的行锁也不是绝对的，如果在执 行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表，例如update table set num=1 where name like “%aaa%

应用场景

MyISAM管理非事务表 它提供高速检索和存储 以及全文搜索的能力

innoDB用于事务处理应用程序具有众多特性，包括ACID事务支持

MyISA存储优化

1.禁用索引

对于非空表 mysql 会根据表的索引插入的记录建立索引，如果插入大量的数据 建立索引会降低插入数据速度

为了解决这个问题 可以再批量插入数据之前禁用索引 数据插入完成之后在开启

禁用索引的语句 alter table table_name disable keys

开启索引的语句 alter table table_name enable keys

2.禁用唯一性检查

唯一性检查会降低插入记录的速度，可以再插入之前禁用唯一检查，插入数据后在开启

禁用唯一检查的语句 set unique_checks=0

开启唯一检查的语句 set unique_checks=1

3.批量插入数据

插入数据时，可以使用一条inser语句插入一条数据，也可以插入多条数据

4.使用load data infile

当需要批量导入数据时，使用 load data infile 语句比insert语句插入速度快很多

InnoDB存储优化

1.禁用唯一性检查

  唯一性检查会降低插入记录的速度，可以再插入之前禁用唯一检查，插入数据后在开启

禁用唯一检查的语句 set unique_checks=0

开启唯一检查的语句 set unique_checks=1

2.禁用外检检查

插入数据之前执行禁止对外键检查 数据插入完成后在恢复 可以提供插入速度

禁用：set foreign_key_checks=0;

开启：set foreign_key_checks=1;

3.禁止自动提交

插入数据之前执行禁止事务的自动提交，数据插入完成之后在恢复，可以提高插入速度

禁用：set autocommit=0；

开启：set autocommit=1；

数据库结构优化

优化表结构

尽量将表字段定义为 not null 约束，这是由于在mysql中含有空值的列很难进行查询优化，NULL值会使索引以及索引的统计信息变得更复杂

对于只包含特定类型的字段，可以使用enum，set等符合数据类型

数值型字段的比较字符串的比较效率高得多，字段类型尽量使用最小 、最简单的数据类型。

尽量使用 tinyint smallint medium_int 作为整数类型而非int 如果非负则加上unsigned

varchar的长度只分配真正需要的空间

单表不要有太多字段，建议在20以内

合理的加入冗余字段可以提高查询速度。

表拆分

垂直拆分

垂直拆分按照字段进行拆分，其实就是吧组成一行的多个列分开放到不同的表中，这些表具有不同的结构，拆分后表具有更少的列。

水平拆分

水平拆分 按照行进行拆分 常见的就是分库分表

1.动态数据源

2.MyCat数据库中间件

分区

使用分区是大数据处理后的产物

分区适用于例如 日志记录，查询少，一般用于后台的数据报表分析

mysql只要支持4中模式的分区：range分区、list预定义列表分区、hash分区、key键值分区

读写分离

大型网站会有大量的并发访问，如果还是传统的数据结构，或者只是单单靠一台服务器扛，如此多的数据库连接操作，数据库必然会崩溃，数据丢失的话，后果更是不堪设想，这时候，我们需要考虑如何减少数据库的链接

我们发现一般情况对数据库而言都是“读多写少”，也就是说对数据库读取数据的压力比较大，这样分析可以采用数据库集群的方案。其中一个是主库，负责写入数据。我们称之为写库，其他都是从库，负责读取数据，我们称之为 读库，这样可以缓解一台服务器的压力。

数据库集群

如果访问量非常大 虽然使用读写分离能够缓解压力 但是一旦写操作一台服务器都不能接受了 这个时候我们就需要考虑使用多台服务器实现写操作

例如可以使用MyCat搭建mysql集群 对ID求3的余数，这样可以把数据分别存放到3台不同的服务器上，由MyCat负责维护集群节点的使用

硬件优化

是服务器的硬件性能直接决定着MyCat数据库的性能，硬件的性能瓶颈，直接决定MySQL数据库的运行速度和效率

1.配置较大的内存

2.磁盘i/0相关

3.配置CPU相关：

在服务器的BIOS设置中，可调整下面的几个配置：

选择Performance Per Watt Optimized(DAPC)模式，发挥CPU最大性能；

关闭C1E和C States等选项，提升CPU效率

Memory Frequency（内存频率） 选择Maximum Performance

MySQL缓存

全局缓存

局部缓存

其他缓存