超全MySQL学习笔记

吾爱主题阅读：109 2024-04-02 18:53:53 评论：0

myisam和innodb

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

性能下降sql慢的原因：

查询语句写的差
索引失效
关联查询太多join （设计缺陷或不得已的需求）
服务器调优及各个参数设置(缓冲，线程参数)

mysql执行顺序

手写
机读先从from开始

sqljoin

a表

1 2 3 4 5 6 7 8 9 10 11 mysql> select * from tbl_dept; + ----+----------+--------+ | id | deptname | locadd | + ----+----------+--------+ | 1 | rd | 11 | | 2 | hr | 12 | | 3 | mk | 13 | | 4 | mis | 14 | | 5 | fd | 15 | + ----+----------+--------+ 5 rows in set (0.00 sec)

b表

1 2 3 4 5 6 7 8 9 10 11 12 13 + ----+------+--------+ | id | name | deptid | + ----+------+--------+ | 1 | z3 | 1 | | 2 | z4 | 1 | | 3 | z5 | 1 | | 4 | w5 | 2 | | 5 | w6 | 2 | | 6 | s7 | 3 | | 7 | s8 | 4 | | 8 | s9 | 51 | + ----+------+--------+ 8 rows in set (0.00 sec)

mysql不支持全连接

使用以下方式可以实现全连接

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 mysql> select * from tbl_dept a right join tbl_emp b on a.id=b.deptid -> union -> select * from tbl_dept a left join tbl_emp b on a.id=b.deptid; + ------+----------+--------+------+------+--------+ | id | deptname | locadd | id | name | deptid | + ------+----------+--------+------+------+--------+ | 1 | rd | 11 | 1 | z3 | 1 | | 1 | rd | 11 | 2 | z4 | 1 | | 1 | rd | 11 | 3 | z5 | 1 | | 2 | hr | 12 | 4 | w5 | 2 | | 2 | hr | 12 | 5 | w6 | 2 | | 3 | mk | 13 | 6 | s7 | 3 | | 4 | mis | 14 | 7 | s8 | 4 | | null | null | null | 8 | s9 | 51 | | 5 | fd | 15 | null | null | null | + ------+----------+--------+------+------+--------+ 9 rows in set (0.00 sec)

a的独有和b的独有

?

1 2 3 4 5 6 7 8 9 10 mysql> select * from tbl_dept a left join tbl_emp b on a.id=b.deptid where b.id is null -> union -> select * from tbl_dept a right join tbl_emp b on a.id=b.deptid where a.id is null ; + ------+----------+--------+------+------+--------+ | id | deptname | locadd | id | name | deptid | + ------+----------+--------+------+------+--------+ | 5 | fd | 15 | null | null | null | | null | null | null | 8 | s9 | 51 | + ------+----------+--------+------+------+--------+ 2 rows in set (0.01 sec)

索引

索引的定义：

索引是帮助sql高效获取数据的数据结构，索引的本质：数据结构

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

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式（引用）指向数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引，下图就是一种示例：

一般来说索引也很大，因此索引往往以索引文件的方式存储在磁盘上

我们平常所说的索引，如果没有特别指明，一般都是指b树（多路搜索树，不一定是二叉的）结构组织的索引，

其中聚集索引，次要索引，复合索引，前缀索引，唯一索引默认都是使用b+树索引，统称索引，当然除了b+树这种类型的索引之外，还有哈希索引。

索引的优劣

1.优势

类似大学图书馆图书编号建索引，提高了数据检索的效率，降低数据库的io成本

通过索引对数据进行排序，降低数据排序的成本，降低了cpu的消耗

2.劣势

实际上索引也是一张表，该表保存了主键与存在索引的字段，并指向实体表的记录，所以索引列也是占用空间的

虽然索引大大提高了查询速度，但是会降低更新表的速度，比如 update，insert，delete操作，因为更新表时，mysql不仅要数据也要保存索引文件每次更新添加了索引的字段，都会调整因为更新所带来的键值变化后的索引信息

索引只是提高效率的一个因素，在一个大数据量的表上，需要建立最为优秀的索引或者写优秀的查询语句，而不是加了索引就能提高效率

索引分类

单值索引
唯一索引
复合索引
基本语法:

创建

1	`create` `[` `unique` `]` `index` `indexname` `on` `mytable(cloumnname(length));`

1	`alter` `mytable` `add` `[` `unique` `]` `index` `[indexname]` `on` `(columnname(length));`

删除

1	`drop` `index` `[indexname]` `on` `mytable`

查看

1	`show` `index` `from` `table_name\g`

有四种方式来添加数据表的索引

mysql索引结构

btree索引
hash索引
full-text全文索引
r-tree

那些情况建索引

主键自动建立唯一索引
频繁作为查询条件的字段应该创建索引
查询中与其他表相关联的字段，外键关系建立索引
频繁更新的字段不适合创建索引，因为每次更新不单单更新了记录还更新了索引
where条件里用不到的字段不要创建索引
单键/组合索引的选择问题 who？（高并发下建议组合索引）
查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
查询中统计或分组字段

哪些情况不要建索引

表记录少
经常操作dml语句的表
数据重复且平均分布的表字段，因此只为最经常查询和最经常排序的数据列建立索引，注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果

性能分析

explian重点

能干什么

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

id三种情况

id 相同，执行顺序由上至下
id不同，如果是子查询，id序号递增，id越大优先级越高
id相同不同，同时存在

select_type

simple 简单查询
primary 主查询（最外层的查询）
subquery 子查询
deriued 某个查询的子查询的临时表
union 联合查询
union result 联合查询结果

type：：

