MySQL 续集 02

1、Mysql索引优化分析

1.1 性能下降SQL慢执行时间长等待时间长

   1.查询语句写的不好
   2.索引失效
        表：user  字段： id name sex email  
   		2.1单值索引（只用了一个字段来建立索引）
   		create index  idx_user_name on user(name) 
   		2.2符合索引
   		create index  idx_user_nameEmail  on user(name,email) 
   3.关联查询太多join（设计缺陷或不得已的需求）
   		
   4.服务器调优及各个参数设置（缓冲线程数）

1.2 常见JOIN查询

1.SQL执行顺序
	1.1手写顺序
			SELECT DISTINCT 
				<select_list>
			FROM
				<left_table>  <join_type>
			JOIN  <right_table>  ON <join_condition>
			WHERE
				 <where_condition>
			GROUP BY 
				<group_by_list>			
			HAVING
				<having_condition>
		  	ORDER BY 
				<order_by_condition>
			LIMIT 
					<limit_number>
	1.2机器怎么读的
		FROM
			<left_table>
		ON <join_condition>
			<join_type> JOIN <right_table>
		WHERE 
			<where_condition>
		GROUP BY 
			<group_by_list>
		HAVING 
			<having_condition>
		SELECT 
		DISTINCT 
			<select_list>
		ORDER_BY
			<order_by_condition>
		LIMIT 
			<limit_number>
	3.总结 
			查看下图

MySQL 续集 02
join:

