MySQL优化学习笔记

最新推荐文章于 2024-03-24 12:00:49 发布

risingsun001

最新推荐文章于 2024-03-24 12:00:49 发布

阅读量1.9k

点赞数 1

分类专栏： PHP mysql

本文链接：https://blog.csdn.net/risingsun001/article/details/46008281

版权

PHP 同时被 2 个专栏收录

31 篇文章 0 订阅

订阅专栏

mysql

20 篇文章 1 订阅

订阅专栏

未完，待续，格式写完后再调整。。。

1. 安装MySQL 5.5

在官网下载安装，选择适合自己系统的MySQL即可

https://www.mysql.com/downloads/

2. 安装测试数据库

http://dev.mysql.com/doc/index-other.html
mysql -uroot -p <sakila-schema.sql
mysql -uroot -p <sakila-data.sql

登陆MySQL验证数据是否导入成功

3. 如何发现有问题的SQL语句

开启慢查询日志：

1）查看所有日志状态： show variables like '%log'

2）查看慢查询状态：show variables like 'show%'

3）查看是否开启了慢查询日志：show variables like 'slow_query_log'

4）设置慢查询时间：set global long_query_time=10

注：直接修改global 的long_query_time 之后在当前的的窗口中是没有效果的，在新打开的窗口中才会有效果。如果想让本窗口也有效果的话，不用加 global关键字。

5）设置记录未使用索引的查询：set global log_queries_not_using_indexes=on

6）设置慢查询日志记录地址：

set global slow_query_log_file='/alidata/log/mysql/slow_query.log'

6）开启慢查询日志：set global slow_query_log=on

我们在开启之后进入sakila数据库查询select * from store;
就会在之前设置的slow_query_log_file文件中看到慢查询日志

慢查日志格式如下：

(1)执行时间

\#Time: 150526 13:56:20

(2)用户主机信息

\# User@Host: root[root]

(3)执行信息(如：执行时间、锁定时间、发送行数、扫描行数)

\# Query_time:0.000282 Lock_time: 0.000094 Rows_sent: 2 Rows_examined: 2

SET timestamp=1432619780;

(5)SQL的内容

select * from store;

4. MySQL慢查询日志分析工具之mysqldumpslow

这个工具是MySQL官方自带的工具，可以满足平时简单统计。

我们直接通过mysqldumpslow --help（如果不可用的话，请设置mysql环境变量）可以查看工具的参数有哪些

> Usage: mysqldumpslow [ OPTS... ] [ LOGS... ]
>
> Parse and summarize the MySQL slow query log. Options are
>
> --verbose verbose --debug debug --help write this
> text to standard output
>
> -v verbose -d debug -s ORDER what to
> sort by (al, at, ar, c, l, r, t), 'at' is default
> al: average lock time
> ar: average rows sent
> at: average query time
> c: count
> l: lock time
> r: rows sent
> t: query time -r reverse the sort order (largest last instead of first) -t NUM just show the top n
> queries -a don't abstract all numbers to N and strings to
> 'S' -n NUM abstract numbers with at least n digits within
> names -g PATTERN grep: only consider stmts that include this
> string -h HOSTNAME hostname of db server for *-slow.log filename
> (can be wildcard),
> default is '*', i.e. match all -i NAME name of server instance (if using mysql.server startup script) -l
> don't subtract lock time from total time

比如我们现在需要知道按照行数排序的前3条数据

我们就可以通过 mysqldumpslow -t 3 -s r /alidata/log/mysql/slow_query.log

下面就是通过执行了三条语句之后的统计结果：

Reading mysql slow query log from /alidata/log/mysql/slow_query.log
Count: 1 Time=0.01s (0s) Lock=0.00s (0s) Rows=1000.0 (1000), root[root]@localhost
select * from film

Count: 4 Time=0.00s (0s) Lock=0.00s (0s) Rows=17.2 (69), root[root]@localhost
show tables

