innodb_rollback_on_timeout参数对锁的影响

hanchangyue 发表了文章 • 3 个评论 • 1253 次浏览 • 2016-12-21 10:51 • 来自相关话题

前提提要:
MySQL版本:5.6.21-log MySQL Community Server (GPL)
innodb_rollback_on_timeout是啥作用?
答:事务B在锁等待超时后是回滚事务内所有的statement还是最后一条语句;
0表示rollback最后一条语句,默认值;有点坑
1表示回滚事务B内所有的statements;
此参数是只读参数,需在my.cnf中配置,并且重启生效;
注意:回滚statements后不自动commit或rollback事务;坑表结构:mysql> show create table t12\G;
*************************** 1. row ***************************
Table: t12
Create Table: CREATE TABLE `t12` (
`a` int(10) unsigned NOT NULL AUTO_INCREMENT,
`b` varchar(766) DEFAULT NULL,
`c` int(11) DEFAULT NULL,
PRIMARY KEY (`a`),
KEY `b` (`b`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=latin1
row in set (0.00 sec)实验一:
innodb_rollback_on_timeout开启的情况下,锁等待超时后,该事务所持有锁会一并释放;参数配置:
mysql> show variables like 'innodb_rollback_on_timeout';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| innodb_rollback_on_timeout | ON |
+----------------------------+-------+
row in set (0.00 sec)

mysql> show variables like 'tx_iso%';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| tx_isolation | REPEATABLE-READ |
+---------------+-----------------+
row in set (0.00 sec)

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
row in set (0.00 sec)过程:


实验二:innodb_rollback_on_timeout关闭的情况下,锁等待超时后事务持有的锁不会释放;参数配置:
mysql> show variables like 'innodb_rollback_on_timeout';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| innodb_rollback_on_timeout | OFF |
+----------------------------+-------+
row in set (0.00 sec)

mysql> show variables like 'tx_iso%';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| tx_isolation | REPEATABLE-READ |
+---------------+-----------------+
row in set (0.00 sec)

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
row in set (0.00 sec)过程:


总结:1、关闭innodb_rollback_on_timeout后,一旦以begin;start transaction;等语句开启一个事务,当锁等待超时后,该事务请求的锁将不释放,直到事务提交或回滚或会话超时;

所以autocommit参数建议设置成ON,只要程序没有显示开启事务,就可以避免上述锁未释放问题。

2、开启innodb_rollback_on_timeout后,一旦锁等待超时,是事务内sql将全部回滚,且释放之前请求的锁。

3、当autocommit=on,只要不显示开启事务,将不存在上面2个问题,即锁的问题和回滚的问题。
  查看全部
前提提要:
MySQL版本:5.6.21-log MySQL Community Server (GPL)
 
innodb_rollback_on_timeout是啥作用?
答:事务B在锁等待超时后是回滚事务内所有的statement还是最后一条语句;
0表示rollback最后一条语句,默认值;有点坑
1表示回滚事务B内所有的statements;
此参数是只读参数,需在my.cnf中配置,并且重启生效;
注意:回滚statements后不自动commit或rollback事务;坑
表结构:
mysql> show create table t12\G;
*************************** 1. row ***************************
Table: t12
Create Table: CREATE TABLE `t12` (
`a` int(10) unsigned NOT NULL AUTO_INCREMENT,
`b` varchar(766) DEFAULT NULL,
`c` int(11) DEFAULT NULL,
PRIMARY KEY (`a`),
KEY `b` (`b`)
) ENGINE=InnoDB AUTO_INCREMENT=16 DEFAULT CHARSET=latin1
row in set (0.00 sec)
实验一:
innodb_rollback_on_timeout开启的情况下,锁等待超时后,该事务所持有锁会一并释放;
参数配置:
mysql> show variables like 'innodb_rollback_on_timeout';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| innodb_rollback_on_timeout | ON |
+----------------------------+-------+
row in set (0.00 sec)

mysql> show variables like 'tx_iso%';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| tx_isolation | REPEATABLE-READ |
+---------------+-----------------+
row in set (0.00 sec)

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
row in set (0.00 sec)
过程:


实验二:innodb_rollback_on_timeout关闭的情况下,锁等待超时后事务持有的锁不会释放;
参数配置:
mysql> show variables like 'innodb_rollback_on_timeout';
+----------------------------+-------+
| Variable_name | Value |
+----------------------------+-------+
| innodb_rollback_on_timeout | OFF |
+----------------------------+-------+
row in set (0.00 sec)

mysql> show variables like 'tx_iso%';
+---------------+-----------------+
| Variable_name | Value |
+---------------+-----------------+
| tx_isolation | REPEATABLE-READ |
+---------------+-----------------+
row in set (0.00 sec)

mysql> show variables like 'autocommit';
+---------------+-------+
| Variable_name | Value |
+---------------+-------+
| autocommit | ON |
+---------------+-------+
row in set (0.00 sec)
过程:


总结:1、关闭innodb_rollback_on_timeout后,一旦以begin;start transaction;等语句开启一个事务,当锁等待超时后,该事务请求的锁将不释放,直到事务提交或回滚或会话超时;

所以autocommit参数建议设置成ON,只要程序没有显示开启事务,就可以避免上述锁未释放问题。

2、开启innodb_rollback_on_timeout后,一旦锁等待超时,是事务内sql将全部回滚,且释放之前请求的锁。

3、当autocommit=on,只要不显示开启事务,将不存在上面2个问题,即锁的问题和回滚的问题。
 

mysql 误删,误更新数据恢复 脚本,基于row格式binlog

earl86 发表了文章 • 4 个评论 • 926 次浏览 • 2016-01-18 14:54 • 来自相关话题

mysql 误删,误更新数据恢复 脚本,基于row格式binlog
恢复时需要指定-s columnsize(字段行数) -t update/delete(恢复类型)
update语句需要手动更新colunmlist的值(字段列表)
python脚本如下:
 #!/usr/bin/python
#coding=utf-8
#Author:earl86
#version 1.0
#the script for 'delete from xxxtable;' and 'update xxxtable set yyycol='',xxxcol='';'
#mysqlbinlog -v --base64-output=decode-rows --start-position=6908 --stop-position=8900 mysql-bin.000001 >mysql-bin.000001.sql
#how to use:python sqlconvert.py -f mysql-bin.000001.sql -s columnsize -t update/delete
#need to modify the value of colunmlist
#update sql need customed by yourself


import os
import string
import sys
import argparse

reload(sys)
sys.setdefaultencoding('utf8')

parser = argparse.ArgumentParser()
parser.add_argument("-f", action="store", dest='sqlfile', help="input the sql file dir and file name", required=True)
parser.add_argument("-s", action="store", dest='colnum', help="input the Number of columns", required=True)
parser.add_argument("-t", action="store", dest='sqltype', help="input the convert sql type.update or delete", required=True)
args = parser.parse_args()



def updatereadlines():
    result = list()
    with open(args.sqlfile, 'r') as f:
        f = f.readlines()
        for line in f:
            if line.startswith('### '):
                if line.startswith('### UPDATE '):
                    #print result
                    convertupdatesql(result)
                    result =
                result.append(line.strip('\n').replace('### ',' '))
        #print result
        convertupdatesql(result)

def updatereadline():
    result = list()
    f = open(args.sqlfile,'r')
    for line in open(args.sqlfile):  
        line = f.readline()  
        if line.startswith('### '):
            if line.startswith('### UPDATE '):
                #print result
                convertupdatesql(result)
                result =
            result.append(line.strip('\n').replace('### ',' '))  
    #print result
    convertupdatesql(result)
    f.close()

def convertupdatesql(result):
     if (len(result) > 0):
         sql = result[0] + result[int(args.colnum)+2] + result[8].replace('@7','local_backup_status') + result[1] + result[2].replace('@1','id') + ';' 
         #print sql
         open('result-update.sql', 'a').write(sql+'\n')  

def deletereadlines(colunmlist):
    result = list()
    with open(args.sqlfile, 'r') as f:
        f = f.readlines()
        for line in f:
            if line.startswith('### '):
                if line.startswith('### DELETE '):
                    #print result
                    convertdeletesql(result,colunmlist)
                    result =
                result.append(line.strip('\n').replace('### ',' '))
        #print result
        convertdeletesql(result,colunmlist)

def deletereadline(colunmlist):
    result = list()
    f = open(args.sqlfile,'r')
    for line in open(args.sqlfile):
        line = f.readline()
        if line.startswith('### '):
            if line.startswith('### DELETE '):
                #print result
                convertdeletesql(result,colunmlist)
                result =
            result.append(line.strip('\n').replace('### ',' '))
    #print result
    convertdeletesql(result,colunmlist)
    f.close()

def convertdeletesql(result,colunmlist):
     if (len(result) > 0):
         sql =  result[0].replace('DELETE FROM','INSERT INTO') + ' SET'
         for i in range(2,int(args.colnum)+2):
             if (i == int(args.colnum)+1):
                 sql = sql + result.replace('@' + str(i-1),colunmlist[i-2]) + ';'
             else:
                 sql = sql + result[i].replace('@' + str(i-1),colunmlist[i-2]) + ','
         #print sql
         open('result-delete.sql', 'a').write(sql+'\n')


    
if __name__ == '__main__':
    colunmlist =('id','backup_time','host_name','service_name','file_name','file_md5','local_backup_status','remote_backup_status','backup_user')
    if ('delete' == args.sqltype):
        try:
            os.remove('result-delete.sql')
        except:
            pass 
        deletereadline(colunmlist)
    elif ('update' == args.sqltype): 
        try:
            os.remove('result-update.sql')
        except:
            pass
        updatereadline()[/i]
  查看全部
mysql 误删,误更新数据恢复 脚本,基于row格式binlog
恢复时需要指定-s columnsize(字段行数) -t update/delete(恢复类型)
update语句需要手动更新colunmlist的值(字段列表)
python脚本如下:
 #!/usr/bin/python
#coding=utf-8
#Author:earl86
#version 1.0
#the script for 'delete from xxxtable;' and 'update xxxtable set yyycol='',xxxcol='';'
#mysqlbinlog -v --base64-output=decode-rows --start-position=6908 --stop-position=8900 mysql-bin.000001 >mysql-bin.000001.sql
#how to use:python sqlconvert.py -f mysql-bin.000001.sql -s columnsize -t update/delete
#need to modify the value of colunmlist
#update sql need customed by yourself


import os
import string
import sys
import argparse

reload(sys)
sys.setdefaultencoding('utf8')

parser = argparse.ArgumentParser()
parser.add_argument("-f", action="store", dest='sqlfile', help="input the sql file dir and file name", required=True)
parser.add_argument("-s", action="store", dest='colnum', help="input the Number of columns", required=True)
parser.add_argument("-t", action="store", dest='sqltype', help="input the convert sql type.update or delete", required=True)
args = parser.parse_args()



def updatereadlines():
    result = list()
    with open(args.sqlfile, 'r') as f:
        f = f.readlines()
        for line in f:
            if line.startswith('### '):
                if line.startswith('### UPDATE '):
                    #print result
                    convertupdatesql(result)
                    result =
                result.append(line.strip('\n').replace('### ',' '))
        #print result
        convertupdatesql(result)

def updatereadline():
    result = list()
    f = open(args.sqlfile,'r')
    for line in open(args.sqlfile):  
        line = f.readline()  
        if line.startswith('### '):
            if line.startswith('### UPDATE '):
                #print result
                convertupdatesql(result)
                result =
            result.append(line.strip('\n').replace('### ',' '))  
    #print result
    convertupdatesql(result)
    f.close()

def convertupdatesql(result):
     if (len(result) > 0):
         sql = result[0] + result[int(args.colnum)+2] + result[8].replace('@7','local_backup_status') + result[1] + result[2].replace('@1','id') + ';' 
         #print sql
         open('result-update.sql', 'a').write(sql+'\n')  

def deletereadlines(colunmlist):
    result = list()
    with open(args.sqlfile, 'r') as f:
        f = f.readlines()
        for line in f:
            if line.startswith('### '):
                if line.startswith('### DELETE '):
                    #print result
                    convertdeletesql(result,colunmlist)
                    result =
                result.append(line.strip('\n').replace('### ',' '))
        #print result
        convertdeletesql(result,colunmlist)

def deletereadline(colunmlist):
    result = list()
    f = open(args.sqlfile,'r')
    for line in open(args.sqlfile):
        line = f.readline()
        if line.startswith('### '):
            if line.startswith('### DELETE '):
                #print result
                convertdeletesql(result,colunmlist)
                result =
            result.append(line.strip('\n').replace('### ',' '))
    #print result
    convertdeletesql(result,colunmlist)
    f.close()

def convertdeletesql(result,colunmlist):
     if (len(result) > 0):
         sql =  result[0].replace('DELETE FROM','INSERT INTO') + ' SET'
         for i in range(2,int(args.colnum)+2):
             if (i == int(args.colnum)+1):
                 sql = sql + result.replace('@' + str(i-1),colunmlist[i-2]) + ';'
             else:
                 sql = sql + result[i].replace('@' + str(i-1),colunmlist[i-2]) + ','
         #print sql
         open('result-delete.sql', 'a').write(sql+'\n')


    
if __name__ == '__main__':
    colunmlist =('id','backup_time','host_name','service_name','file_name','file_md5','local_backup_status','remote_backup_status','backup_user')
    if ('delete' == args.sqltype):
        try:
            os.remove('result-delete.sql')
        except:
            pass 
        deletereadline(colunmlist)
    elif ('update' == args.sqltype): 
        try:
            os.remove('result-update.sql')
        except:
            pass
        updatereadline()
[/i]
 

MySQL优化索引-----定位需要优化的索引( 我的SQL支持5.6,5.7,mariadb。另下面附了姜总原文中的SQL支持5.6外的环境)

huzilin 发表了文章 • 17 个评论 • 1449 次浏览 • 2016-01-18 10:33 • 来自相关话题

前些日子在姜承尧姜总那里看到他公众号分享了个索引优化,他给了个SQL说这个SQL5.6不好使,让大家来写一个5.6能使用的,然后给了一些信息。然后我就写了下面的sql,姜总说这个SQL在SELECTIVITY是0.1左右的都需要关注了。

下面这个SQL后来请吴总帮忙看了下,说是没有问题,给了个意见,然后我按照吴总的建议修改了下现在只输出SELECTIVITY<=0.4的行。

SELECT t.TABLE_SCHEMA, t.TABLE_NAME, INDEX_NAME, s.CARDINALITY, t.TABLE_ROWS
    , s.CARDINALITY / t.TABLE_ROWS AS SELECTIVITY
FROM information_schema.TABLES t, (SELECT t1.database_name, t1.table_name, t1.index_name, t2.stat_value AS CARDINALITY
    FROM (SELECT database_name, table_name, index_name, MAX(substring(stat_name, 11)) AS max_stat_name
        FROM mysql.innodb_index_stats
        WHERE stat_name LIKE 'n_diff_pfx%'
        GROUP BY database_name, table_name, index_name
        ) t1, mysql.innodb_index_stats t2, (SELECT database_name, table_name, group_concat(index_name) AS all_indexs
        FROM mysql.innodb_index_stats
        WHERE stat_name LIKE 'n_diff_pfx%'
        GROUP BY database_name, table_name
        ) t3
    WHERE t2.database_name = t1.database_name
        AND t2.table_name = t1.table_name
        AND t2.index_name = t1.index_name
        AND t2.stat_name LIKE 'n_diff_pfx%'
        AND t2.database_name = t3.database_name
        AND t2.table_name = t3.table_name
        AND t3.all_indexs REGEXP '^PRIMARY,'
        AND t2.index_name != 'PRIMARY'
        AND substring(t2.stat_name, 11) = t1.max_stat_name - 1
    UNION ALL
    SELECT t1.database_name, t1.table_name, t1.index_name, t2.stat_value AS CARDINALITY
    FROM (SELECT database_name, table_name, index_name, MAX(substring(stat_name, 11)) AS max_stat_name
        FROM mysql.innodb_index_stats
        WHERE stat_name LIKE 'n_diff_pfx%'
        GROUP BY database_name, table_name, index_name
        ) t1, mysql.innodb_index_stats t2, (SELECT database_name, table_name, group_concat(index_name) AS all_indexs
        FROM mysql.innodb_index_stats
        WHERE stat_name LIKE 'n_diff_pfx%'
        GROUP BY database_name, table_name
        ) t3
    WHERE t2.database_name = t1.database_name
        AND t2.table_name = t1.table_name
        AND t2.index_name = t1.index_name
        AND t2.stat_name LIKE 'n_diff_pfx%'
        AND t2.database_name = t3.database_name
        AND t2.table_name = t3.table_name
        AND t3.all_indexs REGEXP '^PRIMARY'
        AND t2.index_name = 'PRIMARY'
        AND substring(t2.stat_name, 11) = t1.max_stat_name
    UNION ALL
    SELECT t1.database_name, t1.table_name, t1.index_name, t2.stat_value AS CARDINALITY
    FROM (SELECT database_name, table_name, index_name, MAX(substring(stat_name, 11)) AS max_stat_name
        FROM mysql.innodb_index_stats
        WHERE stat_name LIKE 'n_diff_pfx%'
        GROUP BY database_name, table_name, index_name
        ) t1, mysql.innodb_index_stats t2, (SELECT database_name, table_name, group_concat(index_name) AS all_indexs
        FROM mysql.innodb_index_stats
        WHERE stat_name LIKE 'n_diff_pfx%'
        GROUP BY database_name, table_name
        ) t3
    WHERE t2.database_name = t1.database_name
        AND t2.table_name = t1.table_name
        AND t2.index_name = t1.index_name
        AND t2.stat_name LIKE 'n_diff_pfx%'
        AND t2.database_name = t3.database_name
        AND t2.table_name = t3.table_name
        AND t3.all_indexs NOT REGEXP '^PRIMARY'
        AND t2.index_name != 'PRIMARY'
        AND substring(t2.stat_name, 11) = t1.max_stat_name
    ) s
WHERE t.table_schema = s.database_name
    AND t.table_name = s.table_name
    AND t.table_rows != 0
    AND t.table_schema NOT IN ('mysql', 'performance_schema', 'information_schema')
HAVING SELECTIVITY <=0.4
ORDER BY SELECTIVITY ;

里面的3个子查询分别是1)表有主键 当前匹配的索引不是主键 2)表有主键 当前匹配索引是主键 3)当前表没有主键
 