type显示的是访问类型排列，是较为重要的一个指标

从最好到最差依次是：

system > const > eq_ref> ref > range > index > all;

一般来说，得保证查询至少达到range级别，最好ref

----------------------------------------------type类型-------------------------------------------------------

system:表只有一行记录（等于系统表）这是const类型的特列一般不会出现，可忽略不计
const：表示通过索引一次就查询到了，const用来比较primary key或者unique索引。因为只匹配一行数据，所以很快，如将主键置于where列表中，mysql就能将该查询转换为一个常量
eq_ref：唯一性索引扫描，表中只有一条记录与之匹配，常用于主键或唯一索引扫描（两个表是多对一或者一对一的关系，被连接的表是一的情况下，他的查询是eq_ref）
ref：非唯一性索引扫描，返回匹配某个单独值的所有行，本质上也是一种索引访问，它返回匹配某个单独值的所有行，然而他可能会找到多个复合条件的行，属于查找和扫描的结合体
range：只检索给定范围的行，使用一个索引来选择行，key列显示使用了哪个索引，一般where语句里出现了betweent，<,>,in等的查询，这种范围扫描索引比全表扫描好
index：index与all的区别，index只遍历索引树，索引文件通常比数据文件小
all：全表扫描

----------------------------------------------type类型-------------------------------------------------------

possible_keys：显示可能应用的的索引（理论上）
key：实际使用的索引，查询中若使用了覆盖索引，则该索引仅仅出现在key中
key_len：表示索引中使用的字节数，在不损失精度的情况下越短越好，kenlen显示的值为索引字段的最大可能长度，并非实际使用长度，kenlen是根据表定义计算而得，而不是通过表内检索出的

key_len长度：13是因为char(4)*utf8(3)+允许为null(1)=13

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

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

没建立索引时查询t1 t2表 t1表对应t2表的id t2表 col1的值要为'ac'

对于id这个字段t1表对t2表相当于一对多

t1表的type为 eq_ref代表唯一性索引扫描，表中只有一条记录与之匹配，t2表对应t1的这个id对应的col值只有一个，根据t2表的主键id索引查询，t1表读取了一行，t2表读取了640行

建立索引后

t1读取一行，t2读取142行，ref非唯一性索引扫描，返回匹配某个单独值的所有行，返回t2对应id的col所有行，而t1对应id的col只有一行，所以type为eq_ref

extra

包含不适合在其他列展现但十分重要的信息

\g ：竖直显示排序

using filesort：说明mysql会对数据使用一个外部的索引排序，而不是按照表内的索引顺序进行读取，mysql中无法利用索引完成排序的操作称为文件排序未被方框框住的图建立了复合索引，但是直接使用col3进行排序导致空中楼阁，mysql不得已只能进行filesoft

using temporary：使用了临时表保存中间中间结果，mysql在对查询结果排序时使用临时表。常见于order by排序和group by分组上表中建立了复合索引 col1_col2 但是直接通过col2进行分组导致了mysql不得已只能进行filesoft和建立临时表
using index 表示相应的select操作中使用了覆盖索引，避免访问了表的数据行，如果同时出现using where 表示索引被用来执行索引键值的查找，没有usingwhere表示索引用来读取数据而非执行查找动作
using where 表示使用了 where过滤
using join buffer 私用了链接缓存
impossible buffer where子句的值总是false 不能用来获取任何元组
select tables optimized away 在没有group by子句的情况下，基于索引优化min/max操作，或者对myisam存储引擎执行count(*)操作，不必等到执行操作进行，查询执行计划生成的阶段即完成优化
distinct 优化distinct操作，在找到第一匹配的元组后立即停止查找同样值的操作

案例

索引优化

单表优化

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 create table if not exists `article`( `id` int (10) unsigned not null primary key auto_increment, `author_id` int (10) unsigned not null , `category_id` int (10) unsigned not null , `views` int (10) unsigned not null , `comments` int (10) unsigned not null , `title` varbinary(255) not null , `content` text not null ); insert into `article`(`author_id`,`category_id` ,`views` ,`comments` ,`title` ,`content` ) values (1,1,1,1, '1' , '1' ), (2,2,2,2, '2' , '2' ), (1,1,3,3, '3' , '3' ); select * from article; mysql> select id,author_id from article where category_id = 1 and comments > 1 order by views desc limit 1; + ----+-----------+ | id | author_id | + ----+-----------+ | 3 | 1 | + ----+-----------+ 1 row in set (0.00 sec) mysql> explain select author_id from article where category_id = 1 and comments > 1 order by views desc li imit 1; + ----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra | + ----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+ | 1 | simple | article | null | all | null | null | null | null | 3 | 33.33 | using where ; using filesort | + ----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+ 1 row in set , 1 warning (0.00 sec)

可以看出虽然查询出来了但是 type是all，extra里面出现了using filesort证明查询效率很低

需要优化

建立索引

1	`create` `index` `idx_article_ccv` `on` `article(category_id,comments,views);`

查询

这里发现type 变为了 range 查询全表变为了范围查询优化了一点

但是 extra 仍然有 using filesort 证明索引优化并不成功

所以我们删除索引

1	`drop` `index` `idx_article_ccv` `on` `article;`

建立新的索引，排除掉range

这时候会发现优化成功 type 变为了ref extra变为了 using where

在这次实验中我又加入了一次试验发现当建立索引时comments放在最后也是可行的

这里发现了 type仍然是ref，extra也是usingwhere，而只是把索引建立的位置换了一换，把范围查询的字段挪到了最后!!!!

