怎样进行MySQL的学习
导读:本文共28807字符,通常情况下阅读需要96分钟。同时您也可以点击右侧朗读,来听本文内容。按键盘←(左) →(右) 方向键可以翻页。
摘要: MyISAM和InnoDB对比MyISAMInnoDB主外键不支持支持事务不支持支持行表锁表锁,操作时即使操作一条记录也会锁住一整张表,不适合高并发的操作行锁,操作时只锁住某一行,不会影响到其他行,适合高并发缓存只缓存索引,不缓存其他数据缓存索引和真实数据,对内存要求较高,而且内存大小对性能有影响表空间小大关注点性能事务默认安装YY性能下降SQL慢的原因:查询语... ...
目录
(为您整理了一些要点),点击可以直达。MyISAM和InnoDB
性能下降SQL慢的原因:
查询语句写的差
索引失效
关联查询太多join (设计缺陷或不得已的需求)
服务器调优及各个参数设置(缓冲,线程参数)
Mysql执行顺序
手写
机读先从from开始
SQLJoin
a表
mysql>select*fromtbl_dept;+----+----------+--------+|id|deptName|locAdd|+----+----------+--------+|1|RD|11||2|HR|12||3|MK|13||4|MIS|14||5|FD|15|+----+----------+--------+5rowsinset(0.00sec)
b表
+----+------+--------+|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|+----+------+--------+8rowsinset(0.00sec)
mysql不支持全连接
使用以下方式可以实现全连接
mysql>select*fromtbl_deptarightjointbl_empbona.id=b.deptId->union->select*fromtbl_deptaleftjointbl_empbona.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|+------+----------+--------+------+------+--------+9rowsinset(0.00sec)
a的独有和b的独有
mysql>select*fromtbl_deptaleftjointbl_empbona.id=b.deptIdwhereb.idisnull->union->select*fromtbl_deptarightjointbl_empbona.id=b.deptIdwherea.idisnull;+------+----------+--------+------+------+--------+|id|deptName|locAdd|id|name|deptId|+------+----------+--------+------+------+--------+|5|FD|15|NULL|NULL|NULL||NULL|NULL|NULL|8|s9|51|+------+----------+--------+------+------+--------+2rowsinset(0.01sec)
索引
索引的定义:
索引是帮助SQL高效获取数据的数据结构,索引的本质:数据结构
可以简单的理解为:排好序的快速查找数据结构
在数据之外,数据库系统还维护着满足特定查找算法的数据结构,这些数据结构以某种方式(引用)指向数据,这样就可以在这些数据结构上实现高级查找算法。这种数据结构,就是索引,下图就是一种示例:
一般来说索引也很大,因此索引往往以索引文件的方式存储在磁盘上
我们平常所说的索引,如果没有特别指明,一般都是指B树(多路搜索树,不一定是二叉的)结构组织的索引,
其中聚集索引,次要索引,复合索引,前缀索引,唯一索引默认都是使用B+树索引,统称索引,当然除了B+树这种类型的索引之外,还有哈希索引。
索引的优劣
1.优势
类似大学图书馆图书编号建索引,提高了数据检索的效率,降低数据库的IO成本
通过索引对数据进行排序,降低数据排序的成本,降低了CPU的消耗
2.劣势
实际上索引也是一张表,该表保存了主键与存在索引的字段,并指向实体表的记录,所以索引列也是占用空间的
虽然索引大大提高了查询速度,但是会降低更新表的速度,比如 update,insert,delete操作,因为更新表时,MySQL不仅要数据也要保存索引文件每次更新添加了索引的字段,都会调整因为更新所带来的键值变化后的索引信息
索引只是提高效率的一个因素,在一个大数据量的表上,需要建立最为优秀的索引或者写优秀的查询语句,而不是加了索引就能提高效率
索引分类
单值索引
唯一索引
复合索引
基本语法:
创建
create[unique]indexindexNameonmytable(cloumnname(length));
altermytableadd[unique]index[indexName]on(columnname(length));
删除
dropindex[indexName]onmytable
查看
showindexfromtable_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操作,在找到第一匹配的元组后立即停止查找同样值的操作
案例
索引优化
单表优化
CREATETABLEIFNOTEXISTS`article`(`id`INT(10)UNSIGNEDNOTNULLPRIMARYKEYAUTO_INCREMENT,`author_id`INT(10)UNSIGNEDNOTNULL,`category_id`INT(10)UNSIGNEDNOTNULL,`views`INT(10)UNSIGNEDNOTNULL,`comments`INT(10)UNSIGNEDNOTNULL,`title`VARBINARY(255)NOTNULL,`content`TEXTNOTNULL);INSERTINTO`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*FROMARTICLE;mysql>selectid,author_idfromarticlewherecategory_id=1andcomments>1orderbyviewsdesclimit1;+----+-----------+|id|author_id|+----+-----------+|3|1|+----+-----------+1rowinset(0.00sec)mysql>explainselectauthor_idfromarticlewherecategory_id=1andcomments>1orderbyviewsdescliimit1;+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+|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|Usingwhere;Usingfilesort|+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-----------------------------+1rowinset,1warning(0.00sec)
可以看出虽然查询出来了 但是 type是all,Extra里面出现了using filesort证明查询效率很低
需要优化
建立索引
createindexidx_article_ccvonarticle(category_id,comments,views);
查询
mysql>explainselectauthor_idfromarticlewherecategory_id=1andcomments>1orderbyviewsdesclimit1;+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+---------------------------------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+---------------------------------------+|1|SIMPLE|article|NULL|range|inx_article_ccv|inx_article_ccv|8|NULL|1|100.00|Usingindexcondition;Usingfilesort|+----+-------------+---------+------------+-------+-----------------+-----------------+---------+------+------+----------+---------------------------------------+1rowinset,1warning(0.00sec)
这里发现type 变为了 range 查询全表变为了 范围查询 优化了一点
但是 extra 仍然 有 using filesort 证明 索引优化并不成功
所以我们删除索引
dropindexidx_article_ccvonarticle;
建立新的索引,排除掉range
createindexidx_article_cvonarticle(category_id,views);mysql>explainselectauthor_idfromarticlewherecategory_id=1andcomments>1orderbyviewsdesclimit1;+----+-------------+---------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+---------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+|1|SIMPLE|article|NULL|ref|idx_article_cv|idx_article_cv|4|const|2|33.33|Usingwhere|+----+-------------+---------+------------+------+----------------+----------------+---------+-------+------+----------+-------------+1rowinset,1warning(0.00sec)这时候会发现优化成功type变为了refextra变为了usingwhere在这次实验中我又加入了一次试验发现当建立索引时comments放在最后也是可行的mysql>createindexidx_article_cvconarticle(category_id,views,comments);QueryOK,0rowsaffected(0.02sec)Records:0Duplicates:0Warnings:0mysql>explainselectauthor_idfromarticlewherecategory_id=1andcomments>1orderbyviewsdesclimit1;+----+-------------+---------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+---------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------------+|1|SIMPLE|article|NULL|ref|idx_article_cvc|idx_article_cvc|4|const|2|33.33|Usingwhere|+----+-------------+---------+------------+------+-----------------+-----------------+---------+-------+------+----------+-------------+1rowinset,1warning(0.00sec)
这时候会发现 优化成功 type 变为了ref extra变为了 using where
在这次实验中我又加入了一次试验 发现当建立索引时comments放在最后也是可行的
这里发现了 type仍然是ref,extra也是usingwhere,而只是把索引建立的位置换了一换,把范围查询的字段挪到了最后!!!!
双表优化
CREATETABLEIFNOTEXISTS`class`(`id`INT(10)UNSIGNEDNOTNULLPRIMARYKEYAUTO_INCREMENT,`card`INT(10)UNSIGNEDNOTNULL);CREATETABLEIFNOTEXISTS`book`(`bookid`INT(10)UNSIGNEDNOTNULLPRIMARYKEYAUTO_INCREMENT,`card`INT(10)UNSIGNEDNOTNULL);INSERTINTOclass(card)VALUES(FLOOR(1+(RAND()*20)));INSERTINTOclass(card)VALUES(FLOOR(1+(RAND()*20)));INSERTINTOclass(card)VALUES(FLOOR(1+(RAND()*20)));INSERTINTOclass(card)VALUES(FLOOR(1+(RAND()*20)));INSERTINTOclass(card)VALUES(FLOOR(1+(RAND()*20)));INSERTINTOclass(card)VALUES(FLOOR(1+(RAND()*20)));INSERTINTOclass(card)VALUES(FLOOR(1+(RAND()*20)));INSERTINTOclass(card)VALUES(FLOOR(1+(RAND()*20)));INSERTINTOclass(card)VALUES(FLOOR(1+(RAND()*20)));INSERTINTOclass(card)VALUES(FLOOR(1+(RAND()*20)));INSERTINTOclass(card)VALUES(FLOOR(1+(RAND()*20)));INSERTINTOclass(card)VALUES(FLOOR(1+(RAND()*20)));INSERTINTOclass(card)VALUES(FLOOR(1+(RAND()*20)));INSERTINTOclass(card)VALUES(FLOOR(1+(RAND()*20)));INSERTINTOclass(card)VALUES(FLOOR(1+(RAND()*20)));INSERTINTOclass(card)VALUES(FLOOR(1+(RAND()*20)));INSERTINTOclass(card)VALUES(FLOOR(1+(RAND()*20)));INSERTINTOclass(card)VALUES(FLOOR(1+(RAND()*20)));INSERTINTOclass(card)VALUES(FLOOR(1+(RAND()*20)));INSERTINTOclass(card)VALUES(FLOOR(1+(RAND()*20)));INSERTINTObook(card)VALUES(FLOOR(1+(RAND()*20)));INSERTINTObook(card)VALUES(FLOOR(1+(RAND()*20)));INSERTINTObook(card)VALUES(FLOOR(1+(RAND()*20)));INSERTINTObook(card)VALUES(FLOOR(1+(RAND()*20)));INSERTINTObook(card)VALUES(FLOOR(1+(RAND()*20)));INSERTINTObook(card)VALUES(FLOOR(1+(RAND()*20)));INSERTINTObook(card)VALUES(FLOOR(1+(RAND()*20)));INSERTINTObook(card)VALUES(FLOOR(1+(RAND()*20)));INSERTINTObook(card)VALUES(FLOOR(1+(RAND()*20)));INSERTINTObook(card)VALUES(FLOOR(1+(RAND()*20)));INSERTINTObook(card)VALUES(FLOOR(1+(RAND()*20)));INSERTINTObook(card)VALUES(FLOOR(1+(RAND()*20)));INSERTINTObook(card)VALUES(FLOOR(1+(RAND()*20)));INSERTINTObook(card)VALUES(FLOOR(1+(RAND()*20)));INSERTINTObook(card)VALUES(FLOOR(1+(RAND()*20)));INSERTINTObook(card)VALUES(FLOOR(1+(RAND()*20)));INSERTINTObook(card)VALUES(FLOOR(1+(RAND()*20)));INSERTINTObook(card)VALUES(FLOOR(1+(RAND()*20)));INSERTINTObook(card)VALUES(FLOOR(1+(RAND()*20)));INSERTINTObook(card)VALUES(FLOOR(1+(RAND()*20)));mysql>createindexYonbook(card);explainselect*frombookleftjoinclassonbook.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|Usingindex||1|SIMPLE|class|NULL|ALL|NULL|NULL|NULL|NULL|20|100.00|Usingwhere;Usingjoinbuffer(BlockNestedLoop)|+----+-------------+-------+------------+-------+---------------+------+---------+------+------+----------+----------------------------------------------------+2rowsinset,1warning(0.00sec)
会发现并无多大区别 还是全表查询 这是因为俩表查询左连接把左表必须全查询 这时候只有对右表建立索引才有用
相反的右链接必须对左表建立索引才有用
对右表建立索引
createindexYonclass;explainselect*frombookleftjoinclassonbook.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|Usingindex||1|SIMPLE|class|NULL|ref|Y|Y|4|db01.book.card|1|100.00|Usingindex|+----+-------------+-------+------------+-------+---------------+------+---------+----------------+------+----------+-------------+2rowsinset,1warning(0.00sec)
会发现 右表只查询了一次。。type为ref
三表优化
CREATETABLEIFNOTEXISTS`phone`(`phoneid`INT(10)UNSIGNEDNOTNULLPRIMARYKEYAUTO_INCREMENT,`card`INT(10)UNSIGNEDNOTNULL)ENGINE=INNODB;INSERTINTOphone(card)VALUES(FLOOR(1+(RAND()*20)));INSERTINTOphone(card)VALUES(FLOOR(1+(RAND()*20)));INSERTINTOphone(card)VALUES(FLOOR(1+(RAND()*20)));INSERTINTOphone(card)VALUES(FLOOR(1+(RAND()*20)));INSERTINTOphone(card)VALUES(FLOOR(1+(RAND()*20)));INSERTINTOphone(card)VALUES(FLOOR(1+(RAND()*20)));INSERTINTOphone(card)VALUES(FLOOR(1+(RAND()*20)));INSERTINTOphone(card)VALUES(FLOOR(1+(RAND()*20)));INSERTINTOphone(card)VALUES(FLOOR(1+(RAND()*20)));INSERTINTOphone(card)VALUES(FLOOR(1+(RAND()*20)));INSERTINTOphone(card)VALUES(FLOOR(1+(RAND()*20)));INSERTINTOphone(card)VALUES(FLOOR(1+(RAND()*20)));INSERTINTOphone(card)VALUES(FLOOR(1+(RAND()*20)));INSERTINTOphone(card)VALUES(FLOOR(1+(RAND()*20)));INSERTINTOphone(card)VALUES(FLOOR(1+(RAND()*20)));INSERTINTOphone(card)VALUES(FLOOR(1+(RAND()*20)));INSERTINTOphone(card)VALUES(FLOOR(1+(RAND()*20)));INSERTINTOphone(card)VALUES(FLOOR(1+(RAND()*20)));INSERTINTOphone(card)VALUES(FLOOR(1+(RAND()*20)));INSERTINTOphone(card)VALUES(FLOOR(1+(RAND()*20)));
先删除所有索引
dropindexYonbook;dropindexYonclass;explainselect*fromclassleftjoinbookonclass.card=book.cardleftjoinphoneonbook.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|ALL|NULL|NULL|NULL|NULL|20|100.00|Usingwhere;Usingjoinbuffer(BlockNestedLoop)||1|SIMPLE|phone|NULL|ALL|NULL|NULL|NULL|NULL|20|100.00|Usingwhere;Usingjoinbuffer(BlockNestedLoop)|+----+-------------+-------+------------+------+---------------+------+---------+------+------+----------+----------------------------------------------------+3rowsinset,1warning(0.00sec)
建立索引
createindexyonbook(card);createindexzonphone(card);explainselect*fromclassleftjoinbookonclass.card=book.cardleftjoinphoneonbook.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|Usingindex||1|SIMPLE|phone|NULL|ref|z|z|4|db01.book.card|1|100.00|Usingindex|+----+-------------+-------+------------+------+---------------+------+---------+-----------------+------+----------+-------------+3rowsinset,1warning(0.00sec)
会发现索引建立的非常成功。。 但是left join 最左表必须全部查询
建立索引
createindexxonclass(card);explainselect*fromclassleftjoinbookonclass.card=book.cardleftjoinphoneonbook.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|Usingindex||1|SIMPLE|book|NULL|ref|y|y|4|db01.class.card|1|100.00|Usingindex||1|SIMPLE|phone|NULL|ref|z|z|4|db01.book.card|1|100.00|Usingindex|+----+-------------+-------+------------+-------+---------------+------+---------+-----------------+------+----------+-------------+3rowsinset,1warning(0.00sec)
结果仍然一样
建立表
CREATETABLEstaffs(idINTPRIMARYKEYAUTO_INCREMENT,`name`VARCHAR(24)NOTNULLDEFAULT''COMMENT'姓名',`age`INTNOTNULLDEFAULT0COMMENT'年龄',`pos`VARCHAR(20)NOTNULLDEFAULT''COMMENT'职位',`add_time`TIMESTAMPNOTNULLDEFAULTCURRENT_TIMESTAMPCOMMENT'入职时间')CHARSETutf8COMMENT'员工记录表';INSERTINTOstaffs(`name`,`age`,`pos`,`add_time`)VALUES('z3',22,'manager',NOW());INSERTINTOstaffs(`name`,`age`,`pos`,`add_time`)VALUES('July',23,'dev',NOW());INSERTINTOstaffs(`name`,`age`,`pos`,`add_time`)VALUES('2000',23,'dev',NOW());建立索引ALTERTABLEstaffsADDINDEXindex_staffs_nameAgePos(`name`,`age`,`pos`);
索引口诀
1.带头大哥不能死,中间兄弟不能断:当建立复合索引时,必须带上头索引,不能跳过中间索引直接使用后面的索引,使用后面的索引必须加上中间的索引(可以先使用后面的索引再使用中间的索引,但是不能直接使用后面的索引而跳过中间索引)(针对where)
可以从上图看出 跳过name的都用不了索引
mysql>explainselect*fromstaffswherename='july';+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+|1|SIMPLE|staffs|NULL|ref|index_staffs_nameAgePos|index_staffs_nameAgePos|74|const|1|100.00|NULL|+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-------+1rowinset,1warning(0.00sec)mysql>explainselect*fromstaffswherename='july'andpos='dev';+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-----------------------+|id|select_type|table|partitions|type|possible_keys|key|key_len|ref|rows|filtered|Extra|+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-----------------------+|1|SIMPLE|staffs|NULL|ref|index_staffs_nameAgePos|index_staffs_nameAgePos|74|const|1|33.33|Usingindexcondition|+----+-------------+--------+------------+------+-------------------------+-------------------------+---------+-------+------+----------+-----------------------+1rowinset,1warning(0.00sec)
可以从语句中看出跳过中间的索引后 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全没使用
假设index(a,b,c, d)
createtabletest03(idintprimarykeynotnullauto_increment,aint(10),bint(10),cint(10),dint(10),insertintotest03(a,b,c,d)values(3,4,5,6);insertintotest03(a,b,c,d)values(3,4,5,6);insertintotest03(a,b,c,d)values(3,4,5,6);insertintotest03(a,b,c,d)values(3,4,5,6);createindexidx_test03_abcdontest03(a,b,c,d);
###
group by 更严重group by先分组再排序 把order by换为 group by 甚至会产生using temporary,与order by差不多,但是更严重 而且与group by产生的效果差不多就不做演示了
Order By 索引优化
exsites
selecta.*fromAawhereexists(select1fromBbwherea.id=b.id)以上查询使用了exists语句,exists()会执行A.length次,它并不缓存exists()结果集,因为exists()结果集的内容并不重要,重要的是结果集中是否有记录,如果有则返回true,没有则返回false.它的查询过程类似于以下过程ListresultSet=[];ArrayA=(select*fromA)for(inti=0;i<A.length;i++){if(exists(A[i].id){//执行select1fromBbwhereb.id=a.id是否有记录返回resultSet.add(A[i]);}}returnresultSet;当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慢查询日志命令
showVARIABLESlike'%slow_query_log%';
显示是否开启mysql慢查询日志
setglobalslow_query_log=0;
关闭mysql慢查询日志
setglobalslow_query_log=1;
开启mysql慢查询日志
showVARIABLESlike'%long_query_time%';
显示超过多长时间即为 慢查询
setgloballong_quert_time=10;
修改慢查询时间为10秒,当查询语句时间超过10秒即为慢查询
showglobalstatuslike'%Slow_queries%';
显示一共有几条慢查询语句
[root@iZ0jlh2zn42cgftmrf6p6sZdata]#catmysql-slow.log
linux查询慢sql
函数操作批量插入数据
CREATETABLEdept(idINTUNSIGNEDPRIMARYKEYAUTO_INCREMENT,deptnoMEDIUMINTUNSIGNEDNOTNULLDEFAULT0,dnameVARCHAR(20)NOTNULLDEFAULT'',locVARCHAR(13)NOTNULLDEFAULT'')ENGINE=INNODBDEFAULTCHARSET=GBK;CREATETABLEemp(idINTUNSIGNEDPRIMARYKEYAUTO_INCREMENT,empnoMEDIUMINTUNSIGNEDNOTNULLDEFAULT0,#编号ennameVARCHAR(20)NOTNULLDEFAULT'',#名字jobVARCHAR(9)NOTNULLDEFAULT'',#工作mgrMEDIUMINTUNSIGNEDNOTNULLDEFAULT0,#上级编号hiredateDATENOTNULL,#入职时间salDECIMAL(7,2)NOTNULL,#薪水commDECIMAL(7,2)NOTNULL,#红利deptnoMEDIUMINTUNSIGNEDNOTNULLDEFAULT0#部门编号)ENGINE=INNODBDEFAULTCHARSET=GBK;
showvariableslike'log_bin_trust_function_creators';setgloballog_bin_trust_function_creators=1;
创建函数:随机产生部门编号 随机产生字符串
DELIMITER $$
是因为sql都是;进行结尾但是创建函数过程要多次使用;所以改变sql执行结束的条件为输入$$,相当于代替了分号' ;'
//定义函数1DELIMITER$$CREATEFUNCTIONrand_string(nINT)RETURNSVARCHAR(255)BEGINDECLAREchars_setVARCHAR(100)DEFAULT'abcdefghigklmnopqrstuvwxyzABCDEFGHIGKLMNOPQRSTUVWXYZ';DECLAREreturn_strVARCHAR(255)DEFAULT'';DECLAREiINTDEFAULT0;WHILEi<nDOSETreturn_str=CONCAT(return_str,SUBSTRING(chars_set,FLOOR(1+RAND()*52),1));SETi=i+1;ENDWHILE;RETURNreturn_str;END$$//定义函数2DELIMITER$$CREATEFUNCTIONrand_num()RETURNSINT(5)BEGINDECLAREiINTDEFAULT0;SETi=FLOOR(100+RAND()*10);RETURNi;END$$//定义存储过程1DELIMITER$$CREATEPROCEDUREinsert_emp(INstartINT(10),INmax_numINT(10))BEGINDECLAREiINTDEFAULT0;SETautocommit=0;REPEATSETi=i+1;INSERTINTOemp(empno,enname,job,mgr,hiredate,sal,comm,deptno)VALUES((start+i),rand_string(6),'SALESMAN',0001,CURDATE(),2000,400,rand_num());UNTILi=max_numENDREPEAT;COMMIT;END$$//定义存储过程2DELIMITER$$CREATEPROCEDUREinsert_dept(INstartINT(10),INmax_numINT(10))BEGINDECLAREiINTDEFAULT0;SETautocommit=0;REPEATSETi=i+1;INSERTINTOdept(deptno,dname,loc)VALUES((start+i),rand_string(10),rand_string(8));UNTILi=max_numENDREPEAT;COMMIT;END$$//开始插入数据DELIMITER;callinsert_dept(100,10);callinsert_emp(100001,500000);showProfile分析sqlmysql>showvariableslike'profiling';+---------------+-------+|Variable_name|Value|+---------------+-------+|profiling|OFF|+---------------+-------+1rowinset(0.00sec)mysql>setprofiling=on;QueryOK,0rowsaffected,1warning(0.00sec)mysql>showvariableslike'profiling';+---------------+-------+|Variable_name|Value|+---------------+-------+|profiling|ON|+---------------+-------+1rowinset(0.01sec)————————————————
随便写几条插入语句‘
显示查询操作语句的速度
mysql>showprofiles;+----------+------------+----------------------------------------------------------------+|Query_ID|Duration|Query|+----------+------------+----------------------------------------------------------------+|1|0.00125325|showvariableslike'profiling'||2|0.00018850|select*fromdept||3|0.00016825|select*fromtb1_empeinnerjointbl_deptdone.deptId=d.id||4|0.00023900|showtables||5|0.00031125|select*fromtbl_empeinnerjointbl_deptdone.deptId=d.id||6|0.00024775|select*fromtbl_empeinnerjointbl_deptdone.deptId=d.id||7|0.00023725|select*fromtbl_empeinnerjointbl_deptdone.deptId=d.id||8|0.00023825|select*fromtbl_empeleftjointbl_deptdone.deptId=d.id||9|0.35058075|select*fromempgroupbyid%10limit15000||10|0.35542250|select*fromempgroupbyid%10limit15000||11|0.00024550|select*fromtbl_empeleftjointbl_deptdone.deptId=d.id||12|0.36441850|select*fromempgroupbyid%20orderby5|+----------+------------+----------------------------------------------------------------+12rowsinset,1warning(0.00sec)
显示查询过程 sql生命周期
mysql>showprofilecpu,blockioforquery3;+----------------------+----------+----------+------------+--------------+---------------+|Status|Duration|CPU_user|CPU_system|Block_ops_in|Block_ops_out|+----------------------+----------+----------+------------+--------------+---------------+|starting|0.000062|0.000040|0.000021|0|0||checkingpermissions|0.000004|0.000003|0.000001|0|0||checkingpermissions|0.000015|0.000006|0.000003|0|0||Openingtables|0.000059|0.000039|0.000020|0|0||queryend|0.000004|0.000002|0.000001|0|0||closingtables|0.000002|0.000001|0.000000|0|0||freeingitems|0.000014|0.000010|0.000005|0|0||cleaningup|0.000009|0.000006|0.000003|0|0|+----------------------+----------+----------+------------+--------------+---------------+8rowsinset,1warning(0.00sec)mysql>showprofilecpu,blockioforquery12;+----------------------+----------+----------+------------+--------------+---------------+|Status|Duration|CPU_user|CPU_system|Block_ops_in|Block_ops_out|+----------------------+----------+----------+------------+--------------+---------------+|starting|0.000063|0.000042|0.000021|0|0||checkingpermissions|0.000006|0.000003|0.000002|0|0||Openingtables|0.000013|0.000009|0.000004|0|0||init|0.000028|0.000017|0.000008|0|0||Systemlock|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||Creatingtmptable|0.000028|0.000018|0.000009|0|0||Sortingresult|0.000003|0.000002|0.000001|0|0||executing|0.000002|0.000002|0.000001|0|0||Sendingdata|0.364132|0.360529|0.002426|0|0||Creatingsortindex|0.000053|0.000034|0.000017|0|0||end|0.000004|0.000002|0.000002|0|0||queryend|0.000007|0.000005|0.000002|0|0||removingtmptable|0.000005|0.000003|0.000002|0|0||queryend|0.000003|0.000002|0.000001|0|0||closingtables|0.000006|0.000004|0.000002|0|0||freeingitems|0.000023|0.000016|0.000007|0|0||cleaningup|0.000012|0.000007|0.000004|0|0|+----------------------+----------+----------+------------+--------------+---------------+20rowsinset,1warning(0.00sec)
如果出现以上这四个 中的任何一个就需要 优化查询语句
全局查询日志
setglobalgeneral_log=1;setgloballog_output='TABLE';
此后你编写的sql语句将会记录到mysql库里的general_log表,可以用下面的命令查看
select*frommysql.general_log;mysql>select*frommysql.general_log;+----------------------------+---------------------------+-----------+-----------+--------------+---------------------------------+|event_time|user_host|thread_id|server_id|command_type|argument|+----------------------------+---------------------------+-----------+-----------+--------------+---------------------------------+|2021-12-0611:53:53.457242|root[root]@localhost[]|68|1|Query|select*frommysql.general_log|+----------------------------+---------------------------+-----------+-----------+--------------+---------------------------------+1rowinset(0.00sec)
Mysql锁
读锁(共享锁):针对同一份数据,多个读操作可以同时进行而不会互相影响
写锁(排它锁):当前写操作没有完成时,它会阻断其他写锁和读锁
行锁:偏向InnoDB引擎,开销大,加锁慢,会出现死锁:锁定粒度最小,发生锁冲突的概率最低,并发量高
表锁:偏向myisam引擎,开销小,加锁快;锁定粒度大,发生锁冲突的概率最高,并发度最低
在下面进行表锁的测试
usebig_data;createtablemylock(idintnotnullprimarykeyauto_increment,namevarchar(20)default'')enginemyisam;insertintomylock(name)values('a');insertintomylock(name)values('b');insertintomylock(name)values('c');insertintomylock(name)values('d');insertintomylock(name)values('e');select*frommylock;
锁命令
locktablemylockread,bookwrite;##读锁锁mylock写锁锁bookshowopentables;##显示哪些表被加锁了unlocktables;##取消锁
表锁:读锁
##添加读锁后不可修改mysql>locktablemylockread;##1QueryOK,0rowsaffected(0.00sec)mysql>select*frommylock;##1+----+------+|id|name|+----+------+|1|a||2|b||3|c||4|d||5|e|+----+------+5rowsinset(0.00sec)mysql>updatemylocksetname='a2'whereid=1;##1ERROR1099(HY000):Table'mylock'waslockedwithaREADlockandcan'tbeupdated##改不了当前读锁锁住的表##读不了其他表mysql>select*frombook;##1ERROR1100(HY000):Table'book'wasnotlockedwithLOCKTABLES
为了区分两个命令 把1当作原有的mysql命令终端上的操作,2当作新建的mysql终端
新建一个mysql终端命令操作
##新建一个mysql终端命令操作mysql>updatemylocksetname='a3'whereid=1;##2
发现会出现阻塞操作
在原有的mysql命令终端上取消锁
unlocktables;##1QueryOK,1rowaffected(2min1.46sec)##2Rowsmatched:1Changed:1Warnings: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释放锁
表锁:写锁
mysql>locktablemylockwrite;QueryOK,0rowsaffected(0.00sec)给当前sessionmylock表加上写锁mysql>updatemylocksetname='a4'whereid=1;QueryOK,1rowaffected(0.00sec)Rowsmatched:1Changed:1Warnings:0mysql>select*frommylock;+----+------+|id|name|+----+------+|1|a4||2|b||3|c||4|d||5|e|+----+------+mysql>select*frombook;ERROR1100(HY000):Table'book'wasnotlockedwithLOCKTABLES
会发现无法操其他表但是可以操作加上锁的表
再开启一个新的客户端测试被锁住的表
mysql>select*frommylock;5rowsinset(2min30.92sec)
发现新的客户端上操作(增删改查)被写锁锁住的表会陷入阻塞状态
作
分析表锁定
mysql>showstatuslike'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|+----------------------------+-------+5rowsinset(0.00sec)
行锁
InnoDB 的行锁模式
InnoDB 实现了以下两种类型的行锁。
共享锁(S):又称为读锁,简称S锁,共享锁就是多个事务对于同一数据可以共享一把锁,都能访问到数据,但是只能读不能修改。
排他锁(X):又称为写锁,简称X锁,排他锁就是不能与其他锁并存,如一个事务获取了一个数据行的排他锁,其他事务就不能再获取该行的其他锁,包括共享锁和排他锁,但是获取排他锁的事务是可以对数据就行读取和修改。
对于UPDATE、DELETE和INSERT语句,InnoDB会自动给涉及数据集加排他锁(X);
对于普通SELECT语句,InnoDB不会加任何锁;
可以通过以下语句显示给记录集加共享锁或排他锁 。
共享锁(S):SELECT*FROMtable_nameWHERE...LOCKINSHAREMODE排他锁(X):SELECT*FROMtable_nameWHERE...FORUPDATE
由于行锁支持事务,在此复习一下
事务
事务是一组由SQL语句组成的逻辑处理单元,事务具有四个属性:ACID
原子性(Atomicity):事务是一个原子操作单元,其对数据的操作要么全部执行,要么全不执行。
一致性(Consistent):在事务开始和完成时,数据都必须保持一致状态。这意味着所有相关的数据都必须应用于事务的修改,以保持数据的完整性;事务结束时,所有的内部数据结构(如B树索引或双向链表)也都必须是正确的。
隔离性(Isolation):数据库提供一定的隔离机制,保证事务在不受外部并发操作影响的"独立"环境执行。这意味着事务处理过程的中间状态对外部都是不可见的,反之亦然。
持久性(Durable):事务完成后,它对数据的操作是永久性的,哪怕出现系统故障也能维持
并发事务带来的问题:
更新丢失,脏读,不可重复读,幻读
并发事务处理带来的问题
事务隔离级别
为了解决上述提到的事务并发问题,数据库提供一定的事务隔离机制来解决这个问题。数据库的事务隔离越严格,并发副作用越小,但付出的代价也就越大,因为事务隔离实质上就是使用事务在一定程度上“串行化” 进行,这显然与“并发” 是矛盾的。
数据库的隔离级别有4个,由低到高依次为Read uncommitted、Read committed、Repeatable read、Serializable,这四个级别可以逐个解决脏写、脏读、不可重复读、幻读这几类问题。
备注 : √ 代表可能出现 , × 代表不会出现
。
Mysql 的数据库的默认隔离级别为 Repeatable read , 查看方式:
showvariableslike'tx_isolation';
行锁测试建表, 案例准备工作
createtabletest_innodb_lock(idint(11),namevarchar(16),sexvarchar(1))engine=innodbdefaultcharset=utf8;insertintotest_innodb_lockvalues(1,'100','1');insertintotest_innodb_lockvalues(3,'3','1');insertintotest_innodb_lockvalues(4,'400','0');insertintotest_innodb_lockvalues(5,'500','1');insertintotest_innodb_lockvalues(6,'600','0');insertintotest_innodb_lockvalues(7,'700','0');insertintotest_innodb_lockvalues(8,'800','1');insertintotest_innodb_lockvalues(9,'900','1');insertintotest_innodb_lockvalues(1,'200','0');createindexidx_test_innodb_lock_idontest_innodb_lock(id);createindexidx_test_innodb_lock_nameontest_innodb_lock(name);
行锁测试
还是开俩个终端测试,关闭事自动事务提交,因为自动事务提交会自动加锁释放锁;
mysql>setautocommit=0;mysql>setautocommit=0;
会发现查询无影响
对左边进行更新操作
mysql>updatetest_innodb_locksetname='100'whereid=3;QueryOK,0rowsaffected(0.00sec)Rowsmatched:1Changed:0Warnings:0
对左边进行更新操作
对右边进行更新操作后停止操作
mysql>updatetest_innodb_locksetname='340'whereid=3;ERROR1205(HY000):Lockwaittimeoutexceeded;tryrestartingtransaction
会发现进行阻塞了 直到锁释放或者提交事务(commit)为止
对于innodb引擎来说,对某一行数据进行DML(增删改)操作会对操作的那行添加排它锁
别的事务就不能执行这行语句了,但是可以操作其他行的数据
无索引行锁会升级成表锁:如果不通过索引条件检索数据,那么innodb会对表中所有记录加锁,实际效果和表锁一样
记住进行操作时使用索引:innodb引擎索引失效时时行锁会升级为表锁
mysql>updatetest_innodb_locksetsex='2'wherename=400;QueryOK,0rowsaffected(0.00sec)Rowsmatched:2Changed:0Warnings:0
注意这里name没有加单引号 索引失效
mysql>updatetest_innodb_locksetsex='3'whereid=3;QueryOK,1rowaffected(23.20sec)Rowsmatched:1Changed:1Warnings:0
发现了对其他行操作也陷入了阻塞状态,这是没加索引导致的行锁升级为表锁
本来只对一行数据加锁 但是由于忘记给name字段加单引号导致索引失效给全表都加上了锁;
间隙锁
当我们使用范围条件而不是想等条件进行检索数据,并请求共享或排它锁,在那个范围条件中有不存在的记录,叫做间隙,innodb也会对这个间隙进行加锁,这种锁机制就叫做间隙锁
mysql>select*fromtest_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的数据
行锁征用情况查看
mysql>showstatuslike'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|+-------------------------------+--------+5rowsinset(0.00sec)Innodb_row_lock_current_waits:当前正在等待锁定的数量Innodb_row_lock_time:从系统启动到现在锁定总时间长度Innodb_row_lock_time_avg:每次等待所花平均时长Innodb_row_lock_time_max:从系统启动到现在等待最长的一次所花的时间Innodb_row_lock_waits:系统启动后到现在总共等待的次数
行锁总结
InnoDB存储引擎由于实现了行级锁定,虽然在锁定机制的实现方面带来了性能损耗可能比表锁会更高一些,但是在整体并发处理能力方面要远远由于MyISAM的表锁的。当系统并发量较高的时候,InnoDB的整体性能和MyISAM相比就会有比较明显的优势。
但是,InnoDB的行级锁同样也有其脆弱的一面,当我们使用不当的时候,可能会让InnoDB的整体性能表现不仅不能比MyISAM高,甚至可能会更差。
优化建议:
尽可能让所有数据检索都能通过索引来完成,避免无索引行锁升级为表锁。
合理设计索引,尽量缩小锁的范围
尽可能减少索引条件,及索引范围,避免间隙锁
尽量控制事务大小,减少锁定资源量和时间长度
尽可使用低级别事务隔离(但是需要业务层面满足需求)
</div> <div class="zixun-tj-product adv-bottom"></div> </div> </div> <div class="prve-next-news">
怎样进行MySQL的学习的详细内容,希望对您有所帮助,信息来源于网络。