CentOS MySQL Optimization 13th session

CentOS MySQL Optimization 13th session

MySQL Index

Primary key value cannot be duplicated, nor can it be null
Unique key + NOT NULL business PRIMARY key with unique index instead of

Case: A InnoDB table, there is no self-increment column primary key, after a period of time, to produce fragments, reorganization of the table space, from 13G into 9G
3 ways to restructure table spaces
ALTER TABLE XX ENGINE=INNODB;
Optimize table xx;
Create primary key on ID plus primary key clustered index

mysql5.6 still unable to add primary key clustered index after the online DDL

Prefix index
Partial index reason
Char/varchar too long to do index, inefficient, there is a waste
The Blob/text type does not support an entire column as an index, so use the prefix index

Statistical averages
Follow 2/8 principles

Syntax: ALTER TABLE T add index K1 (username (9));
The first 9 characters of a username field

Partial index selection Suggestions
Min: 3
Maximum: 15
Average: 8
COUNT (distinct left (x, 9))/Total
COUNT (distinct left (x, 8))/total≈60%
COUNT (distinct left (x, 9))/total≈70%
COUNT (distinct left (x, ten))/total≈85%
COUNT (distinct left (x, one))/total≈95%

The maximum percentage is 80%, which is used as the prefix index

Disadvantages
Unable to complete sorting with prefix index

Show table status; View total number of records in a table
Ye Jinlong (4700963) 21:10:35
Slow log
Init-file

FOREIGN KEY constraints
Create a foreign key for an associated field
All keys must be unique.
Avoid using composite keys
Foreign keys are always associated with unique key fields

Full-Text Indexing
MyISAM engine only supported before 5.6
After 5.5, support InnoDB engine
5.7 ago Chinese support is poor
Preferential use of Lucene\solr\shpinx

Index management

CREATE index idx on T (C1) using BTREE
CREATE index idx on T (C1) using HASH
Heap/memory table to use hash index

Pt-index-usage principle: Check the execution plan of slow log or general log, give advice, just suggest

How the index improves SQL efficiency

Second from the bottom.

Table A is the driver table
b table is the driver table
Then the D field in table A is sorted and indexed, so the index of the D field cannot be used to complete the sorting
A Join B

Artificially specifying the driver table is risky and may result in a different set of results

A. From before
B. Where before
C. Before xxx
D. Before T1
E. After xxx
F. XXX before
G. After T1
G and B
from XXX T1 use index (k1) where

MyISAM Index Features
MyISAM PRIMARY Key Index

MyISAM Secondary Index

InnoDB PRIMARY Key Index

InnoDB Secondary Index

MyISAM and InnoDB Index comparison

MyISAM can use NFS as a shared file system, two instances read a data file at the same time
One instance reads and writes, another instance is read-only, made highly available, MyISAM file can be opened MyISAM file handle by multiple processes

F

CentOS MySQL Optimization 13th session