mysql整理

mysql常用的存储引擎mylasm和InnoDB

mylsam :

mysql5.5之前默认的存储引擎，MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。其采用索引文件与数据文件，索引文件只存放索引，叶子节点存放数据的物理地址。数据文件存放数据。其索引方式是非聚集的

包含三个文件 1.frm 保存表结构 2.myd 数据库文件 3.myi索引文件

特性：

1. 并发性与表级锁
2. 支持全文索引
3. 支持数据压缩

使用场景：

1. 非事务型应用（数据仓库，报表，日志数据）
2. 只读类应用，查询效率高
3. 空间类应用（地图，空间函数，坐标）

 

InnoDB

mysql5.5之后默认的存储引擎，InnoDB也使用B+Tree作为索引结构。但是它的主索引与数据都放在一个文件中。这种索引叫做聚集索引，因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形。

表空间：存储索引和数据

1. 独立表空间：tableName.idb 每个表一个文件
2. 系统表空间：ibdataX 所有表一个文件

mysql5.6之前默认是系统表空间，

系统表空间和独立表空间的区别：

• 系统表空间无法简短的收缩文件大小
• 独立表空间可以通过optimize table收缩系统文件
• 系统表空间会产生IO瓶颈
• 独立表空间可以同时向多个文件刷新数据

建议生产环境使用独立表空间

特性：

1. Innodb是一种事务性存储引擎，完全支持事务的ACID特性
2. Redo log和 Undo log
3. Innodb支持行级锁（并发程度更高）

锁

• MyISAM和MEMORY存储引擎采用的是表锁
• InnoDB存储引擎采用的是行级锁，同时也指出表锁，默认为行级锁

锁的分类:

1. 表级锁：开销小，加锁快，不会出现死锁，锁定的力度大，但发生锁冲突的概率最高，并发度最低
2. 行级锁：开销大，加锁慢，会出现死锁，锁定力度最小，发生锁冲突的概率最低，并发度最高
3. 页面锁：开销相对适中，会出现死锁，锁力度适中，并发度一般

从锁的角度来说：

表级锁更适合以查询为主，和只有少量按索引条件更新数据的应用

行级锁更适合有大量按索引条件并发更新少量不同数据，同时又有并发查询的应用，比如用到事务的系统

很难说那个锁更好，只能就具体应用特点来说哪种锁更合适

MyLSAM的表锁：

• 读操作会阻塞其他用户对同一表的读请求，可以修改这张表的数据，但更新或访问其他表都会报错
• 写操作会阻塞其他用户对同一个表的读写操作，并且只能CRUD当前表
• 读写操作会相互阻塞

InnoDB的行锁

行锁：

• 共享锁又称为读锁，当一个事务对某几行上读锁时，允许其他事务对这几行进行读，但不可以写，不允许上写锁，可以上读锁
• 排它锁又称为血锁，当一个事务对某几个上写锁时，不允许其他事务写，但可以读，不允许上任何锁

注意：

• 两个事务不能锁同一个索引
• insert delete uodate在事务中都会自动默认加上排它锁
• 行锁必须有索引才能实现，否则会自动上全表，那就不是行锁了
• 开启一个新事物的时候会解锁表

 

面试题：

• 系统允许一段时间，数据量已经很大，这时候系统升级，有张表A需要增加个字段，并发量百天晚上都很大，请问在， 修改表结构。
• 考的：修改表结构会导致表锁，数据量大修改数据时间很长会导致用户读锁，无法访问！

答：

1.  首先创建一个和你要执行alter操作的表一样的空的表结构
2. 执行我们赋予的表结构的修改，然后copy源表中的数据到新表中来
3. 在源表上创建一个触发器，在数据copy的过程中，将原表的更新数据的操作全部更新到新表中来。
4. copy完成后，用rename table新表代替原表

事务：

特性：

• 原子性，一个事务是一个不可分割的工作单位，事务中的诸多操作都要么都做，要么都不做
• 一致性，事务必须使数据库从一个一致性状态变到另一个一致性状态，一致性与原子性是密切相关的
• 隔离性，一个事务的执行不能被其他事务干扰，既一个事务内部的操作以及使用的数据库对并发的其他事务是完全隔离的，互不干扰
• 持久性，是指一个事务一旦提交，那么数据库中数据就应该是永久性的，即使系统崩溃以及提交的数据也不会丢

 

隔离性

• read uncommit（读未提交）：读不加锁，写加共享锁。会产生脏读、幻读。
• read commit（读已提交）：读加共享锁，写加排它锁，但不加间隙锁。间隙锁的主要作用是防止不可重复读，但会加大锁的范围。
• repeatable read（可重复读）：innodb默认，读加共享锁，写加间隙排它锁。会产生幻读，当事务A先前读取了数据，或者修改了全部数据，事务B还是可以insert数据提交，这时事务A就会发现莫名其妙多了一条之前没有的数据，这就是幻读，读用读锁，写用写锁，读锁和写锁互斥，这么做可以有效的避免幻读、不可重复读、脏读等问题，但会极大的降低数据库的并发能力。但是MySQL、ORACLE、PostgreSQL等成熟的数据库，出于性能考虑，都是使用了以乐观锁为理论基础的MVCC（多版本并发控制）来实现
• serialization（串行化）：会给整张表加锁，强一致，但是效率低

慢查询

慢查询日志，顾名思义，就是查询慢的日志，是指mysql记录所有执行超过long_query_time参数设定的时间阈值的SQL语句的日志。该日志能为SQL语句的优化带来很好的帮助。默认情况下，慢查询日志是关闭的，要使用慢查询日志功能，首先要开启慢查询日志功能。

慢查询工具：pt_query_digest，mysqldumpslow

