12.9

一、索引补充

复习索引：

普通索引

最基本的索引，没有任何限制。普通索引的<font color='orange'>唯一任务就是加速对数据的访问</font>。因此，应该只为那些最经常出现的查询条件<font color='cerise'>WHERE column = ……</font>或排序条件<font color='cerise'>OPDER BY column</font>中的数据创建索引。只要有可能，就应该选择一个数据最整齐、最紧凑的数据列(如一个整数类型的数据列)来创建索引。

创建索引

CREATE INDEX indexName ON mytable(username(length)); 

如果是char、varchar类型，<font color='cornflowerblue'>length可以小于字段实际长度</font>；如果是blob和text类型，<font color='cornflowerblue'>必须指定length</font>

修改表结构

ALTER mytable ADD INDEX [indexName] ON (username(length))

创建表的时候直接指定

CREATE TABLE mytable(ID INT NOT NULL,username VARCHAR(16) NOT NULL,INDEX [indexName] (username(length))); 

删除索引语句：

DROP INDEX [indexName] ON mytable;

唯一索引

与前面的普通索引类似，不同的就是：普通索引允许被索引的数据列包含重复的值，而<font color='clear green'>唯一索引列的值必须唯一，但允许有空值。如果是组合索引，则列值的组合必须唯一</font>

创建索引

CREATE UNIQUE INDEX indexName ON mytable(username(length));

修改表结构

ALTER mytable ADD UNIQUE [indexName] ON (username(length));

创建表的时候直接指定

CREATE TABLE mytable(ID INT NOT NULL,username VARCHAR(16) NOT NULL,UNIQUE [indexName] (username(length))); 

主键索引

它是一种特殊的唯一索引，不允许有空值。一般是在建表的时候同时创建主键索引

组合索引

基于多个字段而创建的索引就称为组合索引

组合索引多字段是<font color='cornflowerblue'>有序</font>的，并且是个完整的<font color='cornflowerblue'>BTree 索引，有最左原则</font>

多列索引是先按照第一列进行排序，然后在第一列排好序的基础上再对第二列排序，如果没有第一列的话，直接访问第二列，那第二列肯定是无序的，直接访问后面的列就用不到索引了。
搜索需要从根节点出发，上层节点对应靠左的值，搜索需要从根节点出发，否则不从根节点出发，后面的节点对应下层的值，依旧是乱序的，需要遍历，所以索引就失效了，所以有最左原则

组合索引的使用

例如组合索引（a,b,c），组合索引的生效原则是

从前往后依次使用生效，如果中间某个索引没有使用，那么断点前面的索引部分起作用，断点后面的索引没有起作用；

• where a=3 and b=45 and c=5 .... 这种三个索引顺序使用中间没有断点，全部发挥作用；
• where a=3 and c=5... 这种情况下b就是断点，a发挥了效果，c没有效果
• where b=3 and c=4... 这种情况下a就是断点，在a后面的索引都没有发挥作用，这种写法联合索引没有发挥任何效果；
• where b=45 and a=3 and c=5 .... 这个跟第一个一样，全部发挥作用，abc只要用上了就行，跟写的顺序无关

select * from mytable where a=3 and b=5 and c=4;
abc三个索引都在where条件里面用到了，而且都发挥了作用

select * from mytable where c=4 and b=6 and a=3;
这条语句列出来只想说明 mysql没有那么笨，where里面的条件顺序在查询之前会被mysql自动优化，效果跟上一句一样

select * from mytable where a=3 and c=7;
a用到索引，b没有用，所以c是没有用到索引效果的

select * from mytable where a=3 and b>7 and c=3;(范围值就算是断点)
a用到了，b也用到了，c没有用到，这个地方b是范围值，也算断点，只不过自身用到了索引

select * from mytable where b=3 and c=4;
因为a索引没有使用，所以这里 bc都没有用上索引效果

select * from mytable where a>4 and b=7 and c=9;
a用到了 b没有使用，c没有使用

select * from mytable where a=3 order by b;
a用到了索引，b在结果排序中也用到了索引的效果，前面说了，a下面任意一段的b是排好序的

select * from mytable where a=3 order by c;
a用到了索引，但是这个地方c没有发挥排序效果，因为中间断点了，使用 explain 可以看到 filesort

select * from mytable where b=3 order by a;
b没有用到索引，排序中a也没有发挥索引效果

索引的优缺点

优点