-- ----------------------------
-- Table structure for test1
-- ----------------------------
DROP TABLE IF EXISTS `test1`;
CREATE TABLE `test1` (
  `id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

-- ----------------------------
-- Records of test1
-- ----------------------------

-- ----------------------------
-- Table structure for test2
-- ----------------------------
DROP TABLE IF EXISTS `test2`;
CREATE TABLE `test2` (
  `id` int(11) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8;

笛卡尔：
绿色+2分灰色+黄色
MySQL 续集 02

mysql> select * from test1;
+----+
| id |
+----+
|  1 |
|  2 |
|  3 |
|  4 |
+----+
4 rows in set (0.00 sec)

mysql> select * from test2
    -> ;
+----+
| id |
+----+
|  3 |
|  4 |
|  5 |
|  6 |
+----+
4 rows in set (0.00 sec)

mysql> select *from test1,test2 ; # 4*4=16条 
+----+----+
| id | id |
+----+----+
|  1 |  3 |
|  2 |  3 |
|  3 |  3 |
|  4 |  3 |
|  1 |  4 |
|  2 |  4 |
|  3 |  4 |
|  4 |  4 |
|  1 |  5 |
|  2 |  5 |
|  3 |  5 |
|  4 |  5 |
|  1 |  6 |
|  2 |  6 |
|  3 |  6 |
|  4 |  6 |
+----+----+
16 rows in set (0.00 sec)

inner join:

#test1 的 3 4 能与 test2 的 3 4 匹配到   2条 
mysql>  select a.id aid ,b.id bid   from test1 a inner join test2 b on a.id = b.id ;
+-----+-----+
| aid | bid |
+-----+-----+
|   3 |   3 |
|   4 |   4 |
+-----+-----+
2 rows in set (0.00 sec)

left join:
黄色部分
MySQL 续集 02

mysql>  select a.id aid ,b.id bid   from test1 a left join test2 b on a.id = b.id ;
+-----+------+
| aid | bid  |
+-----+------+
|   1 | NULL |
|   2 | NULL |
|   3 |    3 |
|   4 |    4 |
+-----+------+
4 rows in set (0.00 sec)

right join：
黄色部分
MySQL 续集 02

mysql>  select a.id aid ,b.id bid   from test1 a right join test2 b on a.id = b.id ;
+------+-----+
| aid  | bid |
+------+-----+
|    3 |   3 |
|    4 |   4 |
| NULL |   5 |
| NULL |   6 |
+------+-----+
4 rows in set (0.00 sec)

left join - inner join 左表的独有
绿色部分
MySQL 续集 02

mysql>  select a.id aid ,b.id bid   from test1 a left join test2 b on a.id = b.id  where b.id is null ;
+-----+------+
| aid | bid  |
+-----+------+
|   1 | NULL |
|   2 | NULL |
+-----+------+
2 rows in set (0.00 sec)

right join - inner join 右表的独有
黄色部分
MySQL 续集 02

mysql>  select a.id aid ,b.id bid   from test1 a right join test2 b on a.id = b.id  where a.id is null ;
+------+-----+
| aid  | bid |
+------+-----+
| NULL |   5 |
| NULL |   6 |
+------+-----+
2 rows in set (0.00 sec)

左右全连接：左表右表全保留
黄色部分：注意中间交叉部分只保留一份
MySQL 续集 02

#Mysql 不支持 full join  但是可以用union 来达到这种效果
mysql>  select a.id aid ,b.id bid   from test1 a left join test2 b on a.id = b.id  
    -> union 
    ->  select a.id aid ,b.id bid   from test1 a right join test2 b on a.id = b.id ;
+------+------+
| aid  | bid  |
+------+------+
|    1 | NULL |
|    2 | NULL |
|    3 |    3 |
|    4 |    4 |
| NULL |    5 |
| NULL |    6 |
+------+------+
6 rows in set (0.00 sec)

左表独有+右边独有
黄色部分
MySQL 续集 02

mysql> select a.id aid ,b.id bid   from test1 a left join test2 b on a.id = b.id where b.id is null    
			union   select a.id aid ,b.id bid   from test1 a right join test2 b on a.id = b.id where a.id is null;
+------+------+
| aid  | bid  |
+------+------+
|    1 | NULL |
|    2 | NULL |
| NULL |    5 |
| NULL |    6 |
+------+------+
4 rows in set (0.00 sec)

1.3 索引简介

1.3.1 索引基础

索引是帮助Mysql高效获取数据的数据结构（索引：数据结构）
索引可以说是排好序的快速查找数据结构
回忆：
frm 数据结构
MYD 数据
MYI 索引

在数据之外，数据库还维护这满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样我们就可以在这些数据结构上实现高级查找算法，这种数据结构就是索引。
MySQL 续集 02
注：一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往会以索引文件的形式存储在磁盘上。

我们平常所说的索引，如果没有特别指明的话，默认即使值B树（多路搜索树，并不一定是二叉树）结构组织的索引，其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，除了B树还有其他索引比如哈希索引（hash index）等

**索引优势：**
1. 提高数据检索效率，降低数据库的IO成本
2. 降低数据排序成本，降低cpu消耗 （索引对数据进行排序了）
**索引劣势：**
1.索引实际也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引也是要占用空间的
2.索引会降低更新速度，INSERT UPDATE DELETE  操作都会带来索引的调整
3.索引只是提高效率的一个因素，如果你的MYSQL有大量的数据，就需要花时间研究建立最优秀的索引，或优化查询SQL

1.3.2 索引分类与语法

单值索引
一个索列只包含单个列，一个表可以有多个单值索引
唯一索引
索引列的值必须唯一可以为空值 unique
复合索引

基本语法：
创建：
create [unique] index indexName on tableName (columnName1,columnName2…);
create tableName add [unique] index [indexName] on (columnName1,columnName2…);
删除：
drop index [indexName] on tableName;
查看：
show index from tableName;
ALERT命令创建
alert table tableName ADD PRIMARY KEY (column_list) 主键索引值必须唯一不能为空
alert table tableName ADD UNIQUE index_name (column_list) 索引值必须唯一可以为Null null可以出现多次
alert table tableName ADD INDEX index_name (column_list) 普通索引索引值可出现多次
alert table tableName ADD FULLTEXT index_name (column_list) 指定索引为FULLTEXT 用于全文检索

1.3.32 索引结构和检索原理

BTree索引、Hash索引、full_text全文索引、R-Tree索引

MySQL 续集 02
解释：这里需要注意 17 和 35 并不是真的存在数据表它只是我为了最终叶子节点的数据服务而产生的

查找过程：

真实情况下，3层的b+树可以表示上百万数据，如果上百万数据查找只需要三次IO ,那将会有很大的性能提高，如果没有索引，每个数据都要发生一次IO 那性能是很高的

1.3.32 索引创建场景

需要：

主键自动建立唯一索引
频繁作为查询条件的字段应该创建索引
查询与其他表关联的字段，外键关系建立索引
频繁更新的字段不适合创建索引，（因为每次更新还需要更新索引性能消耗大）
where 里边用不到的字段不创建索引
单值索引？组合索引？倾向于组合索引
查询中排序的字段，排序字段如果通过索引去访问将大大提高排序速度（索引给排序了）。
查询中统计或分组字段

不需要：

表记录太少啦
经常增删改的表
数据重复且分布平均的字段，因此应该只为经常查询和最经常排序的数据列建立索引。
如果某列包含重复内容，为它建立索引没有什么太大实际效果