索引

一、索引的概念

索引：是排序的快速查找的特殊数据结构，定义作为查找条件的字段上，又称为键key，索引通过存储引擎实现。

索引是一个排序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址（类似于c语言的链表通过指针指向数据记录的内存地址）。
使用索引后可以不用扫描全表来定位某行的数据，而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据，因此能加快数据库的查询速度。
索引就好比是一本书的目录，可以根据目录中的页码快速找到所需的内容。
索引是表中一列或者若干列值排序的方法。
建立索引的目的是加快对表中记录的查找或排序。（加快查询速度、对字段值进行排序）

二、索引的作用

1. 索引的优点

设置了合适的索引之后，数据库利用各种快速定位技术，能够大大加快查询速度，这是创建索引的最主要的原因。
当表很大或查询涉及到多个表时，使用索引可以成千上万倍地提高查询速度。
可以降低数据库的I/O成本，并且索引还可以降低数据库的排序成本。
通过创建唯一性索引，可以保证数据表中每一行数据的唯一性。
可以加快表与表之间的连接。
使用分组和排序时，可大大减少分组和排序的时间。
建立索引在搜索和恢复数据库中的数据时能显著提高性能。

2. 索引的缺点

1）索引需要占用额外的磁盘空间。
- 对于 MyISAM 引擎而言，索引文件和数据文件是分离的，索引文件用于保存数据记录的地址。
- 而 InnoDB 引擎的表数据文件本身就是索引文件。（索引文件和数据文件是同一个）
2）在插入和修改数据时要花费更多的时间、消耗更多性能，因为索引也要随之变动。

3. 索引类型

B+ TREE、HASH、R TREE、FULL TEXT
聚簇（集）索引、非聚簇索引：数据和索引是否存储在一起
主键索引、二级（辅助）索引
稠密索引、稀疏索引：是否索引了每一个数据项
简单索引、组合索引: 是否是多个字段的索引
左前缀索引：取前面的字符做索引
覆盖索引：从索引中即可取出要查询的数据，性能高

4. 索引结构

参考链接 : www.cs.usfca.edu/~galles/vis…

二叉树

参考链接: www.cs.usfca.edu/~galles/vis…

红黑树

参考链接:www.cs.usfca.edu/~galles/vis…

B树

参考链接: www.cs.usfca.edu/~galles/vis…

B+树

参考链接: www.cs.usfca.edu/~galles/vis…

三、索引管理

1. 查看索引

show index from students;

2. 建立索引

语法:

create index 索引名 on 表名 (列名[(length)]);

示例:

create index idx_name on students (name(5)); //以name为字段建立索引

3. 删除索引

语法:

drop index 索引名 on 表名;

示例：

drop index idx_name on students;

4. explain 工具

以通过EXPLAIN来分析索引的有效性,获取查询执行计划信息，用来查看查询优化器如何执行查询

语法:

EXPLAIN SELECT clause

示例:

explain select * from students where stuid=20;  //可以看到使用了索引

四、索引优化

独立地使用列：尽量避免其参与运算，独立的列指索引列不能是表达式的一部分，也不能是函数的参数，在where条件中，始终将索引列单独放在比较符号的一侧，尽量不要在列上进行运算（函数操作和表达式操作）
左前缀索引：构建指定索引字段的左侧的字符数，要通过索引选择性（不重复的索引值和数据表的记录总数的比值）来评估，尽量使用短索引，如果可以，应该制定一个前缀长度
多列索引：AND操作时更适合使用多列索引，而非为每个列创建单独的索引
选择合适的索引列顺序：无排序和分组时，将选择性最高放左侧
只要列中含有NULL值，就最好不要在此列设置索引，复合索引如果有NULL值，此列在使用时也不会使用索引
对于经常在where子句使用的列，最好设置索引
对于有多个列where或者order by子句，应该建立复合索引
对于like语句，以 % 或者 _ 开头的不会使用索引，以 % 结尾会使用索引
尽量不要使用not in和<>操作,虽然可能使用索引,但性能不高
不要使用RLIKE正则表达式会导致索引失效
查询时，能不要就不用，尽量写全字段名，比如:select id,name,age from students;
大部分情况连接效率远大于子查询
在有大量记录的表分页时使用limit
对于经常使用的查询，可以开启查询缓存
多使用explain和profile分析查询语句
查看慢查询日志，找出执行时间长的sql语句优化

