SQL Index

发表于 | 分类于 ，

什么是索引？

SQL索引有两种，聚集索引和非聚集索引，索引主要目的是提高了SQL Server系统的性能，加快数据的查询速度与减少系统的响应时间。

索引的存储机制

无索引的表，查询时，是按照顺序存续的方法扫描每个记录来查找符合条件的记录，这样效率十分低下。

聚集索引和非聚集索引的根本区别是表记录的排列顺序和与索引的排列顺序是否一致。

举字典的例子：如果按照拼音查询，那么都是从a-z的，是具有连续性的，a后面就是b，b后面就是c。

聚集索引就是这样的，他是和表的物理排列顺序是一样的，例如有id为聚集索引，那么1后面肯定是2,2后面肯定是3，所以说这样的搜索顺序的就是聚集索引。

非聚集索引就和按照部首查询是一样是，可能按照偏房查询的时候，根据偏旁‘弓’字旁，索引出两个汉字，张和弘，但是这两个其实一个在100页，一个在1000页，
（这里只是举个例子），他们的索引顺序和数据库表的排列顺序是不一样的，这个样的就是非聚集索引。

什么情况下设置索引

http://www.cnblogs.com/AK2012/archive/2013/01/04/2013-0104.html

建立索引的原则：

  1. 定义主键的数据列一定要建立索引。

  2. 定义有外键的数据列一定要建立索引。

  3. 对于经常查询的数据列最好建立索引。

  4. 对于需要在指定范围内的快速或频繁查询的数据列;

  5. 经常用在WHERE子句中的数据列。

  6. 经常出现在关键字order by、group by、distinct后面的字段，建立索引。如果建立的是复合索引，索引的字段顺序要
    和这些关键字后面的字段顺序一致，否则索引不会被使用。

  7. 对于那些查询中很少涉及的列，重复值比较多的列不要建立索引。

  8. 对于定义为text、image和bit的数据类型的列不要建立索引。

  9. 对于经常存取的列避免建立索引

  10. 限制表上的索引数目。对一个存在大量更新操作的表，所建索引的数目一般不要超过3个，最多不要超过5个。索引虽说提高了
    访问速度，但太多索引会影响数据的更新操作。

  11. 对复合索引，按照字段在查询条件中出现的频度建立索引。在复合索引中，记录首先按照第一个字段排序。对于在第一个字段
    上取值相同的记录，系统再按照第二个字段的取值排序，

以此类推。因此只有复合索引的第一个字段出现在查询条件中，该索引才可能被使用,因此将应用频度高的字段，放置在复合索引的前面，
会使系统最大可能地使用此索引，发挥索引的作用。

创建索引

创建索引有两种方式，一种是直接利用CREATE INDEX进行创建，另外一种则是通过修改表结构来进行添加，则是利用ALTER TABLE语句。

方式一：CREATE INDEX

  • 语法格式
1
2
3
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name
[USING index_type]
ON table_name (index_col_name,...)

  • 索引类型

    • UNIQUE：唯一索引
    • FULLTEXT：全文索引
    • SPATIAL：空间索引
  • index_type

索引的具体实现方式，在MySQL中，有两种不同形式的索引——BTREE索引和HASH索引。在存储引擎为MyISAM和InnoDB的表中只能使用BTREE，其默认值就是BTREE；在存储引擎为MEMORY或者HEAP的表中可以使用HASH和BTREE两种类型的索引，其默认值为HASH。

  • index_colname

创建索引的字段名称，我们还可以针对多个字段创建复合索引，只需要在多个字段名称之间以英文逗号隔开即可。

此外，对于CHAR或VARCHAR类型的字段，我们还可以只使用字段内容前面的一部分来创建索引，只需要在对应的字段名称后面加上形如(length)的指令即可，表示只需要使用字段内容前面的length个字符来创建索引。在这里，我们以customers表的cust_name字段(类型为VARCHAR(50))为例，使用cust_name字段的6个字符前缀来创建索引。

  • 复合索引

如果我们的查询where条件只有一个，我们完全可以用单列索引，这样的查询速度较快，索引也比较瘦身。

如果我们的业务场景是需要经常查询多个组合列，不要试图分别基于单个列建立多个单列索引。

因为虽然有多个单列索引，但是MySQL只能用到其中的那个它认为似乎最有效率的单列索引

举例说明，为firstname、lastname和age创建复合索引

如果要根据这三个索引进行查找，由于索引文件以B+树格式保存，MySQL能够立即转到合适的firstname，然后再转到合适的lastname，最后转到合适的age。

在没有扫描数据文件任何一个记录的情况下，MySQL就正确地找出了搜索的目标记录!

那么，如果在firstname、lastname、age这三个列上分别创建单列索引，效果是否和创建一个firstname、lastname、age的多列索引一样呢？

答案是否定的，两者完全不同。当我们执行查询的时候，MySQL只能使用一个索引。如果你有三个单列的索引，MySQL会试图选择一个限制最严格的索引。但是，即使是限制最严格的单列索引，它的限制能力也肯定远远低于firstname、lastname、age这三个列上的多列索引。

参考链接：https://blog.csdn.net/xtdhqdhq/article/details/17582779

方式二：ALTER TABLE

  • 语法格式
1
2
3
ALTER TABLE table_name
ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX index_name 
(index_col_name,...) [USING index_type]

示例

  • 创建简单索引

在表上创建一个简单的索引。允许使用重复的值，”column_name” 规定需要索引的列。

1
2
CREATE INDEX index_name
ON table_name (column_name)
  • 唯一索引

唯一的索引意味着两个行不能拥有相同的索引值。

