一、概要
索引就像一本书的目录。而当用户通过索引查找数据时，就好比用户通过目录查询某章节的某个知识点。这样就帮助用户有效地提高了查找速度。所以，使用索引可以有效地提高数据库系统的整体性能
在我们的一个应用系统中,读写的比例一般大概8:2左右，在实战开发中,当数据量比较大的时候查询的速度比较慢的时候,我们可以通过创建索引的方式来加快我们的查询速度
二、什么叫索引
系统根据某种算法，将已有的数据（未来可能新增的数据），单独建立一个文件，这个文件能够实现快速匹配数据，并且能够快速的找到对应的记录,本质上是一种数据结构
三、优缺点
1、优点
- 提升查询数据的效率
- 可以加速表与表之间的连接
- 在使用分组和排序进行检索的时候，可以减少查询中分组和排序的时间
2、缺点
- 当对表中的数据进行增加、删除和修改的时候，索引也要动态的维护，这样就降低了数据的维护速度。
- 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加。
- 索引需要占物理空间，除了数据表占数据空间之外，每一个索引还要占一定的物理空间，如果要建立聚簇索引，那么需要的空间就会更大。
四、索引分类
1、按存储结构
- BTree索引
- Hash索引
- 位图索引(mysql不支持)
2 、按应用层次
- 普通索引，
- 主键索引
- 唯一索引
- 全文索引
- 复合索引
3、数据行的物理顺序与列值的逻辑顺序相同
- 聚集索引
- 非聚集索引
五、基本使用
4.1、主键索引
- 说明
创建主键约束自动会建立主键索引，不允许重复，不允许空值； - 语法格式
-- 创建主键时数据库自动创建 CREATE TABLE 表名 ( 列名 类型 PRIMARY KEY ) -- 或者 表级创建 CREATE TABLE 表名 ( 列名 类型 , PRIMARY KEY(列名) ) - 示例代码
CREATE TABLE t_test( -- 自动创建主键索引 tid int AUTO_INCREMENT PRIMARY KEY ) -- 或者 CREATE TABLE t_test( -- 自动创建主键索引 tid int AUTO_INCREMENT , PRIMARY KEY(tid) )
4.2、普通索引
- 说明
在创建普通索引时，不附加任何限制条件。这类索引可以创建在任何数据类型中，其值是否唯一和非空由字段本身的完整性约束条件决定。 - 语法格式
-- 在创建表的时候创建 (不推荐) CREATE TABLE 表名( 列名 类型 约束, ...., key () ) -- 创建表之后在创建索引 (推荐方式创建) CREATE INDEX 索引名 ON 表 (列名,); - 示例代码
CREATE TABLE t_index_key ( tid int PRIMARY KEY AUTO_INCREMENT, name varchar(64) NOT NULL, KEY (name) )-- 删除索引 DROP INDEX name ON t_index_key -- 创建索引 推荐 CREATE INDEX idx_key_name ON t_index_key(name) -- 查看索引 EXPLAIN SELECT * from t_index_key WHERE name='123'
4.3、唯一索引
- 说明
用来建立索引的列的值必须是唯一的，允许空值, 可以通过创建表的时候使用唯一约束创建 - 语法格式
CREATE UNIQUE INDEX 索引名 ON 表名(列名 DESC,列名) - 示例代码
CREATE UNIQUE INDEX idx_user_username ON t_user(username DESC)
4.4、全文索引
说明
即为全文索引，Mysql5.6之前只有MyISAM引擎支持，Mysql5.6之后InnoDB也支持。目前只有 CHAR、VARCHAR ，TEXT 列上可以创建全文索引
主要解决 它的出现是为了解决WHERE name LIKE “%word%"这类针对文本的模糊查询效率较低的问题-
创建格式
-- 1. 创建表 DROP TABLE IF EXISTS t_myisam; CREATE TABLE t_myisam ( mid int AUTO_INCREMENT PRIMARY KEY, name varchar(64) NOT NULL, detail text ) ENGINE = MYISAM DEFAULT CHARSET = UTF8MB4; -
查询格式
MATCH (列名,...) AGAINST ('查询的关键字' 检索模式) -
搜索语法规则
-
+一定要有(不含有该关键词的数据条均被忽略)。 -
-不可以有(排除指定关键词，含有该关键词的均被忽略)。 -
>提高该条匹配数据的权重值 -
*全匹配，不像其他语法放在前面，这个要接在字符串后面。
-
-
检索模式
- 自然语言检索： IN NATURAL LANGUAGE MODE
- 布尔检索： IN BOOLEAN MODE
特点如下不剔除50%以上符合的row。
不自动以相关性反向排序。
可以对没有FULLTEXT index的字段进行搜寻，但会非常慢。
限制最长与最短的字符串。
套用Stopwords。
-
示例代码
-- 1. 创建 DROP TABLE IF EXISTS t_myisam; CREATE TABLE t_myisam ( mid int AUTO_INCREMENT PRIMARY KEY, name varchar(64) NOT NULL, detail text ) ENGINE = MYISAM DEFAULT CHARSET = UTF8MB4; -- 2.创建全文索引 CREATE FULLTEXT INDEX idx_username_detail ON t_myisam(detail) -- 查看索引信息 SHOW INDEX FROM t_myisam -- 或者 SHOW KEYS FROM t_myisam SELECT * FROM t_myisam WHERE MATCH(detail) AGAINST('ab') -- 查看索引是否生效 EXPLAIN SELECT * FROM t_myisam WHERE MATCH(detail) AGAINST('ab') -- 查询必须包含ab开头的词 SELECT * FROM articles WHERE MATCH (detail) AGAINST ('+ab*' IN BOOLEAN MODE);
4.5、联合索引(多列索引)
- 说明
联合索引遵守“最左前缀”原则，即在查询条件中使用了联合索引的第一个字段，索引才会被使用。因此，在联合索引中索引列的顺序至关重要。如果不是按照索引的最左列开始查找，则无法使用索引 - 语法格式
CREATE INDEX 索引名 ON 表名(索引字段,索引字段,...) - 示例代码
CREATE TABLE t_user ( uid int AUTO_INCREMENT PRIMARY KEY, username varchar(64) NOT NULL, password varchar(128) NOT NULL, phone varchar(11) NOT NULL, is_delete tinyint DEFAULT 0 NOT NULL, create_date timestamp DEFAULT CURRENT_TIMESTAMP NOT NULL, CONSTRAINT index_user_name UNIQUE (username) ); -- 创建普通的联合索引 CREATE INDEX index_user_date ON t_user (username,phone,create_date);-- 联合索引字段 username,phone,create_date -- 1. 可以 SELECT * FROM t_user WHERE username = 'admin'; -- 2. 可以 SELECT * FROM t_user WHERE username = 'admin' AND phone = '123456'; -- 3. 可以 SELECT * FROM t_user WHERE username = 'admin' AND phone = '123456' AND create_date = '2019-07-18 17:04:12'; -- 不可以 SELECT * FROM t_user WHERE phone = '123456'; - 最左前缀的原则
- 如果我们建立了一个2列的联合索引(a,b),实际上已经相当于建立了两个联合索引( a ) 、( a, b ）;
- 如果有一个3列索引( a, b, c )，实际上已经建立了三个联合索引( a )、(a, b)、(a, b, c)。依次内推
- 总结
当创建(a,b,c)联合索引时，相当于创建了(a)单列索引，(a,b)联合索引以及(a,b,c)联合索引
想要索引生效的话,只能使用 a和a,b和a,b,c三种组合；当然，我们上面测试过，a,c组合也可以，但实际上只用到了a的索引，c并没有用到！
六、什么情况使用索引
- 索引应该经常建在where 子句经常用到的列上。如果某个大表经常使用某个字段进行查询，并且检索行数小于总表行数的5%。则应该考虑。
- 对于两表连接的字段，应该建立索引。如果经常在某表的一个字段进行Order By 则也经过进行索引。
- 不应该在小表上建设索引(例如表中只有三四个字段)。
七、索引失效的情况
-
对索引列运算，运算包括（+、-、*、/、！、%），导致索引失效
-- 则会使索引失效， EXPLAIN SELECT * FROM tbl WHERE age + 5 > 10 - 不等于(!=)比较特殊 除主键索引或索引是整数类型外的其它索引都失效
EXPLAIN SELECT * FROM t_user WHERE username != 'OlUldQDIVV'; -- 索引有效 EXPLAIN SELECT * FROM t_user WHERE uid != 1; - like以通配符开头（‘%’）,如果非要用使用全文索引
- 如果条件中有or，即使其中有条件带索引也不会使用 如果想使用or，又想让索引生效，只能将or条件中的每个列都加上索引
- 小于 大于这个根据实际查询数据来判断，如果全盘扫描速度比索引速度要快则不走索引 。
- 索引列上不要使用函数,oracle必须使用函数索引
总结一句话就是, 使用explain 关键执行一下 key是否有值, 有值就说明走了索引,null就表示索引失效-- 索引失效 SELECT * FROM t_user WHERE substr(username ,1 ,3 ) = 'ABC'
八、key和index区别
1、说明
key 是数据库的物理结构，它包含两层意义，一是约束（偏重于约束和规范数据库的结构完整性），二是索引（辅助查询用的）。包括primary key, unique key, foreign key
2、primary key
- 一是约束作用（constraint），用来规范一个存储主键和唯一性，
- 同时也在此key上建立了一个index
3、 unique key
- 约束作用（constraint），规范数据的唯一性
- 在这个key上建立了一个index
4、foreign key
- 约束作用（constraint），规范数据的引用完整性
- 在这个key上建立了一个index
九、总结
- 索引占磁盘空间，不要重复的索引，尽量短
- 只给常用的查询条件加索引
- 过滤性高的列建索引，取值范围固定的列不建索引
- 唯一的记录添加唯一索引
- 频繁更新的列不要建索引
- 不要对索引列运算
- 同样过滤效果下，保持索引长度最小
- 合理利用组合索引，注意索引字段先后顺序
- 多列组合索引，过滤性高的字段最前
- order by 字段建立索引，
- 组合索引，不同的排序顺序不能使用索引
十、其它
1、查看索引
- 语法
show index from tblname; -- 或者 show keys from tblname; - 说明
- Non_unique：如果索引不能包括重复词，则为0。如果可以，则为1
- Key_name：索引的名称
- Column_name：索引列名称
- Index_type ：索引类型
2、执行计划(explain)
-
概要
在 select 语句之前增加 explain 关键字，MySQL 会在查询上设置一个标记，执行查询时，会返回执行计划的信息，而不是执行这条SQL（如果 from 中包含子查询，仍会执行该子查询，将结果放入临时表中）
image - 主要字段说明
- type
system > const > eq_ref > ref > range > index > all
一般来说，得保证查询至少达到range级别，最好能达到ref。
index all 性能差需要优化 至少要达到 range 级别，要求是 ref 级别，如果可以是 consts 最好 - possible_keys
显示可能应用在这张表中的索引，一个或多个。查询涉及到的字段上若存在索引，则该索引将被列出，但不一定被查询实际使用 - key
实际使用的索引，如果为NULL，则没有使用索引
- type