Count: 1 Time=0.00s (0s) Lock=0.00s (0s) Rows=10.0 (10), root[root]@localhost
select * from film limit N

5.MySQL慢查日志分析工具之pt-query-digest

pt-query-digest是Percona Toolkit 工具中的一个。

percona-toolkit是一组高级命令行工具的集合，用来执行各种通过手工执行非常复杂和麻烦的mysql和系统任务。这些任务包括：

检查master和slave数据的一致性

有效地对记录进行归档

查找重复的索引

对服务器信息进行汇总

分析来自日志和tcpdump的查询

当系统出问题的时候收集重要的系统信息

pt-query-digest是用于分析mysql慢查询的一个工具，它可以分析binlog、General log、slowlog，也可以通过SHOWPROCESSLIST或者通过tcpdump抓取的MySQL协议数据来进行分析。可以把分析结果输出到文件中，分析过程是先对查询语句条件进行参数化，然后对参数化以后的查询进行分组统计，统计出各查询的执行时间、次数、占比等，可以借助分析结果找出问问题进行优化。

安装percona-toolkit：

下载地址：https://www.percona.com/downloads/percona-toolkit/LATEST/

根据自己的linux系统来下载安装：

比如我的是centos，可以进行下面的方法安装：

1）下载 wget https://www.percona.com/downloads/percona-toolkit/2.2.14/RPM/percona-toolkit-2.2.14-1.noarch.rpm

2）安装 rpm -ivh percona-toolkit-2.2.14-1.noarch.rpm

注：如果提示error: Failed dependencies perl的依赖未安装，或者通过其他方式安装后运行提示下面的错误：

Can't locate Time/HiRes.pm in @INC (@INC contains: /usr/local/lib/perl5 /usr/local/share/perl5 /usr/lib/perl5/vendor_perl /usr/share/perl5/vendor_perl /usr/lib/perl5 /usr/share/perl5 .) at ./pt-query-digest line 3189.
BEGIN failed--compilation aborted at ./pt-query-digest line 3189.

可以通过安装perl来解决：

yum -y install perl perl-IO-Socket-SSL perl-DBD-MySQL perl-Time-HiRes perl-TermReadKey

如果安装过程中出现” Error Downloading Packages”错误，尝试yum clean all后再安装。使用其Percona Toolkit中其他工具也可能会遇到类似的问题，按照提示安装相应的perl包就可以了。

pt-query-digest [OPTIONS] [FILES] [DSN]
--create-review-table 当使用--review参数把分析结果输出到表中时，如果没有表就自动创建。
--create-history-table 当使用--history参数把分析结果输出到表中时，如果没有表就自动创建。
--filter 对输入的慢查询按指定的字符串进行匹配过滤后再进行分析
--limit限制输出结果百分比或数量，默认值是20,即将最慢的20条语句输出，如果是50%则按总响应时间占比从大到小排序，输出到总和达到50%位置截止。
--host mysql服务器地址
--user mysql用户名
--password mysql用户密码
--history 将分析结果保存到表中，分析结果比较详细，下次再使用--history时，如果存在相同的语句，且查询所在的时间区间和历史表中的不同，则会记录到数据表中，可以通过查询同一CHECKSUM来比较某类型查询的历史变化。
--review 将分析结果保存到表中，这个分析只是对查询条件进行参数化，一个类型的查询一条记录，比较简单。当下次使用--review时，如果存在相同的语句分析，就不会记录到数据表中。
--output 分析结果输出类型，值可以是report(标准分析报告)、slowlog(Mysql slow log)、json、json-anon，一般使用report，以便于阅读。
--since 从什么时间开始分析，值为字符串，可以是指定的某个”yyyy-mm-dd [hh:mm:ss]”格式的时间点，也可以是简单的一个时间值：s(秒)、h(小时)、m(分钟)、d(天)，如12h就表示从12小时前开始统计。

--until 截止时间，配合—since可以分析一段时间内的慢查询。

比如我们分析一下之前的慢查询日志：

