1.索引

1.1什么是索引？

如果说数据库是一本字典的话，索引就是字典的目录
MySQL常用的索引有两种，B-Tree（实际上是B+Tree）和 hash
默认是前者 因为（innoDB只支持Btree）
索引：一种帮助MySQL提高查询效率的数据结构
索引的优点：
1.大大加快查询效率
索引的缺点：
1.维护索引需要耗费数据库资源
2.索引需要占用磁盘空间
3.当对表的数据进行增删改查的时候，因为要维护索引，速度会受到影响

1.1.1创建索引 删除索引语句：

  1. create index idx_email on student(email)
  2. drop index idx_email on student
  3. create index_idx_email using hash on studen(email) #还是B树
  4. #创建复合索引(符合索引用于量表联查的条件字段，这时候比较快)
  5. create index idx_email on student(email,name)
  7. #创建表的时候指定存储引擎
  8. create table test(id int primary key.name varchar(10))
  9.             engine = 'innodb/memory/myisam'

1.2索引的分类

主键索引 唯一索引 复合索引 普通索引
InnoDB

  • 主键索引

设定逐渐后数据库会自动建立索引，innoDB为聚簇索引 主键索引索引列的值不能为空

  • 单值索引 也叫单列索引 普通索引

即一个索引只包含单个列，一个表可以有多个单列索引（id name index age index） 用于单条件查询

  • 唯一索引

索引列的值必须唯一，但允许有空值 唯一索引索引列的值可以存在null，但是只能存在一个null

  • 复合索引

即一个索引包含多个列 id (name age) index bir 用于where name = ? and age = ?

  • Full Text全文索引（Mysql5.7之前只能有Myisam引擎）

1.3索引相关操作

  1. #主键索引  无需创建，在建表时自动创建
  2. create table t_user(
  3.   id varchar(20) primary key,name varchar(20));
  5. show index from t_user;

image.png
主键索引无需创建，在建表时自动创建，如果没有主键，则innoDB会自动创建一个隐式索引，类似于Oracle的row_id

  1. #普通索引创建的两种方式：建表时创建   建表后创建
  2. #建表后创建普通索引
  4. create index name_index on t_user(name);+

image.png

  1. #创建表的时候创建普通索引
  2. create table t_user1(
  3.   id varchar(20) primary key,name varchar(20),key(name));
  4.   #key中的名字就代表索引的列 默认创建的索引名就是name

image.png

  1. #创建表的时候创建唯一索引
  2. create table t_user(id varchar(20) primary key,name varchar(20),unique(name));
  4. #创建表以后创建唯一索引
  5. create unique index nameindex on t_user(name);
  1. #复合索引
  2. --建表时创建
  3. create table t_user(id varchar(20) primary key,name varchar(20),age int,key(name,age));
  5. --建表后创建
  6. create index nameageindex on t_user(name,age);

image.png

1.3.1最左匹配原则

假设我现在使用 name age bir三个字段建立了联合索引，那么有以下几个问题
name bir age 能否利用索引？ 可以
name age bir 能否利用索引？ 可以
age bir 能否利用索引？ 不能
bir age name 能否利用索引？ 可以
age bir 能否利用索引？ 不可以
根据最左前缀（包含）原则 第二个肯定是可以用上的
mysql引擎在查询时为了更好的利用索引，在查询中会动态调整字段顺序以便使用索引

1.3.2索引失效

like 以%开头，索引无效；当like前缀没有%，后缀有%时，索引有效。
or语句前后没有同时使用索引。当or左右查询字段只有一个是索引，该索引失效，只有当or左右查询字段均为索引时，才会生效
组合索引，不是使用第一列索引，索引失效。
数据类型出现隐式转化。如varchar不加单引号的话可能会自动转换为int型，使索引无效，产生全表扫描。
在索引字段上使用not，<>，!=。不等于操作符是永远不会用到索引的，因此对它的处理只会产生全表扫描。 优化方法： key<>0 改为 key>0 or key<0。
对索引字段进行计算操作、字段上使用函数。（索引为 emp(ename,empno,sal)）
当全表扫描速度比索引速度快时，mysql会使用全表扫描，此时索引失效。
索引失效分析工具：
可以使用explain命令加在要分析的sql语句前面，在执行结果中查看key这一列的值，如果为NULL，说明没有使用索引。

1.4Mysql回表

回表就是先通过数据库索引扫描出数据所在的行，再通过行主键id取出索引中未提供的数据，即基于非主键索引的查询需要多扫描一棵索引树。
因此，可以通过索引先查询出id字段，再通过主键id字段，查询行中的字段数据，即通过再次查询提供MySQL查询速度。
image.png

