MySQL学习笔记

---

---

MySQL学习笔记

1. MySQL架构和历史

1.1 事务

1.1.1 事务的原子性

• 一个事务必须被视为一个不可分割的最小工作单元，整个事务的所有操作要么全部提交成功，要么全部失败回滚，对于一个事务来说，不可能只执行其中的一部分操作，这就是事务的原子性

1.1.2 事务的一致性

• 数据库总是从一个一致性的状态转换到另外一个一致性的状态，一致性确保了在执行过程中，没有提交的事务不会保存的数据库中

1.1.3 事务的隔离性

• 通常来说，一个事务所做的修改在最终提交之前，对其他事务是不可见的

1.1.4 事务的持久性

• 一旦事务提交，那么其所做的修改就会永久保存到数据库中，此时即使系统崩溃，修改的数据也不会丢失。持久性是一个优点模糊的概念， 有些持久性的策略能够提供非常强的安全保障，而有些未必，而且不可能有能做到100%的持久性保证的策略

1.2 隔离级别

• Read UnCommitted(未提交读)

  在Read UnCommitted级别，事务中的修改，即使没有提交，对其他事务也都是可见的。事务可以读取未提交的数据，这也成为脏读(Dirty Read)。这个级别会导致很多问题，从性能上来说，Read UnCommitted不会比其他的级别好太多，但却缺乏其他级别的好处，除非真的有非常必要的理由，在实际应用中一般很少使用

• Read Committed(提交读)

  大多数数据库系统的默认级别都是Read Committed(但MySQL不是)。Read Committ满足前面提到的隔离性的简单定义：一个事务开始时，只能“看见”已经提交的事务所做的修改。换句话说，一个事务从开始直到提交之前，所做的任何修改对其他事务都是不可见的。这个级别有时候也叫不可重复读，因为两次执行同样的查询，可能会得到不一样的结果

• Repeatable Read(可重复读)

  Repeatable Read解决了脏读的问题，该级别保证了在同一个事务中多次读取同样记录的结果是一致的。但是理论上，可重复读隔离级别还是无法解决另外一个幻读的问题。所谓幻读，指的是当某个事务在读取某个范围内的记录时，另外一个事务又在该范围内插入了新的记录，当之前的事务再次读取该范围的记录时，会产生换行。可重复读是MySQL的默认事务隔离级别。

• Serializable(可串行化)

  Serializable是最高的隔离级别。它通过强制事务串行执行，避免了前面说的幻读的问题。简单来说，Serializable会在读取的每一行数据都加锁，所以可能导致大量的超时和锁争用的问题。实际应用中也很少用到这个隔离级别，只有在非常需要确保数据的一致性而且可以接受没有并发的情况下，才考虑采用该级别
  

1.3 MySQL中的事务

• MySQL提供了两种事务型的存储引擎：InnoDB和NDB Cluster。

• 自动提交(AutoCommit)

  MySQL默认采用自动提交模式。也就是说，如果不是显式地开始一个事务，则每个查询都被当做一个事务执行提交操作，在当前连接中，可以通过设置AutoCommit变量来启动或者禁用自动提交模式

​ 1或者ON表示启用，0或者OFF表示禁用。当AutoCommit=0时，所有的查询都是在一个事务中，直到显式地执行Commit提交豁然Rollback回滚，该事务结束，同时又开始了另一个新事务。

1.4 隐式和显示锁定

• 在事务执行过程中，随时都已执行锁定，锁只有在执行Commit和Rollback的时候才会释放，并且所有的锁是在同一个时刻被释放，这个锁定为隐式锁定。另外InnoDB也支持通过特定的语句进行显式锁定。
• MySQL支持Lock Tables和UnLock Tables语句，这些是在服务器层实现的，和存储引擎无关。但它们有自己的用途，并不能替代事务处理。如果应用需要用到事务，还是选择事务型存储引擎。
• 除了事务中禁用了Autcomitted可以使用Lock Tables之外，其他任何时候都不要显式地执行Lock Tables，不管用的是什么存储引擎

1.5 MVCC

• InnoDB的MVCC，是通过在每行记录后面保存两个隐藏的列来实现的。这两个列，一个保存了行的创建时间，一个保存行的过期时间(过删除时间)。当然储存的并不是实际的时间值，而是系统版本号。每开始一个新的事务，系统版本号都会自动递增。事务开始时刻的系统版本号会作为事务的版本号，用来和查询到的每行记录的版本号进行比较

• 在Repeatable Read隔离级别下，MVCC的操作

  • SELECT

• INSERT

• DELETE

• UPDATE

• 保存两个额外系统版本号，使大多数读操作都可以不用加锁。这样设计使得读数据操作很简单，性能很好，并且也能保证只会读取到符合标准的行。不足之处是每行记录都需要额外的村春空间，需要做更多的行检查工作，以及一些额外的维护工作

• MVCC只在Repeatable Read 和 Read Committed 两个隔离级别下工作，其他两个隔离级别都和MVCC不兼容

2. 使用MySQL

