MySQL索引及索引的优化策略

1.什么是索引？

        索引是对数据库表中一列或多列的值进行排序的一种结构，使用索引可快速访问数据库表中的特定信息

2.为什么使用索引：

        1.高效性：利用索引可以提高数据库的查询效率

        2.唯一性：索引可以确保所查的数据的唯一性

        3.完整性：用户可以加速表和表之间的连接，实现表与表之间的参照完整性

        4.特殊能力：通过索引，可以在查询过程中，使用优化隐藏器，提高系统性能

3.索引的缺点

        1.虽然所用大大提高了查询速度，同时缺降低更新表的速度，入轨表进行insert，update，delete

        2.因为更新表是，MySQL不仅要保存数据，还要保存一下索引文件，建立索引会占用磁盘空间的索引文件

        3.如果你在一个大表上创建了多种组合索引，索引文件会快速膨胀

注意：索引只是提高效率的一个因素，如果你的MySQL有大量数据的表，就需要花时间建立最优秀的索引，或优化查询语句

4.索引的分类：

        主键索引：在数据库为表定义一个主键将自动创建主键索引

-- 说明：
-- 某一属性组能唯一标识一条记录
-- 最常见的索引类型  -- 确保数据记录的唯一性
-- 确定特定数据记录在数据库中的位置
create table 表名(
    字段名  类型  auto_increment  primary key;

)

        唯一索引：不允许具有索引值相同的行，从而禁止重复的索引或键值

-- 说明
-- 快速定位特定数据
-- index和key关键字都可设置常规索引
-- 应加在查找条件的字段；  -- 不易添加太多，影响删除，插入，修改
create table 表民(
    字段 类型  not null unique,
     #或  unique key(字段)
)

        常规索引：最基本的索引类型，没有唯一性之类的限制

-- 说明
-- 避免同一个表中某数据列中的值重复
-- 对比主键索引只能有一个，唯一索引可有多个
create table 表明(
    字段  类型  not null，
    index/key(字段)
)

        全文索引：搜索引擎的关键技术，用于检索文本信息，可以是词语，段落

-- 语法
create table 表名(
    字段  varchar(10) not null,
    fulltext  key(字段，字段，字段) with  parser  ngram
)
-- 用法
select 字段  from 表名
where match(字段)
against('搜索的关键词')

注意：

        聚簇索引：自增主键的主键索引

        特点：

        1.使用记录主键值的大小进行记录和页的排序

                页内的记录按照主键的大小顺序排成一个单向链表

                各个存放用户记录的页 也是根据用户巨鹿的主键大小顺序拍成一个双向链表

                存放 目录项记录的页，分为不同的层次，在同一层次中的页也是根据野种目录项记录的主键顺序拍成一个双向链表

           2.B+Tree  叶子节点 存储的是完整的用户记录（存储了所有列的值，包括隐藏列）

                数据访问快：因为聚簇索引和数据保存在同一个B+Tree中，因此获取数据比非聚簇快

                聚簇索引对于主键的排序查找和范围查找速度非常快

                按照聚簇索引排列顺序，查询显示一定范围的数据，由于数据紧密相连，数据库不用从多个数据块提取数据，节省了IO操作

        缺点：

                插入速度严重依赖插入顺序，按照主键的顺序插入是最快的方式，否则将会出现页分裂，严重影响性能，因此，对于InnoDB表，我们一般都定义自增的ID为主键

                更新主键的代价很高，因为将会导致更新的行移动，第二次根据主键值找到行数据

        非聚簇索引（辅助索引或二级索引）：在聚簇索引基础上创建的索引。

                回表，以某列大小顺序排序的B+Tree只能确定我们要查找的记录的主键值，所以入股我们想根据某列的值查找完整用户记录，仍然需要 聚簇索引先查主键，这个过程称为回表，再查完整记录。

 创建索引的优化原则

        【强制】：1.InnoDB表必须主键为id  int/bigint  auto——increment 且主键值禁止被更新

                          2.InnoDB和MyISAM存储引擎表，索引类型必须为BTREE

        【建议】：1.主键的名称以pk开头，唯一键以uni或uk开头，普通索引以idx开头，一律小写格式，以字段的名称或缩写作为后缀

                        2.多单词组成的columnname，取前几个单词首字母，加莫单词组成column——nam

                        3.单个表上的索引个数，不能超过6个

                        4.在建立索引时，多考虑联合索引，并把区分度最高的字段放在最前面

                        5.在多表join 的SQL语句里，保证被驱动表的连接列上有索引，这样join执行效率最高

                        6.建表或加索引时，保证表里互相不存在冗余索引，

--- 索引失效：

        1.最佳左前缀法则：

        2.主键插入顺序

        3.计算，函数导致索引失效

        4.类型转换导致索引失效

        5.范围条件右边的列索引失效

        6.不等于（！=或者<>）索引失效

        7.is  null 可以使用索引，is not  null无法使用索引

        8.like 以通配符% 开头索引失效

        9.or 前后存在非索引的列，索引失效

        10.数据库个表的字符集统一使用utf8mb4