content
{:toc}

数据库并发。

数据库并发

脏页、事务、B-Tree索引

事务

数据库事务有严格的定义，必须同时满足4个特性：原子性(Atomic)、一致性(Consistency)、隔离线(Isolation)和持久性(Durabiliy)。简称ACID。

原子性：表示组成一个事务的多个数据库操作是一个不可分割的原子单元，只有所有的操作执行成功，整个事务才提交，事务中任何一个数据库操作失败，已执行的任何操作都必须撤销，让数据库回到初始状态。
一致性：事务操作成功后，数据库所处的状态和它的业务规则是一致的，即数据不会被破坏。
隔离性：在并发数据操作时，不同的事务拥有各自的数据空间，它们的操作不会对对方产生干扰。准确地说，并非要求做到完全无干扰，数据库规定了多种隔离级别，不同的蛤蜊级别对应不同的干扰程度，隔离级别越高，数据一致性越好，并发性越弱。
持久性：一旦事务提交成功后，事务中所有的数据操作都必须被持久化到数据库中，即使提交事务后，数据库马上崩溃，在数据库重启时，也必须保证能够通过某种机制回复数据。

数据库并发问题归结为5种，3类

脏读(dirty read)

A事务读取B事务尚未提交的更改数据，并在这个数据基础上操作。如果B事务回滚，则A事务读到的数据根本是不被承认的。

对A来说，B事务执行过程中对共享可变数据会进行2次修改，而A事务在B事务执行过程中读取了共享可变数据，而此时B事务只对共享可变数据进行了1次修改，还未完全改完(没有进行第2此修改)。即A读取了B事务执行过程中的一个临时值，而不是最终值。

读的是其他事务执行过程中的一步的数据，是不被承认的。是错的。

不可重复读(unrepeatable read)

read uncommitted

存在脏读、不可重复读、幻读、第二类丢失更新

解决了第一类丢失更新问题

一个事务中的任意一步的修改后（非最终值），其他事务都可以立即查到修改后的值。

其他事务修改相同的记录会被阻塞。

一个事务的任意一步

read committed

repeatable read

实际操作：

-- User表表结构：

mysql> describe user;
+-------+--------------+------+-----+---------+----------------+
| Field | Type         | Null | Key | Default | Extra          |
+-------+--------------+------+-----+---------+----------------+
| id    | int(11)      | NO   | PRI | NULL    | auto_increment |
| name  | varchar(255) | YES  |     | NULL    |                |
| age   | int(11)      | YES  |     | NULL    |                |
+-------+--------------+------+-----+---------+----------------+
3 rows in set (0.01 sec)

-- age字段是一个int类型的字段，模拟两个事务同时操作age字段，最后看结果。 

mysql> select @@tx_isolation;
+-----------------+
| @@tx_isolation  |
+-----------------+
| REPEATABLE-READ |
+-----------------+
1 row in set (0.01 sec)

-- *************都是Repeatable Read隔离级别的****************

Transaction 1:::

mysql> start transaction ;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | ss   |    3 |
+----+------+------+
1 row in set (0.00 sec)

-----------------
此处插入 事务2 ： 

transaction2
mysql> start transaction;
Query OK, 0 rows affected (0.00 sec)

mysql> select * from user;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | ss   |    3 |
+----+------+------+
1 row in set (0.00 sec)

-- 开始操作age字段，将age字段值在原来的基础上-1.
mysql> update user set age=age-1 where id=1; 
Query OK, 1 row affected (0.00 sec)
Rows matched: 1  Changed: 1  Warnings: 0

mysql> select * from user;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | ss   |    2 |
+----+------+------+
1 row in set (0.00 sec)

-- 提交
mysql> commit;
Query OK, 0 rows affected (0.01 sec)

mysql> select * from user;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | ss   |    2 |
+----+------+------+
1 row in set (0.01 sec)

---------------
-- 接着 操作事务1 

-- 此处查询user表，发现age还是3，没有出现不可重复读。 
mysql> select * from user;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | ss   |    3 |
+----+------+------+
1 row in set (0.00 sec)

-- 此处操作age字段，在age基础上-1.
mysql> update user set age=age-1 where id=1;
Query OK, 1 row affected (0.01 sec)
Rows matched: 1  Changed: 1  Warnings: 0

-- ！此处发现，age-1后直接变为1，而不是2。 保证了第二类丢失更新操作不会发生。 
mysql> select * from user ;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | ss   |    1 |
+----+------+------+
1 row in set (0.00 sec)

mysql> select * from user;
+----+------+------+
| id | name | age  |
+----+------+------+
|  1 | ss   |    1 |
+----+------+------+
1 row in set (0.00 sec)

由上可见，repeatable read，可重复读的隔离级别，在5类问题上，只剩下幻像读不能解决。

Mysql默认就是这个隔离级别。

锁

按锁定对象不同，分为表锁定和行锁定。

按并发事务锁定关系不同，分为共享锁定和独占锁定。

共享锁定会防止独占锁定，但允许其他的共享锁定。

而独占锁定既防止其他的独占锁定，也防止其他的共享锁定。

数据库基础

MySQL配置与启动

Linux平台下

配置文件时*.cnf

多个位置部署配置文件，大多数情况放在/etc/下，文件名称只能是my.cnf。

自带文件放在/usr/share/mysql下

1 2	# netstat -nlp //3306是MySQL服务器监听端口

#mysqladmin -uroot -p123456 shutdown

#cd /usr/bin
#./mysqld_safe &
#Starting mysqld daemon with databases from /var/lib/mysql

# service mysql start

#service mysql restart

#service mysql stop

[client] 
#password = your_password
port = 3306
socket = /tmp/mysql.sock
default-character-set=utf8 
#*************************分割线*******************

