MySQL

专题二：MySQL索引

索引是关系型数据库中对某一列或多个列的值进行预排序的数据结构，索引的目的是提高查询速率，其原理本质上是通过不断缩小想要获取的数据的范围来筛选出最终想要的结果，同时把随机事件变成有顺序的；这种索引机制可以用一种特地的方式来查找锁定数据。

MySQL中也有类似页的逻辑存储单位，在MySQL的InnoDb引擎中，页的大小时16kb，是操作系统的4倍，所以一页是可以存储很多数据的，而MySQL的数据正是以页为基本单位组合而成的。存入到页中的数据也就会自动排序了。而页的内部存放数据的模块实质上就是一个链表结构，特点就是增删快，查询慢所以优化是必须的。

单页模式：当我们在磁盘中读取数据时，因为一个程序在访问了一条数据之后，还会有极大的可能再次访问这条数据或者访问这条数据相邻的数据，所以就直接加载了这条数据所在的页，也就是说要取出的数据是1kb，但是操作系统并不会只取出1kb而是直接取出4kb大小的数据，下次要访问时直接在内存中查找可以减少磁盘io次数；在插入的数据中查找数据的话是先将整页数据查询出来在进行逐个对比直至找到该数据。如果插入时没有进行排序，逐条查找直至结尾，查询完整个页才能返回是否存在这条数据。单页模式也就只能在查询某条数据时将一整页的数据加载到内存中以减少硬盘io次数从而提高性能；它的内部实际上是链表结构，是通过数据的逐条比较来去除特定的数据。

页目录：每个页目录会存放自己这个目录中最小的id，查询数据时先查询存放在哪个页目录中，然后通过目录像进行数据查找即可。如果该目录项下没有找到就可以直接确定数据不存在，提升了查找效率。目录页本质也是页，普通页存放数据，而目录页中存放的是普通页的地址。在单页时，采用页目录的目录项来指定一行数据，这条数据就是存在于这个目录中的最小数据，这样就可以通过页目录来查找所需要的数据。

多页模式：可以进行多数据的存储，就是采用开辟新页的方式将数据存放在不同的页中，然后用链表结构将不同的页连接起来。但是多页模式的本质还是链表结构，也会对查询效率产生一定的影响。所以也可以用类似于页目录优化页数据的方式来优化多页模式的数据查询。

优化：用这种方式优化之后的数据就时b+树了。

B+树：
B+树的非页子节点存储键值，是不存储数据的，每一个叶子节点都包含指向下一个叶子节点的指针，方B+树的阶数是等于键值数量的，B+树一节点可以存储一千个键值，那么三层就可以存储10亿个数据，所以一般查找10亿数据只需要2词磁盘io操作。
因为B+树索引的所有数据均存储在叶子结点，而且数据是按照顺序排序的，这样就使得范围查找，排序查找，分组查找等变得简单。B+树各个页之间是通过双向链表连接的，叶子节点是通过单向链表连接的。在innoD便遍历查询。不存储数据就可以存储更多的键值，相应的树阶会更大，树会更矮更胖，这样查询的时候进行的磁盘io次数就会减少，查询速率就会提高。另外，一般根节点时常驻内存，B+ 树中数据页之间通过双向链表连接以及叶子结点中数据的单向链表连接方式可以很快找到表中的所有数据。
优势：磁盘读写代价低；查询效率更稳定；更有利于数据库的扫描

索引分类：

普通索引：最基本的索引，没有任何限制，关键字key或index定义，加速查询。

唯一索引：unique关键字，类似普通索引，不同的是普通索引允许重复的值，而唯一索引的值必须是唯一的。也是加速查询

主键索引：primary key关键字，值必须是不为空且唯一的。

全文索引：关键字的来源不是所有字段的数据，而是从字段中提取的特别关键词。
目前MySQL中只有MyISAM存储引擎支持全文索引，并且只有CHAR、VARCHAR、TEXT类型支持。它用于替代效率较低的LIKE模糊匹配操作，而且可以通过多字段组合的全文索引一次性全模糊匹配多个字段。同样使用B-Tree存放索引数据，但使用的是特定的算法，将字段数据分割后再进行索引，索引文件存储的是分割前的索引字符串集合，与分割后的索引信息，对应Btree结构的节点存储的是分割后的词信息以及它在分割前的索引字符串集合中的位置。

