MySQL数据库调优

1.定义字段时，选择合适的类型和长度。

比如：金额用decimal,日期用date,时间用time,定义枚举字典表等。

2.每个表要定义主键，确定每行数据的唯一性。

3.选择合适的数据库引擎，比如：myISAM，innoDB等。

innoDB支持事务、外键、数据恢复等，适用于对数据一致性要求高，除增查外删、更新也比较频繁的情况。
myISAM插入和读取数据效率高，对数据删、更新较少的情况可以选择。
根据具体业务选择合适的引擎。

4.连接两个表使用join时，尽量保持字段类型一致。

5.不要使用select * ,降低内存消耗、提供传输效率。

百万条数据测试（下文所有测试以及时间仅供参考，如有需求，请自行测试）
生成百万条数据测试传送门

SELECT * FROM vote_record 
Time: 1.239s
SELECT id,user_id FROM vote_record
Time: 1.034s

6.统计总数函数count的选择。

count(※),mysql对其进行了优化，会自动优化到指定字段按行累加；
count(1),对表进行遍历，给每一行赋值为1，返回server层进行累加；
count(id),对表进行遍历，取出每一行id，返回server层进行累加；
count(字段)，对表进行遍历，取出每一行字段，返回server层判断，不为空则累加。
（1）十万条数据测试

SELECT COUNT(1) FROM vote_record 
Time: 0.028s
SELECT COUNT(*) FROM vote_record 
Time: 0.027s
SELECT COUNT(id) FROM vote_record 
Time: 0.029s

SELECT COUNT(1) FROM vote_record WHERE id>30000
Time: 0.023s
SELECT COUNT(*) FROM vote_record WHERE id>30000
Time: 0.023s
SELECT COUNT(id) FROM vote_record WHERE id>30000
Time: 0.022s

百万条数据测试

SELECT COUNT(1) FROM vote_record 
Time: 0.230s
SELECT COUNT(*) FROM vote_record 
Time: 0.222s
SELECT COUNT(id) FROM vote_record 
Time: 0.238s

SELECT COUNT(1) FROM vote_record WHERE id>30000
Time: 0.285s
SELECT COUNT(*) FROM vote_record WHERE id>30000
Time: 0.282s
SELECT COUNT(id) FROM vote_record WHERE id>30000
Time: 0.287s

耗时统计：count(1)约等于count(※)<count(主键)<count(字段)
（2）差别：count(1)、count(*)会统计为null的行数，而count(字段)会统计字段不为null的行数。
（3）《高性能MySQL》推荐写法是count(※)

7.适时使用limit1

limit 1原理：查询到一条符合条件的记录则结束查询，如果查询条件是主键，不需要加limit 1 ，因为效果相同。
如果查询结果明确是一条记录并且条件非主键的话，可以加limit 1，提高查询效率。

SELECT * FROM vote_record WHERE user_id="mDTo9pqKkf3mvgFmG9z3"
Time: 0.002s
SELECT * FROM vote_record WHERE user_id="mDTo9pqKkf3mvgFmG9z3" LIMIT 1
Time: 0.000s

8.使用explain来查询select语句执行情况，针对性优化.

expain出来的信息有10列，分别是id、select_type、table、type、possible_keys、key、key_len、ref、rows、Extra
概要描述：
（1）id:选择标识符，sql的执行顺序，从大到小执行。
（2）select_type:表示子句查询的类型，是否使用UNION或依赖于外部查询等。
（3）table:输出结果集的表
（4）partitions:匹配的分区
（5）type:表示表的连接类型
对表访问方式，表示MySQL在表中找到所需行的方式，又称“访问类型”。
常用的类型有：ALL、index、range、 ref、eq_ref、const、system、NULL(从左到右，性能从差到好)
（6）possible_keys:表示查询时，可能使用的索引
（7）key:表示实际使用的索引
（8）key_len:索引字段的长度
（9）ref:列与索引的比较
（10）rows:扫描出的行数(估算的行数)
（11）filtered:按表条件过滤的行百分比，即目标记录行数占引擎查询行数的百分比。
（12）Extra:执行情况的描述和说明，可以通过该字段判断是否适合建立索引。

9.建立索引

索引是一种帮助数据库引擎快速找到记录的数据结构。
建立索引测试表