[mysqld]
basedir=D:/Program Files (x86)/MySql	 # 设置mysql的安装目录
datadir=D:/Program Files (x86)/MySql/data	 # 设置mysql数据库的数据的存放目录，必须是data，或者是//xxx/data


basedir=/usr/local/mysql
datadir=/usr/local/mysql/data

port = 3306
socket = /tmp/mysql.sock
default-character-set=utf8	# 设置mysql服务器的字符集
skip-locking
key_buffer = 16k
max_allowed_packet = 1M
table_cache = 4
sort_buffer_size = 64K
read_buffer_size = 256K
read_rnd_buffer_size = 256K
net_buffer_length = 2K
thread_stack = 64K

#*************************分割线*******************
[mysqldump]
quick
max_allowed_packet = 16M

[mysql]
no_auto_rehash

[isamchk]
key_buffer = 8M
sort_buffer_size = 8M

[myisamchk]
key_buffer = 8M
sort_buffer_site = 8M

[mysqlhotcopy]
interactive-timeout

[client]
port = 3306
socket = /tmp/mysql.sock
default-character-set = utf8mb4

[mysqld]
port = 3306
socket = /tmp/mysql.sock
basedir = /usr/local/mysql
datadir = /data/mysql
pid-file = /data/mysql/mysql.pid
user = mysql
bind-address = 0.0.0.0
server-id = 1
init-connect = 'SET NAMES utf8mb4'
character-set-server = utf8mb4

#skip-name-resolve
#skip-networking
back_log = 300
max_connections = 1000
max_connect_errors = 6000
open_files_limit = 65535
table_open_cache = 128
max_allowed_packet = 4M
binlog_cache_size = 1M
max_heap_table_size = 8M
tmp_table_size = 16M
read_buffer_size = 2M
read_rnd_buffer_size = 8M
sort_buffer_size = 8M
join_buffer_size = 8M
key_buffer_size = 4M
thread_cache_size = 8
query_cache_type = 1
query_cache_size = 8M
query_cache_limit = 2M
ft_min_word_len = 4
log_bin = mysql-bin
binlog_format = mixed
expire_logs_days = 30
log_error = /data/mysql/mysql-error.log
slow_query_log = 1
long_query_time = 1
slow_query_log_file = /data/mysql/mysql-slow.log
performance_schema = 0
explicit_defaults_for_timestamp
#lower_case_table_names = 1
skip-external-locking
default_storage_engine = InnoDB
#default-storage-engine = MyISAM
innodb_file_per_table = 1
innodb_open_files = 500
innodb_buffer_pool_size = 64M
innodb_write_io_threads = 4
innodb_read_io_threads = 4
innodb_thread_concurrency = 0
innodb_purge_threads = 1
innodb_flush_log_at_trx_commit = 2
innodb_log_buffer_size = 2M
innodb_log_file_size = 32M
innodb_log_files_in_group = 3
innodb_max_dirty_pages_pct = 90
innodb_lock_wait_timeout = 120
bulk_insert_buffer_size = 8M
myisam_sort_buffer_size = 8M
myisam_max_sort_file_size = 10G
myisam_repair_threads = 1
interactive_timeout = 28800
wait_timeout = 28800

[mysqldump]
quick
max_allowed_packet = 16M

[myisamchk]
key_buffer_size = 8M
sort_buffer_size = 8M
read_buffer = 4M
write_buffer = 4M

Mac平台

ps -ef|grep mysqld

netstat -an |grep 3306

mysql –help|grep ‘my.cnf’

ps aux|grep mysql|grep ‘my.cnf’

支付宝的五福很有意思。
社会主义核心价值观有十二个。
支付宝挑出来的是，富强，和谐，敬业，爱国，友善。
剩下的七个是民主、文明、自由、平等、公正、法治、诚信.

1	/etc/init.d/mysql start --log_error=/tmp/DB-Server.localdomain.err

OS X 系统安装的mysql默认是不用my.cnf配置文件的，仅是使用默认的数据库配置值。

如要进行数据库定制，可到’/usr/local/mysql/support-files/‘文件夹底下，把里面的任一个.cnf配置文件复制到/etc/目录底下并修改文件名称为my.cnf。

不过MAC OS X 系统的mysql确实不需要my.cnf来进行配置就已经足够可以使你满意运行了。

在Mac OS X 中默认是没有my.cnf 文件，如果需要对MySql 进行定制，拷贝以下目录中任意一个.cnf 文件。笔者拷贝my-medim.cnf 文件

1	/usr/local/mysql/support-files/

粘贴到以下目录下，

/etc

并且重命名为my.cnf ，然后修改my.cnf 即可进行定制了。

配置文件实例

数据类型

整型：

1~4字节、8字节都有。

1字节，2^8次方位。 java是byte类型。 256

2字节，2^(8*2)次方位。java是short类型。65535

4字节，2^32次方位。java是int类型。 4294967295.

8字节，2^64次方位。java是long类型。9223372036854775807,18446744073709551615。

位类型： BIT(M)

1~8字节。

最小值： BIT(1) 最大值：BIT(64)

浮点型：

float , 4 字节，最小值 +-

double，8字节，

定点数类型：

dec(m,d)， M+2字节，最大范围与 double相同， m表示整数位，d表示小数有效范围。

decimal(m,d)，默认是10，0.

float(m,d),double(m,d) decimal(m,d)

当存入的小数超过m和d指定的位数时，会截取，float和double是四舍五入。

bit()最终存的是二进制，且需要指定最大字节数，如果存的数超过最大字节数，则不能存入。

日期类型：