pt-query-digest /alidata/log/mysql/slow_query.log

标准分析报告解释：

第一部分: 总体统计结果,如下

# 240ms user time, 10ms system time, 16.01M rss, 23.78M vsz
# Current date: Tue May 26 16:36:32 2015
# Hostname: iZ25fdgopifZ
# Files: /alidata/log/mysql/slow_query.log
# Overall: 22 total, 13 unique, 0.01 QPS, 0.00x concurrency ______________
# Time range: 2015-05-26 13:43:18 to 14:18:22
# Attribute          total     min     max     avg     95%  stddev  median
# ============     ======= ======= ======= ======= ======= ======= =======
# Exec time          158ms     6us   146ms     7ms   690us    29ms   273us
# Lock time            1ms       0   552us    67us   119us   115us       0
# Rows sent          1.07k       0    1000   49.73   22.53  200.11    0.99
# Rows examine      36.89k       0  35.82k   1.68k   22.53   7.25k       0
# Query size           491      11      32   22.32   28.75    6.99   26.08

这个部分是一个大致的概要信息

通过它可以对当前MySQL的查询性能做一个初步的评估，比如各个指标的最大值(max)，平均值(min)，95%分布值，中位数(median)，标准偏差(stddev)。这些指标有查询的执行时间（Exec time），锁占用的时间（Lock time），MySQL执行器需要检查的行数（Rows examine），最后返回给客户端的行数（Rows sent），查询的大小（Query size）

第二部分：查询分组统计结果,如下

这个部分对所有”重要”的查询(通常是比较慢的查询)做了个一览表:

# Profile
# Rank Query ID           Response time Calls R/Call V/M   Item
# ==== ================== ============= ===== ====== ===== ===============
#    1 0x4B8014EA5E017DB0  0.1461 92.5%     1 0.1461  0.00 SELECT sales_by_store
#    2 0x687D590364E29465  0.0072  4.5%     1 0.0072  0.00 SELECT film
# MISC 0xMISC              0.0047  3.0%    20 0.0002   0.0 <11 ITEMS>

由上面可见，这部分对查询进行参数化并分组，然后对各类查询的执行情况进行分析，结果按总执行时长，从大到小排序。
Response: 总的响应时间。
time: 该查询在本次分析中总的时间占比。
calls: 执行次数，即本次分析总共有多少条这种类型的查询语句。
R/Call: 平均每次执行的响应时间。
Item : 查询对象

V/M: 响应时间的差异平均对比率

在尾部有一行输出，显示了其他11个占比较低而不值得单独显示的查询的统计数据

每个查询都有一个Query ID，这个ID通过Hash计算出来的。pt-query-digest是根据这个所谓的Fingerprint来group by的。

第三部分：每一种查询的详细统计结果，如下：

# Query 1: 0 QPS, 0x concurrency, ID 0x4B8014EA5E017DB0 at byte 3932 _____
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 2015-05-26 14:18:17
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          4       1
# Exec time     92   146ms   146ms   146ms   146ms   146ms       0   146ms
# Lock time     36   552us   552us   552us   552us   552us       0   552us
# Rows sent      0       2       2       2       2       2       0       2
# Rows examine  97  35.82k  35.82k  35.82k  35.82k  35.82k       0  35.82k
# Query size     5      28      28      28      28      28       0      28
# String:
# Databases    sakila
# Hosts        localhost
# Users        root
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms
#  10ms
# 100ms  ################################################################
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `sakila` LIKE 'sales_by_store'\G
#    SHOW CREATE TABLE `sakila`.`sales_by_store`\G
# EXPLAIN /*!50100 PARTITIONS*/
select * from sales_by_store\G

