文章目录
Sql性能下降原因
执行时间长 等待时间长
- 查询语句烂
- 索引失效
- 关联太多
- 服务器调优以及各个参数设置
join查询
sql执行顺序
- FROM
- ON
- WHERE
- GROUP BY
- HAVING
- SELECT
- DISTINCT
- ORDER BY
- LIMit
7 种join
-
INNER JOIN
-
FULL OUTER JOIN
-
FULL OUTER JOIN where
a.key is null and b.key is null -
LEFT JON
-
LEFT JOIN where b.key is null
-
RIGHT JOIN
-
RIGHT JOIN where a.key is null
索引简介
索引(Index)时帮助MySQL高效获取数据的数据结构
参考:http://note.youdao.com/noteshare?id=a12dd3a8ed630a2dc835b67e85146f89&sub=B9949C147F17483AA8CBD450115CB5FE
优势和劣势
- 优势
快速查找
排序
- 劣势
每次更新表,都会对索引所在的列进行更新。
索引分类
- 单值索引
一个索引只包含单个列,一个表可以有多个单列索引
-
唯一索引
-
复合索引
一个索引只包含多个列
检索原理
b+树,浅蓝色时一个磁盘块,包含了几个数据项(蓝色)和指针(黄色)
- 真实的数据存在叶子节点,即3、5、、9、10。
- 非叶子节点只存储搜索方向的数据项 17 35 并不真实存在数据表中
如查找29,做三次io加二分查找
建立索引的情况
建立索引
- 主键自动建立唯一索引
- 频繁查询 更新
- 外键关联
- where用不到的不能建立索引
- 排序字段
- 查询中统计或者分组字段
不能建立
- 表记录少(300W左右)
- 频繁增删改
- 数据平均且重复,建立索引没有太大实际效果。
索引的选择性是指索引中不同值的数据与表中记录数的比,
性能分析
Mysql自带优化器
优化器按照自己认为最优的执行计划,并不是DBA认为最有的。
Mysql常见瓶颈
- Cpu:
- io:写入数据远大于内存的时候
- 硬件配置
Explain
使用EXPLAIN关键字可以模拟优化器执行SQL查询语句,从而知道MySql是如何处理你的Sql语句的
能干吗
- 表的读取顺序
- 数据读取操作的操作类型
- 那些索引可以使用
- 那些索引实际被使用
- 表之间的引用
- 每张表有多少行被优化器查询
具体字段解释
id
表示查询中执行select子句或者操作表的顺序
- id相同,顺序由上倒下
- id不同,id值越大,越先被执行
- 以上2种组合
select_type和table
select_type查询的类型。主要是区别普通查询、联合查询、子查询
(1) SIMPLE(简单SELECT,不使用UNION或子查询等)
(2) PRIMARY(子查询中最外层查询,查询中若包含任何复杂的子部分,最外层的select被标记为PRIMARY)
(3) UNION(UNION中的第二个或后面的SELECT语句)
(4) DEPENDENT UNION(UNION中的第二个或后面的SELECT语句,取决于外面的查询)
(5) UNION RESULT(UNION的结果,union语句中第二个select开始后面所有select)
(6) SUBQUERY(子查询中的第一个SELECT,结果不依赖于外部查询)
(7) DEPENDENT SUBQUERY(子查询中的第一个SELECT,依赖于外部查询)
(8) DERIVED(派生表的SELECT, FROM子句的子查询)
(9) UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存,必须重新评估外链接的第一行)
table显示这一步访问数据库表名称
type
all,index,range,ref,eq_ref,const,system
- system:表中仅有一行(=系统表)这是const联结类型的一个特例。
- const:表示通过索引一次就找到,const用于比较primary key或者unique索引。因为只匹配一行数据,所以如果将主键置于where列表中,mysql能将该查询转换为一个常量
- eq_ref:唯一性索引扫描,对于每个索引键,表中只有一条记录与之匹配。常见于唯一索引或者主键扫描
- ref:非唯一性索引扫描,返回匹配某个单独值的所有行,本质上也是一种索引访问,它返回所有匹配某个单独值的行,可能会找多个符合条件的行,属于查找和扫描的混合体
- range:只检索给定范围的行,使用一个索引来选择行。key列显示使用了哪个索引,一般就是where语句中出现了between,in等范围的查询。这种范围扫描索引扫描比全表扫描要好,因为它开始于索引的某一个点,而结束另一个点,不用全表扫描
- index:index 与all区别为index类型只遍历索引树。通常比all快,因为索引文件比数据文件小很多。
- all:遍历全表以找到匹配的行
注意:一般保证查询至少达到range级别,最好能达到ref。
possible_key 和key
possible_key:可能应用在这张表中的索引,一个或多个,但是实际查询不一定用到
。可以理解为Mysql的草稿
key:显示MySQL实际决定使用的键(索引)。如果没有选择索引,键是NULL。
查询中如果使用覆盖索引,则该索引和查询的select字段重叠。
覆盖索引:直接由索引查询到数据 不用回表查询,查询结果受索引覆盖
key_len
索引使用的字节数,长度越短越好
ref
显示索引的那一列被使用了。
rows
找到记录所读取的行数
extra
包含不适合在其他列中显示,但是十分重要的额外信息
-
useing filesort:mysql会对数据使用一个外部的索引排序,而不是按照表内的索引顺序进行读取,Mysql无法利用索引完成的排序操作称为** 文件排序**
-
useing temporary:
使用临时表保存中间结果,在对查询结果排序时,使用临时表。常见order by 和 group by -
useing index :
使用了覆盖索引
如果同时出现useing where,表明索引被用来执行索引键值的查找
如果没有同时出现useing where 表明是读取数据而非执行查找动作
索引优化
小表驱动大表
索引失效
- 全值匹配
- 最佳左前缀原则(带头大哥不能死 中间兄弟不能断)
- 不在索引列做任何操作(计算 函数 类型转换)
我的理解是对索引列进行操作 已经不是原来的索引列了 - 不能使用索引中范围条件右边的列(范围之后全失效)
- 尽量使用覆盖索引
- 使用!=、not null、is null 、is not null 等也会使索引失效
- like以通配符开头的(使用覆盖索引去解决)
- 字符串不加单引号,索引失效
- 少用OR