11 minutes read (About 1671 words)

Mysql data optimization

Server environment

1
2
3
4
5
6
7
8
9
10
11
12
13
14
$ cat /etc/issue
  Ubuntu 14.04.5 LTS
  
$ free -h
             total       used       free     shared    buffers     cached
Mem:          7.8G       7.7G       132M       260K       276K       6.8M
-/+ buffers/cache:       7.7G       139M
Swap:         8.0G       2.3G       5.7G  
  
$ mysqld -V
mysqld  Ver 5.5.58-0ubuntu0.14.04.1 for debian-linux-gnu on x86_64 ((Ubuntu))

$ php -v
PHP 7.1.12

Fake Data

There is no transaction operate, the table’s engine will use the myisam type.

Create mysql table

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
CREATE TABLE `users` (
  `id` int(10) NOT NULL AUTO_INCREMENT,
  `name` varchar(191) COLLATE utf8mb4_unicode_ci NOT NULL,
  `email` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `emailPrefix` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `password` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `age` int(11) NOT NULL,
  `account` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `address` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `country` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `city` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `streetName` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `streetAddress` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `sex` int(11) NOT NULL,
  `job` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `income` double(8,2) NOT NULL,
  `deposit` int(11) NOT NULL,
  `phone` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `company` varchar(50) COLLATE utf8mb4_unicode_ci NOT NULL,
  `about` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `friendsNum` int(11) NOT NULL,
  `created_at` timestamp NULL DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=MyISAM AUTO_INCREMENT=1000 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Using php script to insert datas

1
2
3
4
$ mkdir fakeData;cd fakeData
$ composer init
$ composer require catfan/Medoo
$ composer require fzaninotto/faker
1
$ vim main.php
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
<?php
require 'vendor/autoload.php';
use Medoo\Medoo;
$faker = Faker\Factory::create();

$database = new Medoo([
    'database_type' => 'mysql',
    'database_name' => 'app',
    'server' => '127.0.0.1',
    'username' => 'root',
    'password' => 'password',
    'port' => '3306',
]);

$emails = [
    "@gmail.com","@qq.com","@163.com","@126.com",
    "@hotmail.com","@outlook.com","@foxmail.com",
    "@sina.com","@sohu.com","@yeah.net",
    "@189.com","@139.com","@21cn.com","@189.com",
];

for ($j = 0; $j < 2050; $j++) {
    try {
        echo date('Y-m-d H:i:s') . PHP_EOL;
        $usersData = [];
        for ($i = 0; $i < 5000; $i++) {
            $username = $faker->name . ' ' . $faker->userName . ' ' . $faker->firstName;
            $emailPrefix = $emails[$faker->numberBetween(0, count($emails) - 1)];
            $email = str_replace([' ', '.'], '_', $username) . $emailPrefix;
            $data = [
                'name' => $username,
                'email' => $email,
                'emailPrefix' => str_replace('@','',$emailPrefix),
                'password' => "123456",
                'age' => $faker->numberBetween(10, 120),   //年龄
                'account' => $faker->firstName . '-' . $faker->name . '-' . $username,   //账户
                'address' => $faker->address,
                'country' => $faker->country,
                'city' => $faker->city,
                'streetName' => $faker->streetName,
                'streetAddress' => $faker->streetAddress,
                'sex' => rand(0, 1),
                'job' => $faker->jobTitle,
                'income' => $faker->numberBetween(3000, 300000),
                'deposit' => $faker->numberBetween(500, 9000000),
                'phone' => $faker->phoneNumber,
                'company' => $faker->companySuffix . ' ' . $faker->company,
                'about' => $faker->text,
                'friendsNum'=>$faker->numberBetween(3, 1000),
                'created_at'=>$faker->dateTimeThisYear('+1 year')->format('Y-m-d H:i:s'),
            ];
            $usersData[] = $data;
        }
        echo date('Y-m-d H:i:s') . PHP_EOL;
        $database->insert("users", $usersData);
        echo date('Y-m-d H:i:s') . PHP_EOL;
        echo PHP_EOL;
    } catch (Exception $e) {
        echo $e->getMessage() . PHP_EOL;
    }
}
echo "END \n";

Check slow log

Open slow query log

1
2
3
4
5
6
7
8
9
10
11
12
mysql> set global slow_query_log=on;
Query OK, 0 rows affected (0.00 sec)

mysql> show variables like 'slow%';
+---------------------+--------------------------------------------------+
| Variable_name       | Value                                            |
+---------------------+--------------------------------------------------+
| slow_launch_time    | 2                                                |
| slow_query_log      | ON                                               |
| slow_query_log_file | /var/lib/mysql/vagrant-ubuntu-trusty-64-slow.log |
+---------------------+--------------------------------------------------+
3 rows in set (0.00 sec)

Execute a query without data index

1
2
3
4
5
6
7
mysql> select count(id) from users where age < 35 and age > 30 and sex = 1 and income > 10000 and country = 'Hong Kong';
+-----------+
| count(id) |
+-----------+
|       744 |
+-----------+
1 row in set (10.06 sec)

Tail the slow query log

1
2
3
4
5
6
7
8
9
10
11
$ tail -f /var/lib/mysql/vagrant-ubuntu-trusty-64-slow.log

Time                 Id Command    Argument
/usr/sbin/mysqld, Version: 5.5.58-0ubuntu0.14.04.1 ((Ubuntu)). started with:
Tcp port: 3306  Unix socket: /var/run/mysqld/mysqld.sock
Time                 Id Command    Argument
# Time: 171217  6:16:14
# User@Host: root[root] @ localhost [127.0.0.1]
# Query_time: 10.053302  Lock_time: 0.000211 Rows_sent: 1  Rows_examined: 10150175
use app;
select count(id) from users where age < 35 and age > 30 and sex = 1 and income > 10000 and country = 'Hong Kong';

Notice this info ‘Lock_time: 0.000211 Rows_sent: 1 Rows_examined: 10150175’
Full scanning the whole table to get one record but locked the table 0.000211

Explain slow SQL query

1
2
3
4
5
6
7
mysql> explain select count(id) from users where age < 35 and age > 30 and sex = 1 and income > 10000 and country = 'Hong Kong';
+----+-------------+-------+------+---------------+------+---------+------+----------+-------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows     | Extra       |
+----+-------------+-------+------+---------------+------+---------+------+----------+-------------+
|  1 | SIMPLE      | users | ALL  | NULL          | NULL | NULL    | NULL | 10150175 | Using where |
+----+-------------+-------+------+---------------+------+---------+------+----------+-------------+
1 row in set (0.00 sec)

Query Cache

Mysql query cache is opened default and the default size is 16M.

1
2
3
$ cat /etc/mysql/my.cnf | grep query_cache
 query_cache_limit     = 1M
 query_cache_size      = 16M
1
2
3
4
5
6
7
8
9
10
11
12
mysql> show variables like '%query_cache%';
+------------------------------+----------+
| Variable_name                | Value    |
+------------------------------+----------+
| have_query_cache             | YES      |
| query_cache_limit            | 1048576  |
| query_cache_min_res_unit     | 4096     |
| query_cache_size             | 16777216 |
| query_cache_type             | ON       |
| query_cache_wlock_invalidate | OFF      |
+------------------------------+----------+
6 rows in set (0.00 sec)

Suitable for very low update frequency and read only high query frequency. Otherwise it is not recommend to use.
Query cache size recommend to maintain with 100M size.
It is strict required two sql to be exactly the same. SQL statement, database protocol version, character set and other factors will affect the results 

1
2
3
mysql> set names latin1; SELECT * FROM table_name;
mysql> set names latin1; select * from table_name;
mysql> set names utf8; select * from table_name;

The most important, query cache is controlled by a global lock in mysql, which will be locked if updating the query cache block.
For example , a query result is 20kb, and current ‘query_cache_min_res_unit’ setting is 4kb(the default value, adjustable), the the query results need to be written in query cache in five times.
It becomes a high costs.

The query cache is better for the reading operations.

Closing methods are simple, there are two way:

  1. set the options query_cache_type = 0 和 query_cache_size = 0；
  2. if compiled with source code MySQL, compiling with parameters –without-query-cache

Create Index

Type Desc
Index Allow to have same index
UNIQUE Index Not allow to have same index, value can be NULL
PRIMARY KEY Not allow to have same index, value can not be NULL, only one primary key in each table
FULLTEXT Index only support in myisam and english language 
1
mysql> ALTER TABLE users ADD INDEX country_age_income (country,age,sex,income);

Re-query above sql

1
2
3
4
5
6
7
mysql> explain select count(id) from users where age < 35 and age > 30 and sex = 1 and income > 10000 and country = 'Hong Kong';
+----+-------------+-------+-------+--------------------+--------------------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys      | key                | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+--------------------+--------------------+---------+------+------+-------------+
|  1 | SIMPLE      | users | range | country_age_income | country_age_income | 770     | NULL | 1025 | Using where |
+----+-------------+-------+-------+--------------------+--------------------+---------+------+------+-------------+
1 row in set (0.02 sec)

You can see the use of index “key=country_age_income”

1
2
3
4
5
6
7
mysql> select count(id) from users where age < 35 and age > 30 and sex = 1 and income > 10000 and country = 'Hong Kong';
+-----------+
| count(id) |
+-----------+
|       744 |
+-----------+
1 row in set (0.52 sec)

Time from 10.06s shortened to 0.52s by index

Optimize SQL query

Please google search base on your business background

Splitting Read operation in slave

  • Using Database Proxy

    Mysql-router
    Kingshard
    360 Atlas
    …

  • Separate in code

    PHP framework can deal with this like Laravel,Symfony etc.
    If you want to do this by yoursef ,the code logic looks like this:

    1
    2
    3
    4
    5
    6
    7
    8
    9
    10
    11
    12
    13
    14
    15
    16
    17
    18
    19
    20
    21
    22
    23
    24
    25
    26
    27
    28
    29
    30
    31
    32
    33
    34
    35
    36
    37
    38
    39
    40
    41
    42
    43
    44
    45
    46
    47
    48
    49
    50
    51
    52
    53
    54
    55
    56
    57
    <?php
    class DB
    {
        private $writeConn;
        private $readConns = [];

        function __construct()
        {
            $this->initWriteConn();
            $this->initReadConns();
        }

        private function initWriteConn()
        {
            $conn = mysql_connect('192.168.0.101:3306', 'root', '111111');
            mysql_select_db('test');
            mysql_query('set names utf8');
            $this->writeConn = $conn;
        }

        private function initReadConns()
        {
            $conn1 = mysql_connect('192.168.0.105:3306', 'root', '111111');
            mysql_select_db('users');
            mysql_query('set names utf8');
            $conn2 = mysql_connect('192.168.0.106:3306', 'root', '111111');
            mysql_select_db('users');
            mysql_query('set names utf8');

            $this->readConns[] = $conn1;
            $this->readConns[] = $conn2;
        }

        public function query($sql)
        {
            $queryStr = trim($sql);
            $queryType = strtolower(substr($queryStr, 0, 6));
            if ($queryType == 'select') {

                $result = $this->readQuery($queryStr);
            } else {
                $result = $this->writeQuery($queryStr);
            }
            return $result;
        }
    }

    $sql1 = "select * from users";
    $sql2 = "insert into users (name) values ('Alice')";
    $sql3 = "delete from users where id = 5";
    $sql4 = "update users set name='Jerry' where id = 4";

    $db = new DB();
    $result1 = $db->query($sql1);
    $result2 = $db->query($sql2);
    $result3 = $db->query($sql3);
    $result4 = $db->query($sql4);

Partition

Please read another blog About Mysql Partition

Vertical Split Table

Based on the relational database column to split table with lots of columns.
Split the filed which will not be used usually or with long length into a extended table.

Horizontal Split Table

According to rules, different data row in the table will be distributed to different database tables
Please read another blog Mysql Table Horizontal Split By Mycat

Vertical Split Database

In accordance with the business module to separate different databases

Meet some errors

  • Cannot allocate memory
    1
    2
    3
    4
    5
    6
    7
    $ php artisan db:seed

    mmap() failed: [12] Cannot allocate memory
    mmap() failed: [12] Cannot allocate memory
    PHP Fatal error:  Out of memory (allocated 2160070656) (tried to allocate 2147483656 bytes) in /home/liang/learnlaravel5/vendor/laravel/framework/src/Illuminate/Database/Eloquent/FactoryBuilder.php on line 194
    In FactoryBuilder.php line 194:                                                          
      Out of memory (allocated 2160070656) (tried to allocate 2147483656 bytes)
    Increase your system swap, here i will add 8G swap, adjust according to your actual situation!
1
2
3
$ dd if=/dev/zero of=/mnt/swapfile bs=1M count=8192
$ mkswap /mnt/swapfile
$ swapon /mnt/swapfile
This problem still appears. I'm using Laravel to create a lot of mysql recode and these data are created in memory.
To fix this problem, code in Laravel seed must be changed.
  • Maximum retries of 10000 reached without finding a unique value
    When using faker mock the email data, it seems that the max unique email number is 10 million.

Mysql Command Tips

show columns: 

1
mysql> desc {TABLE_NAME};

update table name: 

1
mysql> alter table {OLD_TABLE_NAME} rename to {NEW_TABLE_NAME};

add column:

1
mysql> alter table {TABLE_NAME} add column {COLUMN_NAME} varchar(30);

drop column:

1
mysql> alter table {TABLE_NAME} drop column {COLUMN_NAME};

update column property:

1
mysql> alter table {TABLE_NAME} modify name varchar(22);

show table size:

1
mysql> select concat(round(sum(DATA_LENGTH/1024/1024),2),'MB') as data from information_schema.TABLES WHERE TABLE_SCHEMA='{DATABASE_NAME}' and TABLE_NAME = '{TABLE_NAME}';

update database default character:

1
mysql> ALTER DATABASE {DATABASE_NAME} CHARACTER SET = utf8mb4 COLLATE = utf8mb4_unicode_ci;
# Mysql, SlowLog, Ubuntu

Thank you for reading.
This post is copyrighted by Liyuliang’s Blog.
If reproduced, please indicate the source: Liyuliang’s Blog
This blog uses Creative Commons Attribution-NonCommercial-Share-Sharing 4.0 International License Agreement to license.

Build new mysql slave without stop service or locked table by Xtrabackup
Mysql Table Horizontal Split By Mycat

Archives

Tag Cloud

Alipay Apache Baidu Canal Centos CloudDisk Daemon Elasticsearch Filebeat Glide Iterm JQuery Keepalived Kibana Linux Login Logstash Mac Mongodb Mycat Mysql NFS Nginx Nodejs PHP Pagespeed Partition Redis Replica Security Slave SlowLog Symfony TCP Twig Ubuntu Varnish Vim Virtualbox Wordpress auth backup beanstalkd binlog blog brew composer cookie css dante docker domain download file fork git github gitlab golang homestead iptable laravel log nginx proxy redis-cluster redis-sentinel sock5 supervisor sync tips tmux usb vagrant web
Your browser is out-of-date!

Update your browser to view this website correctly. Update my browser now

×