数据库高级知识——索引优化分析（一）

1.SQL性能下降原因

查询数据过多

能不能拆，条件过滤尽量少

关联了太多的表，太多join

join 原理。用  A 表的每一条数据 扫描 B表的所有数据。所以尽量先过滤。

没有利用到索引

索引针对 列 建索引。但并不可能每一列都建索引
索引并非越多越好。当数据更新了，索引会进行调整。也会很消耗性能。
且 mysql 并不会把所有索引都用上，只会根据其算法挑一个索引用。所以建的准很重要。

服务器调优及各个参数设置（缓冲、线程数等）(不重要DBA的工作)

2.常见通用的Join查询

2.1 SQL执行顺序

手写：

机读：

总结：

2.2 Join图

什么叫共有，什么叫独有？

共有：满足 a.deptid = b.id 的叫共有
A独有:  A 表中所有不满足  a.deptid = b.id  连接关系的数据
同时参考 join 图

3.索引简介

3.1 索引是什么

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。
可以得到索引的本质：索引是数据结构。

索引的目的在于提高查询效率，可以类比字典，
 
如果要查“mysql”这个单词，我们肯定需要定位到m字母，然后从下往下找到y字母，再找到剩下的sql。
 
如果没有索引，那么你可能需要a----z，如果我想找到Java开头的单词呢？或者Oracle开头的单词呢？
 
是不是觉得如果没有索引，这个事情根本无法完成？

你可以简单理解为：索引是“排好序的快速查找数据结构”

在数据之外，数据库系统还维护着满足特定查找算法的数据结构，
这些数据结构以某种方式引用（指向）数据，这样就可以在这些数据结构上实现高级查找算法。
这种数据结构，就是索引。下图就是一种可能的索引方式示例：

左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址
为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。
二叉树弊端之一：二叉树很可能会发生两边不平衡的情况。
B-TREE: (B:balance)  会自动根据两边的情况自动调节，使两端无限趋近于平衡状态。可以使性能最稳定。
(myisam使用的方式)
B-TREE弊端：(插入/修改操作多时，B-TREE会不断调整平衡，消耗性能)从侧面说明了索引不是越多越好。
B+TREE:Innodb 所使用的索引

数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，
这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。

一般来说索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储的磁盘上

我们平常所说的索引，如果没有特别指明，都是指B树(多路搜索树，并不一定是二叉的)结构组织的索引。
其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引。
当然，除了B+树这种类型的索引之外，还有哈稀索引(hash index)等。

3.2 索引优势

类似大学图书馆建书目索引，提高数据检索的效率，降低数据库的IO成本
通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗

3.3 索引劣势

实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的
虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。
因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息
索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句

3.4 索引分类

1.主键索引

设定为主键后数据库会自动建立索引，innodb为聚簇索引

随表一起建索引：
CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,
						customer_no VARCHAR(200),
						customer_name VARCHAR(200),
  						PRIMARY KEY(id) 
);
unsigned (无符号的)
使用  AUTO_INCREMENT 关键字的列必须有索引(只要有索引就行)。
 
CREATE TABLE customer2 (id INT(10) UNSIGNED   ,customer_no VARCHAR(200),customer_name VARCHAR(200),
  PRIMARY KEY(id) 
);
 
单独建主键索引：
ALTER TABLE customer  add PRIMARY KEY customer(customer_no);  
 
删除建主键索引：
ALTER TABLE customer  drop PRIMARY KEY ;  
 
修改建主键索引：
必须先删除掉(drop)原索引，再新建(add)索引

2.单值索引

即一个索引只包含单个列，一个表可以有多个单列索引
索引建立成哪种索引类型？
根据数据引擎类型自动选择的索引类型
除开 innodb 引擎主键默认为聚簇索引 外。 innodb 的索引都采用的 B+TREE
myisam 则都采用的 B-TREE索引

随表一起建索引：
CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),
  PRIMARY KEY(id),
  KEY (customer_name)  
);
 随表一起建立的索引 索引名同 列名(customer_name)
单独建单值索引：
CREATE  INDEX idx_customer_name ON customer(customer_name); 
 
删除索引：
DROP INDEX idx_customer_name ;

3.唯一索引

索引列的值必须唯一，但允许有空值

随表一起建索引：
CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),
  PRIMARY KEY(id),
  KEY (customer_name),
  UNIQUE (customer_no)
);
建立 唯一索引时必须保证所有的值是唯一的（除了null），若有重复数据，会报错。  
 
单独建唯一索引：
CREATE UNIQUE INDEX idx_customer_no ON customer(customer_no); 
 
删除索引：
DROP INDEX idx_customer_no on customer ;

4.复合索引

即一个索引包含多个列

 随表一起建索引：
CREATE TABLE customer (id INT(10) UNSIGNED  AUTO_INCREMENT ,customer_no VARCHAR(200),customer_name VARCHAR(200),
  PRIMARY KEY(id),
  KEY (customer_name),
  UNIQUE (customer_name),
  KEY (customer_no,customer_name)
);
 
单独建索引：
CREATE  INDEX idx_no_name ON customer(customer_no,customer_name); 
 
删除索引：
DROP INDEX idx_no_name  on customer ;

5.基本语法
创建：

ALTER mytable ADD  [UNIQUE ]  INDEX [indexName] ON (columnname(length)) 

删除：

DROP INDEX [indexName] ON mytable; 

查看：

SHOW INDEX FROM table_name\G

3.5 mysql索引结构

1.BTree索引

【初始化介绍】 
一颗b树，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），
如磁盘块1包含数据项17和35，包含指针P1、P2、P3，
P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。
真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。
非叶子节点不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。
 
【查找过程】
如果要查找数据项29，那么首先会把磁盘块1由磁盘加载到内存，
此时发生一次IO，在内存中用二分查找确定29在17和35之间，锁定磁盘块1的P2指针，
内存时间因为非常短（相比磁盘的IO）可以忽略不计，
通过磁盘块1的P2指针的磁盘地址把磁盘块3由磁盘加载到内存，发生第二次IO，29在26和30之间，
锁定磁盘块3的P2指针，通过指针加载磁盘块8到内存，发生第三次IO，同时内存中做二分查找找到29，
结束查询，总计三次IO。
 
真实的情况是，3层的b+树可以表示上百万的数据，如果上百万的数据查找只需要三次IO，性能提高将是巨大的，
如果没有索引，每个数据项都要发生一次IO，那么总共需要百万次的IO，显然成本非常非常高。

2.B+Tree索引

B+TREE 第二级的 数据并不能直接取出来，只作索引使用。在内存有限的情况下，查询效率高于 B-TREE
B-TREE 第二级可以直接取出来，树形结构比较重，在内存无限大的时候有优势。

3.聚簇索引与非聚簇索引

聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。
术语‘聚簇’表示数据行和相邻的键值进错的存储在一起。
 如下图，左侧的索引就是聚簇索引，因为数据行在磁盘的排列和索引排序保持一致。

4.full-text全文索引
5.Hash索引
6.R-Tree索引

3.6 哪些情况需要创建索引

主键自动建立唯一索引
频繁作为查询条件的字段应该创建索引(where 后面的语句)
查询中与其它表关联的字段，外键关系建立索引
单键/组合索引的选择问题，who？(在高并发下倾向创建组合索引)
查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
查询中统计或者分组字段

3.7 哪些情况不要创建索引

表记录太少
经常增删改的表：Why:提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。
因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件
Where条件里用不到的字段不创建索引：索引建多了影响 增删改 的效率
数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。
注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。