db_mysql

db_mysql

索引为什么用B-tree索引

* 我们首先思考我们不用B_TREE索引用什么呢？*

HASH

​ 数据结构中查找最快的是什么–>是数组，hash索引基础就是数组，所以我们探讨hash

    select * from table where id = 5;   //sql1    如果有100条数据 

​ hash只要查一次就OK了，B-Tree还要比较，hash完胜

​ 但我们看第二种常见情况 这种情况下hash就只能全表了 因为hash无序

    select * from table where id > 5; //sql2   

​

​ 所以索引需要是一种有序的数据结构

​ 那我们看看二叉树吧

二叉树

​

​ 好吧 即便有序 这种二叉树怎么办? 查找深度很深，效率底下 运气不好(比如最后的字节的)就是全表扫描

​ 所以索引需要平衡树

红黑树

​ 红黑树平衡吧 会左旋右旋保证树的平衡性，

​

​

​ 确实 红黑树相对与二叉树好了很多，但树的深度还是会很深，能不能将树的深度降低呢 提高查询效率

​

B-tree

​ B-tree满足这些要求

​

B_Tree	HASH	二叉	红黑
id=5	正常树查找	1次	可能出现全是右节点
id>5	顺着链路查	全表	顺着链路查
	有序的 平衡树	无序	有序

​

索引怎么实现的

innodb

​ 如上面这个B_tree的图，索引和数据是在同一个文件.ibd下，data即一行数据，通过主键组织成B—tree结构， 当然即便没有主键 innodb也是帮生成一个主键用于维护数据， 这种结构就是聚集索引，也叫聚簇索引，

​ 而非主键索引子节点存的数据是主键的值，先找到主键再找数据

​

MyISAM

​ 索引和数据是不同的文件，每个索引子节点存的数据是一个指针，指向.myd文件的地址，这一种就是非聚集索引

​

​ 很明显聚集索引，可以少定位一次，查的更快

innodb vs MyISAM

​ 1、 存储结构

MyISAM：每个MyISAM在磁盘上存储成三个文件。第一个文件的名字以表的名字开始，扩展名指出文件类型。.frm文件存储表定义。数据文件的扩展名为.MYD (MYData)。索引文件的扩展名是.MYI (MYIndex)。
InnoDB：所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB表的大小只受限于操作系统文件的大小，一般为2GB。

​ 2、 事务支持

MyISAM：强调的是性能，每次查询具有原子性,其执行数度比InnoDB类型更快，但是不提供事务支持。
InnoDB：提供事务支持事务，外部键等高级数据库功能。 具有事务(commit)、回滚(rollback)和崩溃修复能力(crash recovery capabilities)的事务安全(transaction-safe (ACID compliant))型表。

​ 3、 表锁差异

MyISAM：只支持表级锁，用户在操作myisam表时，select，update，delete，insert语句都会给表自动加锁，如果加锁以后的表满足insert并发的情况下，可以在表的尾部插入新的数据。
InnoDB：支持事务和行级锁，是innodb的最大特色。行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁，只是在WHERE的主键是有效的，非主键的WHERE都会锁全表的。

事务的基本原理简述：

sql语句和表数据文件之间相当于有中间事务日志文件的存在，sql语句作用到事务日志文件，如果发生了commit，事务日志再集中的作用到表数据文件。

如果发生了rollback，事务日志文件打上标志不提交，但也不会把日志删除

复合索引在B_Tree中的结构

​ 比如name，tel, idno建了个联合索引

​ 一定也还是一颗树 不可能是多颗树交叉，不然通过tel查询也会走索引 那应该怎么合成一颗树 呢?

​ a、正常按name建索引，但是子节点存的数据不是主键的值，而是tel的内容，然后匹配tel的内容

​

。。。。。这联合主键只有两个字段的时候可以搞一搞 字段多了怎么存呢 那每个子节点要存多少数据，并且怎么搞定最左之后的键也是按顺序来

​ b、我猜测是将复合索引的列名按照一定的规律组装成了一个键

​ 例如name_tel_idno作为索引键，然后查的时候就按照类似name% 或者name_tel%的查找

建索引的几大原则

​ 1.最左前缀匹配原则

​ 2.B Tree索引不对NULL值进行索引

​ 3.尽量选择区分度高的列作为索引

​ 4.索引列不能参与计算

​ 5.尽量的扩展索引，不要新建索引 即可以建联合索引