Mysql进阶知识

仅个人笔记

一、索引

索引是帮助MYSQL高效获取数据的数据结构–>排好序的快速查找数据结构

我们平时所说的索引，没有特别指明，都是指B树（多路搜索树，不一定是二叉）
结构组织的索引。其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引默认都是使用B+树索引，统称索引，除了B+树这种类型的索引外，还有哈希索引等。

优势：

1. 类似大学图书馆书目索引，提高数据检索效率，降低数据库的io成本。
2. 通过索引列对数据进行排序，降低数据排序的成本，降低了CPU的消耗。

劣势：

1. 实际上索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录，所以索引列也是要占用空间的。
2. 虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERTE,UPDATE,和DELETE。因为更新表时，MYSQL不仅要保存数据，还要保存一下索引文件每次更新添加了索引列的字段，都会调整因为更新所带来的键值变化后的索引信息。（维护B+树索引结构）
3. 索引只是提高效率的一个因素，如果你的MYSQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。

索引分类

基本语法

创建：

CREATE [UNIQUE] INDEX indexName ON myTable(clumn name(length));

使用ALTER命令创建：

1. 添加主键（意味着索引值必须唯一，且不能为NULL）

ALTER TABLE mytable ADD PRIMARY KEY(column_list)

1. 创建唯一索引（索引值唯一，可以为NULL,为NULL可重复出现）

ALTER TABLE mytable ADD UNIQUE index_name (column_list)

1. 添加普通索引（索引值可以出现多次）

ALTER TABLE mytable ADD INDEX index_name(column_list)

1. 指定索引为FULLTEXT,用于全文索引

ALTER TABLE mytable ADD FULL TEXT index_name(column_list)

删除：

DROP INDEX [indexName] ON mytable;

查看：

SHOW INDEX FROM table_name

哪些情况需要创建索引

1. 主键自动建立唯一索引
2. 频繁作为查询条件的字段应该创建索引
3. 查询中与其他表关联的字段，外键关系建立索引
4. 频繁更新的字段不适合创建索引（因为每次更新都要维护索引结构）
5. where条件里用不到的字段不创建索引
6. 在高并发下倾向创建组合索引
7. 查询中排序的字段，排序字段若通过索引去访问将大大提高排序速度
8. 查询中统计或者分组字段

哪些情况下不适合创建索引

1. 表记录太少
2. 经常增删改的表
3. 数据重复且分布平均的表字段，因此应该只为最经常查询和最经常排序的数据列建立索引。（如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果）

查询优化

1. 小表驱动大表

   select * from A where A.id in (select id from B);#当B表数据集小于A表数据集时,用in 优与existselect * from A where exist (select 1 from B where B.id = A.id);#当A表数据集小于B表数据集时，用exist优与in

2. order by 关键字优化

   mysql排序使用index和filesort两种方式。尽量使用using Index,避免出现using filesort.

   order by在满足下列条件会使用index方式排序：

   order by 语句使用索引最左前列

   • 使用Where子句和order by子句条件列组合满足索引最左前列

   如果order by字段不在索引列上，filesort有两种算法：
   双路排序：mysql4.1之前的算法
   单路排序：mysql4.1之后出现。从磁盘读取查询需要的所有列，按照order by 列在buffer对它们进行排序，然后扫描排序后的列表进行输出。

3. group by 关键字优化

   group by 实质是先排序后进行分组，遵照索引键的最佳左前缀，where高于having能写在where限定的条件就不要使用having了。

二、Mysql锁机制

定义

锁是计算机协调多个进程或线程并发访问某一资源的机制。

在数据库中，除传统的计算资源（CPU，RAM,I/O等）的争用以外，数据也是一种供许多用户共享的资源。如何保证数据并发访问的一致性、有效性是所有数据库必须解决的一个问题，锁冲突也是影响数据库并发访问性能的一个重要因素。从这个角度来说，锁对数据库而言显得尤其重要也更加复杂。

锁的分类

• 从对数据操作的类型上分为读锁和写锁读锁（共享锁）：针对同一份数据，多个读操作可以同时进行而不会互相影响。
• 写锁（排他锁）：当前写操作没有完成前，它会阻断其他写锁和读锁。

1. 表锁

   特点：偏向MyISAM存储引擎，开销小，加锁快；无死锁；锁定粒度大，发生锁冲突的概率最高，并发度最低。

   命令：
   加锁： lock table 表名1 read/write，表名2 read/write…
   查看表加锁情况：show open tables;有锁时In_user = 1
   释放表锁：unlock tables

   读锁：表被某一会话加上读锁后，大家都可以读数据，但是不可以写**（包括加锁者，且加锁者会话不可以再操作其他表）**，其他会话发起写数据请求将被阻塞到表锁被释放。

   写锁：表被某一会话加上写锁后，加锁者会话可以对此表进行读操作，但不可以再操作其他表。而其他会话 对此表进行写操作，读操作将被阻塞至写锁被释放。

   总结：读锁会阻塞写，但是不会阻塞读；而写锁 会阻塞读和写。

2. 行锁

   特点：偏向InnoDB存储引擎，开销大，加锁慢；会出现思索；锁定力度最小，发生锁冲突的概率最低，并发度也最高

   注意：当索引失效或者无索引时时会导致行锁变为表锁。

