mysql数据库相关

相关命令用到一个记一个吧~

磁盘IO与预读

磁盘读取数据靠的是机械运动，每次读取数据花费的时间可以分为寻道时间、旋转延迟、传输时间三个部分
寻道时间指的是磁臂移动到指定磁道所需要的时间，主流磁盘一般在5ms以下；
旋转延迟就是我们经常听说的磁盘转速，比如一个磁盘7200转，表示每分钟能转7200次，也就是说1秒钟能转120次，旋转延迟就是1/120/2 = 4.17ms；
传输时间指的是从磁盘读出或将数据写入磁盘的时间，一般在零点几毫秒，相对于前两个时间可以忽略不计。
那么访问一次磁盘的时间，即一次磁盘IO的时间约等于5+4.17 = 9ms左右

考虑到磁盘IO是非常高昂的操作，计算机操作系统做了一些优化，当一次IO时，
不光把当前磁盘地址的数据，而是把相邻的数据也都读取到内存缓冲区内，因为局部预读性原理告诉我们，
当计算机访问一个地址的数据的时候，与其相邻的数据也会很快被访问到

mysql page

为什么需要引入page

 在操作系统的概念中，当我们往磁盘中取数据的时候，如果我们要取出来数据的大小是1kb，但操作系统不会只取1kb,
 而是一次性取4kb的数据，因为操作系统的一个页表项的大小是4kb,一个程序在访问了一条数据之后，
 在之后会有极大的可能再次访问这条数据和访问这条数据的相邻数据,所以为了减少磁盘的io次数，会取多余数据



参考：https://blog.haohtml.com/archives/19232
从InnoDB存储引擎的逻辑结构看，所有数据都被逻辑地存放在一个空间内，称为表空间(tablespace)，
而表空间由段（sengment）、区（extent）、页（page）组成。 在一些文档中extend又称块（block）

段（Segment）由一个或多个区组成
当我们创建数据表、索引的时候，就会相应创建对应的段，比如创建一张表时会创建一个表段，创建一个索引时会创建一个索引段。

区：
在文件系统是一个连续分配的空间（在InnoDB中是连续的64个页，64*16KB=1MB）

页：
页是InnoDB存储引擎磁盘管理的最小单位，每个页默认16KB；InnoDB存储引擎从1.2.x版本开始，
可以通过参数innodb_page_size将页的大小设置为4K、8K、16K。若设置完成，则所有表中页的大小都为innodb_page_size

行：
InnoDB存储引擎是按行进行存放的，每个页存放的行记录也是有硬性定义的，最多允许存放16KB/2-200，即7992行记录


页结构整体上可以分为三大部分，分别为通用部分(文件头、文件尾)、存储记录空间、索引部分
页的主要作用是存储记录，所以“最小和最大记录”和“用户记录”部分占了页结构的主要空间
一个页内必须存储2行记录，否则就不是B+tree，而是链表了

在页中，记录是以单向链表的形式进行存储的,单向链表特点插入和删除快，但是检索不好，所以
在页目录中提供了二分查找法，用来提供记录的检索效率。这个过程就好比是给记录创建了一个目录：
将所有的记录分成几个组，这些记录包括最小记录和最大记录，但不包括标记为“已删除”的记录

页目录用来存储每组最后一条记录的地址偏移量，这些地址偏移量会按照先后顺序存储起来，
每组的地址偏移量也被称之为槽（slot），每个槽相当于指针指向了不同组的最后一个记录

页目录存储的是槽，槽相当于分组记录的索引。我们通过槽查找记录，实际上就是在做二分查找
举个例子，例如5个槽的编号分别为 0，1，2，3，4，想查找主键为9的用户记录，我们初始化查找的槽的下限编号，
设置为 low=0，然后设置查找的槽的上限编号 high=4，然后采用二分查找法进行查找

首先找到槽的中间位置 p=(low+high)/2=(0+4)/2=2  这时我们取编号为 2 的槽对应的分组记录中最大的记录，
取出关键字为 8。因为 9 大于 8，所以应该会在槽编号为 (p,high] 的范围进行查找

接着重新计算中间位置 p’=(p+high)/2=(2+4)/2=3，我们查找编号为 3 的槽对应的分组记录中最大的记录，
取出关键字为 12。因为 9 小于 12，所以应该在槽 3 中进行查找

遍历槽 3 中的所有记录，找到关键字为 9 的记录，取出该条记录的信息即为我们想要查找的内容

事物

ACID

针对事务的所有行为都是围绕ACID来做的

• A(Atomicity)原子性

一个事务必须被视为一个不可分割的最小工作单元，整个事务中的所有操作要么全部提交成功，要么全部失败，对于一个事务来说，不可能只执行其中的一部分操作

• C(Consistency) 一致性

数据的一致，操作的一致，

• I(Isolation)隔离性

