用于快速查询数据库表中的数据
优点 : 提高检索数据的速度
缺点 : 索引需要占用物理空间，因此在增加、删除、修改数据时会造成维护速度降低
MySQL 种索引类型
1> 普通索引 : CREATE INDEX index_name ON table(column(length))
2> 唯一索引 : CREATE UNIQUE INDEX indexName ON table(column(length))
3> 主键索引 : CREATE TABLE table_name(id INT(11) NOT NULL AUTO_INCREMENT, title CHAR(255) NOT NULL ,PRIMARY KEY(id))
4> 组合索引 : ALTER TABLE table ADD INDEX name_city_age(name,city,age)
5> 全文索引 : CREATE TABLE table_name(id INT(11) NOT NULL AUTO_INCREMENT, title CHAR(255) CHARACTER NOT NULL, content TEXT CHARACTER NULL , time INT(10) NULL DEFAULT NULL , PRIMARY KEY(id), FULLTEXT (content))
索引的设计原则
1> 选择唯一性索引，可更快通过索引确定某条记录
2> 为经常需要 排序ORDER BY、分组GROUP BY 和 联合操作UNION 的字段建立索引
3> 为常作为查询条件的字段建立索引
4> 限制索引的数目，索引太多需要的磁盘空间就越大，修改表示对索引的重构和更新会很麻烦
5> 尽量使用数据量少的索引，对 CHAR(100) 全文索引肯定会比 CHAR(10) 耗时多
6> 尽量使用前缀来索引
7> 删除不再使用或者很少使用的索引
8> 避免多个范围条件 : MySQL 支持单列的范围索引，但不支持多列范围索引
9> 尽量避免NULL，含有 NULL 的索引将很难进行优化
mysql> SELECT user.* FROM user WHERE login_time>'2017-04-01' AND age BETWEEN 18 AND 30;
Hash 索引
mysql 中，只有 Memory(Memory表只存在内存中，断电会消失，适用于临时表) 存储引擎显示支持 Hash索引，是 Memory表的默认索引类型，尽管 Memory表也可以使用 B+Tree索引。Hash索引 把数据以 hash 形式组织起来，因此当查找某一条记录的时候，速度非常快。但是因为 hash 结构，每个键只对应一个值，而且是散列的方式分布，所以它并不支持范围查找和排序等功能
B+Tree 索引
通常所说的索引是指 B-Tree索引，它是目前关系型数据库中查找数据最为常用和有效的索引，大多数存储引擎都支持这种索引，且 MySQL 默认采用这种索引。使用 B-Tree 这个术语，是因为 MySQL 在 CREATE TABLE 或其它语句中使用这个关键字，但实际上不同的存储引擎可能使用不同的数据结构，比如 InnoDB 就是使用的 B+Tree。B+Tree中的B是指balance，意为平衡。相对 Hash索引，B+Tree在查找单条记录的速度比不上 Hash索引，但是因为更适合排序等操作，所以它更受欢迎。毕竟不可能只对数据库进行单条记录的操作
注 : B+树索引 并不能找到一个给定键值的具体行，它找到的只是被查找数据行所在的页，接着数据库会把页读入到内存，再在内存中进行查找，最后得到要查找的数据
MySQL 中索引组织数据的存储
mysql> CREATE TABLE People(last_name VARCHAR(50) NOT NULL,first_name VARCHAR(50) NOT NULL,dob DATE NOT NULL,gender ENUM('m','f') NOT NULL, KEY(last_name,first_name,dob));
对于表中每一行数据，索引中包含 last_name、first_name、dob列的值，如下为 索引如何组织数据存储
可以看到，索引首先根据第一个字段来排列顺序，当名字相同时，则根据第三个字段，即出生日期来排序，正是因为这个原因，才有索引的 "最左原则"
1> MySQL 不会使用索引的情况 : 非独立的列
独立的列 是指索引列不能是表达式的一部分，也不能是函数的参数
mysql> SELECT * FROM WHERE id+1=5; # 等价于 id=4，但是MySQL无法自动解析这个表达式，使用函数是同样的道理
2> 前缀索引 : 如果列很长，通常可以索引开始的部分字符，这样可以有效节约索引空间，从而提高索引效率
3> 多列索引和索引顺序 : 在多数情况下，在多个列上建立独立的索引并不能提高查询性能。理由非常简单，MySQL不知道选择哪个索引的查询效率更好，所以在老版本，比如 MySQL5.0 之前就会随便选择一个列的索引，而新的版本会采用合并索引的策略
mysql> SELECT film_id,actor_id FROM film_actor WHERE actor_id=1 OR film_id=1;
# 老版本的 MySQL 会随机选择一个索引，但新版本做如下的优化
mysql> SELECT film_id,actor_id FROM film_actor WHERE actor_id=1
UNION ALL
SELECT film_id,actor_id FROM film_actor WHERE film_id=1 AND actor_id <> 1;
当出现多个索引做相交操作时 (多个AND条件)，通常来说一个包含所有相关列的索引要优于多个独立索引
当出现多个索引做联合操作时 (多个OR条件)，对结果集的合并、排序等操作需要耗费大量的CPU和内存资源，特别是当其中的某些索引的选择性不高，需要返回合并大量数据时，查询成本更高，所以这种情况下还不如走全表扫描
因此 explain 时如果发现有索引合并 (Extra字段出现 Using union)，应该好好检查一下查询和表结构是不是已经是最优的，如果查询和表都没有问题，那只能说明索引建的非常糟糕，应当慎重考虑索引是否合适，有可能一个包含所有相关列的多列索引更适合
索引选择性是指不重复的索引值和数据表的总记录数的比值，选择性越高查询效率越高，因为选择性越高的索引可以让 MySQL在查询时过滤掉更多的行。唯一索引的选择性是 1，这时最好的索引选择性，性能也是最好的
mysql> SELECT * FROM payment where staff_id=2 AND customer_id=584;
# 执行下面的查询，哪个字段的选择性更接近1就把哪个字段索引前面就好
mysql> SELECT COUNT(DISTINCT staff_id)/COUNT(*) AS staff_id_selectivity,COUNT(DISTINCT customer_id)/COUNT(*) AS customer_id_selectivity,COUNT(*) FROM payment;
一些特殊情况
mysql> SELECT user_id FROM trade WHERE user_group_id=1 AND trade_amount>0;
MySQL为这个查询选择了索引(user_group_id,trade_amount)，如果不考虑特殊情况，这看起来没有任何问题，但实际情况是这张表的大多数数据都是从老系统中迁移过来的，由于新老系统的数据不兼容，所以就给老系统迁移过来的数据赋予了一个默认的用户组。这种情况下，通过索引扫描的行数跟全表扫描基本没什么区别，索引也就起不到任何作用
注 : 经验法则和推论在多数情况下是有用的，可以指导开发和设计，但实际情况往往会更复杂，实际业务场景下的某些特殊情况可能会摧毁整个设计
覆盖索引 : 如果一个索引包含或者说覆盖所有需要查询的字段的值，那么就没有必要再回表查询，这就称为覆盖索引。覆盖索引是非常有用的工具，可以极大的提高性能，因为查询只需要扫描索引会带来许多好处
1> 索引条目远小于数据行大小，如果只读取索引，极大减少数据访问量
2> 索引是有按照列值顺序存储的，对于 I/O密集型的范围查询要比随机从磁盘读取每一行数据的 IO 要少的多
使用索引扫描来排序
MySQL 有两种方式可以生产有序的结果集，其一是对结果集进行排序的操作，其二是按照索引顺序扫描得出的结果自然是有序的。如果explain的结果中type列的值为 index 表示使用了索引扫描来做排序。扫描索引本身很快，因为只需要从一条索引记录移动到相邻的下一条记录。但如果索引本身不能覆盖所有需要查询的列，那么就不得不每扫描一条索引记录就回表查询一次对应的行。这个读取操作基本上是随机I/O，因此按照索引顺序读取数据的速度通常要比顺序地全表扫描要慢。在设计索引时，如果一个索引既能够满足排序，又满足查询，是最好的。只有当索引的列顺序和 ORDER BY子句 的顺序完全一致，并且所有列的排序方向也一样时，才能够使用索引来对结果做排序。如果查询需要关联多张表，则只有 ORDER BY子句 引用的字段全部为第一张表时，才能使用索引做排序。ORDER BY子句和查询的限制是一样的，都要满足最左前缀的要求 (有一种情况例外，就是最左的列被指定为常数，下面是一个简单的示例)，其它情况下都需要执行排序操作，而无法利用索引排序
# 最左列为常数，索引 : (date,staff_id,customer_id)
mysql> SELECT staff_id,customer_id FROM demo WHERE date='2015-06-01' ORDER BY staff_id,customer_id;
冗余和重复索引
冗余索引是指在相同的列上按照相同的顺序创建的相同类型的索引，应当尽量避免这种索引，发现后立即删除。比如有一个索引(A,B)，再创建索引(A)就是冗余索引。冗余索引经常发生在为表添加新索引时，比如有人新建了索引(A,B)，但这个索引不是扩展已有的索引(A)。大多数情况下都应该尽量扩展已有的索引而不是创建新索引。但有极少情况下出现性能方面的考虑需要冗余索引，比如扩展已有索引而导致其变得过大，从而影响到其他使用该索引的查询
索引失效
1> 如果条件中有 OR，且 OR 两边存在不带索引的查询列，此时将无法使用索引
mysql> EXPLAIN SELECT * FROM acc_login WHERE id='379'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: acc_login
partitions: NULL
type: const
possible_keys: PRIMARY
key: PRIMARY
key_len: 8
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * FROM acc_login WHERE id='379' OR scope='consumer'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: acc_login
partitions: NULL
type: ALL
possible_keys: PRIMARY,uix_acclogin_scope_mobile,ix_acclogin_smp
key: NULL
key_len: NULL
ref: NULL
rows: 162
filtered: 10.56
Extra: Using where
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * FROM acc_login WHERE id='379' OR pin='491678653223145472'\G # 当 OR 两边都是索引列时，将会使用索引
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: acc_login
partitions: NULL
type: index_merge
possible_keys: PRIMARY,uix_acclogin_pin
key: PRIMARY,uix_acclogin_pin
key_len: 8,98
ref: NULL
rows: 2
filtered: 100.00
Extra: Using union(PRIMARY,uix_acclogin_pin); Using where
1 row in set, 1 warning (0.00 sec)
2> 对于多列索引，不是使用的第一部分，则不会使用索引，且对于多列索引一定要按顺序如果跳列则跳列后的列的索引将不会使用
mysql> show create table acc_login\G
*************************** 1. row ***************************
Table: acc_login
Create Table: CREATE TABLE `acc_login` (
`id` bigint(20) NOT NULL AUTO_INCREMENT COMMENT ',',
`pin` varchar(32) NOT NULL COMMENT 'pin',
`scope` varchar(8) NOT NULL COMMENT '[agent consumer]',
`mobile` varchar(11) NOT NULL COMMENT '()',
`password` varchar(64) NOT NULL,
`status` tinyint(4) NOT NULL COMMENT '[1: 2:]',
`last_login_time` datetime DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `uix_acclogin_scope_mobile` (`scope`,`mobile`) USING BTREE,
UNIQUE KEY `uix_acclogin_pin` (`pin`) USING BTREE,
KEY `ix_acclogin_smp` (`scope`,`mobile`,`password`) USING BTREE
) ENGINE=InnoDB AUTO_INCREMENT=380 DEFAULT CHARSET=utf8
1 row in set (0.00 sec)
mysql> EXPLAIN SELECT * FROM acc_login WHERE mobile='18582457775'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: acc_login
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 162
filtered: 10.00
Extra: Using where
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * FROM acc_login WHERE scope='consumer'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: acc_login
partitions: NULL
type: ref
possible_keys: uix_acclogin_scope_mobile,ix_acclogin_smp
key: uix_acclogin_scope_mobile
key_len: 26
ref: const
rows: 86
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
3> like 查询是以 %开头将不会使用索引
mysql> EXPLAIN SELECT * FROM acc_login WHERE pin LIKE '%491678653223145472'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: acc_login
partitions: NULL
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 162
filtered: 11.11
Extra: Using where
1 row in set, 1 warning (0.00 sec)
mysql> EXPLAIN SELECT * FROM acc_login WHERE pin LIKE '491678653223145472%'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: acc_login
partitions: NULL
type: range
possible_keys: uix_acclogin_pin
key: uix_acclogin_pin
key_len: 98
ref: NULL
rows: 1
filtered: 100.00
Extra: Using index condition
1 row in set, 1 warning (0.00 sec)
4> 如果列类型是字符串，那一定要在条件中将数据使用引号引用起来，否则不使用索引
mysql> EXPLAIN SELECT * FROM acc_login WHERE pin=491678653223145472\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: acc_login
partitions: NULL
type: ALL
possible_keys: uix_acclogin_pin
key: NULL
key_len: NULL
ref: NULL
rows: 162
filtered: 10.00
Extra: Using where
1 row in set, 3 warnings (0.00 sec)
mysql> EXPLAIN SELECT * FROM acc_login WHERE pin='491678653223145472'\G
*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: acc_login
partitions: NULL
type: const
possible_keys: uix_acclogin_pin
key: uix_acclogin_pin
key_len: 98
ref: const
rows: 1
filtered: 100.00
Extra: NULL
1 row in set, 1 warning (0.00 sec)
5> 不要在索引上做任何操作 (计算、函数、自动/手动类型转换)，不然会导致索引失效而转向全表扫描
6> 索引字段上使用 ( != 或者 <>) 判断时，会导致索引失效而转向全表扫描
7> 范围条件 (bettween、<、>、in等) 将不会使用索引
8> 索引字段上使用 is null / is not null 判断时，会导致索引失效而转向全表扫描
7> 如果 mysql 估计使用全表扫描要比使用索引快，则不使用索引，这种情况会出现在当数据量不多时，或者列中很多值都相同的情况
创建索引
创建表时创建索引，其最基本形式是 :
CREATE TABLE 表名(属性名 数据类型 [完整性约束条件],属性名 数据类型 [完整性约束条件]... 属性名 数据类型 [UNIQUE|FULLTEXT|SPATIAL] INDEX|KEY [别名] (属性名1 [(长度)] [ASC|DESC]))
FULLTEXT 是可选参数表示全文索引，SPATIAL是可选参数表示空间索引，INDEX和KEY指定那些字段是索引二择其一作用相同
1> 创建主键索引 :
mysql> CREATE TABLE T1(id INT PRIMARY KEY AUTO_INCREMENT,name VARCHAR(50),);
mysql> ALTER TABLE table_name ADD PRIMARY KEY(column_list);
聚集索引 : 一种索引，该索引中键值的逻辑顺序决定表中相应行的物理顺序，聚集索引确定表中数据的物理顺序，MySQL 中 MyISAM表是没有聚集索引的，innodb 有 (主键就是聚集索引)
2> 创建唯一索引 :
mysql> CREATE TABLE T1(id INT UNIQUE,name VARCHAR(50),INDEX(id ASC));
mysql> ALTER TABLE table_name ADD UNIQUE(column_list);
3> 创建全文索引 (FULLTEXT) : 全文索引可以在CREATE TABLE ，ALTER TABLE ，CREATE INDEX 使用，只建立在CHAR、VARCHAR或TEXT类型的字段上，且只有 MyISAM 存储支持全文索引。全文索引并不是和 MyISAM 一起诞生的，它的出现是为解决 WHERE name LIKE "%word%" 这类针对文本的模糊查询效率较低的问题。在没有全文索引之前，这样一个查询语句是要进行遍历数据表操作的，可见，在数据量较大时是极其的耗时的，如果没有 异步IO 处理，进程将被挟持，很浪费时间
注 : 在数据量较大时候，现将数据放入一个没有全局索引的表中，然后再用 CREATE INDEX 创建 FULLTEXT 索引，要比先为一张表建立 FULLTEXT 然后再将数据写入的速度快很多
mysql> CREATE TABLE T1(id INT,name VARCHAR(50),FULLTEXT INDEX(name));
mysql> ALTER TABLE table_name ADD FULLTEXT(column);
# 创建
mysql> ALTER TABLE table ADD INDEX `FULLINDEX` USING FULLTEXT(`cname1`[,cname2…]);
# 使用
mysql> SELECT * FROM table WHERE MATCH(cname1[,cname2…]) AGAINST('word' MODE);
MODE 为搜寻方式 :
IN BOOLEAN MODE : 允许word里含一些特殊字符用于标记一些具体的要求，如+表示一定要有，-表示一定没有，*表示通用匹配符
IN NATURAL LANGUAGE MODE : 简单的单词匹配；含表达式的自然语言模式，就是先用自然语言模式处理，对返回的结果，再进行表达式匹配
IN NATURAL LANGUAGE MODE WITH QUERY EXPANSION / WITH QUERY EXPANSION
4> 创建单列索引 : 在单个字段上的一部分建立索引只查询前面若干索引
mysql> CREATE TABLE T1(id INT,name VARCHAR(50),INDEX(name(10)));
mysql> ALTER TABLE table_name ADD INDEX index_name(column);
注 : 索引名，可要可不要；如果不要，当前的索引名就是该字段名
5> 创建多列索引 : 在表的多个字段上创建一个索引
mysql> CREATE TABLE T1(id INT,name VARCHAR(50),INDEX(id,name));
mysql >ALTER TABLE table_name ADD INDEX index_name(column1,column2,column3...);
注 : 1> 只有在使用索引中的第一个字段时才会触发索引
2> 在 INDEX(a,b,c,d..) 也可以当作 INDEX(a) INDEX(a,b) 这样的索引，但必须从第一个索引开始且不能跳列
6> 创建空间索引
7> 在已存在的表上创建索引 :
CREATE [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名 ON 表名 (属性名 [(长度)] [ASC|DESC])
8> 用 ALTER TABLE语句来创建索引 :
ALTER TABLE 表名 ADD [UNIQUE|FULLTEXT|SPATIAL] INDEX 索引名 ON 表名 (属性名 [(长度)] [ASC|DESC])
mysql> ALTER TABLE Info_Pictures ADD INDEX pictures_index_userId(picturesUserId, picturesCreatetime ASC);
9> BTREE 索引 : BTREE索引就是一种将索引值按一定的算法，存入一个树形的数据结构中，BTREE 在 MyISAM 里的形式和 Innodb稍有不同。在 Innodb里，有两种形态 : 一是 primary key 形态，其 leaf node里存放的是数据，而且不仅存放了索引键的数据，还存放了其他字段的数据。二是 secondary index，其 leaf node 和普通的 BTREE差不多，只是还存放指向主键的信息。而在MyISAM里，主键和其他的并没有太大区别。不过和Innodb不太一样的地方是在MyISAM里，leaf node里存放的不是主键的信息，而是指向数据文件里的对应数据行的信息
mysql> ALTER TABLE commission_summary ADD UNIQUE KEY `uix_commissionsummary_insflag` (`comm_pay_cat`,`settle_company_code`,`service_flag`) USING BTREE;
删除索引
DROP INDEX 索引名 ON 表名
删除索引实例
mysql> ALTER TABLE commission_summary DROP INDEX `uix_commissionsummary_insflag`;
索引的查看
mysql> SHOW INDEX FROM tablename;
查看索引的使用情况
通过 show status 查看 Handler_read_* 相关的数据，其显示数据库处理 SELECT查询语句的状态
mysql> SHOW STATUS LIKE 'Handler_read%';
+-----------------------+-------+
| Variable_name | Value |
+-----------------------+-------+
| Handler_read_first | 4 |
| Handler_read_key | 5 |
| Handler_read_last | 0 |
| Handler_read_next | 0 |
| Handler_read_prev | 0 |
| Handler_read_rnd | 0 |
| Handler_read_rnd_next | 2447 |
+-----------------------+-------+
7 rows in set (0.00 sec)
Handler_read_first : 从索引中读取第一个条目的次数，如果这个值非常高，表明服务正在做大量的全文扫描，比如说 SELECT col1 FROM foo 假定 col1 是索引。如果这个选项的数值很大，既是好事 也是坏事。说它好是因为毕竟查询是在索引里完成的，而不是数据文件里，说它坏是因为大数据量时，简便是索引文件，做一次完整的扫描也是很费时的
Handler_read_key : 根据键读取一行的请求数，如果这个值很高，这就很好地表明对表查询使用合适的索引
Handler_read_next : 要读取下一行的键顺序的请求数。如果使用范围约束查询索引列，或者正在进行索引扫描，则该值将递增。此选项表明在进行索引扫描时，按照索引从数据文件里取数据的次数
Handler_read_prev : 表明在进行索引扫描时，按照索引倒序从数据文件里取数据的次数，一般就是ORDER BY … DESC
Handler_read_rnd : 查询直接操作数据文件，很多时候表现为没有使用索引或者文件排序
Handler_read_rnd_next : 在进行数据文件扫描时，从数据文件里取数据的次数，这个值越高，说明查询低效
扫一扫
10万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
