索引
定义：
索引是一个排序的列表，包含索引字段的值和其对应的行记录数据所在的物理地址
作用：
加快表的查询速度（主要作用），还可以对字段排序
副作用：
会额外占用磁盘空间；更新包含索引的表会花费更多的时间
工作方式：
没有索引的情况下，要查询某行记录数据时，需要先扫描全表，再定位某行记录数据的位置
有了索引后，会先通过索引查询到行记录数据所在的物理地址，即可直接访问相应的行记录数据，就像通过书目录的页码快速查找书内容一样。
创建索引的依据：
1）表的记录行数较多时（一般超过三五百行时），应该要创建索引
2）建议在表的 主键字段、外键字段、多表连接使用的公共字段、唯一性较好的字段、不经常更新的字段、where条件字段、分组(group by)字段、排序(order by)字段、短小的字段 上创建索引
3）不建议在 唯一性较差的字段、更新太频繁的字段、大文本字段 上创建索引
索引的分类
普通索引：最基本的索引类型，没有唯一性之类的限制。
直接创建索引
先创建一个新的表
create index 索引名 on 表名(字段(长度));
修改表方式创建
alter table 表名 add index 索引名(字段);
创建表的时候指定索引
create table 表名 (.... , index 索引名(字段));
唯一索引：与普通索引类似，但区别是唯一索引列的每个值都唯一。唯一索引允许有空值（注意和主键不同）。如果是用组合索引创建，则列值的组合必须唯一。添加唯一键将自动创建唯一索引。
create unique index 索引名 on 表名(字段);
alter table 表名 add unique 索引名(字段);
create table 表名 (.... , unique 索引名(字段));
与普通索引相似 添加unique
主键索引：是一种特殊的唯一索引，必须指定为“PRIMARY KEY”。一个表只能有一个主键，不允许有空值。 添加主键将自动创建主键索引。
alter table 表名 add primary key(字段);
create table 表名 (.... , primary key(字段));
与普通索引相似 添加primary
组合索引（单列索引与多列索引）：可以是单列上创建的索引，也可以是在多列上创建的索引。需要满足最左原则，因为 select 语句的 where 条件是依次从左往右执行的，所以在使用 select 语句查询时 where 条件使用的字段顺序必须和组合索引中的排序一致，否则索引将不会生效。
create index 索引名 on 表名(字段1, 字段2, ....);
alter table 表名 add index 索引名(字段1, 字段2, ....);
select 字段列表 from 表名 where 字段1=XX and 字段2=XX .... ; #查询语句使用 and 做逻辑运算符时，字段顺序要与创建多列索引的字段顺序一致（要满足最左原则）
全文索引（FULLTEXT）：适合在进行模糊查询的时候使用，可用于在一篇文章中检索文本信息。在 MySQL5.6 版本以前
FULLTEXT 索引仅可用于 MyISAM 引擎，在 5.6 版本之后 innodb 引擎也支持 FULLTEXT 索引。全文索引可以在 CHAR、VARCHAR 或者 TEXT 类型的列上创建。
直接创建索引
create fulltext index 索引名 on 表名(字段);
修改表方式创建
alter table 表名 add fulltext 索引名(字段);
创建表的时候指定索引
create table 表名 (.... , fulltext 索引名(字段));
全文索引可用作于模糊查询 select 字段列表 from 表名 where match(字段) against('单词');
全文索引只能在类型为CHAR、VARCHAR或者TEXT的字段上创建全文索引，只支持InnoDB和MyISAM引擎。
全文索引只支持英文全文索引，不支持中文全文索引，需要使用ngram全文解析器，用来支持中文全文索引
vim /etc/my.cnf
[mysqld]
ngram_token_size=2 #指定查询的单词的最小字数
create fulltext index 索引名 on 表名(字段) WITH PARSER ngram;;
alter table 表名 add fulltext 索引名(字段) WITH PARSER ngram;;
create table 表名 (.... , fulltext 索引名(字段) WITH PARSER ngram);
select 字段列表 from 表名 where match(字段) against('单词'); #默认使用自然语言模式
select 字段列表 from 表名 where match(字段) against('+单词1 -单词2' IN BOOLEAN MODE); #使用BOOLEAN模式，必须包含"单词1"，且不能包含"单词2"
select 字段列表 from 表名 where match(字段) against('+单词1 +单词2' IN BOOLEAN MODE); #使用BOOLEAN模式，必须同时包含"单词1"和"单词2"
select 字段列表 from 表名 where match(字段) against('单词1 单词2' IN BOOLEAN MODE); #使用BOOLEAN模式，要么包含"单词1"，要么包含"单词2"
查看索引
show index from 表名;
show keys from 表名;
show create table 表名
．删除索引
直接删除索引
drop index 索引名 on 表名;
#这两种方法都可以用来删除普通索引、唯一索引、全文索引、组合索引
修改表方式删除索引
alter table 表名 drop index 索引名;
alter table 表名 drop primary key; #删除主键索引
名字段的含义
遇到 select 查询语句执行速度慢该怎么办？
1）升级 CPU 内存 硬盘 硬件性能
2）对 MySQL 配置进行优化
3）对查询语句的结构进行优化，比如将嵌套子查询优化成表连接查询；或连接表时，可以先用where条件对表进行过滤，然后做表连接
4）进行索引优化：先使用 explain 分析 select 语句，判断这个查询语句是否正确的使用了索引
再根据查询语句中的 where 条件字段建立相应的单列索引或者多列组合索引（多列组合索引要满足最左原则）
事务
事务的概念
定义：
事务就是一组数据库操作序列（包含一个或多个SQL操作命令），事务会把所有操作看作是一个不可分割的整体向数据库系统提交或撤销操作，所有操作要么都执行，要么都不执行。
事务的ACID特点
事务的 ACID 特性：原子性、一致性、隔离性、持久性
原子性：事务管理的基础。把事务中的所有操作看作是一个不可分割的工作单元，要么都执行，要么都不执行。
一致性：事务管理的目的。保证事务开始前和事务结束后数据的完整和一致
隔离性：事务管理的手段。使多个事务并发操作同一个表数据时，每个事务都有各自独立的数据空间，事务的执行不会受到其它事务的干扰。可通过设置隔离级别来解决不同的一致性问题。
持久性：事务管理的结果。当事务被提交以后，事务中的命令操作修改的结果会被持久化保存，且不会被回滚。
//当多个客户端并发地访问同一个表时，可能出现下面的一致性问题：
（1）脏读：当一个事务正在访问数据，并且对数据进行了修改，而这种修改还没有提交到数据库中，这时，另外一个事务也访问这个数据，然后使用了这个数据。
（2）不可重复读：指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，由于第二个事务的修改，那么第一个事务两次读到的的数据可能是不一样的。这样就发生了在一个事务内两次读到的数据是不一样的，因此称为是不可重复读。（即不能读到相同的数据内容）
（3）幻读：一个事务对一个表中的数据进行了修改，这种修改涉及到表中的全部数据行。同时，另一个事务也修改这个表中的数据，这种修改是向表中插入一行新数据。那么，操作前一个事务的用户会发现表中还有一个没有修改的数据行，就好象发生了幻觉一样。
（4）丢失更新：两个事务同时读取同一条记录，A先修改记录，B也修改记录（B不知道A修改过），B提交数据后B的修改结果覆盖了A的修改结果。
隔离级别：
未提交读 Read Uncommitted 允许 脏读 不可重复读 幻读
提交读 Read Committed 不允许 脏读，允许 不可重复读 幻读 （一般生产环境使用的隔离级别）
可重复读 Repeatable Read 不允许 脏读 不可重复读，有条件的允许 幻读（InnoDB存储引擎可以通过多版本并发控制MVCC解决幻读问题）
串行读 Serializable 都不允许，相当于表级锁定，但是会影响数据库的读写效率性能
注：mysql默认的事务处理级别是repeatable read ，而Oracle和SQL Server是read committed
事务隔离级别的作用范围分为两种：
● 全局级：对所有的会话有效
● 会话级：只对当前的会话有效
全局隔离级别修改不会立马生效 必须重新登陆
会话隔离级别修改立马生效 但只在当前会话级别生效
查询全局事务隔离级别：
show global variables like '%isolation%';
设置全局事务隔离级别：
set global transaction isolation level read committed;
查询会话事务隔离级别：
show session variables like '%isolation%';
设置会话事务隔离级别：
set session transaction isolation level repeatable read;
总结：在事务管理中，原子性是基础，隔离性是手段，一致性是目的，持久性是结果。
事务管理操作：
begin; #显式的开启一个事务
.... insert into update 表 set delete from #事务性操作
savepoint XX; #在事务中创建回滚点
rollback to XX; #在事务中回滚到指定的回滚点位置
commit; 或 rollback; #提交或回滚（即撤销） 结束事务
自动提交事务
set global/session autocommit = 0/1 #global全局级别，session会话级别，0关闭自动提交，1开启自动提交
show global/session variables like 'autocommit';
存储引擎
定义：
存储引擎是MySQL数据库的组件，负责执行实际的数据IO操作（数据的存储和提取）。工作在文件系统之上，数据库的数据会先传输到存储引擎，再按照存储引擎的存储格式保存到文件系统。
常用的存储引擎：InnoDB MyISAM
MyISAM 和 InnoDB 的区别？
MyISAM：
不支持事务、外键约束；支持全文索引；锁定类型只支持表级锁定；适合单独的查询和插入的操作；读写会相互阻塞；硬件资源占用较小；数据文件和索引文件是分开存储的，存储成三个文件：表结构文件.frm、数据文件.MYD、索引文件.MYI
使用场景：适用于不需要事务支持，单独的查询或插入数据的业务场景
InnoDB：
支持事务、外键约束；也支持全文索引；锁定类型支持行级锁定（在全表扫描时仍会表级锁定）；读写并发能力较好；缓存能力较好可以减少磁盘IO的压力；数据文件也是索引文件，存储成：表结构文件.frm、表空间文件.ibd
使用场景：适用于需要事务支持，数据一致性要求较高，数据会频繁更新，读写并发高的业务场景
InnoDB行锁与索引的关系：
InnoDB的行级锁是通过给索引项加锁来实现的。如果对没有索引的字段进行操作会使用全表扫描并表级锁定。
MySQL 查询数据的执行过程：
1）客户端向 MySQL 服务器发送一条查询请求，连接器负责处理连接，并进行身份验证和权限控制。
2）MySQL 先检查查询缓存，如果命中缓存，则立刻返回存储在缓存中的结果；否则使用查询解析器进行SQL语句解析、预处理，再由优化器生成对应的执行计划。
3）MySQL 根据执行计划，调用存储引擎来执行查询。
4）将结果返回给客户端，同时缓存查询结果。
查看系统支持的存储引擎
show engines;
查看表使用的存储引擎
方法一：
show table status from 库名 where name='表名'\G
方法二：
use 库名;
show create table 表名;
修改存储引擎
1．通过 alter table 修改
use 库名;
alter table 表名 engine=MyISAM;
2．通过修改 /etc/my.cnf 配置文件，指定默认存储引擎并重启服务
vim /etc/my.cnf
......
[mysqld]
......
default-storage-engine=INNODB
systemctl restart mysql.service
注意：此方法只对修改了配置文件并重启mysql服务后新创建的表有效，已经存在的表不会有变更。
死锁：
定义：死锁是指两个或多个事务在同一个资源上相互占用，并请求对方锁定的资源，从而导致相互阻塞的现象。
如何避免死锁？
1）设置事务的锁等待超时时间 innodb_lock_wait_timeout
2）设置开启死锁检测功能 innodb_deadlock_detect
3）为表建立合理的索引，减少表锁发生的概率
4）如果业务允许，可以降低隔离级别，比如选用 提交读 Read Committed 隔离级别，从而避免间隙锁导致死锁
5）建议开发人员尽量使用更合理的业务逻辑，比如多表操作时以固定顺序访问表，尽量避免同时锁定多个资源
6）建议开发人员尽量保持事务简短，减少对资源的占用时间和占用范围
7）建议开发人员在读多写少的场景下采用乐观锁机制
#乐观锁：乐观锁在操作数据时非常乐观，认为别人不会同时修改数据。因此乐观锁不会上锁，只是在执行更新的时候判断一下在此期间别人是否修改了数据，如果别人修改了数据则放弃操作，否则执行操作。适用于读多写少的场景。
SELECT * from t1 where id = 1 lock in share MODE;
#悲观锁：悲观锁在操作数据时比较悲观，认为别人会同时修改数据。因此操作数据时直接把数据锁住，直到操作完成后才会释放锁；上锁期间其他人不能修改数据。一般适用于写多的场景。
SELECT * from t1 where id = 1 for update;
10万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
