mysql索引介绍，使用原则，乐观锁实现，哈希索引不能范围查找的原因，explain，联合索引建立原则，最左前缀

单例索引

一个索引只包含单个列，一个表可以有多个单例索引
普通索引的唯一任务是加快对数据的访问速度。因此，应该只为那些最经常出现在查询条件（WHERE column = ）或排序条件（ORDER BY column）中的数据列创建索引。只要有可能，就应该选择一个数据最整齐、最紧凑的数据列来创建索引

唯一索引

索引列的值必须唯一，但允许有空值
优点

• 简化了MySQL对这个索引的管理工作，这个索引也因此而变得更有效率
• MySQL会在有新记录插入数据表时，自动检查新记录的这个字段的值是否已经在某个记录的这个字段里出现过了；如果是，MySQL将拒绝插入那条新记录。也就是说，唯一索引可以保证数据记录的唯一性。

作用

• 在许多场合，创建唯一索引的目的往往不是为了提高访问速度，而只是为了避免数据出现重复。

主键索引

是一种特殊的唯一索引，不允许有空值。一般是在建表的时候指定了主键，就会创建主键索引， CREATE INDEX不能用来创建主键索引。

自增主键

优点

• 数据库自动编号，速度快，而且是增量增长，按顺序存放，对于检索非常有利；
• 数字型，占用空间小，易排序，在程序中传递也方便； - 如果通过非系统增加记录时，可以不用指定该字段，不用担心主键重复问题

UUID

UUID含义是通用唯一识别码，指在一台机器上生成的数字，它保证对在同一时空中的所有机器都是唯一的。通常平台会提供生成的API。换句话说能够在一定的范围内保证主键id的唯一性。
优点

• 出现数据拆分、合并存储的时候，能达到全局的唯一性

缺点

• 影响插入速度， 并且造成硬盘使用率低
• uuid之间比较大小相对数字慢不少， 影响查询速度
• uuid占空间大， 如果你建的索引越多， 影响越严重

覆盖索引

覆盖索引是指，索引上的信息足够满足查询请求，不需要再回到主键上去取数据

当一条查询语句符合覆盖索引条件时，sql只需要通过索引就可以返回查询所需要的数据，这样避免了查到索引后再返回表操作，减少I/O提高效率。

使用覆盖索引Innodb比MyISAM效果更好----InnoDB使用聚集索引组织数据，如果二级索引中包含查询所需的数据，就不再需要在聚集索引中查找了

注：遇到以下情况，执行计划不会选择覆盖查询
　　1.select选择的字段中含有不在索引中的字段 ，即索引没有覆盖全部的列。
　　2.where条件中不能含有对索引进行like的操作。

mysql死锁及解决方案

当出现死锁以后，有两种策略：
1.直接进入等待，直到超时。
2.发起死锁检测，发现死锁后，主动回滚死锁链条中的某一个事务，让其他事务得以继续执行。
3.事物按顺序执行

乐观锁的实现

使用数据版本（Version）记录机制实现，这是乐观锁最常用的一种实现方式。何谓数据版本：即为数据增加一个版本标识，一般是通过为数据库表增加一个数字类型的 “version” 字段来实现。当读取数据时，将version字段的值一同读出，数据每更新一次，对此version值加一。当我们提交更新的时候，判断数据库表对应记录的当前版本信息与第一次取出来的version值进行比对，如果数据库表当前版本号与第一次取出来的version值相等，则予以更新，否则认为是过期数据

最左前缀

由于构建一棵B+树只能根据一个值来确定索引关系，所以数据库依赖联合索引最左的字段来构建
对 a，b，c简历联合索引，可以查询 a，(a,b)，(a,b,c)
对于 (b,a) 也是可以的，因为优化器会优化
sql语句中字段的顺序不需要和联合索引中定义的字段顺序一致，查询优化器会自己调整顺序

mysql使用索引的原则

链接: link

1. 最左前缀原则
2. 计算，函数，类型转换会使索引失效
3. 范围之后的索引都会失效
4. !=时无法使用索引
5. is null和is not null也会让索引失效
6. like以通配符开头会让索引失效
7. 使用字符串的时候不加单引号会导致索引失效
8. or也会导致索引失效

