MySQL: Clustered Index

Clustered Index is the InnoDB term for a primary key index. 

InnoDB table storage is organized based on the values of the primary key columns, to speed up queries and sorts involving the primary key columns. 

If you do not define a PRIMARY KEY for your table, MySQL locates the first UNIQUE index where all the key columns are NOT NULL and InnoDB uses it as the clustered index.

If the table has no PRIMARY KEY or suitable UNIQUE index, InnoDB internally generates a hidden clustered index on a synthetic column containing row ID values. 

The rows are ordered by the ID that InnoDB assigns to the rows in such a table. The row ID is a 6-byte field that increases monotonically as new rows are inserted.
Thus, the rows ordered by the row ID are physically in insertion order. 

All indexes other than the clustered index are known as secondary indexes.
In InnoDB, each record in a secondary index contains the primary key columns for the row, as well as the columns specified for the secondary index. 

InnoDB uses this primary key value to search for the row in the clustered index.

Physical Strcuture:
All InnoDB indexes are B-trees where the index records are stored in the leaf pages of the tree. 
The default size of an index page is 16KB. 
When new records are inserted, InnoDB tries to leave 1/16 of the page free for future insertions and updates of the index records.

The page size can be specified for all InnoDB tablespaces in a MySQL instance by setting the innodb_page_size configuration option before creating the instance.
 
Once the page size for a MySQL instance is set, you cannot change it.
Supported sizes are 16KB, 8KB, and 4KB, corresponding to the option values 16k, 8k, and 4k.

A MySQL instance using a particular InnoDB page size cannot use data files or log files from an instance that uses a different page size.

MySQL: B-Tree Index

 When any CREATE TABLE is executed, InnoDB internally creates a clustered B-tree index  representing the table. 
If no primary key is explicitly given, internally a primary key is added. 

Each node of B-tree is represented by one page (which is by default is of size 16KB). 


Below is an useful set of info, copied from other blog
(guptavikas.wordpress.com/2012/12/17/b-tree-index-in-mysql/):


B-Tree is the default index for most storage engines in MySql. 
The general idea of a B-Tree is that all the values are stored in order, and each leaf page is the same distance from the root.

A B-Tree index speeds up data access because the storage engine doesn’t have to scan the whole table to find the desired data. 
Instead, it starts at the root node. The slots in the root node hold pointers to child nodes, and the storage engine follows these pointers.

It finds the right pointer by looking at the values in the node pages, which define the upper and lower bounds of the values in the child nodes. 

Eventually, the storage engine either determines that the desired value doesn’t exist or successfully reaches a leaf page. Leaf pages are special, because they have pointers to the indexed data instead of pointers to other pages.

MySQL: InnoDB doublewrite buffer

That's an useful piece of information, so did a copy-paste from MySQL site:

InnoDB uses a novel file flush technique called doublewrite. 

Before writing pages to the data files, InnoDB first writes them to a contiguous area called the doublewrite buffer. Only after the write and the flush to the doublewrite buffer have completed, does InnoDB write the pages to their proper positions in the data file. 

If the operating system crashes in the middle of a page write, InnoDB can later find a good copy of the page from the doublewrite buffer during crash recovery.

Although data is always written twice, the doublewrite buffer does not require twice as much I/O overhead or twice as many I/O operations. Data is written to the buffer itself as a large sequential chunk, with a single fsync() call to the operating system.

To turn off the doublewrite buffer, specify the option innodb_doublewrite=0.

MySQL: SUBSTRING_INDEX - Select Patterns

Consider, a MySQL table having values in a column like below:

SELECT location  FROM geo LIMIT 3;

"location"
"India.Karnataka.Shimoga.Gopala"
"India.Karnataka.Bengaluru.BTM"
"India.Karnataka.Chikmaglore.Koppa"

My requirement is to take only 4th value from each of the rows(such as, Gopala,BTM,Koppa). 

I don't want to display remaining values. 

Its same as what 'cut' command will do in Linux.

For this, we can use SUBSTRING_INDEX function.