类型	字节	最小值	最大值
Date	4	1000-01-01	9999-12-31
DateTime	8	1000-01-01 00:00:00	9999-12-31 23:59:59
TimeStamp	4	1970010180001	2038年的某个时刻
Time	3	-838:59:59	838:59:59
Year	1	1901	2155

插入date类型，2017-01-04 12:12:13 或 20170104121213等

字符串：

字符串类型	字节	描述和存储需求
Char(M)	M	M为0~255之间的整数
VarChar(M)		M为0~65535之间的整数，值的长度+1字节
TinyBlob		允许长度0~255字节，
Blob		0~65535
MediumBlob		0~16772150
LongBlob		int长度
TinyText		0~255字节
Text		0~65535字节
MediumText		0~167772150字节
LongText		int长度字节
VarBinary(M)		0~M个字节的变长字节字符串
Binary(M)	M

char和varchar

char会删除保存的字符串末尾的空格。

保存的字符串长度比较短， 0~255。

存储引擎：

特点	MyISAM	InnoDB	memory	merge	ndb
存储限制	有？	64TB	有	没有	有
事务安全		支持
锁机制	表锁	行锁	表锁	表锁	行锁
B树搜索	支持	支持	支持	支持	支持
哈希索引			支持		支持
全文索引	支持
集群索引		支持
数据缓存		支持	支持		支持
索引缓存	支持	支持	支持	支持	支持
数据可压缩	支持
空间使用	低	高	N/A	低	低
内存使用	低	高	中	低	高
批量插入的速度	高	低	高	高	高
支持外键		支持

MyISAM

5.5版本前的默认引擎。

不支持事务、外键，优势是访问的速度快。

对事务完整性没有要求，或以Select、insert为主的应用基本上都可以使用这个引擎来创建表。

InnoDB

支持具有提交、回滚和崩溃恢复能力的事务安全。

对比MyISAM引擎，innodb写的效率差一些，并且会占用更多的磁盘空间以保留数据和索引。

如何选择

MyISAM：如果应用以读操作和插入操作为主，只有很少的更新和删除操作，并且对事务的完整性、并发性要求不是很高，那么选择这个存储引擎是非常合适的。MyISAM是在Web、数据仓储和其他应用环境下最常使用的存储引擎之一。
InnoDB：用于事务处理应用程序，支持外键。如果应用对事务完整性有比较高的要求，在并发环境下要求数据的一致性，数据操作出了插入和查询意外，还包括很多的更新、删除操作，那么InnoDB存储引擎是比较合适的选择。InnoDB存储引擎出了有效地降低由于删除护额更新导致的锁定，还可以确保事务的完整提交(Commit)和回滚(Rollback)，对于类似计费系统或者财务系统等对数据准确性要求比较高的系统，InnoDB都是合适的选择。
MEMORY：将所有数据保存在RAM中，在需要快速定位记录和其他类似数据的环境下可提供极快的访问。MEMORY的却显示对表的大小有限制，太大的表无法缓存在内存中，其次是要确保表的数据可以恢复，数据库异常终止后表中的数据hi可以恢复的。MEMORY表通常用于更新不太频繁的小表，泳衣快速得到访问结果。
MERGE：用于将一系列等同的MyISAM表以逻辑方式组合在一起，并作为一个对象引用它们。MERGE表的有点在于可以突破对单个MyISAM表大小的限制，并且通过将不同的表分布在多个磁盘上，可以有效改善MERGE表的访问效率。这对于诸如数据仓储等VLDB环境十分适合。
其他：以上是Mysql自带的，还有很多第三方的存储引擎，如列式存储引擎Infobright，高写性能高压缩的TokuDB。

选择合适的数据类型

Char和VarChar

区别：

char 固定空间大小，且会删除字符串末尾的空格。处理速度比varchar快，浪费空间。

vachar，存储空间依字符串变化而变化，不会自动删除空格。

还要根据使用的存储引擎选择。

MyISAM：建议使用固定长度的数据列替代可变长度。
MEMORY：无论使用char或是varchar，都使用固定长度的数据行存储。
InnoDB：建议使用VarChar。

Text和Blob

保存少量字符串时，使用char和varchar。而在保存较大文本时，通常会选择text和blob。

blob能用来保存二进制数据，比如照片。而Text只能保存字符数据，比如一篇文章或者日记。

sh-3.2# du -sh t.*

12K t.frm

19M t.ibd

optimize table ，删除记录存在空洞问题，

浮点数和定点数

float、double 浮点数。会四舍五入

decimal 定点数。定点数实际上是以字符串形式存放的，所以定点数可以更精确地确保存数据。会四舍五入

浮点数存在误差。java中使用bigdecimal

对货币等对精度敏感的数据，应该使用定点数表示或存储。

在编程中，如果用到浮点数，要特别注意误差问题，并尽量避免做浮点数比较。

注意浮点数中一些特殊值的处理。

存储过程

#创建数据库
DROP DATABASE IF EXISTS Dpro;
CREATE  DATABASE Dpro
CHARACTER SET utf8
;

USE Dpro;

#创建部门表
DROP TABLE IF EXISTS Employee;
CREATE TABLE Employee
(id INT NOT NULL PRIMARY KEY COMMENT '主键',
 name VARCHAR(20) NOT NULL COMMENT '人名',
 depid INT NOT NULL COMMENT '部门id'
);

#插入测试数据
INSERT INTO Employee(id,name,depid) VALUES(1,'陈',100),(2,'王',101),(3,'张',101),(4,'李',102),(5,'郭',103);

#创建存储过程
DROP PROCEDURE IF EXISTS Pro_Employee;
DELIMITER $$
CREATE PROCEDURE Pro_Employee(IN pdepid VARCHAR(20),OUT pcount INT )
READS SQL DATA
SQL SECURITY INVOKER
BEGIN
SELECT COUNT(id) INTO pcount FROM Employee WHERE depid=pdepid;