2.1 三大范式

• 第一范式

  无重复的列，每一列都是不可分割的基本数据项，同一 列中不能有多个值，即实体中的某个属性不能有多个值或者不 能有重复的属性。除去同类型的字段，就是无重复的列
  （说明:第一范式(1NF)是对关系模式的基本要求，不满足第 一范式(1NF)的数据库就不是关系数据库）

• 第二范式

  属性完全依赖于主键，第二范式必须先满足第一范式， 要求表中的每个行必须可以被唯一地区分。通常为表加上一个 列，以存储各个实例的唯一标识PK，非PK的字段需要与整个 PK有直接相关性

• 第三范式

  属性不依赖于其它非主属性，满足第三范式必须先满足 第二范式。第三范式要求一个数据库表中不包含已在其它表中 已包含的非主关键字信息，非PK的字段间不能有从属关系

2.2 检索数据

2.2.1 SELECT语句

• 检索单个列

SELECT prod_name From products;
利用 SELECT 语句从products表中检索一个名为pro_name的列，如上简单的一条 SELECT 语句将返回表中所有行

• 检索多个列

SELECT prod_id,prod_name,prod_price FROM products;
使用 SELECT 语句从表products查询了三个列的数据

• 检索所有列

SELECT * FROM products;
如果给定一个通配符(*), 则返回表中所有列，列的顺序一般是列在表定义中出现数据，但有时候也可能并不是这样
# 一般，除非确实需要表中的每个列，否则最好别使用*通配符，检索不需要的列通常会降低检索和应用程序的性能

• 检索不同的行

SELECT DISTINCT vend_id FROM products;
使用 DISTINCT 关键字 MySQL只返回不同的值，上句则只返回不同(唯一)的ven_id行
------
不能部分使用DISTINCT，DISTINCT关键字应用于所有列而不仅是前置他的列。如果给 SELECT DISTINCT vend_id , prod_price，除非指定的两个列都不同，否则所有航都会被检索出来

• 限制结果

SELECT prod_name FROM products LIMIT 5;
此语句用于检索单个列， LIMIT 5 指示MySQL返回不多于5行的结果
--------
SELECT prod_name FROM products LIMIT 5,5
此语句指示MySQL返回从行5开始的5行数据。 LIMIT n，n 第一个数为开始的位置，第二个数为要检索的行数

注意：行0检索出来的第一行为行0而不是行1.因此LIMIT 1,1将检索出第二行而不是第一行

2.2.2 排序检索数据

• 排序数据

SELECT prod_name FROM products ORDER BY prod_name;
这条语句对prod_name列以字母顺序排序数据
通常， ORDER BY子句中使用的列将是为显示所选择的列，但是，实际上并一定要这样，用非检索的列排序数据完全是合法的。

• 按多个排序

SELECT prod_id,prod_price,prod_name FROM products ORDER BY prod_price,prod_name;
先按价格进行排序，然后按名称排序。注意：仅在多个行具有相同的prod_price值时才对产品prod_那么排序，
即如果prod_price的所有值都是唯一的，则不会按prod_name排序

• 指定排序方向

SELECT prod_id,prod_price,prod_name FROM products ORDER BY prod_price DESC;
价格按降序排序产品
------
SELECT prod_id,prod_price,prod_name FROM products ORDER BY prod_price DESC,prod_name;
以降序排序产品的价格，然后再对产品名排序

注意：DESC关键字只应用到直接位于其前面的列明。在上例中，只对prod_price列指定DESC，对prod_name不指定，那么，prod_price列以降序排序，而prod_name列仍然按标准的升序排序

如果想在多个列上进行降序排序，必须对每个列指定DESC关键字

在给出ORDER BY子句时，应该保证它位于FROM子句之后；如果使用LIMIT，它必须位于ORDER BY之后，使用子句的次序不对将产生错误消息

2.3 过滤数据

2.3.1 WHERE子句

• where子句操作符

= 等于   <>不等于  !=不等于  <小于 >大于 <=小于等于  >=大于等于  BETWEEN在指定的两个值之间

• 空值检查

SELECT prod_name FROM products WHERE prod_price IS NULL;
这条语句返回没有价格(空prod_price字段，不是价格为0)的所有产品

• 计算次序

AND 大于 OR

• IN 操作符

SELECT prod_name,prod_price FROM products WHERE vend_id IN (1002,1003)
ORDER BY prod_name;
此语句检索供应商1002和1003制造的所有产品， IN操作符用来指定条件范围，范围中的每个条件都可以进行匹配

• NOT 操作符

SELECT prod_name,prod_price FROM products WHERE vend_id NOT IN (1002,1003)
ORDER BY prod_name;
NOT是用来否定后跟条件的关键字，此句将列出除1002和1003之外的所有供应商制造的产品

2.3.2 通配符过滤

• %通配符