CREATE TABLE `index_test` (
	`id` INT (11) NOT NULL AUTO_INCREMENT,
	`user_id` VARCHAR (20) NOT NULL,
	`vote_id` INT (11) NOT NULL,
	`group_id` INT (11) NOT NULL,
  `dept_id` VARCHAR (20) NOT NULL,
	`project_id` INT (11) NOT NULL,
	`flow_id` INT (11) NOT NULL,
	`create_time` datetime NOT NULL,
	PRIMARY KEY (`id`),
	KEY `index_user_id` (`user_id`) USING HASH
) ENGINE = INNODB AUTO_INCREMENT = 1 DEFAULT CHARSET = utf8

索引分类：主键、unique索引、index索引等，这里主要测试unique索引、index索引的使用。
#建立unique索引，该行可以为null，但不能重复

ALTER TABLE index_test ADD UNIQUE (vote_id)

#建立unique组合索引，组合不能重复，索引不能重复

ALTER TABLE index_test ADD UNIQUE (group_id,dept_id)

#建立index索引， 没有任何限制。

ALTER TABLE index_test ADD INDEX(project_id)

#建立index组合索引，没有任何限制。

ALTER TABLE index_test ADD INDEX(flow_id,create_time)

SHOW INDEX FROM index_test（表名）：可以查看该表使用的索引。

建立索引原则：可以在where子句和连接子句（左右连接）中建立索引；
由于索引要占用磁盘空间，降低写入的性能，建立索引要适度。

索引相关概念
基数，每列唯一数的数量，比如id（主键）和性别，有n行数据，id基数为n，性别为2（男和女）。

索引实战，测试是否命中索引
#命中索引

EXPLAIN SELECT *FROM index_test WHERE user_nm="张三"

#没有命中索引

EXPLAIN SELECT *FROM index_test WHERE gener="男"

结论：建立索引，如果返回数据库中超过30%的数据则会全局扫描，不超过则会走索引。（百分之三十是大概值、经验值）

回表：给列建立索引，索引包括该列的值以及对应的rowid，如果查询该列数据需要通过rowid来进行则称为回表，
回表太多次，说明该列不适合建立索引，适合进行全局扫描查询。
explain语句下的extra可以看出是否会有回表：using where代表回表，using index代表使用索引。
可以根据是否回表确定是否需要建立索引。

#增加cust_id、cust_name索引

ALTER TABLE ad_user ADD INDEX(cust_id)
ALTER TABLE ad_user ADD INDEX(cust_name)

#查询解释

EXPLAIN SELECT * FROM ad_user WHERE user_id>2140
EXPLAIN SELECT * FROM ad_user WHERE cust_id=2157
EXPLAIN SELECT * FROM ad_user WHERE cust_name="神州广告制作公司"

#查看索引使用情况

SHOW STATUS LIKE 'handler_read%'

#Handler_read_key值很高说明索引使用率很高，Handler_read_rnd_next值很高说明文件读取下一行请求很高，索引使用率低

#前导模糊查询不会命中索引

EXPLAIN SELECT *FROM ad_user WHERE cust_name LIKE '%RRRR%'

#非前导模糊查询会命中索引

EXPLAIN SELECT *FROM ad_user WHERE cust_name LIKE 'RRRR%'

#出现隐式转换时不会命中索引，比如字符串的字段111，没有加引号。

EXPLAIN SELECT *FROM ad_user WHERE cust_name=1111
EXPLAIN SELECT *FROM ad_user WHERE cust_name="1111"

#给WEIXIN_ID、NICK_NAME、USER_PASS增加复合索引

ALTER TABLE ad_user ADD INDEX(WEIXIN_ID,NICK_NAME,USER_PASS)

#展示表索引

SHOW INDEX FROM ad_user

#复合索引中包含最左的字段才会命中索引(复合索引最左原则)

EXPLAIN SELECT *FROM ad_user WHERE weixin_id="2153" AND NICK_NAME="tian"
EXPLAIN SELECT *FROM ad_user WHERE NICK_NAME="tian"

#UNION可以命中索引 使用了额外暂存空间 Using temporary

EXPLAIN SELECT *FROM ad_user WHERE weixin_id="2154"
UNION
SELECT *FROM ad_user WHERE weixin_id="2153"

#in可以命中索引 Using index condition

EXPLAIN SELECT* FROM ad_user WHERE  weixin_id in ("2154","2153")

