Database: Database engine, Index, Pymysql (iv)

I. Database storage engine 1, storage engine

Library ===> folder created in MySQL

Table ===> files created in the library

In real life, the files we use to store the data should have different types: for example, TXT type for storing text, Excel for Save form, PNG for image, and so on.

Tables in the database should also have different types, with different types of tables, and different access mechanisms for MySQL, and the table type is also known as the storage engine.

The storage engine plainly is how to store the data, how to index the stored data, and how to update, query the data and other technologies to implement the
Method. Because the storage of data in a relational database is stored as a table, the storage engine can also be called a table type (that is, storage and
Manipulate the type of this table).

There is only one storage engine in a database such as Oracle and SQL Server, and all data storage management mechanisms are the same. and MySQL
The database provides a variety of storage engines. Users can choose different storage engines for their data tables according to different requirements, and users can also
Own the need to write your own storage engine.

2. mysql-supported storage engine

Mysql> show Engines\g;  #查看所有支持的存储引擎mysql'%storage_engine%';   #查看正在使用的存储引擎

MySQL's common storage engine

MyISAM Storage Engine
Because the storage engine does not support transactions and does not support foreign keys, access is faster. Therefore, it is appropriate to use the storage engine when there is no requirement for transactional integrity and an access-oriented application.

InnoDB storage engine (primarily used )
Because the storage engine has a transactional advantage, which supports transactional features such as commit, rollback, and crash resiliency, it consumes more disk space than the MyISAM storage engine. Therefore, when frequent updates and deletions are required and the integrity of the transaction is high, concurrency control needs to be implemented, and the recommended choice is to do so.

MEMORY
The memory storage engine stores data in a location that is RAM and therefore accesses the fastest, but is not guaranteed to be secure. Suitable for fast access or temporary tables.

Blackhole
A black hole storage engine that can be applied to the distribution Master library in primary and standby replication.

3. Using the storage engine

Method 1: Specify when the table is built

int Char) engine=innodb;mysqlint) engine=innodb;mysql> Show CREATE TABLE Innodb_t1;mysql > Show CREATE TABLE innodb_t2;

Method 2: Specify the default storage engine in the configuration file

/etc/my.cnf[mysqld]default-storage-engine=innodbinnodb_file_per_table=1

View

[Email protected] db1]# CD/var/lib/mysql/db1/[[email protected] db1]# lsdb.opt  innodb_t1.frm  INNODB_T1.IBD  innodb_t2.frm  innodb_t2.ibd

Second, the index1. Introduction to the Index

The index, also called a "key" in MySQL, is a data structure used by the storage engine to quickly find records. Indexes are critical to good performance, especially when the amount of data in a table is increasing, and the impact of indexes on performance becomes increasingly important.

Index optimization should be the most effective means of optimizing query performance.
Indexes can easily improve query performance by several orders of magnitude.
The index is equivalent to the dictionary's Sequencer list, if you want to check a word, if you do not use a sequencer, you need to check from page hundreds of.

Index features: Creating and maintaining indexes can consume a lot of time and disk space, but query speed is greatly improved!

2. Index syntax

Create an index

--when you create a table--Syntax: CREATE table table name (field name 1 data type [integrity constraint ...], field name 2 data type [integrity constraint ...], [ UNIQUE] INDEX|KEY [index name] (field name [(length)] [ASC|DESC]) );----------------------------------Create a normal index example: Create TABLE emp1 (id INT, name VARCHAR ( -), Resume VARCHAR ( -), INDEX index_emp_name (name)--KEY Index_dept_name (dept_name));--Create a unique index example: Create TABLE emp2 (id INT, name VARCHAR ( -), Bank_num CHAR ( -) UNIQUE, resume VARCHAR ( -), UNIQUE INDEX index_emp_name (name));--Create a full-text index example: Create TABLE Emp3 (id INT, name VARCHAR ( -), Resume VARCHAR ( -), Fulltext INDEX Index_resume (Resume));--example of creating a multicolumn index: Create TABLE emp4 (id INT, name VARCHAR ( -), Resume VARCHAR ( -), INDEX Index_name_resume (Name,resume));---------------------------------

Adding and removing indexes