SELECT prod_id,prod_name FROM products WHERE prod_name LIKE 'jet%'
执行此句子将检索任意以jet起头的此，%告诉mysql接受jet之后的任意字符，不管它有多少字符
-----
SELECT prod_id,prod_name FROM products WHERE prod_name LIKE '%anvil%';
此句将检索任何位置包含文本anvil的值，而不论它之前或之后出现什么字符

• _通配符

SELECT prod_id,prod_name FROM products WHERE prod_name LIKE '_ton anvil'
_只匹配单个字符而不是多个字符

2.3.3 计算字段

• 拼接字段

SELECT CONCAT(vend_name , '(' , vend_country , ')') FROM vendors ORDER BY vend_name;
CONCAT()拼接串，即把多个串连接起来形成一个较长的串。CONCAT()需要一个或多个指定的串，各个串之间用逗号分隔。

• 使用别名

SELECT CONCAT( vend_name , '(' , vend_country , ')') AS vend_title FROM vendors ORDER BY vend_name;
多了 AS 关键字之后指示MySQL创建一个包含指定计算的名为vend_title的计算字段，任何客户机应用都可以按名引用这个这个列，就像它是一个实际的表列一样

2.3 数据处理函数

2.3.1 文本处理函数

Left()   返回串左边的字符       Length() 返回串的长度      Locate() 返回串的一个子串
Lower()  将串转换为小写         LTrim()  去掉串左边的空格   Right()  返回串右边的字符
RTrim()  去掉串右边的空格       Soundex() 返回串的SOUNDEX值  SubString() 返回子串的字符
Upper()  将串转换为大写

2.3.2 日期和时间处理函数

AddDate()  增加一个日期(天、周等)  AddTime()  增加一个时间(时、分等)   CurDate()  返回当前日期
CurTime()  返回当前时间     Date() 返回日期时间的日期部分   DateDiff() 计算两个日期之差
Day() 返回一个日期的天数部分  now() 返回当前日期和时间

2.3.3 聚集函数

• AVG()函数

SELECT AVG(prod_price) AS avg_price FROM products WHERE vend_id=1003;
此句返回vend_id=1003的供应厂的所有产品的平均值

• COUNT()函数

COUNT()函数有两种使用方法
1. 使用 COUNT(*)对表中行的数目进行计数，不管表列中包含的是空值(NULL)还是非空值
2. 使用 COUNT(columm)对特定的列中具有值得行进行计数，忽略NULL值 

2.4 分组数据

• 创建分组

SELECT vend_id COUNT(*) AS num_prods FROM products GROUP BY vend_id;
GROUP BY 子句指示MySQL按vend_id排序并分组数据，这导致对每个vend_id而不是整个表计算num_prods一次。因为使用了 GROUP BY，就不必指定要计算和估值的每个组了，系统会自动完成， GROUP BY 子句指示MySQL分组数据，然后对每个组而不是整个结果集进行聚集

• 过滤分组

SELECT cust_id,COUNT(*) AS orders FROM orders GROUP BY cust_id HAVING COUNT(*) >= 2;
此语句的结果类似于上述两例，不过它过滤 COUNT(*)>=2(两个以上的)那些分组

WHERE与HAVING的差别：WHERE过滤行，HAVING过滤分组。或者说WHERE在数据分组前进行过滤，HAVING在数据分组后进行过滤。

SELECT vend_id,COUNT(*) AS num_prods FROM products WHERE prod_price >= 10 GROUP BY vend_id HAVING COUNT(*) >= 2;
WHERE子句过滤所有prod_id至少为10的行，然后按vend_id分组数据， HAVING子句过滤计数为2或2以上的分组

• 分组和排序

SELECT order_num , SUM(quantity*item_price) AS ordertotal FROM orderitems GROUP BY order_num
HAVING SUM(quantity*item_price) >=50 ORDER BY ordertotal;
在这个列子中， GROUP BY 子句用来按订单号(order_num列)分组数据，以便 SUM(*)函数能够返回总计订单价格， HAVING子句过滤数据，使得只返回总计订单价格大于等于50的订单，最后用 ORDER BY 子句排序输出

2.5 使用子查询

• 利用子查询进行过滤

SELECT cust_id FROM orders WHERE order_num IN (
                                         SELECT order_num FROM orderitems WHERE prod_id='TNT2')
在 SELECT 语句中，子查询总是从内向外处理，在处理上面的语句中，先执行括号内的查询，然后返回的值以 IN操作符要求的逗号分隔的形式传递给外部查询的 WHERE 子句                      

**注意：**在WHERE子句中使用子查询，应该保证SELECT语句具有与WHERE子句中相同数目的列，通常，子查询将返回单个列并且与单个列匹配，但如果需要也可以使用多个列

• 作为计算字段使用子查询

SELECT cust_name,cust_state,(
                          SELECT COUNT(*) FROM orders WHERE orders.cust_id = customers.cust_id)
AS order FROM customers ORDER BY cust_name;
此条语句对每个客户执行 COUNT(*)计算，将 COUNT(*)作为一个子查询

2.6 联结表

• 创建联结