事务开始和结束之间的中间状态不会被其他事务看到，事务的隔离级别是适当的破坏一致性来提升性能与并行速度

• D(Durability) 持久性

事务提交之后，需要将提交的事务持久化到磁盘。即使系统崩溃，提交的数据也不应该丢失。

事务实现的原理

事务的实现是基于数据库的存储引擎,不同的存储引擎对事务的支持程度不一样，
事务通过破坏一致性来提升性能与并行速度来控制数据库的隔离性.
事务的隔离性是通过锁实现，而事务的原子性、一致性和持久性则是通过事务日志(UNDO+REDO)实现。

事务的核心包括两个方面:
锁和并发,MySQL默认的存储引擎是InnoDB，默认的隔离级别是RR，并且在RR的级别下，通过
多版本并发控制（MVCC，Multiversion Concurrency Control）（解决重复读），乐观锁机制，
加上GAP LOCK解决了幻读问题(不考虑快照读的情况下)，InnoDB的默认隔离级别RR实现了串行化级别的效果
，而且保留了比较好的并发性能。

mysql mvcc实现方式：

1、数据引擎给每张表都加两个字段 create version和delete version
插入时候：记录创建的版本号
更新的时候：旧的那行记录为删除，并且删除版本号是当前事务版本号，再插入新的记录。
删除时候：当前事务版本号作为删除版本号
当我们做查询操作的时候，要符合以下条件才能被事物查询出来
1、delete version>当前版本号 也就是说删除操作是在当前事务启动之后做的
2、create version<=当前事物版本号

数据库事物隔离级别

https://zhuanlan.zhihu.com/p/103580034?utm_source=wechat_session

命令：select @@global.tx_isolation;
set global transaction isolation level repeatable read;

• Read uncommitted

会出现脏读、重复读、幻读情况

• Read committed

会出现重复读、幻读情况

• Repeatable read
  会出现幻读情况，mysql默认级别
  什么时候出现幻读，（）

• Serializable

REDO LOG

为了满足事物的持久性，防止buffer pool数据丢失引入了redo log,redolog用于保证crashsafe能力
通常是物理日志，记录的是数据页的物理修改，而不是某一行或某几行修改成怎样怎样，
它用来恢复提交后的物理数据页(恢复数据页，且只能恢复到最后一次提交的位置)。

UNDO LOG

undo log一般是逻辑日志，根据每行记录进行记录。
为了满足事物的一致性，引入了undo log,在操作任何数据之前，首先将数据备份到Undo Log，然后进行数据的修改。
如果出现了错误或者用户执行了ROLLBACK语句，系统可以利用Undo Log中的备份将数据恢复到事务开始之前的状态。
与redo log不同的是，磁盘上不存在单独的undo log文件，它存放在数据库内部的一个特殊段(segment)中，
这称为undo段(undo segment)，undo段位于共享表空间内,
记录undo log时候也会插入一条redo记录。

事物时序

比如执行一个update操作，a原来的值为1 ，update table set a=3;
1、开始事物
2、记录undo log（记录undo log时候也会写入redo log的）
3、记录redo log
4、将redo log 写入磁盘
5、提交事物
6、binglog
7、刷新磁盘
Undo + Redo事务的特点
为了保证持久性，必须在事务提交前将Redo Log持久，
 数据不需要在事务提交前写入磁盘，而是缓存在内存中

事物回滚

事物回滚其实就是执行当前事物记录的undo日志。

数据恢复

数据恢复其实就是对redo log进行重新执行，因为undo log 也会记录到redo log里面的，
innodb对事物回滚时候的操作也会记录到redo log里面的，回滚操作本质上也是对数据进行修改，
因此回滚时对数据的操作也会记录到Redo Log中。

binlog

binlog日志用于记录所有更新且提交了数据或者已经潜在更新提交了数据（例如，没有匹配任何行的一个DELETE）的所有语句。语句以“事件”的形式保存，它描述数据更改。
binlog作用：1.恢复使能够最大可能地更新数据库，因为二进制日志包含备份后进行的所有更新。2.在主复制服务器上记录所有将发送给从服务器的语句。
binlog一般用于主从同步。

MyISAM与innodb

5.5版本之前默认的存储引擎是myisam ,5.5版本开始MySQL默认存储引擎是innodb
MyISAM锁的力度是表级别，索引结构是⾮聚簇索引；innodb是行级别，索引结构是聚簇索引
MyISAM是不支持事物的，innodb支持。
MyISAM 适合于一些需要大量查询的应用，但其对于有大量写操作并不是很好。
甚至你只是需要update一个字段，整个表都会被锁起来
如果一些初始化大量数据，不需要用到事物，可以先将表类型改成MyISAM插入，完成后
再改成innodb类型 ALTER TABLE test ENGINE = InnoDB;

如何查看连接当前数据库连接数

show full processlist
show variables like 'max_connections';(查可以看当前的最大连接数)
max_connections 为mysql默认配置的连接数量，全局的限制连接数
max_user_connections  这个就是单用户的连接数