双表优化

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 create table if not exists `class`( `id` int (10) unsigned not null primary key auto_increment, `card` int (10) unsigned not null ); create table if not exists `book`( `bookid` int (10) unsigned not null primary key auto_increment, `card` int (10) unsigned not null ); insert into class(card) values (floor(1+(rand()*20))); insert into class(card) values (floor(1+(rand()*20))); insert into class(card) values (floor(1+(rand()*20))); insert into class(card) values (floor(1+(rand()*20))); insert into class(card) values (floor(1+(rand()*20))); insert into class(card) values (floor(1+(rand()*20))); insert into class(card) values (floor(1+(rand()*20))); insert into class(card) values (floor(1+(rand()*20))); insert into class(card) values (floor(1+(rand()*20))); insert into class(card) values (floor(1+(rand()*20))); insert into class(card) values (floor(1+(rand()*20))); insert into class(card) values (floor(1+(rand()*20))); insert into class(card) values (floor(1+(rand()*20))); insert into class(card) values (floor(1+(rand()*20))); insert into class(card) values (floor(1+(rand()*20))); insert into class(card) values (floor(1+(rand()*20))); insert into class(card) values (floor(1+(rand()*20))); insert into class(card) values (floor(1+(rand()*20))); insert into class(card) values (floor(1+(rand()*20))); insert into class(card) values (floor(1+(rand()*20))); insert into book(card) values (floor(1+(rand()*20))); insert into book(card) values (floor(1+(rand()*20))); insert into book(card) values (floor(1+(rand()*20))); insert into book(card) values (floor(1+(rand()*20))); insert into book(card) values (floor(1+(rand()*20))); insert into book(card) values (floor(1+(rand()*20))); insert into book(card) values (floor(1+(rand()*20))); insert into book(card) values (floor(1+(rand()*20))); insert into book(card) values (floor(1+(rand()*20))); insert into book(card) values (floor(1+(rand()*20))); insert into book(card) values (floor(1+(rand()*20))); insert into book(card) values (floor(1+(rand()*20))); insert into book(card) values (floor(1+(rand()*20))); insert into book(card) values (floor(1+(rand()*20))); insert into book(card) values (floor(1+(rand()*20))); insert into book(card) values (floor(1+(rand()*20))); insert into book(card) values (floor(1+(rand()*20))); insert into book(card) values (floor(1+(rand()*20))); insert into book(card) values (floor(1+(rand()*20))); insert into book(card) values (floor(1+(rand()*20))); mysql> create index y on book(card); explain select * from book left join class on book.card=class.card; + ----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------------------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra | + ----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------------------------+ | 1 | simple | book | null | index | null | y | 4 | null | 20 | 100.00 | using index | | 1 | simple | class | null | all | null | null | null | null | 20 | 100.00 | using where ; using join buffer (block nested loop) | + ----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------------------------+ 2 rows in set , 1 warning (0.00 sec)

会发现并无多大区别还是全表查询这是因为俩表查询左连接把左表必须全查询这时候只有对右表建立索引才有用

相反的右链接必须对左表建立索引才有用

对右表建立索引

1 2 3 4 5 6 7 8 9 create index y on class; explain select * from book left join class on book.card=class.card; + ----+-------------+-------+------------+-------+---------------+------+---------+----------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra | + ----+-------------+-------+------------+-------+---------------+------+---------+----------------+------+----------+-------------+ | 1 | simple | book | null | index | null | y | 4 | null | 20 | 100.00 | using index | | 1 | simple | class | null | ref | y | y | 4 | db01.book.card | 1 | 100.00 | using index | + ----+-------------+-------+------------+-------+---------------+------+---------+----------------+------+----------+-------------+ 2 rows in set , 1 warning (0.00 sec)

会发现右表只查询了一次。。type为ref

三表优化

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 create table if not exists `phone`( `phoneid` int (10) unsigned not null primary key auto_increment, `card` int (10) unsigned not null )engine = innodb; insert into phone(card) values (floor(1+(rand()*20))); insert into phone(card) values (floor(1+(rand()*20))); insert into phone(card) values (floor(1+(rand()*20))); insert into phone(card) values (floor(1+(rand()*20))); insert into phone(card) values (floor(1+(rand()*20))); insert into phone(card) values (floor(1+(rand()*20))); insert into phone(card) values (floor(1+(rand()*20))); insert into phone(card) values (floor(1+(rand()*20))); insert into phone(card) values (floor(1+(rand()*20))); insert into phone(card) values (floor(1+(rand()*20))); insert into phone(card) values (floor(1+(rand()*20))); insert into phone(card) values (floor(1+(rand()*20))); insert into phone(card) values (floor(1+(rand()*20))); insert into phone(card) values (floor(1+(rand()*20))); insert into phone(card) values (floor(1+(rand()*20))); insert into phone(card) values (floor(1+(rand()*20))); insert into phone(card) values (floor(1+(rand()*20))); insert into phone(card) values (floor(1+(rand()*20))); insert into phone(card) values (floor(1+(rand()*20))); insert into phone(card) values (floor(1+(rand()*20)));

先删除所有索引

建立索引

1 2 3 4 5 6 7 8 9 10 11 12 create index y on book(card); create index z on phone(card); explain select * from class left join book on class.card=book.card left join phone on book.card=phone.card; + ----+-------------+-------+------------+------+---------------+------+---------+-----------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra | + ----+-------------+-------+------------+------+---------------+------+---------+-----------------+------+----------+-------------+ | 1 | simple | class | null | all | null | null | null | null | 20 | 100.00 | null | | 1 | simple | book | null | ref | y | y | 4 | db01.class.card | 1 | 100.00 | using index | | 1 | simple | phone | null | ref | z | z | 4 | db01.book.card | 1 | 100.00 | using index | + ----+-------------+-------+------------+------+---------------+------+---------+-----------------+------+----------+-------------+ 3 rows in set , 1 warning (0.00 sec)

