Mysql从简单到深入大全

作 用:                                                   函数名称:

求绝对值                                                  ABS

求二次方根                                                SQRT

求余数                                                    MOD

两个函数功能相同，都是返回不小于参数的最小整数，即向上取整      CEIL 和 CEILING

向下取整，返回值转化为一个BIGINT                            FLOOR

生成一个0~1之间的随机数，传入整数参数是，用来产生重复序列       RAND

对所传参数进行四舍五入                                     ROUND

求正弦值                                                  SIN

求反正弦值，与函数 SIN 互为反函数                           ASIN

求余弦值                                                  COS

求反余弦值，与函数 COS 互为反函数                           ACOS

求正切值                                                  TAN

求反正切值，与函数 TAN 互为反函数                           ATAN

求余切值                                                  COT

MySQL 字符串函数

函数名称:             作 用:

LENGTH                计算字符串长度函数，返回字符串的字节长度

CONCAT                合并字符串函数，返回结果为连接参数产生的字符串，参数可以使一个或多个

CONCAT_WS             CONCAT的变种，可以将多个字符串按照统一格式进行拼接

INSERT                替换字符串函数下标从1开始，INSERT(idcard,2,2,'**'):将身份证号第二个开始连                
                      续两个替换成

LOWER                 将字符串中的字母转换为小写

UPPER                 将字符串中的字母转换为大写

LEFT                  从左侧字截取符串，返回字符串左边的若干个字符

RIGHT                 从右侧字截取符串，返回字符串右边的若干个字符

TRIM                  删除字符串左右两侧的空格

REPLACE               将指定的字符串替换成新的字符串

SUBSTRING             截取字符串，返回从指定位置开始的指定长度的字符换

JSON_EXTRACT          Mysql5.7及之后的版本里提供了json_extract函数，可以通过key查询value值(如果是json数组类型,可以通过下标获取对应位置的值)，非常方便（select JSON_EXTRACT(spec_ids, "$[0].text") FROM tb_type_template）

MySQL 日期和时间函数

MySQL 聚合函数

函数名称:        作用:

MIN              查询指定列的最小值

COUNT            统计查询结果的行数

SUM              求和，返回指定列的总和

AVG              求平均值，返回指定列数据的平均值

MAX              查询指定列的最大值

MySQL 流程控制函数

函数名称:                                             作用:

IF(v,v1,v2)                                           判断v值,如果符合返回v1，不符合返回v2，流程控制

IFNULL(v1,v2)                                         判断是否为空,v1不为空返回自己，为空返回v2

CASE WHEN... THEN  WHEN...THEN  ... ELSE ... END      搜索语句,类似于Java中的if .... else if ....  else

CASE ... WHEN ... THEN  WHEN...THEN ... ELSE ..END    搜索语句,类似于Java中的Switch case

Mysql引擎

mySql存储数据时，有不同的存储方式，这些方式都使用了不同的底层实现(如：存储机制，索引技巧，索引技巧，锁定水平)，底层实现的差异带来的功能也就不同。在不同的场景下使用合适的存储方式就能让你的数据读写速度更快或者获得额外的功能。

mySql主要有什么存储引擎:

主要的有： MyIsam , InnoDB, Mrg_Myisam, Memory, Blackhole, CSV, Performance_Schema, Archive, Federated

可用通过 show engines 查看mySql的存储引擎

默认使用InnoDB

MyIsam与innoDB区别:

MyIsam 存储引擎独立于操作系统，简单说就是可用在windows上使用，也可用将数据转移到Lunex操作系统上。系统兼容性很好，这种存储引擎在建表的时候，它会创建3个文件。分别是(.frm, .MYD, .MYI)，其中.frm 存储是表结构，.MYD存储的是表里面的数据。.MYD存储索引。

MyIsam特点:

不支持事务
不支持外键
查询速度很快。
对表进行加锁，如果数据库insert和update的操作比较多的话采用表锁效率低(建议使用innodb)。