END$$
DELIMITER ;

#执行存储过程
CALL Pro_Employee(101,@pcount);

SELECT @pcount;

分区

Range分区

给定连续范围，把数据分配到不同的分区

List分区

类似emun，必须指定出分区的名字。


create table pageview(
	system_type varchar(20)
)partition by list columns(system_type)(
  partition p1 values in ('windows'),
  partition p2 values in ('linux')
);

Columns 分区

是mysql5.5引入的。

细分为Range Columns && List Columns。都支持整数、日期时间和字符串三种类型。

Hash分区

基于给定的分区个数，把数据分配到不同分区

Key分区

优化SQL

通过`show status`了解SQL的执行频率

Mysql客户端连接后，通过show [session|global] status提供服务器状态信息。

session和global分别显示当前连接的统计信息和自数据库上次启动至今的统计结果。

默认是session级别的。

执行show session status like 'Com_%'

重点观察：

Com_select : 执行Select语句的次数，一次查询累计加1.
Com_insert : 执行Insert操作的次数，对于批量插入的Insert操作，只累加一次。
Com_update : 执行Update操作的次数。
Com_delete : 执行Delete操作的次数。

以上，的统计，对于所有存储引擎都会统计。以下，这几个参数支队InnoDB引擎统计：

Innodb_rows_read : Select查询返回的行数。
Innodb_rows_inserted : 执行Insert操作插入的行数。
Innodb_rows_updated : 执行Update操作更新的行数。
Innodb_rows_deleted : 执行Delete操作删除的行数。

通过以上几个参数，可以容易的了解到当前数据库是以插入更新为主还是以查询操作为主。以及各种类型的SQL大致的执行比例是多少。对于更新操作的计数，是对执行次数的计数，不论提交还是回滚都会进行累加。

对事务性应用：

Com_commit :
Com_rollback :

可以了解事务提交和回滚的情况，对于回滚操作非常频繁的数据库，可能意味着应用编写存在问题。

Connections : 试图连接MySQL服务器的次数
Uptime : 服务器工作时间
Slow_queries : 慢查询的次数

定位执行效率较低的SQL语句

通过慢查询日志定位，用--log-slow-queries[=file_name]选项启动时，mysqld写一个包含所有执行时间超过long_query_time秒的SQL语句的日志文件。
慢查询日志在查询结束以后才记录，所以在应用反应执行效率出现问题的时候查询慢查询日志并不能定位问题，可以使用show processlist 命令查看当前MySQL在进行的线程，包括线程的状态、是否锁表等，可以实时地查看SQL的执行情况，同时对一些锁表操作进行优化。

通过`Explain`分析低效SQL的执行计划

通过上述步骤得到低效率的SQL语句后，可以通过Explain或者Desc命令获取MySQL如何执行Select语句的信息，包括在Select语句执行过程中表如何连接和连接的顺序，比如

select_type : 表示Select的类型，常见的取值有Simple(简单表，即不使用表连接或者子查询)，Primary(主查询，即外层的查询)、Union(Union中第二个或后面的查询语句)、SubQuery(子查询中的第一个Select)等。
table ：输出结果集的表

type ： MySQL根据语句执行时，在表中查找可能用到的方式：

|All|index|range|ref|eq_ref|const,system|NULL|

按顺序，性能有差到好。

All ：全表扫描。MySQL遍历全表来找到匹配的行
index ：索引全扫描。MySQL遍历整个索引查询匹配的行。
range ：索引范围扫描。常见between、<、>、<=等操作。
ref ：使用非唯一索引扫描或者唯一索引的前缀扫描，返回匹配讴歌单独值的记录行。
eq_ref : 类似ref，区别就是使用的索引是唯一索引，对于每个索引键值，表中只有一条记录匹配； primary key 或者 unique index 。

const/system ：单表中最多有一个匹配行，查询起来非常迅速

alter table user add unique index uk_name(name);

explain select * from (select * from user where name='Jack'); 
外层：type ： system
内层： eq_ref 

内层使用name这个唯一索引，所以是eq_ref
外层，使用内层的返回值，值返回了1条，所以是常量级别。

NULL : MySQL不用访问表或者索引，直接就能够得到结果，
1
explain select 1 from dual where 1;
其他如： ref_or_null（与ref类似，区别在于条件中包含对NULL的查询）、index_merge（索引合并优化）、unique_subquery(in的后面是一个查询主见字段的子查询)、index_subquery（与unique_subquer类似，in的后面是查询非唯一索引字段的子查询）等

possible_keys : 表示查询时可能使用的索引。
key ：表示实际使用的索引。
key_len : 使用到索引字段的长度。
rows ：扫描行的数量。
Extra ：执行情况的说明和描述，包含不适合在其他列中显示但是对执行计划非常重要的额外信息。

从4.1开始，引入了explain extended 命令，通过explain extended 后，加上show warnings 能够看到在Sql真正被执行前优化器做了哪些Sql改写。

以及，5.1开始支持分区功能，使用 explain partitions 命令查看sql所访问的分区。

使用show profile 分析SQL

从5.0.37版本开始增加了对show profiles和show profile语句的支持。

使用have_profiling , select @@have_profiling 查看当前使用的MySQL是否支持profile 。

且，通过select @@profiling; 可查看开启情况，默认是关闭的。通过set的session级别开启profiling。

通过profile，我们能更清楚的了解到SQL执行过程。

通过trace分析优化器如何选择执行计划

从5.6开始，提供了对SQL的追踪trace，通过trace文件能够进一步了解为什么优化器选择A执行而不是B。

确定问题并采取相应的优化措施

比如，全表扫描的，加上索引。

索引问题

