SQL-进阶版

目录

存储引擎

MYSQL 体系结构

存储引擎简介

存储引擎特点

存储引擎选择

索引

索引概述

索引结构

二叉树

Hash

索引分类

索引语法

SQL性能分析

SQL优化

索引使用规则

索引列运算

SQL提示

存储引擎

MYSQL 体系结构

连接层

服务层

引擎层

存储层

存储引擎简介

存储数据、建立索引、更新/查询等技术实现方式。基于表而不是库。

ENGINE=InnoDB(默认)

SHOW ENGINES；（查询引擎）

存储引擎特点

XXX.ibd ：表空间文件，存储该表的表结构（frm、sdi）、数据和索引。

innodb_file_per_table

逻辑存储结构：

1）InnoDB

Tablespace表空间

Segment段

Extent区1M

Page页16K

Row行

2）MYISAM

xxx.MYD：存储数据

xxx.MYI：存储索引

xxx.sdi:存储表结构信息

3）Memory

xxx.sdi:存储表结构信息

特点	InnoDB	MyISAM	Memory
存储限制	64TB	有	有
事务安全	支持	—	—
锁机制	行锁	表锁	表锁
B+tree索引	支持	支持	支持
Hash索引	—	—	—
全文索引	支持（5.6支持之后）	支持	—
空间使用	高	低	N/A
内存使用	高	低	中等
批量插入速度	低	高	高
支持外键	支持

存储引擎选择

InnoDB：支持事务、外键，若对事务完整性要求较高，在并发条件下要求数据一致性，数据操作包含插入查询，还包括很多更新和删除。核心数据

MYISAM：以读操作和插入操作为主，很少更新和删除，对事务完整性、并发性要求不是很高。非核心事务

Memory：将所有数据保存内存中，通常用于临时表及缓存。缺点：对表的大小有限制，太大的表无法缓存在内存中，无法保障数据的安全性。

索引

索引概述

帮助MYSQL高效获取数据的数据结构（有序）

优势：

提高查询效率：提高数据检索的效率，降低数据库的IO成本

提高排序效率：通过索引列对数据进行排序，降低排序成本，降低CPU的消耗

劣势：

索引列占用空间

索引大大提高了查询效率，同时降低更新表的速度，INSERT、UPDATE、DELETE操作效率降低。

索引结构	描述
B+Tree索引	最常见的索引类型，大部分引擎都支持B+树索引
HASH索引	底层数据结构是用哈希表实现的，只有精确匹配索引列的查询才有效，不支持范围查询
R-tree（空间索引）	空间索引是MyISAM引擎的一个特殊索引类型，主要用于地理空间数据类型，通常使用较少
Full-text(全文索引)	通过建立倒排索引，快速匹配文档的方式。类似于Lucene,Solr,ES

索引	InnoDB	MyISAM	Memory
B+tree索引	支持	支持	支持
HASH索引	不支持	不支持	支持
R-tree索引	不支持	支持	不支持
Full-text	5.6版本之后支持	支持	不支持

索引结构

二叉树

缺点：顺序插入时，会形成一个链表，查询性能大大降低，大数据量情况下，层级较深，检索速度慢。

红黑树：大数量情况下，层级较深，检索速度慢。

B-Tree（多路平衡查找树）

动态变化过程网站：https://www.cs.usfca.edu/~galles/visualization/BTree.html

B+Tree

区别：所有数据都会出现在叶子节点

叶子节点形成一个单向

Hash

哈希索引采用了一定hash算法，将键值换算成新的hash值，映射到对应的槽位上，然后存储在hash表中。

如果多个键值映射到一个相同槽位上，他们就产生了hash冲突，可以通过链表来解决。

hash索引特点：只能用于比较，不支持范围查询；无法利用索引完成排序操作；查询效率高，通常以此检索，效率通常高于B+tree索引。

存储引擎支持：在MYSQL中，支持hash索引的是Memory引擎，而InnoDB中具有自适应hash功能，hash索引是存储引擎根据B+Tree索引在指定条件下自动构建的。

索引分类

分类	含义	特点	关键词
主键索引	针对于表中主键创建的索引	默认自动创建	PRIMARY
唯一索引	避免重复值	多个	UNIQUE
常规索引	快速定位特定数据	多个
全文索引	查找文本中的关键词，而不是比较索引中的值	多个	FULLTEXT

索引语法

创建索引

CREATE [UNIQUE|FULLTEXT] INDEX index_name ON table_name (index_col_name,...);

查看索引

SHOW INDEX FROM table_name\G;

删除索引

DROP INDEX index_name ON table_name; 

SQL性能分析

查看当前数据库的insert等访问次数:SHOW[SESSION| GLOBAL] STATUS LIKE 'Com_____';

慢查询日志记录超过一定时间的SQL语句的日志，在MYSQL配置文件（/etc/my.cnf）中配置信息：

开启MYSQL慢日志查询开关：slow_query_log=1

设置慢日志时间，超过则视为慢查询，记录慢查询日志：long_query_time=2

慢日志文件中记录的信息：/var/lib/mysql/localhost-slow.log

查询慢日志相关参数：show variables like '%query%';

profile详情：show profile能在SQL优化时帮助了解时间都耗费哪里去了

查看当前MYSQL是否支持profile操作：SELECT @@have_profiling;

默认profiling关闭，SET profiling=1;开启

查看每一条SQL耗时基本情况：SHOW profiles;

指定query_id 的SQL语句各个阶段的耗时情况:show profile for query query_id;

查看query_id的SQL语句CPU的使用情况:show profile cpu for query query_id;

explain2执行计划：获取MYSQL如何执行SELECT语句的信息，

EXPLAIN/DESC SELECT 字段列表 FROM 表名 WHERE 条件；

索引使用

索引涉及原则

SQL优化

explain执行计划

id

select 查询的序列号，表示查询中执行select子句或操作表的顺序（执行顺序从上到下；id越大越先执行）

select_tpye

表示SELECT的类型，常见的取值有SIMPLE(简单表，即不使用表连接或子查询)、PRIMARY(主查询，即外层的查询)、UNION(UNION中的第二或者后面的查询语句)、SUBQUERY(select/where之后包含子查询)等

type

表连接类型，性能由好到差的连接类型为NULL、system、const、eq_ref、ref、range、index、all。

possible_key

explain执行计划各字段含义：

key：实际使用的索引，如果围殴NULL，则没有使用索引。

Key_len：表示索引中使用的字节数，索引字段最大可能长度，并非实际使用长度。

rows：MYSQL认为必须要执行查询的行数，在innodb引擎的表中，是一个估计值，可能不总是准确的。

索引使用规则

1）最左前缀法则：查询从索引的最左列开始，并且不跳过索引中的列，如果跳跃某一列，索引将部分生效（后面的字段索引失效）

2）范围查询：联合索引中出现<,>，范围查询右侧的列索失效

索引列运算

不要在索引列进行运算操作，索引将失效。

where substring(phone,10,2)='15'

字符串类型字段使用时，不加索引，索引将失效。

尾部模糊匹配，索引不失效，头部模糊匹配，索引失效。

如果MYSQL评估使用索引比全表慢，则不使用索引。

SQL提示

use index:explain select * from tb_user use index(idx_user_pro)where profession='软件工程';

ignore index:explain select*  from tb_user ignore index(idx_user_pro)where profession='软件工程';

force  index:explain select * from tb_user force index(idx_user_pro)where profession='软件工程';

格式化json文件：json.cn