mysql必知必会

索引

索引失效

OR操作符

• OR条件的两边都是同一个索引列的情况下，如果WHERE条件是主键，则可以使用索引
• OR条件的两边都是同一个索引列的情况下，如果WHERE条件不是主键，则是否使用索引取决于MySQL查询优化器的代价估算。
• OR条件的两边是不同的索引列，是否使用索引取决于MySQL查询优化器的代价估算。如果能使用索引，MySQL会使用索引，如果代价太高，仍然会走全表索引
• 如果多个OR条件中有其中一个条件没有索引，则必须进行全表索引

索引底层原理

索引分类

1）从存储结构上来划分

• Btree 索引（B+tree，B-tree)
• 哈希索引
• full-index 全文索引

2）从应用层次上来划分

• 主键索引：主键索引
• 普通索引：即一个索引只包含单个列，一个表可以有多个单列索引。
• 唯一索引：索引列的值必须唯一，但允许有空值。
• 复合索引：一个索引包含多个列。

3）从表记录的排列顺序和索引的排列顺序是否一致来划分

• 聚集索引：表记录的排列顺序和索引的排列顺序一致。
• 非聚集索引：表记录的排列顺序和索引的排列顺序不一致。

索引建立原则

• 最左前缀匹配原则
• 尽量选择区分度高的列作为索引
• 索引列不能参与计算
• 尽量的扩展索引，不要新建索引

存储引擎

不同的存储引擎提供不同的存储机制、索引技巧、锁定水平等功能

"	MyISAM	InnoDB
存储结构	每张表被存放在三个文件：frm - 格定义,MYD (MYData)- 数据文件,MYI (MYIndex)- 索引文件	所有的表都保存在同一个数据文件中（也可能是多个文件，或者是独立的表空间文件），InnoDB 表的大小只受限于操作系统文件的大小，一般为 2GB
存储空间	MyISAM 可被压缩，存储空间较小	InnoDB 的表需要更多的内存和存储，它会在主内存中建立其专用的缓冲池用于高速缓冲数据和索引
可移植性、备份及恢复	由于 MyISAM 的数据是以文件的形式存储，所以在跨平台的数据转移中会很方便。在备份和恢复时可单独针对某个表进行操作	免费的方案可以是拷贝数据文件、备份 binlog，或者用 mysqldump，在数据量达到几十 G 的时候就相对痛苦了
事务安全	不支持 每次查询具有原子性	支持 具有事务 (commit)、回滚 (rollback) 和崩溃修复能力 (crash recovery capabilities) 的事务安全 (transaction-safe (ACID compliant)) 型表
AUTO_INCREMENT	MyISAM 表可以和其他字段一起建立联合索引	InnoDB 中必须包含只有该字段的索引
SELECT	MyISAM 更优
INSERT		InnoDB 更优
UPDATE		InnoDB 更优
DELETE		InnoDB 更优 它不会重新建立表，而是一行一行的删除
COUNT without WHERE	MyISAM 更优。因为 MyISAM 保存了表的具体行数	InnoDB 没有保存表的具体行数，需要逐行扫描统计，就很慢了
COUNT with WHERE	一样	一样，InnoDB 也会锁表
锁	只支持表锁	支持表锁、行锁 行锁大幅度提高了多用户并发操作的性能。但是 InnoDB 的行锁，只是在 WHERE 的主键是有效的，非主键的 WHERE 都会锁全表的
外键	不支持	支持
FULLTEXT 全文索引	支持	不支持（5.6.4 以上支持英文全文索引） 可以通过使用 Sphinx 从 InnoDB 中获得全文索引，会慢一点
索引	支持 B-tree/FullText/R-tree 索引类型	支持 Hash/B-tree 索引类型

锁

事务

事务的特性（ACID）

原子性（Atomicity）、一致性（Consistency）、隔离性（Isolation）、持久性（Durability）

隔离级别

• 读未提交（ read-uncommitted ）
• 读已提交（ read-committed ）
• 可重复读（ repeatable-read ）
• 串行化 （ serializable ）

隔离级别产生问题

隔离级别	脏读	不可重复读	幻读
读未提交（ read-uncommitted ）	是	是	是
读已提交（ read-committed ）	-	是	是
可重复读（ repeatable-read ）	-	-	是
串行化（ serializable ）	-	-	-

字符集&字段类型