慢sql日志开启

show variables like 'slow_query_log';

show global variables like 'long_query_time';
set global long_query_time =1;

查询数据库相关配置

show variables
SELECT VERSION(); mysql版本

获取数据库某个表自增id

当有插入的sql执行后调用下面sql就可以获取到当前表的id
SELECT LAST_INSERT_ID() from  table
mybatis 就是利用这个特性获取到自增id的

表的存储空间大小

mysql创建的表默认的空间大小分数据存储的大小和索引的存储大小
--查询当前数据的所有存储大小
select concat(round(sum(DATA_LENGTH/1024/1024),2),'M') from information_schema.tables where table_schema='DPEducation'
mysql空表默认的data size=16kb  inde size 0kb
--查询单个表的存储大小
select sum(DATA_LENGTH)+sum(INDEX_LENGTH) from information_schema.tables where table_schema='DPEducation' AND table_name='DP_EduCardPromotionScanDetail';
单行记录长度为 data size/row=avg_row_length

表的空间估算

参考

数据库业务特点关键词
OLTP/OLAP、并发请求、读写比例、数据量、冷热数据比、数据分级存储
1、OLTP与OLAP
T=Transaction，面向广大用户，高并发，较短事务操作 互联网应用绝大部分属于OLTP
OLTP看中服务器CPU,内存，写事务较多或内存不够则依赖磁盘IO
A=Analytical，通常面向内部人员，大规模复杂查询，OLAP看中磁盘扫描的IO能力，部分依赖内存排序
2、qps估算与读写比例
3、多读场景相关资源：内存，多写场景相关资源：磁盘IO
4、数据总量与冷热数据比（冷数据迁移历史库）
5、数据库服务器选型
   磁盘IO性能（单盘和盘针 sas和sata 机械盘和SSD）
   内存容量、cpu(单核、多核)
   网络吞吐量（千兆和万兆，单网卡和多路）

mysql千万表

DELIMITER ;;
CREATE DEFINER=`mobile`@`%` PROCEDURE `test2`()
begin
declare v_cnt decimal (10)  default 0 ;
dd:loop
        insert  into `test` values
        (null,'11111111111'),
        (null,'11111111111'),
        (null,'11111111111'),
        (null,'11111111111'),
        (null,'11111111111'),
        (null,'11111111111'),
        (null,'11111111111'),
        (null,'11111111111'),
        (null,'11111111111'),
        (null,'11111111111');
        commit;
        set v_cnt = v_cnt+10 ;
            if  v_cnt = 10000000 then leave dd;
            end if;
        end loop dd ;
end;;
DELIMITER ;
//执行存储过程
call test2();

数据库主从延迟

1、优化Mysql参数，比如增大innodbbufferpool_size，让更多操作在Mysql内存中完成，减少磁盘操作。
2、使用高性能CPU主机
3、数据库使用物理主机，避免使用虚拟云主机，提升IO性能
4、使用SSD磁盘，提升IO性能。SSD的随机IO性能约是SATA硬盘的10倍。
5、业务代码优化，将实时性要求高的某些操作，使用主库做读操作

mysql 大表DDL操作锁表