SELECT vend_name,prod_name,prod_price FROM vendors,products WHERE vendors.vend_id = products.vend_id ORDER BY  vend_name,prod_name;
此语句从两张表中进行查询， WHERE子句指示 MySQL 匹配vendors表中的vend_id 和 products表中的vend_id

• 内部联结

SELECT vend_name,prod_name,prod_price FROM vendors INNER JOIN products ON vendors.vend_id = products.vend_id
此句查询的结果与上列相同的数据

• 联结多个表

SELECT prod_name,vend_name,prod_price,quantity FROM orderitems,products,vendors WHERE 
products.vend_id = vendors.vend_id AND orderitems.prod_id = products.prod_id AND order_num = 2005;
此例显示编号为2005的订单中的物品，订单物品存储在orderitems表中，每个产品按其产品ID存储，它引用products表中的产品，这些产品通过供应商ID联结到vendors表中相应的供应商，供应商ID存储在每个产品的记录中。

2.7 高级联结

• 自联结

SELECT p1.prod_id,p1.prod_name FROM products AS p1,products AS p2 WHERE p1.vend_id = p2.vend_id 
AND p2.prod_id = 'DTNTR';
WHERE通过匹配p1中的vend_id和p2中的vend_id首先联结两个表，然后按第二个表中的prod_id过滤数据，返回所需要的数据

• 自然联结

SELECT c.*,o.order_num,o.order_date,oi.prod_id,oi.quantity,oi.item_price FROM customers AS c，order AS o，orderitems AS oi WHERE c.cust_id = o.cust_id AND oi.order_num = o.order_num AND prod_id = 'FB'；

• 外部联结

SELECT customers.cust_id,order.order_num FROM customers LEFT OUTER JOIN orders ON customers.cust_id=orders.cust_id；
与内部联结关联两个表中的行不同的是，外部联结还包括没有关联行的行，在使用 OUTER JOIN语法时，必须使用 RIGHT或 LEFT关键字指定包括其所有行的表( RIGHT 指出的是 OUTER JOIN右边的表， LEFT指出的是 OUTER JOIN左边的表)

• 带聚集函数的联结

SELECT customers.cust_name.customers.cust_id,COUNT(orders.order_num) AS num_ord FROM customers INNER JOIN orders ON customers.cust_id = orders.cust_id GROUP BY customers.cust_id;
此 SELECT语句使用 INNER JOIN 将customers和orders表互相关联， GROUP BY 子句按客户分组数据，因此，函数调用 COUNT(orders.order_num)对每个客户的订单计数，将它作为num_ord返回

2.8 组合查询

• 使用UNION

SELECT vend_id,prod_id,prod_price FROM products WHERE prod_price <=5 UNION SELECT vend_id,prod_id,prod_price FROM products WHERE vend_id IN (1001,1002);
此例将查询出价格小于等于5的所有产品同时还包括1001和1002生产的所有产品
------------
SELECT vend_id,prod_id,prod_price FROM products WHERE prod_price <= 5 OR vend_id IN (1001,1002)
此句查询结果与上例相同，有些情况使用 UNION 可能比使用 WHERE子句更为复杂，但对于更复杂的过滤条件或者从多个表中检索数据的情形，使用 UNION可能会使处理更简单

• 包含或取消重复的行

SELECT vend_id,prod_id,prod_price FROM products WHERE prod_price <= 5 UNION ALL SELECT vend_id,prod_id,prod_price FROM products WHERE vend_id IN (1001,1002);
UNION语句默认去除重复的的行，如果需要可以使用 UNION ALL 返回所有匹配行

• 对组合查询结果排序

SELECT vend_id,prod_id,prod_price FROM products WHERE prod_price <= 5 UNION SELECT vend_id,prod_id,prod_price FROM products WHERE vend_in IN(1001,1002) ORDER BY vend_id,prod_price;
此条语句在最后一条SELECT 语句中使用了 ORDER BY 子句，虽然 ORDER BY 子句似乎只是最后一条 SELECT子句的组成部分，但实际上 MySQL 将用它来排序所有 SELECT 语句返回的所有结果

2.9 存储过程

• 执行存储过程，

  MySQL称存储过程的执行为调用，因此MySQL执行存储过程的语句去CALL。CALL接受存储过程的名字以及需要传递给它的任意参数

CALL productpricing(@pricelow,@pricehigh,@priceaverage)；
执行名为productpricing的存储过程，它计算并返回产品的最低、最高和平均价格

• 创建存储过程

  CREATE PROCEDURE productpricing()
  BEGIN
    SELECT AVG(prod_price) AS priceaverage
    FROM products;
  END；

此存储过程名为productpricing，用CREATE PROCEDURE productpricing()语句定义，如果存储过程接受参数，它们将在( )中列举出来，此存储过程没有参数，但后面的( )仍然需要。BEGIN和END语句用来限定存储过程体，过程体本身仅是一个简单的SELECT语句

• 使用存储过程

CALL productpricing()；
返回上述过程体中的查询结果

• 使用参数

