MySQL索引和事务

2023-08-06 21:35:58

MySQL索引和事务

索引

索引是特殊数据结构，定义在查找时作为查找条件的字段，在MySQL又称为键key，索引通过存储引擎实现

优点：

索引可以降低服务需要扫描的数据量，减少了IO次数

索引可以帮助服务器避免排序和使用临时表

索引可以帮助将随机I/O转为顺序I/O

缺点：

占用额外空间，影响插入速度

索引类型：

B+ TREE(默认索引类型)、HASH、R TREE

聚簇（集）索引、非聚簇索引：数据和索引是否存储在一起

主键索引、二级（辅助）索引

稠密索引、稀疏索引：是否索引了每一个数据项

简单索引、组合索引

​ 左前缀索引：取前面的字符做索引

​ 覆盖索引：从索引中即可取出要查询的数据，性能高

比较有代表性的：二叉树、红黑树、B-TREE、B+TREE

B-TREE：每一级都存有数据

B+TREE：只有叶子节点存数据，枝和根都是指针

B+TREE

顺序存储，每一个叶子节点到根结点的距离是相同的；左前缀索引，适合查询范围类的数据

使用B+Tree索引的查询类型:
	全值匹配：精确所有索引列，如：姓，名，年龄
	匹配最左前缀：即只使用索引的第一列，如：姓
	匹配列前缀：只匹配一列值开头部分，如：姓以什么开头的
	匹配范围值：如：姓x和姓y之间
	精确匹配某一列并范围匹配另一列：如：姓x,名以y开头的
	只访问索引的查询	
不建议使用B+Tree索引的:
	如不从最左列开始，则无法使用索引，如：查找名结尾，或姓结尾
	不能跳过索引中的列：如：查找姓，年龄的，跳过了名，只能使用索引第一列
	如果查询中某个列是为范围查询，那么其右侧的列都无法再使用索引：如：姓x,名y%,年龄，只能利用姓和名上面的索引
需要于开发定义好:
	索引列的顺序和查询语句的写法应相匹配，才能更好的利用索引
	为优化性能，可能需要针对相同的列但顺序不同创建不同的索引来满足不同类型的查询需求

Hash索引

基于哈希表实现，只有精确匹配索引中的所有列的查询才有效，索引自身只存储索引列对应的哈希值和数据指针，索引结构紧凑，查询性能好

Memory存储引擎支持显式hash索引，InnoDB和MyISAM存储引擎不支持

适用场景：只支持等值比较查询，包括=, <=>, IN()

不适用场景：

​ 不适用于顺序查询：索引存储顺序的不是值的顺序

​ 不支持模糊匹配

​ 不支持范围查询

​ 不支持部分索引列匹配查找：如A，B列索引，只查询A列索引无效

空间数据索引R-Tree

MyISAM支持地理空间索引，可以使用任意维度组合查询，使用特有的函数访问，常用于做地理数据存储，使用不多InnoDB从MySQL5.7之后也开始支持

全文索引(FULLTEXT)

在文本中查找关键词，而不是直接比较索引中的值，类似搜索引擎InnoDB从MySQL 5.6之后也开始支持

冗余和重复索引

​ 冗余索引：（A），（A，B）

​ 重复索引：已经有索引，再次建立索引

索引优化策略

独立地使用列：尽量避免其参与运算，独立的列指索引列不能是表达式的一部分，也不能是函数的参数，在where条件中，始终将索引列单独放在比较符号的一侧

左前缀索引：构建指定索引字段的左侧的字符数，要通过索引选择性来评估

索引选择性：不重复的索引值和数据表的记录总数的比值

多列索引：AND操作时更适合使用多列索引，而非为每个列创建单独的索引

选择合适的索引列顺序：无排序和分组时，将选择性最高放左侧

索引优化建议

只要列中含有NULL值，就最好不要在此例设置索引，复合索引如果有NULL值，此列在使用时也不会使用索引

尽量使用短索引，如果可以，应该制定一个前缀长度

对于经常在where子句使用的列，最好设置索引

对于有多个列where或者order by子句，应该建立复合索引

对于like语句，以%或者‘-’开头的不会使用索引，以%结尾会使用索引

尽量不要在列上进行运算（函数操作、比较和表达式操作）

尽量不要使用not in和<>操作

SQL语句性能优化

查询时，能不要*就不用*，尽量写全字段名

大部分情况连接效率远大于子查询

多表连接时，尽量小表驱动大表，即小表 join 大表

在有大量记录的表分页时使用limit

对于经常使用的查询，可以开启缓存

多使用explain和profile分析查询语句

查看慢查询日志，找出执行时间长的sql语句优化

创建索引

create index idx_age on students(age); -- age字段建立索引
create index idx_name on students(name(10)); -- 前缀索引
create unique index uni_age on students(stuid); -- 唯一索引

删除索引

drop index idx_name on students;

查询有什么索引

show indexes from students;

查询索引的使用

show index from students\G;

EXPLAIN

分析索引的有效性