会发现索引建立的非常成功。。但是left join 最左表必须全部查询 建立索引

1 2 3 4 5 6 7 8 9 10 create index x on class(card); explain select * from class left join book on class.card=book.card left join phone on book.card=phone.card; + ----+-------------+-------+------------+-------+---------------+------+---------+-----------------+------+----------+-------------+ | id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | extra | + ----+-------------+-------+------------+-------+---------------+------+---------+-----------------+------+----------+-------------+ | 1 | simple | class | null | index | null | x | 4 | null | 20 | 100.00 | using index | | 1 | simple | book | null | ref | y | y | 4 | db01.class.card | 1 | 100.00 | using index | | 1 | simple | phone | null | ref | z | z | 4 | db01.book.card | 1 | 100.00 | using index | + ----+-------------+-------+------------+-------+---------------+------+---------+-----------------+------+----------+-------------+ 3 rows in set , 1 warning (0.00 sec)

结果仍然一样

建立表

1 2 3 4 5 6 7 8 9 10 11 12 13 create table staffs( id int primary key auto_increment, ` name ` varchar (24) not null default '' comment '姓名' , `age` int not null default 0 comment '年龄' , `pos` varchar (20) not null default '' comment '职位' , `add_time` timestamp not null default current_timestamp comment '入职时间' )charset utf8 comment '员工记录表' ; insert into staffs(` name `,`age`,`pos`,`add_time`) values ( 'z3' ,22, 'manager' ,now()); insert into staffs(` name `,`age`,`pos`,`add_time`) values ( 'july' ,23, 'dev' ,now()); insert into staffs(` name `,`age`,`pos`,`add_time`) values ( '2000' ,23, 'dev' ,now()); 建立索引 alter table staffs add index index_staffs_nameagepos(` name `,`age`,`pos`);

索引口诀

1.带头大哥不能死，中间兄弟不能断:当建立复合索引时，必须带上头索引，不能跳过中间索引直接使用后面的索引，使用后面的索引必须加上中间的索引（可以先使用后面的索引再使用中间的索引，但是不能直接使用后面的索引而跳过中间索引）（针对where）

可以从上图看出跳过name的都用不了索引

可以从语句中看出跳过中间的索引后 key_len 不变证明第索引pos没有被用到

2.不能对索引列进行任何操作（计算，类型转换等等）
3.存储引擎不能使用索引中范围条件右边的列（索引列上少计算）
4.尽量使用覆盖索引，即是只访问索引的查询减少select *的用法
5.少使用（！=，<>,<,>） is not null ,is null;
6.like以 '%'开头会导致索引失效（使用覆盖索引课避免索引失效）覆盖索引：（建立的索引与查询的字段顺序数量尽量一致）
7.字符串不加单引号会导致索引失效（mysql会将字符串类型强制转换导致索引失效）
8.少用or，用它连接会失效

索引案例

假设index（a，b，c）

y代表索引全部使用了 n全没使用

where语句	索引是否被使用
where a=3 and c=5	(中间b断掉了)使用了a 没使用c
where a=3 and b=4 andc=5	y
where a=3 and c=5 and b=4	y这里mysql自动做了优化对语句排序
where a=3 and b>4 and c=5	a,b被使用
where a=3 and b like 'k%' and c=5	y like后面常量开头索引全用
where b=3 and c=4	n
where a=3 and c>5 and b=4	y:mysql自动做了优化对语句排序范围c之后索引才会失效
where b=3 and c=4 and a=2	y :mysql自动做了优化对语句排序
where c=5 and b=4 and a=3	y :mysql自动做了优化对语句排序

假设index（a，b，c, d）

