【MySql】存储引擎，索引及优化

一，存储引擎

MySql5.0支持的的存储引擎包含：InnoDB 、MyISAM 、BDB、MEMORY、MERGE、EXAMPLE、NDB Cluster、ARCHIVE、CSV、BLACKHOLE、FEDERATED等，其中InnoDB和BDB提供事务安全表，其他存储引擎是非事务安全表。

MySQL5.5之前的默认存储引擎是MyISAM，5.5之后就改为了InnoDB。

二，各种存储引擎特性

特点	InnoDB	MyISAM	MEMORY	MERGE
存储限制	64TB	有	有	没有
事务安全	支持
锁机制	行锁（适合高并发）	表锁	表锁	表锁
B树索引	支持	支持	支持	支持
哈希索引			支持
全文索引	支持（5.6版本之后）	支持
集群索引	支持
数据索引	支持		支持
索引缓存	支持	支持	支持	支持
数据可压缩		支持
空间使用	高	低	N/A	低
内存使用	高	低	中等	低
批量插入速度	低	高	高	高
支持外键	支持

下面重点介绍两种最常使用的引擎：InnoDB,MyISAM：

1.InnoDB:

InnoDB存储引擎是Mysql5.5版本后默认的存储引擎，InnoDB存储引擎提供了提交，回滚，崩溃恢复能力的事务安全，写的处理效率差一些，并且会占用更多的磁盘空间来保留数据和索引；

InnoDB存储引擎不同于其他存储引擎的特点：事务控制，外键约束；

InnoDB存储引擎存储方式：

Ⅰ.使用共享表空间存储，这种方式的表的表结构保存在.frm文件中，数据和索引保存在innodb_data_home_dir 和 innodb_data_file_path 定义的表空间中，可以是多个文件。

Ⅱ.使用多表空间存储，这种方式创建的表的表结构仍然在.frm文件中，但是每个表的数据和索引单独保存在.ibd中。

2.MyISAM:

MyISAM不支持事务，也不支持外键，优势是访问的速度快，对事务的完整性没有要求或者Select，Insert为主的应用基本上都可以使用这个引擎来创建表；

MyISAM存储引擎存储方式：

每个MyISAM在磁盘上存储3个文件，其文件名和表名相同，但扩展名分别是：

.frm ( 存储表定义 )；

.MYD（MYData，存储数据）；

.MYI（MYIndex，存储索引）;

三，存储引擎的选择

在选择存储引擎时，应该根据应用系统的特点选择合适的存储引擎。对于复杂的应用系统，还可以根据实际情况选择多种存储引擎进行组合。以下是几种常用的存储引擎的使用环境。

• InnoDB : 是Mysql5.5版本后的默认存储引擎，用于事务处理应用程序，支持外键。如果应用对事务的完整性有比较高的要求，在并发条件下要求数据的一致性，数据操作除了插入和查询意外，还包含很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。InnoDB存储引擎除了有效的降低由于删除和更新导致的锁定， 还可以确保事务的完整提交和回滚，对于类似于计费系统或者财务系统等对数据准确性要求比较高的系统，InnoDB是最合适的选择。

• MyISAM： 如果应用是以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。

四，索引

1.索引结构

