索引和事务
树
索引 索引是存储引擎用于快速查找记录的一种数据结构，需要额外开辟空间和数据维护工作 按照物理存储方式：聚簇和非聚簇 MyISAM：frm元数据文件、myd数据、myi索引数据，MyISAM都是非聚簇索引，数据和索引分别 存放 InnoDB：frm元数据文件、ibd数据和索引，innodb存储引擎支持聚簇索引 按照数据结构：B+树、hash等 mysql -uroot -p123456 use mysql; – 切换当前系统库 update user set host=‘%’ where user=‘root’; 1 2 3 4 按照逻辑：主键索引、普通索引、唯一索引、空间索引（针对空间类型的数据）和全文索引
B-tree
- B树是一种自平衡的多叉查找树，在一个B树种最多可以开m个叉（m>=2）称为m阶B树
- 定义任意非叶子节点最多有m个儿子
- 根节点可以有m-1个键值【节点中存储了具体的数据key-value】，根节点的儿子数为[2,m]
- 除去根节点以外的非叶子节点儿子数为[m/2,m]，非叶子节点的关键字个数为儿子数-1
- 所有的叶子节点位于同一层
- 利用k个关键字将节点拆分为k+1段，分别指向k+1个儿子，同时需要满足查找树的大小关系
B-Tree的特点：
- 关键字集合分布在整个树中
- 任何一个关键字出现且只能出现在一个节点中
- 搜索可能在非叶子节点就直接结束，其搜索性能等价于在关键字全集内进行一次二分查找O(logN)
- B树在插入删除数据时会破坏B树的性质，所以插入删除时需要对数据进行分裂、合并、转移等操作 以保持B树性质
B+树
就是在B树的基础上添加了顺序访问指针 有n个子树的节点包含有n个关键字 所有的关键字全部存储在叶子节点上，且叶子节点根据关键字从小到大的顺序连接 非叶子节点可以看作索引部分，节点中仅仅包含有其子树中最大或者最小的关键字
B+树的查找过程类似B树，只是查找时，如果非叶子节点上的关键字等于给定值时，并不终止，而是继 续沿着指针指到叶子节点位置。因此不管在B+树中是否查找成功，每次查找都时走了一条从根到叶子的 全路径
B+树的特点：
- 所有关键字都存储在叶子节点上，且链表中的关键字恰好是有序的
- 不可能是非叶子节点命中，所有查询的执行时间稳定
- 非叶子节点相当于叶子节点的索引，叶子节点相当于存储数据的存储层
- 非常适合文件索引系统
引入B树的原因
红黑树也可以实现索引，但是文件系统和数据库系统普遍采用的是B树或者B+树 首先需要建立索引的数据规模比较大，所以造成索引的数据量也不会太小，不可能全部存储在内存中， 索引经常需要以文件的形式存储在磁盘上，所以索引查找过程中就需要有磁盘IO的消耗。索引的组织结 构需要尽量减少查找过程中磁盘的IO存取次数。 磁盘的读取并不是严格的按需读取，每次都会预读，这是依据局部性原理：当一个数据被用到时，其附 近的数据也通常会被用到。磁盘的顺序读取效率很高，按照局部性原理，采用预读机制能够提高IO效 率。预读的长度一般是页page的整数倍，页是计算机存储管理的逻辑块，页大小一般都是4k或者8k。 数据库中利用磁盘预读的原理，将一个节点的大小设置为一个页大小，每个节点只需要一次IO就可以完 全载入，所以使用B树作为索引结构是非常高效的。使用红黑树则树的高度比B树要高的多，而B树一般 只有3-4层高度
MySQL 索引
MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。
打个比方，如果合理的设计且使用索引的MySQL是一辆兰博基尼的话，那么没有设计和使用索引的MySQL就是一个人力三轮车。
拿汉语字典的目录页（索引）打比方，我们可以按拼音、笔画、偏旁部首等排序的目录（索引）快速查找到需要的字。
索引分单列索引和组合索引。单列索引，即一个索引只包含单个列，一个表可以有多个单列索引，但这不是组合索引。组合索引，即一个索引包含多个列。
创建索引时，你需要确保该索引是应用在 SQL 查询语句的条件(一般作为 WHERE 子句的条件)。
实际上，索引也是一张表，该表保存了主键与索引字段，并指向实体表的记录。
上面都在说使用索引的好处，但过多的使用索引将会造成滥用。因此索引也会有它的缺点：虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存一下索引文件。
建立索引会占用磁盘空间的索引文件。
普通索引
- id查询操作的唯一标识 select_type显示查询的类型
- table显示本次查询相关的表
- partitions查询匹配的分区
- type显示访问表使用哪种类型，ALL全表查询、index使用索引树、range给定范围查询
- possible_keys查询时可能会使用的索引
- key使用的索引
- key_len索引字段的长度 ref使用索引列等值查询时，和索引列进行等值匹配的对象信息
- rows扫描出的行数
- filtered按照表条件过滤出的行百分比
- Extra执行形况的描述和说明
创建索引
这是最基本的索引，它没有任何限制。它有以下几种创建方式：
CREATE INDEX indexName ON table_name (column_name)
如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。
修改表结构(添加索引)
ALTER table tableName ADD INDEX indexName(columnName)
创建表的时候直接指定
CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
INDEX [indexName] (username(length))
);
删除索引的语法
DROP INDEX [indexName] ON mytable;
唯一索引
它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一。它有以下几种创建方式：
创建索引
-- 建立一个复合索引，并且命名为name1
alter table tb_users add key name1(first_name,last_name);
-- 创建全文索引,具体存储采用倒排索引的方式，不支持中文分词。如果业务需要使用搜索引擎，建议
使用ES实现全文搜索
alter table tb_users add fulltext(memo)
CREATE UNIQUE INDEX indexName ON mytable(username(length))
修改表结构
ALTER table mytable ADD UNIQUE [indexName] (username(length))
创建表的时候直接指定
CREATE TABLE mytable(
ID INT NOT NULL,
username VARCHAR(16) NOT NULL,
UNIQUE [indexName] (username(length))
);
使用ALTER 命令添加和删除索引
有四种方式来添加数据表的索引：
-
ALTER TABLE tbl_name ADD PRIMARY KEY (column_list):
该语句添加一个主键，这意味着索引值必须是唯一的，且不能为NULL。
-
ALTER TABLE tbl_name ADD UNIQUE index_name (column_list): 这条语句创建索引的值必须是唯一的（除了NULL外，NULL可能会出现多次）。
-
ALTER TABLE tbl_name ADD INDEX index_name (column_list): 添加普通索引，索引值可出现多次。
-
**ALTER TABLE tbl_name ADD FULLTEXT index_name (column_list)😗*该语句指定了索引为 FULLTEXT ，用于全文索引。
以下实例为在表中添加索引。
mysql> ALTER TABLE testalter_tbl ADD INDEX (c);
你还可以在 ALTER 命令中使用 DROP 子句来删除索引。尝试以下实例删除索引:
mysql> ALTER TABLE testalter_tbl DROP INDEX c;
使用 ALTER 命令添加和删除主键
主键作用于列上（可以一个列或多个列联合主键），添加主键索引时，你需要确保该主键默认不为空（NOT NULL）。实例如下：
mysql> ALTER TABLE testalter_tbl MODIFY i INT NOT NULL;
mysql> ALTER TABLE testalter_tbl ADD PRIMARY KEY (i);
你也可以使用 ALTER 命令删除主键：
mysql> ALTER TABLE testalter_tbl DROP PRIMARY KEY;
删除主键时只需指定PRIMARY KEY，但在删除索引时，你必须知道索引名。
显示索引信息
你可以使用 SHOW INDEX 命令来列出表中的相关的索引信息。可以通过添加 \G 来格式化输出信息。
尝试以下实例:
索引注意事项
索引的使用场景
1、针对经常需要搜索的列和主键列
explain select * from tb_users where id>1
根据id查询记录，因为id列上创建了主键索引，因此sql语句执行的可选索引possible_keys只有主键索 引，如果有多个不同索引，最终会选择一个较好的作为检索的依据
2、order by排序或者范围查询条件的列
执行order by排序操作时，如果该字段没有建立索引，那么执行计划会将查询出的所有数据使用外部排 序。操作很影响性能。可以使用 alter table 表名称 add index(列名称) 建立索引，由于索引本身就 是有序的，所以直接按照索引顺序逐条读取数据即可。如果还涉及分页操作时，只用获取索引表中某个 范围内的索引对应的数据，而不是获取所有数据
具体开发中做查询必做分页
- 逻辑分页就是将数据先全部加载到内存中，然后获取部分数据进行显示
- 物理分页就是查询时只获取所需要显式的数据，不显示的数据不加载。 select * from tb_users limit 起始行号，每页行数
3、需要进行连接查询join 对join语句匹配关系on所涉及的字段建立索引可以提供连接查询的执行效率
索引覆盖
如果要查询的字段都建立了索引，那么存储引擎会直接在索引表中进行查询，而不会访问原始数据，这 个叫做索引覆盖。因此需要尽可能在select后写上必要查询的字段，以增加索引覆盖的几率 注意：不要在每个字段上建立索引，因为优先使用索引的优势在于索引的体积小。 针对一个表一般建议建立的索引数目不要超过6个，因为索引除了需要额外的存储空间外，还会对 数据的增删改性能造成影响
索引失效
在满足所用使用的情况下，例如where、order by、join…on或者索引覆盖，索引也不一定被使用
1、字段要独立出现
select * from tb_users where id=20-1; -- 会使用主键索引
select * from tb_users where id+1=20; -- 不会使用主键索引,针对列使用了表达式计算或
者函数
2、like查询
select * from tb_users where name like '%方%'; -- 由于使用%统配符开头，所以在
name上创建的索引无效，只能进行全表扫描，效率极低，实际开发中不建议使用；如果使用较为频繁建议
引入第三方的全文索引来实现
select * from tb_users where name like '方%'; -- name上创建的索引是有效
3、复合索引要求对左边的字段有效，最左前缀匹配原则
alter table tb_users add index(first_name,last_name);
具体的实现原理是先按照first_name中提取的关键字进行排序，如果无法确定先后时再按照从 last_name中提取的关键排序，索引复合索引生效的前提是左端的列参与的查询
select * from tb_users where first_name=’zhangsan‘; -- 可以使用索引
select * from tb_users where first_name='zhang' and last_name='san'; -- 索引生
效
select * from tb_users where last_name="lisi"; -- 无法利用索引
从查询条件 first_name=? and last_name=? 来说，针对first_name和last_name创建复合索引的效率 比分别创建first_name索引和last_name索引的查询效率高
4、or条件查询 or两边都有索引时索引生效，一边有一边没有会导致整个sql语句的全表扫描
5、状态值不用索引 取值的可能性较少，例如sex或者订单状态之类的字段，这种字段即使创建索引，也往往利用不上，因为 一个状态值可能匹配大量的记录，这种情况下mysql会认为索引比全表扫描的效率低，从而弃用索引。
如何创建索引
建立索引是以浪费空间为代价，换取高查询效率。建立索引会对修改操作的性能造成影响
1、建立基础索引：在使用where、order by、join字段上建立索引 2、优化组合索引：基于业务规则和业务逻辑 如果条件经常性的出现在一起，可以考虑将多个字段的索引升级为复合索引 如果添加个别字段的索引就可能出现索引覆盖，可以考虑为该字段建立索引
3、如果查询时不常出现的索引应该删除掉
4、建立索引后注意sql语句的使用 %开头的模糊查询导致索引失效 索引列最好非空，null值不会出现在索引中。 sex boolean default 1 少用not in或者!=查询，not in可以使用not exists替代 不要针对列进行计算，针对列建议的索引无效
5、使用explain查看执行计划，判断索引是否生效
前缀索引
一般希望索引表应该比具体的数据表要小。当建立索引时默认使用字段的全部内容建立索引，可以指定 使用字段的前10个字符建立索引，而不是整个字段内容。语法： index(列名称(长度)) 使用前缀索引的前提是：字段的前缀标识度比较高，例如口令比较适合创建前缀索引，因为各个密码几 乎都不相同 使用前缀索引的难度就是如何定义前缀截取的长度 可以使用 select count(*)/count(distinct left(password,len)) 进行比较，通过调整len值的大 小查看不同前缀的平均匹配度，接近1时就可以了
索引的具体实现z
Innodb存储引擎采用的是索引组织表，在Innodb中表数据文件本身就是按照B+树组织的一个索引结 构。MyISAM中主索引和辅助索引在结构上没有任何区别，只是主索引要求key值唯一的，而辅助索引的 key值允许重复
MySQL 事务
MySQL 事务主要用于处理操作量大，复杂度高的数据。比如说，在人员管理系统中，你删除一个人员，你既需要删除人员的基本资料，也要删除和该人员相关的信息，如信箱，文章等等，这样，这些数据库操作语句就构成一个事务！
- 在 MySQL 中只有使用了 Innodb 数据库引擎的数据库或表才支持事务。
- 事务处理可以用来维护数据库的完整性，保证成批的 SQL 语句要么全部执行，要么全部不执行。
- 事务用来管理 insert,update,delete 语句
一般来说，事务是必须满足4个条件（ACID）：：原子性（Atomicity，或称不可分割性）、一致性（Consistency）、隔离性（Isolation，又称独立性）、持久性（Durability）。
- **原子性：**一个事务（transaction）中的所有操作，要么全部完成，要么全部不完成，不会结束在中间某个环节。事务在执行过程中发生错误，会被回滚（Rollback）到事务开始前的状态，就像这个事务从来没有执行过一样。
- **一致性：**在事务开始之前和事务结束以后，数据库的完整性没有被破坏。这表示写入的资料必须完全符合所有的预设规则，这包含资料的精确度、串联性以及后续数据库可以自发性地完成预定的工作。
- **隔离性：**数据库允许多个并发事务同时对其数据进行读写和修改的能力，隔离性可以防止多个事务并发执行时由于交叉执行而导致数据的不一致。事务隔离分为不同级别，包括读未提交（Read uncommitted）、读提交（read committed）、可重复读（repeatable read）和串行化（Serializable）。
- **持久性：**事务处理结束后，对数据的修改就是永久的，即便系统故障也不会丢失。
在 MySQL 命令行的默认设置下，事务都是自动提交的，即执行 SQL 语句后就会马上执行 COMMIT 操作。因此要显式地开启一个事务务须使用命令 BEGIN 或 START TRANSACTION，或者执行命令 SET AUTOCOMMIT=0，用来禁止使用当前会话的自动提交。
事务控制语句：
- BEGIN 或 START TRANSACTION 显式地开启一个事务；
- COMMIT 也可以使用 COMMIT WORK，不过二者是等价的。COMMIT 会提交事务，并使已对数据库进行的所有修改成为永久性的；
- ROLLBACK 也可以使用 ROLLBACK WORK，不过二者是等价的。回滚会结束用户的事务，并撤销正在进行的所有未提交的修改；
- SAVEPOINT identifier，SAVEPOINT 允许在事务中创建一个保存点，一个事务中可以有多个 SAVEPOINT；
- RELEASE SAVEPOINT identifier 删除一个事务的保存点，当没有指定的保存点时，执行该语句会抛出一个异常；
- ROLLBACK TO identifier 把事务回滚到标记点；
- SET TRANSACTION 用来设置事务的隔离级别。InnoDB 存储引擎提供事务的隔离级别有READ UNCOMMITTED、READ COMMITTED、REPEATABLE READ 和 SERIALIZABLE。
MYSQL 事务处理主要有两种方法：
1、用 BEGIN, ROLLBACK, COMMIT来实现
- BEGIN 开始一个事务
- ROLLBACK 事务回滚
- COMMIT 事务确认
2、直接用 SET 来改变 MySQL 的自动提交模式:
- SET AUTOCOMMIT=0 禁止自动提交
- SET AUTOCOMMIT=1 开启自动提交
事务测试
PHP中使用事务实例
mysql> use RUNOOB;
Database changed
mysql> CREATE TABLE runoob_transaction_test( id int(5)) engine=innodb; # 创建数据表
Query OK, 0 rows affected (0.04 sec)
mysql> select * from runoob_transaction_test;
Empty set (0.01 sec)
mysql> begin; # 开始事务
Query OK, 0 rows affected (0.00 sec)
mysql> insert into runoob_transaction_test value(5);
Query OK, 1 rows affected (0.01 sec)
mysql> insert into runoob_transaction_test value(6);
Query OK, 1 rows affected (0.00 sec)
mysql> commit; # 提交事务
Query OK, 0 rows affected (0.01 sec)
mysql> select * from runoob_transaction_test;
+------+
| id |
+------+
| 5 |
| 6 |
+------+
2 rows in set (0.01 sec)
mysql> begin; # 开始事务
Query OK, 0 rows affected (0.00 sec)
mysql> insert into runoob_transaction_test values(7);
Query OK, 1 rows affected (0.00 sec)
mysql> rollback; # 回滚
Query OK, 0 rows affected (0.00 sec)
mysql> select * from runoob_transaction_test; # 因为回滚所以数据没有插入
+------+
| id |
+------+
| 5 |
| 6 |
+------+
2 rows in set (0.01 sec)
mysql>
多点回滚
begin;
update t1 set name='modify1' where id=1;
select * from t1; savepoint s1;
delete from t1; rollback to s1;
select * from t1; commit;
相关日志问题
- 事务的原子性是通过undo log来实现
- 事务的持久性是通过redo log来实现
redo log
如果每次读写数据都需要磁盘的IO，效率会很低。innodb提供了缓存buffer pool作为访问数据库的缓 存，读取和修改操作都会涉及到缓存的操作，缓存会定期刷新到磁盘中，但是写入缓存的数据在系统宕 机时会丢失，事务的持久性则无法保证。每次读写硬盘数据的IO成本太高，为了解决这个问题，引入了 redo log来提升更新数据的执行效率。
当事务提交时，先将redo log buffer写入redo log文件进行持久化，待事务commit操作完成时才算完 成。这种作为成为预先日志持久化write-ahead log。在持久化一个数据页之前，先将内存中相应的日志 页持久化。当有一条数据需要更新时，innodb会将记录写入到redo log中，并更新内存，这时更新就算 完成。innodb会在适当的时候，例如系统空闲时，才真正将操作记录更新到磁盘。如果在数据落盘之前 系统宕机，数据库重启后，可以通过日志来保证数据的完整性
undo log
undo log提供了两个作用：提供回滚和多版本控制MVCC 在数据修改时不仅记录redo，还记录了相对应的undo。undo log主要记录的是数据的逻辑变化，为了 在发生错误时回滚之前的所有操作。 undo日志用于将数据库逻辑的恢复到原来的样子，所以实际上记录的时相反反的工作。例如insert对应的 是delete。undo日志用于事务的回滚操作，进而保证了事务的原子性
隔离级别
数据库重要的功能就是实现数据共享，对于同时运行的多个事务，当多个事务同时访问数据库中相同的 数据时，如果没有采取必要的隔离机制，则会导致出现各种并发问题。 问题的本质就是共享数据的线程安全问题
常见问题
1、第一类丢失更新：A事务回滚时把已经提交的B事务更新的数据覆盖了。解决方案是锁机制
2、脏读：A事务读取到B事务更新但是还没有提供的数据，如果B回滚撤销，则A读取的数据就是临时而 且无效的数据。
3、不可重复读：A事务读取到了一个字段值，但是B更新并提交了该字段的修改，A再次读取同一个字段 值，但是两次读取到的内容不一致
4、幻读：A事务从一个表中读取了多行数据，但是B事务插入或者删除了一些新的行，如果A再次读取， 则发现数据会有多出来或者少掉的行
5、第二类丢失更新：A事务修改记录，同时B事务修改记录，B提交数据后使用B的修改结果覆盖了事务 A的修改结果
事务隔离性
数据库系统必须具有隔离并发各个事务的能力，使其相互不会影响，避免各种并发问题。一个事务和其 它事务隔离的程度就成为隔离等级。数据库中规定了多种事务隔离级别。不同的隔离级别对应不同的干 扰程度，隔离级别越到，数据的一致性就越号，但是并发性越差 MySQL数据库支持4种隔离级别，默认可重复读
隔离级别的范围
隔离级别的作用范围可以分为全局级和会话级两种。全局级对所有的会话有效，会话级只对当前会话有 效
- 设置全局隔离等级 set global transaction isolation level read committed;
- 设置会话级隔离等级 set session transaction isolation level read uncommitted
读未提交
MySQL数据库中事务的隔离实际上是依靠锁机制来实现的，但是加锁会带来性能的损失。读未提交隔离 等级是不加锁的，所以性能最好，但是由于基本没有什么限制，所以脏读问题都无法解决。
读已提交
解决脏读问题的方法就是只允许读取别的事务已经提交的数据，其它事务未提交数据当前事务不能读 取。例如oracle默认的事务隔离级别就是读已提交。由于只能读取已经提交的数据，所以可能出现两次 读取的数据不一致
可重复读
针对不可重复读的问题提出了可重复读的隔离等级，针对查询采用了MVCC多版本并发控制引入快照机 制，每个事务都有自己的数据快照，即使其它事务提交数据，也不影响当前事务相关行的数据快照。幻 读仍旧会出现 为了解决不可重复读的问题，MySQL采用了MVCC多版本并发控制的方式。数据库中的一行记录实际上 有多个版本，每个版本除了有数据之外，还有一个标识版本的字段row trx_id，这个字段就是产生的对 应事务的id，在事务开始的时候向事务系统申请，按照时间先后顺序递增
一行记录现在有3个不同的版本，每个版本中都记录了使其产生的事务id，每个数据存储的备份就是快照 【一致性视图】,可重读读就是在事务开始的时候生成一个当前事务的全局性的快照，但是读提交则是每 次执行语句时都会重新生成一次快照。
读取快照数据的规则：
-
版本未提交不能读取
-
版本已经提交，但是却是在快照创建后提交的，不能读取
-
版本已经提交，且是在快照创建前提交的，则可以读取
可重复读和读已提交两者主要的区别在于快照的创建上，可重复读仅仅在事务开始时创建一次，而读已 提交每次执行sql语句时都要创建一次
串行化
隔离等级最高，隔离效果最好，可以解决脏读、不可重复度和幻读问题，当时并发性最差。将事务的并 发执行转换为顺序执行，后一个事务的执行必须等待前一个事务结束
并发写问题
事务A执行update操作，update时要对所修改的数据行进行加锁，这个行所在事务提交后才能释放，而 在事务A提交之前，如果事务B也希望修改这行数据，必须先进行行锁的申请，但是由于A已经占用了行 锁，所以B申请不到，此时B一直会处于等待状态，直到A提交释放锁后，B才能执行 id就是主键PK，是有索引的，那么MySQL在索引树种查找到这行数据，然后加上行锁
假设表种并没有针对age设置索引，所以MySQL无法直接定位这行数据。MySQL会给这个表种的所有行 加锁，但是添加行锁后，MySQL会再次执行一次过滤，发现不满足条件的行就释放锁，最后只留下符合 条件的行。但是一次锁定一次解锁的过程对性能影响比较大，如果是大表的化，还是建议合理设计索引
幻读问题
解决并发问题的方案就是行锁，解决幻读也是依赖于锁机制实现，使用间隙锁。MySQL把行锁和间隙锁 合并在一起，就可以解决并发写和缓读问题，这个锁叫做next-key锁
例如： select * from tb_student 可以获取age=10和age=30的数据，针对索引数据库会创建维护一 个B+树，树可以用来快速定位行记录
针对具体的行数据，例如age=10和age=30的数据，添加一个行锁，根据age=10和age=30可以将整个区 间划分为3部分，（负无穷大,10]、(10,30)和[30,正无穷大)三个部分，在这3个区间上可以添加间隙锁
JDBC事务实现
使用JDBC连接mysql默认每一个连接是自动提交事务的。如果需要使用JDBC执行多条语句，并要求组成 一个事务一起执行的话
1、在执行之前关闭自动提交，设置手动提交事务Connection的对象.setAutoCommit(false)
2、如果执行成功，手动提交事务Connection的对象.commit();
3、如果执行过程中出现异常，则手动回滚撤销操作Connection的对象.rollback();
4、补充说明：希望养成习惯，在关闭Connection的对象之前，把连接对象设置回自动提交， Connection的对象.setAutoCommit(true)
因为实际开发中，每次获取的连接，不一定是新的连接，而是从连接池中获取的旧的连接，而且关闭也 不是真关闭，而是还给连接池，供别人接着用。以防别人拿到后，以为是自动提交的，而没有commit， 最终数据没有成功。
-
一般涉及到事务处理的话，那么业务逻辑都会比较复杂。例如购物车结算时，1）在订单表中添加一条记 录。2）在订单明细表中添加多条订单明细的记录，表示该订单买了什么东西。3）修改商品表的销量和 库存量。 用两条修改语句来模拟组成一个简单的事务。
-
update t_department set description = ‘xx’ where did = 2;
-
update t_department set description = ‘yy’ where did = 3;
希望这两个语句要么一起成功，要么一起回滚。为了制造失败，可以故意把第二条语句写错 update t_department set description = ‘yy’ （少了where） did = 3;
Connection conn = null;
try {
Class.forName("com.mysql.cj.jdbc.Driver");
conn = DriverManager.getConnection("jdbc:mysql:///test?
serverTimezone=UTC", "root", "123456");
// 默认情况下单语句单事务，如果需要手动定义事务范围，则需要关系自动提交
conn.setAutoCommit(false);
PreparedStatement ps = conn.prepareStatement("insert into t1
values(?,?)");
ps.setInt(1, 125);
ps.setString(2, "xxx5");
int len = ps.executeUpdate();
ps.setObject(1, "tttt");//数据类型错误
ps.setString(2, "66666");
ps.executeUpdate();
conn.commit();// 提交事务
} catch (Exception e) {
if (conn != null)
conn.rollback();//回滚撤销事务
System.out.println(e.getMessage());
} finally {
//如果使用直连方式，conn是否恢复原来的提交方式都没有关系；如果使用连接池则必须恢复原来的自
动提交方式
if (conn != null)conn.close();
}
分区处理
一般情况下创建的表对应一组存储文件，当数据量较大时MySQL的性能就开始下降 解决方案：如果数据表中的数据具有特定业务含义数据的特性，可以将表中数据分散到多个存储文件 中，以保证单个文件的执行效率。
最常见的分文件的方法是按照id值进行分区，不同的分区对应不同的存储问题。采用id的hash值进行分 区，实际上就是对10进行取模，可以将数据均匀的分散到10个文件中
create table tb_article(
id int primary key,
title varchar(32),
content mediumtext
) partition by hash(id) partitions 10;-- 按照id的hash值进行分区，总共分为10个区
表分区的用途
-
逻辑数据分割
-
提高单一的写入或者读取的应用速度
-
提高分区范围查询的速度
-
分割数据能够有多个不同的物理文件路径
-
高效的保存历史数据
分区算法
- MySQL支持的常见分区类型有Range、List、Hash、key分区，其中range最为常见
- Range范围：允许将数据划分到不同的范围，例如可以将一个表通过年份划分成若干个分区
- List预定义列表：允许系统通过预先定义的列表的值将数据进行分割 hash哈希：允许通过对表中的一个或者多个列的hash key进行计算，最后通过这个hash码将数据 对应到不同的分区
- key键值：是hash分区的而一种扩展，这里的hash key是由mysql系统产生的
- 复合模式：多种模式的组合使用，例如对已经进行了range分区的表上，对其中的分区再次进行 hash分区
指定分区中的列名称时需要使用主键列或者主键中的一部分，否则设置失败
hash哈希分区
一般用于不按照业务规则进行数据文件的均匀拆分，输出的结果和输入是否有规律无关，仅适用于整形 字段 一般要求hash中的值最好有一定的线性关系，否则分区数据将不能均匀分布。
create table tb_emp(
id int primary key auto_increment,
name varchar(32) not null,
hiredate date default '1989-2-3'
)partition by hash(id) partitions 4;
关键字分区key
key用于处理字符串，比hash()多一步从字符串中计算出一个整数，然后再进行取模计算
create table tb_article(
id int auto_increment,
title varchar(64) comment '文章标题',
content text,
primary key(id,title)
)partition by key(title) partitions 10;
范围分区range
range是按照一种指定数据的大小范围进行分区，例如按照文章的发布时间将数据分区存放 获取时间戳 select unix_timestamp('2022-4-30 23:59:59 ') 1651334399 select unix_timestamp('2022-3-31 23:59:59 ') 1648742399
create table tb_article(
id int auto_increment,
title varchar(32) not null,
pub_date int,
primary key(id,pub_date)
) partition by range(pub_date)(
-- 2022年3月和以前的数据
partition p202203 values less than (1648742399),
-- 2022年4月的数据
partition p202204 values less than (1651334399),
-- 2022年4月以后的数据
partition p202205 values less than maxvalue
);
其中maxvalue表示最大值。MySQL允许在分区键中使用null，分区键允许是一个字段，也可以是一个表 达式。一般MySQL的分区会把null当作0或者最小值进行处理。需要注意：range中null当作最小值；list 中null必须出现在枚举列表中，否则不作处理；hash或者key分区中null被当作0值处理 条件运算符只能使用less than，所以要求小值在前
列表分区
列表分区也是一种条件分区，使用列表值进行分区。列表值应该是离散值，而范围分区是连续值
create table tb_artitle(
id int auto_increment,
title varchar(32) not null
status tinyint(1), -- 用于表示文章的状态，例如0草稿、1完成未发布、2已发布、3下架
primary key(id,status)
)partition by list(status)(
partition writing values in (1,0), -- 表示正在写的文章
partition publishing values in(2,3) -- 表示已经完成的文章
);
概述视图
MySQL视图是一种虚拟存在的表，视图也是由行列构成，但是视图并不会实际存在于数据库中，行和列
的数据来自于定义视图的查询中所使用的表，并在使用视图时动态生成
视图和数据表的区别
视图不是真实的表，是一个虚拟表，其结构和数据都是建立对基表真实查询的基础上
存储在数据库中的查询操作定义了视图的内容和结构，视图的行和列的数据来自于查询所引用的实
际表，引用视图时动态生成
视图没有实际的物理记录，数据集实际存储在基表中
视图是数据的窗口，基表才是真实内容。视图是查看数据表的一种方式。从安全的角度上来看，视
图的数据安全性高，使用视图的开发人员不涉及数据表，甚至可以不知道基表的真实结构
视图的创建和删除只影响视图本身，不会影响对应的基本表
视图和基表
基表是用于存储真实数据
建立视图
基础语法： create view 视图名称 as 查询语句；
视图是一个虚表，其中并不直接存储数据，但是可以当作表的方式进行使用，具体数据来源于定义视图
的查询结果集
查看视图的结构 desc 视图名称;
查看视图的定义，可以查看创建视图对应的SQL语句
插入数据到视图【注意增删改的要求是一致的】
因为向视图中插入数据实际上就是向基本表中插入数据，也就是执行 insert into v_emp1 values(4,‘熊二’,1);
操作就是向基本表 tb_emp 中插入数据，只要没有违反基本表中的约束规则，则插入成功。
修改视图
当基表的结构发生变化时，可以修改视图定义。但是如果在视图定义中使用的列没有修改，则无需修改
视图定义
语法： alter view 视图名称 as 新的查询语句 ;
删除视图
删除视图实际上就是删除视图的定义，不会删除数据
语法： drop view 视图名称
视图的特点
视图的特点：视图的列可以来自不同的表或者计算出的列，是表的抽象和在逻辑意义上建立的新关
系。
视图是由基本表(实表)产生的表(虚表)。视图的建立和删除不影响基本表。
对视图内容的更新(添加，删除和修改)直接影响基本表。
当视图来自多个基本表时，有时可以修改数据，有时不允许修改数据。
视图的操作包括创建视图，查看视图，删除视图和修改视图。
优点
1、可以从基表中进行数据定制，简化数据操作，提高数据的安全性
2、共享所需数据、修改数据格式
3、重用sql语句
库表切分
使用分区可以将数据文件的变小，在一定程度上提高查询效率，但是业务区分很困难
使用视图可以实现按需获取数据，但是所查询的数据并没有变化，所以并不能提高查询效率
引入切分的原因：
为数据库减压
分区算法的局限性
针对分区算法只有MySQL5.1+之后才支持
水平拆分
水平分割：通过建立结构相同的几张表分别存储数据 类似分区
能够把一个特别大的表，水平拆分到不同的数据库服务器上，来分担一个数据库服务器的压力。提高数
据库的性能和效率。
优点：
单表的并发能力提高了，磁盘 I/O 性能也提高了。
如果出现高并发的话，总表可以根据不同的查询，将并发压力分到不同的小表里面。
缺点：
无法实现表连接查询。
垂直拆分
垂直分割：将经常一起使用的字段放在一个单独的表中，其余字段存放在另外的表中，分割后的表记录
之间是一对一的关系。
可以避免所有的业务表全部放在一台MySQL数据库服务器上，通过增加MySQL数据库实例的数，来分担
来自业务层模块的数据请求压力，从而达到对降低MySQL数据库的压力。
优点：
减少增量数据写入时的锁对查询的影响。
由于单表数量下降，常见的查询操作由于减少了需要扫描的记录，使得单表单次查询所需的检索行
数变少，减少了磁盘 IO，时延变短。
缺点：
无法解决单表数据量太大的问题。
集群
在一些大型网站业务场景中，单台数据库服务器所能提供的并发量已经无法满足业务需求，为了满足这
种情况，一般而言是通过主从同步的方式来同步数据，在此基础上，通过读写分离来提升数据库的并发
和负载能力。
一般可以采用横向扩展和库表切分的方法实现数据库处理能力的提升，因为单机的硬件处理能力一定是
有上限。由此而生的相关技术有：读写分离和负载均衡
MySQL的主从复制
需要实现集群多台机器共同对外提供服务，需要考虑的是如何实现读操作和写操作的工作划分。首先就
需要部署主从复制，只有实现了主从复制，才能在此基础上实现读写分离
MySQL所支持的复制类型
基于语句的复制，MySQL默认采用的是基于语句的复制，效率比较高
基于行的复制，把改变的内容复制过去，而不是把命令复制到从服务器上再次执行一次
混合类型复制，默认采用基于语句的复制，一旦发现基于语句无法精确复制时，就会采用基于行的
复制
主从复制的工作过程
master负责完成写操作，slave负责执行复制操作
1、在每个事务更新数据完成之前，master主机在二进制日志种记录这些改变binlog。再写入二进制日
志完成后，master通知存储引擎提交事务
2、从机slave将master的binlog复制到其中的中继日志relaylog。首先slave启动一个工作线程IO线程，
IO线程在master上打开一个普通的连接，然后开始binlog dump process。binlog dump process从
master的二进制日志种读取操作事件，如果已经跟上了master，它会休眠并等待master产生新的事
件。IO线程将事件写入中继日志relay log
3、SQL从线程从中继日志读取事件，重放其中的事件而更新slave种的数据，使slave种的数据和master
种的一致
注意：master的并行更新在slave种是串行执行的
数据库优化
需要考虑优化的场景
系统的吞吐量一般出现在数据库的访问速度上
随着数据量的逐步增大，处理时间会相应变慢
数据存储在硬盘上，读写速度和内存不匹配
优化方案
- 硬件优化
- 缓存优化
- 设计优化
- sql语句优化
sql性能分析profile
利用mysql中的profile可以记录所有的SQL执行的详细信息
开启profil
慢查询日志
MySQL慢查询全名称为慢查询日志，是MySQL提供的一种日志记录，用来记录在MySQL中执行sql语句
的响应时间超过阈值的sql语句
具体环境中，运行时间超过long_query_time值的sql语句会被记录在慢查询日中。
long_query_time的默认值为10，意思是记录运行时间为10秒以上的sql语句
默认情况下，MySQL并不会开启慢查询日志，需要手工设置这个参数。如果不是进行系统调优的需
要时，不建议启动该参数。因为启动慢查询日志会带来一定的性能损耗
慢查询日志支持将日志信息记录在文件或者数据表。用于记录执行时间超过临界阈值的sql语句，
可以快速定位应用中的慢查询，是sql语句优化的参考和基准
开启慢查询
配置项slow_query_log
查看慢查询是否开启 show variables like ‘slow_query_log’; ，环境变量值为OFF表示关闭
启动慢查询 set global slow_query_log=on
开启后在datadir下则会产生一个xxx-slow.log文件，其中存储慢查询日志
慢查询的相关配置参数
slow_query_log是否开启慢查询日志，1开启0关闭
slow_query_log_file设置慢查询日志的存储路径，可以不设置该参数，系统会默认一个缺省文件
【主机名称-slow.log】
long_query_time设置慢查询的阈值，默认为10秒钟，当查询时间多于设置的阈值时，可以记录日
志
log_queries_not_using_indexes设置没有使用索引的查询会被记录到查询日志中
log_output设置日志存储方式，值为FILE表示采用文件的方式记录日志，默认值FILE；如果值为
TABLE表示将慢查询日志记录存储在数据库
在命令行中执行 set global show_query_log=1 可以开启慢查询日志，但是仅仅只对当前数据库生
效，如果MySQL重启后则会失效。如果需要永久生效，就必须修改my.ini配置文件
注意：实际上的慢查询的阈值设置需要考虑具体情况，一般从大到小逐步筛查，目的在于将最慢的SQL语句优化掉
设计优化
设计数据库时，应该充分考虑数据库表和字段的设计以及存储引擎的选择
1、尽量使用整型数表示字符串。例如存储IP地址：inet_aton(字符串)和inet_ntoa(数值)
2、表的字段类型一般不采用enum和set类型，因为维护成本太高，可以采用关联表的方式来
替代enum
3、使用decimal可以对浮点数进行精确存放，但是存储空间会随着数值的增大而增大；一般
建议使用固定空间，例如double，但是double会损失存储精度。
4、尽可能使用not null约束，针对允许为null的字段可以考虑定义default。由于null值判断
比较繁琐。例如不要使用 id int ，可以考虑使用 id int default 0
5、字段注释需完整，最好可以见名知意
6、一般建议单表的字段不易过多。一般20-30就是极限
7、可以有预留字段。
总之所有的设计过程就是在性能和需求之间平衡的结果
充分利用MySQL自身提供的功能，例如索引等
需要记忆NF3和反范式
需要记忆常见的索引失效情形和执行计划查询的命令 exaplain
横向扩展，引入MySQL集群、负载均衡和读写分离
SQL语句优化
1、使用limit对查询结果的记录进行限定
2、避免使用 select * ，将需要查询的字段列表出来
3、使用join代替子查询
4、拆分大的delete或者insert语句【delete和truncate table】
5、可以通过开启慢查询定位应用中的执行较慢的sql语句
6、一般不进行列计算。例如 select id from t_users where age+1=10; 但是针对列的计
算操作会导致整表扫描，一般建议查询时尽可能将操作移动到等号的右边 select if from
t_users where age=10-1
7、sql语句尽可能简单，因为一个sql语句只能在一个CPU中进行计算，将大语句拆分为小语
句，可以减少锁定时间，避免出现一个大sql语句锁定整个库的访问
8、or尽可能修改为in，因为or的效率为O(n)，而in的效率是O(logN)。但是in的个数建议控
制在200以内
9、避免使用%xxx样式的查询
10、尽可能使用同类型数据进行比较，例如 ‘123’=123
11、尽量避免在where子句中使用!=操作符，因为可能会出现使用全表扫描
12、对于连续值使用between/and，不使用in
语句优化步骤
1、分析使用慢查询日志，查找需要进行优化的SQL语句。
2、针对慢查询的常见优化方法
创建合理的索引，并使用explain查看执行计划，确认索引是否生效
优化数据库结构。在设计过程中需要考虑数据冗余、查询和更新的速度、字段的数据类型是
否合理等方面。
分解关联查询，可以将一个大的复杂查询分为多个小查询，可以对每个需要关联的表单独查
询，然后将查询结果在应用程序中进行关联
典型的考试题：针对limit分页的优化查询 select * from t_users limit 1000000,10 ;
分析具体的SQL语句，例如选择数据量小的文件充当驱动表，将in子查询转换为exists子查询
706
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
