MySQL高级-索引优化分析
目录
MySQL高级-索引优化分析一.简介1. 索引是什么2. 优势3. 劣势4. 分类5. 结构5. 需要创建索引的情况6. 不要创建索引的情况
二.性能分析1. mysql常见瓶颈2. Explain 分析(1). Explain是什么(2). Explain能干嘛(3).explain 执行计划包含的信息(3).explain字段解释-id三种情况:
(4).explain字段解释-select_type类型:
(5).explain字段解释-table(6).explain字段解释-type类型:
(7).explain字段解释-possible_keys(8).explain字段解释-key(9).explain字段解释-key_len(10).explain字段解释-ref(11).explain字段解释-rows(12).explain字段解释-Extra
三.性能优化
一.简介
1. 索引是什么
索引index 是帮助mysql高效获取数据的数据结构本质:索引就是数据结构面试回答: 排好序的快速查找数据结构
2. 优势
提高数据检索的效率,降低数据库的IO成本降低数据排序的成本,降低了cpu的消耗
3. 劣势
索引也是一张表,该表保存了主键和索引字段,并指向实体表的记录,所以索引列也是要占用空间的索引大大提高了查询速度,同时降低了更新表的速度索引只是其中一个因素,数据量大的话
4. 分类
单值索引:一个索引只包含单个列,一个表可以有多个单列索引唯一索引:索引列的值必须唯一,但是允许有空值复合索引一个索引包含多个列
5. 结构
BTree索引:Java主要使用索引Hash索引full-text全文索引R-Tree索引
5. 需要创建索引的情况
主键自动建立唯一索引频繁作为查询条件的字段应该创建索引查询中与其他表关联的字段,外键关系的应该建立索引频繁更新的字段不适合创建索引where 条件中用不到的字段不创建索引高并发下倾向创建组合索引查询中排序的字段,排序字段若是通过索引去访问将大大提高排序速度查询中统计或者分组的字段
6. 不要创建索引的情况
表的记录太少经常性增删改查的表数据重复且分布平均的表字段
二.性能分析
1. mysql常见瓶颈
CPU在饱和的时候一般发生在数据装入内存或从磁盘上读取数据时候磁盘I/O瓶颈发生在装入数据远大于内存容量的时候服务器硬件的性能瓶颈,top,free,iostat和vmstat来查看系统的性能状态
2. Explain 分析
(1). Explain是什么
使用explain关键字可以模拟优化器执行SQL查询语句,从而知道mysql是如何处理SQL语句的,分查询语句或是表结构的性能瓶颈
(2). Explain能干嘛
表的读取顺序数据读取操作的操作类型哪些索引可以使用哪些索引被实际使用表之间引用每张表有多少行被优化器查询
(3).explain 执行计划包含的信息
idselect_typetablepartitionstypepossible_keyskeykey_lenrefrowsfilteredExtra
(3).explain字段解释-id
select 查询的序列号,包含一组数字,表示查询中执行select子句或操作表的顺序
三种情况:
id相同,执行顺序由上至下id不同,如果是子查询,id的序号会递增,id值越大优先级越高,越先被执行id相同不同,同时存在
(4).explain字段解释-select_type
查询类型,主要是用于区别普通查询,联合查询,子查询等的复杂查询
类型:
SIMPLE 简单查询,不包含子查询等复杂PRIMARY 包含子查询,最外层查询被标记SUBQUERY 在select /where 包含了子查询DERIVED 在from列表中包含的子查询被标记为derived (衍生) 临时表UNION 若是第二个select出现在union之后,则被标记为unionUNION RESULT 从union表获取结果的select
(5).explain字段解释-table
显示这一行的数据是关于哪一张表
(6).explain字段解释-type
访问类型排列
类型:
systemconsteq_refrefrangeindexall
(7).explain字段解释-possible_keys
显示可能应用在这张表中的索引,一个或者多个
(8).explain字段解释-key
实际使用的索引
(9).explain字段解释-key_len
标识索引中使用的字节数,可通过该列计算查询中使用的索引长度.在不失精确性的情况下,长度越短越好
(10).explain字段解释-ref
显示索引的哪一列被使用了
(11).explain字段解释-rows
根据表统计信息及索引选用的情况,大致估算出找到所需记录所需要的读取的行数
(12).explain字段解释-Extra
包含不适合其他列中显示但十分重要的额外信息
三.性能优化
日后连载…
