Mysql学习笔记

🎁Mysql

🛠InnoDb与MyISAM引擎对比

对比项	MyISAM	InnoDb
主外键	不支持	支持
事务	不支持	支持
行表锁	表锁，即操作一条记录也会锁住整个表，不适合高并发的操作	行锁，操作时只锁定某一行，不会对其他影响。适合高并发的操作
缓存	只支持缓存索引，不支持缓存真是数据	既支持缓存索引，又支持缓存数据，对内存要求高，而且内存大小对性能有决定性的影响
表空间	小	大
关注点	性能	事务
默认安装	Y	Y

SQL中七种JOIN写法

1. 左连接：左表的全部信息与与右表关联，右表不存在则补null

   select <select_list> from tablea a jeft join table b on a.key = b.key ;
   

2. 右连接：右表的所有信息与左表关联，左表不存在则补null

   select <select_list> from tablea a right join table b on a.key = b.key ;
   

3. 内连接：取两表的交集部分

   select <select_list> from tablea a inner join tableb b on a.key = b.key ;
   

4. 左连接排除内连接结果：取左表独有部分

   select <select_list> from tablea a jeft join table b on a.key = b.key where b.key is null ;
   

5. 右连接排除内连接结果：取右表独有部分

   select <select_list> from tablea a right join table b on a.key = b.key where b.key is null ;
   

6. 全连接：取两表的全部信息，没有补null

   --mysql
   select <select_list> from tablea a left join tableb b in a.key = b.key
   union
   select <select_list> from tablea a right join tableb b in a.key = b.key;
   --oracle
   select <select_list> from tablea a full outer join tableb b on a.key = b.key;
   

7. 外连接排除内连接结果：取两表全集，去除交集

   --mysql
   select <select_list> from tablea a left join tableb b in a.key = b.key where b.key is null
   union  --union关键字起交集去重作用
   select <select_list> from tablea a right join tableb b in a.key = b.key where a.key is null;
   --oracle
   select <select_list> from tablea a full outer join tableb b on a.key = b.key where a.key is null or b.key is null;
   

索引

定义：索引是帮助mysql高效获取数据的数据结构；可以类比字典，对数据进行排序，可以简单的理解排好序的快速查找数据结构

​ 数据本身之外，数据库还维护者一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现该机查找算哒，这种数据结构就是索引，一般为BTREE索引。

​ 优点：提高数据的检索效率，减低数据库的IO成本，通过索引对数据进行排序，降低排序的成本，降低cpu消耗

​ 缺点：实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表记录，所以索引也会占用磁盘空间；虽然索引可以提高查找速度，但是会降低表的更新速度，因为更新时不仅更新表的数据，也会保存索引文件每次更新所添加索引列字段；

1、索引创建

1. 单表：根据where group order 使用的顺序创建所用，出现范围选择时会导致索引失效(>、<)

2. 两张表：left join，对右表的on创建索引；right join 对左表的哦那创建索引

3. 三张表：左右关联遵循量表的创建逻辑

   使用小表驱动大表

2、如何避免索引失效

1. 全值匹配

2. 最佳左前缀法则

   最左开始创建的第一个字段不可以丢失

   中间的字段不可以断，如果断裂会导致断裂后的字段部分索引失效

3. 不在索引列上做任何操作(计算、函数)，会导致索引失效而转向全表扫描

4. 存储引擎不能使用索引中范围条件右边的列

5. 尽量使用覆盖索引，减少使用select *

6. mysql在使用不等于的时候无法使用索引，而会导致全表扫描

7. is null，is not null无法使用该索引

8. like以及通配符开头mysql索引会失效而导致全表扫描，末尾时不失效

   如果使用like name = “%name%” 则应该使用覆盖索引，这样可以保证不失效

9. 字符串不加单引号索引失效

   mysql会将数据进行自动转换，导致索引失效，参见第3条

10. 少用or，用它连接时索引失效

11. 定值、范围还是排序，一般order by时给个范围

12. group by基本上都会进行排序，会有临时表产生，需要遵守联合索引顺序

    总结：假设index(a,b,c)

where语句	索引是否被使用感到
where a = 3	Y 使用到a
where a = 3 and b = 5	Y 使用到了a,b
where a = 3 and b = 4 and c = 5	Y 使用到了a,b,c
where b = 3 或者 where b = 3 and c = 4或者 c = 4	N
where a = 3 and c = 5	Y 使用到a，由于b断裂，所以c未能使用
where a = 3 and b > 4 and c = 5	Y 使用到了a，b 范围之后的c失效
where a =3 and b like ‘kk%’ and c = 5	Y 使用了a,b,c
where a =3 and b like ‘%kk’ and c = 5	Y 使用了a
where a =3 and b like ‘%kk%’ and c = 5	Y 使用了a
where a =3 and b like ‘k%kk%’ and c = 5	Y 使用到了a,b,c

