mysql学习

最新推荐文章于 2024-11-01 11:00:05 发布

白纸画江湖

最新推荐文章于 2024-11-01 11:00:05 发布

阅读量199

点赞数

分类专栏： mysql 文章标签： mysql

本文链接：https://blog.csdn.net/weixin_41536380/article/details/108899467

版权

mysql 专栏收录该内容

1 篇文章 0 订阅

订阅专栏

一、mysql性能分析

1.Explain + SQL语句
执行计划包含的信息：
1.表的读取顺序
2.数据读取操作的操作类型
3.哪些索引可以使用
4.哪些索引被实际使用
5.表之间的引用
6.每张表有多少行被优化器查询

id	select_type	table	type	possible_keys	key	key_len	ref	rows	Extra
id越大优先级越高	查询类型	表名	访问类型	显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用	实际使用的索引。如果为NULL,则没有使用索引。查询中若使用了覆盖索引，则该索引仅出现在key列表中。所谓覆盖索引就是查询列表中的字段个数及顺序与复合索引匹配	索引字段的最大可能长度，并非实际使用长度	显示索引的哪一列被使用了，如果可能的话，是一个常数。即查询条件中索引列上的值	根据表统计信息及索引选用情况，大致估算出找到所需的记录所需要读取的行数	包含不适合在其它列中显示但十分重要的额外信息

id:
id相同时，表的执行顺序由上而下；不同时，id值越大，越先执行

================================================
type:
访问的类型

ALL	index	range	ref	eq_ref	const	system	NULL
全表扫描	全索引扫描，只遍历索引树	只检索给定范围的行，使用一个索引来选择行	非唯一性索引扫描	唯一性索引扫描	表示通过索引一次就找到了	表只有一行记录

从最好到最差依次是：
system>const>eq_ref>ref>range>index>ALL
一般来说，得保证查询至少到达rang级别，最好能达到ref

===================================================
key:
实际使用的索引

===================================================
rows:
被优化器查询的行数

===================================================
Extra:
十分重要的额外信息

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

二、mysql优化

基础知识：
新建索引

ALTER TABLE `article` ADD INDEX idx_article_cv(`category_id`,`views`);
create index idx_article_cv on article(category_id,views);

删除索引

DROP INDEX idx_article_cv ON article;

查看索引

show index from article;

1.单表优化：
原型

select id,author_id from article where category_id = 1 AND comments > 1 order by views desc limit 1;

调优

create index idx_article_cv on article(category_id,views);

查看执行计划，分析调优性能

explain select id,author_id from article where category_id = 1 AND comments > 1 order by views desc limit 1;

2.两表优化
1)内连接
原型

select * from class inner join book on class.card = book.card;

2)左外连接(索引加右表)
原型

select * from class left join book on class.card = book.card;

调优

 alter table `book` add index idx_book_card(`card`) ;

查看执行计划，分析调优性能

explain select * from class left join book on class.card = book.card;

3)右外连接(索引加左表)
原型

 select * from class right join book on class.card = book.card;

调优

 alter table `class ` add index idx_class _card(`card`) ;

查看执行计划，分析调优性能

 explain select * from class right join book on class.card = book.card;

3.三表优化(索引加从表)
原型

 select * from class left join book on class.card=book.card left join phone on book.card = phone.card;

调优

  alter table `phone` add index idx_phone_card(`card`);#为phone表建立索引
    alter table `book ` add index idx_book _card(`card `);#为book 表建立索引

查看执行计划，分析调优性能

 explain select * from class left join book on class.card=book.card left join phone on book.card = phone.card;

结论
1.索引最好设置在需要经常查询的字段中。
2.尽可能减少Join语句中的NestedLoop的循环总次数。
3.永远用小结果集驱动大结果集。
4.优先优化NestedLoop的内层循环。
5.保证Join语句中被驱动表上Join条件字段已经被索引。
6.当无法保证被驱动表的Join条件字段被索引且内存资源充足的情况下，不要太吝啬JoinBuffer的设置。

三、防止索引失效

创建索引idx_staffs_nameAgePos

create index idx_staffs_nameAgePos on staffs(name,age,pos);