五、并发控制

1. 锁机制

锁类型：

读锁：共享锁，也称为 S 锁,只读不可写（包括当前事务），多个读互不阻塞只能读不能写别人也能看
写锁：独占锁，排它锁，也称为 X 锁,写锁会阻塞其它事务（不包括当前事务）的读和写写锁别人看都看不了

特点：

S 锁和 S 锁是兼容的，X 锁和其它锁都不兼容，举个例子，事务 T1 获取了一个行 r1 的 S 锁，另外事务 T2 可以立即获得行 r1 的 S 锁，此时 T1 和 T2 共同获得行 r1 的 S 锁，此种情况称为锁兼容，但是另外一个事务 T2 此时如果想获得行 r1 的 X 锁，则必须等待 T1 对行 r1 锁的释放，此种情况也称为锁冲突

锁粒度：

表级锁：MyISAM 存储引擎 mysql
行级锁：InnoDB

实现：

存储引擎：自行实现其锁策略和锁粒度
服务器级：实现了锁，表级锁，用户可显式请求

分类：

隐式锁：由存储引擎自动施加锁
显式锁：用户手动请求

锁策略：在锁粒度及数据安全性寻求的平衡机制

1.1 手动加锁

人为加锁比较少见，一般在备份的时候会加锁，备份期间数据一直在改，可能备份时与备份的内容不一致。

语法：

lock tables 表名 type；

示例：

lock tables students  read;   //给students表加读锁，只可以查不能修改

1.2 解锁

unlock tables;   //释放锁

1.3 全局加锁

通常在备份前加全局读锁

flush tables  with  read  lock;   //整个数据库加锁

事务

一、事务的概念

事务是一种机制、一个操作序列，包含了一组数据库操作命令，并且把所有的命令作为一个整体一起向系统提交或撤销操作请求，即这一组数据库命令要么都执行，要么都不执行。
事务是一个不可分割的工作逻辑单元，在数据库系统上执行并发操作时，事务是最小的控制单元。
事务适用于多用户同时操作的数据库系统的场景，如银行、保险公司及证券交易系统等等。
事务通过事务的整体性以保证数据的一致性。
事务能够提高在向表中更新和插入信息期间的可靠性。

总结：所谓事务，它是一个操作序列，这些操作要么都执行，要么都不执行，它是一个不可分割的工作单位。

数据库是一个公司的核心资产，关系型数据库是可靠的，就是因为有事务的存在。

二、事务的ACID特点

ACID，是指在可靠数据库管理系统(DBMS) 中，事务(transaction)应该具有的四个特性：原子性(Atomicity) 、一致性(Consistency )、隔离性(Isolation) 、持久性(Durability) 。这是可靠数据库所应具备的几个特性。

A原子性(Atomicity)：整个事务中的所有操作要么全部成功执行，要么失败后回滚。
C一致性(Consistency )：数据库总是从一个一致性状态转换为另一个一致性状态,类似于质量守恒定律。(A1wB 0 A1w 给 B转1w 始终保持A+B=1w)
I隔离性(Isolation)：一个事务所做出的操作在提交之前，能不能为其他事务所见，和隔离级别有关系，4个隔离级别。
D持久性(Durability)：一旦事务提交，其所做的修改会永久保存于数据库中。

三、事务的隔离级别

MySQL 支持四种隔离级别，事务隔离级别从上至下更加严格。

