MySQL之性能优化

一、一些概念知识

1、各数据库存储引擎

mysql的存储引擎是针对表进行设置的，一个库的不同表可以设置不同的存储引擎，mysql默认支持多种存储引擎，以适用不同领域的数据库应用需要，主要的几个数据库引擎如下：

a、MyISAM存储引擎

5.5之前默认的存储引擎，不支持事务、不支持外键，表级锁，内存和硬盘空间占用率低，其优势是访问速度快，对事务完整性没有要求，以select、insert为主的应用基本上都可以使用这个引擎；

b、InnoDB存储引擎

5.5之后默认的存储引擎，提供了具有提交、回滚和奔溃恢复能力的事务安全，支持外键并提供了行级锁，其劣势在于写的处理效率相对较低，并且会占用更多的磁盘空间以保留数据和索引；

c、MEMORY存储引擎

使用存于内存中的内容来创建表，MEMORY类型的表数据存于内存访问非常的快，默认使用HASH索引，一旦数据库服务重启或关闭，表中的数据就会丢失；

d、MERGE存储引擎

MERGE存储引擎是一组MyISAM表组合，这些MyISAM表结构完全相同。MERGE表本身没有数据，对MERGE表的CRUD操作都是通过内部的MyISAM表进行的；

2、MySQL的两种索引

a、B-Tree索引

B-Tree 索引是 MySQL 数据库中使用最为频繁的索引类型， 除了 Archive 存储引擎之外的其他所有的存储引擎都支持 B-Tree 索引。不仅仅在 MySQL 中是如此，实际上在其他的 很多数据库管理系统中B-Tree 索引也同样是作为最主要的索 引类型，这主要是因为 B-Tree 索引的存储结构在数据库的 数据检 索中有非常优异的表现。

b、hash索引

Hash 索引结构的特殊性，其检索效率非常高，索引的检索可 以一次定位，不像B-Tree 索引需要从根节点到枝节点，最后 才能访问到页节点这样多次的IO访问，所以 Hash 索引的查 询效率要远高于 B-Tree 索引。 但是hash索引不常用，因为hash索引没有默认排序。

c、索引的相关概念

普通索引： 最基本的索引，没有任何限制。
唯一索引： 与"普通索引"类似，不同的就是：索引列的值必须唯一，但允许有空值。
主键索引： 它是一种特殊的唯一索引，不允许有空值。
全文索引： 仅可用于 MyISAM 表，针对较大的数据，生成全文索引很耗时好空间。
组合索引： 为了更多的提高mysql效率可建立组合索引，遵循”最左前缀“原则。
补充：
聚簇索引(Clustered Indexes)
聚簇索引保证关键字的值相近的元组存储的物理位置也相同（所以字符串类型不宜建 立聚簇索引，特别是随机字符串，会使得系统进行大量的移动操作），且一个表只能 有一个聚簇索引。因为由存储引擎实现索引，所以，并不是所有的引擎都支持聚簇索 引。目前，只有solidDB和InnoDB支持。
非聚簇索引
二级索引叶子节点保存的不是指行的物理位置的指针，而是行的主键值。这意味着通 过二级索引查找行。
InnoDB对主键建立聚簇索引。如果你不指定主键，InnoDB会用一个具有唯一且非空值 的索引来代替。如果不存在这样的索引，InnoDB会定义一个隐藏的主键，然后对其建 立聚簇索引。一般来说，DBMS都会以聚簇索引的形式来存储实际的数据，它是其它二 级索引的基础。
关于 窄索引 和 宽索引

三星索引

d、存储引擎及文件格式比较

sql查询过程：

e、建索引的目的

1. 加快查询速度，当然了，使用索引后查询有迹可循。
2. 减少I/O操作，通过索引的路径来检索数据，不是在磁盘中随机检索。
3. 消除磁盘排序，索引是排序的，走完索引就排序完成

二、sql的优化

优化目标:

1. 根据需求建立索引
2. 每个查询都要使用索引以提高查询效率,至少达到range级别，最好能达到ref；
3. 追求key_len和rows最小；

sql语句的优化可以从以下几种情况考虑：

1、大批量插入数据

• 大批量数据插入空表

  可将表设置成为MyISAM，并通过disable keys将唯一索引关闭；

• 大批量数据插入非空Innodb表，可采取如下措施提高效率：

  1. 导入数据时按照主键顺序排列；
  2. 导入数据前使用set UNIQUE_CHECKS=0,关闭唯一性校验，导入后恢复；
  3. 如果使用了自动提交，建议在导入前执行SET AUTOCOMMIT=0，关闭自动提交，导入后恢复；

2、优化INSERT语句

• 尽量使用多个值表的insert语句，降低连接、关闭的消耗；
• 将索引文件和数据文件分在不同的磁盘上存放；
• 从一个文本文件装入一个表时，使用LOAD DATA INFLIE ,比一般的insert语句快20倍；

3、查询优化

sql优化的大部分内容说的都是查询优化，而查询优化和索引是分不开的。

1. 尽量减少额外的排序，通过索引直接返回有序数据；
2. 尽量少排序，尽量早过滤，避免类型转换。
3. where条件和order by使用相同的索引，并且order by的顺序与索引顺序相同，并且order by的字段都是升序或者都是降序；
4. 尽量只选择查询必要的字段，提高sql性能；
5. 尽量少用join关联表，因为多关联表每次就多扫描一个表，要尽量减少表的关联。
6. 在不得不关联表的时候，在选择目标字段部分，能用join关联查询的不要用子查询，而在where后面的查询条件可以用子查询来缩小范围。
7. 对于包含or的查询语