CREATE PROCEDURE productpricing(
      OUT p1 DECIMAL(8,2)
      OUT ph DECIMAL(8,2)
      OUT pa DECIMAL(8,2)
)
BEGIN
     SELECT MIN(prod_price)
     INTO p1
     FROM products;
     SELECT MAX(prod_price)
     INTO ph
     FROM products;
     SELECT AVG(prod_price)
     INTO pa
     FROM products
END;
注： DECIMAL(N,M)指的是 N为整数部分加小数部分的总长度，N为小数部分的长度

调用修改过得存储过程，必须制定3个变量名

CALL productpricing(@pricelow,@pricehigh,@priceaverage)；
为了显示检索出的产品平均价格:
SELECT @priceaverage;
为了获得3个值，可使用以下语句:
SELECT @pricehigh,@pricelow,@priceaverage；

• 使用 IN 和 OUT参数

CREATE PROCEDURE ordertotal(
      IN onumber INT，
      OUT ototal DECIMAL(8,2)
)
BEGIN
  SELECT SUM(iten_price*quantity)
  FROM orderitems
  WHERE order_num = onumber
  INTO ototal;
END;

onumber定义为IN,因为订单号传入存储过程。ototal定义为OUT，因为要从存储过程返回合计，SELECT语句使用这两个参数，WHERE子句使用onumber旋蒸正确的行，INTO使用ototal存储计算出来的合计

为调用这个新存储过程，使用以下语句:

CALL ordertotal(20005,@total);
为了显示此合计，可如下进行：
SELECT @total;
-------------
为了得到另一个订单的合计显示，需要再次调用存储过程
CALL ordertotal(20009,@total);
SELECT @total;

• 创建游标

CREATE PROCEDURE processorders()
BEGIN
   DECLARE ordernumbers CURSOR
   FOR
   SELECT order_num FROM orders;
END;

这个存储过程并没有做很多事情，DECLARE语句用来定义和命名游标，这里为ordernumbers，存储过程处理完成后，游标就消失(因为它局限于存储过程)

• 打开和关闭游标

OPEN ordernumbers;
在处理 OPEN 语句时执行查询，存储检索出的数据以供浏览和滚动
CLOSE ordernumbers;
CLOSE释放游标使用的所有内部内存和资源，因此在每个游标不再需要时都应该关闭

• 使用游标数据

CREATE PROCEDURE processorders()
BEGIN
  -- Declare local variables
  DECLARE ordernumbers CURSOR
  FOR
  SELECT order_num FROM orders;
  
  -- open the cursor
  OPEN ordernumbers;
  
  -- get order number
  FETCH ordernumbers INTO o;
  
  -- close the cursor
  CLOSE ordernumbers;
END;

从游标中检索检索单个行(第一行),其中FETCH用来检索当前行的order_num列（将自动从第一行开始）到一个名为o的局部声明变量中，对检索出的数据不做任何处理。

3. MySQL优化

3.1 触发器

3.1.1 创建触发器

• 在创建触发器时，需要给出4条信息:
  • 唯一的触发器名
  • 触发器关联的表
  • 触发器应该响应的活动(DELETE、INSERT或UPDATE)
  • 触发器何时执行(处理之前或之后)
• 触发器使用CREATE TRIGGER语句创建

CREATE TRIGGER newproduct AFTER INSERT ON products
FOR EACH ROW SELECT 'Product added';

CREATE TRIGGER用来创建名为 newproduct 的新触发器，触发器可在一个操作发生之前或之后执行，这里给出了AFTER INSERT,所以此触发器将在INSERT语句成功执行后执行，这个触发器还指定FOR EACH ROW,因此代码对每个插入行执行，这个上述例子中，文本Product added将对每个插入的行显示一次。

3.2.2 删除触发器

DROP TRIGGER newproduct；

3.2.3 使用触发器

• INSERT触发器

CREATE TRIGGER neworder AFTER INSERT ON orders
FOR EACH ROW SELECT NEW.order_num;

此代码创建一个名为neworder的触发器，它按照AFTER_INSERT ON orders执行，在插入一个新订单到orders表时，MySQL生成一个新订单号保存到order_num中，触发器从NEW.order_num取得这个值并返回它

• DELETE触发器

CREATE TRIGGER deleteorder BEFORE DELETE ON orders
FOR EACH ROW
BEGIN
   INSERT INTO archive_orders(order_num,order_date,cust_id)
   VALUES(OLD.order_num,OLD.order_date,OLD.cust_id);
END;

在任意订单被删除前将执行此触发器，它使用一条INSERT语句将OLD中的值(需要被删除的订单)保存到一个名为archive_orders的存档表中

• UPDATE触发器

CREATE TRIGGER updatevendor BEFORE UPDATE ON vendors
FOR EACH ROW SET NEW.vend_state = Upper(NEW.vend_state);

显然，任何数据净化都需要在update语句之前进行，就像这个例子一样，每次更像一个行时，NEW.vend_state中的(将用来更新表行的值)都用Upper(NEW.vend_state)替换

3.2 索引