1.全值匹配

explain select * from staffs where name = 'Tom' and age = 22 and pos = 'manager';

2.最佳左前缀法则

explain select * from staffs where age = 22 and pos = 'manager';
#age和pos都不能用上索引

explain select * from staffs where name = 'Tom' and pos = 'manager';
#name用得上索引，pos不能

3.不在索引列上做任何操作(计算、函数、自动或手动类型转换)，否则会导致索引失效而转向全表扫描
原型：

explain select * from staffs where left(name,3) = 'Tom';
#全表扫描

优化:

explain select * from staffs where name = 'Tom';

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

explain select * from staffs where name = 'Tom' and age > 21 and pos = 'manager'
#只有name 和 age用得上索引，pos不能

*5.尽量使用覆盖索引。减少select **

explain select name,age,pos from staffs where name = 'Tom' and age > 21 and pos = 'manager'
#复合索引覆盖了查询列表

6.mysql在使用不等于(!=或者<>)的时候无法使用索引会导致全表扫描

explain select * from staffs where name != 'Tom';
#全表扫描

7.is null,is not null也无法使用索引

explain select * from staffs where name is null;#失效
explain select * from staffs where name is not null;#失效

8.like以通配符开头(’%abc…’)mysql索引失效会变成全表扫描的操作

explain select * from staffs where name like 'To%';#不失效
explain select * from staffs where name like '%To';#失效
explain select * from staffs where name like '%To%';#失效

使用覆盖索引解决’%To%索引失效问题

explain select id,name,age,pos from staffs where name like '%To';#不失效

9.字符串不加单引号索引失效

explain select * from staffs where name = 2001;#失效

10.少用or,用它来连接时会索引失效

explain select * from staffs where name = 'July' or name ='Tom';#失效

11.慎用in 和 not in逻辑
原型

select id from t1 where num in(select id from t2 where id >10);#外层查询会全表扫描

优化

select id from t1,(select id from t2 where id >10) t_2 where t1.num = t_2 .id ;

12.order by的分析

     explain select * from staffs where name = 'Tom' order by age,pos;#排序用了索引
     explain select * from staffs where name = 'Tom' order by pos;#文件排序

13.group by的分析

     explain select * from staffs where name = 'Tom' group by age,pos;#分组用了索引
     explain select * from staffs where name = 'Tom' group by pos;#文件排序，临时表

在这里引用网上一个牛人的优化总结口诀供大家参考
全职匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上少计算，范围之后全失效；
LIKE百分写最右，覆盖索引不写星；

四、查询截取分析

1.查询优化
#永远小表驱动大表，即小的数据集驱动大的数据集
1.当B表的数据集小于A表的数据集时，用in优于exists

select * from A where id in(select id from B);
#等价于
for select id from B
for select * from A where A.id = B.id;

2.当B表的数据集大于A表的数据集时，用exists优于in

select * from A where exists(select 1 from B where B.id = A.id)
#等价于
for select * from A
for select * from B where B.id = A.id

#order by 关键字优化
mysql有两种排序方式：文件排序、扫描有序索引排序
create index a_b_c on 表名(a,b,c)
1.扫描有序索引排序
order by 能使用索引最左前缀
-order by a
-order by a,b
-order by a,b,c
-order by a desc,b desc,c desc
如果where使用索引的最左前缀定义为常量，则order by能使用索引
-where a = const order by b,c
-where a = const and b = const order by c
-where a = const order by b
-where a = const and b > const order by b,c
2.文件排序
-order by a asc,b desc,c desc
-where g = const order by b,c
-where a = const order by c
-where a = const order by a,d
-where a in (…) order by b,c #a不是常量
当然，文件排序有两种算法：双路排序、单路排序；
1.双路排序：扫描两次磁盘
2.单路排序：理想情况下，扫描一次磁盘；但不理想情况下，扫描多次磁盘
#针对单路排序的优化策略：
增大sort_buffer_size参数的设置、增大max_length_for_sort_data参数的设置