SELECT SUBSTRING_INDEX(location,'.',-1)  from geo LIMIT 3;

"location"

"Gopala"

"BTM"

"Koppa"

Syntax: SUBSTRING_INDEX(string,delimiter,count)

Here count means column number based on delimiter. 

Negative value indicates that the column numbers calculated from right side.

So, if I give '-2' instead of  '-1':

SELECT SUBSTRING_INDEX(location,'.',-2)  from geo LIMIT 3;

"location"

"Shimoga.Gopala"

"Bengaluru.BTM"

"Chikmaglore.Koppa"

Linux: awk - Grouping Data in a file

Consider the below file:

# cat test.csv
aa 1 qwer
ab 2 tyui
aa 3 poiu
ab 2 mnb
bb 1 njio
ba 2 njtwe

test.csv  is a tab separated file with 3 columns. 

Here, I want to segregate the whole lines with matching 1st and 2nd columns into separate files. 

Like below:

# cat file_bb_1.csv

bb 1 njio

# cat file_ba_2.csv

ba 2 njtwe

# cat file_ab_2.csv

ab 2 tyui

ab 2 mnb

# cat file_aa_3.csv

aa 3 poiu

# cat file_aa_1.csv

aa 1 qwer

Though you can do this manually, think of a file with more than million lines.

Here, 'awk', being an powerful data manipulation tool,comes to our help. 

Below is the command we can use:

#cat test.csv | awk '{a=$1;b=$2; print $0 >> "file_" a "_" b ".csv"}'

[You can give any name instead of 'file_']

MySQL: Master-Master Circular Replication

While MySQL is one of the simplest RDBMS, it gives vast opportunity to play around!

One of the common question asked in most of the interviews is about Master-Master Circular Replication(means, both servers are master and both are slaves), though its not so practical to be use in a environment where you need high performance.. !!

Here are the brief steps for setting up such a set up..! :

* Install MySQL on both the servers which are our masters

* Configure my.cnf with below options:

server-id = 2 

log_bin = /var/log/mysql/mysql-bin.log

log-slave-updates

[give your convenient values, but make sure server-id is unique]

Note: log-slave-updates-> Normally, a slave does not log to its own binary log any updates that are received from a master server. This option tells the 

slave to log the updates performed by its SQL thread to its own binary log.

This option is must in any chain replication, as well.

* Start MySQL on both the servers and make a note of 'SHOW MASTER STATUS' values.

* Using the above values of each, set the replication parameters on the other 

server. And start slave.

slave stop; 

CHANGE MASTER TO MASTER_HOST = '3.3.3.3', 

MASTER_USER = 'replicator', 

MASTER_PASSWORD = 'password',

MASTER_LOG_FILE = 'mysql-bin.000001',

MASTER_LOG_POS = 107; 

slave start;  

MySQL: Why Server ID ?

In any MySQL replication  set up, server ID will be a common variable seen in my.cnf. Ans usually it will be unique across the servers.

[mysqld]

server-id = 2

But why its so important?

For any normal Master-Slave setup, it doesn't matter whatever the number you set.

However, when you go for a Multi-master or circular replication, its a basic need.

Consider an  example, we have 3 servers A, B and C. Their server ids are accordingly 11, 22 and 33. 

They are replicating in circular manner (multi-master) like A is master of B server, B is master of C server and C is master of A server.

Now when any mysql client will execute insert/update/delete statement on A server, it will be logged in the binary of A server with server id 11.

As that statement logged in binary of A server, it will go to B server (because B is slave of A), executed there and logged in binlog of B server with the same server id 11.  (why same server id? because originally that statement initiated by A server with server id 11 and on B server its executed by sql thread not regular mysql client).

Now again this statement will go to C server (because C is slave of B), executed there and logged in the binlog of C server with the same server id 11.

As C is master for A server, when the statement will come to A server via binlog of C server, sql thread of A server will compare the server id and will find that its same 11 means this statement is originally initiated by the server itself and no need to execute so finally sql thread will skip that statement.

If sever ID wasn't unique, this setup would have entered into  an endless loop state..!