MySQL索引

最新推荐文章于 2021-05-27 11:38:34 发布

小短腿要早睡.

最新推荐文章于 2021-05-27 11:38:34 发布

阅读量115

点赞数 3

文章标签： mysql

本文链接：https://blog.youkuaiyun.com/weixin_50230647/article/details/114526481

版权

一索引、事务和锁
1 、索引简介

什么是索引？
一般的应用系统，读写比例在 10:1 左右，而且插入操作和一般的更新操作很少出现性能问题，在生产环境
中，我们遇到最多的，也是最容易出问题的，还是一些复杂的查询操作，因此对查询语句的优化显然是重中之重。
说起加速查询，就不得不提到索引了。
为什么要有索引？
索引在 MySQL 中也叫做“键”，是存储引擎用于快速找到记录的一种数据结构。索引对于良好的性能非常关
键，尤其是当表中的数据量越来越大时，索引对于性能的影响愈发重要。
索引优化应该是对查询性能优化最有效的手段了。善用索引能够轻易将查询性能提高好几个数量级。
索引相当于字典的音序表，如果要查某个字，如果不使用音序表，则需要从几百页中逐页去查。
索引的优劣势：
索引的优势：可以快速检索，减少 I/O 次数，加快检索速度；根据索引分组和排序，可以加快分组和排序。
索引的劣势：索引本身也是表，因此会占用存储空间，一般来说，索引表占用的空间是数据表的 1.5 倍；索
引表的维护和创建需要时间成本，这个成本随着数据量增大而增大；构建索引会降低数据表的修改操作（删除，
添加，修改）的效率，因为在修改数据表的同时还需要修改索引表。
1：创建表时创建索引
CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
INDEX myindex (username(16))
);
2：创建表之后创建索引
CREATE INDEX myindex ON mytable(username(16));
或者
ALTER table mytable ADD INDEX myindex(username)
3：查看表中的索引
SHOW INDEX FROM mytable
4：删除表中索引
DROP INDEX myindex ON mytable;
或者
ALTER TABLE mytable DROP INDEX myindex;
5：通过 explain 命令查看索引使用情况
explain select * from mytable where username = ‘jack’
运行结果如下
索引的使用规则
索引分类
常见的索引类型有：主键索引、唯一索引、普通索引、全文索引、组合索引。
 主键索引：即主索引，根据主键 pk_clolum（length）建立索引，不允许重复，不允许空值；
 唯一索引：用来建立索引的列的值必须是唯一的，允许空值；
 普通索引：用表中的普通列构建的索引，没有任何限制；
 全文索引：用大文本对象的列构建的索引；
 组合索引：用多个列组合构建的索引，这多个列中的值不允许有空值。
适合建立索引的情况
 主键自动建立唯一索引；
 经常作为查询条件在 WHERE 或者 ORDER BY 语句中出现的列要建立索引；
 作为排序的列要建立索引；
 查询中与其他表关联的字段，外键关系建立索引；
 高并发条件下倾向组合索引；
 用于聚合函数的列可以建立索引，例如使用了 max(column_1)或者 count(column_1)时的 column_1 就需
要建立索引。
不适合建立索引的情况
索引的使用规则
索引分类
常见的索引类型有：主键索引、唯一索引、普通索引、全文索引、组合索引。
 主键索引：即主索引，根据主键 pk_clolum（length）建立索引，不允许重复，不允许空值；
 唯一索引：用来建立索引的列的值必须是唯一的，允许空值；
 普通索引：用表中的普通列构建的索引，没有任何限制；
 全文索引：用大文本对象的列构建的索引；
 组合索引：用多个列组合构建的索引，这多个列中的值不允许有空值。
适合建立索引的情况
 主键自动建立唯一索引；
 经常作为查询条件在 WHERE 或者 ORDER BY 语句中出现的列要建立索引；
 作为排序的列要建立索引；
 查询中与其他表关联的字段，外键关系建立索引；
 高并发条件下倾向组合索引；
 用于聚合函数的列可以建立索引，例如使用了 max(column_1)或者 count(column_1)时的 column_1 就需
要建立索引。
不适合建立索引的情况
二事务
1：使用 update 语句实现转账操作，实现账号 1 向账号 2 转账 100 元
update t_act set balance=balance-100 where actno=1;
update t_act set balance=balance+100 where actno=2;
开启事务
开启事务的语法如下所示：
start transaction;
或者
begin transaction;
提交事务
提交事务的语法如下所示：
commit
回滚事务
回滚事务的语法如下所示：
rollback;
2：手动开启事务，向 t_act 表中插入一行记录，提交事务
start transaction;-- 手动开启事务
insert into t_act values(3,300); – 更新操作
commit;-- 提交事务，commit 之后即改变底层数据库数据
select * from t_act;
运行结果如下:
3：手动开启事务，向 t_act 表中插入一行记录，回滚事务
start transaction;-- 手动开启事务
insert into t_act values(4,200); – 更新操作
rollback;-- 回滚事务
select * from t_act;
运行结果如下:

三事务的隔离级别
事务的隔离级别有 4 个：
 读未提交（read uncommitted）：事务 A 和事务 B，事务 A 未提交的数据，事务 B 可以读取到，这里读
取到的数据叫做“脏数据”。这种隔离级别最低，一般是在理论上存在，数据库隔离级别一般都高于该
级别；
 读已提交（read committed）：事务 A 和事务 B，事务 A 提交的数据，事务 B 才能读取到。这种隔离级
别高于读未提交，可以避免“脏数据”。但如果事务 A 多次读取同一数据，而事务 B 在事务 A 多次读
取的过程中，对数据作了更新并提交，导致事务 A 多次读取同一数据时，结果不一致，这种现象称为“不
可重复读”；
 可重复读（repeatable read）：事务 A 和事务 B，事务 A 提交之后的数据，事务 B 读不到事务 A 提交
的数据。这种隔离级别高于读已提交，但如果事务 A 在修改数据的过程中（比如将所有记录状态设为 1），
如果事务 B 向同一张表中插入一条新记录（状态为 0），事务 A 提交后再次查询表，会发现有一条记录
状态没有改成 1，好像发生了幻觉，这种现象称为“幻读”。可重复读是 MySQL 默认隔离级别；
 串行化（serializable）：事务 A 和事务 B，事务 A 在操作数据库时，事务 B 只能排队等待。这种级别
可以避免“幻读”，每一次读取的都是数据库中真实存在数据，事务 A 与事务 B 串行，而不并发
四总结