mysql高级积累

最新推荐文章于 2024-09-26 17:51:13 发布

一个会写代码的文艺青年

最新推荐文章于 2024-09-26 17:51:13 发布

阅读量169

点赞数

分类专栏：开发工具类文章标签： mysql

本文链接：https://blog.youkuaiyun.com/qq_41908550/article/details/112014014

版权

开发工具类专栏收录该内容

3 篇文章

订阅专栏

# 查询整个数据库增删改查的次数，不加global是显示当前连接的次数，加是全局的数据
show GLOBAL STATUS LIKE 'Com_______';

#查询innodb引擎的增删改查的次数
show STATUS LIKE 'innodb_rows_%';

#查询所有连接当前主机MySQL服务的客户端信息，可以捕获到慢查询的SQL语句
show PROCESSLIST;

-- 查询结果解析：
-- id：表示执行顺序，越大越优先
-- select_type: 查询的类型，一般有简单查询、子查询、关联查询等类型。
-- type: 访问类型，const:表示通过索引一次就找到了。一般是用于主键或者唯一索引的查询，只返回一条记录。 ref:非唯一性索引， range：检索给定范围的行，走的索引。index：遍历索引树，也就是查询的内容都是索引。all：全表扫描
-- possible_keys : 可能用到的索引
-- key: 实际用到的索引
-- key_len: 索引的长度
-- rows：扫描的行数，也就是在执行查询时，实际扫描的行数，可作为查询效率的参考值，扫描的行数越少，效率越高
-- extra: useing index : 表示用了索引。using index condition：s使用了索引，但是回表查询了。
EXPLAIN SELECT * FROM test1 WHERE c1 =4;

#查询是否开启profile,是一种sql分析工具
SELECT @@profiling;

# 查询执行的SQL语句的耗时
show PROFILES;

#查询某个SQL语句具体的执行时间
show PROFILE FOR QUERY 10;

#trace 分析MySQL优化器的优化情况。

#索引优化：

-- or 条件如果任意一个条件没有通过索引查询，则整个语句的索引将会失效。
-- like 如果%加在了前面，索引就会失效，可以通过select 索引列优化，索引就会使用。
-- in 可以索引，not in 不走索引
-- 每创建一个索引，数据库就会给该索引维护一个数据结构表，当查询时，如果走索引，只会选择其中一个索引进行使用。并且，查询时，如果选择的这个索引中没有需要查询的列，则会回表查询(尽管该列也是一个索引，但不在该索引的叶子节点中)

#mysql 的内存缓存。可以帮助我们针对一些不经常更新的数据，增加查询效率。缓存会在服务重启后清空
-- 可以通过配置文件，将MySQL的查询缓存打开，查询的SQL会将结果缓存在内存中，下一次查询时，直接从缓存中获取，当数据被修改时，缓存失效。
-- SQL_CACHE SQL_NO_CACHE 可以指定SQL的查询结果是否需要进行缓存： SELECT SQL_NO_CACHE * from test1;
-- 可以通过配置文件，系统参数，修改缓存的内存大小，缓存开启的类型。
-- SQL语句不一致，无法命中缓存；使用不确定的函数时，比如now()；当数据表发生更新时，缓存会失效被删除

#MySQL的内存优化，可以通过优化每个连接占用的内存、MySQL服务占用的内存等方面进行优化。根据不同的搜索引擎，来设置不同的参数。
#MySQL的并发参数调整，来增加MySQL的并发访问量。
-- 可以通过 show VARIABLES like '参数名' 来查询对应参数的当前值
-- MAX_CONNECTIONS 最大连接数，可以通过增加改值，增加并发访问，取决于服务器的配置。默认是151
-- back_log: 积压请求栈的大小，当MySQL达到了最大连接数后，其余的链接会被存放在栈中，增加该值，可增加队列中等待的连接数，栈慢后，会直接报错
-- INNODB_lock_wait_timeout 行锁的超时时间，默认是50ms

#MySQL的锁机制

-- 对于myisam引擎来说，是表锁：读锁会阻塞写，不会阻塞读，写锁读写都会堵塞。

-- INNODB的引擎来说，是行锁：读不会加锁，写会对写的这一行加锁。测试时，可以通过关闭MySQL的自动提交 set autocommit=0;手动commit观察行锁对数据库读写的影响。
-- INNODB什么时候会变成表锁呢？：where中的查询条件如果没有走索引，或者索引失效。此时的操作，就是一个锁表的操作。innodb的行锁是通过给索引项加锁实现的，这就意味着只有通过索引条件检索数据时，innodb才使用行锁，否则使用表锁。
-- **** UPDATE test1 SET c2=2 WHERE c4 = 'c'; 如果此时，c4不是索引，或者c4导致了索引失效，那次是的update操作，会将整个表加锁。
-- 间隙锁：对于键值在条件范围内但并不存在的记录，叫做间隙。例如查询id <10 ，如果当前表中只有1-6，则7-9就是间隙。加锁时，也会对这个间隙进行加锁。
-- 例如： update test1 set name='test' where id < 4; 当在执行该条语句的时候，还未提交时，执行 insert 插入一条满足where条件，但是不存在的数据时，会阻塞，因为该数据虽然不存在，但是已经被加锁了。

#MySQL日志：

#①错误日志查看：可以通过以下指令查询错误日志的存放路径：
show VARIA BLES LIKE 'log_error%';

#②二进制日志,默认是没有开启的，需要在MySQL的配置文件中开启：

log_bin=mysqlbin mysqlbin就是生成的二进制文件名前缀

binlog_format=STATEMENT #statement的意思是记录的都是SQL语句，除了select的语句外都会记录在二进制文件中。这是主从复制的主要原理、ROW记录的所有行的数据变更（例如执行一个全表更新，statement记录的就是一个update语句，而row则会记录更新每一行数据的update语句，如果有100条数据，就会记录1000个update语句）、

默认二进制的日志文件是存在放mysql的数据目录下，/var/lib/mysql中，由于记录的是二进制的文件类型，所以查看时，需要使用mysqlbinlog命令查看二进制文件。

可以通过不同的方式，对二进制日志文件进行删除。

③查询日志，会记录mysql所有的操作日志，包括查询的日志，默认是不开启的。需要在配置文件中开启

general_log=1 1表示开启，0表示关闭

general_log_file=file_name.log 指定日志的文件名

开启后，所有指定的mysql语句都会在该文件中显示。包括用mybatis指定的SQL均会显示。是一个排错利器

④慢查询日志，记录查询效率比较低的SQL语句

可以通过控制两个参数，来决定什么样的查询被mysql认定为慢查询：mysql配置文件中：

slow_query_log=1 手动开启慢查询日志

slow_query_log_file=file_name.log 慢查询日志的文件名

long_query_time=10 超过查询时间的超时时间，默认是10秒。执行时间超过10秒，就会被日志文件记录。