1.5索引的底层原理

  1. 1.插入id无序顺序
  2. insert into t_emp values(5,'d',22);
  3. insert into t_emp values(6,'d',21);
  4. insert into t_emp values(7,'e',22);
  5. insert into t_emp values(1,'a',22);
  6. insert into t_emp values(2,'b',32);
  7. insert into t_emp values(3,'c',22);
  8. insert into t_emp values(4,'a',53);
  9. insert into t_emp values(8,'f',17);
  10. insert into t_emp values(9,'v',13);
  12. 2.最终查询的时候结果发现id竟然排序了
  13. 思考：id  主键索引
  16. 3.主键索引进行排序？为什么要排序？
  17.     方便快速查询

主键索引在插入的时候会对数据进行一个排序，然后每个数据节点还有一个Point指针，指向下一个数据区image.png
这样虽然查询的时候非常快，时间复杂度为O(N)，但是当id非常大的时候，时间复杂度就是O（1000），非常浪费时间 所以MySQL对此又进行了优化
有了页管理模式
一页存储16KB 上一页只存下一页的索引和指针 索引为int 4字节 指针4-8字节 加上数据
三层B+树大概能存8亿数据
image.png

1.4.1B-tree和B+tree

B+Tree是B-Tree基础上的一种优化，使其更适合实现外存储索引结构，InnoDB存储引擎就是用B+Tree实现其索引结构的。
B+Tree只有叶子结点存储数据，其他节点存储指针和索引
B-Tree所有的节点都要求存储一份完整的数据
B+Tree和B-Tree存储同样的数据 B-Tree可能需要多一层 ，而树的多层遍历效率会下降很多

综上可得：
B-Tree的每个节点不仅包含数据的Key值，还有data值，而每一页的存储空间是有限的，如果data数据较大时，将会导致每个节点（即一个页）能存储的key的数量很小，当存储的数量很大时，同样会导致B-Tree的深度较大，增大查询时的磁盘I/O次数，进而影响效率。在B+Tree中，所有数据记录节点都是按照键值大小顺序存放在同一层的叶子节点上，而非叶子结点上值存储key的信息，这样可以大大加大每个节点存储Key的数量，降低B+Tree的高度。
B+Tree相对于B-Tree有几个不同点：

  1. 非叶子结点只存储键信息
  2. 所有的叶子结点之间都有一个指针
  3. 数据都记录在叶子节点上

MySQL在设计之初，把根节点常驻在内存中，也就是说第一次查询不需要动用I/O

1.6聚簇索引与非聚簇索引

  1. # 聚簇索引与非聚簇索引
  2. - 聚簇索引：将数据存储与索引放到一块儿，索引结构的叶子结点保存了行数据
  3. - 非聚簇索引：将数据与索引分开，索引结构指向了数据对应的位置

注意：在innoDB中，在聚簇索引之上创建的索引称之为辅助索引，非聚簇索引都是辅助索引，像复合索引，前缀索引，唯一索引。附注索引叶子结点存储的不再是行的物理位置，而是主键值，辅助索引访问数据总是需要二次查找
非聚簇索引存储的叶子结点可以理解为存储的是主键和指针
image.png在访问数据时，如果使用到非聚簇索引，首先是寻找匹配的id，当寻找到匹配的id，在拿id去聚簇索引中访问数据，所以说非聚簇索引访问数据需要两次查找

  1. 聚簇索引为什么不直接存地址而要存指针？
  2. # 因为，我们在增删改操作的时候，往往牵扯到索引树上地址的变化，如果存放地址，
  3. # 每次辅助索引树上的地址都要跟着改变，而存主键id的话，
  4. # 则不需要修改，主键永远不变，变得是聚簇索引只需要拿主键去聚簇索引重现查找即可

