mysql高级 索引、事务、锁

一、索引index

1.什么是索引:索引的做用相当于图书的目录,可以根据目录中的页码快速找到所需要的内容

2.为什么要有索引:索引在mysql中也叫做“键”,是存储引擎用于快速找到记录的一种数据结构

索引优化应该是对查询性能最有效的手段,善用索引能够轻易将查询性能提高好几个数量级

3.索引的优劣势 :

                           优势:高效查询

                            劣势:

                                    索引本身也是表,因此会占用存储空间;

                                    索引表的维护和创建需要时间成本,这个成本随着数据量增大而增大

                                     构建索引会降低数据表的更新效率,因此在修改数据表的同时还需要修4改索引表

4.mysql中索引的使用

       在创建表时创建索引代码如下:

              CREATE TABLE 表名(

                 ID INT NOT NULL,

                 username VARCHAR(16) NOTNULL,

                 INDEX myindex(username(16))

                     )

5.查看索引: SHOW INDEX FROM 表名

6.删除索引:DROP INDEX 索引名 表名;  或者 ALTER TABLE 表名 DROP INDEX 索引名;

7.索引的分类:

主键索引:不允许重复,不允许空值

唯一索引:用来建立 索引的列的值必须是唯一的,允许空值

普通索引:用表中的普通列构建的索引,没有任何限制

全文索引:用大文本对象的列构建的索引

组合索引:用多个列组合构建的索引,这多个列中的值不允许有空值

适合使用:经常查询条件的列,排序的列,主键外键

不适合使用:经常增删改的列和记录少

索引失效的情况 :
 在组合索引中不能有列的值为 NULL,如果有,那么这一列对组合索引就是无效的
 在一个 SELECT 语句中,索引只能使用一次,如果在 WHERE 中使用了,那么在 ORDER BY 中就不要用了
 LIKE 操作中,‘%aaa%‘不会使用索引,也就是索引会失效,但是‘aaa%’可以使用索引
 在索引的列上使用表达式或者函数会使索引失效
 在查询条件中使用不等于,包括<符号、>符号和!=会导致索引失效
 在查询条件中使用 IS NULL 或者 IS NOT NULL 会导致索引失效
 字符串不加单引号会导致索引失效
 在查询条件中使用 OR 连接多个条件会导致索引失效,除非 OR 链接的每个条件都加上索引
 如果排序的字段使用了索引,那么 select 的字段也要是索引字段,否则索引失效
 尽量不要包括多列排序,如果一定要,最好为这队列构建组合索引

 

   

 

事务

 什么是事务:一个最小的不可再分的工作单元;
                    通常一个事务对应一个完整的业务(例如银行账户转 账业务,该业务就是一个最小的工作单元)
 转账操作理解事务:在一个转账业务中,以上两条 DML 语句必须同时成功或者同时失败
 MySQL 中事务的使用
  开启事务:start transaction; begin transaction;

提交事务 commit

回滚事务rollback;

 事务的特征
 原子性:事务是最小单位,不可再分

一致性: 事务要求所有的 DML 语句操作的时候,必须保证同时成功或者同时失败

隔离性:同一时间,只允许一个事务请求同一数据,不同的事务之间彼此没有任何干扰

持久性:是事务的保证,事务终结的标志(内存的数据持久到硬盘文件中)
 事务处理不当的现象

脏读:A读取B未提交的数据,如果A用了B没提交的数据那该数据无效(B回滚),脏读数据。

不可重复读:A读完数据后,B修改数据并提交A再次读取发现数据变了,前后数据不一致,就证明该数据不能重复读取,因为它是可变的。

  幻读
   A读取后B插入数据A再次读取时发现数量不对,应生幻觉一般。
 事务的隔离级别
  读未提交(read uncommitted):一个现象解决不了
  读已提交(read committed):
解决脏读

  可重复读(repeatable read):解决脏读,不可重复读(MySQL默认)
  串行化(serializable):解决所有不好的现象:脏读,不可重复读,幻读
  事务隔离级别与一致性的关系


 1、乐观锁
  想法很乐观,认为 这次的操作不会导致冲突。
 2、悲观锁
  悲观锁就是在操作数据时,认为此操作会出现数据冲突。
 3共享锁与排它锁
  共享锁
   也称为读锁,允许事务读取一行数据。
    1.A为某条数据加共享锁,他可以查询修改。其他事物只能查询,直到A释放。
    2.A为某条数据加共享锁,B同时也为该数据加共享锁,A和B互相制约都只能查询。
    3.加了共享锁以后不能再加排它锁,只能加共享锁。
  排它锁
   也称写锁,允许事务删除或更新一行数据。
 注意!
  共享锁之间是兼容的,而排它锁与其他任意锁都不兼容。因此我们在数据库中可以并行读,但只能串行写,只有这样才不会发生线程竞争。
 锁的粒度
  锁的粒度就是你要锁定的范围是多大。使用锁时,锁定的东西越少,对其他竞争者影响越小,这个东西的大
小就是锁粒度

 

mysql高级 索引、事务、锁

上一篇:Oracle数据库备份和恢复


下一篇:Oracle重命名sys或system操作记录