MySQL02-事务和索引

MySQL02-事务和索引

1.MySQL事务的特性-ACID

  1. 原子性，Atomic。事务的所有操作要么全部成功，要么全部回滚，不能有中间状态。
  2. 一致性，Consist。经典案列转账，转账前后数据库的钱数总和保持一致。一致性表示，数据库总是从一个一致性的状态转换到另一个一致性的状态。
  3. 隔离性，Isolated。多个事务并发执行时，事务之间的操作相互隔离，互不影响。
  4. 持久性，Durable。已被提交的事务对数据库的修改应该永久保存在数据库中。

2.MySQL事务所带来的的问题

  1. 丢失更新。一个事务的更新操作会被另外一个事务的更新操作所覆盖，从而导致数据的不一致。
  2. 脏读。一个事务读取到了另外一个事务中未提交的数据。
  3. 可重复读指。一个事务多次读取同一行数据，但是多次读取的数据却不一样(数据的内容不一致)。导致这一问题的主要原因就是一个事务读取到了其他事务已提交的数据。
  4. 幻读。在同一事务中，用同样的操作读取两次，得到的记录数却不一样(数据的多少不一样)。

3.MySQL的隔离级别

  1. Read Uncommitted，读未提交。事务可以看到其他未提交事务的执行结果。
  2. Read Committed，读已提交。事务只能看见提交了的事务所做的改变。
  3. Repeatable Read，可重复读。MySQL中InnoDB默认的隔离级别，确保在同一个事务中的多次读取可以读到相同的结果。可重复读通过MVCC(多版本并发控制)和Gap Lock(间隙锁)来解决不可重复度和幻读的问题。
  4. Serializable，可串行化，最高的隔离级别。

4.MySQL索引的类型

  1. primary key，主键索引。每一个表中只能有一个。
  2. unique key，唯一索引。
  3. key，普通索引。
  4. fulltext，全文索引。
  5. hash，哈希索引。

5.MySQL常用命令-操作索引

  1. show index from tb_student，查看tb_student表的索引。
  2. 创建表示增加索引。
create table tb_user(
    `id` int not null auto_increment,
    `phone` varchar(20) default null,
    `name` varchar(10) default null,
    `address` varchar(100) default null,
    `age` int default '10',
    primary key (`id`), -- 主键索引
    unique index `index_phone`(`phone`) using btree, -- 唯一索引
    index `index_name`(`name`) using btree, -- 普通索引
    fulltext index `index_address`(`address`), -- 全文索引
    index `index_age`(`age`) using btree
)engine=innodb default charset=utf8;
  1. 创建表后添加索引。
##### 添加索引的第一种方式 #####
-- 添加唯一索引
alter table tb_user add unique index `index_phone`(`phone`) using btree;
-- 添加普通索引
alter table tb_user add index `index_name`(`name`) using btree;
-- 添加全文索引
alter table tb_user add fulltext index `index_address`(`address`);

##### 添加索引的第二种方式 #####
create index 索引名 on 表名(字段名)
-- 为tb_user表的name字段添加索引
create index `index_name` on tb_user(`name`)
-- 添加联合索引。使用name的前两个字符和age作为联合索引。
create index index_name on tb_user(name(2),age);
  1. 全文索引的使用。
-- address为全局索引列。通过全文索引搜索，搜索address中包含字符 t 的数据。
explain select * from tb_user where match(address) against('t');
  1. 索引的删除
drop index 索引名 on 表名
-- 删除tb_user表中，索引名为 index_name 的索引。
drop index index_name on tb_user;

6.索引的使用条件

  1. 适合创建索引的情况。
    1. 主键自动创建索引。
    2. 频繁查询的条件应该创建索引。
    3. 排序的字段需要创建索引。
    4. 统计或者分组的字段需要创建索引。
  2. 不适合创建索引的情况。
    1. 频繁更新的字段不适合创建索引。
    2. 表记录太少不适合创建索引。
    3. 如果某个字段包含许多重复的内容，也不需要建立索引，没有太多的实际效果。