1
2
CREATE UNIQUE INDEX index_name
ON table_name (column_name)

删除索引

  • 语法格式
1
2
ALTER TABLE table_name
DROP INDEX index_name;

示例

  • 删除索引
1
2
ALTER TABLE customers
DROP INDEX idx_cust_name;

修改索引

在MySQL中并没有提供修改索引的直接指令，一般情况下，我们需要先删除掉原索引，再根据需要创建一个同名的索引，从而变相地实现修改索引操作。

示例

1
2
3
4
5
-先删除
ALTER TABLE user
DROP INDEX idx_user_username;
--再以修改后的内容创建同名索引
CREATE INDEX idx_cust_name ON customers (cust_name(8));

查看索引

  • 语法格式
1
2
3
4
5
--如果查看索引前，没有使用user db_name等命令指定具体的数据库，则必须加上FROM db_name
SHOW INDEX FROM table_name [FROM db_name]

--如果查看索引前，没有使用user db_name等命令指定具体的数据库，则必须加上db_name.前缀
SHOW INDEX FROM [db_name.]table_name

字段解释

  • Non_unique：如果索引不能包括重复词，则为0。如果可以，则为1。
  • Key_name：索引的名称。
  • Seq_in_index：索引中的列序列号，从1开始。
  • Column_name
  • Collation：列以什么方式存储在索引中。在MySQL中，有值‘A’（升序）或NULL（无分类）
  • Cardinality：索引中唯一值的数目的估计值。通过运行ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数，
    所以即使对于小型表，该值也没有必要是精确的。基数越大，当进行联合时，MySQL使用该索引的机会就越大。
  • Sub_part：如果列只是被部分地编入索引，则为被编入索引的字符的数目。如果整列被编入索引，则为NULL。
  • Packed：指示关键字如何被压缩。如果没有被压缩，则为NULL。
  • Null：如果列含有NULL，则含有YES。如果没有，则该列含有NO。
  • Index_type：用过的索引方法（BTREE, FULLTEXT, HASH, RTREE）。

加锁语句

http://www.fordba.com/locks-set-by-different-sql-statements-in-innodb.html

举例1

1
2
3
4
5
6
7
CREATE TABLE `test` (
  `id` int(11) NOT NULL DEFAULT '0',
  `name` varchar(10) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8

select * from test where id > 3 and name <'A' for update;

这条SQL语句的会将所有id>3的记录进行加锁，而不是id>3 and name <’A’ 进行加锁，因为name上面没有索引。

SQL分析

  1. SELECT … FROM 是一个快照读，通过读取数据库的一个快照，不会加任何锁，除非将隔离级别设置成了 SERIALIZABLE 。在 SERIALIZABLE 隔离级别下，
    如果索引是非唯一索引，那么将在相应的记录上加上一个共享的next key锁。如果是唯一索引，只需要在相应记录上加index record lock。

  2. SELECT … FROM … LOCK IN SHARE MODE 语句在所有索引扫描范围的索引记录上加上共享的next key锁。如果是唯一索引，只需要在相应记录上加index record lock。

  3. SELECT … FROM … FOR UPDATE 语句在所有索引扫描范围的索引记录上加上排他的next key锁。如果是唯一索引，只需要在相应记录上加index record lock。
    这将堵塞其他会话利用SELECT … FROM … LOCK IN SHARE MODE 读取相同的记录，但是快照读将忽略记录上的锁。

  4. UPDATE … WHERE …语句在所有索引扫描范围的索引记录上加上排他的next key锁。如果是唯一索引，只需要在相应记录上加index record lock。

当UPDATE 操作修改主键记录的时候，将在相应的二级索引上加上隐式的锁。当进行重复键检测的时候，将会在插入新的二级索引记录之前，在其二级索引上加上一把共享锁。

  1. DELETE FROM … WHERE … 语句在所有索引扫描范围的索引记录上加上排他的next key锁。如果是唯一索引，只需要在相应记录上加index record lock。

  2. INSERT 语句将在插入的记录上加一把排他锁，这个锁是一个index-record lock，并不是next-key 锁，因此就没有gap锁，他将不会阻止其他会话在该条记录之前的gap插入记录。

索引分类

  • 主键

自带最高效的索引属性

  • 唯一索引

该属性值重复率为0，一般可作为业务主键

  • 普通索引

属性值的重复率大于0，不能作为唯一指定条件

普通索引对并发的影响

mysql 5.6在 update 和 delete 的时候，where 条件如果不存在索引字段，那么这个事务是否会导致表锁？

针对 “普通索引是表锁” 进行了验证，结果发现普通索引并不一定会引发表锁，在普通索引中，是否引发表锁取决于普通索引的高效程度。

https://wing324.github.io/2017/03/01/SELECT-%E2%80%A6-FOR-UPDATE%E5%A6%82%E4%BD%95%E5%BD%B1%E5%93%8DINNODB%E7%9A%84%E9%94%81%E7%BA%A7%E5%88%AB/

小结：

  • WHERE条件使用主键，SELECT … FOR UPDATE为行级锁；
  • WHERE条件使用唯一索引，SELECT … FOR UPDATE为行级锁；
  • WHERE条件使用普通索引，SELECT … FOR UPDATE为行级锁；
  • WHERE条件使用联合索引的前缀索引，SELECT … FOR UPDATE为行级锁；
  • WHERE条件不使用索引，SELECT … FOR UPDATE为表级锁；

即：

  • WHERE条件能使用索引时，SELECT … FOR UPDATE表现为行级锁；
  • WHERE条件不使用索引，SELECT … FOR UPDATE表现为表级锁；