当给一个千万级表进行ddl操作的时候回锁表，导致服务不可用。
Mysql在5.6版本之前，直接修改表结构的过程中会锁表 ，在线ddl(加字段、加索引等修改表结构之类的操作）
A.对表加锁(表此时只读)
B.复制原表物理结构
C.修改表的物理结构
D.把原表数据导入中间表中，数据同步完后，锁定中间表，并删除原表
E.rename中间表为原表
F.刷新数据字典，并释放锁

对于大表的变更有以下几种方式：
1、选择业务低峰期DDL
2、原表A，新建零时表B，同步B表数据到A表，同时上线双写A、B功能，只读A表，
当A和B表数据一致时候，切换只读B表，再上线只写A表。

mysql 解锁

锁单个表：LOCK TABLES tablename read/write
锁多个表：LOCK TABLES tablename1 read/write,tablename2 read/write

1、找到锁进程，kill id

show processlist;

2、查询到死锁表，再解锁

show OPEN TABLES where In_use > 0;
UNLOCK TABLES

锁的算法

mysql innodb引擎支持事务，更新时采用的是行级锁。
行级锁必须建立在索引的基础
行级锁并不是直接锁记录，而是锁索引，如果一条SQL语句用到了主键索引，mysql会锁住主键索引；
如果一条语句操作了非主键索引，mysql会先锁住非主键索引，再锁定主键索引。
如果操作用到了主键索引会先在主键索引上加锁，然后在其他索引上加锁，否则加锁顺序相反。
对于没有用索引的操作会采用表级锁

的粒度取决于具体的存储引擎，InnoDB实现了行级锁，页级锁，表级锁。
他们的加锁开销从大到小，并发能力也是从大到小

共享锁/排他锁

在 InnoDB 中实现了两个标准的行级锁，可以简单的看为两个读写锁：
    锁与锁之间具有兼容性，兼容性是指事务 A 获得一个某行某种锁之后，事务 B 同样的在这个行上尝试获取某种锁
    ，如果能立即获取，则称兼容，反之叫冲突

    共享锁(Share Lock)又叫读锁，其他事务可以继续加共享锁，但是不能继续加排他锁。
    select…lock in share mode

    排他锁(Exclusive Lock)又叫写锁，一旦加了写锁之后，其他事务就不能加锁了
    select…for update

记录锁(Record Lock)

如果锁的是非主键索引，会在自己的索引上面加锁之后然后再去主键上面加锁锁住。
如果没有表上没有索引(包括没有主键)，则会使用隐藏的主键索引进行加锁。
如果要锁的列没有索引，则会进行全表记录加锁。

间隙锁(Gap Lock)

锁间隙，不锁记录。锁间隙的意思就是锁定某一个范围，间隙锁又叫 gap lock，（开区间）
其不会阻塞其他的gap lcok，但是会阻塞插入意向锁 ，这也是用来防止幻读的关键。RR级别
一般使用 for update 才会用到gap锁

插入意向锁（insertion intention gap lock）

插入意向锁是一种Gap Lock，在insert操作时产生。对于插入的记录会持有一个记录锁
插入意向锁之间互不排斥，所以即使多个事务在同一区间插入多条记录，
只要记录本身（主键、唯一索引）不冲突，那么事务之间就不会出现冲突等待


简单的insert会在insert的行对应的索引记录上加一个排它锁，这是一个record lock，并没有gap
，所以并不会阻塞其他session在gap间隙里插入记录。
不过在insert操作之前，还会加一种锁，官方文档称它为insertion intention gap lock，
也就是意向的gap锁。这个意向gap锁的作用就是预示着当多事务并发插入相同的gap空隙时，
只要插入的记录不是gap间隙中的相同位置，则无需等待其他session就可完成，这样就使得insert操作无须加真正的gap lock。

假设有一个记录索引包含键值4和7，不同的事务分别插入5和6，每个事务都会产生一个加在4-7之间的插入意向锁，
获取在插入行上的排它锁，但是不会被互相锁住，因为数据行并不冲突。

假设发生了一个唯一键冲突错误，那么将会在重复的索引记录上加读锁。
当有多个session同时插入相同的行记录时，如果另外一个session已经获得该行的排它锁，那么将会导致死锁。

插入意向锁作用

提升并发插入能力，
插入意向锁本质上可以看成是一个Gap Lock，普通的Gap Lock 不允许 在 （上一条记录，本记录） 范围内插入数据
插入意向锁Gap Lock 允许 在 （上一条记录，本记录） 范围内插入数据

Next-key Lock

这个锁本质是记录锁+ 间隙锁。

在 RR 隔离级别下(InnoDB 默认)，InnoDB 对于行的扫描锁定都是使用此算法，但是如果查询扫描中有唯一索引会退化成只使用记录锁，
默认情况下，innodb使用next-key locks来锁定记录，锁住索引本身

更新、删除时候锁

更新时候会用到 next-key lock

insert时候锁

InnoDb 在插入记录时，是不加锁的。（加的是隐式锁）
如果事务 A 插入记录且未提交，这时事务 B 尝试对这条记录加锁，事务 B 会先去判断记录上保存的事务 id 是否活跃，
如果活跃的话，那么就帮助事务 A 去建立一个锁对象，然后自身进入等待事务 A 状态，这就是所谓的隐式锁转换为显式锁

首先对插入的间隙加插入意向锁（Insert Intension Locks）
如果该间隙已被加上了 GAP 锁或 Next-Key 锁，则加锁失败进入等待；
如果没有，则加锁成功，表示可以插入；

不同场景下更新锁的情况

id列是主键，RR隔离级别(主键行加行写锁)

id列是二级唯一索引，RR隔离级别 (加两个写锁，一个是聚簇索引上的写锁，一个是unique索引上的写锁)

id列是二级不唯一索引，RR隔离级别
在RR隔离级别下，id列上有非唯一索引，对于上述的SQL语句；首先，通过id索引定位到第一条满足条件的记录，给记录加上X锁，
并且给Gap加上Gap锁，然后在主键聚簇索引上满足相同条件的记录加上X锁，然后返回；
之后读取下一条记录重复进行。直至第一条出现不满足条件的记录，此时，不需要给记录加上X锁，但是需要给Gap加上Gap锁吗，最后返回结果



id列上没有索引，RR隔离级别

快照读与当前读

快照读 就是读取数据的时候会根据一定规则读取事务可见版本的数据
（一般使用 select * from where  **）

当前读 就是读取最新版本读数据
(一般是
 select * from .... where ... for update
 select * from .... where ... lock in share mode
 update .... set .. where ...
 delete from. . where ..
 )
如果事务中都使用快照读，那么就不会产生幻读现象

如果是快照读与当前读共同使用就会产生幻读！！！！

MySQL索引实现

MyISAM索引实现

MyISAM引擎使用B+Tree作为索引结构，叶节点的data域存放的是数据在物理磁盘上的指针。下图是MyISAM索引的原理图

这里设表一共有三列，假设我们以Col1为主键，则图8是一个MyISAM表的主索引（Primary key）示意。
可以看出MyISAM的索引文件仅仅保存数据记录的地址。在MyISAM中，主索引和辅助索引（Secondary key）在结构上没有任何区别，
只是主索引要求key是唯一的，而辅助索引的key可以重复。如果我们在Col2上建立一个辅助索引，则此索引的结构如下图所示：

同样也是一颗B+Tree，data域保存数据记录的地址。因此，MyISAM中索引检索的算法为首先按照B+Tree搜索算法搜索索引，
如果指定的Key存在，则取出其data域的值，然后以data域的值为地址，读取相应数据记录。
MyISAM的索引方式也叫做“非聚集”的，之所以这么称呼是为了与InnoDB的聚集索引区分。

InnoDB索引实现

虽然InnoDB也使用B+Tree作为索引结构，但具体实现方式却与MyISAM截然不同。
第一个重大区别是InnoDB的数据文件本身就是索引文件。从上文知道，MyISAM索引文件和数据文件是分离的，
索引文件仅保存数据记录的地址。而在InnoDB中，表数据文件本身就是按B+Tree组织的一个索引结构，
这棵树的叶节点data域保存了完整的数据记录。这个索引的key是数据表的主键，因此InnoDB表数据文件本身就是主索引。

上图是InnoDB主索引（同时也是数据文件）的示意图，可以看到叶节点包含了完整的数据记录。这种索引叫做聚集索引。
因为InnoDB的数据文件本身要按主键聚集，所以InnoDB要求表必须有主键（MyISAM可以没有），如果没有显式指定，
则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键，如果不存在这种列，则MySQL自动为InnoDB表生成一个隐含字段作为主键，
这个字段长度为6个字节，类型为长整形。

第二个与MyISAM索引的不同是InnoDB的辅助索引data域存储相应记录主键的值而不是地址。
换句话说，InnoDB的所有辅助索引都引用主键作为data域。例如，下图为定义在Col3上的一个辅助索引

聚集索引这种实现方式使得按主键的搜索十分高效，但是辅助索引搜索需要检索两遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。

了解不同存储引擎的索引实现方式对于正确使用和优化索引都非常有帮助，
例如知道了InnoDB的索引实现后，就很容易明白为什么不建议使用过长的字段作为主键，因为所有辅助索引都引用主索引，
过长的主索引会令辅助索引变得过大。再例如，用非单调的字段作为主键在InnoDB中不是个好主意，
因为InnoDB数据文件本身是一颗B+Tree，非单调的主键会造成在插入新记录时数据文件为了维持B+Tree的特性而频繁的分裂调整，
十分低效，而使用自增字段作为主键则是一个很好的选择

B+ 树是如何进行记录检索的

页与页之间是双向链表关系

如果通过 B+ 树的索引查询行记录，首先是从 B+ 树的根开始，逐层检索，直到找到叶子节点，
也就是找到对应的数据页为止，将数据页加载到内存中，页目录中的槽（slot）采用二分查找的方式先找到一个粗略的记录分组，
然后再在分组中通过链表遍历的方式查找记录

计算3层索引树能容纳的数据量

InnoDB存储引擎中页的大小为16KB

首先两个假设：
主键id，我们采用bigint，8字节
一条数据大小1KB
第一层：
一个页16K，每一个索引键的大小8字节（bigint）+6字节（指针大小），因此第一层可存储16*1024/14=1170个索引键
第二层
第二层只存储索引键，能存储多少个索引键呢，因为第一层可以存储1170个索引键，每个都可以存1170 也就是可以存1170*1170=1368900 条索引
如果第二层存储数据，那么可以存储1170*16kb/1kb=18720 一万多条数据
第三层：
1170*1170*16kb/1kb=21902400  3次IO就可以查询到2千多万左右的数据

索引类型和优化

聚簇索引

主键会默认创建聚簇索引，且一张表只允许存在一个聚簇索引，聚簇索引的叶子节点就是数据节点，特点是存储数据的顺序和索引顺序一致。
inodb的主键索引就是聚簇索引。

非聚簇索引

叶子节点仍然是索引节点，只不过有指向对应数据块的指针,索引表中的顺序通常与实际的页码顺序是不一致。
MyISAM的索引方式“非聚集”索引
创建二级索引的代价，主要表现在维护代价、空间代价和回表代价三个方面

最后是回表的代价。二级索引不保存原始数据，通过索引找到主键后需要再查询聚簇索引，才能得到我们要的数据。
比如，使用SELECT*按照login_name字段查询用户，使用EXPLAIN查看执行计划：
explain select * from user where login_name='devym'
1    SIMPLE    user    NULL    const    uidx_login_name    uidx_login_name    514    const    1    100.00    NULL

explain select login_name from user where login_name='devym'
1    SIMPLE    user    NULL    const    uidx_login_name    uidx_login_name    514    const    1    100.00    Using index

可以看到，Extra列多了一行Usingindex的提示，证明这次查询直接查的是二级索引，免去了回表。

联合索引

多个字段建立的索引，组合索引的第一个字段必须出现在查询组句中，不然会导致索引失效
比如组合索引create index index_a_b_c on table（a,b,c）
如果是全列匹配 a,b,c或者c,b,a 都会被优化成abc顺序，匹配联合索引
如果不是全列匹配，例如a|(a,b|b,a)| 都只会匹配到最左前缀索引 a
如果不包含最左前缀的索引列，就会导致索引失效，例如 b,c或者c,b
最左前缀就是最左的索引列优先，b,a都会被数据库引擎优化成ab这种顺序。

前缀索引(索引优化策略)

    有一种与索引选择性有关的索引优化策略叫做前缀索引，就是用列的前缀代替整个列作为索引key，当前缀长度合适时，
    可以做到既使得前缀索引的选择性接近全列索引，同时因为索引key变短而减少了索引文件的大小和维护开销

利用如下SQL计算索引命中率：
//从左开始截取字符串
select count(distinct left(pinyin_initial,3))/count(*) as sel3,
count(distinct left(pinyin_initial,4))/count(*) as sel4,
count(distinct left(pinyin_initial,5))/count(*) as sel5,
count(distinct left(pinyin_initial,6))/count(*) as sel6,
count(distinct left(pinyin_initial,7))/count(*) as sel7
from city;
以此算出城市拼音缩写长度为3时，命中率和唯一性比较高，则写下如下SQL：
ALTER TABLE `city` ADD INDEX `index_on_pinyinInitial` USING BTREE (pinyin_initial(3));

最左前缀原理与相关优化

create index index_a_b_c on table（a,b,c）

全列匹配

select * from table where a=? and b=? and c=?
mySQL的查询优化器会自动调整where子句的条件顺序以使用适合的索引
=和in可以乱序

最左前缀匹配

select * from table where a=?
select * from table where a=? and b=?
当查询条件精确匹配索引的左边连续一个或几个列时,索引的第一列前缀

查询条件用到了索引中列的精确匹配，但是中间某个条件未提

select * from table where a=? and c=?
根据最左前缀匹配原则，只会使用索引的第一列前缀

查询条件没有指定索引第一列。

select * from table where  c=?
索引失效

范围查询

 mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，
   select * from table where a = 1 and b = 2 and c > 3 and d = 4
范围列可以用到索引（必须是最左前缀），但是范围列后面的列无法用到索引。
同时，索引最多用于一个范围列，因此如果查询条件中有两个范围列则无法全用到索引。
如果建立(a,b,c,d)顺序的索引，d是用不到索引的。

如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
因为sql where a=1 and b=2 and c>3 and d=4会被优化成 where a=1 and b=2 and d=4 and c>3

CREATE TABLE `tb_project_user` (
  `id` int(11) unsigned NOT NULL AUTO_INCREMENT,
  `type` int(11) DEFAULT NULL,
  `user_id` int(11) DEFAULT NULL,
  `project_id` int(11) DEFAULT NULL,
  `is_deleted` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `ix_type_user_project` (`type`,`user_id`,`project_id`)
) ENGINE=InnoDB AUTO_INCREMENT=4 DEFAULT CHARSET=utf8;
会走两个type和user_id 索引
explain select * from tb_project_user where  type=1 and user_id>1 and project_id=1
会走三个 type 、user_id、project_id、索引
explain select * from tb_project_user where  type=1  and project_id>1 and user_id=1

索引选择性

索引文件本身要消耗存储空间，同时索引会加重插入、删除和修改记录时的负担，另外，MySQL在运行时也要消耗资源维护索引，因此索引并不是越多越好。
一般两种情况下不建议建索引。
1、数据量比较少，
2、另一种不建议建索引的情况是索引的选择性较低。
   所谓索引的选择性（Selectivity），是指不重复的索引值（也叫基数，Cardinality）与表记录数（#T）的比值
   SELECT count(DISTINCT(xx))/count(*) AS xx FROM table;

数据库根据成本决定是否走索引

IO成本和CPU成本：
IO成本，是从磁盘把数据加载到内存的成本。默认情况下，读取数据页的IO成本常数是1（也就是读取1个页成本是1）。
CPU成本，是检测数据是否满足条件和排序等CPU操作的成本。默认情况下，检测记录的成本是0.2。

MySQL选择索引，并不是按照WHERE条件中列的顺序进行的；
即便列有索引，甚至有多个可能的索引方案，MySQL也可能不走索引

其原因就是，MySQL并不是猜拳决定是否走索引的，而是根据成本来判断的。虽然表的统计信息不完全准确，但足够用于策略的判断了。
不过，有时会因为统计信息的不准确或成本估算的问题，实际开销会和MySQL统计出来的差距较大，
导致MySQL选择错误的索引或是直接选择走全表扫描，这个时候就需要人工干预，使用强制索引了。比如，像这样强制走name_score索引：

  EXPLAIN SELECT * FROM person FORCE INDEX(name_score) WHERE NAME >'name84059' AND create_time >'2020012405:00:00'

如果对EXPLAIN给出的执行计划有疑问的话， 你还可以利用optimizer_trace查看详细的执行计划做进一步分析

建索引的几大原则

1.最左前缀匹配原则，非常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = 4 如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。
2.=和in可以乱序，比如a = 1 and b = 2 and c = 3 建立(a,b,c)索引可以任意顺序，mysql的查询优化器会帮你优化成索引可以识别的形式
3.尽量选择区分度高的列作为索引,区分度的公式是count(distinct col)/count(*)，表示字段不重复的比例，比例越大我们扫描的记录数越少，
唯一键的区分度是1，而一些状态、性别字段可能在大数据面前区分度就是0，那可能有人会问，这个比例有什么经验值吗？使用场景不同，这个值也很难确定，
一般需要join的字段我们都要求是0.1以上，即平均1条扫描10条记录
4.索引列不能参与计算，保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);
5.尽量的扩展索引，不要新建索引。比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可