索引是数据库优化中最常用也是最重要的手段之一，通过索引通常可以帮助用户解决大多数的SQL性能问题。

索引的存储分类

索引是在MySQL的存储引擎中实现的，而不是在服务器层实现的。每种存储引擎的索引都不一完全相同。

MySQL目前提供了以下4中索引：

B-Tree 索引：最常见的索引类型，大部分引擎都支持
HASH索引：只有Memory引擎支持，使用场景简单。
R-Tree索引(空间索引) ：空间索引是MyISAM的一个特殊索引类型，主要用于地理空间数据类型，实际使用较少。
Full-text（全文索引）：全文索引也是MyISAM的一个特殊索引类型，主要用于全文索引，Innodb从5.6版本开始提供对全文索引的支持。

Hash索引只有Memory支持，且hash索引只有在=条件下才会使用。条件范围查询搜索不会用到hash索引，如between，大于等于，小于等于等都不会用到hash索引。因为hash是对查询的值进行函数变换直接找到内存地址，并没有比较判断的步骤，所以不支持大小比较，也就无法使用范围查询。

1 2	alter tabel tbl_name add index idx_name(col_name); alter table tbl_name drop index idx_name;

MySQL如何使用索引

B-Tree索引是最常见的索引，构造类似二叉树，能根据键值提供一行或者一个行集的快速访问，通常只需要很少的读操作就可以找到正确的行。B-Tree中的B不是二叉树(Binary)，而是代表平衡树(balanced)。B-Tree不是一颗二叉树？

MySQL中能够使用索引的典型场景
1. 匹配全值(Match the full value)，对索引中所有列都指定具体的值。
2. 匹配值的范围查询(Match a range of values)，对索引的值能够进行范围查找。
3. 匹配最左前缀(Match a leftmost prefix)，仅仅使用索引中的最左边列进行查找。是MySQL中B-Tree的首要原则。说明：一个索引(col1+col2+col3)联合索引，能够包含 col1、(col1+col2)、(col1+col2+col3)、(col1+col3)为条件的等值查询时，使用该索引，但使用col2、(col2+col3)等值时，是不会使用该索引的。
4. 仅仅对索引进行查询(Index only query)。不仅where语句使用复合索引中的字段，select语句也使用复合索引中的字段。
5. 匹配列前缀(Match a column prefix)，仅仅使用索引中的第一列，且值包含索引第一列的开头的一部分进行查找。如 name like ‘pete%’。
6. 能够实现索引匹配部分精确而其他部分进行范围匹配(Match one part exactly and match a range on another part)。
7. 列名是索引，那么使用column_name is null 就会使用索引。
8. 5.6版本开始支持Index Condition Pushdown （ICP）特性。
存在索引但不会使用的典型场景
1. 以%开头的Like查询不能够利用B-Tree索引；执行计划中key的值null表示未使用索引。
2. 数据类型出现隐式转换的时候也不会使用索引，特别是当列类型是字符串，一定记得在where条件中吧字符串常量值用引号引用起来，否则即便这个列上有索引，MySQL也不会用到，应为MySQL默认吧输入的常量值进行转换后才进行检索。
3. 符合索引的情况下，假如查询条件不包含索引列最左边的部分，即不满足最左原则Leftmost，是不会使用复合索引的。
4. 如果MySQL估计使用索引比全表扫描更慢，则不适用索引。
5. 用or分隔开的条件，如果or前的条件中的列有索引，而后面的列中没有索引，那么涉及的索引都不会被用到。

查看索引使用情况

Handler_read_key的值很高，表示一个行被索引值读到的次数。很低表示索引并没有经常用到。

Handler_read_rnd_next的值高则意味着查询运行低效，并且应该建立索引补救。含义是在数据文件中读下一行的请求书。如果进行大量的表扫描，则通常后面表索引不正确或写入的查询没有利用索引。

show status like ‘Handler_read%’

两个简单实用的优化方法

定期分析表和检查表

分析语法：

Analyze [Local|No_write_to_binlog] Table tbl_name [,tbl_name]...

用于分析和存储表的关键字分布，分析的结果将可以使得系统得到准确的统计信息，使SQL能够生成正确的执行计划。

检查表的语法：

Check Table tbl_name [,tbl_name]... [option]...option={Quick|Fast|Medium|Extended|Changed}

检查表的作用是检查一个或多个表是否有错误。 check table 对 MyISAM和InnoDB表有作用。

也可以用于价差视图是否有错误。

定期优化表

优化表语法：

Optimize [Local|No_write_to_binlog] Table tbl_name [,tbl_name] ...

下列情况：如已经删除了表的一大部分，或者如果已经对含有可变长度的表(含有VarChar、Blob和Text列的表)进行了很多更改，则使用Optimize Table命令来进行表优化。

这个命令可以讲表中的空间碎片进行合并，消除由于删除或者更新造成的空间浪费。

此命令只对MyISAM、BDB和Innodb表起作用。

常用SQL优化

大批量插入数据

使用load命令导入数据时，适当的设置可以提高导入速度。

对MyISAM引擎的表：

1
2
3

Alter table tbl_name Disable Keys;
loading the data ...
Alter table tbl_name Enable Keys;

Disable keys和Enable keys用来打开或关闭MyISAM引擎表的非唯一索引的更新。

当导入的是空表时，默认就是先导入后创建索引，此时不用进行设置。

对于Innodb表引擎，上述方式不适用。

Innodb表是按照主键的顺序保存的，所以讲导入的数据按照主键的顺序排列，可以有效地提高导入数据的效率。
在导入数据前执行Set Unique_checks=0，关闭唯一性校验，在导入结束后执行Set Unique_Checks=1，恢复唯一性校验，可以提高导入的效率。
如果应用使用自动提交的方式，建议在导入前执行set Autocommit=0，关闭自动提交。导入结束后再执行set autocommit=1，打开自动提交。