3. 间隙锁

   定义
   当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但不存在的记录，叫做：“间隙（GAP）”

   InnoDB也会对这个间隙锁加锁

   危害：
   因为Query执行过程中通过范围查找的话，他会锁定整个范围内所有的索引键值，即使这个键值不存在。
   间隙锁有一个比较致命的弱点，就是当锁定一个范围键值之后，及时某些不存在键也会被无辜锁定，而造成在锁定的时候无法插入锁定键值范围内的任何数据。在某些场景下这可能会对性能造成很大的危害。

4. 优化建议

   尽可能让所有数据检索都通过索引来完成，避免无索引行锁升级为表锁

5. 合理设计索引，尽量缩小锁的范围
6. 尽可能减少检索条件，避免间隙锁
7. 尽量控制事物大小，减少锁定资源量和时间长度
8. 尽可能低级别事物隔离

三、数据库连接池的工作原理

连接池的工作原理主要由三部分组成，分别为

1. 连接池的建立
2. 连接池中连接的使用管理
3. 连接池的关闭

• 第一、连接池的建立。一般在系统初始化时，连接池会根据系统配置建立，并在池中创建了几个连接对象，以便使用时能从连接池中获取。连接池中的连接不能随意创建和关闭，这样避免了连接随意建立和关闭造成的系统开销。Java中提供了很多容器类可以方便的构建连接池，例如Vector、Stack等。

• 第二、连接池的管理。连接池管理策略是连接池机制的核心，连接池内连接的分配和释放对系统的性能有很大的影响。其管理策略是：

  当客户请求数据库连接时，首先查看连接池中是否有空闲连接，如果存在空闲连接，则将连接分配给客户使用；如果没有空闲连接，则查看当前所开的连接数是否已经达到最大连接数，如果没达到就重新创建一个连接给请求的客户；如果达到就按设定的最大等待时间进行等待，如果超出最大等待时间，则抛出异常给客户。

  当客户释放数据库连接时，先判断该连接的引用次数是否超过了规定值，如果超过就从连接池中删除该连接，否则保留为其他客户服务。

  该策略保证了数据库连接的有效复用，避免频繁的建立、释放连接所带来的系统资源开销。

• 第三、连接池的关闭。当应用程序退出时，关闭连接池中所有的连接，释放连接池相关的资源，以便连接可以返回池中重复利用。我们可以通过Connection对象的Close或Dispose方法，也可以通过C#的using语句来关闭连接。该过程正好与创建相反。

• 移除无效连接。无效连接，即不能正确连接到数据库服务器的连接。对于连接池来说，存储的与数据库服务器的连接的数量是有限的。因此，对于无效连接，如果如不及时移除，将会浪费连接池的空间。其实不用担心，连接池管理器已经很好的为我们处理了这些问题。如果连接长时间空闲，或检测到与服务器的连接已断开，连接池管理器会将该连接从池中移除。

主要参数

最小连接数Min Pool Size: 默认为0。是连接池一直保持的数据库连接,所以如果应用程序对数据库连接的使用量不大,将会有大量的数据库连接资源被浪费。
最大连接数Max Pool Size: 默认为100。是连接池能申请的最大连接数,如果数据库连接请求超过次数,后面的数据库连接请求将被加入到等待队列中,这会影响以后的数据库操作。
最大空闲时间，获取连接超时时间Connection Timeout：连接请求等待超时时间。默认为15秒，单位为秒。
超时重试连接次数

Pooling: 是否启用连接池。ADO.NET默认是启用连接池的，因此，你需要手动设置Pooling=false来禁用连接池。

常见需要注意的点

1. 并发问题

   确保线程是同步的

2. 事务处理

   “ALL-OR-NOTHING”原则,即对于一组SQL语句要么全做，要么全不做。

3. 连接池的分配与释放

4. 连接池的配置与维护

   如何确保连接池中的最小连接数：有动态和静态两种策略。

   动态即每隔一定时间就对连接池进行检测，如果发现连接数量小于最小连接数，则补充相应数量的新连接,以保证连接池的正常运转。

   静态是发现空闲连接不够时再去检查。

四、数据库规范及优化

规范

1、数据库命名规范

• 所有的数据库对象名称必须使用小写字母并使用下划线分割(mysql 数据库对大小写敏感)

• 所有数据库对象名称禁止使用MySqL保留关键字 eg. from关键字

• 数据库对象的命名要见名之意 , 最好不要超过32个字符.

• 数据库操作的时候 ,总会导入导出表 , 有一些临时表 , 临时表的最好以tmp为前缀并且以日期为后缀

• 备份表的命名, 最好以bak为前缀并且以日期为后缀

• 所有存储相同数据的列名和列类型最好一致

2、 数据结构基本设计规范

• 所有的表使用Innodb存储引擎
• 数据库和标的字符集统一使用UTF-8 utf8-mb4
• 所有的表和字段都要添加注释
• 尽量控制单表数据量的大小 , 尽量在500万行以内
• 尽量做到冷热数据分离,减小表的宽度
• 禁止在表中建立预留字段
• 禁止在数据库中存储图片, 文件等二进制数据
• 禁止在线上数据库做数据库压力测试 dev test 预发 pre
• 禁止从开发环境,测试环境直连生产环境数据库

3、数据库索引设计规范

• 限制每张表的上的索引数量 , 建议单张表索引不超过 5 个Innodb是按照主键的顺序来组织表的
• 不使用更新频繁的列作为主键 , 不使用多列主键
• 不使用UUID , MD5 , HASH , 字符串列作为主键
• 主键建议使用自增ID使用主键