mysql索引总结

1，首先是mysql的体系结构，从外到内分为四层：
连接层，
服务层（sql解析优化，缓存等），
引擎层，show engines; 查看所有的引擎。
存储层（文件和日志）；
所以select的执行过程就是，首先是通过连接层连接服务器，然后是查询缓存，缓存没有进行sql解析优化，然后是到底层文件查询数据。

2，常用的存储引擎：
InnnoDB,特点支持事务，支持行级锁，支持外键约束。 xxx.idb 文件是底层文件。
逻辑存储结构：idb文件就是一个表空间文件，接下来是段，再就是区(1M)，最后是页 (16K)，然后是行（row）,其中物理层面的最小存储单元就是页 page,一页的大小是 16K 这个很关键，16K大约可以存储1000个左右的长度为8字节的索引。也就是是一页最多有1000个左右的指针，对应另外的1000个page，每个page16K。
MyISAM: 不支持事务，支持表锁不支持行锁，访问速度快。
Memory: 访问较快，hash索引。存放在内存中。

3，mysql 索引：
索引是引擎层实现的，不同引擎支持的索引不同。
b+tree 所有的引擎都支持。 它不仅仅是所有的数据都在叶子节点，而且每页之间还会构建一个双向链表。
hash索引被memory引擎支持。
另外根据功能分：
主键索引（聚集索引），聚集索引是指关联具体表数据的索引，如果有主键么主键就是聚集索引，否则第一个唯一索引时聚集索引，通过聚集索引查询不用回表查询
唯一索引（二级索引），二级索引查询出来是聚集索引，如果需要行数据需要回表查询。
常规索引（二级索引），
全文索引（二级索引）。

相关sql语句:create index; show index; drop index;

4,SQL 性能分析：
a，sql执行频率：show global status like 'com_______'; //查询数据各种sql执行的次数
b，慢查询日志：slow_query_log
c，profile分析，select @@have_profiling; set global profiling=1;show profiling;show profile for query query_id;
d，最主要的还是explain:
各个字段的意义：
id:表的执行顺序，值越大越先执行。
select_type:查询类型。
type: null, system ,const ,eq_ref,ref,range,index,all，效率越来越低。
possible_key:可能用到的索引。
key:实际用到的索引。
key_len：索引长度。
rows:执行查询的行数。
filtered:返回的结果占查询所有行数的百分比 越高性能越好。

5，索引使用：
a，最左前缀法则：（联合索引 多个索引） 最左边的必须存在，中间跳过后面的索引会失效。
b，联合索引出现范围查询，如 > < 等右面的索引会失效，尽量使用 >= <= 不会失效。
c，不要在索引列上进行运算操作。
d，字符串类型的索引需要加引号，否则会触发类型转换，会使索引失效。
e，模糊查询：左边模糊查询会使索引失效。
f，or 只有两侧都有索引 才会生效。
g，数据分布影响，mysql会评估如果走索引时间更长，则直接全表扫描。
h，索引提示：
select * from t use index(idx_xxx); //建议mysql使用某个索引
select * from t ignore index(idx_xxx)；//忽略
select * from t forceindex(idx_xxx);//强制
i，覆盖索引：查询需要的列在索引中已经全部存在，不需要回表，效率更高。extral:useing index condition需要回表。
j，前缀索引：对于长度较大的字符串，会让索引变得很大，可以截取字符串的一部分前缀降低索引的大小。
create index idx_xx on table name(column(n)).
截取多长呢：select count(distinct substring(emial,1,5))/count(*) from user; 值越接近1越快，需要取舍。
k，单列索引与联合索引：每次只会使用一个索引，根据查询的数据不同，创建单列或者联合索引，配合索引提示，避免回表查询，建议使用联合索引。

6，索引设计原则：
a，数据量较大，且查询比较频繁的表建立索引
b，针对常作为where，order by，group by的列建立索引。
c，选择值区分度高的列建立索引。
d，对于长字符串，建立前缀索引
e，尽量使用联合索引，达到覆盖索引的效果。
f，控制索引数量。
g，尽量使用not null 约束。

7，mysql日志：
a，mysql 安装之后默认密码在，var/log/mysqld.log 里面。
b，redo log 是保证数据的持久性的日志，当修改数据时先写如redolog,如果数据库发生崩溃，重启之后根据这个redolog来重新修改数据。
c，undo log 是回滚日志，就是修改操作的逆操作，如果回滚的话 使用undo log 来执行。如果正常提交，undolog 会被清除。
d，binlog 对数据库的增删改都会记录binlog，可以用来数据的备份、恢复和同步。
e，slow log,慢查询日志，首先是开启，然后配置文件路径默认是 /var/lig/mysql/{host}-slow.log 其中host是值客户端地址 比如 localhost-slow.log。
f，error log,错误日志

8，根据b+tree的数据结构 ，以及mysql的逻辑存储结构页，16KB，计算一页存储的索引的数量：
n * 8 + (n+1)*6 = 16 * 1024,n是指索引的数量，一个索引8个字节，一个指针6个字节，n = 1170
16K的页可以存储 大约1170个指针，也就是对应1170多个页，假设一行的数据是1KB，一页可以存储1170 * 16=18724 行的数据，这是两层b+tree的树，
如果是三层树的话，第二层只存储索引，每页存储 1170个索引，每页索引指针又对应 18724的数据，就是21907748条数据，所以千万级别的数据走索引的效率还是能保证的。