MYSQL之索引详谈（三种介绍⽅式）
概述
⽤来加快查询的技术很多，其中最重要的是索引。通常索引能够快速提⾼查询速度。如果不适⽤索引，MYSQL必须从第⼀条记录开始然后读完整个表直到出相关的⾏。表越⼤，花费的时间越多。但也不全是这样。本⽂讨论索引是什么以及如何使⽤索引来改善性能，以及索引可能降低性能的情况。
索引的本质
MySQL官⽅对索引的定义为：索引（Index）是帮助MySQL⾼效获取数据的数据结构。提取句⼦主⼲，就可以得到索引的本质：索引是数据结构。
数据库查询是数据库的最主要功能之⼀。我们都希望查询数据的速度能尽可能的快，因此数据库系统的设计者会从查询算法的⾓度进⾏优化。最基本的查询算法当然是顺序查（linear search），这种复杂度为O(n)的算法在数据量很⼤时显然是糟糕的，好在计算机科学的发展提供了很多更优秀的查算法，例如⼆分查（binary search）、⼆叉树查（binary tree search）等。如果稍微分析⼀下会发现，每种查算法都只能应⽤于特定的数据结构之上，例如⼆分查要求被检索数据有序，⽽⼆叉树查只能应⽤于⼆叉查树上，但是数据本⾝的组织结构不可能完全满⾜各种数据结构（例如，理论上不可能同
openstack各个组件之间的耦合是非常紧密的时将两列都按顺序进⾏组织），所以，在数据之外，数据库系统还维护着满⾜特定查算法的数据结构，这些数据结构以某种⽅式引⽤（指向）数据，这样就可以在这些数据结构上实现⾼级查算法。这种数据结构，就是索引。
索引的存储分类
索引是在MYSQL的存储引擎层中实现的，⽽不是在服务层实现的。所以每种存储引擎的索引都不⼀定完全相同，也不是所有的存储引擎都⽀持所有的索引类型。MYSQL⽬前提供了⼀下4种索引。
B-Tree 索引：最常见的索引类型，⼤部分引擎都⽀持B树索引。
HASH 索引：只有Memory引擎⽀持，使⽤场景简单。
R-Tree 索引(空间索引)：空间索引是MyISAM的⼀种特殊索引类型，主要⽤于地理空间数据类型。
Full-text (全⽂索引)：全⽂索引也是MyISAM的⼀种特殊索引类型，主要⽤于全⽂索引，InnoDB从MYSQL5.6版本提供对全⽂索引的⽀持。
Mysql⽬前不⽀持函数索引，但是能对列的前⾯某⼀部分进⾏索引，例如标题title字段，可以只取title的前10个字符进⾏索引，这个特性可以⼤⼤缩⼩索引⽂件的⼤⼩，但前缀索引也有缺点，在排序Order
By和分组Group By 操作的时候⽆法使⽤。⽤户在设计表结构的时候也可以对⽂本列根据此特性进⾏灵活设计。
语法：create index idx_title on film (title(10))
MyISAM、InnoDB引擎、Memory三个常⽤引擎类型⽐较
索引MyISAM引擎InnoDB引擎Memory引擎
B-Tree 索引⽀持⽀持⽀持
HASH 索引不⽀持不⽀持⽀持
R-Tree 索引⽀持不⽀持不⽀持
Full-text 索引不⽀持暂不⽀持不⽀持
B-TREE索引类型
普通索引
这是最基本的索引类型，⽽且它没有唯⼀性之类的限制。普通索引可以通过以下⼏种⽅式创建：
（1）创建索引: CREATE INDEX 索引名 ON 表名(列名1，列名2,...);
（2）修改表: ALTER TABLE 表名ADD INDEX 索引名 (列名1，列名2,...);
（3）创建表时指定索引：CREATE TABLE 表名 ( [...], INDEX 索引名 (列名1，列名 2,...) );
UNIQUE索引
表⽰唯⼀的，不允许重复的索引，如果该字段信息保证不会重复例如⾝份证号⽤作索引时，可设置为unique：
（1）创建索引：CREATE UNIQUE INDEX 索引名 ON 表名(列的列表);
（2）修改表：ALTER TABLE 表名ADD UNIQUE 索引名 (列的列表);
（3）创建表时指定索引：CREATE TABLE 表名( [...], UNIQUE 索引名 (列的列表) );
mysql面试题索引主键：PRIMARY KEY索引
主键是⼀种唯⼀性索引，但它必须指定为“PRIMARY KEY”。
（1）主键⼀般在创建表的时候指定：“CREATE TABLE 表名( [...], PRIMARY KEY (列的列表) ); ”。
（2）但是，我们也可以通过修改表的⽅式加⼊主键：“ALTER TABLE 表名ADD PRIMARY KEY (列的列表); ”。
每个表只能有⼀个主键。 （主键相当于聚合索引，是查最快的索引）
注：不能⽤CREATE INDEX语句创建PRIMARY KEY索引
索引的设置语法
⼀设置索引
在执⾏CREATE TABLE语句时可以创建索引，也可以单独⽤CREATE INDEX或ALTER TABLE来为表增加索引。
1．ALTER TABLE - ALTER TABLE⽤来创建普通索引、UNIQUE索引或PRIMARY KEY索引。
ALTER TABLE table_name ADD INDEX index_name (column_list)
java最新版本是几
ALTER TABLE table_name ADD UNIQUE (column_list)
ALTER TABLE table_name ADD PRIMARY KEY (column_list)
2．CREATE INDEX - CREATE INDEX可对表增加普通索引或UNIQUE索引。
CREATE INDEX index_name ON table_name (column_list)
CREATE UNIQUE INDEX index_name ON table_name (column_list)
⼆删除索引
可利⽤ALTER TABLE或DROP INDEX语句来删除索引。类似于CREATE INDEX语句，DROP INDEX可以在ALTER TABLE内部作为⼀条语句处理，语法如下。
DROP INDEX index_name ON talbe_name
ALTER TABLE table_name DROP INDEX index_name
ALTER TABLE table_name DROP PRIMARY KEY
其中，前两条语句是等价的，删除掉table_name中的索引index_name。
第3条语句只在删除PRIMARY KEY索引时使⽤，因为⼀个表只可能有⼀个PRIMARY KEY索引，因此不需要指定索引名。如果没有创建PRIMARY KEY索引，但表具有⼀个或多个UNIQUE索引，则MySQ
L将删除第⼀个UNIQUE索引。
如果从表中删除了某列，则索引会受到影响。对于多列组合的索引，如果删除其中的某列，则该列也会从索引中删除。如果删除组成索引的所有列，则整个索引将被删除。
三查看索引
mysql> show index from tblname;
mysql> show keys from tblname;
Table：表的名称
Non_unique：如果索引不能包括重复词，则为0。如果可以，则为1
Key_name：索引的名称
Seq_in_index：索引中的列序列号，从1开始
Column_name：列名称
Collation：列以什么⽅式存储在索引中。在MySQL中，有值‘A’（升序）或NULL（⽆分类）。
Cardinality：索引中唯⼀值的数⽬的估计值。通过运⾏ANALYZE TABLE或myisamchk -a可以更新。基数根据被存储为整数的统计数据来计数，所以即使对于⼩型表，该值也没有必要是精确的。基数越⼤，当进⾏联合时，MySQL使⽤该索引的机会就越⼤。
Sub_part：如果列只是被部分地编⼊索引，则为被编⼊索引的字符的数⽬。如果整列被编⼊索引，则为NULL。
Packed：指⽰关键字如何被压缩。如果没有被压缩，则为NULL。
Null：如果列含有NULL，则含有YES。如果没有，则该列含有NO。
Index_type：⽤过的索引⽅法（BTREE, FULLTEXT, HASH, RTREE）。
Comment：更多评注。
索引选择性
⼀索引选择原则
1. 较频繁的作为查询条件的字段应该创建索引
2. 唯⼀性太差的字段不适合单独创建索引，即使频繁作为查询条件
3. 更新⾮常频繁的字段不适合创建索引
当然，并不是存在更新的字段就适合创建索引，从判定策略的⽤语上也可以看出，是"⾮常频繁"的字段。到底什么样的更新频率应该算是"⾮常频繁"呢？每秒？每分钟？还是每⼩时呢？说实话，还真难定义。很多时候是通过⽐较同⼀时间段内被更新的次数和利⽤该字段作为条件的查询次数来判断的，如果通过该字段的查询并不是很多，可能⼏个⼩时或是更长才会执⾏⼀次，更新反⽽⽐查询更频繁，那这样的字段肯定不适合创建索引。反之，如果我们通过该字段的查询⽐较频繁，但更新并不是特别多，⽐如查询⼏⼗次或更多才可能会产⽣⼀次更新，那我个⼈觉得更新所带来的附加成本也是可以接受的。
4. 不会出现在 WHERE ⼦句中的字段不该创建索引
属性同好会拉链哪一集⼆索引选择原则细述
性能优化过程中，选择在哪个列上创建索引是最⾮常重要的。可以考虑使⽤索引的主要有 两种类型的列：在where⼦句中出现的列，在join⼦句中出现的列，⽽不是在SELECT关键字后选择列表的列；
索引列的基数越⼤，索引的效果越好。例如，存放出⽣⽇期的列具有不同的值，很容易区分⾏，⽽⽤
来记录性别的列，只有"M"和"F"，则对此进⾏索引没有多⼤⽤处，因此不管搜索哪个值，都会得出⼤约⼀半的⾏,（ 见索引选择性注意事项对选择性解释；）
使⽤短索引，如果对字符串列进⾏索引，应该指定⼀个前缀长度，可节省⼤量索引空间，提升查询速度；
例如，有⼀个CHAR(200)列，如果在前10个或20个字符内，多数值是唯⼀的，那么就不要对整个列进⾏索引。对前10个或者20个字符进⾏索引能够节省⼤量索引空间，也可能会使查询更快。较⼩的索引涉及的磁盘IO较少，较短的值⽐较起来更快。更为重要的是，对于较短的键值，所以⾼速缓存中的快能容纳更多的键值，因此，MYSQL也可以在内存中容纳更多的值。这样就增加了到⾏⽽不⽤读取索引中较多快的可能性。
利⽤最左前缀
三索引选择注意事项
既然索引可以加快查询速度，那么是不是只要是查询语句需要，就建上索引？答案是否定的。因为索引虽然加快了查询速度，但索引也是有代价的：索引⽂件本⾝要消耗存储空间，同时索引会加重插⼊、删除和修改记录时的负担，另外，MySQL在运⾏时也要消耗资源维护索引，因此索引并不是越多越好。
⼀般两种情况下不建议建索引：
1. 表记录⽐较少，例如⼀两千条甚⾄只有⼏百条记录的表，没必要建索引，让查询做全表扫描就好了;
⾄于多少条记录才算多，这个个⼈有个⼈的看法，我个⼈的经验是以2000作为分界线，记录数不超过 2000可以考虑不建索
引，超过2000条可以酌情考虑索引。
2. 索引的选择性较低。所谓索引的选择性（Selectivity），是指不重复的索引值（也叫基数，Cardinality）与表记录数（#T）的⽐值：
Index Selectivity = Cardinality / #T
显然选择性的取值范围为(0, 1]，选择性越⾼的索引价值越⼤，这是由B+Tree的性质决定的。例如，上⽂⽤到的employees.titles 表，如果title字段经常被单独查询，是否需要建索引，我们看⼀下它的选择性：
SELECT count(DISTINCT(title))/count(*) AS Selectivity FROM employees.titles;
+-------------+
| Selectivity |
app免费制作软件+-------------+
|      0.0000 |
+-------------+
title的选择性不⾜0.0001（精确值为0.00001579），所以实在没有什么必要为其单独建索引。
3. MySQL只对⼀下操作符才使⽤索引：<,<=,=,>,>=,between,in, 以及某些时候的like(不以通配符%或_开头的情形)。
4. 不要过度索引，只保持所需的索引。每个额外的索引都要占⽤额外的磁盘空间，并降低写操作的性能。 在修改表的内容时，索引必须
进⾏更新，有时可能需要重构，因此，索引越多，所花的时间越长。
四索引的弊端
索引的益处已经清楚了，但是我们不能只看到这些益处，并认为索引是解决查询优化的圣经，只要发
现 查询运⾏不够快就将 WHERE ⼦句中的条件全部放在索引中。
确实，索引能够极⼤地提⾼数据检索效率，也能够改善排序分组操作的性能，但有不能忽略的⼀个问题就是索引是完全独⽴于基础数据之外的⼀部分数据。假设在Table ta 中的Column ca 创建了索引 idx_ta_ca，那么任何更新 Column ca 的操作，MySQL在更新表中 Column ca的同时，都须要更新Column ca 的索引数据，调整因为更新带来键值变化的索引信息。⽽如果没有对 Column ca 进⾏索引，MySQL要做的仅仅是更新表中 Column ca 的信息。这样，最明显的资源消耗就是增加了更新所带来的 IO 量和调整索引所致的计算量。此外，Column ca 的索引idx_ta_ca须要占⽤存储空间，⽽且随着 Table ta 数据量的增加，idx_ta_ca 所占⽤的空间也会不断增加，所以索引还会带来存储空间资源消耗的增加。
--------------------------------------------------------------------------------------------------------------------------
Mysql学习之索引学习
优点: 索引可以⼤⼤提⾼MySQL的检索速度
缺点: 虽然索引⼤⼤提⾼了查询速度，同时却会降低更新表的速度，如对表进⾏INSERT、UPDATE和DELETE.因为更新表时，MySQL 不仅要保存数据，还要保存⼀下索引⽂件。
使⽤原则：通过上⾯说的优点和缺点，我们应该可以知道，并不是每个字段度设置索引就好，也不是索引越多越好，⽽是需要⾃⼰合理的使⽤。
1、对经常更新的表就避免对其进⾏过多的索引，对经常⽤于查询的字段应该创建索引，
2、数据量⼩的表最好不要使⽤索引，因为由
于数据较少，可能查询全部数据花费的时间⽐遍历索引的时间还要短，索引就可能不会产⽣优化效果。 3、在⼀同值少的列上(字段上)不要建⽴索引，⽐如在学⽣表的”性别”字段上只有男，⼥两个不同值。相反的，在⼀个字段上不同值较多可是建⽴索引。
索引分类
索引我们分为四类来
单列索引(普通索引，唯⼀索引，主键索引)、组合索引、全⽂索引、空间索引、
单列索引：⼀个索引只包含单个列，但⼀个表中可以有多个单列索引。这⾥不要搞混淆了。
|
|
|----普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插⼊重复值和空值，纯粹为了查询数据更快⼀点。
|
|----唯⼀索引：索引列中的值必须是唯⼀的，但是允许为空值，
|
|----主键索引：是⼀种特殊的唯⼀索引，不允许有空值。
组合索引: 在表中的多个字段组合上创建的索引，只有在查询条件中使⽤了这些字段的左边字段时，索引才会被使⽤，使⽤组合索引时遵循最左前缀集合全⽂索引：全⽂空间索引: 空间索引是对空间数据类型的字段建⽴的索引，MySQL中的空间数据类型有四种，GEOMETRY、POINT、LINESTRING、POLYGON。在创建空间索引时
准备:
创建表:
CREATE TABLE IF NOT EXISTS `demo1`(
ID INT NOT NULL,
name VARCHAR(16) NOT NULL
);
插⼊数据:
INSERT INTO `demo1` VALUES(1,'A'),(2,'B'),(3,'C'),(4,'A'),(5,'A'),(2,'B'),(2,'B'),(2,'B'),(5,'A'),(6,'A'),(6,'A'),(6,'A'),(9,'A');
INSERT INTO `demo1` SELECT * FROM  `demo1`;
查询测试⼀下:
SELECT * FROM `demo1` WHERE `name` = 'A';
结果:
[SQL]SELECT * FROM `demo1` WHERE `name` = 'A';
受影响的⾏: 0
时间: 0.003s
普通索引
创建索引
这是最基本的索引，它没有任何限制。它有以下⼏种创建⽅式:
CREATE INDEX index_name ON demo1(`name`)
查看表结构
sqlserver查询指定行
CREATE TABLE `demo1` (
`ID` int(11) NOT NULL,
`name` varchar(16) NOT NULL,
KEY `index_name` (`name`)
)
ENGINE=MyISAM DEFAULT CHARSET=latin1;

版权声明：本站内容均来自互联网，仅供演示用，请勿用于商业和其他非法用途。如果侵犯了您的权益请与我们联系QQ:729038198，我们将在24小时内删除。