READ UNCOMMITTED 未提交可读

可读取到未提交数据，产生脏读（隔离性最差）
READ COMMITTED 提交可读

可读取到提交数据，但未提交数据不可读，产生不可重复读，即可读取到多个提交数据，导致每次读取数据不一致可读。
REPEATABLE READ 幻读

可重复读，多次读取数据都一致，产生幻读，即读取过程中，即使有其它提交的事务修改数据，仍只能读取到未修改前的旧数据。此为MySQL默认设置。
SERIALIZABLE 串读

可串行化，未提交的读事务阻塞修改事务（加读锁，但不阻塞读事务），或者未提交的修改事务阻塞其它事务的读写（加写锁，其它事务的读，写都不可以执行）。会导致并发性能差。

存储引擎

一、存储引擎的概念

MySQL中的数据用各种不下同的技术存储在文件中，每一种技术都使用不同的存储机制、索引技巧、锁定水平并最终提供不同的功能和能力，这些不同的技术以及配套的功能在MySQL中称为存储引擎。
存储引擎是MySQL将数据存储在文件系统中的存储方式或者存储格式。
存储引擎是MySQL数据库中的组件，负责执行实际的数据I/O操作。
MySQL系统中，存储引擎处于文件系统之上，在数据保存到数据文件之前会传输到存储引擎，之后按照各个存储引擎的存储格式进行存储。

二、MySQL常用的存储引擎

MylSAM
InnoDB

注意：一个表只能使用一个存储引擎，一个库中不同的表可以使用不同的存储引擎。

三、MyISAM 和 InnoDB 对比

storage limits（存储上限）
- myisam 256TB
- innodb 64TB （但是mysql 根本达不到这么大的数据量）
transactions（事务）
- myisam 不支持
- innodb 支持
locking granularity（锁级别）
- myiasam 表级
- innodb 行级
提高mvcc（多版本的并发控制）
- myiasam 不支持
- innodb 支持（提高数据库的并发性）
data caches （数据缓存）
- myiasam 不支持
- innodb 支持
foreign key （外键）
- myiasam 不支持
- innodb 支持

对比	MyISAM	InnoDB
存储限制	256TB	64TB
事务	不支持	支持
锁定方式	表级锁定	行级锁定
mvcc	不支持	支持
数据缓存	不支持	支持
外键约束	不支持	支持
全文索引	支持	支持
读写性能	只支持单独的查询与插入，读写阻塞	读写和事务并发能力较好
硬件	硬件资源占用较小	缓存能力较好，可以减少磁盘IO的压力
存储文件	数据文件和索引文件分开存储，存储为三个文件（.frm的表结构文件，.MYD的数据文件，.MYI的索引文件）	数据文件也是索引文件，存储成两个文件(.frm的表结构文件，.ibd的数据和索引文件)
使用场景	适用不需要事务支持，单独的查询和插入的业务场景	适用于需要事务支持，一致性要求比较高，数据会频繁更新，读写并发高的业务场景

1. MyISAM 引擎特点

不支持事务
表级锁定
读写相互阻塞，写入不能读，读时不能写
只缓存索引
不支持外键约束
不支持聚簇索引
读取数据较快，占用资源较少
不支持MVCC（多版本并发控制机制）高并发
崩溃恢复性较差
MySQL5.5.5 前默认的数据库引擎

MyISAM 存储引擎适用场景

只读（或者写较少）
表较小（可以接受长时间进行修复操作）

MyISAM 引擎文件

tbl_name.frm 表格式定义（表格属性字段数据类型）
tbl_name.MYD 数据文件（数据 myisam data）
tbl_name.MYI 索引文件（索引目录和文章分开储存）

2. InnoDB 引擎特点

行级锁
支持事务，适合处理大量短期事务
读写阻塞与事务隔离级别相关
可缓存数据和索引
支持聚簇索引
崩溃恢复性更好
支持MVCC高并发
从MySQL5.5后支持全文索引
从MySQL5.5.5开始为默认的数据库引擎