• 可以通过建立唯一索引或者主键索引，保证数据库表中每一行数据的唯一性
• 建立索引可以大大提高检索的数据，以及减少表的检索行数
• 在表连接的连接条件，可以加速表与表直接的相连
• 在分组和排序字句进行数据检索，可以减少查询时间中分组和排序所消耗的时间<font color='orange'>数据库的记录会重新排序</font>
• 建立索引，在查询中使用索引，可以提高性能

缺点

虽然索引大大提高了查询速度，同时却会降低更新表的速度，如对表进行INSERT、UPDATE和DELETE。因为更新表时，MySQL不仅要保存数据，还要保存索引文件。

建立索引会占用磁盘空间的索引文件。一般情况这个问题不太严重，但如果你在一个大表上创建了多种组合索引，索引文件的会膨胀很快。

如果某个数据列包含许多<font color='red'>重复</font>的内容，为它建立索引就没有太大的实际效果。

select count(distinct 列表字段)/count (*) from 表名;
查看在该表中该字段重复值的多少(1为没有重复值)

对于非常小的表，大部分情况下简单的全表扫描更高效；

建立索引的几大原则

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

使用索引需要注意的地方

• 在经常需要搜索的列上,可以加快索引的速度
• 主键列上可以确保列的唯一性
• 在表与表的连接条件上加上索引,可以加快连接查询的速度
• 在经常需要排序 (order by) ,分组 (group by) 和 列上加索引 可以加快排序查询的时间, (<font color='red'>单独order by 用不了索引，索引考虑加where 或加limit</font>)
• 在一些 where 之后的<font color='amber'> < <= > >= BETWEEN IN </font>以及某个情况下的like 建立字段的索引(B-TREE)
• <font color='red'>like语句</font>，前导模糊查询 <font color='amber'>like "%XXX" </font>不能使用索引，而非前导模糊查询 <font color='amber'>like "XXX%" </font>则可以
• <font color='red'>索引不会包含 NULL 列</font>,如果列中包含 NULL 值都将不会被包含在索引中,复合索引中如果有一列含有NULL值那么这个组合索引都将失效,一般需要给默认值0或者 ' ' 字符串
• 使用短索引,如果你的一个字段是 Char(32) 或者 int(32) ,在创建索引的时候指定前缀长度 比如前10个字符 (<font color='red'>前提是多数值是唯一的..</font>)那么短索引可以提高查询速度,并且可以减少磁盘的空间,也可以减少I/0操作.
• <font color='red'>不要在列上进行运算</font>,这样会使得mysql索引失效,也会进行全表扫描
• 选择越小的数据类型越好,因为通常越小的数据类型通常在磁盘,内存,cpu,缓存中 占用的空间很少,处理起来更快

不能创建索引

• <font color='red'>查询中很少使用到的列不应该创建索引,</font>如果建立了索引然而还会降低mysql的性能和增大了空间需求.
• <font color='red'>很少数据的列也不应该建立索引</font>,比如 一个性别字段 0或者1,在查询中,结果集的数据占了表中数据行的比例比较大,mysql需要扫描的行数很多,增加索引,并不能提高效率
• 定义为 <font color='red'>text</font> 和 <font color='red'>image</font> 和 <font color='red'>bit</font> 数据类型的列不应该增加索引
• 当表的修改(<font color='red'>UPDATE,INSERT,DELETE</font>)操作远远大于检索(<font color='red'>SELECT</font>)操作时不应该创建索引,这两个操作是互斥的关系

MySQL索引失效的情况

1.如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)

[图片上传失败...(image-e783e1-1609491805489)]

注意：要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引

2.对于多列索引，不是使用的第一部分(第一个)，则不会使用索引（最左原则）

3.like查询是以%开头

[图片上传失败...(image-b68754-1609491805489)]

4.如果列类型是字符串，那一定要在条件中将数据使用引号引用起来,否则不使用索引

[图片上传失败...(image-a8b0e7-1609491805489)]

5.如果mysql估计使用全表扫描要比使用索引快,则不使用索引

其他的

• 没有查询条件，或者查询条件没有建立索引

• 在查询条件上没有使用引导列

• 查询的数量是大表的大部分，应该是30％以上。

• 索引本身失效

• 查询条件使用函数在索引列上，或者对索引列进行运算，运算包括(+，-，*，/，! 等) 错误的例子：select * from test where id-1=9; 正确的例子：select * from test where id=10;

• 对小表查询

• 提示不使用索引

• 统计数据不真实

• CBO计算走索引花费过大的情况。其实也包含了上面的情况，这里指的是表占有的block要比索引小。

