mysql

MyISAM和InnoDB区别

在MySQL 5.5之前，MyISAM是mysql的默认数据库引擎，之后的版本是InnoDB。

InnoDB

• 支持事务
• 有行级锁定和外键约束
• 不支持FULLTEXT类型的索引
• 没有保存表的行数
• 叶子结点是聚集索引

MyISAM

• 不支持事务
• 不支持行锁和外键，因此当INSERT或UPDATE数据时即写操作需要锁定整个表，效率便会低一些
• 叶子结点是非聚集索引

区别

• 事务
  • InnoDB支持，MyISAM不支持
• 行数
  • InnoDB没有保存表的行数，MyISAM保存了表的行数，可以直接读取
• 索引存储
  • InnoDB是聚集索引，MyISAM是非聚集索引
• 外键
  • InnoDB支持，MyISAM不支持
• 锁
  • InnoDB支持行锁，表锁。行锁可以提高多用户并发操作，但InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的
  • MyISAM支持表锁

mysql中的锁

读写锁

• 读锁会阻塞写，但不会阻塞读
• 写锁会阻塞读和写

行页表锁

• 对表中的记录加锁，叫做记录锁，又称行锁，行锁只锁定一行，偏写
• 表锁会锁定整个表，偏读
• 页锁在行锁和表锁之间

InnoDB加锁

• 对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及及数据集加排他锁
• 对于普通SELECT语句，InnoDB不会加任何锁
• 事务可以通过以下语句显示给记录集加共享锁或排锁。

//共享锁（Ｓ）
SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE
//排他锁（X）
SELECT * FROM table_name WHERE ... FOR UPDATE

乐观锁和悲观锁

悲观锁

先获取锁，再进行业务操作，悲观的认为所有的操作均会导致并发安全问题，因此要先确保获取锁成功再进行业务操作。select ... for update 实现悲观锁

乐观锁

先进行业务操作，再获取锁，一般的做法是在需要锁的数据上增加一个版本号，或者时间戳，提交更新时，时间戳或版本号必须大于当前版本才提交更新

SELECT data AS old_data, version AS old_version FROM …;
//根据获取的数据进行业务操作，得到new_data和new_version
UPDATE SET data = new_data, version = new_version WHERE version = old_version
if (updated row > 0) {
    // 乐观锁获取成功，操作完成
} else {
    // 乐观锁获取失败，回滚并重试
}

使用场景

一般情况下，读多写少更适合用乐观锁，读少写多更适合用悲观锁。乐观锁在不发生取锁失败的情况下开销比悲观锁小，但是一旦发生失败回滚开销则比较大，因此适合用在取锁失败概率比较小的场景，可以提升系统并发性能。

行锁怎么实现

• 显示系统上行锁的争夺情况show status like 'innodb_row_lock%';
• 只有通过索引条件检索数据，InnoDB才使用行级锁，否则，InnoDB将使用表锁

//显式锁定
set auoto_commit = 0;
begin;
//加索引
 ALTER TABLE account ADD INDEX index_user_id (user_id);
//检索索引
SELECT * FROM account WHERE user_id = 1 FOR UPDATE
commit;

间隙锁

概念

• 当我们使用范围条件而不是相等条件查询数据时，InnoDB会把符合条件的数据记录索引项都加锁，此时键值在条件范围内，但并不存在的数据记录称为间隙。
• 比如检索1-3之间的数据，但表中并没有数据2，此时InnoDB会对这个间隙也加锁
• 若范围检索不提交，另一个对间隙进行操作（比如添加2）会被阻塞，无法插入数据，导致性能变差
• 在使用范围条件检索并锁定记录时，InnoDB这种加锁机制会阻塞符合条件范围内键值的并发插入，这往往会造成严重的锁等待

作用

• 间隙锁的主要作用是为了防止出现幻读

解除间隙锁

• 修改系统参数innodb_locks_unsafe_for_binlog=on就可以关闭改间隙锁机制，该值默认为off
• 修改事务隔离级别为read-committed也可以避免间隙锁

