2020.11.07面试总结_select 索引字段 from 表 order by 索引字段 一定会命中索引吗

工作的的原因，还有自己不努力学习（哈哈哈）尽量引导面试官问ssm的框架
文章是对我 这个星期，5天面试的总结
先说mysql

mysql：原文的地址
1.引擎
2.索引
3.隔离级别
4.锁

1.存储引擎
一 Innodb（mysql默认引擎）
支持事务，是事务安全的，提供行级锁与外键约束，有缓冲池，用于缓冲数据和索引
适用场景：用于事务处理，具有ACID事物支持，应用于执行大量的insert和update操作的表
二 MyISAM
不支持事务，不支持外键约束，不支持行级锁，操作时需要锁定整张表，不过会保存表的行数，所以当执行select count(*) from tablename时执行特别快
适用场景：用于管理非事务表，提供高速检索及全文检索能力，适用于有大量的select操作的表，如 日志表
三 MEMORY
使用存在于内存中的内容创建表，每一个memory只实际对应一个磁盘文件。该引擎使用hash索引，可以一次定位，不需要像B树一样从根节点查找到支节点，所以精确查询时访问速度特别快，但是非精确查找时，比如like，这种范围查找，hash就起不到作用了。
适用场景：主要用于内容变化不频繁的表，或者作为中间的查找表。对表的更新要谨慎因为数据没有被写入到磁盘中，服务关闭前要考虑好数据的存储
四 MERGE
MERGE存储引擎把一组MyISAM数据表当做一个逻辑单元来对待，让我们可以同时对他们进行查询。构成一个MERGE数据表结构的各成员MyISAM数据表必须具有完全一样的结构。每一个成员数据表的数据列必须按照同样的顺序定义同样的名字和类型，索引也必须按照同样的顺序和同样的方式定义。

二、索引种类
普通索引：仅加速查询
唯一索引：加速查询 + 列值唯一（可以有null）
主键索引：加速查询 + 列值唯一（不可以有null）+ 表中只有一个
组合索引：多列值组成一个索引，专门用于组合搜索，其效率大于索引合并
创建索引的时机：
MySQL只对<，<=，=，>，>=，BETWEEN，IN，以及某些时候的LIKE才会使用索引
索引不生效的情况:
1、使用like关键字模糊查询时，第一个位置是任意字符
3、OR前后的两个条件中的列都是索引时，索引才会生效。
4、尽量避免在where子句中使用!=或<>操作符，否则全表扫描。
5、在 where 子句中对字段进行表达式 , 函数操作，否则全表扫描。
6、order by 索引 ，不起作用的问题（除了主键索引之外）：
　　1、 如果select 只查询索引字段，order by 索引字段会用到索引，要不然就是全表排列；
　　 2、如果有where 条件，比如where vtype=1 order by vtype asc . 这样order by 也会用到索引！
组合索引？在哪些场景中，组合索引会失效:
1、组合索引字段无论顺序如何改变都会用到索引，前提是所有字段都在where条件上
2、如果想要使用一个或者两个字段在where条件上，必须有组合索引里的第一个字段，但是与顺序无关，例如a,c或c,a，这种场景是可以命中索引的。但是，b,c或c,b这种是不会命中索引的。。
3、order by 只能使用a，才能用到索引
mysql的优化方法：
创建表考虑的问题：
1、选择最合适的字段属性
2、尽量把字段设置为NOT NULL
3 使用连接(JOIN)来代替子查询
4.连接查询推荐：inner join（内连接）
5.创建合适的索引

优化SQL的查询语句
1 不使用子查询
2 避免函数索引
3 用IN来替换OR
4 LIKE第一个位置是任意字符无法使用到索引
6 避免数据类型不一致
7 分组统计可以禁止排序
8 禁止不必要的ORDER BY排序
9 批量INSERT插入

为什么子查询比连接查询（LEFT JOIN）效率低：
执行子查询时，MYSQL需要创建临时表，查询完毕后再删除这些临时表，所以，子查询的速度会受到一定的影响，这里多了一个创建和销毁临时表的过程

事务：
原子性：一个事物(transaction)中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚(Rollback)到事务开始的状态，就像这个事务从来没有执行过一样。
一致性：在事务开始之前和事务结束之后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
隔离性：数据库允许多个事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同的级别，包括读未提交(Read uncommitted)、读已提交(Read committed)、可重复读(repeateable read)和串行化(Serializable).
持久性：事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。

