MySQL (Thu)

Database storage Engine What is a storage engine

Library ===> folder created in MySQL

Table ===> files created in the library

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

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
Your own need to write your own storage engine

Two MySQL-supported storage engines

MariaDB [(None)]> show Engines\g  #查看所有支持的存储引擎MariaDB [(none)]> show variables like ' storage_engine% '; View the storage engine in use

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.

Three using the storage engine

Method 1: Specify when the table is built

MariaDB [db1]> CREATE TABLE innodb_t1 (ID int,name char) engine=innodb; MariaDB [db1]> CREATE TABLE innodb_t2 (id int) Engine=innodb; MariaDB [db1]> Show create TABLE innodb_t1; MariaDB [db1]> 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

Four stored procedures

A stored procedure is a collection of SQL statements in which the internal SQL statements are executed logically when the stored procedure is actively invoked.

Create a stored procedure

--Create Stored procedure delimiter//create procedure P1 () BEGIN    select * from T1; End//delimiter;--Execute Stored procedure call P1 ()

Introduction to index one 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!

Two-index syntax to create an index

--When creating 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 Varc HAR (+), resume VARCHAR (+), INDEX index_emp_name (name)--key index_dept_name (Dept_name));--Create Unique Index Example: CREATE TABLE emp2 (id INT, name VARCHAR, Bank_num CHAR) unique, resume VA Rchar, UNIQUE index Index_emp_name (name);--Creating a full-text index example: Create TABLE Emp3 (id INT, NAM e VARCHAR (+), resume VARCHAR (fulltext), index Index_resume (resume);--Creating a multicolumn Index example: Create Tabl        E 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)]  [ASC | DESC]);        ---ALTER TABLE to create an index on an existing table          ALTER TABLE name ADD  [UNIQUE] Index                    index name (field name [(length)]  [ASC | 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;

Three-index test experiment

--Create TABLE indexdb.t1 (ID int,name varchar (20));--stored procedure delimiter $ $create Procedure AutoInsert () begindeclare i int Default 1;while (i<500000) Doinsert into indexdb.t1 values (i, ' yuan '); set i=i+1;end while; end$ $delimiter;--Invoke function call AutoInsert ();--Take time to compare:--Create INDEX before   select * from indexdb.t1 where id=300000;--0.32s--add index C1/>create index index_id on indexdb.t1 (ID);--After creating an index   select * from Indexdb.t1 where id=300000;--0.00s

Pymsql

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

First, download the installation:

?

1	pip3 install pymysql

Second, use

1. Execute SQL?

1234567891011121314151617181920212223242526

#!/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() # 执行SQL，并返回收影响行数effect_row = cursor.execute("update hosts set host = ‘1.1.1.2‘") # 执行SQL，并返回受影响行数#effect_row = cursor.execute("update hosts set host = ‘1.1.1.2‘ where nid > %s", (1,)) # 执行SQL，并返回受影响行数#effect_row = 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()

2. Get the newly created data self-increment ID?

12345678910111213

#!/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() # 获取最新自增IDnew_id = cursor.lastrowid

3. Get query data + View Code?

12345678910111213141516171819

#!/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") # 获取第一行数据row_1 = cursor.fetchone() # 获取前n行数据# row_2 = cursor.fetchmany(3)# 获取所有数据# 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.:

?

123456789101112131415

#!/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=pymysql.cursors.DictCursor)r = cursor.execute("call p1()") result = cursor.fetchone() conn.commit()cursor.close()conn.close()

MySQL (Thu)