索引sql优化
最左前缀法则，如果创建是联合索引，就要遵守该法则，使用索引时，where后面需要从索引最左列开始使用，不能跳过索引列使用。
不要在索引列上做任何计算，字符串不加单引号，需要进行隐式转换转向全表扫描。
范围查询之后全失效
避免使用is null is not null ，!= <> or
like 以% 开头会使索引失效，两边都有%索引也会失效。
like失效原理，由于b+树索引顺序按照首字母大小进行排序，%在右匹配又是首字母可以在b树进行有序查找，符合要求。
%在左是匹配尾部的数据，尾部的字母是没有顺序的，所以不能按照索引顺序查找，所以用不到索引。
%%是查询任意位置字母满足条件即可，只有首字母是进行索引排序的，其他位置的字母相对无序的，查找任意位置就用不到索引。
数据库死锁
表死锁：
用户a访问表a锁了表a，又访问表b，另一个用户访问表b锁了表b，又去访问表a，a等b释放锁，b等a释放锁。
是由于程序的BUG产生的，除了调整的程序的逻辑没有其它的办法。仔细分析程序
的逻辑，对于数据库的多表操作时，尽量按照相同的顺序进行处理，尽量避免同时锁定两个资源，如操
作A和B两张表时，总是按先A后B的顺序处理， 必须同时锁定两个资源时，要保证在任何时刻都应该按
照相同的顺序来锁定资源。
行死锁：
如果在事务中执行了一条没有索引条件的查询，引发全表扫描，把行级锁上升为全表记录锁定（等价于
表级锁），多个这样的事务执行后，就很容易产生死锁和阻塞，最终应用系统会越来越慢，发生阻塞或
死锁。
解决方案1：
SQL语句中不要使用太复杂的关联多表的查询；使用explain“执行计划"对SQL语句进行分析，对于有全
表扫描和全表锁定的SQL语句，建立相应的索引进行优化。
产生原因2：
两个事务分别想拿到对方持有的锁，互相等待，于是产生死锁。、
每个事务只有一个SQL,但是有些情况还是会发生死锁.比如两个session操作同一个表的同一个id，一个查询修改，一个删除，同时占用对方的id
但是加锁时发现跟事务1的加锁顺序正好相反，两个Session恰好都持有了第一把锁，请求加第二
把锁，死锁就发生了。
对索引加锁顺序的不一致很可能会导致死锁，所以如果可以，尽量
以相同的顺序来访问索引记录和表。在程序以批量方式处理数据的时候，如果事先对数据排序，保证每
个线程按固定的顺序来处理记录，也可以大大降低出现死锁的可能；
死锁总结
1. 对索引加锁顺序的不一致很可能会导致死锁, 所以如果可以, 尽量以相同的顺序来访问索引记录和
表. 在程序以批量方式处理数据的时候, 如果事先对数据排序, 保证每个线程按固定的顺序来处理记
录, 也可以大大降低出现死锁的可能.
2. 间隙锁往往是程序中导致死锁的真凶, 由于默认情况下 MySQL 的隔离级别是 RR,所以如果能确定
幻读和不可重复读对应用的影响不大, 可以考虑将隔离级别改成 RC, 可以避免 Gap 锁导致的死锁.
3. 为表添加合理的索引, 如果不走索引将会为表的每一行记录加锁, 死锁的概率就会大大增大.
4. 避免大事务, 尽量将大事务拆成多个小事务来处理. 因为大事务占用资源多, 耗时长, 与其他事务冲
突的概率也会变高.
5. 避免在同一时间点运行多个对同一表进行读写的脚本, 特别注意加锁且操作数据量比较大的语句.
6. 设置锁等待超时参数：innodb_lock_wait_timeout，在并发访问比较高的情况下，如果大量事务
因无法立即获得所需的锁而挂起，会占用大量计算机资源，造成严重性能问题，甚至拖跨数据库。
我们通过设置合适的锁等待超时阈值，可以避免这种情况发生。
数据库的锁种类
读写锁：
读写锁就是进一步细化锁的颗粒度，区分读操作和写操作，让读和读之间不加锁，这样下面的两个事务
就可以同时被执行了。
读写锁，可以让读和读并行，而读和写、写和读、写和写这几种之间还是要加排他锁。
读写锁：
读和写操作：读读、写写、读写、写读。
共享锁，又称之为读锁，简称S锁，当事务对数据加上读锁后，其他事务只能对该数据加读锁，不能做任何修改操作，也就是不能添加写锁。只有当数据上的读锁被释放后，其他事务才能对其添加写锁。共享锁主要是为了支持并发的读取数据而出现的，读取数据时，不允许其他事务对当前数据进行修改操作，从而避免”不可重读”的问题的出现。
排它锁，又称之为写锁，简称X锁，当事务对数据加上写锁后，其他事务既不能对该数据添加读写，也不能对该数据添加写锁，写锁与其他锁都是互斥的。只有当前数据写锁被释放后，其他事务才能对其添加写锁或者是读锁。写锁主要是为了解决在修改数据时，不允许其他事务对当前数据进行修改和读取操作，从而可以有效避免”脏读”问题的产生。
全局锁是对整个数据库实例加锁，添加全局锁后,以下语句会被阻塞：数据更新语句（增删改）、
数据定义语句（建表、修改表结构等）和更新类事务的提交语句。
表级锁：表级锁是mysql中锁定粒度最大的一种锁，表示对当前操作的整张表加锁，它实现简单，资源消耗较少，被大部分mysql引擎支持。
行级锁. 行级锁是Mysql中锁定粒度最细的一种锁，表示只针对当前操作的行进行加锁。.
行级锁能大大减少数据库操作的冲突。. 其加锁粒度最小，但加锁的开销也最大。. 行级锁分为 共享锁 和 排他锁 。.
页级锁：每次锁定相邻的一组记录，锁定粒度界于表锁和行锁之间，开销和加锁时间界于表锁和行
锁之间，并发度一般。
乐观锁：一般的实现方式是对记录数据版本进行比对，在数据更新提交的时候才会进行冲突检测，
如果发现冲突了，则提示错误信息。
悲观锁：在对一条数据修改的时候，为了避免同时被其他人修改，在修改数据之前先锁定，再修改
的控制方式。共享锁和排他锁是悲观锁的不同实现，但都属于悲观锁范畴。
乐观锁实现原理
使用版本字段（version）
先给数据表增加一个版本(version) 字段，每操作一次，将那条记录的版本号加 1。version
是用来查看被读的记录有无变化，作用是防止记录在业务处理期间被其他事务修改。
使用时间戳（Timestamp）
与使用version版本字段相似,同样需要给在数据表增加一个字段,字段类型使用timestamp时
间戳.也是在更新提交的时候检查当前数据库中数据的时间戳和自己更新前取到的时间戳进行
对比，如果一致则提交更新，否则就是版本冲突,取消操作。
SQL优化思路
慢查询SQL优化思路，当我们拿到sql之后可以进行以下分析：
等待时间长，锁表导致查询一直处于等待状态
执行时间长
1.查询sql写的烂
2.索引失效
3.关联查询太多的join
4.服务器调优以及各个参数的设置
优化思路，优先选择优化高并发的sql，因为并发高的sql带来的后果更加严重
然后从explain执行计划入手
1. 用小结果集驱动大的结果集
2.尽可能在索引中完成排序
3.只获取自己需要的列
4.只是用有效的过滤条件
5.避免复杂的join和子查询
6.合理设计并利用索引
如果子查询得出的结果集记录较少，主查询中的表较大且又有索引时应该用 in
如果主查询得出的结果集记录较少，子查询中的表较大且又有索引时应该用 exists
一句话: in后面跟的是小表，exists后面跟的是大表。
索引排序: 通过有序索引顺序扫描直接返回有序数据
额外排序: 对返回的数据进行文件排序
ORDER BY优化的核心原则: 尽量减少额外的排序，通过索引直接返回有序数据。
ORDER BY排序字段要么全部正序排序，要么全部倒序排序，否则无法利用索引排序。
说说数据库事务
说说数据库事务
说说数据库事务
acid
原子性，一致性，隔离性，持久性
原子性，事务是一个原子性操作，对修改的数据要么全部执行，要么全都不执行。
事务提交之后redo log刷入磁盘，挂机后根据redo log回复事务修改的缓存数据。
如果要回滚事务，根据undo log回滚。
一致性：事务开始前和事务开始之后，数据的完整性未被破坏，包括约束一致性和数据一致性。
隔离性：事务执行不能被其他事务干扰，事务内操作以及使用对其他的事务是隔离的。
不考虑隔离性会引发，脏读，一个事务读取到另一个事务修改但未提交的数据。
不可重复度，一个事务中多次读取同一行记录的结果不一致。
幻读：幻读指当用户读取某一范围的数据行时，另一个事务又在该范围内插入了新行，当用户再读取该范围的数据行时，会发现有新的“幻影” 行。
innodb支持的隔离有四种：读未提交，读提交，可重复读，串行化，锁和多版本控制。
持久性：指的是一个事务一旦提交，它对数据库中数据的改变就应该是永久性的，后续的操作或故障不
应该对其有任何影响，不会丢失。
MySQL持久性的保证依赖两个日志文件: redo log 和 binlog
Explain性能分析
通过explain我们可以获得以下信息：
表的读取顺序。（对应id）
数据读取操作的操作类型。（对应select_type）
哪些索引可以使用。（对应possible_keys）
哪些索引被实际使用。（对应key）
表直接的引用。（对应ref）
每张表有多少行被优化器查询。（对应rows）
2) ID字段说明
select查询的序列号，包含一组数字，表示查询中执行select子句或操作表的顺序
id相同，执行顺序由上至下
id不同，如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行
3) select_type和table字段说明
3) select_type和table字段说明
表示查询类型，主要用于区别普通查询，联合查询，子查询等的复杂查询
union : union连接的两个select查询，第一个查询是dervied派生表，除了第一个表外，第二个
以后的表select_type都是union
derived : 在from列表中包含的子查询被标记为derived（派生表），MySQL会递归执行这些子
查询，把结果放到临时表中
union result : UNION 的结果
4) type字段说明
system: 表中就仅有一行数据的时候. 这是const连接类型的一个特例,很少出现。
const: const表示命中主键索引(primary key) 或者唯一索引(unique),表示通过索引一次就找到数
据记录.
eq_ref : 对于前一个表中的每个一行，后表只有一行被扫描。除了system和const类型之外，这是
最好的连接类型。只有当联接使用索引的部分都是主键或惟一非空索引时，才会出现这种类型。
ref : 非唯一性索引扫描(使用了普通索引), 对于前表的每一行(row)，后表可能有多于一行的数据被
扫描,它返回所有匹配某个单独值的行.
range : 索引上的范围查询,检索给定范围的行,between，in函数，> 都是典型的范围(range)查询,
例如以下查询:
index : 出现index 是 SQL 使用了索引, 但是没有通过索引进行过滤,需要扫描索引上的全部数据 (
查找所有索引树,比ALL快一些,因为索引文件要比数据文件小 ), 一般是使用了索引进行排序分组.
ALL : 没有使用到任何索引, 连接查询时对于前表的每一行，后表都要被全表扫描。
5) possible_keys 与 key说明
possible_keys
显示可能应用到这张表上的索引, 一个或者多个. 查询涉及到的字段上若存在索引, 则该索引将被列
出, 但不一定被查询实际使用.
key
实际使用的索引，若为null，则没有使用到索引。（两种可能，1.没建立索引, 2.建立索引，但索引
失效）。查询中若使用了覆盖索引，则该索引仅出现在key列表中。
6) key_len字段说明
表示索引中使用的字节数, 可以通过该列计算查询中使用索引的长度.
key_len 字段能够帮你检查是否充分利用了索引 ken_len 越长, 说明索引使用的越充分
7) ref 字段说明
显示索引的哪一列被使用了，如果可能的话，是一个常数。哪些列或常量被用于查找索引列上的值
8) rows 字段说明
表示MySQL根据表统计信息及索引选用情况,估算的找到所需的记录所需要读取的行数
9) filtered 字段说明
它指返回结果的行占需要读到的行(rows列的值)的百分比
10) extra 字段说明
Extra 是 EXPLAIN 输出中另外一个很重要的列，该列显示MySQL在查询过程中的一些详细信息.
Using filesort
执行结果Extra为 Using filesort ，这说明，得到所需结果集，需要对所有记录进行文件排序。
这类SQL语句性能极差，需要进行优化。
Using temporary
表示MySQL需要使用临时表来存储结果集，常见于排序和分组查询
Using where
意味着全表扫描或者在查找使用索引的情况下,但是还有查询条件不在索引字段当中.
需要注意的是：
1. 返回所有记录的SQL，不使用where条件过滤数据，大概率不符合预期，对于这类SQL往往需
要进行优化；
2. 使用了where条件的SQL，并不代表不需要优化，往往需要配合explain结果中的type（连接
类型）来综合判断。例如本例查询的 age 未设置索引，所以返回的type为ALL，仍有优化空
间，可以建立索引优化查询。
Using index
表示直接访问索引就能够获取到所需要的数据(覆盖索引) , 不需要通过索引回表.
Using join buffer
使用了连接缓存, 会显示join连接查询时,MySQL选择的查询算法 .
Using index condition
查找使用了索引 (但是只使用了一部分,一般是指联合索引)，但是需要回表查询数.
Extra主要指标的含义(有时会同时出现)
using index ：使用覆盖索引的时候就会出现
using where ：在查找使用索引的情况下，需要回表去查询所需的数据
using index condition ：查找使用了索引，但是需要回表查询数据
using index & using where ：查找使用了索引，但是需要的数据都在索引列中能找到，所以
不需要回表查询数据
mysql的索引
索引会存储在数据文件中，可以加快检索速度，同时会降低增删改的效率，维护需要代价。
MySQL中索引的常用数据结构有两种: 一种是BTree,另一种则是Hash.
hash是用hash表来实现，适合等值，对每行数据计算一个hash码，如果相同会拉取一条链表，等值查询效率高，不适合范围查找和排序。
btree索引，分为聚簇索引和非聚簇索引。
聚簇索引是数据和索引放在一块。
非聚簇索引是数据和索引分开存储，索引结构的叶子节点指向数据行的位置。
InnoDB的表要求必须要有主键索引：
如果表定义了主键，则主键索引就是聚簇索引
如果表没有定义主键，那么 InnoDB 会使用第一个唯一索引作为聚簇索引
否则InnoDB会创建一个隐藏的row-id作为聚簇索引，使用主键索引最好使用int类型的自增，对树的影响最小，主键占用空间越大辅助索引保存的主键索引也会跟着变大，占用存储空间。
不建议使用uuid，因为uuid的值太过离散，不适合排序且可能会插入在索引树中间的位置。
二级索引：叶子节点中存储主键值，每次查找数据时，根据索引找到叶子节点中的主键值，根据主键值再到聚簇索引中得到完整的一行记录。
一个表InnoDB只能创建一个聚簇索引，但可以创建多个辅助索引。
常用的二级索引包括：
唯一索引(Unique Key) ：唯一索引也是一种约束。唯一索引的属性列不能出现重复的数据，但是允许数据为 NULL，一张表允许创建多个唯一索引。
普通索引(Index) ：普通索引的唯一作用就是为了快速查询数据，一张表允许创建多个普通索引，并允许数据重复和 NULL。
前缀索引(Prefix) ：前缀索引只适用于字符串类型的数据。前缀索引是对文本的前几个字符创建索引。
组合索引：将多个列作为一个索引，最左前缀原则进行匹配、
回表: 先根据普通索引查询到主键值，再根据主键值在聚集索引中获取行记录,这就是回表.
什么是覆盖索引：如果一个索引包含了所有需要查询的字段的值 ，不需要查询聚集索引，这个索引就是覆盖索引。
mysql的数据结构
MySQL默认使用B+树结构管理索引,B+树中的B代表平衡（balance ),在讲B+树之前必须先了解二叉树、
平衡二叉树(AVL) 和 B-Tree，因为B+Tree即由这些树逐步优化而来。
二叉查找树的缺点：
二叉查找树是要求左低右高，如果数据一个比一个大，就会形成单向链表，层数过深，查找效率过慢。
平衡二叉树：在符合二叉查找树的条件下还满足任何节点的两个子树的高度最大差为1，通过旋转的方式保证两个子树的平衡。
优点：叶子节点层级减少了，形态上保持平衡，查询效率提升了，大量的顺序插入也不会导致性能降低
缺点：
一个节点最多分裂2个子节点，，数据量大的时候数的高度太高，导致io次数过多。
节点保存着一个关键字，每次操作获取目标数据太少。
b+树是由二叉查找树演变而来的，b树第一层是根节点，第二层是分支节点，第三层是叶子节点，减少树的层级深度，允许一个节点存放多个数据
键值对[key, data] ，key为记录的键值，对应表中的主键值(聚
簇索引)，data为一行记录中除主键外的数据。
优点: B树可以在内部节点存储键值和相关记录数据，因此把频繁访问的数据放在靠近根节点的位
置将大大提高热点数据的查询效率。
缺点: B树中每个节点不仅包含数据的key值,还有data数据. 所以当data数据较大时,会导致每个节点
存储的key值减少,并且导致B树的层数变高.增加查询时的IO次数.
b+树
非叶子节点只存储键值信息.
所有叶子节点之间都有一个链指针.
数据记录都存放在叶子节点中.
1.B+Tree是B Tree的变种，B Tree能解决的问题，B+Tree也能够解决
2.B+Tree排序能力更强，B+Tree天然具有排序功能。
3.B+Tree查询效率更加稳定，每次查询数据，查询IO次数一定是稳定的。
4.+Tree磁盘读写能力更强，他的根节点和支节点不保存数据区，所有根节点和支节点同样大小的
情况下，保存的关键字要比B Tree要多。
5.B+Tree扫库和扫表能力更强，如果我们要根据索引去进行数据表的扫描，对B Tree进行扫描，需
要把整棵树遍历一遍，而B+Tree只需要遍历他的所有叶子节点即可