这里再贴下姜总的SQL吧,姜总说这个SQL在5.6中因为information_schema.STATISTICS统计的信息有BUG索引无法得出准确的结果。但是支持其他的版本。
SELECT 
     t.TABLE_SCHEMA,t.TABLE_NAME,INDEX_NAME, CARDINALITY, 
    TABLE_ROWS, CARDINALITY/TABLE_ROWS AS SELECTIVITY
FROM
    information_schema.TABLES t,
 (
  SELECT table_schema,table_name,index_name,cardinality
  FROM information_schema.STATISTICS 
  WHERE (table_schema,table_name,index_name,seq_in_index) IN (
  SELECT table_schema,table_name,index_name,MAX(seq_in_index)
  FROM information_schema.STATISTICS
  GROUP BY table_schema , table_name , index_name )
 ) s
WHERE
    t.table_schema = s.table_schema 
        AND t.table_name = s.table_name AND t.table_rows != 0
        AND t.table_schema NOT IN ( 'mysql','performance_schema','information_schema') 
ORDER BY SELECTIVITY;

----------------------------------------------------------------
关于会有SELECTIVITY列大于1的情况
我是这么理解的 information_schema.TABLES这个表不是实时跟新,这个表相对更新慢,如果行数少,比如查出在某一个时刻基数是5对应当时的行数是5, 但是如果information_schema.TABLES收集的那个时间点,有4条还未插入,就可能会导致计算的结果是5。我做过尝试,当看到超过1的行的时候,稍后再执行,那一行一般都会变成另一个数值。
如果理解有误请勘正,或者还有其他的理解希望大家也能分享讨论下。共同提高。 查看全部
前些日子在姜承尧姜总那里看到他公众号分享了个索引优化,他给了个SQL说这个SQL5.6不好使,让大家来写一个5.6能使用的,然后给了一些信息。然后我就写了下面的sql,姜总说这个SQL在SELECTIVITY是0.1左右的都需要关注了。

下面这个SQL后来请吴总帮忙看了下,说是没有问题,给了个意见,然后我按照吴总的建议修改了下现在只输出SELECTIVITY<=0.4的行。

SELECT t.TABLE_SCHEMA, t.TABLE_NAME, INDEX_NAME, s.CARDINALITY, t.TABLE_ROWS
    , s.CARDINALITY / t.TABLE_ROWS AS SELECTIVITY
