赤水网站建设,网络游戏网站开发,wordpress更改后台登陆地址,域名购买网索引
索引是一个列表 —— 若干列集合和这些值的记录在数据表存储位置的物理地址
作用
加快检索速度唯一性索引 —— 保障数据唯一性加速表的连接分组和排序进行检索的时候 —— 减少时间消耗
一般建立原则
经常查询的数据主键外键连接字段排序字段少涉及、重复值多的字段…索引
索引是一个列表 —— 若干列集合和这些值的记录在数据表存储位置的物理地址
作用
加快检索速度唯一性索引 —— 保障数据唯一性加速表的连接分组和排序进行检索的时候 —— 减少时间消耗
一般建立原则
经常查询的数据主键外键连接字段排序字段少涉及、重复值多的字段不建立索引
MySQL中 InnoDB存储引擎支持索引
nameuse普通索引 INDEX值可空没有唯一性限制唯一值索引 UNIQUE值可空但唯一主键索引 PRIMARY KEY一个表只能由一个PK 系统自动创建全文索引 FULLTEXT在 varchar、char、text 类型的列上创建便于查询字符串类型
物理存储区分
聚集索引非聚集索引
创建 修改 删除 显示
CREATE INDEX
CREATE [UNIQUE | FULLTEXT | SPATIAL] INDEX 索引名[索引类型]
on 表名索引列名
[索引选项]
索引列名 :
列名[(长度)][ASC|DESC]SPATIAL表示为空间索引索引类型BTREE或HASH列名 CHARVARCHAR, length可以小于字段实际长度 —— 减少索引文件BLOB和TEXT类型必须指定 length可包含属于同一个表的多个列用逗号分开 —— 复合索引 没有PK索引 ALTER TABLE语句可以修改表定义包括向表中添加索引 ALTER TABLE tbl_name ADD PRIMARY KEY | UNIQUE | INDEX | FULLTEXT (column_list)
删除
DROP INDEX 索引名 ON 表名
Alter TABLE 表名 ...
DROP PRIMARYKEY
| DROP {INDEX | KEY} 索引名
| DROP FOREIGN KEY 外键名显示
SHOW INDEXES FROM tbl_name;
SHOW INDEXES FROM tbl_name IN db_name;
# *INDEX和KEY是INDEXES同义词返回
nameusetable表的名称NON_UNIQUE如果索引可以包含重复项则为1;如果可以则为0。KEY_NAME索引的名称。主键索引始终具有PRIMARY名称。seq_in_index索引中的列序列号。第一列序列号从1开始。column_name列名称。collation排序规则表示列在索引中的排序方式。A表示升序B表示降序NULL表示未分类。cardinality基数返回索引中估计的唯一值数。请注意基数越高 —— 查询优化器使用索引进行查找的可能性就越大。sub_part索引前缀。如果对整个列编制索引则为null。否则它会显示部分索引列的索引字符数。packed表示密钥是如何打包的。nullYES——如果列可能包含NULL值如果不包含空值则为空。INDEX_TYPE表示使用诸如索引方法BTREEHASHRTREE或FULLTEXT。comment有关索引的信息未在其自己的列中描述index_comment显示使用COMMENT属性创建索引时指定的索引的注释。¤visible索引是否对查询优化器可见或不可见; YES 是NO 不是。expression如果索引使用表达式而不是列或列前缀值则表达式指示键部分的表达式并且column_name列也为NULL。
索引的使用情况
建议使用
唯一性的限制比如用户名频繁用WHERE查询字段GROUP BY和ORDER BY的列UPDATE、DELETE的WHERE条件列(类似2)DISTINCT字段需要创建索引
不建议使用
WHERE GROUPBY ORDERBY 未出现的字段记录较少的表 1000个大量重复数据比重偏差小频繁更新的字段 字段频繁更新导致索引更新效率慢
失效
对索引进行 表达式计算使用函数使用LIKE且前缀为%
注意
多表JOIN连接操作时
连接表尽量不超3张对WHERE条件创造index于连接的字段创建索引并且该字段在多张表中的类型必须一致
索引列尽量设置为 NOT NULL 约束使用联合索引的时候要注意最左原则
从左到右的使用索引中的字段一条 SQL 语句可以只使用联合索引的一部分但要从最左侧开始否则失效当遇到范围查询(、、between、like)就会停止匹配
最左匹配原则
EXPAIN
nameuse¤ id选择标识符¤ select_type表示查询的类型¤ table输出结果集的表¤ partitions匹配的分区¤ type访问类型常用的有 ALL、index、range、 ref、eq_ref、const、system、NULL从左到右性能从差到好¤ possible_keys表示查询时可能使用的索引为NULL表示没有相关索引¤ key表示实际使用的索引如果为NULL表示没有选择索引¤ key_len索引字段的长度不损失精确性的情况下长度越短越好¤ ref列与索引的比较¤ rows扫描出的行数(估算的行数)¤ filtered按表条件过滤的行百分比¤ Extra执行情况的描述和说明
事务管理
MySQL 4.1开始支持事务事务由作为一个单独单元的一个或多个SQL语句组成。
这个单元中的每个SQL语句是互相依赖的而且单元作为一个整体是不可分割的不能完成整个单元就会回滚事务中的所有语句都成功的执行这个事务才被成功地执行
提交
当一个会话开始时系统变量AUTOCOMMIT值为1即自动提交功能是打开的
任意一条SQL语句发送到服务器时MySQL服务器会立即解析、执行并将更新结果提交到数据库文件中
在执行事务时要首先关闭MySQL的自动提交使用命令“set autocommit0;”可以关闭MySQL的自动提交
当MySQL关闭自动提交后可以使用COMMIT命令来完成事务的提交也标志transaction的结束使用命令“start transaction;”可以开启一个事务 —— 隐式关闭MySQL的提交
注意
transaction不能嵌套 —— 开始第二个事务会自动提交第一个事务下面语句会隐式执行commit
set autocommit1、rename table、truncate tablecreate、alter、dropgrant、revoke、set password、create user、drop user、rename userlock tables、unlock tables
example
set autocommit0;
insert into account values(111,500);
commit;
insert into account values(222,500);
create table student(
studentid char(6) primary key,
name varchar(10),
sex char(2)
)engineinnodb;
insert into account values(333,500);
select * from account;在上面SQL语句执行过程中
首先使用命令“set autocommit0;”关闭 MySQL的自动提交。插入第一条记录后使用commit命令完成事务的提交。当插入第二条记录后使用create命令创建数据表由于create命令在执行时会隐式地执行commit命令所以插入的第二条记录也会被提交。当插入完第三条记录时使用select语句查询到的是内存中的记录所以查询结果可以看到新添加的三条记录。
由于最后一条语句并没有提交所以该值并没有写到数据库文件中。另一客户机执行查询时看到的是外存数据库文件在服务器内存中的一个副本所以只查询到两条添加记录当前客户机使用commit命令提交事务后两个客户机看到的查询结果是相同的
回滚
销未提交的事务所做的各种修改操作并结束当前这个事务
若只撤销一部分可以用“部分回滚”
savepoint 保存点名;”可以在事务中设置一个保存点使用“rollback to savepoint 保存点名;”可以将事务回滚到保存点状态
四大特性和隔离级别
四大特性
事务是一个单独的逻辑工作单元事务中的所有更新操作要么都执行要么都不执行。事务保证了一系列更新操作的原子性。如果事务与事务之间存在并发操作则可以通过事务之间的隔离级别来实现事务的隔离从而保证事务间数据的并发访问。
ACID ATOMICITY CONSISTENCY ISOLATION DURABILITY
原子性意味着每个事务都必须被认为是一个不可分割的单元事务中的操作必须同时成功事务才是成功的。如果事务中的任何一个操作失败则前面执行的操作都将回滚以保证数据的整体性没有受到影响事务的一致性保证了事务完成后数据库能够处于一致性状态。如果事务执行过程中出现错误那么数据库中的所有变化将自动地回滚回滚到另一种一致性状态
由MySQL的日志机制处理它记录了数据库的所有变化为事务恢复提供了跟踪记录如果系统在事务处理中发生错误MySQL恢复过程将使用这些日志来发现事务是否已经完全成功地执行是否需要返回
事务的隔离性确保多个事务并发访问数据时各个事务不能相互干扰 的每个事务在自己的空间执行并且事务的执行结果只有在事务执行完才能看到 —— 其他事务暂时看不到结果 可以使用页级锁定或行级锁定来隔离事务的持久性意味着事务一旦提交其改变会永久生效不能再被撤销。 —— 即使系统崩溃一个提交的事务仍然存在
隔离级别
从低到高分别是
read uncommitted读取未提交的数据
read committed读取提交的数据
repeatable read可重复读
serializable串行化。
read uncommitted读取未提交的数据提供了事务之间的最小隔离程度处于这个隔离级别的事务可以读到其他事务还没有提交的数据read committed读取提交的数据处于这一级别的事务可以看见已经提交事务所做的改变repeatable read可重复读这是MySQL默认的事务隔离级别它确保在同一事务内相同的查询语句其执行结果总是相同的(即使某个事务突然改了某个数据而且事务还没结束前后查询的内容还是一样的)serializable串行化 最高级别的隔离它强制事务排序使事务一个接一个地顺序执行
解决多用户问题
用户对数据库并发访问时为了确保事务完整性和数据库一致性需要使用锁定 —— 防止用户读取正在由其他用户更改的数据并可以防止多个用户同时更改相同数据 高级别的事务隔离 —— 有效地实现并发但会降低事务并发访问的性能 低级别的事务隔离可以提高事务的并发访问性能但可能导致并发事务中的脏读、不可重复读和幻读等问题
三个问题
脏读不可重复读幻读read uncommitted✔✔✔read committed×✔✔repeatable read××✔serializable×××
脏读
一个事务可以读到另一个事务未提交的数据
打开MySQL客户机A将当前MySQL会话的事务隔离级别设置为read uncommitted。 set session transaction isolation level read uncommitted开启事务查询账号为“111”账户的余额。打开MySQL客户机B将当前MySQL会话的事务隔离级别设置为read uncommitted。开启事务将账号为“111”账户余额增加800。在MySQL客户机中查看账号为“111”账户的余额。关闭MySQL客户机和客户机后再查看账号为“111”账户的余额。
这个时候 A 读到了B未结束事务但已更新的结果也就是加了 800
不可重复读
同一个事务中两条相同的查询语句其查询结果不一致
一个事务访问数据时另一个事务对该数据进行修改并提交导致第一个事务两次读到的数据不一样
将MySQL客户机与客户机使用语句“set session transaction isolation level read committed;”将他们的隔离级别都设置为read committed。与例6-5相同首先在MySQL客户机中查询账号为“111”账户的余额。在MySQL客户机中将账号为“111”账户余额增加800未提交事务时在MySQL客户机中查询账号为“111”账户的余额对比是否出现脏读。MySQL客户机中提交事务后在MySQL客户机中查询账号为“111”账户的余额对比是否出现不可重复读。
A读到的数据是B事务提交后的数据
幻读
当前事务读不到其他事务已经提交的修改别人已经改了而且提交事务了而你的读的内容还是修改之前的
将MySQL客户机与客户机使用语句“set session transaction isolation level repeatable read;”将他们的隔离级别都设置为repeatable read。在MySQL客户机中开启事务并查询账号为“999”的账户信息。在MySQL客户机B中开启事务插入一条账户信息999,700然后提交事务。在MySQL客户机中再次查账号为“999”的账户信息判断是否可以避免不可重复读。在MySQL客户机中插入账户信息999,700并判断是否可以插入