聚集索引和非聚集索引

聚集索引和非聚集索引

聚集索引和非聚集索引的根本区别是表记录的排列顺序和与索引的排列顺序是否一致。聚集索引的逻辑顺序和物理顺序相同，非聚集索引则不同。

B+树叶子结点可以存哪些数据

• 聚集索引的叶节点就是数据节点，而非聚集索引的叶节点仍然是索引节点，只不过其包含一个指向对应数据块的指针
• 叶子结点存储的是主键整行数据的是聚簇索引，叶子结点存储的仅仅是主键，然后通过指针指向具体信息的是非聚簇索引。

优化

索引优化

• 全值匹配我最爱
  • 创建索引后，尽量在where筛选条件中使用索引列
• 最左前缀要遵守
  • 建立索引，将最常用的放在最左侧
• 带头大哥不能死
  • 查询从索引最左列开始 ，否则全部失效
• 中间兄弟不能断
  • 不能跳过索引中的列，否则部分失效
• 索引列上少计算
  • 不在索引列上做任何操作，比如计算或类型转换，有些VARCHAR不写引号，sql会默认转换，但索引失效
• 范围之后全失效
  • 用<或>时，当前索引不失效，范围条件右边的列失效
• LIKE百分写最右
  • LIKE百分号不要写在最左侧，否则索引失效
• 覆盖索引不写*
  • 当查询索引列时，select后面不写*，而是写索引列
• 不等空值还有or
  • 尽量不使用！=或<>符号，不使用is NULL或is not NULL，少用or，否则索引失效
• VARCHAR引号不能丢
  • 字符串不用单引号会失效，相当于索引列做自动类型转换

查询优化

• 慢查询步骤和过程
  • 开启慢查询
    • set slow_query_log = 1慢查询开启状态。
    • slow_query_log_file 慢查询日志存放的位置（这个目录需要MySQL的运行帐号的可写权限，一般设置为MySQL的数据存放目录）。
  • 设置超时阈值，比如5s或10s，超时的sql语句会放在慢日志中
    • long_query_time 查询超过多少秒才记录。
  • cat查看慢日志
  • 用explain分析慢sql语句
• explain优化，实际上模拟优化器执行sql语句，查看mysql如何执行你的sql语句
  • table：显示这一行的数据是关于哪张表的
  • type：这是重要的列，显示连接使用了何种类型。从最好到最差的连接类型为const、eq_ref、ref、range、index和ALL，一般来说到range不错，最好到ref
    • all:full table scan ;MySQL将遍历全表以找到匹配的行；
    • index: index scan; index 和 all的区别在于index类型只遍历索引；
    • range：索引范围扫描，对索引的扫描开始于某一点，返回匹配值的行，常见与between ，等查询；
    • ref：非唯一性索引扫描，返回匹配某个单独值的所有行，常见于使用非唯一索引即唯一索引的非唯一前缀进行查找；
    • eq_ref：唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常用于主键或者唯一索引扫描；
    • const，system：当MySQL对某查询某部分进行优化，并转为一个常量时，使用这些访问类型。如果将主键置于where列表中，MySQL就能将该查询转化为一个常量。
  • possible_keys：显示可能应用在这张表中的索引。如果为空，没有可能的索引。可以为相关的域从WHERE语句中选择一个合适的语句
  • key： 实际使用的索引。如果为NULL，则没有使用索引。很少的情况下，MySQL会选择优化不足的索引。这种情况下，可以在SELECT语句中使用USE INDEX（indexname）来强制使用一个索引或者用IGNORE INDEX（indexname）来强制MySQL忽略索引
  • key_len：使用的索引的长度。在不损失精确性的情况下，长度越短越好
  • rows：MySQL认为必须检查的用来返回请求数据的行数
  • Extra：关于MySQL如何解析查询的额外信息
    • Using temporary和Using filesort，意思MySQL根本不能使用索引，结果是检索会很慢。
    • Using index表示不错的信息，使用了覆盖索引
• 根据explain的结果，进行索引优化