msyql -uroot -p123456

msyqladmin -uroot -p123456

GPG keys
rpm -ivh --force --nodoeps

mysql 配置
查看编码 show variables like "%char%"

设置编码
[mysql]
default-character-set=utf8

[client]    
default-character-set=utf8
[mysqld]
character_set_server=utf8
character_set_client=utf8
collation_server=utf8_general_ci

修改编码只对之后的数据库生效

清屏 ctrl+l   ||   system clear
sql 执行顺序  from ..on..join..group..having..select (dinstinct)..order by limit... 

原理
逻辑分层
show engines
连接层-->服务层-->引擎层-->数据层
show variables like "%storage_engine%"

sql优化
1索引是种数据结构
索引的缺点 1>本身很大
2>不是所有情况都适用
3>降低增删效率
索引的优点
1>提高查询效率
2>降低cpu使用率
索引
主键索引:主键不能为null
单值索引:单列
唯一索引:不能重复
复合索引:多个列构成的索引
创建索引1
创建索引
create index 索引名 on 表名(字段)
创建唯一索引
create unqiue index 索引名 on 表名(字段)
创建复合索引
create index 索引名 on 表名(字段)
方式2
创建索引 alter table 表名 索引类型 索引名(字段)

        alter table 表名 add  index 索引名 on 表名(字段)
    创建唯一索引
        alter table 表名 add unqiue index 索引名 on 表名(字段)
    创建复合索引
        alter table 表名 add index 索引名 on 表名(字段1,字段2....)

主键默认主键索引
    删除索引
    drop index 索引名 on 表名
    查询索引
    show index on 表名
select type(查询类型)
    primary :最外层
    subquery:费最外层
    simple: 简单查询
    derived:衍生查询  使用到了临时表
            a:在from 子查询中只有一张表
            b:在from子查询中如果 有table1 union table2 则table1 就是derived,table2就是union
    union result  有union关系的表
type (索引类型)
    system>const>eq_ref>ref>range>index>all
    system:只有一条数据的系统表 衍生表只有一条数据的子查询
    const:仅能查到一条数据的sql 用于primary_key或unique索引   
    eq_ref: 对于每个索引键的查询 返回唯一匹配行数据
    ref:非唯一索引
    range:检索指定范围的行(between,>=,>,=,<,<=) in有时候会不显示
    idnex:查询索引的列
    all:查询整个表
possible keys:可能用到的索引  预测  不准
    
key 实际用到的索引             
如果possible keys/key是null 没有索引
key_len 索引的长度
    作用:复合索引是否完全被使用
    如果索引字段可以为null则用一个字节标注
    mysql用两个字节表示可变长度
    
ref:指明当前表 所参照的字段 
rows:  索引优化查询的数据个数
Extra:
    using filesort 性能消耗大 需要额外一次排序
        对于单索引 如果排序和索引是同一个字段不会出现 using filesort 
    
    避免  where 哪些字段  就order by哪些字段
    using where:需要回表查询
    using temporary 性能消耗大 用到临时表一般出现的group by  已经有表了  再需要一个临时表
    避免:查询的字段就是group by的字段
    using index:性能提升  索引覆盖 只从索引中获取数据 不需要回表查询
    impossible where :where字句永远为false
    如果(a,b,c,d)复合索引和使用的顺序全部一致(且不跨列使用)  则复合索引全部使用  如果部分一致 

优化单表,多表
    1 加索引
        根据sql执行顺序  调整索引顺序()删除之前的索引顺序
    索引不能跨烈使用(最佳左前缀),保持索引定义和使用的一致性
        a.索引需要逐步优化  b.将含in的范围查询放到最后
两张表优化
    左链接
        a.小表驱动大表
        b. 索引建立在经常使用的key上  左外链接给左表加  右外连接给右表加
        
    using join buffer:mysqk引擎使用了连接缓存

避免索引失效的原则
    a.复合索引不能跨列或无序使用(最佳左前缀)
    b.复合索引 尽量使用全索引匹配
    c.不要在索引上进行任何操作
    d.复合索引不能使用(!= ,<>,>)
sql优化是一种概率层面优化
    一般而言sql范围查询本身有效之后失效
    e 尽量使用索引覆盖
    f:like  尽量用常量开头  不用%
    g:尽量不使用转换
    h:不能使用or 否则索引失效
    
exist/in
    如果子查询数据集大用exist
    如果主查询数据集大用in
order by 
    using filesort算法:双路排序,单路排序
    
