MySQL知识点（第一部分）

MySQL

基础：

1、SQL语句的分类：

• DDL：用于控制数据库的操作
• DML：用于控制表结构的字段，增、删、修
• DQL：用于查询语句
• DCL：用于管理数据库，用户，数据库的访问 权限。

2、MySQL的DQL语句的执行顺序：

先执行 from where group by select order by limit

3、事务

1、事务的实现方式：

方式一：

查询当前系统是否为自动提交事务，如果是 0 是手动提交，1 是自动提交

select @@autocommit;

设置为手动提交

set @@autocommit = 0;

手动提交事务

commit;

回滚事务

rollback;

方式二：

手动开启事务，表示下面的操作要手动控制事务

start transaction;

2、事务的四大特性(ACID)：

1、原子性：是最小分割的操作单元，要么全部成功，要么全部失败。

2、一致性：事务完成时，必须使所有的数据都保持一致。

3、隔离性：数据库系统提供的隔离机制，保证事务在不受外部并发操作影响的独立环境下运行。

4、持久性：事务一旦提交或回滚，它对数据库中的数据的改变就是永久的。

3、并发事物的问题：

1、脏读：一个事务读取到另一个事务还没有提交的数据。

2、不可重复读：一个事务先后读取同一条记录，但是两次读取的数据不同。

3、幻读：一个事务按照条件查询数据时，没有对应的数据行，但是在插入数据时，又发现这行数据已经存在，好像出现了一个幻影，再次查询又查

​ 不到，因为已经解决了不可重读的问题，因此会出现幻读的问题。

4、事务的隔离级别(解决并发事务的问题)：

1、Read uncommitted ：什么都不能解决。

2、Read committed ：只能解决脏读。

3、Repeatable Read(默认) ：只解决脏读、不可重复读。

4、Serializable ：所有的都能解决。

-- 查看事务的隔离级别
select @@transaction_isolation;
-- 修改事务的隔离级别
-- session 表示仅在当前窗口有效，global 表示全局有效。
set session transaction isolation level serializable;

进阶：

1、储存引擎：

1、MySQL的体系结构：

1、连接层：与客户端的链接，密码的校验等

2、服务层：所有的查询接口，优化器，部分函数的执行等

3、引擎层：可插拔式的存储引擎，不同的引擎具有不同的功能，默认InnoDB

4、存储层：磁盘文件，日志等

2、存储引擎的介绍：

存储引擎就是存储数据、建立索引、更新/查询数据等技术的实现方式。存储引擎是基于表的，而不是基于库的，所以存储引擎可以被称为表类型。

-- 查看数据库所支持的存储引擎
show engines;

3、存储引擎的特点：

1、InnoDB：

• 特点

  • DML操作遵循ACID模型，支持事务。

  • 支持行级锁，提高并发性。

  • 支持外键约束，保证数据的完整性和正确性。

• 文件

  • xxx.ibd ：xxx 代表的是表名，innoDB 引擎的每张表都会对应这样一个表空间，存储该表的表结构、数据、索引。
  • 参数：innodb_file_per_table: 表示是否开启每张表都对应一个表空间文件，默认是开启的。

• 逻辑空间结构：
  表空间 —> 段 —> 区 —> 页 —> 行

2、MySAM

• 特点
  • 不支持事务，不支持外键
  • 支持表锁，不支持行锁
  • 访问速度快
• 文件
  • xxx.sdi 存储表结构信息
  • xxx.MYD 存储数据
  • xxx.MYI 存储索引

3、Memory（了解）：

• 特点
  • 内存存放
  • hash索引
• 文件
  • xxx.sdi 存储表结构信息

2、索引

1、索引概述：

索引是一种有序的数据结构，用于高效的获取数据。

**优点：**提高检索效率，降低IO成本。通过索引排序，降低CPU的消耗。

**缺点：**索引也会占用存储空间，并且再插入和删除数据时，需要维护索引结构。

2、索引结构：

1、B+Tree索引：最常见的索引类型，大部分引擎都支持B+树索引。

• 二叉树的缺点：顺序插入时，会形成一个链表，查询性能大大降低，大数据量的情况下，层级越深，检索速度越慢。
• 红黑树的缺点：大数据量的情况下，层级越深，检索速度越慢。
• B-Tree：不论是叶子节点还是非叶子节点，都会保存数据，导致保存大量数据时，只能增加树的高度，导致性能降低。
• MySQL的B+Tree：所有数据都出现在叶子节点，叶子节点也形成一个双向链表。

2、Hash索引：底层数据结构使用hash表实现的，只有精确匹配索引类的查询才有效，不支持范围查询，排序操作。

