latte Studio

mysql索引与事务

mysql索引与事务
 运维/数据库  5272  11 分钟  

index索引

定义

​ 一种特殊的数据组织结构；一般建立在经常作为查询条件的字段上，索引靠存储引擎实现；mysql中索引即index，也称为key；

优缺点

优点：

  • 对合适的数据类型建立合适的索引，并结合合适的查询条件，可以减少表扫描的次数，提高查询效率
  • 可以将随机io转为顺序io
  • 帮助服务器避免排序和使用临时表

缺点：

  • 更新数据时，需要对应更新索引，降低了插入速度
  • 占用额外空间

类型

  • B+树索引、hash索引、r 树索引
  • 聚集索引、非聚集索引，（数据索引是否存储在一起）
  • 一级索引，二级索引，（二级索引存储的指针是指向一级索引的数据，再由一级索引指向真实的数据行地址）
  • 稠密索引、稀疏索引（是否索引了每个数据项
  • 简单索引、组合索引
    • 左前缀索引

b+树

image-20200923103531749

1、特点

b+树特点：磁盘块根据地址指针，组成成一个向下的树状结构，枝干节点不存储实际数据，只存储向下某范围（小于、之间、大于）三个范围的数据的地址指针，三层节点即可索引百万级别数据，数据只存储于最底层的叶子节点；

eg：

  1. 假设查找数据50，加载第一个根节点，和3个数据5、28、65做比较，在28和65之间，对应p2指针的范围；
  2. 再和二级磁盘块数据范围比较，50在35到56之间，对应该磁盘块的p2指针
  3. 再向下级查找，遍历查找即可找到50所对应的数据内容，
  4. 相比于没有索引时的全表扫描，大大的提高了效率！

2、适用范围

b+树，存储的数据，其索引会按照一定顺序排列，各级别磁盘块都在某个范围内，因此适合范围查找，精确查询，字符串类型按照顺序排列也适合左前缀查找

  1. 全值精确匹配；如姓名wb
  2. 匹配最左前缀；如姓是wang的
  3. 匹配范围，如姓wang和ma之间

3、不适用

  1. 不适用右后缀索引，如以end结尾的字符串，因为b+树的索引顺序排列字符串型数据时，是按照左前缀的顺序排列，的有后缀是无序的！

ps：同一列，有可能会根据查询条件的不同，建立不同类型的索引

b-树

红黑树

hash索引

1、特点：

​ 对查询值做hash处理，不同值具有唯一的hash值，hash索引存储了hash值和对应的数据指针，根据hash值对应可以快速找到对应的一条数据，因为hash值的散列和唯一性

2、适合：

因为hash索引只使用等值查询：查询条件：=、in（）

3、不适合

  • 不适合模糊查询；
  • 不适合范围查询
  • 不适合顺序查询；

空间索引R-tree

​ myisam支持地理空间索引，用于地理数据存储；innodb在mysql5.7之后支持

全文索引fulltext

​ 在文本中检索关键词，类似搜索引擎、elasticserach等，innodb在mysql5.6之后支持

聚集索引

​ 以b+树为例，根据查询条件在某索引上进行查询时，在末端叶子节点找到了满足查询条件的数据，如id=666，那么此时如果一整行数据和id=666存储在同一块磁盘空间，（确切的说是相邻），那么这个索引就是聚集索引

​ 即索引和数据存储在一起

非聚集索引

​ 仍以b+树为例，在根据查询条件查到目标叶子节点时，没有立刻找到对应的数据，而是找到一个指针，该指针指向了完整数据所在的物理磁盘地址，那么这个索引就是非聚集索引；

​ 即索引和数据分开存放，不在相邻的磁盘地址空间

主键索引

​ 是在主键上建立的索引，一般是聚集索引，索引和数据相邻存储

二级索引

​ 在非主键字段上建立的索引，一般是非聚集索引，索引的值是对应主机索引的地址指针，再通过主键索引间接找到数据项；属于间接索引，也就二级索引；

索引优化策略

冗余索引

​ 根据不同查询需求，可能对某字段建立多个索引，如a字段建立索引，a和b又建立了联合索引；

优化策略

  1. sql语句中单独使用索引列：即索引列尽量不参与运算，不做函数参数，即where子句中，索引列尽量单独出现
  2. 左前缀索引：利用指定字段的前多少个字符构建左前缀索引，
  3. 多列索引：and操作时，适合多列索引；
  4. 列中中null值的，最好不设置索引
  5. 尽量使用短索引，最好指定前缀长度
  6. like语句，%结尾的适用前缀索引
  7. 索引列尽量不参于运算和函数
  8. 尽量不用不确定的not in和<>，而用相对确定的in 和=
  9. 多表连接时，尽量小表join大表
  10. 大量数据表，使用limit
  11. explain和profile分析查询语句
  12. 查询慢查询日志，找出慢sql分析优化

