索引介绍
索引是什么
-
官方介绍索引是帮助MySQL 高效获取数据 的 数据结构 。更通俗的说，数据库索引好比是一本书前面的目录，能 加快数据库的查询速度
-
一般来说索引本身也很大，不可能全部存储在内存中，因此 索引往往是存储在磁盘上的文件中的 （可能存储在单独的索引文件中，也可能和数据一起存储在数据文件中）
-
我们通常所说的索引，包括聚集索引、覆盖索引、组合索引、前缀索引、唯一索引等，没有特别说明，默认都是使 用B+树结构组织（多路搜索树，并不一定是二叉的）的索引
索引的优势和劣势
优势
-
可以提高数据检索的效率，降低数据库的IO成本 ，类似于书的目录
-
通过 索引列对数据进行排序 ，降低数据排序的成本，降低了CPU的消耗
-
被索引的列会自动进行排序，包括【单列索引】和【组合索引】，只是组合索引的排序要复杂一些
-
如果按照索引列的顺序进行排序，对应order by语句来说，效率就会提高很多
-
劣势
-
索引会占据磁盘空间
-
索引虽然会提高查询效率，但是会降低更新表的效率 。比如每次对表进行增删改操作，MySQL不仅要保存数据，还有保存或者更新对应的索引文件
索引的使用
索引的类型
-
主键索引：索引列中的值必须是唯一的，不允许有空值
ALTER TABLE table_name ADD PRIMARY KEY (column_name); -
普通索引：MySQL中基本索引类型，没有什么限制，允许在定义索引的列中插入重复值和空值
ALTER TABLE table_name ADD INDEX index_name (column_name) ; -
唯一索引：索引列中的值必须是唯一的，但是允许为空值
CREATE UNIQUE INDEX index_name ON table(column_name) ; -
全文索引：只能在文本类型CHAR,VARCHAR,TEXT类型字段上创建全文索引。字段长度比较大时，如果创建普通索引，在进行like模糊查询时效率比较低，这时可以创建全文索引
ALTER TABLE
t_fulltextADD FULLTEXT INDEXidx_content(content);全文搜索时候，全文索引一般很少使用，数据量比较少或者并发度低的时候可以用
-
空间索引：MySQL在5.7之后的版本支持了空间索引，而且支持OpenGIS几何数据模型。MySQL在空间索引这方面遵循OpenGIS几何数据模型规则
-
前缀索引：在文本类型如CHAR,VARCHAR,TEXT类列上创建索引时，可以指定索引列的长度，但是数值类型不能指定
ALTER TABLE table_name ADD INDEX index_name (column1(length)); -
按照索引列的数量
-
单列索引 ：索引中只有一个列
-
组合索引 ：使用2个以上的字段创建的索引
组合索引的使用，需要遵循 最左前缀原则
一般情况下， 建议使用组合索引代替单列索引
ALTER TABLE table_name ADD INDEX index_name (column1,column2); -
删除索引
DROP INDEX index_name ON table
查看索引
SHOW INDEX FROM table_name \G
索引创建原则
哪些情况需要创建索引
- 频繁出现在where 条件字段，order排序，group by分组字段
- select 频繁查询的列，考虑是否需要创建联合索引（覆盖索引，不回表）
- 多表join关联查询，on字段两边的字段都要创建索引
索引优化建议
-
表记录很少不需创建索引 （索引是要有存储的开销）
-
一个表的索引个数不能过多
-
空间：浪费空间。每个索引都是一个索引树，占据大量的磁盘空间
-
时间：更新（插入/Delete/Update）变慢。需要更新所有的索引树，太多的索引也会增加优化器的选择时间
-
-
频繁更新的字段不建议作为索引
频繁更新的字段引发频繁的页分裂和页合并，性能消耗比较高
-
区分度低的字段，不建议建索引
-
在InnoDB存储引擎中，主键索引建议使用自增的长整型，避免使用很长的字段
-
不建议用无序的值作为索引。例如身份证、UUID
-
尽量创建组合索引，而不是单列索引
-
1个组合索引等同于多个索引效果，节省空间
-
可以使用覆盖索引
创建原则：组合索引应该把把频繁的列，区分度高的值放在前面。频繁使用代表索引的利用率高，区分度高代表筛选粒度大，可以尽量缩小筛选范围
-
口诀
全值匹配我最爱，最左前缀要遵守；
带头大哥不能死，中间兄弟不能断；
索引列上不计算，范围之后全失效；
Like百分写最右，覆盖索引不写星；
不等空值还有OR，索引失效要少用。