3. Myisam和InnoDB的区别

MyISAM：不支持事务和外键约束，占用空间较小，访问速度快，表级锁定，适用于不需要事务处理、单独写入或查询的应用场景。（写入和查询不一起使用的场景）

InnoDB：支持事务处理、外键约束、占用空间比MyISAM 大，支持行级锁定，读写开发能力较好，适用于需要事务处理、读写频繁的应用场景。

四、管理存储引擎

查看mysql支持的存储引擎

show engines;

查看当前默认的存储引擎

show variables like '%storage_engine%';

设置默认的存储引擎

vim /etc/my.cnf
[mysqld]
default_storage_engine = InnoDB
//默认的就是 InnoDB ，一般不做修改

五、MySQL 中的系统数据库

mysql 数据库

是mysql的核心数据库，类似于Sql Server中的master库，主要负责存储数据库的用户、权限设置、关键字等mysql自己需要使用的控制和管理信息

information_schema 数据库

MySQL 5.0之后产生的，一个虚拟数据库，物理上并不存在information_schema数据库类似与"数据字典"，提供了访问数据库元数据的方式，即数据的数据。比如数据库名或表名，列类型，访问权限（更加细化的访问方式）

performance_schema 数据库

MySQL 5.5开始新增的数据库，主要用于收集数据库服务器性能参数,库里表的存储引擎均为PERFORMANCE_SCHEMA，用户不能创建存储引擎为PERFORMANCE_SCHEMA的表

sys 数据库

MySQL5.7之后新增加的数据库，库中所有数据源来自performance_schema。目标是把performance_schema的把复杂度降低，让DBA能更好的阅读这个库里的内容。让DBA更快的了解DataBase的运行情况

六、服务器配置和状态

可以通过mysqld选项，服务器系统变量和服务器状态变量进行MySQL的配置和查看状态。

官方文档帮助：
https://dev.mysql.com/doc/refman/8.0/en/server-option-variable-reference.html
https://dev.mysql.com/doc/refman/5.7/en/server-option-variable-reference.html
https://mariadb.com/kb/en/library/full-list-of-mariadb-options-system-and-status-variables/

注意：

其中有些参数支持运行时修改，会立即生效
有些参数不支持动态修改，且只能通过修改配置文件，并重启服务器程序生效
有些参数作用域是全局的，为所有会话设置
有些可以为每个用户提供单独（会话）的设置

1. 服务器选项

获取mysqld的可用选项列表

#查看mysqld可用选项列表和及当前值
mysqld --verbose --help

#获取mysqld当前启动选项
mysqld --print-defaults

注意:服务器选项用横线,不用下划线

设置服务器选项方法：

在命令行中设置

/usr/bin/mysqld_safe --skip-name-resolve=1
/usr/libexec/mysqld --basedir=/usr

在配置文件my.cnf中设置

vim /etc/my.cnf
[mysqld]
skip_name_resolve=1
skip-grant-tables

2. 服务器变量

服务器系统变量：可以分全局和会话两种

注意:系统变量用下划线,不用横线

查看所有变量

show variables;   #显示所有变量

show variables like 'innodb%';  #过滤变量

select  @@变量名

SHOW GLOBAL VARIABLES; #只查看global变量

#修改mysql的最大并发连接数
show variables like 'max_connections';
set global max_connections=2000;

vim /etc/my.cnf.d/mariadb-server.cnf 
[mysqld]
max_connections = 8000

vim /usr/lib/systemd/system/mariadb.service 
[Service]
#加下面一行
LimitNOFILE=65535

3. 状态变量

show status ;  #查看服务器的基本信息
 
show global status like 'com_select';   #当前数据库查询次数

总结

一、索引

1. 索引的主要作用和副作用

