关于Mysql优化的常用方式（存储引擎方面）

1.Mysql的优化概述

大概是从四个方面去优化mysql. 设计，功能，架构，合理sql语句，我们这里就讲一下存储引擎方面的相关知识

1.存储引擎方面
首先我们先说一下什么是存储引擎，存储引擎就是数据存储的某一种格式，就是一种存储文件的系统，因为我们的数据是以文件的格式存储在磁盘上的，那么某一种文件系统就是我们存储引擎的概念。
mysql的存储引擎有多种，常用的就是innodb，myisam。
我们把mysql分为了三个层次 如图：
mysql其实算是比较高级的数据库，因为其他的数据库有很多都没有存储引擎这个概念。
我们可以通过show engines来查看所有支持的存储引擎：

下面我们来介绍两种最常见的存储引擎之间的差异:
1、MyISAM：默认表类型，它是基于传统的ISAM类型，ISAM是Indexed Sequential Access Method (有索引的顺序访问方法) 的缩写，它是存储记录和文件的标准方法。不是事务安全的，而且不支持外键，如果执行大量的select，insert MyISAM比较适合。

2、InnoDB：支持事务安全的引擎，支持外键、行锁、事务是他的最大特点。如果有大量的update和insert，建议使用InnoDB，特别是针对多个并发和QPS较高的情况。
一、表锁差异

MyISAM:

myisam只支持表级锁，用户在操作myisam表时，select，update，delete，insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。也可以通过lock table命令来锁表，这样操作主要是可以模仿事务，但是消耗非常大，一般只在实验演示中使用。

InnoDB ：

Innodb支持事务和行级锁，是innodb的最大特色。

事务的ACID属性：atomicity,consistent,isolation,durable。

并发事务带来的几个问题：更新丢失，脏读，不可重复读，幻读。

事务隔离级别：未提交读(Read uncommitted)，已提交读(Read committed)，可重复读(Repeatable read)，可序列化(Serializable)

二、数据库文件差异

MyISAM ：

myisam属于堆表

myisam在磁盘存储上有三个文件，每个文件名以表名开头，扩展名指出文件类型。

.frm 用于存储表的定义

.MYD 用于存放数据

.MYI 用于存放表索引

myisam表还支持三种不同的存储格式：

静态表(默认，但是注意数据末尾不能有空格，会被去掉)

动态表

压缩表

InnoDB ：

innodb属于索引组织表

innodb有两种存储方式，共享表空间存储和多表空间存储

两种存储方式的表结构和myisam一样，以表名开头，扩展名是.frm。

如果使用共享表空间，那么所有表的数据文件和索引文件都保存在一个表空间里，一个表空间可以有多个文件，通过innodb_data_file_path和innodb_data_home_dir参数设置共享表空间的位置和名字，一般共享表空间的名字叫ibdata1-n。

如果使用多表空间，那么每个表都有一个表空间文件用于存储每个表的数据和索引，文件名以表名开头，以.ibd为扩展名。
三、索引差异

• 1、关于自动增长*

  myisam引擎的自动增长列必须是索引，如果是组合索引，自动增长可以不是第一列，他可以根据前面几列进行排序后递增。

  innodb引擎的自动增长咧必须是索引，如果是组合索引也必须是组合索引的第一列。

  2、关于主键

  myisam允许没有任何索引和主键的表存在，

  myisam的索引都是保存行的地址。

  innodb引擎如果没有设定主键或者非空唯一索引，就会自动生成一个6字节的主键(用户不可见)

  innodb的数据是主索引的一部分，附加索引保存的是主索引的值。

  3、关于count()函数

  myisam保存有表的总行数，如果select count(*) from table;会直接取出出该值

  innodb没有保存表的总行数，如果使用select count(*) from
  table；就会遍历整个表，消耗相当大，但是在加了wehre 条件后，myisam和innodb处理的方式都一样。

  4、全文索引

  myisam支持 FULLTEXT类型的全文索引

  innodb不支持FULLTEXT类型的全文索引，但是innodb可以使用sphinx插件支持全文索引，并且效果更好。（sphinx
  是一个开源软件，提供多种语言的API接口，可以优化mysql的各种查询）

  5、delete from table

  使用这条命令时，innodb不会从新建立表，而是一条一条的删除数据，在innodb上如果要清空保存有大量数据的表，最
  好不要使用这个命令。(推荐使用truncate table，不过需要用户有drop此表的权限)

  6、索引保存位置

  myisam的索引以表名+.MYI文件分别保存。

  innodb的索引和数据一起保存在表空间里。
  六、重复地总结一遍

1、MyISAM不支持事务，InnoDB是事务类型的存储引擎，当我们的表需要用到事务支持的时候，那肯定是不能选择MyISAM了。

2、MyISAM只支持表级锁，BDB支持页级锁和表级锁默认为页级锁，而InnoDB支持行级锁和表级锁默认为行级锁

表级锁：直接锁定整张表，在锁定期间，其他进程无法对该表进行写操作，如果设置的是写锁，那么其他进程读也不允许

MyISAM是表级锁定的存储引擎，它不会出现死锁问题

对于write，表锁定原理如下：

如果表上没有锁，在其上面放置一个写锁，否则，把锁定请求放在写锁队列中。

对于read，表锁定原理如下 ：

如果表上没有写锁定，那么把一个读锁放在其上面，否则把锁请求放在读锁定队列中

当一个锁定被释放时，表可被写锁定队列中的线程得到，然后才是读锁定队列中的线程。这意味着，如果你在一个表上有许多更新，那么你的SELECT语句将等到所有的写锁定线程执行完。

行级锁：只对指定的行进行锁定，其他进程还是可以对表中的其他行进行操作的。

行级锁是Mysql粒度最小的一种锁，它能大大的减少数据库操作的冲突，但是粒度越小实现成本也越大。

行级锁可能会导致“死锁”，那到底是怎么导致的呢，分析原因：Mysql行级锁并不是直接锁记录，而是锁索引。索引分为主键索引和非主键索引两种，如果一条sql语句操作了主键索引，那么Mysql就会锁定这个主键索引，如果sql语句操作的是非主键索引，那么Mysql会先锁定这个非主键索引，再去锁定主键索引。

在UPDATE 和 DELETE操作时Mysql不仅会锁定所有WHERE 条件扫描过得索引，还会锁定相邻的键值。