# Query 2: 0 QPS, 0x concurrency, ID 0x687D590364E29465 at byte 3368 _____
# This item is included in the report because it matches --limit.
# Scores: V/M = 0.00
# Time range: all events occurred at 2015-05-26 14:17:29
# Attribute    pct   total     min     max     avg     95%  stddev  median
# ============ === ======= ======= ======= ======= ======= ======= =======
# Count          4       1
# Exec time      4     7ms     7ms     7ms     7ms     7ms       0     7ms
# Lock time      6    96us    96us    96us    96us    96us       0    96us
# Rows sent     91    1000    1000    1000    1000    1000       0    1000
# Rows examine   2    1000    1000    1000    1000    1000       0    1000
# Query size     3      18      18      18      18      18       0      18
# String:
# Databases    sakila
# Hosts        localhost
# Users        root
# Query_time distribution
#   1us
#  10us
# 100us
#   1ms  ################################################################
#  10ms
# 100ms
#    1s
#  10s+
# Tables
#    SHOW TABLE STATUS FROM `sakila` LIKE 'film'\G
#    SHOW CREATE TABLE `sakila`.`film`\G
# EXPLAIN /*!50100 PARTITIONS*/
select * from film\G

这个部分会列出Profile表中每个查询的详细信息，包括Overall中有的信息、查询响应时间的分布情况以及该查询”入榜”的理由。

由上面可见，2号查询的详细统计结果，最上面的表格列出了执行次数、最大、最小、平均、95%等各项目的统计。
Databases: 库名
Users: 各个用户执行的次数（占比）
Query_time distribution : 查询时间分布, 长短体现区间占比，本例中1s-10s之间查询数量是10s以上的两倍。
Tables: 查询中涉及到的表
Explain: 示例

上面是一个简单慢查询日志分析结果的示例，当然，pt_query_digest 还可以做很多事情，下面摘抄了一些常用的命令：

用法示例
(1)直接分析慢查询文件:
pt-query-digest slow.log > slow_report.log

(2)分析最近12小时内的查询：
pt-query-digest --since=12h slow.log > slow_report2.log

(3)分析指定时间范围内的查询：
pt-query-digest slow.log --since '2014-04-17 09:30:00' --until '2014-04-17 10:00:00'> > slow_report3.log

(4)分析指含有select语句的慢查询
pt-query-digest--filter '$event->{fingerprint} =~ m/^select/i' slow.log> slow_report4.log

(5) 针对某个用户的慢查询
pt-query-digest--filter '($event->{user} || "") =~ m/^root/i' slow.log> slow_report5.log

(6) 查询所有所有的全表扫描或full join的慢查询
pt-query-digest--filter '(($event->{Full_scan} || "") eq "yes") ||(($event->{Full_join} || "") eq "yes")' slow.log> slow_report6.log

(7)把查询保存到query_review表
pt-query-digest --user=root –password=abc123 --review h=localhost,D=test,t=query_review--create-review-table slow.log

(8)把查询保存到query_history表
pt-query-digest --user=root –password=abc123 --review h=localhost,D=test,t=query_ history--create-review-table slow.log_20140401
pt-query-digest --user=root –password=abc123--review h=localhost,D=test,t=query_history--create-review-table slow.log_20140402

(9)通过tcpdump抓取mysql的tcp协议数据，然后再分析
tcpdump -s 65535 -x -nn -q -tttt -i any -c 1000 port 3306 > mysql.tcp.txt
pt-query-digest --type tcpdump mysql.tcp.txt> slow_report9.log

(10)分析binlog
mysqlbinlog mysql-bin.000093 > mysql-bin000093.sql
pt-query-digest --type=binlog mysql-bin000093.sql > slow_report10.log

(11)分析general log
pt-query-digest --type=genlog localhost.log > slow_report11.log

(12)从processlist中查询某个MySQL中最慢的查询

pt-query-digest –processlist h=host1

(13)从一台机器上讲slow log保存到另外一台机器上待稍后详细分析

pt-query-digest --review h=host2 --no-report slow.log

更多的用法请官方文档

官方文档地址：http://www.percona.com/doc/percona-toolkit/2.2/pt-query-digest.html

建议：当slow log很大的时候最好还是将日志文件移到其他机器上进行分析，pt_query_digest分析，比较耗费资源。

