随笔录--索引种类

索引种类

以下是Python代码示例，介绍常见的索引种类及其适用场景：

1. 主键索引

主键索引是针对表中的主键字段构建的索引，用于唯一标识表中的每一行数据。在Python中，可以使用SQLAlchemy库来创建主键索引。例如：

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base

engine = create_engine('mysql+pymysql://user:password@host:port/database')
Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String(50))
    age = Column(Integer)

Base.metadata.create_all(engine)

2. 唯一索引

唯一索引确保索引列中的所有值都是唯一的，但允许空值。在Python中，可以使用SQLAlchemy库来创建唯一索引。例如：

from sqlalchemy import create_engine, Column, Integer, String
from sqlalchemy.orm import sessionmaker
from sqlalchemy.ext.declarative import declarative_base
from sqlalchemy.schema import UniqueConstraint

engine = create_engine('mysql+pymysql://user:password@host:port/database')
Base = declarative_base()

class User(Base):
    __tablename__ = 'users'
    id = Column(Integer, primary_key=True)
    name = Column(String(50), unique=True)
    age = Column(Integer)

    __table_args__ = (UniqueConstraint('name', 'age'),)

Base.metadata.create_all(engine)

3. 聚簇索引

聚簇索引是指定了数据存储的物理顺序，表中的行按照聚簇索引的顺序存储。在Python中，可以使用MySQL命令来创建聚簇索引。例如：

import pymysql

connection = pymysql.connect(host='localhost',
                             user='root',
                             password='password',
                             database='database',
                             cursorclass=pymysql.cursors.DictCursor)

with connection.cursor() as cursor:
    sql = '''ALTER TABLE users ADD PRIMARY KEY (id) CLUSTERED'''
    cursor.execute(sql)

connection.commit()

4. 非聚簇索引

非聚簇索引（或称辅助索引）即普通的索引结构，它们并不改变表中数据的物理顺序，而是单独维护索引和数据的映射关系。在Python中，可以使用MySQL命令来创建非聚簇索引。例如：

import pymysql

connection = pymysql.connect(host='localhost',
                             user='root',
                             password='password',
                             database='database',
                             cursorclass=pymysql.cursors.DictCursor)

with connection.cursor() as cursor:
    sql = '''CREATE INDEX idx_name ON users (name)'''
    cursor.execute(sql)

connection.commit()

5. 复合索引

复合索引是针对多个列构建的索引，用于加快多列条件的查询速度。在Python中，可以使用MySQL命令来创建复合索引。例如：

import pymysql

connection = pymysql.connect(host='localhost',
                             user='root',
                             password='password',
                             database='database',
                             cursorclass=pymysql.cursors.DictCursor)

with connection.cursor() as cursor:
    sql = '''CREATE INDEX idx_name_age ON users (name, age)'''
    cursor.execute(sql)

connection.commit()

这些索引种类和数据结构在实际数据库设计和优化中起着非常重要的作用，可以根据具体业务需求来选择合适的索引类型和数据结构。

适用场景

不同类型的索引和数据结构在不同的场景下具有不同的优势，根据具体业务需求来选择合适的索引类型和数据结构是非常重要的。下面列举一些常见的场景和适用的索引类型：

1. 主键索引

主键索引适用于需要唯一标识每行数据的表，例如用户表、订单表等。

2. 唯一索引

唯一索引适用于需要保证某个字段值唯一的表，例如用户表中的手机号、邮箱等字段。

3. 聚簇索引

聚簇索引适用于需要按照特定顺序存储数据的表，例如订单表按照创建时间排序。

4. 非聚簇索引

非聚簇索引适用于需要快速查询某个字段值的表，例如用户表按照姓名查询。

5. 复合索引

复合索引适用于需要按照多个字段查询的表，例如订单表按照用户ID和创建时间查询。

需要注意的是，创建过多的索引会增加数据库的存储空间和维护成本，同时也可能降低性能。因此，在使用索引时需要权衡利弊，并根据具体情况进行调整。