MYSQL---索引问题汇总

1、什么是索引？

数据库的索引类似于字典的索引，可以按照偏旁、拼音、首字母进行查询，实现快速查询的目的。如果不加索引的话，那么查找任何哪怕只是一条特定的数据都会进行一次全表扫描，如果一张表的数据量很大而符合条件的结果又很少，那么不加索引会引起致命的性能下降。

2、在什么情况下创建索引？

表的主关键字
　　自动建立唯一索引
　　　　如zl_yhjbqk（用户基本情况）中的hbs_bh（户标识编号）
表的字段唯一约束
　　　　ORACLE利用索引来保证数据的完整性
　　　　如lc_hj（流程环节）中的lc_bh+hj_sx（流程编号+环节顺序）
直接条件查询的字段
　　在SQL中用于条件约束的字段
　　　　如zl_yhjbqk（用户基本情况）中的qc_bh（区册编号）
　　　　select * from zl_yhjbqk where qc_bh=’7001’
查询中与其它表关联的字段
　　字段常常建立了外键关系
　　　　如zl_ydcf（用电成份）中的jldb_bh（计量点表编号）
　　　　select * from zl_ydcf a,zl_yhdb b where a.jldb_bh=b.jldb_bh and b.jldb_bh=’540100214511’
查询中排序的字段
　　排序的字段如果通过索引去访问那将大大提高排序速度
　　　　select * from zl_yhjbqk order by qc_bh（建立qc_bh索引）
　　　　select * from zl_yhjbqk where qc_bh=’7001’ order by cb_sx（建立qc_bh+cb_sx索引， 注：只是一个索引，其中包括qc_bh和cb_sx字段）
查询中统计或分组统计的字段
　　　　select max(hbs_bh) from zl_yhjbqk
　　　　select qc_bh,count(*) from zl_yhjbqk group by qc_bh

3、什么情况下不使用或者少使用索引？

表记录太少
　　如果一个表只有5条记录，采用索引去访问记录的话，那首先需访问索引表，再通过索引表访问数据表，一般索引表与数据表不在同一个数据块，这种情况下ORACLE至少要往返读取数据块两次。而不用索引的情况下ORACLE会将所有的数据一次读出，处理速度显然会比用索引快。
　　如表zl_sybm（使用部门）一般只有几条记录，除了主关键字外对任何一个字段建索引都不会产生性能优化，实际上如果对这个表进行了统计分析后ORACLE也不会用你建的索引，而是自动执行全表访问。如：select * from zl_sybm where sydw_bh=’5401’（对sydw_bh建立索引不会产生性能优化）
　　
经常插入、删除、修改的表
　　对一些经常处理的业务表应在查询允许的情况下尽量减少索引，如zl_yhbm，gc_dfss，gc_dfys，gc_fpdy等业务表。
　　
数据重复且分布平均的表字段
　　假如一个表有10万行记录，有一个字段A只有T和F两种值，且每个值的分布概率大约为50%，那么对这种表A字段建索引一般不会提高数据库的查询速度。
　　
经常和主字段一块查询但主字段索引值比较多的表字段
　　如gc_dfss（电费实收）表经常按收费序号、户标识编号、抄表日期、电费发生年月、操作 标志来具体查询某一笔收款的情况，如果将所有的字段都建在一个索引里那将会增加数据的修改、插入、删除时间，从实际上分析一笔收款如果按收费序号索引就已 经将记录减少到只有几条，如果再按后面的几个字段索引查询将对性能不产生太大的影响。

4、如何添加索引和删除索引？

可以在创建表的时候创建索引
create table test(
id int(11) auto_increment primary key not null,
mane varchar(50),
UNIQUE INDEX inde(id));
创建表的时候创建了唯一索引，名字叫inde,索引以id列为主。

可以在表生成后再添加索引
create UNIQUE INDEX inde on table_name(id);
实现了在表名为table_name的表中创建了唯一索引，索引名称为inde,索引以id列为主.

可以通过修改数据表结构添加索引
ALTER TABLE table_name ADD UNIQUE INDEX inde(id);
在表名为table_name的表中创建了唯一索引，索引名称为inde,索引以id列为主.

删除索引
DROP INDEX inde on table_name;

5、索引的类型

普通索引 添加INDEX
　　　　ALTER TABLE ‘table_name’ ADD INDEX index_name (‘column’);
主键索引 添加PRIMARY KEY
　　　　ALTER TABLE ‘table_name’ ADD PRIMARY KEY (‘column’);
唯一索引 添加UNIQUE
　　　　ALTER TABLE ‘table_name’ ADD UNIQUE (‘column’);
全文索引 添加FULLTEXT
　　　　ALTER TABLE ‘table_name’ ADD FULLTEXT (‘column’);
多列索引
　　　　ALTER TABLE ‘table_name’ ADD INDEX index_name (‘column1’, ‘column2’, ‘column3’)