#OR 可以命中索引 Using index condition

EXPLAIN SELECT* FROM ad_user WHERE  weixin_id ="2153"OR  weixin_id ="2154"

#or 使用or的条件中如果有一行有索引、一行没有索引，则不会走索引，因为没有索引的会全局扫描，没必要再进行索引扫描增加IO。

EXPLAIN SELECT *FROM ad_user WHERE weixin_id ="2153"OR SALT="cljz2c"

#负条件："!="、“not in”、"not like"都不会命中索引 使用IN优化

EXPLAIN SELECT *FROM ad_user WHERE weixin_id !="2153"
EXPLAIN SELECT *FROM ad_user WHERE weixin_id NOT IN ("2154","2153")
EXPLAIN SELECT *FROM ad_user WHERE weixin_id NOT LIKE "2153"

#范围条件会命中索引：">"、"<"、"BETWEEN"等。

EXPLAIN SELECT *FROM ad_user WHERE weixin_id<="2153"
EXPLAIN SELECT *FROM ad_user WHERE weixin_id BETWEEN "2152" AND "2154"

#查询中有两个范围 无法两个索引都用到

EXPLAIN SELECT *FROM ad_user WHERE weixin_id<="2153" AND user_id>2145

#查询中有范围索引和等值索引 优先使用等值索引

EXPLAIN SELECT *FROM ad_user WHERE weixin_id<="2153" AND user_id=2140

#执行计算操作不能命中索引 业务操作放在业务处理层 减少CPU的负荷

EXPLAIN SELECT *FROM ad_user WHERE weixin_id<="2153"
EXPLAIN SELECT *FROM ad_user WHERE weixin_id+1<="2153"

#建立索引尽量不为null null不是一种不优秀的数据设计
#IS NULL 会命中索引和 IS NOT NULL无法命中索引

EXPLAIN SELECT * FROM ad_user WHERE weixin_id IS NULL  
EXPLAIN SELECT *FROM ad_user WHERE weixin_id IS NOT NULL

索引优化总结：
如果使用索引比全局扫描慢则不应该使用索引。
经验值，百分之三十，即要查询的数据占总数据的百分之三十内则容易命中索引。
更新比较频繁的字段不适合建立索引，因为更新字段会变更B+树，这非常消耗数据库性能。
业务上有唯一性的字段或者有唯一性多个字段组合都应该建立索引，可能插入的时候性能会变差，但查询时候可以提高效率，对于高并发产生脏数据的情况也是一种良好的预防。
多表关联的字段一定要有索引。

10.缓存优化的两种方案

方案一、MySQL自动刷新redis,MySQL触发器+UDF函数实现。
过程：对MySQL要设置缓存的数据设置触发器Trigger进行监听；
在客户端对数据库进行写操作时，触发器被触发调用MySQL的UDF函数；
UDF函数会把数据写入到redis,从而实现同步的目的。
场景：触发器本身会降低效率，适用于并不存在并发的情况。
方案二、解析MySQL的binlog实现，将数据库数据同步到redis。
MySQL主从复制原理：主数据库操作数据，写入到binlog中，从服务器调用IO读取主服务器的binlog
写入到从服务器的relaylog中，从服务器再解析relaylog写入到数据库中。
过程：这种方案就是将从服务器用redis来代替，同样读取数据库的binlog文件，
再解析binlog文件同步到redis中，难点就是解析binlog文件的过程。

11.分库分表

分库就是把单个数据库拆成多个数据库的过程。
分表就是把单给表拆成多个表的过程。
优点：增加可用性，拆成四个库，如果一个库宕机发生，其余三给库还能用，恢复起来速度比较快。
需要分库，怎么分库合适？
根据业务峰值读写QPS来进行分库，假设只需要3500数据库连接数，每个库的最大连接数为1000，则至少需要分成四个库才能满足连接数需求。
如何对数据进行切分？
常见的有横向切分和竖向切分。
横向切分最常见的是根据会员等级进行切分，存放在不同库表内。
竖向切分是根据不同的业务将对应的字段从表中切分开，比如把商品、用户、公司、订单等信息拆分开来。
或者根据表中数据进行更新的频率来，表中20个字段中只有5给字段经常更新，其余15个字段基本不变，将这5个字段拆分成一个单独的表，降低IO.