什么是索引：
索引(Index)是帮助MySQL高效获取数据的数据结构~
索引的目的在于提高查询效率，可以类比字典~
在数据之外，数据库系统还维护着满足特定查找算法的数据结构(BTREE)，这些数据结构以某种方式指向数据~
这样就可以在这些数据结构上实现快速查找，这种数据结构就是索引。下图就是一种可能的索引方式示例：
左边是数据表，一共有两列七条数据，最左边是数据记录的物理地址~
就像我想找Col2=89的记录，比较一次就找到了，如果全表扫描的话就会很慢~
为了加快Col2的查找，可以维护一个右边所示的二叉查找树，每个节点分别包含索引键值和一个指向对应数据记录的物理地址的指针，这样就可以运用二叉查找在一定的复杂度内获取到相应的数据。从而快速的检索出符合条件的记录。
一般来说索引本身也很大,不可能全部存储在内存中，因此索引往往以索引文件的形式存储在磁盘上。
我们平常所说的索引，如果没有特别指明，都是指B树(多路搜索树，不一定是二叉树)结构组织的索引。其中聚集索引，次要索引，覆盖索引，复合索引，前缀索引，唯一索引
默认都是使用B+树索引，统称索引。当然除了B+树这种类型的索引之外还有哈希索引等。
索引的好处：
第一，通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
第二，可以大大加快数据的检索速度，这也是创建索引的最主要的原因。
第三，可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
第四，在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
第五，通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。
也许会有人要问：增加索引有如此多的优点，为什么不对表中的每一个列创建一个索引呢？因为，增加索引也有许多不利的方面。
索引带来的问题：
第一，创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
第二，索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
第三，当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。
索引是建立在数据库表中的某些列的上面。在创建索引的时候，应该考虑在哪些列上可以创建索引，在哪些列上不能创建索引。
一般来说，应该在这些列上创建索引：
1.主键自动建立唯一索引
2.频繁作为查询条件的字段应该建立索引，可以加快搜索的速度
3.在经常用在连接的列上，这些列主要是一些外键，可以加快连接的速度；
4.在经常需要根据范围进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的；
5.在经常需要排序的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
同样，对于有些列不应该创建索引。一般来说，不应该创建索引的的这些列具有下列特点：
1，对于那些在查询中很少使用或者参考的列不应该创建索引。这是因为，既然这些列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
2，对于那些只有很少数据值的列也不应该增加索引。这是因为，由于这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
3，对于那些定义为text, image和bit数据类型的列不应该增加索引。这是因为，这些列的数据量要么相当大，要么取值很少。
4，当修改性能远远大于检索性能时，不应该创建索引。这是因为，修改性能和检索性能是互相矛盾的。当增加索引时，会提高检索性能，但是会降低修改性能。当减少索引时，会提高修改性能，降低检索性能。因此，当修改性能远远大于检索性能时，不应该创建索引。
1.应该避免的索引失效的情况
CREATE TABLE staffs( id INT(10) PRIMARY KEY AUTO_INCREMENT, NAME VARCHAR(24) NOT NULL DEFAULT '' COMMENT '姓名', age INT(10) NOT NULL DEFAULT 0 COMMENT '年龄', pos VARCHAR(24) NOT NULL DEFAULT '' COMMENT '职位', add_time TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP COMMENT '入职时间' )CHARSET utf8 COMMENT '员工记录表'; INSERT INTO staffs(NAME,age,pos,add_time) VALUES('z3',22,'manager',NOW()); INSERT INTO staffs(NAME,age,pos,add_time) VALUES('July',23,'dev',NOW()); INSERT INTO staffs(NAME,age,pos,add_time) VALUES('2000',23,'dev',NOW()); ALTER TABLE staffs ADD INDEX idx_staffs_nameAgePos(NAME,age,pos);
mysql> show index from staffs; +--------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | Table | Non_unique | Key_name | Seq_in_index | Column_name | Collation | Cardinality | Sub_part | Packed | Null | Index_type | Comment | Index_comment | +--------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ | staffs | 0 | PRIMARY | 1 | id | A | 3 | NULL | NULL | | BTREE | | | | staffs | 1 | idx_staffs_nameAgePos | 1 | NAME | A | 3 | NULL | NULL | | BTREE | | | | staffs | 1 | idx_staffs_nameAgePos | 2 | age | A | 3 | NULL | NULL | | BTREE | | | | staffs | 1 | idx_staffs_nameAgePos | 3 | pos | A | 3 | NULL | NULL | | BTREE | | | +--------+------------+-----------------------+--------------+-------------+-----------+-------------+----------+--------+------+------------+---------+---------------+ 4 rows in set (0.00 sec)
规则1：全值匹配我最爱
mysql> explain select * from staffs where name='July'; +----+-------------+--------+------+-----------------------+-----------------------+---------+-------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+-----------------------+-----------------------+---------+-------+------+-----------------------+ | 1 | SIMPLE | staffs | ref | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 74 | const | 1 | Using index condition | +----+-------------+--------+------+-----------------------+-----------------------+---------+-------+------+-----------------------+ 1 row in set (0.01 sec) mysql> explain select * from staffs where name='July' and age=23; +----+-------------+--------+------+-----------------------+-----------------------+---------+-------------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+-----------------------+-----------------------+---------+-------------+------+-----------------------+ | 1 | SIMPLE | staffs | ref | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 78 | const,const | 1 | Using index condition | +----+-------------+--------+------+-----------------------+-----------------------+---------+-------------+------+-----------------------+ 1 row in set (0.00 sec) mysql> explain select * from staffs where name='July' and age=23 and pos='dev'; +----+-------------+--------+------+-----------------------+-----------------------+---------+-------------------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+-----------------------+-----------------------+---------+-------------------+------+-----------------------+ | 1 | SIMPLE | staffs | ref | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 152 | const,const,const | 1 | Using index condition | +----+-------------+--------+------+-----------------------+-----------------------+---------+-------------------+------+-----------------------+ 1 row in set (0.00 sec)
第三种效果最好~ 用到了我们创建的索引，下面再看一种情况
mysql> explain select * from staffs where age=23 and pos='dev'; +----+-------------+--------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | staffs | ALL | NULL | NULL | NULL | NULL | 3 | Using where | +----+-------------+--------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.01 sec) mysql> explain select * from staffs where pos='dev'; +----+-------------+--------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | staffs | ALL | NULL | NULL | NULL | NULL | 3 | Using where | +----+-------------+--------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec)
全表扫描！！我们看到，索引竟然失效了
mysql> explain select * from staffs where name='July'; +----+-------------+--------+------+-----------------------+-----------------------+---------+-------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+-----------------------+-----------------------+---------+-------+------+-----------------------+ | 1 | SIMPLE | staffs | ref | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 74 | const | 1 | Using index condition | +----+-------------+--------+------+-----------------------+-----------------------+---------+-------+------+-----------------------+ 1 row in set (0.00 sec)
单独使用name就不会使索引失效，由此引入规则2
规则2：最佳左前缀法则，即如果索引了多列，要遵守最佳左前缀法则，即查询必须从索引的最左前列开始，并且不跳过索引中的列~
ALTER TABLE staffs ADD INDEX idx_staffs_nameAgePos(name,age,pos);
索引的最左前列是name,name相当于火车头，没有火车头，搞毛~所以索引的第一个字段不能丢失
再来看一种情况
mysql> explain select * from staffs where name='July' and pos='dev'; +----+-------------+--------+------+-----------------------+-----------------------+---------+-------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+-----------------------+-----------------------+---------+-------+------+-----------------------+ | 1 | SIMPLE | staffs | ref | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 74 | const | 1 | Using index condition | +----+-------------+--------+------+-----------------------+-----------------------+---------+-------+------+-----------------------+ 1 row in set (0.00 sec)
只有一个const,这和只用name是一样的，违背了什么原则呢？不跳过索引中的列
mysql> explain select * from staffs where name='July' and age=23; +----+-------------+--------+------+-----------------------+-----------------------+---------+-------------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+-----------------------+-----------------------+---------+-------------+------+-----------------------+ | 1 | SIMPLE | staffs | ref | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 78 | const,const | 1 | Using index condition | +----+-------------+--------+------+-----------------------+-----------------------+---------+-------------+------+-----------------------+ 1 row in set (0.00 sec)
以上两个对比一下即可~
规则3.不要在索引列上做任何操作(计算，函数，类型转换)，会导致索引失效而转向全表扫描~
mysql> select * from staffs where name='July'; +----+------+-----+-----+---------------------+ | id | NAME | age | pos | add_time | +----+------+-----+-----+---------------------+ | 2 | July | 23 | dev | 2017-03-17 23:14:23 | +----+------+-----+-----+---------------------+ 1 row in set (0.00 sec) mysql> select * from staffs where left(name,4)='July'; +----+------+-----+-----+---------------------+ | id | NAME | age | pos | add_time | +----+------+-----+-----+---------------------+ | 2 | July | 23 | dev | 2017-03-17 23:14:23 | +----+------+-----+-----+---------------------+ 1 row in set (0.04 sec) mysql> explain select * from staffs where name='July'; +----+-------------+--------+------+-----------------------+-----------------------+---------+-------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+-----------------------+-----------------------+---------+-------+------+-----------------------+ | 1 | SIMPLE | staffs | ref | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 74 | const | 1 | Using index condition | +----+-------------+--------+------+-----------------------+-----------------------+---------+-------+------+-----------------------+ 1 row in set (0.00 sec) mysql> explain select * from staffs where left(name,4)='July'; +----+-------------+--------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | staffs | ALL | NULL | NULL | NULL | NULL | 3 | Using where | +----+-------------+--------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec)
规则4.存储引擎不能使用索引中范围条件右边的列
mysql> explain select * from staffs where name='July' and age=23 and pos='dev'; +----+-------------+--------+------+-----------------------+-----------------------+---------+-------------------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+-----------------------+-----------------------+---------+-------------------+------+-----------------------+ | 1 | SIMPLE | staffs | ref | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 152 | const,const,const | 1 | Using index condition | +----+-------------+--------+------+-----------------------+-----------------------+---------+-------------------+------+-----------------------+ 1 row in set (0.00 sec) mysql> explain select * from staffs where name='July' and age>23 and pos='dev'; +----+-------------+--------+-------+-----------------------+-----------------------+---------+------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+-----------------------+-----------------------+---------+------+------+-----------------------+ | 1 | SIMPLE | staffs | range | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 78 | NULL | 1 | Using index condition | +----+-------------+--------+-------+-----------------------+-----------------------+---------+------+------+-----------------------+ 1 row in set (0.00 sec)
age>23导致后面的pos索引失效
规则5.尽量使用覆盖索引(只访问索引的查询(索引列和查询列一致))，减少select *
mysql> explain select * from staffs where name='July' and age=23 and pos='dev'; +----+-------------+--------+------+-----------------------+-----------------------+---------+-------------------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+-----------------------+-----------------------+---------+-------------------+------+-----------------------+ | 1 | SIMPLE | staffs | ref | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 152 | const,const,const | 1 | Using index condition | +----+-------------+--------+------+-----------------------+-----------------------+---------+-------------------+------+-----------------------+ 1 row in set (0.00 sec) mysql> explain select name,age,pos from staffs where name='July' and age=23 and pos='dev'; +----+-------------+--------+------+-----------------------+-----------------------+---------+-------------------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+-----------------------+-----------------------+---------+-------------------+------+--------------------------+ | 1 | SIMPLE | staffs | ref | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 152 | const,const,const | 1 | Using where; Using index | +----+-------------+--------+------+-----------------------+-----------------------+---------+-------------------+------+--------------------------+ 1 row in set (0.00 sec) mysql> explain select name,age,pos from staffs where name='July' and age>23 and pos='dev'; +----+-------------+--------+------+-----------------------+-----------------------+---------+-------+------+--------------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+-----------------------+-----------------------+---------+-------+------+--------------------------+ | 1 | SIMPLE | staffs | ref | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 74 | const | 1 | Using where; Using index | +----+-------------+--------+------+-----------------------+-----------------------+---------+-------+------+--------------------------+ 1 row in set (0.00 sec) mysql> explain select * from staffs where name='July' and age>23 and pos='dev'; +----+-------------+--------+-------+-----------------------+-----------------------+---------+------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+-----------------------+-----------------------+---------+------+------+-----------------------+ | 1 | SIMPLE | staffs | range | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 78 | NULL | 1 | Using index condition | +----+-------------+--------+-------+-----------------------+-----------------------+---------+------+------+-----------------------+ 1 row in set (0.00 sec)
规则6.MySQL中在使用不等于(!=或者<>)的时候无法使用索引导致全表扫描
mysql> explain select * from staffs where name='July'; +----+-------------+--------+------+-----------------------+-----------------------+---------+-------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+-----------------------+-----------------------+---------+-------+------+-----------------------+ | 1 | SIMPLE | staffs | ref | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 74 | const | 1 | Using index condition | +----+-------------+--------+------+-----------------------+-----------------------+---------+-------+------+-----------------------+ 1 row in set (0.00 sec) mysql> explain select * from staffs where name!='July'; +----+-------------+--------+------+-----------------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+-----------------------+------+---------+------+------+-------------+ | 1 | SIMPLE | staffs | ALL | idx_staffs_nameAgePos | NULL | NULL | NULL | 3 | Using where | +----+-------------+--------+------+-----------------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec) mysql> explain select * from staffs where name<>'July'; +----+-------------+--------+------+-----------------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+-----------------------+------+---------+------+------+-------------+ | 1 | SIMPLE | staffs | ALL | idx_staffs_nameAgePos | NULL | NULL | NULL | 3 | Using where | +----+-------------+--------+------+-----------------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec)
规则7.is null,is not null也无法使用索引
mysql> explain select * from staffs where name is null; +----+-------------+-------+------+---------------+------+---------+------+------+------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+-------+------+---------------+------+---------+------+------+------------------+ | 1 | SIMPLE | NULL | NULL | NULL | NULL | NULL | NULL | NULL | Impossible WHERE | +----+-------------+-------+------+---------------+------+---------+------+------+------------------+ 1 row in set (0.00 sec) mysql> explain select * from staffs where name is not null; +----+-------------+--------+------+-----------------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+-----------------------+------+---------+------+------+-------------+ | 1 | SIMPLE | staffs | ALL | idx_staffs_nameAgePos | NULL | NULL | NULL | 3 | Using where | +----+-------------+--------+------+-----------------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec)
所以建表的时候字段不能为null
规则8.like以通配符开头('%abc')mysql索引失效会变成全表扫描的操作~
mysql> explain select * from staffs where name like '%July%'; +----+-------------+--------+------+---------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+---------------+------+---------+------+------+-------------+ | 1 | SIMPLE | staffs | ALL | NULL | NULL | NULL | NULL | 3 | Using where | +----+-------------+--------+------+---------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec)
mysql> explain select * from staffs where name like 'July%'; +----+-------------+--------+-------+-----------------------+-----------------------+---------+------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+-------+-----------------------+-----------------------+---------+------+------+-----------------------+ | 1 | SIMPLE | staffs | range | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 74 | NULL | 1 | Using index condition | +----+-------------+--------+-------+-----------------------+-----------------------+---------+------+------+-----------------------+ 1 row in set (0.00 sec)
问题：解决like%字符串%时索引不被使用的问题~
使用覆盖索引~
规则9.字符串不加单引号索引失效
mysql> select * from staffs where name=2000; +----+------+-----+-----+---------------------+ | id | NAME | age | pos | add_time | +----+------+-----+-----+---------------------+ | 3 | 2000 | 23 | dev | 2017-03-17 23:14:23 | +----+------+-----+-----+---------------------+ 1 row in set, 1 warning (0.00 sec) mysql> explain select * from staffs where name='2000'; +----+-------------+--------+------+-----------------------+-----------------------+---------+-------+------+-----------------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+-----------------------+-----------------------+---------+-------+------+-----------------------+ | 1 | SIMPLE | staffs | ref | idx_staffs_nameAgePos | idx_staffs_nameAgePos | 74 | const | 1 | Using index condition | +----+-------------+--------+------+-----------------------+-----------------------+---------+-------+------+-----------------------+ 1 row in set (0.00 sec) mysql> explain select * from staffs where name=2000; +----+-------------+--------+------+-----------------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+-----------------------+------+---------+------+------+-------------+ | 1 | SIMPLE | staffs | ALL | idx_staffs_nameAgePos | NULL | NULL | NULL | 3 | Using where | +----+-------------+--------+------+-----------------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec)
底层做了隐式的数据转换，将2000转换成'2000'，导致索引失效
规则10.少用or,用它来连接时索引会失效
mysql> explain select * from staffs where name='July' or name='z3'; +----+-------------+--------+------+-----------------------+------+---------+------+------+-------------+ | id | select_type | table | type | possible_keys | key | key_len | ref | rows | Extra | +----+-------------+--------+------+-----------------------+------+---------+------+------+-------------+ | 1 | SIMPLE | staffs | ALL | idx_staffs_nameAgePos | NULL | NULL | NULL | 3 | Using where | +----+-------------+--------+------+-----------------------+------+---------+------+------+-------------+ 1 row in set (0.00 sec)
扫一扫
177万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