之前有提到percona-toolkit有很多工具，常用的还有下面这3个工具：

pt-index-usage

这个工具主要是用来分析查询的索引使用情况。

pt-index-usage slow_query.log --h localhost --password 123456
详细的用法 –help查看再对照官网就差不再赘述。

注意使用这个工具需要MySQL必须要有密码，另外运行时可能报找不到/var/lib/mysql/mysql.sock的错，简单的从/tmp/mysql.sock链接一个就行了。
重点要说明的是pt-index-usage只能分析慢查询日志，所以如果想全面分析所有查询的索引使用情况就得将slow_launch_time设置为0，因此请谨慎使用该工具，线上使用的话最好在凌晨进行分析，尤其分析大量日志的时候是很耗CPU的。
整体来说这个工具是不推荐使用的，要想实现类似的分析可以考虑一些其他第三方的工具，比如：mysqlidxchx, userstat和check-unused-keys。网上比较推荐的是userstat，一个Google贡献的patch。
Oracle是可以将执行计划保存到性能视图中的，这样分析起来可能更灵活，但是目前我还没找到MySQL中类似的做法。

pt-upgrade

这个工具用来检查在新版本中运行的SQL是否与老版本一样，返回相同的结果，最好的应用场景就是数据迁移的时候。
pt-upgrade h=host1 h=host2 slow.log

上面这些工具最好不要直接在线上使用，应该作为上线辅助或故障后离线分析的工具，也可以做性能测试的时候配合着使用。

6. 通过pt_query_digest分析来查找问题SQL

1) 查询次数多且每次查询占用时间长的SQL

通常是pt_query_digest分析的第三部分前几条

2) IO大的SQL

注意pt_query_digest分析中的Rows examine项

数据库大多数的瓶颈都是IO，扫描的行数越多，消耗也就越大

3) 未命中索引的SQL

注意pt_query_digest分析中Rows examine 和Rows Send的对比

扫描行数Rows examine远远大于发送客户端的行数Rows Send的时候，有可能是数据库的索引命中率不高

7. 通过explain查询和分析SQL的执行计划

这块给大家推荐一篇文章来学习：

http://www.cnblogs.com/zhanjindong/p/3439042.html

EXPLAIN对于我们后面SQL语句的优化有很重要的作用

8. SQL语句优化

1) count()和max()的优化

这2个语句通常用于查询最大或者最后的条件

比如我们现在查询最后支付时间SQL语句为：

select max(payment_date) from payment

我们查看sql的执行计划：

mysql> explain select max(payment_date) from payment;
+----+-------------+---------+------+---------------+------+---------+------+-------+-------+
| id | select_type | table   | type | possible_keys | key  | key_len | ref  | rows  | Extra |
+----+-------------+---------+------+---------------+------+---------+------+-------+-------+
|  1 | SIMPLE      | payment | ALL  | NULL          | NULL | NULL    | NULL | 14757 |       |
+----+-------------+---------+------+---------------+------+---------+------+-------+-------+
1 row in set (0.01 sec)

可以看出，这个SQL的性能并不是很高，type类型是ALL，进行了全表扫描。

对于这种sql，我们可以通过增加索引的方式来优化

create index idx_paydate on payment(payment_date);

建完索引后，我们再来分析执行计划:

mysql> explain select max(payment_date) from payment;
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra                        |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
|  1 | SIMPLE      | NULL  | NULL | NULL          | NULL | NULL    | NULL | NULL | Select tables optimized away |
+----+-------------+-------+------+---------------+------+---------+------+------+------------------------------+
1 row in set (0.00 sec)

可以看到，并没有查询表的数据，只需要索引就可以查到结果。

count()对多个关键字进行查询，比如在一条SQL中同时查出2006年和2007年电影的数量，语句：

select count(release_year='2006' or null) as '2006年电影数量',
count(release_year='2007' or null) as '2007年电影数量'
from film;