explain

rows是核心指标，绝大部分rows小的语句执行一定很快（有例外，下面会讲到）。所以优化语句基本上都是在优化rows。

“select_type” “type” “possible_keys” “key” “key_len” “ref” “rows” “filtered” “Extra”

select_type

select_type：执行类型 simple为简单查询类型,
(1) SIMPLE(简单SELECT，不使用UNION或子查询等)
(2) PRIMARY(子查询中最外层查询，查询中若包含任何复杂的子部分，最外层的select被标记为PRIMARY)
(3) UNION(UNION中的第二个或后面的SELECT语句)
(4) DEPENDENT UNION(UNION中的第二个或后面的SELECT语句，取决于外面的查询)
(5) UNION RESULT(UNION的结果，union语句中第二个select开始后面所有select)
(6) SUBQUERY(子查询中的第一个SELECT，结果不依赖于外部查询)
(7) DEPENDENT SUBQUERY(子查询中的第一个SELECT，依赖于外部查询)
(8) DERIVED(派生表的SELECT, FROM子句的子查询)
(9) UNCACHEABLE SUBQUERY(一个子查询的结果不能被缓存，必须重新评估外链接的第一行)

type

对表访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”。
常用的类型有： ALL、index、range、 ref、eq_ref、const、system、NULL（从左到右，性能从差到好）

