Mysql优化（高级篇）

约定不等于承诺〃 2023-07-17 15:53 55阅读 0赞

[Mysql优化（高级篇）][Mysql]

#### Mysql数据库优化篇 ####

Q: 为什么别人问你MySQL优化的知识 总是没有底气.
    A: 因为你只是回答一些大而化之的调优原则,
    比如:”建立合理索引”(什么样的索引合理?)
    “分表分库”(用什么策略分表分库?)
    “主从分离”(用什么中间件?)
    并没有从细化到定量的层面去分析.
    如qps提高了%N? 有没有减少文件排序?语句的扫描行数减少了多少?
    
    没有大量的数据供测试,一般在学习环境中,只是手工添加几百上万条数据,
    数据量小,看不出语句之间的明确区别.
    
    Q: 如何提高MySQL的性能?
    A: 需要优化,则说明效率不够理想.
    因此我们首先要做的,不是优化,而是---诊断.
    治病的前提,是诊病,找出瓶颈所在. CPU,内存,IO? 峰值,单条语句?
     
      
     
      
       1
       2
       3
       4
       5
       6
       7
       8
       9
       10
       11
       12
       13
       14
       15

准备环境

1、安装确保以下系统相关库文件

gcc gcc-c++ autoconf automake zlib* libxml* ncurses-devel libmcrypt* libtool*(libtool-ltdl-devel*)
    # yum –y install gcc gcc-c++ autoconf automake zlib* libxml* ncurses-devel libmcrypt* libtool* cmake
     
      
     
      
       1
       2

2、 建立mysql安装目录及数据存放目录

# mkdir /usr/local/mysql
    # mkdir -p /data/mysql
     
      
     
      
       1
       2

3、 创建用户和用户组

# groupadd mysql
    # useradd -g mysql mysql
     
      
     
      
       1
       2

4、 赋予数据存放目录权限

# chown mysql.mysql –R /data/mysql
     
      
     
      
       1

二、安装MySQL 5.5.35  
1、 获取解压mysql-5.5.35.tar.gz  
在mysql.com官网或国内镜像下载源码

# wget http://mirrors.sohu.com/mysql/MySQL-5.5/mysql-5.5.35.tar.gz
    # tar zxvf mysql-5.5.35.tar.gz
    # cd mysql-5.5.35
     
      
     
      
       1
       2
       3

2、 编译mysql-5.5.35

# cmake -DCMAKE_INSTALL_PREFIX=/usr/local/mysql \
    -DMYSQL_UNIX_ADDR=/tmp/mysqld.sock \ -DDEFAULT_CHARSET=utf8 \ -DDEFAULT_COLLATION=utf8_general_ci \ -DWITH_EXTRA_CHARSETS:STRING=utf8,gbk \ -DWITH_MYISAM_STORAGE_ENGINE=1 \ -DWITH_INNOBASE_STORAGE_ENGINE=1 \ -DWITH_MEMORY_STORAGE_ENGINE=1 \ -DWITH_READLINE=1 \ -DENABLED_LOCAL_INFILE=1 \ -DMYSQL_DATADIR=/data/mysql \ -DMYSQL_USER=mysql \ -DMYSQL_TCP_PORT=3306 # make
    # make install
     
      
     
      
       1
       2
       3
       4
       5
       6
       7
       8
       9
       10
       11
       12
       13
       14
       15

3、 复制配置文件

# cp support-files/my-medium.cnf /etc/my.cnf
     
      
     
      
       1

4、 初始化数据库  
执行前需赋给scripts/mysql\_install\_db文件执行权限

# chmod 755 scripts/mysql_install_db
    # scripts/mysql_install_db --user=mysql --basedir=/usr/local/mysql/ \
    --datadir=/data/mysql/
    注：basedir：mysql安装路径   datadir：数据库文件储存路径
     
      
     
      
       1
       2
       3
       4

5、 设置mysqld的开机启动

# cp support-files/mysql.server /etc/init.d/mysql
    # chmod 755 /etc/init.d/mysql
    # chkconfig mysql on
     
      
     
      
       1
       2
       3

6、 为MySQL配置环境变量  
将mysql的bin目录加到PATH中，有利于以后管理和维护，在/etc/profile中加入myslq/bin，同时增加两个别名方便操作：

# export PATH=/usr/local/mysql/bin:$PATH
    # alias mysql_start="mysqld_safe &"
    # alias mysql_stop="mysqladmin –u root -p shutdown"
     
      
     
      
       1
       2
       3

7、 启动mysql服务

# /etc/init.d/mysql start
    启动完成之后用ps -ef |grep mysql 命令查看是否启动
     
      
     
      
       1
       2

8、 登陆mysql

#mysql -uroot -p
     
      
     
      
       1

提示 在自行编译mysql,并连接本地机时,常出现找不到mysqld.sock的错误.

[root@lfqb data]# mysql -uroot
    ERROR 2002 (HY000): Can't connect to local MySQL server through socket '/tmp/mysqld.sock' (2)
     
      
     
      
       1
       2

错误原因: mysql客户端默认去找 /tmp/mysqld.sock 做连接,而mysqld.sock有可能不在此处.  
比如在 /var/lib/mysql/mysql.sock  
解决:

1: mysql -S /sock/path 指定真实的路径
    mysql -S /var/lib/mysql/mysql.sock
    
    
    2: 在/tmp下做一个链接,链接到真实sock文件.
    
    #  ln /var/lib/mysql/mysql.sock /tmp/mysqld.sock
    
    
    
    3: sock文件在linux环境中连接本地机才能使用,速度比用本机IP要快.
      你也可以强行指定用IP来连接.
    
    Sysbench的编译
    下载:http://bazaar.launchpad.net/~sysbench-developers/sysbench/0.5/files
    解压:
    #cd /path/to/sysbench
    # ./autogen.sh
    
    
    #../configure --prefix=/usr/local/sysbench --with-mysql-includes=/usr/include/mysql/ \
    --with-mysql-libs=/usr/lib64/mysql/
    #make && make install
    
    Sysbench测试CPU性能
    
    4线程查找20000以内的素数
    [root@localhost ~]# /usr/bin/sysbench --test=cpu --cpu-max-prime=20000 --num-threads=4 run
    sysbench 0.5:  multi-threaded system evaluation benchmark
    
    Running the test with following options:
    Number of threads: 4
    Doing CPU performance benchmark
    Threads started!
    Done.
    Maximum prime number checked in CPU test: 20000
    Test execution summary:
        total time:                          96.7210s
        total number of events:              10000
        total time taken by event execution: 114.5518
        per-request statistics:
             min:                                  2.17ms
             avg:                                 11.46ms
             max:                                 51.54ms
             approx.  95 percentile:              17.27ms
    
    Threads fairness:
        events (avg/stddev):           2500.0000/6.96
        execution time (avg/stddev):   28.6380/0.01
    
    
    注意: 服务器类型,有偏运算型的,有偏存储, 所需要的指标不一样.
    偏运算的(如视频转码服务器)要求CPU强,而存储则优先选择大容量和快速存储备.
    
    测试的数据,孤立起来看,是没有意义的.
    数据要有比较才有意义, 比如多台服务器的测试数据,比较CPU性能.
    
    Sysbench测试磁盘IO性能
    Sysbench --test=fileio --file-total-size=10G prepare
    解释: 创建10G的内容,供测试用
    Sysbench --test=fileio --file-total-size=10G --file-test-mode=rndrw run
    解释:针对10G文件,做随机读写,测试IO
    --file-test-mode 还可以为
    seqwr：顺序写入
    seqrewq：顺序重写
    seqrd：顺序读取
    rndrd：随机读取
    rndwr：随机写入
    rndrw：混合随机读写
    
    测试顺序读
    [root@localhost sysbench]# ./bin/sysbench --test=fileio --file-total-size=10G --file-test-mode=seqrd run 
    sysbench 0.5:  multi-threaded system evaluation benchmark
    
    Running the test with following options:
    Number of threads: 1
    Random number generator seed is 0 and will be ignored
    
    
    Extra file open flags: 0
    128 files, 80Mb each
    10Gb total file size
    Block size 16Kb
    Periodic FSYNC enabled, calling fsync() each 100 requests.
    Calling fsync() at the end of test, Enabled. Using synchronous I/O mode Doing sequential read test Threads started! Operations performed: 10000 reads, 0 writes, 0 Other = 10000 Total Read 156.25Mb Written 0b Total transferred 156.25Mb (201.09Mb/sec) 12869.92 Requests/sec executed General statistics: total time: 0.7770s total number of events: 10000 total time taken by event execution: 0.7595s response time: min: 0.01ms avg: 0.08ms max: 15.58ms approx. 95 percentile: 0.02ms Threads fairness: events (avg/stddev): 10000.0000/0.00 execution time (avg/stddev): 0.7595/0.00 测试随机读 [root@localhost sysbench]# ./bin/sysbench --test=fileio --file-total-size=10G --file-test-mode=rndrd run sysbench 0.5: multi-threaded system evaluation benchmark Running the test with following options: Number of threads: 1 Random number generator seed is 0 and will be ignored Extra file open flags: 0 128 files, 80Mb each 10Gb total file size Block size 16Kb Number of IO requests: 10000 Read/Write ratio for combined random IO test: 1.50 Periodic FSYNC enabled, calling fsync() each 100 requests. Calling fsync() at the end of test, Enabled. Using synchronous I/O mode Doing random read test Threads started! Operations performed: 10000 reads, 0 writes, 0 Other = 10000 Total Read 156.25Mb Written 0b Total transferred 156.25Mb (5.5698Mb/sec) 356.47 Requests/sec executed General statistics: total time: 28.0530s total number of events: 10000 total time taken by event execution: 28.0305s response time: min: 0.01ms avg: 2.80ms max: 76.69ms approx. 95 percentile: 11.42ms Threads fairness: events (avg/stddev): 10000.0000/0.00 execution time (avg/stddev): 28.0305/0.00 通过上两例对比, 顺序读与随机读之间的速度,差了40倍! 合理的索引+where语句会尽量达成顺序读. 作业:测试随机写与顺序写的速度差异 Sysbench测试事务性能 #sysbench --test=/path/to/sysbench-source/tests/db/oltp.lua --mysql-table-engine=innodb \ --mysql-user=root --db-driver=mysql --mysql-db=test \ --oltp-table-size=3000 --mysql-socket=/var/lib/mysql/mysql.sock prepare 实测结果: 双核CPU,8G内存,7200转机械硬盘 [root@localhost sysbecn]# ./bin/sysbench --test=/path/to/sysbench-source/tests/db/oltp.lua --mysql-table-engine=innodb --mysql-user=root --db-driver=mysql --mysql-db=test --oltp-table-size=3000 --mysql-socket=/var/lib/mysql/mysql.sock run sysbench 0.5: multi-threaded system evaluation benchmark Running the test with following options: Number of threads: 1 Random number generator seed is 0 and will be ignored Threads started! OLTP test statistics: queries performed: read: 140000 write: 40000 other: 20000 total: 200000 transactions: 10000 (30.84 per sec.) deadlocks: 0 (0.00 per sec.) read/write requests: 180000 (555.10 per sec.) other operations: 20000 (61.68 per sec.) General statistics: total time: 324.2651s total number of events: 10000 total time taken by event execution: 324.2226s response time: min: 22.56ms avg: 32.42ms max: 453.49ms approx. 95 percentile: 34.75ms Threads fairness: events (avg/stddev): 10000.0000/0.00 execution time (avg/stddev): 324.2226/0.00
     
      
     
      
       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
       63
       64
       65
       66
       67
       68
       69
       70
       71
       72
       73
       74
       75
       76
       77
       78
       79
       80
       81
       82
       83
       84
       85
       86
       87
       88
       89
       90
       91
       92
       93
       94
       95
       96
       97
       98
       99
       100
       101
       102
       103
       104
       105
       106
       107
       108
       109
       110
       111
       112
       113
       114
       115
       116
       117
       118
       119
       120
       121
       122
       123
       124
       125
       126
       127
       128
       129
       130
       131
       132
       133
       134
       135
       136
       137
       138
       139
       140
       141
       142
       143
       144
       145
       146
       147
       148
       149
       150
       151
       152
       153
       154
       155
       156
       157
       158
       159
       160
       161
       162
       163
       164
       165
       166
       167
       168
       169
       170
       171
       172
       173
       174
       175
       176
       177
       178
       179
       180
       181
       182
       183
       184
       185