InnoDB特点:

支持行锁并发处理比较好。
支持事务。
可以从灾难中恢复(通过bin-log日志等)。
外键约束。只有他支持外键。
支持自动增加列属性auto_increment。

索引

索引按照物理实现方式，索引可以分为两种：聚簇（也叫聚集）和非聚簇（也叫非聚集索引），我们也把非聚集索引称之为尔加索引或者辅助索引

聚簇索引:

聚簇的意思是数据行和相邻的键值聚簇的存储在一起，聚簇索引并不是一种单独的索引类型，而是一种数据存储方式，所有的用户记录都存出在了叶子节点上。在innodb存储引擎中，自增的主键生成的就是聚簇索引：由于数据物理存储排序方式只能有一种，所以每个mysql的表只能有一个聚簇索引，一般情况下就是该表的主键

简单来讲：

聚簇索引就是按照每张表的主键构造一颗B+树，同时叶子节点中存放的就是整张表的行记录数据，也将聚集索引的叶子节点称为数据页。这个特性决定了索引组织表中数据也是索引的一部分，每张表只能拥有一个聚簇索引。（概念：）Innodb通过主键聚集数据，如果没有定义主键，innodb会选择非空的唯一索引代替。如果没有这样的索引，innodb会隐式的定义一个主键来作为聚簇索引。

聚簇索引也不需要我们通过创建索引的语句来创建，innodb会自动的为我们创建聚簇索引

回表查询:

当我们使用非聚簇索引进行搜索数据的时候，查询的是此字段的B+树，我们从B+树中找到要查询的数据之后，在通过从这棵树上查询出来的字段以及所对应的聚簇索引，再根据聚簇索引从聚簇索引的B+树上去查找，这个过程叫回表查询

索引覆盖:

当使用非聚簇索引进行搜索数据的时候，查询到的只是当前索引数据以及id数据，为了避免回表查询带来的效率影响，可以使用组合索引进行查询，在组合索引中设置额外需要查询的字段，这个时候在使用非聚簇索引查询B+Tree的时候，可以通过组合索引查询到需要的字段数据

聚簇索引与非聚簇索引的区别:

聚簇索引的叶子节点存储的是我们的数据记录，非聚簇索引的叶子节点存储的是数据所在的位置，非聚簇索引不会影响数据表的物理存储顺序。
一个表只能有一个聚簇索引，因为只能有一种排序存储的方式，但是可以有多个非聚簇索引。
使用聚簇索引的时候，数据的查询效率高，但如果对数据进行插入，删除，更新等操作，效率会比非聚簇索引低

索引的分类

mysql的索引包括普通索引、唯一索引 、全文索引、单列索引、多列索引和空间索引

从功能逻辑上时候，索引主要有4种，分别是普通索引、唯一索引、主键索引、全文索引

按照物理实现方式，索引分为两种：聚簇索引和非聚簇索引

按照作用字段个数进行划分，分成单列索引和联合索引

普通索引

在创建普通索引时，不附加任何限制条件，只适用于提高查询效率，这类索引可以创建在任何数据类型中，其值是否唯一和非空，要有字段本身的完整性约束条件决定。建立索引后，可以通过索引进行查询，提高查询效率。

唯一索引

使用unique参数可以设置唯一索引，在创建唯一索引时，限制该索引的值必须是唯一的，但允许有空值，当我们添加唯一约束的时候mysql会自动创建唯一索引，也可以单独创建唯一索引，在一张数据表里可以有多个唯一索引。

主键索引

主键索引就是一种特殊的唯一索引，在唯一索引的基础上加了不为null的约束，一张表里最多只有一个主键索引（也就是聚簇索引）

多列索引

也叫组合索引或者联合索引，是在表的多个字段组合上创建的一个索引，该索引指向创建时对应的多个字段，可以通过这几个字段进行查询，但是只有查询条件中使用了这些字段中的第一个字段时才会被使用，最左前缀原则

