Mysql python实现（三）

关于索引

索引是提高数据库性能的重要方式，mysql中的所有数据类型都可以被索引

一、索引概述

1. mysql索引概述
   使用索引好处：提高查询速度、降低服务器负载
   不足：占用物理空间，给数据维护造成麻烦
   如果操作多为插入操作，尽量不要使用索引
2. mysql索引分类
   普通索引、唯一性索引（unique retrieval）、全文索引（fulltext retrieval）、单列索引、多列索引、空间索引（spatial retrieval）

二、创建索引

声明：以下截图是所有都运行一遍的样子

1.在创建表时创建普通索引

#首先导入pymysql包，连接服务器
import pymysql

# 连接数据库服务器并指定连接db_1数据库
db = pymysql.connect('localhost','root','111','db_1')

cursor = db.cursor()

# 在创建表时创建普通索引
sql1 = "create table score( \
        id int(11) auto_increment primary key not null, \
        name varchar(50) not null, \
        math int(5) not null, \
        english int(5) not null, \
        chinese int(5) not null, \
        index(id) );" 

# 查看索引
sql2 = "show create table score;"
cursor.execute(sql1)
cursor.execute(sql2)
result = cursor.fetchall()
print(result)

#图片倒数第二行 KEY ‘id’ （‘id’）表示索引创建成功

2. 创建唯一性索引

sql1 = "create table address( \
        id int(11) auto_increment primary key not null, \
        name varchar(50) not null, \
        address varchar(200), \
        unique index address(id asc) );" 

# 查看索引
sql2 = "show create table address;"
cursor.execute(sql1)
cursor.execute(sql2)
result = cursor.fetchall()
print(result)
# 有时候唯一性索引 

3. 创建全文索引

## 全文索引只能在char varchar text类型的字段上创建
## 只有MyISAM类型的数据表支持FULLTEXT全文索引
sql1 = "create table cards(\
        id int(11) auto_increment primary key not null,\
        name varchar(50), \
        number bigint(11),\
        info varchar(50), \
        fulltext key cards_info(info))engine=MyISAM;"
## 查看索引
sql2 = "show create table cards;"
cursor.execute(sql1)
cursor.execute(sql2)
result = cursor.fetchall()
print(result)

4. 创建单列索引

## 在数据表的单个字段上创建索引
sql1 = "create table telephone( \
        id int(11) auto_increment primary key not null,\
        name varchar(50) not null, \
        tel varchar(50) not null, \
        index tel_num(tel(20)));"
## 查看索引
sql2 = "show create table telephone;"
cursor.execute(sql1)
cursor.execute(sql2)
result = cursor.fetchall()
print(result)

5. 创建多列索引

sql1 = "create table information(\
        id int(11) auto_increment primary key not null, \
        name varchar(50) not null, \
        sex varchar(50) not null, \
        birthday varchar(50) not null, \
        index info(name,sex));"
cursor.execute(sql1)
sql2 = "show create table information;"
cursor.execute(sql2)
result=cursor.fetchall()
print(result)

6. 创建空间索引

## 只有MyISAM类型表支持空间索引，而且索引字段必须有非空约束
sql1 = "create table list(\
        id int(11) primary key not null,\
        goods geometry not null,\
        spatial index listinfo(goods))engine=MyISAM;"
cursor.execute(sql1)
sql2 = "show create table list;"
cursor.execute(sql2)
result=cursor.fetchall()
print(result)
# geometry是空间数据类型（还有point、linestring、polygon），空间数据类型不能用其他类型代替

三、 在已建立的数据表中创建索引

1. 创建普通索引

   sql1 = "create index stu_info on score(english);"
    cursor.execute(sql1)
    sql2 = "show create table score;"
    cursor.execute(sql2)
    result=cursor.fetchall()
    print(result)
   

2. 创建唯一性索引

# create index 索引名 on 表名(字段名)
sql1 = "create unique index address_info on address(name)"
cursor.execute(sql1)
sql2 = "show create table address;"
cursor.execute(sql2)
result=cursor.fetchall()
print(result)

3. 创建全文索引

sql1 = "create fulltext index cards_info1 on cards(name);"
cursor.execute(sql1)
sql2 = "show create table cards;"
cursor.execute(sql2)
result=cursor.fetchall()
print(result)

4. 创建单列索引

sql1 = "create index telephone_info on telephone(name);"
cursor.execute(sql1)
sql2 = "show create table telephone;"
cursor.execute(sql2)
result=cursor.fetchall()
print(result)

5. 创建多列索引

sql1 = "create index information_info on information(id,birthday);"
cursor.execute(sql1)
sql2 = "show create table information;"
cursor.execute(sql2)
result=cursor.fetchall()
print(result)

6. 创建空间索引

# 注意：创建的表的时候必须是MyISAM类型，否则无法正常创建空间索引
sql3 = "create table list2(\
        id int(11) not null,\
        goods geometry not null,\
        price int (10) not null)engine=MyISAM;"
cursor.execute(sql3)
sql1 = "create spatial index list2_info on list2(goods);"
cursor.execute(sql1)
sql2 = "show create table list2;"
cursor.execute(sql2)
result=cursor.fetchall()
print(result)

四、修改数据表结构添加索引（在表上创建索引的另一种方式）

1添加普通索引

sql1 = "alter table studentinfo add index timer(time(20));"
sql2 = "create table studentinfo(\
        sid int(11) not null auto_increment primary key,\
        name varchar(50) not null,\
        age varchar(11) not null,\
        sex varchar(2) not null default 'M',\
        tel bigint(11) not null,\
        time varchar(50) not null)engine=MyISAM;"
cursor.execute(sql2)
cursor.execute(sql1)
sql3 = "show create table studentinfo"
cursor.execute(sql3)
result = cursor.fetchall()
print(result)

2. 添加唯一性索引

sql1 = "alter table studentinfo add unique index s_1(sid);"
cursor.execute(sql1)
sql2 = "show create table studentinfo;"
cursor.execute(sql2)
result=cursor.fetchall()
print(result)

3. 添加全文索引

sql1 = "alter table studentinfo add fulltext index s_4(age);"
cursor.execute(sql1)
sql2 = "show create table studentinfo;"
cursor.execute(sql2)
result=cursor.fetchall()
print(result)

4. 添加单列索引

sql1 = "alter table studentinfo add index s_2(tel);"
cursor.execute(sql1)
sql2 = "show create table studentinfo;"
cursor.execute(sql2)
result=cursor.fetchall()
print(result)

5. 添加多列索引

sql1 = "alter table studentinfo add index s_3(name,sex);"
cursor.execute(sql1)
sql2 = "show create table studentinfo;"
cursor.execute(sql2)
result=cursor.fetchall()
print(result)

6. 添加空间索引

sql1 = "create table info(\
        sid int(11) not null auto_increment primary key,\
        name varchar(50) not null,\
        age varchar(11) not null,\
        birth geometry not null)engine=MyISAM;"
cursor.execute(sql1)
sql2 = "alter table info add spatial index info_1(birth);"
cursor.execute(sql2)
sql3 = "show create table info;"
cursor.execute(sql3)
result=cursor.fetchall()
print(result)

五、删除索引

sql1 = "drop index info_1 on info;"
sql2 = "show create table info;"
cursor.execute(sql1)
cursor.execute(sql2)
result = cursor.fetchall()
print(result)

# 删除成功，该表中不存在索引info_1