聚簇索引：就是将索引和数据放到一起，找到索引也就找到了数据，B+树就是一种聚簇索引。但是innoDB只有一个聚簇索引，因为如果将所有的索引都用聚簇索引则每一个索引都保存一份数据，会造成数据的冗余，在数据量巨大的情况下就会大量消耗资源。MySQL中不仅可以对某一列的建立索引，还可以对多列建立一个联合索引，联合索引的排序匹配有一个原则：最左前缀匹配原则。从左往右依次比较大小，最终对这个索引进行排序匹配。

最左前缀匹配原则:b+树的数据项是复合数据结构的，它是按照从左至右的顺序来建立搜索树的，如果使用联合索引检索数据时就会优先比较左边的数据来确定下一步的搜索方向，如果左边的数据相等再对右边的数据进行比较，最后得到检索结果。

基于主键索引和非主键索引的查询：主键查询只需要查询一个树，而因为非主键的索引树的叶子节点存放的是主键的值，索引在使用非主键查询是，需要先查出主键索引，在根据主键索引查询数据，需要进行两次的索引树查询，也就是回表。

专题三：SQL索引底层优化

explain优化查询检测：
explain可以帮助开发人员分析sql问题，它显示了MySQL如何使用索引来处理select语句以及如何链接表，以便于写出更好的的索引和语句。
使用方法：在查询语句前加上explain

select_type:
simple: 简单select（不使用union或子查询）
primary: 最外面的select
union:
dependent union: union中的第二个或后面的select语句。取决于外面的查询
union result: union的结果
subquery: 子查询中的第一个select
dependent subquery: 子查询中的第一个select，取决于外面的查询
dervied: 导出表的select（from子句的子查询）

优化索引的设计：