ALL：

Full Table Scan， MySQL将遍历全表以找到匹配的行
explain select * from res_book where book_name="dd"

index

Full Index Scan，index与ALL区别为index类型只遍历索引树
explain select version_id from res_book
可以看下index与 ref类型的区别

range

只检索给定范围的行，使用一个索引来选择行
explain select * from res_book where id in(3,10011)

ref

表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值

explain select * from res_book where version_id=1

eq_ref

类似ref，区别就在使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配，简单来说，就是多表连接中使用primary key或者 unique key作为关联条件

explain select * from kg_chapter k, res_book b where k.book_id=b.id and b.id=3

const、system

当MySQL对查询某部分进行优化，并转换为一个常量时，使用这些类型访问。
如将主键置于where列表中，MySQL就能将该查询转换为一个常量，system是const类型的特例，当查询的表只有一行的情况下，使用system

explain select * from res_book where id =3

NULL

MySQL在优化过程中分解语句，执行时甚至不用访问表或索引，例如从一个索引列里选取最小值可以通过单独索引查找完成。

key

实际使用的索引。如果为NULL，则没有使用索引

rows

MySQL认为必须检查的用来返回请求数据的行数


key_len的长度计算公式：
所有的索引字段，如果没有设置not null，则需要加一个字节。

定长字段，int占四个字节、TINYINT 占用1个字节，biging 占用8个字节，date占三个字节、char(n)占n个字符。

