关于mysql的总结

MySQL储存引擎

MySQL储存引擎主要用到比较频繁有MyISAM与InnoDB，他们之间主要的区别：

如果要提供提交、回滚和恢复的事务安全（ACID 兼容）能力，并要求实现并发控制，InnoDB 是一个很好的选择。
如果数据表主要用来插入和查询记录，则 MyISAM 引擎提供较高的处理效率。

• MyISAM不支持事务，而InnoDB支持事务（ACID）
• MyISAM支持表锁，而InnoDb支持行锁、表锁，锁的粒度更小，并发能力更高（InnoDB的行销是基于索引实现的，如果不通过索引访问数据，InnoDB会使用表锁。可以通过explain执行计划查询索引是否被实际使用。加锁的方式：自动加锁。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁；对于普通SELECT语句，InnoDB不会加任何锁）
• MyISAM是非聚簇索引，所以需要查询两次，而InnoDB是聚簇索引，所有信息都储存在主键索引的叶子节点下，只需要查询一次。

什么是事务：就是一组操作，要么全部执行，要么全部失败，不能出现一个成功，一个失败的情况。

原子性（Atomicity）：事务是一个原子操作单元。在当时原子是不可分割的最小元素，其对数据的修改，要么全部成功，要么全部都不成功。

一致性（Consistent）：事务开始到结束的时间段内，数据都必须保持一致状态。

隔离性（Isolation）：数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的”独立”环境执行。

持久性（Durable）：事务完成后，它对于数据的修改是永久性的，即使出现系统故障也能够保持。

在高并发的情况下，如果不做任何的控制，一个事务的操作就可能影响到另一个事务，从而导致事务并发问题，由此引发出脏读、幻读、不可重复读。

• 脏读：一个事务更新了一份数据，另一个事务过来读取到了这个数据，但是某些原因，前一个事务回滚了，导致后一个事务读取到了脏数据。也就是一个事务读取到了另一个事务未提交的数据。
• 不可以重复读：一个事务两次读取到的数据不一样，可能两次查询的过程中插入另一个事务更新了原有的数据。
• 幻读：一个数据两次查询的数据集不一样。可能另一个数据插入了几条新的数据。

MySQL为了避免上面的并发事务问题，设置了四大隔离级别:

• 未提交读：默认不做任何操作，可能回导致脏读、幻读、不可重复读
• 已提交读：允许事务只能读取到已提交的数据，可以阻止脏读，但是不能组织幻读和不可重复读。
• 可重复读：对于同一字段的多次读取结果都是一致的，除非被本身事务所修改，可以阻止脏读和不可重复读
• 串行化：最高级别，所有事务依次执行，互不干扰，可以防止脏读、幻读、不可重复读

注意：

 

从锁的粒度来划分

 

主从复制

原理：主库进行操作后产生binlog二进制日志文件，推送给从库，从库在解析，执行sql，达到和主库数据一致。

作用：主库出现问题，切换到从库。可以主库写，从库读，实现读写分离

 

索引

• MySQL默认的级别是可重复读
• 事务的隔离机制的实现是锁和并发调度，其中MySQL的并发调度是MVVC(多版本并发控制)每条数据行后面都会有着一个事务ID和删除事务ID，每次查询只会查询小于等于当前事务id，保证事务开始前就已经存在，删除版本要么未定义，要么必须大于当前版本号，保证当前行未被删除。

• 锁

• 从锁的类别上划分

• 共享锁：又叫读锁，可以保证多个线程同时读操作
• 排他锁：又叫写锁，在用户进行写操作的时候，加上排他锁，保证某一时刻只能一个线程获取，其他线程只能等到持有线程释放之后才能获得锁。
• 行锁：是MySQL中粒度最小的一种锁，只针对当前行进行加锁，虽然粒度最小，但是开销也是最大的
• 表锁：粒度最大的一种锁，锁住整张表
• 页锁：介于行锁和表锁之间

数据结构：索引的数据结构与储存引擎有关，一般都Hash索引和B+树索引。Hash索引底层的数据结构就是哈希表，可以很快的查询到数据，但是不支持范围查找。大多数采用的是B+树索引。B+树主要有以下几个特性：

所有叶子节点保存数据，非叶子节点只保存索引，这样可以保存更多的索引，降低树的高度，较少磁盘IO 各个叶子节点之间采用双向链表进行连接，便于范围查找

聚簇索引与非聚簇索引

聚簇索引：将数据与索引放在一起，找到索引也就找到了数据

非聚簇索引：将数据与索引分开，叶子节点存储主键的值，需要二次查找

回表

非聚簇索引的叶子存储的是主键的值，需要再次从主键索引中根据主键找到对应的数据。，在InnoDB中，只有主键索引才是聚簇索引，建立的其他索引都是非聚簇索引，叶子节点中存放的都是主键的值，需要再次去主键索引中二次查找，也叫回表。

优化：可以使用覆盖索引，在建立非主键索引时，在索引的字段就包含了需要查找的字段，这样在查询的时候直接将索引保存 的字段返回，不需要再次查找主键索引。

联合索引

在MySQL中，多个字段可以同时建立索引，也叫做联合索引，在联合索引中，想要命中索引，就需要按照建立索引的顺序依次使用，否则无法命中索引。这也是“最左匹配原则”

 

 

 

索引失效

1、最好全值匹配； 
2、最左前缀法则：如果索引了多列，查询从索引的最左前列开始，且不能跳过索引中的列； 
3、不在索引列上做任何操作（计算，函数，类型转换），会导致索引时校而转向全表扫描； 
4、存储引擎不能使用索引中范围条件右边的列，即范围之后全失效； 
5、尽量使用覆盖索引，只访问索引的查询（索引列和查询列一致），减少selec *； 
6、MySQL在使用不等于的时候无法使用索引会导致全表扫描； 
7、is null，is not null 也无法使用索引； 
8、like 以通配符开头（’%aa‘）索引会失效，变成全表扫描； 
9、字符串不加单引号，索引失效； 
10、少用 or，用它来连接时候会索引失效
 

mysql优化

永远小表驱动大表 
in 和 exists 选择：

// 工作原理，先查B表数据，然后查A的 id 
select * from A where id in (select id from B)
// 工作原理，先查A表的id，然后查B表的id
select * from A a where exists (select 1 from B b where a.id = b.id )

 

// 结论：当B表的数据小于A表时候用 in；当A表数据小时候用 exists

 

1、观察，查看慢SQL情况； 
2、开启查询日志，设置阀值； 
3、explain 分析； 
4、show profile 查看执行细节和生命周期情况 
5、dba 进行参数调优

慢查询日志 
响应时间超过long_query_tine的SQL，被记录到慢查询日志中。

// SHOW VARIABLES LIKE '%slow_query_log%' ; 查看是否开启，默认没开启
// set global slow_quary_log = 1; 开启，仅本数据库有效，重启MySQL之后失效。

// show variables like '%long_query_time%'; 查看当前多少秒算慢
// set global long_query_time = 3; 设置慢的阙值时间

// show global status like '%Slow_queries%'; 查看当前数据库有多少条慢SQL