MySQL概念整理

连接数据库指令：mysql - u用户名 -p密码 -h IP地址 -P 端口号

一、MySQL 基础使用

1、数据库操作：

查询数据库：show databases;
删除数据库：drop database db_name;
使用数据库：use db_name;
创建数据库：create database db_name character set utf8mb4;

2、表操作

创建表：create tables tables_name(字段1，字段2，…);
删除表：drop table table_name;
查询所有表：show tables; (删除所有数据以及表结构)
修改表结构：alter table table_name add clumom 字段名 类型;
新增数据：
  添加单条数据：insert into table_name(字段名…) values(…)
  添加多行信息：insert into table_name(字段名…) values(…),(…)
修改数据：update 表名 set 字段名=值 where…
清空数据：delete; （清空表中数据，保留表结构）
全列查询：select * from 表名;
指定列查询：select 列明 from 表名;
表达式查询：select math+10 from 表名; 【禁止使用】
别名查询：select uname as username from 表名;
聚合查询：
  聚合函数包括： count()、sum()、avg()、max()、min()
去重：distinct —— select distinct 列名 from 表名;
排序：order by 成绩 desc/asc; desc是倒叙；asc是升序
where查询：
  Null和’'不是同一个意思
分页查询：limit
  limit 3,3 ——> 查询3条数据，跳过前3条，查询的是第4-6条数据

3、进阶操作

(1)查询的进阶（多表查询）：

内联查询：inner join
  select a.,b. from a inner join b; （这个查询出来的是一个笛卡尔积，是说有数据的组合）
  select a.,b. from a inner join b on a.id = b.id
外联查询：
  左连接查询：left join
    select a.,b. from a left join b on a.id = b.id
  右连接查询： right join

子查询： select * from student where classid in (select classid from student where username=‘周成’);
现通过学表表查到’周成’的classid，然后将这个calssid作为另一张表的入参

(2)表约束：

非空约束：not null;
唯一约束：unique;
主键约束：primary key;
外键约束：foreign key;【禁止使用，物理外键】
  将 物理外键 转换成 逻辑外键 使用
  物理删除 delect where … 将数据直接删除
  逻辑删除 给表添加一个delect的属性，默认为0，当想要删除这个表时，另delect的属性为1，在程序上认为它被删除
默认值：default;

(3)表设计：

三范式：

• 第一范式（1NF）无重复的列（确保每列的原子性.）
• 第二范式（2NF）属性完全依赖于主键（确保表中的每列都和主键相关.）
• 第三范式（3NF）属性不依赖于其它非主属性（确保每列都和主键列直接相关,而不是间接相关.）

表的关系：

• 1对1：1张表就可以完成（一到两张表）
• 1对多：一个班级里面有多个学生
• 多对多：最少需要三张表

二、索引（保证 MySQL 高效运行的主要手段）

1、索引的分类

（1）普通索引： create index 索引名 on 表名 (字段)
（2）主键索引： 在设置表的主键时就会设置索引，无需显示的创建
（3）唯一索引： create unique index 索引名 on 表名 (字段)
（4）组合索引： create index 索引名 on 表名(字段A，字段B)

2、索引的操作

查看一个表的所有索引：show index from table_name;
删除索引：drop index [索引名] on [表名];

索引使用的注意事项：

• 1、在生产服务器不要直接执行添加索引的操作（创建索引的过程特别慢，且数据量十分巨大的情况下运行时间越长）
• 2、对于多读（多查询）场景适合使用索引。面对经常添加、删除的场景，尽量不使用索引
  3、避免查询的时候，不触发索查询

什么情况下索引不会被触发？

• 1、当一个列（已设置索引的列），进行赋值查询的时候。
• 2、避免使用LIke 查询，当使用Like 查询的时候 可能不触发索引，
  比如使用 like ‘%XXX’ 就不会触发
  当使用 like ‘XXX%’ 会触发索引