count(*) 查询的结果中，包含了该列值为null的结果

而count(id)的时候，不包含null的结果

上面的SQL为什么要加上or NULL呢？

因为当 release_year不是 2006时，release_year='2006' 结果false 不是 NULL，Count在值是NULL是不统计数，至于加上or NULL ，很像其他编程里的or运算符，第一个表达式是true就是不执行or后面的表达式，第一个表达式是false 执行or后面的表达式。当release_year不为2006时release_year = '2006' or NULL 的结果是NULL，Count才不会统计上这条记录数

2)子查询的优化

一般对与in的子查询用join来优化

create table t1 (id int);
create table t2 (id int);
insert t1 values(1),(1);
insert t2 values(1);
select * from t1 where id in (select t2.id from t2);

优化成：

select distanct(t1.id) from t1 join t2 on t2.id=t1.id;

需要注意的是如果t1或者t2中的id有重复的就会出现重复的数据，需要distanct

3)group by 的优化

group by可能会出现临时表（Using temporary），文件排序（Using filesort）等，影响效率。

可以通过关联的子查询，来避免产生临时表和文件排序，可以节省io

explain select actor.first_name,actor.last_name,count(*) from sakila.film_actor inner join sakila.actor USING(actor_id) group by film_actor.actor_id \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: actor
         type: ALL
possible_keys: PRIMARY
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 200
        Extra: Using temporary; Using filesort
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: film_actor
         type: ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 2
          ref: sakila.actor.actor_id
         rows: 1
        Extra: Using index

优化后：

EXPLAIN select actor.first_name,actor.last_name,c.cnt from sakila.actor inner join(select actor_id,count(*) as cnt from sakila.film_actor group by actor_id)as c using(actor_id);
+----+-------------+------------+--------+---------------+---------+---------+------------+------+-------------+
| id | select_type | table      | type   | possible_keys | key     | key_len | ref        | rows | Extra       |
+----+-------------+------------+--------+---------------+---------+---------+------------+------+-------------+
|  1 | PRIMARY     | <derived2> | ALL    | NULL          | NULL    | NULL    | NULL       |  200 |             |
|  1 | PRIMARY     | actor      | eq_ref | PRIMARY       | PRIMARY | 2       | c.actor_id |    1 |             |
|  2 | DERIVED     | film_actor | index  | NULL          | PRIMARY | 4       | NULL       | 5920 | Using index |
+----+-------------+------------+--------+---------------+---------+---------+------------+------+-------------+

4)limit 的优化

limit常用于分页处理，时常会伴随order by从句使用，因此大多时候会使用Filesorts这样会造成大量的io问题

（1）.使用有索引的列或主键进行order by操作

（2）.记录上次返回的主键，在下次查询时使用主键过滤
使用这种方式有一个限制，就是主键一定要顺序排序和连续的，如果主键出现空缺可能会导致最终页面上显示的列表不足5条，解决办法是附加一列，保证这一列是自增的并增加索引就可以了

比如下面的sql语句，执行过程中用到了文件全表排序

explain select film_id,description from sakila.film order by title limit 50,5;
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
| id | select_type | table | type | possible_keys | key  | key_len | ref  | rows | Extra          |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+
|  1 | SIMPLE      | film  | ALL  | NULL          | NULL | NULL    | NULL | 1022 | Using filesort |
+----+-------------+-------+------+---------------+------+---------+------+------+----------------+

优化1.使用有索引的列或主键进行order by操作

explain select film_id,description from sakila.film order by film_id limit 50,5;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+
|  1 | SIMPLE      | film  | index | NULL          | PRIMARY | 2       | NULL |   55 |       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------+

优化2. 记录上次返回的主键，在下次查询时使用主键过滤

explain select film_id,description from sakila.film where film_id>55 and film_id<=60 order by film_id limit 1,5;
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
| id | select_type | table | type  | possible_keys | key     | key_len | ref  | rows | Extra       |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+
|  1 | SIMPLE      | film  | range | PRIMARY       | PRIMARY | 2       | NULL |    5 | Using where |
+----+-------------+-------+-------+---------------+---------+---------+------+------+-------------+