varchar(n) n表示字符数
对于变成字段varchar(n)，则有n个字符+两个字节。
不同的字符集，一个字符占用的字节数不同。latin1编码的，一个字符占用一个字节，gbk编码的，一个字符占用两个字节，utf8编码的，一个字符占用三个字节。
varchr(10)变长字段且允许NULL    =  10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL)+2(变长字段) ：字节数
varchr(10)变长字段且不允许NULL =  10 *( character set：utf8=3,gbk=2,latin1=1)+2(变长字段) ：字节数

varchar最大长度可以是多少?
根据字符集，字符类型若为gbk，每个字符占用2个字节，最大长度不能超过32766，字符类型若为utf8
，每个字符最多占用3个字节，最大长度不能超过21845，若超过这个限制，则会自动将varchar类型转为mediumtext或longtext


char(10)固定字段且允许NULL        =  10 * ( character set：utf8=3,gbk=2,latin1=1)+1(NULL) ：字节数
char(10)固定字段且不允许NULL        =  10 * ( character set：utf8=3,gbk=2,latin1=1) ：字节数

text  65,535 bytes  约 64k  如果换算成字符串的话 一个字符占用3字节，那就是对应有64*1024/3=21845个字符串。
LONGTEXT  4,294,967,295 bytes ~4GB

