MySQL

MySQL

MySQL体系结构

存储引擎

MyISAM

早些年使用的节约空间，速度较快

InnoDB

安全性高，事务的处理，多表多用户操作每次磁盘IO会利用局部性取出，一页数据大小默认为16kb，，每页的数据都是默认按主键顺序存储，包含内容：页头：头尾指针页目录：数组结构，给数据区分组，每6条数据为一组，按分组查找对应数据区，目录也会用二分法进行查找用户数据区：链表结构，每一条数据，数据库会根据字段设置设置好固定的空间，varchar等不确定的空间会直接存放指针

区别

                     MYISAM    INNODB事务支持       不支持        支持数据行锁定    不支持        支持外键约束        不支持        支持全文索引         支持          不支持表空间大小      较小          较大，约两倍

索引：索引（Index）是帮助MySQL高效获取数据的数据结构。提取句子主干，就可以得到索引的本质：索引是数据结构1.在创建表的时候给字段增加索引2.创建完毕后，增加索引MYSQL8索引可以进行ESC DESC升序降序排列

数据结构角度

B+树索引

B树为什么性能高：主存读取时不存在机械操作，即经过地址总线请求，输出的数据总线无论数据所在什么为止都是固定的时间磁盘存取存在机械操作，即移动磁头至磁道，移动盘片至扇区才可以读取数据，每次加载一个或多个页的数据至主存一次B树检索最多访问h个节点（树高），数据库的设计利用了磁盘预读原理（一次从磁盘读取一页数据），将一个节点的大小设为等于一个页，这样每个节点只需要一次IO就可以完全载入。特点：1.叶子节点有指针，2.一个节点里面可以存多个元素，3.所有的非叶子节点不存储数据MYSQL的B+树叶子节点是双向指针，为了优化查询<

B-树与B+树的区别：B+叶子节点有指针，B没有B+数据都在叶子节点上，B不是

Hash索引

查询单条快，范围查询慢

Full-Text索引

R-Tree索引

物理存储角度

聚集索引

InnoDB的索引方式就是聚集索引，即数据文件本身就是索引文件在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，这个字段长度为6个字节，类型为长整形第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。换句话说，InnoDB的所有辅助索引都引用主键作为data域

了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在InnoDB中不是个好主意，因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，十分低效，而使用自增字段作为主键则是一个很好的选择。

非聚集索引，也叫辅助索引

MyISAM的索引方式为“非聚集的”，索引文件和数据文件是分离的data域保存数据记录的地址，索引文件仅保存数据记录的地址

逻辑角度

普通索引Index

加速查找

唯一索引

主键索引 PRIMARY　KEY

加速查找+约束（不为空且唯一）B+树的主键索引：唯一的标识，不可重复，只能有一个列作为主键，若没有主键，会创建隐藏主键主键索引会以主键的顺序来排序生成一个索引目录，为主键+字段为用户数据区而每个数据区会链表结构以6条为一组分为数组结构的页目录，每个页中存储的为数固定的数据区+上一个主键索引 页目录如果数据大于16K，则以页为单位，页又是一个链表，就需要再建立一个以页首个数据的主键值组成的一个页目录。

唯一索引UNIQUE KEY

加速查找+约束（唯一）

联合索引

联合索引就是把主键索引的主键顺序替换为 A/B/C 顺序，即 先比较A再比较B再比较C的顺序再这个排序基础上建立索引，且建立的索引只包含bcd并会指向真实数据的主键

最左原则：因为多列索引是按照ABC的顺序排序的索引以A或AB来查询会走多列索引的数据顺序，索引可以利用索引查询但BC 或 C 无法判断出多列索引中 数据的顺序，索引无法查询

全文索引FullText

特定的数据库引擎才有，MyISMA用于搜索很长一篇文章

空间索引SPATIAL

几乎不用

事务

四大特性