聚簇索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据，也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分，每张表只能拥有一个聚簇索引。
Innodb通过主键聚集数据，如果没有定义主键，innodb会选择非空的唯一索引代替。如果没有这样的索引，innodb会隐式的定义一个主键来作为聚簇索引。

  1. # 9-使用聚簇索引的优势
  2. - 问题:每次使用辅助索引检索都要经过两次B+树查找，看上去聚簇索引的效率明显要低于非聚簇索引，这不是多此一举吗?聚簇索引的优势在哪?
  3. - 1.由于行数据和聚簇索引的十子节点存储在一起，同一页中会有多条行数据，访问同—数据页不同行记录时，已经把页加载到了Buffer中(缓存器)
  4.   再次访问时，会在内存中完成访问，不必访问磁盘。这样主键和行数据是一起被载入内存的，找到叶子节点就可以立刻将行数据返回了，如果按照主键Td来组织数据，获得数据更快。
  5.   -⒉.辅助索引的十子节点，存储主键值，而不是数据的存放地址。好处是当行数据放生变化时，索引树的节点也需要分裂变化﹔或者是我们需要查找的数据，
  6.   在上一次1o读写的暖存中没有，需要发生一次新的To操作时，可以避免对辅时索引的维护工作，只需要维护聚簇索引树就好了。
  7.   另一个好处是，因为陈即索引存放的是主键值，减少了辅助索引占用的存储空间大小。
  9. # 10.聚簇索引需要注意什么?
  10. - 当使用主键为聚簇索引时，主键最好不要使用uid，因为uid的值太过离散，不适合排序且可能出现新增加记录的uuid，会插入在索引树中间的位置，
  11.   导致索引树调整复杂度变大，消耗更多的时间和资源。
  12. - 建议使用int类型的自增，方便排序并且默认会在索引树的末尾增加主键值，对索引树的结构影响最小。
  13.   而且，主键值占用的存储空间越大，辅助索引中保存的主键值也会跟着变大，占用存储空间，也会影响到Io操作读取到的数据量。
  15. # 11．为什么主键通常建议使用自增id
  16. - 聚簇索引的数据的物理存放顺序与索引顺序是一致的，即﹔只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。
  17.   如果主键不是自增id,那么可以想象，它会干些什么，不断地调整数据的物理地址、分页，当然也有其他一些措施来减少这些操作，
  18.   但却无法彻底避免。但，如果是自增的，那就简单了，它只需要一页一页地写，索引结构相对紧凑，磁盘碎片少，效率也高。
  20. # 12．什么情况下无法利用索引呢?
  21. - 1.LIKE关键字
  22.         模糊查询时，如果第一个匹配字符为“%” 则不会使用索引，如果“%”不是在第一个位置，索引就会被使用
  23. - 2.多列索引（复合索引）
  24.         只有查询中的字段使用了复合索引中的第一个字段的时候才会使用索引
  25. - 3.查询中使用or关键字
  26.         查询语句只有OR关键字时，如果OR后的两个条件的列都是索引，那么查询中将使用索引。如果oR前后有一个条件的列不是索引，那么查询中将不使用索引。
  1. 优点：
  3. 　　　　1.数据访问更快，因为聚簇索引将索引和数据保存在同一个B+树中，因此从聚簇索引中获取数据比非聚簇索引更快
  5. 　　　　2.聚簇索引对于主键的排序查找和范围查找速度非常快
  6. 缺点：
  8. 　　　　1.插入速度严重依赖于插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能。因此，对于InnoDB表，我们一般都会定义一个自增的ID列为主键
  9. 　　　　2.更新主键的代价很高，因为将会导致被更新的行移动。因此，对于InnoDB表，我们一般定义主键为不可更新。
  10. 　　　　3.二级索引访问需要两次索引查找，第一次找到主键值，第二次根据主键值找到行数据。
  12.     问题：主键索引是聚集索引还是非聚集索引？
  14. 在Innodb下主键索引是聚集索引，在Myisam下主键索引是非聚集索引
  18. # 聚簇索引和非聚簇索引的区别
  20. 聚簇索引的叶子节点存放的是主键值和数据行，支持覆盖索引；二级索引的叶子节点存放的是主键值或指向数据行的指针。
  22. 由于节子节点(数据页)只能按照一颗B+树排序，故一张表只能有一个聚簇索引。辅助索引的存在不影响聚簇索引中数据的组织，所以一张表可以有多个辅助索引
  25. 注： 覆盖索引（covering index）指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取。
  26. 也可以称之为实现了索引覆盖。 当一条查询语句符合覆盖索引条件时，MySQL只需要通过索引就可以返回查询所需要的数据，这样避免了查到索引后再返回表操作，
  27. 减少I/O提高效率。
  28. 如，表covering_index_sample中有一个普通索引 idx_key1_key2(key1,key2)。
  29. 当我们通过SQL语句：select key2 from covering_index_sample where key1 = ‘keytest’;的时候，就可以通过覆盖索引查询，无需回表。

1.7explain关键字

