logo

MySQL

MySQL is the top open-source db on DB-Engines, also proved its scalability at Facebook, and Uber migrated from Postres to MySQL. MariaDB is a fork of MySQL that is NOT controlled by Oracle.

  • Save index in memory to speed up search
  • The default engine is InnoDB (MyISAM before 5.5.5). MyISAM no longer under active development. Another option is MyRocks, which is based on Facebook's RocksDB. To list all the engines: mysql> SHOW ENGINES;
  • In most cases, only one storage engine will be needed. Sometimes you may need to have different storage engines for different tables in the same database.

Index Types

  • primary: unique, one per table.
  • unique: unique, no two rows have the same combination of the values.
  • index: may not be unique, but improves lookup efficiency.
  • fulltext: for full text search, by creating an index for each word in that column.

Data Structure:

  • PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT are stored in B+trees.
  • spatial data types use R-trees;
  • MEMORY tables also support hash indexes; choose B-tree for MEMORY tables if some queries use range operators.
  • InnoDB uses inverted lists for FULLTEXT indexes.

Auth:

  • MySQL is moving to x509 for authentication, away from username + pw

Index Types

  • primary: must be unique, is an index, is (likely) the physical index, can be only one per table.
  • unique: can't have more than one row with a tuple of this value. Note that since a unique key can be over more than one column, this doesn't necessarily mean that each individual column in the index is unique, but that each combination of values across these columns is unique.
  • index: if it's not primary or unique, it doesn't constrain values inserted into the table, but it does allow them to be looked up more efficiently.
  • fulltext: allows full text search. Essentially creating an "index" for each "word" in the specified column.

Data Structure:

  • PRIMARY KEY, UNIQUE, INDEX, and FULLTEXT are stored in B+trees.
  • spatial data types use R-trees;
  • MEMORY tables also support hash indexes; choose B-tree for MEMORY tables if some queries use range operators.
  • InnoDB uses inverted lists for FULLTEXT indexes.

Auth

MySQL is moving to x509 for authentication, away from username + pw

Engines

https://en.wikipedia.org/wiki/Comparison_of_MySQL_database_engines

To show all the options:

mysql> SHOW ENGINES;
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| Engine             | Support | Comment                                                        | Transactions | XA   | Savepoints |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+
| MRG_MYISAM         | YES     | Collection of identical MyISAM tables                          | NO           | NO   | NO         |
| CSV                | YES     | CSV storage engine                                             | NO           | NO   | NO         |
| InnoDB             | DEFAULT | Supports transactions, row-level locking, and foreign keys     | YES          | YES  | YES        |
| BLACKHOLE          | YES     | /dev/null storage engine (anything you write to it disappears) | NO           | NO   | NO         |
| MEMORY             | YES     | Hash based, stored in memory, useful for temporary tables      | NO           | NO   | NO         |
| PERFORMANCE_SCHEMA | YES     | Performance Schema                                             | NO           | NO   | NO         |
| ARCHIVE            | YES     | Archive storage engine                                         | NO           | NO   | NO         |
| MyISAM             | YES     | MyISAM storage engine                                          | NO           | NO   | NO         |
| FEDERATED          | NO      | Federated MySQL storage engine                                 | NULL         | NULL | NULL       |
+--------------------+---------+----------------------------------------------------------------+--------------+------+------------+

InnoDB is the default option. In most cases, only one storage engine will be needed. Sometimes you may need to have different storage engines for different tables in the same database.

InnoDB

The default option in MySQL 5.7, InnoDB is a robust storage engine that offers:

  • Full ACID compliance.
  • Commit, rollback, and crash-recovery.
  • Row-level locking.
  • FOREIGN KEY referential-integrity constraints.
  • Increase multi-user concurrency (via non-locking reads).

It is an engine that performs well and offers many of the required attributes that any database would need. This is the engine that will most likely be used in the majority of applications.

MyISAM

The functionality that sets MyISAM apart is its capability for:

  • Full-text search indexes.
  • Table-level locking.
  • Lack of support for transactions.

Though it is a fast storage engine, it is best suited for use in read-heavy and mostly read applications such as data warehousing and web applications that don’t need transaction support or ACID compliance.

NDB (or NDBCLUSTER)

If a clustered environment is where your database will be working, NDB is the storage engine of choice. It is best when you need:

  • Distributed computing.
  • High-redundancy.
  • High-availability.
  • The highest possible uptimes.

Take note that support for NDB is not included in the distribution of standard MySQL Server 5.7 binaries. You will have to update to latest binary release of MySQL Cluster. Though, if you’re developing in a cluster environment, you probably have the necessary experience to deal with these tasks.

CSV

A useful storage engine when data needs to be shared with other applications that use CSV formatted data. The tables are stored as comma separated value text files. Though this makes sharing the data with scripts and applications easier, one drawback is that the CSV files are not indexed. So, the data should be stored in an InnoDB table until the Import/Export stage of the process.

Blackhole

This engine accepts but does not store data. Similar to the UNIX /dev/null, queries always return an empty set. This can be useful in a distributed database environment where you do not want to store data locally or in performance or other testing situations.

Archive

Just as the name implies, this engine is excellent for seldom-referenced historical data. The tables are not indexed and compression happens upon insert. Transactions are not supported. Use this storage engine for archiving and retrieving past data.

Federated

This storage engine is for creating a single, local, logical database by linking several different physical MySQL servers. No data is stored on the local server and queries are automatically executed on the respective remote server. It is perfect for distributed data mart environments and can vastly improve performance when using MySQL for analytical reporting.

Trouble Shooting

Error:

Another MySQL daemon already running with the same unix socket.
Starting mysqld:                                           [FAILED]

Cause:

Unexpectedly shutdown the daemon, mysql.sock is till there.

Solution:

Manually remove mysql.sock file.

$ sudo mv /var/lib/mysql/mysql.sock /var/lib/mysql/mysql.sock.bak

$ sudo /sbin/service mysqld start

Install

Install from apt-get:

$ sudo apt-get install mysql-server

Start mysql command line:

$ mysql -u root -p

MySQL Compatible

TiDB

An open-source MySQL compatible NewSQL database that supports Hybrid Transactional and Analytical Processing (HTAP) workloads.

SingleStore

MySQL wire-compliant distributed RDBMS. F.k.a. MemSQL.

Schemaless(Uber's)

Write only

  • row key: UUID, equivalent to “primary key"
  • column key: string
  • ref key: version number(the highest is the latest)

https://eng.uber.com/schemaless-part-one/

logo
PRIVACY POLICYABOUT中文