目录
Welcome to Code Block's blog
本篇文章主要介绍了
[Mysql中的分区和索引]
❤博主广交技术好友，喜欢文章的可以关注一下❤
1.编写目的
在MySQL中，索引和分区是提高查询效率的关键技术。通过创建合适的索引，可以显著提升数据检索速度。而分区可以作为作为进一步提高查询效率的方式,在较大数量据时通常可以使用这两个结合的方式优化查询速度,所以这边将这两个进行整理,巩固个人知识,同时也希望帮助到有需要的朋友。
2.索引
索引分为单列索引、多列索引、唯一索引、全文索引.
2.1 创建方法
单列索引
CREATE INDEX {索引名} ON {表名} ({列名});
多列索引
CREATE INDEX {索引名} ON {表名} ({列名1}, {列名2});
唯一索引
CREATE UNIQUE INDEX {索引名} ON {表名} ({列名});
全文索引
CREATE FULLTEXT INDEX {索引名} ON {表名} ({列名});
2.2 最佳适用
索引不能随意创建,需要选择合适的列,我们应选择常用的查询条件列(即字段在where经常使用的列作为索引)、JOIN 操作列(做左外、右外、全连接时的列)、排序列(作为排序使用的列),频繁更新列应避免添加索引影响性能,同时避免创建过多的索引.若不需要进行全文搜索操作则尽力避免使用全文索引.
2.3 索引相关语句
查询表内索引
SHOW INDEX FROM {表名};
查看查询性能
EXPLAIN SELECT * FROM {表名} WHERE {索引字段} = {值};
删除索引
ALTER TABLE {表名} DROP INDEX {索引名};
3.分区
分区根据基于不同的属性进行分区可分为以下几种:
- RANGE 分区：基于值的范围进行分区
- LIST 分区：基于值的列表进行分区
- HASH 分区：基于哈希值进行分区
- KEY 分区：基于列的键值进行分区
3.1 创建方法
基本语法
CREATE TABLE {表名} ( id INT AUTO_INCREMENT PRIMARY KEY, column1 datatype, column2 datatype, ... ) PARTITION BY {分区类型} ({列名});
RANGE分区
适用于时间范围的分区，可以进行按年份分区:
CREATE TABLE orders ( id INT AUTO_INCREMENT, order_date DATE, amount DECIMAL(10, 2), PRIMARY KEY (id, order_date) ) PARTITION BY RANGE (YEAR(order_date)) ( PARTITION p2020 VALUES LESS THAN (2021), PARTITION p2021 VALUES LESS THAN (2022), PARTITION p2022 VALUES LESS THAN (2023) );
LIST 分区
适用于特定值的分区，可进行按国家、省市区进行分区,如下按国家分区:
CREATE TABLE employees ( id INT AUTO_INCREMENT, name VARCHAR(100), country VARCHAR(50), PRIMARY KEY (id, country) ) PARTITION BY LIST (country) ( PARTITION p_us VALUES IN ('USA'), PARTITION p_uk VALUES IN ('UK'), PARTITION p_ca VALUES IN ('Canada') );
HASH 分区
根据不同字段组成的hash值字段进行分区:
CREATE TABLE products ( id INT AUTO_INCREMENT, product_name VARCHAR(100), category_id INT, PRIMARY KEY (id, category_id) ) PARTITION BY HASH (category_id) PARTITIONS 4;
KEY 分区
根据多个字段进行分区,这类似于根据hash值分区
CREATE TABLE sales ( id INT AUTO_INCREMENT, product_id INT, sale_date DATE, amount DECIMAL(10, 2), PRIMARY KEY (id, product_id) ) PARTITION BY KEY (product_id) PARTITIONS 5;
3.2 最佳适用
在分区时应尽量选择合适的分区类型、分区数量,避免频繁修改分区键的值,进行分区后应使用EXPLAIN分析查询.
如:当我们创建不同区域拥有不同权限的系统时,我们可以使用LIST分区,或者根据区域字段进行hash生成额外字段,并使用hash分区.或者直接使用key分区的方式对区域列进行分区.这样可以有效提升不同区域用户查询速度.
感谢您的关注和收藏!!!!!!
编辑
