MySql高级语法

最流行的开源数据库

配置文件

日志

log-bin：用于主从复制

log-error：记录MySQL的错误日志，默认是关闭的

log：用于记录sql日志，默认是关闭的

数据文件

frm文件：存放表结构

myd文件：存放表数据

myi文件：存放表索引

架构层

连接层

最上层的架构层，包括一些客户端和连接服务，包含本地socket通信和大多数基于客户端/服务端工具实现的类型tcp/ip的通信。主要完成一些类似于连接处理、授权认证及相关的安全方案。在盖层引入了线程池的概念，为通过认证安全接入的客户端提供线程。同样该层可以实现基于SSL的安全连接。服务器也会为安全接入的每个客户端验证它所具备的操作权限

服务层

第二层，主要完成大部分的核心功能，如SQL接口，并完成缓存的查询，SQL的分析和优化及部分内置函数的执行。所有跨存储引擎的功能也在这一层实现，如过程，函数等。服务器会解析查询并创建相应的内部解析树，并对其完成相应的优化，如确定查询表的顺序，是否利用索引等，最后生成相应的执行操作。如果是select语句，服务器还会查询内部的缓存。如果缓存空间足够大，这样在解决大量读操作的环境中能够很好的系统的性能。

引擎层

第三层，存储引擎真正的负责MySql中数据的存储和读取，服务器通过API与存储引擎进行通信。不同的存储引擎具有的功能不同，包括MyISAM和InnoDB等。

存储层

第四层，数据存储层，主要是将数据存储在运行于裸设备的文件系统之上，并完成于存储引擎的交互

MyISAM和InnoDB

show engines;：查看数据库的使用的引擎

show variables like ‘%storage_engine%’;：查看默认的存储引擎

对比	MyISAM	InnoDB
主外键	不支持	支持
事务	不支持	支持
行表锁	表锁，即使一条记录也会锁住整个表不适合高并发的操作	行锁，操作时只锁住一行，不对其他影响适合高并发
缓存	只缓存索引，不缓存真实数据	不仅缓存索引还缓存真实数据对内存要求较高，而且内存大小对性能有影响
表空间	小	大
关注点	性能	事务
默认安装	安装	安装

索引

sql慢

查询语句问题

索引失效

关联查询的join太多

服务器调优及各个参数的设置问题(缓冲，线程数)

常见join查询

-- inner join
select * from emp e inner join dep d on e.did = d.id;

-- left join
select * from emp e left join dep d on e.did = d.id;

-- right join
select * from emp e right join dep d on e.did = d.id;

-- left a join，只查询左侧
select * from emp e left join dep d on e.did = d.id where d.id is null;

-- right a join，只查询左侧
select * from emp e right join dep d on e.did = d.id where e.did is null;

-- full join，mysql不支持，所以使用这个
select * from emp e left join dep d on e.did = d.id
union
select * from emp e right join dep d on e.did = d.id;

-- full join 排除共有
select * from emp e left join dep d on e.did = d.id where d.id is null
union
select * from emp e right join dep d on e.did = d.id where e.did is null;

索引理解

Mysql官方对索引的定义：索引(Index)是帮助Mysql高效获取数据的数据结构，索引是数据结构

索引简单理解为：排好序的快速查找的数据结构

结论：数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高效查找算法，这种数据结构就是索引

一般来说索引本身也很大，不可能全部存储在内存，因此索引往往以索引文件的形式存储在磁盘

优势

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

劣势

• 实际上索引也是一张表，表保存了主键和索引字段，并指向实体表的记录，所以索引列也是要占空间的
• 索引大大提高了查询速度，但同时降低了update，insert，delete的速度，因为在更新表时，mysql不仅仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段

Mysql索引分类

单值索引：一个索引只包含单个列，一个表可以有多个单列索引

唯一索引：索引列的值必须唯一，但允许有空值

复合索引：一个索引包含多个列

创建

-- []中的可以省略，加上unique表示为 唯一索引
create [unique] index 索引名 on 表名(字段1,...);
alter 表名 add [unique] index 索引名 on (字段1,...);

删除

drop index 索引名 on 表名;

查看

show index from 表名;

-- 添加一个主键，索引必须是唯一的，并且不能为NULL
alter table 表名 add primary key (字段);

-- 添加一个唯一索引，可以为NULL
alter table 表名 add unique 索引名 (字段);

-- 添加普通索引
alter table 表名 add index 索引名 (字段);

-- 添加全文索引
alter table 表名 add fulltext 索引名 (字段);

Mysql索引结构

创建索引的条件

需要创建索引

1. 主键自动创建唯一索引
2. 频繁作为查询条件的字段
3. 查询中与其他表关联的字段，外键关系建立索引
4. 频繁更新的字段不适合创建索引
5. where条件里用不到的字段不创建
6. 高并发情况下建议使用复合索引
7. 查询中的排序字段，排序字段若通过索引访问将大大提高排序速度
8. 查询中统计或者分组字段

不需要创建索引

1. 表记录太少
2. 经常增删改的表
3. 数据重复分布平均的字段

Explain