避免了数据量过大时扫描了过多的记录

5）如何选择合适的列建立索引

这块可以查看博主以前的关于索引的一篇文章：http://blog.csdn.net/risingsun001/article/details/44408129

选择合适的索引列

（1）.在where，group by，order by，on从句中出现的列

（2）.索引字段越小越好(因为数据库的存储单位是页，一页中能存下的数据越多越好 )

（3）.离散度（或者叫区分度）大得列放在联合索引前面

select count(distinct customer_id), count(distinct staff_id) from payment;
查看离散度通过统计不同的列值来实现 count越大离散程度越高

（4）.最左前缀匹配原则

常重要的原则，mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，比如a = 1 and b = 2 and c > 3 and d = （4）如果建立(a,b,c,d)顺序的索引，d是用不到索引的，如果建立(a,b,d,c)的索引则都可以用到，a,b,d的顺序可以任意调整。

（5）. 索引列不能参与计算

保持列“干净”，比如from_unixtime(create_time) = ’2014-05-29’就不能使用到索引，原因很简单，b+树中存的都是数据表中的字段值，但进行检索时，需要把所有元素都应用函数才能比较，显然成本太大。所以语句应该写成create_time = unix_timestamp(’2014-05-29’);

（6）. 尽量的扩展索引，不要新建索引

比如表中已经有a的索引，现在要加(a,b)的索引，那么只需要修改原来的索引即可

比如下面这个例子：

explain select * from payment where staff_id =2 and customer_id =584 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
         type: index_merge
possible_keys: idx_fk_staff_id,idx_fk_customer_id
          key: idx_fk_customer_id,idx_fk_staff_id
      key_len: 2,1
          ref: NULL
         rows: 14
        Extra: Using intersect(idx_fk_customer_id,idx_fk_staff_id); Using where
建立联合索引之后：ALTER TABLE payment add index idx_merge(customer_id ,staff_id );
explain select * from payment where staff_id =2 and customer_id =584 \G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: payment
         type: ref
possible_keys: idx_fk_staff_id,idx_fk_customer_id,idx_merge
          key: idx_merge
      key_len: 3
          ref: const,const
         rows: 17
        Extra:

7）索引优化SQL的方法

过多的索引不但影响写入，而且影响查询，索引越多，分析越慢

如何找到重复和多余的索引，主键已经是索引了，所以primay key 的主键不用再设置unique唯一索引了

冗余索引，是指多个索引的前缀列相同，innodb会在每个索引后面自动加上主键信信息

通过SQL语句查询冗余索引

use information_schema;
select a.table_schema as '数据名',    a.table_name as '表名', a.index_name as  '索引1', b.index_name as '索引2', a.column_name as '重复列名' from statistics a join statistics b on  a.table_schema = b.table_schema and a.table_name = b.table_name and a.seq_in_index = b.seq_in_index and a.column_name = b.column_name where a.seq_in_index = 1 and a.index_name <> b.index_name;
+-----------+---------+--------------------+--------------------+--------------+
| 数据名    | 表名    | 索引1              | 索引2              | 重复列名     |
+-----------+---------+--------------------+--------------------+--------------+
| sakila    | payment | idx_merge          | idx_fk_customer_id | customer_id  |
| sakila    | payment | idx_fk_customer_id | idx_merge          | customer_id  |
+-----------+---------+--------------------+--------------------+--------------+

冗余索引查询工具

pt-duplicate-key-checker

用法也很简单：pt-duplicate-key-checker -u用户名 -p密码

我们对数据库分析的结果如下：

