MYSQL索引

§什么是索引？

MySQL官方对索引的定义为：索引（Index）是帮助MySQL高效获取数据的数据结构。

算法：顺序查找 O(N)  VS 二分查找O(logN) 、二叉树查找O(logN)。

数据结构：每种查找算法都只能应用于特定的数据结构之上。数据库数据本身的组织结构不可能完全满足高效查询需求，所以数据库系统还维护着满足特定查找算法的数据结构（索引）

§索引的优点：

减少服务器需要扫描的数据量。

帮助服务器避免排序和临时表。

将随机IO变为顺序IO。

§索引类型

B-TREE索引：主流索引，大多数MYSQL存储引擎都支持B-TREE索引。

HASH索引：HASH索引基于哈希表实现，MYSQL中只有MEMERY引擎显示支持HASH索引。

R-TREE索引：空间索引，用于地理数据存储。

FULLTEXT索引：不支持中文，需要安装插件。

§存储引擎与索引

MYSQL的索引是在MYSQL的存储引擎层中实现的，而不是在服务层实现的。

MYISAM与INNODB的BTREE索引区别：聚集索引与非聚集索引(INNODB设置自增字段做主键，主键不要过长)

§索引选择

选择索引的数据类型

    1.越小的数据类型通常更好：越小的数据类型通常在磁盘、内存和CPU缓存中都需要更少的空间，处理起来更快。

    2.简单的数据类型更好：整型数据比起字符，处理开销更小，因为字符串的比较更复杂。在MySQL中，应该用内置的日期和时间数据类型，而不是用字符串来存储时间；以及用整型数据类型存储IP地址。

    3.尽量避免NULL：应该指定列为NOT NULL，除非你想存储NULL。在MySQL中，含有空值的列很难进行查询优化，因为它们使得索引、索引的统计信息以及比较运算更加复杂。你应该用0、一个特殊的值或者一个空串代替空值。

索引列的选择（业务场景）

    1.较频繁的作为查询条件的字段应该创建索引

    2.WHERE子句或连接子句的列合适做索引

    3.对于唯一值的列，索引效果好，唯一性太差的列不适合单独创建索引，即使查询频繁

    4.对串列索引时，使用前缀索引

    5.建立联合索引时，选择性高的列在前

    6.不是索引越多越好：尽量扩展索引，而不是增加多个单列索引

    7.更新非常频繁的字段不适合创建索引

§索引优化

单列索引：索引列不能使用函数。例子：SELECT id FROM test WHERE id + 1 = 3;

前缀索引：节省索引空间，查询更快。不能使用order by和group by操作。不能用于covering index。需要兼顾选择性 SELECT COUNT(DISTINCT LEFT(title, 4))/COUNT(*) AS selectivity FROM test。

联合索引：匹配全值，匹配最左前缀，匹配值的范围查询，匹配部分精确而其它部分进行范围匹配，覆盖索引。

§Explain

Id:  查询序列号

Select_type: 查询类型（simple，primary，subquery, dependent subquery, union…）

Table: 表名

Type:联接类型（const, eq_ref, ref, ref_or_null, range, index, all …）

Possible_keys:使用哪个索引会在该表中找到行，不太重要

Key：实际决定使用的索引

Key_len:决定使用的索引的长度

Ref:显示使用哪个列或常数与key一起从表中选取行

Rows:认为它执行查询时必须检查的行数

Extra:查询实现中的额外信息 Using index, Using where, Using filesort, Using temporary

 

§参考文献

高性能Mysql

MySQL索引背后的数据结构及算法原理: http://blog.codinglabs.org/articles/theory-of-mysql-index.html

http://database.51cto.com/art/201107/275030.htm

MYSQL-索引:https://segmentfault.com/a/1190000003072424

http://blog.youkuaiyun.com/zhuxineli/article/details/14455029

转载于:https://my.oschina.net/u/1429959/blog/1570709