#### Awk脚本 ####

Awk是一个简便的直译式的文本处理工具.
    擅长处理--多行多列的数据
    
    处理过程:
    While(还有下一行) {
    1:读取下一行,并把下一行赋给$0,各列赋给$1,$2...$N变量
            2: 用指定的命令来处理该行
    }
    
    如何处理1行数据?
    答: 分2部分,   pattern (条件)  + action(处理动作)
    
    第1个简单awk脚本
    awk  ‘{
       printf “%s\n” , $1}’ xx.txt  // 把xx.txt的每一行进行输出
    
    第2个简单awk脚本 统计mysql服务器信息
    mysqladmin -uroot ext|awk 
    'Queries/{q=$4}/Threads_connected/{c=$4}/Threads_running/{r=$4}END{printf("%d %d %d\n",q,c,r)}'
     
      
     
      
       1
       2
       3
       4
       5
       6
       7
       8
       9
       10
       11
       12
       13
       14
       15
       16
       17
       18

mysql性能调优的思路

0: 最好的优化-----不查询!
    这不是开玩笑.
    
    
    
    如果一台服务器出现长时间负载过高 /周期性负载过大,或偶尔卡住
    如何来处理?
    答:大的思路--------
    是周期性的变化还是偶尔问题?
    是服务器整体性能的问题, 还是某单条语句的问题?
    具体到单条语句, 这条语句是在等待上花的时间,还是查询上花的时间.
    唯一的办法-----监测并观察服务器的状态.
    1:观察服务器状态, 一般用如下2个命令
    Show status;  Show processlist;
    例: mysql> show status;
       #mysqladmin ext
    
    MySQL周期性波动试验
    实验目的: 模拟数据库高低峰时的压力波动,并会观察绘制波动曲线
    实验思路: 反复查询数据库并缓存入memcached, 缓存定期失效,
    观察记录服务器参数,并作图表.
    
    实验准备: nginx+php+memcached+awk+ab
    1: index.php  (随机访问3W条热数据,并储存在memcached中
    2: memcached (储存查询结果)
    3: ab 压力测试工具
    4: awk脚本
    
    编译PHP-memcache扩展 (此步骤适合任意PHP扩展)
    以memcache-2.2.7为例(注意,这是PHP连接memcached的一个扩展)
    解压后,假设路径/path/to/memcache
    step1 : /path/to/memcached/# /path/to/php/bin/phpize #作用是根据PHP的版本生成编译文件
    此步骤后,memcache目录下,产生configure文件
    step2: configure --with-php-config =/path/to/php/bin/php-config
    step3: make && make install
    此步骤编译出一个memcache.so文件
    step4: 修改php.ini引入memcache.so
    
    实验步骤:
    总数据3W以上,50个并发,每秒请求500-1000次
    请求结果缓存在memcache,生命周期为60秒,
    (生命周期要结合请求周期来制定,比如3万条数据随机,每秒1000条,30秒能走一遍,生命周期可设为60秒)
    观察mysql连接数,每秒请求数的周期变化.
    
    
    
    看上图,mysql的每秒请求数,随着缓存失效,有短时间的高峰.
    
    解决办法:
    1: 减少无关请求(业务逻辑层面,暂不讨论,但其实是最有效的手段)
    2: 如果请求数是一定的,不可减少的. 我们要尽量让请求数平稳,不要有剧烈波动.
    很多时候,不是服务器撑不住总的查询量,而是在某个时间段撑不住高峰请求.
    
    该实际问题最后的解决:
    
    
    ----夜间负载低时,集中失效.
    短时间内会有波峰,但夜间访问量少,因此波峰并不剧烈,当到上午10点左右人多时,缓存已经建立了一部分. 白天时,波峰也不剧烈.
    
    
    或者让缓存的生命周期在一定范围内随机,也可以减缓波峰剧烈的情况
    我们把实验中的生命周期由80秒,改为[40-120秒],其他实验条件不变.
    得到如下曲线
    
    
    
    可以看出,稳定运行后,请求在[1000-1500]之间波动,
    而固定缓存周期是,请求在[500-1700]之间波动.
    
    实验附件:
    bench.php
    <?php
    
    // 30K hot news
    $rangeid = rand(1,30000)+13000000;
    
    $mconn = memcache_connect('localhost',11211);
    
    if( ($com = memcache_get($mconn,$rangeid)) === false) {
     $conn = mysql_connect('localhost','root');
    
     $sql = 'use bigdata';
     mysql_query($sql,$conn);
    
     $sql = 'set names utf8';
     mysql_query($sql,$conn);
     $sql = 'select id,name,brief from lx_com where id=' . $rangeid;
    
     $rs = mysql_query($sql,$conn);
     $com = mysql_fetch_assoc($rs);
    
     memcache_add($mconn , $rangeid , $com , false, mt_rand(40,120));
    } else {
      echo 'from cache';
    }
    print_r($com);
    
    status.sh
    #!/bin/bash
    while true
    do
    mysqladmin -uroot ext|awk '/Queries/{q=$4}/Threads_connected/{c=$4}/Threads_running/{r=$4}END{printf("%d %d %d\n",q,c,r)}' >> status.txt
    sleep 1
    done
    
    对于不规则的延迟现象的观察
    
    不规则的延迟现象往往是由于效率低下的语句造成的,如何抓到这些效率低的语句.
    可以用show processlist命令长期观察,或用慢查询.
    
    Show processlist;
    这个命令是显示当前所有连接的工作状态.
    
    #!/bin/bash
    while true
    do
    mysql -uroot -e 'show processlist\G'|grep State:|uniq -c|sort -rn
    echo '---'
    sleep 1
    Done
    
     ```
    如果观察到以下状态,则需要注意
    converting HEAP to MyISAM 查询结果太大时,把结果放在磁盘 (语句写的不好,取数据太多)
    create tmp table             创建临时表(如group时储存中间结果,说明索引建的不好)
    Copying to tmp table on disk   把内存临时表复制到磁盘 (索引不好,表字段选的不好)
    locked         被其他查询锁住 (一般在使用事务时易发生,互联网应用不常发生)
    logging slow query 记录慢查询
    
    mysql 5.5 以后加了一个profile设置,可以观察到具体语句的执行步骤.
    0:查看profile是否开启
     
      
     
      
       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
       63
       64
       65
       66
       67
       68
       69
       70
       71
       72
       73
       74
       75
       76
       77
       78
       79
       80
       81
       82
       83
       84
       85
       86
       87
       88
       89
       90
       91
       92
       93
       94
       95
       96
       97
       98
       99
       100
       101
       102
       103
       104
       105
       106
       107
       108
       109
       110
       111
       112
       113
       114
       115
       116
       117
       118
       119
       120
       121
       122
       123
       124
       125
       126
       127
       128
       129
       130
       131

> Show variables like ‘profiling’  
> \+—————+——-+  
> | Variable\_name | Value |  
> \+—————+——-+  
> | profiling | OFF |  
> \+—————+——-+

1:> set profiling=on;  
\+—————+——-+  
| Variable\_name | Value |  
\+—————+——-+  
| profiling | On |  
\+—————+——-+

mysql> show profile for query 1;  
\+———————-+———-+  
| Status | Duration |  
\+———————-+———-+  
| starting | 0.000058 |  
| checking permissions | 0.000008 |  
…  
…  
| Sorting result | 0.000004 |  
| Sending data | 0.000120 |  
| end | 0.000005 |  
| query end | 0.000006 |  
| closing tables | 0.000008 |  
| freeing items | 0.000023 |  
| logging slow query | 0.000003 |  
| cleaning up | 0.000004 |  
\+———————-+———-+  
“\`

##### 疑问; 如何定位到有问题的语句? #####

答:
    1: 开启服务器慢查询
    2: 了解临时表的使用规则      
    3: 经验
    MySQL如何使用内部临时表
    官方:http://dev.mysql.com/doc/refman/5.5/en/internal-temporary-tables.html
    
    在处理请求的某些场景中,服务器创建内部临时表. 即表以MEMORY引擎在内存中处理,或以MyISAM引擎储存在磁盘上处理.如果表过大,服务器可能会把内存中的临时表转存在磁盘上.
    用户不能直接控制服务器内部用内存还是磁盘存储临时表
    
    临时表在如下几种情况被创建:
    如果group by 的列没有索引,必产生内部临时表,
    如果order by 与group by为不同列时,或多表联查时order by ,group by 包含的列不是第一张表的列,将会产生临时表(实验1)
    mysql> explain select goods_id,cat_id from goods group by cat_id \G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: goods
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 20
            Extra: Using temporary; Using filesort
    1 row in set (0.00 sec)
    
    mysql> alter table goods add index cat_id(cat_id);
    Query OK, 0 rows affected (0.18 sec)
    Records: 0  Duplicates: 0  Warnings: 0
    
    mysql> explain select goods_id,cat_id from goods group by cat_id \G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: goods
             type: index
    possible_keys: NULL
              key: cat_id
          key_len: 2
              ref: NULL
             rows: 20
            Extra: Using index
    mysql> explain select goods_id,cat_id from goods group by cat_id order by 1 \G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: goods
             type: index
    possible_keys: NULL
              key: PRIMARY
          key_len: 3
              ref: NULL
             rows: 20
            Extra: Using temporary
    
    distinct 与order by 一起使用可能会产生临时表(实验2)
    mysql> explain select distinct cat_id from goods order by 1 \G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: goods
             type: index
    possible_keys: NULL
              key: cat_id
          key_len: 2
              ref: NULL
             rows: 20
            Extra: Using index
    1 row in set (0.00 sec)
    
    mysql> explain select distinct cat_id from goods order by goods_id \G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: goods
             type: index
    possible_keys: NULL
              key: PRIMARY
          key_len: 3
              ref: NULL
             rows: 20
            Extra: Using temporary
    1 row in set (0.00 sec)                                               
    mysql> explain select distinct cat_id from goods order by click_count\G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: goods
             type: index
    possible_keys: NULL
              key: cat_id
          key_len: 2
              ref: NULL
             rows: 20
            Extra: Using temporary; Using filesort
    1 row in set (0.00 sec)
    如果使用SQL_SMALL_RESULT,MySQL会使用内存临时表,除非查询中有一些必须要把临时表建立在磁盘上.
    union合并查询时会用到临时表
    某些视图会用到临时表,如使用temptable方式建立,或使用union或聚合查询的视图
    
    想确定查询是否需要临时表,可以用EXPLAIN查询计划,并查看Extra列,看是否有Using temporary.
    
    如果一开始在内存中产生的临时表变大,会自动转化为磁盘临时表. 内存中临时表的最大值为tmp_table_size和max_heap_size中较小值.
    这和create table时显示指定的内存表不一样:这些表只受max_heap_table_size系统参数影响.
    
    当服务器创建内部临时表(无论在内存还是在磁盘),create_tmp_tables变量都会增加.
    如果创建了在磁盘上内部临时表(无论是初始创建还是由in-memory转化),
    create_tmp_disk_tables 变量都会增加.
    
    一些情况下限制了内存临时表的使用,而使用磁盘临时表:
    
    (使用了内部临时表的前提下) 语句中存在BLOB或TEXT列(实验3)
    在GROUP BY 或 DISTINCT子句中有大于512字节的string列
    在UNION或UNION ALL时,SELECT语句里有大于512字节的string列.
    mysql> create table t1 (
    num int,
    intro text(1000)
    );
    
    mysql>insert into t1 values (3,'this is USA' , 4,'China');
    mysql> show status like '%tmp%';
    +-------------------------+-------+
    | Variable_name           | Value |
    +-------------------------+-------+
    | Created_tmp_disk_tables | 5     |
    | Created_tmp_files       | 9     |
    | Created_tmp_tables      | 74    |
    +-------------------------+-------+
    3 rows in set (0.00 sec)
    
    mysql> select * from t1 group by num;
    +------+-------------+
    | num  | intro       |
    +------+-------------+
    |    3 | this is USA |
    |    4 | China       |
    +------+-------------+
    2 rows in set (0.00 sec)
    
    mysql> show status like '%tmp%';
    +-------------------------+-------+
    | Variable_name           | Value |
    +-------------------------+-------+
    | Created_tmp_disk_tables | 6     |
    | Created_tmp_files       | 9     |
    | Created_tmp_tables      | 75    |
    +-------------------------+-------+
    3 rows in set (0.00 sec)
    
    通过前面实验,可以看出,数据库的优化是一个系统工程:
    
    建表: 表结构的拆分,如核心字段都用int,char,enum等定长结构
         非核心字段,或用到text,超长的varchar,拆出来单放一张表.
    
    建索引: 合理的索引可以减少内部临时表(索引优化策略里详解)
    
    写语句: 不合理的语句将导致大量数据传输以及内部临时表的使用.
    表的优化与列类型选择
     ```
    ##### 表的优化:
     
      
     
      
       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
       63
       64
       65
       66
       67
       68
       69
       70
       71
       72
       73
       74
       75
       76
       77
       78
       79
       80
       81
       82
       83
       84
       85
       86
       87
       88
       89
       90
       91
       92
       93
       94
       95
       96
       97
       98
       99
       100
       101
       102
       103
       104
       105
       106
       107
       108
       109
       110
       111
       112
       113
       114
       115
       116
       117
       118
       119
       120
       121
       122
       123
       124
       125
       126
       127
       128
       129
       130
       131
       132
       133
       134
       135
       136
       137
       138
       139
       140
       141
       142
       143
       144
       145
       146
       147
       148
       149
       150
       151
       152
       153
       154
       155
       156
       157
       158
       159
       160
       161

1: 定长与变长分离  
如 id int, 占4个字节, char(4) 占4个字符长度,也是定长, time  
即每一单元值占的字节是固定的.  
核心且常用字段,宜建成定长,放在一张表.

而varchar, text,blob,这种变长字段,适合单放一张表, 用主键与核心表关联起来.

2:常用字段和不常用字段要分离.  
需要结合网站具体的业务来分析,分析字段的查询场景,查询频度低的字段,单拆出来.

3:合理添加冗余字段.  
看如下BBS的效果

每个版块里,有N条帖子, 在首页,显示了版块信息,和版块下的帖子数.  
这是如何做的?  
boardid boardname postnum  
8 开班信息 2  
9 每日视频及代码 1

postid boardid title  
123 8 论坛开张了  
129 8 灌水  
133 9 来一帖

如果board表只有前2列,则需要取出版块后,  
再查post表,select count(\*) from post group by board\_id,得出每个版块下的帖子数.

如果有postnum字段,每发一个帖子时,对postnum字段+1;  
再查询版块下的帖子数时, 只需要1条语句直接查boardid  
select boradid, boardname,postnum from board;  
“\`

##### 典型的”空间换时间” #####

列选择原则:
    1:字段类型优先级 整型 > date,time > enum,char>varchar > blob
    列的特点分析:
    整型: 定长,没有国家/地区之分,没有字符集的差异
    time定长,运算快,节省空间. 考虑时区,写sql时不方便 where > ‘2005-10-12’;
    enum: 能起来约束值的目的, 内部用整型来存储,但与char联查时,内部要经历串与值的转化
    Char 定长, 考虑字符集和(排序)校对集
    varchar, 不定长 要考虑字符集的转换与排序时的校对集,速度慢.
    text/Blob 无法使用内存临时表
    
    附: 关于date/time的选择,大师的明确意见
    http://www.xaprb.com/blog/2014/01/30/timestamps-in-mysql/
    
    性别:  以utf8为例
    char(1) , 3个字长字节
    enum(‘男’,’女’);  // 内部转成数字来存,多了一个转换过程
    tinyint() ,  // 0 1 2 // 定长1个字节.
    
    2: 够用就行,不要慷慨 (如smallint,varchar(N))
    原因: 大的字段浪费内存,影响速度,
    以年龄为例 tinyint unsigned not null ,可以存储255岁,足够. 用int浪费了3个字节
    以varchar(10) ,varchar(300)存储的内容相同, 但在表联查时,varchar(300)要花更多内存
    
    3: 尽量避免用NULL()
    原因: NULL不利于索引,要用特殊的字节来标注.
    在磁盘上占据的空间其实更大.
    
    实验:
    可以建立2张字段相同的表,一个允许为null,一个不允许为Null,各加入1万条,查看索引文件的大小. 可以发现,为null的索引要大些.(mysql5.5里,关于null已经做了优化,大小区别已不明显)
    另外: null也不便于查询,
    where 列名=null;   
    where 列名!=null; 都查不到值,
    where 列名 is null  ,或is not null 才可以查询.
    create table dictnn (
    id int,
    word varchar(14) not null default '',
    key(word)
    )engine myisam charset utf8;
    
    create table dictyn (
    id int,
    word varchar(14),
    key(word)
    )engine myisam charset utf8;
    
    alter table dictnn disable keys;
    alter table dictyn disable keys;
    
    insert into dictnn select id,if(id%2,word,'') from dict limit 10000;
    insert into dictyn select id,if(id%2,word,null) from dict limit 10000;
    
    alert table dictnn enable keys;
    alter table dictyn enable keys;
    
    
    Enum列的说明
    1: enum列在内部是用整型来储存的
    2: enum列与enum列相关联速度最快
    3: enum列比(var)char 的弱势---在碰到与char关联时,要转化. 要花时间.
    4: 优势在于,当char非常长时,enum依然是整型固定长度.
    当查询的数据量越大时,enum的优势越明显.
    5: enum与char/varchar关联 ,因为要转化,速度要比enum->enum,char->char要慢,
    但有时也这样用-----就是在数据量特别大时,可以节省IO.
    试验:
    create table t2 (
    id int,
    gender enum('man','woman'),
    key(gender)
    )engine myisam charset utf8;
    
    create table t3 (
    id int,
    gender char(5) not null default '',
    key(gender)
    )engine myisam charset utf8;
    
    alter table t2 disable keys;
    alter table t3 disable keys;
    
    insert into t2 select id,if(id%2,'man','woman') from dict limit 10000;
    insert into t3 select id,if(id%2,'man','woman') from dict limit 10000;
    
    alter table t2 enable keys;
    alter table t3 enable keys;
    
    mysql> select count(*) from t2 as ta,t2 as tb where ta.gender=tb.gender mysql> select count(*) from t3 as ta,t3 as tb where ta.gender=tb.gender
    
    列<---->列 时间
    Enum<--->enum 10.53
    Char<---->char 24.65
    Enum<---->char 18.22
    如果t2表的优势不明显, 加大t3的gender列 ,char(15), char(20)...
    随着t3 gender列的变大,t2表优势逐渐明显.
    
    原因----无论enum(‘manmaman’,’womanwomanwoman’) 枚举的字符多长,内部都是用整型表示, 在内存中产生的数据大小不变,而char型,却在内存中产生的数据越来越多.
    
    总结: enum 和enum类型关联速度比较快
         Enum 类型 节省了IO
     ```
    
    ##### 索引优化策略
     ```
    1:索引类型
      1.1 B-tree索引
      注: 名叫btree索引,大的方面看,都用的平衡树,但具体的实现上, 各引擎稍有不同,
    比如,严格的说,NDB引擎,使用的是T-tree
      Myisam,innodb中,默认用B-tree索引
    
    但抽象一下---B-tree系统,可理解为”排好序的快速查找结构”.  
    
    1.2 hash索引
         在memory表里,默认是hash索引, hash的理论查询时间复杂度为O(1)
    
    疑问: 既然hash的查找如此高效,为什么不都用hash索引?
    答:
    1:hash函数计算后的结果,是随机的,如果是在磁盘上放置数据,
    比主键为id为例, 那么随着id的增长, id对应的行,在磁盘上随机放置.
    2: 不法对范围查询进行优化.
    3: 无法利用前缀索引. 比如 在btree中, field列的值“hellopworld”,并加索引
    查询 xx=helloword,自然可以利用索引, xx=hello,也可以利用索引. (左前缀索引)
    因为hash(‘helloword’),和hash(‘hello’),两者的关系仍为随机
    4: 排序也无法优化.
    5: 必须回行.就是说 通过索引拿到数据位置,必须回到表中取数据
    
    2: btree索引的常见误区
     2.1 在where条件常用的列上都加上索引
      例: where cat_id=3 and price>100 ; //查询第3个栏目,100元以上的商品
      误: cat_id上,和, price上都加上索引.
      错: 只能用上cat_id或Price索引,因为是独立的索引,同时只能用上1个.
    
     2.2 在多列上建立索引后,查询哪个列,索引都将发挥作用
    误: 多列索引上,索引发挥作用,需要满足左前缀要求.
    以 index(a,b,c) 为例,
    语句 索引是否发挥作用
    Where a=3 是,只使用了a列
    Where a=3 and b=5 是,使用了a,b列
    Where a=3 and b=5 and c=4 是,使用了abc
    Where b=3 / where c=4 否
    Where a=3 and c=4 a列能发挥索引,c不能
    Where a=3 and b>10 and c=7 A能利用,b能利用, C不能利用
    同上,where a=3 and b like ‘xxxx%’ and c=7 A能用,B能用,C不能用
    
    
    为便于理解, 假设ABC各10米长的木板, 河面宽30米.
    全值索引是则木板长10米,
    Like,左前缀及范围查询, 则木板长6米,
    
    自己拼接一下,能否过河对岸,就知道索引能否利用上.
    如上例中, where a=3 and b>10, and c=7,
    A板长10米,A列索引发挥作用
    A板正常接B板, B板索引发挥作用
    B板短了,接不到C板, C列的索引不发挥作用.
    
    多列索引经典题目:
    http://www.zixue.it/thread-9218-1-4.html
    
    假设某个表有一个联合索引（c1,c2,c3,c4）一下——只能使用该联合索引的c1,c2,c3部分
    A where c1=x and c2=x and c4>x and c3=x
    B where c1=x and c2=x and c4=x order by c3
    C where c1=x and c4= x group by c3,c2
    D where c1=x and c5=x order by c2,c3
    E where c1=x and c2=x and c5=? order by c2,c3
    
    create table t4 (
    c1 tinyint(1) not null default 0,
    c2 tinyint(1) not null default 0,
    c3 tinyint(1) not null default 0,
    c4 tinyint(1) not null default 0,
    c5 tinyint(1) not null default 0,
    index c1234(c1,c2,c3,c4)
    );
    insert into t4 values (1,3,5,6,7),(2,3,9,8,3),(4,3,2,7,5);
    
    对于A:
    c1=x and c2=x and c4>x and c3=x  <==等价==> c1=x and c2=x and c3=x and c4>x
    因此 c1,c2,c3,c4都能用上. 如下:
    mysql> explain select * from t4 where c1=1 and c2=2 and c4>3 and c3=3 \G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: t4
             type: range
    possible_keys: c1234
              key: c1234
          key_len: 4 #可以看出c1,c2,c3,c4索引都用上
              ref: NULL
             rows: 1
            Extra: Using where
    
    对于B: select * from t4 where c1=1 and c2=2 and c4=3 order by c3
    c1 ,c2索引用上了,在c2用到索引的基础上,c3是排好序的,因此不用额外排序.
    而c4没发挥作用.
    mysql> explain select * from t4 where c1=1 and c2=2 and c4=3 order by c3 \G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: t4
             type: ref
    possible_keys: c1234
              key: c1234
          key_len: 2
              ref: const,const
             rows: 1
            Extra: Using where
    1 row in set (0.00 sec)
    
    mysql> explain select * from t4 where c1=1 and c2=2 and c4=3 order by c5 \G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: t4
             type: ref
    possible_keys: c1234
              key: c1234
          key_len: 2
              ref: const,const
             rows: 1
            Extra: Using where; Using filesort
    1 row in set (0.00 sec)
    
    对于 C: 只用到c1索引,因为group by c3,c2的顺序无法利用c2,c3索引
    mysql> explain select * from t4 where c1=1 and c4=2 group by c3,c2 \G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: t4
             type: ref
    possible_keys: c1234
              key: c1234
          key_len: 1 #只用到c1,因为先用c3后用c2分组,导致c2,c3索引没发挥作用
              ref: const
             rows: 1
            Extra: Using where; Using temporary; Using filesort
    1 row in set (0.00 sec)
    
    mysql> explain select * from t4 where c1=1 and c4=2 group by c2,c3 \G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: t4
             type: ref
    possible_keys: c1234
              key: c1234
          key_len: 1
              ref: const
             rows: 1
            Extra: Using where
    1 row in set (0.00 sec)
    
    D语句: C1确定的基础上,c2是有序的,C2之下C3是有序的,因此c2,c3发挥的排序的作用.
    因此,没用到filesort
    mysql> explain select * from t4 where c1=1 and c5=2 order by c2,c3 \G  
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: t4
             type: ref
    possible_keys: c1234
              key: c1234
          key_len: 1
              ref: const
             rows: 1
            Extra: Using where
    1 row in set (0.00 sec)
    
    E: 这一句等价与 elect * from t4 where c1=1 and c2=3 and c5=2 order by c3;
    因为c2的值既是固定的,参与排序时并不考虑
    
    mysql> explain select * from t4 where c1=1 and c2=3 and c5=2 order by c2,c3 \G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: t4
             type: ref
    possible_keys: c1234
              key: c1234
          key_len: 2
              ref: const,const
             rows: 1
            Extra: Using where
    1 row in set (0.00 sec)
     ```
    ##### 一道面试题:
     
      
     
      
       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
       63
       64
       65
       66
       67
       68
       69
       70
       71
       72
       73
       74
       75
       76
       77
       78
       79
       80
       81
       82
       83
       84
       85
       86
       87
       88
       89
       90
       91
       92
       93
       94
       95
       96
       97
       98
       99
       100
       101
       102
       103
       104
       105
       106
       107
       108
       109
       110
       111
       112
       113
       114
       115
       116
       117
       118
       119
       120
       121
       122
       123
       124
       125
       126
       127
       128
       129
       130
       131
       132
       133
       134
       135
       136
       137
       138
       139
       140
       141
       142
       143
       144
       145
       146
       147
       148
       149
       150
       151
       152
       153
       154
       155
       156
       157
       158
       159
       160
       161
       162
       163
       164
       165
       166
       167
       168
       169
       170
       171
       172
       173
       174
       175
       176
       177
       178
       179
       180
       181
       182
       183
       184
       185
       186
       187
       188
       189
       190
       191
       192
       193
       194
       195
       196
       197
       198
       199
       200
       201
       202
       203
       204
       205
       206
       207
       208
       209
       210
       211
       212
       213
       214
       215
       216
       217
       218
       219
       220
       221
       222
       223
       224
       225
       226
       227
       228
       229
       230
       231
       232
       233
       234
       235
       236
       237
       238
       239
       240
       241
       242
       243
       244
       245
       246
       247
       248
       249
       250
       251
       252
       253
       254
       255
       256
       257
       258
       259
       260
       261
       262
       263
       264
       265
       266
       267
       268
       269
       270
       271
       272
       273
       274
       275
       276
       277
       278
       279
       280
       281
       282
       283
       284

有商品表, 有主键,goods\_id, 栏目列 cat\_id, 价格price  
说:在价格列上已经加了索引,但按价格查询还是很慢,  
问可能是什么原因,怎么解决?

答: 在实际场景中,一个电商网站的商品分类很多,直接在所有商品中,按价格查商品,是极少的,一般客户都来到分类下,然后再查.

改正: 去掉单独的Price列的索引, 加 (cat\_id,price)复合索引  
再查询.  
聚簇索引与非聚簇索引  
Myisam与innodb引擎,索引文件的异同

===============================================================

innodb的主索引文件上 直接存放该行数据,称为聚簇索引,次索引指向对主键的引用  
myisam中, 主索引和次索引,都指向物理行(磁盘位置).

注意: innodb来说,  
1: 主键索引 既存储索引值,又在叶子中存储行的数据  
2: 如果没有主键, 则会Unique key做主键  
3: 如果没有unique,则系统生成一个内部的rowid做主键.  
4: 像innodb中,主键的索引结构中,既存储了主键值,又存储了行数据,这种结构称为”聚簇索引”

聚簇索引  
优势: 根据主键查询条目比较少时,不用回行(数据就在主键节点下)  
劣势: 如果碰到不规则数据插入时,造成频繁的页分裂.

C) 聚簇索引的页分裂过程

