索引

  • 优势：
    • 快速检索，降低查询时间
    • 减少I/O次数
    • 提高并发能力
  • 劣势：
    • 索引本身也是表，因此会占用存储空间，一般来说，索引表占用的空间的数据表的1.5倍；索引表的维护和创建需要时间成本，这个成本随着数据量增大而增大；构建索引会降低数据表的修改操作（删除，添加，修改）的效率，因为在修改数据表的同时还需要修改索引表；

索引语法

https://dev.mysql.com/doc/refman/8.0/en/create-index.html

•**添加索引：**

•ALTER TABLE my_table ADD [UNIQUE] INDEX idx_name(column_name);

•CREATE INDEX index_name ON my_table(column_name);

•**删除索引：**

•DROP INDEX my_index ON tablename；

•ALTER TABLE table_name DROP INDEX index_name;

•**查看索引：**

•SHOW INDEX FROM tablename；

•**查看查询语句使用索引的情况：**

•explain SELECT * FROM table_name WHERE column_1=’123’;

索引分类

  • 主键索引：根据主键pk_clolum（length）建立索引，不允许重复，不允许空值 - ALTER TABLE ‘table_name’ ADD PRIMARY KEY pk_index(‘col’)；
  • 唯一索引：索引的列的值必须是唯一的，允许空值 - ALTER TABLE ‘table_name’ ADD UNIQUE index_name(‘col’)；
  • 普通索引：没有任何限制 - ALTER TABLE ‘table_name’ ADD INDEX index_name(‘col’)；
  • 联合索引：用多个列组合构建的索引 - ALTER TABLE ‘table_name’ ADD INDEX index_name(‘col1’,’col2’,’col3’)；
  • 全文索引：大文本对象构建的索引，不建议使用 - ALTER TABLE ‘table_name’ ADD FULLTEXT INDEX ft_index(‘col’)；

MySQL索引实现原理

B+树：平衡查找树

MySQL优化 - 图1

所有的数据都在叶子节点，且每一个叶子节点都带有指向下一个节点的指针，形成了一个有序的链表。为什么要有序呢？其实是为了范围查询。比如说select * from Table where id > 1 and id < 100; 当找到1后，只需顺着节点和指针顺序遍历就可以一次性访问到所有数据节点，极大提高了区间查询效率。

索引模型

MySQL优化 - 图2

MySQL优化 - 图3

MySQL索引使用策略

什么时候要使用索引？

•主键自动建立唯一索引；row id

•经常作为查询条件在WHERE或者ORDER BY 语句中出现的列要建立索引；

•作为排序的列要建立索引；

•查询中与其他表关联的字段，外键关系建立索引

•优先选择组合索引（多列索引）

什么时候不要使用索引？

•经常增删改的列不要建立索引

•有大量重复的列不建立索引（选择度低）

•表记录极少不要建立索引（有可能不走索引）

索引失效的情况：

•在组合索引中不能有列的值为NULL，如果有，那么这一列对组合索引就是无效的。

•在一个SELECT语句中，索引只能使用一次，如果在WHERE中使用了，那么在ORDER BY中就不要用了。

•LIKE操作中，‘%aaa%’不会使用索引，也就是索引会失效，但是‘aaa%’可以使用索引。

•在索引的列上使用表达式或者函数会使索引失效

•隐式转换会导致失效，比如字段email是字符串类型的，使用WHERE email=99999 则会导致失败，应该改为WHERE email=’99999’。

•在查询条件中使用OR连接多个条件会导致索引失效，除非OR链接的每个条件都加上索引，这时应该改为两次查询，然后用UNION ALL连接起来。

•尽量不要包括多列排序，如果一定要，最好为这队列构建组合索引。

事务和锁

事务定义

•事务（Transaction）是一组SQL组成的执行单元（Unit），是数据库并发控制和回滚的基本单位

•一个事务可能包含多个SQL，要么都成功，要么都失败

•4个基本属性：ACID

–Atomic：原子性， 同一个事务里，要么都提交，要么都回滚

–Consistency：一致性， 即在事务开始之前和事务结束之后，数据库的完整性约束没有被破坏

–Isolation：隔离性， 并发事务之间的数据是彼此隔离的

–Durabiliy：持久性， 事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失

•支持事务的引擎：InnoDB,InnoDBCluser,TokuDB,MyRocks

•不支持事务的引擎：MyISAM，MEMORY，Blackhole

事务存储引擎

MySQL优化 - 图4

事务控制语句

•START TRANSACTION（或 BEGIN）

–显式开始一个新事务

•SAVEPOINT

–分配事务过程中的一个位置，以供将来引用

•COMMIT

–永久记录当前事务所做的更改

•ROLLBACK

–取消当前事务所做的更改

•ROLLBACK TO SAVEPOINT

–取消在 savepoint之后执行的更改

•RELEASE SAVEPOINT

–删除 savepoint标识符

•SET AUTOCOMMIT=0

–为当前连接禁用或启用默认 autocommit模式

事务控制流程

MySQL优化 - 图5

执行计划

desc、explain

  1. desc select * from t3 where id=1;
  2. explain select * from t3 where id=1;