只需要在SQL语句前加上explain关键字就可以查看执行计划，执行计划包括以下信息：id、select_type、table、partitions、type、possible_keys、key、key_len、ref、rows、filtered、Extra，总共12个字段信息。 

  1. 一、ID
  2. SELECT识别符。这是SELECT的查询序列号
  4. SQL执行的顺序的标识，SQL从大到小的执行。id列有以下几个注意点：
  6.     id相同时，执行顺序由上至下。id不同时，如果是子查询，id的序号会递增，
  7.     id值越大优先级越高，越先被执行。
  8.     根据原则，当id不同时，SQL从大到小执行，id相同则从上到下执行。
  10. 二、select_type
  11. 表示select查询的类型，用于区分各种复杂的查询，例如普通查询，联合查询
  12. ，子查询等等。
  14. SIMPLE
  15. 表示最简单的查询操作，也就是查询SQL语句中没有子查询、union等操作。
  17. PRIMARY
  18. 当查询语句中包含复杂查询
  19. 的子部分，表示复杂查询中最外层的 select
  21. SUBQUERY
  22. 当 select 或 where 中包含有子查询，该子查询被标记为SUBQUERY。
  24. DERIVED
  25. 在SQL语句中包含在from子句中的子查询。
  27. UNION
  28. 表示在union中的第二个和随后的select语句
  30. UNION RESULT
  31. 代表从union的临时表中读取数据。
  33. MATERIALIZED
  34. MATERIALIZED表示物化子查询，子查询来自视图。
  36. 三、table
  37. 表示输出结果集的表的表名，并不一定是真实存在的表，也有可能是别名，临时表等等。
  39. 四、partitions
  40. 表示SQL语句查询时匹配到的分区信息，对于非分区表值为NULL，当查询的是分区表
  41. 则会显示分区表命中的分区情况。
  43. 五、type
  44. 需要重点关注的一个字段信息，表示查询使用了哪种类型，
  45. 在 SQL优化中是一个非常重要的指标，
  46. 依次从优到差分别是：system > const > eq_ref > ref > range > index > ALL。
  47. system和const
  48. 单表中最多有一条匹配行，查询效率最高，
  49. 所以这个匹配行的其他列的值可以被优化器在当前查询中当作常量来处理。
  50. 通常出现在根据主键或者唯一索引进行的查询，system是const的特例，
  51. 表里只有一条元组匹配时（系统表）为system。
  52. eq_ref
  53. primary key 或 unique key 索引的所有部分被连接使用 ，
  54. 最多只会返回一条符合条件的记录，所以这种类型常出现在多表的join查询
  55. ref
  56. 相比eq_ref，不使用唯一索引，而是使用普通索引或者唯一性索引的部分前缀，
  57. 可能会找到多个符合条件的行。
  58. range
  59. 使用索引选择行，仅检索给定范围内的行。一般来说是针对一个有索引的字段，
  60. 给定范围检索数据，
  61. 通常出现在where语句中使用 bettween...and、<、>、<=、in 等条件查询 。
  62. index
  63. 扫描全表索引，通常比ALL要快一些。
  64. ALL
  65. 全表扫描，MySQL遍历全表来找到匹配行，性能最差。
  67. 六、possible_keys
  68. 表示在查询中可能使用到的索引来查找，别列出的索引并不一定是最终查询数据所用到的索引。
  70. 七、key
  71. 跟possible_keys
  72. 有所区别，key表示查询中实际使用到的索引，若没有使用到索引则显示为NULL。
  74. 八、key_len
  75. 表示查询用到的索引key的长度(字节数)。如果单列索引，那么就会把整个索引长度计算进去，
  76. 如果是联合索引，不是所有的列都用到，那么就只计算实际用到的列，
  77. 因此可以根据key_len来判断联合索引是否生效。
  79. 九、ref
  80. 显示了哪些列或常量被用于查找索引列上的值。常见的值有：const，func，null，字段名。
  82. 十、rows
  83. mysql估算要找到我们所需的记录，需要读取的行数。
  84. 可以通过这个数据很直观的显示 SQL 性能的好坏，一般情况下 rows 值越小越好。
  86. 十一、filtered
  87. 指返回结果的行占需要读到的行(rows列的值)的百分比，一般来说越大越好。
  89. 十二、Extra
  90. 表示额外的信息。此字段能够给出让我们深入理解执行计划进一步的细节信息。
  91. Using index
  92. 说明在select查询中使用了覆盖索引。
  93. 覆盖索引的好处是一条SQL通过索引就可以返回我们需要的数据。
  94. Using where
  95. 查询时没使用到索引，然后通过where条件过滤获取到所需的数据。
  96. Using temporary
  97. 表示在查询时，MySQL需要创建一个临时表来保存结果。
  98. 临时表一般会比较影响性能，应该尽量避免。
  99. 有时候使用DISTINCT去重时也会产生Using temporary。
  100. Using filesort
  101. 我们知道索引除了查询中能起作用外，排序也是能起到作用的，
  102. 所以当SQL中包含 ORDER BY 操作，而且无法利用索引完成排序操作的时候，
  103. MySQL不得不选择相应的排序算法来实现，这时就会出现Using filesort，
  104. 应该尽量避免使用Using filesort。