实验: 聚簇索引使用随机值导致页频繁分裂影响速度  
过程:建立innodb表, 利用php连接mysql,  
分别规则插入10000条数据,不规则插入10000条数据  
观察时间的差异,体会聚簇索引,页分裂的影响.

create table t5(
    id int primary key,
    c1 varchar(500),
    c2 varchar(500),
    c3 varchar(500),
    c4 varchar(500),
    c5 varchar(500),
    c6 varchar(500)
    ) engine innodb charset utf8;
    create table t6(
    id int primary key,
    c1 varchar(500),
    c2 varchar(500),
    c3 varchar(500),
    c4 varchar(500),
    c5 varchar(500),
    c6 varchar(500)
    ) engine innodb charset utf8;
    
    // testinnodb.php
    $time_start = microtime_float();
    
    $str = str_repeat('hello',100);
    for($i=1;$i<=10000;$i++) {
       $sql = "insert into t5 values ($i,'$str' , '$str' , '$str' , '$str' , '$str' , '$str' )";
       //echo $sql;
       mysql_query($sql , $conn);
    }
    
    $time_end = microtime_float();
    echo 'seq insert cost' , ($time_end - $time_start) , "seconds\n";
    function microtime_float()
    {
        list($usec, $sec) = explode(" ", microtime());
        return ((float)$usec + (float)$sec);
    }
    
    // rndinnodb.php
    $base = range(1,10000);
    shuffle($base);
    
    $time_start = microtime_float();
    $str = str_repeat('hello',100);
    foreach($base as $i) {
       $sql = "insert into t6 values ($i,'$str' , '$str' , '$str' , '$str' , '$str' , '$str' )";
       //echo $sql;
       mysql_query($sql , $conn);
    }
    
    $time_end = microtime_float();
    echo 'rand insert cost' , ($time_end - $time_start) , "seconds\n";
    
    function microtime_float()
    {
        list($usec, $sec) = explode(" ", microtime());
        return ((float)$usec + (float)$sec);
    }
    
    字段数 混乱程度(步长) 顺序1000条(秒数) 乱序1000条(秒数) 顺序写入page页数 乱序写入page数
    1 1 54.365 53.438 62 91
    10 1 53.413 62.940 235 1301
    10 100  64.18  1329
    10 1000  67.512  1325
     
      
     
      
       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
       63
       64
       65

