MySQL索引及性能优化分析
⼀、SQL性能下降的原因
查询语句问题，各种连接、⼦查询
索引失效（单值索引、复合索引）
服务器调优及各个参数设置（缓冲、线程池等）
⼆、索引排好序的快速查数据结构
1. 索引分类
单值索引⼀个索引只包含单个列，⼀个表可以有多个
复合索引⼀个索引包含多个列
唯⼀索引索引的值必须唯⼀，但是允许空值
默认使⽤B+树索引，除B+树索引外，还有哈希索引（hash index）等
unicode是我国最新发布的也是收字最多的
2. 索引结构
B-Tree 索引
Hash 索引
Full-Text 索引
R-Tree 索引
B-Tree ⽰意图
3. 索引的优势和劣势
优势提⾼检索效率、降低排序成本
mysql语句的执行顺序
劣势索引需要占⽤空间，降低表更新速度
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⼦句或操作表的顺序
id相同，执⾏顺序由上⾄下
id不同，如果是⼦查询，id的序号会递增，id值越⼤优先级越⾼
id有相同有不同，id如果相同，可以认为是⼀组，从上往下顺序执⾏；所有组中，id值越⼤，优先级越⾼，越先执⾏select_type 查询的类型，主要是⽤于区别普通查询、联合查询、⼦查询等复杂查询
SIMPLE 简单的select查询，查询中不包含⼦查询或union
PRIMARY 查询中若包含任何复杂的⼦部分，最外层查询则被标记为primary
SUBQUERY 在select或where列表中包含了⼦查询
DERIVED 在from列表中包含的⼦查询被标记为DERIVED（衍⽣），MySQL会递归执⾏这些⼦查询，把结果放在临时表⾥UNION 若第⼆个select出现在UNION之后，则被标记为UNION
UNION RESULT 从UNION表获取结果的select
table 显⽰这⼀⾏数据是关于哪张表的
type 访问类型
system 表只有⼀⾏记录（等于系统表），这是const类型的特例，平时不会出现，这个可以忽略不计
const 表⽰通过索引⼀次就到了，⽤于⽐较primary key或unique索引
eq_ref 唯⼀性索引扫描，对于每个索引，表中只有⼀条记录与之匹配，常见于主键或唯⼀索引扫描
ref ⾮唯⼀性索引扫描，返回所有匹配某个单独值的所有⾏
range 只检索给定范围的⾏，使⽤⼀个索引来选择⾏。key列显⽰了使⽤哪个索引，⼀般是where语句中出现了between、<、>、in等index Full Index Scan,index与all的区别为index类型只遍历索引树。这通常⽐All快，因为索引⽂件通常⽐数据⽂件⼩。
ALL 全表扫描
possible_keys 可能⽤到的索引
key 实际使⽤的索引。如果为NULL，则没有使⽤索引。查询中若使⽤了覆盖索引，则该索引仅出现在Key列中。
key_len 表⽰索引中使⽤的字节数，显⽰为索引字段的最⼤可能长度，并⾮实际使⽤长度。
ref 显⽰索引的哪⼀列被使⽤了，如果可能的话，是⼀个常数。
rows 根据表统计信息及索引选⽤情况，⼤致估算出到所需的记录所要读取的⾏数。
extra 包含不适合在其它列中显⽰但是⼗分重要的额外信息。
Using Filesoft MySQL⽆法利⽤索引完成的排序操作成为⽂件排序
Using temporary 使⽤了临时表保存中间结果，MySQL在对查询结果排序时使⽤临时表。常见于排序order by 和分组查询group by。
Using Index 表⽰相应的select操作中使⽤了覆盖索引（Covering Index），避免访问了表的数据，如果同时出现了Using Where 表名索引被⽤来执⾏索引键值的查，如果没有出现Using Where 表名索引⽤来读取数据⽽⾮执⾏查动作。
Using where 表明使⽤了where过滤
Using join buffer 使⽤了连接缓存, 出现在两表连接时，驱动表没有索引的情况下，给驱动表建⽴索引可解决此问题，type将变成ref Impossible where where⼦句的值总是false，不能⽤来获取任何元组
※覆盖索引
select的数据列只⽤从索引中就能够取得，不必读取数据⾏，MySQL可以利⽤索引返回select列表中的字段，⽽不必根据索引再次读取数据⽂件，换句话说，查询列要被所建的索引覆盖。
四、⼀般查询优化
尽量使⽤全值匹配
最左前缀法则，如果索引了多列，查询从索引的最左列开始并且不跳过索引中的列。
不要在索引列上做任何操作（计算、函数、类型转换）
存储引擎不能使⽤索引中范围条件右边的列
尽量使⽤覆盖索引
使⽤不等于⽆法使⽤索引
is not null ⽆法使⽤索引，但是is null 可以的
like以通配符开头索引失效，以通配符结尾索引type为range ，通配符开头和结尾⽤覆盖索引type为index
字符串不加单引号索引失效(底层转换、使⽤函数)
⽤or连接时，索引失效
多表查询有索引的情况下inner join效果是最好的，如果⽤in或exists注意⼩表驱动⼤表
※ Order By 排序使⽤索引情况
五、查询截取分析
1. 分析⽅案
开启慢查询⽇志，例如设置超过5秒就是慢SQL
explain + 慢SQL分析
show profile 查询SQL在MySQL服务器的执⾏细节和⽣命周期情况
SQL服务器参数调优
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
odbc连接excel  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相关的开销信息
springcloud执行流程图  | SWAPS --显⽰交换次数相关开销的信息
⽇常开发需要注意的结论
converting HEAP to MyISAM 查询结果太⼤，内存都不够⽤了往磁盘上搬了。
Creating tmp table 创建临时表
Copying to tmp table on disk 把内存中临时表复制到磁盘，危险！
locked
5. 全局查询⽇志
配置启⽤
  在mysql的myf中，设置如下：
  #开启
  general_log=1
  # 记录⽇志⽂件的路径个人主页在哪
  general_log_file=/path/logfile
  log_output=FILE
编码启⽤
  set global log_output='TABLE';
  select * al_log;
六、MySQL锁机制
1. MyISAM锁
MyISAM在执⾏查询语句（SELECT）前，会⾃动给涉及的所有表加读锁，在执⾏增删改操作前，会⾃动给涉及的表加写锁。
MySQL的表级锁有两种模式：
表共享读锁（Table Read Lock）
表独占写锁（Table Write Lock）
锁类型  他⼈可读  他⼈可写
读锁    是      否
写锁    否      否
结论：
clothes的单词
结合上表，所以对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⾼，甚⾄可能会更差。
尽可能让所有数据检索都通过索引来完成，避免⽆索引⾏锁升级为表锁。
尽可能较少检索条件，避免间隙锁
尽量控制事务⼤⼩，减少锁定资源量和时间长度
锁住某⾏后，尽量不要去调别的⾏或表，赶紧处理被锁住的⾏然后释放掉锁。
涉及相同表的事务，对于调⽤表的顺序尽量保持⼀致。
在业务环境允许的情况下，尽可能低级别事务隔离

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。