索引 索引是对数据库表中一列或多列的值进行排序的数据结构,他们的底层使用的都是BTree,使用索引可以快速定位到特定的信息,提高查找效率。 创建索引分为以下几种方式:
1 2 3 4 5 6 create index on tablename(line1,line2);//直接创建索引 alert table tablename add index indexname(line);//修改表结构 create table tablename( id int not null, index indexname(linename) );
分类如下
普通索引 最基本的索引之一,没有唯一性的限制。 创建方式:create index indexname on tablename(line1,line2,...)
唯一索引 在普通索引的基础上,增加了唯一性的限制,即索引列中不可能出出现相同的值。 创建方式:create unique index indexname on tablename(line1,line2,...)
主键索引 在主键上增加索引,在MySQL中默认会为主键增加索引,如果没有指定主键,则会将默认生成一个以行号为标志的索引。具有唯一性且不为空。 主键索引也是一种聚集索引。
联合索引 primary key(id,name):联合主键索引 unique(id,name):联合唯一索引 index(id,name):联合普通索引
全文索引 当一条数据的某个字段可能会很长的时候,如果直接进行匹配,消耗的时间是比较多的,因此引入全文索引,它通过对每个词的建立倒排索引的方式进行快速查找,每次查找时只需要根据其索引中是否存在查找的词即可。 创建索引的方法如下:
1 2 fulltext key content_index(content)//在创建表时添加索引 create fulltext index content_tag_fulltext on fulltext_test(content,tag);//对已有的表创建全文索引,支持全文索引
使用全文索引查询时,可使用如下方法查询匹配最小搜索长度:
1 2 select * from fulltext_test where match(content,tag) against('xxx xxx');
使用全文索引查询的速度比like+%的模糊查询要快很多,当文本内容较大时尤其明显,但是有可能出现精度问题,因此当文本数据大时应使用全文索引 使用全文索引时应该保证数据数量大于4,否则查询可能遇到意想不到的结果 查询的字段长度应在一定范文内,在INNODB(MySQL5.6版本后支持)中长度为(3.84),在MYISAM中为(4,84) 查询时又分为自然语言查询和布尔全文索引,自然语言查询匹配相关度,查询对应的单词出现的次数,布尔查询可以支持通配符查询等控制关联度查询。
聚集索引 建立索引后数据的物理关系和索引的逻辑关系是相同的。
覆盖索引 指查询时索引覆盖的查询条件的内容,只需要通过索引就可以获取到查询的内容的情况
数据库事物 指单个逻辑单元执行一系列操作,这些操作要么全部执行,要么全部不执行,它确保每次处理成为一个整体。
ACID原则
原子性(Atomicity) 原子性是指事务包含的所有操作要么全部成功,要么全部失败回滚,因此事务的操作如果成功就必须要完全应用到数据库,如果操作失败则不能对数据库有任何影响。
一致性(Consistency) 一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态,也就是说一个事务执行之前和执行之后都必须处于一致性状态。 拿转账来说,假设用户A和用户B两者的钱加起来一共是5000,那么不管A和B之间如何转账,转几次账,事务结束后两个用户的钱相加起来应该还得是5000,这就是事务的一致性。
隔离性(Isolation) 隔离性是当多个用户并发访问数据库时,比如操作同一张表时,数据库为每一个用户开启的事务,不能被其他事务的操作所干扰,多个并发事务之间要相互隔离。 即要达到这么一种效果:对于任意两个并发的事务T1和T2,在事务T1看来,T2要么在T1开始之前就已经结束,要么在T1结束之后才开始,这样每个事务都感觉不到有其他事务在并发地执行。 多个事务并发访问时,事务之间是隔离的,一个事务不应该影响其它事务运行效果。这指的是在并发环境中,当不同的事务同时操纵相同的数据时,每个事务都有各自的完整数据空间。由并发事务所做的修改必须与任何其他并发事务所做的修改隔离。 不同的隔离级别: Read Uncommitted(读取未提交内容):最低的隔离级别,什么都不需要做,一个事务可以读到另一个事务未提交的结果。所有的并发事务问题都会发生。 Read Committed(读取提交内容):只有在事务提交后,其更新结果才会被其他事务看见。可以解决脏读问题。 Repeated Read(可重复读):在一个事务中,对于同一份数据的读取结果总是相同的,无论是否有其他事务对这份数据进行操作,以及这个事务是否提交。可以解决脏读、不可重复读。 Serialization(可串行化):事务串行化执行,隔离级别最高,牺牲了系统的并发性。可以解决并发事务的所有问题。
持久性(Durability) 持久性是指一个事务一旦被提交了,那么对数据库中的数据的改变就是永久性的,即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。 例如我们在使用JDBC操作数据库时,在提交事务方法后,提示用户事务操作完成,当我们程序执行完成直到看到提示后,就可以认定事务以及正确提交,即使这时候数据库出现了问题,也必须要将我们的事务完全执行完成,否则就会造成我们看到提示事务处理完毕,但是数据库因为故障而没有执行事务的重大错误。
MySQL如何保证ACID原则 原子性:通过undo log进行回滚。 隔离性:通过四个隔离级别保证。 持久性:redo log记录了每次提交的操作,每次提交后不仅更新内存中的数据,此时磁盘中的数据可能还没有发生改变,还进行刷盘更新磁盘中的redo log,当出现宕机时,只需要通过redo log进行恢复。
事务隔离级别
读未提交内容:在其他事务执行一系列操作并且还没有提交时,本事物已经获取了这一系列操作的结果,并且会在后续的读取中读取到这些内容,这种操作很明显的暴露出来了一个缺点,由于事物是支持回滚的,如果另一个事务执行操作后因为某种原因回滚到执行前的状态,但是本事物并不知道另一个事务已经回滚,则后续读出来的数据可能就是“脏数据”,因此出现了脏读的问题。
读提交内容:为了解决上述问题,本隔离级别不允许事务获取其他事务未提交的内容,使得如果其他事务出现了回滚操作时结果是一样的,这也是许多数据库使用的隔离级别,但如果本事务在执行的时候其他事务提交了内容,那么本次事务在另一事务提交前后读取到的内容是不一样的,这种现象称之为不可重复读取。
可重复读:为了解决不可重复读的问题,采取MVCC版本控制机制进行版本控制,当一个事务不进行更新等可能修改数据的操作时,事务读取的数据始终停留在原来的版本,无论其他事务是否提交了修改数据指令,本事务两次读取的内容都控制在同一个版本中,因此重复读取到的结果是一样的。当本次事务出现了数据更新时,数据库将更新操作应用于当前表格的最新版本上,同时将版本设置为修改数据后的版本,这样也保证了并发事务间的同步。但是当本事务两次读取行为间增加一个更新操作,而更新操作执行前另一个事务提交了一个插入操作,这个更新不会应用到插入和行中,就好像产生了幻觉一样,则称这种现象为“幻读”。
串行化读取:为了解决幻读问题,通过使用锁机制对数据进行访问控制,具体使用的锁为行锁和范围锁,当某一事务执行了读取操作后,会将读取的部分“锁定”,即锁住对应的行(行锁)不允许更改,也锁定读取的范围(范围锁),不允许插入操作,使得其他事务无法对读取到的部分进行任何操作从而解决这一问题。
存储引擎 对于MySQL来说,最常用的两个引擎分别是MAISAM和INNODB。
INNODB 事务型存储引擎,有行级锁和外键约束。 提供数据库ACID事务支持,实现了SQL标准四种隔离级别,不保存表的行数,统计行数时要访问整个表,锁的粒度小,写操作不会锁定全表。 使用场景:适用于经常更新的表,适合处理多重并发请求。 特点:B+树数据存储索引值,因此是聚集索引。
MYISAM MySQL的默认引擎,不提供数据库事务,也不提供行级锁和外键,因此在执行写操作时会锁定整个表。 独立于操作系统,可以比较简单的进行数据转移。 特点:查询速度快,表保存了行数,统计行数时不需要访问整个表,只需要输出表的行数即可,但是如果加了where条件,一样需要遍历整个表。索引的B+树数据域存储实际数据地址,因此是非聚集索引。
二者区别
事务:MyISAM类型不支持事务处理等高级处理,而InnoDB类型支持,提供事务支持已经外部键等高级数据库功能。
性能:MyISAM类型的表强调的是性能,其执行速度比InnoDB类型更快。
行数保存:InnoDB 中不保存表的具体行数,也就是说,执行select count() from table时,InnoDB要扫描一遍整个表来计算有多少行,但是MyISAM只要简单的读出保存好的行数即可。注意的是,当count()语句包含where条件时,两种表的操作是一样的。 因为InnoDB是事务型的,每个事务的行数可能不同。
索引存储:InnoDB使用聚簇索引且支持外键,而MyISAM是非聚簇索引且不支持外键,因此InnoDB必须要有一个主键索引,辅助索引需要通过主键索引二次查找,如果没有主键索引会产生自增的行号作为索引。MyISAM可以不增加主键索引,主键索引和辅助索引相互独立。
服务器数据备份:InnoDB必须导出SQL来备份,LOAD TABLE FROM MASTER操作对InnoDB是不起作用的,解决方法是首先把InnoDB表改成MyISAM表,导入数据后再改成InnoDB表,但是对于使用的额外的InnoDB特性(例如外键)的表不适用。 MyISAM应对错误编码导致的数据恢复速度快。MyISAM的数据是以文件的形式存储,所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作。 InnoDB是拷贝数据文件、备份 binlog,或者用 mysqldump,在数据量达到几十G的时候就相对痛苦了。
锁的支持:MyISAM只支持表锁。InnoDB支持表锁、行锁 行锁大幅度提高了多用户并发操作的新能。但是InnoDB的行锁,只是在WHERE的主键是有效的,非主键的WHERE都会锁全表的。
三大范式 第一范式 列不可再分
第二范式 不存在非主属性对码的部分依赖
第三范式 不存在非主属性对码的部份依赖和传递依赖
BCNF 不存在主属性对码的部份依赖和传递依赖
为什么MySQL要用B+树而不是B树或者红黑树 不选择红黑树的理由 相同数据量下红黑树高度过高,进行搜索时io次数多很多。 io次数多的理解:由于数据库中的数据都是存在于硬盘中的,想要对数据进行查找时就需要读取硬盘中的信息,数据很大时并不能一次读取所有信息,因此必须层层查找,每一次获取一个节点,并将节点中的信息与要查找的信息作对比,获得下一个需要得到的节点,如果使用红黑树,io的复杂度高达log(n),而使用B+树,只需要几次io即可找到。
不选择B树
由于B树的非叶子节点也会存放数据,因此相同内存大小下B树所能存放的节点更少,导致相同内存B树储存的数据量小。
B树范围索引需要跨层查找。
B+树的特点
所有数据存在叶子节点中,非叶子节点只存关键字和子节点地址,因此每个节点能够存放的子节点数量更大。
各个叶子节点最后通过链表的形式连接起来,对于链表来说,数据是有序的,因此B+树的查找类似一次二分查找,且支持范围查找
由于数据都存放在叶子节点中,查找的速度十分稳定,可以认为用固定次数即可查找出来。
MySQL中的锁 乐观锁 乐观锁对并发保持乐观态度,认为数据不存在并发冲突,只在事务提交前检查数据是否被其他事务更改,一般采用版本号机制或CAS(比较并交换)实现。
悲观锁 悲观锁对并发持悲观态度,认为并发很容易对出现冲突,每次修改数据时都对数据进行加锁。
行锁 行锁是通过索引上的索引项加锁来实现的,只有通过索引条件检索数据,innoDB才使用行锁,否则将使用next-key lock
间隙锁 又叫范围锁,锁定一个范围,范围内不可插入和修改数据,用来解决幻读时有用。
next-key lock 使用行锁+间隙锁实现加锁
排他锁 又叫写锁,在执行写入操作时将表格锁住不允许其他事务操作。 在select语句后加for update可实现查询时加排他锁
共享锁 又叫读锁,可以被多个事务共同获取,但是被完全释放前数据不可被修改,即只允许读取。 在select语句后面加in share mode可实现查询时加共享锁
回表 辅助索引的叶子节点只存放存储列值和主键id,想要查找数据还需要在主键中二次查找 解决方法——覆盖索引:将要查找的字段添加到联合索引中
联合索引的最左原则 从左到右依次作为索引条件,但是遇到范围查询将终止。
主键非自增带来的问题 会使得主键索引的插入效率降低,因为当插入数据的主键应在某个已经满的块中时,需要移动数据块后插入。
SQL优化
为经常需要查询、链接、集合操作和group by或order by的字段建立索引
避免使索引失效的语句,’or’ ‘!=’ ‘link’
避免使用select *
使用覆盖索引避免二次查询
使用join代替嵌套查询,嵌套查询会产生临时表,开销大
小表驱动大表,先查小表再查大表
避免使用null(索引失效)
索引失效的情况
有or
复合索引未用到左列字段
like以%开头
需要类型转换
where中索引列有运算/函数
MySQL绑定变量 当你可能需要指令大量重复且类似的指令时,如果这些指令只有部分参数不同,可以使用绑定变量简化操作,用户只需要提交参数给服务器就可完成操作,大大提高的信息传输的效率。
当创建一个绑定变量 SQL 时,客户端会向服务器发送一个SQL语句的原型。服务器端收到这个SQL语句框架后,解析并存储这个SQL语句的部分执行计划,返回给客户端一个 SQL 语句处理句柄。以后每次执行这类查询,客户端都指定使用这个句柄。 绑定变量的SQL,使用问号标记可以接受参数的位置,当真正需要执行具体查询的时候,则使用具体值来替代这些问号。例如,下面是一个绑定变量的SQL语句:INSERT INTO tb1(col1, col2, col3) VALUES(?,?,?);
可以通过向服务器端发送各个问号的取值和这个SQL的句柄来执行一个具体的查询。反复使用这样的方式执行具体的查询,正是使用绑定变量的优势所在。具体如何发送取值参数和SQL句柄,这各个客户端的编程语言有关。
因为如下的原因,MySQL可以在使用绑定变量的时候高效执行大量的重复语句:
在服务器端只需要执行一次SQL语句
在服务器端某些优化器的工作只需要执行一次,因为它会缓存一部分的执行计划
以二进制的方式只发送参数和句柄,比每次都发送 ASCII 码文本效率更高
仅仅是参数而不是整个查询语句,因此网络开销会更小
MySQL在存储参数的时候,直接将其存放到缓存中,不再需要在内存中多次复制 绑定变量相对也更加安全。因为无需在应用程序中处理转义,一则更加简单了,二则也大大减少了SQL指令被攻击的风险。
绑定变量的优化 对于绑定变量的SQL,MySQL 能够缓存其部分执行计划,如果某些执行计划需要根据传入的参数来计算时,MySQL就无法缓存这部分的执行计划。根据优化器什么时候工作,可以将优化分为三类:
准备阶段 服务器解析SQL语句,移除不可能的条件并且重写子查询
在第一次执行的时候 如果可能,服务器先简化嵌套循环的关联,并将外关联转化成内关联
在每次SQL语句执行时 服务器会做如下事情:
过滤分区
如果可能的话,尽可能移除COUNT()、 MIN() 和 MAX()
移除常数表达式
检测常量表
做必要的等值传播
分析和优化 ref、 range和索引优化等访问数据的方法
优化关联顺序
在4.1 和更新版本中,MySQL 支持了SQL接口的绑定变量。不使用二进制传输协议也可以直接以SQL 的方式使用绑定变量: sql接口的绑定变量
1 2 3 prepare actor_name from 'select * from table_name where id=?';//创建绑定变量SQL set @test_id := 1;//创建变量 execute actor_name using @test_id;//使用绑定变量
SQL接口的绑定变量的最主要用途就是在存储过程中使用。在MySQL5.0 版本中,这就可以在存储过程中使用绑定变量,其语法和前面介绍的SQL接口的绑定变量类似。这意味着可以在存储过程中构建并且执行动态的SQL语句,即可以通过灵活的拼接字符串等参数构建SQL语句。
绑定变量的限制 关于绑定变量的一些限制和注意事项如下:
绑定变量是会话级别的,所以链接之间不能公用绑定变量句柄。同样的,一旦连接断开,则原本的句柄也不能再使用了。(连接池和持久化连接可以在一定程度上缓解这个问题)
并不是所有的时候使用绑定变量都能获得更好的性能。如果只是执行一次的SQL,那么使用绑定变量方式无疑比直接执行多了一次额外的准备阶段消耗,而且还需要一次额外的网络开销。
绑定变量SQL总数的限制是一个全局限制,如果总是忘记释放绑定变量资源,则在服务器端很容易发生资源“泄漏”。
不过使用绑定变量最大的障碍可能是:它是如何实现以及原理是什么样的,这两点很容易让人困惑。有时候,很难解释如下三种绑定变量类型之间的区别是什么:
客户端模拟的绑定变量 客户端的驱动程序接收一个带参数的SQL,再将指定的值带入其中,最后将完整的查询发送到服务器端。
服务器端的绑定变量 客户端使用特殊的二进制协议将带参数的字符串发送到服务器端,然后使用二进制协议将具体的参数值发送给服务器端并执行。
SQL接口的绑定变量 客户端先发送一个带参数的字符串到服务器端,这类似于使用 PREPARE 的 SQL 语句,然后发送设置参数的SQL,最后使用 EXECUTE 来执行SQL。所有这些都是使用普通的文本传输协议。
redis 跳表 在有序链表的基础上,通过层层索引的方式提高查询效率。 每一层索引都是在前一层的基础上随机选取一半的数据作为下一级的索引,因此查询效率可以降低至log n,且每个节点都有两个下一级指针,一个指向下一层的节点一个指向同一层右边的节点,根节点为最上层最左节点。
跳表相对于红黑树 跳表可以支持范围查询,只需要找到范围的开头和结尾节点即可,而红黑树需要找到每个范围内的节点。 且跳表实现更加简单,不容易出错。
跳表相对于B+树 跳表和红黑树一样,都是在基于内存时效率高,而B+树基于硬盘时效率高(硬盘内B+树io次数更少,但内存内查询时间复杂度高于跳表等)
MySQL层次结构 原文链接 MySQL分为连接层、服务层、引擎层和存储层,每一层具体功能如下:
连接层 功能:管理用户连接,为连接分配权限 注意事项:
登录成功后,用户权限就被用户获取,如果权限被修改,重新连接后才能生效
连接分为长连接、短链接,
服务层 提供用户使用的接口和优化器,用于对用户请求提供服务。
引擎层 提供存储引擎,不同引擎可能使用不同的存储方式。
存储层 用于存放数据。
MySQL日志 error_log(错误日志) 记录运行过程中的Error、Worning、Note信息 可通过修改/etc/my.conf中添加–error-log = [filename]开启错误日志 可使用show variables like "log_error";
查看错误日志路径
General Query Log(日常日志) 记录使用的sql语句,报错select、delete和update 如果开启日志功能但不能成功写入日志,可能是因为权限不够,运行如下指令赋予权限:chown mysql:mysql /tmp/mysql_query.log
最后是日志地址,可用查询错误日志的方式查询地址。
Bing Log(二进制日志) 记录所有可能修改表格的sql语句,属于逻辑日志,可以用来查看数据库变化情况。
Slow Query Log(慢查询日志) 用于记录查询时间超过一定时间的查询记录,可以知道哪些查询语句执行速度较慢,便于优化。
redolog innoDB引擎的日志,用于记录事务操作的变化,记录修改后的值,不论事务是否提交,都会被记录。 每个事务在提交到数据库前都先在redolog中记录,如果因为某种原因出现异常,则通过redolog进行数据恢复。 属于物理日志,记录的是每次修改的值。 redolog有大小限制,当写到结尾时会回到开头循环写。
undolog 保证事务原子性的关键,所有的更新语句都会在undolog中存放他的反向指令,如insert对应delete,当事务需要回滚时,就通过undolog进行回滚。 同时在MVCC机制中也使用undolog回退版本。