简介

使用explain关键字可以模拟优化器执行SQL查询语句，从而知道Mysql是如何处理自己的SQL语句。分析出查询语句或者是表结构的性能瓶颈

作用：

1. 得到表的读取顺序
2. 得到数据读取操作的操作类型
3. 得到哪些索引可以使用
4. 得到哪些索引被实际使用
5. 得到表之间的引用
6. 得到每张表有多少行被优化器查询

使用

explain + SQL语句
包含的信息

id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |

id

select查询的序列号，包含一组数字，表示查询中执行select字句或操作表的顺序，值越大越先执行

id相同：执行顺序由上至下

id不同：如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行

id相同不同：同时存在

select_type和table

select_type：代表查询的类型，主要用来区别普通查询，联合查询，子查询等复杂的查询

table：代表操作的是哪一张表

select_type	含义
SIMPLE	简单的select查询，查询中不包含子查询或union
PRIMARY	查询中若包含任何复杂的子部分，最外层查询则被标记为primary
SUBQUERY	在select或where列表包含了子查询
DERIUED	在from列表中包含的子查询被标记为derived(衍生) Mysql会递归执行这些子查询，把结果放在临时表里
UNION	若第二个select出现在union之后，则被标记为union 若union包含在from子句的子查询中，外层select将被标记为derived
UNION RESULT	从union表获取结果的select

type

type	含义
ALL	Full Table Scan，将遍历全表以找到匹配的行
index	Full Index Scan，index于ALL区别为index类型只遍历索引树
range	只检索给定范围的行，使用一个索引来选择行。key列显示用了哪个索引 一般就是在where语句中出现了between、>、<、in等的索引，这种**范围扫描索引扫描比全表扫描好** 因为它只需要开始于索引的某一点，而结束语另一点，不用扫描全部索引
ref	非唯一性索引扫描，返回匹配某个单独值的所有行 本质上也是一种索引访问，它返回所有匹配某个单独值的行 它可能会找到多个符合条件的行，所以它应该属于查找和扫描的混合体
eq_ref	唯一性索引扫描，对于每个索引键，表中只有一条记录与之匹配，常见于主键或唯一索引扫描
const	表示通过索引一次就找到了，const用于比较primary key获取unique索引 因为只匹配一行数据，所以很快
system	system**表只有一行记录(等于系统表)**，这是const类型的特例，平时不会出现
NULL	表示空

• 显示查询使用了哪种类型
• 从最好到最差依次是

system > const > eq_ref > ref > range > index > ALL

possible_keys和key和key_len

possible_keys：显示可能应用这张表的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引被列出，但不一定被查询实际使用

key：表示实际使用的索引。如果为NULL，则表示没有索引。查询中若使用了覆盖索引，则该索引仅出现在key列表中

key_len：表示索引中使用的字节数，可以通过该列计算查询中使用的索引的长度。在不损失精确性的情况下，长度越短越好。key_len显示的值为索引字段的最大可能长度，并非实际长度，即key_len是根据表定义计算可得，不是通过表内检索出的

ref

显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值

rows

根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数

Extra

包含不适合在其他列中显示但十分重要的额外信息

类型	解释
Using filesort	说明Mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取 Mysql中无法利用索引完成的排序操作称为文件排序
Using temporary	使用了临时表保存中间结果，Mysql在对查询结果排序时使用临时表。 常见于order by和分组查询group by
Using index	表示相应的select操作中使用了覆盖索引(下面有解释)，避免访问了表的数据行，效率不错 如果出现了using where，表明索引被用来执行索引值的查找 没有出现表明索引用来读取数据而非执行查找动作
Using where	表示使用了where过滤
using join buffer	表示使用了连接缓存
impossible where	where子句中的值总是false，不能用来获取任何元组
select tables optimized away	在没有group by子句的情况下，基于索引优化min/max操作或者MyISAM存储引擎优化count(*)操作 不必等到执行阶段再进行计算，查询执行计划阶段生成的阶段即完成优化
distinct	优化distinct操作，在查找到第一匹配的元组后即停止找同样值的动作

索引优化

join优化

左连接时建立右表索引，右连接建立左表索引

尽可能减少join语句中嵌套循环(NestedLoop)的循环总次数：永远用小的结果集驱动大的结果集

优先优化嵌套循环(NestedLoop)的内层循环

保证join语句中被驱动表上join条件字段已经被索引

当无法保证被驱动表的join条件字段被索引且内存资源充足的前提下，不要太吝啬JoinBuffer的设置

like优化

like**使用右%**比左%和左右%性能更好

使用覆盖索引来优化左右%的使用

索引失效原因

全值匹配时会导致索引失效

最佳左前缀法则，使用索引时使用创建索引顺序的最左侧开始使用，否则导致索引失效

**不在索引列上做任何操作(计算、函数、自动或手动类型转换)**，导致索引失效

存储引擎不能使用索引中范围条件右侧的列

尽量**使用覆盖索引(只访问索引的查询)**，减少使用select *

mysql在**使用!=或者<>**时会导致索引失效

使用is null，is not null会导致索引失效