#group by 关键字优化
1.mysql使用group by会有两种情况产生：理想、产生文件排序、产生临时表
2.group by 实质是先排序后进行分组，遵照索引建的最佳左前缀，和group by 一致，不再举例了
3.where高于having,能写在where限定的条件就不要去having限定了
2.慢查询日志
#慢查询日志支持将日志记录写入文件
#开启慢查询日志，并设置时间

     set global slow_query_log=1;
     set global slow_query_log_file=/var/lib/mysql/xx-slow.log;
     show variables like '%slow_query_log%';
     set global long_query_time=3;
     show global variables like 'long_query_time%';

         如果需要永久有效，就需要在配置文件my.cnf里[mysqld]下增加或修改参数

        slow_query_log=1
        slow_query_log_file=/var/lib/mysql/xx-slow.log
        long_query_time=3
        log_output=FILE

#模拟耗时查询

     select sleep(4);

#查看慢查询的sql语句数量

 show global status like '%Slow_queries%';

#查看/var/lib/mysql/xx-slow.log文件，结果会出现select sleep(4);

[root@myidea ~]#tail -5 /var/lib/mysql/xx-slow.log

#借助日志分析工具mysqldumpslow

 [root@myidea ~]#mysqldumpslow --help

s:是表示按照何种方式排序
c:访问次数
l:锁定时间
r:返回记录
t:查询时间
al:平均锁定时间
ar:平均返回记录数
at:平均查询时间
t:即为返回前面多少条数据
g:后边搭配一个正则匹配模式，大小写不敏感的
1.得到返回记录集最多的10个SQL

mysqldumpslow -s r -t 10 /var/lib/mysql/xx-slow.log

2.得到访问次数最多的10个SQL

 mysqldumpslow -s c -t 10 /var/lib/mysql/xx-slow.log

3.得到按照时间排序的前10条里面含有左连接的查询语句

  mysqldumpslow -s t -t 10 -g "left join"  /var/lib/mysql/xx-slow.log

4.使用这些命令时，可以结合 | more,否则有可能出现爆屏情况

 mysqldumpslow 参数 /var/lib/mysql/xx-slow.log | more

3.批量数据脚本

 *1.建表*
 
 *2.设置参数log_bin_trust_function_creators*
 如果创建函数失败，This funcation has none of DETERMINISTIC...
 由于开启过慢查询日志，因为我们开启了bin-log,我们就必须为我们的function指定一个参数。

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

 这样子的话，如果mysql服务重启，上述参数又会消失，永久方法：

     windows 下my.ini[mysqld]加上log_bin_trust_function_creators=1
     linux下  /etc/my.cnf下my.cnf[mysqld]加上log_bin_trust_function_creators=1

 *3.创建函数，保证每条数据都不同*
  #产生随机字符串

DELIMITER $$
CREATE FUNCTION rand_string(n INT) RETURNS VARCHAR(255)
BEGIN
 DECLARE chars_str VARCHAR(100) DEFAULT 'abccdefghijklmnopqrstuvwxyzABCDEFGHIJKLMNOPQRSTUVWXYZ';
 DECLARE return_str VARCHAR(255) DEFAULT '';
 DECLARE i INT DEFAULT 0;
 WHILE i < n DO
 SET return_str = CONCAT(return_str,SUBSTRING(chars_str,FLOOR(1+RAND()*52),1));
 SET i = i + 1;
 END WHILE;
 RETURN return_str;
END $$

#产生随机数字

DELIMITER $$
CREATE FUNCTION rand_num() RETURNS INT(5)
BEGIN
 DECLARE i INT DEFAULT 0;
 SET i = FLOOR(100+RAND()*10);
 RETURN i;
END $$

 #删除

drop function rand_num;

  *4.创建存储过程*
  #创建往emp表中插入数据的存储过程

   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,ename,job,mgr,hiredate,sal,comm,deptno)VALUES((START+i),rand_string(6),'SALESMAN',00001,CURDATE(),23000,300,rand_num());
   UNTIL i = max_num
   END REPEAT;
   COMMIT;
   END $$

  #创建往dept表中插入数据的存储过程

   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 $$

  *5.调用存储过程*

   DELIMITER;
   CALL insert_emp(100000,600000);
   CALL insert_dept(100,10)