• 先计算每行的hash值，再根据hash的字段，通过hash算法计算在hash表中的槽位，并存储这一行所计算出来的hash值来定位。

3、R-Tree(空间索引)：空间索引是MySAM引擎的一个特殊索引，只要用于地理空间数据类型，通常使用较少

4、Full-text(全文索引)：是一种通过建立倒排索引，快速匹配文档的方式，雷素与ES

3、索引分类：

1、在innoDB中储存引擎中可分为两种：注意：底层结构都是B+Tree，只不过是叶子节点的数据不同

• 唯一索引（了解）：不可重复

• 全文索引（了解）：类似于ES的倒排索引

• 聚集索引：将数据存储与索引放到一块，索引结构的叶子节点保存行数据。必须有，而且只能有一个。

  • 聚集索引的选取规则：
    • 如果存在主键，主键索引就是聚集索引。
    • 如果不存在主键，将使用第一个唯一索引作为聚集索引。
    • 如果表中没有主键，或者没有适合的唯一索引，则innoDB会自动生成一个rowid作为隐藏的聚集索引。

• 二级索引：将数据与索引分开存储，索引结构的叶子节点关联的时对应的主键。可以存在多个。

• 查询流程：
  由于二级索储存的是索引数据和该条数据的主键，对于非覆盖索引查询，都需要回表查询。

4、索引语法：

-- 创建索引：[唯一索引|全文索引] index 索引名 on 表名 (字段名1,字段2,...)
-- 索引表的名称一般为：idx_表明_字段名
create [UNIQUE|FULLTEXT] index index_name on table_name (index_col_name,...);
-- 查看索引
show index from table_name
-- 删除索引
drop index index_name on table_name

5、SQL性能分析

-- 查看数据库所有语句的执行频次：session表示当前会话的，global表示全局
show [session|global] status like 'Com_______';
-- 慢查日志
	-- 查看慢查询日志是否开启
	show variables like 'slow_query_log';
	-- 开启MySQL慢日志查询开关
	slow_query_log=1
	-- 设置慢日志的时间为2秒，SQL语句执行时间超过2秒，就会被视为慢查询，记录慢查询的日志。
	long_query_time=2
-- profiling
	-- 查看当前数据库是否支持
	select @@have_profiling
	-- 是否开启
	select @@profiling
	-- 开启
	set profiling=1
	-- 查看
	show profiles
-- explain 查看搜索的执行情况
	explain select * from user;

6、索引的使用

1、最左前缀法则：

​ 如果索引了多列，（联合索引），要遵循最左前缀法则，指的是查询从索引的最左列开始，并且不跳过索引中的列。如果跳过了某一列，索引将部分失效（后面的字段索引也失效）

​ 就是如果一个索引表关联了多个字段，那末，查询时必须包含最左边的字段时，索引才生效，否则，索引不生效。如果关联的是三个字段，如：A B C 那么，在查询时，如果where A=’‘，B= ’‘，C= ’‘ ；索引生效，如果B = ’‘，C=’‘。索引失效，如果：A=’‘，C=’‘；A索引生效，C索引失效；只与存不存在有关，跟位置无关：C B A 也走索引，而且全部生效；

2、范围查询

如果使用了> 或 < 的字段，则其后面的索引失效，但是，如果使用的是 >= 或 <= 则不失效。

3、索引运算

如果索引字段进行函数运算，则索引失效

4、字符串不加单引号

字符串不加 ‘ ’ 则索引失效

5、模糊查询

尾部模糊（后面加%）查询走索引，后面模糊（前面加%）不走索引

6、or

or连接的索引，如果前面有索引，后面没索引，那么索引失效。解决：给字段建立索引。

7、数据分布

如果Mysql判断，走全表扫描快还是走索引快，那个快用哪个。

8、SQL提示

1、use index(索引名) ：可以用哪个索引

2、ignore index(索引名) ：忽略哪个索引

3、force index(索引名) ：必须使用哪个索引

9、覆盖索引

覆盖索引就是只查询二级索引，就能查出来需要的字段，不需要回表查询

select 后不要写 * 要写需要的字段

10、前缀索引

在处理比较长的索引的时候。

语法：create index idx_xxxx on table_name(column(n))表示我要将字符串的一部分前缀建立索引从而节省索引的空间。

通过数据的 字段不重复的记录数 / 总记录数 的值，如果越接近 1 ，就越好。

3、SQL优化

1、插入数据

• insert优化
  • 批量插入
  • 手动提交事务
  • 主键顺序插入
