EricYuan
文章 分类
EricYuan

Mysql_2_索引和事务

 收录于  类别 Study Notes 和 系列 Mysql   阅读量次
     约 237 字   预计阅读 2 分钟 
系列 -

说起提高数据库性能，索引是最物美价廉的东西了。不用加内存，不用改程序，不用调 sql，查询速度就可能提高百倍干倍。

sql

# 给表的某列添加索引
create index index_name on tb_name (tb_col_name);

创建索引后，查询只对创建了索引的列有效，性能提高显著

副作用：

  • 索引自身也是占用空间的，添加索引后，表占用空间会变大
  • 对 DML (insert into, update, delete) 语句有效率影响 (因为需要重新构建索引)
  • 没有索引时：从头到尾全表扫描
  • 创建索引后：存储引擎 innodb，B+树，牺牲空间换时间～ TODO
  • 主键索引，主键自动地为主索引
  • 唯一索引，unique 修饰的列
  • 普通索引，index
  • 全文索引，FULLTEXT，一般不用 mysql 自带的全文索引
    • 开发中考虑使用全文搜索 solr，或者 ElasticSearch（即 es）

sql

# 查询是否有索引
SHOW INDEXES FROM tb_name

# 创建或修改
create [unique] index index_name on tb_name (col_name [(length)])
alter table tb_name add index index_name (col_name)

# 删除索引
drop index index_name on tb_name
# 删除主键索引
alter table tb_name drop primary key
  • 一般频繁查询的字段应该创建索引
  • 唯一性太差的字段不适合创建索引
  • 更新非常频繁的字段不适合创建索引
  • 不会出现在 where 子句中的字段不该创建索引

事务 用于保证数据的一致性，它由一组 dml 语句组成，该组的 dml 语句，要么全部成功，要么全部失败。– 比如转账，如果转出成功，转入失败，是很恐怖的事情。这就需要事务确保了。

当执行事务操作时，mysql 会在表上加锁，防止其他用户修改表的数据。

mysql 事务机制需要使用 innodb 引擎， MyISAM 不好使。

  • start transaction，– 开始一个事务 或者 set autocommit=off
  • savepoint point_name，– 设置保存点
  • rollback to point_name，– 回退事务
  • rollback，– 回退全部事务
  • commit，– 提交事务，所有的操作生效，不能回退，删除保存点，释放锁

默认情况下，dml 操作时自动提交的。

多个端连接开启各自事务操作数据库时，数据库要负责隔离操作，以保证各个连接在获取数据时的准确性。

事务与事务之间的隔离程度，一共有四种：

mysql 隔离级别 脏读 不可重复读 幻读 加锁读 解释｜
读未提交 (READ UNCOMMITTED) ✅ ✅ ✅ 不加锁 一个事务可以读取到另一个事务未提交的数据
读已提交 (READ COMMITTED) ❌ ✅ ✅ 不加锁 一个事务只能读取到已提交的数据
可重复读 (REPEATABLE READ) ❌ ❌ ❌ 不加锁 同一事务中多次读取相同记录时，结果始终一致
可串行化 (SERIALIZABLE) ❌ ❌ ❌ 加锁 最严格的隔离级别，确保事务之间彼此完全隔离,可能会影响系统的并发性能。

不考虑事务隔离，就会导致下列问题：

  • 脏读：指一个事务读取了另一个事务未提交的数据。换句话说，当一个事务正在修改数据时，另一个事务读取了这些未提交的数据。如果修改事务最终回滚，那么读取事务就会读取到无效的数据，这就是脏读。
  • 不可重复读：指在同一事务中，多次读取同一数据，但由于其他事务的修改导致读取结果不一致。换句话说，一个事务在多次读取同一数据时，由于其他事务的更新操作，导致了数据的不一致性，这就是不可重复读。
  • 幻读：指在同一事务中，多次执行相同的查询，但由于其他事务的插入或删除操作，导致了结果集的变化。换句话说，一个事务在多次查询相同条件的数据时，由于其他事务的插入或删除操作，导致了结果集的变化，这就是幻读。

隔离级别默认为可重复读

sql

# 查看隔离级别
SELECT @@transaction_isolation; # 老版本叫 tx_isolation
# 设置隔离级别
set session transaction isolation level [read committed |read uncommitted | repeatable read | serializable]

以下是 gpt 给出的结论：

  1. 事务支持：

    • MyISAM：不支持事务，无法实现回滚和提交操作。
    • InnoDB：支持事务，可以实现回滚和提交操作，确保数据的一致性和完整性。
    • Memory：不支持事务，数据存储在内存中，不会持久化到磁盘上。

  2. 锁机制：

    • MyISAM：采用表级锁，当进行写操作时会锁定整个表，可能导致并发性能下降。
    • InnoDB：采用行级锁，可以实现更好的并发性能，允许多个事务同时对同一表进行读写操作。
    • Memory：采用表级锁，类似于 MyISAM，但由于数据存储在内存中，锁的影响相对较小。

  3. 外键支持：

    • MyISAM：不支持外键约束，无法实现关系型数据库的完整性。
    • InnoDB：支持外键约束，可以定义和管理表之间的关系，确保数据的完整性。
    • Memory：不支持外键约束，适合用于临时数据存储和快速访问，但不适合长期持久化的数据存储。

  4. ACID 属性：

    • MyISAM：不满足 ACID（原子性、一致性、隔离性、持久性）属性，无法保证事务的完整性和持久性。
    • InnoDB：满足 ACID 属性，可以确保事务的原子性、一致性、隔离性和持久性。
    • Memory：不满足 ACID 属性，适合用于临时数据存储和快速访问，但不适合长期持久化的数据存储。

  5. 性能特点：

    • MyISAM：适合于读密集型操作，对于大量的查询操作性能较好。
    • InnoDB：适合于写密集型操作和事务处理，对于数据的插入、更新和删除操作性能较好。
    • Memory：适合于对数据的快速访问和临时存储，但不适合长期持久化的数据存储。

  6. 可靠性：

    • MyISAM：在发生故障时，可能会导致数据损坏，不够可靠。
    • InnoDB：具有良好的容错性和恢复能力，对数据的完整性和可靠性有较好的保障。
    • Memory：数据存储在内存中，不具备持久化能力，不够可靠。

  7. 缓存和索引：

    • MyISAM：采用缓存和索引机制，适合于大量的查询操作。
    • InnoDB：采用缓存和索引机制，支持更复杂的查询和事务处理。
    • Memory：数据存储在内存中，具有非常快速的访问速度，适合于临时数据存储和快速访问。

综上所述，选择合适的存储引擎取决于应用程序的特定需求。如果需要事务支持、数据完整性和可靠性，则 InnoDB 是一个不错的选择。如果对性能要求较高，可以考虑 MyISAM。而 Memory 存储引擎适合于临时数据存储和快速访问的场景，比如用户的在线状态。

更新于 2023-12-07
阅读原始文档
返回 | 主页