MySql数据库知识学习笔记------索引，优化，事务
⼀.事务是什么，事务的四⼤特性？
⼆.并发事务带来的问题
脏读（Dirty read）: 当⼀个事务正在访问数据并且对数据进⾏了修改，⽽这种修改还没有提交到数据库
中，这时另外⼀个事务也访问了这个数据，然后使⽤了这个数据。因为这个数据是还没有提交的数据，那么另外⼀个事务读到的这个数据是“脏数据”，依据“脏数据”所做的操作可能是不正确的。
不可重复读（Unrepeatableread）: 指在⼀个事务内多次读同⼀数据。在这个事务还没有结束时，另⼀个事务也访问该数据。那么，在第⼀个事务中的两次读数据之间，由于第⼆个事务的修改导致第⼀个事务两次读取的数据可能不太⼀样。这就发⽣了在⼀个事务内两次读到的数据是不⼀样的情况，因此称为不可重复读。
程序员之家
幻读（Phantom read）: 幻读与不可重复读类似。它发⽣在⼀个事务（T1）读取了⼏⾏数据，接着另⼀个并发事务（T2）插⼊了⼀些数据时。在随后的查询中，第⼀个事务（T1）就会发现多了⼀些原本不存在的记录，就好像发⽣了幻觉⼀样，所以称为幻读。
不可重复读和幻读的区别是，⼀个是多次读同⼀条数据，发现数据的值不⼀样了；⼀个是读取数据时，数据本⾝并未变化，⽽是记录的数⽬变多了。
三.事务的四个隔离级别
1. READ-UNCOMMITTED(读取未提交)： 最低的隔离级别，允许读取尚未提交的数据变更，可能会导致脏读、幻读或不可重复读。
2. READ-COMMITTED(读取已提交)： 允许读取并发事务已经提交的数据，可以阻⽌脏读，但是幻读或不可重复读仍有可能发⽣。
3. REPEATABLE-READ(可重复读)： 对于当前的事务⽽⾔，对同⼀字段的多次读取结果都是⼀致的，除⾮数据是被本⾝事务⾃⼰所修
改，可以阻⽌脏读和不可重复读，但幻读仍有可能发⽣。
4. SERIALIZABLE(可串⾏化)： 最⾼的隔离级别，完全服从ACID的隔离级别。所有的事务依次逐个执⾏，这样事务之间就完全不可能
产⽣⼲扰，也就是说，该级别可以防⽌脏读、不可重复读以及幻读
MYSQL的默认隔离级别是可重复读
四.MySql数据库底层是B+树
为什么是B+树⽽不是B-树？？：
操作系统从磁盘读取数据到内存是以磁盘块（block）为基本单位的，位于同⼀个磁盘块中的数据会被⼀次性读取出来。即使只需要⼀个字节，磁盘也会从这个位置开始，顺序向后读取⼀定长度的数据放⼊内存。这样做的理论依据是计算机科学中著名的局部性原理。
由于B+Tree所有的数据都在叶⼦结点，并且结点之间有指针连接，在⼤于某个关键字或者⼩于某个
关键字的数据的时候，B+Tree只需要到该关键字然后沿着链表遍历就可以了，⽽B-Tree还需要遍历该关键字结点的根结点去搜索。
由于B-Tree的每个结点（⼀个数据块）都存储主键+实际数据，⽽B+Tree⾮叶⼦结点只存储关键字信息，⽽每个页的⼤⼩有限是有限的，所以同⼀页能存储的B-Tree的数据会⽐B+Tree存储的更少。这样同样总量的数据，B-Tree的深度会更⼤，增⼤查询时的磁盘I/O 次数，进⽽影响查询效率
五.乐观锁&悲观锁
悲观锁：总是假设最坏的情况，每次去拿数据的时候都认为别⼈会修改，所以每次在拿数据的时候都会上锁，这样别⼈想拿这个数据就会阻塞直到它拿到锁（共享资源每次只给⼀个线程使⽤，其它线程阻塞，⽤完后再把资源转让给其它线程）。Java中synchronized和ReentrantLock等独占锁就是悲观锁思想的实现。
乐观锁：总是假设最好的情况，每次去拿数据的时候都认为别⼈不会修改，所以不会上锁，但是在更新的时候会判断⼀下在此期间别⼈有没有去更新这个数据，可以使⽤版本号机制和CAS算法实现。乐观锁适⽤于多读的应⽤类型，这样可以提⾼吞吐量，像数据库提供的类似于write_condition机制，其实都是提供的乐观锁。在Java中urrent.atomic包下⾯的原⼦变量类就是使⽤了乐观锁的⼀种实现⽅式CAS实现的。
像乐观锁适⽤于写⽐较少的情况下（多读场景），即冲突真的很少发⽣的时候，这样可以省去了锁的开销，加⼤了系统的整个吞吐量。但如果是多写的情况，⼀般会经常产⽣冲突，这就会导致上层应⽤会不断的进⾏retry，这样反倒是降低了性能，所以⼀般多写的场景下⽤悲观锁就⽐较合适。
六.乐观锁的实现⽅式
版本号机制：⼀般是在数据表中加上⼀个数据版本号version字段，表⽰数据被修改的次数，当数据被修改时，version值会加⼀。当线程A要更新数据值时，在读取数据的同时也会读取version值，在提交更新时，若刚才读取到的version值为当前数据库中的version值相等时才更新，否则重试更新操作，直到更新成功。
CAS：compare and swap（⽐较与交换），是⼀种⽆锁算法。⽆锁编程，即不使⽤锁的情况下实现多线程之间的变量同步，也就是在没有线程被阻塞的情况下实现变量的同步，所以也叫⾮阻塞同步（Non-blocking Synchronization）。CAS算法涉及到三个操作数：
需要读写的内存 V
进⾏⽐较的值A
新值B
当且仅当 V 的值等于 A时，CAS通过原⼦⽅式⽤新值B来更新V的值，否则不会执⾏任何操作（⽐较和替换是⼀个原⼦操作）。⼀般情况下是⼀个⾃旋操作，即不断的重试。
七.⾃旋锁
是指当⼀个线程在获取锁的时候，如果锁已经被其它线程获取，那么该线程将循环等待，然后不断的判断锁是否能够被成功获取，直到获取到锁才会退出循环。
⼋.MyISAM和InnoDB存储引擎使⽤的锁：
MyISAM 采⽤表级锁(table-level locking)
InnoDB ⽀持⾏级锁(row-level locking)和表级锁，默认为⾏级锁
表级锁：Mysql中锁定粒度最⼤ 的⼀种锁，对当前操作的整张表加锁，实现简单，资源消耗也⽐较少，加锁快，不会出现死锁。其锁定粒度最⼤，触发锁冲突的概率最⾼，并发度最低，MyISAM和 InnoDB引擎都⽀持表级锁。
⾏级锁： Mysql中锁定 粒度最⼩ 的⼀种锁，只针对当前操作的⾏进⾏加锁。 ⾏级锁能⼤⼤减少数据库操作的冲突。其加锁粒度最⼩，并发度⾼，但加锁的开销也最⼤，加锁慢，会出现死锁。
九.⼤表优化
常见的优化措施：
1. 限定数据的范围：务必禁⽌不带任何限制数据范围条件的查询语句。⽐如：我们当⽤户在查询订单历史的时候，我们可以控制在⼀个
⽉的范围内；
2. 读/写分离：经典的数据库拆分⽅案，主库负责写，从库负责读；
3. 垂直分区：根据数据库⾥⾯数据表的相关性进⾏拆分。 例如，⽤户表中既有⽤户的登录信息⼜有⽤户的基本信息，可以将⽤户表拆分
成两个单独的表，甚⾄放到单独的库做分库。
4. 垂直拆分的优点缺点：优点：可以使得列数据变⼩，在查询时减少读取的Block数，减少I/O次数。此外，垂直分区可以简化表的结
构，易于维护。缺点：主键会出现冗余，需要管理冗余列，并会引起Join操作，可以通过在应⽤层进⾏Join来解决。此外，垂直分区会让事务变得更加复杂；
5. ⽔平分区：保持数据表结构不变，通过某种策略存储数据分⽚。这样每⼀⽚数据分散到不同的表或者库中，达到了分布式的⽬的。 ⽔
平拆分可以⽀撑⾮常⼤的数据量。**⽔平拆分可以⽀持⾮常⼤的数据量。需要注意的⼀点是：分表仅仅是解决了单⼀表数据过⼤的问题，但由于表的数据还是在同⼀台机器上，其实对于提升MySQL并发能⼒没有什么意义，所以 ⽔平拆分最好分库 **
6. ⽔平拆分能够 ⽀持⾮常⼤的数据量存储，应⽤端改造也少，但 分⽚事务难以解决 ，跨节点Join性能较差，逻辑复杂。《Java⼯程师
修炼之道》的作者推荐 尽量不要对数据进⾏分⽚，因为拆分会带来逻辑、部署、运维的各种复杂度 ，⼀般的数据表在优化得当的情况下⽀撑千万以下的数据量是没有太⼤问题的。如果实在要分⽚，尽量选择客户端分⽚架构，这样可以减少⼀次和中间件的⽹络I/O。
⼗.SQL查询优化
可通过开启慢查询⽇志查出较慢的SQL.
不做列运算: SELECT id WHERE age+1=10 ，任何对列的操作都将导致全表扫描.它包括数据库教程函数，计算表达式等等，查询时尽量将操作移⾄等号右边. —>age=10-1
SQL语句尽可能简单:⼀条SQL只能在⼀个cpu进⾏运算;⼤语句拆分成⼩语句，减少锁时间;⼀条⼤SQL可以堵死整个库.
不⽤ SELECT *.
少⽤ JOIN.
避免 %xxx式查询.
不⽤函数和触发器，在应⽤程序实现.
尽量避免在WHERE ⼦句中使⽤!= <>操作，否则将导致引擎放弃索引使⽤全表扫描.
⼗⼀.InnoDB引擎的锁机制
共享锁（S）：允许⼀个事务去读⼀⾏，阻⽌其他事务获得相同数据集的排他锁。解释： ⾃⾝可以读该资源，其他⼈也可以读该资源（也可以再继续加共享锁，即 共享锁可多个共存），但⽆法修改（⽆法进⾏添加排他锁的操作）。
排他锁（X)：允许获得排他锁的事务更新数据，阻⽌其他事务取得相同数据集的共享读锁和排他写锁。解释： 对某⼀资源加排他锁，⾃⾝可以进⾏增删改查，其他⼈⽆法进⾏任何操作
int指针占几个字节
数据库规定同⼀资源上不能同时共存共享锁和排他锁
共享锁和排他锁都是⾏级锁，
//下⾯的语句为该⾏资源添加共享锁
SELECT*FROM table_LOCK IN SHARE MODE
java培训机构线上//下⾯的语句为该⾏资源添加排他锁
SELECT*FROM table_FOR UPDATE
意向共享锁（IS）：事务打算给数据⾏加⾏共享锁，事务在给⼀个数据⾏加共享锁前必须先取得该表的IS锁。
意向排他锁（IX）：事务打算给数据⾏加⾏排他锁，事务在给⼀个数据⾏加排他锁前必须先取得该表的IX锁。
意向锁都是表锁，应⽤中我们只会使⽤到共享锁和排他锁，意向锁是mysql内部使⽤的，不需要⽤户⼲预。
mysql面试题acid
意向锁存在的意义在于，使得⾏锁和表锁能够共存。
意向锁是表级别的锁，⽤来说明事务稍后会对表中的数据⾏加哪种类型的锁(共享锁或独占锁)。
当⼀个事务对表加了意向排他锁时，另外⼀个事务在对表加锁前就会通过该表的意向排他锁知道前⾯已经有事务在对该表进⾏独占操作，从⽽等待。另⼀个事务如果想对⾏加锁，则不会影响。
假设⼀个表有⼗万⾏数据，有⼀个事务想对其中的⼏⾏进⾏操作，如果没有意向锁机制，另⼀个事务过来的时候，只能遍历⼗万⾏数据看原来的事务占⽤了哪⼏⾏，这样效率很低。有了IX锁，新的事务就会知道有事务在对表进⾏操作。
⼗⼆.Mysql数据库逻辑分层
⼗三.Mysql解析过程
编写过程：select dinstinct …from …join …on …where …group by …having …order by …limit …
解析过程：from … on… join …where …group by …having …select dinstinct …order by limit …
⼗四.⾏锁和表锁区别
表锁 是通过unlock tables，也可以通过事务解锁 ; ⾏锁 是通过事务解锁。
特斯拉异步电机⾏锁，⼀次锁⼀⾏数据；因此 如果操作的是不同数据，则不⼲扰。
如果没有索引或者索引失效，则⾏锁会转为表锁
Innodb的⼆级索引是怎么回事？
⼆级索引：叶⼦节点中存储主键值，每次查数据时，根据索引到叶⼦节点中的主键值，根据主键值再到聚簇索引中得到完整的⼀⾏记录。
今日郴州新闻问题：
1.相⽐于叶⼦节点中存储⾏指针，⼆级索引存储主键值会占⽤更多的空间，那为什么要这样设计呢？
InnoDB在移动⾏时，⽆需维护⼆级索引，因为叶⼦节点中存储的是主键值，⽽不是指针。
2.那么InnoDB有了聚簇索引，为什么还要有⼆级索引呢？
聚簇索引的叶⼦节点存储了⼀⾏完整的数据，⽽⼆级索引只存储了主键值，相⽐于聚簇索引，占⽤的空间要少。当我们需要为表建⽴多个索引时，如果都是聚簇索引，那将占⽤⼤量内存空间，所以InnoDB中主键所建⽴的是聚簇索引，⽽唯⼀索引、普通索引、前缀索引等都是⼆级索引。
3.为什么⼀般情况下，我们建表的时候都会使⽤⼀个⾃增的id来作为我们的主键？

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。