慢查询
    永久开启
    [mysqld]
    slow_query_log=1
    slow_query_log_file="/usr/local/mysql/local-slow.log"
    [mysqld]
    long_query_time=3  
    查询慢查询阀值
    show variables like "%long_query_time"  
    设置临时慢查询阀值
    set global long_query_time=5
    select sleep(4);
分析海量数据
    
    
    
    --查询超过阀值的sql:
    show global status like "%slow queries%"

存储过程
    delimiter $ 
    create function randingstring(n int) returns varchar(255)
    begin
        declare all_str varchar(100) default 'qwertyuioasdfghjklzxcvbnmQWRETYUIOPASDFGHJKLZXCVBNM';
        declare return_str varchar(255) defult '';
        declare i int default 0;
        while i<n
        do
            set return_str=contact(return_str,substring(all_atr,FLOOR(i+rand()*52),1)); 
            set i=i+1;
        endwhile;
        return return_str;
        
    end $
    
    
存储过程/存储函数因为慢查询报错  开启log_bin_trust_fucntion_creators
show variables like "%log_bin_trust_fucntion_creators%"
    临时解决:set log_bin_trust_fucntion_creators=1
    永久解决:
        [mysqld]
        log_bin_trust_fucntion_creators=1
        
产生随机整数
    create function rand_num() returns int()
        declare i int default 0;
        set i=FLOOR(rand()*100);
            return i;
    end $

存储过程插入数据
    create procedure insert_emp(in eid_start int(10),in data_times int(10))
    begin
        declare i int default 0;
        set auto_commit=0;
        repeat
            insert into  emp values(eid_start+i,randingstring(5),'others',rand_num())
            set i=i+1;
            until data_times  --until后面不需要分号(;)结束
        end repeat;
        commit;
    end;
    
    delimiter ;


分析海量数据:profiles
    show profiles;
    set profiling=on;
    show variables like '%profiling%'
    缺点不够精确
    精确分析 sql 
        show profile all for query 2;2是上一步查询的query_id
        show profile cpu,block for query;
    全局查询日志
        记录开启之后的全部sql语句
        show variables like '%general_log%'
        set global general_log=1 ; 开启全局日志
        set global log_out='table';--设置将所有数据记录在表中
        set global log_out='file';--设置将所有数据记录在文件中
        set global general_log_file='/tmp/general.log';


锁机制
    解决因资源共享造成的并发问题 
    操作类型
        a 读锁(共享锁) 多个操作可以同时进行,互不干扰
        b 写锁(互斥锁) 读写过程中,不能进行读写操作
    操作范围
        a.表锁:一次性对一张表加锁 开销少加锁快;无死锁  容易发生锁冲突,并发度低  
        b.行锁:一次性对一条数据加锁  开销大加锁慢  容易产生死锁 ;锁的范围小,不易发生锁冲突,并发度高
        c.页锁:
        
表锁
增加锁
    lock table 表1 read/write,表2 read/write,....
    
    查看加锁的表 
        show open tables
        加读锁:
            session0 :
                开启一个回话 对a表加了read锁,则该会话可以对a表进行读操作,不能进行写操作,且该会话不能对其他表进行读写操作
            session1:
                可以进行读操作,写操作会等待其他session 释放锁
                会话0给a表加了锁 其他回话的操作 可以对a表
            
        释放锁
            unlock tables;  通过事务解锁
        加写锁:
            lock table 表1 write
            会话1
                当前会话可以对加了锁的表进行读写操作  但是不能操作其他的表
                
            会话2
                对会话一进行操作的前提是  会话一释放了当前的锁 
                    
        分析表锁定
            show open tables; 1代表加锁
            分析表锁定的严重程度:show status like 'table%'
            table_locks_immediate:可能获取到的锁
            table_locks_waited;需要等待的表锁数(如果该值越大则说明锁竞争越大)
            table_locks_immediate/table_locks_waited>5000 建议采用innnodb  否则用myisam
            
            

    行锁  set commit=0;/strat transaction;begin
    
        a.如果会话x对某条数据进行dml操作,则其他 会话需要等会话x完成会话才能进行dml操作
        行锁通过事务解锁
        注意事项
        1 没有索引 则行锁转为表锁
            发生类型转换索引失效
        b 行锁的特殊情况 间隙锁   值在范围内却不存在
            mysql会自动对间隙加锁
            如果有where 则实际加锁的是where后面的范围  不是实际的值
            优点:并发能力强 效率高
            缺点:比表锁性能损耗大
            建议:高并发用innodb 否则用myisam
        行锁分析
            show status like "%inndb_row_lock%"
            innodb_row_lock_current_wait;当前正在对等待锁的数量
            innodb_row_lock_time;等待总时长
            innodb_row_lock_time_avg;
            innodb_row_lock_time_max;
            innodb_row_lock_waits;等待的次数
        通过 for update 对查询语句进行加锁 