FROM information_schema.TABLES t, (SELECT t1.database_name, t1.table_name, t1.index_name, t2.stat_value AS CARDINALITY
    FROM (SELECT database_name, table_name, index_name, MAX(substring(stat_name, 11)) AS max_stat_name
        FROM mysql.innodb_index_stats
        WHERE stat_name LIKE 'n_diff_pfx%'
        GROUP BY database_name, table_name, index_name
        ) t1, mysql.innodb_index_stats t2, (SELECT database_name, table_name, group_concat(index_name) AS all_indexs
        FROM mysql.innodb_index_stats
        WHERE stat_name LIKE 'n_diff_pfx%'
        GROUP BY database_name, table_name
        ) t3
    WHERE t2.database_name = t1.database_name
        AND t2.table_name = t1.table_name
        AND t2.index_name = t1.index_name
        AND t2.stat_name LIKE 'n_diff_pfx%'
        AND t2.database_name = t3.database_name
        AND t2.table_name = t3.table_name
        AND t3.all_indexs REGEXP '^PRIMARY,'
        AND t2.index_name != 'PRIMARY'
        AND substring(t2.stat_name, 11) = t1.max_stat_name - 1
    UNION ALL
    SELECT t1.database_name, t1.table_name, t1.index_name, t2.stat_value AS CARDINALITY
    FROM (SELECT database_name, table_name, index_name, MAX(substring(stat_name, 11)) AS max_stat_name
        FROM mysql.innodb_index_stats
        WHERE stat_name LIKE 'n_diff_pfx%'
        GROUP BY database_name, table_name, index_name
        ) t1, mysql.innodb_index_stats t2, (SELECT database_name, table_name, group_concat(index_name) AS all_indexs
        FROM mysql.innodb_index_stats
        WHERE stat_name LIKE 'n_diff_pfx%'
        GROUP BY database_name, table_name
        ) t3
    WHERE t2.database_name = t1.database_name
        AND t2.table_name = t1.table_name
        AND t2.index_name = t1.index_name
        AND t2.stat_name LIKE 'n_diff_pfx%'
        AND t2.database_name = t3.database_name
        AND t2.table_name = t3.table_name
        AND t3.all_indexs REGEXP '^PRIMARY'
        AND t2.index_name = 'PRIMARY'
        AND substring(t2.stat_name, 11) = t1.max_stat_name
    UNION ALL
    SELECT t1.database_name, t1.table_name, t1.index_name, t2.stat_value AS CARDINALITY
    FROM (SELECT database_name, table_name, index_name, MAX(substring(stat_name, 11)) AS max_stat_name
        FROM mysql.innodb_index_stats
        WHERE stat_name LIKE 'n_diff_pfx%'
        GROUP BY database_name, table_name, index_name
        ) t1, mysql.innodb_index_stats t2, (SELECT database_name, table_name, group_concat(index_name) AS all_indexs
        FROM mysql.innodb_index_stats
        WHERE stat_name LIKE 'n_diff_pfx%'
        GROUP BY database_name, table_name
        ) t3
    WHERE t2.database_name = t1.database_name
        AND t2.table_name = t1.table_name
        AND t2.index_name = t1.index_name
        AND t2.stat_name LIKE 'n_diff_pfx%'
        AND t2.database_name = t3.database_name
        AND t2.table_name = t3.table_name
        AND t3.all_indexs NOT REGEXP '^PRIMARY'
        AND t2.index_name != 'PRIMARY'
        AND substring(t2.stat_name, 11) = t1.max_stat_name
    ) s
WHERE t.table_schema = s.database_name
    AND t.table_name = s.table_name
    AND t.table_rows != 0
    AND t.table_schema NOT IN ('mysql', 'performance_schema', 'information_schema')
HAVING SELECTIVITY <=0.4
ORDER BY SELECTIVITY ;

里面的3个子查询分别是1)表有主键 当前匹配的索引不是主键 2)表有主键 当前匹配索引是主键 3)当前表没有主键
 
这里再贴下姜总的SQL吧,姜总说这个SQL在5.6中因为information_schema.STATISTICS统计的信息有BUG索引无法得出准确的结果。但是支持其他的版本。
SELECT 
     t.TABLE_SCHEMA,t.TABLE_NAME,INDEX_NAME, CARDINALITY, 
    TABLE_ROWS, CARDINALITY/TABLE_ROWS AS SELECTIVITY
FROM
    information_schema.TABLES t,
 (
  SELECT table_schema,table_name,index_name,cardinality
  FROM information_schema.STATISTICS 
  WHERE (table_schema,table_name,index_name,seq_in_index) IN (
  SELECT table_schema,table_name,index_name,MAX(seq_in_index)
  FROM information_schema.STATISTICS
  GROUP BY table_schema , table_name , index_name )
 ) s
WHERE
    t.table_schema = s.table_schema 
        AND t.table_name = s.table_name AND t.table_rows != 0
        AND t.table_schema NOT IN ( 'mysql','performance_schema','information_schema') 
ORDER BY SELECTIVITY;

----------------------------------------------------------------
关于会有SELECTIVITY列大于1的情况
我是这么理解的 information_schema.TABLES这个表不是实时跟新,这个表相对更新慢,如果行数少,比如查出在某一个时刻基数是5对应当时的行数是5, 但是如果information_schema.TABLES收集的那个时间点,有4条还未插入,就可能会导致计算的结果是5。我做过尝试,当看到超过1的行的时候,稍后再执行,那一行一般都会变成另一个数值。
如果理解有误请勘正,或者还有其他的理解希望大家也能分享讨论下。共同提高。

开发人员应该知道的索引原理

Fred 发表了文章 • 4 个评论 • 943 次浏览 • 2015-12-30 15:55 • 来自相关话题

作者:黄湘龙
架构师交流群(312254004)
欢迎非商业转载,商业使用请联系我 
 

索引是有效使用数据库的基础,但你的数据量很小的时候,或许通过扫描整表来存取数据的性能还能接受,但当数据量极大时,当访问量极大时,就一定需要通过索引的辅助才能有效地存取数据。一般索引建立的好坏是性能好坏的成功关键。

1.InnoDb数据与索引存储细节

使用InnoDb作为数据引擎的Mysql和有聚集索引的SqlServer的数据存储结构有点类似,虽然在物理层面,他们都存储在Page上,但在逻辑上面,我们可以把数据分为三块:数据区域,索引区域,主键区域,他们通过主键的值作为关联,配合工作。默认配置下,一个Page的大小为16K。

一个表数据空间中的索引数据区域中有很多索引,每一个索引都是一颗B+Tree,在索引的B+Tree中索引的值作为B+Tree的节点的Key,数据主键作为节点的Value。

在InnoDB中,表数据文件本身就是按B+Tree组织的一个索引结构,这棵树的叶节点数据域保存了完整的数据记录。这个索引的key是数据表的主键,因此InnoDB表数据文件本身就是主键索引。这种索引也叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集,所以InnoDB要求表必须有主键(MyISAM可以没有),如果没有显式指定,则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键,如果不存在这种列,则MySQL自动为InnoDB表生成一个隐含字段作为主键,这个字段长度为6个字节,类型为长整形。

 表数据都以Row的形式放在一个一个大小为16K的Page中,在每个数据Page中有都页头信息和一行一行的数据。其中页头信息中主要放置的是这一页数据中的所有主键值和其对应的OFFSET,便于通过主键能迅速找到其对应的数据位置。






2.索引优化检索的原理

索引是数据库的灵魂,如果没有索引,数据库也就是一堆文本文件,存在的意义并不大。索引能让数据库成几何倍数地提高检索效率。使用Innodb作为数据引擎的Mysql数据库的索引分为聚集索引(也就是主键)和普通索引。上节我们已经讲解了这两种索引的存储结构,现在我们仔细讲解下索引是如何工作的。

聚集索引和普通索引都有可能由多个字段组成,我们又称这种索引为复合索引,1.2.3将为大家解析这种索引的性能情况.

2.1聚集索引
从上节我们知晓,Innodb的所有数据是按照聚集索引排序的,聚集索引这种存储方式使得按主键的搜索十分高效,如果我们SQL语句的选择条件中有聚集索引,数据库会优先使用聚集索引来进行检索工作。
数据库引擎根据条件中的主键的值,迅速在B+Tree中找到主键对应的叶节点,然后把叶节点所在的Page数据库读取到内存,返回给用户,如上图绿色线条的流向。下面我们来运行一条SQL,从数据库的执行情况分析一下:

select * from UP_User where userId = 10000094;

......

# Query_time: 0.000399  Lock_time: 0.000101  Rows_sent: 1  Rows_examined: 1  Rows_affected: 0

# Bytes_sent: 803  Tmp_tables: 0  Tmp_disk_tables: 0  Tmp_table_sizes: 0

# InnoDB_trx_id: 1D4D

# QC_Hit: No  Full_scan: No  Full_join: No  Tmp_table: No  Tmp_table_on_disk: No

# Filesort: No  Filesort_on_disk: No  Merge_passes: 0

#   InnoDB_IO_r_ops: 0  InnoDB_IO_r_bytes: 0  InnoDB_IO_r_wait: 0.000000

#   InnoDB_rec_lock_wait: 0.000000  InnoDB_queue_wait: 0.000000

#   InnoDB_pages_distinct: 2

SET timestamp=1451104535;

select * from UP_User where userId = 10000094;



我们可以看到,数据库读从磁盘取了两个Page就把809Bytes的数据提取出来返回给客户端。
下面我们试一下,如果试一下选取条件没有包含主键和索引的情况:

select * from `UP_User` where bigPortrait = '5F29E883BFA8903B';

# Query_time: 0.002869 Lock_time: 0.000094 Rows_sent: 1 Rows_examined: 1816 Rows_affected: 0

# Bytes_sent: 792 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0

# QC_Hit: No Full_scan: Yes Full_join: No Tmp_table: No Tmp_table_on_disk: No

# InnoDB_pages_distinct: 25


可以看到如果使用主键作为检索条件,检索时间花了0.3ms,只读取了两个Page,而不使用主键作为检索条件,检索时间花了2.8ms,读取了25个Page,全局扫描才把这条记录给找出来。这还是一个只有1000多行的表,如果更大的数据量,对比更加强烈。

 对于这两个Page,一个是主键B+Tree的数据,一个是10000094这条数据所在的数据页。


2.2普通索引
我们用普通索引作为检索条件来搜索数据需要检索两遍索引:首先检索普通索引获得主键,然后用主键到主索引中检索获得记录。如上图红色线条的流向。
下面我用一个例子来看看数据库的表现:

 select * from UP_User where userName = 'fred';# Query_time: 0.000400 Lock_time: 0.000101 Rows_sent: 1 Rows_examined: 1 Rows_affected: 0

# Bytes_sent: 803 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0

# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No

# InnoDB_pages_distinct: 4


 我们可以看到数据库用了0.4ms来检索这条数据,读取了4个page,比用主键作为检索条件多用了0.1ms,多读取了两个Page,而这两个Page就是userName这个普通索引的B+Tree所在的数据页。


2.3复合索引

聚集索引和普通索引都有可能由多个字段组成,多个字段组成的索引的叶节点的Key由多个字段的值按照顺序拼接而成。这种索引的存储结构是这样的,首先按照第一个字段建立一棵B+Tree,叶节点的Key是第一个字段的值,叶节点的value又是一棵小的B+Tree,逐级递减。对于这样的索引,用排在第一的字段作为检索条件才有效提高检索效率。排在后面的字段只能在排在他前面的字段都在检索条件中的时候才能起辅助效果。下面我们用例子来说明这种情况。

