Mysql_2_索引和事务

系列 -
注意
本文最后更新于 2023-12-07,文中内容可能已过时。

说起提高数据库性能,索引是最物美价廉的东西了。不用加内存,不用改程序,不用调 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 存储引擎适合于临时数据存储和快速访问的场景,比如用户的在线状态。