索引管理

创建

MariaDB [db1]> create index idx1 on tb1(id);
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0


MariaDB [db1]> alter table tb1 add index idx2(id);

查看帮助

创建索引，设置索引名，索引类型有b树，和hash，选择在哪个表的哪些字段创建索引，
字段可以选择一定长度前缀进行索引创建，选择倒序，或正序
而额外的选项
MariaDB [db1]> help create index;
Name: 'CREATE INDEX'
Description:
Syntax:
CREATE [ONLINE|OFFLINE] [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
    [index_type]
    ON tbl_name (index_col_name,...)
    [index_option] ...

index_col_name:
    col_name [(length)] [ASC | DESC]

index_type:
    USING {BTREE | HASH}

index_option:
    KEY_BLOCK_SIZE [=] value
  | index_type
  | WITH PARSER parser_name
  | COMMENT 'string'

删除

MariaDB [db1]> drop index idx2 on tb1;
Query OK, 0 rows affected (0.02 sec)
Records: 0  Duplicates: 0  Warnings: 0

# drop或alter语句均可
MariaDB [db1]> alter table tb1 drop index idx1;
Query OK, 0 rows affected (0.00 sec)
Records: 0  Duplicates: 0  Warnings: 0

查看

MariaDB [db1]> show indexes from tb1\G;
自动为主键字段添加的主键索引；
*************************** 1. row ***************************
        Table: tb1
   Non_unique: 0
     Key_name: PRIMARY
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment: 
*************************** 2. row ***************************
        Table: tb1
   Non_unique: 1
     Key_name: idx1
 Seq_in_index: 1
  Column_name: id
    Collation: A
  Cardinality: 0
     Sub_part: NULL
       Packed: NULL
         Null: 
   Index_type: BTREE
      Comment: 
Index_comment:

优化表空间

MariaDB [db1]> optimize table tb1;
+---------+----------+----------+-------------------------------------------------------------------+
| Table   | Op       | Msg_type | Msg_text                                                          |
+---------+----------+----------+-------------------------------------------------------------------+
| db1.tb1 | optimize | note     | Table does not support optimize, doing recreate + analyze instead |
| db1.tb1 | optimize | status   | OK                                                                |
+---------+----------+----------+-------------------------------------------------------------------+

查看索引使用情况

MariaDB [db1]> set global userstat=1;
Query OK, 0 rows affected (0.00 sec)

MariaDB [db1]> show index_statistics;
Empty set (0.00 sec)

explain分析索引

explain select ...
可以获取查询语句的执行计划，用于查看查询优化器如何执行查询；

MariaDB [db1]> explain select * from mysql.user;
+------+-------------+-------+------+---------------+------+---------+------+------+-------+
| id   | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+------+-------------+-------+------+---------------+------+---------+------+------+-------+
|    1 | SIMPLE      | user  | ALL  | NULL          | NULL | NULL    | NULL |    7 |       |
+------+-------------+-------+------+---------------+------+---------+------+------+-------+

id：select语句编号

select_type：
	simple 简单查询
	subquery 简单子查询
	primary 最外面的select
	derived
	..

table：select用到的表

详见官方手册：
https://dev.mysql.com/doc/refman/5.7/en/explain.html

transaction事务

锁与并发

锁粒度：

  • 表级锁
  • 行级锁

锁种类：

  • 读锁，共享锁，多个读可同时进行，互不影响；互不阻塞
  • 写锁，独占锁，一个时刻只能有一个写锁，会阻塞其他写和读；

实现：

  • 存储引擎实现；不同的锁策略、粒度；
  • 服务器实现，mysql-server进程实现，表级别锁，用户会话可显式请求加锁

分类：

  • 隐式，存储引擎自动添加
  • 显式，用户手动显式请求加锁

显式锁

语法：
锁类型有read和write
LOCK TABLES
    tbl_name [[AS] alias] lock_type
    [, tbl_name [[AS] alias] lock_type] ...

lock_type: {
    READ [LOCAL]
  | [LOW_PRIORITY] WRITE
}

UNLOCK TABLES
解锁


# 通常备份前加全局读锁
flush tables tb-name with read lock

# 查询时加锁
select clause for update

死锁

2个或多个事务，占用了其他人将要请求的资源，并请求别人先释放的状态，

事务

定义：

transacitions，一组原子性的sql语句，要么全部成功提交，要么全部失败回滚，典型例子，银行转账操作，a向b转张1w，1、从a账户扣钱；2、向b账户加钱，这2步的sql必须一起成功或执行失败，这样才能保证了钱不会凭空增加或消失；

事务日志：

记录了一个个事务操作的日志，实现redo重做，undo等故障恢复功能；为追加写入，顺序io，效率较高，也叫write ahead logging WAL 预写入日志，顺序写的事务日志比随机写的数据修改优先写，也更快，

事务日志文件

[root@host2 ~]# ll /data/mysql/ib_log*
-rw-rw---- 1 mysql mysql 50331648 Sep 23 17:16 /data/mysql/ib_logfile0
-rw-rw---- 1 mysql mysql 50331648 Sep 18 14:42 /data/mysql/ib_logfile1

acid特性

  • a：atomicity原子性，一组原子性的sql语句，要么全部成功提交，要么全部失败回滚
  • c：consistency一致性，数据库总是从一个一致性状态，转换为另一个一致性状态
  • i：isolation隔离性，一个事务做出的修改，在提交前不能被其他事务所见，隔离共有4种级别
  • d：durability，提交事务所做修改永久保存；

事务生命周期

某些sql会自动隐式的开启事务，用户也可以显示的用begin开启事务，commit提交事务，rollback结束事务；

事务操作

启动：

begin
begin work
start transaction

结束：

commit 提交事务
rollback 回滚事务

自动事务提交

set autocommit={1|0}
MariaDB [(none)]> select @@autocommit;
+--------------+
| @@autocommit |
+--------------+
|            1 |
+--------------+
默认是1，自动提交

事务保存点：

savepoint id_string
设置保存点，id_string是自定义标识字符串
rollback to id_string
返回到某个保存点
release savepoint id_string
删除某保存点

事务隔离等级

image-20200923154929062

1、读未提交

​ 开启某事务1，在此事务期间，别的事务修改了数据，即使还没提交，事务1里可以查看到其修改后的数据，出现了脏读，当然，也出现了不可重复读，其他事务每修改一次，在事务1里看到的数据就变了一次，即在一次事务内，看到的数据会有多次变化！

2、读提交

​ 开启某事务1，在此事务期间，别的事务对数据做了修改，且提交之后，在事务1里通过查询语句可以查到到修改后的数据，没提交时无法查看到修改后的数据，其他事务每修改一次，在事务1里看到的数据就变了一次，产生了不可重复读

3、可重复读

​ 开启某事务1，在此事务期间，别的事务对数据做了修改，不管提交还是没提交，事务1里都看不到数据的修改，即事务1期间看到的数据都是事务1开启前一刻的“快照”，数据一致且多次查询都是一样的，即：可重复读；

4、串行化

​ 一个事务1期间，不可以开启其他事务，完全串行，没有并发可言！

一般采用可重复读级别，事务隔离的实现依靠MVCC多版本并发控制，同一条数据可能存在不同的版本，被同时进行的事务并发操作，数据的多版本共存实现了多个事务的并发进行

事务隔离等级设置

设置方式：

  • 配置文件的 mysqld配置段加入 transaction-isolation=四种级别之一
[mysqld]
...
transaction-isolation=serializable
然后重启；
  • 会话中，设置global或session级别的变量tx_isolation为四种级别之一
MariaDB [(none)]> set [global|session] tx_isolation="read-uncommitted";
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> select @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+

MariaDB [(none)]> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.00 sec)
# 默认是可重复读级别；