全文索引

全文索引(也称全文检索)是目前搜索引擎使用的一种关键技术。它能够利用【分词技术】等多种算法智能分析出文本文字中关键词的频率和重要性，然后按照一定的算法规则智能地筛选出我们想要的搜索结果。全文索引非常适合大型数据集，对干小的数据集，它的用处比较小。

使用参数FULLTEXT可以设置索引为全文索引。在定义索引的列上支持值的全文查找，允许在这些索引列中插入重复值和空值。全文索引只能创建在CHAR、VARCHAR或TEXT类型及其系列类型的字段上，查询数据量较大的字符串类型的字段时，使用全文索引可以提高查询速度。

随着大数据时代的到来，关系型数据库对应全文索引的需求已经力不从心，逐渐被solr、elasticsearch等专门的搜索引擎所替代

空间索引（了解）

使用参数SPATIAL可以设置索引为空间索引。空间索引只能建立在空间数据类型上，这样可以提高系统获取空间数据的效率。MySQL中的空间数据类型包括GEOMETRY、POINT、LINESTRING和POLYGON等。目前只有MylSAM存储引擎支持空间检索，而且索引的字段不能为空值。这类索引很少会用到。

不同的存储引擎支持的索引类型也不一样 InnoDB: 支持B+tree、Full-text等索引，不支持Hash索引;

MyISAM: 支持B+tree、Full-text等索引，不支持Hash索引:

Memory: 支持B+tree、Hash等索引，不支持Full-text索引

NDB: 支持Hash索引，不支持B-treeFull-text等索引;

Archive: 不支持B+tree、Hash、Full-text等索引;

不适合创建索引的情况

索引的作用是增加我们的查询效率，那么什么情况不适合创建索引呢？

在where中使用不到的字段不要设置，包括group by 以及order by
数据量小的表不要使用索引
有大量重复数据的列上不要创建索引，例如性别等
经常更新的表不适合创建索引

数据库锁

全局锁

用作全量备份时，保证表与表之间的数据一致性

如果不加任何包含，数据备份时就可能产生不一致的情况，如下图所示:

全局锁的语法：

flush tables with read lock;

使用全局读锁锁定所有数据库的所有表。这时会阻塞其它所有 DML 以及 DDL 操作，这样可以避免备份过程中的数据不一致。接下来可以执行备份，最后用 unlock tables 来解锁

表级锁 - 表锁

语法：加锁 lock tables 表名 read/write，解锁 unlock tables
缺点：粒度较粗，在 InnoDB 引擎很少使用

LOCK table emp READ;//只能查询
unlock tables;

LOCK table emp write;//增删改查都不能操作
unlock tables;

表级锁 - 元数据锁

即 metadata-lock（MDL），主要是为了避免 DML 与 DDL 冲突，DML 的元数据锁之间不互斥

BEGIN
insert into emp....

BEGIN
alter TABLE emp add gender varchar(50)
//会产生冲突

表级锁 - IS（意向共享）与 IX（意向排他）

主要是避免 DML 与表锁冲突，DML 主要目的是加行锁，为了让表锁不用检查每行数据是否加锁，加意向锁（表级）来减少表锁的判断
- select … lock in share mode 会加 IS共享锁
- insert，update，delete， select … for update 会加 IX排他锁

//意向排它锁
BEGIN
select * from emp where id = 1003  for update;
commit

//在加表锁会阻塞
lock tables emp read;
lock tables emp write;

//意向共享锁
BEGIN
select * from emp where id = 1003 lock in share mode;
lock tables emp read;//不阻塞
lock tables emp write;//阻塞

行级锁

种类
- 行锁 –锁住的是行，防止其他事务对此行 update 或 delete
- 间隙锁 –锁住的是间隙，防止其他事务在这个间隙 insert 产生幻读
- 临键锁 –锁住的是前面间隙+行，特定条件下可优化为行锁

测试数据 :