通过上面的规律可以看出—–  
1: innodb的buffer\_page 很强大.  
2: 聚簇索引的主键值,应尽量是连续增长的值,而不是要是随机值,  
(不要用随机字符串或UUID)  
否则会造成大量的页分裂与页移动.  
高性能索引策略  
0:对于innodb而言,因为节点下有数据文件,因此节点的分裂将会比较慢.  
对于innodb的主键,尽量用整型,而且是递增的整型.  
如果是无规律的数据,将会产生的页的分裂,影响速度.

索引覆盖:  
索引覆盖是指 如果查询的列恰好是索引的一部分,那么查询只需要在索引文件上进行,不需要回行到磁盘再找数据.  
这种查询速度非常快,称为”索引覆盖”

理想的索引  
1:查询频繁 2:区分度高 3:长度小 4: 尽量能覆盖常用查询字段.

1: 索引长度直接影响索引文件的大小,影响增删改的速度,并间接影响查询速度(占用内存多).

针对列中的值,从左往右截取部分,来建索引  
1: 截的越短, 重复度越高,区分度越小, 索引效果越不好  
2: 截的越长, 重复度越低,区分度越高, 索引效果越好,但带来的影响也越大–增删改变慢,并间影响查询速度.

所以, 我们要在 区分度 + 长度 两者上,取得一个平衡.

惯用手法: 截取不同长度,并测试其区分度,

mysql> select count(distinct left(word,6))/count(*) from dict; +---------------------------------------+
    | count(distinct left(word,6))/count(*) | +---------------------------------------+
    | 0.9992 | +---------------------------------------+
    1 row in set (0.30 sec)
    
    
    
    
    
    
    对于一般的系统应用: 区别度能达到0.1,索引的性能就可以接受.
     
      
     
      
       1
       2
       3
       4
       5
       6
       7
       8
       9
       10
       11
       12
       13
       14

##### 2:对于左前缀不易区分的列 ,建立索引的技巧 #####