优化Insert语句

以下优化方式：

从同一个客户端插入很多行，应尽量使用多个值表的Insert语句，这种方式将大大缩减客户端和数据库之间的连接、关闭等消耗。Insert into table_name values (),(),()...
从不同的客户端插入很多行，通过使用Insert delayed 语句得到更高的速度。Delayed含义是让Insert语句马上执行，其实数据都被放在内存的队列中，并没有真正的写入磁盘，比每条语句分别插入快的多；Low_priority刚好相反，在所有其他用户对表的额度写完成后才进行插入。
将索引文件和数据文件分在不同的磁盘上存放
批量插入，可以通过增加bulk_inset_buffer_size变量值的方法来提高速度，只队MyISAM有效。
当从一个文本文件装在一个表时，使用Load data infile，比insert快20倍。

优化Order By语句

MySQL两种排序方式：

第一种：通过有序索引，顺序扫描直接返回有序数据，使用explain分析时显示为Using Index，不需要额外的排序，操作效率较高。

第二种：通过对返回数据进行排序，即Filesort排序。所有不是通过索引直接返回排序结果的排序豆角Filesort排序。即通过索引返回的结果集的顺序不是想要的顺序，还需要一次额外的排序。

优化目标：尽量减少额外的排序，通过索引直接返回有序数据。Where条件和orderby使用相同的索引。
Filesort的优化

通过创建合适的索引减少filesort出现。
- 两次扫描算法(Two Passes)：第一次去除条件和指针信息，在sort buffer中排序去除的内容，然后根据排序后的指针内容在去扫描，取得数据。内存开销小。
- 一次扫描算法(Single Pass) ：一次去除所有满足条件的字段，在sort buffer中进行排序后直接输出结果集。内存开销比较大，效率比较高。
MySQL通过比较系统变量max_length_for_sort_data的大小和Query语句取出的字段总大小来判断使用何种方式。

适当的加大系统变量max_length_for_sort_data的值，能让MySQL选择更优化的filesort算法。

尽量使用必要的字段，Select具体字段的名称，而不是Select*选择所有字段，可以减少排序区的使用，提高SQL性能。

优化Group By语句

默认情况下，MySQL对所有GroupBy col1,col2…的字段进行排序。

如果查询包括GroupBy但用户想要避免排序结果的消耗，则可以指定OrderByNull进制排序。

优化嵌套查询

从4.1开始支持子查询，可以使用select语句创建一个单列的查询结果，把这个结果作为过滤条件用在另一个查询中。但是，有些情况下，可以被更有效率的连接(Join)替代。

MySQL优化OR条件

对于含有or的查询子句，如果利用索引，则or之间的每个条件列都必须用到索引；如果没有索引，则应该考虑增加索引。

查看执行计划描述，发现MySQL在处理含有or子句的查询时，实际是对or的各个字段分别查询后的结果进行了union操作。

优化分页查询

一种优化思路

在索引上完成排序分页的操作，最后根据主键关联回原表查询所需要的其他列内容。

mysql> explain select * from user order by name\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: Using filesort
1 row in set, 1 warning (0.00 sec)

mysql> explain select * from user order by name limit 2,1\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: Using filesort
1 row in set, 1 warning (0.01 sec)

mysql> explain select a.id,a.name from user a inner join(select id from user order by name limit 2,1)b on a.id=b.id\G
*************************** 1. row ***************************
           id: 1
  select_type: PRIMARY
        table: a
   partitions: NULL
         type: index
possible_keys: PRIMARY
          key: idx_name
      key_len: 62
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: Using index
*************************** 2. row ***************************
           id: 1
  select_type: PRIMARY
        table: <derived2>
   partitions: NULL
         type: ref
possible_keys: <auto_key0>
          key: <auto_key0>
      key_len: 4
          ref: us.a.id
         rows: 2
     filtered: 100.00
        Extra: Using index
*************************** 3. row ***************************
           id: 2
  select_type: DERIVED
        table: user
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_name
      key_len: 62
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: Using index
3 rows in set, 1 warning (0.03 sec)

mysql> explain select a.id,a.name from user a inner join(select id from user order by name)b on a.id=b.id\G
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: a
   partitions: NULL
         type: index
possible_keys: PRIMARY
          key: idx_name
      key_len: 62
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: Using index
*************************** 2. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
   partitions: NULL
         type: eq_ref
possible_keys: PRIMARY
          key: PRIMARY
      key_len: 4
          ref: us.a.id
         rows: 1
     filtered: 100.00
        Extra: NULL
2 rows in set, 1 warning (0.01 sec)

mysql> explain select * from user order by name desc\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
   partitions: NULL
         type: ALL
possible_keys: NULL
          key: NULL
      key_len: NULL
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: Using filesort
1 row in set, 1 warning (0.00 sec)

ERROR: 
No query specified

mysql> explain select id from user order by name desc\G;
*************************** 1. row ***************************
           id: 1
  select_type: SIMPLE
        table: user
   partitions: NULL
         type: index
possible_keys: NULL
          key: idx_name
      key_len: 62
          ref: NULL
         rows: 3
     filtered: 100.00
        Extra: Using index
1 row in set, 1 warning (0.00 sec)

ERROR: 
No query specified

直接使用不应用索引，指定select id应用索引，子查询。

第二种优化思路

把limit查询转换成某个位置的查询。

针对id连续的记录，可以直接算出本次分页要取得记录的id范围，那么where条件直接写idbetweenand接limitx，而不使用limitm,n。

使用SQL提示

SQL提示(SQL HINT),是数据库优化的一个重要手段！

Use Index