• 隐式转换导致索引失效.这一点应当引起重视.也是开发中经常会犯的错误. 由于表的字段tu_mdn定义为varchar2(20),但在查询时把该字段作为number类型以where条件传给Oracle,这样会导致索引失效. 错误的例子：select * from test where tu_mdn=13333333333; 正确的例子：select * from test where tu_mdn='13333333333';

• 1,<> 2,单独的>,<,(有时会用到，有时不会)

• like "%_" 百分号在前.

• 表没分析.

• 单独引用复合索引里非第一位置的索引列.

• 字符型字段为数字时在where条件里不添加引号.

• 对索引列进行运算.需要建立函数索引.

• not in ,not exist.

• 当变量采用的是times变量，而表的字段采用的是date变量时.或相反情况。

• B-tree索引 is null不会走,is not null会走,位图索引 is null,is not null 都会走

Explain

explain结果示例：

mysql> explain select * from staff;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | staff | ALL  | NULL          | NULL | NULL    | NULL |    2 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set

这是explain结果的各个字段，分别解释下含义：

<font color='red'>ID</font>

SQL查询中的序列号。

id列数字越大越先执行，如果说数字一样大，那么就从上往下依次执行。

<font color='red'>select_type</font>

查询的类型，可以是下表的任何一种类型：

DEPENDENT 意味着使用了关联子查询。

<font color='red'>table</font>

查询的表名。不一定是实际存在的表名。
可以为如下的值：

• <unionM,N>: 引用id为M和N UNION后的结果。
• <derivedN>: 引用id为N的结果派生出的表。派生表可以是一个结果集，例如派生自FROM中子查询的结果。
• <subqueryN>: 引用id为N的子查询结果物化得到的表。即生成一个临时表保存子查询的结果

<font color='red'>type</font>

这是最重要的字段之一，显示查询使用了何种类型。从最好到最差的连接类型依次为：

<font color='red'>system，const，eq_ref，ref，fulltext，ref_or_null，index_merge，unique_subquery，index_subquery，range，index，ALL</font>

除了<font color='red'>all</font>之外，<font color='orange'>其他的type都可以使用到索引</font>，除了<font color='red'>index_merge</font>之外，其他的type<font color='orange'>只可以用到一个索引</font>。

• 1、system

表中只有一行数据或者是空表，这是<font color='red'>const</font>类型的一个特例。且只能用于<font color='red'>myisam</font>和<font color='red'>memory</font>表。如果是Innodb引擎表，type列在这个情况通常都是all或者index

• 2、const

<font color='red'>最多只有一行记录匹配</font>。当联合主键或唯一索引的所有字段跟常量值比较时，join类型为const。其他数据库也叫做唯一索引扫描

• 3、eq_ref

多表join时，对于来自前面表的每一行，在当前表中只能找到一行。这可能是除了system和const之外最好的类型。当主键或唯一非NULL索引的所有字段都被用作join联接时会使用此类型。

eq_ref可用于使用'='操作符作比较的索引列。比较的值可以是常量，也可以是使用在此表之前读取的表的列的表达式。

<font color='red'>相对于下面的ref区别就是它使用的唯一索引，即主键或唯一索引，而ref使用的是非唯一索引或者普通索引。</font>

<font color='red'>eq_ref只能找到一行，而ref能找到多行。</font>

• 4、ref

对于来自前面表的每一行，在此表的索引中可以匹配到多行。若联接只用到索引的<font color='red'>最左前缀</font>或<font color='red'>索引不是主键或唯一索引</font>时，使用<font color='red'>ref</font>类型（也就是说，此联接能够匹配多行记录）。

ref可用于使用'='或'<=>'操作符作比较的索引列。

• 5、 fulltext

使用全文索引的时候是这个类型。要注意，全文索引的优先级很高，若全文索引和普通索引同时存在时，mysql不管代价，优先选择使用全文索引

• 6、ref_or_null

跟ref类型类似，只是增加了null值的比较。实际用的不多。

eg.
SELECT * FROM ref_table
WHERE key_column=expr OR key_column IS NULL;

• 7、index_merge

表示查询使用了两个以上的索引，最后取交集或者并集，常见and ，or的条件使用了不同的索引，官方排序这个在ref_or_null之后，但是实际上由于要读取多个索引，性能可能大部分时间都不如range

• 8、unique_subquery

用于where中的in形式子查询，子查询返回不重复值唯一值，可以完全替换子查询，效率更高。
该类型替换了下面形式的IN子查询的ref：