我们在UP_User表上建立一个用来测试的复合索引,建立在 (`nickname`,`regTime`)两个字段上,下面我们测试下检索性能:

 select * from UP_User where nickName='fredlong'; 
# Query_time: 0.000443 Lock_time: 0.000101 Rows_sent: 1 Rows_examined: 1 Rows_affected: 0
# Bytes_sent: 778 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No 
# InnoDB_pages_distinct: 4 


我们看到索引起作用了,和普通索引的效果一样都用了0.43ms,读取了四个Page就完成了任务。

 select * from UP_User where regTime = '2015-04-27 09:53:02'; 
# Query_time: 0.007076 Lock_time: 0.000286 Rows_sent: 1 Rows_examined: 1816 Rows_affected: 0 
# Bytes_sent: 803 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: Yes Full_join: No Tmp_table: No Tmp_table_on_disk: No 
# InnoDB_pages_distinct: 26 


从这次选择的执行情况来看,虽然regTime在刚才建立的复合索引中,还是做了全局扫描。因为这个复合索引排在regTime字段前面的nickname字段没有出现在选择条件中,所以这个索引我们没用用到。
那么我们什么情况下会用到复合索引呢。我一般在两种情况下会用到:
 需要复合索引来排重的时候。 用索引的第一个字段选取出来的结果不够精准,需要第二个字段做进一步的性能优化。

 我基本上没有建立过三个以上的字段做复合索引,如果出现这种情况,我觉的你的表设计可能出现了大而全的问题,需要在表设计层面调优,而不是通过增加复杂的索引调优。所有复杂的东西都是大概率有问题的。

3.批量选择的效率
我们业务经常这样的诉求,选择某个用户发送的所有消息,这个帖子所有回复内容,所有昨天注册的用户的UserId等。这样的诉求需要从数据库中取出一批数据,而不是一条数据,数据库对这种请求的处理逻辑稍微复杂一点,下面我们来分析一下各种情况。
3.1根据主键批量检索数据
我们有一张表PW_Like,专门存储Feed的所有赞(Like),该表使用feedId和userId做联合主键,其中feedId为排在第一位的字段。该表一共有19个Page的数据。下面我们选取feedId为11593所有的赞:

 select * from PW_Like where feedId = 11593; 
# Query_time: 0.000478 Lock_time: 0.000084 Rows_sent: 58 Rows_examined: 58 Rows_affected: 0 
# Bytes_sent: 865 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No
# InnoDB_pages_distinct: 2 


我们花了0.47ms取出了58条数据,但一共只读取了2个Page的数据,其中一个Page还是这个表的主键。说明这58条数据都存储在同一个Page中。这种情况,对于数据库来说,是效率最高的情况。

3.2根据普通索引批量检索数据
还是刚才那个表,我们除了主键以外,还在userId上建立了索引,因为有时候需要查询某个用户点过的赞。那么我们来看看只通过索引,不通过主键来检索批量数据时候,数据库的效率情况。

 select * from PW_Like where userId = 80000402; 
# Query_time: 0.002892 Lock_time: 0.000062 Rows_sent: 27 Rows_examined: 27 Rows_affected: 0 
# Bytes_sent: 399 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No
# InnoDB_pages_distinct: 15 


我们可以看到的结果是,虽然我们只取出了27条数据,但是我们读取了15个数据Page,花了2.8毫秒,虽然没有进行全局扫描,但基本上也把一般的数据块读取出来了。因为PW_Like中的数据由于是按照feedId物理排序的,所以这27条数据分别分布在13个Page中(有两个Page是索引和主键),所以数据库需要把这13个Page全部从磁盘中读取出来,哪怕某一个Page(16K)上只有一条数据(15Bytes),也需要把这个数据Page读出来才能取出所有的目标Row。

通过普通索引来检索批量数据,效率明显比通过主键来检索要低得多。因为数据是分散的,所以需要分散地读取数据Page进行拼接才能完成任务。但是索引对主键而言还是非常有必要的补充,比如上面这个例子,当用户量达到100万的时候,检索某一个用户点的所有的赞的成本也只是大概读取15个Page,花2ms左右。

3.2检索一段时间范围内的数据
选取一定范围内的数据是我们经常要遇到的问题。在海量数据的表中检索一定范围内的数据,很容易引起性能问题。我们遇到的最常见的需求有以下几种:

选取一段时间内注册的用户信息

这种时候,时间肯定不会是用户表的主键,如果直接用时间作为选择条件来检索,效率会非常差,如何解决这种问题呢?我采取的办法是,把注册时间作为用户表的索引,每次先把需要检索的时间的两端的userId都差出来,然后用这两个userId做选择条件做第三次查询就是我们想要的数据了。这三次检索我们只需要读取大约10个Page就能解决问题。这种方法看起来很麻烦,但是是表的数据量达到亿级的时候的唯一解决方案。
选取一段时间内的日志
日志表是我们最常见的表,如何设计好是经常聊到的话题。日志表之所以不好设计是因为大家都希望用时间作为主键,这样检索一段时间内的日志将非常方便。但是用时间作为主键有个非常大的弊端,当日志插入速度很快的时候,就会出现因为主键重复而引起冲突。
对于这种情况,我一般把日志生成时间和一个自增的Id作为日志表的联合主键,把时间作为第一个字段,这样即避免了日志插入过快引起的主键唯一性冲突,又能便捷地根据时间做检索工作,非常方便。下面是这种日志表的检索的例子,大家可以看到性能非常好。还有就是日志表最好是每天一个表,这样能更便利地管理和检索。

select * from log_test where logTime > "2015-12-27 11:53:05" and logTime < "2015-12-27 12:03:05";
# Query_time: 0.001158 Lock_time: 0.000084 Rows_sent: 599 Rows_examined: 599 Rows_affected: 0 
# Bytes_sent: 4347 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No 
# InnoDB_pages_distinct: 3


4.批量选择的效率
我们在批量检索数据的时候,对选择的结果在大多数情况下,都需要数据是排好序的。排序的性能也是日常需要注意到的,下面我们分三种情况来分析下数据库是如何排序的。
对于ORDER BY 主键这种情况,数据库是非常乐于见到的,基本上不会有额外性能的损耗,因为数据本来就是按照主键顺序存储的,取出来直接返回即可。
下面的所有关于排序的例子是在UP_MessageHistory表上做的实验,这个表一共有195个page,35417行数据,主键建立在字段id上,在sendUserId和destUserId上都建立了索引。
首先我们先做一个没有排序的检索:

 select * from `CU`.`UP_MessageHistory` where sendUserId = 92; 
# Query_time: 0.016135 Lock_time: 0.000084 Rows_sent: 3572 Rows_examined: 3572 Rows_affected: 0 
# Bytes_sent: 95600 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No 
# InnoDB_pages_distinct: 125  


然后我们在相同的条件下对id进行排序: 

 select * from `CU`.`UP_MessageHistory` where sendUserId = 92 order by id; 
# Query_time: 0.016259 Lock_time: 0.000086 Rows_sent: 3572 Rows_examined: 3572 Rows_affected: 0 
# Bytes_sent: 95600 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No 
# InnoDB_pages_distinct: 125 

 
从上面的数据可以看出,性能和没有加order by差不多,基本没有额外的性能损耗。接下来我们对索引进行排序:

 select * from `CU`.`UP_MessageHistory` where sendUserId = 92 order by destUserId; 
# Query_time: 0.018107 Lock_time: 0.000083 Rows_sent: 3572 Rows_examined: 7144 Rows_affected: 0 
# Bytes_sent: 103123 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No
# InnoDB_pages_distinct: 125 



接下来我们用普通字符串字段做排序再看看:

 select * from `CU`.`UP_MessageHistory` where sendUserId = 92 order by content; 
# Query_time: 0.023611 Lock_time: 0.000085 Rows_sent: 3572 Rows_examined: 7144 Rows_affected: 0 
# Bytes_sent: 105214 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No 
# InnoDB_pages_distinct: 125 



然后我们再用普通的数字类型字段排序看看情况:

 select * from `CU`.`UP_MessageHistory` where sendUserId = 92 order by sentTime; 
# Query_time: 0.018522 Lock_time: 0.000107 Rows_sent: 3572 Rows_examined: 7144 Rows_affected: 0 
# Bytes_sent: 95709 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 # QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No 
# InnoDB_pages_distinct: 125 


 针对以上的实验结果,我们可以得出以下结论:
针对主键做排序操作不会有性能损耗;针对不在选择条件中的索引字段做排序操作,索引不会起优化排序的作用;针对数值类型字段排序会比针对字符串类型字段排序的效率要高很多。

下面我们再研究下用选择条件中的索引字段排序,数据库是否会优化排序算法,我们任然用UP_User表来研究。

 select * from UP_User where score > 10000; 
# Query_time: 0.001470 Lock_time: 0.000130 Rows_sent: 122 Rows_examined: 122 Rows_affected: 0 
# Bytes_sent: 9559 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No 
# InnoDB_pages_distinct: 17 


然后我们用选择条件中的索引字段做排序:

 select * from UP_User where score > 10000 order by score 
# Query_time: 0.001407 Lock_time: 0.000087 Rows_sent: 122 Rows_examined: 122 Rows_affected: 0 
# Bytes_sent: 9559 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No# InnoDB_pages_distinct: 17 



然后我们用选择条件中的非索引数值字段做排序:

 select * from UP_User where score > 10000 ORDER BY `securityQuestion` 
# Query_time: 0.002017 Lock_time: 0.000104 Rows_sent: 122 Rows_examined: 244 Rows_affected: 0 
# Bytes_sent: 9657 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No 
# InnoDB_pages_distinct: 17 


从上面三条查询语句的执行时间来分析,使用索引字段排序和不排序花的时间差不多,比使用普通字段排序花的时间少一些,因此我们可以得出第四条结论:

针对在选择条件中的索引字段做排序操作,索引会起优化排序的作用。

5.索引维护

在前面我们可以看到所有的主键和索引都是排好序的,那么排序这件事情就需要销号资源,每次有新的数据插入,或者老的数据的数值发生变更,排序就需要调整,这里面是需要损耗性能的,下面我们分析一下。

自增型字段作为主键时,数据库对主键的维护成本非常低:
每次新增加的值都是一个最大的值,追加到最后即可,其他数据不需要挪动;这种数据一般不做修改。






  
使用业务型字段作为主键时,主键维护成本会比较高。每次生成的新数据都有可能需要挪动其他数据的位置。
 





 
 
因为innodb主键和数据是在放在一块的,每次挪动主键,也需要挪动数据,维护的成本会比较搞,对于需要频繁写入的表,不建议使用业务字段作为主键的。
由于主键是所有索引的叶节点的值,也是数据排序的依据,如果主键的值被修改,那么需要修改所有相关索引,并且需要修改整个主键B+Tree的排序,损耗会非常大。避免频繁更新主键可以避免以上提到的问题。

 update UP_User set userId = 100000945 where userId = 10000094; 