# ########################################################################
# sakila.payment                                                          
# ########################################################################
# idx_fk_customer_id is a left-prefix of idx_merge
# Key definitions:
#   KEY `idx_fk_customer_id` (`customer_id`),
#   KEY `idx_merge` (`customer_id`,`staff_id`),
# Column types:
#  `customer_id` smallint(5) unsigned not null
#  `staff_id` tinyint(3) unsigned not null
# To remove this duplicate index, execute:
ALTER TABLE `sakila`.`payment` DROP INDEX `idx_fk_customer_id`;
# ########################################################################
# Summary of indexes                                                      
# ########################################################################
# Size Duplicate Indexes   64
# Total Duplicate Indexes  1
# Total Indexes            94

8）索引维护的方法

索引的维护及优化——删除不用的索引

但是有一些索引在主没有使用，但在从还在使用，这样的应该保留，要注意

目前MySQL中还没有记录索引的使用情况，但是在PerconMySQL和MariaDB中可以通过INDEX_STATISTICS表来查询哪些索引未使用。

MySQL目前可以通过慢查询日志配合pt-index-usage工具来进行索引使用情况的分析

pt-index-usage -u用户名 -p密码 mysql-slow.log

9）数据库表结构字段类型优化

选择合适的数据类型

1.使用可存下数据的最小的数据类型

2.使用简单地数据类型，Int<varchar

3.尽可能使用not null定义字段

4.尽量少用text，非用不可最好分表

用Int存储日期时间
from_unixtime()可将Int类型的时间戳转换为时间格式
unix_timestamp()可将时间格式转换为Int类型
存储IP地址——bigInt
利用inet_aton(),inet_ntoa()转换

10）数据库表结构优化

数据表结构优化第三范式：要求数据库中不存在非关键字段对任意候选关键字的传递函数依赖
不符合第三范式要求的表存在以下问题：
1.数据冗余:（分类、分类描述）对于每一个商品都会进行记录
2.数据插入异常
3.数据更新异常
4.数据删除异常

范式化和反范式化的资料网上也有很多，这里就不多说了

11）表的垂直拆分

表的垂直拆分的原则
所谓垂直拆分，就是把原来一个有很多列的表拆分成多个表解决表的宽度问题，通常拆分原则如下：
1、把不常用的字段单独存放到一个表中
2、把大字段独立存放到一个表中
3、把经常一起使用的字段放到一起

一个简单的例子：

sakila数据库中的film表

CREATE TABLE `film` (
  `film_id` smallint(5) unsigned NOT NULL AUTO_INCREMENT,
  `title` varchar(255) NOT NULL,
  `description` text,
  `release_year` year(4) DEFAULT NULL,
  `language_id` tinyint(3) unsigned NOT NULL,
  `original_language_id` tinyint(3) unsigned DEFAULT NULL,
  `rental_duration` tinyint(3) unsigned NOT NULL DEFAULT '3',
  `rental_rate` decimal(4,2) NOT NULL DEFAULT '4.99',
  `length` smallint(5) unsigned DEFAULT NULL,
  `replacement_cost` decimal(5,2) NOT NULL DEFAULT '19.99',
  `rating` enum('G','PG','PG-13','R','NC-17') DEFAULT 'G',
  `special_features` set('Trailers','Commentaries','Deleted Scenes','Behind the Scenes') DEFAULT NULL,
  `last_update` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`film_id`),
  KEY `idx_title` (`title`),
  KEY `idx_fk_language_id` (`language_id`),
  KEY `idx_fk_original_language_id` (`original_language_id`),
  CONSTRAINT `fk_film_language` FOREIGN KEY (`language_id`) REFERENCES `language` (`language_id`) ON UPDATE CASCADE,
  CONSTRAINT `fk_film_language_original` FOREIGN KEY (`original_language_id`) REFERENCES `language` (`language_id`) ON UPDATE CASCADE
) ENGINE=InnoDB AUTO_INCREMENT=1001 DEFAULT CHARSET=utf8

可以将其中的部分字段拆分

create table file_text(
`film_id` smallint(5) unsigned not null auto_increment,
`title` varchar(255) not null,
`description` text,
primary key(film_id)
)engine =innodb;