数据结构二叉树左小右大红黑树又名二叉平衡树HASH: in 不支持范围查找BTREE:BTREEBTREE变种mysql使用数据结构:非叶子节点不存储数据MYSQL 存储引擎myisam不支持事务innodb索引与数据存储在一个文件索引依赖于BTREE的有序递增原理mysql优化108军规索引索引结构innodb支持类型BTREE数据结构和FullText。默认创建类型为BTREE索引分类聚集索引只能 有一个通常为主键。聚集索引叶子节点保存的是行数据二级索引可以有多个二级索引叶子节点保存的是聚集索引的值。二级索引使用回表查询方式查询数据所需字段如果通过二级索引都可以获取到那么不会回表查询索引类型主键索引只能有一个唯一索引可以有多个字段值不可重复普通索引联合索引最左前缀原则必须存在与放在where后顺序无关使用大于小于条件范围查询时右侧的查询条件索引失效。大于等于或小于等于不会失效前缀索引场景索引字段为字符串且长度会很长时。覆盖索引需要返回的列在索引中全部可以找到。即联合索引包含所有select 后需要查询的列.避免回表查询提升效率索引失效索引列字段函数运算索引列字段字符串不加单引号索引列字段like查询%放到值前or分割的条件一侧有索引一侧无索引会失效。走索引效率不如全表扫描比如id1id最小值为1或索引列字段均为nullis not null走索引is null不走索引反之亦然sql提示一个字段建立了联合索引也建立了其他索引默认使用联合索引。可以通过sql提示进行索引选择.use index_nameignore index_name索引设计原则查询量大且查询频繁常作为查询条件排序分组操作的字段建立索引尽量选择区分度高尽量建立唯一索引区分度越高使用索引的效率越高如果字符串类型字段字段长度较长可以针对字段特点建立前缀索引尽量使用联合索引减少单列索引查询时联合索引很多时候可以覆盖索引节省存储空间避免回表提高查询效率控制索引数量并不是越多越好索引越多维护索引结构的代价越大会影响增删改的效率如果索引列不能存储null值请在创建表时使用NOT NULL 约束它当优化器知道每列是否包含NULL值时他可以更好地确定哪个索引最有效的由于查询sql性能分析查询数据库不同类型sql执行频次show global status like ‘com_______’查询慢查询日志是否开启是否开启show variables like ‘slow_query_log’修改配置文件开启慢查询日志#开启慢查询日志show_query_log 1#默认10秒修改为两秒long_query_time 2profile详情查看sql耗时情况查看是否支持select have_profiling;查看是否开启select profiling设置开启 set profiling 1;查看当前会话中所有sql耗时情况show profiles查看sql耗时具体体现位置show profile for query query_id;explain执行计划id: id相同执行顺序从上到下。id值不同越大越先执行*type: 连接类型。性能由好到差null,system,const,eq_ref,ref,range,index,all不查询表类型为null根据主键、唯一索引为const。根据非唯一索引为ref。*possible_key: 显示可能用到的索引有多个会展示多个*key实际用到的索引*key-len长度越短越好。索引的长度显示的为索引字段可能的最大长度。rows执行查询的行数innodb中是预估值仅供参考filterd越大越好。返回结果行数占所需读取行数的百分比sql优化insert优化大于500w使用load加载文件insert idinsertBatchByLoadData LOAD DATA LOCAL INFILE #{path,jdbcTypeVARCHAR} INTO TABLE 表名 CHARACTER SET utf8 -- 以什么字符作为分隔符 FIELDS TERMINATED BY || -- 每条记录的分隔符默认为’\n’即为换行符 LINES TERMINATED BY \n ( 字段, 字段, ... ) /insert主键优化规范主键长度尽量短顺序插入推荐使用自增主键长度短有序order by 优化排序可能出现两种情况using index效率高。直接通过索引返回数据。有升序有降序的时候可以创建索引时指定排序方式using filesort需要将查询返回的结果在排序缓冲区排序大批量量还可能用到磁盘文件groupby优化建立索引多字段分组注意最左前缀原则limit优化覆盖索引子查询查询id后作为关联表关联查询后分页count优化COUNT主键innodb引擎遍历每张表每一行主键取出返回给服务层服务层拿到主键直接按行进行累加COUNT字段innodb引擎遍历整张表把每一行的字段值取出返回给服务层。没有notnull约束服务层判断是否为null不为null计数累加。有notnull约束直接按行累加COUNT(1/其他数字)innodb引擎遍历整张表不取值服务层对于返回的每一行放1/其他数字进去直接进行累加COUNT(*)innodb引擎不会把全部字段取出来而是专门做了优化不取值服务层直接按行进行累加。效率排序*≈数字主键字段update优化根据索引字段修改不根据索引会行锁升级为表锁