本文介绍了mysql的索引本质和mysql优化的一些细节。
索引的本质
索引的本质是一种数据结构。
数据库查询算法
- 顺序查找:无法适应数据量大的情况
- 二分查找:要求数据是有序的
- 二叉树查找:基于二叉树
- 索引:满足特定查找算法的数据结构
索引结构
B-Tree
Key-Data的存储结构。
搜索算法:先查找父节点,找到直接返回data,没找到则在子节点内进行递归查找,找到直接返回data,否则返回null。
性能:搜索深度小,而且利用磁盘预读原理可极大提升性能。
B+Tree
是B-Tree的变种之一,Key-Key-data的存储结构。
搜索算法:先查找父节点,父节点只是借点,不保存数据,找到后再往子节点去拿数据,找不到则到子节点内递归查找,找到返回子节点对应的数据,否则返回null。
性能:搜索深度大,逻辑上很近,但物理上可能很远,无法利用磁盘的局部性原理,但适用于外存索引。
mysql用的是B+Tree
mysql的MyISAM和InnoDB索引
MyISAM
存储结构:使用B+Tree作为索引结构,叶节点的data域存放的是数据记录的地址,非聚集索引。
InnoDB
存储结构:使用B+Tree作为索引结构,叶节点data域保存了完整的数据记录,是聚集索引。
类型 | MyISAM | InnoDB |
---|---|---|
索引结构 | B+Tree | B+Tree |
叶节点域存储 | 记录地址 | 记录本身 |
辅助索引域值 | 记录 | 主键(通过主键找记录) |
存放 | 索引和数据分开存储 | 记录即索引 |
自动增长 | 自动增长必须是索引,如果是组合索引,自动增长可以不在第一列 | 自动增长必须是索引,如果是组合索引也必须在第一列 |
主键 | 允许没有任何索引和主键 | 必须有主键,且最好是自增的主键,如果没有设定主键或非空唯一索引,那会自动生成一个6字节的主键(不可见) |
count() | 保存有总行数,直接读取 | 遍历全表,但如果加了where条件两者都一样 |
全文索引 | 支持fulltext类型的全文索引 | 不支持全文索引,但可以通过sphinx代替 |
存储结构 | 每张表被存放在三个文件:1、frm-表格定义;2、MYD(MYData)-数据文件;3、MYI(MYIndex)-索引文件。所有的表都保存在同一个数据文件中(也可能是多个文件,或者是独立的表空间文件) | InnoDB表的大小只受限于操作系统文件的大小,一般为2G |
存储空间 | 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全文索引 | 支持 | 不支持。可以通过使用Sphinx从InnoDB中获得全文索引,会慢一点 |
注意:
范围查询可用到索引,但是如果有多个范围,则只有第一个范围可以用到索引,后面的范围不行。但是用了Between不一定意味着就是范围,当between用到某些索引列上时,其实是多值匹配,作用与IN是一样的。要把多值匹配和范围匹配区分开。
Mysql优化
结构优化
- 联合索引只要用到了第一列,且条件中两个字段中间没有加入其它条件,就会使用索引。
- 查询条件中含有函数或表达式,不会使用索引。
- 范围列可以用到索引(必须是最左前缀),但是范围列后面的列无法用到索引。同时,索引最多用于一个范围列,因此如果查询条件中有两个范围列则无法全用到索引。
- 如果通配符%不出现在开头,则可以用到索引,但根据具体情况不同可能只会用其中一个前缀。
- 记录太少不用建索引。(2000条)
- 索引选择性差不用建索引。选择性计算公式:Index Selectivity = Cardinality / #T。选择性的取值范围为(0,1],选择性越高的索引价值越大,这是由B+Tree的性质决定的。
SELECT count(DISTINCT(title))/count(*) AS Selectivity FROM employees.titles;
- Innodb主键设计规则:1、必须有主键。2、最好是自增字段;3、不宜过长。
查询优化
- MySQL建表,字段需设置为非空,需设置字段默认值。
- MySQL建表,字段需NULL时,需设置字段默认值,默认值不为NULL。
- MySQL建表,如果字段等价于外键,应在该字段加索引。
- MySQL建表,不同表之间的相同属性值的字段,列类型,类型长度,是否非空,是否默认值,需保持一致,否则无法正确使用索引进行关联对比。
- MySQL使用时,一条SQL语句只能使用一个表的一个索引。所有的字段类型都可以索引,多列索引的属性最多15个。
- 如果可以在多个索引中进行选择,MySQL通常使用找到最少行的索引,索引唯一值最高的索引。
- 建立索引index(part1,part2,part3),相当于建立了 index(part1),index(part1,part2)和index(part1,part2,part3)三个索引。
- MySQL针对like语法必须如下格式才使用索引:
SELECT * FROM t1 WHERE key_col LIKE 'ab%'
- SELECT COUNT(*) 语法在没有where条件的语句中执行效率没有SELECT COUNT(col_name)快,但是在有where条件的语句中执行效率要快。
- 在where条件中多个and的条件中,必须都是一个多列索引的key_part属性而且必须包含key_part1。各自单一索引的话,只使用遍历最少行的那个索引。
- 在where条件中多个or的条件中,每一个条件,都必须是一个有效索引。
- ORDER BY 后面的条件必须是同一索引的属性,排序顺序必须一致(比如都是升序或都是降序)。
- 所有GROUP BY列引用同一索引的属性,并且索引必须是按顺序保存其关键字的。
- JOIN 索引,所有匹配ON和where的字段应建立合适的索引。
- 对智能的扫描全表使用FORCE INDEX告知MySQL,使用索引效率更高。
- 定期ANALYZE TABLE tbl_name为扫描的表更新关键字分布 。
- 定期使用慢日志检查语句,执行explain,分析可能改进的索引。
- 条件允许的话,设置较大的key_buffer_size和query_cache_size的值(全局参数),和sort_buffer_size的值(session变量,建议不要超过4M)。
PHP完成一个事务的例子
事务处理(多sql要完成的任务看做一个事务,任何一个sql出错,整个事务都要撤销,如果都成功才去提交)。mysql使用innodb引擎才能支持事务。
默认表都是自动提交的(autocommit),如果需要手工控制事务,需要做如下操作:
- 关闭自动提交
set autocommit=0;
- start事务
start transaction;
- commit / rollback
$mysqli = new mysqli("10.1.1.15","web","web","test","9188");
$mysqli->autocommit(0);
//注意,在此模式下,此连接中的前一个事务回滚或者提交以后,会马上开启下一个事务。
$error = true;
$sql = "update inno set sex=2 where name='andy'";
$result = $mysqli->query($sql);
if(!$result){
$error = false;
echo "sql执行失败";
}else{
if($mysqli->affected_rows == 0){
$error = false;
echo "数据没有改变";
}else{
echo "sql sussceful";
}
if($error){
echo "事务成功";
$mysqli->commit();
}else{
echo "事务失败";
$mysqli->rollback();
}
$mysqli->close();