2. 索引优化分析
2.1 原因
性能下降sql、执行时间长、等待时间长
查询语句写的差
索引失效 （索引建立了，没用上索引）
补充：索引分为单值和复合
单值：单个表中的某个字段建一个索引
复合：单个表中的某个字段建多个索引
可以通过频繁使用给他建立索引，所以查询的比较快
关联查询太多join（设计缺陷或不得已的需求）
服务器调优及各个参数设置（缓冲、线程数等）
SQL执行顺序
select # 5 ... from # 1 ... where # 2 .... group by # 3 ... having # 4 ... order by # 6 ... limit # 7 [offset]
2.2 常见通用的join查询
inner join 全连接
full outer join 外连接
具体其sql代码如下：
执行sql建表
CREATE TABLE `tbl_emp` ( `id` int(11) NOT NULL AUTO_INCREMENT, `name` varchar(20) DEFAULT NULL, `deptId` int(11) DEFAULT NULL, PRIMARY KEY (`id`) , KEY `fk_dept_id`(`deptId`) )ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8; CREATE TABLE `tbl_dept` ( `id` int(11) NOT NULL AUTO_INCREMENT, `deptName` varchar(30) DEFAULT NULL, `locAdd` varchar(40) DEFAULT NULL, PRIMARY KEY (`id`) ) ENGINE = InnoDB AUTO_INCREMENT = 1 CHARACTER SET = utf8; INSERT INTO `atguigudb`.`tbl_dept`(`id`, `deptName`, `locAdd`) VALUES (1, '11', '111'); INSERT INTO `atguigudb`.`tbl_dept`(`id`, `deptName`, `locAdd`) VALUES (11, '11', '111'); INSERT INTO `atguigudb`.`tbl_dept`(`id`, `deptName`, `locAdd`) VALUES (2, '11', '111'); INSERT INTO `atguigudb`.`tbl_dept`(`id`, `deptName`, `locAdd`) VALUES (4, '11', '111'); INSERT INTO `atguigudb`.`tbl_dept`(`id`, `deptName`, `locAdd`) VALUES (5, '11', '111'); INSERT INTO `atguigudb`.`tbl_emp`(`id`, `name`, `deptId`) VALUES (1, '11', 1); INSERT INTO `atguigudb`.`tbl_emp`(`id`, `name`, `deptId`) VALUES (2, '11', 11); INSERT INTO `atguigudb`.`tbl_emp`(`id`, `name`, `deptId`) VALUES (3, '11', 2); INSERT INTO `atguigudb`.`tbl_emp`(`id`, `name`, `deptId`) VALUES (4, '11', 3); INSERT INTO `atguigudb`.`tbl_emp`(`id`, `name`, `deptId`) VALUES (5, '11', 4);
实例代码
/* 1 */ SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key; /* 2 */ SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key; /* 3 */ SELECT <select_list> FROM TableA A INNER JOIN TableB B ON A.Key = B.Key; /* 4 */ SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key WHERE B.Key IS NULL; /* 5 */ SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key WHERE A.Key IS NULL; /* 6 */ SELECT <select_list> FROM TableA A FULL OUTER JOIN TableB B ON A.Key = B.Key; /* MySQL不支持FULL OUTER JOIN这种语法 可以改成 1+2 */ SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key UNION SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key; /* 7 */ SELECT <select_list> FROM TableA A FULL OUTER JOIN TableB B ON A.Key = B.Key WHERE A.Key IS NULL OR B.Key IS NULL; /* MySQL不支持FULL OUTER JOIN这种语法 可以改成 4+5 */ SELECT <select_list> FROM TableA A LEFT JOIN TableB B ON A.Key = B.Key WHERE B.Key IS NULL; UNION SELECT <select_list> FROM TableA A RIGHT JOIN TableB B ON A.Key = B.Key WHERE A.Key IS NULL;
上图中第6个的实现 可以通过如下：
由于有些mysql不能使用full join，不过可以换种方法表示
A 的独有 + AB 共有 + B的独有
union本身就可以去重
所以可以这样使用
select * from tbl_emp a left join tbl_dept b on a.deptId = b.id union select * from tbl_emp a right join tbl_dept b on a.deptId = b.id;
上图中第7个的实现可以通过如下：
也就是A的独有+ B的独有
之后通过union进行合并
select * from tbl_emp a left join tbl_dept b on a.deptId = b.id where b.id is null union select * from tbl_emp a right join tbl_dept b on a.deptId = b.id where a.deptId is null;
想详细学习的请看：博客3.2-5.7
2.3 索引
提高效率，类比资源
排好序的、快速查找（影响order by）数据结构
从而可以获得索引的本质：索引是排好序的快速查找数据结构。
在数据之外，数据库系统还维护着满足特定查找算法的数据结构，这些数据结构以某种方式引用(指向）数据，这样就可以在这些数据结构上实现高级查找算法。这种数据结构，就是索引
重点：索引会影响到MySQL查找(WHERE的查询条件)和排序(ORDER BY)两大功能！
索引的目的在于提高查询效率，可以类比字典的目录。如果要查mysql这个这个单词，我们肯定要先定位到m字母，然后从上往下找y字母，再找剩下的sql。如果没有索引，那么可能需要a—z，这样全字典扫描，如果我想找Java开头的单词呢？如果我想找Oracle开头的单词呢？
官方解释：
数据本身之外，数据库还维护着一个满足特定查找算法的数据结构，这些数据结构以某种方式指向数据，这样就可以在这些数据结构的基础上实现高级查找算法，这种数据结构就是索引。
如下方二叉树的数据结构所示
索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录
左边是数据表，一共有两列七条记录，最左边的是数据记录的物理地址。
为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应数据，从而快速的检索出符合条件的记录。
一般来说，索引本身也很大，不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。
# Linux下查看磁盘空间命令 df -h [root@Ringo ~]# df -h Filesystem Size Used Avail Use% Mounted on /dev/vda1 40G 16G 23G 41% / devtmpfs 911M 0 911M 0% /dev tmpfs 920M 0 920M 0% /dev/shm tmpfs 920M 480K 920M 1% /run tmpfs 920M 0 920M 0% /sys/fs/cgroup overlay 40G 16G 23G 41%
平常所说的索引，如果没有特别指明，都是指B树（多路搜索树，并不一定是二叉的）结构组织的索引。其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引
唯一索引默认都是使用B+树索引，统称索引。
当然，除了B+树这种类型的索引之外，还有哈稀索引(hash index)等。
优势：
提高数据检索的效率，降低数据库的IO成本（不用一直通过磁盘查找）
通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗
劣势
索引列也是要占用空间的（占空间）
更新表时，MySQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息
MysQL有大数据量的表，需要花时间研究建立最优秀的索引，或优化查询
2.3.1 索引分类(重点)
单值索引：即一个索引只包含单个列，一个表可以有多个单列索引。
唯一索引：索引列的值必须唯一，但允许有空值。
复合索引：即一个索引包含多个列
建议：一张表建的索引最好不要超过5个！
基本语法：
INDEX ：索引关键字
indexName ：索引名字（自己起的一般表名+字段名）
mytable：表名
columnName：字段名
创建
CREATE [UNIQUE] INDEX indexName ON mytable(columnName(length)); //或者 ALTER mytable ADD [UNIQUE] INDEX [indexName] ON (columnName(length));
删除 DROP INDEX [indexName] ON mytable;
查看 SHOW INDEX FROM tableName;
/* 基本语法 */ /* 1、创建索引 [UNIQUE]可以省略*/ /* 如果只写一个字段就是单值索引，写多个字段就是复合索引 */ CREATE [UNIQUE] INDEX indexName ON tabName(columnName(length)); /* 2、删除索引 */ DROP INDEX [indexName] ON tabName; /* 3、查看索引 */ /* 加上\G就可以以列的形式查看了 不加\G就是以表的形式查看 */ SHOW INDEX FROM tabName \G;
添加具体有四种方式：
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list);：该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list);：这条语句创建索引的值必须是唯一的(除了NULL外，NULL可能会出现多次)。
ALTER TABLE tbl_name ADD INDEX index_name (column_list);：添加普通索引，索引值可出现多次。
ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list);：该语句指定了索引为FULLTEXT，用于全文索引。
2.3.2 索引结构
BTree索引
Hash索引
full-text全文索引
R-Tree索引
BTree索引检索原理：
初始化介绍
浅蓝色的称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示)
如磁盘块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.3.3 索引情况（那些需要那些不需要）
分为有索引和无索引
索引（查找，排序）
需要建立索引的情况有：
视频地址
主键自动建立唯一索引
频繁作为查询条件的字段应该创建索引
查询中与其它表关联的字段，外键关系建立索引
单键/组合索引的选择问题，who?(在高并发下倾向创建组合索引)
查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
查询中统计或者分组字段（分组都是需要排序的）
不需要简历索引的情况有：
表记录太少
经常增删改的表（提高了查询速度，但是会同时江低更新表的速度，对表进行更新的时候，mysql还要保存数据，还要保存一下索引文件）
数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。注意，如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
频繁更新的字段不适合创建索引，因为每次更新不单单是更新了记录还会更新索引
Where条件里用不到的字段不创建索引
假如一个表有10万行记录，有一个字段A只有true和false两种值，并且每个值的分布概率大约为50%，那么对A字段建索引一般不会提高数据库的查询速度。索引的选择性是指索引列中不同值的数目与表中记录数的比。如果一个表中有2000条记录，表索引列有1980个不同的值，那么这个索引的选择性就是1980/2000=0.99。一个索引的选择性越接近于1，这个索引的效率就越高。
一个索引的选择性越接近于1，这个索引的效率就越高