# Query_time: 0.010916 Lock_time: 0.000201 Rows_sent: 0 Rows_examined: 1 Rows_affected: 1 
# Bytes_sent: 59 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No 
# InnoDB_pages_distinct: 11 从上面的数据可以看出来,索然SQL语句只修改了一条数据,却影响了11个Page。
 

 

相对于主键而已,索引就轻很多,它的叶节点的值是主键,很轻,维护起来成本比较低。但也不建议为一个表建立过多索引。维护一个索引成本低,维护8个就不一定低了,这种事需要均衡地对待。

 
6.索引设计原则

索引其实是一把双刃剑,用好了事半功倍,没用好,事倍功半。

主键的字段无特殊情况,一定要使用数值类型的,排序时占用计算资源少,存储时占用空间也少。若主键的字段值很大,则整个数据表的各种索引也会变得没有效率,因为所有的索引的叶节点的值都是主键。

并不是所有索引对查询都有效,SQL是根据表中数据来进行查询优化的,当索引列有大量数据重复时,SQL查询可能不会去利用索引,如一表中有字段 sex,male、female几乎各一半,那么即使在sex上建了索引也对查询效率起不了作用。 查看全部
作者:黄湘龙
架构师交流群(312254004)
欢迎非商业转载,商业使用请联系我 
 

索引是有效使用数据库的基础,但你的数据量很小的时候,或许通过扫描整表来存取数据的性能还能接受,但当数据量极大时,当访问量极大时,就一定需要通过索引的辅助才能有效地存取数据。一般索引建立的好坏是性能好坏的成功关键。

1.InnoDb数据与索引存储细节

使用InnoDb作为数据引擎的Mysql和有聚集索引的SqlServer的数据存储结构有点类似,虽然在物理层面,他们都存储在Page上,但在逻辑上面,我们可以把数据分为三块:数据区域,索引区域,主键区域,他们通过主键的值作为关联,配合工作。默认配置下,一个Page的大小为16K。

一个表数据空间中的索引数据区域中有很多索引,每一个索引都是一颗B+Tree,在索引的B+Tree中索引的值作为B+Tree的节点的Key,数据主键作为节点的Value。

在InnoDB中,表数据文件本身就是按B+Tree组织的一个索引结构,这棵树的叶节点数据域保存了完整的数据记录。这个索引的key是数据表的主键,因此InnoDB表数据文件本身就是主键索引。这种索引也叫做聚集索引。因为InnoDB的数据文件本身要按主键聚集,所以InnoDB要求表必须有主键(MyISAM可以没有),如果没有显式指定,则MySQL系统会自动选择一个可以唯一标识数据记录的列作为主键,如果不存在这种列,则MySQL自动为InnoDB表生成一个隐含字段作为主键,这个字段长度为6个字节,类型为长整形。

 表数据都以Row的形式放在一个一个大小为16K的Page中,在每个数据Page中有都页头信息和一行一行的数据。其中页头信息中主要放置的是这一页数据中的所有主键值和其对应的OFFSET,便于通过主键能迅速找到其对应的数据位置。

存储结构.jpg


2.索引优化检索的原理

索引是数据库的灵魂,如果没有索引,数据库也就是一堆文本文件,存在的意义并不大。索引能让数据库成几何倍数地提高检索效率。使用Innodb作为数据引擎的Mysql数据库的索引分为聚集索引(也就是主键)和普通索引。上节我们已经讲解了这两种索引的存储结构,现在我们仔细讲解下索引是如何工作的。

聚集索引和普通索引都有可能由多个字段组成,我们又称这种索引为复合索引,1.2.3将为大家解析这种索引的性能情况.

2.1聚集索引
从上节我们知晓,Innodb的所有数据是按照聚集索引排序的,聚集索引这种存储方式使得按主键的搜索十分高效,如果我们SQL语句的选择条件中有聚集索引,数据库会优先使用聚集索引来进行检索工作。
数据库引擎根据条件中的主键的值,迅速在B+Tree中找到主键对应的叶节点,然后把叶节点所在的Page数据库读取到内存,返回给用户,如上图绿色线条的流向。下面我们来运行一条SQL,从数据库的执行情况分析一下:


select * from UP_User where userId = 10000094;

......

# Query_time: 0.000399  Lock_time: 0.000101  Rows_sent: 1  Rows_examined: 1  Rows_affected: 0

# Bytes_sent: 803  Tmp_tables: 0  Tmp_disk_tables: 0  Tmp_table_sizes: 0

# InnoDB_trx_id: 1D4D

# QC_Hit: No  Full_scan: No  Full_join: No  Tmp_table: No  Tmp_table_on_disk: No

# Filesort: No  Filesort_on_disk: No  Merge_passes: 0

#   InnoDB_IO_r_ops: 0  InnoDB_IO_r_bytes: 0  InnoDB_IO_r_wait: 0.000000

#   InnoDB_rec_lock_wait: 0.000000  InnoDB_queue_wait: 0.000000

#   InnoDB_pages_distinct: 2

SET timestamp=1451104535;

select * from UP_User where userId = 10000094;




我们可以看到,数据库读从磁盘取了两个Page就把809Bytes的数据提取出来返回给客户端。
下面我们试一下,如果试一下选取条件没有包含主键和索引的情况:


select * from `UP_User` where bigPortrait = '5F29E883BFA8903B';

# Query_time: 0.002869 Lock_time: 0.000094 Rows_sent: 1 Rows_examined: 1816 Rows_affected: 0

# Bytes_sent: 792 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0

# QC_Hit: No Full_scan: Yes Full_join: No Tmp_table: No Tmp_table_on_disk: No

# InnoDB_pages_distinct: 25



可以看到如果使用主键作为检索条件,检索时间花了0.3ms,只读取了两个Page,而不使用主键作为检索条件,检索时间花了2.8ms,读取了25个Page,全局扫描才把这条记录给找出来。这还是一个只有1000多行的表,如果更大的数据量,对比更加强烈。

 对于这两个Page,一个是主键B+Tree的数据,一个是10000094这条数据所在的数据页。


2.2普通索引
我们用普通索引作为检索条件来搜索数据需要检索两遍索引:首先检索普通索引获得主键,然后用主键到主索引中检索获得记录。如上图红色线条的流向。
下面我用一个例子来看看数据库的表现:


 select * from UP_User where userName = 'fred';# Query_time: 0.000400 Lock_time: 0.000101 Rows_sent: 1 Rows_examined: 1 Rows_affected: 0

# Bytes_sent: 803 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0

# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No

# InnoDB_pages_distinct: 4



 我们可以看到数据库用了0.4ms来检索这条数据,读取了4个page,比用主键作为检索条件多用了0.1ms,多读取了两个Page,而这两个Page就是userName这个普通索引的B+Tree所在的数据页。


2.3复合索引

聚集索引和普通索引都有可能由多个字段组成,多个字段组成的索引的叶节点的Key由多个字段的值按照顺序拼接而成。这种索引的存储结构是这样的,首先按照第一个字段建立一棵B+Tree,叶节点的Key是第一个字段的值,叶节点的value又是一棵小的B+Tree,逐级递减。对于这样的索引,用排在第一的字段作为检索条件才有效提高检索效率。排在后面的字段只能在排在他前面的字段都在检索条件中的时候才能起辅助效果。下面我们用例子来说明这种情况。

我们在UP_User表上建立一个用来测试的复合索引,建立在 (`nickname`,`regTime`)两个字段上,下面我们测试下检索性能:


 select * from UP_User where nickName='fredlong'; 
# Query_time: 0.000443 Lock_time: 0.000101 Rows_sent: 1 Rows_examined: 1 Rows_affected: 0
# Bytes_sent: 778 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No 
# InnoDB_pages_distinct: 4 



我们看到索引起作用了,和普通索引的效果一样都用了0.43ms,读取了四个Page就完成了任务。


 select * from UP_User where regTime = '2015-04-27 09:53:02'; 
# Query_time: 0.007076 Lock_time: 0.000286 Rows_sent: 1 Rows_examined: 1816 Rows_affected: 0 
# Bytes_sent: 803 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: Yes Full_join: No Tmp_table: No Tmp_table_on_disk: No 
# InnoDB_pages_distinct: 26 



从这次选择的执行情况来看,虽然regTime在刚才建立的复合索引中,还是做了全局扫描。因为这个复合索引排在regTime字段前面的nickname字段没有出现在选择条件中,所以这个索引我们没用用到。
那么我们什么情况下会用到复合索引呢。我一般在两种情况下会用到:
  1.  需要复合索引来排重的时候。
  2.  用索引的第一个字段选取出来的结果不够精准,需要第二个字段做进一步的性能优化。


 我基本上没有建立过三个以上的字段做复合索引,如果出现这种情况,我觉的你的表设计可能出现了大而全的问题,需要在表设计层面调优,而不是通过增加复杂的索引调优。所有复杂的东西都是大概率有问题的。

3.批量选择的效率
我们业务经常这样的诉求,选择某个用户发送的所有消息,这个帖子所有回复内容,所有昨天注册的用户的UserId等。这样的诉求需要从数据库中取出一批数据,而不是一条数据,数据库对这种请求的处理逻辑稍微复杂一点,下面我们来分析一下各种情况。
3.1根据主键批量检索数据
我们有一张表PW_Like,专门存储Feed的所有赞(Like),该表使用feedId和userId做联合主键,其中feedId为排在第一位的字段。该表一共有19个Page的数据。下面我们选取feedId为11593所有的赞:


 select * from PW_Like where feedId = 11593; 
# Query_time: 0.000478 Lock_time: 0.000084 Rows_sent: 58 Rows_examined: 58 Rows_affected: 0 
# Bytes_sent: 865 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No
# InnoDB_pages_distinct: 2 



我们花了0.47ms取出了58条数据,但一共只读取了2个Page的数据,其中一个Page还是这个表的主键。说明这58条数据都存储在同一个Page中。这种情况,对于数据库来说,是效率最高的情况。

3.2根据普通索引批量检索数据
还是刚才那个表,我们除了主键以外,还在userId上建立了索引,因为有时候需要查询某个用户点过的赞。那么我们来看看只通过索引,不通过主键来检索批量数据时候,数据库的效率情况。


 select * from PW_Like where userId = 80000402; 
# Query_time: 0.002892 Lock_time: 0.000062 Rows_sent: 27 Rows_examined: 27 Rows_affected: 0 
# Bytes_sent: 399 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No
# InnoDB_pages_distinct: 15 