集群
    1 负载均衡
    2 失败迁移
    主从同步
    a.master 将改变的数据 记录在本地的二进制文件中(binary log)
    b.slave将master 的binary拷贝到relay log(中继日志文件)中
    c.中继日志事件,将数据写入到自己的数据库中
    
    mysql复制是异步的 串行化的 是有一定延时的
    权限
        grant all privileges on *.* to 'a'@'%' indentified by 'root' with grant option 
        
    配置
        主计算机
        [mysqld]
        servie-id=1
        log-bin="d:/mysql-bin"
        错误记录文件
        log-error="d:/mysql-error"
        binlog-ingore-db=mysql  

        主库授权
        grant replication slave,reload,super on *.* to 'a'@'192.168.2.%' indentified    by root;
        
        
        查看主计算机状态
         show master status
    
        
        从计算机(slave)
        [mysqld]
        servie-id=2
        log-bin=mysql-bin
        replicate-do-db=test
    
        授权
        change mster to 
        master_host='192.168.2.3',
        master_user='root',
        master_password='root',
        master_port=3306,
        master_log_file='msyql-bin.00001',
        master_log-pos=107;
         
        如果报错
        stop slave
        
        
        开启主从
            start slave;
            show slave status \G
            show variables like "%server_id%"
        
    server-id 设置无效
        set global server-id=2
        
        
    grep "password" /var/log/mysqld.log 

mysql慢
1.一切都慢
a.检查硬件问题
b.改善硬件环境
c.将数据迁移到独立磁盘上
d.操作系统配置是否正确
e.考虑将有些应用迁移到其他服务器上
f.考虑向外拓展的复制
g.优化服务器性能
2.慢查询
a.规范化数据库模式
b.使用explain识别丢失或不正确的索引
c.使用benchmark()函数部分测试
d.考虑重写查询
e.对标准查询使用视图
f.启用query cache
3.慢应用
a.开启query cache
b.考虑并优化存储引擎
c.确认是否服务器或操作系统问题
d.定义应用程序极准,并将它与已知基准比较
e.检查内部查询,并最大化它们的性能
f.分而治之,一次只检查一部分
g.使用划分来分散数据
h.检查各个分区的索引

MYISAM和Innodb区别
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)

四种隔离级别的比较

读数据一致性及并发副作用

隔离级别

读数据一致性

脏读

不可重复读

幻读

为提交读(read uncommitted)
最低级别，不读物理上顺坏的数据

是

是

是

已提交读(read committed)
语句级

否

是

是

可重复读(Repeatable red)
事务级

否

否

是

可序列化(Serializable)
最高级别，事务级

否

否

否

查看mysql的默认事务隔离级别“show global variables like ‘tx_isolation’; ”

Innodb的行锁模式有以下几种：共享锁，排他锁，意向共享锁(表锁)，意向排他锁(表锁)，间隙锁。

注意：当语句没有使用索引，innodb不能确定操作的行，这个时候就使用的意向锁，也就是表锁

关于死锁：

什么是死锁？当两个事务都需要获得对方持有的排他锁才能完成事务，这样就导致了循环锁等待，也就是常见的死锁类型。

解决死锁的方法：

1、 数据库参数

2、 应用中尽量约定程序读取表的顺序一样

3、 应用中处理一个表时，尽量对处理的顺序排序

4、 调整事务隔离级别（避免两个事务同时操作一行不存在的数据，容易发生死锁）

二、数据库文件差异
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、可以用 show create table tablename 命令看表的引擎类型。

2、对不支持事务的表做start/commit操作没有任何效果，在执行commit前已经提交。

3、可以执行以下命令来切换非事务表到事务（数据不会丢失），innodb表比myisam表更安全：alter table tablename type=innodb;或者使用 alter table tablename engine = innodb;

4、默认innodb是开启自动提交的，如果你按照myisam的使用方法来编写代码页不会存在错误，只是性能会很低。如何在编写代码时候提高数据库性能呢？

