MySQL数据库相关基础知识

一.约束

1.约束概述

（1）在创建表时，可以给表中的字段加一些约束，约束的作用是来保证表中数据的完整性和有效性；
（2）约束的种类：非空约束(not null)、唯一性约束(unique)、主键约束(primary key，简称PK)、外键约束(foreign key，简称FK)、检查约束(check，MySQL不支持，Oracle支持)
（3）列约束：约束直接添加在列后面；
（4）表约束：约束不直接添加在列的后面，如unique(name,email)两个字段联合起来唯一，这种约束称为表级约束，给多个字段联合起来添加某一个约束时需要使用表级约束；
（5）not null只有列级约束，没有表级约束；
（6）在MySQL中，如果一个字段同时被not null和unique约束，该字段自动变成主键字段；
（7）主键约束中的主键值是每一行记录的身份证号，任何一张表都应该有主键且只有一个，没有主键，表是无效的；主键的特征：not null + unique(不能为空，也不能重复)；也可以两个字段联合起来做主键，如primary key(id,name)称为复合主键(不建议使用复合主键)；
（8）子表中的外键引用父表中的某个字段，被引用的这个字段不一定是主键，但至少有unique约束；

2.批量执行SQL语句

（1）批量执行SQL语句时，可以使用sql脚本文件；
（2）在mysql中执行sql脚本文件的方法：source + 文件路径；

3…join连接

内连接：inner join 列出两个表中都存在的记录
外连接：left join即使没有匹配也列出左表上的所有记录
右连接：right join即使没有匹配也列出右表上的所有记录。
select 字段名 as from 表1and表2 where 字句
为什么索引的数据结构是B+树：
B+树的检索数据的速度快，B+树将叶子节点进行排序，通过一个链表将所有叶子节点连接起来，通过链表解决平衡二叉树的回旋查找的问题。

索引的优点：数据检索的速度快，创建唯一索引，保证每条行数据的唯一性
索引的缺点：索引需要占用物理地址，增删改的时候也需要动态维护索引，增加开销，

B+树和B树的区别：
B+树：非叶子节点只存储键的信息，叶子结点存储数据信息，所有叶子结点都有一个链指针
B树：每个节点都存储键和数据。
b+树相比于b树的查询优势：

b+树的中间节点不保存数据，所以磁盘页能容纳更多节点元素，更“矮胖”；
b+树查询必须查找到叶子节点，因此b+树查找更稳定，查询效率更高（并不慢）；
对于范围查找来说，b+树只需遍历叶子节点链表即可，b树却需要重复地中序遍历

二.存储引擎

1.相关知识点

（1）存储引擎是MySQL中特有的一个术语，其他数据库中没有；
（2）存储引擎是一个表存储/组织数据的方式；
（3）用SQL语句建表时，ENGINE来指定存储引擎，CHARSET来指定这张表的字符编码格式；
（4）MySQL默认的存储引擎时InnoDB，默认的字符编码方式是utf8；
（5）MySQL支持九大存储引擎，常用的有InnoDB，MyISAM，MEMORY；
（6）MyISAM存储引擎的特点：使用三个文件表示每个表，包括格式文件—存储表结构的定义(mytable.frm)、数据文件—存储表行的内容(mytable.MYD)、索引文件—存储表上的索引(mytable.MYI)；其优势为：可以转换为压缩、只读表来节省空间；
（7）InnoDB存储引擎的特点：非常安全，支持事务，支持数据库崩溃后自动恢复机制；每个InnoDB表在数据库目录中以.frm格式文件表示；InnoDB表空间tablespace被用于存储表内容；提供一组用来记录事务性活动的日志文件；用COMMIT、SAVEPOINT、ROLLBACK支持事务处理；提供全ACID兼容；多版本(MVCC)和行级锁定；支持外键及引用的完整性，包括级联删除和更新；
（8）InnoDB和MyISAM的区别：MyISAM不支持事务且安全性低，InnoDB支持事务且安全性高是默认的，但InnoDB不能压缩，不能转换为只读，不能很好的节省空间；
（9）MEMORY存储引擎的特点：查询效率高，但不安全，关机之后数据消失；