4.Show Profile
显示sql在mysql服务器里面的执行细节和生命周期情况

1.命令行开启

    set profiling = on;
    show variables like 'profiling';

2.模拟耗时查询

     select * from emp group by id%10 limit 150000;#索引失效，全表扫描
     select * from emp group by id%20 order by 5;#索引失效，全表扫描

3.查看profiles信息

    show profiles;

Query_ID	Duration	Query
查询ID	耗时	查询语句

 4.查看某条查询语句在cpu和block io方面的性能调优

     show profile cpu,block io for query Query_ID

Status	Duration	CPU_user	CPU_system	Block_ops_in	Block_ops_out
状态	耗时	用户cpu	系统cpu	读	写

当Status字段出现以下4种情况，说明sql语句问题很大,需要优化了
#converting HEAP to MyISAM
查询结果太大，内存都不够用了往磁盘上搬
#Creating tmp table
创建临时表
#Copying to tmp table on disk
把内存中临时表复制到磁盘
locked

5.全局查询日志
#配置启用

set global general_log=1;
set global log_output='TABLE';
select * from mysql.general_log;

event_time	user_host	thread_id	server_id	command_type	argument
执行时间	用户主机	线程号	服务号	语句类型	语句

#编码启用
#永远不要在生产环境开启这个功能
6.sql服务器系统调优，参数设置

五、锁机制

1.表锁与行锁
#表锁(偏读，偏向MyIsam存储引擎)
1.特点：开销小，加锁快；无死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低

lock table emp read,book write;#加锁
lock table emp read;#加读锁
lock table emp write;#加写锁
show open tables;#1为被锁定，0没有
unlock tables;#解锁
show status like 'table%';#有两个字段，Table_locks_immediate:获得锁的次数，Table_locks_waited:等待锁的次数;其中Table_locks_waited的值越大，锁争用越严重

读锁会阻塞写，但不会阻塞读；写锁会阻塞读和写
比如说:有线程A和线程B
线程A为表emp加读锁，线程B可以读表emp但不可以对emp进行写操作；
线程A为表emp加写锁，线程B对emp表既不可以读也不可以写。

#行锁(偏写，偏向Innodb存储引擎)
1.特点：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低，并发度最高
Innodb与MyIsam的最大不同：支持事务、采用了行级锁
2.事务的相关知识
(1)事务的四大属性，ACID属性如下
1)原子性:事务是一个原子操作单元，其对数据的修改，要么全部执行，要么全部不执行。
2)一致性:在事务开始和完成时，数据都必须保持一致状态。
3)隔离性:数据库系统提供一定的隔离机制，保证事务在不受外部并发操作影响的独立环境执行。
4)持久性:事务完成之后，它对于数据的修改是永久性的。

(2)并发事务处理带来的问题
1)更新丢失
事务A对数据x做了修改并且提交，事务B也对数据x做了修改并且提交，事务C也对数据x做了修改并且提交。导致事务A和事务B的修改无效
2)脏读
事务A读取了事务B已修改但尚未提交的数据，若事后事务B发生回滚，A读取的数据无效
3)不可重复读
一个事务在读取某些数据后的某个时间，再次读取以前读过的数据，却发现读出的数据已经发生了改变，或某些记录已经被删除了
4)幻读
事务A对某表的所有行做了修改，事务B往该表插入了一行记录，事务A会发现还有一行数据没有修改到，于是就产生了一种幻觉
(3)事务的隔离级别(mysql默认的是可重复读)
(1)未提交读:脏读、不可重复读、幻读
(2)已提交读:避免了脏读，但不能避免不可重复读和幻读
(3)可重复读:避免了脏读、不可重复读,但不能避免幻读
(4)可序列化:避免了脏读、不可重复读、幻读
显示当前事务的隔离级别

show variables like 'tx_isolation';

3.建表SQL

