高性能MySQL（2）

MySQL常见面试总结

1. MyISAM和InnoDB的区别
   是否支持行级锁：MyISAM只有表级锁，InnoDB支持行级锁和表级锁，默认是表级锁。
   是否支持事务和崩溃后的安全恢复：MyISAM不支持事务，InnoDB支持事务，外部键等，具有事务、回滚和崩溃修复能力的事务安全型表。
   是否支持外键：MyISAM不支持，InnoDB支持
   是否支持MVCC：InnoDB支持。

2. 优化
   Performance_schema:实现server端各个阶段的资源监控
   profile
   show profile;显示单个操作各项花费的时间
   set profiling=1;打开查看每个阶段所花费的时间
   show profiles;显示所有操作的各项花费的时间
   show profile for query number（number表示具体的行号）：打开profiles中某一个操作所花费的时间

3. Mysql中的锁机制和InnoDB锁算法

   按照锁的粒度分类
   表级锁：锁住整张表，资源消耗少，枷锁快，不会出现死锁。触发锁的冲突概率高，并发度低。
   行级锁：开销大，加锁慢，会出现死锁。只对当前操作的行进行加锁。可以减少操作数据库的冲突，并发度高。
   InnoDB支持的行级锁包括以下几种
   RecordLock：对索引加锁，锁定符合条件的行。其他事务不能删除或者修改加锁项。
   GapLock：对索引项之间的“间隙”加锁，锁定记录的范围，不包括索引项本身。用于解决幻读问题。
   Next-keyLock：锁定一个范围，包含记录本身。即Record Lock和Gap Lock的结合。可解决幻读问题。

   按照是否可以写分类（行级锁）
   共享锁：又称读锁，只要加了共享锁，其他事务只能加共享锁，直到锁的释放。获取共享锁的事务只能读数据，不能修改数据。
   排他锁：又称写锁，只要加了排他锁，其他事务不能加任何类型的锁，直到锁的释放。获取排他锁的事务既能读数据，又能修改数据。

   InnoDB另外两个表级锁。
   意向锁的作用是当一个事务在需要获取资源锁定的时候，如果遇到自己需要的资源已经被排他锁占用的时候，该事务可以需要锁定行的表上面添加一个合适的意向锁。
   如果需要共享锁，那么就在表上面添加一个意向共享锁。
   而如果自己需要的是某行（或者某些行）上面添加一个排他锁的话，则先在表上面添加一个意向排他锁。意向共享锁可以同时并存多个，但是意向排他锁同时只能有一个存在。
   IS：意向共享锁
   表示事务准备给数据行记入共享锁，事务在一个数据行加共享锁前必须先取得该表的IS锁。
   IX：意向排他锁
   表示事务准备给数据行加入排他锁，事务在一个数据行加排他锁前必须先取得该表的IX锁。

4. 大表优化：
   当Mysql单表记录数过大时，数据库的CURD性能就会明显下降，常见的优化方式如下
   限定数据范围
   禁止不带任何限制条件的数据范围查询。比如：在用户查询历史订单的时候，控制在一个月范围内。

   读写分离
   经典的数据库拆分方案，主库负责写，从库负责读。

   垂直分区
   简单来说就是指数据表列的拆分，把一张列比较多的表拆分为多张表。
   优点：使数据列变小，查询时减少读取的block数，减少I/O次数。此外，可以简化表的结构，易于维护。
   缺点：主键会出现冗余，也会让事务变得更加复杂。
   

   水平分区
   简单来说就是数据行的拆，表的行数超过200万就会变慢。可以把一张表拆分多张表来存放。⽔平拆分可以⽀撑⾮常⼤的数据量。
   特点：支持非常大的数据量存储，但分片事务难以解决。

5. 一条SQL语句执行很慢的原因有哪些
入口处—》可以参考此篇文章
偶尔慢
数据库在刷新脏页，例如redo log写满了需要同步到磁盘
脏页
当内存数据页跟磁盘数据页内容不一致
刷脏页有下面4种场景
（1） redolog写满了：redo log 里的容量是有限的，如果数据库一直很忙，更新又很频繁，这个时候 redo log 很快就会被写满了，这个时候就没办法等到空闲的时候再把数据同步到磁盘的，只能暂停其他操作，全身心来把数据同步到磁盘中去的，而这个时候，就会导致我们平时正常的SQL语句突然执行的很慢**，所以说，数据库在在同步数据到磁盘的时候，就有可能导致我们的SQL语句执行的很慢了。
（2）内存不够用了：如果一次查询较多的数据，恰好碰到所查数据页不在内存中时，需要申请内存，而此时恰好内存不足的时候就需要淘汰一部分内存数据页，如果是干净页，就直接释放，如果恰好是脏页就需要刷脏页。
（3） MySQL 认为系统“空闲”的时候：这时系统没什么压力。
（4） MySQL 正常关闭的时候：这时候，MySQL 会把内存的脏页都 flush 到磁盘上，这样下次 MySQL 启动的时候，就可以直接从磁盘上读数据，启动速度会很快。
干净页
内存数据写入到磁盘后，内存和磁盘上的数据页的内容一致。
执行的时候遇到了锁，比如表锁，行锁
一直慢
（1）没有用上索引
（2）数据库选错了索引
（3）由于统计的失误，导致系统没有走索引，而是走了全表扫描，而这，也是导致我们 SQL 语句执行的很慢的原因。

6. 索引设计原则
   一般选择where子句后，或连接子句中的指定的列
   使用唯一索引
   使用短索引（一列），例如char(200)，如果前10或20多数值是唯一的，那么就不需要对整个列进行索引
   利用最左前缀（多列），多列索引可起几个索引的作用，因为可利用索引中最左边的列集来匹配行，这样的列集称为最左前缀。
   不要过度使用索引。索引需要占用额外的磁盘空间，并降低写操作的性能。在修改表的时候，索引必须进行更新，有时候可能需要重构。因此，索引越多，所花的时间越长。

7. 常用操作数据库记录
   查询最后一行记录 select * from table order by id desc limit 1;
   查看当前表的索引 show index from table;
   随机获取一条记录 select * from table order by rand() limit 1;
   删除表的方式
   drop table table_name. 同时删除表的数据及结构，并且释放空间，效率高。
   delete from table_name. 仅删除表数据，支持条件过滤，回滚，日志记录。效率低。
   truncate table table_name.仅删除所有数据，不支持条件过滤，日志记录，效率高于delete.

   查看事务的隔离
   show variables like ‘transaction_isolation’

   自增主键设置：NOT NULL PRIMARY KEY AUTO_INCREMENT
   全局锁：给整个数据库加锁Flush tables with read lock (FTWRL)