使用UseIndex来提供希望MySQL去参考的索引列表，可以让MySQL不再考虑其他可用的索引。

select * from tbl_name use index(idx_name) where xx=x ordery by xx

MySQL会自动选择是否使用指定的索引，可以不使用。
Ignore index

让MySQL忽略一个或者多个索引，可以使用Ignore Index作为Hint。
1
select * from tbl_name ignore index(idx_name) order by xx;
Force Index

强制MySQL使用一个特定的索引，相比于use index，force index更具有强制性。即使MySQL自己认为不该使用此索引，也会使用

select * from tbl_name force index(idx_name) order by xx;

常用SQL技巧

正则表达式的使用

Regular Expression，用来描述或者匹配符合规则的字符串。熟悉并掌握REGEXP的功能可以使模式匹配工作事半功倍。

巧用Rand()提取随机行

随机排序：

select * from tbl_name order by rand();

可以将记录随机排序，每次产生的都不一样。

利用Group By 的With Rollup子句

group by是用于分组，然后返回每组的统计信息。

加上with rollup，会对每个分组在进行一个‘总计’的统计。

注：rollup，和orderby 互斥。 limit用在rollup后面。

用Big Group Functions做统计

数据库名、表名大小写问题

使用外键需要注意的问题

优化数据库对象

优化表的数据类型

MySQL中可以使用Procedure Analyse()对当前应用的表进行分析，作用是对数据表中列的数据类型提出优化建议。

select * from tbl_name procedure analyse();

会给出每个字段的统计值，建议类型等。

通过拆分高表的访问效率

对数据表进行拆分。

如针对MyISAM类型的表进行，那么有两种拆分方法：

垂直拆分。把主码和一些列放到一个表，然后把主码和另外的额列放到另一个表中。

如果一个表中某些列常用，而另外一些列不常用，则可以采用垂直拆分。可以使得数据航变小，一个数据页就能存放更多的数据，在查询时就会减少I\O次数，缺点是需要管理冗余列，查询数据需要联合(JOIN)操作。
水平拆分，
- 表很大，分割后可以降低在查询时需要读的数据和索引的页数，同时也降低了索引的层数，提高查询速度。
- 表中的数据本来就有独立性，
- 需要把数据存到多个介质上。
水平拆分会增加应用的复杂度，通常查询时需要多个表名，查询所有数据需要Union操作。

逆规范化

使用中间表提高统计查询速度

锁问题

锁是计算机协调多个进程或线程并发访问某一资源的机制。共享的可变对象。

MySQL锁概述

MyISAM :

lock table tbl_name read|write [local] (,tbl_name read|write [local] ...)

优化MySQL Server

MySQL体系结构概览

MySQL内存管理及优化

内存优化原则

将尽量多的内存分配给MySQL做缓存，但要给操作系统和其他程序的运行预留足够的内存，否则如果产生SWAP页交换，将严重影响系统性能。
MyISAM引擎的数据文件读取依赖于操作系统自身的IO缓存，因此，如果有MyISAM表，就要预留更多的内存给操作系统做IO缓存。
排序区、连接区等缓存是分配给每个数据库回话(Session)专用的，如果设置太大，不但浪费内存资源，而且在并发连接较高时会导致物理内存耗尽。

MyISAM内存优化

缓存，对于把“数据”从放到内存(from磁盘)中，就叫缓存。

索引缓存，顾名思义，就是把索引放在内存中，内存的大小。语句需要遍历或者操作索引，就需要把被操作的索引读取到内存块中，且当需要把新的索引放入已经满了的内存块中时，根据对应的策略，删除一部分在内存中的索引腾出空间。

key_buffer_size设置

决定MyISAM索引块缓存区的大小，直接影响MyISAM表的存取效率。可以再MySQL的参数文件中我设置key_buffer_size的值，建议至少将1/4可用内存分配给key_buffer_size;

使用多个索引缓存

各个session共享key_buffer提高了MyISAM索引存取的性能，但也存在资源竞争。

从5.1版本开始引入了多索引缓存机制，可以将不同表的索引缓存到不同的key_buffer中。

set global indexbuffer_name.key_buffer_size=128*1024;

可以创建新的key buffer，index buffer_name是新建索引缓存的名称。

#删除 索引缓存
mysql> set global indexbuffer_name.key_buffer_size=0
#通过 select @@index... 查看
mysql> select @@indexbuffer_name.key_buffer_size;
#查看默认 索引缓存 大小
mysql> show variables like 'key_buffer_size';

默认情况下，使用默认的key buffer 缓存MyISAM表的索引。使用cache index指定目标表的索引缓存。

1	mysql> cache index tbl1_name,tbl2_name in indexbuffer_cachename;

通常情况下，做法是使用配置文件在MySQL启动时自动创建并加载指定索引缓存。

#.ini
key_buffer_size=4G
#indexbuffer_cachename.key_buffer_size=2G
hot_cache.key_buffer_size=1G
cold_cache.key_buffer_size=1G
init_file=/path/mysqld_init.sql

#mysqld_init.sql
cache index tbl_name in hot_cache;
cache index tbl2_name in cold_cache;
load index into cache tbl_name,tbl2_name;

调整过期策略

默认情况下使用LRU(Least Recently Used)策略来选择要淘汰的索引数据块。

调整为中点插入策略(Midpoint Insertion Strategy) 。
调整read_buffer_size和read_rnd_buffer_size

经常顺序扫描MyISAM表，可以通过增大read_buffer_size的值改善性能。这个值为每个session独占，设置太大容易造成内存浪费。

对于需要排序的MyISAM表查询，如带有order by 子句，适当加大read_rd_buffer_size的值。同上，也是每个Session独占。不要设置的太大。

InnoDB内存优化