1.原子性（Atomicity）：原子性是指事务包含的所有操作要么全部成功，要么全部失败回滚。失败回滚的操作事务，将不能对事务有任何影响。2. 一致性（Consistency）：一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说一个事务执行之前和执行之后都必须处于一致性状态。3. 隔离性（Isolation）：　　隔离性是指当多个用户并发访问数据库时，比如同时访问一张表，数据库每一个用户开启的事务，不能被其他事务所做的操作干扰(也就是事务之间的隔离)，多个并发事务之间，应当相互隔离。4. 持久性（Durability）　　持久性是指事务的操作，一旦提交，对于数据库中数据的改变是永久性的，即使数据库发生故障也不能丢失已提交事务所完成的改变。

四个隔离级别

读未提交READ UNCOMMITTED

一个事务能够读取到 别的事务中没有提交的更新数据。事务可以读取到未提交的数据，这也被称为脏读(dirty read)。问题：脏读，不可重复读，幻读

读已提交READ COMMITTED

一个事务只能读取到别的事务提交的更新数据。不可重复读：同一个事务对数据的多次读取的结果不一致。每次读取都生成一个ReadView问题：不可重复读，幻读      解决：脏读

可重复读REPEATBABLE READ

保证同一事务中先后执行的多次查询将返回同意结果，不受其他事务的影响。这种隔离级别可能出现幻读。（mysql使用行锁解决幻读）第一次读取生成ReadView问题：幻读   解决：脏读。不可重复读

串行化SERIALIZABLE

不允许事务并发执行，强制事务串行执行，就是在读取的每一行数据上都加上了锁，读写相互都会阻塞。这种隔离级别最高，是最安全的，性能最低，不会出现脏读，不可重复读，幻读，丢失更新。

事务的传播机制

propagation_requierd：            如果当前没有事务，就新建一个事务，如果已存在一个事务中，加入到这个事务中，这是最常见的选择。propagation_supports：           支持当前事务，如果没有当前事务，就以非事务方法执行。propagation_mandatory：        使用当前事务，如果没有当前事务，就抛出异常。propagation_required_new：    新建事务，如果当前存在事务，把当前事务挂起。propagation_not_supported：  以非事务方式执行操作，如果当前存在事务，就把当前事务挂起。propagation_never：                 以非事务方式执行操作，如果当前事务存在则抛出异常。propagation_nested：               如果当前存在事务，则在嵌套事务内执行。如果当前没有事务，则执行与propagation_required类似的操作

MVCC多版本并发控制

版本链

对于使用InnoDB存储引擎的表来说，它的聚簇索引记录中都包含两个必要的隐藏列（row_id并不是必要的，我们创建的表中又主键或者非NULL唯一键时都不会包含row_id列）tx_id：每次对某条记录进行改动时，都会把对应的事务id赋值给trx_id隐藏列roll_pointer：每次都某条记录进行改动时，这个隐藏列会存一个指针，可以通过这个指针找到该记录修改前的信息

ReadView

对于使用Read UNCOMMITTED隔离级别的事务来说，直接读取记录的最新版就好了，对于使用SERIALIZABLE隔离级别的事务来说。使用加锁的方式来访问记录。对于使用READ COMMITTED和REPEATABLE READ隔离级别来说，就需要使用版本链了。核心问题：需要判断一下版本链中那个版本是当前事务可见的。

四个重要内容：m_ids : 表示生成ReadView时当前系统中活跃的读写事务的事务id列表min_trx_id : 表示在生成ReadView时当前系统中活跃的读写事务中最小的事务id，也就是m_ids中的最小值max_trx_id : 表示生成ReadView时系统中应该分配给下一个事务的id值creator_trx_id ： 表示生成该ReadView的事务的事务id

锁

读写锁

读锁：共享锁，Shared Locks，S锁写锁：排他锁，Exclusive Locks，X锁select：不加锁

         S锁        X锁S锁    不冲突   冲突X锁    冲突      冲突