所以以后设置不可变的文本的话最好是用长文本来存储！！

MySQL要求一个行定义长度不能超过65535个字节，不包括text、blob等大字段类型，varchar长度受此长度限制，和其他非大字段加起来不能超过65535个字节

考虑额外创建二级索引的代价

首先是维护代价。创建N个二级索引，就需要再创建N棵B+树，新增数据时不仅要修改聚簇索引，还需要修改这N个二级索引。
其次是空间代价。虽然二级索引不保存原始数据，但要保存索引列的数据，所以会占用更多的空间
最后是回表的代价。二级索引不保存原始数据，通过索引找到主键后需要再查询聚簇索引，才能得到我们要的数据

数据库基于成本决定是否走索引

查询数据可以直接在聚簇索引上进行全表扫描，也可以走二级索引扫描后到聚簇索引回表
这里的成本，包括IO成本和CPU成本
IO成本，是从磁盘把数据加载到内存的成本。默认情况下，读取数据页的IO成本常数是1
CPU成本，是检测数据是否满足条件和排序等CPU操作的成本。默认情况下，检测记录的成本是0.2

MySQL选择索引，并不是按照WHERE条件中列的顺序进行的；
即便列有索引，甚至有多个可能的索引方案，MySQL也可能不走索引。

可以通过以下命令查询索引执行情况：
SET optimizer_trace="enabled=on";
SELECT*FROM information_schema.OPTIMIZER_TRACE;
SET optimizer_trace="enabled=off";

//force index(强制走索引)

数据库连接池

连接池大小

initialPoolSize=10、maxPoolSize=20、minPoolSize=10
如果只是单体应用访问数据库，最大连接数可以设置大一点
如果有N个应用访问同一个数据库，最大连接数就要控制。比较数据库的最大连接数是有限制的。

连接池的大小设置

是不是连接池的最大连接数设置越大越好勒？
用jmeter自带的数据库压测模板做了个测试，100并发，每个线程循环执行100次，也就是10000次查询。
可以看到在并发量不变的情况下，线程池数量的减小，平均响应时间也在减小。

线程池大小	Average	Median	90% Line	95% Line	99% Line	Min	Max
80pool	566	104	728	1658	26801	28	30247
50pool	501	215	838	952	14899	28	17089
30pool	454	296	537	982	8017	28	11520
20pool	416	331	554	631	3577	29	8138

一颗CPU核心同一时刻只能执行一个线程，然后操作系统切换上下文，核心开始执行另一个线程的代码，以此类推。
给定一颗CPU核心，其顺序执行A和B永远比通过时间分片“同时”执行A和B要快，这是一条计算机科学的基本法则。
一旦线程的数量超过了CPU核心的数量，再增加线程数系统就只会更慢，而不是更快。

当然连接池的大小我们还需要考虑磁盘、网络等情况，因为如果在遇到长事物的慢查询里面会一直占着这个连接，
如果这个时候连接池大小比较小就会导致链接不够用。

连接池常见参数

initialSize:池初始大小
maxActive：池支持的最大连接数
minIdle：池中最小可空闲maxIdle个连接
maxIdle：池中最多可空闲maxIdle个连接
maxWait：连接池中连接用完时,新的请求等待时间
timeBetweenEvictionRunsMillis：配置间隔多久才进行一次检测，检测需要关闭的空闲连接，相当于C3P0的idleConnectionTestPeriod 参数，单位是毫秒
minEvictableIdleTimeMillis：配置一个连接在池中最小生存的时间，单位是毫秒
timeBetweenEvictionRunsMillis与minEvictableIdleTimeMillis搭配使用：
timeBetweenEvictionRunsMillis毫秒秒检查一次连接池中空闲的连接,
把空闲时间超过minEvictableIdleTimeMillis毫秒的连接断开,直到连接池中的连接数到minIdle为止

参考博客

1、MySQL索引原理及慢查询优化
2、MySQL索引背后的数据结构及算法原理
3、“MySQL中B+Tree索引原理”