• 大批量插入数据
  • 使用load指令
  • 连接服务端时，加上参数：mysql --local-infile -u root -p
  • 开启本地加载目录的开关：set global local_infile=1
  • 执行load指令，将准备好的数据加载到表结构中：load data local infile '文件路径' into table '表名' fields terminated by ',' lines terminated by '\n'

2、主键优化

1、数据组织方式

在innoDB引擎中，表数据都是根据主键顺序组织存放的，这种存储方式的表称为索引组织表。

2、页分裂

在主键乱序插入的情况下，由于要保证在每一页中的主键是有序的。因此，当一个主键的位置被占用时，就会开辟一块新的页，将这个中一半的数据都移动到新的页上面，然后再将当前数据插入到对应的位置，最后修改页指针，确保每一页之间也是有序的。

3、页合并

当删除一行记录时，并没有被物理的删除，只是被标记为删除，当页中删除的记录数道道MERGE_THRESHOLD（阈值默认为 50%），就会寻找最靠近的页看看是否能将两个页合并以优化空间的使用。阈值可以在创建表时指定。

4、主键的设计原则

• 尽量降低主键的长度
• 插入时，尽量使用AUTO_INCREMENT主键自增
• 避免对主键的修改

3、order by 优化

① Using filesort ：通过表的索引或全表扫描，读取满足条件的数据行，然后在排序缓冲区sort buffer中完成排序操作，所有不是通过索引直接返回排序结果的排序都叫FileSort排序

② Using index ：通过有序索引顺序扫描直接返回有序数据，这种情况即为using index，不需要额外排序，操作效率高。

• 尽量使用覆盖排序，也可以调整排序缓冲区，默认是(256K)

4、group by 的优化

通过建立索引，并满足最左前缀原则。

5、limit 的优化

一般通过覆盖索引 + 子查询的方式。

select * from user u,(select id from user order by id limit 2000000,10) a where u.id = a.id;

也就等于

select * from user u right join (select id from user order by id limit 2000000,10) a on a.id = u.id;

6、count 的优化

目前没有好的优化记录，但是可以自己维护总数据数。

• count(主键)：直接把每一行的id取出来，返回服务层后累加。
• count(字段)：会判断字段是否为空，为空则不记录数，如果用not null 约束后，则不用判断
• count(1)：遍历整张表，不取值，每一行放一个 1 进去，后累加
• count(*)：由于Mysql专门做了优化，因此不取值，直接按行累加（建议使用）

3、Update 语句

执行update语句时，要根据索引进行更新，否则会将行锁升级为表锁，锁住整张表，提交事务之前，其他的事务不能对这张表做修改操作。

4、视图

1、概括

视图是一种虚拟存在的表。只是保存了查询SQL逻辑，不保存查询结果。所以我们在创建视图的时候，主要的工作就是创建SQL语句上。

2、创建/修改/删除视图

-- create or replace view 视图名 as 查询语句
create or replace view test as select * from user;

-- 检查选项：with [cascaded（默认）,local] check option
create or replace view test as select * from user where id <= 30 with [cascaded,local] check option;
-- 当向视图中插入、更新、删除时 id > 30 的数据时，就会报错失败。
-- cascaded：由于视图可以依赖其他的视图创建，这个可以检查这个视图所依赖的视图，
-- 注意：如果创建的视图没有检查选项，那么即便是他所依赖的视图有检查选项，也不会检查，检查选项只能向上传递，不能向下传递。
--  	同时，如果上一层视图没有定义检查选项，那末也会检查（区别）

-- local：表示，检查当前视图的的条件，同时也会递归去找上一层，但是，如果上一层没有定义检查选项，就不检查上一层。
-- 删除视图
drop view 视图名

3、查询/修改视图

-- 查询视图的创建语句
show create view 视图名
-- 由于试图是一张虚拟的表，也可以通过查询表的方式，查询视图
select * from 视图名 where ....

-- 修改就是正常表的修改，但是，如果视图是定义的聚合函数，那末视图就不可进行插入，删除等操作。

5、存储过程

1、概括：

存储过程是事先金经过编译并储存在数据库中的一段SQL集合，调用存储存储过程可简化开发，减少数据库和应用服务器之间的传输，提高效率。简单来说：就是数据库SQL语言层面的代码封装和重用。

2、特点：

• 封装、重用。
• 可以接收参数，返回数据
• 减少网络交互，减少网络的开销

3、创建、调用存储过程：

• 创建

  create procedure 储存过程名([参数列表])
  begin
  -- SQL语句
  end：
  

• 调用

  call 名称([参数])
  

• 查看创建命令、删除

  show procedure 储存过程名