2.innodb的使用：

（1）使用B+树实现，查找某一键值的行记录时，在主键里是1次，不在主键里2次。
聚簇索引将数据存储和索引放在一起，叶子节点保存行数据。分为主键索引和辅助索引，辅助索引存储主键值，辅助索引访问要二次查找。
非聚簇索引将数据与索引分开存储，叶子结点指向数据对应的位置。辅助索引存储的是地址，主键索引和辅助所以都指向表数据。

三.事务

1.事务概述

（1）一个事务其实就是一个完整的业务逻辑，是一个最小的工作单元，不可再分；
（2）只有DML语句(insert、delete、update)才会有事务这一说，其他语句和事务无关；
（3）一个事务其实就是多条DML语句同时成功或同时失败；
（4）提交事务(commit)：、回滚事务(rollback)；
（5）MySQL中默认是自动提交的，用start transaction来关闭自动提交机制；
（6）回滚是回滚到上一次提交的位置；

2.事务特性

（1）ACID特性：原子性（原子性是指一个事务是一个不可分割的工作单位，其中的操作要么都做，要么都不做）、一致性（事务同时成功同时失败）、隔离性（隔离性是指，事务内部的操作与其他事务是隔离的，并发执行的各个事务之间不能互相干扰）、持久性（持久性是指事务一旦提交，它对数据库的改变就应该是永久性的，接下来的其他操作或故障不应该对其有任何影响）；
（2）事务和事务之间隔离性的隔离级别：有四个级别----读未提交(read uncommitted，事务A可以读到事务B未提交的数据，最低的隔离级别，问题是会读到脏数据)、读已提交(read committed，事务A只能读到事务B提交之后的数据，解决脏读现象，但不可重复读取数据)、可重复读(repeatable read，事务A读不到事务B提交之后的数据，读到的是幻象)、序列化/串行化(serializable，最高的隔离级别)；

四.索引

1.索引概述

（1）索引相当于一本书的目录，为了缩小扫描范围而存在；
（2）MySQL在查询方面有两种方式：全表扫描、根据索引检索；
（3）在MySQL中索引是一个B-Tree结构，遵循做小右大原则存放，采用中序遍历取数据；
（4）在任何数据库中主键都会自动添加索引，在MySQL中，一个字段如果有unique约束，也会自动创建索引；

2.索引创建的条件

（1）数据量庞大；
（2）字段经常出现在where的后面；
（3）字段有很少的DML(insert、delete、update)操作；

3.索引失效的情况

（1）当模糊查询以%开始时，此时索引会失效，应尽量避免%开头；
（2）采用or时，如果两边字段都有索引才有效，如果只有一边有索引，就会失效，尽量少用or；
（3）使用复合索引(两个字段，或者更多的字段联合起来添加一个索引，叫做复合索引)时，没有使用左侧的列查找，索引会失效；
（4）在where中索引列参加了运算，索引失效；
（5）在where中索引列使用了函数，索引失效；

4.索引分类

（1）单一索引：一个字段上添加索引；
（2）复合索引：两个字段或者更多字段上添加索引；
（3）主键索引：主键上添加索引；
（4）唯一性索引：在unique约束的字段上添加索引；

5.索引的最左原则

（1）最左原则：多列索引是在表的多个字段上创建一个索引，只有查询条件中使用了这些字段中的第一个字段，索引才有效，才会被使用；
索引：

6.命令：show index from table_name; 查看索引详情。

