mysql知识点

1、MySQL多表连接有哪些方式？怎么用的？这些连接都有什么区别？

连接方式：左连接、右连接、内连接
使用方法：
左连接：select * from A LEFT JOIN B on A.id=B.id;右连接：select * from A RIGHT JOIN B on A.id=B.id;内连接：select * from A inner join B on a.xx=b.xx;（其中inner可以省略）

区别：
Inner join 内连接，在两张表进行连接查询时，只保留两张表中完全匹配的结果集left join 在两张表进行连接查询时，会返回左表所有的行，即使在右表中没有匹配的记录。right join 在两张表进行连接查询时，会返回右表所有的行，即使在左表中没有匹配的记录。

2、说一下索引的优势和劣势？
优势：
唯一索引可以保证数据库表中每一行数据的唯一性
索引可以加快数据查询速度，减少查询时间
劣势：
创建索引和维护索引要耗费时间
索引需要占物理空间，除了数据表占用数据空间之外，每一个索引还要占用一定的物理空间
给表中的数据进行增、删、改的时候，索引也要动态的维护。

3、什么是死锁？怎么解决？

死锁是指两个或多个事务在同一资源上相互占用，并请求锁定对方的资源，从而导致恶性循环的现象。

解决死锁：
1、解决死锁思路，一般就是切断环路，尽量避免并发形成环路。
2、如果不同程序会并发存取多个表，尽量约定以相同的顺序访问表，可以大大降低死锁机会；
3、在同一个事务中，尽可能做到一次锁定所需要的所有资源，减少死锁产生概率；
4、对于非常容易产生死锁的业务部分，可以尝试使用升级锁定颗粒度，通过表级锁定来减少死锁产生的概率；
5、如果业务处理不好可以用分布式事务锁或者使用乐观锁；
6、死锁与索引密不可分，解决索引问题，需要合理优化索引。

4、MySQL的约束有哪些？
NOT NULL: 约束字段的内容一定不能为NULL。
UNIQUE: 约束字段唯一性，一个表允许有多个Unique约束。
PRIMARY KEY: 约束字段唯一，不可重复，一个表只允许存在一个。
FOREIGN KEY: 用于预防破坏表之间连接的动作，也能防止非法数据插入外键。
CHECK: 用于控制字段的值范围。

5、对慢查询都怎么优化过？
分析语句，是否加载了不必要的字段/数据。
分析SQL执行语句是否命中索引等。
如果SQL很复杂，优化SQL结构
如果表数据量太大，考虑分表情况

6、MySQL数据库cpu飙升的话，要怎么处理呢？
排查过程：
使用top命令观察，确定是mysqld导致还是其他原因。如果是mysqld导致的，show processlist，查看session情况，确定是不是有消耗资源的sql在运行。找出消耗高的 sql，看看执行计划是否准确， 索引是否缺失，数据量是否太大。

处理：
kill掉这些线程(同时观察cpu使用率是否下降)，进行相应的调整(比如说加索引、改sql、改内存参数)重新跑SQL。

其他情况：
也有可能是每个sql消耗资源并不多，但是突然之间，有大量的session 连进来导致cpu飙升，这种情况就需要跟应用一起来分析为何连接数会激增，再做出相应的调整，比如说限制连接数等。

7、怎么优化sql的？
1、加索引
2、避免返回不必要的数据
3、适当分批量进行
4、优化sql结构
5、分库分表
6、读写分离

8、什么情况下应不建或少建索引？
1、表记录太少
2、经常插入、删除、修改的表
3、数据重复且分布平均的表字段，假如一个表有10万行记录，有一个字段A只有T和F两种值，且每个值的分布概率大约为50%，那么对这种表A字段建索引一般不会提高数据库的查询速度。
4、经常和主字段一块查询但主字段索引值比较多的表字段

9、详细说一下一条 MySQL 语句执行的步骤
Server 层按顺序执行 SQL 的步骤为：

• 客户端请求 -> 连接器（验证用户身份，给予权限）
• 查询缓存（存在缓存则直接返回，不存在则执行后续操作）
• 分析器（对 SQL 进行词法分析和语法分析操作）
• 优化器（主要对执行的 SQL 优化选择最优的执行方案方法）
• 执行器（执行时会先看用户是否有执行权限，有才去使用这个引擎提供的接口）-> 去引擎层获取数据返回（如果开启查询缓存则会缓存查询结果）