我们可以看到的结果是,虽然我们只取出了27条数据,但是我们读取了15个数据Page,花了2.8毫秒,虽然没有进行全局扫描,但基本上也把一般的数据块读取出来了。因为PW_Like中的数据由于是按照feedId物理排序的,所以这27条数据分别分布在13个Page中(有两个Page是索引和主键),所以数据库需要把这13个Page全部从磁盘中读取出来,哪怕某一个Page(16K)上只有一条数据(15Bytes),也需要把这个数据Page读出来才能取出所有的目标Row。

通过普通索引来检索批量数据,效率明显比通过主键来检索要低得多。因为数据是分散的,所以需要分散地读取数据Page进行拼接才能完成任务。但是索引对主键而言还是非常有必要的补充,比如上面这个例子,当用户量达到100万的时候,检索某一个用户点的所有的赞的成本也只是大概读取15个Page,花2ms左右。

3.2检索一段时间范围内的数据
选取一定范围内的数据是我们经常要遇到的问题。在海量数据的表中检索一定范围内的数据,很容易引起性能问题。我们遇到的最常见的需求有以下几种:

选取一段时间内注册的用户信息

这种时候,时间肯定不会是用户表的主键,如果直接用时间作为选择条件来检索,效率会非常差,如何解决这种问题呢?我采取的办法是,把注册时间作为用户表的索引,每次先把需要检索的时间的两端的userId都差出来,然后用这两个userId做选择条件做第三次查询就是我们想要的数据了。这三次检索我们只需要读取大约10个Page就能解决问题。这种方法看起来很麻烦,但是是表的数据量达到亿级的时候的唯一解决方案。
选取一段时间内的日志
日志表是我们最常见的表,如何设计好是经常聊到的话题。日志表之所以不好设计是因为大家都希望用时间作为主键,这样检索一段时间内的日志将非常方便。但是用时间作为主键有个非常大的弊端,当日志插入速度很快的时候,就会出现因为主键重复而引起冲突。
对于这种情况,我一般把日志生成时间和一个自增的Id作为日志表的联合主键,把时间作为第一个字段,这样即避免了日志插入过快引起的主键唯一性冲突,又能便捷地根据时间做检索工作,非常方便。下面是这种日志表的检索的例子,大家可以看到性能非常好。还有就是日志表最好是每天一个表,这样能更便利地管理和检索。


select * from log_test where logTime > "2015-12-27 11:53:05" and logTime < "2015-12-27 12:03:05";
# Query_time: 0.001158 Lock_time: 0.000084 Rows_sent: 599 Rows_examined: 599 Rows_affected: 0 
# Bytes_sent: 4347 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No 
# InnoDB_pages_distinct: 3



4.批量选择的效率
我们在批量检索数据的时候,对选择的结果在大多数情况下,都需要数据是排好序的。排序的性能也是日常需要注意到的,下面我们分三种情况来分析下数据库是如何排序的。
对于ORDER BY 主键这种情况,数据库是非常乐于见到的,基本上不会有额外性能的损耗,因为数据本来就是按照主键顺序存储的,取出来直接返回即可。
下面的所有关于排序的例子是在UP_MessageHistory表上做的实验,这个表一共有195个page,35417行数据,主键建立在字段id上,在sendUserId和destUserId上都建立了索引。
首先我们先做一个没有排序的检索:


 select * from `CU`.`UP_MessageHistory` where sendUserId = 92; 
# Query_time: 0.016135 Lock_time: 0.000084 Rows_sent: 3572 Rows_examined: 3572 Rows_affected: 0 
# Bytes_sent: 95600 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No 
# InnoDB_pages_distinct: 125  



然后我们在相同的条件下对id进行排序: 


 select * from `CU`.`UP_MessageHistory` where sendUserId = 92 order by id; 
# Query_time: 0.016259 Lock_time: 0.000086 Rows_sent: 3572 Rows_examined: 3572 Rows_affected: 0 
# Bytes_sent: 95600 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No 
# InnoDB_pages_distinct: 125 


 
从上面的数据可以看出,性能和没有加order by差不多,基本没有额外的性能损耗。接下来我们对索引进行排序:


 select * from `CU`.`UP_MessageHistory` where sendUserId = 92 order by destUserId; 
# Query_time: 0.018107 Lock_time: 0.000083 Rows_sent: 3572 Rows_examined: 7144 Rows_affected: 0 
# Bytes_sent: 103123 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No
# InnoDB_pages_distinct: 125 




接下来我们用普通字符串字段做排序再看看:


 select * from `CU`.`UP_MessageHistory` where sendUserId = 92 order by content; 
# Query_time: 0.023611 Lock_time: 0.000085 Rows_sent: 3572 Rows_examined: 7144 Rows_affected: 0 
# Bytes_sent: 105214 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No 
# InnoDB_pages_distinct: 125 




然后我们再用普通的数字类型字段排序看看情况:


 select * from `CU`.`UP_MessageHistory` where sendUserId = 92 order by sentTime; 
# Query_time: 0.018522 Lock_time: 0.000107 Rows_sent: 3572 Rows_examined: 7144 Rows_affected: 0 
# Bytes_sent: 95709 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 # QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No 
# InnoDB_pages_distinct: 125 



 针对以上的实验结果,我们可以得出以下结论:
  1. 针对主键做排序操作不会有性能损耗;
  2. 针对不在选择条件中的索引字段做排序操作,索引不会起优化排序的作用;
  3. 针对数值类型字段排序会比针对字符串类型字段排序的效率要高很多。


下面我们再研究下用选择条件中的索引字段排序,数据库是否会优化排序算法,我们任然用UP_User表来研究。


 select * from UP_User where score > 10000; 
# Query_time: 0.001470 Lock_time: 0.000130 Rows_sent: 122 Rows_examined: 122 Rows_affected: 0 
# Bytes_sent: 9559 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No 
# InnoDB_pages_distinct: 17 



然后我们用选择条件中的索引字段做排序:


 select * from UP_User where score > 10000 order by score 
# Query_time: 0.001407 Lock_time: 0.000087 Rows_sent: 122 Rows_examined: 122 Rows_affected: 0 
# Bytes_sent: 9559 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No# InnoDB_pages_distinct: 17 




然后我们用选择条件中的非索引数值字段做排序:


 select * from UP_User where score > 10000 ORDER BY `securityQuestion` 
# Query_time: 0.002017 Lock_time: 0.000104 Rows_sent: 122 Rows_examined: 244 Rows_affected: 0 
# Bytes_sent: 9657 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No 
# InnoDB_pages_distinct: 17 



从上面三条查询语句的执行时间来分析,使用索引字段排序和不排序花的时间差不多,比使用普通字段排序花的时间少一些,因此我们可以得出第四条结论:

针对在选择条件中的索引字段做排序操作,索引会起优化排序的作用。

5.索引维护

在前面我们可以看到所有的主键和索引都是排好序的,那么排序这件事情就需要销号资源,每次有新的数据插入,或者老的数据的数值发生变更,排序就需要调整,这里面是需要损耗性能的,下面我们分析一下。

自增型字段作为主键时,数据库对主键的维护成本非常低:
  1. 每次新增加的值都是一个最大的值,追加到最后即可,其他数据不需要挪动;
  2. 这种数据一般不做修改。



聚集索引索引维护.jpg

  
使用业务型字段作为主键时,主键维护成本会比较高。每次生成的新数据都有可能需要挪动其他数据的位置。
 

聚集索引索引维护2.jpg

 
 
因为innodb主键和数据是在放在一块的,每次挪动主键,也需要挪动数据,维护的成本会比较搞,对于需要频繁写入的表,不建议使用业务字段作为主键的。
由于主键是所有索引的叶节点的值,也是数据排序的依据,如果主键的值被修改,那么需要修改所有相关索引,并且需要修改整个主键B+Tree的排序,损耗会非常大。避免频繁更新主键可以避免以上提到的问题。


 update UP_User set userId = 100000945 where userId = 10000094; 
# Query_time: 0.010916 Lock_time: 0.000201 Rows_sent: 0 Rows_examined: 1 Rows_affected: 1 
# Bytes_sent: 59 Tmp_tables: 0 Tmp_disk_tables: 0 Tmp_table_sizes: 0 
# QC_Hit: No Full_scan: No Full_join: No Tmp_table: No Tmp_table_on_disk: No 
# InnoDB_pages_distinct: 11 从上面的数据可以看出来,索然SQL语句只修改了一条数据,却影响了11个Page。
 


 

相对于主键而已,索引就轻很多,它的叶节点的值是主键,很轻,维护起来成本比较低。但也不建议为一个表建立过多索引。维护一个索引成本低,维护8个就不一定低了,这种事需要均衡地对待。

 
6.索引设计原则

索引其实是一把双刃剑,用好了事半功倍,没用好,事倍功半。

主键的字段无特殊情况,一定要使用数值类型的,排序时占用计算资源少,存储时占用空间也少。若主键的字段值很大,则整个数据表的各种索引也会变得没有效率,因为所有的索引的叶节点的值都是主键。

并不是所有索引对查询都有效,SQL是根据表中数据来进行查询优化的,当索引列有大量数据重复时,SQL查询可能不会去利用索引,如一表中有字段 sex,male、female几乎各一半,那么即使在sex上建了索引也对查询效率起不了作用。

MySQL 5.7 传统复制到GTID在线切换

wubx 发表了文章 • 2 个评论 • 718 次浏览 • 2015-12-29 10:13 • 来自相关话题

作者:吴炳锡 来源:http://wubx.net/ 联系方式: wubingxi#163.com 转载请注明作/译者和出处,并且不能用于商业用途,违者必究.

前题:
     1. 要求MySQL 5.7.6及以后版本。
    2. 所有组中成员gtid_mode 为off状态。


实现步骤:
1. 所有的Server执行 
  set @@global.enforce_gtid_consistency = warn;
    
   特别注意: 这一步是关建的一步使用不能出现警告。

2.所有的server上执行:
    set @@global.enforce_gtid_consistency = on;

3.所有的Server上执行(不关心最先最后,但要执行完):
    set @@global.gtid_mode = off_permissive;

4. 执行:
    set @@global.gtid_mode=on_permissive;

    实质在这一步骤生的日志都是带GTID的日志了,这个步骤号称是不关心任何节点,但从实管理上推荐在slave上先执行,然后再去master上执行。


5. 确认传统的binlog复制完毕,该值为0
    show status like 'ongoing_anonymous_transaction_count';

  需要所有的节点都确认为0.

6.  所有节点进行判断 show status like 'ongoing_anonymous_transaction_count'; 为零
    所有的节点也可以执行一下: flush logs; 用于切换一下日志。

7.  所有的节点启用gtid_mode
    set @@global.gtid_mode=on

8.  把gtid_mode = on相关配置写入配置文件
    gtid_mode=on
    enforce_gtid_consistency=on

9. 启用Gtid的自动查找节点复制:
stop slave;
change master to master_auto_position=1;
start slave;

完毕。Good Luck。
     查看全部