一般性建议

1. 对于单值索引，尽量选择针对当前查询过滤行更好的索引
2. 在选择组合索引时，当前查询中过滤行最好的字段在索引中的顺序，越靠前越好
3. 在选择组合索引时，尽量选择可以包含当前查询中where子句中更多的字段索引
4. 尽可能通过分析统计信息和调整查询的写法来达到选择合适索引的目的

执行计划 Explain

执行计划的字段	字段说明
id	select查询的序列号，包含一组数字表示查询中执行select子句或操作表的顺序 id相同则可以认为是同组，从上向下顺序执行，在所有组中。id越大，优先级越高
select_type	查询的类型 SIMPLE:简单的select查询，查询中不包含子查询或者UNION PRIMARY：查询中若包含任何复杂的子部分，最外层查询被标记成PRIMARY SUBQUERY：在select或者where包含了子查询 DERIVED：在from列表中包含的子查询被标记为DERIVERD(衍生)，MYSQL会递归查询这些子查询，把结果放在临时表中 UNION:若第二个select出现在UNION之后，则被标记为UNION UNION RESULT：从UNION表获取结果的select
table	显示这一行的数据是关于哪张表的
type	显示查询使用了何种类型，从好到差依次是 system>const>rq_ref>ref>range>index>ALL System: 表只有一行记录，是const类型的特例，平时不会出现 const:表示主键索引或者唯一索引，因为只匹配到一条数据，如同将主键置于where条件，MYSQL可以将该查询转换成一个常量 eq_ref:唯一性扫描索引，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描 ref:非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回所有的单独值行，可能会找到多条记录 range:只检索给定范围的行，使用一个索引选择行。key列显示使用了那个索引，一般就是在你的where语句中出现了between、<、>、in等的查询，这种扫描比全表扫描要好，因为它只需要开始于索引的某一点，结束于另一点，不用扫描全部索引 index:index与all的区别为index类型只遍历索引树，这通常比all快，因为索引文件通常比数据文件小 ALL：将遍历全表以查找匹配的行 最好可以优化到ref，保证可以优化到range
possible_keys	显示可能应用到这张表的索引，一个或多个。查询涉及到的字段若存在索引，则索引被列出，但是不一定被查询实际使用
key	实际使用的索引，如果为null，则没有使用索引 查询中若使用了覆盖索引，则该索引仅出现在key列表中
key_len	表示索引中使用的字节数，可通过该列计算查询中使用的索引长度，在不损失精度的情况下，长度越短越好，key_len显示的值为索引字段的最大可能长度，并非实际使用长度，即key_len是根据表定义计算得来的，不是通过表内检索来的
ref	显示索引的那一列被使用了，如果可能的话是一个常数，那些列或常量被用于查询索引列上的值
rows	根据表统计及索引的选用情况，大致估算找出所需的记录所需要读取的行数
Extra	包含不适合在其他列显示，但是非常重要的信息 useing filesort:说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序读取，Mysql中无法利用索引完成的排序成为“文件排序” usering temporary：产生了内部的临时表保存中间结果，Msql在对查询结果配许时使用了临时表，常见于order by，group by usering index：表示对相应的select操作中使用了覆盖索引，避免了表的数据行，效率高；如果没有同时出现useing where 表明索引被用于读取数据而非执行查找动作，如果同时出现useing where ，表示索引被用于执行索引键值的查找 useing where：表明使用了where过滤 useing join buffer：表明使用 了连接缓存 impossible where：表示wher 何单元 distinct：优化操作，找到第一个匹配单元后就立刻停止查找操作

能做什么

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

数据库调优

1、查询优化

1. 小表驱动大表

   EXISTS：select * from table where exists (subquery)

   该语法可以理解为：将主查询的数据，放到子查询中做条件验证，根据验证结果(true or false)来决定朱查询的结果是否保留

   提示：

   1. exists(subquery)只返回true或false，因此子查询中的select × 可以是 select 1或其他常量，官方的说法是实际执行是会忽略select的查询清单，因此没有区别
   2. exists子查询的实际过程可能是经过了优化而不是我们理解的逐条对比
   3. exists子查询旺旺可以用条件表达式、或者子查询、或者join来代替，何种优化需要具体分析

2. Order by关键字优化

   1. 尽量使用index方式排序，避免FileSort方式排序
   2. 尽可能在索引列上培训操作，遵循索引建立的最佳左前缀原则
   3. 如果不在索引裂伤，filesort有两种算法：双路排序和单路排序（4.1之后，将数据读取到缓冲区。如果缓冲区过小，会导致多次的IO操作）
   4. 优化策略：增加sort_buffer_size、max_length_for_sort_data参数的设置

   排序索引使用情况index（a,b,c）

   Mysql两种培训方式：文件排序或扫描有序的索引排序

   使用情况	order by 语句
   能使用索引最佳左前缀	order by a （b、c）
   	order by a,b（a,b,c）
   	ordy by a desc,b desc,c desc
   若索引最左为常量，这也可以使用	where a = const order by b,c
   	where a = const , b = const order by c
   	where a = const , b > const order by b,c
   不能使用索引排序(原因)
   排序不一致	order by a desc ,b asc ,c desc
   丢失左前缀字段	where g = const order by b,c
   丢失中间字段	order by a,c
   对排序来说，多个相等条件也是范围查询的	where a in (…) order by b,c