隔离级别越高，越能保证数据的完整性和一致性，但是对并发性能的影响也越大，对于多数应用程序，可以优先考虑把数据库系统的隔离级别设为Read Committed，它能够避免脏读取，而且具有较好的并发性能。

 

索引

索引（Index）是帮助MySQL高效获取数据的数据结构。 索引的本质：索引是数据结构。

mysql默认存储引擎innodb只显式支持B-Tree( 从技术上来说是B+Tree)索引

索引分类

1. 普通索引：即一个索引只包含单个列，一个表可以有多个单列索引
2. 唯一索引：索引列的值必须唯一，但允许有空值
3. 复合索引：即一个索引包含多个列
4. 聚簇索引(聚集索引)：并不是一种单独的索引类型，而是一种数据存储方式。索引的键值逻辑顺序决定了表数据行的物理存储顺序。只要是索引是连续的，那么数据在存储介质上的存储位置也是连续的。
5. 非聚簇索引：不是聚簇索引，就是非聚簇索引       

执行计划

执行计划是什么    使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理你的SQL语句的。分析你的查询语句或是表结构的性能瓶颈

执行计划的作用：

1. 表的读取顺序
2. 数据读取操作的操作类型
3. 哪些索引可以使用
4. 哪些索引被实际使用
5. 表之间的引用
6. 每张表有多少行被优化器查询

 

字段解释

id：查询或操作表的顺序

三种情况

1. id相同，执行顺序由上至下
2. id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
3. id相同和不同，同时存在，id如果相同，可以认为是一组，从上往下顺序执行； 在所有组中，id值越大，优先级越高，越先执行

select_type：查询的类型

类型	描述
SIMPLE	简单的 select 查询,查询中不包含子查询或者UNION
PRIMARY	查询中若包含任何复杂的子部分，最外层查询则被标记为
SUBQUERY	在SELECT或WHERE列表中包含了子查询
DERIVED	在FROM列表中包含的子查询被标记为DERIVED(衍生) MySQL会递归执行这些子查询, 把结果放在临时表里。
UNION	若第二个SELECT出现在UNION之后，则被标记为UNION； 若UNION包含在FROM子句的子查询中,外层SELECT将被标记为：DERIVED
UNION RESULT	从UNION表获取结果的SELECT

table：这一行的数据是关于哪张表的

type:访问类型，是较为重要的一个指标

结果值从最好到最坏依次是：

system > const > eq_ref > ref > fulltext > ref_or_null > index_merge > unique_subquery > index_subquery > range > index > ALL

需要记忆的 :system>const>eq_ref>ref>range>index>ALL

• system ：系统表,平时不会出现可以忽略不计
• const：通过唯一索引一次就找到
• eq_ref：出现在要连接过个表的查询计划中，驱动表只返回一行数据，且这行数据是第二个表的主键或者唯一索引，且必须为not null，唯一索引和主键是多列时，只有所有的列都用作比较时才会出现eq_ref
• ref：不像eq_ref那样要求连接顺序，也没有主键和唯一索引的要求，只要使用相等条件检索时就可能出现，常见与辅助索引的等值查找。或者多列主键、唯一索引中，使用第一个列之外的列作为等值查找也会出现，总之，返回数据不唯一的等值查找就可能出现。
• range：索引范围扫描，常见于使用>,<,is null,between ,in ,like等运算符的查询中。
• index：索引全表扫描，把索引从头到尾扫一遍，常见于使用索引列就可以处理不需要读取数据文件的查询、可以使用索引排序或者分组的查询。
• all：这个就是全表扫描数据文件，然后再在server层进行过滤返回符合要求的记录。

key_len:

key_len表示索引使用的字节数， 根据这个值，就可以判断索引使用情况，特别是在组合索引的时候，判断所有的索引字段是否都被查询用到。 char和varchar跟字符编码也有密切的联系, latin1占用1个字节，gbk占用2个字节，utf8占用3个字节。（不同字符编码占用的存储空间不同）

ref：

如果是使用的常数等值查询，这里会显示const，如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段，如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func

rows：

这里是执行计划中估算的扫描行数，不是精确值

Extra：

值	描述
Using filesort	说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取。 MySQL中无法利用索引完成的排序操作称为“文件排序”
Using temporary	使了用临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序 order by 和分组查询 group by。
USING index	是否用了覆盖索引
Using where	表明使用了where过滤
Using join buffer	使用了连接缓存：
Impossible where	where子句的值总是false，不能用来获取任何元组

SQL优化

优化实战策略

1. 尽量全值匹配：当建立了索引列后，能在wherel条件中使用索引的尽量使用。
2. 最佳左前缀法则：如果索引了多列，要遵守最左前缀法则。指的是查询从索引的最左前列开始并且不跳过索引中的列。
3. 不在索引列上做任何操作：不在索引列上做任何操作（计算、函数、(自动or手动)类型转换），会导致索引失效而转向全表扫描
4. 范围条件放最后：中间有范围查询会导致后面的索引列全部失效
5. 覆盖索引尽量用：尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select *
6. 不等于要甚用：如果定要需要使用不等于,请用覆盖索引
7. Null/Not null 查询有影响注意：在字段为not null的情况下，使用is null 或 is not null 会导致索引失效
8. Like查询要当心：like以通配符开头('%abc...')mysql索引失效会变成全表扫描的操作
9. 字符类型加引号：不加单引号导致索引失效
10. OR改UNION效率高

测试题

答案：

 

记忆总结：

1. 全职匹配我最爱，最左前缀要遵守；
2. 带头大哥不能死，中间兄弟不能断；
3. 索引列上少计算，范围之后全失效；
4. LIKE百分写最右，覆盖索引不写*；
5. 不等空值还有OR，索引影响要注意；
6. VAR引号不可丢， SQL优化有诀窍。