MySql面试精选7-12
| 题号 | 题目 |
| 7 | Mysql中乐观锁和悲观锁区别 |
| 8 | Mysql索引主要是哪些 |
| 9 | Mysql联合索引最左匹配原则 |
| 10 | 聚簇索引和非聚簇索引区别 |
| 11 | 如何查询一个字段是否命中了索引 |
| 12 | Mysql中查询数据什么情况下不会命中索引 |
7. Mysql中乐观锁和悲观锁区别
- 悲观锁(Pessimistic Lock)
悲观锁顾名思义，就是很悲观，每次去拿数据的时候都认为别人会修改，所以每次在拿数据的时候都会上锁，这样别人想拿这个数据就会block直到它拿到锁。
传统的关系型数据库里边就用到了很多这种锁机制，比如行锁，表锁等，读锁，写锁等，都是在做操作之前先上锁。
- 乐观锁(Optimistic Lock)
乐观锁顾名思义，就是很乐观，每次去拿数据的时候都认为别人不会修改，所以不会上锁，但是在更新的时候会判断一下在此期间别人有没有去更新这个数据，可以使用版本号等机制。
乐观锁适用于多读的应用类型，这样可以提高吞吐量，像数据库如果提供类似于write_condition机制的其实都是提供的乐观锁。
乐观锁的特点先进行业务操作，不到万不得已不去拿锁。即“乐观”的认为拿锁多半是会成功的，因此在进行完业务操作需要实际更新数据的最后一步再去拿一下锁就好。
乐观锁在数据库上的实现完全是逻辑的，不需要数据库提供特殊的支持。一般的做法是在需要锁的数据上增加一个版本号，或者时间戳，然后按照如下方式实现：
1. SELECT data AS old_data, version AS old_version FROM …; 2. 根据获取的数据进行业务操作，得到new_data和new_version 3. UPDATE SET data = new_data, version = new_version WHERE version = old_version if (updated row > 0) { // 乐观锁获取成功，操作完成 } else { // 乐观锁获取失败，回滚并重试 }
乐观锁是否在事务中其实都是无所谓的，其底层机制是这样：在数据库内部update同一行的时候是不允许并发的，即数据库每次执行一条update语句时会获取被update行的写锁，直到这一行被成功更新后才释放。
因此在业务操作进行前获取需要锁的数据的当前版本号，然后实际更新数据时再次对比版本号确认与之前获取的相同，并更新版本号，即可确认这之间没有发生并发的修改。如果更新失败即可认为老版本的数据已经被并发修改掉而不存在了，此时认为获取锁失败，需要回滚整个业务操作并可根据需要重试整个过程。
两种锁各有优缺点，不可认为一种好于另一种，像乐观锁适用于写比较少的情况下，即冲突真的很少发生的时候，这样可以省去了锁的开销，加大了系统的整个吞吐量。
但如果经常产生冲突，上层应用会不断的进行retry，这样反倒是降低了性能，所以这种情况下用悲观锁就比较合适。
那如果必须在高并发下写多的场景要用乐观锁呢？
听说阿里是这样解决的:使用条件限制实现乐观锁
表结构如下：
mysql> select * from t_goods; +----+--------+------+---------+ | id | status | name | num | +----+--------+------+---------+ | 1 | 1 | 手环 | 10 | | 2 | 2 | 鞋子 | 10 | +----+--------+------+---------+
status表示产品状态：1、在售。2、暂停出售。num表示产品库存，更新库存操作如下：
UPDATE t_goods SET num = num - #{buyNum} WHERE id = #{id} AND num - #{buyNum} >= 0 AND STATUS = 1
说明：num-#{buyNum}>=0 ，这个情景适合不用版本号，只更新是做数据安全校验，适合库存模型，扣份额和回滚份额，性能更高。
注意：乐观锁的更新操作，最好用主键或者唯一索引来更新,这样是行锁，否则更新时会锁表
有事务了为啥还需要乐观锁悲观锁呢？
- 事务是粗粒度的、乐观锁悲观锁可以更细粒度的控制，比如抢票，假设余票只有1张；隔离级别可以保证事务A和事务B不能读到对方的数据，也不能更新对方正在更新的数据，但是事务A和事务B都认为还有1张余票，于是出票，并更新为0；
- 事务解决了并发问题，已经不存在并发问题了；但是事务B读取的是过时数据，依据过时数据做了业务处理；所以需要乐观锁或者悲观锁，来记录一个信息：当前已经读取的数据，是不是已经过时！
- 事务不能保证每个操作结果正确，售票时超卖还是会发生。
- 事务保证整个操作的成一个组，要么全做要么全不做 但是不能保证多个事务同时读取同一个数据
- 数据对象被加上排它锁时，其他的事务不能对它读取和修改；加了共享锁的数据对象可以被其他事务读取，但不能修改
- 事务可以用锁实现，可以保证一致性和隔离性，但是锁用来保证并发性；
- 隔离性和并发性有点类似，但是隔离性只是保证不会出现相互读取中间数据，却无法解决并发的问题
参考：https://www.cnblogs.com/laoyeye/p/8097684.html#top
8. Mysql索引主要是哪些
索引的目的在于提高查询效率. 主要得益于B+树这种数据结构，三层就可以存储亿级别有序(主键索引)数据，而且数据的查找都是按照类似二分这样的算法在树中进行查找，速度是非常快的。
索引的类型：
- UNIQUE(唯一索引)：不可以出现相同的值，可以有NULL值
- INDEX(普通索引)：允许出现相同的索引内容
- PROMARY KEY(主键索引)：不允许出现相同的值
- fulltext index(全文索引)：可以针对值中的某个单词，但效率确实不敢恭维
- 联合索引：实质上是将多个字段建到一个索引里，列值的组合必须唯一
索引的缺点：
- 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT,UPDATE和DELETE。因为更新表时，mysql不仅要保存数据，还要保存一下索引文件.
- 建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在要给大表上建了多种组合索引，索引文件会膨胀很宽, 索引只是提高效率的一个方式，如果mysql有大数据量的表，就要花时间研究建立最优的索引，或优化查询语句。
8.1 使用索引时，有一些技巧
- 索引不会包含有NULL的列只要列中包含有NULL值，都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此符合索引就是无效的。
- 使用短索引 对串列进行索引，如果可以就应该指定一个前缀长度。例如，如果有一个char（255）的列，如果在前10个或20个字符内，多数值是唯一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。
- 索引列排序 mysql查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作，尽量不要包含多个列的排序，如果需要最好给这些列建复合索引。
- like语句操作 一般情况下不鼓励使用like操作，如果非使用不可，注意正确的使用方式。like ‘%aaa%’不会使用索引，而like ‘aaa%’可以使用索引。
- 不要在列上进行运算 比如where age/3<40 或者avg(age) = 3
- 不使用NOT IN 、<>、！=操作，但<,<=，=，>,>=,BETWEEN,IN是可以用到索引的
- 索引要建立在经常进行select操作的字段上 这是因为，如果这些列很少用到，那么有无索引并不能明显改变查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
- 索引要建立在值比较唯一的字段上
- 对于那些定义为text、image和bit数据类型的列不应该增加索引。因为这些列的数据量要么相当大，要么取值很少
- 在where和join中出现的列需要建立索引
- where的查询条件里有不等号(where column != …),mysql将无法使用索引
- 如果where字句的查询条件里使用了函数(如：where DAY(column)=…),mysql将无法使用索引
- 在join操作中(需要从多个数据表提取数据时)，mysql只有在主键和外键的数据类型相同时才能使用索引，否则及时建立了索引也不会使用
8.2 联合索引的作用
- 减少开销建一个联合索引(col1,col2,col3)，实际相当于建了(col1),(col1,col2),(col1,col2,col3)三个索引。每多一个索引，都会增加写操作的开销和磁盘空间的开销。对于大量数据的表，使用联合索引会大大的减少开销。
- 覆盖索引通常指一个查询语句的执行只用从索引中就能够取得，不必从数据表中读取。也可以称之为实现了索引覆盖。对联合索引(col1,col2,col3)，如果有如下的sql: select col1,col2,col3 from test where col1=1 and col2=2。那么MySQL可以直接通过遍历索引取得数据，而无需回表，这减少了很多的随机io操作。减少io操作，特别的随机io其实是dba主要的优化策略。所以，在真正的实际应用中，覆盖索引是主要的提升性能的优化手段之一。
- 效率高 索引列越多，通过索引筛选出的数据越快。
9. Mysql联合索引最左匹配原则
最左前缀匹配原则：
- 在MySQL建立联合索引时会遵守最左前缀匹配原则，即最左优先.
在检索数据时从联合索引的最左边开始匹配，Mysql会一直向右匹配直到遇到范围查询（>、<、between、like）就停止匹配了.
就比如 a=3 and b=4 and c>5 and d=6 如果建立(abcd)顺序的索引,d就用不到索引了，如果建立(abdc)的索引则都可以用到索引，其中abd的顺序可以任意调整，因为查询优化器会重新编排（即使是c>5 and b=4 and d=6 and a=3也会全部用到 abdc索引 ）.
- =和in可以乱序，比如a=1 and b=2 and c=3 建立(abc)索引可以任意顺序，mysql查询优化器会优化顺序.
这里需要注意下, 比如abc索引 那么只要查询条件有a即可用到abc索引（如abc ab ac a）,没有a就用不到。
最左前缀匹配成因：Mysql是创建复合索引的规则是根据索引最左边的字段进行排序，在第一个字段排序的基础上再进行第二个字段排序，类似于order by col1，col2… 所以第一个字段是绝对有序的 第二个字段就是无序的了，所以Mysql 强调最左前缀匹配.
10. 聚簇索引和非聚簇索引区别
聚簇索引与非聚簇索引的区别是：叶子节点是否存放一整行记录.
InnoDB 主键使用的是聚簇索引，MyISAM 不管是主键索引，还是二级索引使用的都是非聚簇索引。
- 对于聚簇索引表来说（左图），表数据是和主键一起存储的，主键索引的叶结点存储行数据(包含了主键值)，二级索引的叶结点存储行的主键值。使用的是B+树作为索引的存储结构，非叶子节点都是索引关键字，但非叶子节点中的关键字中不存储对应记录的具体内容或内容地址。叶子节点上的数据是主键与具体记录(数据内容)。
- 对于非聚簇索引表来说（右图），表数据和索引是分成两部分存储的，主键索引和二级索引存储上没有任何区别。使用的是B+树作为索引的存储结构，所有的节点都是索引，叶子节点存储的是索引+索引对应的记录的数据。
因此, 聚簇索引的叶子节点就是数据节点，而非聚簇索引的叶子节点仍然是索引节点，只不过有指向对应数据块的指针.
11. 如何查询一个字段是否命中了索引
通过explain sql可看下SQL是否走了索引，很快对比出来 .
当一个sql中索引字段为int类型时，例如搜索条件where num="111"与where num=111都可以使用该字段的索引. 当一个中索引字段为varchar类型时，例如搜索条件where num="111"可以使用索引，where num=111不可以使用索引.
12. Mysql中查询数据什么情况下不会命中索引
通常不命中索引有接种情况:
- 索引规范不合理,sql解析器不命中索引.
- 表中索引是以表中数据量字段最多的建立的索引,sql解析器不命中索引.(实际就是索引没用,最后全局查找了)
- bool的字段做索引,sql选择器不命中索引.
- 模糊查询 %like
- 索引列参与计算,使用了函数
- 非最左前缀顺序
- where对null判断
- where不等于
- or操作有至少一个字段没有索引
- 需要回表的查询结果集过大（超过配置的范围）