作者:吴炳锡 来源:http://wubx.net/ 联系方式: wubingxi#163.com 转载请注明作/译者和出处,并且不能用于商业用途,违者必究.

前题:
     1. 要求MySQL 5.7.6及以后版本。
    2. 所有组中成员gtid_mode 为off状态。


实现步骤:
1. 所有的Server执行 
  set @@global.enforce_gtid_consistency = warn;
    
   特别注意: 这一步是关建的一步使用不能出现警告。

2.所有的server上执行:
    set @@global.enforce_gtid_consistency = on;

3.所有的Server上执行(不关心最先最后,但要执行完):
    set @@global.gtid_mode = off_permissive;

4. 执行:
    set @@global.gtid_mode=on_permissive;

    实质在这一步骤生的日志都是带GTID的日志了,这个步骤号称是不关心任何节点,但从实管理上推荐在slave上先执行,然后再去master上执行。


5. 确认传统的binlog复制完毕,该值为0
    show status like 'ongoing_anonymous_transaction_count';

  需要所有的节点都确认为0.

6.  所有节点进行判断 show status like 'ongoing_anonymous_transaction_count'; 为零
    所有的节点也可以执行一下: flush logs; 用于切换一下日志。

7.  所有的节点启用gtid_mode
    set @@global.gtid_mode=on

8.  把gtid_mode = on相关配置写入配置文件
    gtid_mode=on
    enforce_gtid_consistency=on

9. 启用Gtid的自动查找节点复制:
stop slave;
change master to master_auto_position=1;
start slave;

完毕。Good Luck。
    


3分钟搞定MySQL5.7安装

zhangdh 发表了文章 • 3 个评论 • 834 次浏览 • 2015-12-22 19:49 • 来自相关话题

1.下载MySQL5.7二进制版本mkdir /opt/mysql
cd /opt/mysql
wget [url]http://downloads.mysql.com/arc ... ar.gz[/url]2.解压MySQL5.7到/opt/mysql下tar zxf mysql-5.7.9-linux-glibc2.5-x86_64.tar.gz3. 创建一个软连接,方便升级cd /usr/local ln -s /opt/mysql/mysql-5.7.9-linux-glibc2.5-x86_64 mysql4. 创建MySQl启动用户和组groupadd mysql
useradd -d /usr/local/mysql -g mysql -s /sbin/nologin mysql chown -R mysql:mysql /usr/local/mysql5. 创建基本目录mkdir -p /data/mysql/mysql3307/{data,logs,tmp}
chown -R mysql:mysql /data/mysql/mysql33076. 创建my.cnf配置文件(见附件)vim /data/mysql/mysql3307/my.cnf7. 初始化数据库
·注意MySQL5.7初始化已经融合到bin/mysqld命令中,通过“--initialize”来完成初始化,更多详细的命令,可以执行下面的命令来查看·/usr/local/mysql/bin/mysqld --verbose --help|less本例中数据库初始化命令如下(它将没有任何的输出,但是你可以从日志进行查看):/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3307/my.cnf --initialize8. 设置环境变量echo "export PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile
source /etc/profile9. 启动MySQL/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3307/my.cnf &10. 修改MySQL的root用户密码
初始完后需要用户自行更改root用户密码,否则执行“mysql -S /tmp/mysql3307.sock -u root -p”将报下面的错误:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
这时候我们需要关闭MySQL数据库,执行下面的命令更改MySQL root的初始化密码:/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/mysql3307/my.cnf --skip-grant-tables &
mysql -S /tmp/mysql3307.sock

>update mysql.user set authentication_string=password('3wedu.net') where user='root' and host='localhost';
>flush privileges;
>\q关闭MySQL数据库,然后使用mysqld启动数据库/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3307/my.cnf &启动完毕后,如果直接执行“/usr/local/mysql/bin/mysql -S /tmp/mysql3307.sock -u root -p”,将报下面的错误:
ERROR 1862 (HY000): Your password has expired. To log in you must change it using a client that supports expired passwords.
这时候需要执行下面的命令对数据库进行密码更改/usr/local/mysql/bin/mysql -S /tmp/mysql3307.sock -u root -p --connect-expired-password

>set password=password('3wedu.net');
>flush privileges; >\q更改完成后,就可以使用下面的命令直接登录了/usr/local/mysql/bin/mysql -S /tmp/mysql3307.sock -u root -p11. 附件:my.cnf
#my.cnf
[client]
port            = 3307
socket          = /tmp/mysql3307.sock

[mysql]
prompt="\\u@\\h:\p  \\R:\\m:\\s [\\d]>"
#tee=/data/mysql/mysql_3306/data/query.log
no-auto-rehash
[mysqld_multi]
mysqld     = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
user       = zhangdh
password   = zhangdh1113
log        = /data/mysql/mysql3307/logs/multi.log
#tcp-ip

[mysqld]
#misc
user = mysql
basedir = /usr/local/mysql
datadir = /data/mysql/mysql3307/data
port = 3307
socket = /tmp/mysql3307.sock
event_scheduler = 0
#tmp
tmpdir=/data/mysql/mysql3307/tmp

#timeout
interactive_timeout = 300
wait_timeout = 300

#character set
character-set-server = utf8

open_files_limit = 65535
max_connections = 100
max_connect_errors = 100000

skip-name-resolve = 1
#logs
log-output=file
slow_query_log = 1
slow_query_log_file = slow.log
log-error = error.log
log_warnings = 2
pid-file = mysql.pid
long_query_time = 1
#log-slow-admin-statements = 1
#log-queries-not-using-indexes = 1
log-slow-slave-statements = 1


#binlog
binlog_format = row
server-id = 2003307
log-bin = /data/mysql/mysql3307/logs/mysql-bin
binlog_cache_size = 4M
max_binlog_size = 1G
max_binlog_cache_size = 2G
sync_binlog = 0
expire_logs_days = 10

#relay log
skip_slave_start = 1
max_relay_log_size = 1G
relay_log_purge = 1
relay_log_recovery = 1
log_slave_updates
#slave-skip-errors=1032,1053,1062

explicit_defaults_for_timestamp=1
#buffers & cache
table_open_cache = 2048
table_definition_cache = 2048
table_open_cache = 2048
max_heap_table_size = 96M
sort_buffer_size = 2M
join_buffer_size = 2M
thread_cache_size = 256
query_cache_size = 0
query_cache_type = 0
query_cache_limit = 256K
query_cache_min_res_unit = 512
thread_stack = 192K
tmp_table_size = 96M
key_buffer_size = 8M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 32M

#myisam
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1

#innodb
innodb_buffer_pool_size = 100M
innodb_buffer_pool_instances = 1
innodb_data_file_path = ibdata1:1G:autoextend
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 64M
innodb_log_file_size = 500M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 50
innodb_file_per_table = 1
innodb_rollback_on_timeout
innodb_status_file = 1
innodb_io_capacity = 2000
transaction_isolation = READ-COMMITTED
innodb_flush_method = O_DIRECT

#[mysqld3306]
#port            = 3306
#datadir = /data/mysql/mysql_3306/data
#socket = /tmp/mysql_3306.sock
#tmpdir=/data/mysql/mysql_3306/tmp
#server-id = 2143306
#log-bin = /data/mysql/mysql_3306/logs/mysql-bin

#[mysqld3316]
#port            = 3316
#datadir = /data/mysql/mysql_3316/data
#socket = /tmp/mysql_3316.sock
#tmpdir=/data/mysql/mysql_3316/tmp
#server-id = 2143316
#log-bin = /data/mysql/mysql_3316/logs/mysql-bin

[mysqld_safe]
#malloc-lib=/usr/local/mysql/lib/jmalloc.so 
nice=-19
open-files-limit=65535 查看全部
1.下载MySQL5.7二进制版本
mkdir /opt/mysql
cd /opt/mysql
wget [url]http://downloads.mysql.com/arc ... ar.gz[/url]
2.解压MySQL5.7到/opt/mysql下
tar zxf mysql-5.7.9-linux-glibc2.5-x86_64.tar.gz
3. 创建一个软连接,方便升级
cd /usr/local ln -s /opt/mysql/mysql-5.7.9-linux-glibc2.5-x86_64 mysql
4. 创建MySQl启动用户和组
groupadd mysql
useradd -d /usr/local/mysql -g mysql -s /sbin/nologin mysql chown -R mysql:mysql /usr/local/mysql
5. 创建基本目录
mkdir -p /data/mysql/mysql3307/{data,logs,tmp} 
chown -R mysql:mysql /data/mysql/mysql3307
6. 创建my.cnf配置文件(见附件)
vim /data/mysql/mysql3307/my.cnf
7. 初始化数据库
·注意MySQL5.7初始化已经融合到bin/mysqld命令中,通过“--initialize”来完成初始化,更多详细的命令,可以执行下面的命令来查看·
/usr/local/mysql/bin/mysqld --verbose --help|less
本例中数据库初始化命令如下(它将没有任何的输出,但是你可以从日志进行查看):
/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3307/my.cnf --initialize
8. 设置环境变量
echo "export PATH=$PATH:/usr/local/mysql/bin" >> /etc/profile
source /etc/profile
9. 启动MySQL
/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3307/my.cnf &
10. 修改MySQL的root用户密码
初始完后需要用户自行更改root用户密码,否则执行“mysql -S /tmp/mysql3307.sock -u root -p”将报下面的错误:
ERROR 1045 (28000): Access denied for user 'root'@'localhost' (using password: NO)
这时候我们需要关闭MySQL数据库,执行下面的命令更改MySQL root的初始化密码:
/usr/local/mysql/bin/mysqld_safe --defaults-file=/data/mysql/mysql3307/my.cnf --skip-grant-tables &
mysql -S /tmp/mysql3307.sock

>update mysql.user set authentication_string=password('3wedu.net') where user='root' and host='localhost';
>flush privileges;
>\q
关闭MySQL数据库,然后使用mysqld启动数据库
/usr/local/mysql/bin/mysqld --defaults-file=/data/mysql/mysql3307/my.cnf &
启动完毕后,如果直接执行“/usr/local/mysql/bin/mysql -S /tmp/mysql3307.sock -u root -p”,将报下面的错误:
ERROR 1862 (HY000): Your password has expired. To log in you must change it using a client that supports expired passwords.
这时候需要执行下面的命令对数据库进行密码更改
/usr/local/mysql/bin/mysql -S /tmp/mysql3307.sock -u root -p --connect-expired-password

>set password=password('3wedu.net');
>flush privileges; >\q
更改完成后,就可以使用下面的命令直接登录了
/usr/local/mysql/bin/mysql -S /tmp/mysql3307.sock -u root -p
11. 附件:my.cnf
#my.cnf
[client]
port            = 3307
socket          = /tmp/mysql3307.sock