value IN (SELECT primary_key FROM single_table WHERE some_expr)

• 9、index_subquery

该联接类型类似于unique_subquery。适用于非唯一索引，可以返回重复值。

• 10、range

索引范围查询，常见于使用 =, <>, >, >=, <, <=, IS NULL, <=>, BETWEEN, IN()或者like等运算符的查询中。

SELECT * FROM tbl_name
  WHERE key_column BETWEEN 10 and 20;

SELECT * FROM tbl_name
  WHERE key_column IN (10,20,30);

• 11、index

索引全表扫描，把索引从头到尾扫一遍。这里包含两种情况：
一种是查询使用了覆盖索引，那么它只需要扫描索引就可以获得数据，这个效率要比全表扫描要快，因为索引通常比数据表小，而且还能避免二次查询。在extra中显示Using index，反之，如果在索引上进行全表扫描，没有Using index的提示。

# 此表见有一个name列索引。
# 因为查询的列name上建有索引，所以如果这样type走的是index
mysql> explain select name from testa;
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key      | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
|  1 | SIMPLE      | testa | index | NULL          | idx_name | 33      | NULL |    2 | Using index |
+----+-------------+-------+-------+---------------+----------+---------+------+------+-------------+
1 row in set

# 因为查询的列cusno没有建索引，或者查询的列包含没有索引的列，这样查询就会走ALL扫描，如下：
mysql> explain select cusno from testa;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | testa | ALL  | NULL          | NULL | NULL    | NULL |    2 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set

# 包含有未见索引的列
mysql> explain select * from testa;
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
|  1 | SIMPLE      | testa | ALL  | NULL          | NULL | NULL    | NULL |    2 | NULL  |
+----+-------------+-------+------+---------------+------+---------+------+------+-------+
1 row in set

• 12、all

全表扫描，性能最差。

table

数据来自那个表

passinle_keys字段

显示可能应用在这张表中的索引，<font color='red'>一个或多个</font>

查询涉及到的字段若存在索引，则该索引被列出，但不一定被实际使用

key

实际使用的索引，若为null，则没有使用索引

查询中若使用了覆盖索引<font color='red'>查询的列刚好是索引</font>，则该索引仅出现在key列表

key_len

查询用到的索引长度（字节数）。
如果是单列索引，那就整个索引长度算进去，如果是多列索引，那么查询不一定都能使用到所有的列，用多少算多少。留意下这个列的值，算一下你的多列索引总长度就知道有没有使用到所有的列了。

key_len只计算where条件用到的索引长度，而排序和分组就算用到了索引，也不会计算到key_len中。

ref

如果是使用的常数等值查询，这里会显示const，如果是连接查询，被驱动表的执行计划这里会显示驱动表的关联字段，如果是条件使用了表达式或者函数，或者条件列发生了内部隐式转换，这里可能显示为func

<font color='red'>rows</font>

rows 也是一个重要的字段。 这是mysql估算的需要扫描的行数（<font color='orange'>不是精确值</font>）。

这个值非常直观显示 SQL 的效率好坏, 原则上 rows 越少越好

事务

事务由 begin 或 start transaction 命令开始
结束语句为：commit 代表提交事务，对数据库做的修改成为永久性
          rollback 代表回滚事务，撤销正在进行的所有没有提交的修改

四大属性

分别是原子性、一致性、隔离性、持久性。

1、原子性（Atomicity）

原子性是指事务包含的所有操作要么<font color='orange'>全部成功</font>，要么<font color='orange'>全部失败回滚</font>，因此事务的操作<font color='orange'>如果成功就必须要完全应用到数据库，如果操作失败则不能对数据库有任何影响</font>。

2、一致性（Consistency）

一致性是指事务必须使数据库从一个一致性状态变换到另一个一致性状态，也就是说<font color='orange'>一个事务执行之前和执行之后都必须处于一致性状态。</font>举例来说，假设用户A和用户B两者的钱加起来一共是1000，那么不管A和B之间如何转账、转几次账，事务结束后两个用户的钱相加起来应该还得是1000，这就是事务的一致性。

3、隔离性（Isolation）

隔离性是当多个用户并发访问数据库时，比如同时操作同一张表时，数据库为每一个用户开启的事务，不能被其他事务的操作所干扰，多个并发事务之间要相互隔离。关于事务的隔离性数据库提供了多种隔离级别

4、持久性（Durability）