①、字段推荐使用自增的整型（int），因为引擎在处理查询和连接是会逐个比较，int数据类型的索引容易比较，速度较快。
②、尽量不要让字段默认值为null，即设置为自增；含有空值的列很难进行比较，null使得索引信息的计算变得复杂，只要列中有null值都不会包含在索引中，复合索引中只要有一列含有null值，这一列的复合索引就是无效的。
③、注意冗余的索引，不使用的索引，mysql允许在同一个列上创建多个索引，多个索引就会占用多余的物理空间，并且对增删改会有一定的性能影响。
④、在查询一行数据时使用limit 1 ， 当字段查询只有一个结果时使用limit mysql 数据库就会在找到一条数据后停止搜索，不会继续往下比较，可以增加性能
⑤、在join时使用相同类型并建立索引，两个表join字段做了索引后mysql内部启动会优化join sql语句的机制。
⑥、千万不要使用order by rand()，因为他会打乱原来的数据，这样很耗性能，数据库的性能就会下降
⑦、避免使用select ，因为再数据库中查询的数据越多，性能消耗多，速度越慢。还可能增加网络传输的负载。
⑧、永远为一张表设置一个id（建议自增整型），使用varchar作为主键影响性能。
⑨、使用enum而不是varchar，enum类型是快和紧凑的，这样效率更快。
⑩、Procedure analyse(), 它会让mysql去分析字段和实质的数据并给出建议。数据越多时建议才会变得用更加准确有。
⑪、尽可能使用not null ，null 需要额外空间，在进行比较时程序会更加复杂，就会减慢查询效率。
⑫、Perpared statements（预处理语句） 是一种运行子后台的SQL 语句集合，无论是性能问题还是安全问题都可以从这里得到好处；他会检查一些绑定好的变量，保护程序不会受到“sql 注入式”攻击；MySQL在传输Prepared Statements时使用二进制形势，使得网络传输非常有效率；但是ps不支持查询缓存。
⑬、无缓冲查询，不需要等到所有的查询结果都返回后才开始处理查询结果，节约内存。限制：要把所有行读走或下次查询前清空结果。
⑭、把ip地址存成 unsigned int 用整型存放ip只需要4个字节且可以有定长的字段，可以优化查询。
⑮、固定长度的表mysql检索会更快，因为固定长度容易计算偏移量，读取速度更快，性能更高，定长的表也更容易缓存和重建；但因为无论用不用都分配空间，会浪费空间；如果字段不定长查找需要程序找到主键。
⑯、垂直分割，把数据库中的表按列变成几张表，可以降低复杂度和字段数目，从而达到优化的目的。
⑰、拆分大的delete 或insert 语句，因为这两个操作是会锁表的，锁表后其他操作就无法执行；Apache中的线程和数据库连接是极大占用服务器资源的，如果把表锁住了线程就会累积就会加大服务器的压力。
⑱、列越小越快，把数据变得紧凑就会减少硬盘的访问，加快查询速率。
⑲、使用一个对象关系映像器（ORM），ORM的懒加载只有在真正需要取值的时候才去做，能增长性能；ORM还可以把sql语句打包成事务，比单独执行快；但是懒加载有可能因为要创建多个小查询而降低性能。
⑳、“永久链接”，会永远处于连接状态，会占用有限的连接数和内存；Apache运行会创建很多进程，如果都用永久链接内存占用就会非常的大，速度就会变慢。
21、避免索引失效，对所有的索引列指定具体值，这种情况下索引生效效率高。
22、字符串不加单引号会造成索引失效。
23、用or分割条件查询时，or前面使用索引or后面没用使用索引，索引也会失效，若果是用and索引不会失效。
24、以%开头的模糊查询索引会失效，而只是尾部用%则索引不会失效。但是覆盖索引的情况下以%开头索引也会有效。
25、Is null 和 is not null ，在mysql评估使用索引比全表扫描慢、代价高时则不会使用索引。
26、In 索引有效，not in 索引失效。
27、尽量使用复合索引，少使用单列索引；因为数据库会选择一个最优的索引使用，如果是复合索引会选择都使用。
28、show status like ‘Handler_read%’就可以查看索引使用情况。
29、Mysql推荐使用innoDB存储引擎，支持事务，行级锁，外键约束，mysql 5.6版本以后还支持全文索引；事务和外键约束都是为了数据的完整性；innoDB擅长处理并发，因为行级锁只对该行锁定，对其他行没有锁定；在一定情况下可以选择行级锁多版本并发控制，效果可以达到无阻塞读操作。使用时：
独立表空间：
每个表自己的独立表空间，存储每个表的数据和索引；
可以实现表单在不同的数据进行迁移；
表空间可以进行回收（drop table操作不能自己回收）；
Drop table 删除表操作对统计分析或日值表，在删除大量数据之后可自动回收不用的空间；
对于使用独立表空间，不管怎么删除表空间的碎片都不会太严重；
表单增加过大时使用共享空间把文件分开，但是访问过大时会访问多个文件，速度就会减慢；
使用分区表可以把大的空间移到别的空间上然后做连接。
*共享空间表：
可以将表空间分成多个文件存放在各个磁盘上（文件大小不收表大小限制），数据和文件放在一起方便管理；
所有的数据和索引存放在同一个文件，文件会变大，多个表及索引在表空间混合存储，在对表做了大量删除的之后就存在大量的空隙，对统计分析、日值系统这类应用最不适合使用共享表空间。

总的来说：innoDB 擅长事务，更加注重数据的完整性、安全性，高并发处理；但是不擅长快速插入，因为插入前需要排序，消耗时间和检索。

30、MyISAM 存储引擎：
插入速度快，空间占用少，不会排序
支持全文索引，数据能压缩处理，能够节省磁盘减少io操作；但是压缩后只能读不能写，修改需要先解压再更新。
只支持表级锁，支持高并发，但是支持并发插入，不会阻塞读操作。

31、范式和逆范式：
第一范式：原子性，要求数据库中的字段值是不可拆分的原子值
第二范式：消除部分依赖，确保主键的每列与非主键相关
第三范式：消除传递依赖，确保每列与主键有直接关系
逆范式：通过增加冗余或重复的数据来提高数据库的性能

32、

索引减小了服务器需要扫描的数据量，提高了查询速率，但是因为要保存索引文件，就降低了增删改的速度。