一、SQL性能下降的原因
二、索引 排好序的快速查找数据结构
1. 索引分类
默认使用B+树索引,除B+树索引外,还有哈希索引(hash index)等
2. 索引结构
B-Tree 示意图
3. 索引的优势和劣势
4. 基本语法
创建
CREATE [UNIQUE] INDEX indexName ON mytable(column_list);
ALTER mytable ADD [UNIQUE] INDEX indexName ON (column_list);
ALTER mytable ADD PRIMARY KEY (column_list);
ALTER mytable ADD FULLTEXT indexName(column_list);
删除
DROP INDEX indexName ON mytable;
查看
SHOW INDEX FROM table_name\G;
三、explain执行计划
id select 查询的序列号,表示查询中执行select子句或操作表的顺序
select_type 查询的类型,主要是用于区别普通查询、联合查询、子查询等复杂查询
table 显示这一行数据是关于哪张表的
type 访问类型
possible_keys 可能用到的索引
key 实际使用的索引。如果为NULL,则没有使用索引。查询中若使用了覆盖索引,则该索引仅出现在Key列中。
key_len 表示索引中使用的字节数,显示为索引字段的最大可能长度,并非实际使用长度。
ref 显示索引的哪一列被使用了,如果可能的话,是一个常数。
rows 根据表统计信息及索引选用情况,大致估算出找到所需的记录所要读取的行数。
extra 包含不适合在其它列中显示但是十分重要的额外信息。
※ 覆盖索引
select的数据列只用从索引中就能够取得,不必读取数据行,MySQL可以利用索引返回select列表中的字段,而不必根据索引再次读取数据文件,换句话说,查询列要被所建的索引覆盖。
四、一般查询优化
※ Order By 排序使用索引情况
五、查询截取分析
1. 分析方案
2. 慢查询日志
使用方法
查看开启 SHOW VARIABLES LIKE '%slow_query_log%';
开启 SET GLOBAL slow_query_log=1; #针对当前数据库有效
查看阙值时间 SHOW VARIABLES LIKE 'long_query_time%';
修改 SHOW VARIABLES LIKE 'long_query_time%'; #需要重新连接或新开一个会话才能看到修改值。
set session long_query_time=1 #改变当前session变量;
查询当前系统中有多少条慢查询记录 SHOW GLOBAL STATUS LIKE '%Slow_queries%';
分析工具
mysqldumpslow
得到返回记录集最多的10个SQL
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log
得到访问次数最多的10个SQL
mysqldumpslow -s c -t 10 /var/lib/mysql/atguigu-slow.log
得到按照时间排序的前10条里面含有左连接的查询语句
mysqldumpslow -s t -t 10 -g "left join" /var/lib/mysql/atguigu-slow.log
另外建议在使用这些命令时结合 | 和more 使用 ,否则有可能出现爆屏情况
mysqldumpslow -s r -t 10 /var/lib/mysql/atguigu-slow.log | more
3. Show Profile
开启功能,默认是关闭,使用前需要开启
show variables like 'profiling';
set profiling=1;
运行SQL
select * from emp group by id%10 limit 150000;
select * from emp group by id%20 order by 5;
查看结果
show profiles;
诊断SQL
show profile cpu,block io for query n
type:
| ALL --显示所有的开销信息
| BLOCK IO --显示块IO相关开销
| CONTEXT SWITCHES --上下文切换相关开销
| CPU --显示CPU相关开销信息
| IPC --显示发送和接收相关开销信息
| MEMORY --显示内存相关开销信息
| PAGE FAULTS --显示页面错误相关开销信息
| SOURCE --显示和Source_function,Source_file,Source_line相关的开销信息
| SWAPS --显示交换次数相关开销的信息
日常开发需要注意的结论
5. 全局查询日志
配置启用
在mysql的my.cnf中,设置如下:
#开启
general_log=1
# 记录日志文件的路径
general_log_file=/path/logfile
#输出格式
log_output=FILE
编码启用
set global log_output='TABLE';
select * from mysql.general_log;
六、MySQL锁机制
1. MyISAM锁
MyISAM在执行查询语句(SELECT)前,会自动给涉及的所有表加读锁,在执行增删改操作前,会自动给涉及的表加写锁。
MySQL的表级锁有两种模式:
表共享读锁(Table Read Lock)
表独占写锁(Table Write Lock)
锁类型 他人可读 他人可写
读锁 是 否
写锁 否 否
结论:
结合上表,所以对MyISAM表进行操作,会有以下情况:
1、对MyISAM表的读操作(加读锁),不会阻塞其他进程对同一表的读请求,但会阻塞对同一表的写请求。只有当读锁释放后,才会执行其它进程的写操作。
2、对MyISAM表的写操作(加写锁),会阻塞其他进程对同一表的读和写操作,只有当写锁释放后,才会执行其它进程的读写操作。
简而言之,就是读锁会阻塞写,但是不会堵塞读。而写锁则会把读和写都堵塞
2. Innodb锁
InnoDB与MyISAM的最大不同有两点:一是支持事务(TRANSACTION);二是采用了行级锁.
事务(Transaction)及其ACID属性
事务是由一组SQL语句组成的逻辑处理单元,事务具有以下4个属性,通常简称为事务的ACID属性。
l 原子性(Atomicity):事务是一个原子操作单元,其对数据的修改,要么全都执行,要么全都不执行。
l 一致性(Consistent):在事务开始和完成时,数据都必须保持一致状态。这意味着所有相关的数据规则都必须应用于事务的修改,以保持数据的完整性;事务结束时,所有的内部数据结构(如B树索引或双向链表)也都必须是正确的。
l 隔离性(Isolation):数据库系统提供一定的隔离机制,保证事务在不受外部并发操作影响的“独立”环境执行。这意味着事务处理过程中的中间状态对外部是不可见的,反之亦然。
l 持久性(Durable):事务完成之后,它对于数据的修改是永久性的,即使出现系统故障也能够保持。
并发事务处理带来的问题
1>更新丢失(Lost Update)
当两个或多个事务选择同一行,然后基于最初选定的值更新该行时,由于每个事务都不知道其他事务的存在,就会发生丢失更新问题--最后的更新覆盖了由其他事务所做的更新。
例如,两个程序员修改同一java文件。每程序员独立地更改其副本,然后保存更改后的副本,这样就覆盖了原始文档。最后保存其更改副本的编辑人员覆盖前一个程序员所做的更改。
如果在一个程序员完成并提交事务之前,另一个程序员不能访问同一文件,则可避免此问题。
2>脏读(Dirty Reads)
一个事务正在对一条记录做修改,在这个事务完成并提交前,这条记录的数据就处于不一致状态;这时,另一个事务也来读取同一条记录,如果不加控制,第二个事务读取了这些“脏”数据,并据此做进一步的处理,就会产生未提交的数据依赖关系。这种现象被形象地叫做”脏读”。
一句话:事务A读取到了事务B已修改但尚未提交的的数据,还在这个数据基础上做了操作。此时,如果B事务回滚,A读取
的数据无效,不符合一致性要求。
3>不可重复读(Non-Repeatable Reads)
在一个事务内,多次读同一个数据。在这个事务还没有结束时,另一个事务也访问该同一数据。那么,在第一个事务的两次读数据之间。由于第二个事务的修改,那么第一个事务读到的数据可能不一样,这样就发生了在一个事务内两次读到的数据是不一样的,因此称为不可重复读,即原始读取不可重复。
一句话:一个事务范围内两个相同的查询却返回了不同数据。
4>幻读(Phantom Reads)
一个事务按相同的查询条件重新读取以前检索过的数据,却发现其他事务插入了满足其查询条件的新数据,这种现象就称为“幻读”。
一句话:事务A 读取到了事务B提交的新增数据,不符合隔离性。
事务隔离级别
脏读”、“不可重复读”和“幻读”,其实都是数据库读一致性问题,必须由数据库提供一定的事务隔离机制来解决。
数据库的事务隔离越严格,并发副作用越小,但付出的代价也就越大,因为事务隔离实质上就是使事务在一定程度上 “串行化”进行,这显然与“并发”是矛盾的。同时,不同的应用对读一致性和事务隔离程度的要求也是不同的,比如许多应用对“不可重复读”和“幻读”并不敏感,可能更关心数据并发访问的能力。
常看当前数据库的事务隔离级别:show variables like 'tx_isolation';
Innodb存储引擎由于实现了行级锁定,虽然在锁定机制的实现方面所带来的性能损耗可能比表级锁定会要更高一些,但是在整体并发处理能力方面要远远优于MyISAM的表级锁定的。当系统并发量较高的时候,Innodb的整体性能和MyISAM相比就会有比较明显的优势了。
但是,Innodb的行级锁定同样也有其脆弱的一面,当我们使用不当的时候,可能会让Innodb的整体性能表现不仅不能比MyISAM高,甚至可能会更差。
手机扫一扫
移动阅读更方便
你可能感兴趣的文章