• 索引对于良好的性能非常关键，尤其是当表中的数据量越来越大时，索引对性能的影响越发重要，索引优化是对查询性能优化最有效的手段，索引能够轻易将查询性能提高几个数量级

3.2.1 B-Tree索引

• B-Tree通常意味着所有的值都是按顺序存储的，并且每个叶子页到根的距离相同，下图为B-Tree索引的抽象表示

B-Tree索引能加快访问数据的速度，因为存储引擎不再需要进行全表扫描来获取需要的数据，取而代之的是从索引的根节点开始进行搜索。根节点的槽中存放了指向子节点的指针，存储引擎根据这些指针向下层查找，通过比较节点页的值和要查找的值可以找到合适的指针进入下层子节点，这些指针实际上定义了子节点页中值得上限和下限。最后存储引擎要么找到对应的值，要么该记录不存在。

叶子节点比较特别，它们的指针指向的是被索引的数据，而不是其他的节点页。上图中绘制了一个节点和其对应的叶子节点，其实在根节点和叶子节点之间可能还有很多层节点页，树的深度和表达大小直接相关。

• B-Tree对索引列是顺序组织存储的，所以很适合查找范围数据。例如，在一个基于文本域的索引树上，按字母顺序传递连续的值进行查找是非常合适的，所以像"找出所有以I到K开头的名字"这样的查找效率会非常高。
• 假如有如下数据表:

对于表中的每一行数据，索引中包含了 last_name、first_name和dob列的值，下图显示该索引是如何组织数据的存储的。

索引对多个值进行排序的依据是 CREATE TABLE 语句中定义索引时列的顺序。看一下最后两个条目，两个人的姓和名都一样，则根据他们的出生日期来排序，前面所述的索引对如下类型的查询有效：

1.全值匹配
全值匹配指的是和索引中的所有列进行匹配，例如前面提到的索引可用于查找姓名为Cuba Allen、出生于1960-01-01的人
2.匹配最左前缀
前面提到的索引可用于查找所有姓为Allen的人，即只使用索引的第一列
3.匹配列前缀
也可以只匹配某一列的值得开头部分。例如前面提到的索引可用于查找所有以J开头的姓的人。这里也只使用了索引的第一列
4.匹配范围值
例如前面提到的索引可用于查找姓Allen和Barrymore之间的人，这里也只使用了索引的第一列
5.精确匹配某一列兵范围匹配另外一列
前面提到的索引页可用于查找所有行为Allen，并且名字是字母K开头(比如Kim、Karl等)的人，即第一列last_name全匹配，第二列first_name范围匹配
6.只访问索引的查询
B-Tree通常可以支持"只访问索引的查询"，即查询只需要访问索引，无需访问数据行。

3.2.2 B-Tree索引的限制

• 如果不是按照索引最左列开始查找，则无法使用索引。例如上面例子中的索引无法用于查找名字为Bill的人，也无法查找某个特定生日的人，因为这两列都不是最左数据列。类似的，也无法查找姓氏以某个字母结尾的人。
• 不能跳过索引中的列。也就是说，前面所述的索引无法用于查找姓为Smith并且某个特定日期出生的人。如果不指定名(first_name)，则MySQL只能使用索引的第一列
• 如果查询中有某个列的范围范围查询，则其右边所有列都无法使用索引优化查找。例如有如下查询

WHERE last_name='Smith' AND first_name LIKE 'J%' AND dob='1976-12-23'

这个查询只能用索引的前两列，因为这个Like是一个范围条件(但是服务器可以把其余列用于其他目的)。如果范围查询列值得数量有限，那么可以通过使用多个等于条件来代替范围条件

**综上：**索引列的顺序非常重要，这些限制都和索引列的顺序有关，在优化性能的时候，可能需要使用相同过得列但顺序不同的索引来满足不同类型的查询需求

3.2.3 哈希索引

• 哈希索引基于哈希表实现，只有精确匹配索引所有列的查询才有效。对于每一行数据，存储引擎都会对所有的索引列计算一个哈希码，哈希码是一个较小的值，并且不同键值的行计算出来的哈希码也不一样。哈希索引将所有的哈希码存储在索引中，同时在哈希表中保存指向每个数据行的指针
• 假如有如下表

现在有如下的查询:

SELECT lname FROM testhash WHERE fname='Peter';

MySQL先计算’Peter’的哈希值，并使用该值寻找对应的记录指针，因为 f(‘Peter’)=8784，所以MySQL在索引中查找8784，可以找到指向第3行的指针，最后一步是比较第三行的值是否为’Peter’，以确保就是要查找的行。因为索引自身只需要存储对应的哈希值，所以索引的结构十分紧凑，这也让哈希索引查找的速度非常快。

3.2.4 哈希索引的限制