事务隔离级别
读未提交
读已提交
可重复读
串行化
产生的问题：
1、脏读：事务A读取了事务B更新的数据，然后B回滚操作，那么A读取到的数据就是脏数据
2、不可重复读：事务A多次读取同一事物，事务B在事务A多次读取的过程中，对数据做了更新并提交，导致事务A多次读取同一数据时，结果不一致。
3、幻读：系统管理员A将数据库中的所有学生的成绩从具体分数改为ABCDE等级，但是系统管理员B就在这个时候插入了一条具体分数的记录，当系统管理员A改结束后发现还有一条记录没有改过来，就好像发生了幻觉一样，这就叫幻读。

锁

InnoDB的行锁是针对索引加的锁，不是针对记录加的锁。并且该索引不能失效，否则都会从行锁升级为表锁

行锁
行锁的劣势：开销大；加锁慢；会出现死锁
行锁的优势：锁的粒度小，发生锁冲突的概率低；处理并发的能力强
加锁的方式：自动加锁。对于UPDATE、DELETE和INSERT语句，InnoDB会自动给涉及数据集加排他锁；对于普通SELECT语句，InnoDB不会加任何锁；当然我们也可以显示的加锁：
共享锁：select * from tableName where … + lock in share more
排他锁：select * from tableName where … + for update
排他锁
排他锁，也称写锁，独占锁，当前写操作没有完成前，它会阻断其他写锁和读锁。
共享锁
共享锁，也称读锁，多用于判断数据是否存在，多个读操作可以同时进行而不会互相影响。当如果事务对读锁进行修改操作，很可能会造成死锁。

分析行锁定

通过检查InnoDB_row_lock 状态变量分析系统上的行锁的争夺情况
innodb_row_lock_current_waits: 当前正在等待锁定的数量
innodb_row_lock_time: 从系统启动到现在锁定总时间长度；非常重要的参数，
innodb_row_lock_time_avg: 每次等待所花平均时间；非常重要的参数，
innodb_row_lock_time_max: 从系统启动到现在等待最常的一次所花的时间；
innodb_row_lock_waits: 系统启动后到现在总共等待的次数；非常重要的参数。直接决定优化的方向和策略
行锁优化
1 尽可能让所有数据检索都通过索引来完成，避免无索引行或索引失效导致行锁升级为表锁。
2 尽可能避免间隙锁带来的性能下降，减少或使用合理的检索范围。
3 尽可能减少事务的粒度，比如控制事务大小，而从减少锁定资源量和时间长度，从而减少锁的竞争等，提供性能。
4 尽可能低级别事务隔离，隔离级别越高，并发的处理能力越低。

表锁
表锁的优势：开销小；加锁快；无死锁
表锁的劣势：锁粒度大，发生锁冲突的概率高，并发处理能力低
加锁的方式：自动加锁。查询操作（SELECT），会自动给涉及的所有表加读锁，更新操作（UPDATE、DELETE、INSERT），会自动给涉及的表加写锁。也可以显示加锁：
共享读锁：lock table tableName read;
独占写锁：lock table tableName write;
批量解锁：unlock tables;

共享读锁
对MyISAM表的读操作（加读锁），不会阻塞其他进程对同一表的读操作，但会阻塞对同一表的写操作。只有当读锁释放后，才能执行其他进程的写操作。在锁释放前不能取其他表。

独占写锁
对MyISAM表的写操作（加写锁），会阻塞其他进程对同一表的读和写操作，只有当写锁释放后，才会执行其他进程的读写操作。在锁释放前不能写其他表。

什么场景下用表锁
InnoDB默认采用行锁，在未使用索引字段查询时升级为表锁。
第一种情况：全表更新。
第二种情况：多表查询。
总结
1 InnoDB 支持表锁和行锁，使用索引作为检索条件修改数据时采用行锁，否则采用表锁。
2 InnoDB 自动给修改操作加锁，给查询操作不自动加锁
3 行锁可能因为未使用索引而升级为表锁，所以除了检查索引是否创建的同时，也需要通过explain执行计划查询索引是否被实际使用。
4 行锁相对于表锁来说，优势在于高并发场景下表现更突出，毕竟锁的粒度小。
5 当表的大部分数据需要被修改，或者是多表复杂关联查询时，建议使用表锁优于行锁。
6 为了保证数据的一致完整性，任何一个数据库都存在锁定机制。锁定机制的优劣直接影响到一个数据库的并发处理能力和性能。