like以通配符开头会导致索引失效

字符串不用单引号会导致索引失效

少用or，or会导致索引失效

SQL分析步骤

观察在生产环境运行一天的结果

开启慢查询分析

用explain+慢SQL分析

使用show profile查询SQL在Mysql服务器中的执行细节和生命周期情况

SQL数据库服务器的参数调优

in和exists

select ... from table where exists (sub)

将主查询的数据，放到子查询中做条件验证，根据验证结果(true或false)来决定主查询的数据结果是否得以保留

exists(sub)只返回true或false，因此在子查询中的select * 也可以是 select 1，官方说法是实际执行时会忽略select清单，所以没有区别

exists子查询的实际执行过程可能经过了优化而不是我们理解的逐条优化

exists子查询往往也可以用条件表达式，其他子查询获取join来替代

order by

Mysql支持两种方式的排序，FileSort和Index，Index效率高，FileSort效率较低，Index指Mysql扫描索引本身完成排序

order by满足两种情况时会使用Index排序

1. order by语句使用索引最左前列
2. 使用where字句于order by子句条件列满足索引最左前列

group by

同order by 类型

group by 实质是先排序后进行分组，遵循索引建的最佳左前缀原则

当无法使用索引列时，增大max_length_for_sort_data参数设置和增大sort_buffer_size参数设置

where高于having，能写在where限定的条件就不要去having限定了

慢查询日志

Mysql的慢查询日志是Mysql提供的一种日志记录，它用来记录在Mysql中响应时间超过阈值的语句，具体指运行时间超过long_query_time的值的SQL

long_query_time的默认值是10秒

默认情况下，Mysql数据库没有开启慢查询日志，需要手动打开。慢查询日志会对性能有一定的影响，所以不是调优的时候建议关闭

-- 查看是否开启
show variables like '%slow_query_log%';

-- 开启，只对当前数据库生效，重启失效
set global slow_query_log = 1;

-- 查看默认的秒数
show variables like 'long_query_time%';
-- 用于修改后查看，不用重新连接
show global variables like 'long_query_time';

-- 自定义设置时间为3秒
-- 修改后需要重新连接或新开一个会话才能看到修改的值
-- 或者使用带global的查看
set global long_query_time = 3;

-- 查看有几条慢的SQL
show global status like '%slow_queries%';

mysqldumpslow工具是Mysql官方提供的慢查询日志分析工具

在终端输入：mysqldumpslow –help

show profile

是Mysql提供可以用来分析当前会话中语句执行的资源消耗情况，用于SQL调优

默认是关闭的，并保存15次查询的结果

分析步骤

1. 查看是否支持：show variables like ‘profiling’;
2. 开启功能：set profiling = on;
3. 运行SQL
4. 查看结果：show profiles;
5. 诊断SQL：show profile cpu,block io for query 上一步查看后的SQL数字号码;

全局日志

Mysql锁机制

分类

数据操作的类型分类

• **读锁(共享锁)**：针对同一份数据，多个读操作可以同时进行而不会互相影响
• **写锁(排它锁)**：当前写操作没有完成前，它会阻断其他写锁和读锁

操作粒度分类

• 表锁
• 行锁

表锁

偏向MyISAM存储引擎，开销小，加锁快，无死锁，锁定粒度大，发生锁冲突的概率最高，并发度低

-- 手动增加表锁
-- read 代表读锁，write代表写锁
lock table 表名字 read/write, 表名字2 read/write;

-- 查看加过锁的表
show open tables;

-- 释放表锁
unlock tables;

MyISAM表添加读锁后

• 当前终端可以读锁定的表，但不能修改和增加数据到当前的表，不能读取其他的表
• 其他终端可以读取锁定的表和其他的表，但修改和增加数据到锁定的表会一直处于阻塞状态

MyISAM表添加写锁后

• 当前终端可以读和修改锁定的表，不能读取其他的表
• 其他终端阻塞读取锁定的表

总结：读锁会阻塞写，但不会阻塞读。而写锁则会把读和写都阻塞

-- 分析表锁定
show status like 'table%';

Table_locks_immediate：产生表级锁定的次数，表示可以立即获取锁的查询次数，每立即获取锁值加1

Table_locks_waited：出现表级锁定争用而发生等待的次数(不能立即获取锁的次数，每次等待一次锁值加1)，此值高则说明存在着较严重的表级锁争用情况

MyISAM的读写锁调度是写优先，所以MyISAM不适合做写为主表的引擎，因为写锁后，其他线程不能做任何操作，大量的更新会使查询难得到锁，从而造成永久阻塞

行锁

偏向InnoDB存储引擎，开销大，加锁慢，会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度也最高

InnoDB与MyISAM的的不同点：一是支持事务，而是采用了行锁

行锁更新了数据没有提交，其他终端操作同一条数据会处于阻塞状态，但操作其他数据不会有影响

-- 设置行锁，关闭自动提交
set autocommit = 0;

-- 提交
commit;

索引失效后行锁会升级为表锁

间隙锁

单行锁定

查看锁状态

相关文章

MySql常用语法

Redis