10、索引的三种常见底层数据结构以及优缺点
三种常见的索引底层数据结构：分别是哈希表、有序数组和搜索树。

• 哈希表这种适用于等值查询的场景，比如 memcached 以及其它一些 NoSQL 引擎，不适合范围查询。

哈希表就是一种以键值对来存储数据的结构，你可以通过一个 key 就可以很快的查询出对用的 value
值。哈希表主要是利用了数组的随机访问特性，实现思想主要是通过一个哈希函数把 key 转换成一个哈希值，这个哈希值就对应数组中的某个下标。
但是由于哈希表是无序的，区间查询效率会非常的慢，所以哈希表通常只用于查询单个值

• 有序数组索引只适用于静态存储引擎，等值和范围查询性能好，但更新数据成本高。

数组具有连续性和随机访问特性，因此数组都能很高效的进行单个等值查询和区间查询，但是 mysql不仅仅是查询数据，还会有插入和删除数据的操作。
在有序数组中插入或删除一个数据会需要批量移动数组中其他数据，这是一个不小的消耗，影响性能。因此有序数组适合处理静态数据，比如一些过往的不会再修改的数据。
在这里你可能会问，既然哈希表其实也是利用了数组的特性，那有了数组为啥还需要哈希表呢。是因为数组下标 key只能是数字，而哈希表可以支持字符串 key，哈希函数可以把这个 key 转换成一个数组下标。 同时，不同的 key如果通过哈希函数转换成了相同的数组下标，这就会造成冲突，在哈希表中一般会通过再拉出一个链表来保存这个冲突的值。

• N 叉树由于读写上的性能优点以及适配磁盘访问模式以及广泛应用在数据库引擎中。二叉搜索树和二叉树不一样，二叉树是指每个节点的左儿子小于父节点，父节点又小于右儿子，即二叉搜索树的中序遍历就是一个有序序列。由于索引不仅仅是存在内存中，还会存储在硬盘中，因此就会涉及到 IO 性能了，就要求树的高度不能太高。实际上 B+ 树就是通过二叉搜索树推演改进的，

11、MyISAM 和 InnoDB 实现 B 树索引方式的区别是什么？

• InnoDB 存储引擎：B+ 树索引的叶子节点保存数据本身，其数据文件本身就是索引文件。
• MyISAM 存储引擎：B+ 树索引的叶子节点保存数据的物理地址，叶节点的 data域存放的是数据记录的地址，索引文件和数据文件是分离的。

12、哪些操作会导致索引失效？

• 对索引使用左或者左右模糊匹配，也就是 like %xx 或者 like %xx%这两种方式都会造成索引失效。原因在于查询的结果可能是多个，不知道从哪个索引值开始比较，于是就只能通过全表扫描的方式来查询。
• 对索引进行函数/对索引进行表达式计算，因为索引保持的是索引字段的原始值，而不是经过函数计算的值，自然就没办法走索引。
• 对索引进行隐式转换相当于使用了新函数。
• WHERE 子句中的 OR语句，只要有条件列不是索引列，就会进行全表扫描。

13、MySQL中的B+树索引结构
基于磁盘的平衡树，但树非常矮，通常为3~4层，能存放千万到上亿的排序数据。树矮意味着访问效率高，从千万或上亿数据里查询一条数据，只用 3、4 次I/O。又因为现在的固态硬盘每秒能执行至少10000次I/O，所以查询一条数据，哪怕全部在磁盘上，也只需要0.0030.004秒。另外，因为B+树矮，在做排序时，也只需要比较34次就能定位数据需要插入的位置，排序效率非常不错。
B+树索引由根节点（root node）、中间节点（non leaf node）、叶子节点（leaf node）组成，其中叶子节点存放所有排序后的数据。

B+树是B树的一种变形形式，B+树上的叶子结点存储关键字以及相应记录的地址，叶子结点以上各层作为索引使用。

从上图我们可以归纳出B+树的几个特征：
所有记录节点都是按键值的大小顺序存放在同一层的叶子节点上，由各叶子节点指针进行连接；
相同节点数量的情况下，B+树高度远低于平衡二叉树；非叶子节点只保存索引信息和下一层节点的指针信息，不保存实际数据记录；只有叶子节点存储实际的数据。