索引结构	描述
B+Tree索引	最常见的索引类型，大部分索引都支持B+树索引
Hash索引	只有Memory引擎支持，底层数据结构是用哈希表实现的, 只有精确匹配索引所有列的查询才有效, 不支持范围查询
R-tree(空间索引）	空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少
Full-text(全文索引)	全文索引查找的是文本中的关键词，而不是比较索引中的值，类似于Lucene,Solr,ES

2.索引分类

（按属性）

分类	含义	特点	关键字
主键索引	针对于表中主键创建的索引	默认自动创建，只能有一个	PRIMARY
唯一索引	避免同一个表中某数据列中的值重复	可以有多个	UNIQUE
常规索引	快速定位特定数据	可以有多个
全文索引	全文索引查找的是文本中的关键字，而不是比较索引中的值	可以有多个	FULLTEXT

---

（按数据存储方式）

分类	含义	特点	索引结构
聚簇/聚集索引	将数据存储于索引放到了一块，索引结构的叶子节点保存了行数据	必须有，而且只有一个
非聚簇索引/辅助索引/二级索引	将数据与索引分开存储，索引结构的叶子节点关联的是对应的主键	可以存在多个

3.避免索引失效

Ⅰ.全局匹配，对索引中所有列都指定具体值。

Ⅱ.如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始，并且不能跳过索引中的列。

Ⅲ.范围查询右边的列，不能使用索引。

Ⅳ.不要在索引列上进行运算操作，否则索引失效。

Ⅴ.字符串不加单引号，造成索引失效。

Ⅵ.尽量使用覆盖索引，减少使用select *，但如果查询列，超出索引列，会降低性能。

Ⅶ.用or分割开的条件，如果or前的条件中有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

Ⅷ.以%开头的Like模糊查询，索引失效；如果仅仅是尾部模糊匹配，索引不会失效，如果是头部模糊匹配，索引失效。

Ⅸ.如果mysql评估使用索引比全表更慢，则不适用索引。

Ⅹ. is null，is not null 有时索引失效；in，not in 有时索引失效；

4.索引设计原则

• 对查询频次较高，且数据量比较大的表。

• 索引字段的选择，最佳候选列应当从where子句的条件中提取。

• 使用唯一索引，区分度越高，使用索引的效率越高。

• 索引可以有效的提升查询数据的效率，但索引数量不是多多益善，索引越多，维护索引的代价自然也就水涨船高。

• 使用短索引，索引创建之后也是使用硬盘来存储的，因此提升索引访问的I/O效率，也可以提升总体的访问效率。

• 利用最复合索引，对N个列组合而成的组合索引，那么相当于是创建了N个索引，如果查询时where子句中使用了组成该索引的前几个字段，那么这条查询SQL可以利用组合索引来提升查询效率。

建议使用复合索引，少使用单列索引。

五，SQL优化

先看看sql语句的顺序

编写顺序
SELECT DISTINCT
	<select list>
FROM
	<left_table> <join_type>
JOIN
	<right_table> ON <join_condition>
WHERE
	<where_condition>
GROUP BY
	<group_by_list>
HAVING
	<having_condition>
ORDER BY
	<order_by_condition>
LIMIT
	<limit_params>

-----------------------------------------------------------------------------------------

执行顺序
FROM	<left_table>

ON 		<join_condition>

<join_type>		JOIN	<right_table>

WHERE		<where_condition>

GROUP BY 	<group_by_list>

HAVING		<having_condition>

SELECT DISTINCT		<select list>

ORDER BY	<order_by_condition>

LIMIT		<limit_params>

1.优化insert语句

如果需要同时对一张表插入很多行数据时，应该尽量使用多个值表的insert语句，这种方式将大大的缩减客户端与数据库之间的连接，关闭的消耗；数据的插入尽量按照有序插入；

2.优化order by语句

先说两种排序方式：

Ⅰ：通过返回数据进行排序，也就是通常的filesort排序，所有不是通过索引直接返回排序结果的排序都叫FileSort排序。

Ⅱ：通过有序索引顺序扫描直接返回的有序数据，这种为using index，不需要额外排序，操作效率高。

了解了两种排序方式，优化目标就清晰了：尽量减少额外的排序，通过索引直接返回有序数据，where条件和order by使用相同的索引，并且order by的顺序和索引顺序相同，并且order by的字段都是升序或降序。否则肯定需要额外的操作，这样就出现了FileSort。

对于FileSort，mysql有两种排序算法：一种是（两次扫描算法）：首先根据条件去除排序字段和行指针信息，然后再排序去sort buffer中排序，如果sort buffer不够，则在临时表temporary table 中存储排序结果。完成排序之后，再根据行指针回表读取记录，该操作可能会导致大量随机I/O操作。另外一种是（一次扫描算法）：次性取出满足条件的所有字段，然后在排序区 sort buffer 中排序后直接输出结果集。排序时内存开销较大，但是排序效率比两次扫描算法要高。

MySQL 通过比较系统变量 max_length_for_sort_data 的大小和Query语句取出的字段总大小， 来判定是否那种排序算法，如果max_length_for_sort_data 更大，那么使用第二种优化之后的算法；否则使用第一种。

可以适当提高 sort_buffer_size 和 max_length_for_sort_data 系统变量，来增大排序区的大小，提高排序的效率。

3.优化group by语句

由于group by实际上也同样会进行排序操作，而且与order by相比，group by主要是多了排序之后的分组操作，当然在分组如果使用了一些聚合函数，那么还有进行一些聚合函数的计算，所以在group by的实现中，我们也可以一样利用索引。

如果查询包含group by，但是用户想要避免排序结果的消耗，则可以执行order by null 禁止排序。

4.优化嵌套查询

Mysql4.1版本之后，开始支持SQL的子查询。这个技术可以使用SELECT语句来创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起来也很容易。但是，有些情况下，子查询是可以被更高效的连接（JOIN）替代。

5.优化or条件

对于包含OR的查询子句，如果要利用索引，则OR之间的每个条件列都必须用到索引 ， 而且不能使用到复合索引； 如果没有索引，则应该考虑增加索引。建议使用 union 替换 or ；

6.使用SQL提示

SQL提示，是优化数据库的一个重要手段，简单来说，就是在SQL语句中加入一些人为的提示来达到优化操作的目的。

1.在查询语句中表名的后面，添加 use index 来提供希望MySQL去参考的索引列表，就可以让MySQL不再考虑其他可用的索引。

select * from tb_user use index(idx_seller_name) where name = 'zhangsan';

2.如果用户只是单纯的想让MySQL忽略一个或者多个索引，则可以使用 ignore index 作为 hint 。

select * from tb_user ignore index(idx_seller_name) where name = 'zhangsan';

3.为强制MySQL使用一个特定的索引，可在查询中使用 force index 作为hint 。

select * from tb_user force index(idx_seller_name) where name = 'zhangsan';