--- add an    index ---create an index on an existing table creation  [UNIQUE] index  index name on              table name (field name [(length)]  | DESC]) ;         ---ALTER TABLE to create an index on an existing table          ALTER TABLE name ADD  [UNIQUE] Index                    index name (field name [(length)]  |  DESC]);              CREATE INDEX index_emp_name on EMP1 (name);      ALTER TABLE emp2 ADD UNIQUE INDEX index_bank_num (band_num); -- Delete index        syntax: DROP index name on table name        DROP index index_emp_name on EMP1;    DROP INDEX bank_num on EMP2;

3. Index Test Experiment

--CREATE TABLE indexdb.t1 (IDint, Name varchar ( -));--stored procedure delimiter $ $create Procedure AutoInsert () Begindeclare iint default 1; while(i<500000) DoINSERT into INDEXDB.T1 values (i,'Yuan');Seti=i+1; end while; end$ $delimiter;--calling Function call AutoInsert ();--spend time comparing:--before you create an indexSelect* fromIndexdb.t1whereId=300000;--2. 42s--Add index CREATE INDEX index_id on indexdb.t1 (ID);--After you create an indexSelect* fromIndexdb.t1whereId=300000;--0.09s

Third, Pymysql

Pymysql is a module that operates MySQL in Python and is used almost the same way as MySQLdb.

First, download the installation:

PIP3 Install Pymysql

Second, use 1, execute SQL

#!/usr/bin/Env python#-*-coding:utf-8-*-Import Pymysql # Create connection conn= Pymysql.connect (host='127.0.0.1', port=3306, user='Root', passwd='123', db='T1') # Create cursors cursor=Conn.cursor () # Executes SQL and returns the number of affected rows Effect_row= Cursor.execute ("Update hosts set host = ' 1.1.1.2 '"# Executes SQL and returns the number of rows affected #effect_row= Cursor.execute ("Update hosts set host = ' 1.1.1.2 ' where nid >%s", (1,)) # executes SQL and returns the number of rows affected #effect_row= Cursor.executemany ("INSERT INTO hosts (host,color_id) VALUES (%s,%s)", [("1.1.1.11",1),("1.1.1.11",2]) # Submit, otherwise unable to save new or modified Data conn.commit () # Close Cursor cursor.close () # Close Connection conn.close ()

2. Get the newly created data self-increment ID

#!/usr/bin/Env python#-*-coding:utf-8-*-Import Pymysql Conn= Pymysql.connect (host='127.0.0.1', port=3306, user='Root', passwd='123', db='T1') Cursor=conn.cursor () Cursor.executemany ("INSERT INTO hosts (host,color_id) VALUES (%s,%s)", [("1.1.1.11",1),("1.1.1.11",2)]) Conn.commit () Cursor.close () Conn.close () # Get the latest auto-increment idnew_id= Cursor.lastrowid

3. Get Query data

#!/usr/bin/Env python#-*-coding:utf-8-*-Import Pymysql Conn= Pymysql.connect (host='127.0.0.1', port=3306, user='Root', passwd='123', db='T1') Cursor=conn.cursor () cursor.execute ("SELECT * from hosts") # Gets the first row of data row_1=Cursor.fetchone () # Gets the first n rows of data # Row_2= Cursor.fetchmany (3) # Get all data # Row_3=Cursor.fetchall () conn.commit () Cursor.close () Conn.close ( )

Note: In order to fetch data, you can use Cursor.scroll (Num,mode) to move the cursor position, such as:

• Cursor.scroll (1,mode= ' relative ') # moves relative to the current position
• Cursor.scroll (2,mode= ' absolute ') # relative absolute position movement

4. Fetch data type

The data that is obtained by default is the Ganso type, if desired or the dictionary type of data, i.e.:

#!/usr/bin/Env python#-*-coding:utf-8-*-Import Pymysql Conn= Pymysql.connect (host='127.0.0.1', port=3306, user='Root', passwd='123', db='T1') # Cursor set to dictionary type cursor= Conn.cursor (cursor=pymysql.cursors.DictCursor) R= Cursor.execute ("Call P1 ()") Result=Cursor.fetchone () conn.commit () Cursor.close () Conn.close ( )

Database: Database engine, Index, Pymysql (iv)