6、数据库底层索引结构使用哪几种结构实现的？

　索引类型: B-TREE索引，哈希索引
　
　　B-TREE索引(默认的索引类型)加速了数据访问，因为存储引擎不会扫描整个表得到需要的数据。相反，它从根节点开始。根节点保存了指向子节点的指针，并且存储引擎会根据指针寻找数据。它通过查找节点页中的值找到正确的指针，节点页包含子节点的指针，并且存储引擎会根据指针寻找数据。它通过查找节点页中的值找到正确的指针，节点页包含子节点中值的上界和下界。最后，存储引擎可能无法找到需要的数据，也可能成功地找到包含数据的叶子页面。
　　例：B-TREE索引 对于以下类型查询有用。匹配全名、匹配最左前缀、匹配列前缀、匹配范围值、精确匹配一部分并且匹配某个范围中的另一部分；
　　B-TREE索引的局限：如果查找没有从索引列的最左边开始，它就没什么用处。不能跳过索引中的列，存储引擎不能优先访问任何在第一个范围条件右边的列。例：如果查询是where last_name=’Smith’ AND first_name LIKE ‘J%’ AND dob=’1976-12-23’;访问就只能使用索引的头两列，因为LIKE是范围条件。
　　哈希索引建立在哈希表的基础上，它只对使用了索引中的每一列的精确查找有用。对于每一行，存储引擎计算出了被索引列的哈希码，它是一个较小的值，并且有可能和其他行的哈希码不同。它把哈希码保存在索引中，并且保存了一个指向哈希表中每一行的指针。
　　因为索引只包含了哈希码和行指针，而不是值自身，MYSQL不能使用索引中的值来避免读取行。
　　MYSQL不能使用哈希索引进行排序，因为它们不会按序保存行。
　　哈希索引不支持部分键匹配，因为它们是由被索引的全部值计算出来的。也就是说，如果在（A，B）两列上有索引，并且WHERE子句中只使用了A，那么索引就不会起作用。
　　哈希索引只支持使用了= IN（）和<=>的相等比较。它们不能加快范围查询。例如WHERE price > 100;
　　访问哈希索引中的数据非常快，除非碰撞率很高。当发生碰撞的时候，存储引擎必须访问链表中的每一个行指针，然后逐行进行数据比较，以确定正确的数据。如果有很多碰撞，一些索引维护操作就有可能会变慢。

7、MySQL数据库建立索引的事项

1，创建索引
　　对于查询占主要的应用来说，索引显得尤为重要。很多时候性能问题很简单的就是因为我们忘了添加索引而造成的，或者说没有添加更为有效的索引导致。如果不加索引的话，那么查找任何哪怕只是一条特定的数据都会进行一次全表扫描，如果一张表的数据量很大而符合条件的结果又很少，那么不加索引会引起致命的性能下降。但是也不是什么情况都非得建索引不可，比如性别可能就只有两个值，建索引不仅没什么优势，还会影响到更新速度，这被称为过度索引。

2，复合索引
　　比如有一条语句是这样的：select * from users where area=’beijing’ and age=22;
　　如果我们是在area和age上分别创建单个索引的话，由于mysql查询每次只能使用一个索引，所以虽然这样已经相对不做索引时全表扫描提高了很多效率，但是如果在area、age两列上创建复合索引的话将带来更高的效率。如果我们创建了(area, age,salary)的复合索引，那么其实相当于创建了(area,age,salary)、(area,age)、(area)三个索引，这被称为最佳左前缀特性。因此我们在创建复合索引时应该将最常用作限制条件的列放在最左边，依次递减。

3，索引不会包含有NULL值的列
　　只要列中包含有NULL值都将不会被包含在索引中，复合索引中只要有一列含有NULL值，那么这一列对于此复合索引就是无效的。所以我们在数据库设计时不要让字段的默认值为NULL。

4，使用短索引
　　对串列进行索引，如果可能应该指定一个前缀长度。例如，如果有一个CHAR(255)的 列，如果在前10 个或20 个字符内，多数值是惟一的，那么就不要对整个列进行索引。短索引不仅可以提高查询速度而且可以节省磁盘空间和I/O操作。

5，排序的索引问题
　　mysql查询只使用一个索引，因此如果where子句中已经使用了索引的话，那么order by中的列是不会使用索引的。因此数据库默认排序可以符合要求的情况下不要使用排序操作；尽量不要包含多个列的排序，如果需要最好给这些列创建复合索引。

6，like语句操作
　　一般情况下不鼓励使用like操作，如果非使用不可，如何使用也是一个问题。like “%aaa%” 不会使用索引而like “aaa%”可以使用索引。

7，不要在列上进行运算
　　select * from users where YEAR(adddate)

8，不使用NOT IN和操作
　　NOT IN和操作都不会使用索引将进行全表扫描。NOT IN可以NOT EXISTS代替，id != 3则可使用id>3 or id < 3