mysql 索引

'''
1.基础知识
'''
定义：
主键（primary key）：唯一标识一条记录，不能有重复的，不允许为空
外键(forigen key)：表的外键是另一表的主键, 外键可以有重复的, 可以是空值
索引(index)：是对数据库表中一列或多列的值进行排序的一种结构

作用：
主键：用来保证数据完整性
外键：用来和其他表建立联系用的
索引：是提高查询排序的速度

个数：
主键：主键只能有一个
外键：一个表可以有多个外键
索引：一个表可以有多个唯一索引

添加：
主键：ALTER TABLE “表名” ADD PRIMARY KEY (字段名)
外键：ALTER TABLE “表名” ADD FOREIGN KEY (字段名) REFERENCES “另一张表名”( 字段名)
索引：ALTER TABLE “表名” ADD INDEX (字段名)

'''  
2. 为什么加索引后会使查询变快？
'''

变成了基于B+树的查询
我们平时建表的时候都会为表加上主键， 在某些关系数据库中，如果建表时不指定主键，数据库会拒绝建表的语句执行。 事实上， 一个加了主键的表，并不能被称之为「表」。一个没加主键的表，它的数据无序的放置在磁盘存储器上，一行一行的排列的很整齐， 跟我认知中的「表」很接近。如果给表上了主键，那么表在磁盘上的存储结构就由整齐排列的结构转变成了树状结构，也就是上面说的「平衡树」结构，换句话说，就是整个表就变成了一个索引。没错， 再说一遍， 整个表变成了一个索引，也就是所谓的「聚集索引」。 这就是为什么一个表只能有一个主键， 一个表只能有一个「聚集索引」，因为主键的作用就是把「表」的数据格式转换成「索引（平衡树）」的格式放置。
查询的次数等于=log(总条数)(底数：树枝)

'''    
3.为什么加索引后会使写入、修改、删除变慢？
'''

增删会改变树的结构
然而， 事物都是有两面的， 索引能让数据库查询数据的速度上升， 而使写入数据的速度下降，
原因很简单的， 因为平衡树这个结构必须一直维持在一个正确的状态， 增删改数据都会改变
平衡树各节点中的索引数据内容，破坏树结构， 因此，在每次数据改变时，DBMS必须去重新梳理树（索引）的
结构以确保它的正确，这会带来不小的性能开销，也就是为什么索引会给查询以外的操作带来副作用的原因。

'''
4.聚集索引和非聚集索引
'''
聚集索引：数据行的物理顺序与列值（一般是主键的那一列）的逻辑顺序相同（mysql中，主键就是聚集索引）

非聚集索引：该索引中索引的逻辑顺序与磁盘上行的物理存储顺序不同，一个表中可以拥有多个非聚集索引。

非聚集索引和聚集索引的区别在于， 通过聚集索引可以查到需要查找的数据，而通过非聚集索引可以
查到记录对应的主键值 ， 再使用主键的值通过聚集索引查找到需要的数据

1.使用聚集索引的查询效率要比非聚集索引的效率要高，但是如果需要频繁去改变聚集索引的值，
  写入性能并不高，因为需要移动对应数据的物理位置。
2.非聚集索引在查询的时候可以的话就避免二次查询，这样性能会大幅提升。
3.不是所有的表都适合建立索引，只有数据量大表才适合建立索引，且建立在选择性高的列上面性能会更好

 

'''
5.原理索引
'''

构造B+树

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据记录的地址。
若仅仅是  select * from table where id=45  , hash，二叉树等算法可以轻易实现，
但若是select * from table where id<6  , 就不好使了，它们的查找方式就类似于"全表扫描"，
因为他们的高度是不可控的(如下图)。B+Tree的高度是可控的，mysql通常是3到5层。