MySQL学习-索引的使用

最新推荐文章于 2023-08-26 18:14:25 发布

什巳

最新推荐文章于 2023-08-26 18:14:25 发布

阅读量1.1k

点赞数 1

分类专栏： MySQL学习文章标签： MySQL 学习索引索引类型优化

本文链接：https://blog.csdn.net/qq_48772498/article/details/124123208

版权

MySQL学习专栏收录该内容

15 篇文章 3 订阅

订阅专栏

一.常见索引类型

1.主键索引

主键索引是一种特殊的唯一索引，不允许有空值。

ALTER TABLE `table_name` ADD PRIMARY KEY(`column`);

主键索引采用有以下原则：

采用一个 没有业务用途 的自增属性列作为主键。
主键字段值总是不更新，只有新增或者删除两种操作。
不选择会动态更新的类型。

2.普通索引

最基本的索引，没有任何限制。

ALTER TABLE `table_name` ADD INDEX `index_name`(`column`);
CREATE UNIQUE INDEX `index_name` ON `table_name`(`column`);

3.覆盖索引

覆盖索引简单来说就是只查询索引就能获取到数据不必再回表查询，换句话说要查询的列已经被索引列覆盖。

CREATE INDEX `index_name` ON `table_name` (`column1`,`column2`)

使用覆盖索引有如下优点：

索引项通常比记录要小，所以 MySQL 访问更少的数据；
索引都按值的大小顺序存储，相对于随机访问记录，需要 更少的 I/O；
大多数据引擎能更好的 缓存索引。比如 MyISAM 只缓存索引;
覆盖索引 对于 InnoDB 表尤其有用，因为 InnoDB 使用聚集索引组织数据，如果二级索引中包含查询所需的数据，就不再需要在聚集索引中查找了。
覆盖索引 不能是任何索引，只有 B Tree 索引存储相应的值。而且不同的存储引擎实现覆盖索引的方式都不同，并不是所有存储引擎都支持覆盖索引( Memory 和 Falcon 就不支持)。

4.唯一索引

它与前面的普通索引类似，不同的就是：索引列的值必须唯一，但允许有空值。如果是联合索引，则列值的组合必须唯一。

CREATE UNIQUE INDEX `index_name` ON `table_name` (`column`);
ALTER TABLE `table_name` ADD UNIQUE INDEX `index_name`(`column`)

5.联合索引

有的时候我们会对多个列建立一个索引，这种索引被称为联合索引。而关于联合索引的建立和使用需要严格要遵循 “最左匹配原则”。

最左匹配原则：

若不是按照索引最左边列开始查找，无法使用索引。
不能跳过索引中的列，否则只能使用部分索引。
如果查询中有某个列的范围查询，则右边的列无法用到索引优化。

6.二级索引

二级索引就是指除了主键索引外的索引。主键索引和所有的二级索引都是各自维护各自的 B+ 树结构，但是有个不同的地方在于，二级索引的叶子节点存储的不是数据，而是主键索引对应的主键值。

即二级索引不再保存一份 data 数据，而是去主键索引中查数据。那么对于二级索引查找一条数据索要做的操作就是：

首先在二级索引中找到叶子节点对应的数据主键值；
根据这个主键值去聚集索引中找到真正对应的数据行。

所以这里需要两次 B+ Tree 查找。

二.索引优化

1.Explain优化查询检测

EXPLAIN可以帮助开发人员分析SQL问题，explain显示了mysql如何使用索引来处理select语句以及连接表，可以帮助选择更好的索引和写出更优化的查询语句。

使用方法，在select语句前加上Explain就可以了：

Explain SELECT * FROM `table_name` WHERE 条件表达式;

mysql在执行一条查询之前，会对发出的每条SQL进行分析，决定是否使用索引或全表扫描如果发送一条select * from blog where false，Mysql是不会执行查询操作的，因为经过SQL分析器的分析后MySQL已经清楚不会有任何语句符合操作。

2.如何创建主键

使用数据库自增ID作为主键
通过程序自助生成ID作为主键
不使用业务相关字段作为主键，例如电话号码，身份证号，银行卡号等等。

3.如何创建二级索引

分析需要查询的内容和条件，选择合适的索引方式（单个索引，联合索引，覆盖索引等）。
低区分度的字段不建议创建索引，即使创建了也不会使用，例如性别这种只有男女区分的字段。

4.索引字段进行运算

不在索引列上做任何操作（计算、函数、（自动 or 手动）类型转换），会导致索引失效而转向全表扫描

5.索引查询

能查索引就查索引不要查数据，数据比索引大
尽量使用覆盖索引，少用select *。
避免使用%开头的模糊匹配。
谨慎使用count，大数据量情况下count耗时高，容易导致慢sql

6.limit分页优化

语法： ELECT * FROM TABLE LIMIT [offset,] rows 或者 rows OFFSET offset

SELECT * FROM TABLE LIMIT 5,10; //检索记录行6-15
SELECT * FROM TABLE LIMIT 10 OFFSET 5;

SELECT * FROM TABLE LIMIT 95,-1; //检索记录行96-last
SELECT * FROM TABLE LIMIT 95 OFFSET -1;

SELECT * FROM TABLE LIMIT 5; //检索前5个记录行

6.1 不同offset大小使用方式

1.offset较小时

SELECT * FROM TABLE LIMIT 5,10;

直接使用limit即可，其他优化方案效果差异不大

2.offset较大时

SELECT * From TABLE WHERE id >=(  
SELECT id From TABLE ORDER BY id LIMIT 10000,1;
) limit 10;

offset较大时，使用这种查询方案效率会高！这是用了id做索引的结果。

用id作为数据表的主键：

SELECT id FROM TABLE LIMIT 10000,10;

效率没有因为offset而参数太大影响，因为主键作为索引结果。

分页数据性能优化：

对于数据量较大数据表，可以建立主键和索引字段建立索引表，通过索引表查询相应的主键，在通过主键查询数据量的数据表；
如果对于有where 条件，又想走索引用limit的，必须设计一个索引，将where 放第一位，limit用到的主键放第2位，而且只能select 主键！这样能提高读取速度
利用in：先通过where条件取得相应的主键值，然后利用主键值查询相应的字段值。

本文章整理自上课ppt，部分来自于网上，仅供学习使用。