MYSQL索引优化及其存储引擎~

如何定位SQL的操作效率低？

1、慢查询日志

MySQL可以设置慢查询日志，当SQL执行的时间超过我们设定的时间，那么这些SQL就会被记录在慢查询日志当中，然后可以通过查看日志，用explain分析这些SQL的执行计划，来判定为什么效率低下，是没有使用到索引？还是索引本身创建的有问题？或者是索引使用到了，但是由于表的数据量太大，花费的时间就是很长，那么对此我们可以把表分成n个小表，比如订单表按照年份划分成为多个小表等。

2、查看磁盘IO读写的数据量

数据库毕竟是磁盘存储，可以通过项目运行过程中，检测磁盘IO读写的数据量，来定位效率低下的SQL，在Windows上打开任务管理器就可以检测，Linux上通过命令可以检测。

以windows为例，选择IO读写查看列，检测我们Java程序运行过程中，执行哪些SQL时花费的磁盘IO太大，读写的字节数过多，一般不加索引，或者索引创建不正确没有使用到索引，查询花费的磁盘IO字节数都会上M，而添加了索引的查询一般消耗在K级别。

3、show status

MySQL提供了show status命令，查看MySQL Server的运行参数，可以查看select、insert、delete、update语句的执行频率，慢查询次数，事务的提交和回滚的次数。

常用的优化方式

1、对查询进行优化，应尽量避免全表扫描，首先应考虑在where和order by涉及的列上建立索引。

2、注意会使得索引失效的运算符。比如：like后面的通配符在前面，索引失效；没有使用联合索引的第一列，not in ，！=，使用MySQL函数，类型转换，or等都无法用到索引。

3、应该尽量避免在where子句中使用or来连接条件，否则将导致MySQL放弃使用索引而进行全表扫描，如：select id from t where num=10 or num=20可以这样查询：select id from t where num=10 union all id from t where num=20.

4、in和not in也要慎用，否则导致全表扫描，如：select id from t where num in(1,2,3)对于连续的数值，能用between就不要用in了：select id from t where num between 1 and 3.

5、应尽量避免在where子句中对字段进行表达式操作，这将导致引擎放弃使用索引而进行全表扫描。如：select id from t where num/2=100应改为：select id from where num=100*2。

6、应尽量避免在where子句中对字段进行函数操作，这将导致引擎放弃使用索引而进行全表扫描。如：select id from t where substring(name,1,3)='abc'，name以abc开头的id，应改为：select id from t where name like 'abc%'

7、不要在where子句中的“=”左边进行函数、算术运算或者其他表达式运算，否则系统将可能无法正确使用索引。

8、在使用索引字段作为条件时，如果该索引是复合索引，那么必须使用到该索引中的第一个字段作为条件时才能保证系统使用该索引，否则该索引将不会被使用，并且应尽可能的让字段顺序与索引顺序相一致。

9、并不是所有的索引对查询都是有效的，SQL是根据表中数据来进行查询优化的，当索引列有大量数据重复时，SQL查询可能不会去利用索引，如一表中有字段sex、male、female几乎各一半，那么即使咋sex上建了索引也对查询效率起不了作用，这个是属于MySQL的SQL优化器对索引的一种优化。

10、索引并不是越多越好，索引固然可以提高相应的select的效率，但是同时也降低了insert及update的效率，因为insert或者update是有可能重建索引，索引怎样创建索引需要慎重考虑，视具体情况而定。一个表的索引数最好不要超过6个，若太多则应考虑一些不常使用到的列上建的索引是否有必要。

11、尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，这会降低查询和连接的性能，并会增加存储开销。这是因为引擎在处理查询和连接时逐步比较字符串中的每一个字符，而对于数字型而言只需要比较一次就够了。

12、尽可能的使用varchar/nvarchar代替char/nchar，因为首先变长字段存储空间小，可以节省存储空间，其次对于查询来说，在一个相对较小的字段内搜索效率显然要高一些。

存储引擎

存储引擎就是指表的类型，数据库的存储引擎决定了表在计算机中的存储方式。

使用show engines语句可以查看MySQL数据库支持的存储引擎。

1、InnoDB给MySQL的表提供了事务、回滚、崩溃修复能力和多版本并发控制的事务安全，且InnoDB是MySQL上第一个提供外键约束的表引擎，InnoDB存储引擎中支持自动增长列，且规定自增列必须为主键，InnoDB存储引擎支持外键，InnoDB存储引擎中，创建的表的表结构存储在.frm文件中，对于类似计费系统或者财务系统等对数据准确性要求比较高的系统，可以选择该引擎。

InnoDB存储引擎的优势在于提供了良好的事务管理、崩溃修复能力和并发控制。缺点是其读写效率较差，占用的数据空间相对较大。

2、MyISAM不支持事务、也不支持外键，其优势是访问的速度快，对事务的完整性没有要求或者以select、insert为主的应用基本上都可以使用这个引擎来创建表。每个MyISAM在磁盘上存储成3个文件，其文件名和表名相同，扩展名分别为：.frm（存储表的定义）；.MYD（MYData，存储数据）；.MYI（MYIndex，存储索引）。以读操作或者插入操作为主，只有很少的更新和删除操作。

MyISAM存储引擎的优势在于占用空间小，处理速度快。缺点是不支持事务的完整性和并发性。

3、MEMORY使用存储在内存中的内容来创建表，而且所有数据也放在内存中。每个基于MEMORY存储引擎的表实际上对应于一个磁盘文件，该文件的文件名与表名相同，类型为frm类型，该文件只存储表的结构。而其数据文件，都是存储在内存中，这样有利于对数据的快速处理，提高整个表的处理效率。基于MEMORY存储引擎的表的生命周期很短，一般都是一次性的，且MEMORY表的大小是受到限制的。默认使用HASH索引，但是一旦服务关闭，表中的数据就会丢失掉。MEMORY通常用于更新不太频繁的小表，用以快速得到访问结果。

MYSQL不同的存储引擎的区别
---------------------------------------------------------------------------------------------------------------
 种类       |锁机制     |B_树索引   |哈希索引   |外键    | 事务    | 索引缓存    |数据缓存
---------------------------------------------------------------------------------------------------------------
MYISAM| 表锁          |支持            | 不支持    | 不支持 |不支持 | 支持           |  不支持 
---------------------------------------------------------------------------------------------------------------
INNODB| 行锁         | 支持            | 不支持    | 支持     | 支持    | 支持           |  支持 
----------------------------------------------------------------------------------------------------------------
MEMORY| 表锁      | 支持            | 支持        | 不支持  |不支持 | 支持          |  支持
---------------------------------------------------------------------------------------------------------------
锁机制：在事务并发操作时，并发操作粒度
B-树索引|hash索引：提高访问速度
外键:子表的字段依赖于父表的键，设置两张表的依赖关系
事务：多个SQL语句执行，要么全部成功，要么全部失败，不存在部分成功
索引缓存|数据缓存：在查询时，对源数据未做修改，重复查询是否可以减少磁盘IO

创建表时设置存储引擎：
create table table_name() engine = innodb;

修改存储引擎：
alter table table_name engine = myisam;

修改配置文件来达到全部表的存储引擎修改
Windows 下my.ini文件 ，Linux下my.cnf文件