如 url列
    http://www.baidu.com
    http://www.zixue.it
    列的前11个字符都是一样的,不易区分, 可以用如下2个办法来解决
    1: 把列内容倒过来存储,并建立索引
    Moc.udiab.www//:ptth
    Ti.euxiz.www//://ptth
    这样左前缀区分度大,
    
    2: 伪hash索引效果
    同时存 url_hash列
    
    
    3:多列索引
     3.1 多列索引的考虑因素---  
    列的查询频率 , 列的区分度,
    以ecshop商城为例, goods表中的cat_id,brand_id,做多列索引
    从区分度看,Brand_id区分度更高,
    mysql> select count(distinct cat_id) / count(*) from goods; +-----------------------------------+ | count(distinct cat_id) / count(*) |
    +-----------------------------------+
    |                            0.2903 |
    +-----------------------------------+
    1 row in set (0.00 sec)
    
    mysql> select count(distinct brand_id) / count(*) from goods; +-------------------------------------+ | count(distinct brand_id) / count(*) |
    +-------------------------------------+
    |                              0.3871 |
    +-------------------------------------+
    1 row in set (0.00 sec)
    
    
    但从 商城的实际业务业务看, 顾客一般先选大分类->小分类->品牌,
    最终选择 index(cat_id,brand_id)来建立索引
    有如下表(innodb引擎), sql语句在笔记中,
    给定日照市,查询子地区, 且查询子地区的功能非常频繁,
    如何优化索引及语句?
    
    +------+-----------+------+
    | id   | name      | pid  |
    +------+-----------+------+
    | .... | .... | .... |
    | 1584 | 日照市 | 1476 |
    | 1586 | 东港区 | 1584 |
    | 1587 | 五莲县 | 1584 |
    | 1588 | 莒县    | 1584 |
    +------+-----------+------+
    
    1: 不加任何索引,自身连接查询
    mysql> explain select s.id,s.name from it_area as p inner join it_area as s on p.id=s.pid   where p.name='日照市' \G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: p
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 3263
            Extra: Using where
    *************************** 2. row ***************************
               id: 1
      select_type: SIMPLE
            table: s
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 3263
            Extra: Using where; Using join buffer
    2 rows in set (0.00 sec)
    
    
    
    2: 给name加索引
    mysql> explain select s.id,s.name from it_area as p inner join it_area as s on p.id=s.pid   where p.name='日照市' \G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: p
             type: ref
    possible_keys: name
              key: name
          key_len: 93
              ref: const
             rows: 1
            Extra: Using where
    *************************** 2. row ***************************
               id: 1
      select_type: SIMPLE
            table: s
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 3243
            Extra: Using where; Using join buffer
    2 rows in set (0.00 sec)
    
    
    3: 在Pid上也加索引
    mysql> explain select s.id,s.name from it_area as p inner join it_area as s on p.id=s.pid   where p.name='日照市' \G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: p
             type: ref
    possible_keys: name
              key: name
          key_len: 93
              ref: const
             rows: 1
            Extra: Using where
    *************************** 2. row ***************************
               id: 1
      select_type: SIMPLE
            table: s
             type: ref
    possible_keys: pid
              key: pid
          key_len: 5
              ref: big_data.p.id
             rows: 4
            Extra: Using where
    2 rows in set (0.00 sec)
    
    延迟关联
    mysql> select * from it_area where name like '%东山%';
    +------+-----------+------+
    | id   | name      | pid  |
    +------+-----------+------+
    |  757 | 东山区 |  751 |
    | 1322 | 东山县 | 1314 |
    | 2118 | 东山区 | 2116 |
    | 3358 | 东山区 | 3350 |
    +------+-----------+------+
    4 rows in set (0.00 sec)
    
    分析: 这句话用到了索引覆盖没有?
    答: 没有,1 查询了所有列, 没有哪个索引,覆盖了所有列.
       2  like %xx%”,左右都是模糊查询, name本身,都没用上索引
    
    第2种做法:
    select a.* from it_area as a inner join (select id from it_area where name like '%东山%') as t on a.id=t.id;
    
    Show profiles; 查看效率:
    |       18 | 0.00183800 | select * from it_area where name like '%东山%'                                                                                                                                        
    |       20 | 0.00169300 | select a.* from it_area as a inner join (select id from it_area where name like '%东山%') as t on a.id=t.id         |
    
    发现 第2种做法,虽然语句复杂,但速度却稍占优势.
    
    第2种做法中, 内层查询,只沿着name索引层顺序走, name索引层包含了id值的.
    所以,走完索引层之后,找到所有合适的id,
    再通过join, 用id一次性查出所有列. 走完name列再取.
    
    第1种做法: 沿着name的索引文件走, 走到满足的条件的索引,就取出其id,
    并通过id去取数据, 边走边取.
    
    通过id查找行的过程被延后了. --- 这种技巧,称为”延迟关联”.
    
    索引与排序
    排序可能发生2种情况:
    1: 对于覆盖索引,直接在索引上查询时,就是有顺序的, using index
    2: 先取出数据,形成临时表做filesort(文件排序,但文件可能在磁盘上,也可能在内存中)
    
    我们的争取目标-----取出来的数据本身就是有序的! 利用索引来排序.
    
    比如: goods商品表, (cat_id,shop_price)组成联合索引,
    where cat_id=N order by shop_price ,可以利用索引来排序,
    select goods_id,cat_id,shop_price from goods order by shop_price;
    // using where,按照shop_price索引取出的结果,本身就是有序的.
    
    select goods_id,cat_id,shop_price from goods order by click_count;
    // using filesort 用到了文件排序,即取出的结果再次排序
     
      
     
      
       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
       63
       64
       65
       66
       67
       68
       69
       70
       71
       72
       73
       74
       75
       76
       77
       78
       79
       80
       81
       82
       83
       84
       85
       86
       87
       88
       89
       90
       91
       92
       93
       94
       95
       96
       97
       98
       99
       100
       101
       102
       103
       104
       105
       106
       107
       108
       109
       110
       111
       112
       113
       114
       115
       116
       117
       118
       119
       120
       121
       122
       123
       124
       125
       126
       127
       128
       129
       130
       131
       132
       133
       134
       135
       136
       137
       138
       139
       140
       141
       142
       143
       144
       145
       146
       147
       148
       149
       150
       151
       152
       153
       154
       155
       156
       157
       158
       159
       160
       161
       162
       163
       164
       165
       166
       167
       168
       169
       170
       171
       172
       173
       174
       175
       176
       177
       178
       179
       180

##### 重复索引与冗余索引 #####

重复索引: 是指 在同1个列(如age), 或者 顺序相同的几个列(age,school), 建立了多个索引,
    称为重复索引, 重复索引没有任何帮助,只会增大索引文件,拖慢更新速度, 去掉.
    
    冗余索引:
    冗余索引是指2个索引所覆盖的列有重叠, 称为冗余索引
    比如 x,m,列   , 加索引  index x(x),  index xm(x,m)
    x,xm索引, 两者的x列重叠了,  这种情况,称为冗余索引.
    
    甚至可以把 index mx(m,x) 索引也建立, mx, xm 也不是重复的,因为列的顺序不一样.
    
    
    索引碎片与维护
    在长期的数据更改过程中, 索引文件和数据文件,都将产生空洞,形成碎片.
    我们可以通过一个nop操作(不产生对数据实质影响的操作), 来修改表.
    比如: 表的引擎为innodb , 可以 alter table xxx engine innodb optimize table 表名 ,也可以修复. 注意: 修复表的数据及索引碎片,就会把所有的数据文件重新整理一遍,使之对齐. 这个过程,如果表的行数比较大,也是非常耗费资源的操作. 所以,不能频繁的修复. 如果表的Update操作很频率,可以按周/月,来修复. 如果不频繁,可以更长的周期来做修复.
     
      
     
      
       1
       2
       3
       4
       5
       6
       7
       8
       9
       10
       11
       12
       13
       14
       15
       16
       17
       18
       19
       20
       21
       22
       23
       24

#### sql语句优化 ####

1:  sql语句的时间花在哪儿?
    答: 等待时间 , 执行时间.
    这两个时间并非孤立的, 如果单条语句执行的快了,对其他语句的锁定的也就少了.
    所以,我们来分析如何降低执行时间.
    
    2: sql语句的执行时间,又花在哪儿了?
    答：
    a: 查 ----> 沿着索引查,甚至全表扫描
    b: 取 ----> 查到行后,把数据取出来(sending data)
    
    3: sql语句的优化思路？
    答: 不查, 通过业务逻辑来计算,
    比如论坛的注册会员数,我们可以根据前3个月统计的每天注册数, 用程序来估算.
    
    少查, 尽量精准数据,少取行. 我们观察新闻网站,评论内容等,一般一次性取列表 10-30条左右.
    
    必须要查,尽量走在索引上查询行.
    
    取时, 取尽量少的列.
    比如  select * from tableA, 就取出所有列, 不建议. 比如 select * from tableA,tableB, 取出A,B表的所有列. 4: 如果定量分析查的多少行,和是否沿着索引查? 答: 用explain来分析 explain的列分析 id: 代表select 语句的编号, 如果是连接查询,表之间是平等关系, select 编号都是1,从1开始. 如果某select中有子查询,则编号递增. mysql> explain select goods_id,goods_name from goods where goods_id in (sele ct goods_id from goods where cat_id=4) \G *************************** 1. row *************************** id: 1 select_type: PRIMARY table: goods type: ALL possible_keys: NULL key: NULL key_len: NULL ref: NULL rows: 31 Extra: Using where *************************** 2. row *************************** id: 2 select_type: DEPENDENT SUBQUERY table: goods type: unique_subquery possible_keys: PRIMARY,cat_id key: PRIMARY key_len: 3 ref: func rows: 1 Extra: Using where 2 rows in set (0.00 sec) select_type: 查询类型
     
      
     
      
       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

#### table: 查询针对的表 ####

有可能是
    实际的表名  如select * from t1;
    表的别名    如 select * from t2 as tmp;
    derived      如from型子查询时
    null         直接计算得结果,不用走表
    
    possible_key: 可能用到的索引
    注意: 系统估计可能用的几个索引,但最终,只能用1个.
    
    key : 最终用的索引.
    key_len: 使用的索引的最大长度
    
    type列: 是指查询的方式, 非常重要,是分析”查数据过程”的重要依据
    可能的值
    all:  意味着从表的第1行,往后,逐行做全表扫描.,运气不好扫描到最后一行.
    
    例: 把goods_name列上的索引去掉, 并根据goods_name来查询
    mysql> explain select goods_name from goods where goods_name='诺基亚N85' \G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: goods
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 31
            Extra: Using where
    1 row in set (0.00 sec)
    
    index: 比all性能稍好一点，
    通俗的说: all 扫描所有的数据行,相当于data_all  index 扫描所有的索引节点,相当于index_all
     
      
     
      
       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

２种情况可能出现：

> １：索引覆盖的查询情况下, 能利用上索引,但是又必须全索引扫描.

mysql> explain select goods_id from goods where goods_id=1 or goods_id+1>20
    \G
    *************************** 1. row ***************************
     id: 1
      select_type: SIMPLE
     table: goods
     type: index
    possible_keys: PRIMARY
     key: PRIMARY
     key_len: 3
     ref: NULL
     rows: 31
     Extra: Using where; Using index
    1 row in set (0.00 sec)
    
    mysql> explain select goods_id,click_count from  goods where goods_id=1 or go
    ods_id+1>20 \G
    *************************** 1. row ***************************
     id: 1
      select_type: SIMPLE
     table: goods
     type: ALL
    possible_keys: PRIMARY
     key: NULL
     key_len: NULL
     ref: NULL
     rows: 31
     Extra: Using where
    1 row in set (0.00 sec)
     
      
     
      
       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

> 2: 是利用索引来进行排序,但取出所有的节点

