一、索引
1、索引简介
- 什么是索引?
一般的应用系统,读写比例在 10:1 左右,而且插入操作和一般的更新操作很少出现性能问题,在生产环境
中,我们遇到最多的,也是最容易出问题的,还是一些复杂的查询操作,因此对查询语句的优化显然是重中之重。
说起加速查询,就不得不提到索引了。 - 为什么要有索引?
索引在 MySQL 中也叫做“键”,是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关
键,尤其是当表中的数据量越来越大时,索引对于性能的影响愈发重要。
索引优化应该是对查询性能优化最有效的手段了。善用索引能够轻易将查询性能提高好几个数量级。
索引相当于字典的音序表,如果要查某个字,如果不使用音序表,则需要从几百页中逐页去查。 - 索引的优劣势:
索引的优势:可以快速检索,减少 I/O 次数,加快检索速度;根据索引分组和排序,可以加快分组和排序。
索引的劣势:索引本身也是表,因此会占用存储空间,一般来说,索引表占用的空间是数据表的 1.5 倍;索
引表的维护和创建需要时间成本,这个成本随着数据量增大而增大;构建索引会降低数据表的修改操作(删除,
添加,修改)的效率,因为在修改数据表的同时还需要修改索引表。
2、MySQL 中索引的使用 - 创建索引
索引可以在创建表时创建,也可以在创建表之后添加。
任务 1-1:在创建表时创建索引,代码如下所示:
CREATE TABLE mytable(
ID INT NOT NULL, username VARCHAR(16) NOT NULL, INDEX myindex (username(16))
);
语法解析:代码中的 index 表示索引,myindex 表示定义的索引名称。
注意:如果是 CHAR,VARCHAR 类型,length 可以小于字段实际长度;如果是 BLOB 和 TEXT 类型,必须
指定 length。
任务 1-2:在创建表之后创建索引,代码如下所示:
CREATE INDEX myindex ON mytable(username(16));
或者
ALTER table mytable ADD INDEX myindex(username) - 查看索引
创建索引后可以使用下面语句查看索引。4
任务 1-3:查看表中的索引,代码如下所示:
SHOW INDEX FROM mytable - 删除索引
删除索引可以使用 drop 或者 alter 命令来完成。
任务 1-4:删除表中索引,代码如下所示:
DROP INDEX myindex ON mytable;
或者
ALTER TABLE mytable DROP INDEX myindex;
语法分析:代码中的 myindex 是创建索引时定义的索引名称。 - 通过 EXPLAIN 分析 SQL 是否使用到索引
任务 1-5:通过 explain 命令查看索引使用情况,代码如下所示:
explain select * from mytable where username = ‘jack’ 运行结果如下:
注意:如果 key 值为索引名称,则该查询使用了索引,如果 key 值为 null,则表示没有使用索引。
3、索引的使用规则 - 索引分类
常见的索引类型有:主键索引、唯一索引、普通索引、全文索引、组合索引。
主键索引:即主索引,根据主键 pk_clolum(length)建立索引,不允许重复,不允许空值;
唯一索引:用来建立索引的列的值必须是唯一的,允许空值;
普通索引:用表中的普通列构建的索引,没有任何限制;
全文索引:用大文本对象的列构建的索引;
组合索引:用多个列组合构建的索引,这多个列中的值不允许有空值。 - 适合建立索引的情况
主键自动建立唯一索引;
经常作为查询条件在 WHERE 或者 ORDER BY 语句中出现的列要建立索引;
作为排序的列要建立索引;
查询中与其他表关联的字段,外键关系建立索引;
高并发条件下倾向组合索引;
用于聚合函数的列可以建立索引,例如使用了 max(column_1)或者 count(column_1)时的 column_1 就需
要建立索引。 - 不适合建立索引的情况5
经常增删改的列不要建立索引;
有大量重复的列不建立索引;
表记录太少不要建立索引。只有当数据库里已经有了足够多的测试数据时,它的性能测试结果才有实际
参考价值。如果在测试数据库里只有几百条数据记录,它们往往在执行完第一条查询命令之后就被全部
加载到内存里,这将使后续的查询命令都执行得非常快–不管有没有使用索引。只有当数据库里的记录
超过了 1000 条、数据总量也超过了 MySQL 服务器上的内存总量时,数据库的性能测试结果才有意义。 - 索引失效的情况
在组合索引中不能有列的值为 NULL,如果有,那么这一列对组合索引就是无效的;
在一个 SELECT 语句中,索引只能使用一次,如果在 WHERE 中使用了,那么在 ORDER BY 中就不要用了;
LIKE 操作中,’%aaa%'不会使用索引,也就是索引会失效,但是‘aaa%’可以使用索引;
在索引的列上使用表达式或者函数会使索引失效;
在查询条件中使用不等于,包括<符号、>符号和!=会导致索引失效;
在查询条件中使用 IS NULL 或者 IS NOT NULL 会导致索引失效;
字符串不加单引号会导致索引失效;
在查询条件中使用 OR 连接多个条件会导致索引失效,除非 OR 链接的每个条件都加上索引;
如果排序的字段使用了索引,那么 select 的字段也要是索引字段,否则索引失效;
尽量不要包括多列排序,如果一定要,最好为这队列构建组合索引。
具体查看索引是否有效,可以使用 explain 命令查询。 二、事务
1、事务简介 - 什么是事务?
事务(Transaction):一个最小的不可再分的工作单元;通常一个事务对应一个完整的业务(例如银行账户转
账业务,该业务就是一个最小的工作单元)。
事务只和 DML 语句有关,或者说 DML 语句才有事务。 - 转账操作理解事务
以下是银行账户表 t_act(账号、余额),进行转账操作。
actno balance
1 500
2 100
任务 2-1:使用 update 语句实现转账操作,实现账号 1 向账号 2 转账 100 元。
分析:转账操作,需要账号 1 余额减少 100 元,同时需要账号 2 余额增加 100 元。代码如下所示:
update t_act set balance=balance-100 where actno=1;
update t_act set balance=balance+100 where actno=2;
在一个转账业务中,以上两条 DML 语句必须同时成功或者同时失败。最小单元不可再分,当第一条 DML6
语句执行成功后,并不能将底层数据库中的第一个账户的数据修改,只是将操作记录了一下;这个记录是在内存
中完成的;当第二条 DML 语句执行成功后,和底层数据库文件中的数据完成同步。若第二条 DML 语句执行失败,
则清空所有的历史操作记录,要完成以上的功能必须借助事务。
2、MySQL 中事务的使用
在 mysql 中,事务的常规操作包括:开启事务、提交事务与回滚事务。 - 开启事务
开启事务的语法如下所示:
start transaction;
或者
begin transaction; - 提交事务
提交事务的语法如下所示:
commit; - 回滚事务
回滚事务的语法如下所示:
rollback;
任务 2-2:手动开启事务,向 t_act 表中插入一行记录,提交事务。代码如下所示:
start transaction;-- 手动开启事务
insert into t_act values(3,300); – 更新操作
commit;-- 提交事务,commit 之后即改变底层数据库数据
select * from t_act;
运行结果如下:
看到这个结果,似乎和直接执行插入一条记录没什么不同。确实,在 MySQL 中,默认情况下,事务是自动提
交的,也就是说,只要执行一条 DML 语句就开启了事务,并且提交了事务。
任务 2-2:手动开启事务,向 t_act 表中插入一行记录,回滚事务。代码如下所示:
start transaction;-- 手动开启事务
insert into t_act values(4,200); – 更新操作
rollback;-- 回滚事务
select * from t_act;
运行结果如下:7
从运行结果中可以看到,回滚事务会撤销更新操作,代码执行前后数据表中的数据没有发生任何变化。
3、事务的特征
事务四大特征(ACID):
原子性(A):事务是最小单位,不可再分;
一致性©:事务要求所有的 DML 语句操作的时候,必须保证同时成功或者同时失败;
隔离性(I):同一时间,只允许一个事务请求同一数据,不同的事务之间彼此没有任何干扰;
持久性(D):是事务的保证,事务终结的标志(内存的数据持久到硬盘文件中)。
4、事务的隔离级别
事务的隔离级别有 4 个:
读未提交(read uncommitted):事务 A 和事务 B,事务 A 未提交的数据,事务 B 可以读取到,这里读
取到的数据叫做“脏数据”。这种隔离级别最低,一般是在理论上存在,数据库隔离级别一般都高于该
级别;
读已提交(read committed):事务 A 和事务 B,事务 A 提交的数据,事务 B 才能读取到。这种隔离级
别高于读未提交,可以避免“脏数据”。但如果事务 A 多次读取同一数据,而事务 B 在事务 A 多次读
取的过程中,对数据作了更新并提交,导致事务 A 多次读取同一数据时,结果不一致,这种现象称为“不
可重复读”;
可重复读(repeatable read):事务 A 和事务 B,事务 A 提交之后的数据,事务 B 读不到事务 A 提交
的数据。这种隔离级别高于读已提交,但如果事务 A 在修改数据的过程中(比如将所有记录状态设为 1),
如果事务 B 向同一张表中插入一条新记录(状态为 0),事务 A 提交后再次查询表,会发现有一条记录
状态没有改成 1,好像发生了幻觉,这种现象称为“幻读”。可重复读是 MySQL 默认隔离级别;
串行化(serializable):事务 A 和事务 B,事务 A 在操作数据库时,事务 B 只能排队等待。这种级别
可以避免“幻读”,每一次读取的都是数据库中真实存在数据,事务 A 与事务 B 串行,而不并发。
事务隔离级别与一致性的关系也可以用下表表示:
隔离级别 脏读 不可重复读 幻读
读未提交 ✔ ✔ ✔
读已提交 ❌ ✔ ✔
可重复读 ❌ ❌ ✔
串行化 ❌ ❌