1，获取元数据
2，information_schema搭配concat
3，show语句
4，索引介绍及B树索引算法
5，聚集索引和辅助索引
功能对比
区别及索引树高度
6，索引的操作命令
7，执行计划分析
8，计算执行计划的ken_len
9，联合索引优化细节
10，执行计划分析-extra
11，索引优化的小案例
12，索引应用规范
1，获取元数据
元数据是指库属性、表属性，列名等一系列不是数据行的数据，元数据存储在“基表”中，只能通过DDL、DCL语句如建表、删表的语句进行修改元数据，通过专用视图和命令查询元数据，库information_schema保存了大量元数据查询的视图，show命令也有查询元数据的功能，但结果没有前者全面(够一般情况使用)
视图：当一条很长的select语句会被经常执行时，可以给这条语句起一个类似别名的东西，即视图，如create view aa as select ...，aa代表select语句执行的结果，可以再次使用select查询aa，select * from aa或select 列名 from aa
information_schema库的TABLES表存储了所有数据库中的表的元数据
TABLES表的列信息：TABLE_SCHEMA，表所在的库；TABLE_NAME，表名；ENGINE，存储引擎；TABLE_ROWS，数据行；AVG_ROW_LENGTH，平均行长度；INDEX_LENGTH，索引长度
显示所有库和表的信息：
所有库和其包含的表：
查询所有innodb引擎的表：
一张表的大小：平均行长度*行数+索引长度
统计world库下city表占用空间大小：
统计world库的大小：
统计所有数据库的大小并从大到小排序：
2，information_schema搭配concat
select配合concat函数创建备份语句进行数据库的分库分表备份：
3，show语句
SHOW DATABASES;查看数据库
SHOW TABLES;查看库中的表
SHOW CREATE DATABASE world;SHOW CREATE TABLE student;查看建库建表语句
SHOW PROCESSLIST;查看当前用户的连接情况
SHOW CHARSET;查看支持的字符集
SHOW COLLATION;查看支持的校对规则
SHOW GRANTS FOR root;查看用户的权限信息
SHOW VARIABLES LIKE '%xx%';查看参数信息
SHOW ENGINES;查看所有支持的存储引擎类型
SHOW INDEX FROM world.city;查看表的索引信息
SHOW ENGINE INNODB STATUS \G;查看innodb引擎详细状态信息
SHOW BINARY LOGS;查看二进制日志的列表信息
SHOW BINLOG EVENTS IN '文件名';查看二进制日志的事件信息
SHOW MASTER STATUS;查看mysql当前使用的二进制日志信息
SHOW SLAVE STATUS\G;查看从库状态
SHOW relaylog EVENTS IN '文件名';查看中继日志的事件信息
SHOW STATUS LIKE '';查看数据库整体状态
4，索引介绍及B树索引算法
类似查找书中的某个知识点，比从头到尾查看更快的一种方式是，通过书的目录来查找，索引就是大概这样的功能，建立索引目的是为了加快数据查找
索引的分类：B树(innodb默认使用的索引类型)，R树、Hash、FUllText、GIS
B树索引算法演变
在数据库中查找数据涉及到数据的查找算法，但诸如最普通的二分查找、二叉查找树，都不能保证查找每个元素都是经历相等的步骤，这就造成不公平的现象。而在数据库中的查找应该遵循几个原则：查找速度快、查找每个数据要进行的操作步数是相同的
下图为一棵普通B树
在普通B树中，每个节点都为16KB(数据页大小)，每次查找都从根节点开始，叶子节点存储的是实际的数据，中间的枝节点做查找方向的判断
比如，根节点存储了5、28、65，代表它下面有3个枝节点——枝节点1存储5、10、20，枝节点2存储28、35、56，枝节点3存储65、80、90。三个枝节点存储的第一个数分别是5、28、65，同理，每个枝节点存储的也是下面三个叶子节点的第一个数，每个节点的第一个数是每个节点中最小的数。上面两层节点中还包含“P”，可以理解为指针，分别存储了下层节点的位置
以查找叶子节点中的数据33为例，首先从根节点开始，28<33<65，应该查找中间的枝节点，28<33<35，应该查找左边的叶子节点，然后到达存储33的叶子节点。对于每个数据的查找，都会经历这三步，这也是添加一条数据的过程
B+树与B树的区别在于叶子节点上，当查找的条件是>33的范围查询时，B树会先找33，再找34、35...每一次都是从根节点到叶子节点的一次完整查找，每个节点都是16KB，这对I/O的性能影响非常大。B+树会在叶子节点中新添加指向相邻叶子节点的双向指针，当找到33后，大于33的数可能存在于同一叶子节点中——33后面的数，因为每个节点中数据都是由小到大排列，也有可能在它下一个相邻的叶子节点中，因为有了指针，所以不需要再次遍历整棵树就能跳转到下一个正确的叶子节点上接着判断，这都是因为B+树中数据排列的有序性
B*树在枝节点上加入了双向指针，做了更进一步的优化，目前MySQL即使用B*树做索引
5，聚集索引和辅助索引
B树索引按功能分类可分为辅助索引和聚集索引
辅助索引建立过程：
假设在一张人员表中以name列生成索引(字符串最终是要转换成编码存储在计算机上的，所以字符串之间也是可以比较“大小”——比较转换的相应进制数的大小)
1，管理员选择name列并执行一条命令创建辅助索引
2，MySQL将此列的值取出并由小到大排序
3，将排好序的值均匀的存储到B树的叶子节点中，然后生成枝节点、根节点
查找过程：
假设查找name为"zs"的数据行，在走索引的情况下首先在B树中查找到zs，zs所在的叶子节点中，还应存储这样一个指针——它指向zs所在的行。这就好比查找一本书中的某个单词时，在目录中找到了这个单词，目录还记录了这个单词所在的页码，在该页码中有对这个单词完整的解释。在MySQL中也是页码，因为数据都存在数据页上，通过页码即找到包含“zs”的行所在的数据页，就能取出里面的数据
辅助索引可能会出现一个问题，当页码不是顺序存储时，相当于一本书的页码是乱的(还是得一页一页的查找页码)，即使知道了“zs”在第13页，还是得遍历一遍页码才能找到第13页，但这比遍历每行数据要好的多了
聚集索引
辅助索引“辅助”的是聚集索引，聚集索引能解决上面提到的页码无规则的情况。在建表时设置主键，MySQL会将数据行有序的存储到数据页中，而且保证页码不会乱(这是因为主键的关系，设置了主键且为自增长时，数据的存储是严格遵守顺序的)。除此之外，在生成B树时，与辅助索引不同的是，聚集索引将整个数据行存储到叶子节点中，而不单只是一个主键的id值和指向的页码，也就是将数据所在的页直接作为叶子节点，向上生成枝节点和根节点。这样在查找时就不用再经历一次指针而直接取到数据，枝节点、根节点的生成则相同
主键一般都会设置，所以聚集索引基本上是一定有的，即使没有主键，MySQL也会自动选择唯一键作为索引，即使没有唯一键，MySQL会自动生成隐藏列作为索引
功能对比
当辅助索引是常用的查询条件时，配合聚集索引的查找过程：
查找“zs”时，首先遍历name列生成的B树(辅助索引)，找到“zs”所在的叶子节点，这时下一步不是跳转到“zs”所在的页码，而是取出“zs”所在行的id值(由原来存储的指针变为id)，再拿id查找id列生成的B树(聚集索引)，找到数据行(id的B树的叶子节点就是数据行)
区别及索引树高度
两种索引的区别：
1，表中任意列都可以创建辅助索引，辅助索引的叶子节点只存储索引列的有序值加聚集索引列值(如主键)
2，表中的聚集索引只能有一个，一般是主键，聚集索引的叶子节点存储有序的整行数据
辅助索引细分
单列辅助索引——普通列建立的索引，联合索引(覆盖索引)——多个列建立索引，唯一索引——唯一键建立的索引
索引树的高度
索引树的高度越低越好，一般维持在3~4层最佳
数据行较多时，早期采用分表的操作将一张大表分成小表；字段长度也会影响高度，尽量选择长度短的字段作为索引列，或是使用前缀索引；数据类型，当数据较多时选择varchar，因为char会将空格也算成数据，这就浪费了叶子节点的空间
6，索引的操作命令
查询表的列是否建立索引
Key列即代表索引类型，PRI为主键索引，MUL为辅助索引，UNI为唯一索引
查看索引详细信息
建立辅助索引
为name列建立了名为idx_name的辅助索引，这种在线建索引的操作会锁表，因为会先拿出数据并排序
建立联合索引
建立唯一索引
首先要判断列是否有重复值，可以先统计行数，再统计去重后的行数两者对比
或是group by分组后统计每组的个数select count(district) from city group by district;
或是直接建立唯一索引，报错则是有重复值
创建前缀索引，只针对字符串列
删除索引，首先查看表中有哪些索引
7，执行计划分析
一条select语句的执行过程中，在SQL层会对这条语句进行解析，生成多种执行计划树，以此来选择最优的执行方式。我们可以利用这种机制，将优化器选择后的执行计划截取出来，以管理、判断语句的执行效率(此时语句未被执行)
获取执行
desc或是explain select语句，并没有执行select语句，只是获取执行计划，对性能没有影响
其中重要的字段有：table，当查询涉及到多个复杂的表，就要在此找出可能影响性能低下的表；type，查询的类型，MySQL中只有两种类型，全表扫描(将表中所有数据加载到内存进行遍历查找)或索引扫描
全表扫描时类型为ALL，走索引时type会给出语句走的是哪种类型的索引，具体有index、range、ref、eq_ref、const(system)、null，从左到右性能依次变好
全表扫描的情况：DESC select * FROM city;或是where条件无索引，或是where条件为!=、not in、like '%aa%'时，即不会走任何索引时就是全表扫描
对于索引扫描：
index：全索引扫描，id列做聚集索引，即从索引树中遍历所有节点的情况
range：索引范围扫描
即只遍历索引树的部分节点，所以这些——<、>、<=、>=、between and、or、in、like都可以是索引范围扫描，但or、in、like的性能没有>、<一类的好，因为在索引树的叶子节点中有双向指针，在进行如>某个值的查找时只需要先查找一次树找到起始的值，再通过指针直接取出大于它的值即可，而诸如or、in每个值都需要完整的查找一次索引树，所以在遇到像这样类型时range的语句时
可以将其改写为
两条语句的类型变为ref，ref类型的索引性能高于range
ref：辅助索引等值查询，相等=条件的查询，性能比range好
name列没有建立索引
eq_ref：多表联结查询时，from后面的第一张表是驱动表，其余是子表，一般拿数据行最少的作为驱动表。这是因为驱动表的列不走索引，子表的列走索引，类型为eq_ref，所以要求子表使用主键列或唯一键列作为联结条件，这在表设计阶段就得规划好，尽量将联结条件设计为主键
结果可知a表是全表扫描，b表走eq_ref类型的索引
const(system)：当使用主键或唯一键的等值查询时走const类型索引，这二者性能上相近
null：当查找的数据不在表中时，性能最好
补充：对于辅助索引，!=和not in等语句不走索引，即ALL的全表扫描
对于主键索引则是走range类型的索引，但尽量不要使用这种语句和like '%aa%'
使用索引进行优化，对需要进行优化查找的列使用索引，可以解决数据库百分之七八十的优化工作
其他字段：
possible_keys：可能会走的索引
key：真正选择的索引，在possible_keys中显示的索引可能会有多个，要看MySQL具体选择哪个
8，计算执行计划的ken_len
key_len：索引的覆盖长度，为一个列建立索引后，这个列的值在叶子节点中会存储的最大长度
比如一个char(4)的列，既可以存储4个英文字符也可以存储4个中文字符，在utf8md4分别对应4个和16个字节，MySQL索性分配最大的长度，所以该列每个索引会占用16个字节长度
这是在该列指定了为非空的情况下，如果没有指定为非空，则还需要加1个字节长度(第一个字节)来表示该列是否为空。同理对于int类型的索引列，长度为5(int的4个字节加上如果未指定为非空时的1个字节)，varchar(4)长度为19(17加上两个字节，一个索引值的表示开始，一个表示结束)
单列索引是索引长度越短越好，节省索引存储空间
9，联合索引优化细节
与key_len最相关的是联合索引查找，如果建立了一个4个列的联合索引，在查找时将这四个列全用上，则key_len为这四个索引覆盖长度之和，这种性能是最好的
相应的，少一个联合列的查找，key_len就会少这个列的字节长度，性能随之下降。这其中有一个条件，四个列都必须是等值查询的，且虽然条件无关排列的顺序(因为MySQL优化器会自动调整四个条件与建立索引时顺序相同，索引的查找顺序也是建立索引时的顺序规定的，这是必要的要求，以前版本的MySQL中没有这个功能)，但如果出现只用a、c和d列做等值查询条件，b列跳过了，那它就只会走a的索引
由此也可得知提高性能的方法，在建立联合索引时，将唯一值最多的列放在最前面，这样在第一次索引查找时就最大程度的缩小了范围；对于acd列做索引查找语句的优化，可以单独为acd列建立联合索引，这样就能走三个列的索引，不尽人意的是，这条语句还是可能会走原来的四个列的索引，即使后创建的三个列索引是包含在possible_keys中的，这种情况下可以考虑删除原来的索引
不等值的查询：这里不是!=、not in这种的，因为它们压根就不会走索引，对于>、<、>=、<=，如果上面的例子中，只有第二个列(建立索引时排第二的列)不是等值查询，即使四个列的索引全用上，也只会到第二列索引，即一二列的索引会被用到，三四列不走索引，此时也可依实际情况考虑修改建立索引的顺序，将第二列放在最后，这样四个列的索引都能用到
10，执行计划分析-extra
Extra：当这个字段出现Using filesort时就得注意，说明在查询中有关排序的条件列没有合理地应用索引而出现了额外的排序操作(建立索引时该列已经排过序)，查询语句中可能有order by或group by或distinct或union子句，此时就得检查这些子句后面跟的列有没有用到索引——看key_len是否为最大
11，索引优化的小案例
explain、desc的使用场景
当公司业务慢时，且是数据库的问题，可能是两种情况，一种是应急性的慢，数据库突然夯住，可能是资源耗尽的原因，先执行show processlist;查看连接到数据库的用户和他执行的操作，找到可能使数据库出现问题的语句，然后用desc查看该语句的执行计划，看有没有走索引，查看走的索引类型，没走则可以建立索引，即便走了索引还是慢，则改写语句。另一种情况是持续性的在一段时间内很慢，这时分析慢日志文件slowlog，desc查看执行计划，建索引、改语句
12，索引应用规范
建索引的原则(DBA规范)
建表必须要有主键，一般是无关列，自增长
建索引的前提，该列经常作为where条件列，或是order by、group by、join on、distinct条件的列
最好使用唯一值较多的列作为联合索引的前导列，其他的按照联合索引优化细节来做
列值较长的索引列建议使用前缀索引
降低索引条目，一方面不要创建没用的索引，一方面清理不常使用的索引(SQLyog的表信息中有不必要索引)，更改索引列的值或新增删除数据都会改变索引树的值和结构
索引维护要避开业务繁忙期
小表不建索引
不走索引的语句(开发规范)
数据库建了索引，但开发写的查询语句中没有用到索引列或查询条件没有建立索引
针对辅助索引，查询结果集是原表的大部分数据(25%以上)，这种情况下不走索引，因为优化器认为即使走了索引还是和全表扫描一样慢
频繁的更改索引可能会导致索引更新不及时，统计信息过旧导致索引失效，一个例子是同一条语句在前几天执行只需要零点几秒，今天就需要执行几分钟
查询条件在索引列上使用函数，或是对索引列进行加减乘除的运算，desc select * from city where id-99=1;，这种情况不走索引
隐式转换导致索引失效，假设一个列是存储字符串的char类型，但实际存储数字，查询条件是字符串比较时走索引，查询条件是数字的比较时不走索引，且其中还有一个数字转换为字符串的隐式操作
!=、not in在辅助索引中不走索引，在聚集索引走range类型索引，like '%aa'不走索引
联合索引，查询条件使用的索引列和创建联合索引的列不完全符合，只走部分索引