读锁：对于普通的SELECT语句，InnoDB不会加任何锁加一个S锁 select ... lock in share mode 使用场景：读出数据后，其他事务不能修改，但是自己也不一定能修改，因为读锁可以多个事务添加。

写锁：将查找的数据加一个X锁，不允许其他事务进行加锁 ， 提交或回滚事务时候释放锁加一个X锁：select ... for update使用场景: 读出数据后，其他事务既不能写，也不能加读锁，那么就导致只有自己可以修改数据。

行锁

LOCK_REC_NOT_GAP : 单个行记录上的锁LOCK_GAP :  间隙锁，锁定一个范围，但不包括记录本身。GAP锁的目的，是为了防止同一事务的两次当前读，出现幻读的情况LOCK_ORDINARY : 锁定一个范围，并且锁定记录本身。对于行的插入，都是采用该方法，主要目的是解决幻读问题

读已提交：加的是单行锁锁只会加在查询出来的数据上，行锁Insert 可以插入数据

可重复读： 基于索引加的是一个间隙锁 为了解决幻读 Insert 插入不了已经加锁的数据区全表扫描情况下 因为所有的都可以修改，相当于把所有的行锁间隙锁都锁住

写操作

DELETE : 删除一条数据时，先加X锁，再执行删除操作INSERT : 先加隐式锁保护这条数据再事务提交前不被其他事务访问到UPDATE：如果被更新的列修改签后没有导致存储空间变化，那么先加X锁，再对其修改。如果变化，先加X锁，再删除，再INSERT一条新数据

MySQL调优

0.先运行看看是否真的很慢，注意设置SQL_NO_CACHE1.where条件单表查，锁定最小返回记录表。这句话的意思是把查询语句的where都应用到表中返回的记录数最小的表开始查起，单表每个字段分别查询，看哪个字段的区分度最高2.explain查看执行计划，是否与1预期一致（从锁定记录较少的表开始查询）3.order by limit 形式的sql语句让排序的表优先查4.了解业务方使用场景5.加索引时参照建索引的几大原则6.观察结果，不符合预期继续从0分析

MySQL语句

数据表操作

索引

显示所有索引信息：Show INDEX FROM xxx 增加一个全文索引：ALTER TABLE xxx库.xxx表 ADD FULLTEXT INDEX `索引名`(`索引列`);分析sql执行的状况：EXPLAIN sql语句

创建索引

create index index_name on table_name (column_list [DSC/DESC]) ;create unique index index_name on table_name (column_list) ;

删除索引

drop index index_name on table_name ;alter table table_name drop index index_name ;alter table table_name drop primary key ;

表链接JOIN

分组 GROUP BY

排序 ORDER BY

多表查询 UNION

NULL值处理

插入数据 INSERT INTO

删除数据 DELETE

如果没有指定 WHERE 子句，MySQL 表中的所有记录将被删除。DELETE FROM table_name [WHERE Clause]

更新 UPDATE

LIKE 子句

数据库操作

使用mysql二进制方式连接

[root@host]# mysql -u root -pEnter password:******

创建数据库

CREATE DATABASE 数据库名;

删除数据库

drop database <数据库名>;

选择数据库

[root@host]# mysql -u root -pEnter password:******mysql> use RUNOOB;Database changedmysql>

数据类型

类型  范围\t格式\t用途DATE             1000-01-01/9999-12-31                              YYYY-MM-DD                     日期值TIME              '-838:59:59'/'838:59:59'                               HH:MM:SS                           时间值或持续时间YEAR             1901/2155                                                    YYYY                                    年份值DATETIME     1000-01-01 00:00:00/9999-12-31 23:59:59\tYYYY-MM-DD HH:MM:SS   混合日期和时间值TIMESTAMP  1970-01-01 00:00:00/2038                           YYYYMMDD HHMMSS       混合日期和时间值，时间戳

数据库链接