Reference
1.数据库优化
1.1 SQL优化/表优化/数据库优化
SELECT 语句执行过程
-- 选取表 将多个表数据通过笛卡尔积变成一个表
FROM <left_table>
-- 指定join 用于添加数据到 on 之后的虚表中
<join_type> JOIN <right_table>
-- 对笛卡尔积的虚表进行筛选
ON <join_condition>
-- 对虚表数据进行过滤筛选
WHERE <where_condition>
-- 分组
GROUP BY <group_by_list>
-- 对分组后的结果进行聚合筛选
HAVING <having_condition>
-- 返回数据列表 返回的单列必须在 group by 子句中 聚合函数除外
SELECT <select_list>
-- 数据去重
DISTINCT <select_list>
-- 排序
ORDER BY <order_by_condition>
-- 限制结果返回
LIMIT <limit_number>
从sql优化方面来讲：
- 不要返回任何用不到的字段
- 尽量避免全表扫描，在where和order by涉及的字段上加索引
- 尽量避免在 where 子句中对字段进行 null 值判断，会使索引失效
- 尽量避免在 where 子句中使用 != 或 <> 操作符，会使索引失效
- 尽量避免在 where 子句中使用 or 来连接条件，会使索引失效
- 尽量避免在where子句中对字段进行函数操作，会使索引失效
从表优化方面来讲：
- 可以水平分表，将一张表拆成多张结构一样的表，用表名区分
- 可以垂直分表，将表中较大的字段拆分到另一张表中，该表与原表是一对一的关系
从库优化方面来讲：
可以将数据库分为主从库，主库用来写数据，多个从库用来读数据，主库与从库之间通过某种机制实现数据同步
MySQL优化原则：
- 减少数据访问：设置合理的字段类型，启用压缩，通过索引访问等减少磁盘IO；
- 返回更少的数据：只返回需要的字段和数据分页处理，减少磁盘IO及网络IO；
- 较少交互次数：批量DML操作、函数存储等减少数据库连接次数；
- 减少服务器CPU开销：尽量减少数据库排序操作以及全表查询，减少CPU内存占用。
- 利用更多资源：使用表分区，可以增加并行操作，更大限度利用CPU资源。
总结下来最重要的也就三点：
- 最大化利用索引
- 尽可能避免全表扫描
- 减少无效数据的查询
1.1.1 SQL优化
explain 命令
对于低性能的SQL语句定位，MySQL提供了 explain 命令来查看语句的执行计划。
执行结果：
| id | select_type | table | partitions | type | key | ke_len | ref | rows | filtered | Extra |
|---|---|---|---|---|---|---|---|---|---|---|
| 1 | SIMPLE | table_name | (Null) | index | idx_table_name_indexName | 500 | (Null) | 2 | 75000 | Using where;Using index |
id：SELECT识别符(查询序号)，表示一个查询中各个子查询的执行顺序。
- id 相同时，执行顺序由上至下；
- 如果是子查询，id的序号会递增，id值越大优先级越高，越先被执行；
- id 如果相同，可以认为是一组，从上往下顺序执行；
select type：子查询的查询类型。
- SIMPLE：不包含任何子查询或 UNION 等查询；
- PRIMARY：包含子查询最外层查询就显示为 PRIMARY；
- SUBQUERY：在 SELECT 或 WHERE 子句中包含的查询；
- DERIVED：FROM 子句中包含的查询；
- UNION：出现在 UNION 后的查询语句中；
- UNION RESULT：从 UNION 中获取结果集；
table：查询的数据表；partitions：使用的分区，需要结合表分区才可以看到。
type：访问类型。
- ALL 扫描全表；
- index 遍历索引；
- range 索引范围查找；
- index_subquery 在子查询中使用 ref；
- unique_subquery 在子查询中使用 eq_ref；
- ref_or_null 对 Null 进行索引的优化的 ref；
- fulltext 使用全文索引；
- ref 使用非唯一索引查找数据；
- eq_ref 在join查询中使用 PRIMARY KEY or UNIQUE NOT NULL 索引关联；
possible_keys：可能使用到的索引(不一定会使用)。
查询涉及到的字段上若存在索引，则该索引将被列出来。当该列为 Null 时就要考虑当前的SQL是否需要优化了。
key：显示 MySQL 在查询中实际使用的索引，若没有使用索引，显示为 Null。
PS：查询中若使用了覆盖索引(索引的数据覆盖了需要查询的所有数据，即所查询数据在索引中即可取到无需回表操作)，则该索引仅出现在 key 列表中。
key_length：索引长度。
ref：表示上述表的连接匹配条件，即哪些列或常量被用于查找索引列上的值。
rows：返回估算的结果集数目，并不是一个准确的值。
extra：
- Using index：使用覆盖索引；
- Using where：使用了 where 子句来过滤结果集；
- Using filesort：使用文件排序，使用非索引进行排序时出现，非常消耗性能，尽量优化；
- Using temporary：使用临时表。
推荐：SQL性能优化的目标至少要达到 range 级别，要求是 ref 级别，若是可以达到 const 最好。
- const 单表中最多只有一个匹配行(主键或者唯一索引)，在优化阶段即可读取到数据；
- ref 指的是使用普通索引(normal index)；
- range 对索引进行范围检索 ；
SQL生命周期：
- 应用服务器与数据库服务器建立一个连接；
- 数据库进程拿到请求sql；
- 解析并生成执行计划，执行；
- 读取数据到内存并进行逻辑处理；
- 通过步骤1的连接，发送结果给客户端；
- 关掉连接，释放资源。
大表查询优化
- 优化 schema、sql语句+索引
- 使用缓存 memcached、redis
- 主从复制、读写分离
- 垂直拆分，根据模块耦合度拆分系统(分布式系统)
- 水平切分，针对数据量大的表，可以选择合适的 sharding key 进行分表，且sql中尽量待 sharding key,将数据定位到限定的表上去查，而不是扫描全部的表。
超大分页sql优化
select * from table where age > 20 limit 1000000,10;
-- 使用覆盖索引优化
select * from table where id in (select id from table where age > 20 limit 1000000,10);
-- 若是 id 连续
select * from table where id > 1000000 limit 10
-- 数据提前缓存至redis 查询直接走缓存
-- LIMIT 偏移量大的时候，查询效率低下，可以记录上次查询的最大ID，下次查询直接根据该ID来查询。
开启慢查询日志/设置临界时间
-- 查看慢查询日志是否开启
SHOW VARIABLES LIKE 'slow_query_log';
-- 开启慢查询日志
SET GLOBAL slow_query_log = on;
-- 关闭慢查询日志
SET GLOBAL slow_query_log = off;
-- 查看临界时间(s) 一旦sql执行时间超过临界时间将被记录至慢查询日志
SHOW VARIABLES LIKE 'long_query_time';
-- 设置临界时间
SET long_query_time = 10;
慢查询的优化思路：
- 分析语句，是否 load 了额外的数据；
- 分析语句执行计划，获取索引使用情况，然后优化sql语句，使得语句可以尽可能的命中索引；
- 若语句已经无法优化，可以考虑是否是数据表太大了，考虑横向或者纵向的分表。
优化查询过程中的数据访问
1.访问数据太多导致查询性能下降
- 确定应用程序是否在检索大量超过需要的数据，可能是太多行或列。
- 确认MySQL服务器是否在分析大量不必要的数据行。
2.避免执行如下SQL语句
-
查询不需要的数据(使用 limit 解决)
-
多表关联返回全部列(指定列名)
-
总是返回全部列(避免使用 SELECT *)
-
重复查询相同的数据(缓存数据，下次直接取缓存)
-
扫描额外的记录(使用 explain 进行分析，若发现查询需要扫描大佬的数 据，但只返回少数行:)
-
使用索引覆盖扫描，把所有列都放到索引中，这样存储引擎就不需要回表直接获取索引中的数据就可以返回结果
-
改变数据库和表的结构，修改数据表范式
-
重写SQL，让优化器可以以更优的方式执行查询
-
优化长难的查询语句
- 复杂查询拆分为简单查询
- 切分查询，将一个大的查询分为多个小的相同的查询。(一次性删除100w数据要比一次删1w,删100次更加损耗服务器资源)
- 分解关联表，让缓存的效率更高
- 执行单个查询可以减少锁的竞争
- 在应用层做关联更容易对数据库进行拆分
优化特定类型的查询语句
- count(*) 会忽略所有的列，直接统计所有列数，不要使用 count(别名)
- MyISAM中，没有任何 where 条件的 count(*) 非常快
- 当有 where 条件时，MyISAM 的 count 统计不一定比其他引擎快
- 可以使用 explain 查询近似值，用近似值替代 count(*)
- 增加汇总表/使用缓存
优化关联查询
- 确定 ON 或者 USING 子句中是否有索引
- 确保 GROUP BY 和 ORDER BY 只有一个表中的列，这样 MySQL 才有可能使用索引
优化子查询
- 用关联查询替代
- 优化 GROUP BY 和 DISTINCT(使用索引来优化是最有效的优化方式)
- 关联查询中，使用标识列分组的效率更高
- 如果不需要 ORDER BY，进行 GROUP BY 时加上 ORDER BY NULL, MySQL将不会在进行文件排序
- WITH ROLLUP 超级聚合，可以挪到应用程序处理
优化 UNION 查询
UNION ALL 的效率高于 UNION
UNION 会给临时表加上一个 DISTINCT 选项，这回导致对整个临时表的数据做唯一性校验，资源消耗相当高。
优化 GROUP BY 语句
默认情况下，MySQL 会对 GROUP BY 分组的所有值进行排序。
若在SQL中显式的包括一个相同的列的 ORDER BY 语句，MySQL 就可以毫不犹豫的对其进行优化，尽管仍然进行了排序。
若不想对分组数据的值进行排序，可以指定 ORDER BY NULL 禁止排序。
优化 JOIN 语句
MySQL 中可以通过子查询来使用 SELECT 语句创建一个单列的查询结果，然后把这个结果作为过滤条件用在另一个查询中。
使用子查询可以一次性的完成很多逻辑上需要多个步骤才能完成的SQL操作，同时也可以避免事务或者表锁死，并且写起来很容易。
但是有些情况下，子查询可以被更有效率的连接(JOIN)替代。JOIN 更优销量是因为MySQL不需要在内存中创建临时表来完成这个逻辑上的需要俩个步骤的查询工作。
-- 子查询
SELECT col1 FROM customerinfo WHERE CustomerID NOT in (SELECT CustomerID FROM salesinfo)
-- join 优化后
SELECT col1 FROM customerinfo
LEFT JOIN salesinfoON customerinfo.CustomerID=salesinfo.CustomerID
WHERE salesinfo.CustomerID IS NULL
使用 TRUNCATE 代替 DELETE
当删除全表中记录时，使用 DELETE 语句的操作会被记录到 undo 块中，删除记录也记录到 binlog。
当确认需要删除全表时，会阐释大量的 binlog 并占用大量的 undo 数据块。
使用 TRUNCATE 替代不会记录可恢复的信息，即数据不可恢复。因此使用 TRUNCATE 操作仅会占用极少的资源和时间。
此外 TRUNCATE 可以回收表的水位，使自增字段值归零。
增删改 DML 语句优化
- 大批量插入数据，建议使用多个值得 INSERT 语句(减少SQL语句的解析操作/在特定场景下可以减少对DB的连接次数/语句短减少网络传输中的IO)。
- 适当使用 commit (及时释放事务占用的 undo 数据块/事务在 redo log 中记录的数据块/释放锁 减少锁争用)
- 读写优先级(MySQL 允许改变语句调度的优先级)，MySQL默认的调度策略：
- 写入操作优先于读取操作；
- 写入操作无法同时进行；
- 读取操作可以同时进行；
1.2 大型存储方面优化
1.2.1 数据库主从复制读写分离
- master 将改变记录到二进制日志中，slave 将 master的二进制文件拷贝到它的中继日志中，重新将数据写入到自己的数据中，达到复制主服务器数据的目的；
主从复制可以用作：数据库负载均衡、数据库备份、读写分离等功能； - 实现数据库的读写分离需要依赖 MySQL的中间件，如mysql_proxy, atlas等。通过配置这些中间件来对主从服务器进行读写分离，使从服务器承担被读取的责任，
从而减轻主服务器的负担。
主从复制原理：
PS：DML(数据操作语言，如insert/delete/update/select等)；DDL(数据定义语言，如create、alter、drop等)
谈到MySQL数据库主从同步延迟原理，得从MySQL的数据库主从复制原理说起，MySQL的主从复制都是单线程的操作，主库对所有 DDL 和 DML产生 binlog，binlog 是顺序写，所以效率很高。slave 的 Slave_IO_Running 线程到主库取日志记录到自己的中继日志，效率也比较高。下一步， 问题来了，slave 的 Slave_SQL_Running 线程将主库的 DDL和 DML 操作在 slave 实施。DML 和 DDL 的IO操作是随机的，不是顺序的，成本高很多，还可能有 slave 上的其他查询产生 lock 争用，由于 Slave_SQL_Running 也是单线程的，所以一个 DDL 卡主了，需要执行10分钟，那么所有之后的 DDL 会等待这个 DDL 执行完才会继续执行，这就导致了延时。有朋友会问：“主库上那个相同的DDL也需要执行10分，为什 么slave会延时？”，答案是master可以并发，Slave_SQL_Running线程却不可以。
当主库的TPS并发较高时，产生的 DDL数量超过 slave 一个 sql 线程所能承受的范围，那么延时就产生了，当然还有就是可能与 slave 的大型 query 语句产生了锁等待。
MySQL主从同步延迟解决方案：
- 最简单的方法就是在架构上做优化，尽量让主库的DDL快速执行。
- 将从服务器作为备份使用，而不提供查询，那么的负载下来了， 执行
relay log里面的SQL效率自然就高了。 - 增加从服务器喽，这个目的还是分散读的压力， 从而降低服务器负载。
- 因为主服务器要负责更新操作， 其对安全性的要求比从服务器高， 所有有些设置可以修改，比如
sync_binlog=1，innodb_flush_log_at_trx_commit=1之类的设置，而slave则不需要这么高的数据安全，完全可以讲sync_binlog设置为0或者关闭binlog，innodb_flushlog，innodb_flush_log_at_trx_commit也可以设置为0来提高sql的执行效率，这个能很大程度上提高效率。另外就是使用比主库更好的硬件设备作为slave。
参数介绍：
sync_binlog：该参数对于MySQL来说至关重要，①当sync_binlog=0时，事务提交后，MySQL不会做fsync之类的磁盘同步指令来刷新binlog_cache中的信息到磁盘，而是让filesystem自行决定什么时候来同步指令，或者等到binlog_cache满了之后同步到磁盘；②当sync_binlog=n时，每提交n次事务，MySQL将会进行异常fsync之类的磁盘同步指令来将binlog_cache中的数据强制写入磁盘。③MySQL中sync_binlog默认是0，此时性能是最好的，但风险也是最大的，一旦系统crash，binlog_cache中的所有binlog信息都将丢失。而当sync_binlog=1时，是最安全但性能损耗最大的。innodb_flush_log_at_trx_commit：默认是1，即每一次事务提交或者事务外的指令都需要将日志写入(flush)磁盘；当其设置为2时；指令不写入硬盘而是写入系统缓存。
如何判断主从间是否存在同步延迟？
- 使用
show slave status来进行查看，查看Seconds_Behind_Master参数的值判断：①当值为NULL时，表示io_thread或者是sql_thread
中有任何一个发生了故障，也就是该线程的Running状态是NO而非YES；②当值为0时，表示主从复制正常。③当值为正值时，表示主从同步延迟已经出现，数字越大延迟越严重； mk-heartbeat
-- 主从复制的配置
-- 配置主服务器 master
-- 修改 my.ini/my.conf
[mysqld]
log-bin=mysql-bin -- 启用二进制日志
server-id=102 -- 指定服务器唯一ID
-- 配置从服务器 slave
log-bin=mysql-bin -- 启用二进制日志
server-id=226 -- 服务器唯一ID
-- 主服务器上授权从服务器
GRANT REPLICATION SLAVE ON *.* to 'slavename'@'IP' identified by 'root'
-- 服务器上使用
change master to
master_host="masterIP"
master_user="masterUser"
master_password="masterpassword"
-- 开始主从复制
start slave
PS:每次修改配置后都需要重启服务器，然后可以在主从服务器上用 show master/slave status 查看主从状态
1.2.2 数据库 sharding
在数据库中数据表的数据量非常庞大，无论是索引还是缓存的压力都很大，对数据库进行 sharding，使之分别以多个数据库服务器或多个表存储，以减轻查询压力。
- 垂直切分：在数据表非常多时，把数据库中关系紧密的表切分出来分别放在不同的 server 上(根据字段热度切分出另一个表)。
- 水平切分：在表不多，但表中的数据量非常大时，为了加快查询，可以使用哈希算法，将一个数据表分为多个，分别放在不同的服务器上，加快查询。
- 联合切分：当数据表和数据表中数据量都非常大时，则可以同时进行水平切分和垂直切分，将数据库切分为一个分布式的矩阵来存储。
1.2.3 索引优化(聚集与非聚集索引)
MyISAM索引(非聚集索引)的实现：
- MyISAM 引擎使用 B+Tree 实现索引结构，叶子节点的 Data 域中存放仅仅为数据记录的地址。
- 在 MyISAM 中，主索引和辅助索引在结构上没有任何区别，只是主索引要求 key 是唯一的，而辅助索引的 key 是可以重复的。
- MyISAM 索引检索的算法为首先按照 B+Tree 搜索算法搜索索引，如果指定 Key 的存在，则取出 data 域的值，然后以其为地址，读取相应的数据记录。
InnoDB索引(聚集索引)的实现： - InnoDB 引擎同样使用 B+Tree 实现索引结构，但具体实现上与 MyISAM 截然不同。
- 第一个重大区别就是 InnoDB 的数据文件本身就是索引文件，而 MyISAM 的索引文件和数据文件是分离的，索引文件仅保存数据记录的地址。
- 在 InnoDB 中，表数据文件本身就是按 B+Tree 组织的一个索引结构，其叶子节点的 data 域保存了完整的数据记录。
- 因为索引的 key 就是数据表的主键，因此InnoDB表数据文件本身就是主索引。
- 因为InnoDB的数据文件本身按照主键进行聚集，所以这种索引也叫作聚集索引，并且要求 InnoDB 必须有主键。
- 若没有显示指定主键，则MySQL系统会自动选择一个唯一标识数据记录的列作为主键，若不存在这种列，则MySQL会自动为InnoDB表生成一个隐含字段作为主键，
这个字段长度为6个字节，类型为长整型。 - InnoDB 的辅助索引 data 域存储的相应记录的主键的值，而非地址。也就是说，InnoDB的所有辅助索引都引用主键作为 data 域。
(也就是辅助索引搜索需要检索俩遍索引：首先检索辅助索引获得主键，然后用主键到主索引中检索获得记录。)
何时使用聚簇索引或非聚簇索引？
| 操作 | 使用聚簇索引 | 使用非聚簇索引 |
|---|---|---|
| 列经常被分组排序 | YES | YES |
| 返回某范围内的数据 | YES | NO |
| 一个或极少不同值 | NO | NO |
| 小数目的不同值 | YES | NO |
| 大数目的不同值 | NO | YES |
| 频繁更新的列 | NO | YES |
| 外键列 | YES | YES |
| 主键列 | YES | YES |
| 频繁修改索引列 | NO | YES |
PS:
- 不建议使用过长的字段作为 InnoDB 表的主键索引(因为所有的辅助索引都引用主索引，过长的主键索引会令辅助索引变得过大)；
- 不建议使用非单调的字段作为 InnoDB 表的主键索引(因为InnoDB数据文件本身就是一个 B+Tree ，非单调 UUID() 形式的主键会造成插入新
记录时数据文件为了维持 B+Tree 的特性而频繁的分裂调整，十分低效，所以建议使用自增ID作为主键)
1.2.4 数据库设计方面优化
- 数据库设计符合第三范式，为了查询方便可以有一定的数据冗余。
- 选择数据类型时的优先级：
int > date/time > enum/char > varchar > blob，选择数据类型时，可以考虑替换。 - 对于 char(n) 类型，在数据完整的情况下，尽量使用较小的 n 值；
- 在建表时，使用 partition 命令对单个表区分可以大大提高查询效率，MySQL支持
RANGE/LIST/HASH/KEY分区类型，其中RANGE最常用；
MyISAM和InnoDB的区别：
- 存储结构：MyISAM在磁盘上存储三个文件 frm(表定义文件)、MYD(数据文件)、MYI(索引文件)，而InnoDB存储俩个文件 frm(表定义文件)、ibd(数据文件)
- 事务支持：MyISAM不提供事务支持，InnoDB支持事务；
- 表锁差异：MyISAM 只支持表级锁，InnoDB支持行级锁；
- 全文索引：MyISAM 支持 FULLTEXT类型的全文索引(不适用中文)，InnoDB不支持；
- 表的具体行数：MyISAM保存有表的总行数，查询 COUNT(*) 很快，InnoDB则需要重新计算；
- 外键：MyISAM 不支持外键，InnoDB支持。
数据库三范式(层层递增)：
- 第一范式(1NF)：数据库表中的字段都是单一属性，不可能再分(每个属性都是原子项，不可分割)；
- 第二范式(2NF)：数据库中不存在非关键字对任一候选关键字段的部分函数依赖(不存在一个字段的值是依赖于另一个字段的值来确定的)；
- 第三范式(3NF)：数据库中不存在非关键字对任一候选关键字段的传递函数依赖。
其他
1.为什么要尽量设定一个主键?
主键是数据库确保数据行在整张表唯一性的保障，设定了主键后，在后续的删改查操作时都可以更快速的确保操作的数据范围安全。
2.主键使用自增ID还是UUID？
推荐使用自增ID，不要使用UUID。
- 因为在InnoDB存储引擎中，主键索引是作为聚簇索引存在的，也就是说，主键索引的B+树叶子节点上存储了主键索引以及全部的数据(有序)。
- 如果主键索引是自增ID，那么只需要不断向后排列即可。但若是UUID，由于到来的ID与原来的大小不确定，会造成非常多的数据插入，
- 数据移动中就会导致很多的内存碎片，进而造成插入性能的下降。
- 关键在于InnoDB的索引特性导致了自增ID做主键的效率是最好的，如果没有主键(主键是聚簇索引)，InnoDB会选择一个唯一键来做聚簇索引，如果没有唯一键，
会生成一个隐式的主键。
3.字段为什么要求定义为 NOT NULL?
null 值会占用更多的字节，且在程序中造成很多与预期不符的情况。
4.存储用户的密码散列，用什么字段存储？
密码散列,盐(salt)，用户身份证号等固定长度的字符串都应该使用 char 而不是 varchar 来存储，这样可以节省空间而且提高检索效率。
5.SQL语句优化：
- 对查询进行优化，应尽量避免全表扫描，首先应考虑在 where 和 order by 涉及的列上建立索引。
- 应尽量避免在 where 子句中进行 null 值判断，否则将导致引擎放弃使用索引而进行全表扫描。
- 应尽量避免在 where 子句中使用 != 或 <> 操作符，否则引擎将放弃使用索引而进行全表扫描。
- 应尽量避免在 where 子句中使用 or 来连接条件，否则引擎将放弃使用索引而进行全表扫描(使用 union 代替 or)。
- in 和 not in 同样会导致全表扫描(部分情况下可以使用 between 代替)。
- like 以通配符(’%abc…’)开头时，索引会失效变成全表扫描
- 若在 where 子句中使用参数，也会导致全表扫描，因为SQL只有在运行时才会解析局部变量，但优化程序不能将访问计划的选择推迟到运行时。
- 应尽量避免在 where 子句中对字段进行表达式判断，否则引擎将放弃使用索引而进行全表扫描。
- 应尽量避免在 where 子句中对字段进行函数操作，否则引擎将放弃使用索引而进行全表扫描。
- 多表关联查询时，小表在前，大表在后(对小表进行全表扫描，效率比较高)。
- 涉及多表操作时，使用表的别名，减少解析时间和语法错误。
- 用 where 子句替换 having 子句，having 子句只会在检索出所有记录之后对结果集进行过滤，而 where 则是在聚合前筛选记录(where子句会在查询时就过滤掉数据)。
having 中的条件一般用于聚合函数的过滤，除此之外，应该将条件写在 where 子句中。俩个关键字的区别是：where 后面不能使用组函数。 - 调整 where 子句的连接顺序，应将过滤数据多的条件往前放，最快速度缩小结果集。
2.索引
2.1 MySQL索引类型
索引是一种特殊的文件，它们包含着对数据表中所有记录的引用指针，是需要占据物理空间的。
更通俗的说，索引就相当于目录。为了方便查找书中的内容，通过对内容建立索引形成目录。
从逻辑角度来讲：
- 普通索引：最基本的索引，没有任何限制；
- 主键索引：一种特殊的唯一索引，不允许有空值；
- 唯一索引：索引字段的值必须唯一，可以有空值；
- 组合索引：在多个字段上创建的索引，遵循最左前缀集合的原则；
- 全文索引：可以在varchar或text的字段上创建；
从物理角度来讲：
- 聚集索引
- 非聚集索引
2.2 索引的优缺点
优点：
- 可以大大加快数据的检索速度，将随机IO变成顺序IO；
- 索引可以帮助服务器避免排序和临时表；
- 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能；
缺点：
- 时间方面：创建和维护索引是需要耗费时间的(对表中的数据进行增删改的时候，索引都需要动态维护，会降低增删改的执行效率)；
- 空间方面：索引需要占用物理空间。
2.3 创建索引注意事项
- 非空字段：使用
0/特殊值/空串代替； - 离散值大的字段放在联合索引的前面：因为其能更快的定位数据；
- 索引字段越小越好；
- 注意最左前缀匹配原则；
- 较频繁作为查询条件的字段才去创建索引；
- 更新频繁的字段不适合创建索引；
- 不能有效区分的数据列不适合做索引(即该数据重复性高，索引效果不明显)；
- 尽量扩展索引，不要新建索引；
- 定义有外键的数据列一定要建立索引；
-- 1.建表时创建索引
CREATE TABLE user_index (
id INT auto_increment PRIMARY KEY ,
first_name VARCHAR(16),
last_name VARCHAR(16),
id_card VARCHAR(18),
information text,
KEY name (first_name,last_name),
FULLTEXT KEY (information),
UNIQUE KEY (id_card)
);
-- 2.使用 ALTER TABLE命令增加索引
ALTER TABLE user_index ADD INDEX name (first_name,last_name);
-- 3. 使用 CREATE INDEX 创建索引
CREATE INDEX index_name ON table_name (column_list);
-- 删除索引
ALTER TABLE user_index DROP KEY name;
ALTER TABLE user_index DROP KEY id_card;
ALTER TABLE user_index DROP KEY information
2.4 MySQL索引失效原因
- 不满足最佳左前缀匹配法则;
- 在索引上进行任何操作(计算、函数、类型转换)都会导致索引失效转为全表扫描；
- 存储引擎不能使用索引中范围条件右边的列；
- MySQL 在使用不等于 (!= / <>) 的时候会导致索引失效，从而全表扫描；
- is null / is not null 也无法使用索引；
- like 以通配符(’%abc…’)开头时，索引会失效变成全表扫描；
- 字符串不加单引号，索引会失效，因为 int 转 字符串 会导致类型转换 ；
- 尽量少用 or 查询，会导致索引失效；
- 全值匹配:全值匹配的意思就是联立的复合索引的顺序和个数要和检索的条件的顺序和个数相同
- 最佳左前缀法则：若索引了多列，要遵守该法则，指的是查询从索引的最左前列开始并且不跳过索引中的列。
-- 建表 sys_user
CREATE TABLE `sys_user` (
`id` varchar(64) NOT NULL COMMENT '主键',
`name` varchar(64) DEFAULT NULL COMMENT '名字',
`age` int(64) DEFAULT NULL COMMENT '年龄',
`pos` varchar(64) DEFAULT NULL COMMENT '职位',
PRIMARY KEY (`id`),
KEY `idx_sys_user_nameAgePos` (`name`,`age`,`pos`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COMMENT='用户表';
-- 创建复合索引
ALTER TABLE sys_user ADD INDEX idx_sys_user_nameAgePos(name,age,pos);
-- 检索语句
SELECT * FROM sys_user WHERE name='小明' AND age = 22 AND pos ='java';
-- 测试A 满足索引规则
-- 不仅满足最左前缀法则，而且满足全值匹配，很显然是最优的查询方式
EXPLAIN SELECT * FROM sys_user WHERE name='小明' AND age = 22 AND pos ='java';
-- 满足俩个索引条件 name 和 age 字段
EXPLAIN SELECT * FROM sys_user WHERE name='小明' AND age = 22;
-- 仅满足一个索引条件 name 字段
EXPLAIN SELECT * FROM sys_user WHERE name='小明';
-- 测试B 不满足索引规则 因为没有name检索条件，索引失效了 变成了全表扫描
EXPLAIN SELECT * FROM sys_user WHERE age = 22;
EXPLAIN SELECT * FROM sys_user WHERE age = 22 AND pos ='java';
EXPLAIN SELECT * FROM sys_user WHERE pos ='java';
-- 满足最左前缀索引规则 但是不满足全值匹配
EXPLAIN SELECT * FROM sys_user WHERE age = 22 AND name ='小红';
-- 在索引上进行任何操作(计算、函数、类型转换)都会导致索引失效转为全表扫描
EXPLAIN SELECT * FROM sys_user WHERE LEFT(name,1)='小明';
-- 存储引擎不能使用索引中范围条件右边的列
-- age 字段为范围查询,则 name 字段索引有效, pos 字段索引无效
EXPLAIN SELECT * FROM sys_user WHERE name='小明' AND age < 22 AND pos ='java';
-- 尽量使用覆盖索引 而不是 select * 虽然不会导致索引无效，但查询效率会变低
EXPLAIN SELECT * FROM sys_user WHERE name='小明' AND age =22 AND pos ='java';
EXPLAIN SELECT name,age,pos FROM sys_user WHERE name='小明' AND age =22 AND pos ='java';
-- MySQL 在使用不等于 (!= / <>) 的时候会导致索引失效，从而全表扫描
EXPLAIN SELECT * FROM sys_user WHERE name !='小明';
-- is null / is not null 也无法使用索引
EXPLAIN SELECT * FROM sys_user WHERE name is not null;
-- like 以通配符('%abc...')开头时，索引会失效变成全表扫描
-- 某些条件下，左通配符也可以使用索引，但不会进行回表操作
EXPLAIN SELECT * FROM sys_user WHERE name like '%明%';
-- 通配符写在右边时，可以避免索引失效
EXPLAIN SELECT * FROM sys_user WHERE name like '明%';
-- 覆盖索引下可以避免索引失效
EXPLAIN SELECT name,age,pos FROM sys_user WHERE name like '%明%';
-- 字符串不加单引号，索引会失效，因为 int 转 字符串 会导致类型转换
EXPLAIN SELECT * FROM sys_user WHERE name=222;
-- 尽量少用 or 查询，会导致索引失效
EXPLAIN SELECT * FROM sys_user WHERE name='小明' or age = 22;
其他
1. B+ 树相对于 B树的改进点
- 扫库、扫表能力更强：如果我们需要进行全表扫描，只需要遍历 B+ 树的叶子节点就可以了，而 B 树需要遍历整颗树。
- B+Tree 的磁盘读写能力相对于 B Tree 来说更强：根节点和枝节点不保存数据区，所以一个节点可以保存更多的关键字，一次磁盘加载(IO操作)
能获取到相对更多的关键字。 - 天然具备排序能力：B+Tree 的叶子节点有下一个数据区的指针，数据形成了链表
- 效率稳定：B+ Tree 永远是在叶子节点拿到数据，所以IO次数是稳定的，而 B 树运气好根节点就能够拿到数据，运气不好叶子节点才能拿到数据，
所以时间上会有差异。
2. 为什么MySQL索引选择使用 B+树而不是 B树？
- B+ 树更适合外部存储(一般指磁盘存储)，由于内节点(非叶子节点)不存储 data，所以一个节点可以存储更多的内节点，每个节点能索引的范围更大更精确。
也就是说使用 B+ Tree 单次磁盘 I/O 的信息量相比较 B Tree 更大，I/O 效率更高。 - MySQL 是关系型数据库，经常会按照区间来访问某个索引列，B+ Tree 的叶子节点按顺序建立了链指针，加强了区间访问性，所以 B+ Tree 对于索引列上的区间范围查询很友好。而 B Tree 每个节点的 key 和 data 绑定在一起，无法进行区间查找。
3. 百万级别或以上的数据如何删除？
- 因为删除数据会涉及到索引的维护，所以优先删除索引；
- 删除其中的无用数据；
- 删除完成后重建索引；
3.事务
满足ACID特性的一组操作(原子性/隔离性/一致性/持久性)。
- 原子性(atomicity)：一个事务必须被视为一个不可分割的最小工作单元，整个事务的所有操作要么全部提交成功，要么全部失败回滚，
对于一个事务来说，不可能只执行其中的一部分操作。 - 一致性(consistency)：数据库总是从一个状态转换为另一个状态；
- 隔离性(isolation)：通常(涉及隔离级别)来说，一个事务所做的修改在最终提交前，对其他事务是不可见的；
- 持久性(durability)：一旦事务提交，则其多做的修改就会永久的保存在数据库中，即使系统崩溃，修改的数据也不会丢失。
MySQL在多线程并发场景下，可能会出现脏读(dirty read)、不可重复读(non-repeatable read)、幻读(phantom read)这类并发问题，
为了解决这些问题，引申出“隔离级别”的概念。需要知道的是，隔离级别越高，效率就会越低。
3.1 并发场景下事务存在的数据问题
3.1.1 脏读(dirty read)-未提交读
事务A修改了数据，但未提交，而事务B查询了事务A修改过但未提交的数据，这就是脏读，因为事务A可能会回滚。
3.1.2 不可重复读(non-repeatable read)-提交前后，读取数据本身的对比
- 事务A先查询数据，发现值为 3000，未提交。事务B在事务A查询完之后，修改了该数据为 13000，在事务A之前提交了。
- 若此时事务A再查询一次数据，就会发现数据和上次查询不一致，13000而非3000。这就是不可重复读,强调了事务A对要操作的数据被别人修改了，但在不知情的情况下拿去做之前的用途。
不可重复读，通俗说就是同一个事务内，查到的结果不一致，失去了MySQl的一致性。
3.1.3 幻读-其他提交前后，读取数据条数的对比
幻读是指在同一个事务中，存在前后俩次查询同一个范围的数据，但是第二次查询却看到了第一次查询没看到的行，一般情况下指新增。
-
事务A先修改某个表的所有记录的状态字段为已处理，未提交；事务B也在此时新增了一条未处理的记录，并提交了；
-
事务A随后查询记录，发现仍有一条记录是未处理的，这就是幻读。
-
脏读说的是事务查到了本不该查到的数据，强调的动作是查询；
-
不可重复读强调的是一个事务执行查询操作的时候，其他事务依然可以增删改数据，而查询事务并不知道数据已经改变；
-
幻读强调的是一个事务修改了数据后进行查询操作，却发现仍有未被修改的数据，因为修改后查询前有事务进行了插入操作；
3.2 隔离级别
针对上述并发场景下存在的数据问题，MySQL制定了四种不同的“隔离级别”：
3.2.1 读未提交 RU(read uncommitted)
事务中的提交，即使没有提交，对其他事务也都是可见的。
3.2.2 读提交 RC(read committed)
- 大多数数据库系统默认隔离级别都是 READ COMMITED （MySQL不是）。
- 该隔离级别下，一个事务开始时，只能“看见”已经提交的事务所做的修改。也就是说，一个事务从开始到提交之前，所做的任何修改对其他事务都是不可见的。
- 该隔离级别也叫作不可重复读（norepeatable read），因为俩次执行同样的查询，可能会出现不一样的结果。
3.2.3 可重复读 RR(repeatable read)
- 一个事务在执行过程中查询到的数据，总是和这个事务在启动时看到的数据(生成的一张视图)时一致的。
- InnoDB和XtraDB存储引擎通过多版本并发控制（MVCC）解决幻读的问题。
3.2.4 串行化 S(serializable)
最高隔离级别，通过强制事务串行(单线路)执行，避免了幻读的问题。
简单来说就是，可串行化会在读取的每一行数据上都加上锁，所以可能导致大量的超时和锁争用的问题。
4.锁
对于UPDATE/DELETE/INSERT语句，InnoDB会自动给涉及数据集加排它锁
而MyISAM在执行查询语句SELECT前，会自动给涉及的所有表加读锁，在执行增删改操作前，会自动给涉及的表加写锁。
4.1 锁的类型
按使用方式分为：
- 乐观锁
- 悲观锁
按粒度分为：
- 表级锁(MyISAM)：表共享读锁/表独占写锁
- 行级锁(innoDB)：共享锁(S)/排它锁(X)—间隙锁
- 页级锁(BDB)
4.1.1 表锁和行锁
表锁： 开销小，加锁快，不会出现死锁，锁定粒度大，但是锁冲突概率高，并发度低。
行锁： 开销大，加锁慢，会出现死锁；锁粒度小，发生锁冲突的概率低，并发度高。
- InnoDB行锁和表锁都支持、MyISAM支持表锁；
- InnoDB只有通过索引条件检索数据时才使用行级锁，否则，InnoDB使用表锁，也就是说，InnoDB的行锁是基于索引的。
4.1.2 共享锁和排它锁
InnoDB和MyISAM的俩个本质区别就是：InnoDB支持行锁和事务；
InnoDB实现了俩种类型的行锁：
- 共享锁(S锁/读锁)：允许一个读事务，阻止其他事务获取相同数据集的排他锁。也就是允许同时读不允许读写同时进行；
- 排他锁(X锁/写锁)：允许获得排他锁的事务更新数据，同时该事务下回阻止其他取得相同的数据集的读锁和写锁。也就是说写锁会阻塞写锁和读锁。
-- 共享锁(S)
SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE;
-- 排他锁(X)
SELECT * FROM table_name WHERE ... FOR UPDATE;
4.2.3 悲观锁和乐观锁
悲观锁(pessimistic lock)：
- 悲观认为每次查询数据的时候都会有事务对该数据集进行修改，所以每次查取数据的时候都会给他上锁。
- 悲观锁是假定会发送并发冲突，屏蔽一切可能违反数据完整性的操作。
- 悲观并发控制实际上是"先取锁，再访问"的保守策略，为数据处理的安全提供了保证。
- 在效率上，悲观锁会让数据库产生额外的开销，还会有死锁的可能性，降低了并行性。
乐观锁(optimistic lock)：
- 乐观认为每次去获取数据的时候，别人都不会修改它，因此不会加锁；
- 但是在提交更新时会判断在此期间是否有其他事务更新该数据集。
- 乐观锁适用于读多写少的场景，可以提高吞吐量。
悲观锁的实现：
- 在对数据修改前，尝试增加排他锁；
- 加锁失败，意味着数据正在被修改，进行等待或抛出异常；
- 加锁成功，对数据进行修改，提交事务，释放锁；
- 若加锁成功，则会阻塞对该数据集进行操作的其他事务，其他事务只能等待或抛出异常；
乐观锁的实现：
- version方式：一般是在数据表中加上一个数据版本号 而version字段，表示数据被修改的次数，但数据被修改时，version就会+1。
当事务A要更新数据时，读取数据的同时也会读取version的值，在提交更新时，若读取到的version值与当前数据集的version值相等则更新，
否则重试更新操作，直到更新成功。 - CAS操作方式：即compare and swap 或者 compare and set，涉及到三个操作数，数据所在的内存值，预期值，新值。
当需要更新时，判断当前内存值与之前取到的值是否相等，若相等，则用新值更新，若失败则重试，一般情况下是一个自旋操作，即不断的重试。
悲观锁和乐观锁的合理使用：
- 乐观锁：比较适合读取操作频繁的场景，若出现了大量的写入操作，数据发生冲突的可能性就会增大，为了保证数据的一致性，应用层需要不断的重新获取数据，
这样会增加大量的查询操作，降低系统的吞吐量。 - 悲观锁：比较适合写入操作比较频繁的场景，如果出现大量的读取操作，每次读取的时候都进行加锁，这样会增加大量的锁的开销，降低系统的吞吐量。
PS:
- 锁机制一定要在事务中才能生效，事务是基于MySQL InnoDB引擎的；
- 访问量不大，不会造成压力时使用悲观锁，面对高并发的情况下，使用乐观锁；
- 读取频繁时使用乐观锁，写入频繁时使用悲观锁(悲观锁不能解决脏读的问题)。
4.2.4 间隙锁/死锁
间隙锁：
- 是一种加在两个索引之间的锁，或者加在第一个索引之前，或最后一个索引之后的间隙。
- 这个间隙可以跨一个索引记录，多个索引记录，甚至是空的。
- 使用间隙锁可以防止其他事务在这个范围内插入或修改记录，保证两次读取这个范围内的记录不会变，从而不会出现幻读现象。
- 间隙锁和间隙锁之间是互不冲突的。
死锁：
- 死锁是指俩个或俩个以上的进程在执行过程中，因争夺资源而造成的一种互相等待的现象。
- 若无外力作用，它们将一直等待下去，此时系统处于死锁状态或系统产生了死锁，这些永远在互相等待的进程称为死锁进程。
- 表级锁不会出现死锁，解决死锁主要是针对InnoDB。
- 死锁的关键在于：俩个(或以上)的Session加锁的顺序不一致。
避免死锁：
- 加锁顺序：确保所有的线程都按照相同的顺序获取锁；
- 加锁时限：加上一个超时时间，若一个线程没有在给定的时限内成功获得所需要的锁，则会进行回退并释放所有已经获得锁，然后等待一段随机的时间后重试。
- 死锁检测：死锁检测即每当一个线程获得了锁，会在线程和锁相关的数据结构中将其记录下来，每当有线程请求锁时，也需要记录在该数据结构中。
2100
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
