MySQL索引相关

3:MySQL索引
创建索引语句:
CREATE INDEX indexName ON table_name (column_name)
如果是CHAR，VARCHAR类型，length可以小于字段实际长度；如果是BLOB和TEXT类型，必须指定 length。
修改索引语句
ALTER table tableName ADD INDEX indexName(columnName)
1:磁盘IO与预读
磁盘IO消耗大,计算机的优化就是当一次IO时,不光把当前磁盘地址数据,而且把相邻的数据也都读到内存缓存区域内
每一次IO读取到的数据为一页(page),一般为4k/8k,也就是我们读取到一页就是一次IO.
2:索引的原理
通过不断地缩小想要获取数据的范围来筛选出最终想要的结果，同时把随机的事件变成顺序的事件，
也就是说，有了这种索引机制，我们可以总是用同一种查找方式来锁定数据。想到了树==时间复杂度lgN
3:B+ tree应运而生!

这里只说一些重点，浅蓝色的块我们称之为一个磁盘块，可以看到每个磁盘块包含几个数据项（深蓝色所示）和指针（黄色所示），如磁盘块1包含数据项17和35，包含指针P1、P2、P3，P1表示小于17的磁盘块，P2表示在17和35之间的磁盘块，P3表示大于35的磁盘块。真实的数据存在于叶子节点即3、5、9、10、13、15、28、29、36、60、75、79、90、99。非叶子节点只不存储真实的数据，只存储指引搜索方向的数据项，如17、35并不真实存在于数据表中。
3.1:B+树的性质:
1:索引字段要尽量小:
IO次数取决于b+树的高度h,数据表的数据是N,每个磁盘的数据是m.则h=log(m+1)N.就是每块磁盘数据m越大,h越小.磁盘块的大小又是固定的,所以索引字段越小每块的数据项就越多.这也是为啥b+tree 为啥把真实数据项放在叶子节点.
2:索引最左匹配特性:
当b+树是复合的数据解构,(name,age,sex)时候,b+树会优先比较name来确定下一步方向.
3.2:索引的分类
1:普通索引index:
2:唯一索引
主键索引:primary key:加速查找+约束(不为空且唯一)
唯一索引:unique:加速查找+约束(唯一)
3:联合索引:
-primary key (id,name):联合主键索引
-unique(id,name):联合唯一索引
-index(id,name)联合普通索引
4:全文索引fulltext:用于搜索很长一篇文章时候,效果最好
5:空间索引spatial:了解就好,几乎不用
3.3:索引两大类型hash与btree
Hash:查询单条快,范围查询慢
Btree:层数越多,数据量指数级增长(默认用它)
3.4索引正确使用
3.4.1:索引覆盖
select * from s1 where id=123;
该sql命中了索引，但未覆盖索引。如果只返回id就会索引覆盖
3.4.2:联合索引
3.4.3:索引合并:单条索引name,id都创建ige索引,组合查询那么组合索引的效率要高于索引合并，如果是单条件查，那么还是用索引合并比较合理
3.4.4规则:
1:最左前缀匹配,mysql会一直向右匹配直到遇到范围查询(>、<、between、like)就停止匹配，
2:=和in可以乱序,
3:区分度高的字段,字段不重复比例
4:索引列不能参与计算

• 避免使用select *
• count(1)或count(列) 代替 count(*)
• 创建表时尽量时 char 代替 varchar
• 表的字段顺序固定长度的字段优先
• 组合索引代替多个单列索引（经常使用多个条件查询时）
• 尽量使用短索引
• 使用连接（JOIN）来代替子查询(Sub-Queries)
• 连表时注意条件类型需一致
• 索引散列值（重复少）不适合建索引，例：性别不适合
  3.4.5索引失效:
  —like ‘%XXX’
  select * from tb1 where email like ‘%cn’;
  —使用函数
  select * from tb1 where reverse(email) = ‘wupeiqi’;
  —or
  select * from tb1 where nid = 1 or name = ‘seven@live.com’;
  —类型不一致:如果email是varchar
  select * from tb1 where email = 999;
  —普通索引的不等于不会走索引
  select * from tb1 where email != ‘alex’
  特别的主键还是会走
  —order by:排序字段如果select字段不是该字段不走索引
  select name from s1 order by email desc;
  特别的主键排序还是会走
  —组合索引最左前缀
  如果组合索引为：(name,email)
  name and email – 使用索引
  name – 使用索引
  email – 不使用索引
• count(1)或count(列)代替count(*)在mysql中没有差别了
• create index xxxx on tb(title(19)) #text类型，必须制定长度