目录
LPAD(str,n ,pad)和 RPAD(str,n ,pad)函数
3.case when [...] then [...] ... else[default] end
一、SQL 基础
SQL 分类
- DDL（Data Definition Languages）：数据定义语句，用于不同的数据段、数据库、表、列、索引等数据库对象的定义。常用的语句关键字主要包括 create、drop、alter等。
- DML（Data Manipulation Language）：数据操纵语句，用于添加、删除、更新和查询数据库记录，并检查数据完整性，常用的语句关键字主要包括 insert、delete、udpate 和select 等。
- DCL（Data Control Language）：数据控制语句，用于控制不同数据段直接的许可和访问级别的语句。这些语句定义了数据库、表、字段、用户的访问权限和安全级别。主要的语句关键字包括 grant、revoke 等
DDL 语句
1.创建并使用数据库
create database dbname;
use dbname;
show tables; //查看 dbname 数据库中创建的所有数据表
.
.
.
drop dbname； //删除 dbname 数据库2.创建并查看表定义
CREATE TABLE mytable
(
id INT NOT NULL AUTO_INCREMENT,
col1 INT NOT NULL DEFAULT 1,
col2 VARCHAR(45) NULL,
col3 DATE NULL,
PRIMARY KEY (`id`)
);
desc mytable; //输出了表中各列的类型，可否为 NULL，Default 值等信息
show create table mytable; //输出的创建表的全面信息3.创建外键
create table country
(
country_id int not null auto_increment,
country char(10) not null,
last_update timestamp not null default current_timestamp,
primary key(country_id)
) engine = innodb;
create table city
(
city_id int not null auto_increment,
city char(10) not null,
country_id int not null,
last_update timestamp not null,
primary key(city_id),
foreign key(country_id ) references country(country_id )
)engine = innodb;
4.修改表 (modify,change)
modify 语句，可以修改列的类型
alter table emp
modify ename varchar(20);change 语句，可以同时修改列名与列类型
alter table emp
change age age1 int(4) ;前面介绍的的字段增加和修改语法（ADD/CNAHGE/MODIFY）中，都有一个可选项 first | after column_name，这个选项可以用来修改字段在表中的位置，默认 ADD 增加的新字段是加在表的最后位置，而 CHANGE/MODIFY 默认都不会改变字段的位置。
DML 语句
1.插入记录 insert into 语句
如果只给出了插入行的部分数据，则剩余列为默认或 NULL 值。
INSERT INTO Customers(cust_id, cust_name)
VALUES('100006', 'Tony Land');
VALUES('100007', 'James Bond');
VALUES('100008', 'Alex Brown');也可以不用指定字段名称，但是 values 后面的顺序应该和字段的排列顺序一致，这时必须写出所有列的值，否则会有 Column count doesn't match 异常。
insert into emp
values('lisa','2003-02-01','3000',2);2.更新记录 update 语句
update 命令可以同时更新多个表中数据
update emp as a,dept as b
set a.sal=a.sal*b.deptno,b.deptname=a.ename
where a.deptno=b.deptno;DCL 语句
DCL 语句主要是 DBA 用来管理系统中的对象权限时所使用，一般的开发人员很少使用。
grant 和 revoke 语句可以授出和收回了用户的部分权限。
数值类型
int(x)
MySQL 还支持在类型名称后面的小括号内指定显示宽度，例如 int(5)表示当数值宽度小于 5 位的时候在数字前面填满宽度，如果不显示指定宽度则默认为 int(11)。一般配合 zerofill 使用，顾名思义，zerofill 就是用“0”填充的意思，也就是在数字位数不够的空间用字符“0”填满。以下几个例子分别描述了填充前后的区别。
alter table t1
modify id1 int zerofill;
alter table t1
modify id2 int(5) zerofill;
select * from t1;
//+------------+-------+
//| id1 | id2 |
//+------------+-------+
//| 0000000001 | 00001 |
//+------------+-------+
//1 row in set (0.00 sec)整数类型还有一个属性：AUTO_INCREMENT。在需要产生唯一标识符或顺序值时，可利用此属性，这个属性只用于整数类型。AUTO_INCREMENT 值一般从 1 开始，每行增加 1。在插入 NULL 到一个 AUTO_INCREMENT 列时，MySQL 插入一个比该列中当前最大值大 1 的值。一个表中最多只能有一个 AUTO_INCREMENT列。对于任何想要使用 AUTO_INCREMENT 的列，应该定义为 NOT NULL，并定义为 PRIMARY KEY 或定义为 UNIQUE 键。
CREATE TABLE AI
(ID INT AUTO_INCREMENT NOT NULL PRIMARY KEY);char(x) 和 varchar(x)
都用来保存 MySQL 中较短的字符串。二者的主要区别在于存储方式的不同：CHAR 列的长度固定为创建表时声明的长度，长度可以为从 0～255 的任何值；而 VARCHAR 列中的值为可变长字符串，长度可以指定为 0～255 （5.0.3 以前）或者 65535 （5.0.3以后）之间的值。在检索的时候，CHAR 列删除了尾部的空格，而 VARCHAR 则保留这些空格。
enum 枚举类型
create table t (gender enum('M','F'));
INSERT INTO t VALUES('M'),('1'),('f'),(NULL);
select * from t;
//+--------+
//| gender |
//+--------+
//| M |
//| M |
//| F |
//| NULL |
//+--------+
//4 rows in set (0.01 sec)ENUM 类型是忽略大小写的，对'M'、'f'在存储的时候将它们都转成了大写，还可以看出对于插入不在 ENUM 指定范围内的值时，并没有返回警告，而是插入了 enum('M','F')的第一值'M'，这点用户在使用时要特别注意。
SET 类型
Set 和 ENUM 类型非常类似，也是一个字符串对象，主要的区别在于 Set 类型一次可以选取多个成员，而 ENUM
则只能选一个。
Create table t (col set （'a','b','c','d'）;
insert into t values('a,b'),('a,d,a'),('a,b'),('a,c'),('a');
select * from t;
+------+
| col |
+------+
| a,b |
| a,d |
| a,b |
| a,c |
| a |
+------+对于超出允许值范围的值例如（'a,d,f'）将不允许注入到上面例子中设置的 SET 类型列中，而对于（'a,d,a'）这样包含重复成员的集合将只取一次，写入后的结果为“a,d”。
常用函数
字符串函数
-
concat(s1,s2,…sn)函数
把传入的参数连接成为一个字符串。任何字符串与 NULL 进行连接的结果都将是 NULL。
select concat('aaa','bbb','ccc') ,concat('aaa',null);-
insert(str,x,y,instr)函数
将字符串 str 从第 x 位置开始，y 个字符长的子串替换为字符串 instr。
select INSERT('beijing2008you',12,3, 'me') ;
+-------------------------------------+
| INSERT('beijing2008you',12,3, 'me') |
+-------------------------------------+
| beijing2008me |
+-------------------------------------+-
LPAD(str,n ,pad)和 RPAD(str,n ,pad)函数
用字符串 pad 对 str 最左边和最右边进行填充,直到长度为 n 个字符长度。
select lpad('2008',20,'beijing'),rpad('beijing',20,'2008');
+---------------------------+---------------------------+
| lpad('2008',20,'beijing') | rpad('beijing',20,'2008') |
+---------------------------+---------------------------+
| beijingbeijingbe2008 | beijing2008200820082 |
+---------------------------+---------------------------+-
REPEAT(str,x)函数
返回 str 重复 x 次的结果。
数值函数
注意 TRUNCATE 和 ROUND 的区别在于 TRUNCATE 仅仅是截断，而不进行四舍五入。下例中描述了二者的区别：
select ROUND(1.235,2),TRUNCATE(1.235,2);
+----------------+-------------------+
| ROUND(1.235,2) | TRUNCATE(1.235,2) |
+----------------+-------------------+
| 1.24 | 1.23 |
+----------------+-------------------+
1 row in set (0.00 sec)日期和时间函数
- curdate() 函数：返回当前日期，只包含年月日
- curtime() 函数：返回当前时间，只包含时分秒
- now() 函数：返回当前的日期和时间，年月日时分秒全都包含
- week() 和 year() 函数：参数为 now() 前者返回所给的日期是一年中的第几周，后者返回所给的日期是哪一年
流程函数
1.if(value，t，f)
如果 value 是真，返回 t；否则返回 f
select if(salary>2000,'high','low')
from salary;
//+------------------------------+
//| if(salary>2000,'high','low') |
//+------------------------------+
//| low |
//| low |
//| high |
//| high |
//| high |
//+------------------------------+2.ifnull(column，value)
这个函数一般用来替换 NULL 值的，我们知道 NULL 值是不能参与数值运算的，下面这个语句就是把 NULL 值用 value 来替换
3.case when [...] then [...] ... else[default] end
select case when salary<=2000 then 'low' else 'high' end
from salary;
+---------------------------------------------------+
| case when salary<=2000 then 'low' else 'high' end |
+---------------------------------------------------+
| low |
| low |
| high |
| high |
| high |
| high |
+---------------------------------------------------+二、SQL 开发
存储引擎概述
创建新表的时候可以通过 engine 关键字设置新建表的存储引擎，例如：
CREATE TABLE mytable
(
id INT NOT NULL AUTO_INCREMENT,
col1 INT NOT NULL DEFAULT 1,
col2 VARCHAR(45) NULL,
col3 DATE NULL,
PRIMARY KEY (`id`)
) engine = innodb;可以使用 ALTER TABLE 语句，将一个已经存在的表修改成其他的存储引擎
MyISAM
- MyISAM 不支持事务，也不支持外键，其优势是访问的速度快，对事务完整性没有要求或者以 SELECT、INSERT 为主的应用基本上都可以使用这个引擎来创建表。
- 不支持行级锁，只能对整张表加锁，读取时会对需要读到的所有表加共享锁，写入时则对表加排它锁。但在表有读取操作的同时，也可以往表中插入新的记录，这被称为并发插入（CONCURRENT INSERT）。
- MyISAM 类型的表可能会损坏，原因可能是多种多样的，损坏后的表可能不能访问，可能导致一些数据丢失，而且修复操作是非常慢的。
- MyISAM 支持压缩表和动态表，这样存储的优点是占用的空间相对较少。
InnoDB
- 是 MySQL 默认的事务型存储引擎，支持外键，只有在需要它不支持的特性时，才考虑使用其它存储引擎。
- 实现了四个标准的隔离级别，默认级别是可重复读（REPEATABLE READ）。在可重复读隔离级别下，通过多版本并发控制（MVCC）+ 间隙锁（Next-Key Locking）防止幻影读。
- 主索引是聚簇索引，在索引中保存了数据，从而避免直接读取磁盘，因此对查询性能有很大的提升。
- 内部做了很多优化，包括从磁盘读取数据时采用的可预测性读、能够加快读操作并且自动创建的自适应哈希索引、能够加速插入操作的插入缓冲区等。
- 支持真正的在线热备份。其它存储引擎不支持在线热备份，要获取一致性视图需要停止对所有表的写入，而在读写混合场景中，停止写入可能也意味着停止读取。
索引的设计和使用
所有 MySQL 列类型都可以被索引，对相关列使用索引是提高 SELECT 操作性能的最佳途径。
索引在创建表的时候可以同时创建，也可以随时增加新的索引，创建索引的语法为：
// 创建索引
alter table tab_with_index add index id(id);
create index index_name on table_name(column_name);
// 删除索引
drop index index_name on table_name;(内部被映射成alter table语句);
create table mytable (
id int ,
name varchar(20) ,
sex boolean ,
index(id)
);索引的设计原则
- 搜索的索引列不一定是所要选择的列。也就是说，最适合索引的列是出现在 where 子句中的列，而不是出现在 select 关键字后选择的列。
- 索引的列的基数越大，索引的效果越好。（区分度越大）
- 使用短索引，如果对字符串进行索引，应该指定一个前缀长度。例如，如果有一个 char(200) 列，如果在前10个或20个字符内，多数值是唯一的，就不要对整个列进行索引，而且能节省大量索引空间。
- 最左前缀，在创建一个n列的索引时，实际是创建了 MySQL 可利用的 n 个索引。多列索引可以起几个索引的作用，可利用索引中最左边的列集来匹配行。
最左前缀：查询条件中的所有字段需要从左边起按顺序出现在多列索引中，查询条件的字段数要小于等于多列索引的字段数，中间字段不能存在范围查询的字段(<,like等)，这样的sql可以使用该多列索引。在创建多列索引时，要根据业务需求，where子句中使用最频繁的一列放在最左边。当创建(a,b,c)复合索引时，想要索引生效的话,只能使用 a和a,b和a,b,c三种组合
实例：以下是常见的几个查询：
mysql>SELECT `a`,`b`,`c` FROM A WHERE `a`='aa' ;
- 不要过度索引，创建过多的索引会占用过多的磁盘空间，并降低写操作的性能。在修改表的内容时，索引必须进行更新，有时可能需要重构，因此，索引越多，所花的时间越长。
三、SQL 优化
使用正则表达式
使用 regexp 关键字匹配正则表达式
* 优化 SQL 语句的一般步骤
1.show statue 命令
可以通过 show status 命令了解各种 SQL 语句的 执行效率。
show status like 'Com_%';Com_xxx 表示每个 xxx 语句执行的次数，我们通常比较关心的是以下几个统计参数。
- Com_select：执行 select 操作的次数，一次查询只累加 1。
- Com_insert：执行 INSERT 操作的次数，对于批量插入的 INSERT 操作，只累加一次。
- Com_update：执行 UPDATE 操作的次数。
- Com_delete：执行 DELETE 操作的次数。
通过以上几个参数，可以很容易地了解当前数据库的应用是以插入更新为主还是以查询操作为主，以及各种类型的 SQL 大致的执行比例是多少。对于更新操作的计数，是对执行次数的计数，不论提交还是回滚都会进行累加。
对于事务型的应用，通过 Com_commit 和 Com_rollback 可以了解事务提交和回滚的情况，对于回滚操作非常频繁的数据库，可能意味着应用编写存在问题。
2.定位执行效率较低的 SQL 语句
可以通过以下两种方式：
通过 set global slow_query_log = ON 命令 开启慢查询日志记录，使用 long_query_time 变量控制 记录超过的时间
- 通过慢查询日志定位，用 --log-slow-queries[=file_name] 选项启动时，mysqld 写一个包含所有执行时间超过 long_query_time 秒的 SQL 语句的日志文件。
- 慢查询日志在查询结束以后才记录，所以在应用反映执行效率出现问题的时候查询慢查询日志并不能定位问题，可以使用 show processlist 命令查看当前 MySQL 在进行的线程，包括线程的状态、是否锁表等，可以实时地查看 SQL 的执行情况，同时对一些锁表操作进行优化。
3.通过 explain 命令分析低效的 SQL 计划
通过以上步骤查询到效率低的 SQL 语句后，可以通过 EXPLAIN 命令获取 MySQL如何执行 SELECT 语句的信息，包括在 SELECT 语句执行过程中表如何连接和连接的顺序，比如想计算 2006 年所有公司的销售额，需要关联 sales 表和 company 表，并且对 moneys 字段做求和（sum）操作，相应 SQL 的执行计划如下：
explain select sum(moneys) from sales a,company b
where a.company_id = b.id and a.year = 2006\G;
/************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: a
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1000
Extra: Using where
*************************** 2. row ***************************
id: 1
select_type: SIMPLE
table: b
type: ref
possible_keys: ind_company_id
key: ind_company_id
key_len: 5
ref: sakila.a.company_id
rows: 1
Extra: Using where; Using index*/- select_type：表示 SELECT 的类型，常见的取值有 SIMPLE（简单表，即不使用表连接或者子查询）、PRIMARY（主查询，即外层的查询）、UNION（UNION 中的第二个或者后面的查询语句）、SUBQUERY（子查询中的第一个 SELECT）
- type：表示表的连接类型，性能由好到差的连接类型为 system（表中仅有一行，即常量表）、const（单表中最多有一个匹配行，例如 primary key 或者 unique index）、eq_ref（对于前面的每一行，在此表中只查询一条记录，简单来说，就是多表连接中使用 primary key或者 unique index）、ref （与 eq_ref 类似，区别在于不是使用 primarykey 或者 unique index，而是使用普通的索引）；
- possible_keys：表示查询时，可能使用的索引；
- key：表示实际使用的索引；
- key_len：索引字段的长度；
- rows：扫描行的数量；
- Extra：执行情况的说明和描述。
4.确定问题并采取相应的优化措施
经过以上步骤，基本就可以确认问题出现的原因。已经可以确认是对 a 表的全表扫描导致效率的不理想（explain 的结果显示需要扫描的行为 1000 行），那么对 a 表的 year 字段创建索引。
可以发现建立索引后对 a 表需要扫描的行数明显减少（从 1000 行减少到 1 行），可见索引的使用可以大大提高数据库的访问速度，尤其在表很庞大的时候这种优势更为明显。
索引问题
1.索引的存储分类
MySQL 中索引的存储类型目前只有两种（BTREE 和 HASH），具体和表的存储引擎相关： MyISAM 和 InnoDB 存储引擎都只支持 BTREE 索引；MEMORY/HEAP 存储引擎可以支持 HASH 和 BTREE 索引。
MySQL 还支持建立对文本的前缀索引。例如 name 字段，可以只取 name 的前 4 个字符建立索引，这个特性可以大大缩小索引文件的大小，用户在设计表结构的时候也可以对文本列根据此特性进行灵活的设计。
create index in_name on company(name(4));2.使用索引
（1）对于创建的多列索引，只要查询的条件用到了最左边的列，索引一般就会被使用。
//首先按 company_id，moneys 的顺序创建一个复合索引，具体如下：
create index ind_sales2_companyid_moneys on sales2(company_id,moneys);
//然后按 company_id 进行表查询，具体如下：
explain select * from sales2 where company_id = 2006\G;
/************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sales2
type: ref
possible_keys: ind_sales2_companyid_moneys
key: ind_sales2_companyid_moneys
key_len: 5
ref: const
rows: 1
Extra: Using where */（2）对于使用 like 的查询，后面如果是常量并且只有％号不在第一个字符，索引才可能会被使用
explain select * from company2 where name like '3%'\G;
/************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: company2
type: range
possible_keys: ind_company2_name
key: ind_company2_name
key_len: 11
ref: NULL
rows: 103
Extra: Using where */（3）如果列名是索引，使用 column_name is null 将使用索引。如下例中查询 name 为 null 的记录就用到了索引：
存在但是不会使用索引的几种情况：
（1）根据 MySQL 的优化，如果使用索引比全表扫面更慢，则不使用索引。
（2）MEMORY/HEAP 表并且 where 条件中不使用“=”进行索引列，那么不会用到索引。
（3） 用 or 分割开的条件，如果 or 前的条件中的列有索引，而后面的列中没有索引，那么涉及到的索引都不会被用到。
（4）如果列类型是字符串，那么一定记得在 where 条件中把字符常量值用引号引起来，否则的话即便这个列上有索引，MySQL 也不会用到的。
explain select * from company2 where name = '294'\G3.查看索引使用情况
Handler_read_key 的值将很高，这个值代表了一个行被索引值读的次数，很低的值表明增加索引得到的性能改善不高，因为索引并不经常使用。
Handler_read_rnd_next 的值高则意味着查询运行低效，这个值的含义是在数据文件中读下一行的请求数。如果正进行大量的表扫描，应该建立索引补救。
排错与优化
1.定期分析检查和修复表
analyze table sales;
check table sales;
repair table sales;analyze 语句用于分析和存储表的关键字分布，分析的结果将可以使得系统得到准确的统计信息，使得 SQL 能够生成正确的执行计划。如果用户感觉实际执行计划并不是预期的执行计划，执行一次分析表可能会解决问题。在分析期间，使用一个读取锁定对表进行锁定。
check 检查表的作用是检查一个或多个表是否有错误。CHECK TABLE 对 MyISAM 和 InnoDB 表有作用。也可以检查视图是否有错误，比如在视图定义中被引用的表已不存在。
MyISAM 类型的表可能会损坏，损坏后的表可能不能访问，会提示需要修复或者访问后返回错误的结果。如果指定了 DELAY_KEY_WRITE 选项，在每次修改执行完成时，不会立即将修改的索引数据写入磁盘，而是会写到内存中的键缓冲区，只有在清理键缓冲区或者关闭表的时候才会将对应的索引块写入磁盘。这种方式可以极大的提升写入性能，但是在数据库或者主机崩溃时会造成索引损坏，需要执行修复操作。MyISAM 类型的表提供修复的工具，用 CHECK TABLE 语句来检查 MyISAM 表的健康，并用 REPAIR TABLE 语句修复一个损坏的 MyISAM 表。表损坏可能导致数据库异常重新启动，需要尽快修复并尽可能地确认损坏的原因。
2.定期优化表（定期清理碎片）
optimize table sales;如果已经删除了表的一大部分，或者如果已经对含有可变长度行的表（含有 VARCHAR、BLOB 或 TEXT 列的表）进行了很多更改，则应使用 OPTIMIZE TABLE 命令来进行表优化。这个命令可以将表中的空间碎片进行合并，并且可以消除由于删除或者更新造成的空间浪费，但OPTIMIZE TABLE 命令只对 MyISAM、BDB 和 InnoDB 表起作用。
常用的优化操作
1.大批量导入操作
对于 MyISAM 表来说，导入大量的数据到一个非空的表中时，由于原先表中索引的存在，插入数据会跟着更新索引，所以直接导入的话用时较长，效率较低。可以采用 DISABLE KEYS 关闭索引更新后导入数据后再使用 ENABLE KEYS 用来打开非唯一索引。
alter table city disable keys;
load data infile 'test.txt' into table city;
alter table city enable keys;对于 InnoDB 表是按照主键的顺序保存的所以将导入的数据按照主键的顺序排列，可以有效地提高导入数据的效率。
在导入数据前执行 SET UNIQUE_CHECKS=0，关闭唯一性校验，在导入结束后执行SET UNIQUE_CHECKS=1，恢复唯一性校验，可以提高导入的效率
如果应用使用自动提交的方式，建议在导入前执行 SET AUTOCOMMIT=0，关闭自动提交，导入结束后再执行 SET AUTOCOMMIT=1，打开自动提交，也可以提高导入的效率。
2.优化 insert 语句
如果同一个用户插入多行数据，尽量使用一条 insert 语句插入多个值，这种方式将大大缩短客户端与数据库时间的连接、关闭等消耗。
insert into city values(254,'Seattle'),(255,'Prttwe'),(256,'Rovee')...如果从不同客户端插入很多行，能通过使用 INSERT DELAYED 语句得到更高的速度。当一个客户端使用INSERT DELAYED时，会立刻从服务器处得到一个确定。并且行被排入队列，当表没有被其它线程使用时，此行被插入。
当从一个文本文件装载一个表时，使用 LOAD DATA INFILE。
3.优化 group by 语句
Group by 会对查询得到的数据进行默认的排序，所以显式包括一个包含相同的列的 ORDER BY 子句，则对 MySQL 的实际执行性能没有什么影响。但如果想要避免排序结果的消耗，可以指定 order by null 禁止排序。
使用 explain 语句对有分组的语句进行分析，发现包含 order by null 的语句中 extra 信息少了一项 Using filesort，节省了时间。
4.优化 order by 语句
MySQL 可以使用一个索引来满足 ORDER BY 子句，而不需要额外的排序。但是一下几种情况则不使用索引：
- order 同一字段不同部分混合 asc 和 desc
- where 查询行的关键字与 order by 中所使用的不相同
- 对不同的关键字使用 order by
5.优化嵌套查询
子查询可以被更有效率的连接（JOIN）替代。
explain select * from sales2
where company_id not in
( select id from company2 )\G;
extra:Using where;
explain select * from sales2
left join company2
on sales2.company_id = company2.id
where sales2.company_id is null\G;
从执行计划中可以明显看出查询扫描的记录范围和使用索引的情况都有了很大的改善。连接（JOIN）之所以更有效率一些，是因为 MySQL 不需要在内存中创建临时表来完成这个逻辑上的需要两个步骤的查询工作。
6.优化 or 条件
对于含有 OR 的查询子句，如果要利用索引，则 OR 之间的每个条件列都必须用到索引；如果没有索引，则应该考虑增加索引。但是当在建有复合索引的列 company_id 和 moneys 上面做 OR 操作的时候，却不能用到索引。（复合索引最左条件）
7.使用中间表提高统计查询速度
中间表在统计查询中经常会用到，其优点如下:
中间表复制源表部分数据，并且与源表相“隔离”，在中间表上做统计查询不会对在线应用产生负面影响。
中间表上可以灵活的添加索引或增加临时用的新字段,从而达到提高统计查询效率和辅助统计查询作用。
8.关于索引的提示
use index
在查询语句中表名的后面，添加 USE INDEX 来提供希望 MySQL 去参考的索引列表，就可以让 MySQL 不再考虑其他可用的索引。
explain select * from sales2 use index (ind_sales2_id) where id = 3\G;
/*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sales2
type: ref
possible_keys: ind_sales2_id
key: ind_sales2_id
key_len: 5
ref: const
rows: 1
Extra: Using where
1 row in set (0.00 sec).
**************************** 1. row **************************/ignore index
如果只是单纯地想让 MySQL 忽略一个或者多个索引，则可以使用 IGNORE INDEX 作为 HINT。同样是上面的例子，这次来看一下查询过程忽略索引 ind_sales2_id 的情况：
explain select * from sales2 ignore index (ind_sales2_id) where id = 3\G;
/*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sales2
type: ALL
possible_keys: NULL
key: NULL
key_len: NULL
ref: NULL
rows: 1000
Extra: Using where
1 row in set (0.00 sec).
**************************** 1. row **************************/从执行计划可以看出，系统忽略了指定的索引，而使用了全表扫描。
force index
为强制 MySQL 使用一个特定的索引，可在查询中使用 FORCE INDEX 作为 HINT。例如，当不强制使用索引的时候，因为 id 的值都是大于 0 的，因此 MySQL 会默认进行全表扫描，而不使用索引，如下所示：
explain select * from sales2 where id > 0 \G;
/*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sales2
type: ALL
possible_keys: ind_sales2_id
key: NULL
key_len: NULL
ref: NULL
rows: 1000
Extra: Using where
1 row in set (0.00 sec).
**************************** 1. row **************************/当使用 FORCE INDEX 进行提示时，即便使用索引的效率不是最高，MySQL 还是选择使用了索引，这是 MySQL 留给用户的一个自行选择执行计划的权力。加入 FORCE INDEX 提示后再次执行上面的 SQL：
explain select * from sales2 force index (ind_sales2_id) where id > 0 \G;
/*************************** 1. row ***************************
id: 1
select_type: SIMPLE
table: sales2
type: range
possible_keys: ind_sales2_id
key: ind_sales2_id
key_len: 5
ref: NULL
rows: 1000
Extra: Using where
1 row in set (0.00 sec).
**************************** 1. row **************************/执行计划中使用了 FORCE INDEX 后的索引。
锁问题
- 表级锁：开销小，加锁快；不会出现死锁；锁定粒度大，发生锁冲突的概率最高,并发度最低。
- 行级锁：开销大，加锁慢；会出现死锁；锁定粒度最小，发生锁冲突的概率最低,并发度也最高。
- 页面锁：开销和加锁时间界于表锁和行锁之间；会出现死锁；锁定粒度界于表锁和行锁之间，并发度一般。
就具体应用来看，表级锁更适合以查询为主，只有少量按索引更新数据的应用，如 Web 应用。
行级锁则更适合与有大量的按索引条件并发并更新少量不同数据，同时又有并发查询的应用，如一些在线事务处理系统。
MyISAM 表锁
MySQL 的表级锁有两种模式：表共享读锁（Table Read Lock）和表独占写锁（Table Write Lock）。
MyISAM 表的读操作与写操作之间，以及写操作之间是串行的！当一个线程获得对一个表的写锁后，只有持有锁的线程可以对表进行更新操作。其他线程的读、写操作都会等待，直到锁被释放为止。
如何加表锁：
1.MyISAM 在执行查询语句（SELECT）前，会自动给涉及的所有表加读锁，在执行更新操作（UPDATE、DELETE、INSERT 等）前，会自动给涉及的表加写锁，这个过程并不需要用户干预，因此，用户一般不需要直接用 LOCK TABLE 命令给 MyISAM 表显式加锁。
给 MyISAM 表显示加锁，一般是为了在一定程度模拟事务操作，实现对某一时间点多个表的一致性读取。
例如，有一个订单表 orders，其中记录有各订单的总金额 total，同时还有一个订单明细表 order_detail，其中记录有各订单每一产品的金额小计 subtotal，假设我们需要检查这两个表的金额合计是否相符，可能就需要执行如下两条 SQL：
Lock tables orders read local, order_detail read local;
Select sum(total) from orders;
Select sum(subtotal) from order_detail;
Unlock tables;如果不先给两个表加锁，就可能产生错误的结果。“ local ”选项作用就是在满足 MyISAM 表并发插入条件的情况下，允许其他用户在表尾并发插入记录。
在执行 LOCK TABLES 后，在解锁回复默认获取锁的模式前，只能访问显式加锁的这些表，不能访问未加锁的表，MyISAM 总是一次获得 SQL 语句所需要的全部锁。这也正是 MyISAM 表不会出现死锁（Deadlock Free）的原因。
2.当使用 LOCK TABLES 时，不仅需要一次锁定用到的所有表，而且，同一个表在 SQL 语句中出现多少次，就要通过与 SQL 语句中相同的别名锁定多少次，否则也会出错！
lock table actor as a read,actor as b read;
并发插入：
MyISAM 表的读和写是串行的，但这是就总体而言的。在一定条件下，MyISAM表也支持查询和插入操作的并发进行。
MyISAM存储引擎有一个系统变量concurrent_insert，专门用以控制其并发插入的行为。
- 当 concurrent_insert 设置为0时，不允许并发插入。
- 当 concurrent_insert 设置为1时，如果MyISAM表中没有空洞（即表的中间没有被删除的行），MyISAM允许在一个进程读表的同时，另一个进程从表尾插入记录。这也是MySQL的默认设置。
- 当 concurrent_insert 设置为2时，无论MyISAM表中有没有空洞，都允许在表尾并发插入记录（同时，通过定期在系统空闲
时段执行OPTIMIZE TABLE语句来整理空间碎片，收回因删除记录而产生的中间空洞）
锁调度：
一个进程请求某个 MyISAM 表的读锁，同时另一个进程也请求同一表的写锁，MySQL 如何处理呢？答案是写进程先获得锁。不仅如此，即使读请求先到锁等待队列，写请求后到，写锁也会插到读锁请求之前！
因为 MySQL 认为写请求一般比读请求要重要。这也正是 MyISAM 表不太适合于有大量更新操作和查询操作应用的原因，因为，大量的更新操作会造成查询操作很难获得读锁，从而可能永远阻塞。
- 可以通过执行命令SET LOW_PRIORITY_UPDATES=1，使该连接发出的更新请求优先级降低。
- 系统参数max_write_lock_count设置一个合适的值，当一个表的读锁达到这个值后，MySQL就暂时将写请求的优先级降低，给读进程一定获得锁的机会。
一条包含 select 子查询的语句可能看起来巧妙，往往比较复杂，执行时间较长，使写者饥饿。可能情况下使用中间表提高统计查询速度，减少锁冲突。
InnoDB 锁
InnoDB 支持事务，采用行级锁。通过检查 InnoDB_row_lock 状态变量来分析系统上的行锁的争夺情况。
- 共享锁（S）：允许一个事务去读一行，阻止其他事务获得相同数据集的排他锁。（不会阻塞同一事务的 X 锁）
- 排他锁（X)：允许获得排他锁的事务更新数据，阻止其他事务取得相同数据集的共享读锁和排他写锁。
InnoDB 还有两种内部使用的意向锁（Intention Locks），这两种意向锁都是表锁。
- 意向共享锁（IS）：事务打算给数据行加行共享锁，事务在给一个数据行加共享锁前必须先取得该表的 IS 锁。
- 意向排他锁（IX）：事务打算给数据行加行排他锁，事务在给一个数据行加排他锁前必须先取得该表的 IX 锁。
意向锁是 InnoDB 自动加的，不需用户干预。对于 UPDATE、DELETE 和 INSERT 语句，InnoDB会自动给涉及数据集加排他锁（X)；对于普通 SELECT 语句，InnoDB 不会加任何锁；事务可以通过以下语句显示给记录集加共享锁或排他锁。
- 共享锁（S）：SELECT * FROM table_name WHERE ... LOCK IN SHARE MODE。
- 排他锁（X ) ：SELECT * FROM table_name WHERE ... FOR UPDATE。
行锁的实现：
InnoDB 行锁是通过给索引上的索引项加锁来实现的。
- 只有通过索引条件检索数据，InnoDB 才使用行级锁，否则，InnoDB 将使用表锁！
看起来 session_1 只给一行加了排他锁，但 session_2 在请求其他行的排他锁时，却出现了锁等待！原因就是在没有索引的情况下，InnoDB 只能使用表锁。当我们给其增加一个索引后，InnoDB 就只锁定了符合条件的行。
- 由于 MySQL 的行锁是针对索引加的锁，不是针对记录加的锁，所以虽然是访问不同行的记录，但是如果是使用相同的索引键，是会出现锁冲突的。（使用了同样的 id=1的索引，但是检索的其他列的内容不同，即与上锁的行不同的行，也会造成锁冲突，需要等待）
- 当表有多个索引的时候，不同的事务可以使用不同的索引锁定不同的行，另外，不论是使用主键索引、唯一索引或普通索引，InnoDB 都会使用行锁来对数据加锁。
- 当 InnoDB 优化通过全表扫描而不经过索引或者其他原因，则会对整个表上锁。因此，在分析锁冲突时，别忘了检查 SQL 的执行计划，以确认是否真正使用了索引。
间隙锁（Next_Lock_Key）
当我们用范围条件而不是相等条件检索数据，并请求共享或排他锁时，InnoDB 会给符合条件的已有数据记录的索引项加锁；对于键值在条件范围内但并不存在的记录，叫做“间隙（GAP)”，InnoDB 也会对这个“间隙”加锁，这种锁机制就是所谓的间隙锁（Next-Key 锁）。
举例来说，假如 emp 表中只有 101 条记录，其 empid 的值分别是 1,2,...,100,101，下面的 SQL：
Select * from emp where empid > 100 for update;是一个范围条件的检索，InnoDB 不仅会对符合条件的 empid 值为 101 的记录加锁，也会对empid 大于 101（这些记录并不存在）的“间隙”加锁。
引入间隙锁的目的是为了防止幻读。
mysql 表是否可以没有主键？
如果在创建表时没有显式地定义主键，则InnoDB存储引擎会按如下方式选择或创建主键：
1 首先判断表中是否有非空的唯一索引,如果有,则该列即为主键.
2 如果不符合上述条件,InnoDB存储引擎自动创建一个6字节的主键(该主键是查不到的).
4742
被折叠的 条评论
为什么被折叠?
到【灌水乐园】发言