create table t (id int primary key, name varchar(10),age int, key (name)); 
insert into t values(1, 'zhangsan',18); 
insert into t values(2, 'lisi',20); 
insert into t values(3, 'wangwu',21); 
insert into t values(4, 'zhangsan', 17); 
insert into t values(8,'zhang',18);
insert into t values(12,'zhang',20);

说明**

- 1,2,3,4 之间其实并不可能有间隙
- 4 与 8 之间有间隙
- 8 与 12 之间有间隙
- 12 与正无穷大之间有间隙
- 其实我们的例子中还有负无穷大与 1 之间的间隙，想避免负数可以通过建表时选择数据类型为 unsigned int

间隙锁例子

事务1：

begin;
select * from t where id = 9 for update; /* 锁住的是 8 与 12 之间的间隙 */

事务2：

update t set age=100 where id = 8; /* 不会阻塞 */
update t set age=100 where id = 12; /* 不会阻塞 */
insert into t values(10,'aaa',18); /* 会阻塞 */

临键锁和记录锁例子

事务1：

begin;
select * from t where id >= 8 for update;

临键锁锁定的是左开右闭的区间，与上条查询条件相关的区间有 (4,8]，(8,12]，(12,+∞)
临键锁在某些条件下可以被优化为记录锁，例如 (4,8] 被优化为只针对 8 的记录锁，前面的区间不会锁住

事务2：

insert into t values(7,'aaa',18); /* 不会阻塞 */
update t set age=100 where id = 8; /* 会阻塞 */
insert into t values(10,'aaa',18); /* 会阻塞 */
update t set age=100 where id = 12; /* 会阻塞 */
insert into t values(13,'aaa',18); /* 会阻塞 */

SQL性能分析

SQL执行频率

MySQL 客户端连接成功后，通过 show [session|global] status 命令可以提供服务器状态信

息。通过如下指令，可以查看当前数据库的INSERT、UPDATE、DELETE、SELECT的访问频次：

注意：使用CMD查看，其他客户端可能会存在次数不一样的情况

-- session 是查看当前会话 ; 

-- global 是查询全局数据 ; 

SHOW GLOBAL STATUS LIKE 'Com_______';

那么通过查询SQL的执行频次，我们就能够知道当前数据库到底是增删改为主，还是查询为主。那假

如说是以查询为主，我们又该如何定位针对于那些查询语句进行优化呢？次数我们可以借助于慢查询

日志。

接下来，我们就来介绍一下MySQL中的慢查询日志。

慢查询日志

1、开启慢日志，修改mysql配置文件Windows中my.ini,Linux中my.conf.

2、修改mysql配置文件：

① slow-query-log=on：表示开启慢日志 off表示关闭

② long_query_time=2：表示sql运行超过2秒就会被记录到慢日志中

③slow_query_log_file = /data/mysql/slow.log 设置mysql慢查询日志路径

//使用下面命令查询慢日志配置
show VARIABLES like 'slow%'

explain执行计划

EXPLAIN 或者 DESC命令获取 MySQL 如何执行 SELECT 语句的信息，包括在 SELECT 语句执行

过程中表如何连接和连接的顺序。

语法:

-- 直接在select语句之前加上关键字 explain / desc
EXPLAIN SELECT 字段列表 FROM 表名 WHERE 条件 ;

Explain 执行计划中各个字段的含义:

常用字段:

id字段:

select查询的序列号，表示查询中执行select子句或者是操作表的顺序(id相同，执行顺序从上到下；id不同，值越大，越先执行)。

id相同，顺序从上到下执行

//查询每个学生都选修了什么课程
explain SELECT s.*,c.* FROM  student s,course c,student_course sc where s.id=sc.studentid and c.id=sc.courseid

id不同，序号越大越先执行

//查询MySQL的课程被哪些学员学习
explain select * from student where id in (select studentid from student_course where courseid=(select id from course where name ='MySQL'))

<subquery2>:代表着两个子查询的合并查询结果

type字段

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