（1）主键索引 PRIMARY KEY： 一种特殊的唯一索引，不允许为null,一般建表时会创建主键，若不设置主键，
默认会为每一行生成row_id，查询时row_id不会当作索引使用，所以建表时，建议设置主键。
唯一索引 UNIQUE：唯一索引列的值必须唯一，但允许有空值(一般表字段建议设置为 not null)。如果是组合索引，组合值必须唯一。
可以通过ALTER TABLE table_name ADD UNIQUE (column);创建唯一索引；
可以通过ALTER TABLE table_name ADD UNIQUE (column1,column2);创建唯一组合索引；
普通索引 INDEX：这是最基本的索引，它没有任何限制。可以通过ALTER TABLE table_name ADD INDEX index_name (column);创建普通索引；
组合索引 INDEX：即一个索引包含多个列，多用于避免回表查询。可以通过ALTER TABLE table_name ADD INDEX index_name(column1,column2, column3);创建组合索引
全文索引 FULLTEXT：也称全文检索，是目前搜索引擎使用的一种关键技术。可以通过ALTER TABLE table_name ADD FULLTEXT (column);

五.锁机制

1.锁机制概述

（1）基于锁的粒度分类：表级锁(MySQL支持)、行级锁(innoDB)、页级锁(BDB)；
（2）基于锁的操作分类：读锁（共享锁：可以读但是不可以写）、写锁（排他锁：既不可读也不可写）【共享锁和排他锁最根本的区别在于，加上排他锁后，数据对象只对一个事务可见，而加上共享锁后，数据对所有事务都可见】；
（3）基于锁的实现方式分类：乐观锁、悲观锁；

2.表级锁

（1）表级锁的特点：锁定粒度大，并发度最低，应用在InnoDB、MyISAM、BDB等存储引擎中；
（2）表级锁分为两种：表锁、元数据锁；
（3）表锁的两种表现形式：表共享读锁、表共享写锁；

3.行级锁

（1）行级锁的特点：每次锁住一行数据，锁定粒度小，并发度高，应用在InnoDB存储引擎中；
（2）按照锁定范围行锁的分类：RecordLock锁----锁定单个行记录的锁；GapLock锁----间隙锁，锁定索引记录间隙，确保间隙不变；Next-key Lock锁----行锁和间隙锁组合，同时锁住数据，并且锁住数据前面的Gap；
（3）按照功能分类：共享读锁(S)----允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁；排他写锁(X)----允许获得排他写锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁；

4.读写锁和排他锁

（1）锁是计算机协调多个进程与线程并发访问某一资源的机制；
（2）引入锁后，支持并行处理事务，若事务之间涉及到相同的数据项，会使用排他锁；
（3）读写锁可以让读和读并行，而读和写、写和读、写和写之间还是要加排他锁；

5.死锁

（1）两个事务的锁发生冲突，互相等待对方的锁释放，不能继续执行事务逻辑，就会出现死锁；
（2）死锁的现象主要有：表锁死锁、行级锁死锁、共享锁转化为排他锁；

六.MySQL性能优化

1.优化规则

（1）.SQL的查询一定要基于索引来进行数据扫描；
（2）.避免索引列上使用函数或者运算符，这样会导致索引失效；
（3）.where字句中like %尽量放在右边；
（4）.使用索引扫描，联合索引中的列从左往右，命中越多越好；
（5）.尽可能使用SQL语句用到的索引完成排序；
（6）.查询有效的列信息即可，少用*代替列信息；
（7）.永远用小结果集驱动大结果集；

七.如何定位慢查询

1.慢查询的定义

（1）分析MySQL语句查询性能的方法除了使用EXPLAN输出执行计划，还可以让MySQL记录下查询超过指定时间的语句，我们将超过指定时间的SQL语句查询称为慢查询；

2.定位慢查询的方法

（1）执行指令SHOW VARIABLES LIKE '%query%'可以查看与查询相关的配置信息；
（2）查看慢查询状态，执行指令SHOW STATUS LIKE '%slow_queries%'可以查看产生慢查询的数量；
（3）修改相关参数，执行指令SET GLOBAL slow_query_log=ON， 将默认时间改为1s；