a、尽量将多个语句绑到一个事务中，进行提交，避免多次提交导致的数据库开销。

b、在一个事务获得排他锁或者意向排他锁以后，如果后面还有需要处理的sql语句，在这两条或者多条sql语句之间程序应尽量少的进行逻辑运算和处理，减少锁的时间。

c、尽量避免死锁

d、sql语句如果有where子句一定要使用索引，尽量避免获取意向排他锁。

f、针对我们自己的数据库环境，日志系统是直插入，不修改的，所以我们使用混合引擎方式，ZION_LOG_DB照旧使用myisam存储引擎，只有ZION_GAME_DB，ZION_LOGIN_DB，DAUM_BILLING使用Innodb引擎。

五、究竟该怎么选择

下面先让我们回答一些问题：

◆你的数据库有外键吗？

◆你需要事务支持吗？

◆你需要全文索引吗？

◆你经常使用什么样的查询模式？

◆你的数据有多大？

myisam只有索引缓存
innodb不分索引文件数据文件 innodb buffer
myisam只能管理索引，在索引数据大于分配的资源时，会由操作系统来cache；数据文件依赖于操作系统的cache。innodb不管是索引还是数据，都是自己来管理

思考上面这些问题可以让你找到合适的方向，但那并不是绝对的。如果你需要事务处理或是外键，那么InnoDB 可能是比较好的方式。如果你需要全文索引，那么通常来说 MyISAM是好的选择，因为这是系统内建的，然而，我们其实并不会经常地去测试两百万行记录。所以，就算是慢一点，我们可以通过使用Sphinx从InnoDB中获得全文索引。

数据的大小，是一个影响你选择什么样存储引擎的重要因素，大尺寸的数据集趋向于选择InnoDB方式，因为其支持事务处理和故障恢复。数据库的在小决定了故障恢复的时间长短，InnoDB可以利用事务日志进行数据恢复，这会比较快。而MyISAM可能会需要几个小时甚至几天来干这些事，InnoDB只需要几分钟。

操作数据库表的习惯可能也会是一个对性能影响很大的因素。比如： COUNT() 在 MyISAM 表中会非常快，而在InnoDB 表下可能会很痛苦。而主键查询则在InnoDB下会相当相当的快，但需要小心的是如果我们的主键太长了也会导致性能问题。大批的inserts 语句在 MyISAM下会快一些，但是updates 在InnoDB下会更快一些——尤其在并发量大的时候。

所以，到底你检使用哪一个呢？根据经验来看，如果是一些小型的应用或项目，那么MyISAM 也许会更适合。当然，在大型的环境下使用 MyISAM 也会有很大成功的时候，但却不总是这样的。如果你正在计划使用一个超大数据量的项目，而且需要事务处理或外键支持，那么你真的应该直接使用 InnoDB方式。但需要记住InnoDB 的表需要更多的内存和存储，转换100GB 的MyISAM 表到InnoDB 表可能会让你有非常坏的体验。

对于支持事务的InnoDB类型的表，影响速度的主要原因是AUTOCOMMIT默认设置是打开的，而且程序没有显式调用BEGIN 开始事务，导致每插入一条都自动Commit，严重影响了速度。可以在执行sql前调用begin，多条sql形成一个事务（即使autocommit打开也可以），将大大提高性能。

InnoDB