用explain查看一个sql底层的执行情况

链接: https://blog.csdn.net/qq_15764477/article/details/109602129

id select_type table type possible_keys key key_len ref rows extra

哈希索引不能范围查找的原因，跳表的缺点

Hash操作并不能保证顺序性，所以值相近的两个数据，Hash值相差很远，被分到不同的桶中。
如何在使用Hash索引的同时，依然保留数据值的大小顺序：

1. LRU缓存
   在Hash索引的基础上，将所有数据通过链表有序的排列，每次添加新的值时有序插入，这样就可以执行范围查找了。但是这会让插入的复杂度变为O(n),效率低下
2. 通过Hash表和跳表实现有序集合
   通过这种思路改造Hash索引也可以在支持范围查找的的同时将插入的复杂度降低到O（logN）。但是考虑到磁盘的IO影响，跳表并不能很好的控制IO的次数，平均IO次数也超过B+树，B+tree确实是最适合关系型数据库的索引结构。因此只有基于内存的Redis数据库使用了这种索引方式。

mysql聚簇索引和非聚簇索引的区别

聚簇索引：将数据存储和索引放在一起，数据的物理存放顺序与索引顺序一致，找到索引也就找到了数据
非聚簇索引： 叶子节点不存储数据，存储的是数据行地址，根据索引查找到数据行的位置再去磁盘查找数据
聚簇索引直接获取到数据，==非聚簇索引需要二次查询(覆盖索引除外)==效率要高
一般来讲一堆数据记录最多只能有一个聚簇索引，但可以有很多非聚簇索引
非聚簇索引查询的话会做几次查询？（二次）？（一次）
非聚簇索引存的是什么东西？（叶子节点存的是键值和数据所在物理地址）

MySQL默认/最大支持连接数是多少？

默认为151，最大为10万

mysql二阶段提交

操作

• 执行语句时，存储引擎将新的数据行更新到内存中，同时将这个更新操作记录到redo log，redo log进入prepare状态，告知执行器随时可以提交事务。
• 执行器生成这个操作的binlog,并把binlog写入磁盘。
• 执行器调用引擎接口，引擎把刚刚写的redo log改成提交commit状态，更新完成。

原因

先写redolog再写binlog
如果在一条语句redolog之后崩溃了，binlog则没有记录这条语句。系统在crash recovery时重新执行了一遍binlog便会少了这一次的修改。恢复的数据库少了这条更新。
先写binlog再写redolog
如果在一条语句binlog之后崩溃了，redolog则没有记录这条语句（数据库物理层面并没有执行这条语句）。系统在crash recovery时重新执行了一遍binlog便会多了这一次的修改。恢复的数据库便多了这条更新。

索引建立原则

差异性最大的放在最前面，选择离散度高的列作为索引，越有利于数据的查找，当离散到一定程度就是全表扫描
尽量选择较小的数据类型，这样节点存储的key就多，树的高度就小，从而可以减少IO次数

慢sql分析

• 刷脏
• 索引失效
• 遇到锁或死锁

having和where区别

where是一个约束声明,使用where来约束来自数据库的数据;
having是一个过滤声明;在查询返回结果集以后，对查询结果进行的过滤操作;

索引失效

• like 以%开头，索引无效
• or语句前后没有同时使用索引
• 未遵循最左匹配
• 数据类型出现隐式转化
• 使用范围查询

主键索引和其他索引的区别在哪

1. 主键索引是一种特殊的唯一索引
2. 主键列不允许为空值，唯一性索引列允许空值
3. 主键可以被其他表引用为外键，而唯一索引不能
4. 一个表最多只能创建一个主键索引，但可以创建多个唯一索引

mysql表必须有主键吗

不一定，添加主键会默认对mysql表创建主键索引(聚簇)，但是聚簇索引一个表只能有一个，比较珍贵，因此一般不会把聚簇索引给主键
要不要主键主要看业务方面需求

• 数据查询效率(主键可以提高查询效率，当然合理的索引替代也可以)
• 看表的功能，如果是用来存储大量数据，并需要经常查询这个表的，建立主键可以加快查询效率从而降低服务器的负担