MySQL随笔(2)

索引本身是一个独立的存储单位,在该单位里边有记录着数据表某个字段和字段对应的物理空间.索引内部有算法支持,可以说查询速度非常快. 
聚簇索引;聚簇索引不是单独的索引而是一种数据存储方式;
聚簇索引的优点是访问的速度更快;缺点是插入速度严重依赖插入顺序,更新聚簇索引列的代价很高,页分裂会导致表占用更多的磁盘空间.
工作原理:首先在索引中查找对应的值,其次根据匹配的索引找到相应的值,然后根据匹配到的索引的纪录找到相对应的数据行,最后将数据结果集返回给用户;
索引速度快的原因: 有算法的技术支持,索引字段与物理地址有直接对应.
优点: 
可以给任意字段进行索引;
极大地加快查询速度;
缺点:
创建和维护都需要时间,并且随着数据量的提升所耗费的时间也会增加;
需要占据大量的空间;
将对表中的数据进行增删改查时,索引也需要动态维护,降低了数据的维护速度,
索引的类别:
一、单列索引,一个索引中一个索引中只包含一个列,但是一个表中可以拥有多个单列索引:
普通索引;(index)
唯一索引,允许被索引的数据列包含重复的值(关键字为unique,更多的是为了避免数据重复)允许有空值;
主键索引(关键字为primary) 不允许有空值;
二、全文索引
全文索引(fulltext index) myisam可以设置该索引,只能在myisam引擎上才能使用,只能通过char,varchar,textleixing的字段中才能使用全文索引;
三、组合索引(复合索引):在表中多个字段组合上创建索引只有查询条件中使用了这些字段的左边字段时,索引才会被使用 遵循最左前缀原则.
E.G: index multildx(id,name,age),查询时按照id,name,age或id,name或id,这些情况都会触发索引,age或name,age则不会,这就只最左前缀.
四、空间索引, 空间索引是对空间数据类型的字段建立的索引,MySQL中的空间数据类型有四种: geometry, point, linestring, polygon;要求是引擎是myisam 必须将其声明为not null ,主要运用于地图,游戏;
其他 一、外键索引,简称外键,只有innodb存储引擎的表才能使用外键,如果要删除父表的记录(分列表),必须要先删除子表(带外键的表),不然会出错;
创建唯一索引: create table t1 (id int not null, name char(30) nit null, unique index uniqidx(id));
创建全文索引:create table t2 (id int not null, name char(30) nit null,name nvarchar(50) not null,age int not null, fulltext index FUDEX(name));
创建组合索引:create table t3 (id int not null, name char(30) nit null,name nvarchar(50) not null,age int not null, index dex(id,name,age));
创建空间索引:create table ee(id int not null,name varchar(50) not null,age int not null,a geometry not null,spatial index 11b(a));
建完表格后添加索引: alter table ff add index dexx(name(50));
MySQL的索引类型有很多,但是底层存储的数据结构都是B_TREE,B树索引有自己的限制,
如果不是按着索引最左列开始查找就无法使用索引
使用索引时,不能跳过索引中的列
not in和<>操作无法使用索引
如果索引中有某列的查询范围,则其右边的所有列都会无法使用索引
注意: 尽量避免在索引上的列上使用表达式或函数.
索引列具有选择性,索引的选择性是不重复的索引值合表的记录数的比值;
更新索引统计信息及减少索引碎片: analyze table table_name;

 

事务的隔离性的四个级别:
读取未提交内容(read uncommitted ,也被称之为脏读);
读取提交内容(read committed);
可重读(repeatable read),会出现"幻读"的问题;
可串行化 (serializable),最高级别隔离.
多版本并发控制(multiversion concurrency control).MySQL的默认隔离级别是可重读,利用MVCC机制来解决幻读.
多版本并发控制就是在每一行记录的后面增加两个隐藏列,记录创建版本号和删除版本号,而每一个事务在启动的时候都有一个唯一的递增的版本号.只有read committed和repeatable read 两种事务隔离级别才会使用MVCC,read uncommitted由于是读到未提交的,所以不存在版本问题,而serialiazbel会对所有的读取行进行加锁;
脏读: 当一个事物正在访问数据时,并对数据进行了修改,而这个修改后的数据还没有提交到数据库中,这时另一个事务,也在访问这个数据,然后使用了这个数据;
不可重复读: 在一个事务中,多次读取同一个数据,在这个事务还没结束时,另一个事务也访问该数据,由于第二个事务的修改,第一个事务读取到的数据可能是不一样的,这样就发生了在同一个事务中两次读到的数据是不一样的,因此别称之为不可重复读.
幻读: 是指事务不是独立执行时的一种现象,第一个事务对表中的数据进行修改,这种修改涉及到表中的所有数据行,同时,第二种事务也修改这个表中的数据,这次的修改是向表中插入一行数据,这是第一中操作时就会出现有一个数据行没有被修改的幻觉.
除了事物的三个基本的命令外,还有一个savepoint命令(保存点命令),可以把一个事务分割成几个部分,可以指定在什么位置进行回滚操作.