• 哈希索引只包含哈希值和行指针，而不存储字段值，所以不能使用索引中的值来避免读取行。不过，访问内存中的行的速度很快，所以大部分情况下这一点对性能的影响并不明显。
• 哈希索引数据并不是按照索引值顺序存储的，所以也就无法用于排序
• 哈希索引也不支持部分索引匹配查找，因为哈希索引始终是使用索引列的全部内容来计算哈希值的。例如，在数据列(A,B)上建立哈希索引，如果查询只有数据列A，则无法使用该哈希索引
• 哈希索引只支持等值比较查询，包括=、IN()、<=>(注意<>和<=>是不同的操作)。也不支持任何范围查询，例如WHERE price > 100
• 访问哈希索引的数据非常快，除非有很多哈希冲突(不同的索引列值却又相同的哈希值)。当出现哈希冲突的时候，存储引擎必须遍历链表中所有的行指针，逐行进行比较，直到找到所有符合条件的行
• 如果哈希冲突很多的话，一些索引维护操作的代价也会很高。例如，如果在某个选择性很低(哈希冲突很多)的列上建立哈希索引，那么当从表中删除一行时，存储引擎需要遍历对应哈希表的链表中的每一行，找到并删除对应行的引用，冲突越多，代价越大

3.2.5 处理哈希冲突

• 当使用哈希索引进行查询的时候，必须在WHERE子句中包含常量值，一旦出现哈希冲突，另一个字符串的哈希值也恰好相同，则查询是无法正确工作的
• 要避免哈希冲突问题，必须在WHERE条件中带入哈希值和对应列值。如果不是想查询具体的值，例如只是统计记录数(不精确的)，则可以不带入列值

3.3 高性能的索引策略

3.3.1 独立的列

• 很多时候MySQL无法使用已有的索引，就是因为查询中的列不是独立的，这样MySQL就不会使用索引。"独立的列"是指索引列不能是表达式的一部分，也不能是函数的参数,我们应该养成简化where条件的习惯，始终将索引列单独放在比较符号的一侧

3.3.2 多列索引

• 很多人对多列索引的理解都不够，一个常见的错误就是，为每个列创建独立的索引，或者按照错误的顺序创建多列索引。
• 在一个多列B-Tree索引中，索引列的顺序意味着索引首先按照最左列进行排序，其次是第二列，等等。所以，索引可以按照升序或者降序进行扫描，以满足精确符合列顺序的ORDER BY、GROUP BY、DISTINCTde等子句的查询需求。

3.3.3 聚簇索引

• 聚簇索引并不是一种单独的索引类型，而是一种数据存储方式。具体的细节依赖于其实现方式，但InnoDB的聚簇索引实际上在同一个结构中保存了B-Tree索引和数据行。当表有聚簇索引时，它的数据行实际上存放在索引的叶子页(leaf page)中，术语"聚簇"表示数据行和相邻的键值紧凑地存储在一起。因为无法同时把数据行存放在两个不同的地方，所以一个表只能有一个聚簇索引。聚簇索引默认是主键，如果表中没有定义主键，InnoDB 会选择一个唯一的非空索引代替。如果没有这样的索引，InnoDB 会隐式定义一个主键来作为聚簇索引。InnoDB 只聚集在同一个页面中的记录。包含相邻健值的页面可能相距甚远。

• 聚簇索引的优点：

  1. 可以把相关数据保存在一起。例如实现电子邮箱时，可以根据用户ID来聚集数据，这样只需要从磁盘读取少数的数据页就能获取某个用户的全部邮件。如果没有使用聚簇索引，则每封邮件都可能导致一次磁盘I/O
  2. 数据访问更快。聚簇索引将索引和数据保存在同一个B-Tree中，因此从聚簇索引中获取数据通常比在非聚簇索引中查找更快
  3. 使用覆盖索引扫描的查询可以直接使用页节点中的主键值。

• 聚簇索引的缺点：

  1. 聚簇索引最大限度地提高了I/O密集型应用的性能，但如果数据全部都放在内存中，则访问的顺序就没那么重要了，聚簇索引页就没什么优势了。
  2. 插入速度严重依赖于插入顺序。按照主键的顺序插入是加载数据到InnoDB表中速度最快的方式。但如果不是按照主键顺序加载数据，那么在加载完成后最后使用 OPTIMIZE TABLE 命令重新组织一下表
  3. 更新聚簇索引列的代价很高，因为会强制InnoDB将每个被更新的行移动到新的位置
  4. 基于聚簇索引的表在插入新行，或者主键被更新导致需要移动行的时候，可能面临 "页分裂(page split)"的问题。当行的主键值要求必须将这一行插入到某个已满的页中时，存储引擎会将该页分裂成两个页面容纳该行，这就是一次页分裂的操作。页分裂会导致表占用更多的磁盘空间。
  5. 聚簇索引可能导致全表扫描变慢，尤其是行比较稀疏，或者由于页分裂导致数据存储不连续的时候
  6. 二级索引(非聚簇索引)可能比想象的要更大，因为在二级索引的叶子节点包含了引用行的主键列。
  7. 二级索引访问需要两次索引查找，而不是一次(二级索引叶子节点保存的不是指向行的物理位置的指针，而是行的主键值，这意味着通过二级索引查找行，存储引擎需要找到二级索引叶子节点获得对应的主键值，然后根据这个值去聚簇索引中查找熬对应的行。这里做了重复的工作：两次B-Tree查找而不是一次)

