mysql的数据引擎
主流数据引擎(MyISAM、InnoDB)的对比
性能下降SQL慢分析
- 导致执行时间过长
- 导致等待时间过长
原因
- sql语句写的不合理
- 没有建索引或者索引已经失效
- 出现大量的关联查询和子查询,也就是很多join字段
- 服务器调优及各个参数设置(缓冲\线程数等)
sql执行顺序
索引
索引(Index)是帮助MySQL高效获取数据排好序的数据结构,本质就是:索引是数据结构,目的在于提高查询效率,类比于字典。
本质:排好序的快速查找数据结构,BTree结构
一般来说索引本身也很大,不可能全部存储在内存中,因此索引往往以索引文件的形式存储在磁盘中,我们平常说的索引,如果没有指明一般都是指B树(多路搜索树,并不一定是二叉的)结构组织的索引。其中聚集索引,次要索引,覆盖索引,唯一索引默认都是使用B+树索引。当然,除了B+树这种类型的索引之外,还有hash索引(hash index)等
优势
- 提高数据检索的效率,降低数据库的IO成本
- 通过索引列对数据进行了排序,降低数据排序的成本,降低了cpu的消耗
劣势
- 实际也是一张表,保存了主键与索引的字段,也会占据磁盘的空间
- 降低更新表的速度,执行update的操作的时候,也需要更新索引
索引的分类
- 单指索引:即一个索引只包含单个列,一个表可以有多个单列索引
- 唯一索引:索引列的值必须唯一,但允许有空值
- 复合索引:即一个索引包含了多个列
- 基本语法
索引的选择
- 主键自动建立唯一索引
- 频繁作为查询条件的字段应该创建索引
- 查询中与其他表关联的字段,处理关系建立索引
- 频繁更新的字段不适合创建索引
- 因为每次更新不单单是更新了记录还会更新索引
- where条件里用不到的字段不创建索引
- 单键/复合索引的选择问题?在高并发的下倾向于创建复合索引
- 查询中排序的字段,排序字段若通过索引去访问将大大提高排序的速度
- 查询中统计或者分组字段
explain 分析sql语句
字段
-
id - id如果相同,可以认为是一组,从上往下顺序执行,在所有组中id值越大,优先级越高,越先执行。决定表的读取顺序和加载顺序
-
-
table 显示这一行的数据是关于哪张表的
-
type 显示查询使用了何种类型,从最好到最差依次是system>const>eq_ref>ref>range>index>ALL
-
possible_keys 显示可能应用在这张表中的索引,一个或多个。查询涉及的字段上若存在索引,则该索引将被列出,但不一定被查询实际使用
-
key 实际使用的索引。如果为null则没有使用索引 ;查询中若使用了覆盖索引,则索引和查询的select字段重叠
-
key_len 表示索引中使用的字节数,可通过该列计算查询中使用的索引的长度。在不损失精确性的情况下,长度越短越好
-
ref 显示索引那一列被使用了,如果可能的话,是一个常数。那些列或常量被用于查找索引列上的值
-
rows 根据表统计信息及索引选用情况,大致估算出找到所需的记录所需要读取的行数
-
Extra 包含不适合在其他列中显示但十分重要的额外信息
- 1.Using filesort 说明mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取。MySQL中无法利用索引完成排序操作成为“文件排序
- 2.Using temporary 使用了临时表保存中间结果,MySQL在对查询结果排序时使用临时表。常见于排序order by 和分组查询 group by
- 3.USING index 表示相应的select操作中使用了覆盖索引(Coveing Index),避免访问了表的数据行,效率不错!如果同时出现using where,表明索引被用来执行索引键值的查找;
如果没有同时出现using where,表面索引用来读取数据而非执行查找动作。