一、Mysql 索引
- 索引的定义
索引是对数据库表中一列或多列的值进行排序的一种构造，应用索引可快速访问数据库表中的特定信息。如果想按特定职员的姓来查找他或她，则与在表中搜寻所有的行相比，索引有助于更快地获取信息。
索引的一个次要目标就是放慢检索表中数据，亦即能帮助信息搜寻者尽快的找到合乎限度条件的记录ID的辅助数据结构。
- 索引的类型
1) primary key 主键索引
它是一种非凡的惟一索引，不容许有空值。一张表只能有一个主键
2) unique 惟一索引
惟一索引列的值必须惟一，但容许有空值。如果是组合索引，则列值的组合必须惟一。
3) index 一般索引
这是最根本的索引，它没有任何限度。
【文章福利】须要C/C++ Linux服务器架构师学习材料加群1106747042（材料包含C/C++，Linux，golang技术，Nginx，ZeroMQ，MySQL，Redis，fastdfs，MongoDB，ZK，流媒体，CDN，P2P，K8S，Docker，TCP/IP，协程，DPDK，ffmpeg等）
4) fulltext 全文索引
全文索引（也称全文检索）是目前搜索引擎应用的一种关键技术。它可能利用分词技术等多种算法智能剖析出文本文字中关键字词的频率及重要性，而后依照肯定的算法规定智能地筛选出咱们想要的搜寻后果。
5) 组合索引
组合索引，即一个索引蕴含多个列。
- 索引的构造
mysql中广泛应用B+Tree做索引，但在实现上又依据聚簇索引和非聚簇索引而不同，在这儿不做具体介绍，后续篇章中再开展具体阐明。
- 测试数据
咱们当初有一个订单表，表构造如下：
- 最左前缀匹配准则
十分重要的准则，mysql会从左向右匹配直到遇到范畴查问(>、<、between、like)就进行匹配。
创立一个索引
CREATE INDEX IDX_T_1 USING BTREE ON xa87_v2.t_xa87_order_info (store,delivery_msg,food_fee,delivery_fee);咱们执行一个sql
explain select * from t_xa87_order_info where delivery_msg='高玥静,17836031' and food_fee>1000;+----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+----------+-------------+| 1 | SIMPLE | t_xa87_order_info | NULL | ALL | NULL | NULL | NULL | NULL | 62017 | 3.33 | Using where |+----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+----------+-------------+在where中并没有store这个字段，所以不会应用IDX_T_1这个索引。
咱们把store这个查问条件加上，看一下成果：
explain select * from t_xa87_order_info where store='f9fd2705ad1d740a4bef42833b487cea' and delivery_msg='高玥静,17836031' and food_fee=1000;+----+-------------+-------------------+------------+------+---------------+---------+---------+-------------------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------------------+------------+------+---------------+---------+---------+-------------------+------+----------+-------+| 1 | SIMPLE | t_xa87_order_info | NULL | ref | IDX_T_1 | IDX_T_1 | 267 | const,const,const | 1 | 100.00 | NULL |+----+-------------+-------------------+------------+------+---------------+---------+---------+-------------------+------+----------+-------+explain select * from t_xa87_order_info where store='f9fd2705ad1d740a4bef42833b487cea' and delivery_msg like '%17836031' and food_fee=1000;+----+-------------+-------------------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------------------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------+| 1 | SIMPLE | t_xa87_order_info | NULL | ref | IDX_T_1 | IDX_T_1 | 131 | const | 633 | 1.11 | Using index condition |+----+-------------+-------------------+------------+------+---------------+---------+---------+-------+------+----------+-----------------------+下面两个查问都用了IDX_T_1这个索引， 第一个效率更高， 因为第二个查问在delivery_msg条件上应用了like所以不再持续匹配food_fee
- 抉择区分度高的列作为索引
所谓区分度高就是指雷同的值少。比方性别这个字段，只有男、女两个值，区分度很低，就不适宜作为索引。
- 拓展索引，尽量不要新建索引
比方表中曾经有a的索引，当初要加(a,b)的索引，那么只须要批改原来的索引即可。
二、查问优化
- 不让索引生效
1) Like的参数不以通配符结尾
explain select * from t_xa87_order_info where order_no like '2022%';+----+-------------+-------------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+| 1 | SIMPLE | t_xa87_order_info | NULL | range | PRIMARY | PRIMARY | 130 | NULL | 1 | 100.00 | Using where |+----+-------------+-------------------+------------+-------+---------------+---------+---------+------+------+----------+-------------+explain select * from t_xa87_order_info where order_no like '%2022';+----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+----------+-------------+| 1 | SIMPLE | t_xa87_order_info | NULL | ALL | NULL | NULL | NULL | NULL | 62017 | 11.11 | Using where |+----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+----------+-------------+2) Where 条件要合乎最左前缀匹配准则
上文中曾经有过阐明不再赘述
3) 不要应用!=和<>
explain select * from t_xa87_order_info where created_date<>'2020-08-24';+----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+----------+-------------+| 1 | SIMPLE | t_xa87_order_info | NULL | ALL | IDX_T_2 | NULL | NULL | NULL | 62017 | 50.00 | Using where |+----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+----------+-------------+4) 不要和null进行判断
explain select * from t_xa87_order_info where created_date is not null;+----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+----------+-------------+| 1 | SIMPLE | t_xa87_order_info | NULL | ALL | IDX_T_2 | NULL | NULL | NULL | 62017 | 90.00 | Using where |+----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+----------+-------------+5) 不要应用or
6) =、in 中的列能够乱序
explain select * from t_xa87_order_info where delivery_msg='高玥静,17836031' and food_fee=1000 and store='f9fd2705ad1d740a4bef42833b487cea';+----+-------------+-------------------+------------+------+---------------+---------+---------+-------------------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------------------+------------+------+---------------+---------+---------+-------------------+------+----------+-------+| 1 | SIMPLE | t_xa87_order_info | NULL | ref | IDX_T_1 | IDX_T_1 | 267 | const,const,const | 1 | 100.00 | NULL |+----+-------------+-------------------+------------+------+---------------+---------+---------+-------------------+------+----------+-------+咱们把查问条件中的store放到最初，发现仍然能够匹配到IDX_T_1这个索引，且效率和store在最后面的时候是一样的。
7) 索引列不参加计算
在created_date上创立一个索引
CREATE INDEX IDX_T_2 USING BTREE ON xa87_v2.t_xa87_order_info (created_date);explain select * from t_xa87_order_info where created_date='2020-08-24';+----+-------------+-------------------+------------+------+---------------+---------+---------+-------+------+----------+-------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------------------+------------+------+---------------+---------+---------+-------+------+----------+-------+| 1 | SIMPLE | t_xa87_order_info | NULL | ref | IDX_T_2 | IDX_T_2 | 3 | const | 123 | 100.00 | NULL |+----+-------------+-------------------+------------+------+---------------+---------+---------+-------+------+----------+-------+explain select * from t_xa87_order_info where DATE_FORMAT(created_date,'%Y-%m-%d')='2020-08-24';+----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+----------+-------------+| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |+----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+----------+-------------+| 1 | SIMPLE | t_xa87_order_info | NULL | ALL | NULL | NULL | NULL | NULL | 62017 | 100.00 | Using where |+----+-------------+-------------------+------------+------+---------------+------+---------+------+-------+----------+-------------+能够看到第一条查问语句应用了索引而第二条并没有。
- 查问sql优化
1) 防止select '*'
在sql解析的过程中，会将’*’ 顺次转换成所有的列名，这个是通过查问数据字典实现的，这意味着将消耗更多的工夫。所以，应该养成一个须要什么就取什么的好习惯。
2) order by 优化
重写order by语句以应用索引；
为所应用的列建设另外一个索引；
相对防止在order by子句中应用表达式；
3) group by 优化
进步GROUP BY 语句的效率, 能够通过将不须要的记录在GROUP BY 之前过滤掉
4) exists 代替 in
5) 应用 varchar/nvarchar 代替 char/nchar
6) 能用DISTINCT的就不必GROUP BY
7) 能用UNION ALL就不要用UNION
UNION ALL不执行SELECT DISTINCT函数，这样就会缩小很多不必要的资源。
8) 在Join表的时候应用相当类型的例，并将其索引
如果有很多Join 查问，你应该确认两个表中Join的字段是被建过索引的。这样，MySQL外部会启动为你优化Join的SQL语句的机制。
这些被用来Join的字段，应该是雷同的类型的。例如：如果你要把 DECIMAL 字段和一个 INT 字段Join在一起，MySQL就无奈应用它们的索引。对于那些STRING类型，还须要有雷同的字符集才行。（两个表的字符集有可能不一样）
三、事务（Transaction）
- 事务的个性
原子性（A）：一个事务（transaction）中的所有操作，要么全副实现，要么全副不实现，不会完结在两头某个环节。事务在执行过程中产生谬误，会被回滚（Rollback）到事务开始前的状态，就像这个事务素来没有执行过一样；
一致性（C）：在事务开始之前和事务完结当前，数据库的完整性没有被毁坏。这示意写入的材料必须完全符合所有的预设规定，这蕴含材料的精确度、串联性以及后续数据库能够自发性地实现预约的工作；
隔离性（I）：数据库容许多个并发事务同时对其数据进行读写和批改的能力，隔离性能够避免多个事务并发执行时因为穿插执行而导致数据的不统一；
持久性（D）：事务处理完结后，对数据的批改就是永恒的；
- 隔离级别
读未提交：read uncommitted
读已提交：read committed
可反复读：repeatable read
串行化：serializable
- Read Uncommitted
事物A和事物B，事物A未提交的数据，事物B能够读取到
这里读取到的数据叫做“脏数据”
这种隔离级别最低，这种级别个别是在实践上存在，数据库隔离级别个别都高于该级别
- Read Committed
事物A和事物B，事物A提交的数据，事物B能力读取到
这种隔离级别高于读未提交
换句话说，对方事物提交之后的数据，我以后事物能力读取到
这种级别能够防止“脏数据”
这种隔离级别会导致“不可反复读取”
Oracle默认隔离级别
- Repeatable Read
事务A和事务B，事务A提交之后的数据，事务B读取不到
事务B是可反复读取数据
这种隔离级别高于读已提交
换句话说，对方提交之后的数据，我还是读取不到
这种隔离级别能够防止“不可反复读取”，达到可反复读取
比方1点和2点读到数据是同一个
MySQL默认级别
尽管能够达到可反复读取，然而会导致“幻像读”
- Serializable
事务A和事务B，事务A在操作数据库时，事务B只能排队期待
这种隔离级别很少应用，吞吐量太低，用户体验差
这种级别能够防止“幻像读”，每一次读取的都是数据库中实在存在数据，事务A与事务B串行，而不并发