MySQL索引
一、介绍
1.什么是索引？
索引是对数据库表中一列或多列的值进行排序的一种结构。MySQL索引的建立对于MySQL的高效运行是很重要的，索引可以大大提高MySQL的检索速度。索引只是提高效率的一个因素，如果你的MySQL有大数据量的表，就需要花时间研究建立最优秀的索引，或优化查询语句。
📌简单类比一下，数据库如同书籍，索引如同书籍目录，假如我们需要从书籍查找与 xx 相关的内容，我们可以直接从目录中查找，定位到 xx 内容所在页面，如果目录中没有 xx 相关字符或者没有设置目录（索引），那只能逐字逐页阅读文本查找，效率可想而知。
2.索引的优缺点
索引可以大大提高MySQL的检索速度，为什么不对表中的每一个列创建一个索引呢？
优点
- 索引大大减小了服务器需要扫描的数据量，从而大大加快数据的检索速度，这也是创建索引的最主要的原因。
- 索引可以帮助服务器避免排序和创建临时表
- 索引可以将随机IO变成顺序IO
- 索引对于InnoDB（对索引支持行级锁）非常重要，因为它可以让查询锁更少的元组，提高了表访问并发性
- 关于InnoDB、索引和锁：InnoDB在二级索引上使用共享锁（读锁），但访问主键索引需要排他锁（写锁）
- 通过创建唯一性索引，可以保证数据库表中每一行数据的唯一性。
- 可以加速表和表之间的连接，特别是在实现数据的参考完整性方面特别有意义。
- 在使用分组和排序子句进行数据检索时，同样可以显著减少查询中分组和排序的时间。
- 通过使用索引，可以在查询的过程中，使用优化隐藏器，提高系统的性能。
缺点
- 创建索引和维护索引要耗费时间，这种时间随着数据量的增加而增加
- 索引需要占物理空间，除了数据表占用数据空间之外，每一个索引还要占用一定的物理空间，如果需要建立聚簇索引，那么需要占用的空间会更大
- 对表中的数据进行增、删、改的时候，索引也要动态的维护，这就降低了整数的维护速度
- 如果某个数据列包含许多重复的内容，为它建立索引就没有太大的实际效果。
- 对于非常小的表，大部分情况下简单的全表扫描更高效；
二、创建索引准则
索引是建立在数据库表中的某些列的上面。因此，在创建索引的时候，应该仔细考虑在哪些列上可以创建索引，在哪些列上不能创建索引。
应该创建索引的列
- 在经常需要搜索的列上，可以加快搜索的速度
- 在作为主键的列上，强制该列的唯一性和组织表中数据的排列结构
- 在经常用在连接（JOIN）的列上，这些列主要是一外键，可以加快连接的速度
- 在经常需要根据范围（<，<=，=，>，>=，BETWEEN，IN）进行搜索的列上创建索引，因为索引已经排序，其指定的范围是连续的
- 在经常需要排序（order by）的列上创建索引，因为索引已经排序，这样查询可以利用索引的排序，加快排序查询时间；
- 在经常使用在WHERE子句中的列上面创建索引，加快条件的判断速度。
不应该创建索引的列
-
对于那些在查询中很少使用或者参考的列不应该创建索引。
若列很少使用到，因此有索引或者无索引，并不能提高查询速度。相反，由于增加了索引，反而降低了系统的维护速度和增大了空间需求。
-
对于那些只有很少数据值或者重复值多的列也不应该增加索引。
这些列的取值很少，例如人事表的性别列，在查询的结果中，结果集的数据行占了表中数据行的很大比例，即需要在表中搜索的数据行的比例很大。增加索引，并不能明显加快检索速度。
-
对于那些定义为text, image和bit数据类型的列不应该增加索引。
这些列的数据量要么相当大，要么取值很少。
-
当该列修改性能要求远远高于检索性能时，不应该创建索引。（修改性能和检索性能是互相矛盾的）
三、索引分类
MySQL 的索引有两种分类方式：逻辑分类和物理分类。
mysql的索引分为单列索引(全文索引，主键索引，唯一索引，普通索引)和组合索引。
单列索引:一个索引只包含一个列，一个表可以有多个单列索引。
组合索引:一个组合索引包含两个或两个以上的列。
- 普通索引(index)：这是最基本的索引，它没有任何限制，由关键字KEY或INDEX定义的索引。普通索引的唯一任务是加快对数据的访问速度。因此应该只为那些最经常出现在查询条件(WHERE column = …)或排序条件(ORDER BY column)中的数据列创建索引。
- 唯一索引：它与前面的普通索引类似。不同的就是：普通索引允许被索引的数据列包含重复的值，而唯一索引列的值必须唯一。
- 主键索引(primary key)： 加速查找+约束（不为空且唯一），它是一种特殊的唯一索引，不允许有空值。一个表只能有一个主键。
- 唯一索引(unique)：加速查找+约束 （唯一），允许有空值。
- 联合索引
- primary key(id,name)：联合主键索引
- unique(id,name)：联合唯一索引
- index(id,name)：联合普通索引
- 全文索引fulltext ：用于搜索很长一篇文章的时候，效果最好。
- 空间索引spatial ：了解就好，几乎不用
四、操作索引
创建索引
-
索引名称 index_name 是可以省略的，省略后，索引的名称和索引列名相同。
-
如果字段数据是CHAR，VARCHAR类型，可以指定length，其值小于字段的实际长度，当然也可以忽略length，此时索引长度和字段的实际长度相同，如果是BLOB和TEXT类型就必须指定length。
length的用处：
有时候需要在长文本字段上建立索引，但这种索引会增加索引的存储空间以及降低索引的效率，这时就可以用到length，创建索引时用到length的索引，我们叫做前缀索引，前缀索引是选择字段数据的前n个字符作为索引，这样可以大大节约索引空间，从而提高索引效率。
此处展示的语句用于创建一个索引，索引使用字段数据的前10个字符。
CREATE INDEX part_of_name ON customer (name(10));使用字段数据的一部分创建索引可以使索引文件大大减小，从而节省了大量的磁盘空间，有可能提高INSERT操作的速度。
前缀索引是一种能使索引更小，更快的有效办法，但是MySql无法使用前缀索引做ORDER BY 和 GROUP BY以及使用前缀索引做覆盖扫描。
这里又引出了一个新概念，覆盖扫描！
如果一个索引（如：组合索引）中包含所有要查询的字段的值，那么就称之为覆盖索引，如：
SELECT user_name, city, age FROM user_test WHERE user = 'somnus' AND age > 25;因为要查询的字段（user_name, city, age）都包含在组合索引的索引列中，所以就使用了覆盖索引查询，查看是否使用了覆盖索引可以通过执行计划x中的Extra中的值为Using index则证明使用了覆盖索引，覆盖索引可以极大的提高访问性能。
-- 创建普通索引
CREATE INDEX index_name ON table_name(col_name(length));
-- 创建唯一索引
CREATE UNIQUE INDEX index_name ON table_name(col_name(length));
-- 创建普通组合索引
CREATE INDEX index_name ON table_name(col_name_1(length),col_name_2(length));
-- 创建唯一组合索引
CREATE UNIQUE INDEX index_name ON table_name(col_name_1(length),col_name_2(length));
修改表结构创建索引
-- 创建普通索引
ALTER TABLE table_name ADD INDEX index_name(col_name(length));
-- 创建唯一索引
ALTER TABLE table_name ADD UNIQUE INDEX index_name(col_name(length));
-- 创建普通组合索引
ALTER TABLE table_name ADD INDEX index_name(col_name,col_name_2(length));
-- 创建唯一组合索引
ALTER TABLE table_name ADD UNIQUE INDEX index_name(col_name(length),col_name_2(length));
创建表时直接指定索引
CREATE TABLE table_name (
ID INT NOT NULL,
col_name VARCHAR (16) NOT NULL,
INDEX index_name (col_name(length))
);
查看索引
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：更多评注。
删除索引
-- 直接删除索引
DROP INDEX index_name ON table_name;
-- 修改表结构删除索引
ALTER TABLE table_name DROP INDEX index_name;
索引失效的情况
- 如果条件中有or，即使其中有条件带索引也不会使用(这也是为什么尽量少用or的原因)
要想使用or，又想让索引生效，只能将or条件中的每个列都加上索引
-
使用查询的时候遵循mysql组合索引的"最左前缀"规则，假设现在有组合索引 (col1,col2,col3)，查询语句就只能是col1=condition1 或 (col1=condition1 and col2=condition2) 或 (col1=condition1 and col2=condition2 and col3=condition3)。
这里有两点需要注意：
- (col1=condition1 and col2=condition2) 和 (col2=condition2 and col1=condition1) 一样，没有区别，都会使用索引
- 组合索引（col1,col2,col3）的最左前缀是col1；组合索引（col3,col2,col1）的最左前缀是col3，最左前缀和表字段顺序无关
在组合索引中，如果where查询条件中某个列使用了范围查询（不管%在哪），则其右边的所有列都无法使用索引优化查询
-
like查询以%开头不使用索引，以%结尾可以使用索引。如 col1 like ‘%安’ 和 col1 like ‘%安%’ 不使用索引，但是 col1 like ‘安%’ 使用索引
-
如果列类型是字符串，那一定要在条件中将数据使用引号引用起来，否则不使用索引
-
如果mysql评估使用全表扫描要比使用索引快，则不使用索引
-
索引列不能是表达式的一部分，也不能作为函数的参数，否则无法使用索引查询。下面是例子：
SELECT * FROM user WHERE user_name = concat(user_name, 'fei');
explain
使用EXPLAIN关键字可以模拟优化器执行SQL查询语句，从而知道MySQL是如何处理SQL语句。不展开讲解，大家可自行百度这块知识点。
使用格式：EXPLAIN SQL...;
Look一下EXPLAIN 查询结果包含的字段（v5.7）
mysql> explain select * from student;
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| id | select_type | table | partitions | type | possible_keys | key | key_len | ref | rows | filtered | Extra |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
| 1 | SIMPLE | student | NULL | ALL | NULL | NULL | NULL | NULL | 2 | 100.00 | NULL |
+----+-------------+---------+------------+------+---------------+------+---------+------+------+----------+-------+
- id：选择标识符
- select_type：表示查询的类型。
- table：输出结果集的表
- partitions：匹配的分区
- type：表示表的连接类型
- possible_keys：表示查询时，可能使用的索引
- key：表示实际使用的索引
- key_len：索引字段的长度
- ref：列与索引的比较
- rows：扫描出的行数(估算的行数)
- filtered：按表条件过滤的行百分比
- Extra：执行情况的描述和说明
其他相关命令
-- 查看表结构
desc table_name;
-- 查看生成表的SQL
show create table table_name;
-- 查看索引信息（包括索引结构等）
show index from table_name;
-- 查看SQL执行时间（精确到小数点后8位）
set profiling = 1;
SQL...
show profiles;
2万+
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