create table test_innodb_lock(a int(11),b varchar(16))engine=innodb;
insert into test_innodb_lock values(1,'b2');
insert into test_innodb_lock values(3,'3');
insert into test_innodb_lock values(4,'4000');
insert into test_innodb_lock values(5,'5000');
insert into test_innodb_lock values(6,'6000');
insert into test_innodb_lock values(7,'7000');
insert into test_innodb_lock values(8,'8000');
insert into test_innodb_lock values(9,'9000');
insert into test_innodb_lock values(1,'b1');
create index test_innodb_lock_a_ind on test_innodb_lock(a);
create index test_innodb_lock_b_ind on test_innodb_lock(b);

然后客户端A和B都设置了set autocommit = 0;手动提交事务
4.行锁定基本演示

update test_innodb_lock set b = '3000' where a = 3;#客户端A对a = 3这行记录进行了行锁定
update test_innodb_lock set b = '300000' where a = 3;#客户端B对a = 3这行记录的修改操作将阻塞
update test_innodb_lock set b = '800000' where a = 8;#客户端B对a = 8这行记录的修改操作没有阻塞

5.无索引行锁升级为表锁

update test_innodb_lock set a = 90 where b = 9000;#数据库底层做了隐形转换,b的索引失效，因此客户端A进行了表锁定
update test_innodb_lock set b = '700' where a = 7;#客户端B对a = 7这行记录的修改操作将阻塞，因为客户端A加了表锁

6.间隙锁危害

update test_innodb_lock set b = 'wer' where a > 1 and a < 8;#客户端A为该范围内的所有行加锁
insert into test_innodb_lock(a,b) values(2,'20000');#客户端B进入阻塞状态，直到客户端Acommit提交

当我们用范围条件而不是相等条件检索数据，并请求共享或排它锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙(GAP)”,Innodb也会对这个"间隙"加锁,这种锁机制就是所谓的间隙锁(next-key锁)
7.如何锁定一行

begin;#客户端A
select * from test_innodb_lock where a = 9 for update;#客户端A加了一把行锁
update test_innodb_lock set b = '9000000' where a = 9;#客户端B想修改a=9这行记录，会进入阻塞状态，直到客户端A提交事务

8.行锁分析

show status like 'innodb_row_lock%';

Innodb_row_lock_current_waits:当前正在等待锁定的数量；
Innodb_row_lock_time:从系统启动到现在锁定总时间长度；
Innodb_row_lock_time_avg:每次等待所花平均时间；
Innodb_row_lock_time_max:从系统启动到现在等待最久的一次所花时间；
Innodb_row_lock_waits:系统启动后到现在总共等待的次数；
当然比较重要的是
Innodb_row_lock_time:从系统启动到现在锁定总时间长度；
Innodb_row_lock_time_avg:每次等待所花平均时间；
Innodb_row_lock_waits:系统启动后到现在总共等待的次数；
当上面的指标值不理想(过高)时，就需要分析为什么会有如此多的等待，然后根据分析结果，着手制定优化计划
9.优化建议
1)尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁
2)合理设计索引，尽量缩小锁的范围
3)尽可能减小检索条件范围，避免间隙锁
4)尽量控制事务大小，减少锁定时间长度和资源量
5)尽可能低级别事务隔离

#页锁
开销和加锁时间介于表锁和行锁之间；会出现死锁；锁定粒度介于表锁和行锁之间，并发度一般。
#总结
Innodb存储引擎由于实现了行级锁定，虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些，但是在整体并发处理能力方面要远远优于MyIsam的表级锁定的。当系统并发量较高的时候，Innodb的整体性能和MyIsam相比就会有比较明显的优势了。
但是，Innodb的行级锁定同样也有其脆弱的一面，当我们使用不当的时候，可能会让Innodb的整体性能表现不仅不能比MyIsam高，甚至可能会更差。
六、主从复制
1.整体上来说，复制有3个步骤：
(1)master将改变记录到二进制日志(binary log)中（这些记录叫做二进制日志事件，binary log events）；
(2)slave将master的binary log events拷贝到它的中继日志(relay log)；
(3)slave重做中继日志中的事件，将改变反映它自己的数据。
下图描述了复制的过程：
在这里插入图片描述
2.MySQL复制技术有以下一些特点：
(1)数据分布 (Data distribution )
(2)负载平衡(load balancing)
(3)备份(Backups)
(4)高可用性和容错行 High availability and failover