2、调优流程

1. 慢查询的开启并捕获
2. explain + 慢sql分析
3. show profile查询sql在Mysql服务器里面的执行细节和生命周期情况
4. sql数据库服务起的参数调优

数据库锁

锁时计算机协调多个及逆行或线程并发访问某一资源的限制机制

​ 在数据库中除了传统的计算机资源的征用外，数据也是一种提供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要引树。从这个角度来说，锁对数据库而言显得尤其重要，也更加复杂

​ 按照操作类型来分，可以分为读锁和写锁；

读锁：针对同一分数据，多个读操作可以同时进行而不会相互影响

写锁：当前写操作没有完成前，他会组队其他写锁和读锁

总结：读锁会阻塞写不会阻塞读操作；写锁会阻塞读和写操作

​ 按照细粒度分。可以 分为表锁和行锁；

表锁：开销小，加锁快；无死锁；锁细粒度大，发生锁冲突的概率最高，并发程度低

行锁：偏向InnoDB存储引擎，开销大，加锁慢；会出现死锁；细粒度小，冲突低并发高；支持事务

注：InnoDB使用的是行锁，是针对索引加锁，而不是根据数据加锁，并且该索引不能失效，否则会从行锁升级为表锁；使用的条件若没有使用索引，这会成为表锁

数据库事务

事务是由一组SQL语句组成的逻辑处理单元，事务有以下4个属性，通常成为事务的ACID属性

1. 原子性(Atomicty):事务时一个原子操作单元，对数据的修改要么全部执行，要么全部不执行
2. 一致性(Consostent):在事务开始和完成时数据都必须保持一致状态。则意味着所有相关的数据规则都必须应用于事务的修改，以保证数据的完整性；事务结束后，所有的内部数据结构也必须时正确的
3. 隔离性(Isolation):数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的独立环境执行，则以为则事务处理过程的中间状态对外部时不可见的，反之亦然
4. 持久性(Durable):事务完成之后，它对于数据的修改是永久性的，即使出现了系统故障也能够保持

间隙锁

​ 当我们范围条件不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据加锁；对于在这个条件范围内，但不存在的记录，叫做间隙

​ InnoDB也会给这个间隙加锁，这种锁机制成为间隙锁

​ 危害：因为Query执行过程中通过范围查找的话，他会锁定整个范围内所有的索引键值，急时这个键值不存在。间隙锁有一个比较致命的弱点，就是当一个范围内的键值被锁，即使某些不存在的键值也会被无辜锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这些可能会对性能造成很大伤害

并发事务处理带来的问题

1. 更新丢失

   ​ 当两个或者多个事务选择了同一行，然后基于最初选定的值进行更新时，由于每个事务都不知道其他事务存在，就会发生问题–最后更新的覆盖其他事务做的更新

   ​ 例如：两个程序员同时修改一个文件。每个人独立的修改其副本，这样就覆盖了原始文件，最后提交的人会覆盖之前人提交的文件

   ​ 如果在一个程序员完成并提交事务之前，另一个程序员不能访问同一个文件，则可以避免此问题

2. 脏读

   ​ 一个事务正在对一条记录做修改，这个事务完成提交之前，这条记录的数据就处于不一致的状态中；这时另一个事务也来读取同一条记录，如果不加以控制，第二个事务读取了这些“脏”数据，并且做了进一步的处理，就会产生未提交的数据依赖关系。这种情况成为脏读

   ​ 一句话：事务A读取了B以修改，单位提交的数据，还在这个数据上做了修改；此时，B事务回滚，A读取的数据无效，不符合一致性要求

3. 不可重复读

   ​ 一个事务在读取某些数据的某个时间，再次读取以前读过的数据，却发现其督促的数据已经发生了改变或者被删除了！这种现象就叫做不可重复读

   ​ 即事务A读取了事务B已经提交的修改数据，不符合隔离性

4. 幻读

   ​ 一个事务按照相同的查询条件读取之前检索锅的数据，却发现其他事务插入了满足其查询的新数据，这种现象就称为幻读

   ​ 一句话：事务A读取了事务B提交的新增数据，不符合隔离性

   脏读与幻读有些类似：脏读时事务B修改了数据；幻读是事务B新增了数据

如何锁一行

​ select * from table where id = 1 for update;