2.事务

2.1MySQL的结构

  1. # Mysql 的分层
  2. - 第一层 连接层 连接处理，授权认证，安全
  3. - 第二层 查询解析日志，优化sql语句，以及存储过程，触发器，视图
  4. - 第三层 存储引擎层

2.2 事务的特性

  1. start transaction 开启事务
  4. # 事务的特性
  5. - 只有InnoDB支持事务，MyisAM不支持事务，因为InnoDB支持行锁，MyISAM只有表锁。
  6. - ACID
  7. - A：原子性
  8.         要不全成功，要不全失败。在事务中的某一条操作失败后，会进行回滚操作
  9.     一个事务是不可分割的单位，最小执行单元
  10.     原理：InnoDB引擎提供了SQL日志，包括各种日志，比如错误日志，慢查询日志，innoDB
  11.     提供了两种事务是 一个是 redo log  一个是 undo log 原子性是基于undolog日志实现的
  12.     undolog记录了事务对MySQL的操作，如果失败以后，innoDB会根据undolog去做相反的工作
  13.     比如执行了insert 那么回滚的时候 会执行delete
  14. - C：一致性
  15.         :事务执行之后，数据库的完整性约束没有被破坏，事务执行前后都是一个合法的数据状态。
  16.     完整性体现在比如数据库的主键要唯一，字段类型大小要符合要求，外键的约束要符合要求
  17.     。一致性是事务追求的最终目标。
  18.     原子性、持久性、隔离性都是为了保证数据库最终的一致性。
  19.     如果另外三个特性无法保证，那么一致性肯定也保证不了
  21. - I：隔离性
  22.         多线程时多事务之间互相产生了影响，要避免这个影响，那就加锁。
  23.     mysql的锁有表锁，行锁，间隙锁,好像还有一个锁数据库的，叫全局锁还是什么来着。
  24.     写写操作通过加锁实现隔离性，亵渎操作通过MVCC实现
  26. - D：持久性
  27.         持久性是基于redo log，MySQL的数据存储在磁盘中，当我们进行读取的时候，MySQL会
  28.     提供一个IO BUFFER 缓冲器，当我们去查询数据时，现在buffer中查询，如果没有就去
  29.     数据库查询，查询以后再放到buffer中，当我们向数据库存入数据的时候，也会先向buffer
  30.     中写入数据，定期讲buffer中的数据刷新到磁盘中，实现持久化，虽然这时候效率提高了，但是
  31.     相应的数据丢失的风险也增加了，所以redo log记录的是我们对数据修改的操作，在重启以后
  32.     恢复数据
  33.     redolog是预写式数据，也就是说先记录操作，再把数据写入到buffer，这样就实现了持久性。
  34.     undolog为什么比buffer直接写入磁盘更快？
  35.     buffer中的io是随机io，也就是说io是在随机位置修改的，但是在buffer中是顺序io，
  36.     只会追加，这种操作就很快，kafka也是采用顺序io去操作的。
  37.     buffer持久化数据是根据数据页为单位的，数据页默认大小是16KB，如果修改以后，buffer需要
  38.     修改整个数据页，而redolog只会修改需要修改的部分。
  39.     unlog

2.3事务的隔离级别

事务隔离级别 脏读 不可重复读 幻读
读未提交（read-uncommitted） 是 是 是
不可重复读（read-committed） 否 是 是
可重复读（repeatable-read） 否 否 是
串行化（serializable） 否 否 否

2,4事务的并发问题

1、脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据是脏数据
2、不可重复读：事务 A 多次读取同一数据，事务 B 在事务A多次读取的过程中，对数据作了更新并提交，导致事务A多次读取同一数据时，结果 不一致。
3、幻读：系统管理员A将数据库中所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。
小结：不可重复读的和幻读很容易混淆，不可重复读侧重于修改，幻读侧重于新增或删除。解决不可重复读的问题只需锁住满足条件的行，解决幻读需要锁表