//根据主键查询用户信息
explain select * from student where id = 1
//给name加上唯一索引
explain select * from student where name='韦一笑'

根据主键或者唯一索引查询一般返回const，这个级别算是优化的比较好的sql了

null值出现的条件比较苛刻，比如无表查询

explain select 'A';

ref值出现的原因是使用非唯一索引等值查询

//给student的no加上普通索引
explain select * from student where no='1'

eq_ref值出现的原因是两张表使用了主键和另外一张表的非主键关联查询

EXPLAIN select * from tb_goods g,tb_item d where g.id=d.goods_id

range值出现的原因是给普通索引的值使用了范围查询

//给student的no加上普通索引
explain select * from student where no>'2000100102' and no<'2000100104'

其他字段

参考表格信息即可

sql如何优化？

首先我们需要找到被优化的sql语句，我们可以再Linux的my.conf文件中添加配置来开启慢日志。

1.1. slow-query-log=on：开启
1.2. long_query_time=时间值：来设置慢sql的时间，比如=2的就表示超过2秒就是慢sql
1.3. slow_query_log_file = 指定慢日志的打印位置

当我们根据慢日志找到需要优化的sql语句之后，可以通过explain关键字来查看sql的执行计划在这个执行计划表中我们可以观察type字段：如果出现了all说明全表扫描了，一般我们公司要求至少要在range级别。

type字段的效率从高到低为null > system > const > eq_ref > ref > range > index > all

另外还可能出现 possible_keys 有列, key 表示一定会走的索引

rows: 表示扫描多少行才能找到我们想要的数据

filtered: 表示返回的结果数占扫描行数的百分比,值越大命中率越高,速度越快

当我们分析完执行计划之后，就知道这条sql语句哪些地方需要优化，我们就可以进行具体的优化了:

a. 比如不能写select * 会造成回表查，而且*表示所有字段,io也大,效率也会低
b. 我们再写模糊查询或者是使用联合索引查询数据的时候一定要遵循最左前缀原则
c. 我们不要在where条件后面加一些判断比如is null 或者是 is notnull
d. 尽量不要使用in来查询
e. 在做统计的时候可以使用count 1 或者 count * 来统计这样比较快
f. 不要在where后面写表达计算比如: 非等于!=
g. 我们在写sql的时候尽量避免隐式转换

你给我说一下你优化过的sql的具体案例:

在mysql深度分页的时候，直接使用limit进行分页的时候效率比较低，因为这个时候先通过索引字段去非聚簇索引上查询数据，但是非聚簇索引上只保存当前字段和id，并没有保存查询用到的所有数据，这个时候就需要回表去主键索引上查询需要的数据了。所以我做了一个优化避免了回表查询，是这样的首先先一个子查询这个子查询会根据索引字段去非聚簇索引查询深度分页要的数据id，获取到id的时候在跟本表进行内关联，这个时候查询用到的所有数据就可以在聚簇索引上查询的到了，整个过程没有回表，效率高。

select * from tb_user a INNER JOIN (select id from tb_user where name='aaa' LIMIT 990000,10) b where a.id=b.id

程序员_迪迦

关注

22
点赞
踩
8

收藏

觉得还不错? 一键收藏
打赏
1
评论
Mysql从简单到深入大全

当使用非聚簇索引进行搜索数据的时候，查询到的只是当前索引数据以及id数据，为了避免回表查询带来的效率影响，可以使用组合索引进行查询，在组合索引中设置额外需要查询的字段，这个时候在使用非聚簇索引查询B+Tree的时候，可以通过组合索引查询到需要的字段数据。当我们使用非聚簇索引进行搜索数据的时候，查询的是此字段的B+树，我们从B+树中找到要查询的数据之后，在通过从这棵树上查询出来的字段以及所对应的聚簇索引，再根据聚簇索引从聚簇索引的B+树上去查找，这个过程叫回表查询。id不同，值越大，越先执行)。
复制链接

扫一扫