• 3、当使用组合索引的时候，一定要注意遵循最左匹配原则
  比如当一个表 表名（A,B,C）
  当使用 where A=XXX and B=XXX and C=XXX; 就可以触发索引
  当使用 where A=XXX and B=XXX 也是可以触发
  当使用 where A=XXX and C=XXX 同样可以触发
  但当使用 where C=XXX and A=XXX 是不会触发索引的
• 4、尽量避免使用 or 查询，有可能导致索引不生效
• 5、不要使用 != 或 < >
• 6、如果是字符串查询，一定要加上单引号，否则会导致索引失效

如何优化MySQL的性能？

• 1、在查询比较多的关键列上加索引
• 2、开启慢查询日志，找到执行比较慢的SQL，针对性地进行优化
• 3、能使用主键索引的时候，尽量使用主键索引。
• 4、分表（垂直分割）分库（水平分割）
• 5、提高数据库的硬件配置。更换读写性能更高的磁盘，更换更大的内存

主键索引 和 普通索引 的区别？

• （1）主键索引不需要认为的创建，在创建表的时候就有了
• （2）主键索引不能删除，而普通索引可以
• （3）主键索引查询更快，而普通索引因为有回表查询，索引的性能没有主键索性能高。

数据库使用的是B+树，为什么不是用B树？

• （1）因为当数据量比较大的时候，B树的层级是比较高的，当使用B+树时，数据量越大，每一个叶子节点存放的元素就越多，这样子当它的数据量比较高的情况下B+树的层级是比较低的，这样查询效率就比较高
• （2）当使用B树时，在查询时需要进行中序遍历；当使用B+树时，每一个层级之间使用链表查询，避免了全树的中序遍历

什么是回表查询？

• 对于主键索引来说，粉红框内的数字就是它主键索引的ID；而在他的非叶子节点中存放的就是ID所对应的全数据
• 而对于非主键索引来说，当根据非主键索引找到对应的主键索引ID，然后再去主键索引的树上去找到主键索引ID对应的全数据

如何开启慢查询？

（1）修改配置文件
修改配置文件 my.cnf，在[mysqld]下的下方加入：
[mysqld]
slow_query_log = ON（表示开启慢查询）
slow_query_log_file = 日志文件存放的目录地址
long_query_time = 设置慢查询的时间（根据情况设定，单位为秒）
（2）重启MySQL服务
service mysql restart

三、事务（保证MySQL 稳定的使用）

1、事务特性（ACID）

（1）隔离性：并发事务执行时，隔离问题 —— 事务在执行期间不能相互影响
（2）原子性：要么全部成功，要么全部失败
（3）持久性（永久性）：事务再执行完成后，结果要一直保存下来
（4）一致性：事务在执行前后，数据要保证是正确（比如转账前后金额总数要一致）

2、并发事务存在的问题：

（1）脏读：事务A 读取到了 事务B 没提交的数据，然后 事务B 回滚了。
（2）不可重复读：事务A 使用相同的查询条件，读取到了不一样的结果，因为在这个过程中，事务B 修改了数据
（3）幻读：事务A 将数据修改之后，事务B 又添加了一条数据，导致事务A 执行的结果和预期不一致
不可重复读 和 幻读 的区别：
不可重复读的侧重点在于修改，侧重于修改，数据原本是存在的
幻读侧重点在于添加或删除，数据忽然出现或是消失了

3、事务的隔离级别：

事务的隔离级别就是为了解决并发事务存在的三个问题而提出的。
√ 表示会出现这种问题
× 表示没有这种问题
         脏读    不可重复读    幻读
读未提交       √         √        √
读已提交       ×         √        √
可重复读       ×         ×       √ （MySQL的默认隔离界别）
串行化（加锁）    ×         ×       ×
幻读问题解决方案：
1、使用MVCC 版本号来解决，若在读取过程中发现版本号不一样，那说明在这期间有人去修改了，重新读一次
2、gap 间隙锁 在事务A进行数据修改时，事务B 等待事务A执行完后再进行执行

四、MySQL 执行引擎 / MySQL 日志

myisam （不支持事务、但是运行快）
InnoDB （MySQL 的默认引擎）