7.explain

  1. id字段。

    1. 如果是join连接，id相同，SQL则从上到下执行。
    2. 如果是子查询，id不同，id越大，优先级越高，越优先执行。

  2. select_type。

    1. simple，简单的select查询，不包含unique。
    2. primary，如果有子查询，primary表示子查询最外层的查询。
    3. driver。from后面的子查询会被标记为driver。
    4. subquery，select或者where中的子查询会被标记为subquery。子查询结果为单值。
    5. dependent subquery。select或者where中的子查询会被标记为dependent subquery。子查询结果为多值。
    6. uncacheable subquery，select或者where中的子查询无法缓存结果会被标记为uncacheable subquery。
    7. union，union之后的select查询会被标记为union。
    8. union result，两个通过union连接的查询执行的结果会被标记为union。

  3. table，查询涉及的表。

  4. type。

    1. system，一般不会出现。
    2. const，通过主键索引或者唯一索引查询，只匹配一行数据。
    3. eq_ref，通过主键索引或者唯一索引查询。
    4. ref，通过非唯一索引查询，即通过普通索引查询，可能会匹配到多上数据。
    5. range，针对索引列的范围查询。
    6. index，index会遍历整个索引树，从而得到匹配的数据。
    7. all，全表遍历从而得到匹配的数据。
    8. 一般来说查询需要到range，或者ref级别以上。

  5. possible_keys，可能用到的索引，一个或者多个，但是不一定被实际查询使用。

  6. key，实际用到的索引。如果为null，则表示没有使用索引。

    1. 如果查询使用了覆盖索引，则该索引会出现在key中，possible_keys为null。
    2. select * from student，没有使用索引，type为all，需要进行全部扫描。
    3. select id from student，使用覆盖索引，只对所有的索引进行扫描。

  7. key_len，表示索引中使用的字节数，即使用的索引的长度，但不是索引实际使用的长度。同时key_len越大，使用的索引长度越大，搜索的范围越大。

  8. ref，显示索引的哪一列被使用。

    1. const，表示索引上的查询条件是常量。
    2. db_test.test.id，表示db_test数据库的test表中的id索引被使用。

  9. rows，根据表统计信息及索引选用情况，大致估算出找到所需要读取的行数。

  10. extra，包含不适合在其他列中显示，但是又是十分重要的额外信息。

    1. Using filesort。文件内排序，说明MySQL会对数据进行外部索引排序，而不是按照表内的索引顺序进行读取。当MySQL无法通过索引完成排序操作时，会进行文件内排序。Using filesort会出现在order by name，name排序的字段没有创建索引的情况下。
    2. Using temporary，使用临时表保存中间结果，MySQL对查询结果排序时使用临时表。MySQL4.1之前使用双路排序，先读取行指针和order by字段的值对应的列，对他们进行排序，然后按照排序好的列表，读取对应的行数据。MySQL4.1之后使用单路排序，直接读取需要排序数据对应的这一行数据，然后直接进行排序。当需要排序的数据大于 Sort buffer时，就会产生临时文件。
    3. Using index，表示select查询使用了覆盖索引，避免了数据行的访问。
    -- 1 出现了Using where，表示索引被用来执行索引值的查询，即从覆盖索引就可以读取到查询的数据，
-- 不需要在进行主键索引的查询。
-- Using where; Using index，
create index (ids_index01) on tablename(c01,c02);
select c01 from tablename where c02 = 10;

-- 2 没有出现Using where，表明只是通过索引来读取数据，最终会通过主键索引读取数据。
    1. using where，使用where过滤。
    2. using join buffer，使用了连接缓存。
    3. impossible where，where中表达式的值总是false。
    4. select tables optimized away，在没有group by子句时，基于索引优化min/max操作。或者MYISAM存储引擎优化count(*)操作。
    5. distinct，优化distinct，找到第一个匹配的元组后即停止同样值的查询。

  11. explain查询结果中的id、type、rows、extra信息很重要。

8.索引的使用

  1. where的查询条件要符合最左前缀法则。查询需要从索引的最左前列开始并且不跳过索引中的列。
  2. 不在索引列上做任何操作。
  3. 尽量使用覆盖索引,只进行覆盖索引的查询，减少select *。
  4. !=或者<>无法使用索引，会导致全表扫描。
  5. is not null无法使用索引，但是is null可以使用索引。
  6. like以通配符开头时，索引会失效。如like '%t%'。不以通配符开头，如like 't%'，索引不会失效。
  7. 字符串不加单引号索引失效。
  8. or会导致索引失效。
  9. 现有联合索引a，b，c，where a = 1 and b > 10 and c = 10，a和b可以使用索引，c不会使用索引。
posted @ 2021-10-31 14:30  行稳致远方  阅读(14)  评论(0)    收藏  举报