从零开始java数据库SQL优化（一）：一篇解决MYSQL查询优化总纲

目录

目录

一：问题场景

二：优化

1.查询计划 

2.分析计划

 连接类型

三：索引的使用与选择

1.索引的使用以及优化

(1) MYSQL中的索引原理

(2) 4中树结构在索引中的应用（附红黑树）

2、树结构选择合适的索引。

3、mysql中建立索引的规范

4、mysql的索引分类以及使用场景

（1）索引的总分类

（2）Mysql中的索引

 （3）、建立合适索引，避免滥用索引。

四：优化SQL减低查询时间

1.使用limit关键字

2.添加合适的索引

3.避免全表查询

五：SQL优化建议

1.SQL从程序中查询到程序显示结果的过程

2.SQL层级优化

---

---

一：问题场景

  在一次项目中，出现一个SQL优化问题，问题场景就是，在用户登陆时，将用户的部门、岗位、角色以及权限（其中权限整合在菜单表中，每2个表都是用一个中间表关联）查询出来作为一个全局常量（MSYQL数据库）。

  不多说，直接上SQL语句吧。

SELECT 
			a.fk_user_id AS "fk_user_id",
			a.user_realname AS "user_realname",
			a.user_name AS "user_name",
			a.user_type AS "user_type",
			a.sex AS "sex",
			a.phone AS "phone",
			a.password AS "password",
			a.user_addr AS "user_addr",
			a.avater AS "avater",
			a.status AS "status",
			
			
			
			fr.role_id as role_id,
			fr.role_code as role_code,
			fr.role_name as role_name,
			fr.role_range as role_range,
			
			
			fd.dept_id as dept_id,
			fd.dept_name as dept_name,
			fd.parent_id     as dept_parent,
			fd.parent_ids     as dept_parents,
			fd.company_name as company_name, 
			 
			fp.post_id as post_id,
			fp.post_name as post_name,
			fp.post_split as post_split,
			
			m.perms as perms
			
			FROM fk_user a
		 	LEFT JOIN fk_user_role fkur ON fkur.user_id = a.fk_user_id
			LEFT JOIN fk_role fr ON fr.role_id = fkur.role_id
			LEFT JOIN fk_role_menu rm ON rm.role_id = fr.role_id
			LEFT JOIN fk_menu m ON m.menu_id = rm.menu_id
	
			LEFT JOIN fk_dept fd ON fd.dept_id = a.dept_id
			LEFT JOIN fk_user_post fup ON fup.user_id = a.fk_user_id
			LEFT JOIN fk_post fp ON fp.post_id = fup.post_id

 where a.user_name ='admin'  

  在使用本地数据库连接（localhost:3306）基本查询就消耗了0.270s左右。见下图：

二：优化

1.查询计划 

   很尴尬，实际上查询的几张表数据一共也就在250条左右（其中菜单表和角色菜单表大概在160条），这里我们再来看一下SQL的查询计划统计：

2.分析计划

  其中，我们看type一行的查询涉及到3个名词：ALL,ref,eq_ref。Type学名为连接类型，连接类型决定查询时间。

 连接类型

  ALL：全表扫描，最耗时的查询，即使在查询出符合条件的数据，还要继续查询其余剩下的数据。（在本例场景中实际代码

              已经控制了用户名唯一，那么在查询出该用户就应该停止继续往下查询，但是如果要查询所有的用户则查询类型肯定

              是ALL）。

INDEX: 加索引的全表查询，比较耗时的一种查询，就是在查询字段上添加一个索引。众所周知Mysql中的索引结构为B+树。在

              同等数据结构中，B+数据的时间复杂度为  log（2）n优化程度仅次于1，降低了树高（即查询表列的行数减少了），常见

              的索引优化就是讲表数据按照b+树排列，较少全表查询的实际行数。

RANGE：范围搜索，可以看出在是将查询数据行数按照一定的条件控制，减少实际查询行数（一定是基于索引）。常见得IN,

             BETWEEN等都是这种类型。

REF:  单条件不限查询。简而言之，就是将查询放在一定的范围内查询，允许重复查询（这里区别于全表在于使用的索引，虽然

          不是唯一索引）。