1 2 3 4 5 6 7 8 9 10 11 12 13 create table test03( id int primary key not null auto_increment, a int (10), b int (10), c int (10), d int (10), insert into test03(a,b,c,d) values (3,4,5,6); insert into test03(a,b,c,d) values (3,4,5,6); insert into test03(a,b,c,d) values (3,4,5,6); insert into test03(a,b,c,d) values (3,4,5,6); create index idx_test03_abcd on test03(a,b,c,d);

###

where a=3 and b>4 and c=5	使用了a和b ，b后面的索引全失效
where a=3 and b=4 and d=6 order by c	使用了a和b，c其实也用了但是是用在排序，没有统计到mysql中
where a=3 and b=4 order by c	使用了a和b，c其实也用了但是是用在排序，没有统计到mysql中
where a=3 and b=4 order by d	使用了a和b, 这里跳过c 会导致using filesort
where a=3 and d=6 order by b ,c	使用了a, 排序用到了b,c索引
where a=3 and d=6 order by c ,b	使用了 a，会产生using filesort，因为跳过了b对c进行排序
where a=3 and b=4 order by b ,c	y 全使用
where a=3 and b=4 and d&##61;6 order by c , b	使用了a，b，不会产生using filesort 因为在对c，b排序前对b进行了查询，查询时b已经确定了（常量），这样就没有跳过b对c进行排序了，而是相当于直接对c排序相当于第三格的查询语句

group by 更严重group by先分组再排序把order by换为 group by 甚至会产生using temporary，与order by差不多，但是更严重而且与group by产生的效果差不多就不做演示了

order by 索引优化

orderby 条件	extra
where a>4 order by a	using where using index
where a>4 order by a,b	using where using index
where a>4 order by b	using where, using index ,using filesort（order by 后面带头大哥不在）
where a>4 order by b,a	using where, using index ,using filesort（order by 后面顺序）
where a=const order by b,c	如果where使用索引的最左前缀定义为常量，则order by能使用索引
where a=const and b=const order by c	where使用索引的最左前缀定义为常量，则order by能使用索引
where a=const and b>3 order by b c	using where using index
order by a asc, b desc ,c desc	排序不一致升降机

exsites

1 2 3 4 5 6 7 select a.* from a a where exists( select 1 from b b where a.id=b.id) 以上查询使用了exists语句,exists()会执行a.length次,它并不缓存exists()结果集,因为exists()结果集的内容并不重要,重要的是结果集中是否有记录,如果有则返回 true ,没有则返回 false . 它的查询过程类似于以下过程 list resultset=[]; array a=( select * from a) for ( int i=0;i<a.length;i++) { if(exists(a[i].id) { //执行 select 1 from b b where b.id=a.id是否有记录返回 resultset. add (a[i]); } } return resultset; 当b表比a表数据大时适合使用exists(),因为它没有那么遍历操作,只需要再执行一次查询就行. 如:a表有10000条记录,b表有1000000条记录,那么exists()会执行10000次去判断a表中的id是否与b表中的id相等. 如:a表有10000条记录,b表有100000000条记录,那么exists()还是执行10000次,因为它只执行a.length次,可见b表数据越多,越适合exists()发挥效果. 再如:a表有10000条记录,b表有100条记录,那么exists()还是执行10000次,还不如使用 in ()遍历10000*100次,因为 in ()是在内存里遍历比较,而exists()需要查询数据库,我们都知道查询数据库所消耗的性能更高,而内存比较很快.

mysql慢查询日志命令

1	`show variables` `like` `'%slow_query_log%'` `;`

显示是否开启mysql慢查询日志

1	`set` `global` `slow_query_log=0;`

关闭mysql慢查询日志

1	`set` `global` `slow_query_log=1;`

开启mysql慢查询日志

1	`show variables` `like` `'%long_query_time%'` `;`

显示超过多长时间即为慢查询

1	`set` `global` `long_quert_time=10;`

修改慢查询时间为10秒，当查询语句时间超过10秒即为慢查询

1	`show` `global` `status` `like` `'%slow_queries%'` `;`

显示一共有几条慢查询语句

1	`[root@iz0jlh1zn42cgftmrf6p6sz data]# cat mysql-slow.log`

linux查询慢sql

函数操作批量插入数据

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 create table dept( id int unsigned primary key auto_increment, deptno mediumint unsigned not null default 0, dname varchar (20) not null default '' , loc varchar (13) not null default '' )engine=innodb default charset=gbk; create table emp( id int unsigned primary key auto_increment, empno mediumint unsigned not null default 0, #编号 enname varchar (20) not null default '' , #名字 job varchar (9) not null default '' , #工作 mgr mediumint unsigned not null default 0, #上级编号 hiredate date not null , #入职时间 sal decimal (7,2) not null , #薪水 comm decimal (7,2) not null , #红利 deptno mediumint unsigned not null default 0 #部门编号 )engine=innodb default charset=gbk;

1 2	`show variables` `like` `'log_bin_trust_function_creators'` `;` `set` `global` `log_bin_trust_function_creators=1;`

创建函数：随机产生部门编号随机产生字符串

delimiter $$是因为sql都是；进行结尾但是创建函数过程要多次使用；所以改变sql执行结束的条件为输入$$，相当于代替了分号' ；'

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 //定义函数1 delimiter $$ create function rand_string(n int ) returns varchar (255) begin declare chars_set varchar (100) default 'abcdefghigklmnopqrstuvwxyzabcdefghigklmnopqrstuvwxyz' ; declare return_str varchar (255) default '' ; declare i int default 0; while i < n do set return_str = concat(return_str, substring (chars_set,floor(1 + rand()*52),1)); set i = i + 1; end while; return return_str; end $$ //定义函数2 delimiter $$ create function rand_num() returns int (5) begin declare i int default 0; set i = floor(100 + rand()*10); return i; end $$ //定义存储过程1 delimiter $$ create procedure insert_emp( in start int (10), in max_num int (10)) begin declare i int default 0; set autocommit = 0; repeat set i = i + 1; insert into emp(empno, enname, job, mgr, hiredate, sal, comm, deptno) values ((start + i),rand_string(6), 'salesman' ,0001,curdate(),2000,400,rand_num()); until i = max_num end repeat; commit ; end $$ //定义存储过程2 delimiter $$ create procedure insert_dept( in start int (10), in max_num int (10)) begin declare i int default 0; set autocommit = 0; repeat set i = i + 1; insert into dept(deptno,dname,loc) values ((start + i),rand_string(10),rand_string(8)); until i = max_num end repeat; commit ; end $$ //开始插入数据 delimiter ; call insert_dept(100,10); call insert_emp(100001,500000); show profile分析sql mysql> show variables like 'profiling' ; + ---------------+-------+ | variable_name | value | + ---------------+-------+ | profiling | off | + ---------------+-------+ 1 row in set (0.00 sec) mysql> set profiling= on ; query ok, 0 rows affected, 1 warning (0.00 sec) mysql> show variables like 'profiling' ; + ---------------+-------+ | variable_name | value | + ---------------+-------+ | profiling | on | + ---------------+-------+ 1 row in set (0.01 sec) ————————————————

随便写几条插入语句‘

显示查询操作语句的速度

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 mysql> show profiles; + ----------+------------+----------------------------------------------------------------+ | query_id | duration | query | + ----------+------------+----------------------------------------------------------------+ | 1 | 0.00125325 | show variables like 'profiling' | | 2 | 0.00018850 | select * from dept | | 3 | 0.00016825 | select * from tb1_emp e inner join tbl_dept d on e.deptid=d.id | | 4 | 0.00023900 | show tables | | 5 | 0.00031125 | select * from tbl_emp e inner join tbl_dept d on e.deptid=d.id | | 6 | 0.00024775 | select * from tbl_emp e inner join tbl_dept d on e.deptid=d.id | | 7 | 0.00023725 | select * from tbl_emp e inner join tbl_dept d on e.deptid=d.id | | 8 | 0.00023825 | select * from tbl_emp e left join tbl_dept d on e.deptid=d.id | | 9 | 0.35058075 | select * from emp group by id%10 limit 15000 | | 10 | 0.35542250 | select * from emp group by id%10 limit 15000 | | 11 | 0.00024550 | select * from tbl_emp e left join tbl_dept d on e.deptid=d.id | | 12 | 0.36441850 | select * from emp group by id%20 order by 5 | + ----------+------------+----------------------------------------------------------------+ 12 rows in set , 1 warning (0.00 sec)

显示查询过程 sql生命周期

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 mysql> show profile cpu,block io for query 3; + ----------------------+----------+----------+------------+--------------+---------------+ | status | duration | cpu_user | cpu_system | block_ops_in | block_ops_out | + ----------------------+----------+----------+------------+--------------+---------------+ | starting | 0.000062 | 0.000040 | 0.000021 | 0 | 0 | | checking permissions | 0.000004 | 0.000003 | 0.000001 | 0 | 0 | | checking permissions | 0.000015 | 0.000006 | 0.000003 | 0 | 0 | | opening tables | 0.000059 | 0.000039 | 0.000020 | 0 | 0 | | query end | 0.000004 | 0.000002 | 0.000001 | 0 | 0 | | closing tables | 0.000002 | 0.000001 | 0.000000 | 0 | 0 | | freeing items | 0.000014 | 0.000010 | 0.000005 | 0 | 0 | | cleaning up | 0.000009 | 0.000006 | 0.000003 | 0 | 0 | + ----------------------+----------+----------+------------+--------------+---------------+ 8 rows in set , 1 warning (0.00 sec) mysql> show profile cpu,block io for query 12; + ----------------------+----------+----------+------------+--------------+---------------+ | status | duration | cpu_user | cpu_system | block_ops_in | block_ops_out | + ----------------------+----------+----------+------------+--------------+---------------+ | starting | 0.000063 | 0.000042 | 0.000021 | 0 | 0 | | checking permissions | 0.000006 | 0.000003 | 0.000002 | 0 | 0 | | opening tables | 0.000013 | 0.000009 | 0.000004 | 0 | 0 | | init | 0.000028 | 0.000017 | 0.000008 | 0 | 0 | | system lock | 0.000007 | 0.000004 | 0.000002 | 0 | 0 | | optimizing | 0.000004 | 0.000002 | 0.000002 | 0 | 0 | | statistics | 0.000014 | 0.000010 | 0.000004 | 0 | 0 | | preparing | 0.000008 | 0.000005 | 0.000003 | 0 | 0 | | creating tmp table | 0.000028 | 0.000018 | 0.000009 | 0 | 0 | | sorting result | 0.000003 | 0.000002 | 0.000001 | 0 | 0 | | executing | 0.000002 | 0.000002 | 0.000001 | 0 | 0 | | sending data | 0.364132 | 0.360529 | 0.002426 | 0 | 0 | | creating sort index | 0.000053 | 0.000034 | 0.000017 | 0 | 0 | | end | 0.000004 | 0.000002 | 0.000002 | 0 | 0 | | query end | 0.000007 | 0.000005 | 0.000002 | 0 | 0 | | removing tmp table | 0.000005 | 0.000003 | 0.000002 | 0 | 0 | | query end | 0.000003 | 0.000002 | 0.000001 | 0 | 0 | | closing tables | 0.000006 | 0.000004 | 0.000002 | 0 | 0 | | freeing items | 0.000023 | 0.000016 | 0.000007 | 0 | 0 | | cleaning up | 0.000012 | 0.000007 | 0.000004 | 0 | 0 | + ----------------------+----------+----------+------------+--------------+---------------+ 20 rows in set , 1 warning (0.00 sec)

如果出现以上这四个中的任何一个就需要优化查询语句

全局查询日志

1 2	`set` `global` `general_log=1;` `set` `global` `log_output=` `'table'` `;`

此后你编写的sql语句将会记录到mysql库里的general_log表，可以用下面的命令查看

1 2 3 4 5 6 7 8 select * from mysql.general_log; mysql> select * from mysql.general_log; + ----------------------------+---------------------------+-----------+-----------+--------------+---------------------------------+ | event_time | user_host | thread_id | server_id | command_type | argument | + ----------------------------+---------------------------+-----------+-----------+--------------+---------------------------------+ | 2021-12-06 11:53:53.457242 | root[root] @ localhost [] | 68 | 1 | query | select * from mysql.general_log | + ----------------------------+---------------------------+-----------+-----------+--------------+---------------------------------+ 1 row in set (0.00 sec)

mysql锁

读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响
写锁（排它锁）：当前写操作没有完成时，它会阻断其他写锁和读锁
行锁：偏向innodb引擎，开销大，加锁慢，会出现死锁：锁定粒度最小，发生锁冲突的概率最低，并发量高
表锁：偏向myisam引擎，开销小，加锁快；锁定粒度大，发生锁冲突的概率最高，并发度最低

在下面进行表锁的测试

1 2 3 4 5 6 7 8 9 10 11 12 13 14 use big_data; create table mylock ( id int not null primary key auto_increment, name varchar (20) default '' ) engine myisam; insert into mylock( name ) values ( 'a' ); insert into mylock( name ) values ( 'b' ); insert into mylock( name ) values ( 'c' ); insert into mylock( name ) values ( 'd' ); insert into mylock( name ) values ( 'e' ); select * from mylock;

锁命令

1 2 3 lock table mylock read ,book write;## 读锁锁mylock 写锁锁book show open tables; ##显示哪些表被加锁了 unlock tables;##取消锁

表锁：读锁

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 ##添加读锁后不可修改 mysql> lock table mylock read ;##1 query ok, 0 rows affected (0.00 sec) mysql> select * from mylock;##1 + ----+------+ | id | name | + ----+------+ | 1 | a | | 2 | b | | 3 | c | | 4 | d | | 5 | e | + ----+------+ 5 rows in set (0.00 sec) mysql> update mylock set name = 'a2' where id=1; ##1 error 1099 (hy000): table 'mylock' was locked with a read lock and can 't be updated ##改不了当前读锁锁住的表 ##读不了其他表 mysql> select * from book;##1 error 1100 (hy000): table ' book' was not locked with lock tables

为了区分两个命令把1当作原有的mysql命令终端上的操作，2当作新建的mysql终端

新建一个mysql终端命令操作

1 2	`##新建一个mysql终端命令操作` `mysql>` `update` `mylock` `set` `name` `=` `'a3'` `where` `id=1; ##2`

发现会出现阻塞操作

在原有的mysql命令终端上取消锁

1 2 3 unlock tables;##1 query ok, 1 row affected (2 min 1.46 sec) ##2 rows matched: 1 changed: 1 warnings: 0 ##2

会发现阻塞了两分钟多

总结：当读锁锁表mylock之后：1.查询操作：当前client（终端命令操作1）可以进行查询表mylock

其他client（终端命令操作2）也可以查询表mylock 2.dml操作（增删改）当前client会失效报错 error 1099 (hy000): table 'mylock' was locked with a read lock and can't be updated 其他client进行dml操作会让mysql陷入阻塞状态直到当前session释放锁

表锁：写锁

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 mysql> lock table mylock write; query ok, 0 rows affected (0.00 sec) 给当前session mylock表加上写锁 mysql> update mylock set name = 'a4' where id=1 ; query ok, 1 row affected (0.00 sec) rows matched: 1 changed: 1 warnings: 0 mysql> select * from mylock; + ----+------+ | id | name | + ----+------+ | 1 | a4 | | 2 | b | | 3 | c | | 4 | d | | 5 | e | + ----+------+ mysql> select * from book; error 1100 (hy000): table 'book' was not locked with lock tables

会发现无法操其他表但是可以操作加上锁的表

再开启一个新的客户端测试被锁住的表

1 2 3 mysql> select * from mylock; 5 rows in set (2 min 30.92 sec)

发现新的客户端上操作（增删改查）被写锁锁住的表会陷入阻塞状态

作

分析表锁定

1 2 3 4 5 6 7 8 9 10 11 mysql> show status like 'table%' ; + ----------------------------+-------+ | variable_name | value | + ----------------------------+-------+ | table_locks_immediate | 194 | | table_locks_waited | 0 | | table_open_cache_hits | 18 | | table_open_cache_misses | 2 | | table_open_cache_overflows | 0 | + ----------------------------+-------+ 5 rows in set (0.00 sec)

行锁

innodb 的行锁模式

innodb 实现了以下两种类型的行锁。

共享锁（s）：又称为读锁，简称s锁，共享锁就是多个事务对于同一数据可以共享一把锁，都能访问到数据，但是只能读不能修改。
排他锁（x）：又称为写锁，简称x锁，排他锁就是不能与其他锁并存，如一个事务获取了一个数据行的排他锁，其他事务就不能再获取该行的其他锁，包括共享锁和排他锁，但是获取排他锁的事务是可以对数据就行读取和修改。

对于update、delete和insert语句，innodb会自动给涉及数据集加排他锁（x)；

对于普通select语句，innodb不会加任何锁；

可以通过以下语句显示给记录集加共享锁或排他锁。

1 2 3 共享锁（s）： select * from table_name where ... lock in share mode 排他锁（x) ： select * from table_name where ... for update

由于行锁支持事务，在此复习一下

事务

事务是一组由sql语句组成的逻辑处理单元，事务具有四个属性：acid

原子性（atomicity）：事务是一个原子操作单元，其对数据的操作要么全部执行，要么全不执行。
一致性（consistent）：在事务开始和完成时，数据都必须保持一致状态。这意味着所有相关的数据都必须应用于事务的修改，以保持数据的完整性；事务结束时，所有的内部数据结构（如b树索引或双向链表）也都必须是正确的。
隔离性（isolation）：数据库提供一定的隔离机制，保证事务在不受外部并发操作影响的"独立"环境执行。这意味着事务处理过程的中间状态对外部都是不可见的，反之亦然。
持久性（durable）：事务完成后，它对数据的操作是永久性的，哪怕出现系统故障也能维持

并发事务带来的问题：

更新丢失，脏读，不可重复读，幻读

acid属性	含义
原子性（atomicity）	事务是一个原子操作单元，其对数据的修改，要么全部成功，要么全部失败。
一致性（consistent）	在事务开始和完成时，数据都必须保持一致状态。
隔离性（isolation）	数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的 “独立” 环境下运行。
持久性（durable）	事务完成之后，对于数据的修改是永久的。

并发事务处理带来的问题

问题	含义
丢失更新（lost update）	当两个或多个事务选择同一行，最初的事务修改的值，会被后面的事务修改的值覆盖。
脏读（dirty reads）	当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。
不可重复读（non-repeatable reads）	一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现和以前读出的数据不一致。
幻读（phantom reads）	一个事务按照相同的查询条件重新读取以前查询过的数据，却发现其他事务插入了满足其查询条件的新数据。

事务隔离级别

为了解决上述提到的事务并发问题，数据库提供一定的事务隔离机制来解决这个问题。数据库的事务隔离越严格，并发副作用越小，但付出的代价也就越大，因为事务隔离实质上就是使用事务在一定程度上“串行化” 进行，这显然与“并发” 是矛盾的。

数据库的隔离级别有4个，由低到高依次为read uncommitted、read committed、repeatable read、serializable，这四个级别可以逐个解决脏写、脏读、不可重复读、幻读这几类问题。

隔离级别	丢失更新	脏读	不可重复读	幻读
read uncommitted	×	√	√	√
read committed	×	×	√	√
repeatable read（默认）	×	×	×	√
serializable	×	×	×	×

备注： √ 代表可能出现， × 代表不会出现 。

mysql 的数据库的默认隔离级别为 repeatable read ，查看方式：

1	`show variables` `like` `'tx_isolation'` `;`

行锁测试建表，案例准备工作

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 create table test_innodb_lock( id int (11), name varchar (16), sex varchar (1) )engine = innodb default charset=utf8; insert into test_innodb_lock values (1, '100' , '1' ); insert into test_innodb_lock values (3, '3' , '1' ); insert into test_innodb_lock values (4, '400' , '0' ); insert into test_innodb_lock values (5, '500' , '1' ); insert into test_innodb_lock values (6, '600' , '0' ); insert into test_innodb_lock values (7, '700' , '0' ); insert into test_innodb_lock values (8, '800' , '1' ); insert into test_innodb_lock values (9, '900' , '1' ); insert into test_innodb_lock values (1, '200' , '0' ); create index idx_test_innodb_lock_id on test_innodb_lock(id); create index idx_test_innodb_lock_name on test_innodb_lock( name );

行锁测试

还是开俩个终端测试，关闭事自动事务提交，因为自动事务提交会自动加锁释放锁；

1 2 3 mysql> set autocommit=0; mysql> set autocommit=0;

会发现查询无影响

对左边进行更新操作

1 2 3 mysql> update test_innodb_lock set name = '100' where id=3; query ok, 0 rows affected (0.00 sec) rows matched: 1 changed: 0 warnings: 0

对左边进行更新操作

对右边进行更新操作后停止操作

1 2	`mysql>` `update` `test_innodb_lock` `set` `name` `=` `'340'` `where` `id=3;` `error 1205 (hy000): lock wait timeout exceeded; try restarting` `transaction`

会发现进行阻塞了直到锁释放或者提交事务（commit）为止

对于innodb引擎来说，对某一行数据进行dml（增删改）操作会对操作的那行添加排它锁

别的事务就不能执行这行语句了，但是可以操作其他行的数据

无索引行锁会升级成表锁：如果不通过索引条件检索数据，那么innodb会对表中所有记录加锁，实际效果和表锁一样

记住进行操作时使用索引：innodb引擎索引失效时时行锁会升级为表锁

1 2 3 mysql> update test_innodb_lock set sex= '2' where name =400; query ok, 0 rows affected (0.00 sec) rows matched: 2 changed: 0 warnings: 0

注意这里name没有加单引号索引失效

1 2 3 mysql> update test_innodb_lock set sex= '3' where id=3; query ok, 1 row affected (23.20 sec) rows matched: 1 changed: 1 warnings: 0

发现了对其他行操作也陷入了阻塞状态，这是没加索引导致的行锁升级为表锁

本来只对一行数据加锁但是由于忘记给name字段加单引号导致索引失效给全表都加上了锁；

间隙锁

当我们使用范围条件而不是想等条件进行检索数据，并请求共享或排它锁，在那个范围条件中有不存在的记录，叫做间隙，innodb也会对这个间隙进行加锁，这种锁机制就叫做间隙锁

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 mysql> select * from test_innodb_lock; + ------+------+------+ | id | name | sex | + ------+------+------+ | 1 | 100 | 2 | | 3 | 100 | 3 | | 4 | 400 | 0 | | 5 | 500 | 1 | | 6 | 600 | 0 | | 7 | 700 | 3 | | 8 | 800 | 1 | | 9 | 900 | 2 | | 1 | 200 | 0 | + ------+------+------+ 没有id为2的数据

行锁征用情况查看

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 mysql> show status like 'innodb_row_lock%' ; + -------------------------------+--------+ | variable_name | value | + -------------------------------+--------+ | innodb_row_lock_current_waits | 0 | | innodb_row_lock_time | 284387 | | innodb_row_lock_time_avg | 21875 | | innodb_row_lock_time_max | 51003 | | innodb_row_lock_waits | 13 | + -------------------------------+--------+ 5 rows in set (0.00 sec) innodb_row_lock_current_waits: 当前正在等待锁定的数量 innodb_row_lock_time: 从系统启动到现在锁定总时间长度 innodb_row_lock_time_avg:每次等待所花平均时长 innodb_row_lock_time_max:从系统启动到现在等待最长的一次所花的时间 innodb_row_lock_waits: 系统启动后到现在总共等待的次数