[mysql]
prompt="\\u@\\h:\p  \\R:\\m:\\s [\\d]>"
#tee=/data/mysql/mysql_3306/data/query.log
no-auto-rehash
[mysqld_multi]
mysqld     = /usr/local/mysql/bin/mysqld_safe
mysqladmin = /usr/local/mysql/bin/mysqladmin
user       = zhangdh
password   = zhangdh1113
log        = /data/mysql/mysql3307/logs/multi.log
#tcp-ip

[mysqld]
#misc
user = mysql
basedir = /usr/local/mysql
datadir = /data/mysql/mysql3307/data
port = 3307
socket = /tmp/mysql3307.sock
event_scheduler = 0
#tmp
tmpdir=/data/mysql/mysql3307/tmp

#timeout
interactive_timeout = 300
wait_timeout = 300

#character set
character-set-server = utf8

open_files_limit = 65535
max_connections = 100
max_connect_errors = 100000

skip-name-resolve = 1
#logs
log-output=file
slow_query_log = 1
slow_query_log_file = slow.log
log-error = error.log
log_warnings = 2
pid-file = mysql.pid
long_query_time = 1
#log-slow-admin-statements = 1
#log-queries-not-using-indexes = 1
log-slow-slave-statements = 1


#binlog
binlog_format = row
server-id = 2003307
log-bin = /data/mysql/mysql3307/logs/mysql-bin
binlog_cache_size = 4M
max_binlog_size = 1G
max_binlog_cache_size = 2G
sync_binlog = 0
expire_logs_days = 10

#relay log
skip_slave_start = 1
max_relay_log_size = 1G
relay_log_purge = 1
relay_log_recovery = 1
log_slave_updates
#slave-skip-errors=1032,1053,1062

explicit_defaults_for_timestamp=1
#buffers & cache
table_open_cache = 2048
table_definition_cache = 2048
table_open_cache = 2048
max_heap_table_size = 96M
sort_buffer_size = 2M
join_buffer_size = 2M
thread_cache_size = 256
query_cache_size = 0
query_cache_type = 0
query_cache_limit = 256K
query_cache_min_res_unit = 512
thread_stack = 192K
tmp_table_size = 96M
key_buffer_size = 8M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 32M

#myisam
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1

#innodb
innodb_buffer_pool_size = 100M
innodb_buffer_pool_instances = 1
innodb_data_file_path = ibdata1:1G:autoextend
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 64M
innodb_log_file_size = 500M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 50
innodb_file_per_table = 1
innodb_rollback_on_timeout
innodb_status_file = 1
innodb_io_capacity = 2000
transaction_isolation = READ-COMMITTED
innodb_flush_method = O_DIRECT

#[mysqld3306]
#port            = 3306
#datadir = /data/mysql/mysql_3306/data
#socket = /tmp/mysql_3306.sock
#tmpdir=/data/mysql/mysql_3306/tmp
#server-id = 2143306
#log-bin = /data/mysql/mysql_3306/logs/mysql-bin

#[mysqld3316]
#port            = 3316
#datadir = /data/mysql/mysql_3316/data
#socket = /tmp/mysql_3316.sock
#tmpdir=/data/mysql/mysql_3316/tmp
#server-id = 2143316
#log-bin = /data/mysql/mysql_3316/logs/mysql-bin

[mysqld_safe]
#malloc-lib=/usr/local/mysql/lib/jmalloc.so 
nice=-19
open-files-limit=65535

3分钟搞定MySQL5.6安装

zhangdh 发表了文章 • 2 个评论 • 605 次浏览 • 2015-12-22 16:43 • 来自相关话题

1. 下载MySQL二进制版本mkdir /opt/mysql
cd /opt/mysql
wget [url]http://cdn.mysql.com/archives/ ... ar.gz[/url]2. 将mysql解压到/opt/mysql下tar zxvf mysql-5.6.27-linux-glibc2.5-x86_64.tar.gz3. 创建一个软链,便于版本升级cd /usr/local/
ln -s /opt/mysql/mysql-5.6.27-linux-glibc2.5-x86_64 mysql4. 创建MySQL启动用户groupadd mysql
useradd -g mysql -s /sbin/nologin -d /usr/local/mysql mysql5. 创建基本的目录mkdir -p /data/mysql/mysql3306/{data,logs,tmp}
chown -R mysql:mysql /data/mysql/mysql33066. 创建/etc/my.cnf配置文件(见附件)

7. 做MySQL初始化cd /usr/local/mysql
./scripts/mysql_install_db8. 创建一个启动脚本cp support-files/mysql.server /etc/init.d/mysql9. 启动和关闭/etc/init.d/mysql start|stop|restart10. 配置文件参考:
#my.cnf
[client]
port            = 3306
socket          = /tmp/mysql.sock
[mysql]
prompt="\\u@\\h:\p  \\R:\\m:\\s [\\d]>"
#tee=/data/mysql/mysql3306/data/query.log
no-auto-rehash
[mysqld]
#misc
user = mysql
basedir = /usr/local/mysql
datadir = /data/mysql/mysql3306/data
port = 3306
socket = /tmp/mysql.sock
event_scheduler = 0
#tmp
tmpdir=/data/mysql/mysql3306/tmp
#timeout
interactive_timeout = 300
wait_timeout = 300
#character set
character-set-server = utf8
open_files_limit = 65535
max_connections = 100
max_connect_errors = 100000
skip-name-resolve = 1
#logs
log-output=file
slow_query_log = 1
slow_query_log_file = slow.log
log-error = error.log
log_warnings = 2
pid-file = mysql.pid
long_query_time = 1
#log-slow-admin-statements = 1
#log-queries-not-using-indexes = 1
log-slow-slave-statements = 1
#binlog
binlog_format = row
server-id = 1003306
log-bin = /data/mysql/mysql3306/logs/mysql-bin
binlog_cache_size = 4M
max_binlog_size = 1G
max_binlog_cache_size = 2G
sync_binlog = 0
expire_logs_days = 10
#relay log
skip_slave_start = 1
max_relay_log_size = 1G
relay_log_purge = 1
relay_log_recovery = 1
log_slave_updates
#slave-skip-errors=1032,1053,1062
explicit_defaults_for_timestamp=1
#buffers & cache
table_open_cache = 2048
table_definition_cache = 2048
table_open_cache = 2048
max_heap_table_size = 96M
sort_buffer_size = 2M
join_buffer_size = 2M
thread_cache_size = 256
query_cache_size = 0
query_cache_type = 0
query_cache_limit = 256K
query_cache_min_res_unit = 512
thread_stack = 192K
tmp_table_size = 96M
key_buffer_size = 8M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 32M
#myisam
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
#innodb
innodb_buffer_pool_size = 100M
innodb_buffer_pool_instances = 1
innodb_data_file_path = ibdata1:1G:autoextend
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 64M
innodb_log_file_size = 500M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 50
innodb_file_per_table = 1
innodb_rollback_on_timeout
innodb_status_file = 1
innodb_io_capacity = 2000
transaction_isolation = READ-COMMITTED
innodb_flush_method = O_DIRECT
[mysqld_safe]
#malloc-lib=/usr/local/mysql/lib/jmalloc.so
nice=-19
open-files-limit=65535 查看全部
1. 下载MySQL二进制版本
mkdir /opt/mysql
cd /opt/mysql
wget [url]http://cdn.mysql.com/archives/ ... ar.gz[/url]
2. 将mysql解压到/opt/mysql下
tar zxvf mysql-5.6.27-linux-glibc2.5-x86_64.tar.gz
3. 创建一个软链,便于版本升级
cd /usr/local/ 
ln -s /opt/mysql/mysql-5.6.27-linux-glibc2.5-x86_64 mysql
4. 创建MySQL启动用户
groupadd mysql 
useradd -g mysql -s /sbin/nologin -d /usr/local/mysql mysql
5. 创建基本的目录
mkdir -p /data/mysql/mysql3306/{data,logs,tmp}
chown -R mysql:mysql /data/mysql/mysql3306
6. 创建/etc/my.cnf配置文件(见附件)

7. 做MySQL初始化
cd /usr/local/mysql
./scripts/mysql_install_db
8. 创建一个启动脚本
cp support-files/mysql.server /etc/init.d/mysql
9. 启动和关闭
/etc/init.d/mysql start|stop|restart
10. 配置文件参考:
#my.cnf
[client]
port            = 3306
socket          = /tmp/mysql.sock
[mysql]
prompt="\\u@\\h:\p  \\R:\\m:\\s [\\d]>"
#tee=/data/mysql/mysql3306/data/query.log
no-auto-rehash
[mysqld]
#misc
user = mysql
basedir = /usr/local/mysql
datadir = /data/mysql/mysql3306/data
port = 3306
socket = /tmp/mysql.sock
event_scheduler = 0
#tmp
tmpdir=/data/mysql/mysql3306/tmp
#timeout
interactive_timeout = 300
wait_timeout = 300
#character set
character-set-server = utf8
open_files_limit = 65535
max_connections = 100
max_connect_errors = 100000
skip-name-resolve = 1
#logs
log-output=file
slow_query_log = 1
slow_query_log_file = slow.log
log-error = error.log
log_warnings = 2
pid-file = mysql.pid
long_query_time = 1
#log-slow-admin-statements = 1
#log-queries-not-using-indexes = 1
log-slow-slave-statements = 1
#binlog
binlog_format = row
server-id = 1003306
log-bin = /data/mysql/mysql3306/logs/mysql-bin
binlog_cache_size = 4M
max_binlog_size = 1G
max_binlog_cache_size = 2G
sync_binlog = 0
expire_logs_days = 10
#relay log
skip_slave_start = 1
max_relay_log_size = 1G
relay_log_purge = 1
relay_log_recovery = 1
log_slave_updates
#slave-skip-errors=1032,1053,1062
explicit_defaults_for_timestamp=1
#buffers & cache
table_open_cache = 2048
table_definition_cache = 2048
table_open_cache = 2048
max_heap_table_size = 96M
sort_buffer_size = 2M
join_buffer_size = 2M
thread_cache_size = 256
query_cache_size = 0
query_cache_type = 0
query_cache_limit = 256K
query_cache_min_res_unit = 512
thread_stack = 192K
tmp_table_size = 96M
key_buffer_size = 8M
read_buffer_size = 2M
read_rnd_buffer_size = 16M
bulk_insert_buffer_size = 32M
#myisam
myisam_sort_buffer_size = 128M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
#innodb
innodb_buffer_pool_size = 100M
innodb_buffer_pool_instances = 1
innodb_data_file_path = ibdata1:1G:autoextend
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 64M
innodb_log_file_size = 500M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 50
innodb_file_per_table = 1
innodb_rollback_on_timeout
innodb_status_file = 1
innodb_io_capacity = 2000
transaction_isolation = READ-COMMITTED
innodb_flush_method = O_DIRECT
[mysqld_safe]
#malloc-lib=/usr/local/mysql/lib/jmalloc.so
nice=-19
open-files-limit=65535