MySql 类型名	GetColumnClassName 返回值	返回的 Java 类
bit(1)(MySQL-5.0)	BIT	java.lang.Boolean
bit(大于1)(MySQL-5.0)	BIT	byte[]
tinyint	TINYINT	如果 tinyInt1isBit 配置设置为 true(默认为 true)，是java.lang.Boolean，存储空间为 1；否则是为 java.lang.Integer
bool	boolean	TINYINT 参见 TINYINT。这些是 TINYINT(1) 另一种写法
smallint(M)	[unsigned]	SMALLINT [UNSIGNED] java.lang.Integer(不管是否无符)
mediumint(M)	[unsigned]	MEDIUMINT [UNSIGNED] java.lang.Integer
int integer(M)	[unsigned]	INTEGER [UNSIGNED] java.lang.Integer；无符的话是 java.lang.Long
bigint(M)	[unsigned]	BIGINT [UNSIGNED] java.lang.Long；无符的话是 java.math.BigInteger
float(M,D)	FLOAT	java.lang.Float
double(M,B)	DOUBLE	java.lang.Double
decimal(M,D)	DECIMAL	java.math.BigDecimal
date	DATE	java.sql.Date
datetime	DATETIME	java.sql.Timestamp
timestamp(M)	TIMESTAMP	java.sql.Timestamp
time	TIME	java.sql.Time
year(2/4)	YEAR	如果 yearIsDateType 配置设置为 false，返回的对象类型为 java.sql.Short；如果设置为 true(默认为 true)，返回的对象类型是 java.sql.Date，其具体时间是为一月一日零时零分
char(M)	CHAR	java.lang.String(除非该列字符集设置为 BINARY，那样返回 byte[])
varchar(M)	[binary]	VARCHAR java.lang.String(除非该列字符集设置为 BINARY，那样返回 byte[])
binary(M)	BINARY	byte[]
varbinary(M)	VARBINARY	byte[]
tinyblob	TINYBLOB	byte[]
tinytext	VARCHAR	java.lang.String
blob	BLOB	byte[]
text	VARCHAR	java.lang.String
mediumblob	MEDIUMBLOB	byte[]
mediumtext	VARCHAR	java.lang.String
longblob	LONGBLOB	byte[]
longtext	VARCHAR	java.lang.String
enum(‘value1’,‘value2’,…)	CHAR	java.lang.String
set(‘value1’,‘value2’,…)	CHAR	java.lang.String

高质量高性能sql规范建议

索引建立原则

索引失效情况

减少数据查询与返回，批量

字段类型建立原则

• 查询 SQL 尽量不要使用 select *，而是 select 具体字段

减少网络开销
可能就不会使用到覆盖索引了，就会造成回表查询

• 如果知道查询结果只有一条或者只要最大 / 最小一条记录，无索引情况建议用 limit 1，防止全表扫描
• 应尽量避免在where子句中使用or来连接条件，or可能会使索引失效，从而全表扫描
• 优化limit分页

//方案一 ：返回上次查询的最大记录(偏移量)
select id，name from employee where id>10000 limit 10.

//方案二：order by + 索引
select id，name from employee order by id  limit 10000，10

• 优化你的like语句，最左原则
• 使用where条件限定要查询的数据，避免返回多余的行
• 避免在索引列上使用mysql的内置函数
• 避免在 where 子句中对字段进行表达式操作，这将导致系统放弃使用索引而进行全表扫
• Inner join 、left join、right join，优先使用Inner join，如果是left join，左边表结果尽量小
• 避免在 where 子句中使用!=或<>操作符，否则将引擎放弃使用索引而进行全表扫描。
• 使用联合索引时，注意索引列的顺序，一般遵循最左匹配原则
• 对查询进行优化，应考虑在 where 及 order by 涉及的列上建立索引，尽量避免全表扫描
• 如果插入数据过多，考虑批量插入
• 在适当的时候，使用覆盖索引
• 慎用distinct关键字，过滤的过程会占用系统资源，cpu时间
• 删除冗余和重复索引
• 如果数据量较大，优化你的修改/删除语句，建议分批操作
• where子句中考虑使用默认值代替null

如果把null值，换成默认值，很多时候让走索引成为可能，同时，表达意思会相对清晰一点

如果mysql优化器发现，走索引比不走索引成本还要高，肯定会放弃索引，这些条件！=，>is null，is not null经常被认为让索引失效，其实是因为一般情况下，查询的成本高，优化器自动放弃的

• 不要有超过5个以上的表连接
• exist & in的合理利用，小表驱动大表，小的数据集驱动大的数据集
• 检索结果中不会有重复的记录，尽量用 union all 替换 union
• 索引不宜太多，一般5个以内，索引虽然提高了查询的效率，但是也降低了插入和更新的效率，可能会重建索引
• 尽量使用数字型字段，若只含数值信息的字段尽量不要设计为字符型，相对于数字型字段，字符型会降低查询和连接的性能，并会增加存储开销
• 索引不适合建在有大量重复数据的字段上，如性别这类型数据库字段
• 尽量避免向客户端返回过多数据量，分页查询
• 当在SQL语句中连接多个表时,请使用表的别名，并把别名前缀于每一列上，这样语义更加清晰
• 尽可能使用varchar/nvarchar 代替 char/nchar，变长字段存储空间小，可以节省存储空间，对于查询来说，在一个相对较小的字段内搜索，效率更高
• 为了提高group by 语句的效率，可以在执行到该语句前，把不需要的记录过滤掉
• 如何字段类型是字符串，where时一定用引号括起来，否则索引失效，不加单引号时，是字符串跟数字的比较，它们类型不匹配，MySQL会做隐式的类型转换
• 使用explain 分析你SQL的计划