<font color='orange'>持久性是指一个事务一旦被提交了，那么对数据库中的数据的改变就是永久性的，即便是在数据库系统遇到故障的情况下也不会丢失提交事务的操作。</font>

为什么要设置隔离级别

• <font color='red'>更新丢失（Lost update） </font>
  如果多个线程操作，基于同一个查询结构对表中的记录进行修改，那么后修改的记录将会覆盖前面修改的记录，前面的修改就丢失掉了，这就叫做更新丢失。这是因为系统没有执行任何的锁操作，因此并发事务并没有被隔离开来。
  第1类丢失更新：事务A撤销时，把已经提交的事务B的更新数据覆盖了。

  
  
  第1类丢失更新
  
  

  第2类丢失更新：事务A覆盖事务B已经提交的数据，造成事务B所做的操作丢失。

  
  
  第2类丢失更新
  
  <font color='cornflowerblue'>解决方法：对行加锁，只允许并发一个更新事务。</font>

• <font color='red'>脏读（Dirty Reads）</font>
  脏读（Dirty Read）：A事务读取B事务尚未提交的数据并在此基础上操作，而B事务执行回滚，那么A读取到的数据就是脏数据。

  
  
  脏读图解
  
  

  <font color='cornflowerblue'>解决办法：如果在第一个事务提交前，任何其他事务不可读取其修改过的值，则可以避免该问题。</font>

• <font color='red'>不可重复读（Non-repeatable Reads） </font>
  一个事务对同一行数据重复读取两次，但是却得到了不同的结果。事务T1读取某一数据后，事务T2对其做了修改，当事务T1再次读该数据时得到与前一次不同的值。

  
  
  不可重复读数据
  
  

  <font color='cornflowerblue'>解决办法：如果只有在修改事务完全提交之后才可以读取数据，则可以避免该问题。</font>

• <font color='red'>幻象读 </font>
  指两次执行同一条 select 语句会出现不同的结果，第二次读会增加一数据行，并没有说这两次执行是在同一个事务中。
  [图片上传失败...(image-f131a5-1609491805489)]
  <font color='cornflowerblue'>解决办法：如果在操作事务完成数据处理之前，任何其他事务都不可以添加新数据，则可避免该问题。</font>

设置隔离级别代码

set session transaction isolation level 设置事务隔离级别

如设置read uncommitted级别：

set session transaction isolation level read uncommitted;

查看设置后的结果

SELECT @@tx_isolation;

事务的隔离级别

数据库事务的隔离级别有4个，由低到高依次为<font color='orange'>Read uncommitted(未授权读取、读未提交)</font>、<font color='orange'>Read committed（授权读取、读提交）</font>、<font color='orange'>Repeatable read（可重复读取）</font>、<font color='orange'>Serializable（序列化）</font>，这四个级别可以逐个解决脏读、不可重复读、幻象读这几类问题。

[图片上传失败...(image-4947ab-1609491805489)]

• <font color='red'>Read uncommitted(未授权读取、读未提交)</font>：
  如果一个事务已经开始写数据，则另外一个事务则不允许同时进行写操作，但允许其他事务读此行数据。该隔离级别可以通过“排他写锁”实现。这样就避免了更新丢失，却可能出现脏读。也就是说事务B读取到了事务A未提交的数据。
• <font color='red'>Read committed（授权读取、读提交）</font>：
  读取数据的事务允许其他事务继续访问该行数据，但是未提交的写事务将会禁止其他事务访问该行。该隔离级别避免了脏读，但是却可能出现不可重复读。事务A事先读取了数据，事务B紧接了更新了数据，并提交了事务，而事务A再次读取该数据时，数据已经发生了改变。
• <font color='red'>Repeatable read（可重复读取）</font>：
  可重复读是指在一个事务内，多次读同一数据。在这个事务还没有结束时，另外一个事务也访问该同一数据。那么，在第一个事务中的两次读数据之间，即使第二个事务对数据进行修改，第一个事务两次读到的的数据是一样的。这样就发生了在一个事务内两次读到的数据是一样的，因此称为是可重复读。
• <font color='red'>Serializable（序列化）</font>：
  提供严格的事务隔离。它要求事务序列化执行，事务只能一个接着一个地执行，但不能并发执行。如果仅仅通过“行级锁”是无法实现事务序列化的，必须通过其他机制保证新插入的数据不会被刚执行查询操作的事务访问到。序列化是最高的事务隔离级别，同时代价也花费最高，性能很低，一般很少使用，在该级别下，事务顺序执行，不仅可以避免脏读、不可重复读，还避免了幻像读。