InnoDB 给 MySQL 提供了具有事务(commit)、回滚(rollback)和崩溃修复能力 (crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。 InnoDB 提供了行锁(locking on row level)，提供与 Oracle 类型一致的不加锁读取(non- locking read in SELECTs)。这些特性均提高了多用户并发操作的性能表现。在InnoDB表中不需要扩大锁定 (lock escalation)，因为 InnoDB 的列锁定(row level locks)适宜非常小的空间。 InnoDB 是 MySQL 上第一个提供外键约束(FOREIGN KEY constraints)的表引擎。

InnoDB 的设计目标是处理大容量数据库系统，它的 CPU 利用率是其它基于磁盘的关系数据库引擎所不能比的。在技术上，InnoDB 是一套放在 MySQL 后台的完整数据库系统，InnoDB 在主内存中建立其专用的缓冲池用于高速缓冲数据和索引。 InnoDB 把数据和索引存放在表空间里，可能包含多个文件，这与其它的不一样，举例来说，在 MyISAM 中，表被存放在单独的文件中。InnoDB 表的大小只受限于操作系统的文件大小，一般为 2 GB。
InnoDB所有的表都保存在同一个数据文件 ibdata1 中（也可能是多个文件，或者是独立的表空间文件）,相对来说比较不好备份，免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump。

MyISAM
MyISAM 是MySQL缺省存贮引擎 .
每张MyISAM 表被存放在三个文件 。frm 文件存放表格定义。 数据文件是MYD (MYData) 。 索引文件是 MYI (MYIndex) 引伸。
因为MyISAM相对简单所以在效率上要优于InnoDB..小型应用使用MyISAM是不错的选择.
MyISAM表是保存成文件的形式,在跨平台的数据转移中使用MyISAM存储会省去不少的麻烦

以下是一些细节和具体实现的差别：

1.InnoDB不支持FULLTEXT类型的索引。
2.InnoDB 中不保存表的具体行数，也就是说，执行select count() from table时，InnoDB要扫描一遍整个表来计算有多少行，但是MyISAM只要简单的读出保存好的行数即可。注意的是，当count()语句包含 where条件时，两种表的操作是一样的。
3.对于AUTO_INCREMENT类型的字段，InnoDB中必须包含只有该字段的索引，但是在MyISAM表中，可以和其他字段一起建立联合索引。
4.DELETE FROM table时，InnoDB不会重新建立表，而是一行一行的删除。
5.LOAD TABLE FROM MASTER操作对InnoDB是不起作用的，解决方法是首先把InnoDB表改成MyISAM表，导入数据后再改成InnoDB表，但是对于使用的额外的InnoDB特性（例如外键）的表不适用。

另外，InnoDB表的行锁也不是绝对的，如果在执行一个SQL语句时MySQL不能确定要扫描的范围，InnoDB表同样会锁全表，例如 update table set num=1 where name like “%aaa%”

任何一种表都不是万能的，只用恰当的针对业务类型来选择合适的表类型，才能最大的发挥MySQL的性能优势。

六、重复地总结一遍

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

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

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

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

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

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

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

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

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

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

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

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

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

“死锁”举例分析：

表Test：（ID,STATE,TIME） 主键索引：ID 非主键索引：STATE

当执行"UPDATE STATE =1011 WHERE STATE=1000" 语句的时候会锁定STATE索引，由于STATE 是非主键索引,所以Mysql还会去请求锁定ID索引

当另一个SQL语句与语句1几乎同时执行时：“UPDATE STATE=1010 WHERE ID=1” 对于语句2 Mysql会先锁定ID索引，由于语句2操作了STATE字段，所以Mysql还会请求锁定STATE索引。这时。彼此锁定着对方需要的索引，又都在等待对方释放锁定。所以出现了"死锁"的情况。

行级锁的优点：

有许多线程访问不同的行时，只存在少量的冲突。

回滚时只有少量的更改

可以长时间锁定单一的行

行级锁缺点：

相对于页级锁和表级锁来说占用了更多的内存

当表的大部分行在使用时，比页级锁和表级锁慢，因为你必须获得更多的锁

当在大部分数据上经常使用GROUP BY操作，肯定会比表级锁和页级锁慢。

页级锁：表级锁速度快，但是冲突多；行级锁速度慢，但冲突少；页级锁就是他俩折中的，一次锁定相邻的一组记录。

3、MyISAM引擎不支持外键，InnoDB支持外键

4、MyISAM引擎的表在大量高并发的读写下会经常出现表损坏的情况

我们以前做的项目就遇到这个问题，表的INSERT 和 UPDATE操作很频繁，原来用的MyISAM引擎，导致表隔三差五就损坏，后来更换成了InnoDB引擎。

其他容易导致表损坏原因：

服务器突然断电导致数据文件损坏，强制关机（mysqld未关闭情况下）导致表损坏

mysqld进程在写入操作的时候被杀掉

磁盘故障

表损坏常见症状：

查询表不能返回数据或返回部分数据

打开表失败： Can’t open file: ‘×××.MYI’ (errno: 145) 。

Error: Table 'p' is marked as crashed and should be repaired 。

Incorrect key file for table: '...'. Try to repair it

Mysql表的恢复：

对于MyISAM表的恢复：

可以使用Mysql自带的myisamchk工具： myisamchk -r tablename 或者 myisamchk -o tablename（比前面的更保险） 对表进行修复

5、对于count()查询来说MyISAM更有优势

因为MyISAM存储了表中的行数记录，执行SELECT COUNT() 的时候可以直接获取到结果，而InnoDB需要扫描全部数据后得到结果。

但是注意一点：对于带有WHERE 条件的 SELECT COUNT()语句两种引擎的表执行过程是一样的，都需要扫描全部数据后得到结果

6、 InnoDB是为处理巨大数据量时的最大性能设计，它的CPU效率可能是任何其它基于磁盘的关系数据库引擎所不能匹敌的。

7、MyISAM支持全文索引（FULLTEXT），InnoDB不支持

8、MyISAM引擎的表的查询、更新、插入的效率要比InnoDB高

网上截取了前辈们测试结论：

测试方法：连续提交10个query， 表记录总数：38万 ， 时间单位 s

    引擎类型                    MyISAM                InnoDB              性能相差

    count                      0.0008357            3.0163                3609

    查询主键                  0.005708              0.1574                27.57

    查询非主键                  24.01                  80.37                3.348

    更新主键                  0.008124            0.8183                100.7

    更新非主键                0.004141            0.02625              6.338

    插入                        0.004188            0.3694                88.21


（1）加了索引以后，对于MyISAM查询可以加快：4 206.09733倍，对InnoDB查询加快510.72921倍，同时对MyISAM更新速度减慢为原来的1/2，InnoDB的更

新速度减慢为原来的1/30。要看情况决定是否要加索引，比如不查询的log表，不要做任何的索引。

（2）如果你的数据量是百万级别的，并且没有任何的事务处理，那么用MyISAM是性能最好的选择。

（3）InnoDB表的大小更加的大，用MyISAM可省很多的硬盘空间。

    在我们测试的这个38w的表中，表占用空间的情况如下：
        引擎类型                    MyISAM              InnoDB
        数据                      53,924 KB          58,976 KB
        索引                      13,640 KB          21,072 KB
        占用总空间              67,564 KB          80,048 KB

    另外一个176W万记录的表， 表占用空间的情况如下：

        引擎类型                MyIsam              InnorDB
        数据                  56,166 KB          90,736 KB
        索引                  67,103 KB          88,848 KB
        占用总空间        123,269 KB        179,584 KB

七、性能对比

测试的版本是mysql Ver 14.14 Distrib 5.1.49, for debian-linux-gnu (i686)，使用的是Innodb plugin 1.0.8（官方称比built-in版本性能更好）和默认的MyISAM。

测试机器是笔记本，配置如下：Intel 酷睿2双核 P8600，2G*2 DDR3 1066内存，320G硬盘5400转。

测试一：数据插入性能测试，这里我分别对innodb_flush_log_at_trx_commit参数打开和关闭都测了了一下，每次测试都是运行40s，表中数字都是实际插入条数。

                   MyISAM                 Innodb (打开)      Innodb (关闭)

单线程，逐个插入 120000 60000 60000

4线程，逐个插入 400004 200004 40000*4

单线程，批量100条/次插入 3600100 800100 3000*100

单线程，批量200条/次插入 1800200 400200 1600*200

可以发现批量插入的性能远高于单条插入，但是一次批量的大小对性能影响不大。每条记录是否都刷新日志的参数对innodb性能的影响巨大。总体上来说，MyISAM性能更优一点。这里有一点需要注意，在插入测试过程中，我对系统资源进行了监控，发现MyISAM对系统资源占用很低，但是Innodb对磁盘占用却很高，应该是对事务控制多了很多需要记录的日志。

测试二：数据读取性能测试。每次随机读取1000条记录，反复进行读取。

                    MyISAM        Innodb

单线程，200次读取 5.7s 16.7s

4线程，200次读取 12s 40.8s

可以看出MyISAM的读取性能非常恐怖，性能差距在3倍的样子。

以上两个测试发现MyISAM在无事务的需求下几乎完胜，但是要知道它是表锁，Innodb是行锁，那么在并发读写同时存在的情况下，那结果会是怎么样呢？！

测试三：两个线程并发写入，2个线程并发读取。

                   MyISAM                                 Innodb

逐个插入 写入40s：100002 读取200次2：14s 写入40s：600002 读取200次2：50s

批量100条/次插入 写入40s：10001002 读取200次2：10s 写入40s：15001002 读取200次2：50s

这下立刻显示出Innodb在并发情况下强劲的性能，几乎没有什么性能衰减。而MyISAM单条插入速度变得非常慢，批量插入也下降了40%性能。

总结一下，在写多读少的应用中还是Innodb插入性能更稳定，在并发情况下也能基本，如果是对读取速度要求比较快的应用还是选MyISAM。