作用： 加快查询速度，对字段进行排序。

副作用：

索引需要占用额外的磁盘空间。
- 对于 MyISAM 引擎而言，索引文件和数据文件是分离的，索引文件用于保存数据记录的地址。
- 而 InnoDB 引擎的表数据文件本身就是索引文件。（索引文件和数据文件是同一个）
在插入和修改数据时要花费更多的时间、消耗更多性能，因为索引也要随之变动。

2. 索引是如何提高查询速度的？

索引包含着对数据表里所有记录的引用指针。索引是一个排序的列表，在这个列表中存储着索引的值和包含这个值的数据所在行的物理地址。
使用索引后可以不用扫描全表来定位某行的数据，而是先通过索引表找到该行数据对应的物理地址然后访问相应的数据，因此能加快数据库的查询速度。

二、事务

1. 事务的ACID特性

原子性(Atomicity)
一致性(Consistency )
隔离性(Isolation)
持久性(Durability)

这是可靠数据库所应具备的几个特性。

2. 事务之间的相互影响

当多个客户端并发地访问同一个表时，可能出现下面的一致性问题：

脏读
不可重复读
幻读
丢失更新

3. 事务的隔离级别

（1）未提交读（Read Uncommitted（RU）） ：允许脏读。

（2）提交读（Read Committed （RC）) ：防止脏读。

（3）可重复读（Repeatable Read（RR））：—mysql默认的隔离级别，防止脏读和不可重复读。

（4）串行读（serializable）：—相当于锁表，可以防止脏读、不可重复读和幻读，（事务串行化）会降低数据库的执行效率。

三、存储引擎

1. MyISAM和InnoDB的区别

MyISAM： 不支持事务和外键约束，占用空间较小，访问速度快，表级锁定，适用于不需要事务处理、单独写入或查询的应用场景。（写入和查询不一起使用的场景）

InnoDB： 支持事务处理、外键约束、占用空间比MyISAM 大，支持行级锁定，读写开发能力较好，适用于需要事务处理、读写频繁的应用场景。

2. 管理存储引擎

查看系统支持的存储引擎

show engines;

查看表使用的存储引擎

方法一：show table status from 库名 where name='表名'\G

方法二：show create table 表名;

修改存储引擎

方法一：修改已存在的表使用的存储引擎

alter table 表名 engine=存储引擎名称;

方法二：修改配置文件，指定默认存储引擎

vim /etc/my.cnf

[mysqld]

default-storage-engine=InnoDB #修改这一行，指定默认存储引擎为InnoDB

systemctl restart mysqld #重启服务

方法三：创建表时指定存储引擎

create table 表名(字段1 数据类型,...) engine=存储引擎名称;

MySQL数据库之MySQL架构和性能优化

索引

一、索引的概念

二、索引的作用

1. 索引的优点

2. 索引的缺点

3. 索引类型

4. 索引结构

三、 索引管理

1. 查看索引

2. 建立索引

3. 删除索引

4. explain 工具

四、索引优化

五、并发控制

1. 锁机制

1.1 手动加锁

1.2 解锁

1.3 全局加锁

事务

一、事务的概念

二、事务的ACID特点

三、事务的隔离级别

存储引擎

一、存储引擎的概念

二、MySQL常用的存储引擎

三、MyISAM 和 InnoDB 对比

1. MyISAM 引擎特点

2. InnoDB 引擎特点

3. Myisam和InnoDB的区别

四、管理存储引擎

五、MySQL 中的系统数据库

六、服务器配置和状态

1. 服务器选项

2. 服务器变量

3. 状态变量

总结

一、索引

1. 索引的主要作用和副作用

2. 索引是如何提高查询速度的？

二、事务

1. 事务的ACID特性

2. 事务之间的相互影响

3. 事务的隔离级别

三、存储引擎

1. MyISAM和InnoDB的区别

2. 管理存储引擎

三、索引管理