effective mysql_《Effective MySQL之SQL语句最优化》读书笔记——乱七八糟系列（给自己看）...

该书区别于诸如《MySQL技术内幕——InnoDB存储引擎》等书的一大特色是该书主要讲的是MySQL数据库中的索引技术，并分别讲了InnoDB、MyISAM、Memory三个存储引擎。其中重点当然是InnoDB。该笔记记得是乱七八糟，只适合自己看，记此笔记也是为了以后自己快速查阅回忆看这本书的内容，看起来会有断章取义的可能。

识别性能的问题：show full processlist

索引的用法：

优化数据访问

保持数据的完整性(主要通过外键技术)

表连接优化

结果排序

聚合操作

唯一键可以包含NULL值，并且每个NULL值都是唯一的(即NULL！=NULL)。

三个查看表信息的语句：

show create table

show table status

information_schema tables

索引技术：

B-树

B+树

R-树

散列表

索引的实现：存储引擎实现、数据结构技术

索引的类型：

唯一键

非主码索引

全文本索引

空间索引(R-)

目前的MySQL默认的存储引擎中，只有InnoDB存储引擎支持外键(Foreign key)且不需要存在对应的索引。但是考虑性能应该加上外键索引。

相对于MyISAM，在InnoDB中，数据是按照有序的方式存储的。

当某列声明索引后，在DB中，就存在B+树结构，查询时返回root地址即可。

B+树的索引节点中仅仅包含其子树跟节点中的最大和最小关键字。(这个特别重要，只有这样才能在外存的整页加载带内存是才能比较，B+树经典结构)

InnoDB中聚簇主码是用B+树存储的，但是非主码索引使用的是B-树存储的，但是此B-树不同于MyISAM中的B-树，InnoDB中非主码索引存储的是主码的实际值，MyISAM存储的是包含主码值得数据的指针。

填充因子：

由于InnoDB用聚簇主码存储数据，底层信息占用的磁盘空间的大小很大程度上取决于页面的填充因子。主要用来估计潜在的磁盘存储空间需求。

排序/非排序主码对填充因子的影响

按序排列的主码

用16K页面的15/16作为填充因子。例：id自增

例：id自增

不按序排列的主码

默认情况下，InnoDB会在插入初始数据的时候为每个页面分配50%作为填充因子。例如：学号Sno等现实中的主码。

例如：学号Sno等现实中的主码

聚集索引：

定义：根据数据行的键值在表中排列存储数据行。每表至多一个。

只有表中包含了聚簇索引时，表中的数据行才按照排序的顺序存储。

若没有聚簇索引，则其数据行存储在一个称为堆得无序结构中。

在实际的存储空间中，按序排列的主码空间小；不按序排列的主码空间大。

主码索引和聚集索引的区别：

一般情况下，当建立主键之后，会自动为主键建立聚集，唯一索引。

但是也可以把主键的聚集索引去掉，可以为其他属性建立聚集索引，但是唯一索引不能去。

并且一张表只能有一个聚集索引。

InnoDB内部的散列索引：

InnoDB存储引擎在聚簇B+树索引中存储主码。但是InnoDB内部是使用内存的散列表更高效的进行主码查找(Bloom Filter布隆过滤器)。

单列索引

ALTER TABLE

ALTER TABLE

possible_keys & key & Cardinality基数

例：select * from music where type='BAND' and founded='1980'

设两列均有索引。这就牵扯到索引选择的问题。

这时候就需要Cardinality。他会表明唯一值得个数，越大越代表区分度，故优先选择cardinality比较大的索引。(自动优化问题)——因为列值大就代表区分度高，不相同的值比较多，可以第一次就缩小成很小的集合。

使用索引值进行字符串进行模式匹配的问题：

1、利用通配符%可以做模式匹配的操作。例如：like 'Queen%'

2、如果查找的词是以通配符开头，则MySQL不会使用索引。例如：like '%Queen%'

技巧：使用反序值。reverse_email like REVERSE(‘%.com’)。就可以支持索引了。

3、MySQL中不支持基于索引的函数。

例如：WHERE UPPER(name) =UPPER('Alice')

唯一索引：

完整性。

”短路操作“——告诉优化器只有一行返回，从而避免了额外的索引扫描。

注：在可以为空的列上定义唯一索引也行，NULL！=NULL。三态逻辑。

多列索引：

别名：混合索引或者连接索引

说明：尽管索引的基数是唯一性的重要指标，但也会参考有关唯一性范围和容量的统计信息。

语法：

ALTER TABLE

ALTER TABLE

key_len=7:

这是用来确定索引值所使用的列的效率工具。

当使用多列索引时，交换列的顺序可能会创建更好的索引

其他用途：

最左边的列可当做单一列的索引来高效的使用。例如：group by order by.

应该经常评估多列索引是否比让优化器索引(合并效率更高)

索引造成的影响：

DML影响：重复索引(删多余－针对多列索引第一列情况)；索引的使用(删无效－针对不使用的情况)

DDL影响：

磁盘空间的影响：填充因子，非主码索引。

MySQL的限制和不足：

基于开销的优化器；

指定QEP：

索引的统计信息

基于函数的索引没实现：一个表上面多个索引

创建更好地索引：(覆盖索引、局部索引)

当QEP中Extra列显示using index时候，并不意味着访问底层数据时使用了索引，而是表示只有这个索引才是满足查询所有要求的。

覆盖索引：

指定满足了查询中给定表用到的所有的列。where子句、order by、group by以及select语句中的所有列。

在InnoDB中，主码的值会被附加在非主码索引的每个对应记录的后面。

select语句中，使用索引的列会比没使用索引的列查的快。

技巧：不要所有的查询都是用select * from table，而是需要啥查询啥。一是I/O开销，而是查询开销。

覆盖索引适合那些很多主码较小长度和外键约束的大型规范化约束来说是理想的方式。

SQL优化的生命周期：

1、截取SQL语句；

2、识别并分类有问题的SQL语句

3、确认SQL语句的当前操作

4、分析SQL语句和辅助信息

5、优化SQL语句

6、验证SQL语句的优化结果。

截取SQL语句的途径：

全面查询日志：实验室使用

慢查询日志：实用

二进制日志

应用程序代码

插件

TCP/IP分析

性能优化之隐藏秘籍——索引管理优化

1、整合DDL语句。就是几个SQL写在一个中

2、去除重复的索引；

3、删除无效的索引；

4、删除不同的索引。

索引列的改进：

数据类型：

BIGINT-->INT UNSIGNED

DATATIME-->TIMESTAMP

ENUM

NULL-->NOT NULL

隐含的变化

列的类型：

IP：varchar(15)-->int unsigned——INET_NOTA( INET_ATON(@IP) )

MD5:VARCHAR(32)-->char(32)-->BINARY(16)