MariaDB [(none)]> set global tx_isolation="read-uncommitted";
Query OK, 0 rows affected (0.00 sec)

MariaDB [(none)]> select @@tx_isolation;
+------------------+
| @@tx_isolation   |
+------------------+
| READ-UNCOMMITTED |
+------------------+
1 row in set (0.00 sec)
# 会话中修改全局范围内事务隔离等级为读未提交，其他事务查看时也是读未提交；

事务验证的实验：

开2个xshell创建，都连接到同一个mysql实例，

实验的两个会话，都要关闭自动提交事务，默认mysql会开启，但是影响性能，一般关闭；

MariaDB [hellodb]> set @@session.autocommit=0;
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> select @@session.autocommit;
+----------------------+
| @@session.autocommit |
+----------------------+
|                    0 |
+----------------------+

读未提交：

MariaDB [hellodb]> select @@global.tx_isolation;
+-----------------------+
| @@global.tx_isolation |
+-----------------------+
| REPEATABLE-READ       |
+-----------------------+

MariaDB [hellodb]> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| REPEATABLE-READ        |
+------------------------+
##查看默认的事务隔离等级；

MariaDB [hellodb]> set @@session.tx_isolation='read-uncommitted';
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| READ-UNCOMMITTED       |
+------------------------+
##设置当前会话事务隔离等级为读未提交；开启两个会话-都如此设置；