InnoDB缓存机制

InnoDB用一块内存区做IO缓存池，该缓存不仅用来缓存InnoDB的索引块，也用来缓存InnoDB的数据块。
innodb_buffer_pool_size的设置

决定最大缓存区的大小。在一个专用数据库服务器上，可以将80%可用内存分配给InnoDB buffer pool，mysqladmin -S /tmp/mysql.sock ext|grep -i innodb_buffer_pool查看bffer pool的使用情况。
调整old sublist 大小

mysql>show global variables like '%innodb_old_blocks_pct%';

取值范围是5~95，默认值是37(约等于3/8)
调整innodb_old_blocks_time的设置

决定了缓存数据块有old sublist转移到young sublist的快慢，
调整缓存池数量，减少内部对缓存池数据结构的争用

innodb_buffer_pool_instances配置参数，InnoDB缓存系统将根据参数innodb_buffer_pool_size指定大小的缓存平分为indoor_buffer_pool_instances个buffer pool。
控制innodb buffer刷新，延长数据缓存时间，减缓磁盘IO
- innodb_max_dirty_pages_pct ，控制缓存池中脏页的最大比例，默认值是75%，如果脏页的数量达到或超过该阈值，InnoDB的后台线程开始缓存刷新。
- innodb_io_capacity，代表磁盘系统的io能力，在一定程度上代表磁盘每秒可完成的IO次数。默认值是200，对于转速较低的磁盘，如7200RPM可将值降低到100。对于固态硬盘可适当提高。
InnoDB doublewrite

默认开启，可以通过关闭来提高性能。

调整用户服务线程排序缓存区

调整参数 sort_buffer_size的值增大排序缓存区，改善order by子句和group 子句的SQL性能。

对于无法通过索引进行连接操作的查询，可以尝试通过增大 join_buffer_size的值来改善性能。

注意，sort buffer和join buffer 都是面向session的，不共享，如果设置过大会造成内存浪费。

尤其是join buffer，比较好的策略是设置较小的全局join buffer，对于要做复杂连接的session单独设置较大的join_buffer_size。

InnoDB log 机制及优化

采用redo log机制来保证事务更新的一致性和持久性。

当更新数据时，InnoDB内部操作流程：

将数据读入InnoDB buffer pool ，并对相关记录加独占锁。
将UNDO信息写入undo表空间的回滚字段中。
更改缓存页中的数据，并将更新记录写入 redo buffer中；
提交时，根据innodb_flush_log_at_trx_commit的设置，用不同的方式将redo buffer中的更新记录刷新到InnoDB redo log file中，然后释放独占锁。
最后，后台IO线程根据需要择机将缓存中更新过的数据刷新到磁盘文件中。

应用优化

使用连接池

减少对MySQL的访问

避免对同一数据做重复检索

使用查询缓存

4.1版本后增加查询缓存(MySQL Query Cache)

增加Cache层

负载均衡

负载均衡(Load Balance)是实际应用中使用非常普遍的一种优化方法，它的机制就是利用某种均衡算法，将固定的负载量分不到不同的服务器上，一次来减轻单台服务器的负载，达到优化的目的。

利用MySQL复制分流查询操作

用MySQL的主从复制可以有效地分流更新操作和查询操作，一个主服务器承担更新操作，而多台服务器承担查询操作，主从之间通过复制实现数据的同步。多台子从服务器一方面用来确保可用性，一方面可以创建不同的索引以满足不同查询的需要。

采用分布式数据库架构

MySQL的Cluster功能。

其他优化措施

对于没有删除操作的MyISAM表，插入操作和查询操作可以并行进行。
充分利用列的默认值，只有当插入的值不同于默认值时，才明确的插入值。
表字段尽量不使用自增长变量。

日志

错误日志

二进制日志(改、删操作日志)

开启

flush logs

MySQL5.7 中 my.cnf 配置：

log_bin #开启binlog
binlog_format=MIXED #设置binlog内容默认，raw和可读sql混合制

log_error = /usr/local/mysql/data/mysql-error.log #设置logerror文件位置
port = 3306
server_id = 127
socket = /tmp/mysql.sock

开启二进制日志(用于记录修改删除操作的日志，可用于主从分布)。

错误日志是必须开启的默认开启，使用默认的文件名和位置，可用log_error指定。

使用 mysqlbinlog 命令查看二进制日志。

删除

全部删除

MySQL 客户端执行 Reset Master命令，会将当前所有的日志文件删除，从1开始从新记录。

根据编号删除

mysql> purge master logs to 'mysql-bin.******'

将删除******编号前的日志。

mysql> purge master logs to 'localhost-bin.000006'

根据日期删除

mysql> purge master logs before 'yyyy-mm-dd hh24:mi:ss'

删除指定日期时间前的所有日志。

mysql> purge master logs before '2017-01-20 12:12:12'

配置自动删除

设置参数--expire_logs_days=#

设置日志的过期天数，过了指定天后，日志自动删除。

在my.cnf中，[mysqld]加入 expire_logs_day=3，重启MySQL服务。

MySQL权限

工作原理

MySQL是通过IP和用户名联合进行确认的，如 root@localhost表示用户root只能从本地(localhost)登陆才可以通过认证。

存取过程：

对连接的用户进行身份认证，合法的用户通过认证。
对通过认证的用户，赋予相应的权限。用户可以在指定数据库的指定表的指定列有指定的增删改查或DDL权限

用户信息和权限信息保存在数据库mysql的 user、host和db三个重要的权限表中。

MySQL复制

从3.23版本开始提供复制功能。

指将主数据库的DDL和DML操作通过二进制日志传到从库上，然后在从库上对这些日志进行重新执行，使得从库和主库的数据保持同步。

数据库--并发