Question

数据库索引有什么作用和好处？

Answer 1

数据库索引是为了增加查询速度而对表字段附加的一种标识。见过很多人机械的理解索引的概念，认为增加索引只有好处没有坏处。这里想把之前的索引学习笔记总结一下： 首先明白为什么索引会增加速度，DB在执行一条Sql语句的时候，默认的方式是根据搜索条件进行全表扫描，遇到匹配条件的就加入搜索结果集合。如果我们对某一字段增加索引，查询时就会先去索引列表中一次定位到特定值的行数，大大减少遍历匹配的行数，所以能明显增加查询的速度。那么在任何时候都应该加索引么？这里有几个反例：1、如果每次都需要取到所有表记录，无论如何都必须进行全表扫描了，那么是否加索引也没有意义了。2、对非唯一的字段，例如“性别”这种大量重复值的字段，增加索引也没有什么意义。3、对于记录比较少的表，增加索引不会带来速度的优化反而浪费了存储空间，因为索引是需要存储空间的，而且有个致命缺点是对于update/insert/delete的每次执行，字段的索引都必须重新计算更新。 那么在什么时候适合加上索引呢？我们看一个Mysql手册中举的例子，这里有一条sql语句： SELECT c.companyID, c.companyName FROM Companies c, User u WHERE c.companyID = u.fk_companyID AND c.numEmployees >= 0 AND c.companyName LIKE '%i%' AND u.groupID IN (SELECT g.groupID FROM Groups g WHERE g.groupLabel = 'Executive') 这条语句涉及3个表的联接，并且包括了许多搜索条件比如大小比较，Like匹配等。在没有索引的情况下Mysql需要执行的扫描行数是77721876行。而我们通过在companyID和groupLabel两个字段上加上索引之后，扫描的行数只需要134行。在Mysql中可以通过Explain Select来查看扫描次数。可以看出来在这种联表和复杂搜索条件的情况下，索引带来的性能提升远比它所占据的磁盘空间要重要得多。 那么索引是如何实现的呢？大多数DB厂商实现索引都是基于一种数据结构——B树。因为B树的特点就是适合在磁盘等直接存储设备上组织动态查找表。B树的定义是这样的：一棵m(m>=3)阶的B树是满足下列条件的m叉树： 1、每个结点包括如下作用域(j, p0, k1, p1, k2, p2, ... ki, pi) 其中j是关键字个数，p是孩子指针 2、所有叶子结点在同一层上，层数等于树高h 3、每个非根结点包含的关键字个数满足[m/2-1]<=j<=m-1 4、若树非空，则根至少有1个关键字，若根非叶子，则至少有2棵子树，至多有m棵子树 看一个B树的例子，针对26个英文字母的B树可以这样构造： 可以看到在这棵B树搜索英文字母复杂度只为o(m)，在数据量比较大的情况下，这样的结构可以大大增加查询速度。然而有另外一种数据结构查询的虚度比B树更快——散列表。Hash表的定义是这样的：设所有可能出现的关键字集合为u，实际发生存储的关键字记为k，而|k|比|u|小很多。散列方法是通过散列函数h将u映射到表T[0,m-1]的下标上，这样u中的关键字为变量，以h为函数运算结果即为相应结点的存储地址。从而达到可以在o(1)的时间内完成查找。
然而散列表有一个缺陷，那就是散列冲突，即两个关键字通过散列函数计算出了相同的结果。设m和n分别表示散列表的长度和填满的结点数，n/m为散列表的填装因子，因子越大，表示散列冲突的机会越大。
因为有这样的缺陷，所以数据库不会使用散列表来做为索引的默认实现，Mysql宣称会根据执行查询格式尝试将基于磁盘的B树索引转变为和合适的散列索引以追求进一步提高搜索速度。我想其它数据库厂商也会有类似的策略，毕竟在数据库战场上，搜索速度和管理安全一样是非常重要的竞争点。

Answer 2

数据库小的话体现不出来，数据库大的话就体现的很优越了我有一个测试软件，可以让你看看索引的好处。不加索引的查找时间是100ms，加了之后是50ms。

Answer 3

你觉得字典的索引有啥用，数据库的索引就有啥用。

Answer 4

容易查询, 速度快快, 容量大.以mysql为例子。 表结构:
mysql> desc demo;
+-------+-------------+------+-----+---------+-------+
| Field | Type | Null | Key | Default | Extra |
+-------+-------------+------+-----+---------+-------+
| id | int(11) | NO | PRI | | |
| name | varchar(10) | YES | MUL | NULL | |
+-------+-------------+------+-----+---------+-------+

索引状况:
show index in demo;
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+- --------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+- --------+
| demo | 0 | PRIMARY | 1 | id | A | 4 | NULL | NULL | | BTREE | |
+-------+------------+----------+--------------+-------------+-----------+-------------+----------+--------+------+------------+- --------+

查询计划:
explain select * from demo where name like '李%';
+----+-------------+-------+------+---------------+------+---------+------+- -----+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+------+---------------+------+---------+------+- -----+-------------+
| 1 | SIMPLE | demo | ALL | NULL | NULL | NULL | NULL | 4 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+- -----+-------------+
注意到没有使用任何索引.

添加索引:
create index name_index_demo on demo;

现在的索引状况:
show index in demo;
+-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment |
+-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+
| demo | 0 | PRIMARY | 1 | id | A | 4 | NULL | NULL | | BTREE | |
| demo | 1 | name_index_demo | 1 | name | A | NULL | NULL | NULL | YES | BTREE | |
+-------+------------+-----------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+

新的查询的查询计划:
explain select * from demo where name like '李%';
+----+-------------+-------+-------+-----------------+-----------------+---------+------+------+-------------+
| id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra |
+----+-------------+-------+-------+-----------------+-----------------+---------+------+------+-------------+
| 1 | SIMPLE | demo | range | name_index_demo | name_index_demo | 13 | NULL | 1 | Using where |
+----+-------------+-------+-------+-----------------+-----------------+---------+------+------+-------------+
1 row in set (0.03 sec)

注意到查询已经使用了我们创建的索引.