3.3.4 聚簇表和非聚簇表的区别

• 聚簇索引：将数据存储与索引放到了一块，找到索引也就找到了数据
• 非聚簇索引：将数据存储于索引分开结构，索引结构的叶子节点指向了数据的对应行，myisam通过key_buffer把索引先缓存到内存中，当需要访问数据时（通过索引访问数据），在内存中直接搜索索引，然后通过索引找到磁盘相应数据，这也就是为什么索引不在key buffer命中时，速度慢的原因

澄清一个概念：innodb中，在聚簇索引之上创建的索引称之为辅助索引，辅助索引访问数据总是需要二次查找，非聚簇索引都是辅助索引，像复合索引、前缀索引、唯一索引，辅助索引叶子节点存储的不再是行的物理位置，而是主键值

结合另外一图可看出区别

1. InnoDB使用的是聚簇索引，将主键组织到一棵B+树中，而行数据就储存在叶子节点上，若使用"where id = 14"这样的条件查找主键，则按照B+树的检索算法即可查找到对应的叶节点，之后获得行数据。
2. 若对Name列进行条件搜索，则需要两个步骤：第一步在辅助索引B+树中检索Name，到达其叶子节点获取对应的主键。第二步使用主键在主索引B+树种再执行一次B+树检索操作，最终到达叶子节点即可获取整行数据。（重点在于通过其他键需要建立辅助索引）

MyISM使用的是非聚簇索引，非聚簇索引的两棵B+树看上去没什么不同，节点的结构完全一致只是存储的内容不同而已，主键索引B+树的节点存储了主键，辅助键索引B+树存储了辅助键。表数据存储在独立的地方，这两颗B+树的叶子节点都使用一个地址指向真正的表数据，对于表数据来说，这两个键没有任何差别。由于索引树是独立的，通过辅助键检索无需访问主键的索引树。

• 何时使用聚簇索引与非聚簇索引
  

3.3.5 为什么主键通常建议使用自增id

聚簇索引的数据的物理存放顺序与索引顺序是一致的，即：只要索引是相邻的，那么对应的数据一定也是相邻地存放在磁盘上的。如果主键不是自增id，那么可以想 象，它会干些什么，不断地调整数据的物理地址、分页，当然也有其他一些措施来减少这些操作，但却无法彻底避免。但，如果是自增的，那就简单了，它只需要一 页一页地写，索引结构相对紧凑，磁盘碎片少，效率也高。

因为MyISAM的主索引并非聚簇索引，那么他的数据的物理地址必然是凌乱的，拿到这些物理地址，按照合适的算法进行I/O读取，于是开始不停的寻道不停的旋转。聚簇索引则只需一次I/O。（强烈的对比）

不过，如果涉及到大数据量的排序、全表扫描、count之类的操作的话，还是MyISAM占优势些，因为索引所占空间小，这些操作是需要在内存中完成的。

3.3.6 全文索引

文本字段上(text)如果建立的是普通索引,那么只有对文本的字段内容前面的字符进行索引,其字符大小根据索引建立索引时申明的大小来规定.

如果文本中出现多个一样的字符,而且需要查找的话,那么其条件只能是 where column lick ‘%xxxx%’ 这样做会让索引失效这个时候全文索引就起到了作用了

ALTER TABLE tablename ADD FULLTEXT(column1, column2)

有了全文索引，就可以用SELECT查询命令去检索那些包含着一个或多个给定单词的数据记录了。

ELECT * FROM tablename
WHERE MATCH(column1, column2) AGAINST(‘xxx′, ‘sss′, ‘ddd′)

这条命令将把column1和column2字段里有xxx、sss和ddd的数据记录全部查询出来。

3.4 索引的分类

• 从数据结构的角度

  1. B+树索引

  2. hash索引

  3. FULLTEXT索引

  4. R-Tree索引

• 从物理存储角度

  1. 聚簇索引
  2. 非聚簇索引

• 从逻辑角度

  1. 主键索引：主键索引是一种特殊的唯一索引，不允许有空值

  2. 普通索引或者单列索引

  3. 多列索引（复合索引）：复合索引指多个字段上创建的索引，只有在查询条件中使用了创建索引时的第一个字段，索引才会被使用。使用复合索引时遵循最左前缀集合

  4. 唯一索引或者非唯一索引

  5. 空间索引：空间索引是对空间数据类型的字段建立的索引，MYSQL中的空间数据类型有4种，分别是GEOMETRY、POINT、LINESTRING、POLYGON。
     MYSQL使用SPATIAL关键字进行扩展，使得能够用于创建正规索引类型的语法创建空间索引。创建空间索引的列，必须将其声明为NOT NULL，空间索引只能在存储引擎为MYISAM的表中创建

3.5 使用过程中的坑

• 使用 explain+ 查询语句可以查看是否有启用索引
• 添加unique时，即添加唯一索引。
•