mysql> explain select * from students where age = 20;
+----+-------------+----------+------------+------+---------------+---------+---------+-------+------+----------+-------+
| id | select_type | table    | partitions | type | possible_keys | key     | key_len | ref   | rows | filtered | Extra |
+----+-------------+----------+------------+------+---------------+---------+---------+-------+------+----------+-------+
|  1 | SIMPLE      | students | NULL       | ref  | idx_age       | idx_age | 1       | const |    2 |   100.00 | NULL  |
+----+-------------+----------+------------+------+---------------+---------+---------+-------+------+----------+-------+
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from students where name like "%s";
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
| id | select_type | table    | partitions | type | possible_keys | key  | key_len | ref  | rows | filtered | Extra       |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
|  1 | SIMPLE      | students | NULL       | ALL  | NULL          | NULL | NULL    | NULL |   25 |    11.11 | Using where |
+----+-------------+----------+------------+------+---------------+------+---------+------+------+----------+-------------+
1 row in set, 1 warning (0.00 sec)

explain值说明:

type：关联类型或访问类型，即MySQL决定的如何去查询表中的行的方式，以下顺序，性能从低到高
	ALL: 全表扫描
	index：根据索引的次序进行全表扫描；如果在Extra列出现"Using index"表示了使用覆盖索引，而非全表扫描
	range：有范围限制的根据索引实现范围扫描；扫描位置始于索引中的某一点，结束于另一点
	ref: 根据索引返回表中匹配某单个值的所有行
	eq_ref：仅返回一个行，但与需要额外与某个参考值做比较
	const, system: 直接返回单个行

possible_keys：查询可能会用到的索引
key: 查询中使用到的索引
key_len: 在索引使用的字节数
ref: 在利用key字段所表示的索引完成查询时所用的列或某常量值
rows：MySQL估计为找所有的目标行而需要读取的行数
Extra：额外信息
	Using index：MySQL将会使用覆盖索引，以避免访问表
	Using where：MySQL服务器将在存储引擎检索后，再进行一次过滤
	Using temporary：MySQL对结果排序时会使用临时表
	Using filesort：对结果使用一个外部索引排序

并发控制-锁

锁粒度：
	表级锁
	行级锁
锁：
	读锁：共享锁，只读不可写（包括当前事务） ，多个读互不阻塞
	写锁：独占锁，排它锁，写锁会阻塞其它事务（不包括当前事务）的读和它锁
实现
	存储引擎：自行实现其锁策略和锁粒度
	服务器级：实现了锁，表级锁，用户可显式请求
分类：
	隐式锁：由存储引擎自动施加锁
	显式锁：用户手动请求
锁策略：在锁粒度及数据安全性寻求的平衡机制

读锁

lock tables students read; -- 能读不能写改

-- 可以kill掉
show processlist;
kill Id;

写锁

lock tables students write; -- 不能读不能写

解锁

unlock tables;

事务

事务Transactions：一组原子性的SQL语句，或一个独立工作单元
MySQL索引和事务
事务日志：记录事务信息，实现undo,redo等故障恢复功能

ACID特性：

A：atomicity原子性；整个事务中的所有操作要么全部成功执行，要么全部失败后回滚
C：consistency一致性；数据库总是从一个一致性状态转换为另一个一致性状态
I：Isolation隔离性；一个事务所做出的操作在提交之前，是不能为其它事务所见；隔离有多种隔离级别，实现并发
D：durability持久性；一旦事务提交，其所做的修改会永久保存于数据库j中

启动事务

begin;

结束事务

rollback; -- 回滚
commit; -- 提交
只有事务型存储引擎中的DML语句方能支持此类操作！

事务保存点

savepoint 点;
rollback to 点;

事务隔离级别

事务隔离级别：从上至下更加严格
READ-UNCOMMITTED 可读取到未提交数据，产生脏读
READ-COMMITTED 可读取到提交数据，但未提交数据不可读，产生不可重复读，即可读取到多个提交数据，导致每次读取数据不一致
REPEATABLE-READ 可重复读，多次读取数据都一致，产生幻读，即读取过程中，即使有其它提交的事务修改数据，仍只能读取到未修改前的旧数据。此为MySQL默认设置
SERIALIZABILE 可串行化，未提交的读事务阻塞修改事务，或者未提交的修改事务阻塞读事务。导致并发性能差

MVCC: 多版本并发控制，和事务级别相关

查看当前事务隔离级别

show variables like 'tx_isolation';

修改事务隔离级别

my.cnf

[mysqld]
transaction-isolation=SERIALIZABLE
#transaction-isolation=READ-UNCOMMITTED

临时修改

set tx_isolation='SERIALIZABLE';

死锁：

两个或多个事务在同一资源相互占用，并请求锁定对方占用的资源的状态

事务日志：

事务日志的写入类型为“追加”，因此其操作为“顺序IO”；通常也被称为：预写式日志 write ahead logging

事务日志文件：

ib_logfile0 ib_logfile1
上一篇：MySQL-查询


下一篇：Python_操作redis