MariaDB [hellodb]> insert into teachers values(5,'wang',23,
    -> 'M');
Query OK, 1 row affected (0.00 sec)
——会话1
MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | wang          |  23 | M      |
+-----+---------------+-----+--------+
——会话2
MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | wang          |  23 | M      |
+-----+---------------+-----+--------+
5 rows in set (0.00 sec)
## 会话1中向表插入一条数据、自己本事务能看到变化，会话2也能看到该数据；两者看到的是一样的；产生了读未提交效果；

读提交：

MariaDB [hellodb]> set @@session.tx_isolation=
    -> 'read-committed';
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| READ-COMMITTED         |
+------------------------+
1 row in set (0.00 sec)
## 会话1设置隔离等级为 读提交、会话2也如此设置；

MariaDB [hellodb]> start transaction;
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> insert into teachers values(5,'wang',23,'M');
Query OK, 1 row affected (0.00 sec)

MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | wang          |  23 | M      |
+-----+---------------+-----+--------+

——会话2
MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |

## 会话1插入一条数据，自己能看到，会话2看不到，因为1没有提交，提交之后，2再查表才能看到新增的数据；

可重复读：

MariaDB [hellodb]> set @@session.tx_isolation= 'repeatable-read';
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> select @@session.tx_isolation;
+------------------------+
| @@session.tx_isolation |
+------------------------+
| REPEATABLE-READ        |
+------------------------+
##会话1 2都设置为可重复读、关闭自动提交、手动开启事务；


MariaDB [hellodb]> insert into teachers values(6,'wang',23,'M');
Query OK, 1 row affected (0.00 sec)

MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | wang          |  23 | M      |
|   6 | wang          |  23 | M      |
+-----+---------------+-----+--------+

MariaDB [hellodb]> commit;
##会话1，改了数据，没提交、提交，会话2我都看不到；


ariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | wang          |  23 | M      |
+-----+---------------+-----+--------+
5 rows in set (0.00 sec)

MariaDB [hellodb]> 
MariaDB [hellodb]> 
MariaDB [hellodb]> commit;
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | wang          |  23 | M      |
|   6 | wang          |  23 | M      |
+-----+---------------+-----+-------
## 只有会话2我提交或回滚了，结束了我本身的事务，不然，不管你其他事务提交还是没提交，我统统看不到，保证了我看到的所有数据，都是开启事务那一刻的数据，所有数据的时间戳是一致的！ 保证数据的一致性！备份数据库时，就是这种隔离等级，才能保证备份下来的数据一致性状态；

串行等级；

——会话1
MariaDB [hellodb]> insert into teachers values(7,'wang',23,'M');
Query OK, 1 row affected (0.00 sec)

MariaDB [hellodb]> select * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | wang          |  23 | M      |
|   6 | wang          |  23 | M      |
|   7 | wang          |  23 | M      |
+-----+---------------+-----+--------+
##会话1插入一条数据，

MariaDB [hellodb]> select sql_no_cache * from teachers;
+-----+---------------+-----+--------+
| TID | Name          | Age | Gender |
+-----+---------------+-----+--------+
|   1 | Song Jiang    |  45 | M      |
|   2 | Zhang Sanfeng |  94 | M      |
|   3 | Miejue Shitai |  77 | F      |
|   4 | Lin Chaoying  |  93 | F      |
|   5 | wang          |  23 | M      |
|   6 | wang          |  23 | M      |
+-----+---------------+-----+--------+
6 rows in set (0.00 sec)

MariaDB [hellodb]> commit;
Query OK, 0 rows affected (0.00 sec)

MariaDB [hellodb]> select sql_no_cache * from teachers;
ERROR 1205 (HY000): Lock wait timeout exceeded; try restarting transaction 超时了
## 会话2结束本身事务后，再查询被阻塞了！因为串行化，要查的表正在被会话1的事务占用，所有会话2的查询此表，就得被阻塞，会话1结束事务之后，会话2才可以正常查询！
updatedupdated2021-03-092021-03-09

相关文章：

加载评论