select goods_id from  goods order by goods_id desc;
    分析: 没有加where条件, 就得取所有索引节点,同时,又没有回行,只取索引节点.
    再排序,经过所有索引节点.
    
    mysql> explain select goods_id from  goods order by goods_id asc\G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table:  goods
             type: index
    possible_keys: NULL
              key: PRIMARY
          key_len: 3
              ref: NULL
             rows: 31
            Extra: Using index
    1 row in set (0.00 sec)
    
    range: 意思是查询时,能根据索引做范围的扫描
    mysql> explain select goods_id,goods_name,shop_price from  goods where goods
    id >25 \G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table:  goods
             type: range
    possible_keys: PRIMARY
              key: PRIMARY
          key_len: 3
              ref: NULL
             rows: 8
            Extra: Using where
    1 row in set (0.00 sec)
    
    ref  意思是指 通过索引列,可以直接引用到某些数据行
    mysql> explain select goods_id,goods_name from  goods where cat_id=4 \G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table:  goods
             type: ref
    possible_keys: cat_id
              key: cat_id
          key_len: 2
              ref: const
             rows: 3
            Extra:
    1 row in set (0.00 sec)
    
    在这个例子中,通过cat_id索引 指向N行goods数据,来查得结果.
    
    eq_ref 是指,通过索引列,直接引用某1行数据
    常见于连接查询中
    mysql> explain select goods_id,shop_price from  goods innert join ecs_catego
    y using(cat_id) where goods_id> 25 \G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: innert
             type: range
    possible_keys: PRIMARY,cat_id
              key: PRIMARY
          key_len: 3
              ref: NULL
             rows: 8
            Extra: Using where
    *************************** 2. row ***************************
               id: 1
      select_type: SIMPLE
            table: ecs_category
             type: eq_ref
    possible_keys: PRIMARY
              key: PRIMARY
          key_len: 2
              ref: shop.innert.cat_id
             rows: 1
            Extra: Using index
    2 rows in set (0.00 sec)
    
    const, system, null  这3个分别指查询优化到常量级别, 甚至不需要查找时间.
    
    一般按照主键来查询时,易出现const,system
    或者直接查询某个表达式,不经过表时, 出现NULL
    
    mysql> explain select goods_id,goods_name,click_count from  goods wher
    _id=4 \G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table:  goods
             type: const
    possible_keys: PRIMARY
              key: PRIMARY
          key_len: 3
              ref: const
             rows: 1
            Extra:
    1 row in set (0.00 sec)
    
    mysql> explain select max(goods_id) from  goods \G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table: NULL
             type: NULL myisam表的max,min,count在表中优化过,不需要\真正查找,为NULL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: NULL
            Extra: Select tables optimized away
    1 row in set (0.00 sec)
    
    ref列 指连接查询时, 表之间的字段引用关系.
    mysql> explain select goods_id,cat_name,goods_name from  goods inner join ec
    _category using(cat_id) where ecs_category.cat_name='' \G
    *************************** 1. row ***************************
               id: 1
      select_type: SIMPLE
            table:  goods
             type: ALL
    possible_keys: cat_id
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 31
            Extra:
    *************************** 2. row ***************************
               id: 1
      select_type: SIMPLE
            table: ecs_category
             type: eq_ref
    possible_keys: PRIMARY
              key: PRIMARY
          key_len: 2
              ref: shop. goods.cat_id
             rows: 1
            Extra: Using where
    2 rows in set (0.00 sec)
    
    rows : 是指估计要扫描多少行.
    
    extra:
    index: 是指用到了索引覆盖,效率非常高
    using where 是指光靠索引定位不了,还得where判断一下
    using temporary 是指用上了临时表, group by 与order by 不同列时,或group by ,order by 别的表的列.
    using filesort : 文件排序(文件可能在磁盘,也可能在内存), (?????
    
    select sum(shop_price) from  goods group by cat_id(????  这句话,用到了临时表和文件排序)
    
    in 型子查询引出的陷阱
    
    题: 在ecshop商城表中,查询6号栏目的商品, (注,6号是一个大栏目)
    最直观的: mysql> select goods_id,cat_id,goods_name from  goods where cat_id in (select
    cat_id from ecs_category where parent_id=6);
    误区: 给我们的感觉是, 先查到内层的6号栏目的子栏目,如7,8,9,11
    然后外层, cat_id in (7,8,9,11)
    
    事实: 如下图, goods表全扫描, 并逐行与category表对照,看parent_id=6是否成立
    
    
    
    原因: mysql的查询优化器,针对In型做优化,被改成了exists的执行效果.
    当goods表越大时, 查询速度越慢.
    
    改进: 用连接查询来代替子查询
     explain select goods_id,g.cat_id,g.goods_name from  goods as g
     inner join (select cat_id from ecs_category where parent_id=6) as t
     using(cat_id) \G
    
    内层 select cat_id from ecs_category where parent_id=6 ; 用到Parent_id索引, 返回4行
    +--------+
    | cat_id |
    +--------+
    |      7 |
    |      8 |
    |      9 |
    |     11 |
    +--------+    形成结果,设为t
    
    *************************** 3. row ***************************
               id: 2
      select_type: DERIVED
            table: ecs_category
             type: ref
    possible_keys: parent_id
              key: parent_id
          key_len: 2
              ref:
             rows: 4
            Extra:
    3 rows in set (0.00 sec)
    
    
    第2次查询,
    t和 goods 通过 cat_id 相连,
    因为cat_id在 goods表中有索引, 所以相当于用7,8,911,快速匹配上 goods的行.
    *************************** 2. row ***************************
               id: 1
      select_type: PRIMARY
            table: g
             type: ref
    possible_keys: cat_id
              key: cat_id
          key_len: 2
              ref: t.cat_id
             rows: 6
            Extra:
    
    第1次查询 :
    是把上面2次的中间结果,直接取回.
    *************************** 1. row ***************************
               id: 1
      select_type: PRIMARY
            table: <derived2>
             type: ALL
    possible_keys: NULL
              key: NULL
          key_len: NULL
              ref: NULL
             rows: 4
            Extra:
    
    exists子查询
    题: 查询有商品的栏目.
    按上面的理解,我们用join来操作,如下:
    mysql> select c.cat_id,cat_name from ecs_category as c inner join  goods as g
     on c.cat_id=g.cat_id group by cat_name; (见36)
    
    优化1:  在group时, 用带有索引的列来group, 速度会稍快一些,另外,
    用int型 比 char型 分组,也要快一些.(见37)
    
    
    优化2: 在group时, 我们假设只取了A表的内容,group by 的列,尽量用A表的列,
    会比B表的列要快.(见38)
    
    优化3: 从语义上去优化
    select cat_id,cat_name from ecs_category where exists(select *from  goods where  goods.cat_id=ecs_category.cat_id) (见40)
    
    |       36 | 0.00039075 | select c.cat_id,cat_name from ecs_category as c inner
    join  goods as g on c.cat_id=g.cat_id group by cat_name
                  |
    |       37 | 0.00038675 | select c.cat_id,cat_name from ecs_category as c inner
    join  goods as g on c.cat_id=g.cat_id group by cat_id
                  |
    |       38 | 0.00035650 | select c.cat_id,cat_name from ecs_category as c inner
    join  goods as g on c.cat_id=g.cat_id group by c.cat_id
                  |
    |       40 | 0.00033500 | select cat_id,cat_name from ecs_category where exists
    (select * from  goods where  goods.cat_id=ecs_category.cat_id)
                  |
    
    from 型子查询:
    注意::内层from语句查到的临时表, 是没有索引的.
    所以: from的返回内容要尽量少.
    
    奇技淫巧!
    min/max优化 在表中,一般都是经过优化的. 如下地区表
    id area pid
    1 中国 0
    2 北京 1
    ...  
    3115  3113
    
    我们查min(id), id是主键,查Min(id)非常快.
    
    但是,pid上没有索引, 现在要求查询3113地区的min(id);
    
    select min(id) from it_area where pid=69;  
    
    试想 id是有顺序的,(默认索引是升续排列), 因此,如果我们沿着id的索引方向走,
    那么  第1个 pid=69的索引结点,他的id就正好是最小的id.
    select  id  from it_area use index(primary) where pid=69 limit 1;
    
    |       12 | 0.00128100 | select min(id) from it_area where pid=69                         |
    |       13 | 0.00017000 | select id from it_area  use index(primary) where pid=69  limit 1 |
    
    改进后的速度虽然快,但语义已经非常不清晰,不建议这么做,仅仅是实验目的.
    
    count() 优化
    误区:
    1:myisam的count()非常快
    答: 是比较快,.但仅限于查询表的”所有行”比较快, 因为Myisam对行数进行了存储.
    一旦有条件的查询, 速度就不再快了.尤其是where条件的列上没有索引.
    
    2: 假如,id<100的商家都是我们内部测试的,我们想查查真实的商家有多少?
    select count(*) from lx_com where id>=100; (1000多万行用了6.X秒) 小技巧: select count(*) from lx_com; 快
    select count(*) from lx_com where id<100; 快 select count(*) frol lx_com -select count(*) from lx_com where id<100; 快 select (select count(*) from lx_com) - (select count(*) from lx_com where id<100) 3: group by 注意: 1:分组用于统计,而不用于筛选数据. 比如: 统计平均分,最高分,适合, 但用于筛选重复数据,则不适合. 以及用索引来避免临时表和文件排序 2: 以A,B表连接为例 ,主要查询A表的列, 那么 group by ,order by 的列尽量相同,而且列应该显示声明为A的列 4: union优化 注意: union all 不过滤 效率提高,如非必须,请用union all 因为 union去重的代价非常高, 放在程序里去重. limit 及翻页优化 limit offset,N, 当offset非常大时, 效率极低, 原因是mysql并不是跳过offset行,然后单取N行, 而是取offset+N行,返回放弃前offset行,返回N行. 效率较低,当offset越大时,效率越低 优化办法: 1: 从业务上去解决 办法: 不允许翻过100页 以百度为例,一般翻页到70页左右. 1:不用offset,用条件查询. 例: mysql> select id,name from lx_com limit 5000000,10; +---------+--------------------------------------------+ | id | name | +---------+--------------------------------------------+ | 5554609 | 温泉县人民政府供暖中心 | .................. | 5554618 | 温泉县邮政鸿盛公司 | +---------+--------------------------------------------+ 10 rows in set (5.33 sec) mysql> select id,name from lx_com where id>5000000 limit 10; +---------+--------------------------------------------------------+ | id | name | +---------+--------------------------------------------------------+ | 5000001 | 南宁市嘉氏百货有限责任公司 | ................. | 5000002 | 南宁市友达电线电缆有限公司 | +---------+--------------------------------------------------------+ 10 rows in set (0.00 sec) 问题: 2次的结果不一致 原因: 数据被物理删除过,有空洞. 解决: 数据不进行物理删除(可以逻辑删除). 最终在页面上显示数据时,逻辑删除的条目不显示即可. (一般来说,大网站的数据都是不物理删除的,只做逻辑删除 ,比如 is_delete=1) 3: 非要物理删除,还要用offset精确查询,还不限制用户分页,怎么办? 分析: 优化思路是 不查,少查,查索引,少取. 我们现在必须要查,则只查索引,不查数据,得到id. 再用id去查具体条目. 这种技巧就是延迟索引. mysql> select id,name from lx_com inner join (select id from lx_com limit 5000000,10) as tmp using(id); +---------+-----------------------------------------------+ | id | name | +---------+-----------------------------------------------+ | 5050425 | 陇县河北乡大谈湾小学 | ........ | 5050434 | 陇县堎底下镇水管站 | +---------+-----------------------------------------------+ 10 rows in set (1.35 sec) 
     
      
     
      
       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
       63
       64
       65
       66
       67
       68
       69
       70
       71
       72
       73
       74
       75
       76
       77
       78
       79
       80
       81
       82
       83
       84
       85
       86
       87
       88
       89
       90
       91
       92
       93
       94
       95
       96
       97
       98
       99
       100
       101
       102
       103
       104
       105
       106
       107
       108
       109
       110
       111
       112
       113
       114
       115
       116
       117
       118
       119
       120
       121
       122
       123
       124
       125
       126
       127
       128
       129
       130
       131
       132
       133
       134
       135
       136
       137
       138
       139
       140
       141
       142
       143
       144
       145
       146
       147
       148
       149
       150
       151
       152
       153
       154
       155
       156
       157
       158
       159
       160
       161
       162
       163
       164
       165
       166
       167
       168
       169
       170
       171
       172
       173
       174
       175
       176
       177
       178
       179
       180
       181
       182
       183
       184
       185
       186
       187
       188
       189
       190
       191
       192
       193
       194
       195
       196
       197
       198
       199
       200
       201
       202
       203
       204
       205
       206
       207
       208
       209
       210
       211
       212
       213
       214
       215
       216
       217
       218
       219
       220
       221
       222
       223
       224
       225
       226
       227
       228
       229
       230
       231
       232
       233
       234
       235
       236
       237
       238
       239
       240
       241
       242
       243
       244
       245
       246
       247
       248
       249
       250
       251
       252
       253
       254
       255
       256
       257
       258
       259
       260
       261
       262
       263
       264
       265
       266
       267
       268
       269
       270
       271
       272
       273
       274
       275
       276
       277
       278
       279
       280
       281
       282
       283
       284
       285
       286
       287
       288
       289
       290
       291
       292
       293
       294
       295
       296
       297
       298
       299
       300
       301
       302
       303
       304
       305
       306
       307
       308
       309
       310
       311
       312
       313
       314
       315
       316
       317
       318
       319
       320
       321
       322
       323
       324
       325
       326
       327
       328
       329
       330
       331
       332
       333
       334
       335
       336
       337
       338
       339
       340
       341
       342
       343
       344
       345
       346
       347
       348
       349
       350
       351
       352
       353
       354
       355
       356
       357
       358
       359
       360

##### 巧用变量 #####

1:用变量排名
    例: 以ecshop中的商品表为例,计算每个栏目下的商品数,并按商品数排名.
    select cat_id,count(*) as cnt from goods group by cat_id order by cnt desc; 并按商品数计算这些栏目的名次 set @curr_cnt := 0,@prev_cnt := 0, @rank := 0; select cat_id, (@curr_cnt := cnt) as cnt, (@rank := if(@curr_cnt <> @prev_cnt,@rank+1,@rank)) as rank, @prev_cnt := @curr_cnt from ( select cat_id,count(*) as cnt from shop. goods group by shop. goods.cat_id order by cnt desc) as tmp;
    
    2:用变量计算真正影响的行数
    当插入多条,当主键重复时,则自动更新,这种效果,可以用insert on duplication for update
    要统计真正”新增”的条目, 如下图,我们想得到的值是”1”,即被更新的行数.
    
    
    
     insert into user (uid,uname) values (4,’ds’),(5,'wanu'),(6,’safdsaf’)
     on duplicate key update  uid=values(uid)+(0*(@x:=@x+1)) , uname=values(uname);
    
    mysql> set @x:=0;
    Query OK, 0 rows affected (0.00 sec)
    
    
    总影响行数-2*实际update数, 即新增的行数.
    3: 简化union
    比如有新闻表,news , news_hot,
    new_hot是一张内存表,非常快,用来今天的热门新闻.
    首页取新闻时,逻辑是这样的:
    先取hot, 没有 再取news,为了省事,用一个union来完成.
    select nid,title from news_hot where nid=xxx
    union
    select nid,title from news where nid=xxx;
    
    如何利用变量让后半句select不执行 ,
    select id,content,(@find := 1) from news where id=1
    union
    select id,content,(@find :=1) from news2 where id=1  and (@find <= 0)
    union 1,1,1 where (@find :=null) is not null;
    
    
    3:小心变量的顺序
    如下图:变量先在where中发挥作用,然后再是select操作.
    如果where不成立,select操作再不发生.
    
    
    第2例:
    
    
    在这个例子中, 1,2两行,先排好序, 在内存中,就是这样的顺序  [2] [1]
    再逐行where条件判断,取值.
    
    
    
    对比这2张图,分析:
    1: where先发挥作用,把需要的行 都给找出
    2: 然后再逐行 select
    
    因此, 前者, 最终select时,select@num变量,都是一个值
    后者,不断select,不断修改@num的值, 值不断变化.
    
    
    
    同时: 使用变量,将会使sql语句的结果不缓存.
    
    事务:
    
    事务的4个特性:ACID
    原子性(Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）
    
    
    以银行汇款为例,张三给李四转款300元.
    
    原子性: 是指某几句sql的影响,要么都发生,要么都不发生.
          即:张三减300, 李四+300 , insert银行流水, 这3个操作,必须都完成,或都不产生效果.
    
    一致性: 事务前后的数据,保持业务上的合理一致.
         (汇款前)张三的余额+李四的余额  ====== (汇款后) 张三的余额+李四余额
         比如: 张三只有280元, 280-300=-20,储蓄卡不是信用卡,不能为负,因此张三余0元.
         将导致, 汇款后,2者余额,汇款前,差了20元.
    
    隔离性: 在事务进行过程中, 其他事务,看不到此事务的任何效果.
    持久性: 事务一旦发生,不能取消. 只能通过补偿性事务,来抵消效果.
    
    事务与引擎:
    myisam引擎不支持事务, innodb和BDB引擎支持.
    因此我们的实验用innodb表来做
    
    事务的使用流程:
    比较简单:  
    开启事务   start transaction
    执行查询   xxxx
    提交事务/回滚事务. commit / rollback
    
    
    set session transaction isolation level [read uncommitted |  read committed | repeatable read |serializable]
    
    read uncommitted:  读未提交的事务内容,显然不符原子性, 称为”脏读”. 在业务中,没人这么用.
    
    read commited:   在一个事务进行过程中, 读不到另一个进行事务的操作,但是,可以读到另一个结束事务的操作影响.
    
    repeatable read: 可重复读,即在一个事务过程中,所有信息都来自事务开始那一瞬间的信息,不受其他已提交事务的影响. (大多数的系统,用此隔离级别)
    
    serializeable 串行化  , 所有的事务,必须编号,按顺序一个一个来执行,也就取消了冲突的可能.这样隔离级别最高,但事务相互等待的等待长. 在实用,也不是很多.
    
    设置事务的级别:
    set session transaction isolation level read uncommitted;
     
      
     
      
       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
       63
       64
       65
       66
       67
       68
       69
       70
       71
       72
       73
       74
       75
       76
       77
       78
       79
       80
       81
       82
       83
       84
       85
       86
       87
       88
       89
       90
       91
       92
       93
       94
       95
       96
       97
       98
       99
       100
       101
       102
       103
       104
       105
       106

##### 典型问题: #####

有一业务场景,经过测试,读写比为1:20,请根据读写比,合理设置优化方案.
    
    读写比:
    写数据/读数据的比例,
    Insert/update/delelte     /  select
    
    不管具体技术,从"读写分离"的概念出发来推导一下基本的要素.
    
    
    通俗的说:读服务器就是指写服务器的数据镜像.
    
    从服务端看:要有N台从服务器和主服务器保持数据一致.
    从客户端看:比如有一条insert语句和一条select 语句,
    则要区分读/写语句,并且分别请求从/主服务器.
    
    
    
    服务器端读写分离的具体技术
    1:数据库集群技术
    集群由3个概念
    1:sql节点  sql node
    2:数据节点 data node
    3:管理节点 ndb managerment
    
    Sql语句发送"1sql节点", "1sql"节点发往"2数据节点",再由3管理节点完成数据节点的之间的同步.
    
    集群技术相对复杂,至少有3种节点,4台服务器才能完成.
    
    
    2:数据库复制 replication
    
    
    
    数据库复制 replication 的实现原理
    1:主服务器凡运行语句,都产生一个二进制日志 binlog
    2:从服务器不断读取主服务器的binlog
    3:从主服务读取到的binlog,转换为自身可执行的relaylog,
    4:执行relaylog
    
    
    实现步骤:
    1:首先确保主服务器打开二进制日志功能.
    这样,主服务器一旦有数据变化,立即产生二进制日志.
    
    2:从服务器也需要开启二进制日志和relay日志功能.
    这样可以从主服务器读取binlog,并产生relaylog
    
    3:在主服务器建立一个从服务器的账号,并授予数得上权限.
    
    4: 指定从服务对应的主服务器,开启从服务器.
    
    
    具体实施
    本人虚拟机下有两台linux,IP为199 200
    1: 200做从服务器
    2: 199mysql,做为主服务器.
    3: 保证主从3306端口互通.
    4: 配置主服务器,打开binlog
    
    
    
    #给服务器起一个唯一的id
    server-id=1
     #开启二进制日志
    log-bin=mysql-bin
    #指定日志格式
    binlog-format=mixd/row/statement
    重启mysql
    
    
    已经能够充当master服务器
    
    5: 配置从服务器打开binlog和relaylog
    
    
    
    
    重启从服务器
    
    6: 在主服务器上创建相应的复制账号
    
    
    
    7: 在从服务器通过语句指定要复制的主服务器(注意,可以一主多从,不可一从多主).
    
    
    
    8:启动从服务器功能
    ->start slave;
    
    
    
    8: 测试.
    
    
    
    常用语句:
    show master status ; 查看master的状态, 尤其是当前的日志及位置
    show slave stattus; 查看slave的状态.
    reset slave ;  重置slave状态.
    start slave ; 启动slave 状态(开始监听msater的变化)
    stop slave; 暂停slave状态;
    
    主服务器的日志格式用哪种好?
    有 statement,row, mixed3种,其中mixed是指前2种的混合.
    
    以insert into xxtable values (x,y,z)为例,
    影响: 1行,且为新增1行, 对于其他行没有影响.  
    这个情况,用row格式,直接复制磁盘上1行的新增变化.
    
    以update xxtable set age=21 where name=’sss’;
    这个情况,一般也只是影响1行. 用row也比较合适.
    
    以过年发红包,全公司的人,都涨薪100元.
    update xxtable set salary=salary+100;
    这个语句带来的影响,是针对每一行的, 因此磁盘上很多row都发生了变化.
    此处,适合就statment格式的日志.
    
    2种日志,各有各的高效的地方,mysql提供了mixed类型.
    可以根据语句的不同,而自动选择适合的日志格式.
    主主复制
    在上面的配置中,2台服务器地位有差别,一主一从.
    从服务器一是起到备份作用,一是起到分担查询压力的作用.
    
    接下来的配置,2台服务器之间,没有明显的地位差距, 两者可以同步对方的内容.
    一般的格局如下图:
    两台服务器相互复制
    
    
    
    大致思路:
    1: 2台服务器都设置上2进制日志和relay日志
    2: 都设置上replcation账号
    3: 都设置对方为自己的master
    
    主主复制下一定要注意避免的问题---------同步冲突
    例:
    create table stu (
    id int primary key auto_increment.
    )......
    2台mysql地位相等, 假如2个请求同时到达2台服务器,
    请求的A节点,  stu 的id为1
    请求的B 节点,  stu的id为1 ,
    同步--->冲突
    
    如何解决?
    让1台服务器  1,3,5,7来增长
    另1台服务器  2,4,6,8来增长
    一台服务器：
    set global auto_increment_increment = 2;
    set global auto_increment_offset = 1;
    set session auto_increment_increment = 2;
    set session auto_increment_offset = 1;
    
    另一台服务器：
    set global auto_increment_increment = 2;
    set global auto_increment_offset = 2;
    set session auto_increment_increment=2;
    set session auto_increment_offset = 2;
    注：auto-increment-increment 和　auto-increment-offset　要写到配置文件　中，防止下次重启后失效．
    
    操作后得到如下类似效果
    
    
    
    如果后期需要加服务器,这个办法就有限制了.
    我们可以在业务逻辑上来解决,
    比如在racle 有sequnce,序列.
    序列每次访问,生成递增/递减的数据.
    
    以redis为例, 我们可以专门构建一个 global:userid
    每次PHP插入Mysql前,先 incr->global:userid, 得到一个不重复的userid.
    
    被动模式下的主主复制
    是指 2台服务器地位一样, 但其中一台为只读,并且业务中也只写某1台服务器.
    
    好处: 如果供写入的服务器出了故障,能迅速的切换到从服务器,
    或者出于检修等目的,把写入功能切换到另一台服务器也比较方便.
    
    
    
    
    拥有从服务器的主主复制,在实际应用中也很多.
    
    
    
    
    
    
    环形结构 ()
    即3台服务器,每台服务器均与其前/后的服务器形成master/slave的关系
    
    不好之处: 任一台服务器坏了,影响其他2台. 不如2台形成的主主复制稳定.
    在实际使用中,并不多.
    
    
    
    
    如何在客户端应用的时候,路由语句.
    一般用两种方法
    1: 直接在PHP的mysql类做判断,最简单,不用额外加软件
    比如discuz论坛.
    
    
    
    
    2:用集群中间件
    比如官方的mysql_proxy
    ,还有国产的中间件  amoeba
    
    
    实战: mysql 读写分离  ,mysql_proxy实现
    
    下载安装mysql_proxy:
    http://mysql.cdpa.nsysu.edu.tw/Downloads/MySQL-Proxy/mysql-proxy-0.8.3-linux-glibc2.3-x86-64bit.tar.gz
    
    解压后, 该目录包含已经编译好的二进制文件 .
    
    1: 利用mysql_proxy实现负载均衡
    执行mysql_proxy
    ./mysql-proxy-path/bin/mysql-proxy \
    --proxy-backend-addresses=192.168.1.199:3306 \
    --proxy-backend-addresses=192.168.1.200:3306
    
    
    2: 连接mysql_proxy,用mysql客户就可以,因为proxy是mysql的前端代理
    注意proxy的端口是4040
    mysql -h “proxy的IP” -P 4040 -u username -p password
    
    在连接上之后,做sql查询,却总是往某1台mysql server来发送------负载均衡没体现出来?
    答: 不是没体现出来.
    均衡不是体现在sql语句,一会请求mysqlA服,一会请求mysqlB服.
    均衡是体现”连接”的均衡上.,
    
    mysql_proxy会把连接mysql服务器的tcp/IP连接缓存进连接池,以提高性能.
    在缓存池里, 缓存的连接大致是平均分配在每台mysql服务器上.
    但具体的每一个连接,始终连某台服务器.
    
    
    ./bin/mysql-proxy  \
    --proxy-backend-addresses=192.168.1.199:3306 \
    --proxy-read-only-backend-addresses=192.168.1.200:3306 \
    --proxy-lua-script=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua
    
    简写:
    ./bin/mysql-proxy -b=192.168.0.199:3306 -r=192.168.0.200:3306 -s=/usr/local/mysql-proxy/share/doc/mysql-proxy/rw-splitting.lua  
    表分区 partition
    当一张表的数据非常多的时候,比如单个.myd文件都达到10G, 这时,必然读取起来效率降低.
    可不可以把表的数据分开在几张表上?
    
    1: 从业务角度可以解决. (分表)
    比如, 通过id%10 , user0 , user1....user9, 这10张表
    根据不同的余数,来插入或查询某张表.
    
    2: 通过mysql的分区功能
    mysql将会根据指定的规则,把数据放在不同的表文件上.
    相当于在文件上,被拆成了小块.
    但是,给客户的界面,还是1张表.
    
    常用的规则:
    根据某列的范围来分区, 也可以某列的散点值来分区.
    
    示例: 按列的范围来分区
    以用户表为例, uid
    uid [1,10)  ---> user partition u0
    uid[10, 20) ---> user partition u1
    uid [20, MAX] --> user partion u2
    
    分区 按range分区
     create table goods (
     id int,
     uname char(10)
     )engine myisam
     partition by range(id) (
     partition p1 values less than (10),
     partition p2 values less than (20),
     partition p3 values less than MAXVALUE
     );
    
    按散点值分区
    有一张省表
    pid 主键 prov 省名
    1 北京
    2 安徽
    ... 
    35 西藏
    
    user 表,想按省来分区,
    uid 主键 pid 省份 uname 用户名
    1 1 张北京
    15 2 赵安徽
    
    
    create table user (
    uid int,
    pid int,
    uname
    )engine myisam
    partition by list(pid) (
    partition bj values in (1),
    partition ah values in (2),
    partition xb values in (4,5,6)
    );
    
    
    注意: 在使用分区的时候,注意,分区的那个列,值不要为NULL
    (如果不小心为NULL,mysql为理解为0,尽量执行之)
    
    
    注 :分区甚至可以按照表达式的返回值,计算所属区.
    但用表达式,不如直接用值来得快. 根据情况而定.
    
    比如,用 partition by range (year(regtime)) 可以按注册年份来分区.
    
    服务器参数
    A B C D 4台服务器
    局域网的IP分别是:
    192.168.1.201
    192.168.1.202
    192.168.1.203
    
    192.168.1.204
    
    
    
    账号都是: root
    密码都是: adwind.cn
    
    附录: mysql  show processlist中的State的意义
    Checking table　正在检查数据表（这是自动的）。
    Closing tables
    　正在将表中修改的数据刷新到磁盘中，同时正在关闭已经用完的表。这是一个很快的操作，如果不是这样的话，就应该确认磁盘空间是否已经满了或者磁盘是否正处于重负中。
    Connect Out
    　复制从服务器正在连接主服务器。
    Copying to tmp table on disk
    　由于临时结果集大于tmp_table_size，正在将临时表从内存存储转为磁盘存储以此节省内存。
    Creating tmp table
    　正在创建临时表以存放部分查询结果。
    deleting from main table
    　服务器正在执行多表删除中的第一部分，刚删除第一个表。
    deleting from reference tables　服务器正在执行多表删除中的第二部分，正在删除其他表的记录。
    Flushing tables　正在执行FLUSH TABLES，等待其他线程关闭数据表。
    Killed　发送了一个kill请求给某线程，那么这个线程将会检查kill标志位，同时会放弃下一个kill请求。MySQL会在每次的主循环中检查kill标志位，不过有些情况下该线程可能会过一小段才能死掉。如果该线程程被其他线程锁住了，那么kill请求会在锁释放时马上生效。
    Locked
    　被其他查询锁住了。
    Sending data
    　正在处理SELECT查询的记录，同时正在把结果发送给客户端。
    Sorting for group　正在为GROUP BY做排序。
    　Sorting for order
    　正在为ORDER BY做排序。
    Opening tables
    　这个过程应该会很快，除非受到其他因素的干扰。例如，在执ALTER TABLE或LOCK TABLE语句行完以前，数据表无法被其他线程打开。正尝试打开一个表。
    Removing duplicates
    　正在执行一个SELECT DISTINCT方式的查询，但是MySQL无法在前一个阶段优化掉那些重复的记录。因此，MySQL需要再次去掉重复的记录，然后再把结果发送给客户端。
    Reopen table
    　获得了对一个表的锁，但是必须在表结构修改之后才能获得这个锁。已经释放锁，关闭数据表，正尝试重新打开数据表。
    Repair by sorting
    　修复指令正在排序以创建索引。
    Repair with keycache
    　修复指令正在利用索引缓存一个一个地创建新索引。它会比Repair by sorting慢些。
    Searching rows for update　正在讲符合条件的记录找出来以备更新。它必须在UPDATE要修改相关的记录之前就完成了。
    Sleeping
    　正在等待客户端发送新请求.
    System lock
    　正在等待取得一个外部的系统锁。如果当前没有运行多个mysqld服务器同时请求同一个表，那么可以通过增加--skip-external-locking参数来禁止外部系统锁。
    Upgrading lock
    　INSERT DELAYED正在尝试取得一个锁表以插入新记录。
    Updating
    　正在搜索匹配的记录，并且修改它们。
    User Lock
    　正在等待GET_LOCK()。
    Waiting for tables　该线程得到通知，数据表结构已经被修改了，需要重新打开数据表以取得新的结构。然后，为了能的重新打开数据表，必须等到所有其他线程关闭这个表。以下几种情况下会产生这个通知：FLUSH TABLES tbl_name, ALTER TABLE, RENAME TABLE, REPAIR TABLE, ANALYZE TABLE,或OPTIMIZE TABLE。
    waiting for handler insert
    　INSERT DELAYED已经处理完了所有待处理的插入操作，正在等待新的请求。
    　大部分状态对应很快的操作，只要有一个线程保持同一个状态好几秒钟，那么可能是有问题发生了，需要检查一下。
    　还有其他的状态没在上面中列出来，不过它们大部分只是在查看服务器是否有存在错误是才用得着。
     
      
     
      
       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
       63
       64
       65
       66
       67
       68
       69
       70
       71
       72
       73
       74
       75
       76
       77
       78
       79
       80
       81
       82
       83
       84
       85
       86
       87
       88
       89
       90
       91
       92
       93
       94
       95
       96
       97
       98
       99
       100
       101
       102
       103
       104
       105
       106
       107
       108
       109
       110
       111
       112
       113
       114
       115
       116
       117
       118
       119
       120
       121
       122
       123
       124
       125
       126
       127
       128
       129
       130
       131
       132
       133
       134
       135
       136
       137
       138
       139
       140
       141
       142
       143
       144
       145
       146
       147
       148
       149
       150
       151
       152
       153
       154
       155
       156
       157
       158
       159
       160
       161
       162
       163
       164
       165
       166
       167
       168
       169
       170
       171
       172
       173
       174
       175
       176
       177
       178
       179
       180
       181
       182
       183
       184
       185
       186
       187
       188
       189
       190
       191
       192
       193
       194
       195
       196
       197
       198
       199
       200
       201
       202
       203
       204
       205
       206
       207
       208
       209
       210
       211
       212
       213
       214
       215
       216
       217
       218
       219
       220
       221
       222
       223
       224
       225
       226
       227
       228
       229
       230
       231
       232
       233
       234
       235
       236
       237
       238
       239
       240
       241
       242
       243
       244
       245
       246
       247
       248
       249
       250
       251
       252
       253
       254
       255
       256
       257
       258
       259
       260
       261
       262
       263
       264
       265
       266
       267
       268
       269
       270
       271
       272
       273
       274
       275
       276
       277
       278
       279
       280
       281
       282
       283
       284
       285
       286
       287
       288
       289
       290
       291
       292
       293
       294
       295
       296
       297
       298
       299
       300
       301
       302
       303
       304
       305
       306
       307
       308
       309
       310
       311
       312
       313
       314
       315
       316
       317
       318
       319
       320
       321
       322
       323
       324
       325
       326
       327
       328
       329
       330
       331
       332
       333
       334
       335
       336
       337
       338
       339
       340
       341
       342
       343
       344
       345
       346
       347
       348
       349
       350
       351
       352
       353
       354
       355
       356
       357
       358
       359
       360
       361
       362
       363
       364
       365
       366
       367
       368
       369
       370
       371
       372
       373
       374
       375
       376

[Mysql]: https://blog.csdn.net/qq_35197457/article/details/79863538?depth_1-utm_source=distribute.pc_relevant.none-task&utm_source=distribute.pc_relevant.none-task